从零学大数据系列之数据库篇---第二章:MySQL高级

一. 课前提示

1. 知识点

1.  关联查询(了解)
2. 常用函数
3. DCL操作(了解)

2. 教学重点

1. 基本连接查询
2.基本子查询

二. 正课内容

第一章 关联查询（多表查询）《重点掌握》

1.1 概念

有的时候，我们的业务需求的数据不只是在一张表中，而是在两张或两张以上的表中，而这些表中通常都会存在着“有关系"的字段。那么此时的查询操作涉及到多表查询，我们称之为关联查询。

案例：最简单的关联查询
select * from emp,dept;
select * from emp join dept;

1.2 写法分类

在进行多表关联查询时，通常有两种写法：
- 第一种是在from子句中，直接写多个表名，表名之间使用逗号分隔开，如：
	select A.*,B.*,C.* from A,B,C where 关联条件
- 第二种是在from子句中,多个表名之间使用join关键字连接，并在on关键字后面添加关联条件。
	select A.*,B.*,C.* from A join B on 关联条件
	select A.*,B.*,C.* from A join B on AB关联条件 join C on AC或BC关联条件
	select A.*,B.*,C.* from A join B join C on  AB的关联条件 and  BC或AC关联条件

1.3 笛卡尔积

- 当做关联查询时，如果两张表中不存在关联字段，或者忘记写关联条件。那么会出现如下图所示的结果。
- 即表A中的每一条记录都回与表B中的所有记录进行匹配组合。例如表A中有m条记录。表B中有N条件。那么匹配组合的记录数目为M*N，此值被称之为笛卡儿积。
- 通常没有意义。

1.4 等值查询

在做关联查询时，使用有关系的字段进行等值条件的限制操作。我们称之为等值查询。

select A.*,B.* from A,B where A.colName=B.colName;

1.5 join连接分类

join关联查询操作分为两大类：内连接和外连接，而外连接有细分为三种类型。参考下图

1. 内连接:  [inner] join
2. 外连接 (outer join)：（引出一个驱动表的概念：驱动表里的数据全部显示)
  - 左外连接:left [outer] join, 左表是驱动表
  - 右外连接:right [outer] join, 右表是驱动表
  - 全外连接:full [outer] join, mysql不支持.两张表里的数据全部显示出来
3. 注意： join连接只支持等值连接  

1.6 内链接

语法： from tableName1 [inner] join tableName2 on 条件 注意:与等值查询的效果是一样的。

1.7 外连接

语法：
- 左外连接
	from tableName1 left join tableName2 on 条件
- 右外连接
	from tableName1 right join tableName2 on 条件

- 外连接的两张表分为驱动表和从动表。 驱动表的数据都显示，从动表的数据只显示满足条件的数据。
- 左外连接左边的表 就是主表。
- 右外连接右边的表是主表。

1.8 集合查询

union(去重)/union all(不去重):
两个查询语句使用上述的关键字连接即可。
注意：两个查询语句的字段名，字段个数，必须对应上。

第二章 高级关联查询（子查询）

2.1 简介

有的时候，当一个查询语句A所需要的数据，不是直观在表中体现，而是由另外一个查询语句B查询出来的结果，那么查询语句A就是主查询语句，查询语句B就是子查询语句。这种查询我们称之为高级关联查询，也叫做子查询。

子查询语句的返回数据形式：

- 返回单行单列
- 返回多行单列
- 返回单行多列
- 返回多行多列

子查询语句的位置可以在以下几个子句中：

- 在where子句中:	子查询的结果可用作条件筛选时使用的值。
- 在from子句中:		子查询的结果可充当一张表或视图，需要使用表别名。
- 在having子句中:	子查询的结果可用作分组查询再次条件过滤时使用的值
- 在select子句中:	子查询的结果可充当一个字段。仅限子查询返回单行单列的情况。

2.2 在where子句中

# 需求：查询工资大于员工编号为7369这个员工的所有员工信息。
解析：
第一步：目的是查询工资大于某一个数num的所有员工信息
     select * from emp where sal>num
第二步：num的值7369员工的工资
     select sal from emp where empno = 7369;
第三步：将主查询中的代词使用子查询语句替换
      select * from emp where sal>(select sal from emp where empno = 7369);
# 需求：查询工资大于10号部门的平均工资的所有员工信息
select * from emp where sal>(select avg(ifnull(sal,0)) from emp where deptno=10);
# 需求：查询工资大于10号部门的平均工资的非10号部门的员工信息。
select * from emp where sal>(select avg(ifnull(sal,0)) from emp where deptno=10) and deptno<>10;
# 需求：查询与7369同部门的同事信息。
select * from emp where deptno=(select deptno from emp where empno=7369) and empno<>7369;

2.3 在from子句中

# 需求：查询员工的姓名，工资，及其部门的平均工资。
解析：
第一步：先查询每个部门的平均工资
select deptno,avg(ifnull(sal,0)) from emp group by deptno;
第二步：将上一个查询语句的返回结果当成一张表，与员工表进行关联查询

select A.ename,A.sal,B.avg_sal
from emp A join (select deptno,avg(ifnull(sal,0)) avg_sal from emp group by deptno) B on A.deptno = B.deptno

2.4 在having子句中

# 需求：查询平均工资大于30号部门的平均工资的部门号，和平均工资
select deptno,avg(ifnull(sal,0)) from emp group by deptno having avg(ifnull(sal,0))>
(select avg(ifnull(sal,0)) from emp where deptno=30);

2.5 在select子句中

相当于外连接的另外一种写法
# 查询每个员工的信息及其部门的平均工资，工资之和，部门人数
select A.empno,A.ename,A.sal,
(select avg(ifnull(sal,0)) from emp B where B.deptno=A.deptno) avg_sal,
(select sum(sal) from emp C where C.deptno=A.deptno) sum_sal,
(select count(*) from emp D where D.deptno=A.deptno) count_
from emp A;

2.6 sql完整执行顺序

select distinct..from t1 [inner|left|right] join t2 on 条件 
where...group by...having...order by...limit

1. from t1
2. on 条件
3. [inner|left|right] join t2
4. where...
5. group by...
6. having...
7. select...
8. distinct...
9. order by...
10. limit....

第三章 常用函数

3.1 日期函数

3.2 字符串函数

3.3 数值函数

第四章 DCL语句

4.1 作用

 用于创建用户，授予权限，撤销权限等操作。
 create user
 grant
 revoke
 alter user
 drop user

4.2 管理用户

创建用户，修改密码，删除用户等操作，都需要使用超级管理员root进行操作

- 创建用户语法:
create user [e-mail] identified by 'password';

- 修改用户密码:
alter user [e-mail] identified by 'newPassword';
set password for [e-mail] = password('newPassword');

- 删除用户:
drop user [e-mail]

4.3 权限管理

可进行授予权限，撤销权限，查询权限等操作

- 查看用户权限：使用超级管理员root
show grants for username

- 授权：使用超级管理员root
grant 权限名[,权限名.....] on dbname.* to [e-mail]

DDL权限：create、alter、create view.....,drop
DML权限：insert、update、delete
DQL权限: select

案例：授于某用户全部权限
grant all privileges on *.* to 'scott'@'localhost' identified by '123456' with grant option;
案例：启用root用户的远程连接操作
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;

- 撤销权限
revoke 权限名 [,权限名.....] on dbname.* from [e-mail]

- 刷新权限
flush privilages;