mysql查询语句——select语句——DQL语句

DQL语言（查询）

1. 基础查询

基础查询导图

select

• 查询表中的单个字段
  select 待查字段 from 表名;（select和from中间也可以不换行）

  1. 查询列表可以是：表中的字段、常量值、表达式、函数
  2. 查询的表格是一个虚拟的表格，没有保存一个临时的表格

• 查询表中的多个字段
  select 待查字段1,待查字段2,待查字段3 from 表名;
  用，隔开

• 查询所有字段
  select * from 表名
  *表示左右，也可以逐个的罗列， *不能改变顺序，但是逐个罗列可以按自己想要的顺序来

• 查询字段可以字段来区别于关键字

• 查询常量值
  select 100; :查询100，不过字段名就默认为100

  • 查询字符型常量值（mysql中不区分字符和字符串,都放在’'中）
    select 'join';
  • 查询表达式：±*/
    select 57*9;

• 查询函数
  相当于调用该函数，查询该函数的返回值
  select version();

• 起别名：as、空格

  select 待查询数据 as 别名
  将查找的数据在表格中列出，字段名不是默认的本身了，而是你起得别名

  select last_name as 姓，first_name as 名 from employee
  多个数据同时查找，同时起别名

  • 方式二使用空格，把as换成一个空格，照样能达到想要的效果，但是不是很直观
  • 注：别名中如果有特殊符号：空格、#，要把别名加上" "

• 去重查找：关键字：distinct
  select distinct 待查内容 from 所在列表

• +的作用
  想要把查询的数据1和数据2连接成一个字段，并显示字段3
  select 数据1+数据2 as 字段3名字
  • MySQL中 +只有一个功能：运算 符，不能作为字符连接，想要连接可以使用拼接函数concat
select 100+90;：两个操作符都是数值型做加法运算
    select '12'+34：如果为 字符串+数字，如果能把字符串转化为数字，则转化为数字后再运算
    select 'cat'+345：若无法转化为数字，将字符串的值设为0，再进行+运算
    select null+100：如果一方为null，那么最终结果肯定也为null ，null+任何东西=null

• 拼接函数concat
select concat('a','b','c');：就会成果查找到abc
  concat()会把其中的数据做拼接

例题

1. 下面的语句是否可以执行成功 （可以）
   select last_name , job_id , salary as sal
from employees;

2. 下面的语句是否可以执行成功 （可以）
   select * from employees;

3. 找出下面语句中的错误
   select employee_id , last_name，（应该为英文，）
salary * 12 “ANNUAL SALARY” （应该是英文的双引号）
from employees;

4. 显示表 departments 的结构，并查询其中的全部数据
   desc departments;
select * from departments;

5. 显示出表 employees 中的全部 job_id（不能重复）
   select distinct job_id from employees;

6. 显示出表 employees 的全部列，各个列之间用逗号连接，列头显示成 OUT_PUT
   select
concat(first_name,',','last_name') as out_put
from employees;

---

2. 条件查询

关键词：where

条件查询导图

语法

• 关键词：where

• select 查询列表 from 表名 where 筛选条件

• 执行循序：

  1. 先在库中找表名。
  2. 筛选满足要求的。
  3. 查询

• 筛选条件分类：

  1. 条件表达式筛序
     '>、<、=、<>不等、>=、<=
  2. 逻辑表达式筛序
     &&、||、!
     逻辑运算符就是连接条件表达式
  3. 模糊查询
     like、between and 、in、is null、is not null

条件查询

1. 查询工资大于 12000 的员工姓名和工资
   select * from employees where salary>1200;
2. 查询员工号为 176 的员工的姓名和部门号和年薪
   select last_name,department_id from employees where department_id<>90;

逻辑查询

1. 选择工资不在 5000 到 12000 的员工的姓名和工资
   select last_name,salary,commission_pct where salary>=5000 and salary<=12000;
   两句等价
   select last_name,salary,commission_pct where salary between 5000 and 12000;

2.1模糊查询

like

• 一般和通配符搭配在一起使用
• 通配符：
  • % ：任意多个字符，包含0个
  • _ ：任意单个字符
  • 如果要查找%或_字符的时候可以转义：\% 、\_

例题

1. 选择员工姓名的第三个字母是 a 的员工姓名
   select last_name from employees where last_name like '__a%';
2. 选择姓名中有字母 a 和 e 的员工姓名
   select last_name from employees where last_name like '%a%' and '%e%';
3. 选择员工姓名的第二个字母是_的员工姓名
   select last_name from employees where last_name like '_\_%'

between and

• between and可以使挑选到某一个区间的值表达的更加精简
• between a and b等价于id >=a and id <= b，a,b两个临界值不能颠倒

1. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
   select * from employees where employees_id >=80 and employees_id <= 100;
   会多些一些重复的东西，用between and可以改为
   select * from employees where employees_id between 80 and 100;

in

1. 查询员工的工种编号是 IT,AD,MS中的一个员工名和工种编号
   select last_name,job_id from employees where job_id=‘TI’ OR job_id=‘AD’ OR job_id=‘MS’;
   会多些一些重复的东西，用in可以改为
   select last_name,job_id from employees where job_id IN (‘TI’,‘AD’,‘MS’);

is NULL

1. 查询没有奖金的员工名和奖金率
   select last_name,commission_pct from employees where commission_pct = NULL;
   这种写法是错误的因为**=能判断NULL值**
   select last_name,commission_pct from employees where commission_pct is null;

<=> 安全等于

• 用于判断是否等于

1. 查询工资为12000的员工信息
   select last_name,salary from employees where salary<=>1200;

   	普通类型的值	NULL值	可读性
   is null	×	√	强
   <=>	√	√	差

---

3. 排序查询

排序查询导图

格式

• order by 排序列表 [asc/desc]（升序、降序）
  select [查询列表] from [表[] where [筛选条件] order by 排序列表 [asc/desc]（升序、降序）
• 特点：
  • asc：升序，desc：降序，如果不写默认是升序
  • order by 子句支持单个字段、多的字段、表达式、函数、别名
  • order by 子句一般放在查询语句的最后面

例题

1. 查询员工信息要求工资从高到低排序
   select * from employess order by salary desc ;

2. 查询部门编号>=90的员工信息按入职时间的先后进行排序。
   select * from employees where department_id>=90 order by hiredate asc;

3. 按年薪高低显示员工的信息和年薪,按表达式排序。
   select * ,salary * 12 *(1+ifnull(commission_pct,0))
from emplouees
order by salary * 12 *(1+ifnull(commission_pct,0)) desc;

4. 按姓名的长度显示员工的姓名和工资,按函数排序。
   select length(last_name) as 字节长度 , last_name , salary
form employees
order by lenath(last_name) desc;

5. 按年薪的高低显示员工的信息和年薪,按别名排序。
   select * , salary * 12 *(1+ifnull(commission_pct,0)) as 年薪
form employees
order by length(last_name) desc

6. 查询员工信息要求先按工资排序，再按员工编号排序，(按多个字段排序)
   select * from employees
order by salary asc , employees_id desc;

7. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
   select * , salary * 12 *(1+ifnull(commission_pct,0)) as 年薪
from employees
order by 年薪 desc, last_name asc ;

8. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序
   select last_name,salary
form employess
where salary nor between 8000 ans 17000
order by salary desc;

9. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
   select * from employees
where email like '%e%'
order by length(email desc, department_id asc);

4. 聚合函数

聚合函数导图

就相当于C语言的函数
好处；

1. 隐藏了实现细节 2. 提高了代码的重用性

分类：

1. 单行函数：const、length、ifnull等
2. 分组函数：
   • 字符函数
   • 日期函数
   • 数组函数
   • 其他函数
   • 流程控制函数

1 字符函数

1. length：获取参数值的字节个数
   select length('join'); 结果为4
   select length('数值为678'); 结果为12，因为一个字母占1个字节，一个汉字占3个字节

2. concat：拼接字符串
   select concat(last_name,'_',firts_name) from employees;
   会把所用last_name和firts_name拼接成last_name_firts_name的形式

3. upper：改大写、lower：改小写
   select upper('home'); 会把home变成HOME

   将姓变大写，名变成小写，然后拼接在一起，并该别名为姓名
   select concat ( upper ( last_name ) , lower ( firts_name ) ) as 姓名 from employees;

4. substr：索引
   注意：索引的从1开始
   select substr ( '一二三四五六七八'，5) as out_put;
   会输出五六七八，会截取从第5开始往后的所有字符。

   select substr('一二三四五六七八'，2,3) as out_put;
   会输出二三四，第1个字符代表截取的位置，第2个字符代表截取的个数。（包含截取位置字符）

案例：姓名中首字母大写(检索第一个然后转大写)，其他字符小写(检索从第二个开始然后转小写)，然后用_拼接显示出来。
select concat(upper(substr(last_name,1,1),'_',lower(substr(last_name,2))) as out_put

5. instr：返回子串，在大的串中的第一次出现的位置，如果没有显示0
   select instr('总在屏幕上打出','屏幕') as out_put;
   会返回2

6. trim：去掉前面和末尾指定的所有字符。
   select trim(' 路人甲 ');
   会删掉路人甲前面和后面的空格。

   去指定的字符
   select trim('a' from 'aaaa路aa人aa甲aaaa')
   会返回：aa人aa甲,只会去掉首尾的a，而不会去掉中间的。

7. lpad：左填充，用指定的字符实现左填充，是填充后的字符达到指定长度。
   select lpad('奔波霸',10,'*') as out_put;
   会输出：*******奔波霸

   select lpad('奔波霸',2,'*') as out_put;
   会输出：奔波，在最后面截断一个，使字符串达到指定长度。

8. rpad：右填充

9. replace： 替换，如果有重复的就都进行替换。
   select replace('路人甲,宋兵乙,炮灰丙','宋兵乙','土匪丁') as out_out;
   会输出路人甲,土匪丁,炮灰丙：用土匪丁 替换 宋兵乙

2.数字函数

1. round 四舍五入，保留结果的小数点位数

   1. 四舍五入
      select round(-1.55);
      结果为 -2，无论是正数还是负数，都可以把负号先去掉，然后进行四舍五入，再加上正负号。

   2. 保留小数点位数
      select round(1.567,2);
      结果为1.56,第2个参数2就是保留小数点后两位小数。

2. ceil 向上取整(返回>=该参数的最小整数)
   ceil(1.002);
   结果依然为2
   ceil(1.00);
   结果就为1。

3. floor 向下取整(返回<=该参数的最小整数)
   floor(1.002);
   结果依然为1
   floor(-9.7);
   结果就为-10。

4. mod 取余
   select mod(10,3);
   结果为1。10 % 3 = 1
   select mod(10,-3);
   结果为1。10 % -3 = 1
   select mod(-10,3);
   结果为1。-10 % 3 = -1

5. truncate 清空表，截断表
   只能用于表，删除表中的全部数据

6. rand 获取随机数
   返回[0-1)之间的随机数

3.日期函数

1. now 返回当前系统日期+时间
   select now();

2. curdate 返回当前系统日期,不包含时间
   select curate();

3. curtime 返回当前时间,不包含日期
   select curtime();

4. 可以获取指定部分时间,年、月、日、小时、分钟、秒
   select year(now()); 年
   select year('1998-1-1') 获得字符串中的年份1998

   获取数据库中某一个项中的年份。
   select year(hiredata) as 年 from employees;

   select mouth(now()) as 月 中文的月
   select mouthname(now()) as 英文月 英文的月

5. str_to_data:将日期格式的字符转换成指定格式的日期
   select str_to_date('1999-3-2','%Y+%c+%d') as out_put;
   会输出显示：1999+03+02

   格式符	功能
   %Y	4位的年份
   %y	两位的年份
   %m	月份(01,02…11,12)
   %c	月份(1,2…11,12)
   %d	日（01-31）
   %H	小时(24小时制)
   %h	小时(12小时制)
   %i	分钟（01-60）
   %s	秒（01-60）

   查询入职日期为1999-4-3的员工信息
   select * from employees where hiredate = '1999-4-3'

6. date_format:将日期转换成字符
   date_format('2018/6/6','%Y年%m月%d日');
select date_format(now(),'%Y年%m月%d日') as out_put;

查询有奖金的员工名和入职时间(xx月/xx日xx年)
select laxt_name,date_format(hiredate,'%m月%d日%Y年') as 入职时间
form employees
where commission_pct

4.其他函数

1. version() 查看版本号
2. database() 查看当前所在那个数据库
3. user() 查看当前用户
4. password(‘字符’) 返回输入字符的密码形式
5. md5(‘字符’) 返回输入字符的md5加密形式

5.流程控制函数

1. if函数: if else的效果相同
   select if(10>5,'大','小') 返回大

2. case :与switch case的效果相同
   句型：
   case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要形式的直n或语句n(当上面都不符合时执行这条默认的语句)
   end

   查询员工的工资要求：部门号=30，显示工资的1.1倍，部门号=40，显示员工号的1.2倍，部门号=50，显示工资的1.3倍。
   case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end;

5. 分组函数

分组函数导图

功能：用作统计使用，又称为聚合函数或统计函数或组函数。
分类：sun求和、avg平均值、max最大值、 min最小值、 count计算个数
语法：select sum(字段名) from 表 ;

特点：

1. 一般用于处理数值类型
2. 以上函数都忽略null值
3. 可以和distinct关键字搭配使用,实现去重除运算。

简单的使用

1. 求一下工资之和
   select sum(salary) from employees;

2. 同理
   select avg(salary) from employees;
select min(salary) from employees;
select max(salary) from employees;
select count(salary) from employees;

3. 可以同时放多个
   select sum(salary) , avg(salary) , min(salary) , max(salary) , count(salary) from employees;

4. 叠加其他函数使用
   select round(sum(salary)) from employees;将工资求和的结果保留两位小数。

5. 参数的支持类型
   一般只支持数字类型,但是放入字符类型也不会报错，而是输出0,但是不提倡放入字符类型。

6. 忽略null值
   比如求和的时候算平均的时候，null值是不参与运算的。(null值加任何数都等于null)

7. 和distinct搭配,去重再运算
   select sum(salary) from employees;所有数求和
   select sum(distinct salary) from employees;去重之后再求和
   其他的也可以像sum这样去重之后再进行运算。

8. count的函数的详细介绍
   统计employees数据库中的所有行数。
   select count(*) from employees;
   count(*)就表示统计整个数据库

select count(1) from employees;
该语句也会输出和count(*)同样的值是统计数据库的行数，count(1)就表示在数据库的每一行前面都加上1个2，然后统计1的个数，也就是统计数据库的函数。把1换成其他的常量或字符串同理。

myisam存储引擎下,count()的效率最高。
innodb存储引擎下,count()和count(1)的效率差不多，比count(字符)要高一些。
总之用count(*)为最好

例题

1. 查询员工表中，最大入职时间和最小入职时间相差天数。
   减法函数：datediff(a,b)表示a-b
   select datediff(max(hiredate),min(hiredate)) as date from empployees ;

2. 查询一下部门编号，为90的员工个数。
   select count(*) from employees where department_id =90 ;

6. 分组查询

分组查询导图

• group by 分组语法
  select 分组函数，列（要求出现在group by的后面）
from 表
[where 筛选条件] 举例选填
  group by 分组的列表（也就是按什么条件分组）
  order by 子句 举例选填
  注意：查寻列比较特殊，要求分组函数和group by后面出现的字段

• 分组查询的筛选分两类

  	数据源	位置	关键字
  分组前筛选	原始表	group by子句的前面	where
  分组后筛选	分组后的结果表	group by子句的后面	having

  1. 分组函数做条件肯定放在having子句中
  2. 能用分组前筛选的，就优先考虑使用分组前筛选

• group by语句支持单个字段分组，多个字段分组（每个字段用逗号隔开，没有顺序要求），表达式或函数

• 也可以添加排序

1. 查询每个工种的最高工资
   select max(salary),job_id
from employees
group by job_id;

2. 查询每个位置上的部门个数
   select count(*),location_id
from departments
group by location_id;

3. 查询邮箱中包含A字符的，每个部门的平均工资（添加了筛选条件）
   select AVG(salary),department_id
from employees
where email like '%A%'
group by department_id

4. 查询有奖金的，每个领导手下的最高工资
   select max(salary),manager_id
from employees
where commission_pect is not null
group by manager_id

添加了比较复杂的筛选条件

1. 查询每个部门的员工个数>2
   select count(*) ,department_id (查询每个部门的员工个数)
   from employees
where count(*)>2可不能这样进行筛选，因为from employees是针对employees进行筛选，而employees中没有count(*)这个字段，所以这条语句是错误的
   group by department_id
having count(*)>2; 应该在分组后在进行筛选

2. 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

   1. 先查询每个工种有奖金的员工的最高工资
   2. 在根据1个查询结果，筛选出最高工资>12000
      select max(salary),job_id
from employees
where commission_pect is not null
group by job_id
having max(salary)>12000;

按表达式或函数分组

1. 按员工姓名长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
   1. 查询每个长度的员工个数
      select count(*),length(last_name) as len_name
form employees
group by length(last_name)
   2. 添加筛选条件员工个数>5
      having count(*)>5;
   • group by和having都支持别名

按多个字段分组

1. 查询每个部门，每个工种的员工的平均工资
   select avg(salary),department_id,job_id
from employees
group by job_id,department_id;调换两个前后顺序也相同

添加排序

1. 查询每个部门每个公众的员工的平均工资，并按平均工资从高到低显示
   select avg(salary) as a , department_id, job_id
from employees
where department_id is not null
group by job_id,department_id
having a>10000
order by a desc

7. 连接查询（多表查询）

又称多表查询，当查询的字段涉及多个表的时候，就要用到连接查询

分类：

为表起别名：

• 提高语句的简洁度
• 区分多个重名字段
• 注意：如果为表起了别名，则查询的字段就不能使用原来的别名去限定

内连接

查询A、B 交集部分数据

语法：

1. 隐式内连接
   select 字段列表 from 表1，表2 where 筛选条件 ;

2. 显式内连接
   select 字段列表 from 表1 【inner】 join 表2 on 连接条件 ... ;

• 例题：

查询每一个员工的姓名 , 及关联的部门的名称
表结构: emp , dept
连接条件: emp.dept_id = dept.id

• 隐式内连接实现

select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;

若果有员工没有部门，则不会显示

• 显示内连接实现

select e.name, d.name 
from emp as e 
join dept as d 
on e.dept_id = d.id;

外连接

左右外连接

1. 左外连接

左外连接相当于查询表A(左表)的所有数据和中间绿色的交集部分的数据。
表1的位置为左表，表2的位置为右表

select  字段列表  
from  表1  
left 【outer】 join  表2  
on  条件...

2. 右外连接
   右外连接相当于查询表B(右表)的所有数据和中间绿色的交集部分的数据。
   表1的位置为左表，表2的位置为右表

select  字段列表  
from  表1  
right 【outer】 join  表2  
on  条件...

• 想把右外连接改成左外连接，并且查询结果不改变，可以把right改为left，并且把表1和表2的位置调换

• 例题：

1. 查询emp表的所有数据, 和对应的部门信息
   由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，因为有一些中间数据查询不到，需要考虑使用外连接查询。
   表结构: emp, dept
   连接条件: emp.dept_id = dept.id
   （左外连接）

select e.*, d.name  
from  emp as e
left outer join dept as d
on e.dept_id = d.id

2. 查询dept表的所有数据, 和对应的员工信息

表结构: emp, dept
连接条件: emp.dept_id = dept.id
(右外连接)

select d.*, e.* 
from emp e  # 左表
right outer join dept as d  # 右表 
on e.dept_id = d.id;

将右外改为左外

select d.*, e.* 
from dept as d 
left outer join emp as e 
on e.dept_id = d.id;

自连接

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。

• 语法：

select  字段列表  
from  表1  as 别名1  
join 表1  as 别名2  
on 条件....

注意：自连接表一定要起别名

对于自连接查询，可以是内连接查询，也可以是外连接查询。

• 例题： 1. 查询员工 及其 所属领导的名字，

普通员工和领导其实都属于员工，都在员工表当中，每个员工又有一项manager_id记录这他的领导的id值
用内连接

select a.name , b.name 
from emp as a , emp as b 
where a.managerid = b.id;

• 例题2. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
  表结构: emp a , emp b
  用外连接

select a.name as '员工', b.name as '领导' 
from emp as a 
left join emp as b 
on a.managerid = b.id;

3. 当要查询girl表中的g_name和boy表中的b_name，girl表中的boyfriend_id与boy表中的id是对应的
   select g_name,b_name from boy,bearty
where girl.boufriend_id = boy.id
   如果不加where筛选条件的话，会显示笛卡尔乘机的效果，出现许多的无用项

会用第一个表中的每一行和第二个表中的每一行进行逐个匹配，然后进行筛选，如果匹配会筛选出来
可以自由调换表的顺序

2. 查询员工名和对应的部门名，员工名在employees表中，部门名在department表中
   select last_name,department_name
from employees,department
where employees.'department_id',department.'department_id'

---

SQL92语法

等值连接

为表起别名

当语句中经常出表名作用域的时候，每条语句就会很长，为精简语句

1. 查询员工名，工种号，工种名
   select e.last_name,e.job_id,j.job_title
from employees as e , job as j
where e.'job_id'= j.job_id

加筛选条件

1. 查询有奖金的员工名、部门名
   select last_name,department_name
from employees as e , department as d
where e.'department_id' = d.'department_id'
and e.'commission_pct'is not null 已经有一个where筛选了不能再用where，用and

2. 查询城市名中第二个字符为o的部门名和城市名
   select department_name,city
from department as d,location as l
where d.'location_id' = l.'location_id'
and city like '_o%'

加分组

1. 查询每个城市的部门个数
   select count(*) as 个数 , city
from department as d = location as l
group by city

2. 查询有奖金的每个部门的部门名，和部门的领导编号和该部门的最低工资标准
   select department_name , manager_id,min(salary)
from employees as e , department as d
where e.'department_id' = d.'department_id'
and commission_pct is not null
group by department_name,d,manager_id ;

加排序

1. 查询每个工种的工种名和员工的个数，并按员工个数降序
   select job_title , count(*)
from employees as e , job as j
where e.'job_id'= j.job_id
group by job_title
order by count(*) desc

实现三表连接

1. 查询员工名，部门名，和所用城市名
   select last_name,department_name,city
from employees as e , department as d , location as l
where e.'department_id' = d.'department_id'
and d.'location_id' = l.'location_id'

非等值连接

也就是把上面的等于号换成了不等于（大于、小于、不等）

1. 查询员工的工资和工资级别
   select salary,grade_level
from employees e,job_frades g
where salary between g.'lowest_sal' and g.'higthest_sal'
   追加其他条件。
   and g.'lowest_sal'='A';只查看等级为A的

自连接

把原来这一张表当做多张表来使用，由表中的数据找到另一个数据，再由找到的数据回过头来找另一个数据，可以这样往复下去

1. 查询员工名，和他对应的上级名，每一个有员工对应的上级编号
   select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees.e,employees.me代表员工表，m代表领导表。其实都在一张表里，重命名来避免冲突
   where e.'manager_id' = m.'employees_id'

SQL99语法

语法：
select 查寻列表
from 表1 as 别名 【连接类型】
join 表2 as 别名
on【连接条件】
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

内连接

1.等值连接

• 特点
  1. 可以添加排序、分组、筛选
  2. inner可以省略
  3. 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读。
  4. inner join连接和SQL192语法中的等值连接效果是一样的，都是查询多表的交集。

1. 查询员工名，部门名
   select last_name,department_name
from department d
innner join demployees as e
on e.'department_id' = d.'department_id'

2. 查询名字中包含e的员工名和工种名(添加了筛选条件)
   select last_name,job_id
from employees as e
inner join as j
on e.'job_id' = j.'job_id'
where e.'last_name' like '%e%' ;

3. 查询部门个数>3的城市名和部门个数（添加分组、筛选条件）
   select city,count(*) as 部门个数
from departments as d
inner hoin locations as l
on d.'location_id' = l.'location_id'
group by city
having count(*)>3

4. 查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序
   select count(*) , department_id
from employees as e
inner hoin departments as d
on e.'department_id' = d.'department_id'
group by department_name
having count(*)>3
order by count(*) desc

1.非等值连接

1. 查询员工的工资级别
   select salary,grade_level
from employees as e
join job_grades as g
on e.'salary' between g.'lowest_sal' and g.'hightest_sal' ;

2. 查询每个工资级别的个数，并按照级别降序。
   select count(*),grade_level
from employees as e
join job_grades as g
on e.'salary' between g.'lowest_sal' and g.'hightest_sal'
having count(*)>20
order by grade_lecel desc

自连接

把原来这一张表当做多张表来使用，由表中的数据找到另一个数据，再由找到的数据回过头来找另一个数据，可以这样往复下去

1. 查询员工名，和他对应的上级名 (每一个有员工对应的上级编号)
   select m.employee_id,m.last_name
from employees as e e代表员工表，m代表领导表。其实都在一张表里，重命名来避免冲突
   join employees as m
on e.'manager_id' = m.'employees_id'

外连接

用于查询主表的时候，主表中没有，但是附表中，主表通过外连接附表来查询数据
特点：

1. 外连接查询结果为主表中的所有数据。
   如果主表中有与之匹配的显示匹配值。
   表中没有与之匹配的显示null
   外连接查询结果=内连接结果+主表中有而从表中没有的数据

2. 左外连接，left join左边的是主表
   右外连接,right join右边的是主表。

3. 左外和右外交换两个表的顺序可以实现同样的效果。

4. 查询男朋友 不在男神表的的女神名

   左外连接
   SELECT b.*,bo.*
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.'boyfriend_id' = bo.'id'
WHERE b.'id' IS NULL;

5. 查询哪个部门没有员工

   1. 左外
      SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.'department_id' = e.'department_id'
WHERE e.'employee_id' IS NULL;
   2. 右外
      SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.'department_id' = e.'department_id'
WHERE e.'employee_id' IS NULL;

全外
USE girls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.'boyfriend_id' = bo.id;

交叉连接

SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

sql92和 sql99 pk

功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高

8. 子查询（嵌套查询）

出现在其他语句中的select语句被称为子查询

按位置分类：

• select后面
• from后面
• where或having后面
• exists后面

按结果级的行数不同分类：

• 标量子查询 (结果集有一行一列)
• 列子查询 (结果集只有一列多行)
• 行子查询 (结果集只有一行多列)
• 表子查询 (结果集一般为多行多列)

where或having后面
标量子查询
列子查询
行子查询
特点：

1. 子查询都放在小括号内，结尾不用加 ;
2. 子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3. 子查询优先于主查询执行，主查询使用了子查询的执行结果

子查询根据查询结果的行数不同分为以下两类：单行子查询和多行子查询

1. 单行子查询
   结果集只有一行
   一般搭配单行操作符使用：> < = <> >= <=
   非法使用子查询的情况：

   1. 子查询的结果为一组值
   2. 子查询的结果为空

2. 多行子查询
   结果集有多行
   一般搭配多行操作符使用：any、all、in、not in
   in：属于子查询结果中的任意一个就行
   any和all:往往可以用其他查询代替

标准子查询

1. 谁的工资比abel高?
   1查询Abel的工资
   SELECT salary
FROM employees
WHERE last_name = 'Abel'

   2查询员工的信息，满足 salary>1结果
   SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
   把1的结果放在2的小括号内,括号内就是子查询。

自查行中用到分组函数。
2. 返回公司工资最少的员工的last_name,job_id和salary

1. 查询公司的 最低工资
   SELECT MIN(salary)
FROM employees

2. 查询last_name,job_id和salary，要求 salary=1
   SELECT last_name,job_id,salary
FROM employees
WHERE salary=
(
SELECT MIN(salary)
FROM employees
);

一个查询里面可以放两个子查询。
3. 返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资

SELECT last_name,job_id,salary
FROM employees
WHERE job_id =
(
SELECT job_id
FROM employees
WHERE employee_id = 141
) 查询141号员工的job_id
AND salary>
(
SELECT salary
FROM employees
WHERE employee_id = 143
);查询143号员工的salary

4. 查询最低工资大于50号部门最低工资的部门id和其最低工资

   1. 查询50号部门的最低工资
      SELECT MIN(salary)
FROM employees
WHERE department_id = 50

   2. 查询每个部门的最低工资

      SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id

   3. 在②基础上筛选，满足min(salary)>①
      SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>
(
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);

列子查询（多行子查询）

• 返回多行

• 使用多行比较操作符

  操作符	含义
  in/out in	等于列表中的任意一个
  not in	不在指定的集合范围内
  any/some	和子查询返回的某个值比较，有任意一个满足即可
  all	和子查询返回的所有值比较，必须全部满足

1. 案例: 查询 “销售部” 和 “市场部” 的所有员工信息(in)

分解为以下两步:

①. 查询 "销售部" 和 "市场部" 的部门ID
select id from dept where name = '销售部' or name = '市场部';

②. 根据部门ID, 查询员工信息
select * from emp 
where dept_id in 
(select id from dept where name = '销售部' or name = '市场部');

2. 查询比 财务部 所有人工资都高的员工信息（all）

# ①. 查询所有 财务部 人员工资
select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');

# ②. 比 财务部 所有人工资都高的员工信息
select * from emp 
where salary > all 
( select salary from emp where dept_id =(select id from dept where name = '财务部') );

3. 查询比研发部其中任意一人(比最小值高)工资高的员工信息(any)
   分解为以下两步:

①. 查询研发部所有人工资
select salary from emp where dept_id = (select id from dept where name = '研发部');

②. 比研发部其中任意一人工资高的员工信息
select * from emp 
where salary > any 
( select salary from emp where dept_id =(select id from dept where name = '研发部') );

3. 返回location_id是1400或1700的部门中的所有员工姓名

   1. 查询location_id是1400或1700的部门编号
      SELECT distinct department_id
FROM departments
WHERE location_id IN(1400,1700)

   2. 查询员工姓名，要求部门号是①列表中的某一个
      SELECT last_name
FROM employees
WHERE department_id in
(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);

4. 返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary

   1. 查询job_id为‘IT_PROG’部门任一工资
      SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

   2. 查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
      SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < ANY
(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

      ) AND job_id <> 'IT_PROG';
      或(< any等价于< min)
      SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary <
(
SELECT min(salary)
FROM employees
WHERE job_id = 'IT_PROG'

      ) AND job_id<>'IT_PROG';

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN

A. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;
这个需求同样可以拆解为两步进行:

①. 查询 "张无忌" 的薪资及直属领导
select salary, managerid from emp where name = '张无忌';

②. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select * from emp 
where (salary,managerid) = (select salary, managerid from empwhere name = '张无忌');

查询员工编号最小并且工资最高的员工信息

1. 查询最小的员工编号
   SELECT MIN(employee_id)
FROM employees

2. 查询最高工资
   SELECT MAX(salary)
FROM employees

3. 查询员工信息
   SELECT *
FROM employees
WHERE employee_id=
(
SELECT MIN(employee_id)
FROM employees
)
AND salary=
(
SELECT MAX(salary)
FROM employees
);

常规查询分为以上三步，行查询可转化为：
SELECT * FROM employees
WHERE (employee_id,salary)=
(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);

select后面的子查询

• 仅仅支持标量子查询

1. 查询员工号=102的部门名
   SELECT
(
SELECT department_name,e.department_id
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102
) as 部门名;

from后面的子查询

• 将子查询结果充当一张表，要求必须起别名

1. 查询每个部门的平均工资的工资等级

   1. 查询每个部门的平均工资
      SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
      然后再
      SELECT * FROM job_grades;
      显示查询结果

   2. 连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*,g.'grade_level'
FROM
(
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) as ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

exists后面（相关子查询）

语法：
exists(完整的查询语句)
结果：1或0

SELECT EXISTS(SELECT employee_id FROM employees);
结果为0。只关心他括号里的子查询有没有，有就为1

1. 查询有员工的部门名
   • 用in
     SELECT department_name
FROM departments d
WHERE d.'department_id' IN
(
SELECT department_id
FROM employees
);
   • 用exists
     SELECT department_name
FROM departments d
WHERE EXISTS
(
SELECT *
FROM employees e
WHERE d.'department_id'=e.'department_id'
);

9. 分页查询

• 应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
• 语法：
  select 查询列表
from 表
【join type join 表2】
【on 连接条件】
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序的字段】
limit offset,size;
  • 【中为可选字段】
  • offset:要显示条目的起始索引（起始索引从0开始）
  • size:要显示的条目个数
• 特点：
  1. limit语句放在查询语句的最后
  2. 公式:
     要显示的页数 page，每页的条目数size
     select 查询列表 from 表
limit (page-1)*size,size;公式

1. 查询前五条员工信息
   SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;

2. 查询第11条到第25条
   SELECT * FROM employees LIMIT 10,15;
   起始条是第10条，共15条

3. 有奖金的员工信息，并且工资较高的前10名显示出来
   SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10 ;

10. union联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

select 字段列表 from 表1 ...
union 【all】
select 字段列表 from 表2 ...

• 对于联合查询的多张表的列数和字段类型都需要保持一致。
• union all 会将全部的数据直接合并在一起
• union 会对合并之后的数据去重。

• 例题： 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来

select * from emp where salary < 5000
union all
select * from emp where age > 50;
// 不回去重