一 多表查询【重点…】

同时查询多张表获取到需要的数据组成完整的信息返回给用户。

准备数据

  1. -- 多表查询
  2. create database day20;
  3. use day20;
  4. -- 创建部门表（主表）
  5. CREATE TABLE dept (
  6.   id INT PRIMARY KEY AUTO_INCREMENT,
  7.   NAME VARCHAR(20)
  8. );
  10. INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部'),('销售部');
  12. -- 创建员工表（从表）
  13. CREATE TABLE emp (
  14.   id INT PRIMARY KEY AUTO_INCREMENT,
  15.   NAME VARCHAR(10),
  16.   gender CHAR(1),   -- 性别（sex）
  17.   salary DOUBLE,   -- 工资
  18.   join_date DATE,  -- 入职日期
  19.   dept_id INT -- 外键字段
  20. );
  22. INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
  23. INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
  24. INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
  25. INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
  26. INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
  27. INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('沙僧','男',6666,'2017-03-04',null);

1.1 笛卡尔积

  1. * 功能
  2.         多张表的记录进行组合，这种现象称为笛卡尔积(交叉连接)
  4. * 语法
  5.         select ... from 左表,右表;

1588210274558.png

  1. -- 查询二张表
  2. SELECT * FROM emp,dept;
  3. SELECT COUNT(*) FROM emp,dept;

1.1 内连接

  1. * 功能
  2.         拿左表的记录去匹配由标的记录，若符合条件显示（二张表的交集）
  3. * 语法
  4.     1）隐式内连接
  5.         select ... from 左表,右表 where 连接条件;
  6.     2）显示内连接【推荐】
  7.         select ... from 左表 [inner] join 右表 on 连接条件;
  1. -- 内连接
  2. -- 隐式内连接
  3. SELECT * FROM emp e,dept d WHERE e.dept_id = d.id;
  5. -- 显示内连接
  6. SELECT * FROM emp e INNER JOIN dept d ON e.dept_id = d.id;
  8. -- 查询唐僧的 id，姓名，性别，工资和所在部门名称
  9. -- 1.确定查询表
  10. SELECT * FROM emp e INNER JOIN dept d;
  11. -- 2.确定连接条件
  12. SELECT * FROM emp e INNER JOIN dept d ON e.dept_id = d.id;
  13. -- 3.确定显示字段
  14. SELECT e.id,e.name,e.gender,e.salary,d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id;
  15. -- 4.确定业务条件
  16. SELECT e.id,e.name,e.gender,e.salary,d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.name = '唐僧';

1.3 外连接

  1. 1. 左外连接【推荐】
  2.     功能
  3.         展示左表全部，再去匹配右表记录，若条件符合显示，若条件不符合显示NULL
  4.     语法
  5.         select ... from 左表 left [outer] join 右表 on 连接条件;
  7. 1. 右外连接【了解】
  8.     功能
  9.         展示右表全部，再去匹配左表记录，若条件符合显示，若条件不符合显示NULL
  10.     语法
  11.         select ...from 左表 right [outer] join 右表 on 连接条件;
  1. # 左外连接（推荐）
  2. -- 查询所有员工信息及对应的部门名称
  3. SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;
  4. -- 查询所有部门及对应的员工信息
  5. SELECT * FROM dept d LEFT JOIN emp e ON e.dept_id = d.id;
  7. # 右外连接（了解）
  8. -- 查询所有部门及对应的员工信息
  9. SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id = d.id;

1.4 子查询（嵌套）

  1. * 功能
  2.         一条select语句执行结果，作为另一条select语法的一部分
  3. * 语法
  4.     1）查询结果单值
  5.         SELECT MAX(salary) FROM emp;
  6.     2）查询结果单列多值
  7.         SELECT salary FROM emp;
  8.     3）查询结果多列多值
  9.         SELECT * FROM emp;
  11. * 规律
  12.     子查询结果为单列，肯定作为条件在where后面使用
  13.         select ... from 表名 where 字段 in (子查询);
  14.     子查询结果为多列，一般作为虚拟表在from后面使用
  15.         select ... from (子查询) as 表别名;
  1. # 子查询
  3. # 子查询结果为单值
  4. -- 1 查询工资最高的员工是谁？ 
  5. SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
  6. -- 2 查询工资小于平均工资的员工有哪些？
  7. -- 2.1 先求出平均工资
  8. SELECT AVG(salary) FROM emp;
  9. -- 2.2 查询低于平均工资的员工
  10. SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);
  12. # 子查询结果为单列多行
  13. -- 1 查询工资大于5000的员工，来自于哪些部门的名字  
  14. -- 1.1 查询工资大于5000的员工
  15. SELECT dept_id FROM emp WHERE salary >5000;
  16. -- 1.2 来自于哪些部门的名字  
  17. SELECT * FROM dept WHERE id IN(SELECT dept_id FROM emp WHERE salary >5000);
  20. -- 2 查询开发部与财务部所有的员工信息
  21. -- 2.1 根据部门名称，查询部门主键
  22. SELECT id FROM dept WHERE `name` IN('开发部','财务部');
  23. -- 2.2 根据部门id查询员工信息
  24. SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE `name` IN('开发部','财务部'));
  27. # 子查询结果为多列多行
  28. -- 1 查询出`dept`，包括部门名称
  30. -- 方案一
  31. -- 1.1 查询出2011年以后入职的员工信息
  32. SELECT * FROM emp WHERE join_date > '2011-1-1';
  33. -- 1.2 通过临时表跟部门表关联
  34. SELECT * FROM (SELECT * FROM emp WHERE join_date > '2011-1-1') e LEFT  JOIN dept d ON e.dept_id = d.id;
  37. -- 方案二
  38. -- 1.1 先实现二张表关联
  39. SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;
  40. -- 1.2 再过滤2011年以后入职的
  41. SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id WHERE e.join_date > '2011-1-1';

二 多表案例【第一个作业】

  1. 我们在企业开发时，根据不同的业务需求往往需要通过2张及以上的表中去查询需要的数据。其实不管是几张表的查询，都是有规律可循的。

准备数据

  1. -- 多表案例
  2. create database day20_pro;
  3. use day20_pro;
  4. -- 部门表
  5. CREATE TABLE dept (
  6.   id INT PRIMARY KEY auto_increment, -- 部门id
  7.   dname VARCHAR(50), -- 部门名称
  8.   loc VARCHAR(50) -- 部门位置
  9. );
  11. -- 添加4个部门
  12. INSERT INTO dept(id,dname,loc) VALUES 
  13. (10,'教研部','北京'),
  14. (20,'学工部','上海'),
  15. (30,'销售部','广州'),
  16. (40,'财务部','深圳');
  18. -- 职务表
  19. CREATE TABLE job (
  20.   id INT PRIMARY KEY,
  21.   jname VARCHAR(20), -- 职务名称
  22.   description VARCHAR(50) -- 职务描述
  23. );
  25. -- 添加4个职务
  26. INSERT INTO job (id, jname, description) VALUES
  27. (1, '董事长', '管理整个公司，接单'),
  28. (2, '经理', '管理部门员工'),
  29. (3, '销售员', '向客人推销产品'),
  30. (4, '文员', '使用办公软件');
  32. -- 员工表
  33. CREATE TABLE emp (
  34.   id INT PRIMARY KEY, -- 员工id
  35.   ename VARCHAR(50), -- 员工姓名
  36.   job_id INT, -- 职务id  外键
  37.   mgr INT , -- 上级领导
  38.   joindate DATE, -- 入职日期
  39.   salary DECIMAL(7,2), -- 工资 99999.99
  40.   bonus DECIMAL(7,2), -- 奖金 99999.99
  41.   dept_id INT, -- 所在部门编号  外键
  42.   CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  43.   CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
  44. );
  46. -- 添加员工
  47. INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
  48. (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
  49. (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
  50. (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
  51. (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
  52. (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
  53. (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
  54. (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
  55. (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
  56. (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
  57. (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
  58. (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
  59. (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
  60. (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
  61. (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
  63. -- 工资等级表
  64. CREATE TABLE salarygrade (
  65.   grade INT PRIMARY KEY,  -- 等级
  66.   losalary INT, -- 最低工资
  67.   hisalary INT -- 最高工资
  68. );
  70. -- 添加5个工资等级
  71. INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
  72. (1,7000,12000),
  73. (2,12010,14000),
  74. (3,14010,20000),
  75. (4,20010,30000),
  76. (5,30010,99990);

1588214924487.png

  1. -- 1 查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述
  2. -- 1.1 确定几张表？
  3. SELECT * FROM emp e INNER JOIN job j;
  4. -- 1.2 确定连接条件
  5. SELECT * FROM emp e INNER JOIN job j ON e.job_id = j.id;
  6. -- 1.3 确定显示字段（列）
  7. SELECT e.id,e.ename,e.salary,j.jname,j.description FROM emp e INNER JOIN job j ON e.job_id = j.id;
  1. -- 2 查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
  2. -- 2.1 确定几张表？
  3. SELECT * FROM emp e 
  4.     INNER JOIN job j 
  5.     INNER JOIN dept d;
  7. -- 2.2 确定连接条件
  8. SELECT * FROM emp e 
  9.     INNER JOIN job j ON e.job_id = j.id
  10.     INNER JOIN dept d ON e.dept_id = d.id;
  12. -- 2.3 确定显示字段
  13. SELECT e.id,e.ename,e.salary,j.jname,j.description,d.dname,d.loc FROM emp e 
  14.     INNER JOIN job j ON e.job_id = j.id
  15.     INNER JOIN dept d ON e.dept_id = d.id;
  1. -- 3 查询所有员工信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
  2. -- 3.1 确定几张表
  3. SELECT * FROM emp e
  4.     INNER JOIN job j
  5.     INNER JOIN dept d
  6.     INNER JOIN salarygrade sg;
  8. -- 3.2 确定连接条件
  9. SELECT * FROM emp e
  10.     INNER JOIN job j ON e.job_id = j.id
  11.     INNER JOIN dept d ON e.dept_id = d.id
  12.     INNER JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalary;
  14. -- 3.3 确定显示字段
  15. SELECT e.ename,e.salary,j.jname,j.description,d.dname,d.loc,sg.grade FROM emp e
  16.     INNER JOIN job j ON e.job_id = j.id
  17.     INNER JOIN dept d ON e.dept_id = d.id
  18.     INNER JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalary;
  1. -- 4 查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
  3. -- 直接将第三题代码粘过来
  4. SELECT e.ename,e.salary,j.jname,j.description,d.dname,d.loc,sg.grade FROM emp e
  5.     INNER JOIN job j ON e.job_id = j.id
  6.     INNER JOIN dept d ON e.dept_id = d.id
  7.     INNER JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalary
  8.     WHERE j.jname = '经理';

规律

  1. * 多表查询会产生笛卡尔积
  3. * 消除笛卡尔积
  4.         连接条件 = 表个数-1
  6. * 步骤
  7.         1）确定几张表
  8.         2）确定连接条件
  9.         3）确定显示字段
  10.         4）确定业务条件
  1. -- 5 查询出部门编号、部门名称、部门位置、部门人数 (这个代码至少要敲三遍)
  2. -- 5.1 查询出部门编号、部门名称、部门位置
  3. SELECT * FROM dept;
  4. -- 5.2 部门人数（员工表：分组+聚合 ）
  5. SELECT dept_id,COUNT(*) AS total FROM emp GROUP BY dept_id;
  6. -- 5.3 部门表左外关联临时表
  7. SELECT d.id,d.dname,d.loc,e.total FROM dept d 
  8.     LEFT JOIN (SELECT dept_id,COUNT(*) AS total FROM emp GROUP BY dept_id) e ON d.id = e.dept_id;

1588217618939.png

  1. -- 6 查询每个员工的名称及其上级领导的名称（自关联）
  2. SELECT 
  3.   yuangong.id,
  4.   yuangong.ename,
  5.   lingdao.id,
  6.   lingdao.ename 
  7. FROM
  8.   emp yuangong 
  9.   LEFT OUTER JOIN emp lingdao 
  10.     ON yuangong.mgr = lingdao.id ;

1588218208174.png

三 用户权限 DCL【了解】

  • 我们进入公司后，有专门的DBA（数据库管理员），为你创建一个新用户，授予一定的权限…
  1. 1. 创建用户
  2.     语法：
  3.         create user '用户名'@'主机名' identified by '密码';
  4.     注意：
  5.         主机名：限定客户端登录ip
  6.             指定ip：127.0.0.1 （localhost）
  7.             任意ip：%
  9. 2. 授权用户
  10.     语法：
  11.         grant 权限1,权限2... on 数据库名.表名 to '用户名'@'主机名';
  12.     注意：
  13.         权限：
  14.             select、insert、delete、update、create...
  15.             all 所有权限
  16.         数据库名.*  指定库下面所有的表
  18. 3. 查看权限
  19.     语法：
  20.         show grants for '用户名'@'主机名'; 
  22. 4. 撤销授权
  23.     语法：
  24.         revoke 权限1,权限2... on 数据库名.表名 from '用户名'@'主机名'; 
  25.     注意：
  26.         权限：
  27.             select、insert、delete、update、create...
  28.             all 所有权限
  29.         数据库名.*  指定库下面所有的表
  31. 5. 删除用户
  32.     语法：
  33.         drop user '用户名'@'主机名';
  35. 6. 密码管理
  36.     1.超级管理员
  37.         set password for '用户名'@'主机名'=password('新密码');
  38.     2.普通用户
  39.         set password=password('新密码');
  1. -- 创建用户
  2. CREATE USER 'tom'@'%' IDENTIFIED BY '123';
  4. -- 授权
  5. GRANT SELECT ON day20.dept TO 'tom'@'%' ;
  7. -- 查看权限
  8. SHOW GRANTS FOR 'tom'@'%' ;
  10. -- 撤销权限
  11. REVOKE SELECT ON day20.dept FROM 'tom'@'%' ;
  13. -- 密码管理
  15. -- 加密函数
  16. SELECT PASSWORD('123');
  18. -- 超级管理帮你找回密码
  19. SET PASSWORD FOR 'tom'@'%'= PASSWORD('999');
  22. -- 删除用户
  23. DROP USER 'tom'@'%' ;

四 数据库备份与还原

需求

  1. 将今天的 day20_pro 数据库备份、删除、再还原....

dos命令行【了解】

  1. 1. 备份
  2.     格式：
  3.         mysqldump -u用户名 -p 需要备份数据库名 > 导出路径(*.sql)
  4.     实例：
  5.         mysqldump -uroot -p day20_pro > d:bak.sql
  6.     缺点：
  7.         通过命令备份的只有表结构和数据，没有建库语句...
  9. 2. 还原
  10.     格式：
  11.         mysql -u用户名 -p < 导入路径(*.sql)
  12.     实例：
  13.         mysql -uroot -p < d:bak.sql

图形化工具【sqlyog】

备份

1588219387706.png

还原

1588219433799.png