MYSQL数据操纵功能（DML）（单表，连接，嵌套，集合查询以及增删改）

为了便于理解，我们将以s（学生信息）,c（课程信息）,sc（学生选课信息）这三张表为例

sno	sname	sex	age	sdept
091502	王晓梅	女	20	电信
091503	张小飞	男	23	机械工程
091504	孙志鹏	男	21	计算机
091505	徐颖	女	18	外国语
091506	钱一萌	男	20	电信
091510	李波	男	22	经管
091508	张三	女	28	计算机

cno	cname	credit	pro
180101	C语言程序设计	56
180102	数据结构	48	180101
180104	数据库原理	64	180103
180105	计算机网络	48
180106	网络安全	NULL	NULL

sno	cno	grade
091501	180101	86
091501	180102	88
091503	180101	79
091503	180102	88
091503	180104	82
091504	180101	80
091504	180102	69
091510	180104	NULL

select

单表查询

查询仅涉及一个表，是一种最简单的查询操作

• 选择表中的若干列（投影）
• 选择表中的若干元组（选择）
• 对查询结果排序
• 使用聚集函数
• 对查询结果分组

语句格式： select [all|distinct] <目标列表达式> … from <表名或者视图名>…[

• where<条件表达式>
• group by<列名>
• having<条件表达式>
• order by<列名> [asc（升序）|desc（降序）]（排序）

]
我们先举几个例子，简单的体验一下查询操作

//查询s,c,sc表中所有学生的信息
select * from s;
select * from c;
select * from sc;

//查询s表中某几列的信息
select sno,sname from s;

//查询学生的姓名和出生年份(（now()）获取当前系统时间然后减去年龄就是出生年份)
select sname,year(now())-age from s;

//查询年龄小于18的学生信息
select * from s where age<20;

//查询学生选课信息，并按成绩的降序进行排列
select * from sc order by grade desc;

//查询选修180101课程的每位同学的学号和成绩信息，并且结果按成绩由低到高排列输出。
select * from sc where cno='180101' group by sno order by grade asc;

select 后面的表达式可以为（比较常用的）：

• 算术表达式
• 字符串常量
• 函数
• 列别名等

//修改列的名称 as
select sno,sname as name from s;
//消除取值重复的行 distinct
select distinct sno,cno from sc;
//【注意】：注意 distinct 短语的作用范围是所有目标列

带有where子句的查询

查询条件	谓词
比较	>,>=,<,<=,=,<>
算术类型	+，-，*，/
确定范围	between and,not between and
确定集合	in, not in
字符匹配	like,not like
空值	is null,is not null
多重条件	or,and

例如：

//例如查询年龄在23岁以下的学生信息
select * from s where age<23;

//查询年龄在18到20岁之间的学生信息
select * from s where age between 18 and 20;

//查询学号为091501和091503的选课信息
select * from sc where sno in('091501','091503');

like 可以用来匹配字符串

• % (百分号) 代表任意长度（长度可以为0）的字符串
• _ (下横线) 代表任意单个字符

例如 ：

• _a 是以a结尾长度为二的任意字符串
• a_ 是以a开头长度为二的任意字符串
• a_b 是以a为开头,并且以b为结尾的长度为3的任意字符串

//例如：
//查询姓王的学生信息
select * from s where sname like '王%';
//查询第二个字为'小'的学生信息
select * from s where sname like('_小%');

【注:】当用户要查询的字符串本身就含有 % 或 _ 时，要使用escape（转义字符）短语对通配符进行转义

//假如c表中有一个课程的名字为c_sql
select * from c where cname like('c\_sql') escape '/';
//escape后面是字符‘/’而不是‘\’

is null

//查询没有成绩的学生学号和课号
select sno,cno from sc where grade is null;

[注:] is null 不可以用 = null 代替

or 和 and

and的优先级要高于or，可以用括号改变优先级

//查询年龄小于20并且是计算机系的学生信息
select * from s where sdept='计算机'and age<18;

order by

可以按一个或多个属性列排序。asc:升序 desc:降序

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

//查询学生的选课信息，结果按成绩由升序，成绩相同的按照学号降序排列。
select * from sc order by grade asc,cno desc;

limit

//查询成绩前三的学生的学号(先将成绩降序排列)然后取前三个记录即可
select sno from sc order by grade desc limit 3;
//top 在mysql中不支持，可以用limit

聚集函数

常用的聚集函数：

• count:计数
• sum:计算总和
• avg:计算平均值
• max:求最大值
• min:求最小值

//查询学生的总人数
select count(*) from s;
//或者
select count(sno) from s;

//计算课程号为180101的平均成绩
select cno,avg(grade) from sc where cno = '180101';

//查询课程号为180101的最高成绩
select cno,max(grade) from sc where cno = '180101';

group by (分组)

• group by子句的作用对象是查询的中间结果表。
• 分组方法： 按指定的一列或多列值分组，值相等的为一组
• 注意： 使用group by子句后，select子句的列名列表中只能出现分组属性和集函数

//查询选修了两门以上课程的学生学号
select sno,count(*) from sc group by sno having count(*)>2;

having短语与where子句的区别：作用对象不同。

• where子句作用于基表或视图，从中选择满足条件的元组。
• having短语作用于组，从中选择满足条件的组。

//查询至少选修了两门课程并且至少有两门成绩大于80的学生学号
select sno from sc where grade >80  group by sno having count(*)>=2;

连接查询

• 同时涉及多个表的查询称为连接查询
• 用来连接两个表的条件称为连接条件或连接谓词 。
• 主要类型：
  1. 自然连接
  2. 自身连接
  3. 外连接
  4. 复合条件连接查询

自然连接

//查询学生姓名，学号。及其选课情况
select sname,s.sno,sc.cno from s,sc where s.sno = sc.sno;

连接运算符为 = 的连接操作,叫等值连接，去掉重复的属性列，叫自然连接。

注意： 任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。

连接运算符不是 ‘=’ 的的连接操作叫做非等值连接运算。

自身连接

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

//查询每一门课的间接先修课（即先修课的先修课）
select t1.cno,t2.pro from c as t1, c as t2 where t1.cno = t2.cno;

外连接

• 普通连接操作只输出满足连接条件的元组
• 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
• 左外连接：…left (outer) join …on …;
• 右外连接：…right (outer) join…on…;

//查询每一个学生的选课情况
select s.sno,s.sname,s.sex,s.sdept,sc.cno,sc.grade from s left outer join sc on s.sno = sc.sno;

复合条件连接查询

• where 子句包含多个连接条件

//举个例子体验一下
//查询每个学生的学号，姓名，所选课程号，课程名及其成绩
select sc.sno,sname,sc.cno,cname,grade from s,c,sc where s.sno = sc.sno and c.cno = sc.cno;

//课程号为180101且成绩大于80的学号
select sno from sc where cno = '180101' and grade>80;

集合查询

• 标准sql直接支持并操作（union） ,商业数据库支持**交(intersect)，并，差(except)**操作。
• 格式：<查询块> union
• 参加union操作的各结果表的列数必须相同；对应项的数据类型也必须相同.

//查询计算机系的，或者年龄大于20的学生信息
(select * from s where sdept = '计算机') union (select * from s where age>20);
//也可以用or代替
select * from s where sdept = '计算机' or age>20;

嵌套查询

• 查询块：一个select-from -where语句称为一个查询块。
• 将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询。
• 分类:

  1. 不相关子查询： 子查询的查询条件不依赖于父查询.
     由里向外逐层处理,即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

  2. 相关子查询： 子查询的查询条件依赖于父查询.
     首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若where子句返回值真，则取此元 组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止

• 带有 谓词 in, any, all, exists 或比较运算符的子查询。

补充：(比较运算符 ‘<>’相当于‘！=’)

//查询和王英同一个系的学生
select * from s where sdept in (select sdept from s where sname = '王英');
//或者
select * from s where sdept = (select sdept from s where sname = '王英');

//另外.父查询和子查询可以定义其他的名字

//any(任意),查询比计算机系任意一个学生小的学生信息
select * from s where age < any (select age from s where sdept = '计算机') and sdept <> '计算机';//
//al(全部，注意与any的区别)查询比计算机系全部学生年龄都小的学生信息
select * from s where age < all(select age from s where sdept = '计算机') and sdept <> '计算机'; 
//适当的运用聚集函数可以提高查询效率，在子查询中我们可以用max函数先求出计算机系学生年龄最大的一个，
//然后把结果传给父查询，这样可以减少查询的次数。

带有exists谓词的子查询(相关子查询)

• 带有exists谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。内层查询结果非空则返回真值，为空则返回假值。
• 一些带 exists 或 not exists 谓词的子查询不能被其他形式的子查询等价替换
• 所有带 in 谓词、比较运算符、any 和 all 谓词的子查询都能用带EXISTS谓词的子查询等价替换。
• 可以用exists谓词实现 存在量词和全称量词
• exists引出的子查询，其目标列表达式通常都用 * ，因为带exists的子查询只返回真值或假值，给出列名无实际意义。

//查询选修了180101课程的学生信息
select * from s where exists (select * from sc where sno = s.sno and cno = '180101');

//查询选修了全部课程的同学名字和所在系。
//第一步，相当于是用全部的课程集合减去xx同学所选修的全部课程集合，如果结果为空，说明该学生选修了全部课程
(select cno from c) escape (select cno from s where sno = 'xx');
//第二步，如果里面传出的是空值，说明该学生选修了c表中的所有课程，然后用not exists  把结果取反.
//（另外需要注意这里变成了相关子查询）
select sname,sdept from s where not exists 
  ->((select cno from c) escape (select cno from s where s.sno = sc.sno));
//第三步，因为没有escape 可以用 not exists 代替(集合A-集合B，就是把A里面和B中相同的部分去掉，)
select sname,sdept from s where not exists 
  ->(select cno from c where not exists 
  ->(select distinct cno from sc where s.sno = sc.sno and c.cno = sc.cno));

insert

① 插入单个元组

将新元组插入指定表中
**语句格式：**INSERT INTO <表名> [(<属性列>…] VALUES (<常量> … )

//在sc表中插入新的学生选课信息(成绩为空)
insert into sc(sno,cno) values('091506','180101');
//在s表中插入一条新的学生数据
insert into s values('091507','李四','男',20,'计算机');

② 插入子查询结果

将子查询结果插入指定表中
语句格式: INSERT INTO <表名> [(<属性列>… ] 子查询；

//新建一张学生所在系部的表sdept,然后我们将年龄小于23的插入系部表中
//假定我们已经建立好sdept表,一共有系名（sdname）和年龄（age）两个属性
insert into sdept (sdname,age) select sdept,age from s where age<23;

【注】：DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则。

update

修改指定表中满足where的子句条件的元组
**语句格式：**update <表名> set <列名> =[表达式] ,… where <条件>;

• 修改某一个元素的值

//例如我们将学号为091502的年龄改为18
update s set age=18 where sno='091502';

• 修改多个元组的值

update s set age=18,sex='男' where sno='091502';

• 含有子查询的修改语句

//将计算机学院的所有学生成绩加1
update sc set grade = grade+1 where cno in(select cno from c where c.cno=sc.cno);

【注】：MySql运行在safe-updates模式下的时候，该模式会导致非主键条件下无法执行update或者delete命令，执行以下命令解除该模式 set sql_safe_updates = 0;

delete

删除指定表中满足where子句条件的元组
**语句格式：**delete from <表名> where <.条件>;

• 删除某一个元组
• 删除多个元组
• 含有子查询的删除语句

新手上路，如有错误和不足欢迎指正，创作不易，请多多支持 [微笑]