关于作者
金山老师：从事Java、大数据、Python职业化培训6年，项目管理、软件开发。欢迎添加我的微信号【jshand】,最近建了一些微信交流群，扫描下方二维码添加公众号，回复：进群
文章目录
高级查询多表连接介绍ANSI SQL交叉连接自然连接USING子句ON子句多表联合查询的编码顺序多表联合查询练习分组函数常用的分组函数GROUP BY子句练习SQL语句的执行顺序练习子查询视图视图的创建索引索引简介存储过程用户权限权限维护高级查询
多表连接介绍
连接类型]等值连接]多表连接的写法笛卡尔积-- 限制歧义列名SELECT ename,emp.deptno,dname FROMemp,dept WHERE emp.`deptno` = dept.`deptno` -- 给表起别名SELECT ename,e.deptno,dname FROMemp 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 FROMemp,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` 直接上级名称 FROMemp 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 1WHEN sal BETWEEN 1000 AND 1999 THEN 2WHEN sal BETWEEN 2000 AND 2999 THEN 3ELSE 4END ) 薪资等级FROM emp LEFT JOIN dept ON emp.`deptno` = dept.`deptno`WHERE (CASE WHEN sal BETWEEN 0 AND 999 THEN 1WHEN sal BETWEEN 1000 AND 1999 THEN 2WHEN sal BETWEEN 2000 AND 2999 THEN 3ELSE 4END ) >3-- 显示员工KING和FORD管理的员工姓名及其经理姓名。SELECT employee.`ename`,manager.enameFROMemp manager LEFT JOIN emp employee ON manager.`empno` = employee.mgrWHERE manager.ename IN ('KING','FORD') -- 显示员工姓名，参加工作时间，经理名，参加工作时间，-- 要求参加时间比经理早。SELECT employee.`ename`,employee.`hiredate` 员工的入职日期,manager.`ename`,manager.`hiredate` 经理的入职日期FROMemp 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/-11-11_101950.png)]
常用的分组函数
MINMAXSUMAVGCOUNT-- 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 统计的时候排除nullSELECT 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) 平均工资 FROMemp 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)FROMemp 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 employeeON manager.`empno` = employee.`mgr`GROUP BY manager.`empno`,manager.`ename`
SQL语句的执行顺序
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6YCscTa9-1647349781133)(images/MySQL-Day03/-11-11_115554.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PqSutMml-1647349781134)(images/MySQL-Day03/-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`,dnameHAVING 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`,dnameHAVING AVG(sal) >2000 AND COUNT(1) >2 ORDER BY COUNT(1) DESC
子查询
-- 单行子查询-- 1.查询入职日期最早的员工姓名，入职日期-- select ename,hiredate from emp order by hiredate limit 1select ename,hiredate from emp where hiredate = (select min(hiredate) from emp)-- 2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，-- 工资，部门名称select * fromemp,dept where emp.`deptno` = dept.`deptno` and loc = 'CHICAGO' and sal > (SELECT sal FROMemp 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) fromemp,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 ,hiredateFROMemp WHERE deptno <> 10 AND hiredate > ANY (SELECT hiredate FROMemp 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 <>10AND job = ANY(SELECT job FROM emp WHERE deptno = 10)
视图
视图的创建
将复杂查询SQL变成简单的查询语句
-- 创建或替换视图CREATE OR REPLACE VIEW v_gtavg AS SELECT ename,sal,emp.deptno,b.salavg FROMemp,(SELECT deptno,AVG(sal) salavg FROMemp 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/utestcreate 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
