MySql学习笔记（4）--SQL(DML)语句语法

与DDL操作数据库对象不同，DML主要操作数据表里的数据，使用DML可以完成如下三个任务
--->插入新数据
--->修改已有数据
--->删除不需要的数据
DML语句由insert into /update 和deletefrom三个命令组成
insert into用于向指定数据表中插入记录。对于标准SQL语句而言，每次只能插入一条记录。

insert into命令的语法格式如下:

[sql] view plain copy

1. insert into tabale_name(表名) [(column[,column...])] values (value[,value..]);  
2. insert into teacher (techer_name,techer_age) values ("xiaoming",10);  

同时插入多个值

[sql] view plain copy

1. insert into teacher values ("summer",10),("Sun",20);  

update语句用于修改数据，可以一次修改多条。通过使用Where限定修改那些数据

[sql] view plain copy

1. update teacher set teacher set teacher_name ='小六';  

通过Where条件修改

[sql] view plain copy

1. update teacher set teacher_name = '大嘴' where teacher_id > 1;  

delete from 语句
delete from 语句用于删除指定数据表的数据，使用delete from 删除时不需要指定列名，因为删除总是正行的删除。
delete from 删除可以一次删除多行，删除那些行采用where字句限定，只删除满足where条件的记录。没有where字句将会把表里的记录全部删除
删除全部

[sql] view plain copy

1. delete from teacher  

使用where条件来限定之删除指定的数据

[sql] view plain copy

1. delete from teacher where teacher_id > 2;  

distinct 关键字除去重复行

[sql] view plain copy

1. select distinct teacher_name from teacher;  

between关键字 between val1 and val2   (大于等于val1,且小于等于val2)

[sql] view plain copy

1. select * from teacher where teacher_id between  2 and 4;  

in关键字 ,使用in比较符时，必须在in后的括号里列出一个或多个值，它要求指定列必须与in括号里任意一个值相等

[sql] view plain copy

1. select * from teacher where teacher_id in (2,4);  

like关键字，主要用于模糊查询，例如我们需要查询名字以“孙”开头的所有记录，这就需要用到模糊查询。SQL语句中可以使用两个通配符：下划线(_)和百分号(%)，其中下划线可以代表一个任意的字符，百分号可以代表任意多个字符。
eg:查询所有老师名字中以“孙”开头的

[sql] view plain copy

1. select * from teacher where teacher_name like '孙%'；  

eg:查询出名字为两个字符的所有老师

[sql] view plain copy

1. select * from teacher where teacher_name like '__';  

在某些特殊的情况下，我们需要查询的条件里需要使用下划线或百分号，不希望SQL把下划线和百分号当成同配符使用，这需要使用转义字符，MySQL使用反斜线(\)作为转义字符，
eg:查处所以名字以下划线开头的老师

[sql] view plain copy

1. select * from teacher where teacher_name like '\_%';  

备注:在标准的SQL语句中并没有提供反斜线(\)的转义字符，而是使用escape关键字显示进行转义‘

[sql] view plain copy

1. select * from teacher where teacher_name like '\_%' escape '\';  

is null关键字，用以判断某些值是否为空，判断是否为空不要用=null来判断，因为SQL中null=null返回null。
eg:查询出teacher_name为空的记录

[sql] view plain copy

1. select * from teacher where teacher_name is null;  

如果where字句后有多个条件需要组合，SQL提供了and和or逻辑运算符来组合2个条件，并提供了not来对逻辑表达时求否。
eg:查询出老师名字为两个字符并且teacher_id大于3的所以记录

[sql] view plain copy

1. select * from teacher where teacher_name like '__' and teacher_id > 3;  

eg:查询出所有老师名字不以下划线开头的记录

[sql] view plain copy

1. select * from teacher where teacher_name not like '\_%';  

order by 子句
执行查询后的结果，默认按照插入的顺序排序。如果需要查询结果按照某列值的大小进行排序，则可以使用order by字句
降序排列 desc 生序排列 asc（默认排列方式）
eg:按照teacher_id 列的升序排列

[sql] view plain copy

1. select * from teacher order by teacher_id;  

如果需要按多列排序，每列的asc,desc必须单独设定。如果指定了多个排序列，则第一个排序列是首要排序列，只有当第一个排序列存在多个相同的值时，第二个排序列才会起作用。
eg:先按teacher_name的降序排列，当teacher_name相同时按student_name升序排列

[sql] view plain copy

1. select * from teacher order by teacher_name desc,student_name asc;   

group by 字句

[sql] view plain copy

1. select * from teacher group by teacher_name;  

如果需要对分组进行过滤，则应该时having子句，having子u句后面也是一个条件表达式，只有满足该条件表达式的分组才会被选出。having子句和where子句非常容易混淆，他们都有过滤功能，但它们有如下区别
--->不能在where字句中过滤组，where子句仅用于过滤行。过滤组必须使用having子句
--->不能在where子句中使用组函数，having子句才可使用组函数
[sql] view plain copy

1. select * from teacher group by teacher_name having count(*) > 2;  

多表连接查询和子查询
using 子句的连接
using子句可以指定一列或多列，用于显示指定两个表中同名列作为连接条件。假设两个表中有超过一列的同名列，如果使用natural join则会把所有同名列当成连接条件，使用using子句就可以显示的指定使用那些同名列作为连接条件。

[sql] view plain copy

1. select s.* ,teacher_name   
2. --SQL99多表连接查询的from后只有一个表明  
3. from student s  
4. --join连接另一个表  
5. join teacher using(teacher_id);  

on子句连接
这是最常用的连接方式，SQL99语法的连接条件房子on子句中指定，而且每个on子句之指定一个连接条件。这意味着如果需要进行N表的连接，则需要有N-1个join...on对。

[sql] view plain copy

1. select s.* ,teacher_name  
2. --SQL99多表连接查询的from后只有一个表明  
3. from student s  
4. --join 连接另一张表  
5. join teacher t  
6. --使用on来指定连接条件  
7. on s.java_teacher = t.teacher_id;  

左/右/外连接，3种连接分别使用left[outer] join /right[outer] join 和full[outer] join，这三种外连接的连接条件一样通过on子句来指定。
eg:全外连接

[sql] view plain copy

1. select s.* ,teacher_name  
2. --SQL99多表连接查询的from后只有一个表明  
3. from student s  
4. --full join 全外连接另一张表  
5. full join teacher t  
6. --使用on来指定连接条件  
7. on s.java_teacher = t.teacher_name;  

右外连接，连接条件时非等值连接

[sql] view plain copy

1. select s.* ,teacher_name  
2. from student s  
3. right join teacher  
4. on s.java_teacher < t.teacher_id;  

左外连接，

[sql] view plain copy

1. select s.* ,teacher_name  
2. from studengt s  
3. left join teacher t  
4. on s.java_teacher > t.teacher_id;  

子查询
子查询就是指在查询语句中嵌套另一个查询，子查询支持多层查询。对于一个普通的子查询而言，子查询可以出现在两个位置
-->出现在from语句后当成数据表，这种用法也被称为行内视图，因为该子查询的实质就是一个临时视图
-->出现在where条件后作为过滤条件的值
使用子查询有如下几个注意点
-->子查询要用括号括起来
-->子查询当成数据表时(出现在from之后，)可以为该子查询起别名，尤其是要作为前缀来限定查询列时，必须给与子查询起别名
-->子查询当过滤条件时，将子查询放在比较运算符的右边，这样可以增强查询的可读性
-->子查询当过滤条件时，单行子查询使用单行运算符，多行子查询使用多行运算符
eg:

[sql] view plain copy

1. select *   
2. --把子查询当数据表  
3. from (select * from student) t  
4. where t.java_teacher > 1;  

把子查询当成where条件中的值，如果子查询返回单行/单列值。则被当成一个标量使用
eg:

[sql] view plain copy

1. select *   
2. from student   
3. where java_teacher >   
4. --返回单行/单列的子查询可以当成标量使用  
5. (select teacher_id from teacher where teacher_name ='小六');  

如果子查询返回多个值，则需要使用in/any和all等关键字，in可以单独使用，与前面介绍比较运算符时所介绍的in完全一样，此时可以把子查询返回的多个值当成一个值列表

[sql] view plain copy

1. select * from student where student_id in (select teahcer_id from teacher);  

eg:< any只要小于值列表中的最大值即可，> any只要大于值列表中的最小值即可

[sql] view plain copy

1. select * from student where student_id any (select teahcer_id from teacher);  

eg:< all要求小于值列表中的最小值，>all要求大于值列表中的最大值

[sql] view plain copy

1. select * from student where student_id > all (select teacher_id from teacher);  

还有一种子查询可以返回多行多列，此时where子句中应该有对应的数据列，并使用圆括号将多个数据列组合起来

[sql] view plain copy

1. select * from student where(student_id,student_name) = any(select teacher_id,teacher_name from teacher);