Mysql数据库笔记整理（三）

回顾：
基础查询：
select…from…where…
空值null
a.任何数据类型都可以取空值（插入记录）
b.空值在参与算数运算时结果为空
c.空值参与连接操作（concat）结果为空

空值处理函数
ifnull(d1,d2)

空值判断：
空值不能用等于不等于跟任何数据进行比较
肯定：is null
否定：is not null

in(列表项)：肯定，判断等于任意一项
not in(列表项)：否定，判断不等于所有项
使用not in时列表项中的空值必须去掉

组函数:count/sum/avg/max/min/-avg求平均配合ifnull使用

一、自定义函数（了解）
2019-07-10 10:13:57
//查询当前系统时间
select date_format(now(),’%X-%m-%d %H:%i:%s’);

1）创建不带参数的自定义函数
create function f1() returns varchar(30)
return date_format(now(),’%X-%m-%d %H:%i:%s’);
//使用函数
select f1();
//删除函数
drop function f1;

2）创建带有参数的自定义函数
create function f2(num1 int,num2 int) returns double(4,2)
return (num1+num2)/2;
//使用函数
select f2(6,8);

二、正则表达式
Mysql支持的正则表达式只是正则的一个很小的子集。

//查询员工表中姓名包含’张’的所有记录
select ename from emp_zhang where ename regexp ‘张’;

select ename from emp_zhang where ename like ‘%张%’;

//查询员工表中姓名包含’张’或’郭’的所有记录
select ename from emp_zhang where ename regexp ‘张|郭’;

//插入记录
insert into emp_zhang(empno,ename) values(1015,‘1张三’);
insert into emp_zhang(empno,ename) values(1016,‘2张三’);
insert into emp_zhang(empno,ename) values(1017,‘3张三’);

//查询员工表中姓名包含’1张三’，‘2张三’，'3张三’的所有记录
select ename from emp_zhang where ename regexp ‘[123]张三’;//or匹配
select ename from emp_zhang where ename regexp ‘[1-3]张三’;//范围匹配
select ename from emp_zhang where ename regexp ‘1张三|2张三|3张三’;

insert into emp_zhang(empno,ename) values(1018,‘张三.’);

//查询员工表中姓名包含’.'的所有记录
select ename from emp_zhang where ename regexp ‘\.’;//需要转义

ps：
匹配任意字母：[a-zA-Z]
匹配任意数字：[0-9]

//查询员工表中姓名包含数字的所有记录
select ename from emp_zhang where ename regexp ‘[0-9]’;

ps：匹配多个实例
元字符 描述

• 0或多个
  

• 1或多个
  

？ 0或1个
{n} 指定数量匹配
{n,m} 指定匹配范围
{n,} 不小于指定数量匹配
^ 文本开始
$ 文本结束

//查询员工表中姓名包含1到3的任意数字，后面跟一个’张三’的所有记录
select ename from emp_zhang where ename regexp ‘[0-3]张三{1}’;

insert into emp_zhang(empno,ename) values(1019,’.张三’);

//查询员工表中姓名包含’.'后面跟一个’张三’的所有记录
select ename from emp_zhang where ename regexp ‘\.张三{1}’;

//查询员工表中姓名起始包含’.’、1、2、3的所有记录

select ename from emp_zhang where ename regexp ‘¹’;

三、排序子句
排序：对结果集进行排序（先有结果集再排序）
排序子句：order by 列名(数值、日期)
排序规则：asc 升序（默认的）
desc 降序

//查询信息要求按照薪水由低到高进行排序
select ename,salary from emp_zhang
order by salary asc;//升序 asc可以省略

select ename,salary from emp_zhang
order by salary desc;

select ename,salary from emp_zhang where salary is not null
order by salary desc;
ps：
空值参与排序被看作最小值

//部门号升序，同部门薪水降序，没有部门的不排
select deptno,ename,salary
from emp_zhang
where deptno is not null
order by deptno asc,salary desc;//列名

select deptno d,ename e,salary s
from emp_zhang
where deptno is not null
order by d asc,s desc;//列别名排序

select deptno,ename,salary
from emp_zhang
where deptno is not null
order by 1 asc,2 desc;//数字

执行顺序：
from（检索指定表）->where（记录的过滤）->select（结果集）->order by（排序）

ps：
排序语句的执行是在select之后，因此排序是可以用列名、列别名、表达式、函数，
还可以使用数字（数字表示查询结果集返回字段对应的顺序，第一列用1表示）进行排序。

补充内容：
//根据员工姓名进行排序
//希望按照汉字首字母进行排序
select ename
from emp_zhang
order by convert(ename using gbk);--------------首字母排序

四、分组子句
分组：group by 列名

//查询每个部门的最高薪水和最低薪水，没有部门的不算
select deptno,max(salary),min(salary)
from emp_zhang
where deptno is not null
group by deptno;

//查询每个部门的薪水总和和平均薪水，没有部门的不算
select deptno,sum(salary),avg(ifnull(salary,0))
from emp_zhang
where deptno is not null
group by deptno;

//按照职位进行分组，求每个职位的薪水总、和平均薪水、最高、最低薪水、人数和，没职位不算
select position,sum(salary),avg(ifnull(salary,0)),max(salary),min(salary),count(position)
from emp_zhang
where position is not null
group by position;

ps：
分组查询语句中，select后面的列要么被组函数包围，要么出现group by之后。

having子句：分组之后的再过滤

//查询平均薪水大于5000的部门号和平均薪水，要求没有部门不算
select deptno,avg(ifnull(salary,0))
from emp_zhang
where deptno is not null
group by deptno
having avg(ifnull(salary,0))>5000;

ps：
where行记录的过滤
having分组之后的再过滤

//查询那些职位的人数超过两个人，没职位不算
select position,count()
from emp_zhang
where position is not null
group by position
having count()>2;

//查询那些职位的人数超过两个人，没职位不算,每个职位平均薪水，按照平均薪水降序排列
select position,count(),avg(ifnull(salary,0))
from emp_zhang
where position is not null
group by position
having count()>2
order by avg(ifnull(salary,0)) desc;

总结：基础查询
写法顺序：select-from-where-group by-having-order by

执行顺序：from(检索指定表)-where(记录过滤)-group by(分组)
-having(分组后过滤)-select(结果集)-order by(排序)

select deptno,ename
from emp_zhang
group by deptno;//名字有问题

补充内容：
group_concat( [distinct] 字段[order by 字段 desc] [separator ‘分隔符’])//[]中为可选项

//返回一对多数据的时候，数据在同一行显示，逗号隔开(默认)
select deptno,group_concat(ename)
from emp_zhang
group by deptno;

select deptno,group_concat(ename separator ‘；’)
from emp_zhang
group by deptno;

insert into emp_zhang(empno,ename,position,deptno)
values(1020,‘张张’,‘Analyst’,10);

//查询每个部门中职位（去重）
select deptno,group_concat(distinct position)
from emp_zhang
group by deptno;

//查询每个部门中薪水（排序）
select deptno,group_concat(salary order by salary)
from emp_zhang
group by deptno;

五、子查询（高级查询）-重点
子查询：一条SQL嵌套select查询语句

非关联子查询:嵌套的子查询是独立语句，不依赖主查询

1）查询最高薪水是谁
分析：
//查询最高薪水
select max(salary) from emp_zhang;//99999.99

//根据最高薪水找人
select ename,salary
from emp_zhang
where salary=99999.99;

合并：
select ename,salary
from emp_zhang
where salary=(
select max(salary) from emp_zhang
);

非关联子查询执行过程：[重要]
先执行嵌套子查询（独立）->返回子查询结果作为主查询条件->再去执行主查询

ps：
非关联子查询中，嵌套的子查询是独立语句可以单独执行，只执行一次

2）查询薪水最低的是谁
分布：
//查询薪水最低的
select min(salary) from emp_zhang where salary is not null；
//查询薪水最低的谁
select ename,salary
from emp_zhang
where salary=最低的薪水;
合并：
select ename,salary
from emp_zhang
where salary=(
select min(salary) from emp_zhang where salary is not null
);

3）查询谁的薪水比张无忌高
分布：
//张无忌薪水
select salary from emp_zhang where ename=‘张无忌’;
//根据薪水找人
select ename,salary
from emp_zhang
where salary>张无忌薪水;
合并：
select ename,salary
from emp_zhang
where salary>(
select salary from emp_zhang where ename=‘张无忌’
);

4）查询’研发部’有哪些职位
分布：
//查询’研发部’部门号
select deptno from dept_zhang where dname=‘研发部’;
//根据部门找职位
select deptno,position
from emp_zhang
where deptno=();
合并：
select deptno,position
from emp_zhang
where deptno=(
select deptno from dept_zhang where dname=‘研发部’
);

5）查询谁的薪水比’张无忌’高，如果有多个’张无忌’
insert into emp_zhang values(1021,‘张无忌’,‘Clerk’,8000,600,‘2019-07-10’,1013,null);

//满足大于两个’张无忌’薪水
select ename,salary from emp_zhang where salary>all(
select salary from emp_zhang where ename=‘张无忌’
);//嵌套子查询返回多值时不能直接用大于号比较

//满足大于最大薪水’张无忌’
select ename,salary from emp_zhang where salary>(
select max(salary) from emp_zhang where ename=‘张无忌’
);

6）查询哪些人的薪水比张无忌高，如果有多个’张无忌’
//大于任意一个’张无忌’的薪水
select ename,salary from emp_zhang where salary>any(
select salary from emp_zhang where ename=‘张无忌’
);
//大于最小薪水的’张无忌’
select ename,salary from emp_zhang where salary>(
select min(salary) from emp_zhang where ename=‘张无忌’
);

ps：
比较符选择，是由子查询返回单结果决定的
单值：>,<,=
多值：>all,>any;=any和in

关联子查询:嵌套的子查询不是独立语句，要依赖主查询

7）查询谁和’郭靖’同部门，列出除了’郭靖’之外的员工姓名
select ename,deptno
from emp_zhang
where deptno=(
select deptno from emp_zhang where ename=‘郭靖’
) and ename!=‘郭靖’;

8）查询谁和’郭靖’同部门，列出除了’郭靖’之外的员工姓名,多个’郭靖’
select ename,deptno
from emp_zhang
where deptno=any(
select deptno from emp_zhang where ename=‘郭靖’
) and ename!=‘郭靖’;

select ename,deptno
from emp_zhang
where deptno in(
select deptno from emp_zhang where ename=‘郭靖’
) and ename!=‘郭靖’;

ps：
in等价于=any，两者可以进行互换

9）查询谁是’张三丰’的下属
分析：
满足该员工的leader等于’张三丰’的员工号
select ename,leader
from emp_zhang
where leader=(
select empno from emp_zhang where ename=‘张三丰’
);//返回单值

10）查询每个部门拿最高薪水是谁
分析：
//查询每个部门最高薪是
select deptno,max(salary)
from emp_zhang
where deptno is not null
group by deptno;//多值多列情况
±-------±------------+
| deptno | max(salary) |
±-------±------------+
| 10 | 99999.99 |
| 20 | 6000.00 |
| 30 | 8000.00 |
| 40 | 50000.00 |
±-------±------------+

//根据部门号、薪水找人
select ename,deptno,salary
from emp_zhang
where (deptno,salary)=any(

);

合并：
select ename,deptno,salary
from emp_zhang
where (deptno,salary)=any(
select deptno,max(salary)
from emp_zhang
where deptno is not null
group by deptno
);//要求部门号等于部门号，薪水等于薪水，两者比较规则相同（=）

11）查询哪个部门的人数比30号部门人多
分析：
select deptno,count()
from emp_zhang
where deptno is not null
group by deptno
having count()>(
select count(deptno) from emp_zhang where deptno=30
);

12）查询哪个部门的平均薪水比20号部门薪水高，没有部门不算
select deptno,avg(ifnull(salary,0))
from emp_zhang
where deptno is not null
group by deptno
having avg(ifnull(salary,0))>(
select avg(ifnull(salary,0)) from emp_zhang where deptno=20
);

13）查询员工所在部门的平均薪水大于5000的员工姓名和薪水
员工薪水大于5000的部门：
select deptno
from emp_zhang
where deptno is not null
group by deptno
having avg(ifnull(salary,0))>5000;//结果为多值

主：
select deptno,ename,salary
from emp_zhang
where deptno=any(
select deptno
from emp_zhang
where deptno is not null
group by deptno
having avg(ifnull(salary,0))>5000
);//=any等价于in

14）查询哪些员工的薪水是本部门的平均薪水
//查询每个部门的平均薪水
select deptno,avg(ifnull(salary,0))
from emp_zhang
where deptno is not null
group by deptno;//返回多值多列

//根据部门、薪水等于平均薪水找人
select ename,deptno,salary
from emp_zhang
where (deptno,salary)=any(
select deptno,avg(ifnull(salary,0))
from emp_zhang
where deptno is not null
group by deptno
);//两者比较规则相同（=）

15）查询哪些员工的薪水比本部门的平均薪水低
//分析：
要求部门号等于部门号（=），薪水小于本部门平均薪水（<），
两者比较规则不相同，非关联子查询不能实现的。

ps：非关联子查询
a.清除非关联子查询的执行过程
b.比较符的选择（单值、多值）
c.多值多列的处理（要求比较规则相同）

---

1. 123\. ↩︎