【数据库原理】数据增删改查，DML、单表查询、多表连接查询

DML数据操纵语言，处理对象是数据本身。
DDL数据定义语言，处理对象是数据表的结构。

数据库中数据处理主要包括增删改查。查询属于重点部分。
假设数据库中有表：

student(sno,sname,sex,class);	#学生（学号，姓名，性别，班级）；学号为主键
course(cno,cname,teacher);	#课程（课程号，课程名，教师）；课程号为主键
sc(sno,cno,score);	#学习（学号，课程号，分数）；学号和课程号为主键

1. 增加（INSERT）
向一个表格中插入一条数据。注意非空且无默认值的属性，必须插入数据。
语句格式：

 INSERT INTO <表名> [(<列名>)]
 VALUES (<数据>);

(1) 插入单条数据：

INSERT INTO student
VALUES ('20240101','张三','男','2024级计算机科学与技术1班');

(2)插入多条数据：

INSERT INTO course
VALUES ('C01','数据库管理','teacher1'),
	   ('C02','数据结构','teacher2'),
	   ('C03','C语言程序基础','teacher3');

(3)插入子查询结果
把平均成绩大于80分的学生的学号和平均成绩存入另一个的基本表stud_good(sno,avg_score)中。

INSERT INTO stud_good(sno,avg_score)
SELECT sno,AVG(score)
FROM sc
GROUP BY sno
HAVING AVG(scors)>80;

2. 删除（DELETE）

删除现有表或视图中的一行或多行记录。
语句格式：

 DELTET FROM <表名> [WHERE <条件表达式>];

(1)删除单条数据

DELETE FROM student
WHERE sno='20240101';

(2)删除多条数据
删除计算机科学与技术1班所有学生的成绩

DELETE FROM sc
WHERE sno IN (SELECT sno
			  FROM student
		      WHERE class='计算机科学与技术1班')；

4. 修改（UDPATE）

修改数据表中的单个数据或多条数据。通过设置条件进行选定修改的数据。
语句格式：

UPDATE <表名>
SET <列名1>=<表达式1> [,<列名2>=<表达式2>]
[WHERE <条件表达式>];

举个栗子
将计算机科学与技术1班学生成绩全部置0。

UPDATE sc
SET score=0
WHERE sno IN (SELECT sno
			  FROM student
			  WHERE class='计算机科学与技术1班')；

5. 查询（SELECT）

查询语句较多，第一类是单表查询，条件书写比较简单。第二类是多表查询，需要进行连接操作。

（1）单表查询
语法格式：

SELECT [ALL|DISTINCT] <列名1> [AS <显示列名>]
FROM <表名|视图名> [LIMIT [M,] N]
[WHERE <条件表达式>];

ALL和DISTINCT设置显示全部信息，或去重后的信息。
列名必须有，AS后跟显示列名。比如sno表示学号，使用AS后列名显示‘学号’，但是内容是sno中的信息。*表示数据表的全部列。
LIMIT设置结果的前N行，或从M行开始的前N行。
WHERE设置查询满足条件的记录。
注意：数字类型数据可直接写，除此之外的都需要用单引号‘’。

WHERE条件语句运算符

聚集函数----统计数据
COUNT、AVG、SUM、MAX、MIN

分组筛选----按照某一列进行分组

[GOURP BY 列名清单 [HAVING 条件表达式]]

通常结合聚集函数进行使用，比如统计个数，统计平均数。举个栗子，查询超过35人的班级的人数。

SELECT class,COUNT(*) AS 人数
FROM student
GROUP BY class
HAVING COUNT(*)>35;

注意：WHERE条件查询和HAVING条件查询的区别。WHERE作用于被查询的表，HAVING作用于结果组，选择满足条件的组。

结果排序----按照某规则进行排序
对查询结果按照某一列进行排序，ASC表示升序，DESC表示降序。多列排序亦可。

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

举个栗子，查询全体学生的信息，查询结构按班级进行升序排序，同一个班级内，按照学号进行降序排序。

SELECT *
FROM student
ORDER BY class ASC, sno DESC;

（2）多表连接查询

多表连接查询，在查询的时候涉及到两个或以上的表格。通常分为交叉连接、内连接、自连接和外连接。外连接分为左连接、右连接和全外连接。

交叉连接：笛卡尔连接
两个表做笛卡尔积运算，分别连接，A表有M个数据，B表有N个数据，交叉连接后有MN条记录。

语法格式：

SELECT [ALL|DISTINCT] [别名.]<选项1> [AS <显示列名>]
FROM <表名1> [别名1], <表名2> [别名2];

内连接：交叉连接中选取满足条件的记录

语法格式：

SELECT [ALL|DISTINCT] [别名.]<选项1> [AS <显示列名>]
FROM <表名1> [别名1], <表名2> [别名2]
WHERE <连接条件表达式> [AND  <条件表达式>];

SELECT [ALL|DISTINCT] [别名.]<选项1> [AS <显示列名>]
FROM <表名1> [别名1] INNER JOIN <表名2> [别名2] ON <连接条件表达式>
WHERE <条件表达式>;

两种格式都是可以使用的，连接条件表达式，一般可以设置两个表中的某些数据必须相同，比如学号相同。
条件表达式，可以加以限制其他的，比如成绩高于80分。

自连接

连接操作一般选用两个不同的表格，对于一个表格可以和自身连接，自连接在逻辑上看成两个表。注意必须设置别名。
语法格式：

SELECT [ALL|DISTINCT] [别名.]<选项1> [AS <显示列名>]
FROM <表名1> [别名1], <表名1> [别名2]
WHERE <连接条件表达式> [AND  <条件表达式>];

举个栗子：查询同时选修了C01和C04课程学生的学号

SELECT A.sno
FROM sc A, sc B
WHERE A.sno=B.sno
AND A.cno='C01'
AND B.cno='C04';

外连接

自然连接中，只有在两个表中匹配的行才在结果中显示，在外连接中可以置限制其中一个表格或者两个表格都不加限制。

语法格式：

SELECT [ALL|DISTINCT] [别名.]<选项1> [AS <显示列名>]
FROM <表名1> [别名1] LEFT|RIGHT|FULL [OUTER] JOIN <表名1> [别名2]
ON <表名1.列1>=<表名2.列2>;

（3）嵌套查询

一个SELECT FROM WHERE语句为一个查询块。将一个查询块嵌套在另一个查询WHERE子句中或HAVING子句中。

嵌套子查询：嵌套子查询的执行不溢利阿语外部嵌套。首先执行子查询，结果不显示出来，作为外层查询的条件来使用。子查询只执行一次，即可作为外层父查询的条件。

举个栗子：查询没有选秀高等数学的学生学号和姓名

SELECT sno,sname
FROM student
WHERE sno NOT IN (SELECT sno
				  FROM sc
				  WHERE cno IN (SELECT cno
				  				FROM course
			  					WHERE cname='高等数学');

相关子查询：子查询的执行依赖于父查询。
执行过程：
①子查询为父查询的每一个元组（行）执行一次，父查询将子查询引用列的值传递给子查询。
②如果子查询的任何行与其匹配，则父查询取该行存入结果中
③回到①，直到处理完外部表的每一行。

举个栗子：查询所有选修了C01课程的学生姓名

SELECT sname
FROM student
WHERE EXISTS (SELECT *
			  FROM sc
			  WHERE sno=student.sno
			  AND cno='01');