来自拉钩教育-JAVA就业集训营

1.DQL操作单表

2. SQL约束

3.数据库事务

----------------------------------------------------------

1.DQL操作单表

1.1 排序

• 通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示效果,不会影响真实数据)

ASC 表示升序排序(默认)
DESC 表示降序排序

1.2.1 排序方式

1. 单列排序
   只按照某一个字段进行排序, 就是单列排序

需求1:
使用 salary 字段,对emp 表数据进行排序 (升序/降序)

 # 默认升序排序 ASC
 SELECT * FROM emp ORDER BY salary;

 # 降序排序
 SELECT * FROM emp ORDER BY salary DESC;

2. 组合排序
   同时对多个字段进行排序, 如果第一个字段相同 就按照第二个字段进行排序,以此类推

需求2:
在薪水排序的基础上,再使用id进行排序, 如果薪水相同就以id 做降序排序

 # 组合排序
 SELECT * FROM emp ORDER BY salary DESC, eid DESC;

1.3 聚合函数

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值(另外聚合函数会忽略null空值。)；

语法结构
SELECT 聚合函数(字段名) FROM 表名;

5个常用的聚合函数

需求1:

1 查询员工的总数
2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
3 查询薪水大于4000员工的个数
4 查询部门为'教学部'的所有员工的个数
5 查询部门为'市场部'所有员工的平均薪水

SQL实现

 #1 查询员工的总数
 -- 统计表中的记录条数 使用 count()
 SELECT COUNT(eid) FROM emp; -- 使用某一个字段
 SELECT COUNT(*) FROM emp; -- 使用 *
 SELECT COUNT(1) FROM emp; -- 使用 1,与 * 效果一样

 -- 下面这条SQL 得到的总条数不准确,因为count函数忽略了空值
 -- 所以使用时注意不要使用带有null的列进行统计
 SELECT COUNT(dept_name) FROM emp;

#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
 -- sum函数求和, max函数求最大, min函数求最小, avg函数求平均值
 SELECT
 SUM(salary) AS '总薪水',
 MAX(salary) AS '最高薪水',
 MIN(salary) AS '最低薪水',
 AVG(salary) AS '平均薪水'
 FROM emp;

 #3 查询薪水大于4000员工的个数
 SELECT COUNT(*) FROM emp WHERE salary > 4000;

 #4 查询部门为'教学部'的所有员工的个数
 SELECT COUNT(*) FROM emp WHERE dept_name = '教学部';

 #5 查询部门为'市场部'所有员工的平均薪水
 SELECT
 AVG(salary) AS '市场部平均薪资'
 FROM emp
 WHERE dept_name = '市场部';

1.4 分组

分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组

语法格式
SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];

• 需求1:通过性别字段 进行分组

 #   按照性别进行分组
 select * from emp group by sex  --注意这样分组并没有什么意义

分析group by是如何分组的

image.png

注意:
分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作.查询其他字段没有意义

• 需求2: 通过性别字段 进行分组,求各组的平均薪资

  select sex , avg(salary) '平均薪资' from emp group by sex

• 需求3

1.查询所有部门信息
2.查询每个部门的平均薪资
3.查询每个部门的平均薪资, 部门名称不能为null

 #1. 查询有几个部门
 SELECT dept_name AS '部门名称' FROM emp GROUP BY dept_name;

 #2.查询每个部门的平均薪资
 SELECT
 dept_name AS '部门名称',
 AVG(salary) AS '平均薪资'
 FROM emp GROUP BY dept_name;

 #3.查询每个部门的平均薪资, 部门名称不能为null
 SELECT
 dept_name AS '部门名称',
 AVG(salary) AS '平均薪资'
 FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name;

• 需求4 查询平均薪资大于6000的部门.

分析:
a. 需要在分组后,对数据进行过滤,使用 关键字 hiving
b.分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

  # 查询平均薪资大于6000的部门
 -- 需要在分组后再次进行过滤,使用 having
 SELECT
 dept_name ,
 AVG(salary)
 FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name HAVING 
 AVG(salary) > 6000 ;

where 与 having的区别

1.5 limit关键字

limit 关键字的作用

• limit是限制的意思,用于 限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)
• limit 语法是 MySql的方言,用来完成分页

语法结构
SELECT 字段1,字段2... FROM 表名 LIMIT offset , length;

• 需求1:

查询emp表中的前 5条数据
查询emp表中 从第4条开始,查询6条

 #查询emp表中的前5条数据
 # 参数1 起始值,默认是0 , 参数2 要查询的条数

 select * from emp limit 5;
 select * from emp limit 0,5;

 #查询emp表中 从第4条开始 查询6条
 # 起始值默认是从0开始的
 select * from emp limit 3,6;

• 需求2: 分页操作 每页显示3条数据

 #分页操作 每页显示3条数据
 select * from emp limit 0,3; -- 第1页
 select * from emp limit 3,3; -- 第2页 2-1=1 1*3=3
 select * from emp limit 6,3; -- 第三页
 # 分页公式 起始索引 = (当前页 - 1) * 每页条数
 # limit是MySql中的方言

2. SQL约束

1. 约束的作用:
   对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性.违反约束的不正确数据,将无法插入到表中

2. 常见的约束

2.1 主键约束

特点: 不可重复 唯一 非空
作用: 用来表示数据库中的每一条记录

3.1.1 添加主键约束

语法格式
`字段名 字段类型 primary key

1. 需求1:创建一个带主键的表

 # 方式1 创建一个带主键的表
 create table emp2(
  eid int  primary key ,
  ename  varchar(20),
  sex  char(1)
);

# 方式2 创建一个带主键的表
 create table emp2(
  eid  int,
  ename  varchar(20),
  sex  char(1),
  # 指定主键为 eid字段  
  primary key(eid)
)
#  方式3 创建一个带主键的表
 CREATE TABLE emp2(
  eid INT ,
  ename VARCHAR(20),
  sex CHAR(1)
)
 # 创建的时候不指定主键,然后通过 DDL语句进行设置
 alert table emp2 add primary key(eid);

image.png

2. 测试主键的唯一性 非空性

 # 正常插入一条数据
 insert into emp2 values(1,'宋江','男');

 # 插入一条数据,主键为空
 # Column 'eid' cannot be null 主键不能为空
 insert into emp2 values(NULL,'李逵','男');

 # 插入一条数据,主键为 1
 # Duplicate entry '1' for key 'PRIMARY' 主键不能重复
 INSERT INTO emp2 VALUES(1,'孙二娘','女');

3. 哪些字段可以作为主键 ?
   通常针对业务去设计主键,每张表都设计一个主键id
   主键是给数据库和程序使用的,跟最终的客户无关,所以主键没有意义没有关系,只要能够保证不重复就好,比如 身份证就可以作为主键.

3.1.2 删除主键约束

• 删除 表中的主键约束 (了解)
  alert table emp2 drop primary key(eid);

3.1.3 主键的自增

注: 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值.

 关键字:
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

1.创建主键自增的表

 create table emp2(
  eid int primary key auto_increment,
  ename varchar(20),
  sex char(1)
)

2.添加数据 观察主键的自增

insert into emp2(ename,sex) VALUES('张三','男');
insert into emp2(ename,sex) VALUES('李四','男');
insert into emp2 VALUES(NULL,'翠花','女');
insert into emp2 VALUES(NULL,'艳秋','女');

image.png

3.1.4 修改主键自增的起始值

默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下面的方式

 # 创建主键自增的表,自定义自增其实值
 create table emp2(
  eid int primary key,
  ename varchar(20),
  sex  char(1)
)auto_increment = 100;

 -- 插入数据,观察主键的起始值
 insert into emp2(ename,sex) values('张百万','男');
 insert into emp2(ename,sex) values('艳秋','女');

image.png

3.1.5 DELETE和TRUNCATE对自增长的影响

• 删除表中所有数据有两种方式

测试1: delete 删除表中所有数据

 # 目前最后的主键值是 101
 select * from emp2;
 # delete 删除表中数据,对自增没有影响
 delete from emp2;
 # 插入数据 查看主键
 insert into emp2(ename,sex) values('张百万','男');
 insert into emp2(ename,sex) values('艳秋','女');

image.png

测试2: truncate删除 表中数据

# 使用 truncate 删除表中所有数据,
truncate table emp2;
# 插入数据 查看主键
insert into emp2(ename,sex) values('张百万','男');
insert into emp2(ename,sex) values('艳秋','女');

image.png

2.2 非空约束

• 非空约束的特点: 某一列不予许为空
  语法格式
  字段名 字段类型 not null

需求1:为ename字段添加非空字段

 create emp2(
  eid int primary key auto_increment,
  #添加非空约束,ename字段不能为空
  ename varchar(20) not null,
  sex char(1)
);

image.png

2.3 唯一约束

唯一约束的特点: 表中的某一列的值不能重复( 对null不做唯一的判断 )
字段名 字段类型 unique
1.添加唯一约束

 create table emp3(
    eid int primary key auto_increment,
    ename varchar(20)  unique,
    sex char(1)
);

2.测试唯一约束

# 测试唯一约束 添加一条数据
insert into emp3 (ename,sex) values('张百万','男');

# 添加一条 ename重复的 数据
# Duplicate entry '张百万' for key 'ename' ename不能重复
insert into emp3 (ename,sex) vaules('张百万','女');

image.png

2.4 外键约束

FOREIGN KEY 表示外键约束，将在多表中学习。

2.5 默认值

默认值约束 用来指定某列的默认值
语法格式
字段名 字段类型 default 默认值

1. 创建emp4表, 性别字段默认 女

# 创建带有默认值的表
  create table emp4(
  eid int primary key auto_increment,
  # 为ename 字段添加默认值
  ename varchar(20) default '奥利给',
  sex char(1)
);

2. 测试 添加数据使用默认值

 # 添加数据 使用默认值
 insert into emp4(ename,sex) values (DEFAULT,'男');
 insert into emp4(sex) values ('女');
 # 不使用默认值
 insert into emp4(ename,sex) values('艳秋','女');

3.数据库事务

3.1 什么是事务

事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败

比如: 银行的转账业务,张三给李四转账500元 , 至少要操作两次数据库, 张三 -500, 李四 + 500,这中
间任何一步出现问题,整个操作就必须全部回滚, 这样才能保证用户和银行都没有损失.

3.2 模拟转账操作

1. 创建 账户表

# 创建账户表
 create table account(
   # 主键
   id int primary key auto_increment,
   # 姓名
   name varchar(10),
   # 余额
   money double
 );
 # 添加两个用户
 insert into account (name, money) values('tom', 1000), ('jack', 1000);

2. 模拟tom 给 jack 转 500 元钱，一个转账的业务操作最少要执行下面的 2 条语句：

 #tom账号 -500
  update account set money = 500 where name = 'tom';
 #jack账号 +500
  update account set money = 1500 where name = 'jack';

注:

假设当tom 账号上 -500 元,服务器崩溃了。jack 的账号并没有+500 元，数据就出现问题了。
我们要保证整个事务执行的完整性,要么都成功, 要么都失败. 这个时候我们就要学习如何操作事务.

3.3 MySQL事务操作

• MySQL中可以有两种方式进行事务的操作
  1.手动提交事务
  2.自动提交事务

3.3.1 手动提交事务

3.3.1.1语法格式

• start tansaction
  这个语句显示的标记了一个事务的起始点。
• commit
  表示提交事务,即提交事务的所有操作,具体的说,就是将事务中所有对数据库的更新都写到磁盘上的数据库中,事务正常结束。
• rollback
  表示撤销事务,即在事务运行的过程中发生了某种故障,事务不能继续执行,系统将事务种对数据的所有已完成的操作全部撤销,回滚到事务开始的状态

3.3.1.2 手动提交事务流程

执行成功的情况： 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
执行失败的情况： 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚

image.png

3.3.1.3 成功案例 演示

 #开启事务
  start transaction;
 #tom账号 -500
  update account set money = 500 where name = 'tom';
 #jack账号 +500
  update account set money = 1500 where name = 'jack';

此时我们发现数据并没有改变

在控制台执行 commit 提交事务

 #提交
 commit;

发现数据在事务提交之后,发生改变

image.png

3.3.1.4 事务回滚演示

• 如果事务中，有某条sql语句执行时报错了，我们没有手动的commit，那整个事务会自动回滚
  1.命令行 开启事务

 #开启事务
 start transaction

2.插入两条数据

 insert into account vaules(NULL,'张百万',3000);
 insert into account values(NULL,'有财',3500);

3. 不去提交事务 直接关闭窗口,发生回滚操作,数据没有改变

   
   
   zhu
   
   

   注意:

如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。 如果事务中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

3.3.2 自动提交事务

• MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕 自动提交事务，MySQL 默认开始自动提交事务
• MySQL默认是自动提交事务

3.3.2.1 自动提交事务演示

1.将tom账户金额 +500

image.png

2. 查看数据库：发现数据已经改变

   
   
   image.png

3.3.2.2 取消自动提交

• MySQL默认是自动提交事务,设置为手动提交
  1.登录mysql 查看autocommit的状态

 show variables like 'autocommit';

image.png

2. 把 autocommit 改成 off;
   set @@autocommit = off;
   
   image.png
3. 再次修改,需要提交之后才生效

• 将jack 账户金额 -500元

 # 选择数据库
 use db2;
 # 修改数据
 update account set money = money - 500 where name = 'jack';
 # 手动提交
 commit;

3.4 事务的四大特性 ACID

3.5.1 数据并发访问

一个数据库可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库. 数据库的相同数据可能被多个事务同时访问,如果不采取隔离措施,就会导致各种问题, 破坏数据的完整性

3.5.2 并发访问会产生的问题

事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个 数据。可能引发并发访问的问题

3.5.3 四种隔离级别

通过设置隔离级别,可以防止上面的三种并发问题.
MySQL数据库有四种隔离级别 上面的级别最低，下面的级别最高。

3.5.4隔离级别相关的命令

1.查看隔离级别

 select @@tx_isolation;

image.png

2.设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化

 set global transaction isolation level 隔离级别
 read uncommitted 读未提交
 read committed 读已提交
 repeatable read 可重复读
 serializable 串行化

例如: 修改隔离级别为 读未提交

 set global transaction isolation level read uncommitted;

3.6 隔离性问题演示

脏读: 一个事务读取到了另一个事务中尚未提交的数据

1. 打开窗口登录 MySQL，设置全局的隔离级别为最低 读未提交

set global transaction isolation level read uncommitted;

image.png

2. 关闭窗口,开一个新的窗口A ,再次查询隔离级别

• 开启新的 窗口A

  
  
  image.png
• 查询隔离级别

select @@tx_isolation;

image.png

3. 再开启一个新的窗口 B

• 登录数据库

  
  
  image.png
• 开启事务

 start transaction;

• 查询

select * from account;

image.png

1.A窗口执行

• 开启事务

start transaction;

• 执行修改操作

 # tom账户 -500元
 update account set money = money - 500 where name = 'tom';
 # jack账户 + 500元
 update account set money = money + 500 where name= 'jack';

2. B 窗口查询数据

• 查询账户信息

select * from account;

image.png

3. A窗口转账异常,进行回滚

rollback;

4. B 窗口再次查询 账户

 select * from account;

image.png

3.6.2 解决脏读问题

脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱 已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。

• 解决方案
  将全局的隔离级别进行提升为: read committed

1. 在 A 窗口设置全局的隔离级别为 read committed

 set global transaction isolation level read committed;

2. 重新开启A窗口, 查看设置是否成功.

select @@tx_isolation;

3. 开启B 窗口, A 和 B 窗口选择数据库后, 都开启事务

   
   
   image.png

4. A 窗口 只是更新两个人的账户, 不提交事务

 # tom账户 -500元
 update account set money = money - 500 where name = 'tom';
 # jack账户 + 500元
 update account set money = money + 500 where name = 'jack';

5. B 窗口进行查询,没有查询到未提交的数据

select * from account;

image.png

6. A窗口commit提交数据

 commit;

7.B窗口查看数据

 select * from account;

image.png

3.6.3 不可重复读演示

不可重复读: 同一个事务中,进行查询操作,但是每次读取的数据内容是不一样的

1. 恢复数据 (把数据改回初始状态)

   
   
   image.png
2. 打开两个 窗口A 和 窗口B,选择数据库后 开启事务

 use db2;
 start transaction;

image.png

1. B 窗口开启事务后, 先进行一次数据查询

select * from account;

image.png

2. 在 A 窗口开启事务后，将用户tom的账户 + 500 ,然后提交事务

 # 修改数据
 update account set money = money + 500 where name = 'tom';
 # 提交事务
 commit;

image.png

1. B 窗口再次查询数据

   
   
   image.png

• 两次查询输出的结果不同，到底哪次是对的？
• 不知道以哪次为准。 很多人认为这种情况就对了，无须困惑， 当然是后面的为准。
  我们可以考虑这样一种情况:

比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客 户，结果在一个事务
中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银
行工作 人员就不知道以哪个为准了

3.6.4 解决不可重复读问题

• 将全局的隔离级别进行提升为： repeatable read

1. 恢复数据

 update account set money = 1000;

2. 打开A 窗口, 设置隔离级别为：repeatable read

 # 查看事务隔离级别
select @@tx_isolation;
# 设置事务隔离级别为 repeatable read
set global transaction isolation level repeatable read;

image.png

3. 重新开启 A,B 窗口 选择数据库 ,同时开启事务

   
   
   image.png
4. B 窗口事务 先进行第一次查询

 select * from account;

image.png

5. A 窗口更新数据, 然后提交事务

 # 修改数据
update account set money = money + 500 where name = 'tom';
# 提交事务
commit;

6. B 窗口 再次查询

 select * from account;

image.png

• 同一个事务中为了保证多次查询数据一致，必须使用 repeatable read 隔离级别

3.6.5 幻读演示

幻读: select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

1. 打开 A B 窗口, 选择数据库 开启事务

   
   
   image.png
2. A 窗口 先执行一次查询操作

 # 假设要再添加一条id为3的 数据,在添加之前先判断是否存在
select * from account where id = 3;

image.png

3. B 窗口 插入一条数据 提交事务

insert into account values(3,'lucy',1000);
commit;

image.png

4. A 窗口执行 插入操作, 发现报错. 出现幻读

   
   
   image.png

• 我刚才读到的结果应该可以支持我这样操作才对啊，为什么现在不可以

3.6.6 解决幻读问题

• 将事务隔离级别设置到最高 SERIALIZABLE ，以挡住幻读的发生

如果一个事务，使用了SERIALIZABLE——可串行化隔离级别时，在这个事务没有被提交之前 , 其他的线程，只能等到当前操作完成之后，才能进行操作，这样会非常耗时，而且，影响数据库的性能，数据库不会使用这种隔离级别

1. 恢复数据

 delete from account where id = 3;

2. 打开A 窗口 将数据隔离级别提升到最高

 set global transaction isolation level SERIALIZABLE;

image.png

3. 打开 A B 窗口, 选择数据库 开启事务

   
   
   image.png
4. A 窗口 先执行一次查询操作

SELECT * FROM account WHERE id = 3;

5. B 窗口插入一条数据

 insert into account values(3,'lucy',1000);

image.png

6. A 窗口执行 插入操作, 提交事务 数据插入成功.

insert into account values(3,'lucy',1000);
commit;

image.png

7. B 窗口在 A窗口提交事务之后, 再执行,但是主键冲突出现错误

   
   
   image.png

• 总结:
  serializable 串行化可以彻底解决幻读,但是 事务只能排队执行,严重影响效率,
  数据库不会使用这种隔离级别