一、简介

二、标准查询语言(SQL，Standard Query Language)

子语言

  • 数据定义语言(DDL，Data Definition Language)：用来定义、删除和维护数据库里面的数据模型

  • 查询语言(QL，Query Language)：在数据库中查询、检索数据

  • 数据操纵语言(DML，Data Manipulation Language)：对数据库中存储的数据进行插入、删除、更改
  • 数据控制语言(DCL，Data Control Language)：控制用户对数据的访问权限

查询语言

重要概念

  • 基表(Base table)：在磁盘上真实存储的关系

  • 视图(View)：由基表根据不同用户需求映射而成的虚表，外模式主要由视图构成

  • 空值(NULL)：QL语言中的保留字，表示某一关系中某一属性为空

  • UNIQUE：保留字之一，表示定义一张表的时候是否允许属性有重复值

  • DEFAULT：保留字之一，表示为某一属性指定缺省值

  • PRIMARY KEY：主键

  • FOREIGN KEY：外键

  • CHECK：为某一关系中的属性定义完整性约束

查询方式

1
2
/*基本模式*/：SELECT [DISTINCT] target-list FROM relation-list WHERE qualification;
/*target-list是目标列表(属性)，relation-list是涉及到的表，qualification表示查询需要满足的条件(布尔表达式)*/

查询语句的系统内部执行方式：对涉及到的表做笛卡尔积，拼接起来→根据布尔表达式去除不满足的元组→根据目标列表去除不满足条件的属性→如果有distinct，就去掉重复项；没有distinct，查询完成

这样的执行方式效率非常低！后续会有优化

like模糊查询

1
2
SELECT S.age,age1=S.age-5,2*S.age As age2 FROM Sailors S WHERE S.sname LIKE 'B_%B';
/*下划线匹配任意字符,%表示匹配任意零到多个字符，age1和age2是表达式的别名*/

union表示集合并，except表示集合差，intersect表示集合交

嵌套查询(类似于两重循环)

非关联查询：子查询与外面的查询无关联

1
2
SELECT S.name FROM Sailor S WHERE S.id IN (SELECT R.id FROM Reserves.R WHERE R.bid=103);
/*查询预定过103号船的水手的名字*/

关联查询：子查询与外面的查询有关联

1
2
SELECT S.name FROM Sailor S WHERE EXISTS (SELECT * FROM Reserves.R WHERE R.bid=103 AND S.sid=R.sid);
/*查询预定过103号船的水手的名字*/
1
2
3
SELECT bid FROM Reserves R1 WHERE NOT IN (SELECT bid FROM Reserves R2 WHERE R1.sid!=R2.sid);
/*查询只被定过一次的船的编号，当船的编号不在除A以外其他水手预定过的船的编号集合中时，船就只被A定过一次*/
/*此处'!='应该为'逻辑非=',表示不等于*/

查询思路一般采用反向思路，否定的否定是肯定

实例如下

1
2
3
SELECT S.name FROM Sailor S WHERE NOT EXISTS((SELECT B.bid FROM Boat B)EXCEPT (SELECT R.bid FROM Reserves R WHERE R.bid=S.bid));
/*查询订过所有船的水手的名字*/
/*'SELECT R.bid FROM Reserves R WHERE R.bid=S.bid'查询该水手订过的船的编号，'SELECT B.bid FROM Boat B'查询所有船的编号，用'EXCEPT'去除掉该水手订过的船，剩下的就是没有订的，'NOT EXISTS'表示如果不存在没有订过的船，那就表明该水手订过所有的船*/

函数

  • COUNT(*)：统计一个关系中元组的数量
  • COUNT([DISTINCT] A)：A代表属性，DISTINCT表示去掉重复的，统计属性A的元组数量
  • SUM([DISTINCT] A)：对A的所有值进行求和
  • AVG([DISTINCT] A)：对A的所有值取平均
  • MAX(A)：A的所有值里面的最大值
  • MIN(A)：A的所有值里面的最小值

注意：函数运算不能嵌套使用！！！

完整框架

1
2
3
4
5
SELECT [DISTINCT] target-list
FROM relation-list
WHERE qualification
GROUP BY grouping-list
HAVING group-qualification;

实例说明

1
2
SELECT S.rating,MIN(S.age) AS Minage FROM Sailor S WHERE S.age>=18 GROUP BY S.rating HAVING COUNT(*)>1;
/*查询每个级别最年轻水手的年龄，并且这一级别中年龄大于18岁的水手至少有两个*/
sid sname rating age
29 dustin 7 45
33 brutus 1 33
31 lubber 8 55
18 andy 8 25
20 zorba 10 16
36 horatio 7 35
58 rusty 10 35
36 horatio 9 35
25 art 3 25
27 Bob 3 63
30 frodo 3 25

对于上表执行上述的查询语句后，过程如下所示

最终得到下列结果

rating age
3 25
7 35
8 25

空值(NULL)

允许某一元组的某一属性值为空值，空值代表不知道或者没有。

三值逻辑：TRUE(真)、FALSE(假)、NULL(不知道或没有)

当WHERE语句查询到NULL时，返回NULL，而不是返回FALSE！！！

CAST表达式

表达式的结构如下所示，类似C++的类型强制转换

用法：

  1. 匹配函数参数
1
substr(string1,CAST(x AS Integer),CAST(y AS Integer));
  1. 改变计算精度
1
CAST(elevation AS Decimal(5,0));	/*Decimal第一个参数是某一个十进制数的总位数，第二个参数为小数位数*/
  1. 赋予空值一种数据类型以便更好地进行查询

CASE表达式

类似C++中的switch语句

1
2
3
4
5
6
SELECT SUM(CASE
				WHEN type='chain saw' THEN accidents
				ELSE 0e0
		   END)/SUM(accidents)
FROM Machines;
/*查询"chain saw"设备发生故障的次数在总设备故障次数中所占的比例*/

子查询(sub-query)

  • 标量子查询(Scalar sub-query)：查询结果为一个具体的值
1
2
SELECT d.deptname,d.location FROM dept AS d WHERE (SELECT AVG(bonus) FROM emp WHERE deptno=d.deptno)>(SELECT AVG(salary) FROM emp WHERE deptno=d.deptno);
/*第一个子查询是该部门所有员工的平均奖金，第二个子查询是该部门所有员工的平均工资*/
  • 表表达式(Table expression)：查询结果为一张新的表(关系)
1
2
SELECT startyear,AVG(pay) FROM (SELECT name,salary+bonus AS pay,year(startdate) AS startyear FROM emp) AS emp2 GROUP BY startyear;
/*将雇员的名字、工资和入职年份做成临时表，然后按照入职年份分组计算平均收入*/

外连接(outer-join)

自然连接+用空值补充没有匹配上的元组

1
2
3
4
5
6
7
8
9
10
11
12
WITH /*临时合并表，只进行一次*/
	innerjoin(name,rank,subject,enrollment) AS (SELECT t.name,t.rank,c.subject,c.enrollment FROM teacher 	 AS t,course AS c WHERE t.name=c.teacher AND c.quater='Fall 96'),
	/*将任课老师的姓名、职称、任教课程、课程选修人数做成一张表*/
	teacher-only(name,rank) AS (SELECT name,rank FROM teacher EXCEPT ALL SELECT name,rank FROM 				innerjoin),
	/*将未任教的老师姓名和职称做成一张表*/
	course-only(subject,enrollment) AS (SELECT subject,enrollment FROM course EXCEPT ALL SELECT 			subject,enrollment FROM innerjoin)
	/*将没有开的课程做成一张表*/
SELECT name,rank,subject,enrollment 
FROM innerjoin 
UNION ALL SELECT name,rank,CAST(NULL AS Varchar(20)) AS subject,CAST(NULL AS Integer) AS enrollment FROM teacher-only
UNION ALL SELECT CAST(NULL AS Varchar(20)) AS name,CAST(NULL AS Varchar(20)) AS rank,subject,enrollment FROM course-only;
/*该表展示了所有课程的选修情况和所有老师的任课情况*/

递归查询(Recursion)

表表达式用自身作为定义来进行查询，即为递归查询，类似于C++中的广度优先搜索

1
2
3
4
5
WITH agents(name,salary) AS ((SELECT name,salary FROM FedEmp WHERE manager='Hoover')	/*初始查询*/
	UNION ALL (SELECT f.name,f.salary FROM agents AS a,FedEmp AS f WHERE f.manager=a.name))	
	/*递归查询，将上一层被管理的人作为管理者，查询下一层被管理的人，以此类推直到最后一层，新的结果不断并入agents这张表*/
SELECT name FROM agents WHERE salary>100000
/*查询Hoover管理的员工中工资大于100000的员工*/