by konley

mysql最基础第三弹(完结篇),主要涉及多表查询、事务管理和部分DCL知识

十、多表查询

10.1笛卡尔积

如果直接这样子查两个表

select * from emp,dept;

将会产生表1字段数*表2字段数条记录

我们发现不是所有的数据组合都是有用的, 只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要
通过条件过滤掉没用的数据

笛卡尔积

  • 有两个集合AB,取这两个集合的所有组成情况(相乘)

要完成多表查询,首先应该消除笛卡尔积中无用、冗余的数据。

10.2 内连接查询

用左边表的记录去匹配右边表的记录,如果符合条件的则显示。如:从表.外键 = 主表.主键

1.隐式内连接

语法

...where 多表条件
一般为 表1.外键 = 表2.主键
where 学生表.选课代码 = 课程表.课程代码

使用 where 条件消除无用数据

--查询所有员工信息和对应的部门信息
select * from emp,dept where emp.dept_id = dept.'id';

--查询员工表的名称、性别和部门
select 
    emp.name,
    emp.gender,
    dept.name
from
    emp,
    dept
where
    emp.dept_id = dept.id;

推荐的内连接写法,起别名

--查询员工表的名称、性别和部门
select 
    t1.name as 姓名,
    t1.gender as 年级,
    t2.name as 课程名
from
    --先写from行,再写select行有代码提示
    emp as t1,
    dept as t2
where
    --where也要使用别名
    t1.dept_id = t2.id;

2.显式内连接

语法

select 字段列表 from 表名1 [inner] join 表名2 on 条件

inner可以省略

select * from emp join dept on emp.dept_id = dept.id;

推荐写法:

select 
    emp.name as 姓名,
    emp.gender as 年级,
    dept.name as 课程名
from 
    emp join dept 
on 
    emp.dept_id = dept.id

--可以起别名
select 
    t1.name as 姓名,
    t1.gender as 年级,
    t2.name as 课程名
from 
    emp as t1 join dept as t2
on 
    t1.dept_id = t2.id

10.3 外连接查询

1.左外连接

语法

select 字段列表 from 表1 left join 表2 on 条件

查询的是左表所有数据以及其交集部分。

--查询所有员工信息,如果员工有部门,则查询部门名称,没有部分,则不显示部门名称(null)
select
    t1.*,
    t2.name
from
    emp as t1 left join dept as t2
on
    t1.dept_id = t2.id

2.右外连接

语法

select 字段列表 from 表1 right [outer] join 表2 on 条件

查询的是右表所有数据以及其交集部分

--查询所有部门下的员工信息
select
    * 
from
    dept as t2
    right join emp as t1
on 
    t1.`dept_id` = t2.`id`

3. 如何理解内外连接

内连接:连接字段的值在2个表都完全相同
左外连接:连接字段的值在2个表都相同的,或者连接字段的值在左表有而右表没有
右外连接:连接字段的值在2个表都相同的,或者连接字段的值在右表有而左表没有

左连接就是以左为主,右边的记录可能为空; 右连接就是以右为主,左边的记录可能为空

10.4 子查询

查询中嵌套查询,称嵌套查询为子查询

例子:查工资最高的员工信息

--1.先查出工资最高的
select max(salary) from emp;
--2.根据工资查员工信息
select * from emp where salary=9000;

多条查询可以嵌套查询,即是子查询,记得加括号

--嵌套查询
SELECT
    * 
FROM
    emp 
WHERE
    salary = ( SELECT MAX( salary ) FROM emp );

1.子查询的结果为单行单列

子查询可以做为条件,使用运算符去判断,= > >= < <=等

--查询员工平均工资
SELECT
    * 
FROM
    emp 
WHERE
    salary < ( SELECT avg( salary ) FROM emp );

2.子查询的结果为多行单列

子查询可以作为条件,使用运算符in来判断,适用于多种结果的集合

--查询'财务部'和'市场部'所有的员工信息

--1.查市场部和财务部的id,此时会查出两行id,此时应该用in来获取集合
select id from dept where name = '财务部' or name = '市场部'

--2.嵌套为一条
SELECT
    * 
FROM
    emp 
WHERE
    dept_id IN ( SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部' );

3.子查询的结果为多行多列

将子查询的结果看成一张虚拟的表,参与其他查询

查询在'2011-11-11'后入职的员工信息和部门信息

--用普通内连接
SELECT
    * 
FROM
    emp AS t1,
    dept AS t2 
WHERE
    t1.dept_id = t2.id 
    AND t1.join_date > "2011-11-11"
--用虚拟表

--1.查'2011-11-11'后入职的员工信息
select * from emp where join_date > '2011-11-11'

--2.将1查到的结果当成一张表嵌套进去再次参与查询
select 
    *
from
    (select * from emp where join_date > '2011-11-11') as t1, --这里看成一张表
    dept as t2
where
    t1.dept_id = t2.id;

10.5 查询练习

-- 需求:
-- 1.查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述
SELECT
    emp.id 员工编号,
    emp.ename 员工姓名,
    emp.salary 工资,
    job.jname 职务名称,
    job.description 职务描述 
FROM
    emp
    JOIN job ON emp.job_id = job.id;
    
-- 2.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
SELECT
    emp.id 员工编号,
    emp.ename 员工姓名,
    emp.salary 工资,
    job.jname 职务名称,
    job.description 职务描述,
    dept.dname 部门名称,
    dept.loc 部门位置
FROM
    emp JOIN job ON emp.job_id = job.id
    JOIN dept ON emp.dept_id = dept.id;
    
-- 3.查询员工姓名,工资,工资等级
SELECT
    emp.ename 员工姓名,
    emp.salary 工资,
    salarygrade.grade 工资等级
FROM
    emp JOIN salarygrade ON emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary;
    
-- 4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
SELECT
    emp.ename 员工姓名,
    emp.salary 工资,
    job.jname 职务名称,
    job.description 职务描述,
    dept.dname 部门名称,
    dept.loc 部门位置,
    salarygrade.grade 工资等级
FROM
    emp JOIN salarygrade ON emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary 
    JOIN dept ON emp.dept_id = dept.id
    JOIN job ON emp.job_id = job.id;
    
-- 5.查询出部门编号、部门名称、部门位置、部门人数
SELECT
    dept.dname 部门编号,
    dept.dname 部门名称,
    dept.loc 部门位置,
    COUNT(emp.id) 部门人数
FROM
    emp JOIN dept ON emp.dept_id = dept.id
GROUP BY
    dept.id
    
-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
    -- 即是在查出的交集情况下 再追加现有结果上所没有的表2的补集
SELECT
    t1.ename 员工,
    t1.mgr 领导编号,
    t2.ename 领导
FROM
    emp t1
LEFT JOIN
    emp t2
ON
    t1.mgr = t2.id;

十一、事务

11.1 事务概念

  1. 概念:如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
  2. 操作:

    1. 开启事务: start transaction;
    2. 回滚:rollback;
    3. 提交:commit;
  3. 例子:

    CREATE TABLE account (
                id INT PRIMARY KEY AUTO_INCREMENT,
                NAME VARCHAR(10),
                balance DOUBLE
            );
    -- 添加数据
    INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
    SELECT * FROM account;
    UPDATE account SET balance = 1000;
    -- 张三给李四转账 500 元
            -- 0. 开启事务
            START TRANSACTION;
            -- 1. 张三账户 -500
            
            UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
            -- 2. 李四账户 +500
            -- 出错了...
            UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
            
            -- 发现执行没有问题,提交事务
            COMMIT;
            
            -- 发现出问题了,回滚事务
            ROLLBACK;
  4. MySQL数据库中事务默认自动提交

    • 事务提交的两种方式:

      * 自动提交:
          * mysql就是自动提交的
          * 一条DML(增删改)语句会自动提交一次事务。
      * 手动提交:
          * Oracle 数据库默认是手动提交事务
          * 需要先开启事务,再提交
    • 修改事务的默认提交方式:

      • 查看事务的默认提交方式:SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
      • 修改默认提交方式: set @@autocommit = 0;

11.2 事务的四大特征

  1. 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
  2. 持久性:当事务提交或回滚后,数据库会持久化的保存数据。
  3. 隔离性:多个事务之间。相互独立。
  4. 一致性:事务操作前后,数据总量不变

11.3 事务的隔离级别

  • 概念:多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。

    • 存在问题:

      1. 脏读:一个事务,读取到另一个事务中没有提交的数据
      2. 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
      3. 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
    • 隔离级别:

      1. read uncommitted:读未提交

        • 产生的问题:脏读、不可重复读、幻读
      2. read committed:读已提交 (Oracle)

        • 产生的问题:不可重复读、幻读
      3. repeatable read:可重复读 (MySQL默认)

        • 产生的问题:幻读
      4. serializable:串行化
      • 可以解决所有的问题
    • 注意:隔离级别从小到大安全性越来越高,但是效率越来越低
    • 数据库查询隔离级别:
    • select @@tx_isolation;

      • 数据库设置隔离级别:

        • set global transaction isolation level 级别字符串;
      • 演示:

        set global transaction isolation level read uncommitted;
        start transaction;
        -- 转账操作
        update account set balance = balance - 500 where id = 1;
        update account set balance = balance + 500 where id = 2;

十二、DCL

12.1 SQL分类

  1. DDL:操作数据库和表
  2. DML:增删改表中数据
  3. DQL:查询表中数据
  4. DCL:管理用户,授权

12.2 DBA:数据库管理员

12.3 DCL:管理用户,授权

12.3.1 管理用户

  1. 添加用户:

          * 语法:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
    
  2. 删除用户:

    • 语法:DROP USER '用户名'@'主机名';
  3. 修改用户密码:

    UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
    UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
    
    SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
    SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
    • mysql中忘记了root用户的密码?

      1. cmd -- > net stop mysql 停止mysql服务
         * 需要管理员运行该cmd
      2. 使用无验证方式启动mysql服务: mysqld --skip-grant-tables
      3. 打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登录成功
      4. use mysql;
      5. update user set password = password('你的新密码') where user = 'root';
      6. 关闭两个窗口
      7. 打开任务管理器,手动结束mysqld.exe 的进程
      8. 启动mysql服务
      9. 使用新密码登录。
  4. 查询用户:

    -- 1. 切换到mysql数据库
    USE myql;
    -- 2. 查询user表
    SELECT * FROM USER;
    通配符: % 表示可以在任意主机使用用户登录数据库

12.3.2 权限管理

1.查询权限:
    -- 查询权限
    SHOW GRANTS FOR '用户名'@'主机名';
    SHOW GRANTS FOR 'lisi'@'%';
    
2. 授予权限:
    -- 授予权限
    grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
    -- 给张三用户授予所有权限,在任意数据库任意表上
    GRANT ALL ON *.* TO 'zhangsan'@'localhost';
    
3. 撤销权限:
    -- 撤销权限:
    revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
    REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';