MySQL数据库：DML操纵语言

INSERT、SELECT、UPDATE、DEL

一、INSERT插入

（一）语法

INSERT [INTO] <表名> [列名] VALUES <值列表>

（二）叙述

如果表名后面没写字段名,则默认是向所有的字段添加值,另外字符串值应该用‘ ’或“ ”引号括起来。

（三）举例

向people表中添加一条记录：

insert into people(name,age) values(“zhangsan”,20);

（四）注意

1. 每次插入一行数据，不可能只插入半行或者几列数据。因此，插入的数据是否有效将按照整行的完整性的要求来检验；
2. 每个数据值的数据类型、精度和小数位数必须与相应的列匹配；
3. 如果在设计表的时候就指定了某列不允许为空,则必须插入数据；
4. 具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值。

（五）插入多行数据

INSERT INTO <表名>(列名) VALUES(<列名值>), (<列名值>), (<列名值>) ......

二、UPDATE更新

（一）语法

UPDATE <表名> SET <列名 = 更新值> [WHERE <更新条件>]

（二）叙述

where 子句是判断语句，用来设定条件，限制只更新匹配的行，如果不带where子句，则更新所有行数据。

（三）举例

将student表中的所有学生名称为"Alex"的改为"Tom"：

update student set sname="Tom" where sname="Alex";

三、DELETE 删除

（一）语法

DELETE FROM <表名> [WHERE <删除条件>]

（二）叙述

此语句删除表中的行，如果不带where子句，则删除整个表中的记录，但是表不被删除。

（三）举例

删除student表中的所有年龄小于18岁的记录：

delete from student where sage<18;

四、SELECT查询

（一）语法

SELECT [DISTINCT | DISTINCTROW | ALL]
select_expression,...
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name,...] 集函数
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name |
formula} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name]
]

（二）叙述

from子句：指定查询数据的表
where子句：查询数据的过滤条件
group by子句：对匹配where子句的查询结果进行分组
having子句：对分组后的结果进行条件限制
order by子句：对查询结果结果进行排序，后面跟desc降序或asc升序（默认）。
limit子句：对查询的显示结果限制数目
procedure子句：查询存储过程返回的结果集数据

（三）举例

• 查询全体学生的学号与姓名：

select sno,sname from student;

• 查询全体学生的详细记录：

select sno,sname,ssex,sage,sdept from student;
或
select * from student;

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

select sname,2008-sage from student;

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

select sname,'year of birth: ',2008-sage, lower(sdept) from student;

• 查询选修了课程的学生学号（去掉重复的记录）：

select distinct studentid from sc;

• 查询全体学生的学号与姓名，用中文显示列名：

select sno as ‘编号’,sname as ‘姓名’ from student;

• 给表设置别名：

select s.sno,s.sname from student as s;

• 查询年龄在20以下的学生的姓名：

select sname from student where sage<20;

• 查询全体学生的姓名、年龄，要求按照年龄降序排序：

select sname,sage from student order by sage desc;

• 查询年龄最大的前3个学生的姓名和年龄，或第4、5个学生：

select sname,sage from student order by sage desc limit 3;
或（limit 3,2）

（四）函数集

• 记数函数： count(列名) 计算元素的个数
• 求和函数： sum(列名) 对某一列的值求和，但属性必须是整型
• 计算平均值：avg(列名)对某一列的值计算平均值
• 求最大值： max(列名) 找出某一列的最大值
• 求最小值： min(列名) 找出某一列的最小值

（五）创建计算字段concat

• 举例

select concat(stuName, ' is ', stuAge, ' years old') 
from student order by stuAge desc;

（六）多表查询——连接查询

• 同时涉及多个表的查询称为连接查询。用来连接两个表的条件称为连接条件。
• 连接查询分为两种：
  内连接(INNER JOIN)
  外连接：左外联结 (LEFT JOIN)、右外联结 (RIGHT JOIN)
• 外连接与普通连接的区别：
  普通连接操作只输出满足连接条件的元组
  外连接操作以指定表为连接主体，将主体表中不足连接条件的元组一并输出
• 不支持全连接（笛卡尔乘积）

（七）子查询

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

独立子查询

1. 独立多值子查询
   子查询返回的是数据集。
   举例：当子查询返回多行时，all 所有的 any 任意一个。
   a. 谁比所有的 Lacus 工资高：

select empno,ename,salary from emp where
salary > all(select salary from emp where ename = 'Lacus');

b. 谁比任意一个 Lacus 工资高：

select empno,ename,salary from emp where
salary > any(select salary from emp where ename = 'Lacus');

2. 独立单值子查询（独立标量子查询）
   子查询返回的是单个值，而不是数据集。若返回的是数据集，则会报错。
   举例：
   查询出工资比 ZZ 高的员工的编号、姓名、工资

select empno,ename,salary from emp where salary >
(select salary from emp where ename = 'ZZ');

相关子查询

• 相关子查询最基本的执行逻辑
  将外部表中的 “每一行” 逐行代入到子查询中（理解相关子查询的关键）
• 举例：
  查询分数低于 80 分的人

Select sname from students where exists (select * from sc where
students.sno = sc.studentsid and sc.grade < 80);

求哪些员工的薪水比本部门的平均薪水低

select empno,ename,salary from emp e1 where salary <
(select avg(salary) from emp e2 where e2.deptno = e1.deptno);

求每个部门的最高工资的员工的编号和姓名

select empno,ename,salary from emp e1 where salary =
(select max(salary) from emp e2 where e2.deptno = e1.deptno);

exists 子查询

• 叙述
  exists 判断时，判断的是一个 bool 类型，不判断列值是否相同书写时，直接按照 where exists 的方式使用。子查询中 select 不需要返回一个列值，只需要返回一个 bool 值，所以不需要查询某一列。一般情况下我们用 select 1( 一个常量 ) 做查询条件。
• 举例
  求哪些人是其他人的经理

select empno,ename from emp where empno in
(select mgr from emp where mgr is not null);

select empno,ename from emp where empno = any
(select mgr from emp where mgr is not null);

select empno,ename from emp e1 where exists
(select 1 from emp e2 where e1.empno = e2.mgr);

（八）子查询和连接查询的选择

返回结果位于不同表中的列时，使用连接查询；同一张表中的列时，使用子查询。

（九）组合查询

• 需要使用到组合查询的情况：

在单个查询中从不同的表返回类似结构的数据
对单个表执行多个查询，按单个查询返回数据

• 举例：
  找出年龄为18岁或者是计算机系或者是音乐系的学生：

select stuName from student where stuAge = 18 
union select stuName from student where stuDepart in('计算机系', '音乐系');

• union 使用规则：

1. union 必须由两条或者两条以上的select 语句组成，语句之间用union 关键字分隔（若组合4 个select 语句，则需要使用3 个union 关键字）
2. union 每个查询中必须包含相同的列、表达式或者聚集函数
3. 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐式转换的类型

五、复制表

创建一个副本：
可以通过在SELECT查询中指定字段来限制出现在附表中的字段。

CREATE TABLE grades SELECT * FROM marks;

通过使用SELECT 语句创建已存在表的空副本，并且返回一个空结果集。

CREATE TABLE grades SELECT * FROM marks WHERE 0 = 1;