avatar
文章
52
标签
26
分类
9
主页
文章
标签
分类
友链
关于我
0bs3rver的小屋
主页
文章
标签
分类
友链
关于我
sql语句整理
|sql
字数总计:4.3k

SQL语句整理

最近在上数据库的课，于是整理一下常用的sql语句，便于自己查看

本机为Mac osx 10.15，使用的是mysql，使用navicat作为可视化操作环境

建表

创建sc模式

CREATE SCHEMA sc;

创建学生表Student

1
2
3
4
5
6
7
8
9
10
11
Create table Student

(Sno char(9) primary key, 

Sname char(10) unique,

Ssex char(3), 

Sage smallint,

Sdept char(3) );

学号，姓名，性别，年龄，专业，其中学号为主码，姓名唯一

添加四位学生

1
2
3
4
5
6
7
insert into student(sno,sname,ssex,sage,sdept) values('201215121','李勇','男',20,'CS');

insert into student(sno,sname,ssex,sage,sdept) values('201215122','刘晨','女',19,'CS');

insert into student(sno,sname,ssex,sage,sdept) values('201215123','王敏','女',18,'MA');

insert into student(sno,sname,ssex,sage,sdept) values('201215125','张立','男',19,'IS');

创建课程表Course

1
2
3
4
5
6
Create table Course
(Cno char(4) primary key, 
Cname char(12),
Cpno char(4) ,
Ccredit smallint,
Foreign key(Cpno) references Course (Cno) );

课程号，课程名，先行课，学分，其中课程号为主码，cpno为外码，参照表是Course，参照列是cno

添加七个课程

1
2
3
4
5
6
7
insert into course(cno,cname,cpno,ccredit) values('2','数学',NULL,2); 
insert into course(cno,cname,cpno,ccredit) values('6','数据处理',NULL,2); 
insert into course(cno,cname,cpno,ccredit) values('4','操作系统',6,3); 
insert into course(cno,cname,cpno,ccredit) values('7','PASCAL',6,4); 
insert into course(cno,cname,cpno,ccredit) values('5','数据结构',7,4); 
insert into course(cno,cname,cpno,ccredit) values('1','数据库',5,4);
insert into course(cno,cname,cpno,ccredit) values('3','信息系统',1,4);

创建学生选课表sc

1
2
3
4
5
6
7
Create table SC
(Sno char(9),
Cno char(4),
Grade smallint,
primary key (Sno,Cno),
foreign key (Sno) references Student(Sno), 
foreign key (Cno) references Course(Cno));

添加5个选课关系

1
2
3
4
5
insert into sc (sno,cno, grade) values('201215121',1,92); 
insert into sc (sno,cno, grade) values('201215121',2,85); 
insert into sc (sno,cno, grade) values('201215121',3,88); 
insert into sc (sno,cno, grade) values('201215122',2,90); 
insert into sc (sno,cno, grade) values('201215122',3,80);

查询

单表查询

选择表中的若干列

eg：需要查学生表上的全部信息

1
SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student;

而查询全部信息时也可以用 * 代替

1
2
SELECT *
FROM Student;
选择经过计算的值

eg：查全体学生的姓名及其出生年份

1
2
SELECT Sname,2020-Sage
FROM Student;

而此时计算结果的列名会显示为 ‘2020-Sage’

eg：查询全体学生的姓名、出生年份和所在的院系，要求用 小写字母表示系名

1
2
3
SELECT Sname NAME, 'Year of Birth:' BIRTH, 2020-Sage BIRTHYEAR,
LOWER(Sdept) DEPARTMENT
FROM Student;

可以使用列别名来改变查询结果的列标题，且构造出来一个内容全为 ‘Year of Birth:’ 的BIRTH列，并为其他列都改变了标题

选择表中的若干元组

eg：查询选修了课程的学生学号

1
SELECT DISTINCT Sno FROM SC;

DISTINCT关键字用于去除表中重复的行

常用的查询条件
查询条件 谓词
比较 =,>,<,>=,<=,!=,<>,!>,!<;NOT+上述比较运算符
确定范围 BETWEEN AND, NOT BETWEEN AND
确定集合 IN,NOT IN
字符匹配 LIKE,NOT LIKE
空值 IS NULL,IS NOT NULL
多重条件（逻辑运算） AND,OR,NOT
比较大小

eg：查询计算机科学系全体学生的名单

1
select sname from student where sdept='CS'

π sname(σ sdept=’CS’(Student))

确定范围

eg：查询年龄在20~23岁(包括20岁和23岁)之间的学生 的姓名、系别和年龄

1
select sname,sdept,sage from student where sage between 20 and 23
确定集合

eg：查询CS系和IS系学生的姓名和性别

1
select sname,ssex from student where sdept in ('CS','IS')
字符匹配

谓词:[NOT]LIKE ‘<匹配串>’ [ESCAPE‘<换码字符>’]

<匹配串>可以是一个完整的字符串，也可以含有通配符%和 _

% (百分号) 代表任意长度(长度可以为0)的字符串，例如a%b表示以a开头，以b结尾的任意长度的字符串

_ (下横线) 代表任意单个字符，例如a_b表示以a开头，以b结尾的长度为3的任意字符串

eg：查询学号为201215121的学生的详细情况

1
2
3
SELECT *
FROM Student
WHERE Sno LIKE '201215121';

等价于

1
2
3
SELECT *
FROM Student
WHERE Sno = '201215121';

eg：查询所有姓刘学生的姓名、学号和性别

1
2
select sname,sno,ssex
from student where sname like '刘%'

使用换码字符将通配符转义为普通字符

eg：查询DB_Design课程的课程号和学分

1
2
3
SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\ ';

ESCAPE '\‘ 表示“ \” 为换码字符

涉及空值的查询

eg：查所有有成绩的学生学号和课程号

1
2
3
SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;

“IS” 不能用 “=” 代替

多重条件查询

逻辑运算符:AND和 OR来连接多个查询条件

1、AND的优先级高于OR

2、可以用括号改变优先级

eg：查询计算机系年龄在20岁以下的学生姓名

1
2
3
SELECT Sname
FROM Student
WHERE Sdept= 'CS' AND Sage<20;

聚集函数与ORDER BY子句

1、可以按一个或多个属性列排序

2、升序:ASC;降序:DESC;缺省值为升序

3、对于空值，排序时显示的次序由具体系统实现来决定

eg：查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列

1
2
select sno,grade from sc
where cno='3' order by grade desc

eg：查询全体学生情况，查询结果按所在系的系号升序 排列，同一系中的学生按年龄降序排列

1
select * from student order by sdept,sage desc

eg：查询学生总人数。

1
select count(\*) from student

eg：查询选修了课程的学生人数。 

1
select count(distinct sno) from sc

eg：计算1号课程的学生平均成绩。 

1
select avg(grade) from sc where cno='1'

eg：查询选修1号课程的学生最高分数。

1
select max(grade) from sc where cno='1'

eg：查询学生201215121选修课程的总学分数。 

1
2
select sum(ccredit) from sc,course
where sno='201215121' and sc.cno=course.cno

GROUP BY子句

eg：查询选修了3门以上课程的学生学号

1
select sno from sc group by sno having count(*) >=3

eg：查询平均成绩大于等于80分的学生学号和平均成绩

1
2
select sno,avg(grade) from sc group by sno
having avg(grade)>=80

HAVING短语与WHERE子句的区别

作用对象不同

WHERE子句作用于基表或视图，从中选择满足条件的元组

HAVING短语作用于组，从中选择满足条件的组。

连接查询

连接查询:同时涉及两个以上的表的查询

连接条件或连接谓词: 用来连接两个表的条件，一般格式:

1、[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

2、[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND[<表名2>.]<列名3>**

等值与非等值连接查询

等值连接

等值连接:连接运算符为=

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

1
select student.*, sc.* from student,sc where student.sno=sc.sno
自然连接

eg：对上一例用自然连接完成

自然连接去除了重复的列

1
2
3
select student.sno,sname,ssex,sage,sdept,
cno,grade from student, sc
where student.sno=sc.sno
连接操作的执行过程

嵌套循环法(NESTED-LOOP)
1、首先在表1中找到第一个元组，然后从头开始扫描表2，逐 一查找满足连接件的元组，找到后就将表1中的第一个元 组与该元组拼接起来，形成结果表中一个元组。
2、表2全部查找完后，再找表1中第二个元组，然后再从头开 始扫描表2，逐一查找满足连接条件的元组，找到后就将 表1中的第二个元组与该元组拼接起来，形成结果表中一 个元组。
3、重复上述操作，直到表1中的全部元组都处理完毕

一条SQL语句可以同时完成选择和连接查询

eg：查询选修2号课程且成绩在90分以上的所有学生的学号和姓名

1
2
3
select Student.sno, sname from student,sc
where Student.sno=sc.sno and sc.cno='2'
and grade>90

执行过程：

1、先从SC中挑选出Cno=’2’并且Grade>90的元组形成一个中间关系

2、再和Student中满足连接条件的元组进行连接得到最终的结果 关系

自身连接

自身连接:一个表与其自己进行连接

需要给表起别名以示区别

由于所有属性名都是同名属性，因此必须使用别名前缀

eg：查询每一门课的间接先修课(即先修课的先修课)

1
2
3
select first.cno, second.cpno
from course first, course second
where first.cpno=second.cno

外连接

外连接与普通连接的区别

1、普通连接操作只输出满足连接条件的元组

2、外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

3、左外连接：列出左边关系中所有的元组

4、右外连接：列出右边关系中所有的元组

eg：查询每个学生及其选修课程的情况，没有选修课程的同学也显示出来

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

多表连接

多表连接:两个以上的表进行连接

eg：查询每个学生的学号、姓名、选修的课程名及成绩

1
2
3
4
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短语的条件中的查询称为嵌套查询

带有in谓词的子查询

eg：外层为父查询，内层为子查询

1
2
3
4
5
select sname
from student
where sno in(select sno
            from sc
            where con='2')

可以多层嵌套，但不能使用order by子句，order by字句只能通对最终查询结果排序

带有比较运算符的子查询

当能确切知道内层查询返回单值时，可用比较运算符（>, <, =, >=, <=, !=, < >）

eg：找出每个学生超过他选修课程平均成绩的课程号

1
2
3
4
5
SELECT Sno, Cno
FROM sc x
WHERE Grade >=( SELECT AVG (Grade)
FROM sc y
WHERE y.Sno=x.Sno);

带有ANY（SOME）或ALL谓词的子查询

eg：查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

1
2
3
4
5
6
SELECT Sname,Sage
FROM Student
WHERE Sage < ANY( SELECT Sage
FROM Student
WHERE Sdept='CS')
AND Sdept<>'CS';

带有EXISTS谓词的子查询

存在量词

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

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

若内层查询结果为空，则外层的WHERE子句返回假值

NOT EXISTS谓词返回的值与之相反

eg：查询所有选修了1号课程的学生姓名

1
2
3
4
5
SELECT Sname
FROM Student
WHERE EXISTS( SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='1');
用EXISTS/NOT EXISTS实现全称量词

eg：查询至少选修了学生201215122选修的全部课程的学生号码

1
2
3
4
5
6
7
SELECT DISTINCT sno
FROM SC SCX
WHERE NOT EXISTS( SELECT *
FROM SC SCY
WHERE SCY.Sno='201215122' AND NOT EXISTS(SELECT *
FROM SC SCZ
WHERE SCZ.sno=SCX.Sno AND SCZ.Cno=SCY.Cno));

集合查询

集合操作的种类：

并操作：UNION

交操作：INTERSECT

差操作：EXCEPT

参加集合各项操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

eg：查询计算机科学系的学生及年龄不大于19岁的学生

1
2
3
4
5
6
7
SELECT * 
FROM Student
WHERE Sdept= 'CS' 
UNION
SELECT *
FROM Student
WHERE Sage<=19;

UNION：将多个查询结果合并起来时，系统自动去掉重复元组

UNION ALL：将多个查询结果合并起来时，保留重复元组

基于派生表的查询

子查询不仅可以出现在WHERE子句、Having短语，也可以出现在FROM子句中

子查询生成的临时派生表（Derived Table）成为主查询的查询对象

eg：找出每个学生超过他选修课程平均成绩的课程号

1
2
3
4
5
6
SELECT Sno,Cno
FROM SC,(SELECT sno,AVG(Grade)
FROM SC
GROUP BY Sno)
AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno=Avg_sc.avg_sno AND SC.Grade>=Avg_sc.avg_grade

数据更新

插入数据

插入元组

eg：将一个新学生元组(学号:201215128; 姓名:陈冬; 性别:男; 所在系:IS; 年龄:18岁)插入到Student表中。

1
2
3
INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES('201215128','陈东','男','IS',18);

可以与表中的列顺序不同，但必须一一对应

eg：将学生张成民的信息插入到Student表中

1
2
3
INSERT
INTO Student
VALUES('201215126','张成民','男',18,'CS');

此时必须和创建的表一一对应

eg：插入一条选课记录( ‘200215128’, ‘1 ‘)

1
2
3
INSERT
INTO SC(Sno,Cno)
VALUES('201215128','1');

此时在Grade列上会自动赋空值

1
2
3
INSERT
INTO SC
VALUES('201215128','1',NULL);

插入子查询结果

eg：对每一个系，求学生的平均年龄，并把结果存入数据库。

1
2
3
4
5
6
7
8
CREATE TABLE Dept_age
(Sdept CHAR(15),
Avg_age SMALLINT);
INSERT
INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept;

修改数据

修改元组值

eg：将学生201215121的年龄改为22岁

1
2
3
UPDATE Student
SET Sage=22
WHERE Sno='201215121'

如果更改表中所有值，则可以不添加WHERE条件

带子查询的修改语句

eg：将计算机科学系全体学生的成绩置零

1
2
3
4
5
6
UPDATE SC
SET Grade=0
WHERE Sno IN(
SELECT Sno
FROM Student
WHERE Sdept='CS');

删除数据

eg：删除学号为201215128的成绩记录

1
2
3
DELETE
FROM SC
WHERE Sno='201215128';

eg：删除计算机科学系所有学生的选课记录

1
2
3
4
5
6
DELETE
FROM SC
WHERE Sno IN(
SELECT Sno
FROM Student
WHERE Sdept='CS');

空值的处理

空值的产生

eg：将Student表中学生号为“201215123”的学生所属的系改为空值

1
2
3
UPDATE Student
SET Sdept=NULL
WHERE Sno='201215123';

空值的判断

用IS NULL或IS NOT NULL来进行判断

eg：从Student表中找出漏填了数据的学生信息

1
2
3
SELECT *
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL

空值的约束条件

有NOT NULL约束条件的不能取空值

加了UNIQUE限制的不能取空值

码属性不能取空值

空值的运算

算术运算：结果为空

比较运算：结果为UNKNOWN

逻辑运算：有UNKNOWN后，传统二值（TRUE，FALSE）逻辑扩展成三值逻辑。

eg：找出选修1号课程的不及格的学生以及缺考的学生

1
2
3
SELECT Sno
FROM SC
WHERE Cno='1'AND(Grade<60 OR Grade IS NULL);

定义视图

视图：虚表，是从一个或几个基本表(或视图)导出的表

只存放视图的定义，不存放视图对应的数据

基表中的数据发生变化，从视图中查询出的数据也随之改变

建立视图

WITH CHECK OPTION：对视图进行UPDATE，INSERT和DELETE操作时，保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)

建立视图时SELECT后也可写 * ，但是缺点是修改基表的结构后，基表与视图的映象关系被破坏，导致该视图不能正确工作。

行列子集视图

从单个基本表导出，并且只是去掉了基本表的某些行和某些列，但保留了主码

eg：建立信息系学生的视图，要求进行修改和插入操作时仍需保证该视图只有信息系的学生

1
2
3
4
5
6
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION

基于多个基表的视图

eg：建立信息系选修了1号课程的学生的视图(包括学号、姓名、成绩)

1
2
3
4
5
CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Sage
FROM Student,SC
WHERE Sdept='IS' AND Student.Sno=SC.Sno AND SC.Cno='1';

基于视图的视图

eg：建立信息系选修了1号课程且成绩在90分以上的学生的视图

1
2
3
4
5
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90

带表达式的视图

eg：定义一个反映学生出生年份的视图

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

分组视图

eg：将学生的学号及平均成绩定义为一个视图

1
2
3
4
5
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;

删除视图

eg：删除视图BT_S和IS_S1

1
2
DROP VIEW BT_S;
DROP VIEW IS_S1;

(实践中删除IS_S1不需要使用级联删除)

删除IS_S1后打开IS_S2会报错

查询视图

eg：在信息系学生的视图中找出年龄小于20岁的学生

1
2
3
SELECT Sno,Sage
FROM Is_Student 
WHERE Sage<20;

更新视图

与前面对表的操作类似，但是需要注意仅有行列子集视图更新后能方便的转化为对基本表的更新

插入

eg：向信息系学生视图IS_S中插入一个新的学生记录，其中学号为”201215129”，姓名为”赵新”，年龄为20岁

1
2
3
INSERT
INTO IS_Student
VALUES('201215129','赵新',20);

（操作中出现了2013报错的蜜汁bug，晚上事情太多，下次遇到再解决吧

删除

eg：删除信息系学生视图IS_Student中学号为”201215129”的记录

1
2
3
DELETE
FROM IS_Student
WHERE Sno='201215129'

索引

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

建立索引

eg：Course表按课程号升序建唯一索引

1
CREATE UNIQUE INDEX Coucno ON Course(Cno);

修改与删除索引

eg：将Course表的Coucno索引名改为CCno

尝试教材上的sql语句失败，猜测是环境原因，但是并未在网络上找到对应的解决方案

文章作者: 0bs3rver
文章链接: http://yoursite.com/2020/04/17/sql%E8%AF%AD%E5%8F%A5%E6%95%B4%E7%90%86/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 0bs3rver的小屋！
sql