MySQL_5.DML语句基本语法

DML语言介绍
其语句包括动词 insert、update、delete。它们分别用于添加、修改 和删除，也称为 动作查询语言
官方文档网址
https://dev.mysql.com/doc/refman/5.7/en/sql-data-manipulation-statements.html

1.insert into 语句语法
语法一:

INSERT INTO table_name ( field1, field2,...fieldN ) VALUES( value1, value2,...valueN );

语法二:

INSERT INTO table_name ( field1, field2,...fieldN ) VALUES
( value1, value2,...valueN ),
( value1, value2,...valueN ),
( value1, value2,...valueN );

语法三:

INSERT INTO table_name VALUES ( value1, value2,...valueN );

insert into 语句案例
# 创建一张学生表：

use yzjtestdb;
create table if not exists yzjtestdb.students(
id int(5) primary key comment '学生学号',
name varchar(10) not null comment '学生姓名',
age tinyint not null comment '学生年龄',
gender enum('男','女','保密') comment '学生性别')?
engine=innodb ?default charset=utf8 ?comment '员工表';

插入数据:
语法一插入

insert into yzjtestdb.students values(1,'漩涡鸣人',16,'男'); ?

commit;

语法二插入

insert into yzjtestdb.students ( id ,name ,age )values( 2,'宇智波佐助',16);?
commit;

语法三插入

insert into yzjtestdb.students ?values
( 3,'卡卡西',25,'男'),( 4,'李洛克',16,'男'),
( 5,'宇智波斑',100,'男'),( 6,'春野樱',16,'女');?
commit;

2.update 语句语法

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

update 语句案例
将名字为漩涡鸣人的列数据修改成宇智波鼬：

UPDATE yzjtestdb.students SET name = '宇智波鼬' where name = '漩涡鸣人';
commit;

将性别为男的学员年龄都 + 10：

UPDATE yzjtestdb.students SET age = age + 10 where gender = '男' and age < 50;
commit;
select * from yzjtestdb.students;?

修改性别为NULL的学员的名字和年龄：

UPDATE yzjtestdb.students SET name = '大蛇丸',age = 127 where gender is null;

3.delete 语句语法

DELETE FROM table_name [WHERE Clause]

delete 语句案例
删除性别为null 的学生,年龄大于100的学生：

delete from yzjtestdb.students where gender is null and age > 100;
commit;

查看

select * from yzjtestdb.students;?

删除性别为男的学生或者年龄大于50的学生：

delete from yzjtestdb.students where gender = '男' or age > 50;
commit;
select * from yzjtestdb.students;?

4.select 语句语法
select 字段/表达式 from 表名/视图名 where 查询条件;
# selct 子句:

where ??? ??? ??? ??? ????查询条件
group by ?? ??? ??? ??? ? 在字段内不同的数据分一组
having?? ??? ??? ??? ??? ?分组以后对于数据的过滤
limit?? ??? ??? ??? ??? ? 对于返回数据行数的限制
order by [asc|desc]?? ?? ?排序 asc:升序，默认 desc:降序

# select 表达式:
(1)变量
?

select 10 + 20;?? ??? ??? ?--查询运算
select 10 * 20;
select 10 - 20;
select 10 / 20;
select now();?? ??? ??? ?--查询当前时间

?? ??? ??? ??? ?
(2)from 子句
?? ??? ??? ??? ?

select * from yzjtestdb.yg;?? ??? ??? ??? ??? ??? ??? ??? ??? ??? --全列
select email from yzjtestdb.yg;?? ??? ??? ??? ??? ??? ??? ??? ??? --单列
select email,job_id from yzjtestdb.yg;?? ??? ??? ??? ??? ??? ??? ?--多列
select * from rwxx,ssgj;?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?  --多表
select * from rwxx x1 ,ssgj j1;?? ??? ??? ??? ??? ??? ??? ??? ??? --别名
select * from rwxx x1 ,ssgj j1 where x1.name = j1.name;?? ??? ??? --连接查询

(3)列连接

select name,concat(name,'-',country,'-',skills) from ssgj;?? ??? ??? ??? ??? ?
select name,concat(name,'-',country,'-',skills) "合并显示" from ssgj;?? ??? 

?

(4)虚拟表 dual ,提高兼容性

select 1 + 1 from dual;
select now() from dual;??

?

(5)SQL语句编写规则
大小写不敏感;
SQL语句可以占用多行,以分号结束，关键字不能拆成多行;

(6)SQL运算符

等于 = ?? ??? ??? ?id = 15;
大于 > ?? ??? ??? ?id > 15;
小于 < ?   ?? ??? ?id < 15;
大于等于 >=  ?? ?  id >= 15;
小于等于 <=  ??   ?id <= 15;
不等于 <> ???     ?id <> 15; ??
不等于 !=  ??     ?id != 15; ?
是空:?? ? ??? ?    is null;?
非空:???? ??? ?    is not null;

(6)模糊查询 [not] like ?: ?? ??? ?字段 like "%北京市%"; ?
? ?范围内 [not] between : ?? ?字段 between 1 and 15;
? ?在什么范围值内 [not] in : ??? ?字段 in ('范围值1','范围值2','范围值3');

# 案例:

use naruto;
select * from rwxx where age = 16;
select * from rwxx where age > 16;
select * from rwxx where age < 16;
select * from rwxx where age <> 16;
select * from rwxx where age >= 16;
select * from rwxx where age <= 16;
select * from rwxx where age between 16 and 25;
select * from rwxx where age not between 16 and 25;
select * from rwxx where age >= 16 and age <= 25;
select * from rwxx where age >= 16 && age <= 25;
select * from rwxx where age in (16,17,28,100);
select * from rwxx where age not in (16,17,28,100);
select * from rwxx where name like "%宇智波%";
select * from rwxx where name not like "%宇智波%";

(7)逻辑运算
?

非 ： not
与 ： and ?, &&
或 ： or ?

# 组合查询案例:

use naruto;
select * from rwxx where age = 16 and gender = '男';?
select * from rwxx where age = 16 or name = '卡卡西';?
select * from ssgj where skills is null;
select * from ssgj where skills is not null;

(8)模糊查询like?

精确查询 ?  = ?
模糊查询 ? like ? ?
A%?? ? ?  A开头?? ??? ?
%A ? ?    A结尾
%A% ?     包含A
%A%C% ?   包含A和C
A%C ? ?   A开头C结尾

# 模糊查询案例:

select tel from yzjtestdb.yzjtest_yg where tel like '137%';?
select JOBTITLE from yzjtestdb.yzjtest_yg where JOBTITLE like '销售%';?
select region from yzjtestdb.yzjtest_yg where region like '%海%';?

(9)查询分组与排序

group by?? ?: 分组?
order by?? ?: 排序
# group by 分组
group by 列 {asc 升序|desc 降序},{with rollup} 组内聚合计算

# 分组常用到的函数:

max?? ?? ?最大值
min????? ?最小值
avg?? ?? ?平均值
sum?? ?? ?列/表达式总和
count ?? ?行数总和

# 分组案例:
案例1：统计（yzjtest_sales 销售表） 本月每个（商品消费分类、GOODS）销量单数
--不同类别商品分组

select count(*) ,GOODS from yzjtest_sales group by goods;

--默认升序

select count(*) ,GOODS from yzjtest_sales group by 2 order by 1;??

??? ?
?--降序?? ?

select count(*) ,GOODS from yzjtest_sales group by 2 order by 1 desc ; ??

?--限制输出一行?? ?

select count(*) ,GOODS from yzjtest_sales group by 2 order by 1 desc limit 1;

--案例2：统计（yg员工表）本月共发了多少工资 （工资、SALARY）

select sum(SALARY) from yg;

--案例3：统计（yzjtest_yg 员工表）不同地区（REGION）、不同部门（DEPT）共发了多少工资（SALARY）

select REGION,DEPT,sum(SALARY) from yzjtest_yg group by 1,2;

--案例4：统计（yzjtest_yg 员工表）不同地区（REGION）、共发了多少工资（SALARY）

select REGION,sum(SALARY) from yzjtest_yg group by 1;

--案例5：with rollup 利用组合条件进行排序后，再次统计

select REGION,DEPT,sum(SALARY) from yzjtest_yg group by 1,2 with rollup ;

--案例6：统计（yzjtest_yg 员工表）不同地区（REGION）、发了总数工资（SALARY）超过两百五十万

select sum(SALARY),REGION from yzjtest_yg group by REGION having sum(SALARY) > 2500000;

(10)限制行数?
limit : 限制行数?
# limit 案例:
案例1：查询YG表限制输出5行

select * from yzjtestdb.yg limit 5;

案例2：查询YG表从第3行开始，显示4行

select * from yzjtestdb.yg limit 3,4;

案例3:配合分组函数使用

select count(*) ,GOODS from yzjtest_sales group by 2 order by 1 limit 3;

(11)去除重复记录?

distinct ?? ?: 去除重复记录?

# distinct 案例:
案例1：去除yzjtest_yg表 sex 字段重复记录

select sex from yzjtest_yg;?? ??? ??? ??? ?--先查看重复记录
select distinct sex ?from yzjtest_yg;?? ??? ?--sex字段去重

(12)union和union all

union?? ? : 无重并集，把多个结果组合并后去重
union all : 有重并集，把多个结果组合并不去重

# union 案例:
案例1：查询rwxx表、ssgj表的name字段合并去重

select name from naruto.rwxx union select name from naruto.ssgj;

# union all案例:
案例1：查询rwxx表、ssgj表的name字段合并不去重

select name from naruto.rwxx union all select name from naruto.ssgj;

(13)for update
for update?? ? : 锁表，悲观锁,生产环境切勿使用这条语句
案例1：将rwxx表锁住

select * from naruto.rwxx for update;

5.select 高级查询连接查询和子查询
(1)select 高级查询之连接查询 join
SQL JOIN 子句用于把来自两个或多个表的行结合起来返回数据，基于这些表之间的共同字段
join连接分为3大类：

自然连接 NATURAL JOIN：自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。
内连接 INNER JOIN： ? ?如果表中有至少一个匹配，则返回行外连接
左外连接 LEFT JOIN： 即使右表中没有匹配，也从左表返回所有的行
右外连接 RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
全外连接 FULL JOIN： 只要其中一个表中存在匹配，则返回行 （mysql不支持）

SQL join案例:
案例1：自然连接 NATURAL JOIN

select * from naruto.rwxx natural join naruto.ssgj;

案例2：内连接 INNER JOIN

select * from naruto.rwxx ?a inner join naruto.ssgj b on a.name = b.name ;?? ??? ??? ??? ??? ?--写法1
select * from naruto.rwxx ?a , naruto.ssgj b where a.name = b.name ;?? ??? ??? ?--写法2
select * from naruto.rwxx ?a inner join naruto.ssgj b using(name);?? ??? ??? ??? ??? ??? ?--写法3
select a.name,a.age,b.country,b.skills from naruto.rwxx ?a inner join naruto.ssgj b on a.name = b.name ;?? ??? ??? ??? ??? ?

案例3：左外连接 left join / left outer join

select * from naruto.rwxx r left outer join naruto.ssgj s on r.name = s.name;

案例4：又外连接 right join / right outer join

select * from naruto.rwxx r right outer join naruto.ssgj s on r.name = s.name;

案例5：交叉连接 cross join

select * from naruto.rwxx r cross join naruto.ssgj s ;

(2)select 高级查询之子查询?
selct 子查询是将一个查询语句嵌套在另一个查询语句中，在特定情况下，一个查询语句的条件需要另一个查询语句来获取，
内层查询（inner query）语句的查询结果，可以为外层查询（outer query）语句提供查询条件。
子查询分为：
单行子查询：最多返回一行
多行子查询：返回一行或者多行
子查询可以返回单一值（标量子查询）、一个行（标量子查询）、一个列（标量子查询）、一个表（标量子查询）。

# 子查询案例表:

scott.dept ?? ??? ??? ??? ?部门表?
scott.emp ?? ??? ??? ??? ? 雇员表?
scott.salgrade ?? ??? ??? ?工资等级表
scott.bonus?? ??? ??? ??? ?工资表?? ?

案例1：简单子查询,统计当前部门和员工的数量。

select count(*) from scott.dept; ??? ??? ?--先统计部门数量
select count(*) from scott.emp;?? ? ??? ??? ?--先统计员工数量
select now() 当前时间,
(select count(*) from scott.dept) 部门数量,
(select count(*) from scott.emp) 员工数量
from dual;

案例2：通过查询dept子查询emp表找出“经理”职位的员工是在哪些城市

select * from dept;
select * from emp;
select loc from dept where deptno in (select deptno from emp where job = '经理' );

案例3：求哪一个城市支付的最高薪水

select * from dept where deptno = (select deptno from emp where sal = ( select max(sal) from emp ));

案例4：通过emp员工表、salgrade 工资等级表找到“董事长”职位在工资等级表是个什么级别

select * from salgrade where (select sal from emp where job = '董事长') between losal and hisal;

案例5：通过部门表dept 找出员工表emp 的岗位是“会计”的职位
exists | not exists
exists 子句会将子查询所返回的结果分成TRUE，FALSE状态，如果子查询结果有返回，将结果反回给上一层TRUE状态，
如果子查询结果没有返回，将反馈一个FALSE状态，exists 子句接收到状态值TRUE以后将会对外部查询的表输出数据（假设外部表有数据），
如果状态值为FLASE，外部表则不输出数据。
not exists 子句则反之，接收到状态值为FALSE的输出外部表的数据，接收到状态值为TRUE将不输出外部表的数据。

select * from emp where exists ( select * from bonus ); ? ?? ??? ? --不返回 ? ? ? ? ?
select * from emp where not exists ( select * from bonus );?? ??? ? --返回
select * from emp where exists (select * from dept where dname like '%会计%' and deptno = emp.deptno) ;

案例6：如果有平均工资不小于1500的部门信息则查询所有部门信息(使用not exists)

select * from dept where not exists ( select deptno,avg(sal) from emp group by deptno having avg(sal) < 1500);

案例7：通过emp员工表查询哪些“job”岗位的工资比“job”岗位办事员的高(多行子查询)
单行子查询：子查询返回的行数是单行的。
多行子查询：子查询返回的行数是多行的。

单行子查询案例，查询EMP员工表谁工资比 SMITH 员工高。
标红子查询返回单行

select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH') order by 2 desc;?

多行子查询案例，通过emp员工表查询哪些“job”岗位的工资比“job”岗位办事员的高。
标红子查询返回多行

select job from emp where sal > (select sal from emp where job = '办事员'); ? ?-- error
select job from emp where sal > all (select sal from emp where job = '办事员'); -- true

all 子句的用法：

> all ?| > any?? ???  ?大于子查询结果的所有值
< all ?| < any?? ??? ? 小于子查询结果的所有值
= all ?| = any?? ???  ?等于子查询结果的所有值
>= all | >= any?? ??? ?大于等于子查询结果的所有值
<= all | <= any?? ??? ?小于等于子查询结果的所有值
!= all | != any?? ??? ?不等于子查询结果的所有值

例
?

SELECT NAME,salary FROM employees?
WHERE salary > ALL ( SELECT salary FROM employees WHERE department_id = 6 )?
ORDER BY salary;