1204、基础查询进阶、连接查询

一、基础查询进阶

1、常用函数

**什么是函数：**MySQL服务内置的命令

语法：函数名(字段名)

通常使用的方法： 函数名(表头名)

也可以单独使用： 函数名()

或者 函数里嵌套函数： 函数(函数() )

**示例：**在select 命令里使用函数做查询的格式

第一种格式
SELECT  函数(表头名)  FROM  库名.表名；

第二种格式
SELECT  函数(函数())  ；

第三种格式：单独把函数的执行结果输出
SELECT  函数() ;

1.1 字符函数

作用：处理字符或字符类型的字段

函数（）	说明
LENGTH(str)	返回字符串长度，以字节为单位
CHAR_LENGTH(str)	返回字符串长度，以字符为单位
UPPER(str)和UCASE(str)	将字符串中的字母全部转换成大写
LOWER(str)和LCASE(str)	将str中的字母全部转换成小写
SUBSTR(s, start,end)	从s的start位置开始取出到end长度的子串
INSTR(str,str1)	返回str1参数，在str参数内的位置
TRIM(s)	返回字符串s删除了两边空格之后的字符串

例子：
mysql> select   name  from  tarena.user where name="root";
+------+
| name |
+------+
| root |
+------+
1 row in set (0.00 sec)

mysql> select  char_length(name)  from  tarena.user where name="root";
+-------------------+
| char_length(name) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.00 sec)

1.2 数学函数

作用:处理数据或数值类型的字段

函数（）	说明
ABS(x)	返回x的绝对值
PI()	返回圆周率π，默认显示6位小数
MOD(x,y)	返回x被y除后的余数
CEIL(x)、CEILING(x)	返回不小于x的最小整数 (x 是小数)
FLOOR(x）	返回不大于x的最大整数 (x 是小数)
ROUND(x)	返回最接近于x的整数，即对x进行四舍五入 (x 是小数)
ROUND(x,y)	返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位 (x 是小数)

mysql> select mod(10,3) as 余数;
+--------+
| 余数   |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> select id,name from tarena.user where mod(id,2)=0;
+----+-----------------+
| id | name            |
+----+-----------------+
|  2 | bin             |
|  4 | adm             |
|  6 | sync            |
|  8 | halt            |
| 10 | operator        |
| 12 | ftp             |
| 14 | systemd-network |
| 16 | polkitd         |
| 18 | postfix         |
| 20 | rpc             |
| 22 | nfsnobody       |
| 28 | NULL            |
| 72 | null            |
| 74 | NULL            |
| 76 | 6yaya           |
| 78 | yay8a           |
+----+-----------------+
16 rows in set (0.00 sec)

1.3 聚集函数

作用：数据统计命令 ，输出的值只有1个

函数（）	说明
avg(字段名)	计算平均值
sum(字段名)	求和
min(字段名)	获取最小值
max(字段名)	获取最大值
count(字段名)	统计字段值个数

mysql> select min(uid) from tarena.user;
+----------+
| min(uid) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)


mysql>select min(uid) from tarena.user where shell="/sbin/nologin";
+----------+
| min(uid) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)


mysql> select min(uid),max(uid) from tarena.user;
+----------+----------+
| min(uid) | max(uid) |
+----------+----------+
|        0 |    65534 |
+----------+----------+
1 row in set (0.00 sec)


mysql> select name,sum(uid+gid) from tarena.user where name="ftp";
+------+--------------+
| name | sum(uid+gid) |
+------+--------------+
| ftp  |           64 |
+------+--------------+
1 row in set (0.00 sec)

1.4 日期函数

函数	说明	函数	说明
curtime( )	获取时间	hour( )	获取小时
curdate( )	获取日期	minute( )	获取分钟
now( )	获取日期和时间	second( )	获取秒
year( )	获取年	quarter( )	获取一年中第几季度
month( )	获取月	monthname( )	获取月份名称
day( )/week( )	获取日/一年中的第几周	dayname( )	获取日期对应的星期名
day( )/weekday( )	获取日期/一周中的周几	dayofyear( )	获取一年中的第几天
time( )	获取时间	dayofmonth( )	获取一月中的第几天

mysql> system date
2022年 06月 11日 星期六 16:19:09 CST
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-06-11 16:19:20 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2022-06-11 |
+------------+
1 row in set (0.01 sec)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 16:20:00  |
+-----------+
1 row in set (0.00 sec)

mysql> select year(20191224);
+----------------+
| year(20191224) |
+----------------+
|           2019 |
+----------------+
1 row in set (0.00 sec)

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2022 |
+-------------+
1 row in set (0.00 sec)





mysql> desc tarena.employees;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| employee_id  | int(6)      | NO   | PRI | NULL    | auto_increment |
| name         | varchar(10) | YES  |     | NULL    |                |
| hire_date    | date        | YES  |     | NULL    |                |
| birth_date   | date        | YES  |     | NULL    |                |
| email        | varchar(25) | YES  |     | NULL    |                |
| phone_number | char(11)    | YES  |     | NULL    |                |
| dept_id      | int(4)      | YES  | MUL | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> select hire_date from tarena.employees where employee_id=10;
+------------+
| hire_date  |
+------------+
| 2018-09-03 |
+------------+
1 row in set (0.00 sec)

mysql> select year(hire_date) from tarena.employees where employee_id=10;
+-----------------+
| year(hire_date) |
+-----------------+
|            2018 |
+-----------------+
1 row in set (0.00 sec)

mysql> select month(hire_date) from tarena.employees where employee_id=10;
+------------------+
| month(hire_date) |
+------------------+
|                9 |
+------------------+
1 row in set (0.00 sec)

mysql> select day(hire_date) from tarena.employees where employee_id=10;
+----------------+
| day(hire_date) |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

1.5 数学计算的使用 + - * / %

符号两边的 字段的是数值类型的

符号	用途	例子
+	加法	uid + gid
-	减法	uid - gid
*****	乘法	uid * gid
/	除法	uid / gid
%	取余数（求模）	uid % gid
( )	提高优先级	(uid + gid) / 2

例子：

----------------------1.把前5行 用户的uid号分别加1------------------------------
# 先把前5行用户的uid号查出来
mysql> select  id , name , uid  from   tarena.user where  id <= 5;
+----+--------+------+
| id | name   | uid  |
+----+--------+------+
|  1 | root   |    0 |
|  2 | bin    |    1 |
|  3 | daemon |    2 |
|  4 | adm    |    3 |
|  5 | lp     |    4 |
+----+--------+------+
5 rows in set (0.00 sec)

# 修改
mysql> update  tarena.user set uid=uid+1 where  id <= 5;	  
 
# 修改后查看
mysql>  select  id , name , uid  from   tarena.user where  id <= 5;
+----+--------+------+
| id | name   | uid  |
+----+--------+------+
|  1 | root   |    1 |
|  2 | bin    |    2 |
|  3 | daemon |    3 |
|  4 | adm    |    4 |
|  5 | lp     |    5 |
+----+--------+------+
5 rows in set (0.00 sec)

-------------------------2.再前5行用户的UID还改回去-------------------------------
mysql> update  tarena.user set uid=uid-1 where  id <= 5;
Query OK, 5 rows affected (0.07 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql>  select  id , name , uid  from   tarena.user where  id <= 5;
+----+--------+------+
| id | name   | uid  |
+----+--------+------+
|  1 | root   |    0 |
|  2 | bin    |    1 |
|  3 | daemon |    2 |
|  4 | adm    |    3 |
|  5 | lp     |    4 |
+----+--------+------+
5 rows in set (0.00 sec)

----------------------3.显示uid号是偶数的用户名和对应的UID--------------------------
 mysql> select name ,uid from  tarena.user where  uid % 2 =  0;
 
 mysql> select name ,uid from  tarena.user where  uid % 2  !=  0;  # 奇数
 
---------------------- 4.显示uid 和 gid 的平均值---------------------------------
mysql> select name,uid,gid,(uid+gid)/2 as 平均值 from tarena.user where name="ftp";
+------+------+------+-----------+
| name | uid  | gid  | 平均值    |
+------+------+------+-----------+
| ftp  |   14 |   50 |   32.0000 |
+------+------+------+-----------+
1 row in set (0.00 sec)

-------------------------5.查询每位员工的年龄-------------------------------------
--tarena.employees表的操作
--用当前年份2022减去员工出生年分得出年龄
mysql> select 2022-year(birth_date) as age ,name,employee_id from tarena.employees;
-------------------------6.仅查询员工编号是8的员工的年龄----------------------------
mysql> select  2022 - year(birth_date) as age , name , employee_id 
from employees  where employee_id=8 ;

------------------------7.把编号8的员工 2020年12 月的奖金减去500-------------------
# 先把复合条件的工资查出来
 mysql>select employee_id , bonus  from  tarena.salary 
 where employee_id =8 and year(date)=2020 and month(date)=12;
+-------------+-------+
| employee_id | bonus |
+-------------+-------+
|           8 |  2000 |
+-------------+-------+
1 row in set (0.00 sec)

# 修改
mysql> update  tarena.salary set  bonus=bonus-500 
where employee_id =8 and year(date)=2020 and month(date)=12;

2、流程控制函数：查询表记录事可以加判断语句

2.1 if函数

if语句

语法格式	说明
if(条件,v1,v2)	如果条件是TRUE则返回v1，否则返回v2
ifnull(v1,v2)	如果v1不为NULL，则返回v1，否则返回v2

演示if（） 语句的执行过程

mysql> select  if(1 = 2 , "a","b");
+---------------------+
| if(1 = 2 , "a","b") |
+---------------------+
| b                   |
+---------------------+
1 row in set (0.00 sec)

mysql> select  if(1 = 1 , "a","b");
+---------------------+
| if(1 = 1 , "a","b") |
+---------------------+
| a                   |
+---------------------+
1 row in set (0.00 sec)

演示ifnull（） 语句的执行过程

 mysql> select  ifnull("abc","xxx");
+---------------------+
| ifnull("abc","xxx") |
+---------------------+
| abc                 |
+---------------------+
1 row in set (0.00 sec)

mysql> select  ifnull(null,"xxx");
+--------------------+
| ifnull(null,"xxx") |
+--------------------+
| xxx                |
+--------------------+
1 row in set (0.00 sec)

查询例子：

mysql> select name , uid  , if(uid < 1000 , "系统用户","创建用户") as 用户类型 from tarena.user;
mysql> select name , shell  , if(shell = "/bin/bash" , "交互用户","非交户用户") from tarena.user;
插入家目录是空的用户
mysql> insert   into   tarena.user (name, homedir) values ("jerrya",null);

     
查看没有家目录的用户
mysql> select name , homedir  from  tarena.user where homedir is null ;  
mysql>

查看的时候加判断
mysql> select name  姓名, ifnull(homedir,"NO  home") from  tarena.user;  


mysql> select name  姓名, ifnull(homedir,"NO  home")as 家目录  from  tarena.user;

2.2 case 函数

case语句 语法格式 (可以有多个判断添加)

如果字段名等于某个值，则返回对应位置then后面的结果，如果与所有值都不相等，则返回else后面的结果。

-----------语法格式1------------
CASE  字段名              
WHEN  值1  THEN  结果 
WHEN  值2  THEN  结果  
WHEN  值3  THEN  结果 
ELSE  结果  
END 
 
-----------语法格式2--------------
CASE              
WHEN  判断条件  THEN  结果 
WHEN  判断条件  THEN  结果  
WHEN  判断条件  THEN  结果 
ELSE  结果  
END  

例子：

mysql> select dept_id as "部门ID",dept_name as "部门名" ,
    -> case dept_name
    -> when "运维部" then "技术部门"
    -> when "开发部" then "技术部门"
    -> when "测试部" then "技术部门"
    -> else "非技术部门"
    -> end as 部门类型 from tarena.departments;


或
mysql> select dept_id ,dept_name,
    -> case
    -> when dept_name="运维部" then "技术部"
    -> when dept_name="开发部" then "技术部"
    -> when dept_name="测试部" then "技术部"
    -> when dept_name is null then "未设置"
    -> else "非技术部"
    -> end as 部门类型
    -> from tarena.departments;

3、查询结果处理

对用select 命令查找到的数据再做处理

类似于系统命令管道

例如 ps aux | grep httpd

语法格式：

语法格式1
select  字段名列表  from  库.表  分组|排序|过滤|分页;
 
 
语法格式2
select  字段名列表 from  库.表  where  筛选条件 分组|排序|过滤|分页;

**3.1 **分组 group by

对查找到数据做分组处理(表头值相同为一组，只显示一次) 。

语法：

select  字段名1（要求出现在group by后面）， 分组函数（），...... from  库.表  where  条件  group  by  字段1，字段2；

说明：

​ 1.查询列表必须是分组函数和出现在group by后面的字段；

​ 2.字段中值相同的为一组；

​ 3.分组后的数据筛选放在having子句中，分组前使用where。

例子：

#查看shell的种类 
mysql> select shell  from  tarena.user group by shell;

统计使用每种shell的用户个数    
mysql> select count(name), shell  from  tarena.user group by  shell;

#查看每个部门的人数 
mysql>select dept_id, count(name)  from tarena.employees group by  dept_id;

**3.2 **排序[ASE | DESC]

把查找到的数据排队 ，用来排队的字段数据类型应该是 数值类型

语法：

                [数值类型字段]
select  order  by  字段名  [ASE | DESC];

order by 字段名  asc; # 从小到大排序（升序）默认的排序方式
order by 字段名  desc; # 从大到小排序（降序）

例子：

1.查看满足条件记录的name和uid 字段的值  
mysql> select name , uid from  user where uid is not null;  
# 按照uid升序排序   
mysql> select name , uid  from  user where uid is not null  order by   uid ; 
# 按照uid降序排序 
mysql> select name , uid from  user where uid is not null  order by   uid   desc;


2.把2018 年每个员工的总收入由高到底排序 
#查看每个员工的总收入
select employee_id, sum(basic+bonus) as total from tarena.salary 
where year(date)=2018 group by employee_id ;
#按总收入降序排队
select employee_id, sum(basic+bonus) as total from tarena.salary 
where year(date)=2018 group by employee_id order by total desc；

运算符 和 统计函数的区别:

运算符 #横着计算

统计函数 #竖着计算

3.3 过滤 having

**作用:**对select 查询到的数据,再次做筛选.

格式：

#格式1
select  字段名列表  from  库.表  having  筛选条件;

#格式2
select  字段名列表  from  库.表  where  条件  having  筛选条件;

例子：

# 查询部门人数少于5人
select  dept_id ,  name    from   tarena.employees; # 查出来底层数据
 
select  dept_id ,  count(name)   from  tarena.employees  group  by  dept_id ;   # 分组
 
mysql> select  dept_id ,  count(name)   from  tarena.employees  group  by  dept_id  having
count(name)<5;		# 过滤

3.4 分页 limit

**作用:**限制查询结果显示行数（默认显示全部的查询结果）

使用SELECT查询时，如果结果集数据量很大，比如1万行数据，放在一个页面显示的话数据量太大；不如分页显示,比如每次只显示100行 ，100次显示完

格式：

命令格式1 (只显示查询结果的头几行)
select 字段名列表 from  库.表 limit 数字；
select 字段名列表 from  库.表 where  条件 limit 数字；
例如:
	limit  1 ; 只显示查询结果的第1行
	limit  3 ; 显示查询结果的前3行
	limit  10; 显示查询结果的前10行
       

命令格式2 （显示查询结果指定范围内的行）
select 字段名列表 from  库.表 limit 数字1，数字2；
select 字段名列表 from  库.表 where  条件 数字1，数字2； 
数字1 -- 表示起始行  
		第1行用数字0表示
		第2行用数字1表示  
		第3行用数字2表示 
数字2 -- 表示显示的总行数

例子：

limit   0,1;	  #从查询结果的第1行开始显示，共显示1行
limit   3,5;      #从查询结果的第4行开始显示，共显示5行
limit   10,10     #从查询结果的第11行开始显示，共显示10行

# 只显示查询结果的第1行
mysql> select name,uid , gid  , shell  from tarena.user where shell is not null ;

mysql> select name,uid , gid  , shell  from tarena.user where shell is not null  limit 1;


# 只显示查询结果的前3行
mysql> select name,uid , gid  , shell  from tarena.user  where shell is not null ；

mysql> select name,uid , gid  , shell  from tarena.user  where shell is not null  limit  3;
 
# 只显示查询结果的第1行 到 第3
mysql> select name,uid , gid  , shell  from user   where shell is not null；
mysql> select name,uid , gid  , shell  from user   where shell is not null  limit 0,3;

# 从查询结果的第4行开始显示共显示3行 （0 表示查询结果的第1行）
mysql> select name,uid , gid  , shell  from user  where shell is not null  limit 3,3;

# 比如从表里查询 100条记录  想分5页显示   100/5 = 20   每页显示20条记录,程序员在脚本里写查询命令
select   * from  tarena.user  limit   0， 20； #显示第一页的查询命令
select   * from  tarena.user  limit  20，20；  #显示第二页的查询命令
select   * from  tarena.user  limit  40，20；  #显示第三页的查询命令
select   * from  tarena.user  limit  60，20；  #显示第四页的查询命令
select   * from  tarena.user  limit  80，20；  #显示第五页的查询命令

看到一个对表数据做处理的要求时，分析出sql语句怎么写的思路：

第1步： 确定使用的命令（对数据做处理使用那个命令 select update insert delete ）

第2步： 确定数据在那个表里 （可以确定 from 后的 表名）

第3步： 确定处理的是什么数据 （就知道了 表头名 了）

第4步： 确定处理条件 （就知道 where 后边该怎么写了 ）

二、连接查询

1、概述

**连接查询：**把多张表 通过连接条件 组成1张新表 ，然后在组成的新表里查找数据 ；在工作中 ，不是把所有数据都放在一张表里存储，把数据找全就得从多张表里一起找。

1.1总结：

连接查询也叫多表查询 常用于查询字段来自于多张表；

通过不同连接方式把多张表重新组成一张新表对数据做处理。

如果直接查询两张表，将会得到笛卡尔积 （2张表里行数相乘的积）

通过添加有效的条件可以进行查询结果的限定

例子：

mysql> create table tarena.t1 select name,homedir from tarena.user limit 2;


mysql> create table tarena.t2 select name,uid,shell from tarena.user
    -> limit 4;


mysql> use tarena;
Database changed
mysql> select * from t1;
+------+---------+
| name | homedir |
+------+---------+
| root | /root   |
| bin  | /bin    |
+------+---------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+--------+------+---------------+
| name   | uid  | shell         |
+--------+------+---------------+
| root   |    0 | /bin/bash     |
| bin    |    1 | /sbin/nologin |
| daemon |    2 | /sbin/nologin |
| adm    |    3 | /sbin/nologin |
+--------+------+---------------+
4 rows in set (0.00 sec)

mysql> select * from t1,t2;
+------+---------+--------+------+---------------+
| name | homedir | name   | uid  | shell         |
+------+---------+--------+------+---------------+
| root | /root   | root   |    0 | /bin/bash     |
| bin  | /bin    | root   |    0 | /bin/bash     |
| root | /root   | bin    |    1 | /sbin/nologin |
| bin  | /bin    | bin    |    1 | /sbin/nologin |
| root | /root   | daemon |    2 | /sbin/nologin |
| bin  | /bin    | daemon |    2 | /sbin/nologin |
| root | /root   | adm    |    3 | /sbin/nologin |
| bin  | /bin    | adm    |    3 | /sbin/nologin |
+------+---------+--------+------+---------------+
8 rows in set (0.01 sec)

mysql> select * from t1,t2 where t1.name = t2.name;
+------+---------+------+------+---------------+
| name | homedir | name | uid  | shell         |
+------+---------+------+------+---------------+
| root | /root   | root |    0 | /bin/bash     |
| bin  | /bin    | bin  |    1 | /sbin/nologin |
+------+---------+------+------+---------------+
2 rows in set (0.00 sec)

1.2连接查询的分类：

按功能分类：

​ 内连接

​ 外连接

​ 交叉连接

按年代分类：

SQL92标准：仅支持内连接

SQL99标准：支持所功能的连接

1.3SQL99标准多表查询 的语法

格式：

# 格式一：
SELECT 字段列表  FROM 表1 [AS] 别名  [连接类型]   JOIN   表2 [AS] 别名
ON 连接条件   WHERE 筛选条件 ；

或
# 格式二：
SELECT 字段列表  FROM 表1 [AS] 别名  [连接类型]   JOIN   表2 [AS] 别名
ON 连接条件   
WHERE 筛选条件  
GROUP BY 分组  |  HAVING 分组后筛选条件 |  ORDER BY 排序字段  

2、连接查询

2.1 内连接

语法格式：

SELECT  字段列表
FROM  表1  别名
INNER JOIN  表2  别名  ON 连接条件  INNER JOIN  表3  别名  ON 连接条件
[WHERE 筛选条件]
[GROUP BY 分组]
[HAVING 分组后筛选]
[ORDER BY 排序列表]

根据连接条件的不同又分为：

​ 1.**等值连接：**使用相等判断做连接条件

​ 2.**非等值连接：**连接条件不是相等判断

​ 3.自连接： 自己连接自己，把1张表当做2张表（需要给表定义别名）

例子：

查询每个员工所在的部门名：

select name, dept_name  
from employees inner join departments  on employees.dept_id=departments.dept_id;

显示员工编号8 的 员工所在部门的部门名称；

mysql> select employee_id , name , dept_name from employees , departments 
where employees.dept_id = departments.dept_id and employees.employee_id=8;
+-------------+--------+-----------+
| employee_id | name   | dept_name |
+-------------+--------+-----------+
|           8 | 汪云   | 人事部    |
+-------------+--------+-----------+
1 row in set (0.00 sec)

1.内连接的等值连接例子

select  name, dept_name  from employees  inner join departments 
on employees.dept_id=departments.dept_id;


# 查询每个员工所在的部门名，使用别名（给表定义别名）
select d.dept_name , e.* 
from employees as e inner join departments as d on e.dept_id=d.dept_id;


# 把表连接后，加条件 查找符合条件的数据
select d.dept_name , e.* 
from employees as e inner join departments as d on e.dept_id=d.dept_id
where  d.dept_id=3;


# 查询每个员工所在的部门名，使用别名。两个表中的同名字段，必须指定表名
select  dept_id  from   employees  inner join departments  on  
employees.dept_id=departments.dept_id;
ERROR 1052 (23000): Column 'dept_id' in field list is ambiguous

select  employees.dept_id  from   employees  inner join departments  on  employees.dept_id=departments.dept_id;

select  e.dept_id ,d.dept_id ,e.name from   employees as e  inner join departments as d on  e.dept_id=d.dept_id;



# 查询11号员工的名字及2018年每个月工资
select   e.employee_id,  name, date, basic+bonus as total 
from employees as e   inner join salary as s 
on e.employee_id=s.employee_id
where year(s.date)=2018 and e.employee_id=11;

 
 
# 查询2018年每个员工的总工资
     #没分组前
select name,date,basic,bonus  from employees inner join salary
on employees.employee_id=salary.employee_id  where  year(date)=2018;  
 
    # 分组后
select name, sum(basic+bonus) from employees inner join salary
on employees.employee_id=salary.employee_id  
where year(date)=2018    group by name;

 
 
# 查询2018年每个员工的总工资，按工资升序排列
mysql> select name, sum(basic+bonus) as total  
from employees inner join salary on employees.employee_id=salary.employee_id 
where year(salary.date)=2018 group by name order by total asc;


# 查询2018年总工资大于30万的员工，按工资降序排列
mysql> select name, sum(basic+bonus) as total    
from employees inner join salary on employees.employee_id=salary.employee_id   
where year(salary.date)=2018 group by name having total > 300000
order by  total desc;


# 担心有同名的员工 可以使用员工编号分组
mysql> select employees.employee_id, sum(basic+bonus) as total     
from employees inner join salary on employees.employee_id=salary.employee_id    
where year(salary.date)=2018 group by employees.employee_id 
having total > 300000 order by  total desc;

2.非等值连接： 使用非相等做判断做连接条件

# 环境准备：创建工资等级表 wage_grade
mysql>use tarena;
mysql> create table wage_grade(
id int  primary key  auto_increment,  grade char(1), low int ,  high int  );   
   
mysql>insert into  wage_grade(grade,low,high)  values
 ('A', 5000, 8000),('B', 8001, 10000),('C', 10001, 15000),('D', 15001, 20000),
('E', 20001, 1000000);
 
mysql> select  * from  wage_grade;

# 查询2018年12月员工基本工资级别
select employee_id, date, basic, grade
from salary as s   inner join wage_grade as g 
on s.basic between g.low and g.high  
where year(date)=2018 and month(date)=12;
 
# 查询2018年12月员工各基本工资级别的人数
select grade, count(employee_id)  as  numbers
from salary as s inner join wage_grade as g  
on s.basic between g.low and g.high
where year(date)=2018 and month(date)=12 
group by grade;
 
#查询2018年12月员工基本工资级别，员工需要显示姓名
 select name, date, basic, grade 
 from employees as e  inner join salary as  s  on e.employee_id=s.employee_id 
 inner join wage_grade as g  on s.basic between g.low and g.high  
 where year(date)=2018 and month(date)=12;
 
# 给表头定义别名 
 mysql> select name as 姓名, date as 发工资日期, basic as 基本工资, grade as 工资等级   
 from employees as e  inner join salary as  s  on e.employee_id=s.employee_id   
 inner join wage_grade as g  on s.basic between g.low and g.high    
 where year(date)=2018 and month(date)=12 ；

3.自连接:

自己连接自己 把1张当2张表使用，实现的方法就是查询表的时候给表定义别名来实现。

查看哪些员工的生日月份与入职月份相同
select e.name, e.hire_date, em.birth_date 
from employees as e  inner join employees as em
on month(e.hire_date)=month(em.birth_date) and  e.employee_id=em.employee_id;


或 where 条件 
mysql> select  name ,hire_date , birth_date from  employees 
    -> where month(hire_date) = month(birth_date);

2.2 外连接

外连接的应用场景：

1）比较2个表里记录的不同

2）者哪些数据当前表有而另一张表没有。

分类：

1.左外连接 LEFT JOIN

select  tb1.字段...,tb2.字段
	from  table1  AS tb1  lift  join  table2  AS tb2
on  tb1.字段=tb2.字段;

左边表的记录全都显示出来， 右边的表只显示与条件匹配记录，右边表比左边表少的记录使用NULL匹配

2.右外连接 RIGHT JOIN

select  tb1.字段...,tb2.字段
	from  table1  AS tb1  righr  join  table2  AS tb2
on  tb1.字段=tb2.字段;

右边表的记录全都显示出来 左边的表只显示与条件匹配记录，左表比右边表少的记录使用NULL 匹配

例子：

环境准备：

# 向departments表里添加3个部门  行政部    小卖部   公关部
mysql> insert into departments(dept_name) values("小卖部"),("行政部"),("公关部");
mysql> select name,dept_id from employees where name in ("bob","tom","lily");
+------+---------+
| name | dept_id |
+------+---------+
| bob  |    NULL |
| tom  |    NULL |
| lily |    NULL |
+------+---------+
3 rows in set (0.00 sec)

# 向employees表中添加3个员工 只给name字段赋值
mysql> insert  into  employees(name)values("bob"),("tom"),("lily");
mysql> select  * from tarena.departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
|       3 | 运维部    |
|       4 | 开发部    |
|       5 | 测试部    |
|       6 | 市场部    |
|       7 | 销售部    |
|       8 | 法务部    |
|       9 | 小卖部    |
|      10 | 行政部    |
|      11 | 公关部    |
+---------+-----------+
11 rows in set (0.00 sec)

查看哪些员工没有部门

mysql> select e.name,d.dept_name from departments as d  right  join employees as e on d.dept_id=e.dept_id;
...
...
| 刘倩      | 法务部    |
| 杨金凤    | 法务部    |
| bob       | NULL      |
| tom       | NULL      |
| lily      | NULL      |
+-----------+-----------+
136 rows in set (0.00 sec)

mysql> select e.name,d.dept_name from departments as d  right  join employees as e on d.dept_id=e.dept_id 
where d.dept_name is null ;
+------+-----------+
| name | dept_name |
+------+-----------+
| bob  | NULL      |
| tom  | NULL      |
| lily | NULL      |
+------+-----------+
3 rows in set (0.00 sec)

查看哪些部门没有员工

mysql> select d.dept_name,e.name from departments as d  left  join employees as e on d.dept_id=e.dept_id;
...
...
| 法务部    | 王荣      |
| 法务部    | 刘倩      |
| 法务部    | 杨金凤    |
| 小卖部    | NULL      |
| 行政部    | NULL      |
| 公关部    | NULL      |
+-----------+-----------+
136 rows in set (0.00 sec)

 mysql> select d.dept_name,e.name from departments as d  left  join employees as e on d.dept_id=e.dept_id where e.name is null;
+-----------+------+
| dept_name | name |
+-----------+------+
| 小卖部    | NULL |
| 行政部    | NULL |
| 公关部    | NULL |
+-----------+------+
3 rows in set (0.01 sec)

3.全外连接

(mysql不支持，可以使用UNION实现相同的效果) ：合并查询结果

4.联合查询

语法格式 1  
(SELECT语句 ) UNION (SELECT语句);

语法格式 2  
(SELECT语句 ) UNION  ALL (SELECT语句);

用来合并查询结果;

可以合并同一张的表的查询记录（不同表的查询记录也可合并）;

要求查询时，多个select语句的检索到的字段数量必须一致;

每一条记录的各字段类型和顺序最好是一致的;

UNION关键字默认去重，可以使用UNION ALL包含重复项。

语法格式演示：
mysql> select  "abc";
+-----+
| abc |
+-----+
| abc |
+-----+
1 row in set (0.00 sec)

mysql> select  "bcd";
+-----+
| bcd |
+-----+
| bcd |
+-----+
1 row in set (0.00 sec)

mysql> (select  "bcd") union (select "xyz");
+-----+
| bcd |
+-----+
| bcd |
| xyz |
+-----+
2 rows in set (0.00 sec)

mysql> 
mysql> (select  "xyz") union (select "xyz");
+-----+
| xyz |
+-----+
| xyz |
+-----+
1 row in set (0.00 sec)

mysql> (select  "xyz") union all (select "xyz");
+-----+
| xyz |
+-----+
| xyz |
| xyz |
+-----+
2 rows in set (0.00 sec)

# 查询  1972年  或   2000年后  出生的员工

# 使用where 条件查询
select name, birth_date from employees
where year(birth_date)=1972  or  year(birth_date)>2000;

# 使用联合查询
( select name, birth_date from employees where year(birth_date)=1972)
union
(select name, birth_date from employees where year(birth_date)>2000 );

# 一起输出user表中uid号最小和uid号最大的用户名和uid号
select min(uid) from user;
select max(uid) from user;

(select name, uid from  user where uid = (select min(uid) from user))
union
(select name, uid from  user where uid = (select max(uid) from user));

5.嵌套查询

**子查询：**是指在一个完整的查询语句中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式。

简单的来说就是在select查询语句里嵌套select查询语句。

子查询常出现的位置：

​ select之后

​ from之后

​ where或having之后

1.查询运维部所有员工信息（部门名称是运维部的员工信息）
#先把运维部的id找到
select dept_id from departments where dept_name="运维部";
#员工表里没有部门名称,但有部门编号（和部门表的编号是一致的）
select  *  from  employees   
where  dept_id = (select dept_id from departments where dept_name="运维部");

2.查询2018年12月所有比100号员工基本工资高的工资信息
#把100号员工的基本工资查出来
select basic from salary  where year(date)=2018 and 
month(date)=12 and employee_id=100;  
#查看比100号员工工资高的
select  *  from salary 
where year(date)=2018 and month(date)=12 and 
basic>(select basic from salary where year(date)=2018 and 
month(date)=12 and employee_id=100);


3.查询部门员工人数比开发部人数少的部门
#统计每个部门的人数 
select   dept_id , count(name) from employees group by dept_id  ； 
#统计开发部 员工人数
mysql> select count(*) from employees where dept_id = (select dept_id from departments where dept_name="开发部");
#输出人数比开发部少的部门及人数 
select  dept_id ,  count(name)  as 部门人数   from employees group by dept_id 
having count(name)<(
    select count(name) from employees  where dept_id=(select dept_id from departments where dept_name='开发部')
);

3.查询每个部门的人数
#显示部门表中的所有列表
select d.*  from departments as d;   
#查询每个部门的人数
select  d.* , ( select count(name) from employees as e  where d.dept_id=e.dept_id) as 总人数  
from departments as d;

4.查询人事部和财务部员工信息
#查看人事部和财务部的 部门id
select dept_id from departments  where dept_name in ('人事部', '财务部');
#查询人事部和财务部员工信息
select dept_id , name  from employees 
where dept_id in ( 
select dept_id from departments  where dept_name in ('人事部', '财务部') );

4.查询人事部2018年12月所有员工工资
select   *   from salary where year(date)=2018 and month(date)=12 
and employee_id in (select employee_id from employees 
where dept_id=(select dept_id from departments where dept_name='人事部') );


5.查找2018年12月基本工资和奖金都是最高的工资信息
select * from salary 
where  year(date)=2018 and month(date)=12  and 
basic=(select max(basic) from salary where year(date)=2018 and month(date)=12 ) 
and 
bonus=(select max(bonus) from salary where year(date)=2018 and month(date)=12);

6.查询3号部门及其部门内   员工的编号、名字 和 email      
select dept_id, dept_name, employee_id, name, email from (
select  d.dept_name, e.*  from departments as d  inner join employees as e 
on d.dept_id=e.dept_id ) as tmp_table  where dept_id=3;

6.多表更新与删除

一起修改或删除多张表里的数据

多表更新：

update  表1  as  别名
inner | left | right  join  表2  as 别名
on  连接条件
set  列=值，列=值······
[where 筛选条件]

多表删除：

delete  表1别名，表2别名
from  表1  as  表1别名
inner | left | right  join  表2  as 表2别名
on  连接条件
[where 筛选条件]

例子：

环境准备：
mysql> use  tarena;
mysql> create  table  t3  select  name , uid  from  tarena.user  limit  2;
mysql> create  table  t4  select  uid,homedir,shell  from  tarena.user  limit  4;
mysql> select  * from  t3 ;
mysql> select  * from t4;

uid 字段 是  t3 和 t4 表的 关联字段 
select * from t3 inner join t4 on  t3.uid = t4.uid ;
#多表修改
update   t3 inner join t4 on  t3.uid = t4.uid  
set  t3.uid=101 , t4.uid=102   where t3.uid=0 ;
 
#查看修改
select  * from t3 ;   原先UID=0 没了  看到是  UID  = 101
 select  * from t4;   原先UID=0 没了  看到   UID  = 102  
#删除时使用的多表连接命令
mysql> select  * from  t3 inner join t4 on  t3.uid = t4.uid;
+------+------+------+---------+---------------+
| name | uid  | uid  | homedir | shell         |
+------+------+------+---------+---------------+
| bin  |    1 |    1 | /bin    | /sbin/nologin |
+------+------+------+---------+---------------+
1 row in set (0.00 sec)


#多表删除
mysql> delete t3,t4  from t3 inner join t4 on  t3.uid = t4.uid;
Query OK, 2 rows affected (0.06 sec)

mysql> select  * from t3;
+------+------+
| name | uid  |
+------+------+
| root |  101 |
+------+------+
1 row in set (0.00 sec)

mysql> select  * from t4;
+------+----------+---------------+
| uid  | homedir  | shell         |
+------+----------+---------------+
|  102 | /root    | /bin/bash     |
|    2 | /sbin    | /sbin/nologin |
|    3 | /var/adm | /sbin/nologin |
+------+----------+---------------+
3 rows in set (0.00 sec)

e t4 select uid,homedir,shell from tarena.user limit 4;
mysql> select * from t3 ;
mysql> select * from t4;

uid 字段 是 t3 和 t4 表的 关联字段
select * from t3 inner join t4 on t3.uid = t4.uid ;
#多表修改
update t3 inner join t4 on t3.uid = t4.uid
set t3.uid=101 , t4.uid=102 where t3.uid=0 ;

#查看修改
select * from t3 ; 原先UID=0 没了 看到是 UID = 101
select * from t4; 原先UID=0 没了 看到 UID = 102
#删除时使用的多表连接命令
mysql> select * from t3 inner join t4 on t3.uid = t4.uid;
±-----±-----±-----±--------±--------------+
| name | uid | uid | homedir | shell |
±-----±-----±-----±--------±--------------+
| bin | 1 | 1 | /bin | /sbin/nologin |
±-----±-----±-----±--------±--------------+
1 row in set (0.00 sec)

#多表删除
mysql> delete t3,t4 from t3 inner join t4 on t3.uid = t4.uid;
Query OK, 2 rows affected (0.06 sec)

mysql> select * from t3;
±-----±-----+
| name | uid |
±-----±-----+
| root | 101 |
±-----±-----+
1 row in set (0.00 sec)

mysql> select * from t4;
±-----±---------±--------------+
| uid | homedir | shell |
±-----±---------±--------------+
| 102 | /root | /bin/bash |
| 2 | /sbin | /sbin/nologin |
| 3 | /var/adm | /sbin/nologin |
±-----±---------±--------------+
3 rows in set (0.00 sec)