Mysql数据库（二） 单表&约束&索引

二、MySQL单表&约束&事务

DQL操作单表

排序

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

• 语法结构

  SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC]
  -- ASC 表示升序排序(默认)
  -- DESC 表示降序排序
  

• 排序方式

-- 单列排序 只按照某一个字段进行排序, 就是单列排序 
-- 默认升序排序 
ASC SELECT * FROM emp ORDER BY salary;
-- 降序排序 
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 查询员工的总数 -- 统计表中的记录条数 使用 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;
  

分组

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

• 语法格式

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

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

• having 和 where的区别

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

limit关键字

• limit 关键字的作用

  limit是限制的意思,用于 限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)

  limit 语法是 MySql的方言,用来完成分页

• 语法结构

  SELECT 字段1,字段2... FROM 表名 LIMIT offset,length;
  
  -- limit offset , length; 关键字可以接受一个 或者两个 为0 或者正整数的参数
  -- offset 起始行数, 从0开始记数, 如果省略 则默认为 0.
  -- length 返回的行数
  

  -- 分页操作 每页显示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中的方言
  

SQL约束

约束的作用

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

常见的约束

• 约束

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

主键约束

• 特点：不可重复 唯一 非空

• 作用：用来表示数据库中的每一条记录

• 基本操作

操作	语法格式
添加主键约束	创建表时添加： 字段名 字段类型 primary key primary key(字段名) 创建的时候不指定主键,然后通过 DDL语句进行设置： ALTER TABLE emp2 ADD PRIMARY KEY(eid);
删除主键约束	ALTER TABLE emp2 DROP PRIMARY KEY;
主键自增	AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

• 常作为主键的字段

  通常针对业务去设计主键,每张表都设计一个主键id

  主键是给数据库和程序使用的,跟最终的客户无关,所以主键没有意义没有关系,只要能够保证不重复 就好,比如 身份证就可以作为主建

• DELETE和TRUNCATE对自增长的影响
  删除表中所有数据有两种方式

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

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

非空约束

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

• 语法格式

  字段名 字段类型 not null 
  

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

唯一约束

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

• 语法格式

  字段名 字段值 unique
  

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

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

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

外键约束

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

默认值

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

• 语法格式

  字段名 字段类型 DEFAULT 默认值
  

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

数据库事务

基本概念

• 事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有 一条SQL出现异常,整个操作就会回滚,整个业务执行失败
• 回滚
  即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成 的操作全部撤销，滚回到事务开始时的状态。（在提交之前执行）

MySQL事务操作

• MYSQL 中可以有两种方式进行事务的操作：

  手动提交事务

  自动提交事务

手动提交事务

• 语法格式

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

• START TRANSACTION

  • 这个语句显式地标记一个事务的起始点。

• COMMIT

• 表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写 到磁盘上的物理数据库中，事务正常结束。

• ROLLBACK

  • 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中 对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状

• 手动提交事务流程

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

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

• 示例

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

自动提交事务

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

• MySQL默认是自动提交事务

• 取消自动提交
  MySQL默认是自动提交事务,设置为手动提交.

  -- 登录mysql，查看autocommit状态
  SHOW VARIABLES LIKE 'autocommit';
  -- 设置为手动提交
  SET @@autocommit=off;
  

事务的四大特性 ACID

• 四特性

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

MySQL 事务隔离级别

数据并发访问

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

并发访问会产生的问题

• 事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。

但因为并发操作，多个用户同时访问 同一个 数据，可能引发并发访问的问题

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

四种隔离级别

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

• ✔ 会出现问题，✘ 不会出现问题

级 别	名字	隔离级别	脏 读	不可重复 读	幻 读	数据库的默认隔离级 别
1	读未提交	read uncommitted	✔	✔	✔
2	读已提交	read committed	✘	✔	✔	Oracle和SQLServer
3	可重复读	repeatable read	✘	✘	✔	MySql
4	串行化	serializable	✘	✘	✘

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

隔离级别相关命令

• 查看隔离级别

  select @@tx_isolation
  

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

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