MySQL：DML语句，查询，函数，集合运算

7，DML语句语法

与DDL操作数据库对象不同，DML主要操作数据表里的数据，DML语句由insert into、update和delete from三个命令组成，使用DML可以完成如下任务：

• 插入新数据。
• 修改已有数据。
• 删除不需要的数据。

7.1，insert into语句

insert into用于向指定数据表中插入记录。对于标准的SQL语句而言，每次只能插入一条记录。insert into语句的语法格式如下：

insert into table_name [(column[,column...])]
values(value [, value...]);

执行插入操作时，表名后可以用括号列出所有需要插入列的列名，而values后用括号列出对应需要插入的值。如果省略了表名后面的括号里的列名列表，默认将认为所有列都插入值，则需要为每一列都指定一个值。

根据外键约束规则：外键列里的值必须是被参照列里已有的值，所以向从表中插入记录之前，通常应该先向主表中插入记录，否则从表记录的外键列只能为null。外键约束保证被参照的记录必须存在，并不保证必须有被参照记录，即外键列可为null。如果想保证每条从表记录必须存在对应的主表记录，则应使用非空、外键两个约束。

使用带子查询的插入语句可以一次插入多条记录：

insert into student_table(student_name)
select teacher_name from teacher_table;

MySQL还提供了一种扩展语法，通过这种扩展语法也可以一次插入多条记录。MySQL允许在values后使用多个括号包含多条记录：

insert into teacher_table
values(null,"ysy"),(null,"ssq");

7.2，update语句

update语句用于修改数据表记录，每次可以修改多条记录，通过使用where子句限定修改哪些记录。where子句是一个条件表达式，该条件表达式类似于Java语言的if，只有符合条件的记录才会被修改。

MySQL不允许对同一张表同时进行查询和更新，那么可以使用临时表的方式来处理。

update table_name
set column1=value1[,column2 = value2]...
[where condition];

7.3，delete from语句

delete from语句用于删除指定数据表的记录。使用delete from语句删除时不需要指定列名，因为总是整行地删除。

使用delete from语句可以一次删除多行，删除哪些行采用where子句限定，只删除满足where条件的记录。没有where子句限定将会把表里的全部记录删除。

delete from语句的语法格式如下：

delete from table_name
[where condition];

当主表记录被从表记录参照时，主表记录不能删除，只有先将从表中参照主表记录的所有记录全部删除后，才可删除主表记录。还有一种情况，定义外键约束时定义了主表记录和从表记录级联删除on delete cascade，或者使用on delete set null用于指定当主表记录被删除时，从表中参照该记录的从表记录把外键列的值设为null。

8，单表查询

8.1，select语句

select语句的功能就是查询数据。select语句也是SQL语句中功能最丰富的语句，select语句不仅可以执行单表查询，而且可以执行多表连接查询，还可以进行子查询，select语句用于从一个或多个数据表中选出特定行、特定列的交集。单表查询的select语句的语法格式：

select column1,column2...
from 数据源
[where condition];

数据源可以是表、视图等。从上面的语法格式中可以看出，select后的列用于确定那些列，where条件用于确定选择哪些行，只有满足where条件的记录才会被选择出来；如果没有where条件，则默认选出所有行。如果想选择出所有列，则可以使用星号（*）代表所有列。

如果增加where条件，则只选择出符合where条件的记录。

select * from teacher_table;

8.2，算术表达式

当使用select语句进行查询时，还可以在select语句中使用算术运算符（+、-、*、/），从而形成算术表达式。使用算术表达式的规则如下：

• 对数值型数据列、变量、常量可以使用算术运算符（+、-、*、/）创建表达式。
• 对日期型数据列、变量、常量可以使用部分算术运算符（+、-）创建表达式，两个日期之间可以进行减法运算，日期和数值之间可以进行加、减运算。
• 运算符不仅可以在列和常量之间进行运算，也可以在两列之间进行运算。

不论从哪个角度来看，数据列都很像一个变量，只是这个变量的值具有制定的范围——逐个计算表中的每条记录时，数据列的值依次变化。因此能使用变量的地方，基本都可以使用数据列。

select teacher_id + 5
from teacher_table;
select * from teacher_table
where teacher_id*3 > 4;

select后的不仅可以是数据列，也可以是表达式，还可以是变量、常量等。

select 3*4, 20 from teacher_table;    #没有意义，显示常量。

SQL语句中算术运算符的优先级与Java语言中的运算符优先级完全相同，乘法和除法的优先级高于加法和减法，同级运算的顺序是从左到右，表达式中使用括号可强行改变优先级的运算顺序。 

8.3，连接运算符 

MySQL中没有提供字符串连接运算符，即无法使用加号（+）将字符串常量、字符串变量或字符串列连接起来。MySQL使用concat函数来进行字符串连接运算：

#选择出teacher_name和'xx'字符串连接后的结果
select concat(teacher_name,'xx') from teacher_table;

对于MySQL而言，如果在算术表达式中使用null，将会导致整个算术表达式的值返回为null；如果在字符串连接运算中出现null，将会导致连接后的结果为null。

#查询结果为null
select concat(teacher_name, null) from teacher_table;

如果不希望直接直接使用列名作为列标题，则可以为数据列或表达式起一个别名，为数据列或表达式起别名时，别名紧跟数据列，中间以空格隔开，或者使用as关键字隔开。

select teacher_id + 5 as MY_ID from teacher_table;
select teacher_id + 5 My_ID from teacher_table;
select teacher_id + 5 MY_ID, teacher_name 老师名 from teacher_table;

8.4，distinct关键字

select默认会把所有符合条件的记录全部选出来，即使两行记录完全一样。如果想去除重复行，则可以使用distinct关键字从查询结果中清除重复行。

select distinct student_name, java_teacher from student_table;

使用distinct去除重复行时，distinct紧跟select关键字。它的作用是去除后面字段组合的重复值，而不管对应记录在数据库里是否重复。例如，(1,'a','b')和(2,'a','b')两条记录在数据库里是不重复的，但如果仅选择后面两列，则distinct会认为两条记录重复。

8.5，比较运算符

SQL中的运算符不仅可以比较数值之间的大小，也可以比较字符串、日期之间的大小。判断两个值是否相等的运算符是（=），判断不相等的运算符是（<>），赋值运算的运算符是（:=）。此外，SQL还支持特殊的比较运算符：

运算符	意义
expr1 between expr2 and expr3	要求expr1 >=expr2并且expr2 <=expr3
expr1 in (expr2, expr3, expr4, ...)	要求expr1等于后面括号里任意一个表达式的值
like	字符串匹配，like后的字符串支持通配符 下划线（_）代表任意一个字符 百分号（%）代表任意多个字符
is null	要求指定值等于null 判断是否为空不能用=null判断，因为SQL中null=null返回null

#选择出student_id大于等于2，且小于等于4的所有记录。
select * from student_table where student_id between 2 and 4;
#选择出java_teacher小于等于2，student_id大于等于2的所有记录。
select * from 2 between java_teacher and student_id;

#选出student_id为2或4的所有记录。
select * from student_table where student_id in (2,4);
#选择出java_teacher小于等于2，student_id大于等于2的所有记录。
select * from student_table where 2 in(student_id, java_teacher);

#查询所有学生名中名字以“张”开头的学生。
select * from student_table where student_name like '张%';
#查询出名字为两个字符的所有学生。
select * from student_table where student_name like '__';
#查询所有名字以下划线开头的学生。
select * from student_table where student_name like '\_%';

#选择出student_table表中student_name为null的所有记录。
select * from student_table where student_name is null;
#选择出student_table表中student_name不以下划线开头的所有记录。
select * from student_table where not student_name like '\_%';

8.6，limit

limit：限制返回结果行数，主要用于查询之后要显示返回的前几条或者中间某几条数据。其格式为：

• limit 0,100：从起始位置开始往后获取100条记录，简写为 limit 100。
• limit 10,6：从起始位置后第10条开始，往后获取6条。

select * from student limit 10;
select * from student limit 10,5;

可直接使用limit来进行分页操作，但这个关键字在数据量和偏移量比较大时，效率很低。所以，对limit优化：要么限制分页的数量，要么降低偏移量的大小。一般的解决方法是关联查询或查询优化法，可以先查询出主键，然后利用主键进行关联查询。

【问题】MySQL中的 limit 0,100 与 limit 10000,100 的执行效率一样吗？

【答案】不一样，后者的效率更低。limit 10000,100 实际上MySQL扫描了10100行记录，然后只返回100条记录，将前面的记录抛弃掉。

MySQL的 limit m,n 工作原理就是先读取前m条记录，然后抛弃前m条，再读取n条想要的记录，所以m越大，性能越差。优化思路是，在索引上完成排序分页的操作，最后根据主键关联回原表查询所需的其他列内容。

优化前：select * from memeber order by last_active limit 50,5
优化后：select * from member inner join (select member_id from member order by last_active limit 50,5) using(member_id)

区别在于：优化前的SQL需要更多的I/O浪费，因为先读索引，再读数据，然后抛弃无用的行，而优化后的SQL只读索引就可以了，然后通过member_id读取需要的列。

9，数据库函数

每个数据库都会在标准的SQL基础上扩展一些函数，这些函数用于进行数据处理或复杂计算，它们通过对一组数据进行计算，得到最终的需要的输出结果。函数一般都会有一个或多个输入，这些输入被称为函数的参数，函数内部会对这些参数进行判断和计算，最终只有一个值作为返回值。

根据函数对多行数据的处理方式，函数被分单行函数和多行函数：

• 单行函数对每行输入值单独计算，每行得到一个计算结果返回给用户。
• 多行函数对多行输入值整体计算，最后只会得到一个结果。​​​​

虽然MySQL函数的使用简单，但通常不推荐在Java程序中使用特定数据库的函数，因为这导致程序代码与特定数据库耦合；如果需要把程序移植到其他数据库上时，可能需要打开源程序，重新修改SQL语句。

9.1，单行函数

每个数据库都包含了大量的单行函数，这些函数的用法也存在一些差异，但有一点相同的——每个数据库都会为一些常用的计算功能提供相应的函数，这些函数的函数名可能不同，用法可能有差异，但所有的数据库提供的函数库所能完成的功能大致相似。不同数据库中的单行函数差别非常大，MySQL中的单行函数特性：

• 单行函数的参数可以是变量、常量或数据列。单行函数可以接收多个参数，但只返回一个值。
• 单行函数会对每行单独起作用，每行返回一个结果。
• 使用单行函数可以改变参数的数据类型。单行函数支持嵌套使用，即内层函数的返回值是外层函数的参数。

 MySQL提供了处理null的函数： 

• ifnull(expr1, expr2)：如果expr1为null，则返回expr2，否则返回expr1。
• nullif(expr1, expr2)：如果expr1和expr2相等，则返回null，否则返回expr1。
• if(expr1, expr2, expr3)：类似于?:三目运算符，如果expr1为true，不等于0，且不等于null，则返回expr2，否则返回expr3。
• isnull(expr1)：判断expr1是否为null，如果为null则返回true，否则返回false。

#如果student_name列为null，则返回'没有名字'
select ifnull(student_name, '没有名字') from student_table;
#如果student_name列等于'张三'，则返回null
select nullif(student_name,'张三') from student_table;
#如果student_name列为null，则返回'没有名字'，否则返回'有名字'
select if(isnull(student_name),'没有名字','有名字') from student_table;

MySQL提供了case函数，该函数是一个流程控制函数。case函数有两种语法，推荐使用第二种，因为更加灵活。

case value
when compare_value1 then result1
when compare_value2 then result2
...
else result
end
--------------------------------
select student_name, case java_teacher
when 1 then '燕双嘤'
when 2 then '杜马'
else '陈恭鹏'
end
from student_table;

case
when condition1 then result1
when condition2 then result2
...
else result
end
--------------------------------
select student_name, case
when student_id <= 3 then '初级班'
when student_id <= 6 then '中级班'
else '高级班'
end
from student_table;

9.2，多行函数

多行函数也称为聚集函数、分组函数，主要用于完成一些统计功能，在大部分数据库中基本相同。多行函数将一组记录作为整体计算，每组记录返回一个结果，而不是每条记录返回一个结果。常用函数：

• avg([distinct|all]expr)：计算多行expr的平均值，其中expr可以是变量、常量或数据列，但其数据类型必须是数值型。还可以在变量、列前使用distinct或all关键字，如果使用distinct，则表明不计算重复值；all用和不用的效果完全一样，表明需要计算重复值。
• count({*|[distinct|all]expr})：计算多行expr的总条数，其中expr可以是变量、常量或数据列，其数据类型可以是任意类型；用星号（*）表示统计该表内的记录行数；distinct表示不计算重复值。null不会被计算在内。

COUNT(*)：计算选定的表中所有行的数量，包括包含NULL值的行。由于它不考虑特定列的值，所以通常比较快速。
COUNT(1)：计算选定的表中所有行的数量，但它并不考虑行中的实际数据。相反，它只检查行是否存在（是否非NULL）。此方法通常与COUNT(*)一样快速，因为它只关注行的存在，而不关心列的内容。

• max(expr)：计算多行expr的最大值，其中expr可以是变量、常量或数据列，其数据类型可以是任意类型。
• min(expr)：计算多行expr的最小值，其中expr可以是变量、常量或数据列，其数据类型可以是任意类型。
• sum([distinct|all]expr)：计算多行expr的总和，其中expr可以是变量、常量或数据列，但其数据类型必须是数值型；distinct表示不计算重复值。

#计算student_table表中的记录条数
select count(*) from student_table;
#计算java_teacher列总共有多少个值
select count(distinct java_teacher) from student_table;
#统计所有student_id的总和
select sum(student_id) from student_table;
#统计结果是20 * 记录的行数
select sum(20) from student_table;
#选出student_table表中student_id最大的值
select max(student_id) from student_table;
#选出student_table表中student_id最小的值
select min(student_id) from student_table;

分组函数（group by）：在默认情况下，组函数会把所有记录当成一组，为了对记录进行显示分组，可以在select语句后使用group by子句，group by子句后通常跟一个或多个列名，表明查询结果根据一列或多列进行分组——当一列或多列组合的值完全相同时，系统会把这些记录当成一组。

#将java_teacher列值相同的记录当成一组
select count(*) from student_table group by java_teacher;
#如果对多列值进行分组，要求多列的值完全相同才会被当成一组
select count(*) from student_table group by java_teacher, student_name;

如果需要对分组进行过滤，则应该使用having子句，having子句后面也是一个条件表达式，只有满足该条件表达式的分组才会被选出来。having子句和where子句非常容易混淆，它们都有过滤功能，但它们都有如下区别：

• 不能在where子句中过滤组，where子句仅用于过滤行。过滤组必须使用where子句。
• 不能在where子句中使用组函数，having子句才可以使用组函数。

select * from student_table group by java_teacher having count(*) > 2;

10，多表连接查询

很多时候，需要选择的数据并不是来自一个表，而是来自多个数据表，这就需要使用多表连接查询。多表连接查询有两种规范，较早的SQL92规范支持：等值连接、非等值连接、外连接、广义笛卡尔积。SQL99规范提供了可读性更好的多表连接语法，并提供了更多类似的连接查询。SQL99支持：交叉连接、自然连接、使用using子句的连接、使用on子句的连接、全外连接或左右外连接。

10.1，SQL92 的连接查询

SQL92的多表连接语法比较简洁，这种语法把多个数据表都放在from之后，多个表之间以逗号隔开；连接条件放在where之后，与查询条件之间用and逻辑运算符连接。如果连接条件要求两列值相等，则称为等值连接，否则称为非等值连接；如果没有任何连接条件，则称为广义笛卡尔积。

select column1, column2 ...
from table1, table2 ...
[where join_condition]

多表连接查询中可能出现两个或多个数据列具有相同的列名，则需要在这些同名列之间使用表名前缀或表别名前缀作为限制，避免系统混淆。

如果求等值连接，则where子句后要求两个字段相等的连接条件。

select s.*, teacher_name
from student_table s, teacher_table t
where s.java_teacher = t.teacher_id;

如果求广义笛卡儿积，则where子句后没有任何连接条件。

select s.*, teacher_name from student_table s,teacher_table t;

如果求广义笛卡儿积，则where子句要求指定条件。

select s.*, teacher_name from student_table s,teacher_table t
where s.java_teacher > t.teacher_id;

MySQL不支持SQL92中的左外连接、右外连接。

自连接只是连接的一种用法，并不是一种连接类型，不管是SQL 92还是SQL 99都可以使用自连接查询。自连接本质就是把一个表当成两个表使用。

select emp.emp_id, emp.emp_name, mgr.emp_name from emp_table emp, emp_table mgr
where emp.manager_id = mgr.emp_id;

10.2，SQL99 的连接查询

SQL99 的连接查询与SQL92 的连接查询原理基本相似，不同的是SQL99 连接查询的可读性更强——查询的多个数据表显式使用xxx join连接，而不是直接依次排列在from之后，from侯只需要放一个数据表；连接条件不再放在where之后，而是提供了专门的连接条件子句。

交叉连接：交叉连接效果就是SQL92 中的广义笛卡尔积，所以交叉连接无须任何连接条件。

select s.*, teacher_name from student_table s
cross join teacher_table t;

自然连接：自然连接表面上看起来也无须指定连接条件，但自然连接是有连接条件的，自然连接会以两个表中的同名列作为连接条件；如果两个表中没有通名列，则自然连接与交叉连接效果完全一样——因为没有连接条件。

select s.*, teacher_name from student_table s
natural join teacher_table t;

using子句连接：using子句可以指定一列或多列，用于显式指定两个表中的同名列作为连接条件。假设两个表中有超过一列的同名列，如果使用natural join，则会把所有的同名列当成连接条件；使用using子句，就可显式指定使用哪些同名列作为连接条件，要求两表必须有同名列。

select s.*, teacher_name from student_table s
join teacher_table t
using(teacher_id);

on子句连接：这是最常用的连接方式，SQL 99语法的连接条件放在on子句中指定，而且每个on子句只指定一个连接条件。如果要进行N表连接，则需要有N-1个join...on对。

select s.*, teacher_name from student_table s
join teacher_table t
on s.java_teacher = t.teacher_id;

使用on子句的连接完全可以代替SQL 92中的等值连接、非等值连接，因为on子句的连接条件除等值连接之外，也可以是非等值连接。

select s.*, teacher_name from student_table s
join teacher_table t
on s.java_teacher > t.teacher_id;

左、右、全外连接：这三种外连接分别使用left[outer]join、right[outer]join和full[outer]join，这三种外连接的连接条件一样通过on子句来指定，既可以是等值连接，也可以是非等值连接。

• 左连接指的是将左边的表（A表）中的所有数据和右边的表（B表）中匹配的数据联合起来，未匹配到的数据为NULL值。
• 右连接则是将右边的表（B表）中的所有数据和左边的表（A表）中匹配的数据联合起来，未匹配到的数据为NULL值。
• 全外连接（FULL OUTER JOIN）是左连接和右连接的结合，它会将左表和右表中的所有数据都联合起来，未匹配到的数据为NULL值。（MySQL不支持，通过union代替）

select s.*, teacher_name from student_table s
right join teacher_table t
on s.java_teahcer < t.teacher_id;

select s.*, teacher_name from student_table s
left join teacher_table t
on s.java_teahcer > t.teacher_id;

select s.*, teacher_name from student_table s
full join teacher_table t
on s.java_teahcer = t.teacher_id;

11，子查询

子查询就是指在查询语句中嵌套另一个查询，子查询可以支持多层嵌套。对于一个普通的查询语句而言，子查询可以出现在两个位置：

• 出现在from语句后当成数据表，这种用法也被称为行内视图，因为该子查询的实质就是一个临时视图。
• 出现在where条件后作为过滤条件的值。

使用子查询时要注意：

• 子查询要用括号括起来。
• 把子查询当成数据表时（出现在from之后），可以为该子查询其别名，尤其是作为前缀来限定数据列时，必须给子查询起别名。
• 把子查询当成过滤条件时，将子查询放在比较运算符的右边，这样可以增强查询的可读性。
• 把子查询当成过滤条件时，单行子查询使用单行运算符，多行子查询使用多行运算符。

11.1，子查询的用途

对于把子查询当成数据表是完全 把子查询当做视图来用， 只是把之前的表名变成子查询，其他部分与普通查询没有任何区别。可以将下面的SQL语句理解成在执行查询时创建了一个临时视图，该视图名为t，所以这种临时创建的视图也被称为行内视图。

select * from (select * from student_table) t
where t.java_teacher > 1;

还有另一种用法就是把子查询当成where条件中的值，如果子查询返回单行、单列值，则被当成一个标量值使用，也就是可以使用单行记录比较运算符。

select * from student_table
where java_teacher > 
(select teacher_id from teacher_table where teacher_name='ysy');

11.2，in、any与all

如果子查询返回多个值，则需要使用in、any和all等关键字，in可以单独使用。

select * from student_table where student_id in
(select teacher_id from teacher_table);

any和all可以与>，>=，<，<=，<>，=等运算符结合使用，与any结合使用分别表示大于、大于等于、小于、小于等于、不等于、等于其中任意一个值；与all结合使用分别表示大于、大于等于、小于、小于等于、不等于、等于全部值。=any的作用与in的作用相同。

select * from student_table where student_id = any
(select teacher_id from teacher_table);

<any只要小于值的最大值即可，>any只要大于值列表中的最小值即可。<all只要小于值的列表中的最小值，>all要求大于值列表中的最大值。

select * from student_table where student_id > all
(select teacher_id from teacher_table);

12，集合运算

select语句查询的结果是一个包含多条数据的结果集，类似于数学里的集合，可以进行：交、并和差运算，select查询得到的结果集也可能需要进行这三种运算。为了对两个结果集进行集合运算，这两个结果集必须满足：

• 两个结果集所包含的数据列的数量必须相等。
• 两个结果集所包含的数据列的数据类型也必须一一对应。

12.1，union运算（并集）

union运算的语法格式如下：

select 语句 union select 语句

查询出所有教师的信息和学生信息

select * from teacher_table
union
select student_id, student_name from student_table;

12.2，minus运算（差集）

minus运算的语法格式如下：

select 语句 minus select 语句

从所有学生的记录中“减去”与老师记录的ID相同、姓名相同的记录，则可进行minus运算：

select student_id, student_name from student_table
minus
select teacher_id, teacher_name from teacher_table;

虽然上面的语法很简单，但是MySQL不支持minus运算符，因此只能借助于子查询来“曲线”实现上面的minus运算。

select student_id, student_name from student_table
where(student_id, student_name)
not in
(select teacher_id, teacher_name from teacher_table);

12.3，intersect运算（交集）

intersect运算的语法格式如下：

select 语句 intersect select 语句

找出学生记录中与老师记录中的ID相同、姓名相同的记录，则可进行intersect运算：

select student_id, student_name from student_table
intersect
select teacher_id, teacher_name from teacher_table;

但是MySQL不支持差集运算，但可以通过多表连接来实现：

select student_id, student_name from student_table
join teacher_table
on(student_id = teacher_id and student_name = teacher_name);