2022-12-5 多表查询-CSDN博客

本文链接：https://blog.csdn.net/m0_71442506/article/details/128190668

目录

多表查询

多表查询的分类

内连接查询

外连接查询

子查询

笛卡尔积：

事物

DCL

多表查询
- 多表查询的分类
  - 内连接查询
    - 从哪些表中查询数据
      条件是什么
      查询哪些字段
      - 隐式内连接
        SELECT
                   t1.name,-- 员工表的姓名
                   t1.gender,-- 员工表的性别
                   t2.name-- 部门表的名称
        FROM
                   emp t1,
                   dept t2
        WHERE
                   t1.dept_id = t2.id;
      - 显示内连接
        select 查询的字段链表 from 表名 inner join 表名2 on 条件
        -- 显示内连接
        select * from emp inner join dept on emp.dept_id = dept.id; -- inner可以省略
  - 外连接查询
    - 左外连接
      - 语法：select 字段链表 from 表1 left [outer] join 表2 on 条件;
      - 查询的是左表所有数据以及其交集部分。
    - 右外连接
      - 语法：select 字段链表 from 表1 right [outer] join 表2 on 条件;
      - 查询的是右表所有数据以及其交集部分。
  - 子查询
    - 概念：就是查询中嵌套查询称嵌套查询为子查询。
    - -- 查询工资最高员工信息
      -- 1 查询最高工资是多少
      SELECT MAX(salary) FROM emp;
      -- 2 查询员工信息并且工资等于9000的
      SELECT * FROM emp WHERE emp.salary = 9000;
      
      -- 一条sql完成操作，子查询
      SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);
    - 子查询不同情况
      - 子查询的结果是单行单列的：
        
        子查询可以作为条件使用运算符判断。运算符
        
        -- 查询员工工资小于平均工资的
        SELECT avg(salary) FROM emp;
        SELECT * FROM emp WHERE emp.salary < (SELECT avg(salary) FROM emp);
      - 子查询的结果是多行单列的：
        
        子查询可以作为条件，使用运算符in来判断
        
        -- 查询财务部所有的员工信息
        SELECT id from dept WHERE NAME = '财务部';
        SELECT id from dept WHERE NAME = '财务部' OR NAME = '市场部';
        SELECT * FROM emp where dept_id = 3 OR dept_id = 2;
        SELECT * FROM emp where dept_id = 3;
        
        -- 子查询
        
        -- 查询财务部和市场部所有的员工信息
        SELECT * FROM emp where dept_id in(SELECT id from dept WHERE NAME = '财务部' OR NAME = '市场部');
      - 子查询的结果是多行多列的：
        
        -- 查询员工的入职日期是2011-11-11日之后的员工信息和部门信息
        
        子查询可以作为一张虚拟表；
        
        -- 查询员工的入职日期是2011-11-11日之后的员工信息和部门信息
        
        SELECT * FROM emp where emp.join_date >'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 emp t1,dept t2 WHERE t1.dept_id = t2.dept_id AND join_date > '2011-11-11';
- 多表查询练习：
- 笛卡尔积：
  - 有两个集合a，b取这两个集合所有的组成情况
  - 要完成多表查询，需要消除无用的数据
事物
- 事物的基本介绍
  - 概念
    - 如果一个包含多个步骤的业务操作，被事物管理，那么这些业务要么成功，要么失败。
  - 操作
    - 开启事务：strat transaction;
    - 回滚：rolllback;
    - 提交：commit;
  - 例子
  - 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;-- 开启事务
    START TRANSACTION;
    UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
    UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
    COMMIT;-- 提交事务
    ROLLBACK;-- 回滚事物
    
    UPDATE account SET balance = 1000;
  - MySQL数据库中事物默认自动提交
    - 一条DML(增删改)会自动提交一次事物
    - 事物提交的两种方式
      - 自动提交：mysql自动提交
      - 手动提交：需要先开启事物，然后提交
      - oracle 默认手动提交
    - 查看事物的默认提交方式：SELECT @@autocommit;-- 1代表自动提交， 0 代表手动提交。
    - 修改事物的默认提交方式：set @@autocommit = 0;
- 事物的四大特征
  - 原子性：不可分割的最小操作单位，要么同时成功，要么同时失败。
  - 持久性：当事物提交或回滚后，，数据库会持久化的保存数据
  - 隔离性：多个事物之间。相互独立。
  - 一致性：事物操作前后数据总量不变。
- 事物的隔离级别
  - 概念：多个事务之间是隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题了。
  - 存在的问题：
    - 脏读：一个事务读取到另一个事物中没有提交的数据
    - 不可重复读：在同一个事务中，两次读取到的数据不一样
    - 幻读：一个事物操作(DML)数据表中所有的记录，另一个事物添加了一条数据，则第一个十五查询不到自己的修改。
  - 隔离级别
    - read uncommitted：读未提交
      - 产生问题：脏读、不可重复读、幻读
    - read committed：读已提交 Oracle默认
      - 产生的问题：不可重复读、幻读
    - repeatable read ：可重复读 MySQL默认
      - 产生的问题：幻读
    - serializable：串行化
      - 可以解决所有的问题。
    - 注意：隔离级别从小到大他的安全性越来越高，但是效率越来越低。
    - 数据库查询隔离级别：select @@tx_isolation;
    - 数据库修改隔离级别：set global transaction isolation level;
DCL
- SQL分类
  - DDL：操作数据库和表
  - DML：增删改表中数据
  - DQL：查询表中数据
  - DCL：管理用户，，授权
- DBA数据库管理员
- DCL：管理用户，授权
  - 管理用户
    - 添加用户
      - -- 创建用户
        CREATE USER '用户名'@'主机名' IDENTIFIED by '密码';
    - 删除用户
      - -- 删除用户
        DROP USER '用户名'@'主机名';
    - 修改用户的密码
      - -- 修改lisi用户的密码
        UPDATE USER SET PASSWORD('新密码') WHERE USER = '用户名';
      - SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
      - mysql中忘记用户密码
        cmd net stop MySQL 停止 MySQL服务
        使用无验证方式启动MySQL服务：mysqld --skip-grant-tables
        打开新的cmd窗口，直接输入mysql命令，回车，就可以登陆成功
        use mysql;
        UPDATE USER SET password = PASSWORD('新密码') WHERE USER = 'root';
        关闭两个窗口
        打开任务管理器手动结束mysqld.exe的进程
        启用mysql服务
        使用新密码登录。
    - 查询用户
    - -- 1.切换到mysql数据库库
      USE mysql;
      -- 2.查询user表
      SELECT * from USER;
    - 通配符%表示可以在任意主机使用用户登录数据库。
权限管理
- 查询权限：
  - -- 查询权限
    SHOW GRANTS FOR '用户名'@'主机名';
- 授予权限
  - grant 权限列表 on 数据库库名.表名 to '用户名'@'主机名';
  - -- 给张三授予所有权限，在任意数据库任意表上；
    GRANT ALL ON *.* TO '用户名'@'主机名';
- 撤销权限
  - -- 撤销权限
  - -- 撤销权限
    REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';