2020年中级数据库系统工程师考试笔记9—SQL语言

8. SQL语言

8.1 本章考点

本章内容：很重要！很重要！很重要！应试（上午题、下午题必考）和应付工作必备技能。

8.2 SQL概述与数据库定义

  数据定义语言（DDL）：用来定义数据库模式，包括数据模式定义，数据库存储结构和存取方法定义，数据库模式的修改和删除功能。

  数据操纵语言（DML）：用来表示用户对数据库的操作请求，包括增删改查。

  嵌入式SQL和动态SQL：用于嵌入到某种通用的高级语言（C、C++、Java、VB等等）中混合编程，作为程序设计的子语言使用，其中，SQL负责操纵数据库，高级语言负责控制程序流程。

  完整性：SQL DDL包括定义数据库中的数据必须满足的完整性约束条件的命令，对于破坏完整性约束条件的更新将被禁止。
权限管理：SQL DDL中包括说明对关系和视图的访问权限。

  SQL语句分类及基本结构：

  1.DML语句（数据操作语言）：Insert、Update、Delete、Merge；

  2.DDL语句（数据定义语言）：Create、Alter、Drop、Truncate；

  3.DCL语句（数据控制语言）：Grant、Revoke；

  4.数据查询：Select；

  5.事务控制语句：Commit、Rollback、Savepoint。

8.3 数据库定义

  SQL支持的内部域类型：

1.创建表
  CREATE TABLE 表名称
(
列名称1 数据类型 列完整性约束条件,
列名称2 数据类型 列完整性约束条件,
列名称3 数据类型,列完整性约束条件,
…
表级完整性约束
);
  (1)实体完整性约束：通过主键约束和候选键约束实现。

• 关键字 PRIMARY KEY
    PRIMARY KEY可以在列定义后面加，也可以在建表最后加（等效），但是复合主键只能在表最后加。

Create table t1(
​  tid int primary key,
​  tname varchar(100)
);

Create table t1(
​  tid int,
​  tname varchar(100)
primary key(tid )
);

create table tb(
tb_id int ,
tb_name varchar(10),
primary key (tb_id,tb_name)
);

(2)域完整性：指数据库表的列（即字段）必须符合某种特定的数据类型或约束。

• 非空 NOT NULL
• 不允许重复 UNIQUE
• 按条件检查 CHECK (条件)
  注意：PRIMARY KEY=NOT NULL+UNIQUE。

create table t5(
username varchar(100) not   null  unique,
gender varchar(100) not   null,
phonenum varchar(100)  unique
​);

create table t5(
username varchar(100) primary key,
gender varchar(100) not   null,
phonenum varchar(100)  unique
​);

  以上2个SQL等效。

CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

(3)参照完整性约束：是多表之间的设计，主要使用外键约束实现。

  语法：

foreign key（当前表的列）references 从表表名（从表的主键）
[on delete restrict|cascade|set null|no action]
[on update restrict|cascade|set null|no action]

• Restrict：删除或者更新时，在外键中出现的值操作失败
• cascade：将外键的值一同删除或者更新
• set null：删除更新时外键的值被设置为空

create table tb(
tb_id int ,
tb_name varchar(10),
foreign key (tb_id) references tb1(tb_id) on delete cascade on update cascade
);

create table tb1(
tb_id int primary key,
tb_name varchar(10),
unique(tb_id,tb_name)
);

  例子参考P364例题8.1。

2.修改表

ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <原列名>|<完整性约束名> ]
[MODIFY <原列名> <数据类型> ];

例子：
ALTER TABLE Student ADD Scome DATE not null;
ALTER TABLE Student MODIFY sno varchar(13);
ALTER TABLE Student DROP stuNum;

3.删除表

DROP TABLE <表名>;
DROP TABLE Student;

  删除表时，系统会从数据字典中删去有关该表的描述。

4.索引的创建和删除

  索引是快速定位数据的技术，是加速查询的主要手段。（类似于书本的目录）

  创建索引语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX <索引名> ON <表名>(<列名 [,<列名> ]…);
CREATE INDEX index_name ON table_name (column_list);
CREATE UNIQUE INDEX index_name ON table_name (column_list);

  删除索引语法：

Drop index <索引名> on <表名>;或者Drop index <索引名>;

  删除索引时，系统会从数据字典中删去有关该索引的描述。

DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;

5.视图的创建和删除

  视图创建语法：

CREATE VIEW 视图名（列表名）
AS SELECT 子查询
[WITH CHECK OPTION] --可以省略;

create view v_student (name,age,sex,id,source)
as select * from student
with check option;

  视图删除语法：

DROP VIEW 视图名;
DROP VIEW v_student;

8.4 数据操作

  SQL数据操纵语言就是insert、delete、update、select，也就是数据的增删改查操作。

8.4.1 Select

  数据库查询时数据库的核心操作，Select语法：

SELECT [ALL|DISTINCT [字段或目标列表达式]
FROM table_name
[ WHERE search_condition]
[ GROUP BY group_by_expression]
[ HAVING search_condition]
[ ORDER BY order_expression [ ASC|DESC ] ]

• SELECT子句：指定由查询返回的列。
• FROM子句：用于指定引用的列所在的表或视图，如果对象不止一个，那么它们之间必须用逗号分开。
• WHERE子句：指定用于限制返回的行的搜索条件，如果SELECT语句没有WHERE子句，DBMS假设目标表中的所有行都满足搜索条件。
• GROUP BY子句：指定用来放置输出行的组，SELECT子句中使用的聚集操作符仅用在每个分组上。
• HAVING子句：假如元组在分组前按照某种方式加上限制，使得不需要的分组为空，可以在GROUP BY子句后面跟一个HAVING子句即可。

  执行步骤：

（1）先从from字句一个表或多个表创建工作表；
（2）将where条件应用于（1）的工作表，保留满足条件的行；
（3）Group By 将（2）的结果分成多个组；
（4）Having 将条件应用于（3）组合的条件过滤，只保留符合要求的组；
（5）Order By对结果进行排序。

1.简单查询

  例如：查询张三的昵称和email地址。

SELECT nickname,email FROM users WHERE name='张三';

2.连接查询：两个或者两个以上的表连接查询所需要的数据信息。

  例如：查询每个学生及其选修课程的情况。

SELECT S.*,SC.*
	FROM S,SC
	WHERE S.Sno = SC.Sno;

  顺带说下自连接（一个表与自己连接），需要使用别名以示区别。

  例如：查询每一门课的间接先行课。

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;

3.子查询

  子查询眼角嵌套查询，嵌套在另一个查询中的查询（一个SELECT-FROM-WHERE语句查询块）。

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
           (SELECT Sdept
           FROM Student
           WHERE Sname = '李四');

4.聚集函数

例如：SELECT AVG(age) AS avgage, MAX(age) AS maxage, MIN(age) AS minage FROM Student;

  ANY，ALL谓词：

  例如：查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名和年龄。

select Sname，Sage from student where Sage < ALL (select  Sage from student where Sdept  = "CS") AND Sdept <> "CS";
改写成：
Select Sage,Sname from student where Sage < (select MIN(Sage) from Student where Sdept= 'CS')  AND Sdept != 'CS';

5.分组查询

(1)GROUP BY

  GROUP BY思想：先排序再汇总，通常用于配合聚合函数，达到分类汇总统计的信息的目的。其中分类汇总的本质就是先将信息排序，排序后相同类别的信息会聚在一起，然后通过需求进行统计计算。

  例如，按性别统计学生表。

select sex ,count(*)  as 人数  from student group by sex；

  例如，按照部门号统计员工工资。

select deparmant, GROUP_CONCAT(salary), SUM(salary),AVG(salary) 平均工资,MAX(salary) 最高工资 from employee GROUP BY deparmant;

(2)HAVING子句

  HAVING子句的用法类似WHERE子句，它指定了组或集合的搜索条件。HAVING子句通常与GROUP BY子句一起使用。
实际上，HAVING子句是在分组后对数据进行过滤，后面可以使用分组函数(统计函数)。

  例如，查询工资大于2000的，工资总和大于9000的部门名称以及工资和。

select deparmant,GROUP_CONCAT(salary), SUM(salary) from employee 
WHERE salary > 2000 
GROUP BY deparmant 
HAVING sum(salary) > 9000
ORDER BY SUM(salary) DESC;

6.字符串操作

  字符串操作通常是指用操作符like来进行匹配操作，使用2个特殊的字符来表示：

• %：匹配任意个数的任意字符；
• _：匹配任意1个字符。

  注意，模式匹配大小写敏感。

  例如，查询学生表钟姓学生的信息。

SELECT * FROM student WHERE Sname LIKE '钟%';

7.集合操作

(1)UNION运算（⋃）

  Union可以对两个或多个子查询结果集进行连接，形成“并集”，Union ALL包括重复的行，默认不带ALL，即不包括重复的行。

  限制条件：

• 子结果集要具有相同的结构。
• 子结果集的列数必须相同。
• 子结果集对应的数据类型必须可以兼容。
• 每个子结果集不能包含order by和compute子句。

  去掉重复项的并集：

select SName from student1 
union 
select SName from student2;

  不去掉重复项的并集：

select SName from student1 
union all
select SName from student2;

(2)INTERSECT运算（∩）

  InterSect可以对两个或多个子查询结果集进行连接，形成“交集”，返回左边结果集和右边结果集中都有的记录。

select SName from student1 
InterSect
select SName from student2;

(3)EXCEPT运算（-）

  Except可以对两个或多个子查询结果集进行连接，形成“差集”，返回左边结果集合中已经有的记录，而右边结果集中没有的记录。

select SName from student1 
except
select SName from student2;

8.外连接

  左连接：根据左表的记录，在被连接的右表中找出符合条件的记录与之匹配，如果找不到与左表匹配的，用null填充。

SELECT t.TEACHER_NAME,s.STUDENT_NAME FROM teacher t LEFT JOIN student s ON t.ID=s.TEACHER_ID;

  根据右表的记录，在被连接的左表中找出符合条件的记录与之匹配，如果找不到匹配的，用null填充。

SELECT t.TEACHER_NAME,s.STUDENT_NAME FROM teacher t RIGHT JOIN student s ON t.ID=s.TEACHER_ID;

  全连接：返回符合条件的所有表的记录，没有与之匹配的，用null表示。

select t.teacher_name, s.student_name from teacher t full outer join student s on t.id = s.teacher_id;

8.4.2 Insert/update/delete

1.INSERT语法：INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)

INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing');
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees');

2.UPDATE语法：UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值;

UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson';

3.DELETE语法：DELETE FROM 表名称 WHERE 列名称 = 值;

DELETE FROM Person WHERE LastName = 'Wilson';

8.5 授权与触发器

8.5.1 授权与收回权限

1.授予权限

  授权的语法格式：

GRANT <权限>[,<权限>]... 
ON <对象类型> <对象名>[,<对象类型> <对象名>]…
TO <用户>[,<用户>]...
[WITH GRANT OPTION];

  指定WITH GRANT OPTION子句表示获得该权限的用户还可以将该权限授予其他的用户。

  例如：把Student表修改学生学号的权限授给用户ChengYu。

GRANT UPDATE(Sno), SELECT ON TABLE Student TO ChengYu;

  例如：把对表SC的INSERT权限授予用户ChengYu，并允许ChengYu再将此权限授予其他用户

GRANT INSERT ON TABLE SC TO ChengYu WITH GRANT OPTION;

2.回收权限

  回收权限的语法格式：

REVOKE <权限>[,<权限>]... 
ON <对象类型> <对象名>[,<对象类型><对象名>]…
FROM <用户>[,<用户>]...[CASCADE | RESTRICT];

  例如，把用户ChengYu对SC表的INSERT权限级联收回。

REVOKE INSERT ON TABLE SC FROM ChengYu CASCADE ;

  例如，收回所有用户对表SC的查询权限。

REVOKE SELECT ON TABLE SC FROM PUBLIC;

8.5.2 触发器

1.创建触发器

  触发器（trigger）是种特殊的存储过程，它的执行不是由程序调用，也不需要手动操作，它是由事件来触发的。

  触发器的主要特点：

• 当数据库程序员声明的事件发生时，触发器被激活，时间可以是对某个特定关系的insert、delete、update操作；
• 当触发器被事件激活时，不是立即执行，二是首先由触发器测试出发条件，若条件不成立，响应该事件的触发器什么事情都不做。
• 如果触发器声明的条件满足，则与该触发器相连的动作有DBMS执行，动作可以阻止事件发生，可以撤销事件。

  创建数据库触发器需要指定：

• 名称
• 定义触发器涉及的表
• 触发器激发条件
• 指明触发器执行时应做的动作

  触发动作有两种方式：FOR EACH ROW（行级触发器，对被事件影响的每一行都执行触发过程）和FOR EACH STATEMENT（语句级触发器，默认方式，对整个事件只执行一次触发过程）。

  触发器的定义包括两个方面：

• 指明触发器的触发事件：包括insert、delete、update语句，事件触发的两个相关时间，before（在事件发生之前触发）和after（在事件发生之后触发）。
• 指明触发器执行的动作。

  触发器创建语法：

CREATE <触发器名> < BEFORE | AFTER >
<INSERT | DELETE | UPDATE OF[列名清单]>
ON <表名> 
[REFERENCING <临时视图名>]
[FOR EACH ROW|FOR EACH STATEMENT]
[WHEN <触发条件>]
BEGIN
<触发动作>
END[触发器名]

(1)BEFORE | AFTER：只是DBMS在执行触发语句之前或之后激发触发器；

(2)INSERT | DELETE：分别表示每当一个insert语句向表中插入一行数据时激发触发器、每当delete语句从表中删除一行时激发触发器；

(3)UPDATE/UPDATE OF：每当update语句修改表任何列值时，DBMS都将激发触发器，每当update语句修改有of子句指定的列值时激发触发器；

(4)REFERENCING：指定临时视图的别名，在触发器运行过程中，系统会生成存放更新值（旧值）和更新后的值（新值）的临时视图，行级触发器视图名为OLD和NEW，语句级触发器，默认视图名OLD-TABLE和NEW-TABLE，触发器运行结束，视图也就不存在了；

(5)WHEN：指定触发器的触发条件，触发条件必须包含临时视图名，不包含查询。

  例：假定银行数据库关系模式如下：
  Account（Account-no，branch-name，balance）
  Loan（Loan-no，branch-name，amount）
  Depositor（customer-name，Account-no）

  假设银行处理透支时，不是将账户余额设置为负值，二是将账户余额设置成零，并且建立一比贷款，其金额为透支额，这笔贷款的贷款好等于该透支账户的账户号，这样执行触发器的条件是对关系Account更新导致了负的余额。

CREATE TRIGGER overdraft_trigger AFTER update on account
referencing new row as nrow
for each row
WHEN nrow.balance<0
BEGIN ATOMIC
      INSERT INTO borrower VALUES(select customer_name,account_number from depositor where nrow.account_number=depositor.account_number)
 INSERT INTO loan VALUES(nrow.account_number,nrow.branch_name,-nrow.balance)
 UPDATE account SET balance=0 WHERE account.account_number=nrow.account_number
END

  例题：某航空公司要开发一个订票信息处理系统，该系统的部分关系模式如下：航班（航班编号，航空公司，起飞地，起飞时间，目的地，到达时间，票价）折扣（航班编号，开始日期，结束日期，折扣）

  旅客（身份证号,姓名，性别，出生日期，电话，VIP折扣）

  购票（购票单号，身份证号，航班编号，搭乘日期，购票金额）

  有关关系模式的属性及相关说明如下：

  (1)航班表中的起飞时间和到达时间不包含日期，同一航班不会在一天出现两次及两次以上；

  (2)各航空公司会根据旅客出行淡旺季适时调整机票的折扣，旅客购买机票的购票金额计算公式为：票价X折扣XVIP折扣，其中旅客的VIP折扣与该旅客已购买过的机票的购票金额总和相关，在旅客每次购票后被修改。VIP折扣值的计算由函数float_vip value（char[18]身份证号）完成。

  根据以上描述，回答下列问题。

  问题2.1: 请将如下创建购票关系的SQL语句的空缺部分补充完整，要求指定关系的主键、外键，以及购票金额大于零的约束。

  问题2.2: (1)身份证号为210000196006189999的客户购买了2013年2月18日CA5302航班的机票，购票单号由系统自动生成。下面的SQL语句将上述购票信息加入系统中，请将空缺部分补充完整。

INSERT INTO 购票（购票单号，身份证号，航班编号，搭乘日期，购票金额）
    SELECT ‘201303105555’，‘210000196006189999’，‘CA5302’，‘2013/2/18’，
    FROM航班，折扣，旅客
    WHERE （f） AND 航班.航班编号=‘CA5302’AND
         AND‘2013/2/18’BETWEEN折扣.开始日期AND折扣.结束日期
         AND旅客.身份证号=‘210000196006189999’；

  （2）需要用触发器来实现VIP折扣的修改，调用函数vip_value（）来实现。请将如下SQL语句的空缺部分补充完整。

CREA TETRIGGER  VIP _TRG AFTER （g） ON （h）
REFERENCING new row ASnrow
FOR EACH row
BEGIN
UPDATE 旅客
SET （i）
WHERE Q） ;
END

  问题2.3： 请将如下SQL语句的空缺部分补充完整。

  (1)查询搭乘日期在2012年1月1日至2012年12月31日之间，且合计购票金额大于等于10000元的所有旅客的身份证号、姓名和购票金额总和，并按购票金额总和降序输出。

SELECT旅客.身份证号，姓名，SUM（购票金额）
FROM旅客，购票
WHERE （k）
GROUPBY O）
ORDERBY （m） ;

  （2）经过中转的航班与相同始发地和目的地的直达航班相比，会享受更低的折扣。查询从广州到北京，经过一次中转的所有航班对，输出广州到中转地的航班编号、中转地和中转地到北京的航班编号。

SELECT （n）
FROM航班航班1，航班航班2
WHERE （o） ;

2.更改和删除触发器
(1)更改触发器语法：

ALTER TRIGGER <触发器名> < BEFORE | AFTER >
<INSERT | DELETE | UPDATE OF[列名清单]>
ON 表名|视图名 
AS 
BEGIN
<触发动作>
END

(2)删除触发器语法：

drop trigger 触发器名;

8.6 嵌入式SQL与存储过程

8.6.1 嵌入式SQL

  SQL提供将SQL语句嵌入到某种高级语言中的使用方式，通常采用预编译的方法识别嵌入在高级语言中的SQL语句，该方法的关键问题是必须区分主语言中嵌入的SQL语句，以及主语言这SQL间的通信问题。

1.区分主语言语句与SQL语句

EXEC SQL <SQL语句>;

2.主语言工作单元与数据库工作单元通信

(1)SQL通信区

  SQL通信区（SQL Communication Area，SQLCA）向主语言传递SQL语句执行的状态信息，使主语言能够根据此信息控制程序流程。

(2)主变量：也称共享变量，主语言想SQL语句提供参数主要通过主变量，主变量由主语言的程序定义，并用SQL的declare语句说明。YINYONG SHI ,WEILE YU SQL属性名相区别，须在主变量前加“:”。

  例题：根据共享变量givensno值查询学生关系students中学生的姓名、年龄和性别。

EXEC SQL SELECT sname,age,sex 
INTO :Mson,:Mcno,:givensno
 FROM students
WHERE sno=:Mson;

8.6.2 游标

   SQL语言是面向集合的，一条SQL语句可以产生或者处理多条记录，二主语言是面向记录的，一组主变量一次只能放一条记录，所以，引入游标，通过移动游标指针来决定获取哪条记录。

1.定义游标

  声明游标：

EXEC SQL DECLEAR <游标名> CURSOR FOR
<SELECT 语句>
END_EXEC

  这是一条说明性语句，定义中的SELECT语句并不立即执行。

2.打开游标

EXEC SQL OPEN <游标名> END_EXEC

  该语句执行游标定义中的SELECT语句，同时游标处于活动状态，游标是一个指针，此时只想查询结果的第一行之前。

3.推进游标

EXEC SQL FETCH FROM <游标名> INTO <变量名> END_EXEC

  该语句使用时，游标推进一行，并把游标指向的行中的值取出，送到共享变量中，变量表是由逗号隔开的共享变量组成，该语句常置于宿主语言程序的循环结构中，并借助书主语言的处理语句注意处理查询结果中的一个元祖。

4.删除游标

EXEC SQL CLOSE<游标名> END_EXEC

  该语句关闭游标，使它不再和查询结果相联系。关闭了的游标，可以再次打开，与新查询结果相联系，在游标处于活动状态时，可以修改和删除游标指向的元组。

8.6.3 存储过程

  存储过程（Stored Procedure），计算机用语，是一组为了完成特定功能的SQL语句集，是利用SQL Server所提供的Transact-SQL语言所编写的程序。经编译后存储在数据库中。存储过程是数据库中的一个重要对象，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，存储过程可由应用程序通过一个调用来执行，而且允许用户声明变量。同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。（来源网友整理）

  优点：1）存储过程是预编译过的，执行效率高。2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。3）安全性高，执行存储过程需要有一定权限的用户。

  存储过程语法：

CREATE OR REPLACE PROCEDURE 存储过程名
(IN|OUT|IN OUT参数 数据类型)
AS
BEGIN
<SQL语句>
END 存储过程名;

• IN 默认值，表示输入型参数；
• OUT：输出型参数；
• IN|OUT：既可作为输入参数也可以作为输出参数。

说明：
1.疏忽、遗漏、错误之处，欢迎留言批评指正。
2.第8章总结完毕，后续会继续完善补充本章的历年真题，转载请注明出处，整理不易，谢谢！