MySQL多表查询 事务 DCL

多表查询

• 查询语法:

  select
  	列名列表
  from
  	表名列表
  where
  	条件列表
  ...
  

• 注意:当使用多表查询时例如select * from emp,dept;会形成一个笛卡尔集,导致许多无用错误数据的产生
• 多表查询的分类:

1. 内连接查询:
   • 隐式内连接:
     • 查询所有员工信息以及对应的部门信息:select * from emp,dept where emp.dept_id =dept.id; (限制显示两者id相同的数据)
     • 查询员工表的名称,性别,部门表的名称:select emp.name,emp.gender,dept.name from emp,dept where emp.dept_id = dept.id;
     • 较为方便易看的sql代码

       select
       	t1.name,t1.gender,t2.name
       from
       	emp t1, dept t2   --这里对emp和dept赋予了别名
       where
       	t1.dept_id = t2.id
       

   • 显式内连接:
     • 语法:select 字段列表 from 表名1 [inner] join 表名2 on 条件 例子:select * from emp join dept on dept.id = emp.dept_id;
   • 内连接查询:
     • 从哪些表中查询数据
     • 条件是什么
     • 查询哪些字段
2. 外连接查询:
   • 左外连接
     • 语法:select 字段列表 from 表1 left [outer] join 表2 on 条件 []表示能省略
     • 查询的是左表所有数据以及其交集部分
   • 右外连接:
     • 语法:select 字段列表 from 表1 right [outer] join 表2 on 条件
     • 查询的是右表所有数据以及其交集部分
3. 子查询:
   • 概念:查询中嵌套查询,称嵌套查询为子查询
   • 简单案例:

     ==查询工资最高的员工信息==
     select max(salary) from emp; -- 得到工资最高为9000
     ==查询工资为9000的员工信息==
     select * from emp where salary = 9000;
     ==子查询综合在一起==
     select * from emp where salary = (select max(salary) from emp);
     

   • 子查询的不同情况
     • 子查询结果是单行单列的:
       • 子查询可以作为条件,使用运算符去判断,运算符<,>,<=,>=,=,!=,例子:select * from emp where salary > (select avg(salary) from emp); where后不能加聚合函数,但是这里是子查询
     • 子查询结果是单列多行的:
       • 子查询可以作为条件,使用运算符in来判断
       • 案例:

         ==查询市场部和研发部的id==
         select  id from dept where name = "研发部" or name = "市场部";
         ==合成子查询  查询员工表中id为研发部和市场部的员工信息==
         select * from emp where id in (select id from dept where name = "研发部" or name = "市场部");
         

     • 子查询是多行多列的:
       • 子查询可以作为一张虚拟表
       • 查询员工入职日期是2011-11-11日之后的员工信息和部门信息:

         ==使用子查询==
          select 
          *
          from 
          dept t1,(select * from emp where emp.join_date>"2011-11-11") t2
          where
          t1.id=t2.dept_id;
          ==使用普通内连接==
          select
          *
          from
          dept t1,emp t2
          where
          t1.id=t2.dept_id and t1.join_date>"2011-11-11";
         

查询案例:https://www.bilibili.com/video/BV1uJ411k7wy?p=527&spm_id_from=pageDriver

事务

• 事务的基本介绍
  • 概念:如果一个包含多个步骤的业务操作,杯事务管理,要么同时成功,要么同时失败
  • 操作:
    • 开启事务:start transaction;
    • 回滚:rollback;
    • 提交事务:commit;
    • MySQL数据库中事务默认自动提交:
    • 事务提交的两种方式:
      • 自动提交:
        • mysql就是自动提交的
        • 一条DML(增删改)语句会自动提交一次事务
      • 手动提交:需要先开启事务然后提交 (oracle 是手动提交)
    • 修改事务的默认提交方式:
      • 查看事务的默认提交方式:select @@autocommit; --1代表自动提交,0代表手动提交
      • 修改默认提交方式:set @@autocommit = 0; 修改成0后就算是DML(增删改)语句如果不手动commit是不会提交数据的

    ==[案例]银行转账==
    start transaction; --开启事务
    update 用户表 set balance = balance - 500 where name = "小明";
    故障点
    update 用户表 set balance = balance + 500 where name = "小华";
    commit; --发现没有问题,提交事务
    rollback; --发现问题,回滚事务
    

就像start transaction开启了一个存档点,如果在事务内部出现问题就会rollback回滚到存档点,以免出现扣钱不加钱,如果没有问题就直接commit提交

• 事务的四大特征:
  • 原子性: 是不可分割的最小操作单位,要么同时成功,要么同时失败
  • 持久性:当数据提交或者回滚之后,数据会持久化保存数据
  • 隔离性:多个事物之间,相互独立,但是多个事务处理操作同一批数据会引发一些问题,设置不同的隔离级别就能解决这些问题
  • 一致性:数据操作前后,数据总量是不变的 --守恒定律??
• 事务的隔离级别(了解)
  • 概念:多个事务之间相互隔离,相互独立.但是如果多个事务处理同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题
  • 存在问题:
  1. 脏读:一个事务会读取到另一个事务没有提交的数据
  2. 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样
  3. 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了另一条数据,则第一个事务查询不到自己的修改.
  • 隔离级别:(隔离级别从小到大,但是效率从高到低)
    • read uncommited:读未提交 (问题:脏读,虚读,幻读)
    • read commited:读已提交(问题:虚读,幻读)
    • repeatable read:可重复读(问题:幻读)，
    • serializable:串行化(可以解决所有问题)，含义就是当有事务已经占用表时，其他事务无法调用该表
    • 查询隔离级别:select @@tx_isolation;
    • 设置隔离级别:set global transaction isolation level 级别字符串
    • 隔离级别解释：https://www.bilibili.com/video/BV1uJ411k7wy?p=535&spm_id_from=pageDriver

DCL

• 回顾

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

• DBA：数据库管理员
• DCL：管理用户，授权
  1. 管理用户
     • 添加用户：语法：create user "用户名"@"主机名" identified by "密码";
     • 删除用户:语法:drop user "用户名"@"主机名";
     • 修改用户密码:语法:
       • update user set password = password("新密码") where user = "用户名",因为密码要被加密,所以要用password()
       • set password for "用户名"@"主机名" = password("新密码);
       • mysql中忘记了root密码?
         • cmd -->net stop mysql 停止sql服务
         • 使用无验证方式登录 mysqlid --skip-grant-tables
         • 打开新的cmd窗口,直接输入mysql指令,敲回车就能登录成功
         • use mysql
         • update user set password = password("新密码) where user = "root";
         • 关闭两个窗口
         • 打开任务管理器手动结束mysql.exe的进程
         • 启动mysql的服务,使用新密码登录
     • 查询用户
       • 查询mysql数据库然后查看user表
       • %通配符表示可以在任意主机进行用户登录数据库
  2. 授权
     • 查询权限:show grants for "用户名"@"主机名;"
     • 授予权限:grant 权限列表 on 数据库名.表名 to "用户名"@"主机名";
       当授予所有数据库中表的权限时:grant all on *.* to "用户名"@"主机名";
     • 撤销权限:revoke 权限列表 on 数据库名.表名 from "用户名"@"主机名";