MySql 学习笔记（三）： MySql查询语句②

大菜菜
38 阅读10分钟

第一章 多表查询之子查询

提供表结构如下：

-- 部门表
create table department (
    id int primary key auto_increment,
    name varchar (50)
)

-- 员工表
create table worker(
    int primary key auto_increment,
    name varchar (50), -- 名字
    sex char (2), -- 性别
    money double, -- 工资
    inwork_date date, -- 入职时间
    depart_id int, -- 部门
    foreign key (depart_id) references department(id)
)

1.1初始化数据

-- 插入部门数据
insert into department(name)
values('技术研发'),('市场营销'),('行政财务'),

-- 插入员工数据
insert into worker (name, sex, money, inWork_date,depart_id)
values ('cuihua', '女', 10000, '2019-5-5', 1);

insert into worker (name, sex, money, inWork_date,depart_id)
values ('guoqiang', '男', 20000, '2019-5-5', 2);

insert into worker (name, sex, money, inWork_date,depart_id)
values ('qiangge', '男', 20000, '2019-7-5', 3);

insert into worker (name, sex, money, inWork_date,depart_id)
values ('huahua', '女', 10000, '2019-7-5', 1);

1.2什么是子查询？

一个查询的结果作为另一个查询的条件。

有查询的嵌套，内部的查询就是子查询。

子查询，需要使用小括号包含起来。

-- 查询技术研发部门有哪些员工
-- 1. 先查询所有的员工
select * from worker where depart_id = 1

-- 2．查询部门
select id from department where name = '技术研发';

-- 使用子查询的方式，来统一查询对应的数据
select * from worker where depart_id = (select id from department where name = '技术研发'）

1.3 常见三种做法

  1. 单行单列

也就是结果只有一个

select 指定的字段 from 表 where 字段 =（子查询）
-- 谁的工资最高？
-- 1．先把最高工资找出来
select MAX(money) from worker;

-- 2．再去员工表中，把对应的员工信息查出来
select * from worker where money = (select MAX(money) from worker):

-- 谁的工资少于平均工资？
-- 1. 先把平均工资算出来
select AVG(money) from worker;

-- 2．再去员工表中，把对应的员工信息查出来
select * from worker where money < (select AVG(money) from worker:

2.单行多列

-- 查询那些工资大于12000 的人都来自哪些部门
-- 1．先查大于 12000 的员工对应的部门 id
select depart_id from worker where money > 12000;

-- 2.根据部门的编号，再找出部门的名字
-- 你查找到的记录，多于一行了，subauery returns more than 1row
-- select name from department where id = (select depart_id from worker where money > 12000);

select name from department where id in (select depart_id from worker where money > 12000) ;


-- 查询行政财务和技术研发中的所有员工的信息
-- 1．先根据名字来查找 id
select id from denartment where name in('行政财务','技术研发')

-- 2．再去查询相关的员工
select * from worker where depart_id in (select id from department where name in ('行政财务','技术研发');
  1. 多行多列

当你的子查询只要是多列，那么它肯定在from 后面是以一张表存在的。

select 字段from（子查询） 表别名 where 条作；

-- 从 2019 年后入职的员工和相关部门信息
-- 1. 2019-1-1 后的时间
select * from worker where inwork_date >='2019-01-01';

-- 2．当我们从上面查找到对应的员工，则可以通过员工的 depart_id 找到对应的部门信息
select * from department d, (select * from worker where inwork_date >= '2019-01-01') w where d.id = w.depart_id;

-- 将上面的例子换成内联实现
select * from worker inner join department on worker.depart_id = department.id where inwork_date >= '2019-01-01'

第二章 事务【难点】

背景： 转账 A-500，B+500，转账过程中，有可能出现突发状况，导致转账操作出现一些意料不到的问题。所以，我们需要建立一个通道，在通道中完成一个操作，要么成功，要么失败的时候及时回滚数据，避免造成业务混乱。

-- 账户表
create table bankCount(
    id int primary key auto_increment, 
    name varchar(50),
    money double
)

-- 添加数据
insert ihto bankcount （name, money)
values ('cuihua', 1000), ('banban', 2000);

-- 翠花给班班转钱 500
update bankCount set money = money - 500 where name = 'cuihua'
update bankCount set money = money + 500 where name = "banban"

-- 提供一个事务通道，让转账的操作稳妥一些，如果中途出现问题，及时回滚数据，不要造成数据丟失

2.1 事务原理

1.事务概述

  • 事务指的是逻辑上的一组操作，组成这组操作的各个单元要么全都成功，要么全都失败。
  • 事务作用：保证在一个事务中多次SQL操作要么全都成功，要么全都失败。

2.事务基本特性

(ACID，是针对单个事务的一个完美状态）

  • 原子性 (Atomicity)：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  • 一致性 (Consistency)：事务前后数据的完整性必须保持一致。保证一致性的工具：锁
  • 隔离性 (Isolation）：事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。隔离性由隔离级别保障！
    • 正常情况下数据库是做不到完完全全的隔离，可以增强隔离级别，但是效率会非常低。
    • read uncommitted -->read committed -> repeatable read --> serializable 【MysQL默认隔离级别RR】
      • 事务并发问题：脏读、不可重复读，幻读
      • 丢失更新的问题！
    • MVCC: multiple version concurrency control(多版本并发控制） 为什么需要MVCC?
    • 能不能在隔离性和效果之间找一个平衡点呢？
  • 持久性 (Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
    • 能不能做到100%
  1. 事务并发问题【事务隔离不足导致】

如果不考虑隔离性，事务存在3中并发访问问题。

1.脏读：一个事务读到了另一个事务未提交的数据

2.不可重复读：一个事务读到了另一个事务已经提交(update)的数据。引发另一个事务，在事务中的多次查询结果不一致.

3,虚读/幻读：一个事务读到了另一个事务已经插入(insert)数据。导致另一个事务，在事务中场次查海的结果不一致。

4.事务隔离级别

数据库规范规定了4种隔离级别，分别用于描述两个事务并发的所有情况。

  • read uncommitted 读末提交，一个事务读到另一个事务没有提交的数据。
    • 存在：3个问题（脏读、不可蛋复读、幻读）。
    • 解决：0个问题
  • read committed 读已提交，一个事务读到另一个事务已经提交的数据。
    • 存在：2个问题（不可重复读、幻读）。
    • 解决：1个问题（脏读）
  • repeatable read:可重复读，在一个事务中读到的数据始终保持一致，无论另一个事务是否提交。
    • 存在：1个问题（幻读）
    • 解决：2个问题（脏读、不可重复读）
    • mySql 默认的事务隔离级别
  • serializable 串行化，同时只能执行一个事务，相当于事务中的单线程。
    • 存在：0个问题。
    • 解决：3个问题（脏读、不可蛋复读、幻读）

安全和性能对比

  • 安全性：serializable > repeatable read > read committed > read uncommitted
  • 性能：serial1zable <rebeatable read <read committed < read uncommitted

常见数据库的默认隔离级别

  • MySql: repeatable read
  • Oracle: read committed

2.2 手动提交

1.核心SQL语句

开启事务：start transaction
提交事务：commit
回滚事务：rollback

  1. 实现过程

第一步：开启事务
第二步：执行你的 SQL 语句
第三步：提交事务
第四步：如果出现问题的话，则回滚事务（数据）

  1. 事务提交
-- 第一步：开启事务
start transaction:

-- 第二步：执行你的 SQL 语句
uddate bankCount set money = money - 500 where name = 'cuihua';
ubdate bankcount set money = money + 500 where name = 'banban';

-- 第三步：如果出现问题的话，则回滚事务（数据）
commit;
  1. 事务回滚
-- 第一步：开启事务
start transaction;

-- 第二步：执行你的SQL
update bankCount set money = money - 500 where name = 'cuihua'
update bankcount set money = money + 500 where name= 'hanban'

第三步：回滚事务
rollback;

2.3 自动提交

MySQL默认情况下，每一条DML 语句都是一个单独的事务，都会对应的开启一个事务，当你执行的时候，同时自动默认提交事务

  1. 自动提交事务
-- 随便一个DML 语句都是会自动提交事务的
uodate bankcount set money = money + 500 where name = 'banban';
  1. 取消自动提交

@@autocommit 原来是 1的，如果你要取消的话，则设置为0即可（但不建议）

set @@autocommit = 0:

2.4 事务案例：

  • 设置数据库的隔离级别
    • set session transactionisolation level 级别字符串
    • 级别字符串：read uncommitted、read committed、repeatable read 、serializab1e
    • 例如：set session transaction isolation level read uncommitted;
  • 读未提交：read uncommitted
时间事务A事务B
T0设置A事务隔离级别【读未提交】设置B事务隔离级别【读未提交】
T1开始A事务开始B事务
T2查询
T3更新数据
T4再次查询：查询到B末提交数据【脏读】
T5回滚
T6再次查询：B未提交数据消失
T7A事务提交【必须提交否则会对下次测试产生影响】B事务提交

注意：完成之后必须结束事务，commit

  • 可重复读：repeatable read
时间事务A事务B
T0设置A事务隔离级别【可重复读】设置B事务隔离级别【可重复读】
T1开始A事务开始B事务
T2查询
T3更新数据
T4再次查询：数据没变，解决脏读问题
T5B事务提交
T6再次查询：数据没变，解决不可重复问题
T7A事务提交
  • 串行化：serializable
时间事务A事务B
T0设置A事务隔离级别【串行化】设置B事务隔离级别【串行化】
T1开始A事务开始B事务
T2查询
T3更新数据-提示等待，如果^没有进一步操作B将等待超时
T4A事务提交或者回滚
T5B等待结束，执行操作
T6再次查询：数据没变，解决不可重复问题
T7B事务提交

第三章 DCL

  • DDL create, alter. drop
  • DML insert. update, delete
  • DQL select, show
  • DCL grant, revoke

3.1创建用户

如果将来创建一个新的用户，它并不会拥有与root 用户一样的权限，root 是超级管理员，所有的权限它都有。

create user '用户名'@'主机名' identified by '密码'；
CREATE USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '密码'；
create user "cuihua'@'localhost' identified by '1234'
create user 'huahua'@'localhost' identified by '1234'

3.2 授权

如果想要使用这些新增的用户，则需要授予一定的权限。

grant 权限1，权限2，……，权限N on 数据库名.表名 to '用户'@'主机名'

-- cuihua 的权限
-- 如果希望在某个数据库下所有的表都能用的话，则建议写成 数据库名.*
grant create, alter, insert, update, select on hello.* to 'cuihua'@'localhost ';

-- 简单的赋权限方法
grant all on *.* to 'huahua'@'localhost'

3.3 撤销授权

revoke all on he11o.* from 'cuihua'@'localhost’;

3.4 查看杈限

show grants for '用户名'@'主机名'；

3.5 删除用户

drop user '用户名'@'主机名'；

3.6 修改用户的密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysal_native_password BY '新密码'；
avatar
文章
阅读
粉丝