当前位置：首页 > 有云笔记 > RDBMS1 > 正文内容

用户授权、完全备份、增量备份

小白3年前 (2022-03-14)RDBMS1255110

练习的准备工作

mysql> use tarena;
mysql> show tables;

要清楚4张都有哪些表头名表头下存储的是什么数据表之间的关联字段

+------------------+
| Tables_in_tarena |
+------------------+
| departments      |  部门表    存储部门信息   
| employees        |   员工表     存储员工信息
| salary           |   工资表    存储工资信息
| user             |   用户表   存储操作系统用户信息
+------------------+
4 rows in set (0.00 sec)

1 基础查询进阶

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

函数名()

通常使用的方法是函数名(字段名)

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

在select 命令里使用函数做查询的格式

第一种格式

SELECT 函数(字段名) FROM 库名.表名；

第二种格式

SELECT 函数(字段名) FROM 库名.表名 where 条件；

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

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)
mysql>

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);
+-----------+
| mod(10,3) |
+-----------+
|         1 |
+-----------+
1 row in set (0.01 sec)

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

mysql> 
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       |
| 24 | plj             |
| 26 | mysql           |
| 30 | plj             |
| 32 | halt            |
| 34 | AAA             |
| 36 | bcb             |
+----+-----------------+
17 rows in set (0.00 sec)
mysql>

1.3 聚集函数的使用 sum() avg() min() max() count()

作用：数据统计命令，输出的值只有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> 
mysql> select min(uid),max(uid) from  tarena.user;
+----------+----------+
| min(uid) | max(uid) |
+----------+----------+
|        0       |    65534 |
+----------+----------+
1 row in set (0.00 sec)
mysql> 
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)
mysql>

日期时间函数的使用：

使用mysql服务自带的命令获取系统日期和时间

mysql> system date

2021年 10月 12日星期二 09:26:20 CST

mysql> 
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-10-12 09:27:39 |
+---------------------+
1 row in set (0.00 sec)
mysql> select curdate() ;
+------------+
| curdate()  |
+------------+
| 2021-10-12 |
+------------+
1 row in set (0.00 sec)

mysql> select curtime() ;
+-----------+
| curtime() |
+-----------+
| 09:28:55  |
+-----------+
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()) |
+-------------+
|        2021 |
+-------------+
1 row in set (0.00 sec)
mysql> desc employees;
mysql> select  hire_date from  employees where  employee_id=10；
mysql> select  year(hire_date) from  employees where  employee_id=10;
mysql> select  month(hire_date) from  employees where  employee_id=12;
mysql> select  day(hire_date) from  employees where  employee_id=12;
mysql> select  year(now());
mysql> select  year(20191224);  #从自定义的日期时间中获取对应的值

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

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

把前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)
mysql>

再前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)

mysql>

#显示uid号是偶数的用户名和对应的UID

 mysql> select name ,uid from  tarena.user where  uid % 2 =  0;
 mysql> select name ,uid from  tarena.user where  uid % 2  !=  0;  奇数

#显示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)
mysql>

查询每位员工的年龄

mysql> select  2021 - year(birth_date) as age , name , employee_id from employees；

仅查询员工编号是8的员工的年龄

mysql> select  2021 - year(birth_date) as age , name , employee_id 
from employees  where employee_id=8 ;

把编号8的员工 2020年12 月的奖金减去500

#先把复合条件的工资查出来

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

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

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)
mysql>

演示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>

查询例子

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;

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

如果字段名等于某个值，则返回对应位置then后面的结果，

如果与所有值都不相等，则返回else后面的结果

语法格式1

CASE 字段名

WHEN 值1 THEN 结果

WHEN 值2 THEN 结果

WHEN 值3 THEN 结果

ELSE 结果

END

语法格式2

CASE

WHEN 判断条件 THEN 结果

ELSE 结果

END

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

或

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 departments;

*****************查询结果处理***************

查询结果处理：对用select 命令查找到的数据再做处理，

类似于系统命令管道 | 例如 ps aux | grep httpd

语法格式1

select 字段名列表 from 库.表分组|排序|过滤|分页 ;

语法格式2

select 字段名列表 from 库.表 where 筛选条件分组|排序|过滤|分页 ;

~~~~~~~分组 : 对查找到数据做分组处理(表头值相同为一组) 。命令 group by 字段名

#查看每个部门的人数

mysql> select dept_id, count(name)  from tarena.employees group by  dept_id;

#查看shell的种类

mysql> select shell  from  tarena.user group by shell;

统计使用每种shell的用户个数

mysql> select count(name), shell  from  tarena.user group by shell;

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

命令

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

order by 字段名 desc; 从大到小排序（降序）

查看满足条件记录的name和uid 字段的值

mysql> select name , uid from  user where uid is not null;  
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;#按照uid降序排序

把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;

查询每个部门的人数

select    dept_id , name   from   tarena.employees ;
select    dept_id , count(name)   from   tarena.employees  group by dept_id;

查询每个部门中年龄最大的员工

select   dept_id,birth_date  from  tarena.employees;
select   dept_id, min(birth_date)  from  tarena.employees group by  dept_id;

查询每个部门入职最晚员工的入职时间

select dept_id, max(hire_date) from employees group by dept_id;

统计各部门使用tedu.cn邮箱的员工人数

select  dept_id ,name from tarena.employees where email like "%tedu.cn";
select  dept_id ,count(name) from   tarena.employees  
where  email like "%tedu.cn"  group by  dept_id;

查询2015年1月10号员工工资情况，以基本工资进行降序排列；如果基本工资相同，再以奖金升序排列

select date, employee_id, basic, bonus from tarena.salary  
where date='20150110' order by basic desc , bonus  asc;

查询2015年1月10号员工工资情况，以工资总额升序排序

select employee_id , basic , bonus , basic+bonus as total   
from tarena.salary   where  date='20150110';
select employee_id , basic , bonus , basic+bonus as total   
from tarena.salary   where  date='20150110'  order by total;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3 过滤数据 : 作用对select 查询到的数据再次做筛选使用的命令是having

格式1

select 字段名列表 from 库.表 having 筛选条件;

格式2

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

select   name  from  tarena.user  where shell != "/bin/bash";
mysql> select   name  from  tarena.user  where shell != "/bin/bash"  having name="mysql";
Empty set (0.00 sec)
mysql> 
mysql> 
mysql> select   name  from  tarena.user  where shell != "/bin/bash"  having name="ftp";
+------+
| name |
+------+
| ftp     |
+------+
1 row in set (0.00 sec)
mysql>

查询部门人数少于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;

4 分页

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

使用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;
select   name , uid , shell   from   user where  uid  between 10 and  100
order  by   uid  desc  limit 1;
select   name , uid , shell   from   user where  uid  between 10 and  100
order  by   uid  desc  limit 3;
mysql> select  * from  tarena.user where id <= 10;
mysql> select  * from  tarena.user where id <= 10    limit    1;
mysql> select  * from  tarena.user where id <= 10  limit   2 , 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张新表，然后在组成的新表里查找数据

在工作中，不是把所有数据都放在一张表里存储，把数据找全就得从多张表里一起找。

总结：

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

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

如果直接查询两张表，将会得到笛卡尔积（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;
use tarena;
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.00 sec)
mysql> 
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)

连接查询分类

按功能分类

内连接

外连接

交叉连接

按年代分类

SQL92标准：仅支持内连接

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

SQL99标准多表查询的语法格式1：

SELECT 字段列表 FROM 表1 [AS] 别名 [连接类型] JOIN 表2 [AS] 别名

ON 连接条件 WHERE 筛选条件；

SQL99标准多表查询的语法格式1：

SELECT 字段列表 FROM 表1 [AS] 别名 [连接类型] JOIN 表2 [AS] 别名

ON 连接条件 WHERE 筛选条件 GROUP BY 分组 HAVING 分组后筛选条件

ORDER BY 排序字段

连接查询之内连接

语法格式

SELECT 字段列表

FROM 表1 别名

INNER JOIN 表2 别名 ON 连接条件 INNER JOIN 表3 别名 ON 连接条件

[WHERE 筛选条件]

[GROUP BY 分组]

[HAVING 分组后筛选]

[ORDER BY 排序列表]

连接查询之内连接根据连接条件的不同又分为：

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

非等值连接：连接条件不是相等判断

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

通过例子了解连接方式的使用

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

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)

内连接的登录连接例子

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;

sum(basic+bonus) 的解释
mysql> select id ,uid+gid from user where id <= 3;
+----+---------+
| id | uid+gid |
+----+---------+
|  1 |       0 |
|  2 |       2 |
|  3 |       4 |
+----+---------+
3 rows in set (0.00 sec)
mysql> select uid+gid from user where id <= 3;
+---------+
| uid+gid |
+---------+
|       0 |
|       2 |
|       4 |
+---------+
3 rows in set (0.00 sec)
mysql> select sum(uid+gid) from user where id <= 3;
+--------------+
| sum(uid+gid) |
+--------------+
|            6 |
+--------------+
1 row in set (0.00 sec)
mysql>

总结：不加sum 横着加加了sum 竖着加

查询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;

内连接之非等值连接：使用非相等做判断做连接条件

环境准备：创建工资等级表

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;

内连接之自连接：自己连接自己把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);

外连接分类如下:

左外连接 LEFT JOIN

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

右外连接 RIGHT JOIN

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

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

外连接的应用场景： 1）比较2个表里记录的不同

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

环境准备：

向departments表里添加3个部门行政部小卖部公关部

mysql> insert into departments(dept_name) values("小卖部"),("行政部"),("公关部");

向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> 
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)
mysql>

查看哪些员工没有部门

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>

查看哪些部门没有员工

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)
mysql>

*****联合查询*****

也称联合查询，用来合并查询结果

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

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

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

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

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

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

语法格式演示：

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)
mysql>

查询 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));

~~~~查询运维部所有员工信息（部门名称是运维部的员工信息）

#先把运维部的id 找到

select dept_id from departments where dept_name="运维部";

#员工表里没有部门名称但有部门编号（和部门表的编号是一致的）

select  *  from  employees   
where  dept_id = (select dept_id from departments where dept_name="运维部");

~~~~查询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);

~~~查询部门员工人数比开发部人数少的部门

#统计每个部门的人数

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='开发部')
);

~~~~查询每个部门的人数

#显示部门表中的所有列表

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;

~~~~查询人事部和财务部员工信息

#查看人事部和财务部的 部门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 ('人事部', '财务部') );

~~~~查询人事部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='人事部')
);

~~~查找2018年12月基本工资和奖金都是最高的工资信息

分析

select 工资,奖金 from salary where 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);

~~~查询3号部门及其部门内员工的编号、名字和 email

分析：

select 员工的编号、名字和 email from employees where 3号部门;

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;

以上例子分别演示了子查询的位置

from命令后边 where 或 having 命令后边 select命令后

*******多表更新与删除**************************

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

环境准备：

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)
mysql>

over