MySQL学习笔记-03高级查询、多表联合查询、聚合函数、子查询、视图、存储过程、权限、

本文链接：https://blog.csdn.net/a314649444/article/details/123512418

关于作者
金山老师：从事Java、大数据、Python职业化培训6年，项目管理、软件开发14年。欢迎添加我的微信号【jshand】,最近建了一些微信交流群，扫描下方二维码添加公众号，回复：进群

在这里插入图片描述

高级查询

多表连接介绍

连接类型]
等值连接]
多表连接的写法
笛卡尔积

-- 限制歧义列名
SELECT 
  ename,
  emp.deptno,
  dname 
FROM
  emp,
  dept 
WHERE emp.`deptno` = dept.`deptno` 
-- 给表起别名
SELECT 
  ename,
  e.deptno,
  dname 
FROM
  emp e,
  dept d 
WHERE e.`deptno` = d.`deptno` 

-- 练习
-- 1.写一个查询，显示所有员工姓名，部门编号，部门名称。
SELECT ename,e.deptno,dname FROM emp e ,dept d WHERE e.`deptno` = d.`deptno` 
-- 2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金 
SELECT 
  ename,
  loc,
  comm 
FROM
  emp,
  dept 
WHERE emp.`deptno` = dept.`deptno` 
  AND dept.`loc` = 'CHICAGO' 
  AND NULLIF(emp.`comm`, 0) IS NOT NULL ;

-- 3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。
SELECT ename,loc FROM emp ,dept WHERE emp.`deptno` = dept.`deptno`
AND ename LIKE '%A%'

自身连接

-- 自连接
SELECT a.ename,b.ename FROM emp a  ,emp b WHERE a.mgr =  b.empno

ANSI SQL

交叉连接

-- 交叉连接 （ 笛卡尔积）
SELECT * FROM emp CROSS JOIN dept;

自然连接

-- 自然连接 
SELECT * FROM emp NATURAL JOIN dept

USING子句

-- USING子句 
SELECT * FROM emp  JOIN dept USING(deptno)

ON子句

-- ON子句
SELECT * FROM emp  JOIN dept ON emp.`deptno` = dept.`deptno`

SELECT * FROM emp empl JOIN emp mgr  ON empl.mgr = mgr.empno

内连接&&外连接

-- 内连接  、外连接
-- 1 内连接
SELECT * FROM emp  INNER JOIN dept ON emp.`deptno` = dept.`deptno`

-- 期望没有部门的 员工也显示
-- 2.1 左外连接
SELECT * FROM emp  LEFT JOIN dept ON emp.`deptno` = dept.`deptno`

-- 2.2 右外连接
SELECT * FROM emp  RIGHT JOIN dept ON emp.`deptno` = dept.`deptno`
SELECT * FROM  dept LEFT JOIN emp ON emp.`deptno` = dept.`deptno`

多表联合查询的编码顺序

1.分析要查询的列都来自于哪些表，构成FROM子句;

2.分析这些表之间的关联关系，如果表之间没有直接关联关系，而是通过另一个中间表关联，则也要在FROM子句中补充中间关联表;

3.接下来在WHERE子句中补充表之间的关联关系，通常N个表，至少要有N-1个关联关系;

4.分析是否还有其它限制条件，补充到WHERE子句的表关联关系之后，作为限制条件;

5.根据用户想要显示的信息，补充SELECT子句。

6.分析是否有排序要求，如果排序要求中还涉及到其它表，则也要进行第2步补充排序字段所在的表，并且添加表之间的关联关系;

多表联合查询练习

-- 练习
-- 创建一个员工表和部门表的交叉连接。
SELECT * FROM emp CROSS JOIN dept


-- 使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期
SELECT ename , dname,hiredate FROM emp NATURAL JOIN  dept  WHERE hiredate > '1980-05-01'


-- 使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点
SELECT ename,dname,loc FROM emp JOIN  dept  USING(deptno) WHERE loc = 'CHICAGO'

-- 使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级
SELECT ename,dname,loc,
(CASE WHEN  sal BETWEEN 0 AND 999   THEN '初级'
	    WHEN  sal BETWEEN 1000 AND 1999 THEN '中级'
	    WHEN  sal BETWEEN 2000 AND 2999 THEN '高级'
	    ELSE  '股东'
	END ) 薪资等级
 FROM emp JOIN  dept ON emp.`deptno` = dept.`deptno`
WHERE loc = 'CHICAGO';

-- 使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
SELECT employee.ename 员工的姓名, manager.ename 经理 FROM emp employee LEFT  JOIN emp manager 
ON employee.mgr = manager.`empno`


-- 使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
SELECT employee.ename 员工的姓名, manager.ename 经理 
FROM emp manager  RIGHT  JOIN emp employee 
ON employee.mgr = manager.`empno`

-- 显示员工SMITH的姓名，部门名称，直接上级名称
SELECT 
  employee.ename 员工姓名,
  dname 部门名称,
  manager.`ename` 直接上级名称 
FROM
  emp employee 
  LEFT JOIN dept 
    ON employee.`deptno` = dept.`deptno` 
  LEFT JOIN emp manager 
    ON employee.`mgr` = manager.`empno` 
WHERE employee.ename = 'SMITH' 




-- 显示员工姓名，部门名称，工资，工资级别，要求工资级别大于3级。
SELECT ename,dname,sal, 
(CASE WHEN  sal BETWEEN 0 AND 999   THEN 1
	    WHEN  sal BETWEEN 1000 AND 1999 THEN 2
	    WHEN  sal BETWEEN 2000 AND 2999 THEN 3
	    ELSE  4
	END ) 薪资等级
FROM emp LEFT JOIN dept ON emp.`deptno` = dept.`deptno`
WHERE (CASE WHEN  sal BETWEEN 0 AND 999   THEN 1
	    WHEN  sal BETWEEN 1000 AND 1999 THEN 2
	    WHEN  sal BETWEEN 2000 AND 2999 THEN 3
	    ELSE  4
	END ) >3




-- 显示员工KING和FORD管理的员工姓名及其经理姓名。
SELECT 
  employee.`ename`,
  manager.ename
FROM
  emp manager 
  LEFT JOIN emp employee 
    ON manager.`empno` = employee.mgr
  WHERE manager.ename IN ('KING','FORD') 


-- 显示员工姓名，参加工作时间，经理名，参加工作时间，
-- 要求参加时间比经理早。
SELECT 
  employee.`ename`,
  employee.`hiredate` 员工的入职日期,
  manager.`ename`,
  manager.`hiredate`  经理的入职日期
FROM
  emp employee 
  LEFT JOIN emp manager 
    ON employee.`mgr` = manager.`empno` 
WHERE employee.`hiredate` < IFNULL(manager.`hiredate`,'9999-12-31')

分组函数

为什么用分组函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F45vjz6X-1647349781130)(images/MySQL-Day03/2020-11-11_101950.png)]

常用的分组函数

MIN
MAX
SUM
AVG
COUNT

-- max 取最大值
SELECT MAX( sal) FROM emp

-- min 取最小值 
SELECT MIN(sal) FROM emp 

-- 获取记录条数和
SELECT COUNT(sal) FROM emp;

-- 求和
SELECT SUM(sal) FROM emp ;


-- 求平均值
SELECT  SUM(sal) /COUNT(sal) 自己算的平均值 ,AVG(sal) avg的平均值 FROM emp; 


-- distinct 去重复
-- 查询所有的薪水，相同的显示一次
SELECT DISTINCT sal FROM emp 

SELECT COUNT(sal) 13个人有薪水,COUNT(DISTINCT sal) 去除重复的剩11 FROM emp 

-- 统计有多少个人有奖金（comm）  count
-- COUNT 统计的时候排除null
SELECT COUNT(comm) FROM emp

-- 变种的需求让你统计一共有多少个员工信息
SELECT COUNT(ename) ,COUNT(deptno) FROM emp 

-- 用常量的形式统计
SELECT COUNT(*) FROM emp ;-- 尽量不用星号，常量

SELECT COUNT(1)  FROM emp;
-- 通过ifnull 转换空字段
SELECT COUNT(IFNULL(comm,0)) FROM emp ;


-- 分组函数练习
-- 查询部门20的员工，每个月的工资总和及平均工资。
SELECT SUM(sal) 工资总和 ,AVG(sal) 平均工资 FROM emp WHERE deptno  = 20 

-- 查询工作在CHICAGO的员工人数，最高工资及最低工资。
SELECT COUNT(1) 员工人数 ,
       MIN(sal),
       MAX(sal) 
 FROM emp  INNER JOIN dept ON emp.`deptno` = dept.`deptno`
WHERE  loc ='CHICAGO';

-- 查询员工表中一共有几种岗位类型。
SELECT COUNT(DISTINCT job) FROM emp

GROUP BY子句

-- 根据部门进行分组，获取每个部门薪水的最大值，最小值、平均值
SELECT deptno, MAX(sal),MIN(sal),AVG(sal),COUNT(1),SUM(sal)
 FROM emp  GROUP BY deptno

练习

-- group by 分组练习
-- 查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
SELECT 
  dept.`deptno`,
  dname,
  COUNT(1) 人数,
  MAX(sal) 最高工资,
  MIN(sal) 最低工资,
  SUM(sal) 工资总和, AVG(sal) 平均工资 
FROM
  emp 
  JOIN dept 
    ON emp.`deptno` = dept.`deptno` 
GROUP BY dept.`deptno`,dname 

-- 查询每个部门，每个岗位的部门编号，部门名称，岗位名称，
-- 部门人数，最高工资，最低工资，工资总和，平均工资。
SELECT 
  dept.`deptno`,dname ,job ,
  COUNT(1) 人数,
  MAX(sal),
  MIN(sal),
  SUM(sal),
  AVG(sal)
FROM
  emp   JOIN dept 
    ON emp.`deptno` = dept.`deptno`
    GROUP BY dept.`deptno`,dname ,job;
 

-- 查询每个经理所管理的人数，
-- 经理编号，经理姓名，要求包括没有经理的人员信息。
SELECT manager.`empno`,manager.`ename`,
       COUNT(1)  
 FROM emp manager RIGHT JOIN  emp employee
ON  manager.`empno` = employee.`mgr`
GROUP BY manager.`empno`,manager.`ename`

SQL语句的执行顺序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6YCscTa9-1647349781133)(images/MySQL-Day03/2020-11-11_115554.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PqSutMml-1647349781134)(images/MySQL-Day03/2020-11-11_114936.png)]

在基础表上过滤， where条件生效，针对的是单行数据过滤
在过滤之后的数据上分组，group by
可以在分组的基础上，过滤 having 子句生效
枚举select 中的字段
order by子句生效，针对查询结果排序

练习

-- having 练习
-- 查询部门人数大于2的部门编号，部门名称，部门人数。
SELECT dept.`deptno`,dname,
   COUNT(1) 部门人数
 FROM emp ,dept WHERE emp.`deptno` = dept.`deptno`
GROUP BY dept.`deptno`,dname
HAVING COUNT(1) >3;

-- 查询部门平均工资大于2000，且人数大于2的部门编号，
-- 部门名称，部门人数，部门平均工资，并按照部门人数升序排序。
SELECT dept.`deptno`,dname,
   COUNT(1) 部门人数,
   AVG(sal) 平均工资
 FROM emp ,dept WHERE emp.`deptno` = dept.`deptno`
GROUP BY dept.`deptno`,dname
HAVING AVG(sal)  >2000 AND  COUNT(1) >2 
ORDER BY  COUNT(1) DESC

子查询

-- 单行子查询
-- 1.查询入职日期最早的员工姓名，入职日期
-- select ename,hiredate from emp order by hiredate limit 1
select ename,hiredate from emp where hiredate  = (select min(hiredate) from emp)

-- 2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，
-- 工资，部门名称

select 
  * 
from
  emp,
  dept 
where emp.`deptno` = dept.`deptno` 
  and loc = 'CHICAGO' 
  and sal > 
  (SELECT 
    sal 
  FROM
    emp 
  WHERE ename = 'SMITH') ;




-- 3.查询入职日期比30部门入职日期最早的员工还要早的员工姓名，入职日期
select ename,hiredate From emp 
where hiredate <(  select min(hiredate) from emp where deptno = 30  )


-- 4.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数

select 
  dept.deptno,
  dname,
  count(1) 
from
  emp,
  dept 
where emp.`deptno` = dept.`deptno` 
group by dept.deptno,
  dname 
 having count(1)> (SELECT  COUNT(1)  /COUNT(DISTINCT deptno)   FROM emp )


-- 查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，
-- 不包括10部门员工
SELECT ename ,hiredate
FROM
  emp 
WHERE deptno <> 10 
  AND hiredate > ANY 
  (SELECT 
    hiredate 
  FROM
    emp 
  WHERE deptno = 10);


SELECT hiredate FROM emp WHERE deptno = 10


-- 查询入职日期比20部门所有员工晚的员工姓名、入职日期，不包括10部门员工
SELECT * FROM emp WHERE deptno <> 10 
AND hiredate > ALL (SELECT hiredate FROM emp WHERE deptno = 30);

SELECT * FROM emp ORDER BY deptno ,hiredate ;


-- 查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工
SELECT * FROM emp WHERE deptno  <>10
 AND  job = ANY
 (SELECT job FROM emp WHERE deptno = 10)

视图

视图的创建

将复杂查询SQL变成简单的查询语句

-- 创建或替换视图
CREATE OR REPLACE  VIEW  v_gtavg 
AS 
SELECT 
  ename,
  sal,
  emp.deptno,
  b.salavg 
FROM
  emp,
  (SELECT 
    deptno,
    AVG(sal) salavg 
  FROM
    emp 
  GROUP BY deptno) b 
WHERE emp.deptno = b.deptno 
  AND emp.sal > b.salavg ;
 
 -- 使用视图当表使用，进行查询
 SELECT * FROM  v_gtavg;

您可以通过视图修改基表中数据，只要视图中不出现以下情况：

GROUP函数、
GROUP BY子句，
DISTINCT关键字；
使用表达式定义的列；

索引

索引简介

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。

索引由数据库表中一列或多列组合而成，其作用是提高对表中数据的【查询】速度。

索引是创建在表上的，是对数据库表中一列或多列的值进行【排序】的一种结构。

索引可以提高查询的速度。通过索引，查询数据时可以【不必读完记录的所有信息】，而只是查询索引列。

索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。

索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度【降低】了。

存储过程

用户权限

MySQL使用数据库表维护用户授权、用户验证数据

使用User表记录用户相关的信息

权限维护

创建数据库

CREATE DATABASE test_user CHARSET='utf8mb4';

创建用户

-- 创建一个用户utest/utest
create user  utest IDENTIFIED  BY 'utest'
-- 验证用户是否创建成功
select * from mysql.`user`

给用户授权

-- 给新创建的账号授权
-- 给 utest账号授权 test_user数据库的所有操作权限，登录主机

GRANT ALL ON test_user.* TO utest ;

新创建的用户可以在授权的数据库上进行操作

-- 使用新的账号登录数据库进行操作验证
CREATE TABLE emp (
 id BIGINT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(1000)

)
INSERT INTO emp (NAME) VALUES('张飞');

SELECT * FROM emp