数据库

作者: 廖少少 | 来源:发表于2016-09-17 11:06 被阅读149次

数据库


四个基本概念

  • 数据(Data)

    • 数据是数据库中存储的基本对象
    • 数据(Data):描述事物的符号记录描述事物的符号可以是数字、文字、图形、图像、声音、语言等
  • 数据库(Database)

    • 数据库(Database,简称DB)是长期储存在计算机内、有组织的、可共享的大量数据的集合。这些数据用于某企业的应用系统中(制造公司、银行、医院、大学、政府部门等)
  • 数据库管理系统(DBMS)

    • DBMS( DataBase Management System )
    • 位于用户与操作系统之间系统软件
      一组用以管理、维护、访问数据库的程序
    • 基本目标
      提供一个可以方便地、有效地存取数据库信息的环境
    • 常见数据库管理系统:
      • Oracle
      • Access
      • Sql Server
      • DB2
      • Sybase
      • Infomix
    • DBMS的主要功能:
      • 数据定义功能
        • 提供数据定义语言(DDL)
        • 定义数据库中的数据对象
      • 数据组织、存储和管理
        • 分类组织、存储和管理各种数据
        • 确定组织数据的文件结构和存取方式
        • 实现数据之间的联系
        • 提供多种存取方法提高存取效率
      • 数据操纵功能
        • 提供数据操纵语言(DML)
        • 实现对数据库的基本操作 (查询、插入、删除和修改)
      • 数据库的事务管理和运行管理
        • 数据库在建立、运行和维护时由DBMS统一管理和控制
        • 保证数据的安全性、完整性、多用户对数据的并发使用发生故障后的系统恢复
      • 数据库的建立和维护功能(实用程序)
        • 数据库初始数据装载转换
        • 数据库转储
        • 介质故障恢复
        • 数据库的重组织
        • 性能监视分析等
      • 其它功能
        • DBMS与网络中其它软件系统的通信
        • 两个DBMS系统的数据转换
        • 异构数据库之间的互访和互操作
  • 数据库系统(DBS)

    • 什么是数据库系统(Database System,简称DBS)
      在计算机系统中引入数据库后的系统构成
    • 数据库系统的构成
      • 数据库
      • 数据库管理系统(及其开发工具)
      • 应用系统(用户)
      • 数据库管理员
        • 数据库是为多用户共享的,因此需要有人进行规划,设计,协调,维护和管理,负责这些工作的人员称为DBA(Database Administrator)。

基本表的定义、修改和删除

SQL(Structured Query Language)是1974年由IBM的Ray Boyce和Don Chamberlin依据E.F.Codd关系数据库的12条准则的数学定义提出来的。SQL具有简单的关键字语法和强大的功能包括数据查询、数据操纵、数据定义和数据控制,是一种综合的、通用的、高度非过程化的关系数据库语言,是关系数据库预言的标准。以下是SQL中最常用查询语法。

  • 定义基本表:

      CREATE TABLE <表名>
      (
      <列名> <数据类型> [ <列级完整性约束条件> ]
      [,<列名> <数据类型> [ <列级完整性约束条件>] ] …
      [,<表级完整性约束条件> ] 
      );
    

如果完整性约束条件涉及到该表的多个属性列,则必须定义在表级上,否则既可以定义在列级也可以定义在表级。

- 数据类型
    - SQL中域的概念用数据类型来实现
    - 定义表的属性时 需要指明其数据类型及长
    度
    - 选用哪种数据类型
        - 取值范围
        - 要做哪些运算
    - 不同的数据库产品支持的数据类型不完全相同,下面是Microsoft SQL Server常用数据类型
        ![](https://img.haomeiwen.com/i2106579/73e4b66f4534ed28?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

- 在列级完整性约束定义处可以定义的约束
    - NOT NULL:限制列取值非空。
    - DEFAULT:给定列的默认值。
    - UNIQUE:限制列取值不重。
    - CHECK:限制列的取值范围。
    - PRIMARY KEY:指定本列为主码。
    - FOREIGN KEY:定义本列为引用其他表的外码。
    - 使用形式为:
            [FOREIGN KEY(<外码列名>)]
            REFERENCES <外表名>(<外表列名>)
>几点说明:
> - NOT NULL和DEFAULT只能是列级完整性约束;
> - 其他约束均可在表级完整性约束处定义。
> - 注意以下几点:
>     1. 如果CHECK约束是定义多列之间的取值约束,则只能在表级完整性约束处定义;
>     2. 如果表的主码由多个列组成,则也只能在表级完整性约束处定义,并将主码列用括号括起来,即:PRIMARY KEY(列1{[,列2 ] …});
>     3. 如果在表级完整性约束处定义外码,则“FOREIGN KEY (<外码列名>)”部分不能省

- 建表示例:
        CREATE TABLE Student
        (
            Sno CHAR(9) PRIMARY KEY, --设为主键(主码),列级完整性约束条件
            Sname CHAR(20) UNIQUE, --Sname取唯一值
            Ssex CHAR(2),
            Sage SMALLINT,
            Sdept CHAR(20)
        );
        
        CREATE TABLE Course
        (
            Cno CHAR(4) PRIMARY KEY,
            Cname CHAR,
            Cpno CHAR(4), --先修课
            Ccredit SMALLINT,
            FOREIGN KEY (Cpno) REFERENCES Course(Cno), --Cpno是外键,Course表的Cno是主键(Course是被参照表,Cno是被参照列)
        );
        
        CREATE TABLE SC
        (
            Sno CHAR(9),
            Cno CHAR(4),
            Grade SMALLINT,
            PRIMARY KEY (Sno, Cno), --主键由两个属性构成,必须作为表级完整性进行定义
            FOREIGN KEY (Sno) REFERENCES Student(Sno), --表级完整性约束条件,Sno是外码,被参照表是Student 
            FOREIGN KEY (Cno) REFERENCES Course(Cno) --表级完整性约束条件, Cno是外码,被参照表是Course
        );

- 约束

![UNIQUE KEY 约束](https://img.haomeiwen.com/i2106579/2f69820c930160ee.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
![PRIMARY KEY 约束](https://img.haomeiwen.com/i2106579/b381d411742b434b.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
![FOREIGN KEY 约束](https://img.haomeiwen.com/i2106579/c1668027b2137689.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
  • 修改基本表

    • 标准语法:
      ALTER TABLE <表名>
      [ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
      [ ADD <表级完整性约束>]
      [ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
      [ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
      [ALTER COLUMN <列名><数据类型> ] ;
    • 例子:
      ALTER TABLE Student ADD S_entrance DATE; --向Student表增加“入学时间”列,其数据类型为日期型。不论基本表中原来是否已有数据,新增加的列一律为空值
      ALTER TABLE Student ALTER COLUMN Sage INT; --将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数。
      ALTER TABLE Course ADD UNIQUE(Cname); --增加课程名称必须取唯一值的约束条件。
  • 删除基本表

    • 标准语法:
      DROP TABLE <表名>[RESTRICT|CASCADE];
    • RESTRICT:删除表是有限制的。
      欲删除的基本表不能被其他表的约束所引用
      如果存在依赖该表的对象,则此表不能被删除
    • CASCADE:删除该表没有限制。
      在删除基本表的同时,相关的依赖对象一起删除
    • 例子:
      DROP TABLE Student; --注意外键约束

单表查询

  1. 关于聚集函数、GROUP BY子句、HAVING子句

    • 聚集函数
      • 计数
        • COUNT([DISTINCT|ALL] *)
        • COUNT([DISTINCT|ALL] <列名>)
      • 计算总和
        • SUM([DISTINCT|ALL] <列名>)
      • 计算平均值
        • AVG([DISTINCT|ALL] <列名>)
      • 最大最小值
        • MAX([DISTINCT|ALL] <列名>)
        • MIN([DISTINCT|ALL] <列名>)
      • 上述函数中除COUNT(*)外,其他函数在计算过程中均忽略NULL值。
    • GROUP BY子句
      • 对查询结果分组
      • GROUP BY子句将查询结果按某一列或多列值分组,值相等的为一组。
        • 多列值的分组,按列的次序依次分组
      • 将查询结果分组的目的是为了细化聚集函数的作用对象。
      • GROUP BY子句分组:
        • 未对查询结果分组,聚集函数将作用于整个查询结果
        • 对查询结果分组后,聚集函数将分别作用于每个组
        • 作用对象是查询的中间结果表
        • 按指定的一列或多列值分组,值相等的为一组
      • 例子:统计每个学生选修的课程中成绩高于70分的课程数


    • 使用HAVING
      • 如果分组后,还要求按一定的条件对这些组进行筛选,输出满足一定条件的组,则使用HAVING子句指定筛选条件。
      • HAVING子句的条件一般应为分组的字段或进行某聚集函数的运算,在分组统计时进行判断。而WHERE子句的条件,在分组之前进行判断。
      • HAVING用于对分组自身进行限制,它有点象WHERE子句,但它用于组而不是对单个记录。
      • 例子:查询修课门数等于或大于4门的学生的平均成绩和选课门数。
        SELECT Sno, AVG(Grade) 平均成绩,
        COUNT() 修课门数
        FROM SC
        GROUP BY Sno
        HAVING COUNT(
        ) >= 4
      • 例子:查询选修计算机系各门课程的选课人数,要求按选课人数的多少降序排序(假设计算机系的课程号以CS开头)
        SELECT Cno ,COUNT(Sno)
        FROM dbo.SC
        WHERE Cno like 'CS%'
        GROUP BY Cno
        ORDER BY COUNT(Sno) DESC
      • 例子:
      • 例子:统计选修的课程中成绩高于70分的课程数超过2门的学生学号


  2. 各种查询语法示例(来自幕课网):
    /--------------------------------------------------
    --SQL Server 2012 & T-SQL Lesson 3 - BasicQueries
    ----------------------------------------------------
    /

     --Topic 1
     SELECT <table fields list> 
     FROM <table names list>
     WHERE <row constraints specification>
     GROUP BY <grouping specification>
     HAVING <grouping selection specification>
     ORDER BY <order rules specification>
     
     --Topic 2
     use [AdventureWorks2012]
     go
     
     select Top 100 * from [Production].[Product]  --查询该表的前100行,尽量避免只用*查询数据,因为可能数据过于庞大
     
     SELECT * 
     FROM SALES.SALESORDERDETAIL
     
     --Topic 3
     select * from [Production].[Product]
     
     select ProductID, Name, ProductNumber, Color, Size, ListPrice 
     from Production.Product
     
     select ProductID, Name, ProductNumber, Color, Size, ListPrice 
     from Production.Product
     order by listprice desc --desc=descending order ; asc=ascending order
     
     select ProductID, Name, ProductNumber, Color, Size, ListPrice 
     from Production.Product
     order by listprice desc,Name --按照listprice降序排列,再按照Name升序排列
     
     select ProductID, Name, ProductNumber, Color, Size, ListPrice 
     from Production.Product
     order by 2 --即按第2列(Name列)升序排列
     
     --Topic 4
     select ProductID, Name, ProductNumber, isnull(Color,''), isnull(Size,''), ListPrice --isnull函数用来让NULL值变为replacement_value
     from Production.Product
     
     --Topic 5
     select ProductID, Name, ProductNumber, 
     isnull(Color,'') as Color, isnull(Size,'') as Size123, --using an alias (本来列名为Name的,但为了更友好地输出,把Name变为ProductName输出,注意列名还是Name)
     ListPrice 
     from Production.Product
     
     select ProductID, Name as ProductName, 
     'The list price for ' + ProductNumber + ' is $ ' + convert(varchar,ListPrice) +'.' ,--using the concatenation to join character end-to-end.
     'The list price for ' + ProductNumber + ' is $ ' + convert(varchar,ListPrice) +'.' as [Description] --using brackets to let SQL server conside the strin as a column name
     from Production.Product
     --convert函数用来使某列强转为某数据类型
     
     --Topic 6
     select BusinessEntityID,rate from [HumanResources].[EmployeePayHistory]
     
     select BusinessEntityID
     ,rate*40*52 as AnnualSalary -- +/-/*/%
     ,round(rate*40*52,1) as AnnualSalary --round函数用来规定保留几位小数,在这里保留一位小数
     ,round(rate*40*52,0) as AnnualSalary --这里不保留小数
     from [HumanResources].[EmployeePayHistory]
     
     select BusinessEntityID
     ,(rate+5)*40*52 as AnnualSalary
     from [HumanResources].[EmployeePayHistory]
     
     --Topic 7
     select * from [Sales].[SalesOrderHeader]
     
     select * from [Sales].[SalesOrderHeader]
     where SalesPersonID=275
     
     select * from [Sales].[SalesOrderHeader]
     where SalesOrderNumber='so43670'
     
     select * from [Sales].[SalesOrderHeader]
     where TotalDue>5000
     
     select SalesOrderID,OrderDate,SalesPersonID,TotalDue as TotalSales 
     from [Sales].[SalesOrderHeader]
     where SalesPersonID=275 and TotalDue>5000 --Comparison conditions: =,>,<,>=,<=,<>
     
     select SalesOrderID,OrderDate,SalesPersonID,TotalDue as TotalSales 
     from [Sales].[SalesOrderHeader]
     where SalesPersonID=275 and TotalDue>5000 and Orderdate between '2005-08-01' and '1/1/2006' --两种datetime的表示方式,between...and... 包括两个日期
     
     select SalesOrderID,OrderDate,SalesPersonID,TotalDue as TotalSales 
     from [Sales].[SalesOrderHeader]
     where SalesPersonID=275 and TotalDue>5000 and Orderdate >= '2005-08-01' and Orderdate < '1/1/2006' --包括2005-08-01,不包括2006-1-1
     
     select * from [Production].[Product]
     where name ='Mountain-100 Silver, 38'
     
     --Topic 8 
     --模糊查询
     select * from [Production].[Product]
     where name like'Mountain'
     
     select * from [Production].[Product]
     where name like'%Mountain%' --Wildcard(通配符) % matches any zero or more characters (在通配符“%”的前后都可以有任意个字符)
     
     select * from [Production].[Product]
     where name like'mountain%'
     
     select * from [Production].[Product]
     where name like'_ountain%' -- "_" matches any single character
     
     --Topic 9
     select * from [Production].[Product]
     where color in ('red','white','black') --查询出color为red/white/black的
     
     select * from [Production].[Product]
     where size in ('60','61','62')
     
     select * from [Production].[Product]
     where class not in ('H') -- same as using: <> 'H' (小于大于,即不等于)
     
     --Topic 10
     select * from [Production].[Product]
     where size is null
     
     select * from [Production].[Product]
     where size is not null --在工作中经常用到
     
     --Topic 11
     select * from [Production].[Product]
     where color ='white'or color ='black' --color为white或black的
     
     select * from [Production].[Product]
     where color ='white'and color ='black'
     
     select SalesOrderID,OrderDate,SalesPersonID,TotalDue as TotalSales 
     from [Sales].[SalesOrderHeader]
     where (SalesPersonID=275 or SalesPersonID=278)  and TotalDue>5000 --不加括号
     
     --Topic 12
     select count(SalesPersonID) --统计[Sales].[SalesOrderHeader]表中SalesPersonID列不为空的数量,最后返回一个数字
     from [Sales].[SalesOrderHeader]
     where SalesPersonID is not null
     
     select distinct(SalesPersonID) --查询[Sales].[SalesOrderHeader]表中SalesPersonID列不为空且互不相同的SalesPersonID列
     from [Sales].[SalesOrderHeader]
     where SalesPersonID is not null
     
     select count(distinct(SalesPersonID)) --统计上面的行数量
     from [Sales].[SalesOrderHeader]
     where SalesPersonID is not null
     
     --Topic 13
     select 
     Avg(TotalDue) as AverageTotalSales --aggregate functions(聚合函数)
     from [Sales].[SalesOrderHeader]
     
     select 
     Avg(TotalDue) as AverageTotalSales
     ,Min(TotalDue) as MinimumTotalSales  
     ,Max(TotalDue) as MaximumTotalSales
     ,Sum(TotalDue) as SummaryTotalSales
     from [Sales].[SalesOrderHeader]
     
     select SalesPersonID,Max(TotalDue) as MaximumTotalSales 
     from [Sales].[SalesOrderHeader] 
     --Error Message: Column 'Sales.SalesOrderHeader.SalesPersonID' is invalid in the select list 
     --because it is not contained in either an aggregate function or the GROUP BY clause.
     
     --解决办法,把SalesPersonID列放在group by里面
     
     select SalesPersonID,Max(TotalDue) as MaximumTotalSales 
     from [Sales].[SalesOrderHeader]
     where SalesPersonID is not null
     group by SalesPersonID
     order by SalesPersonID
     
     select SalesPersonID,OrderDate,Max(TotalDue) as MaximumTotalSales 
     from [Sales].[SalesOrderHeader]
     where SalesPersonID is not null
     group by SalesPersonID,OrderDate --Remember to put all un-aggregated columns after "group by"!!!
     order by SalesPersonID
     
     select SalesPersonID,OrderDate,Max(TotalDue) as MaximumTotalSales 
     from [Sales].[SalesOrderHeader]
     where SalesPersonID is not null
     group by SalesPersonID,OrderDate 
     having Max(TotalDue)>150000 --having是对聚合函数的限定,所以不能用一般的语法,必须对所用聚合函数(在这里是Max函数)限定)
     order by SalesPersonID
     
     ----The classical T-SQL query!!!
     select SalesPersonID,OrderDate,Max(TotalDue) as MaximumTotalSales 
     from [Sales].[SalesOrderHeader]
     where SalesPersonID is not null and OrderDate >='2007/1/1'
     group by SalesPersonID,OrderDate 
     having Max(TotalDue)>150000
     order by OrderDate desc
    

    代码相关链接:SQL Server基础教程——幕课网
    模糊查询相关链接:SQL模糊查询语句和Escape转义字

连接查询

待续...

相关文章

  • MySQL数据库day01

    系统数据库 ​​​ 创建数据库 ​​​ ​​​ ​​​ ​​​ 查看所有数据库 使用数据库 修改数据库 删除数据库...

  • 数据库操作

    创建数据库: 选择数据库: 查看数据库: 修改数据库: 删除数据库:

  • Ubuntu操作mysql数据库命令

    一、连接数据库 连接本地数据库 退出数据库 二、操作数据库 创建数据库 显示数据库 删除数据库 连接数据库 查看状...

  • 4-14

    创建数据库 createdatabase数据库名称; 删除数据库 dropdatabase数据库名称; 查看数据库...

  • [后端开发] Mysql学习笔记

    1.0 数据库 创建数据库 查看所有数据库 删除数据库 切换数据库名 查看正在使用的数据库 2.0 数据库表 创...

  • 2020最新最全数据库系统安全

    数据库标识与鉴别 数据库访问控制 数据库安全审计 数据库备份与恢复 数据库加密 资源限制 数据库安全加固 数据库安...

  • 2020-01-05 sql基本概念

    数据库用来储存数据。 数据库的种类大致有:层次数据库、关系数据库、面向对象数据库、XML数据库、键值数据库。 关系...

  • Mysql数据库基本操作

    连接数据库 退出数据库 查看数据库版本 显示数据库时间 查看当前使用的数据库 查看所有数据库 数据库备份 导入数据...

  • mysql命令行操作

    准备 登陆 数据库操作 查看现有数据库 创建数据库 删除数据库 查看数据库 使用数据库 查看当前数据库 表操作 新...

  • 1.7.1 MySQL数据库学习

    1. 数据库命令行学习 进入数据库 创建数据库 查看数据库列表 删除数据库 使用数据库 查看当前数据库 2. 表 ...

网友评论

    本文标题:数据库

    本文链接:https://www.haomeiwen.com/subject/cfaiettx.html