【数据库】三、SQL语言

三、SQL语言

1 概述

• 什么是SQL语言？

  Structured Query Language 结构化查询语言。所有关系数据库产品的标准。

• SQL语言分类：

  • DDL：数据定义语言
  • DML：数据操作语言（有些教材分为数据查询和数据操纵）
  • DCL：数据控制语言
  • INDEX(索引)、VIEW(视图)、嵌入式SQL

• SQL语言的特点：

  1. 综合统一
  2. 高度非过程化：用户只需提出"做什么"，而不是"怎么做"
  3. 面向集合的操作方式
  4. SQL语言可以嵌入到高级语言程序中

• SQL支持数据库三级模式结构：

  • 基本表(模式)：一个表对应一个存储文件
  • 视图(外模式)：是一个虚表，由基本表导出
  • 存储文件(内模式)

2 数据定义(DDL)

定义表、定义视图、定义索引。包括创建、删除、修改操作。

2.1 定义数据库

• 注意：所有的表都必须依赖数据库而存在，所以在定义表之前要先创建一个数据库。

  CREATE DATABASE containerName; -- 创建一个容器
  USE containerName; -- 使用指定的数据库
  

• 数据库中的文件分为数据文件（又分为主数据文件mdf，次数据文件ndf）与事务文件（ldf）

• 数据库维护：

  -- 修改数据库名
  ALTER DATABASE test modify name= test_1
  -- 删除数据库
  DROP DATABASE dbtest
  -- 修改数据库 sjkDB 中数据文件的初始大小，将其初始大小改为 9MB,最大为120MB
  ALTER DATABASE sjkDB
  MODIFY FILE ( NAME=sjkDB_data, SIZE=9, MAXSIZE=120)
  -- 为数据库添加日志文件
  ALTER DATABASE sjkDB
  ADD LOG FILE
  ( NAME=sjkDBlog1, FILENAME='E:\teaching\sjkDBlog1.ldf', SIZE=3, MAXSIZE=20, FILEGROWTH=1 )
  

2.2 定义基本表

一个表必须包括：表名、属性名、属性数据类型、完整性约束

• 创建基本表的语法

  []里的内容表示可以省略，但是最基本的必须有主键PRIMARY KEY

  CREATE TABLE 表名 (
      列名1 数据类型 [约束], -- []里的内容表示可以不写
      列名2 数据类型 [约束],
      ...
      列名N 数据类型 [约束],
      ...
      其他表级完整性约束
  );
  

• 常用的数据类型

  数据类型	描述
  CHAR(n)	定长为n的字符串，不足长度会用空格填充
  VARCHAR(n)	最大长度为n的变长字符串，不足的不会自动补空格
  INT, SMALLINT, BIGINT	长整数 (4字节)、短整数 (2字节)、大整数 (8字节)
  BIT	只能是0或1
  DECIMAL(p,d)	精确的小数类型，p表示总数字位数，d表示小数位数
  FLOAT(n)	可选精度浮点数，n表示总位数，通常指的是存储精度
  DATE	日期，格式为YYYY-MM-DD（年-月-日）
  TIME	时间，格式为HH:MM:SS（时:分:秒）
  DATETIME	日期和时间的组合，通常格式为YYYY-MM-DD HH:MM:SS

• 常用的完整性约束

  • 主键约束：PRIMATY KEY
  • 参照完整性约束(外键-另一个表的主键)：FOREIGN KEY … REFERENCES …
  • 唯一性约束：UNIQUE
  • 非空值约束：NOT NULL
  • 自定义约束：CHECK

  主键和唯一性的异同：相同点是每列中不能有重复的值；不同点是主键不能为空，而唯一性约束可以为空，且一个表中只能有一个主键约束，但可以有很多个唯一性约束。

• 表级完整性约束写法：如果完整性涉及道多个属性列必须定义在表级上

  写在所定义表的所有列之后

  • 多列组成的主键

    PRIMARY KEY(列名1,列名2,...)
    

  • 定义外键：当前表的某列是其他表的主键

    FOREIGN KEY(当前表的列名) REFERENCES 参照的表名(参照表的主键)
    

  • 设置外键并添加约束

    foreign key (course_id) references course(course_id)
        on delete set null -- 当另一个表中删除时设为空
    

  • ON DELETE CASECATE 级联删除(删的更深)

  • ON UPDATE CASECATE 级联修改

2.3 修改基本表

• 基本语法

  1. 添加 ADD

     -- 添加整列
     ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [约束];
     -- 添加约束
     ALTER TABLE 表名 ADD 约束类型(列名);
     

  2. 删除 DROP

     -- 删除某列
     ALTER TABLE 表名 DROP COLUMN 列名;
     -- 删除主键约束
     ALTER TABLE 表名 DROP PRIMARY KEY;
     -- 删除某列约束
     ALTER TABLE 表名 DROP CONSTRAINT 约束名;
     -- 删除某列约束(SQL Server)
     ALTER TABLE 表名 DROP CONSTRAINT 约束名(列名);
     

  3. 更新 MODIFY

     -- 修改某列数据类型
     ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型;
     -- 修改列名
     ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名;
     -- 修改表名
     ALTER TABLE 旧表名 RENAME TO 新表名;
     -- 设置默认值
     ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT 默认值;
     -- 取消默认值
     ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT;
     

2.4 删除基本表

• 删除基本表后，磁盘中对该表的描述，索引，数据都会被删去，视图还保留，但无法引用。

• 语法

  DROP TABLE 表名
  

• ON DELETE CASECATE 级联删除(删的更深)

• ON UPDATE CASECATE 级联修改

3 数据操作(DML)

3.1 数据查询

• 基本语法

  SELECT 列1,列2,... -- 指定显示的列
  FROM 表1,表2,... -- 指定查询对象
  WHERE 条件 -- 查定查询条件
  GROUP BY 分组列名 HAVING 条件 -- 对查询结果按指定列进行分组
  ORDER BY 排序列名 -- 对查询结果指定升序/降序
  

  Tips：先执行FROM 再执行后面的子句 最后才执行SELECT

3.1.1 单表查询

单表查询仅涉及一张表，是最简单的查询操作

基本操作

• 查询某些列

  SELECT 列1,列2,... FROM 表名
  

• 查询所有列

  SELECT * FROM 表名 -- 按照原始顺序查询。也可以把所有列名列出来，可以指定顺序查询
  

• 查询表达式值

  -- 例:查询学生的出生年份
  SELECT year(getdate())-sage FROM student -- year(getdate())是系统内置函数      
  

• 给查询到的列起别名

  给列起别名并不会影响数据库中原来的名字

  SELECT 列名 as 列别名 FROM 表名 -- as可以省略，别名有空格，必须写单引号
  

• 嵌入字符串：SELECT后面还可以跟字符串，这样查询出来的结果每一行都多出来一列指定的字符串

  SELECT 列名 'hello' FROM 表名 -- 结果：第二列所有值都为hello
  

聚集函数

聚集函数用于选取出数据后对数据进行统计操作

• 主要的集函数有：

  COUNT统计个数；SUM计算总和；AVG求平均值；MAX求最大值；MIN求最小值。统计时忽略NULL。

• 基本语法：

  -- 统计某列的所含值得个数
  SELECT COUNT(列名) FROM 表名
  

条件查询-WHERE

• 去掉重复的元组(行）

  关键字DISTINCT:去重 ALL:显示所有(默认)

  SELECT DISTINCT 列1,列2,... FROM 表名
  

• 选择运算：通过WHERE Condition子句实现

  • 比较大小：= > < >= <= != or <> !> !<

    SELECT 列名 FROM 表名 where 列名='name' -- name是指定字符串
    

  • 确定范围：BETWEEN 下限 AND 上限 NOT BETWEEN 下限 AND 上限

    -- 例子1：查询年龄在10-20之间的学生姓名，年龄
    SELECT sname,sage FROM student WHERE sage BETWEEN 10 AND 20
    -- 例子2：查询年龄不在在10-20之间的学生姓名，年龄
    SELECT sname,sage FROM student WHERE sage NOT BETWEEN 10 AND 20
    

  • 确定谓词：IN <值表> NOT IN <值表>

    表示所查询的表在元组中某列的值在<值表>当中

    -- 查询计算机系、数学系的学生姓名
    SELECT snam FROM student WHERE Sdept IN ('CS','MA')
    

  • 字符串匹配：LIKE '匹配串' NOT LIKE '匹配串'

    模糊匹配，配合通配符使用才有意义 %-任意长度的字符串 _-任意单个长度字符

    -- 查询姓刘学生的姓名，年龄
    SELECT sname,sage FROM student WHERE Sname LIKE '刘%'
    

    ESCAPE短语：用户查询字符串中有_或%，要使用换码字符对通配符进行转义

    -- 查询以DB_开头的课程号和学分
    SELECT Cno,Ccredit FROM Course WHERE Cname LIKE 'DB\_%' ESCAPE '\' -- 这里_不再是通配符，而是普通的字符串
    

  • 涉及空值的查询：IS NULL IS NOT NULL

    注意：不能用=NULL来代替

    -- 查询学生成绩为空的学号和课程号
    SELECT Sno,Cno FROM SC WHERE Grade IS NULL
    

• 多重条件查询：使用关键字AND或OR将条件子句连接起来。AND优先级高于OR。

  -- 例子1：查询年龄在10-20之间的学生姓名，年龄
  SELECT sname,sage FROM student WHERE sage>=10 AND sage<=20
  -- 例子2：查询年龄不在在10-20之间的学生姓名，年龄
  SELECT sname,sage FROM student WHERE sage<10 OR sage>20
  

分组查询-GROUP BY

• 注意：SELECT后面可以是分组依据的列，也可以是聚集函数，但不能是其他的列名！

  为什么呢？比如我按课程号分组，选这个课程的人数超过2人，那么我查询学生学号时，这两个人的学号应该显示哪个呢，这样就会冲突。

• 基本语法

  SELECT [分组列名],[聚集函数] FROM 表名 GROUP BY 分组列名
  

• GROUP BY和WHERE配合使用

  -- 求各个课程号及相应课程成绩>90分的学生人数
  SELECT Cno,COUNT(Sno) FROM SC WHERE Grade>90 GROUP BY Cno
  

• HAVING对分组以后的结果筛选

  • 语法

    SELECT [分组列名],[聚集函数] FROM 表名 GROUP BY 分组列名 HAVING 条件
    -- HAVING后面的意思是，只有满足某些条件的组才会被留下来
    

  • 例子1：查询选修了3门以上课程的学生学号

    SELECT Cno FROM SC GROUP BY Cno HAVINF COUNT(*)>3
    

  • 例子2：查询3门课程90分以上的学生学号和其90分以上的课程数

    SELECT Cno,COUNT(*) 
    FROM SC WHERE Grade>90 -- 先把表中90分的留下来
    GROUP BY Cno HAVINF COUNT(*)>3 -- 按学号分组，分完组后组中个数大于3的组才留下来
    

• WHERE和HAVING的区别

  • WHERE是对整个表进行筛选，后不可跟聚合函数
  • HAVING是对分组之后的结果进行筛选，后可跟聚合函数

排序子句-ORDER BY

将查询得到的结果排序后再显示。默认是升序ASC，降序DESC

• 基本语法

  SELECT 列名... FROM 表名 WHERE 条件 ORDER BY 主排序列 排序方式,次排序列 排序方式
  -- 如果是升序 ASC可以不写
  

• 注意：只能对最终的查询结果排序，不能对中间结果排序。

3.1.2 连接查询

同时涉及多个表的查询。用来连接两个表的条件称为连接谓词。多个表中有相同的列要加前缀以示区别，如果是等值连接的列则不用。

交叉连接

• 定义：广义笛卡尔积。把表1的每一行和表2的每一行都连接一遍；很少使用，无任何连接条件，所以无意义。

• 语法：两种方式

  • 直接用逗号把两表隔开

    SELECT Student.* SC.* FROM Student,SC
    

  • 使用CROSS JOIN关键字把两表连接起来

    SELECT Student.* SC.* FROM Student CROSS JOIN SC
    

等值连接

• 定义：交叉连接结果的筛选。大部分情况是同名列相等，一般是主键与外键相等。

• 语法：使用=号连接相等的结果。并且注意多个表的相同列名要加上前缀。

  -- 查询每个学生及其选修课程的情况
  SELECT Student.* SC.* FROM Student,SC WHERE Student.Sno=SC.Sno
  

• 注：两个表WHERE后就跟一个条件即可进行等值连接，3个表则要跟2个条件，以此类推。

• 复合连接就是WHERE后面有好多个AND加上条件罢了

自身连接

• 定义：一个表与自身连接

• 语法：需要给表起别名以示区别；必须加前缀。

• 例子：查询每一门课程的先修课程名

  SELECT a1.Cname,a2.Cname -- 从新的表中选特定的列
  FROM Course a1,Course a2 -- 先把两个表拼成一排
  WHERE a1.Cpno=a2.Cno -- 筛选出符合条件的连接起来放在一个新的表里 Cpno是先修课程学号
  

内连接

其实是等值连接，只不过两个表之间用JION隔开而不是,

外连接

把被舍弃的值，也保留在结果中，但是要加NULL

• 左外连接

  -- 语法
  SELECT 列名... FROM 表1 LEFT OUTER JOIN 表2 ON 查询条件
  

• 右外连接

  -- 语法
  SELECT 列名... FROM 表1 RIGHT OUTER JON 表2 ON 查询条件 -- 如果右表没有匹配的则列返回空值
  

• 全外连接

  -- 语法
  SELECT 列名... FROM 表1 FULL OUTER JION 表2 ON 查询条件
  

  

3.1.3 嵌套查询

• 查询块：一个SELECT语句称为一个查询块

• 嵌套查询：将一个查询块嵌套在另一个查询块的WHERE或HAVING子句中

• 两种方式：

  1. 不相关子查询：子查询的查询条件不依赖于父查询。

     由里往外，先执行完子查询再执行父查询。子查询的结果用于建立父查询的查找条件。

  2. 相关子查询：子查询的查询条件依赖于父查询

     执行一行外层查询就会执行完整一次子查询，然后把内层查询的结果代替子查询作用于外层查询的条件。

• 嵌套查询的谓词：

  1. IN：子查询的结果是一个集合

     -- 例子：查询了选修课程号为2的学生姓名
     SELECT Sname FROM Student WHERE Sno IN  -- ②再通过学号查学生姓名
     (SELECT Sno FROM SC WHERE Cno=2) -- ①子查询的结果，先把课程号为2的学生学生求出来
     

  2. 比较运算符：子查询的结果是单个值。子查询一定跟在比较运算符之后。

  3. ANY或ALL：子查询的结果是多个，配合比较运算符使用。

     ANY：小于子查询任意一个 >ALL：大于子查询的所有

     -- 语法
     SELECT 列名... FROM 表名 WHERE 列名>ALL (子查询)
     

  4. EXISTS：子查询的结果是空或者非空

     EXISTS的值：如果子查询为非空则为True，空则为False。True表示当前行被选中，False则不被选中。

     NOT EXISTS则相反。

     例子：查询选了1号课程的学生姓名
     SELECT Sname FROM Student S WHERE EXISTS -- 有返回值则为True被选中
     (SELECT * FROM SC C WHERE S.Sno=C.Sno AND Cno=1) -- 如果学选了一号课程则有返回值
     

• 子查询的限制

  • 不能使用ORDER BY子句

3.1.4 集合查询

• 并集 UNION

  会自动去掉重复的元组。

  <查询块> UNION <查询块>
  

• 交集 INTERSECT

  大部分系统不提供，所以用户要自己用别的方式来实现。用AND来实现。

• 差集 EXCEPT

  大部分系统也不提供。

3.2 数据更新

3.2.1 插入数据

插入单个元组

• 语法

  INSERT INTO 表名(列名1,列名2,...) VALUES(常量1,常量2,...)
  

• 两种情况

  • 没有列名，则所有的数据项常量都必须有
  • 有列名，后面的数据项常量要与列名的顺序匹配上
  • 考到了，背错了，气死！！

插入多个元组

• 语法

  INSERT INTO 表名 VALUES(常量,常量,...),(常量,常量,...)
  

插入子查询结果

• 前提：子查询的结果要与表的列关系对应上，否则要写清楚列名

• 语法

  INSERT INTO 表名 SELECT查询块
  

注意：执行插入语句时不能破坏原表的完整性规则。

• 实体完整性
• 参照完整性
• 用户自定义完整性
  • 非空、非重复、值域范围

3.2.2 修改数据

• 功能：修改表中满足WHERE子句条件的元组

• 语法

  UPDATE 表名 SET 列名=表达式 WHERE 条件
  

• 修改单个元组、修改多个元组

• 带子查询的修改

  -- 例子：将计算机系全体学生成绩置0
  UPDATE SC SET Grade=0 WHERE 'CS' = 
  (SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno) -- 相关子查询
  

3.2.3 删除数据

• 功能：删除指定表中符合WHERE子句的元组

• 语法

  DELETE FROM 表名 WHERE 条件
  

• 注意：要满足完整性规则。如果没有WHERE将删除整个表

• 也有三种方式，删除单个/多个元组。

• 带子查询的删除

  -- 例子：删除计算机系学生的选课记录
  DELETE FROM SC WHERE Sno IN 
  (SELECT Sno FROM Student WHERE Sdept=SC) -- 不相关子查询
  

• TRUNCATE也可以删除表

  • DELETE是一行一行删，当有很多行数据时效率低。执行的是DML命令。
  • TRUNCATE是先DROP删除整个表，再CREATE创建一个表，效率高。实际上是执行了两条DDL命令。

4 数据控制(DCL)

数据控制也称为数据保护。包括四个方面：

1. 安全性控制
   • DBA授权，包括授权GRANT，回收权力REVOKE
2. 完整性控制
   • 数据库中数据的有效性、正确性、相容性
3. 并发性控制
   • 多个用户并发的对数据库进行操作时，对他们加以控制、协调，保证并发操作的正确执行和数据库的一致性。
4. 恢复
   • 数据库发生故障时，对数据库进行恢复

5 视图

视图是虚拟表，可以从多个表中取出数据构成虚拟表，目的是方便用户操作、多角度理解数据，保证逻辑独立性、保证表的某些数据机密性；对应数据库三级模式结构的外模式。

5.1 定义视图

• 建立视图

  就是把子查询的结构当作视图的结构

  CREATE VIEW 视图名(列名1，列名2...) AS SELECT子查询 WITH CHECK OPTION
  

• 列名：如果不起则默认为子查询的列名；基于多个表的视图最好写列名；查询得到的数据中有表达式的列要起列名

• SELECT语句：创建视图时并不执行，查询视图的时候才执行，并且子查询中必须有表的主键

• WITH CHECK OPTION：可选项；要求透过视图进习更新的时候要满足子查询的条件，防止这种通过视图更新不属于视图范围的数据

• 基于视图的视图：AS后面查询的对象是视图

5.2 删除视图

• 语法

  由该视图导出的其他视图要被显示删除，已经无法使用。注意：删除表时由该表导出的视图也要显示删除。

  DROP VIEW 视图名
  

5.3 查询视图

• 查询视图的语法：与查询基本表完全一样。

• 关键：视图不存任何数据，所以查询视图相当于查询了基本表，只不过它更方便用户操作。

• 视图实体化法：把中间的结果转化为表

• 视图消解法(常用)：把视图定义的子查询和用户的查询结合，转化为等价的对表的查询

• 例子

  • V_SG视图的定义

    CREATE VIEW V_SG(Sno,Gavg) AS SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno;
    

  • 在V_SG视图中查询平均成绩在90分以上的学生学号和平均成绩

  • 对视图的查询

    SELECT Sno,Gavg FROM V_SG WHERE Gavg>90
    

  • 转化为真实的表查询

    SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno HABING AVG(Grade)>90;
    

  • 视图消解法局限：有些情况不能正确使用，比如上述例子，AVG聚集函数不能再WHERE子句中使用

5.4 更新视图

包括删除，修改，插入。

• 同样是转化为更新表。视图消解法(常用)，试图实体化表。
• 视图可更新的条件
  • 行列子集视图：只取自一个表，且包含主键
  • 视图不能包含聚合函数、GROUP BY 子句、DISTINCT 关键字（列不包含计算出来的结果）
  • 其他大多数视图都不可更新

6 索引

对应三级模式的内模式。涉及到磁盘上数据的存储。简单理解为书的目录。

6.1 建立索引

• 目的：加快查询速度的有效手段。结合维护的代价和查询的性能提升确定是否要创建索引。

• 如何建立？

  当我创建表确定主键、唯一键时，数据库会帮我们自动建立索引。维护索引也由数据库自动完成。

• 人为建立(唯一)索引：

  当不存在主键、唯一键时，建立索引才有意义

  CREATE UNIQUE INDEX 索引名 ON 表名(列名) 
  -- 表示按表的某列升序建立索引
  CREATE UNIQUE INDEX 索引名 ON 表名(列名 DESC) 
  -- 表示按表的某列降序建立索引
  CREATE UNIQUE NONCLUSTERED INDEX 索引名 ON 表名(列名) -- 非聚集索引
  

• 人为建立(普通)索引：

  CREATE INDEX 索引名 ON 表名(列名)
  

• 创建复合索引：

  CREATE UNIQUE INDEX 索引名 ON 表名(列名1 DESC,列名2 ASC) 
  

• 什么是聚集索引？

  磁盘上按该索引的顺序进行物理存储的。一个表只有一个聚集索引，且不需要人为创建。

• 什么是非聚集索引？

  不要求数据表的数据按照索引关键字顺序排序。也就是说表的物理顺序与索引关键字顺序不同。

• 什么是唯一索引？

  人为创建的索引。但是注意，为列创建索引时列索引关键字必须符合唯一性。

6.2 删除索引

• 语法

  DROP INDEX 索引名 ON 表
  

6.3 一般准则

• 在经常过滤，查询的列上建立索引
• 在不同值较少的列上不必要建立索引，如性别字段
• 对于经常存取的列避免建立索引

7 嵌入式SQL

• SQL的两种使用方式：

  • 在终端直接交互

  • 嵌入在高级语言

• 嵌入式SQL的实现方式：

  源程序(高级语言+SQL) -> DBMS预处理 -> SQL转化为函数调用的形式 -> 高级语言编译程序(调用SQL数据库) -> 执行程序