与数据库的重逢（一）

1.操作单表

1. 创建数据库，复制表

1）创建一个数据库db2

CREATE DATABASE db2 CHARACTER SET utf8;

2)将db1数据库中的 emp表 复制到当前 db2数据库;

2. 排序
通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示效果,不会影响真实数据)
语法格式：SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC]。
注意：

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

单列排序-----只按照某一个字段进行排序, 就是单列排序
案例：使用 salary 字段,对emp 表数据进行排序 (升序/降序)

-- 默认升序排序 ASC
SELECT * FROM emp ORDER BY salary;
-- 降序排序
SELECT * FROM emp ORDER BY salary DESC;

组合排序----同时对多个字段进行排序, 如果第一个字段相同 就按照第二个字段进行排序,以此类推
案例：在薪水排序的基础上,再使用id进行排序, 如果薪水相同就以id 做降序排序

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

3.聚合函数

• 作用：将一列数据作为一个整体，进行纵向的计算。
• 常用的聚合函数：
  1）count(字段) 统计记录数
  2）sum(字段) 求和操作
  3）max（字段）求最大值操作
  4）min（字段）求最小值操作
  5）avg（字段）求平均值操作
• 语法格式：SELECT 聚合函数(字段名) FROM 表名;
  案例：
• 1 查询员工的总数
• 2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
• 3 查询薪水大于4000员工的个数
• 4 查询部门为’教学部’的所有员工的个数
• 5 查询部门为’市场部’所有员工的平均薪水

#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 = '市场部';

4.分组查询

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

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

-- 按照性别进行分组操作
SELECT * FROM emp GROUP BY sex; -- 注意 这样写没有意义

分组的过程：

注意：

1. 分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作.
2. 查询其他字段没有意义
   案例：通过性别字段 进行分组,求各组的平均薪资

# 通过性别字段 进行分组,求各组的平均薪资
SELECT sex, AVG(salary) FROM emp GROUP BY sex;

#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;

• 查询平均薪资大于6000的部门.
  分析:
  1）需要在分组后,对数据进行过滤,使用 关键字 hiving
  2）分组操作中的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）where 进行分组前的过滤
  where 后面不能写 聚合函数
  2）having 是分组后的过滤
  having 后面可以写 聚合函数

5.LIMIT关键字

limit 关键字的作用：

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

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

参数说明：
limit offset , length; 关键字可以接受一个 或者两个 为0 或者正整数的参数

• offset 起始行数, 从0开始记数, 如果省略 则默认为 0.
• length 返回的行数

案例：

• 查询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;

案例**：分页操作 每页显示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.约束

• 作用：约束是指对数据进行一定的限制,来保证数据的完整性 有效性 正确性
• 违反约束的不正确数据,将无法插入到表中
• 常见的约束
  – 主键约束 primary key 不可重复、唯一、非空
  作用：用来表示数据库中的每一条记录
  如何添加主键约束？
  1）语法格式：字段名 字段类型 primary key
  案例： 创建一个带主键的表

-- 方式1 创建一个带有主键的表
CREATE TABLE emp2(
	eid INT PRIMARY KEY,
	ename VARCHAR(20),
	sex CHAR(1)
);

DESC emp2;

-- 方式2 创建
CREATE TABLE emp2(
	eid INT,
	ename VARCHAR(20),
	sex CHAR(1),
	PRIMARY KEY(eid) -- 指定eid为主键
);

-- 方式3 创建表之后 再添加主键
CREATE TABLE emp2(
	eid INT,
	ename VARCHAR(20),
	sex CHAR(1)
);

-- 通过DDL语句 添加主键约束
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

2）DESC 查看表结构
3）测试主键的唯一性 非空性

# 正常插入一条数据
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
主键是给数据库和程序使用的,跟最终的客户无关,所以主键没有意义没有关系,只要能够保证不重复就好,比如 身份证就可以作为主键.
4） 删除主键约束

-- 使用DDL语句 删除表中的主键
ALTER TABLE emp2 DROP PRIMARY KEY;
DESC emp2;

5）主键自增
主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段
的值
关键字:
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)。
案例：创建主键自增的表

-- 创建主键自增的表
CREATE TABLE emp2(
-- 关键字 AUTO_INCREMENT,主键类型必须是整数类型
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
);

添加数据 观察主键的自增

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

修改主键自增的起始位置：
默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下面的方式

-- 修改自增起始值 

-- 重新创建自增主键的表, 自定义自增的其实位置
CREATE TABLE emp2(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(20),
	sex CHAR(1)
)AUTO_INCREMENT=100;

-- 插入数据进行验证
INSERT INTO emp2(ename,sex) VALUES('张百万','男');
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');

6）DELETE和TRUNCATE对自增长的影响

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

• DELETE 只是删除表中所有数据,对自增没有影响
• TRUNCATE：truncate 是将整个表删除掉,然后创建一个新的表自增的主键,重新从 1开始
  案例：

-- 目前最后的主键值是 101
SELECT * FROM emp2;
-- delete 删除表中数据,对自增没有影响
DELETE FROM emp2;
-- 插入数据 查看主键
INSERT INTO emp2(ename,sex) VALUES('张百万','男');
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');

案例：

-- truncate 删除所有数据
TRUNCATE TABLE emp2; -- 自增从1开始

INSERT INTO emp2(ename,sex) VALUES('张百万','男'); -- 1
INSERT INTO emp2(ename,sex) VALUES('艳秋','女'); -- 2

– 非空约束
1）特点： 某一列不予许为空
2）语法格式：字段名 字段类型 not null
案例：为 ename 字段添加非空约束

CREATE TABLE emp2(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	-- 将 ename字段 添加了 非空约束
	ename VARCHAR(20) NOT NULL,
	sex CHAR(1)
);

– 唯一约束 unique
1）特点：表中的某一列不能够重复 (对null值 不做唯一判断)
2）语法格式：字段名 字段类型 unique;
案例：

-- 创建 emp3表 为ename 字段添加 唯一约束
CREATE TABLE emp3(
	eid INT PRIMARY KEY ,
	ename VARCHAR(20) UNIQUE,
	sex CHAR(1)
);

-- 测试唯一约束
INSERT INTO emp3 VALUES(1,'张百万','女');

-- Duplicate entry '张百万' for key 'ename' 不能重复（报错）
INSERT INTO emp3 VALUES(2,'张百万','女');

注意：
主键约束与唯一约束的区别:

1. 主键约束 唯一且不能够为空
2. 唯一约束,唯一 但是可以为空
3. 一个表中只能有一个主键 , 但是可以有多个唯一约束

– 外键约束

– 默认值
1）特点：用来指定某列的默认值
语法格式：字段名 字段类型 DEFAULT 默认值
案例：

--  创建 emp4表, 指定 sex默认为 女
CREATE TABLE emp4(
	eid INT PRIMARY KEY,
	ename VARCHAR(20),
	sex CHAR(1) DEFAULT '女'
);

插入数据：

INSERT INTO emp4(eid,ename) VALUES(1,'杨幂');
INSERT INTO emp4(eid,ename) VALUES(2,'柳岩');

-- 不使用默认值
INSERT INTO emp4(eid,ename,sex) VALUES(3,'蔡徐坤','男');

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账户 -500元
UPDATE account SET money = money - 500 WHERE NAME = 'tom';
-- jack账户 + 500元
UPDATE account SET money = money + 500 WHERE NAME = 'jack';

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

3.3 MySQL事务操作

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

3.3.1手动提交事务

1. 语法格式：开启事务 start transaction; 或者 BEGIN;
   ** 这个语句显式地标记一个事务的起始点。
2. 提交事务 commit;
   表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。
3. 回滚事务 rollback;
   表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中
   对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态

3.3.2 手动提交事务流程

• 执行成功的情况： 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
• 执行失败的情况： 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚。
  
  注意：如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。 如果事务
  中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

3.3.3自动提交事务

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

• MySQL默认是自动提交事务
  

• 如何查看是否是自动提交
  SHOW VARIABLES LIKE ‘autocommit’;
  

• 如何取消自动提交
  SET @@autocommit=off;

3.3.4 事务的四大特性

• 原子性：每个事务都是一个整体，不可再拆分，事务中的所有SQL要么都执行成功 要么都执行失败
• 一致性：事务在执行之前 数据库的状态,与事务执行之后的状态要保持一致
• 隔离性：事务与事务之间不应该相互影响,执行时要保证隔离状态
• 持久性：一旦事务执行成功,对数据的修改是持久的.

3.3.5 隔离级别

MySql的隔离级别

• 各个事务之间是隔离,相互独立.但是如果多个事务对数据库中的同一批数据进行并发访问的时候,就会引发一些问题,可以通过设置不同的隔离级别来解决对应的问题

• 并发访问的问题
  1）脏读: 一个事务读取到了另一个事务没有提交的数据.
  2）不可重复读: 一个事务中 两次读取的数据不一致. 要求的是在一个事务中多次读取时数据是一致的. 这是进行update 操作时引发的问题。
  3）幻读: 一个事务中,一次查询的结果,无法支撑后续的业务操作.查询得到的数据状态不准确,导致幻读

• 设置隔离级别
  1）read uncommitted: 读未提交
  可以防止哪些问题: 无
  2）read committed: 读已提交 (Oracle默认 隔离级别)
  可以防止: 脏读
  3）repeatable read : 可重复读 (MySql默认的隔离级别)
  可以防止: 脏读 ,不可重复读
  4）serializable : 串行化
  可以防止: 脏读 ,不可重复读 ,幻读

  注意: 隔离级别 从小到大 安全性是越来越高的,但是效率是越来越低的,根据不同的情况选择对应的隔离级别

• 隔离级别相关命令
  查看隔离级别
  select @@tx_isolation;

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

-- 查看隔离级别	MySql默认隔离级别  repeatable read
SELECT @@tx_isolation;

-- 设置隔离级别为 读已提交
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED ;

3.3.6 脏读问题及其解决

• 脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱 已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务（rollback），李四的再查看钱没了。
• 解决脏读问题
  将全局的隔离级别进行提升为: read committed
  
  设置读已提交之后可以解决脏读问题
  
  

3.3.7 不可重复读问题及其解决

• 不可重复读
  
  B窗口开启之后查询一下
  
  

注意：两次查询输出的结果不同，到底哪次是对的？
不知道以哪次为准。 很多人认为这种情况就对了，无须困惑， 当然是后面的为准。
– 我们可以考虑这样一种情况:比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客 户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作 人员就不知道以哪个为准了。

• 解决不可重复读问题
  – 将全局的隔离级别进行提升为： repeatable read
  演示：
  
  
  B 窗口事务 先进行第一次查询
  UPDATE account SET money = 1000
  
  

3.3.8 幻读问题及其解决

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

演示：


2）解决幻读：将事务隔离级别设置到最高 SERIALIZABLE ，以挡住幻读的发生
如果一个事务，使用了SERIALIZABLE——可串行化隔离级别时，在这个事务没有被提交之前 , 其他他的线程，只能等到当前操作完成之后，才能进行操作，这样会非常耗时，而且，影响数据库的性能，数据库不会使用这种隔离级别
演示：



事务排队执行，影响效率。