数据库Part1：关系数据库标准语言SQL（完整版）_sql语言

目录

一：SQL概述

1：定义

2：特点

二:范例：创建一个学生-课程数据库

三：数据定义

1:定义基本表

2：插入数据（增）

（1）插入元组

（2）插入子查询结果

3：数据删除（删）

4：修改数据（改）

​编辑

 四：数据查询（查）

1：单表查询

（1）选择表中的若干列

（2）查询全部列

 （3）查询经过计算的值

   (4)取消重复的值

（5）比较大小

 （6）确定范围

 (7) 确定集合

 (8)字符匹配

(9) 多重条件查询

（10）ORDER BY子句（对结果进行排序）

（11）聚合函数

2：连接查询

（1）自然连接

（2）自身连接

（3）外连接

（4）复合条件连接

 3：嵌套查询

        （1）带有IN谓词的子查询

        (2)带有比较运算符的子查询

        （3）带有any（some）或all谓词的子查询

         (4)带有EXISTS谓词的子查询

4.集合查询

---

一：SQL概述

1：定义

SQL（Structured Query Language）：结构化查询语言，是关系数据库的标准语言

SQL是一个通用的、功能极强的关系数据库语言

2：特点

1.综合统一

集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。可以独立完成数据库生命周期中的全部活动。

2.高度非过程化

SQL只要提出“做什么”，无须了解存取路径。

存取路径的选择以及SQL的操作过程由系统自动完成。

3.面向集合的操作方式

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

SQL是独立的语言

    能够独立地用于联机交互的使用方式

SQL又是嵌入式语言

    SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用

5.语言简洁，易学易用

SQL功能极强，完成核心功能只用了9个动词。

二:范例：创建一个学生-课程数据库

要求：

学生-课程模式 S-T :    

    学生表：Student(Sno,Sname,Ssex,Sage,Sdept)

    课程表：Course(Cno,Cname,Cpno,Ccredit)

     学生选课表：SC(Sno,Cno,Grade)

学生表：

课程表：

学生选课表：

​​​​​​​

 

三：数据定义

        这一小节对应的是数据库中表的创建和增删这几个步骤，比较重要的查（select）和改留在后文

        对需要建立的三个表进行定义，我们将需要定义的属性当做表中的列名，方便我们以后在这些表中按照列名插入数值。

        SQL的数据定义功能: 模式定义、表定义、视图和索引的定义

1:定义基本表

        该步骤的目的是创建一个空的表，它的每一列代表着不同的属性 

        语法：

                CREATE TABLE <表名>

              （<列名> <数据类型>[ <列级完整性约束条件> ]

              [，<列名> <数据类型>[ <列级完整性约束条件>] ] …

              [，<表级完整性约束条件> ] ）；

        对于语法中的名词解释：

        表名：即你将要创建的这个表的名字

        列名：表中列的名字，一般表现为在表中包含的某一种属性

        数据类型：直接用下图来说明： 

开始建立“学生”表Student，学号是主码，姓名取值唯一。

点击新建查询，然后就可以编写代码，最后点击执行，此时将建立一个空的学生表

create table student
	(Sno CHAR(9) PRIMARY KEY,
	 Sname CHAR(20) UNIQUE,
	 Ssex CHAR(2),
	 Sage INT,
	 Sdept CHAR(20)
	);

注：编写代码时需要注意SQL语言时的编写习惯，其中各列的信息用的是小括号”()”来包括而不是平时常用的大括号”{}”,而且每句最后要用逗号隔开，这点常用Python编写程序的可能会忽略，最后需要用分号‘；’来结尾否则会报错

如图，这是还没有插入数据的空表：

 建立课程表：

create table Course
	(Cno CHAR(4)PRIMARY KEY,
	 Cname CHAR(10),
	 Cpno CHAR(4),
	 Ccredit SMALLINT
	 FOREIGN KEY (Cpno) REFERENCES Course(Cno)
	 /*Cpno是外码，被参照表是Course，被参照列是Cno*/
	);

未插入数据时的课程表（其中cpno在后续的自身连接会有用到）：

创建学生选课表

create table sc
	(Sno CHAR(9),
	 Cno CHAR(4),
	 Grade SMALLINT,
	 PRIMARY KEY(Sno, Cno),
	 /* 主码由两个属性构成，必须作为表级完整性进行定义*/
	 FOREIGN KEY(Sno)REFERENCES student(Sno),
	 /* 表级完整性约束条件，Sno是外码，被参照表是Student */
	 FOREIGN KEY(Cno)REFERENCES Course(Cno)
	 /* 表级完整性约束条件，Cno是外码，被参照表是Course*/
 	);

未插入数据的sc表： 

至此为止，三个主要的表格已经创建完毕，接下来要做的就是往这几个表中插入数据和进行增删改查等基本操作。

2：插入数据（增）

插入数据一共有两种插入方式：

（1）插入元组

INSERT INTO 语句用于向表格中插入新的行。

使用语法：

INSERT INTO 表名VALUES (值，值。。。)

使用实例如图所示：

但是这样太过繁琐，也可以这样编写：

INSERT INTO 表名（列名，列名。。。）VALUES (值，值。。。)

INSERT INTO student (Sno,Sname,Ssex,Sage,Sdept)
VALUES
      ('200215121','阿大','男',21,'数学'),
	  ('200215122','阿二','男',20,'数学'),
	  ('200215123','阿三','女',19,'音乐'),
	  ('200515125','阿四','女',18,'音乐');

运行结果： 

由于当数据量太大时，自己逐行输入会变得十分麻烦，我们可以将Excel表中的数据导入到数据库中，其中一种比较直白的方法就是使用Excel自带的函数来生成表达式然后粘贴到数据库中，操作方式如下：

step1：使用Excel填入数据：

step2:在第一行的最后一列（此表中为sdept）的后一行填写以下代码：

=CONCATENATE("insert into student(Sno,Sname,Ssex,Sage,Sdept) values('",A2,"','",B2,"','",C2,"',",D2,",L9'",E2,"');")

然后点击函数按键：

点击确定，之后在我们填写代码的那一行会自动生成我们需要的代码 

step3: 接着只需要将这一行复制，接着下拉粘贴就可以得到我们需要的全部代码（插入数据部分）

插入课程信息表格：

填充sc表：

（2）插入子查询结果

语句格式     

 INSERT    

INTO <表名>  [(<属性列1> [，<属性列2>…  )]    

子查询；（目标列必须与into子句匹配：值的个数，值的类型）

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

第一步：建表

CREATE TABLE det_age
(Sdept char(15),
 Avg_age int
)

第二步：插入数据

INSERT
INTO det_age(sdept, Avg_age)
	select student.Sdept, AVG(Sage)
	from student
	group by Sdept

查询生成结果：

Notes：在进行数据插入过程中遇到的几个问题：

        Q1:由于表sc中存在外键，所以当student和course表需要清除数据的时候，需要先将外键删除，之后才可以清除数据，并且清除之后还要重新建立外键，其过程如下所示：

        以student表为例：

Step1:查找所有引用了 'student' 表的外键约束。您可以使用以下SQL语句：

SELECT name, object_name(parent_object_id)
 
FROM sys.foreign_keys
 
WHERE referenced_object_id = object_id('student'); 

        Step2:对于返回的每个外键约束，使用以下SQL语句删除它：

        ALTER TABLE <referencing_table> DROP CONSTRAINT <constraint_name>;

        其中，<referencing_table>是引用 'student' 表的表名，<constraint_name>是外键约束的名称。

        Step3：现在，您可以截断 'student' 表：

        TRUNCATE TABLE student;

        Step4：重新创建您删除的外键约束。

        ALTER TABLE <referencing_table> ADD CONSTRAINT <constraint_name> FOREIGN KEY (<column_name>) REFERENCES student(<column_name>);

        其中，<referencing_table>是引用 'student' 表的表名，<constraint_name>是外键约束的名称，<column_name>是外键列的名称。

        请注意，截断表将删除该表中的所有数据，并且无法恢复。在执行此操作之前，请确保您已经备份了所有数据。

        至此，对外键的删除和恢复工作算是完成了。

        Q2：如果以前在别的库里进行过表的操作，如何确保自己这次建的表是在自己需要的库中：

        在左上角有个图标可以选择自己需要操作的库：

        Q3：如何在使用INSERT INTO语句时插入空值：

        可以使用关键字NULL来表示空值。在INSERT INTO语句中。

        Q4：例如sc中存在要引入外键的表还需要再次插入外键的数值吗：

        是的，而且输入的列值必须是真实存在的，否则将引发外键约束错误从而报错。

3：数据删除（删）

删除行

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

清除表的数据（保留表，但是数据全部被清除）

语法：TRUNCATE TABLE 表名称;

删除表（什么都不剩）

语法：DROP TABLE 表名称;

4：修改数据（改）

单纯地修改数值使用update

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

比如要将student表中阿大的年龄改成25，专业改为计算机：

update student set sage=25,Sdept='计算机' where sname='阿大' 

  修改表的整体属性使用ALTER：

         在数据库中，使用ALTER命令可以修改表的结构和属性，包括添加、删除、修改列、约束、索引等。

语法：

ALTER TABLE <表名>

        [ ADD <新列名> <数据类型> [ 完整性约束 ] ]

        [ DROP <完整性约束名> ]

        [ ALTER COLUMN<列名> <数据类型> ]；

若要在“student”表中添加一个名为“email”的新列，其数据类型为VARCHAR，长度为255个字符。

ALTER TABLE student ADD email VARCHAR(255);

 注意：此时如若想要填入email的值，不可使用insert函数，因为如果在INSERT INTO语句中只指定了某些列的值，而其他列没有指定值，则这些未指定值的列将被设置为默认值或NULL（如果没有指定默认值）。

此时就应该使用update 语句：

update student set email='123@qq.com' where Sname='阿大'
update student set email='456@qq.com' where Sname='阿二'
update student set email='789@qq.com' where Sname='阿三'
update student set email='741@qq.com' where Sname='阿四'

修改多个元组的值：

将所有的学生年龄加一

update student
set sage = sage + 1

带有子查询的修改语句

例：将计算机系全体学生的成绩改为88

update sc
set Grade = 88
where '计算机' = 
			(select student.Sdept
			 from student
			 where student.Sno = sc.Sno
			)

 四：数据查询（查）

语法：

SELECT [ALL|DISTINCT] <目标列表达式>

                                                [，<目标列表达式>] …

FROM <表名或视图名>[， <表名或视图名> ] …

[ WHERE <条件表达式> ]

[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]

[ ORDER BY <列名2> [ ASC|DESC ] ]；

查询的内容比较多，这里分为四部分来说：

单表查询；连接查询；嵌套查询；集合查询

1：单表查询

（1）选择表中的若干列

语法：

Select 列1,列2,...

From 表名

查看姓名和学号：

select Sno,Sname
from student

（2）查询全部列

语法：

select * from 表名称

 （3）查询经过计算的值

SELECT子句的<目标列表达式>可以为：

算术表达式

字符串常量

函数

列别名

实例：使用birth来表示student中学生的出生年

select Sname,2023-Sage as birth
from student;

   (4)取消重复的值

以sc中的sno为例

关键词：DISTINCT

select distinct Sno
from sc;

（5）比较大小

语法：

Select 列1,列2,...

From 表名

where 条件

例：查询所有年龄在20岁以下的学生姓名及其年龄。

 SELECT Sname，Sage 
FROM    Student    
WHERE Sage < 20

 （6）确定范围

谓词:   BETWEEN …  AND  …   NOT  BETWEEN  …  AND  …

例：查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄

 SELECT Sname,Sdept,Sage
FROM     Student
WHERE   Sage BETWEEN 20 AND 23

 (7) 确定集合

谓词：IN <值表>,  NOT IN <值表> 

 例：查询年龄在20,21,25的学生所有信息

select *
from student
where Sage in(20,21,25)

 注意：关于“null”值的搜索只能使用is不可以使用“=”

 (8)字符匹配

谓词： [NOT] LIKE  ‘<匹配串>’  [ESCAPE ‘ <换码字符>’]（在某些情况下“like”用法等同于“=”）

匹配串为固定字符串

例： 查询学号为200215121的学生的详细情况。

select *
from student
where Sno like 9

   匹配串为含通配符的字符串

分为两种情况：

 %：任意多个字符；

_：单个字符（汉字为2个字符）

例：查询所有姓小学生的姓名、学号和性别

 SELECT Sname,Sno,Sage
      FROM Student
      WHERE  Sname LIKE '小%'

(9) 多重条件查询

查询计算机系年龄在20岁以上的学生姓名。

select *
from student
where student.Sdept = '信息安全' and student.Sage > 20

（10）ORDER BY子句（对结果进行排序）

升序：ASC；降序：DESC；缺省值为升序

注：

当排序列含空值时 ASC：排序列为空值的元组最后显示

DESC：排序列为空值的元组最先显示

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

 SELECT *
      FROM sc
      WHERE cno=3
      order by grade desc

（11）聚合函数

 聚集函数：

计数 COUNT（[DISTINCT|ALL] *） COUNT（[DISTINCT|ALL] <列名>）

计算总和 SUM（[DISTINCT|ALL] <列名>）     

计算平均值 AVG（[DISTINCT|ALL] <列名>）

最大最小值       MAX（[DISTINCT|ALL] <列名>）      MIN（[DISTINCT|ALL] <列名>）

例：查询学生总人数。

select count(*) as '总人数'
from student

 计算2号课程的学生平均成绩。

select AVG(grade) as '科目2的平均分'
from sc
where cno=2

查询2号课程的学生最高成绩

select MAX(grade) as '最高分'
from sc
where Cno=2

现在要查询学号为5的学生所选的课程的学分总和，此时需要有两个表，一个记录课程编号和学分的course表和一个记录了学号和选课记录的sc表。其中两者都有cno这一列，且sc的cno是外码，被参照表是Course，关于外码的概念和作用，该博主的博客笔者感觉解释的很透彻且易懂：【数据库】码，超码，候选码与主码，外码------脚踏实地的大梦想家

了解了外码的概念及作用之后，该段代码的逻辑应该就会比较清晰了：

select SUM(coures.Ccredit) as '选修总分'
from sc, Coures
where sc.Sno= 5 and sc.Cno=Coures.Cno

（12）GROUP BY子句

GROUP BY子句分组：      

细化聚集函数的作用对象  

未对查询结果分组，聚集函数将作用于整个查询结果  

对查询结果分组后，聚集函数将分别作用于每个组

作用对象是查询的中间结果表

按指定的一列或多列值分组，值相等的为一组

只是看定义有些难以理解，辅以下列例子：

求各个课程代码和选择该课的总人数

  SELECT Cno,COUNT(Sno) as '选课总人数'
     FROM    SC
     GROUP BY Cno

 下面介绍几种较为常见的错误：

忘记在最后加上group by：

 第一行中如果少打一个sno

那么结果只有索引没有课程编号。其中"选课"列的总值为

select sc.Cno, COUNT(sno)
from sc

他的值代表着所有备选课程的总数目。

查询选修了两门课以上的人的学号：

 SELECT Sno, COUNT(Sno) as '选课总门数'
     FROM  SC
     GROUP BY Sno
     HAVING  COUNT(sno) > 5

 注：上述代码中使用了having，没有使用where，两者的区别在于：

作用对象不同 ：WHERE子句作用于基表或视图，从中选择满足条件的元组 ；HAVING短语作用于组，从中选择满足条件的组。

2：连接查询

同时涉及多个表的查询

 一般格式： [<表名1>.]<列名1>  <比较运算符>  [<表名2>.]<列名2>

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

（1）自然连接

例：查询每个学生及其选课情况：

SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
 FROM     Student,SC
 WHERE  Student.Sno = SC.Sno

（2）自身连接

        一个表与其自己进行连接 需要给表起别名以示区别 ；由于所有属性名都是同名属性，因此必须使用别名前缀。

例：查询每一门课的间接先修课（即先修课的先修课）

select x.cno, y.cpno as '先行课'
from Coures x, Couresa y
where x.cpno = y.cno

（3）外连接

        外连接与普通连接的区别： 普通连接操作只输出满足连接条件的元组；外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出。

        外连接的分类：

        左外连接 列出左边关系（如下例Student）中所有的元组  

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

        全外连接 列出两边关系中所有的元组 不符合连接条件的元组的另一个关系的属性取空值

例：改写自然连接

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
    FROM  Student  LEFT  JOIN SC ON (Student.Sno=SC.Sno)

 该链接方式与自然连接的不同在于该方式（左连接）在连接时，会将左表的所有行列出来，但是只连接那些符合条件的行，那些不符合链接条件的行只会显示左表的值，右表的值自动填充为null

若改为右连接：

（4）复合条件连接

        查询选修5号课程且成绩在60分以上的所有学生    

select student.Sno,sname,sc.Grade
from student,sc
where sc.Sno = student.Sno
	  and
	  sc.Cno=5 and sc.Grade>60

 使用左连接查询实例：

select student.Sno,sname,sc.Grade
from student,sc
where sc.Sno = student.Sno
	  and
	  sc.Cno=5 and sc.Grade>60

 3：嵌套查询

        嵌套查询概述：一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

        （1）带有IN谓词的子查询

        查询与爱丽丝在同一个系的学生：该查询由两步组成：第一步要先查询爱丽丝所在的系名称：

select student.Sdept
from student
where student.Sname = '爱丽丝'

第二步是使用第一步查询到的系名称，查询该系所有学生的名字

select student.Sname, student.Sage
from student
where student.Sdept = '信息安全'

现在我们要做的是将两者结合，使用IN谓词

select student.Sname, student.Sage
from student
where student.Sdept IN (select student.Sdept
                        from student
                        where student.Sname = '爱丽丝'
                        )

也可以使用自身连接来完成该要求：

select x.Sname, x.Sage 
from student x, student y
where x.Sdept = y.Sdept
	  and y.Sname = '爱丽丝'

        上述的例子都是使用同一个表来查询，那么如果需要使用多个表查询的话需要用什么方法呢：查询选修了课程名称为"高数"的学生学号和姓名

        因为cname = '高数'的项只在coures中有，所以要在coures中找到对应的cno，再由cno找到sc中对应的sno，最后再由sno在student中找到对应的sname：

select student.Sname, student.Sno
from student
where Sno in
			(select sc.Sno
			 from sc
			 where Cno in
						  (select coures.Cno
						  from Coures
						  where Cname = '高数'
						  )
			 )

该例亦可以使用连接查询实现：

select student.Sno, student.Sname
from student, Coures, sc
where student.Sno = sc.Sno
	  AND sc.Cno = Coures.Cno
	  AND Coures.Cname = '高数'

                                                                             

        (2)带有比较运算符的子查询

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

        该例中包含了聚集函数的使用和比较运算符

        先准备需要比较的两项：选修课成绩和选修课平均分

平均分查询：

select AVG(y.Grade)
from sc y
where x.Sno = y.Sno

代码：

select x.Sno, x.Cno, x.grade
from sc x
where x.Grade >= (select AVG(y.Grade)
				  from sc y
				  where x.Sno = y.Sno
				  )

                                                       该段代码中不使用group by进行分组的原因是:子查询中的AVG函数已经使用了WHERE子句来限定了特定学生（y.Sno=x.Sno）的成绩进行平均计算。

        此时如果想要添加一列平均值作为对比，我们可以使用自然连接将子查询的结果作为一个列返回

select x.Sno, x.Cno, x.grade, (SELECT AVG(Grade) FROM SC y WHERE y.Sno = x.Sno) AS AvgGrade
from sc x
where x.Grade >= (select AVG(y.Grade)
				  from sc y
				  where x.Sno = y.Sno
				  )

         此时若要求使用外连接，且要求不按学生学号来将平均值分组，而是按照cno来分组进行比较，此时的代码应为：

SELECT student.Sname, sc.Grade, sc.Cno, avg_gradet.avg_grade
FROM student, sc
JOIN (
    SELECT Cno, AVG(Grade) AS avg_grade
    FROM sc
    GROUP BY Cno
) AS avg_gradet ON sc.Cno = avg_gradet.Cno
WHERE student.Sno = sc.Sno
    AND sc.Grade > (SELECT AVG(Grade) FROM sc)
ORDER BY sc.Cno;

        （3）带有any（some）或all谓词的子查询

        查询其他系中比计算机某一学生年龄小的学生姓名和年龄

        要求:sdept不能为计算机（sdept!='计算机'）

select student.Sname, student.Sage
from student
where student.Sage < any(select Sage
						 from student
						 where student.Sdept = '计算机'
						)
and student.Sdept != '计算机'

         (4)带有EXISTS谓词的子查询

        存在量词

        带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。 若内层查询结果非空，则外层的WHERE子句返回真值 若内层查询结果为空，则外层的WHERE子句返回假值

        由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义

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

select student.Sname
from student
where exists
	  (select *
	   from sc
	   where sc.Sno = student.Sno and Cno = 1
	  )

        这段代码并不像前三种情况子查询中是对某一或多个列的各种值进行筛选，带有exists 谓词的子查询只输出结果为true的结果。

        个人感觉较常用的使用场景：查询选修了全部课程的学生姓名

select student.Sname
from student
where not exists
		(select *
		 from Coures
		 where not exists
				(select *
				 from sc
				 where sc.Sno = student.Sno
				 and sc.Cno = Coures.Cno
				)
		)

        首先，我们从"student"表中选择学生的姓名（"Sname"列）。 然后，我们使用子查询来检查是否存在一个课程（"Coures"表），该课程没有被学生选修。 最后，我们使用另一个子查询来检查是否存在一个学生（"sc"表），该学生没有选修特定的课程（由"sc.Sno = student.Sno"和"sc.Cno = Coures.Cno"条件确定）。

4.集合查询

集合操作的种类：

并操作UNION

交操作INTERSECT

差操作EXCEPT

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

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

select *
from student
where student.Sdept = '计算机'
union
select *
from student
where student.Sage <= 19

查询选修了课程1或者课程2的学生

select sc.Sno
from sc
where sc.Cno = 1
union
select sc.Sno
from sc
where sc.Cno = 2

查询计算机系的学生和年龄不大于19岁的学生的交集

select *
from student
where student.Sdept = '计算机'
intersect
select *
from student
where student.Sage <= 19

        查询计算机科学系中年龄大于19岁的学生（查询计算机科学系的学生与年龄不大于19岁的学生的差集）

select *
from student
where student.Sdept = '信息安全'
except
select *
from student
where student.Sage <= 19