MySQL单表&约束&事务

DQL操作单表

• 排序

# 通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示效果,不会影响真实数据)
SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC]
# 单列排序，只按照某一个字段进行排序, 就是单列排序
-- 降序排序
SELECT * FROM emp ORDER BY salary DESC;
# 组合排序，同时对多个字段进行排序, 如果第一个字段相同 就按照第二个字段进行排序,以此类推
-- 组合排序
SELECT * FROM emp ORDER BY salary DESC, eid DESC;

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

SELECT 聚合函数(字段名) FROM 表名;

聚合函数	作用
count(字段)	统计指定列不为NULL的记录行数
sum(字段)	计算指定列的数值和
max(字段)	计算指定列的最大值
min(字段)	计算指定列的最小值
avg(字段)	计算指定列的平均值

需求：
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 = '市场部';

• 分组查询

# 分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组
SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];
-- 按照性别进行分组操作
SELECT * 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;

# 查询平均薪资大于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的区别

过滤方式	特点
where	where 进行分组前的过滤,where 后面不能写 聚合函数
having	having 是分组后的过滤,having 后面可以写 聚合函数

SQL约束

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

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key

• 主键约束
  特点：不可重复，非空，唯一。

# 方式1 创建一个带主键的表
CREATE TABLE emp2(
-- 设置主键 唯一 非空
eid INT PRIMARY KEY,
ename VARCHAR(20),
sex CHAR(1)
);
-- 删除表
DROP TABLE emp2;
-- 方式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语句进行设置
ALTER TABLE emp2 ADD PRIMARY KEY(eid);
-- 使用DDL语句 删除表中的主键
ALTER TABLE emp2 DROP PRIMARY KEY;
--主键的自增
# 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值

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

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

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

• 非空约束
  非空约束的特点: 某一列不予许为空

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

• 唯一约束
  唯一约束的特点: 表中的某一列的值不能重复( 对null不做唯一的判断 )

CREATE TABLE emp3(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) UNIQUE,
sex CHAR(1)
);

主键约束与唯一约束的区别:

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

• 默认值
  默认值约束 用来指定某列的默认值

-- 创建带有默认值的表
CREATE TABLE emp4(
eid INT PRIMARY KEY AUTO_INCREMENT,
-- 为ename 字段添加默认值
ename VARCHAR(20) DEFAULT '奥利给',
sex CHAR(1)
);

数据库的事务

事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败。
比如: 银行的转账业务,张三给李四转账500元 , 至少要操作两次数据库, 张三 -500, 李四 + 500,这中 间任何一步出现问题,整个操作就必须全部回滚, 这样才能保证用户和银行都没有损失.
语法格式：

功能	语句
开启事务	start transaction; 或者 BEGIN;
提交事务	commit;
回滚事务	rollback;

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

事务的的四大特性ACID

特 性	含义
原 子 性	每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功， 要么都失败。
一 致 性	事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的 总金额是 2000，转账后 2 个人总金额也是 2000.
隔 离 性	事务与事务之间不应该相互影响，执行时保持隔离的状态.
持 久 性	一旦事务执行成功，对数据库的修改是持久的。就算关机，数据也是要保存下来的.

MySQL 事务隔离级别

一个数据库可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库. 数据库的相同数据可能被多个事务同时访问,如果不采取隔离措施,就会导致各种问题, 破坏数据的完整性。事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题。

并发访问的问题	说明
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致, 要求的是在一个事务中多次读取时数据是一致的. 这是进行 update 操作时引发的问题
幻读	一个事务中,某一次的 select 操作得到的结果所表征的数据状态, 无法支撑后续的业务操作. 查询得到的数据状态不准确,导致幻读

• 四种隔离级别
  
• 隔离级别相关命令

# 查看隔离级别
select @@tx_isolation;
# 设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化
set global transaction isolation level 级别名称;
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串行化