mysql命令总结

基础查询

select [字段] from 表名

select * from emp;

查询所有数据 在数据量很庞大的情况下, 效率低, 不推荐使用

select empno,ename.sal from emp;

指定查询字段 (使用逗号隔开)

as 子句

select empno as 编号,ename as 姓名 ,sal as 工资 from emp;

更改表头

select e.empno,e.ename,e.sal from emp as e;

给表起别名

select empno 编号,ename 姓名 ,sal 工资 from emp;

#as 可以省略不写
#distinct 关键字

select job 工作 from emp;

查看 emp 表中的工作种类

select distinct job 工作 from emp;

去除工作中重复性的数据，每一个字段都相同,才可以去除重复

在返回结果中使用表达式(函数)

select ename 姓名,sal 工资 from emp;

select ename 姓名,sal*1.1 工资 from emp;

所有员工的工资涨薪 10%

条件查询
语法 select [字段] from 表名 [where 条件]

比较运算符

< >= <= = != 判断是否为空

is null / is not null between and 在什么与什么之间 相当于 >= && <= in 在集合范围内

like 像 模糊查询

基本比较
1，select * from emp where sal > 2000

#查询工资大于 2000 的所有员工

2，select * from emp where job = 'clerk'

#查询工作为办事员的 所有员工

3，

select ename "姓名" , hiredate "入职时间" from emp where hiredate > '1981-05-31'

#查询入职时间 在1981年5月份之后入职的所有员工的姓名以及 入职时间

is null
1,select empno "编号" , job "职位" from emp where mgr is null;

#查询 没有上级的 员工的编号和职位

2,select * from emp where comm is not null;

#查询 奖金不为null 的 所有员工

between and
1,select ename,sal from emp where sal between 1000 and 2000;

#查询工资在1000 到2000 之间的员工的姓名和工资，

左右边界都包含在内
小值在前, 大值在后
in
1,select * from emp where job = 'CLERK' or job = 'SALESMAN';

2,select * from emp where job in ('CLERK','SALESMAN');

#查询工作为 办事员或者是 销售员的员工

3,select * from emp where deptno in (20,30);

#查询 20号和30号部门中的所有员工

like 模糊查询(占位符)
(_) 一个任意字符

% 任意字符 0个或多个

1，select * from emp where ename like '%A%';

#查询姓名中带有 A 字符的 所有员工

2，select * from emp where ename like 'A%';

#查询姓名中首字母为 A 字符的所有员工

3，select * from emp where ename like '_A%';

#查询姓名中 第二个字母为A的所有员工

注意：

如果说 select * from emp where ename like ‘A’;

相当于

select * from emp where ename = 'A';

逻辑运算符
and &&

or ||

not !

1，

select * from emp WHERE hiredate < '1981-05-01' and job = 'MANAGER'

#查询入职时间早于1981 年5月, 并且职位为经理的员工

排序查询
语法: order by 需要排序的字段 指定规则(默认升序), 第二个需要排序的字段

可以根据字段名 排序
可以根据 字段的别名排序
根据字段的位置编号排序
1，select * from emp order by sal ;

2，select * from emp order by sal desc;

#查询所有员工信息, 按照工资升序排列

3，select * from emp order by sal , hiredate desc;

#查询所有员工信息, 按照工资升序排列,如果工资相同, 再按照入职时间降序排序

4，select * from emp order by 1 desc;

5，select empno 编号 , enamel from emp order by 编号 desc;

#按照员工编号降序 查询所有员工信息

分页查询(限定查询)
语法 语句的最后使用 分页

limit [m ,] n 从第m条记录开始 查询, 一共返回 n条数据

如果m 项不指定, 默认为 0

1，select * from emp order by sal desc limit 5;

#查询工资最高的5 名员工

2，select * from emp order by sal desc limit 6,4;

#查询工资从高到低排名第7 - 10 位的的4 名员工

3，

select * from emp where job = 'SALESMAN' and (sal + comm)> 1500 order by sal desc limit 3

#查询职位为 销售员的 并且总工资高于 1500 的前三名员工, 按照工资降序排列

Mysql函数
统计函数/聚合函数
函数名：count() sum() avg() max() min()

说明：统计, 计数 求和 平均数 最大值 最小值

统计emp 表总数据条数

忽略 值为null 的字段

一般情况下, count() 中 统计那些非空字段 (主键)

1，

select count(*) from emp; # 返回值为一个数字
      select count(1) from emp;
      select count(empno) from emp;

2，select sum(sal) from emp;

#统计该公司所有员工的总工资

3，

select sum(sal)/count(empno) from emp;
     select avg(sal) from emp;

#统计该公司的平均工资

4，select max(sal), min(sal) from emp;

#统计最高工资和 最低工资

数学函数
abs(n)
返回n的绝对值
mod(n,m)
取模运算,返回n被m除的余数(同%操作符)
floor(n)
返回不大于n的最大整数值
ceiling(n)
返回不小于n的最小整数值
round(n,d)
返回n的四舍五入值,保留d位小数(d的默认值为0)
truncate(n,d)
保留数字n的d位小数并返回
pow(x,y)
power(x,y)
　返回值x的y次幂
sqrt(n)
　返回非负数n的平方根
pi()
　返回圆周率
rand()
rand(n)
返回在范围0到1.0内的随机浮点值(可以使用数字n作为初始值)
计算 2的31次幂

1，select pow(2,31); # 不依赖于任何表 , 不需要写from

但是oracle 数据库需要添加虚表

2，select rand();

获取随机数 0-1

3，select round(rand()*5+5);

获取 5-10 之间的随机数

字符串函数
length(str)
ascii(str)
返回字符串str的第一个字符的ascii值(str是空串时返回0)
concat(str1,str2,…)
把参数连成一个长字符串并返回(任何参数是null时返回null)
substring(str,pos,len)
substring(str from pos for len)
replace(str,from_str,to_str)
用字符串to_str替换字符串str中的子串from_str并返回
trim([[both | leading | trailing] [remstr] from] str)
返回前缀或后缀remstr被删除了的字符串str(位置参数默认both,remstr默认值为空格)

1，select * from emp where length(ename) = 5

#查询员工中 名字字符长度为 5 的所有员工

2，select ename,sal from emp;

3，select concat(ename,'的工资是: ',sal) from emp;

#查询员工的姓名和工资, 以如下格式显示 (XXX的工资是: 0.00)

4，select replace(ename,'A','') from emp;

#把所有员工姓名中 的A字符 全部隐藏

日期函数
now()
sysdate()
current_timestamp()
　以’yyyy-mm-dd hh:mm:ss’或yyyymmddhhmmss格式返回当前日期
时间(根据返回值所处上下文是字符串或数字)
curtime()
current_time()
　以’hh:mm:ss’或hhmmss格式返回当前时间值(根据返回值所处上
下文是字符串或数字)
curdate()
current_date()
　以’yyyy-mm-dd’或yyyymmdd格式返回当前日期值(根据返回值所
处上下文是字符串或数字)
month(date)
返回date中的月份数值
datediff(now(),hiredate) 判断两个日期之间相隔天数 (大日期在前, 小日期在后)

timestampdiff(日月年等参数, 两个日期参数小日期在前, 大日期在后)
判断两个日期之间相隔的 年月日

adddate(时间参数 interval 3 month) 日期累加
last_day() 当前月的最后一天

1，select now();

2 select sysdate() ;

#获取系统当前时间

1，insert into emp (empno,ename,hiredate,sal) values (9527,'孙继斌',now(),6000);

2, delete from emp where empno = 9527;

#插入数据

3，select * from emp where month(hiredate) = 5;

#统计五月份入职的员工

4，select datediff(now(),hiredate) from emp;

#查询所有员工入职的天数

5，select * from emp where datediff(now(),hiredate) > 13000

#查询入职的天数大于 13000 的所有员工

查看100 天之后的日期

select adddate(now(), interval 100 day);

加 100 天

select adddate(now(), interval 3 month);

加 三个 月

select adddate(now(),100);

加 100 天 简写

转换函数

字符串转日期 str_to_date(字符串, 日期格式)
日期转字符串 date_format(date,format)
根据format字符串格式化date值

查询当前系统时间

1，select now();

格式化 日期

2，select date_format(now(),'%Y年%m月%d号 %H:%i:%s');

把字符串转换为日期
3，select str_to_date('2008年08月08号','%Y年%m月%d号')

隐式转换
1，SELECT '5'+3 ; # 结果为 8
2，select * from emp where hiredate > '1981-05-01'

如果 日期的书写格式 为 ‘2019-07-25’ 能够自动转换为 日期格式

系统函数
ifnull(字段名,如果为null 需要替换的值)
UUID() 随机字符串数 绝对不可能重复的随机字符串
MD5() 加密函数

select uuid();给淘宝网订单表添加 随机的字符串订单号

复杂查询
子查询
在查询语句中的WHERE条件子句中，又嵌套了另外一个查询语句

一个语句 查询条件 依赖于另外一条语句的 结果

单行单列
#职位和 SMITH 先生 职位相同的所有员工

分步查询
1- 查询 SMITH 职位

select job from emp where ename = 'SMITH';

2- 查询 职位为 该职位的所有员工

select * from emp 
where job = (select job from emp where ename = 'SMITH') 
 and ename != 'SMITH'

#查询入职时间 早于 JAMES 的 所有员工

3，

select * from emp 
where hiredate < (select hiredate from emp where ename = 'JAMES')

#查询 和SMITH 部门相同的员工

4

，select * from emp 
where deptno = (select deptno from emp where ename = 'SMITH')

单列多行

#查询和SMITH 工作相同 部门也相同的员工

1- 查询SMITH 工作, 部门

select job , deptno from emp where ename = 'SMITH'

2-

select * from emp 
where (job,deptno) = (select job , deptno from emp where ename = 'SMITH')

多行多列

#查询人数最多的部门, 有多少人

1，分组查询出每个部门的人数
select deptno,count(empno)
from emp
group by deptno
2，把以上表的查询结果 当成一张新表来查询
select max(ct)
from (SELECT deptno,COUNT(empno) ct FROM emp GROUP BY deptno ) newtable

作为一个字段来存在
#(基于多表查询) 列出 员工姓名以及其 上司的姓名

1，select e.ename 员工,(select m.ename from emp m where e.mgr = m.empno) 上司 from emp e

分组查询

按照某些字段分组, 按照各个组, 分别统计查询

#查询10 号部门的员工人数

1，select count(empno) from emp where deptno = 10

#查询各个部门的员工人数

#先按照部门分组 每个部门多少人

select deptno,count(empno) from emp group by deptno

注意:

使用group by 分组, 不允许在select 中 随意添加字段

在group by 中出现的字段可以添加
统计函数 可以添加
#统计各种工作种类的员工人数

1，select count(empno) from emp group by job

#统计每个部门的平均工资, 最高/最低工资

2，select deptno, avg(sal),max(sal),min(sal) from emp group by deptno

select deptno
from emp
where sal > 1000
group by deptno
order by hiredate
limit 5
查询步骤
1- from
2- where 筛选
3- group by 按照某字段分组
4- select 列出要查看的字段
5- having 在分组之后执行的 筛选条件
6- order by
7- limit
验证语句的执行顺序

#按照总工资排序所有员工

1，select ename 员工姓名,sal+comm 总工资 from emp order by 总工资

null 不能和 其他值做运算

having

#查询平均工资 大于2000 的部门编号

1- 先获取所有部门的平均工资

2- 查询平均工资 > 2000

1，

select deptno , avg(sal) from emp 
      group by deptno having avg(sal) > 2000

having 和 where的对比

两者都是筛选条件
where 在分组之前执行 having 在分组之后执行
where 中不能出现分组函数(统计函数) , having 可以使用该函数
#查询 平均工资 最高的部门编号

1- 先获取所有部门的平均工资

select deptno , avg(sal)  from emp group by deptno 

2- 查询出最高的平均工资

select max(asl) from (select deptno , avg(sal) asl  from emp group by deptno) nt 

3- 该平均工资的部门

select deptno from emp group by deptno 
having avg(sal) = (select max(asl) from (select deptno , avg(sal) asl  from emp group by deptno) nt )

第二种方式
SELECT deptno ,MAX(nt.asl) FROM (SELECT deptno , AVG(sal) asl FROM emp GROUP BY deptno) nt
多表查询

员工表

部门表

工资等级表

笛卡尔积
假设集合A={a, b}，集合B={0, 1, 2}，

则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

查询两个数据表数据, 查询结果是把所有的可能

通过连接条件避免出现笛卡尔积

多表查询
给数据库表起别名, 便于引用
如果要查询的字段没有重复, 可以不指定表名
#查询员工所有信息,以及该员工工作的部门信息

1，select emp.*, dept.* from emp , dept

#第一种语法格式

select emp.*, dept.* from emp , dept where emp.deptno = dept.deptno;

给数据库表起别名, 便于引用

如果要查询的字段没有重复, 可以不指定表名

select e.*, d.* from emp e, dept d where e.deptno = d.deptno;

#第二种语法格式

select e.*, d.* from emp e join dept d on e.deptno = d.deptno

相当于

select e.*, d.* from emp e inner join dept d on e.deptno = d.deptno

分类

内连接

在表中至少一个匹配时，则返回记录

#给emp 添加一条没有部门信息的员工

1，insert into emp (empno,ename,hiredate,sal) values (10086,'支音',now(),10000);

#查询员工的所有信息以及 该员工的工资等级

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

外链接

左外连接

#从左表中返回所有的记录，即便在右中没有匹配的行

1，select e.*, d.* from emp e left join dept d on e.deptno = d.deptno

右外连接

2，select e.*, d.* from emp e right join dept d on e.deptno = d.deptno

#全外连接(MySql 不支持, Oracle 支持)

3，select e., d. from emp e full join dept d on e.deptno = d.deptno

三表查询案例
#查询员工的所有信息, 和所属部门的所有信息, 和工资等级

select e.*, d.* ,s.grade
         from emp e 
         join dept d on e.deptno = d.deptno 
         join salgrade s on e.sal between s.losal and hisal

select e.*, d.* ,s.grade
         from emp e, dept d, salgrade s
         where e.deptno = d.deptno and e.sal between s.losal and hisal

自连接查询
#获取emp表中 所有 员工的姓名 以及该员工上司的姓名

select e.ename,m.ename
      from emp e, emp m
      where e.mgr = m.empno

select concat(e.ename,'的上司是: ',m.ename)
     from emp e, emp m
     where e.mgr = m.empno

联合查询

两次的查询结果 取并集

#查询20 号部门和30 号部门的所有员工

以前的写法

select * from emp where deptno = 20 or deptno = 30;
select * from emp where deptno in (20,30);
#使用并集

select * from emp where deptno = 20
union
select * from emp where deptno = 30;
#使用并集 模拟 全外连接

select e.*, d.* from emp e left join dept d on e.deptno = d.deptno
union 
select e.*, d.* from emp e right join dept d on e.deptno = d.deptno;