结构化英语查询语言SQL_在结构化查询语言中 查询子句为指定查询条件子句

SQL(结构化英语查询语言)

structured English Query Language

SQL特点

集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。

可以独立完成数据库生命周期中的全部活动：

1. 定义和修改、删除关系模式，定义和删除视图，插入数据，建立数据库;
2. 对数据库中的数据进行查询和更新;
3. 数据库重构和维护
4. 数据库安全性、完整性控制，以及事务控制
5. 嵌入式SQL和动态SQL定义

高度非过程化：

1. 非关系数据模型的数据操纵语言“面向过程，必须指定存取路径。
2. SQL只要提出“做什么”，无须了解存取路径。
3. 存取路径的选择以及SQL的操作过程由系统自动完成。

面向集合的操作方式：

1. 非关系数据模型采用面向记录的操作方式，操作对象是一条记录
2. SQL采用集合操作方式
3. 操作对象、查找结果可以是元组的集合
4. 一次插入、删除、更新操作的对象可以是元组的集合

以同一种语法结构提供多种使用方式：

1. SQL是独立的语言，能够独立地用于联机交互地使用方式
2. SQL又是嵌入式语言，能够嵌入到高级语言程序中，供程序员设计程序时使用

语言简介，容易学

SQL基本概念

SQL支持关系数据库三级模式结构

基本表

1. 本身独立存在地表
2. SQL中一个关系就对应一个基本表
3. 一个或多个基本表对应一个存储文件
4. 一个表可以带若干索引

存储文件

逻辑结构组成了关系数据库地内模式

物理结构对用户时隐蔽的

视图

1. 从一个或几个基本表中导出地表
2. 数据库中只存放视图地定义而不存放视图对应的数据
3. 视图是一个虚表
4. 用户可以在视图上再定义视图

为什么不需要一个alter view的语句？

视图是从一个或几个基本表导出的表，用户可以从一个特定的角度来查看数据库中的数据，它与基本表不同，是一个虚表。数据库中只存放视图的定义而没有存放视图对应的数据，所以不需要alter view语句来进行删除。视图是一个虚表，是由基本表到处的，不需要修改虚表，直接修改基本表就好了，导出一个新的视图即可。

数据定义

SQL的数据定义功能：定义各种数据库的”对象“

1. 模式定义
2. 表定义
3. 视图定义
4. 索引定义

各种数据库”对象“（现代关系数据库管理系统提供了一个层次化的数据库对象命名机制）

一个数据库可以建立多个模式，一个模式下通常包括多个表、视图和索引等数据库对象

数据定义方式

模式定义、表定义、索引定义

模式定义

定义模式实际上定义了一个命名空间（或者说目录）

在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。

创建模式

删除模式

基本表定义

创建基本表

数据类型

修改基本表

删除基本表

DROP TABLE <表名> [RESTRICT|CASCADE]

模式与表

每一个基本表需属于某个模式

定义基本表所属模式的方式：

索引定义

建立索引的目的：加快查询速度

关系数据库管理系统常见索引：

1. 顺序文件上的索引
2. B+树索引
3. 散列hash索引
4. 位图索引

特点：

1. B+树索引具有动态平衡的优点
2. HASH索引具有查找速度快的特点

建立索引

修改索引

删除索引

数据字典

数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有对象的定义信息以及一些统计信息：

1. 关系模式、表、视图、索引的定义
2. 完整性约束的定义
3. 各类用户对数据库的操作权限
4. 统计信息等

关系数据库管理系统在执行SQL数据定义语句时，实际上就是再更新数据字典表中的相应信息。

数据查询

数据格式

SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)[AS]<别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
1
2
3
4
5

1. SELECT子句：指定要显示的属性列
2. FROM子句：指定查询对象（基本表或视图）
3. WHERE子句：指定查询条件
4. GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
5. HAVING短语：只有满足指定条件的组才予以输出
6. ORDER BY子句：对查询结果表按指定列值的升序或降序排序

单表查询

选择表中的若干列

查询指定列

查询全部列

查询经过计算的值（eg:2021-sage)

选择表中的若干元组

消除取值重复的行（DISTINCT）

SELECT DISTINCT Sno
FROM SC; 
1
2

查询满足条件的元组

​比较大小

​确定范围

字符匹配：

order by

用户可以通过orderby子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认为升序。

对于空值，升序排在前面，降序排在后面（SQL SERVER），各个系统可能不同。

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

聚集函数

group by

细化聚集函数的作用对象：

1. 如果未对查询结果分组，聚集函数将作用于整个查询结果
2. 对查询结果分组后，聚集函数将分别作用于每个组（按指定的一列或多列值分组，值相等的为一组）

注意：WHERE子句中是不能用聚集函数作为条件表达式

HAVING短语与WHERE子句的区别：作用对象不同

1. WHERE子句作用于基表或视图，从中选择满足条件的元组
2. HAVING短语作用于组，从中选择满足条件的组。

课堂练习

/* order by*/
/*找出所有选课成绩表中人分别按照 课程号升序，学号降序*/ 
select sno,cno,grade
from sc
order by cno, sno desc

/* in */
select sno 学号,sname 姓名, sdepartion 所在系
from student
--where class = '19级1班' and sage>=19 and sage<=21
--where class='19级1班' and sage between 19 and 21
where class='19级1班' and sage in (19,20,21)

/*LIKE*/
select * from student
--where sname LIKE  '张%'
--where sname LIKE '%文%'
--where sname LIKE '_文_'
where sname not LIKE '%文%'
/*is null*/
select * from course
--where cpno is null
--where cpno =''
where cpno is not null

/*distinct*/
select distinct sno,grade/*去重*/
from sc
where grade=100

/*between and*/
select * from sc
where grade between 60 and 70

--自己选修课程总课程数，最高分 最低分 平均分
select sno='201926205028', count(*) 选修课程数,max(grade) 最高分,min(grade)最低分,avg(grade) 平均分 from sc
--group by sno having sno='201926205028'
where sno='201926205028'

/*聚集函数*/
select MAX(grade) 最高分,MIN(grade) 最低分,AVG(grade) 平均分 from sc 
group by cno having cno = '262198'

select cno ,count(grade) from sc
where grade=100
group by cno having count(grade)>=3
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

一个小案例

/*1、在员工表employee中统计员工人数。*/
select COUNT(emp_no) '员工个数' from employee

/*2、统计各部门员工的员工人数及平均薪水*/
select dept '部门',COUNT(*)  '员工人数', AVG(salary) '平均薪水'from employee
group by dept

/*3、查询销售业绩超过300元的员工编号。*/
select sale_id '员工编号' from sales
where total_amt>=300

/*8、显示sale_item表中每种图书的订购金额总和，
并且依据销售金额由大到小排列来显示出每一种图书的排行榜。*/
select book_no,SUM(qty*unit_price) 图书订购金额总和 from sale_item
group by book_no 
order by SUM(qty*unit_price) desc

/*5、统计各部门不同性别、或各部门、或不同性别或所有员工的平均薪水。
（在GROUP  BY 子句中使用CUBE关键字）cube为展现列中所有层次*/
select sex 性别,dept 部门,AVG(salary) 平均薪水 from employee
group by cube(sex,dept)

/*9、显示sale_item表中销售金额在300元以上的订单的销售数量，销售金额，并按降序排序。*/
select order_no 订单编号,SUM(qty) 销售数量,SUM(qty*unit_price) 销售金额 from sale_item
group by order_no having SUM(qty*unit_price)>=300
order by SUM(qty*unit_price) desc

/*6、统计各部门不同性别、或各部门或所有员工的平均薪水。
（在GROUP  BY 子句中使用ROLLUP关键字）rollup 为分层次展现*/
select dept 部门,sex 性别,AVG(salary) 平均薪水 from employee
group by rollup(dept,sex)

/*12、统计各个客户的订单数量。*/
select  cust_name 客户名,COUNT(order_no) 订单数量 from sales
group by cust_name
/*11、统计各个客户的消费金额。*/
select  cust_name 客户名,SUM(total_amt) 消费金额 from sales
group by cust_name
/*10、统计各个类型图书的种类数量。*/
select book_type 图书类型,COUNT(book_no) 种类数量 from books
group by book_type
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

连接查询

等值与非等值连接查询

select student.*,sc.* from student,sc
where student.sno = sc.sno
1
2

PS:若在等值连接中把目标列中重复的属性列去掉就是自然连接

select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc
where student.sno = sc.sno
1
2

本例中，由于sname,ssex,sage,sdept,cno,grade属性列在student表和sc表中是唯一的，因此引用的时候可以去掉表名前缀，而sno在两个表都出现了，所以引用时必须加上表名前缀

查询效率优化：

/*自然连接 36*85   */
select distinct student.sno,sname,class from student,sc
where class = '19级1班' and grade=100 and student.sno = sc.sno
/*嵌套连接*/
select sno,sname,class from student
where class='19级1班' and 
(sno in (select distinct sno from sc where grade=100))
1
2
3
4
5
6
7

外连接

select student.sno,sname,ssex,sage,sdept,cno,grade 
from student left outer join sc on (student.sno = sc.sno)
1
2

自身连接

连接操作也可以是一个表与其自己进行连接

/*间接先修课为空的所有课程的课程号*/
/*嵌套查询*/
select cno,cname,cpno from course 
where  cpno in (select cno from course 
where cpno is null)
/*自身连接*/
select  a.cno,a.cname from course a,course b
where a.cpno = b.cno and b.cpno is null
1
2
3
4
5
6
7
8

多表连接

select student.sno,sname,cname,grade from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno
1
2

嵌套查询

带有IN谓词的子查询

select sno,sname,class from student
where sno in 
(select distinct sno from sc
where cno in
(select cno from course
where cname like '%操作系统%'))
1
2
3
4
5
6

带有比较运算符的子查询

/*找出每个学生超过他自己选修课程平均成绩的课程号*/
select sno,cno from sc x
where grade >=(select AVG(grade) from sc y where y.sno=x.sno)
1
2
3

带有ANY或ALL谓词的子查询

select sname,sage from student
where class !='19级1班' and sage < ANY(select sage from student where class = '19级1班')

select sname,sage from student
where sage < (select max(sage) from student) and class!='19级1班'

select sname,sage from student
where class !='19级1班' and sage < ALL(select sage from student where class = '19级1班')

select sname,sage from student
where sage < (select min(sage) from student) and class!='19级1班'
1
2
3
4
5
6
7
8
9
10
11

带有EXISTS谓词的子查询

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

若内层查询结果非空，则外层的where子句返回真值，否则返回假值

select sname from student
where exists (select * from sc where sno = student.sno and cno='262198')

select sname from student 
where not exists (select * from sc where sno = student.sno and cno='262198')

select sno,sname,class from student s1
where exists (select * from student s2 
where s2.sdepartion = s1.sdepartion and s2.sname='唐璟玥')


select sno,sname from student
where not exists/*不存在课程表中的课程*/
(select * from course
where not exists/*没有被选修*/
(select * from sc
where sno = student.sno and cno=course.cno))

/*查询至少选修了学生201926205001选修的全部课程的学生号码？？？*/
select distinct sno from sc scx
where not exists
(select * from sc scy
where scy.sno = '201926205001' and not exists
(select * from sc scz
where scz.sno = scx.sno and scz.cno = scy.cno))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

集合查询

/*集合查询*/
select * from student where class='19级1班'
union/*并*/
select * from student where sage<=19

select * from student where class='19级1班' or sage<=19


select * from student where class='19级1班'
intersect/*交*/
select * from student where sage<=19

select * from student where class='19级1班' and sage<=19



select * from student where class='19级1班'
except/*补*/
select * from student where sage>19
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

派生表查询

select sno,cno,grade
from sc,
	(select sno,avg(grade) 
	from sc
	group by sno) 
	as avg_sc(avg_sno,avg_grade)
where avg_sc.avg_sno=sc.sno and grade >= avg_sc.avg_grade
1
2
3
4
5
6
7

课堂练习

/*和XXX一个班级的所有学生的信息*/
/*嵌套查询*/
select sno,sname,class from student
where class in
(select class from student
where sname='XXX')

/*子查询得到结果只有一个的时候，外查询可以把in替换成=*/
select sno,sname,class from student
where class =
(select class from student
where sname='XXX')


/*自身连接*/
select b.sno,b.sname,b.class from student a,student b
where a.class=b.class and a.sname = 'XXX'

/*间接先修课为空的所有课程的课程号*/
/*嵌套查询*/
select cno,cname,cpno from course 
where  cpno in (select cno from course 
where cpno is null)
/*自身连接*/
select  a.cno,a.cname from course a,course b
where a.cpno = b.cno and b.cpno is null


/*找出每个学生超过他选修课程平均成绩的课程和分数！！*/
select sno,cno,grade from sc a
where grade >= (select AVG(grade) from sc b  where a.sno=b.sno
group by sno)

/*查询间接先修课为高级程序设计语言所有课程的信息*/
select a.cno,a.cname from course a,course b,course c
where a.cpno = b.cno and b.cpno = c.cno and c.cname like '%高级语言程序设计%'

select a.cno,a.cname,b.cno 先修课,b.cpno 间接先修课
from course a,course b
where a.cpno=b.cno and b.cpno='262191'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

一个小案例

/*查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。*/
select a.emp_name,a.sex,a.title,a.salary,a.addr from employee a,employee b
where a.sex = b.sex  and a.addr=b.addr 
and a.dept = b.dept and a.sex='女' and a.emp_name != b.emp_name

/*查询在订单中订购金额超过100元的顾客姓名及收件人。*/
select cust_name, receiver from customer
where cust_name in (select cust_name from sales
where total_amt >=100)

/*检索实际单价高于50元的图书信息（图书编号、书名、数量、实际销售单价）。*/
select sale_item.book_no,books.book_name,qty,unit_price from sale_item,books
where unit_price>=50 and sale_item.book_no = books.book_no

/*统计所有图书的销售量和销售金额，并按销售金额的大小降序排序，
显示信息包括（图书编号，书名，销售量，销售金额）。*/
select book_name 书名,图书编号,销售量,销售金额 from books,
(select book_no 图书编号,SUM(qty) 销售量,SUM(unit_price)销售金额 from sale_item
group by book_no) as tableone
where books.book_no = tableone.图书编号
order by 销售金额 desc

/*（开放题）该公司现在想做促销活动，想将“数据库系统概论”和其他的图书进行套餐搭配，请你帮忙设计一种方案，并说明理由。
--即查找和“数据库系统概论”一同被购买的图书*/
select book_name from books
where book_no in
(select book_no from sale_item a
where order_no in
(select order_no from sale_item b
where book_no in (select book_no from books
where book_name='数据库系统概论' and a.book_no!=books.book_no)))

/*查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。*/
select a.emp_name,a.sex,a.title,a.salary,b.emp_name,b.sex,b.title,b.addr from employee a,employee b
where a.addr=b.addr and a.emp_no!=b.emp_no

/*统计所有用户的消费金额，并按金额的大小降序排序。*/
select cust_name,SUM(total_amt) 消费金额 from sales
group by cust_name
order by SUM(total_amt) desc

/*  检索所有图书的销售明细（图书编号，书名，数量，实际销售单价） 
提示：使用外连接，将没有售出的书也要显示出来。*/

select books.book_no,book_name 书名,price,数量 from books LEFT OUTER JOIN
(select book_no ,SUM(qty) 数量 from sale_item
group by book_no) as tabletwo
ON (books.book_no = tabletwo.book_no)


/*查找有同时购买了“数据库系统概论”和“java编程思想”的订单号。*/

select order_no from books,
(select order_no,book_no from sale_item a
where order_no in 
(select order_no from sale_item b
where  book_no =
(select book_no from books
where a.book_no!=b.book_no and books.book_name ='数据库系统概论'))) as tablethree
where books.book_no = tablethree.book_no and books.book_name='java编程思想'

/*查询“数据库系统概论”的销售总金额。*/
select book_no,SUM(qty*unit_price)销售总额 from 
(select * from sale_item
where book_no in 
(select book_no from books
where book_name='数据库系统概论')) as tablefour
group by book_no
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

数据插入

插入元组

INSERT
INTO student(sno,sname,ssex,sdept,sage)
VALUES('201926205028','小红','女','IS','22')
1
2
3

插入子查询结果

INSERT
INTO dept_age(sdept,avg_age)
SELECT sdept,AVG(sage)
from student
group by sdept
1
2
3
4
5

数据修改

修改某一个元组的值

UPDATE student
set sage=22
where sno = '201926205028'
1
2
3

修改多个元组的值

UPDATE student
set sage=sage+1
1
2

带子查询的修改语句

UPDATE sc
set grade=0
where sno in
(select sno from student where sdept='IS')
1
2
3
4

删除数据

删除某一个元组的值

DELETE from student
where sno ='201926025'
1
2

删除多个元组的值

DELETE FROM stduent
1

带子查询的删除语句

DELETE from sc
where sno in
(select sno from student
where sdept='CS')
1
2
3
4

空值的处理

空值的产生

INSERT INFO SC(sno,sanme,grade)
VALUES('2019262050','xiaohua',NULL)
1
2

另外外连接也会产生空值

在插入语句时没有赋值的属性，取空值

空值的判断

IS NULL 或IS NOT NULL

select * from student
where sname is null or ssex is null or sage is null or sdept is null
1
2

空值的约束条件

属性定义或者域定义中有NOT NULL约束条件的不能取空值，码属性不能取空值

空值的算术运算、比较运算和逻辑运算

空与另一个的算术运算结果为空

比较运算结果为UNKNOWN，有了UNKNOWN后传统的逻辑运算中二值逻辑就变成了三值逻辑

视图

视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，它是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中，所以一旦基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。

视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图。

建立视图

CREATE VIEW <视图名>[(<列名>)]
AS <子查询>
[WITH CHECK OPTION]
1
2
3

子查询可以是任意SELECT 语句

WITH CHECK OPTION表示对视图进行UPDATE,DELETE INSERT操作时要保证更新、插入删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

若一个视图时从单个基本表导出的，并且知识去掉了基本表的某些行某些列，保留了主码，则这类视图为行列子集视图。

视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图表上，或建立在基本表与视图上。

定义基本表时，为了减少数据库中的冗余数据，表中只存放基本数据，由基本表经过各种计算派生出的数据一般时不存储的由于视图的数据并不实际存储，所以定义视图时可以根据应用的需要设置一些派生属性列。这些派生属性由于在基本表中并不实际村子啊，也称他们为虚拟列。带虚拟列的视图也称为带表达式的视图

带有聚集函数和GROUP BY子句的查询来定义视图，这种视图称为分组视图

CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2014-Sage
FROM Student
1
2
3
4

如果修改基本表的结构，很可能这种基本表和视图的映像关系就会被破坏，该视图不能正常工作了，所以为了避免这些问题，最好在修改基本表之后删除由该基本表导出的视图，然后重建这个视图。

删除视图

DROP VIEW <视图名> [CASCADE]
1

如果该视图还导出了其他视图，那么用CASCADE级联删除语句把该视图和由它导出的所有视图一起删除

查询视图

视图消解：关系数据库管理系统对视图进行查询时，首先进行有效性检查，检查查询中涉及的表、视图是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。

定义视图并查询视图与基于派生表的查询是有区别的。视图一旦定义，其定义将永久保存在数据字典中，，之后的所有查询都可以直接引用该视图。而派生表只是在语句执行时临时定义，语句执行后该定义就被删除。

更新视图

INSERT DELETE UPDATE

由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。

但并不是所有的视图都是可更新的，因为有些视图的更新不能唯一的有意义的转换成对应基本表的更新。

一般行列子集视图是可更新的。而且各个系统对于视图的更新还有更进一步的规定，这些规定也不尽相同。（不可更新和不允许更新是两个不同的概念）

视图的作用

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