MySQL学习笔记--简单/复杂查询

表dept

表emp

 

一、简单查询

1.查询特定的列

-- 示例：查询出所有员工的编号和姓名
select eid,name from emp;

2.查询所有列

select * from emp;
select eid,name,sex,birth,salary,deptid from emp;

3.给列起别名

 -- 示例：查询出所有员工的编号和姓名，使用别名
 select eid as 编号,name as 姓名 from emp;
 select eid 编号,name 姓名 from emp;

as 关键字可以省略，列名称和别名之间保留空格即可

4.显示不同的记录

-- 示例：查询出都有哪些性别的员工
select distinct sex from emp;
-- 练习：查询出员工都分布在哪些部门
select distinct deptid from emp;

5.查询时执行计算

-- 示例：计算出1+2+3.3*5.9+4.8*7.2
select 1+2+3.3*5.9+4.8*7.2;
-- 练习：查询出所有员工的姓名及其年薪
select name,salary*12 from emp;
-- 练习：练习：查询出所有员工的姓名及其年薪，员工工资每月增长2000，年终奖30000
select name,salary*12+2000*12+30000 年薪 from emp;
select name,(salary+2000)*12+30000 年薪 from emp;

6.查询的结果排序

-- 示例查询出所有的部门，结果按照编号升序排列
select * from dept order by did asc;
-- 示例查询出所有的部门，结果按照编号降序排列
select * from dept order by did desc;
-- 练习：查询所有的员工，结果按照工资降序排列
select * from emp order by salary desc;
-- 练习：查询出所有员工，结果按照年龄从大到小排列（生日从小到大）
select * from emp order by birth asc; 
select * from emp order by birth;
-- 练习：查询出所有员工，结果按照姓名升序排列
select * from emp order by name;
-- 示例：查询出所有的员工，结果按照工资降序排列，如果工资相同按照姓名升序排列
select * from emp order by salary desc,name asc;
-- 练习：查询出所有员工，结果按照性别升序排列，如果性别相同按照年龄从小到大排列
select * from emp order by sex asc,birth desc;

不加排序规则，默认是按照升序排列

asc -> ascendant 升序的

desc -> descendant 降序的

desc -> describe 描述

 7.条件查询

select * from emp where 条件
-- 练习：查询出没有明确部门的员工
select * from emp where deptid is null;
-- 练习:查询出有明确部门的员工
select * from emp where deptid is not null;
-- 练习：查询出工资在8000~10000之间的员工
select * from emp where salary>=8000&&salary<=10000;
select * from emp where salary>=8000 and salary<=10000;
-- 练习： 查询出工资在8000以下或者10000以上的员工
select * from emp where salary<8000 || salary>10000;
select * from emp where salary<8000 or salary>10000;
-- 练习：查询出20号部门或者30号部门的员工
select * from emp where deptid=20 || deptid=30;
select * from emp where deptid=20 or deptid=30;
select * from emp where deptid in(20,30);
-- 练习：查询出不在20号部门并且不再30部门的员工
select * from emp where deptid!=20 && deptid!=30;
select * from emp where deptid!=20 and deptid!=30;
select * from emp where deptid not in(20,30);

is null 查找某一列的值为null的数据

is not null 查找某一列的值部位null的数据

|| or 两个条件满足一个就可以

&& and 两个条件都要满足

in() 满足等于其中的一个值

not in() 满足不等于其中任何一个

 8.模糊条件查询

-- 示例：查询出姓名中含有字母e的员工有哪些
select * from emp where name like '%e%';
-- 练习：查询出姓名中以e结尾的员工
select * from emp where name like '%e';
-- 练习：查询出名字中倒数第二个字符是e的员工
select * from emp where name like '%e_';

% 匹配任意n个字符 n>=0

_ 匹配任意n个字符 n=1

以上两个匹配符必须结合like关键词使用

9.分页查询

查询的结果中有太多数据，一次显示不完可以做成分页显示

需要两个已知条件：当前页码，每页显示的数据数据量

每页开始查询的值 = (当前页码 - 1) * 每页的数据量

分页查询的语法：

select * from emp limit 每页开始查询的值，每页数据量;

-- 练习：假设每页显示5条数据，查询出前4页的数据
-- 第1页
select * from emp limit 0,5;
-- 第2页
select * from emp limit 5,5;
-- 第3页
select * from emp limit 10,5;
-- 第4页
select * from emp limit 15,5;

limit后开始查询的值不能写运算，必须写最终计算后的值

limit后的两个值必须是数值型，不能加引号

 二、复杂查询

1.聚合查询/分组查询

聚合函数：count()、sum()、 avg()、max()、 min()

-- 示例：查询出所有员工的数量
select count(*) from emp;-- 19
-- 练习：使用员工的编号查询出员工的数量
select count(eid) from emp;-- 19
-- 练习：使用员工所属的部门编号查询出员工的数量
select count(deptid) from emp;-- 18
-- 练习：查询出10号部门的工资总和
select sum(salary) from emp where deptid=10;
-- 练习：查询出所有女员工的平均工资
select avg(salary) from emp where sex=0;
-- 练习：查询出20号部门的最高工资
select max(salary) from emp where deptid=20;
-- 练习：查询出年龄最大的男员工的生日是多少
select min(birth) from emp where sex=1;

其他函数：

获取日期中的年份 year()

-- 示例：获取日期2023-5-7的年份部分
select year('2023-5-7');
-- 练习：查询所有员工出身的年份
select year(birth) from emp;
-- 练习：查询出1993年出生的员工有哪些
select * from emp where year(birth)=1993;

加密函数 md5()

-- 对一个值进行加密
select md5('123456');

2.分组查询

-- 示例：查询出男女员工的数量、最高工资、最低工资分别是多少
select count(*),max(salary),min(salary),sex from emp group by sex;

分组查询通常只是用于查询聚合函数和分组条件

 3.子查询

-- 示例：查询出工资最高的员工
-- 步骤1：查询出工资的最高值
select max(salary) from emp;-- 22000
-- 步骤2：通过最高值查询出对应的员工
select * from emp where salary=22000；
-- 综合：
select * from emp where salary=(select max(salary) from emp);

4.多表查询

要查询的列分布在多个表中，前提是数据表已经建立了关联

-- 示例：查询出所有员工的姓名及其部门名称
select name,dname from emp,dept where deptid=did;
-- 防止不同表的列名称出现冲突，给列名称前添加表名称
select emp.name,dept.dname from emp,dept where emp.deptid=dept.did;

-- 为了确保显示所有的员工，新增多表查询的方式（左外连接）
select emp.name,dept.dname from emp left join dept on emp.deptid=dept.did;