（2）MySQL命令

MySQL基础命令

查看MySQL数据库的版本号

select version();

查看MySQL中有哪些数据库

show databases;MySQL默认自带4个数据库

创建数据库

create database 数据库名称;

使用/进入数据库

use 数据库名称;

查看当前使用的数据库

select database();

查看数据库中的表

show tables;

导入数据或批量执行SQL语句

以.sql结尾的文件中有批量的SQL语句，称为SQL脚本文件。shift+右键复制文件路径或者直接把文件拖到cmd窗口得到脚本文件路径，注意路径中不要有中文。
source 脚本文件路径
或者使用navicat

查看表中所有的数据

select * from 表名;

查看表的结构(字段)

desc 表名; 或者 describe 表名;

终止语句的输入

\c

演示用表数据

部门表

deptno 部门编码 dname 部门名称 loc 部门地址

员工表

empno 员工编码 ename 员工姓名 job 岗位名称 mgr 上级编码 hiredate 入职日期 sal 工资 comm 福利 deptno 部门编码

工资表

grade 工资级别 losal 最低薪资 hisal 最高薪资

命令分类

TCL：事务操作语言 (Transaction Control Language,TCL)
DDL：数据定义语言 (Data Definition Language, DDL)
DML：数据操纵语言（Data Manipulation Language, DML）
DQL；数据查询语言（Data Query Language, DQL）

DQL（单表查询）

MySQL基础查询命令

查询一个字段

select 字段名 from 表名;

查询两个字段或者多个字段

select 字段名,字段名 from 表名;

查询所有的字段

select * from 表名;
注意：实际开发不推荐使用*,因为会先将*转换为字段，消耗性能

给查询的字段起别名

select 字段名 as 别名 from 表名;
注意：1、不会改变原表的字段名 2、as可以省略，省略处用逗号隔开 3、别名中有空格怎么办，使用单引号或者双引号（Oracle中识别不了双引号，推荐使用单引号）把含空格的别名包裹起来
select deptno,dname deptname from dept;
select deptno,dname 'dept name' from dept;

字段名可以进行数学运算

select sal*12 as '年薪' from emp;

条件查询

条件查询的基本格式

select
​	字段名1,字段名2,...
from
​	表名
where
​	条件;

条件有哪些?

=等于

例如: 查询薪资等于3000的
select empno,ename from emp where sal = 3000;

<>或者!=不等于

例如：查询薪资不等于800的
select empno,ename from emp where sal != 800;

< 小于

例如：查询薪资小于2000的
select empno,ename,sal from emp where sal < 2000;

<= 小于等于

例如：查询薪资小于等于3000的

select empno,ename,sal from emp where sal <= 3000;

> 大于

例如：查询薪资大于3000的

select empno,ename,sal from emp where sal > 3000;

>= 大于等于

例如：查询薪资大于等于3000的
select empno,ename,sal from emp where sal >= 3000;

between…and… 或者…>=… and …<=… 两者之间

注意：包括两端的值
例如：查询薪资在[2450,3000]区间的
select empno,ename,sal from emp where sal between 2450 and 3000;
select empno,ename,sal from emp where sal >= 2450 and sal<= 3000;

is null 为空

例如： 查询员工津贴为空的
select empno,ename,sal,comm from emp where comm is null;
注意：null是指数据啥也没有，和0区分开，0代表有值，值是0，null表示没有值。

is not null 不为null

例如：查询员工津贴不为空的
select empno,ename,sal,comm from emp where comm is not null;

and 并且

例如：查询员工岗位为MANAGER且工资大于2500的
select empno,ename,job,sal from emp where job = 'MANAGER' and sal > 2500;

or 或者

例如：工作岗位是MANAGER和SALESMAN的
select empno,ename,job from emp where job= 'manager'or job = 'salesman';
注意：and 和 or 同时出现的时候，and的优先级比or高，先执行and再执行or，要想or先执行可以使用小括号();
例如：查询工资大于2500并且部门编号为10的或者20部门所有的
select * from emp where sal > 2500 and deptno = 10 or deptno = 20;
查询工资大于2500，并且部门编号为10或20部门的员工
select * from emp where sal > 2500 and (deptno = 10 or deptno = 20);

in 包含

例如：找到工作岗位是manager或者salesman的
select empno,ename,job from emp where job in('manager','salesman');

not in 不包含

例如：找到工作岗位不是manager或者salesman的
select empno,ename,job from emp where job not in('manager','salesman');

like模糊查询

%匹配多个字符
_（下划线）匹配一个字符
例如：查询ename中中间包含’O’的
select ename from emp where ename like '%O%';
注意：如果要匹配含有_的应该使用转义符 ‘\ _’

distinct去重

例如：查询有多少种岗位
select distinct job from emp;
联合起来去重，多个字段都相同才去重
select distinct job,deptno from emp;

limit取条数

limit是将查询结果集的一部分取出来，一般使用在分页查询中。
limit后面可以跟两个数字参数，一个是开始坐标，后面是长度，起始下标默认是0。
例如：从第6条开始取出剩余的所有数据
select ename,sal from emp order by sal desc limit 5;

例如：取出前5条数据
select ename,sal from emp order by sal desc limit 0,5;

查询排序

升序

例如：根据薪资升序排序
select ename,sal from emp order by sal; 或者 select ename,sal from emp order by sal asc;

注意：默认就是升序

降序

例如：根据薪资降序排序
select ename,sal from emp order by sal desc;

多个字段排序

排序字段条件用逗号隔开，先按左边的排序，左边相等的时候，再按右一位的字段进行排序。
例如：先根据薪资升序排序，薪资相同时，再根据ename升序排序
select ename,sal from emp order by sal,ename;
可以给每个字段都添加是按升序还是按降序排列。
例如：薪资升序，ename降序
select ename,sal from emp order by sal asc,ename desc;

（了解即可）根据字段位置进行排序

例如：根据emp表的第二个字段升序排序
select ename,sal from emp order by 2;
注意：不推荐使用，因为字段的顺序可以改变，不利于维护。

基础，条件，排序语句小总结

例如：查询工资在[1250,3000]之间的，根据薪资大小升序排序
select ename,sal from emp where sal between 1250 and 3000 order by sal desc;
注意：关键词的顺序不能变，select… from… where… order by…
关键词的执行顺序是1、from 2、where 3、select 4、order by 排序永远都是在最后面

处理函数（单行处理函数）

数据处理函数又被称为单行处理函数。一个输入对应一个输出，执行的时候，是对表格中的数据一行一行的进行函数执行。
与之对应的是多行处理函数，特点：多个输入对应一个输出

lower(字段) 将内容全部转小写

select lower(ename) from emp;

upper(字段) 将内容全部转换为大写

select upper(name) from t_vip;

substr(字段，起始下标，长度) 取子串

例如：取emp中ename字段值的下标1(首个)的值
select substr(ename,1,1) from emp;
substr 和 like 的相似之处
select ename from emp where substr(ename,1,1) = 'A'; 等同于 select ename from emp where ename like 'A%';

concat(字段名1，字段名2) 字符串拼接

例如：拼接ename和sal字段的值
select concat(ename,sal) from emp;

length(字段名)取字段值长度

练习题：首字母大写
select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_vip;

trim(值) 去除左右空格

实际开发中，trim中的内容是接收过来的变量。
select * from emp where ename = trim(' KING');

round(待处理的数据,保留到多少位)

select round(1234.567,2) from emp;
注意：1、select后面可以跟字段名，也可以跟字面量。当跟字面量时，会借助表的结构进行输出。
2、后面保留到的位数：2保留到小数位第二位；0保留到整数位；-1保留到十位。

rand() 生成0~1大小之间的随机数

select rand() from emp;
借助表的结构

练习：生成100以内的随机数
select round(rand()*100,0) from emp;

ifnull(要判断的字段，当成的值) 可以将null 转换为一个具体的值

select ename,sal+comm from emp;
在数据库中，null参与数学计算，结果都是null

使用ifnull之后
select ename,sal+ifnull(comm,0) from emp;

case…when…then…when…then…else…end

例如：当job是manager时工资x1.5，当job是clerk时工资x2，其它的不变
select ename,job,sal as oldjob,(case job when 'manager' then sal * 1.5 when 'clerk' then sal * 2 else sal end) as newsal from emp;

format数字格式化

例如：显示千分位
select ename,format(sal,'$999.999') as sal from emp;

str_to_date()将字符串类型转换成日期类型

insert into t_user(id,name,birth) values(1,'liujie',str_to_date('01-01-1999','%d-%m-%Y'));

date_format()将日期转换成有格式的字符串

select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;

分组函数（多行处理函数）

输入多行，最终输出一行
注意：分组函数必须先分组才能使用，如果没有分组，整张表就是一组。

min() 最小值

例如：找出工资的最小值
select min(sal) from emp;

max() 最大值

例如：找出工资的最大值
select max(sal) from emp;

avg() 平均值

例如：找出工资的平均值
select avg(sal) from emp;

sum() 求和

例如：找出工资的和
select sum(sal) from emp;

count() 统计数量

例如：统计共多少条数据
select count(ename) from emp;
count（字段）统计的是字段的记录数
count（*）统计的是表的总行数

使用分组函数的注意事项

1. 会自动忽略null,包括count！！！！！！
2. 分组函数不能使用在where子句中。因为where执行在group by 之前，执行where的时候还没有分组，不能执行分组函数。那么为什么select min(sal) from emp;可以执行，因为min()分组函数指向在select之后，当执行select的时候group by 分组已经执行了，因此可以使用分组函数。
3. 分组函数可以组合起来使用
   select min(sal),max(sal),max(sal),avg(sal),count(sal) from emp;
   

分组查询

先进行分组，再对每一组数据进行操作
所有的关键字的书写顺序是

select...	from...	where...	group by...	order by...

所有关键字的执行顺序是

1、from 2、where 3、group by 4、select 5、order by

注意：当有group by 语句时，select 后面只能跟分组的字段和分组函数，否则跟其它的字段时，MySQL中只会匹配每一组的第一条数据显示,Oracle中会报错。
select job,sal from emp group by job;

分组

例如：找出每个部门的平均薪资
select deptno,avg(sal) from emp group by deptno;

嵌套分组

例如：找出每个部门不同岗位的平均薪资
select deptno,job,avg(sal) from emp group by deptno,job;

having对分组后的数据再次过滤

having不能单独使用，搭配分组使用
例如：找出不同部门薪资的最大值，并且只显示大于3000的
select deptno,max(sal) from emp group by deptno having max(sal) > 3000;
类似于：
select deptno,max(sal) from emp where sal > 3000 group by deptno;
下面的方法效率更高，先筛选，再分组，再执行函数(先筛选n+分组(n-x)+过滤），比第一种先分组，再执行函数，再筛选效率高(先分组n+筛选n+过滤）。因为执行了更少次的函数。
结论：能使用where的，尽量使用where，不能使用where的才使用having。
什么时候不能使用where，比如：找出部门的平均薪资 > 2500。此时无法使用where对sal薪资筛选，必须先分组才能知道平均薪资。

单表查询总结

关键字书写顺序

select
​	...
from
​	...
where
​	...
group by
​	...
having
​	...
order by
​	...
;

关键字执行顺序
1\from 2\where 3\group by 4\having 5\select 6\order by
综合案例
例如：找出每个岗位（除manager之外）的平均薪资，只要平均薪资大于1500的，要求按照平均薪资降序排序。
select job,avg(sal) as avg_sal from emp where job != 'manager' group by job having avg_sal > 1500 order by avg_sal desc;
或者
select job,avg(sal) as avg_sal from emp where job not in('manager') group by job having avg_sal > 1500 order by avg_sal desc;

DQL（连接查询）

从一张表中单独查询，称为单表查询。
多个表联合起来查询数据，跨表查询称为连接查询。

连接查询的分类

1. 根据语法年代分类
   SQL92 语法
   SQL99 语法（重点）
2. 根据表连接的方式进行分类
   内连接
   等值连接
   非等值连接
   自连接
   外连接
   右外连接（右连接）
   左外连接（左连接）
   全连接（几乎不用）

连接查询的一种常见现象（笛卡尔积）

当两张表进行连接查询时，如果没有加任何的条件筛选，那么查询的结果条数就是两张表的数据调条数的乘积，这种现象称为：笛卡尔积现象

避免笛卡尔积现象

例如：查询员工对应的部门名称

连接时加where条件筛选
select ename,dname from emp,dept where emp.deptno = dept.deptno;

提高查询效率的办法（给表起别名会提高查询效率）
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

内连接——等值连接

例如：查询每个员工所在部门名称，显示员工名和部门名？
（92语法）
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
92语法的缺点，语法结构不清晰，连接条件和筛选条件都写到了where语句中

（99语法）
select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;
inner可以省略，但是推荐写上，写上之后一眼就能看出来是内连接。on后面还可以再加上where 筛选条件

内连接——非等值连接

例如：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;

内连接——自连接

把一张表当成两张不同的表来对待
例如：查询员工的上级领导，要求显示员工名和对应的领导名？
select a.ename as '员工名',b.ename as '领导名' from emp a inner join emp b on a.mgr = b.empno;

外连接

右外连接（右连接）

概念：就是将join右边的表作为主表，输出的结果包含主表的所有内容，再加上on 关键字 后面条件匹配的数据。
select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno;
outer可以省略

左外连接（左连接）

概念：与右外连接刚好相反
select e.ename,d.dname from emp e left outer join dept d on e.deptno = d.deptno;
outer可以省略
注意：连接条件相同时，外连接查询的结果数量 >= 内连接查询的结果数量

练习：查询每个员工的上级领导，要求显示所有员工的名字和领导名？
select a.ename as '员工名', b.ename as '领导名' from emp a left outer join emp b on a.mgr = b.empno;

外连接与内连接的区别

内连接就是将满足条件的结果，展示出来，不满足的不会展示。
外连接在内连接的基础上会以一个表为主表，不管条件如何主表中的数据都会展示，最终结果是主表的内容+满足条件on的。
外连接的查询结果的条数一定是大于等于内连接的查询结果

多表连接

语法：select … from a join b on a和b的连接条件 join c on a和c的连接条件
例如：找出所有员工显示员工的姓名，工资，部门名称，工资级别，领导名称，包括领导为null的
select e.ename,e.sal,d.dname,s.grade,l.ename as '领导名' from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal left join emp l on e.mgr = l.empno;

DQL（子查询）

select语句中嵌套select语句，被嵌套的select语句称为子查询

子查询都可以出现在什么位置？

select后面

from后面

where后面

where子句中的子查询

一般是查询出一个数据结果，用来做条件判断。
例如：查出不是最低薪资的员工

select ename,sal from emp where sal > (select min(sal) from emp);

from子句中的子查询

from后面的子查询可以将子查询的查询结果当成一张临时的表

例如： 查询每个岗位的平均薪资属于工资的级别

select a.job,a.avg ,s.grade from (select job,avg(sal) as avg from emp group by job ) as a join salgrade s on a.avg between s.losal and hisal;

select 子句中的子查询(了解即可)

例如：将一个表作为另外一个表的字段
select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

DQL（union合并查询）

例如：获取岗位为 manager 和 salesman 的员工
select ename,job from emp where job = 'MANAGER' union select ename,job from emp where job = 'SALESMAN';
等同于：
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
或者：
select ename,job from emp where job in ('MANAGER','SALESMAN');

union和另外两种写法的区别

最大的区别在于效率问题，union的效率更好

union使用的注意事项

union使用的时候要保证合并的两个查询结果集的列数要相同。
比如：
select ename,job from emp where job = 'MANAGER' union select ename from emp where job 'SALESMAN';
因为前面有两列后面只有一列，不相等所以就会报错。

ERROR 1222 (21000): The used SELECT statements have a different number of columns

特别提醒：当列与列的数据类型不相同时，在MySQL中不会报错但是在Oracle中会报错

DQL练习题

1. 取得每个部门最高薪水的人员名称
   select e.ename,e.deptno,d.dname,new_emp.max_sal from (select deptno,max(sal) as max_sal from emp e1 group by deptno) as new_emp inner join emp e on e.sal = new_emp.max_sal and e.deptno = new_emp.deptno left outer join dept d on d.deptno = e.deptno;
   
2. 哪些人的薪水在部门的平均薪水之上
   select e.ename,e.sal,e.deptno,new_emp.avg_sal from emp e inner join (select deptno,avg(sal) as avg_sal from emp group by deptno) as new_emp on e.deptno = new_emp.deptno and e.sal > new_emp.avg_sal;
   
3. 取得部门中（所有人的）平均薪水的等级
   select new_emp.deptno,new_emp.avg_sal,s.grade from salgrade s right outer join (select deptno,avg(sal) as avg_sal from emp group by deptno) as new_emp on new_emp.avg_sal between s.losal and s.hisal;
   
4. 取得部门中（所有人的）平均的薪水等级
   select new_emp.deptno,avg(new_emp.grade) from (select e.deptno, s.grade from emp e left outer join salgrade s on e.sal between s.losal and s.hisal) as new_emp group by new_emp.deptno;
   
5. 不准用组函数（Max ），取得最高薪水
   select sal from emp order by sal desc limit 1;
select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);最大值不会小于另外一个相同表的所有值
   
6. 取得平均薪水最高的部门的部门编号
   select deptno,avg(sal) as max_sal from emp group by deptno order by avg(sal) desc limit 1;
select sal_t.deptno,max(sal_t.avg_sal) as max_sal from (select deptno,avg(sal) as avg_sal from emp group by deptno) as sal_t;
   
7. 取得平均薪水最高的部门的部门名称
   select d.deptno, d.dname,dm.max_sal from dept d right join (select deptno,avg(sal) as max_sal from emp group by deptno order by avg(sal) desc limit 1) as dm on dm.deptno = d.deptno;
select d.deptno,d.dname,dm.max_sal from dept d right join (select sal_t.deptno,max(sal_t.avg_sal) as max_sal from (select deptno,avg(sal) as avg_sal from emp group by deptno) as sal_t) as dm on dm.deptno = d.deptno;
   
8. 求平均薪水的等级最低的部门的部门名称
   select gd.deptno, d.dname,gd.grade from dept d right join (select s.grade,new_emp.deptno from salgrade s right join (select e.deptno,avg(sal) as avg_sal from emp e group by deptno) as new_emp on new_emp.avg_sal between s.losal and s.hisal order by grade limit 1) as gd on gd.deptno = d.deptno;
   
9. 取得比普通员工(员工编码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
   select ename,sal from emp where sal > (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));
   
10. 取得薪水最高的前五名员工
    select ename,sal from emp order by sselect ename,sal from emp order by sal desc limit 0,5;
    
11. 取得薪水最高的第六到第十名员工
    select ename,sal from emp order by sal desc limit 5,5;
    
12. 取得最后入职的 5 名员工
    select ename,hiredate from emp order by hiredate desc limit 5;
    
13. 取得每个薪水等级有多少员工
    select s.grade,count(s.grade) from emp e left join salgrade s on e.sal between s.losal and s.hisal group by s.grade;
    
14. 列出所有员工及领导的姓名
    select e1.ename as '员工名',e2.ename as '领导名 ' from emp e1 inner join emp e2 on e1.mgr = e2.empno;
    
15. 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
    select new_emp.empno,new_emp.ename,d.dname from dept d right join (select e1.empno,e1.ename,e1.deptno from emp e1 inner join emp e2 on e1.mgr = e2.empno where e1.hiredate < e2.hiredate) as new_emp on new_emp.deptno = d.deptno;
    
16. 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门
    select e.*,d.dname from emp e right join dept d on e.deptno = d.deptno;
    
17. 列出至少有 5 个员工的所有部门按照部门编号分组，计数，筛选出 >= 5
    select e.deptno,count(e.deptno) from emp e left join dept d on d.deptno = e.deptno group by e.deptno having count(e.deptno) >= 5;
    
18. 列出薪金比"CLARK" 多的所有员工信息
    select * from emp where sal > (select sal from emp where ename = "CLARK");
    
19. 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
    select new_emp.e name,new_dept.* from (select d.deptno,d.dname as '部门名称',count(d.deptno) as '部门人数' from dept d right join emp e1 on d.deptno = e1.deptno group by d.deptno) as new_dept right join (select * from emp where job = 'CLERK') as new_emp on new_dept.deptno = new_emp.deptno;
    
20. 列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数，按照工作岗位分组求最小值。
    select job,min(sal),count(job) from emp group by job having min(sal) > 1500;
    
21. 列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号
    select ename from emp where deptno = (select DEPTNO from dept where dname = 'SALES');
    
22. 列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级
    select mgr_dname_emp.*,s.grade from salgrade s right join (select mgr_emp.*,d.dname from dept d right join (select more_avg_sal_emp.ename,more_avg_sal_emp.deptno,more_avg_sal_emp.sal, e.ename as mgrname from emp e right join (select * from emp where sal > (select avg(sal) from emp)) as more_avg_sal_emp on more_avg_sal_emp.mgr = e.empno)as mgr_emp on mgr_emp.deptno = d.deptno) as mgr_dname_emp on mgr_dname_emp.sal between s.losal and s.hisal;
    
23. 列出与"SCOTT" 从事相同工作的所有员工及部门名称
    select new_emp.*,d.dname from dept d right join (select ename,deptno from emp where job = (select job from emp where ename = 'SCOTT')) as new_emp on new_emp.deptno = d.deptno;
    
24. 列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金
    select ename,sal from emp where sal in(select distinct sal from emp where deptno = 30) and deptno != 30;
    
25. 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
    select d.dname,new_emp.* from dept d right join (select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno = 30) and deptno != 30) as new_emp on new_emp.deptno = d.deptno;
    
26. 列出在每个部门工作的员工数量, 平均工资和平均服务期限
    select d.deptno, count(e.ename) ecount,ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime from emp e right join dept d on e.deptno = d.deptno group by d.deptno;
    
27. 列出所有员工的姓名、部门名称和工资
    select e.ename,d .dname,e.sal from emp e left join dept d on e.deptno = d.deptno;
    
28. 列出所有部门的详细信息和人数
    select * from dept d left join (select deptno,count(* ) from emp group by deptno) as new_dept on new_dept.deptno = d.deptno;
    
29. 列出各种工作的最低工资及从事此工作的雇员姓名
    select e.ename,new_emp.* from emp e right join (select min(sal) as min_sal,job from emp group by job) as new_emp on new_emp.min_sal = e.sal and new_emp.job = e.job;
    
30. 列出各个部门的 MANAGER( 领导) 的最低薪金
    select ename,job,deptno,sal,min(sal) from emp where job = 'MANAGER' group by deptno;
    
31. 列出所有员工的 年工资, 按年薪从低到高排序
    select sal * 12 as year_sal from emp order by year_sal;
    
32. 求出员工领导的薪水超过3000的员工名称与领导名称
    select e.ename as '员工名称',e2.ename as '领导名称 ' from emp e inner join (select sal,empno,ename from emp where sal > 3000) as e2 on e2.empno = e.mgr
    
33. 求出部门名称中, 带’S’字符的部门员工的工资合计、部门人数
    select count(*),ifnull(sum(e.sal),0) as sum_sal from emp e inner join (select deptno from dept where dname like '%S%') as d on e.deptno = d.deptno group by e.deptno;
34. 给任职日期超过 30 年的员工加薪 10%
    update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;

DDL

表的创建

语法：create table 表名(字段名1 数据类型，字段名 2数据类型...);
例如：create table t_test(no int,name varchar(32),sex char(1),age int(3),email varchar(255));
数据类型有很多，只介绍最常用的

字段数据类型

数据类型有很多，只列举常用的

varchar（255）

可变长度的字符串，比较智能，节省空间，会根据实际的数据长度动态的分配存储空间

char（255）

固定长度的字符串，不管实际的数据长度是多少，分配固定长度的存储空间来存储数据，实际使用起来可能会浪费空间，但是效率比varchar效率高,速度快

varchar和char的选择？

当实际长度比较固定时，可以使用char提升效率，当长度不固定，为了节省空间，只能牺牲效率了，因此varchar和char的选择实际上就是空间和效率的选择

int（11）

整型

bigint

大整型

float

单精度浮点型

double

双精度浮点型

date

短日期类型

datetime

长日期类型

clob

Character large object

字符大对象，最多可以存储4G的字符串（存文章）

一般用来存储超过255长度

blob

Binary large object
二进制大对象
存图片，声音，视频等流媒体数据

date和datetime两个类型的区别

date是短日期只包含年月日而datetime是长日期包含年月日时分秒
date的默认格式是%Y-%m-%d
datetime的默认格式是%Y-%m-%d %h:%i:%s

删除表

语法1：drop table 表名;
当这张表不存在的时候会报错
语法2：drop table if exists 表名;

复制表

语法：create table 表名 as 查询结果
create table t_user2 as select * from t_user;

插入表（了解）

将查询结果表格插入到别的表中
语法：insert into 要插入的表名 查询的结果
insert into t_user select * from t_user;
一般很少用，因为要求插入的表格列数和待插入的表格列数要相同

DML

插入数据

插入普通数据

insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);
insert into t_student(no,name,sex,age,email) values (2017403601,'liujie','m',22,'1765659336@qq.com');

没有给字段指定值，默认值是NULL，可以使用default指定默认值

插入日期数据

创建一个表格包含日期类型的字段
create table t_user(id int,name varchar(32),birth date);

插入数据
insert into t_user(id,name,birth) values(1,'liujie','01-01-1999');

这里的日期是字符串的格式，不是日期类型所以报错
Incorrect date value: '01-01-1999' for column 'birth' at row 1
因此要使用str_to_date函数将日期字符串转换为日期
日期格式 %Y 年 %m 月 %d 日 %h 时 %i 分 %s 秒

insert into t_user(id,name,birth) values(1,'liujie',str_to_date('01-01-1999','%d-%m-%Y'));

值得注意的是，如果传入的日期格式是%Y-%m-%d，就不需要使用str_to_date函数。mysql会自动执行这个函数
insert into t_user(id,name,birth) values(1,'zhangsan','2000-02-11');

当我们查询数据展示的时候要自己修改日期的展示格式
可以使用date_fromat函数
select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;

当我们不使用date_format函数时，展示数据的格式是%Y-%m-%d

mysql获取系统默认时间

now（）函数
insert into t_user values(3,'liujie',now());

一次插入多条记录

语法：insert into 表名(字段名…) values (记录1),（记录2);
例如：insert into t_user values (1,'zhangsha',now()),(2,'lisi',now());

指定默认值与省略字段

指定默认值
例如：指定性别的默认值为男m
create table t_default(name varchar(32),sex char(1) default 'm');
insert into t_default(name) values ('liujie');
select * from t_default;

省略字段
当表名后面不写字段名时（实际上就相当于全写上了），后面传值时不能省略，且顺序不能错。否则会报列数不同的错误Column count doesn’t match value count at row 1

计算时间差

TimeStampDiff(间隔类型, 前一个日期, 后一个日期)

间隔类型：
SECOND 秒，
MINUTE 分钟，
HOUR 小时，
DAY 天，
WEEK 星期
MONTH 月，
QUARTER 季度，
YEAR 年

修改数据

语法：update 表名 set 字段名1=值1，字段名2=值2...where 条件
注意如果不加条件会将表中的数据全部改变
update t_user set name='lisi',birth='2001-02-22' where id = 1;

删除数据

语法:delete from 表名 where 条件;
！！！！一定要加条件，不加条件整张表的数据都会被删除
delete from t_user where id = 1;

快速删除表中的数据

删除dept_bak表中的数据
这种删除数据的方式比较慢。
delete语句删除数据的原理？（delete属于DML语句！！！）
表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！
这种删除缺点是：删除效率比较低。
这种删除优点是：支持事务rollback回滚，后悔了可以再恢复数据！！！
truncate语句删除数据的原理？
这种删除效率比较高，表被一次截断，物理删除。
这种删除缺点：不支持回滚。
这种删除优点：快速。
truncate table dept_bak;
大表非常大，上亿条记录?
删除的时候，使用delete，也许需要执行1个小时才能删除完！效率较低。
可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。
但是使用truncate之前，必须仔细询问客户是否真的要删除，并警告删除之后不可恢复！
truncate是删除表中的数据，表还在！