SQL笔记(3)-多表查询与事务

1. 多表查询

1. 多表查询的原始结果为多表的笛卡尔积结果
2. 正确的多表查询则需要消除无用的笛卡尔积查询结果
3. 方式：
    a. 内连接查询：
        1. 隐式内连接：
            使用where条件
            多表查询时通常在from后为每个表取别名，然后在select的属性中通过表的别名确定查询的属性
            select t1.name, t1.gender,t2.name
            from emp t1, dept t2
            where t1.dept_id = t2.id;
        2. 显示内连接:
            语法：
                select 字段列表 from 表名1 [inner] join 表名2 on 条件;
            例如：select * from emp inner join dept on emp.dept_id = dept.id;
        3. 内连接注意事项：
            1. 从哪些表中查询数据
            2. 条件是什么
            3. 查询的字段是什么
    b. 外连接查询：
        1. 左外连接：
            语法：
                select 字段列表 from 表名1 left [outer] join 表名2 on 条件;
            结果：
                左外连接的查询结果是表1的全部字段列表内容，加上表2与表1中符合条件的内容(交集部分)，不符合的值会为NULL
        2. 右外连接：
            语法：
                select 字段列表 from 表名1 right [outer] join 表名2 on 条件;
            结果：
                右外连接的查询结果是表2的全部字段列表内容，加上表1与表2中符合条件的内容(交集部分)，不符合的值会为NULL
    c. 子查询：
        查询中嵌套查询，其嵌套的查询为子查询
        1. 子查询的结果是单行单列
            单行单列结果可以作为条件语句中的值
            例如:
                select * from emp where emp.salary = (select max(salary) from emp);
        2. 子查询的结果是多行单列
            多行单列使用关键字in作为条件语句中的值
            例如:
                select * from emp where dep_id in (select id from dept where name = "name1" or name = "name2")
        3. 子查询的结果是多行多列
            多行多列的查询结果可以作为一张虚拟表
            例如：
                子查询方式
                select * from dept t1，(select * from emp WHERE empp .join date > "2011-11-11") t2 WHERE t1.id = t2.dept_id;"
                --普通内连接
                select * from emp t1,dept t2 where t1.dept id = t2.id and t1.join_date > "2011-11-11"
4. 举例查询：
    a. 数据库中表：
        部门表dept：id，dname，location
        员工表emp：id，ename, job_id, mgr, join_date, salary, dept_id
        职位表job：id, jname, description
        薪资表salary：grade, lowsalary, highsalary
    b. 查询：
        1. 查询部门编号，部门名称，部门位置，部门人数
        select
            t1.id,
            t1.dname,
            t1.location,
            t2.total
        from 
            dept t1, 
            (select 
                dept_id,
                count(id) total
            from
                emp
            group by
                dept_id
            ) t2
        where
            t1.id = t2.dept_id
        2. 查询所有员工的姓名及其直接上级姓名，没有上级的员工也需要查询
        select
            t1.ename,
            t1.mgr,
            t2.id,
            t2.ename
        from 
            emp t1 -- emp表id与mgr两列关联，称自关联
        left join 
            emp t2
        on 
            t1.id = t2.mgr

2. 事务

1. 事务的基本特征
    a. 概念：
        如果一个包含多个步骤的业务操作，被事务管理，则该业务操作要么全部步骤操作成功，要么全部失败
    b. 使用步骤：
        1. 开启事务：
            start transaction;
        2. 业务操作：
            如果执行过程中出现错误，则回滚
            如果执行成功，则提交
        3. 回滚：
            rollback;
        4. 提交：
            commit;
    c. MySQL数据库中：
        一条DML(增删改)语句会默认自动提交一次事务
        手动提交需要先开启transaction
    d. 修改提交方式：
        1. 查看提交方式：
            select @@autocommit; -- 1代表自动提交， 0代表手动提交
        2. 修改提交方式：
            set @@autocommit = 0; --设置手动提交
        3. MySQL默认为自动提交，Oracle默认为手动提交
2. 事务的四大特征
    1. 原子性：
        是不可分割的最小单位，同时成功，同时失败
    2. 持久性：
        事务提交或者回滚成功后，数据库在的数据会持久化保存数据
    3. 隔离性：
        多个事务之间相互独立
    4. 一致性：
        事务操作的前后，数据总量是不变的
3. 事务的隔离级别
    a. 多个事务操作同一事务引发问题：
        1. 脏读：
            一个事务读取到另一个事务没有提交的事务
        2. 不可重复读(虚读)：
            在同一个事务中，两次读取到的数据不一样
        3. 幻读：
            一个事务操作(DML)数据表的所有记录，另一个事务添加了一条数据，则前一个事务查询不到自己的修改
    b. 隔离级别：
        1. read uncommitted：读未提交
            产生问题：脏读，虚读，幻读
        2. read committed：读已提交 -- Oracle默认级别
            产生问题：虚读，幻读
        3. repeatable read：可重复读 -- MySQL默认级别
            产生问题：幻读
        4. serializable：串行化
            可以解决所有问题
        隔离级别越高，安全性越大，效率越低
    c. 修改隔离级别：
        1. 查询：
            select @@tx_isolation;
        2. 修改：
            set global transaction isolation level 级别名称;
            例如：
                set global transaction isolation level repeatable read;
        3. 修改级别后需要重新开启连接才会生效
4. 事务隔离级别举例：
    a. 脏读，虚读：
        1. A，B账户各有1000
        2. A登陆数据库将隔离权限设置为read uncommitted
        3. A开启事务，转500给B
        4. B登陆数据库开启事务，查看余额，发现为1500
        5. A回滚操作rollback
        6. B再次查看余额，发现余额变为1000
        即，一个事务读取到另一个事务没有提交的事务，且在同一个事务中，两次读取到的数据不一样
    b. 虚读：
        1. A开启事务将报表信息给B，尚未commit
        2. A交完后，原始数据发生变化，导致B查看报表信息时与A查看的报表信息不一致
        即，同一个事务中，两次读取到的数据不一样
    c. repeatable read:
        只有当其他事务commit后，且自己开启的事务commit后，自己才会查看到其他事务commit的结果

3. DCL：管理用户，授权

1. 用户信息：
    a. MySQL保存位置：
        在mysql数据库中，user表内存储用户信息
    b. 创建用户：
        CREATE USER "用户名"@"主机名<%代表所有主机>" IDENTIFIED BY "用户密码";
    c. 删除用户：
        DROP USER "用户名"@"主机名"; 
    d. 修改密码：
        update user set password = password("新密码") where user = "用户名"; -- 方式1
        set password for "用户名"@"主机名" = password("新密码"); -- 方式2
    e. 忘记root密码：
        1. 管理员权限cmd --> net stop mysql
        2. 无验证启动mysql服务：mysqld --skip-grant-table
        3. 新开cmd窗口，输入mysql回车
        4. 进入mysql数据库user表修改密码
        5. 进程管理 --> 结束mysqld进程 --> 管理员cmd --> net start mysql
        6. mysql -u用户名 -p密码 回车登陆
2. 授权：
    a. 查询权限：
        show grants for "用户名"@"主机名";
    b. 授予权限：
        grant 权限列表 on 数据库名.表名 to "用户名"@"主机名";
        grant all on *.* to "用户名"@"主机名"; -- 授予所有权限，即root用户权限
    c. 撤销权限：
        revoke 权限列表 on 数据库名.表名 from "用户名"@"主机名";