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

系统管理员 2023-10-07 14:14 15阅读 0赞

### **一. 课前提示** ###

#### **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，此值被称之为笛卡儿积。
    - 通常没有意义。

![e1f99302275b4392865819ce7304ddce.png][]

**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连接只支持等值连接

![format_png][]

**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 日期函数** ####

![3b31caaf279847aeaa4aa8095d971e2a.png][]

**3.2 字符串函数**

![611f0018fc1f469fbe1dc1c2175a1dae.png][]

**3.3 数值函数**

![a801274c23824f3c98b834a4f765cb98.png][]

### **第四章 DCL语句** ###

#### **4.1 作用** ####

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

#### **4.2 管理用户** ####

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

- 创建用户语法:
    create user username@ip identified by 'password';
    
    - 修改用户密码:
    alter user username@ip identified by 'newPassword';
    set password for username@ip = password('newPassword');
    
    - 删除用户:
    drop user username@ip

#### **4.3 权限管理** ####

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

- 查看用户权限：使用超级管理员root
    show grants for username
    
    - 授权：使用超级管理员root
    grant 权限名[,权限名.....] on dbname.* to username@ip
    
    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 username@ip
    
    - 刷新权限
    flush privilages;

[e1f99302275b4392865819ce7304ddce.png]: https://img-blog.csdnimg.cn/e1f99302275b4392865819ce7304ddce.png
[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9waWNiLnpoaW1nLmNvbS84MC92Mi04ODAyM2UxYzhhN2E2N2Q3OGUwNDFlODYwMWE4MDZkY183MjB3LmpwZw?x-oss-process=image/format,png
[3b31caaf279847aeaa4aa8095d971e2a.png]: https://img-blog.csdnimg.cn/3b31caaf279847aeaa4aa8095d971e2a.png
[611f0018fc1f469fbe1dc1c2175a1dae.png]: https://img-blog.csdnimg.cn/611f0018fc1f469fbe1dc1c2175a1dae.png
[a801274c23824f3c98b834a4f765cb98.png]: https://img-blog.csdnimg.cn/a801274c23824f3c98b834a4f765cb98.png