数据库原理——第三章:关系数据库标准语言SQL

参考书目《数据库系统概论(第5版)》

SQL概述

SQL 结构化查询语言(Structured Query Language) 的主要特点:

  • 综合统一
  • 高度非过程化
  • 面向集合的操作方式
  • 以同一种语法结构提供多种使用方式
  • 语言简洁,易学易用

SQL的动词

SQL功能 动词
数据查询 SELECT
数据定义 CREATE,DROP,ALTER
数据操作 INSERT,UPDATE,DELETE
数据控制 GRANT,REVOKE

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

  • 1.定义基本表

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLE <表名> (
    <属性名> <数据类型> [列级完整性约束],
    <属性名> <数据类型> [列级完整性约束],
    ...
    [表级完整性约束],
    [表级完整性约束],
    ...
    )[CHARSET=utf8];

  • 2.数据类型

数据类型 含义
CHAR(n) 长度为n的定长字符串
VARCHAR(n) 最大长度为n的变长字符串
TINYBLOB/BLOB/LONGBLOB 不同大小的二进制形式的文本数据
TINYTEXT/TEXT/LONGTEXT 不同大小的文本数据
TINTINT/SMALLINT/BIGINT 不同字节大小的整数
FLOAT/DOUBLE 单/双精度浮点数
DATE 日期值(YYYY-MM-DD)
TIME 时间值(HH:MM:SS)
YEAR 年份值(YYYY)
DATETIME 混合日期和时间值
TIMESTAMP 混合日期和时间值,时间戳
  • 3.修改基本表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    #增加列(属性)
    ALTER TABLE <表名> ADD <新属性名> <数据类型> [列级完整性约束];

    #删除列(属性)
    ALTER TABLE <表名> DROP <选择的属性名>;

    #添加表级完整性约束条件
    ALTER TABLE <表名> ADD [列级完整性约束];

    #删除键约束
    ALTER TABLE <表名> DROP PRIMARY KEY;
    ALTER TABLE <表名> DROP FOREIGN KEY <键名>;
    /*可以通过使用SHOW CREATE TABLE <表名>;来查询外键的键名*/

    #修改属性的数据类型
    ALTER TABLE <表名> MODIFY <选择的属性名> <新数据类型>;

  • 4.删除基本表

    1
    DROP TABLE [IF EXIST] <表名>;


索引的建立与删除

  • 1.建立索引

    1
    2
    /*若属性为字符串等类型,可以加上length表示根据前多个字符作为索引,从而节省建立索引的开销*/
    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX <索引名> ON <表名>(属性名[(length)],...);

  • 2.删除索引

    1
    2
    ALTER TABLE <表名> DROP INDEX <索引名>;
    DROP INDEX <索引名> ON <表名>;

  • 3.查看索引

    1
    SHOW INDEX FROM [<数据库名>.]<表名>;


数据字典

  • 数据字典是关系数据库系统内部的一组系统表,它记录了数据库中所有的定义信息,包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。
  • 在进行查询优化查询处理时,数据字典中的信息是其重要的依据。

数据的查询

单表查询

  • 1.选择表中的的若干列

    1
    SELECT [ALL|DISTINCT] <目标属性表达式>,... FROM <表名或视图名>;

    • SECLECT默认使用ALL,表示将结果全部显示。但在有些情况下,投影出来的结果会出现重复行,此时可以用DISTINCT去掉重复行
    • <目标属性表达式>可以简单的写为目标属性名
    • 如果属性列的显示顺序与在基本表中的顺序相同,也可简单的将<目标属性表达式>指定为 *
    • <目标属性表达式>不仅可以是表中的属性列,也可以是算数表达式字符串常量函数等。
    • 用户可以通过指定别名来改变查询结果的列标题
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    #查询指定列或全部列
    SELECT Sname,Sno FROM Student;
    SELECT * FROM Student;

    #使用算术表达式、字符串常量和函数
    SELECT Sname,‘Year of Birth’,2019-Sage,LOWER(Sdept) FROM Student;

    #使用别名
    SELECT Sname NAME,‘Year of Birth’ BIRTH,2019-Sage BIRTHDAY,
    LOWER(Sdept) DEPARTMENT FROM Student;
  • 2.查询满足条件的元组

    1
    SELECT <目标属性表达式>,... FROM <表名或视图名> WHERE <条件表达式>;

    查询满足指定条件的元组可以通过WHERE子句实现。例子如下

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    (1)比较大小
    SELECT Sname FROM Student WHERE Sdept='CS';
    SELECT DISTINCT Sno FROM SC WHERE Grade<60;

    (2)确定范围
    SELECT Sname,Sage FROM Student WHERE Sage [NOT] BETWEEN 20 AND 23;

    (3)确定集合(谓词IN用来查找属性值属于指定集合的元组)
    SELECT Sname FROM Student WHERE Sdept IN ('CS','MA','EE');

    (4)字符匹配
    SELECT * FROM Student WHERE Sname LIKE '刘%';

    (5)涉及空值的查询
    SELECT Sno,Cno FROM SC WHERE Grade IS NULL; /*分数值为空的对应属性*/

    (6)多重条件查询
    SELECT Sname FROM Student WHERE Sdept='CS' AND Sage<20;

    谓词LIKE用来进行字符串的匹配,一般格式如下

    1
    [NOT] LIKE '<匹配串>' [ESCAPE '\']

    ESCAPE '\' 表示''为换码符,用于转义 % 和 _
    <匹配串>可以是一个完整的字符串,也可以含有通配符 % 和 _ ,其中:

    • %(百分号)代表任意长度的字符串。如a%b表示以a开头以b结尾的任意长度的字符串
    • _(下划线)代表任意单个字符,类似于占位符。如a_b表示以a开头以b结尾长度为3的字符串

注:WHERE子句常用的查询条件表如下

查询条件 谓词
比较 =,>,<,>=,<=,!=,<>,!>,!< ; NOT+上述比较运算符
确定范围 BETWEEN AND, NOT BETWEEN AND
确定集合 IN, NOT IN
字符匹配 LIKE, NOT LIKE
空值 IS NULL, IS NOT NULL
多重条件(逻辑运算) AND, OR, NOT
  • 3.ORDER BY子句

    可以用ORDER BY子句对查询结果按照一个或多个属性的升序(ASC)或降序(DESC)排列,默认值为升序
    用法为ORDER BY <属性名> ASC|DESC

    1
    2
    #例
    SELECT Sno,Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC;

  • 4.聚集函数

    聚集函数遇到空值时,除了COUNT(*)外,都跳过空值而只处理非空值
    WHERE子句中是不能用聚集函数作为条件表达式的。聚集函数只能用于SELECT子句GROUP BY中的HAVING子句

函数名 作用
COUNT(*) 统计元组个数
COUNT([ALL/DISTINCT] <列名>) 统计一列中值的个数
SUM([ALL/DISTINCT] <列名>) 计算一列值的总和(此列必须是数值型)
AVG([ALL/DISTINCT] <列名>) 计算一列值的平均值(此列必须是数值型)
MAX([ALL/DISTINCT] <列名>) 求一列值中的最大值
MIN([ALL/DISTINCT] <列名>) 求一列值中的最小值
  • 5.GROUP BY子句

    GROUP BY子句将查询结果按某一列或多列的值分组,值相等的为一组。
    对查询结果分组的目的是为了细化聚集函数的作用对象。分组后聚集函数将作用于每组,即每一组都有一个函数值。
    WHERE子句与HAVING短语的区别在于作用对象不同。WHERE子句作用于基本表或视图,从中选择满足条件的元组HAVING短语作用于,从中选择满足条件的

    1
    2
    3
    4
    #例
    SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno;
    SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) > 3;
    SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade) >= 90;

连接查询

  • 1.等值与非等值连接查询

    当连接运算符为=时,称为等值连接。使用其他运算符称为非等值连接
    注意:若选择的属性列在两个表中是唯一的,则可以省去表名前缀;若不唯一,则引用时必须加上表名前缀。

    1
    2
    3
    4
    5
    6
    #等值连接
    SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.Sno;

    #非等值连接
    SELECT Student.Sno, Sname FROM Student, SC WHERE
    Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90;

  • 2.自身连接

    连接操作不仅可以在两个表之间进行,也可以是一个表与其自身进行连接,称为表的自身连接

    1
    2
    3
    #例
    SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST, Course SECOND WHERE FIRST.Cpno=SECOND.Cno;
    /*为了区分自身的两个表而给这两个表分别取了别名FIRST和SECOND*/

  • 3.外连接

    两个表进行连接时,若其中一个表没有相关的元组信息,则按普通的两表连接则会省去某些元组,若想保留则可以使用外连接。

    1
    2
    #例
    SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);

  • 4.多表连接

    还可以进行两个表以上的连接,称为多表连接。

    1
    2
    #例
    SELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
子查询的SELECT语句不能使用ORDER BY子句,ORDER BY子句只能对最终的查询结果排序
子查询的查询条件不依赖于父查询,称为不相关子查询,否则称为相关子查询

  • 1.带有IN谓词的子查询

    1
    2
    3
    4
    #查询与Sam在同一个系的学生
    SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN (
    SELECT Sdept FROM Student WHERE Sname='Sam'
    );

    有些查询可以用连接查询替代,有些是不能替代的

  • 2.带有比较运算符的子查询

    1
    2
    3
    4
    #查询每个学生超过他自己选修课程平均成绩的课程号(此例为一个相关子查询)
    SELECT Sno, Cno FROM SC x WHERE Grade >= (
    SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno
    );

  • 3.带有ANY(SOME)或ALL谓词的子查询

    子查询返回多值时需要使用ANY和ALL谓词修饰。其中:ANY(表示某个),ALL(表示所有)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    #查询非计算机系中比计算机科学系'某个'学生年龄小的学生姓名和年龄
    SELECT Sname, Sage FROM Student WHERE Sage < ANY (
    SELECT Sage FROM Student WHERE Sdept='CS'
    ) AND Sdept <> 'CS';

    #查询非计算机系中比计算机科学系'所有'学生年龄小的学生姓名和年龄
    SELECT Sname, Sage FROM Student WHERE Sage < ALL (
    SELECT Sage FROM Student WHERE Sdept='CS'
    ) AND Sdept <> 'CS';

  • 4.带有EXISTS谓词的子查询

    带有EXISTS谓词的子查询不返回任何数据,只产生逻辑结果真值“true”或逻辑假值“false”

    1
    2
    3
    4
    #查询所有选修了1号课程的学生姓名
    SELECT Sname FROM Student WHERE EXISTS (
    SELECT * FROM SC WHERE
    Sno=Student.Sno AND Cno='1');

集合查询

  • SQL集合操作包括:并操作UNION、交操作INTERSECT、差操作EXCEPT

  • MySql并没有提供对交和差操作的支持,这里就不再举例

    1
    2
    3
    4
    #查询计算机科学系的学生及年龄不大于19岁的学生
    SELECT * FROM Student WHERE Sdept='CS'
    UNION
    SELECT * FROM Student WHERE Sage<=19;

基于派生表的查询

  • 子查询不仅可以出现在WHERE子句中,也可以出现在FROM子句中,这时子查询生成的临时派生表成为主查询的查询对象

    1
    2
    3
    4
    #例
    SELECT Sname FROM Student,
    (SELECT Sno FROM SC WHERE Cno='1') AS SC1
    WHERE Student.Sno=SC1.Sno;

SELECT语句的一般格式

  • 一般格式

    1
    2
    3
    4
    5
    SELECT [ALL|DISTINCT] <目标属性表达式> [别名],...
    FROM <表名或视图名>,... | (<子查询>) [AS] <别名>
    [WHERE <>]
    [GROUP BY <属性名1> [HAVING <条件表达式>]]
    [ORDER BY <属性名2> [ASC|DESC]];

  • <子查询>由SELECT语句构成

  • 目标属性表达式的可选格式:

    1. *
    2. <表名>.*
    3. COUNT([DISTINCT] *)
    4. [<表名>.]<属性名表达式>,... 其中,属性名表达式可以是由属性名、作用于属性的聚集函数和常量的任意算数运算(+,-,*,/)组成的运算公式
  • 聚集函数的一般格式 COUNT|SUM|AVG|MAX|MIN( [DISTINCT|ALL] <属性名> )

  • WHERE子句的条件表达式的可选格式

    1. <属性名> \(\theta\) <属性名>|<常量>|[ALL|ANY](子查询)
    2. <属性名> [NOT] BETWEEN <属性名>|<常量>|(<子查询>) AND <属性名>|<常量>|(<子查询>)
    3. <属性名> [NOT] IN (<值1>, <值2>, ...)|(<子查询>)
    4. <属性名> NOT LIKE 匹配串
    5. <属性名> IS [NOT] NULL
    6. [NOT] EXISTS(<子查询>)
    7. <条件表达式> AND|OR <条件表达式> AND|OR ...

数据的更新

  • 1.插入数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # 插入元组
    INSERT INTO <表名>[(<属性名>,...)]
    VALUES (<对应属性的常量>,...);

    # 插入子查询结果(建立一个新表)
    INSERT INTO <新的表名>[(<属性名>,...)]
    <子查询>;
    # 例:对每一个系,求学生的平均年龄,并把结果存入新数据表
    INSERT INTO Dept_age(Sdept, Avg_age) (
    SELECT Sdept,AVG(Sage)
    FROM Student
    GTOUP BY Sdept
    );

  • 2.修改数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    UPDATE <表名>
    SET <列名>=<表达式>,...
    [WHERE <条件>|<子查询>];

    # 例:将计算机科学系全体学生的成绩置为零
    UPDATE SC
    SET Grade=0
    WHERE Sno IN (
    SELECT Sno
    FROM Student
    WHERE Sdept='CS'
    );

  • 3.删除数据

    DELETE语句删除的是表中的数据,而不是关于表的定义,表的定义仍在字典中。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    DELETE
    FROM <表名>
    [WHERE <条件>|<子查询>];

    # 例:删除计算机科学系全体学生的选课记录(SC表)
    DELETE
    FROM SC
    WHERE Sno IN (
    SELECT Sno
    FROM Student
    WHERE Sdept='CS'
    );


视图

视图是从一个或几个基本表(或视图)导出的表。它与基本表不同,是一个虚表

  • 1.建立视图

    1
    2
    3
    CREATE VIEW <视图名>[(<属性名>,...)]
    AS <子查询>
    [WITH [CASCADED|LOCAL] CHECK OPTION];

    <子查询>可以是任意的SELECT语句。
    WITH [CASCADED|LOCAL] CHECK OPTION表示对视图进行UPDATEINSERTDELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。
    组成视图的属性列名或者全部省略或者全部指定,没有第三种选择。但在下列三种情况下必须明确指明:
    1. 某个目标列不是单纯的属性名,而是聚集函数或列表达式
    2. 多表连接时选出了几个同名列作为视图的字段
    3. 需要在视图中为某个列启用新的更合适的名字
    关系数据库管理系统执行CREATE VIEW语句的结果,只是把视图的定义存入数据字典,并不执行其中的SELECT语句。
    定义:若一个视图是从单个基本表导出的,并且只是去掉了基本表的某些行和某些列,但是保留了主码,则称这类视图为行列子视图

  • 2.删除视图

    1
    DROP VIEW <视图名> [CASCADE];

    视图删除后视图的定义将从数据字典中删除。如果该视图上还导出了其他视图,则使用CASCADE级联删除语句一起删除。

  • 3.查询视图

    从数据字典中取出视图的定义,将其转换成等价的对基本表的操作后再执行,这一转换过程称为视图消解

    1
    2
    #使用定义的视图IS_Student进行查询年龄小于20岁的学生
    SELECT Sno, Sage FROM IS_Student WHERE Sage<20;

  • 4.更新视图

    对试图的更新最终要转换为对基本表的更新,但不是所有的视图都是可更新的,一般的,行列子集视图是可更新的。

    1
    2
    #例:将信息系学生视图IS_Student中学号为“201215125”的学生姓名改为“lili”
    UPDATE IS_Student SET Sname='lili' WHERE Sno='201215125';

  • 5.视图的作用

    1. 视图能够简化用户的操作
    2. 视图使用户能以多种角度看待同一数据
    3. 视图对重构数据库提供了一定程度的逻辑独立性
    4. 视图能够对机密数据提供安全保护
    5. 适当利用视图可以更清晰地表达查询