用户授权 、 完全备份 、 增量备份
练习的准备工作
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 结果
WHEN 判断条件 THEN 结果
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
