数据库——数据表单表、约束和事务

查询

简单查询

• 语法：select <字段1，字段2…>from<表名>;
• 查询所有的字段,语法：select * from 表名;
• 别名查询：select 字段1 as 别名 from 表名;(最后一列，as可以省略)
• 查询数据去重 select distinct 字段名 from 表名;
• 查询的时候，给某列中的数值+1000：select 字段名+1000 from 表名;
• 注意事项：
• • 查询操作不会对数据库中的数据进行修改，只是一种显示的方式
• • 例如上方给某列数值+1000 的操作，只是展示的时候加了1000，实际上表中的结果未发生改变
    

条件查询

• 基本语法：select<字段名>from <表名> where 表达式A and 表达式B and … and 表达式N
• 编写条件查询，需要确认三件事情
• • 1.查哪张表
• • 2.查哪些字段
• • 3.查询条件是什么
• 条件查询，是先取出表中的每条数据，满足条件就返回，不满足就过滤掉
• 条件表达式用到的运算符如下：

类型	运算符	说明
比较运算符	> 、< 、>=、 <= 、=、 <> 、!=	大于、小于、小于（大于）等于、不等于
比较运算符	BETWEEN …… AND	显示再某一区间的值， 例如2000到10000之间
比较运算符	IN(集合)	集合表示多个值，使用逗号隔开， 例如:name in(A,B)； in中的每个数据都会作为一次条件， 只要满足条件就会显示， 数据匹配括号中的参数
比较运算符	NOT IN(集合)	集合表示多个值，使用逗号隔开 表示不在集合中的数据 例如:name not in(A,B)； 名字不在集合中
比较运算符	LIKE ‘%张%’	模糊查询，%为通配符，表示匹配任意多个字符串
比较运算符	LIKE ‘_张_’	模糊查询，_为通配符，表示匹配一个字符
比较运算符	IS NULL	查询某列为N ULL的值， 注意，这里不能写=NULL
逻辑运算符	And 、&&	多个条件同时成立
逻辑运算符	Or 、I I (英文)	多个条件任意一个成立
逻辑运算符	Not	不成立，取反

• 条件查询的示例如下
  
  
  

排序

• 关键字：order by子句
• 单列排序语法结构：
• • select 字段名 from 表名 [where 字段名 = 值] order by 字段名称 [asc/desc];，语法结构中的[]指的是选填
• • asc 代表升序排序，desc代表降序排序；不写的情况下默认升序排序
• 组合排序语法结构：
• • select 字段名 from 表名 [where 字段名 = 值] order by 字段名称1 [asc/desc],字段名称2 [asc/desc]，语法结构中的[]指的是选填
• • 特点：同时对多个字段进行排序，先按照第一个字段排序，第一个字段相等的情况下，按照第二个字段排序
• • 示例如下：
    

聚合函数

• 将一列数据作为一个整体，进行纵向的计算（按照列计算）
• • 例如统计数据表中，薪资这一列的平均薪资的场景
• 常用的聚合函数有：
• • count(字段):统计表中的记录数；
• • • count函数在统计的时候会忽略空值；因此不要使用含null的列count
• • sum(字段)：求和操作
• • min(字段):求最小值
• • max(字段):求最大值
• • avg(字段):求平均值
• 字符串拼接函数 concat（字段A，字段B）
• 字符串截取函数 substr(字段A，截取开始额为止position,截取字符个数）
• 获取字符串长度 length（字段A）
• 示例如下：
  
  

分组查询

• 关键字：使用group by 子句
• 语法格式：select 分组字段/聚合函数 from 表名 group by 分组字段
• 分组的过程（假设按照性别分组）：
• • 第一步：将性别相同的数据分为一组
• • 第二步：返回的是每组的第一条数据
• select 和from之间，使用分组字段或聚合函数的原因是：
• • 分组是指将具有相同特征的数据看作一个整体
• • 分组的目的是为了做统计操作，一般会和聚合函数进行使用
• • 另外查询的时候，要查询分组字段，以知道是按照什么分组的
• having，在分组之后，进行条件过滤，使用having判断
  

having和where的区别

• where：在分组前进行过滤；where不能跟聚合函数
• having：是在分组之后进行条件过滤，having后可以跟聚合函数

去重

• 关键字：distinct
• 语法结构：select distinct 字段名1 from 表名
  

limit关键字

• 通过limit 来指定查询的数据的条数/行数
• 语法格式：select 字段 from 表名 limit offect， length;
• • offect:起始行数，默认从0开始计数
• • length：想要返回的行数（要查询数据的条数）
• limit 分页操作
• • 起始行数=( 当前页码-1)*每页显示条数
    
• 编写SQ L语句时的顺序：
• select from where (group by) having (order by) limit
• 也就是说：select后面加上from没问题
• • 后面如果进行判断时，加上where进行关联
• • 如果还要加上分组的话，在使用group by进行分组
• • 分组后要在进行过滤的话，要再加上having
• • 如果还有排序要求，再加上order by
• • 最后可以加上limit进行分页

SQL约束

• 约束是指对数据进行一定的限制(例如哪些数据不允许为空)，来保证数据的完整性、有效行和正确性
• 常见的约束有：
• • 主键约束:primary key
• • 唯一约束：unique
• • 非空约束：not null
• • 外键约束：foreign key（详见多表）

主键约束

• 主键约束的特点：不可重复、唯一、非空
• 主键约束的作用：用来表示数据库中的每一条记录
• 语法格式：
• • 创建表的时候：字段名 字段类型 primary key
    
• 主键的自增设置，关键字：auto_increment
• • 使用这种方式，字段类型必须是整数类型
    
• 修改主键自增起始值
• • 注意，是在创建表的后括号和分号之间添加自增语句
• • 下面语句的含义是，新增数据时，第一条数据的主键是100
    
• delete和truante对主键自增的影响
• • delete 删除表中的所有数据，将表中的数据逐条删除
• • truante 删除表中的所有数据，将这个表删除之后，再创建一个相同结构的表
• • delete删除对自增没有影响，跟着删除之前的数值往上增长
• • truante删除数据之后，自增从1开始

非空约束

• 特点：某一列，不允许为空
• 语法格式：字段名 字段类型 not null
• 设置之后，设置非空的那一列没有填写信息的情况下会报错
  

唯一约束

• 特点：表中的某一列不能重复
• 唯一约束不对NULL做唯一判断
• 语法格式：字段名 字段类型 unique
• 设置唯一约束的列，填写相同的数值会报错
  
• 主键约束和唯一约束的区别
• • 主键约束：唯一、不能为空（不允许时NULL）
• • 唯一约束：唯一， 但是可以为空（NULL）
• • 一个表中只能有一个主键，但是可以有多个唯一约束

默认值

• 特点：用来指定某一列的模式值
• 语法格式：字段名 字段类型 default 默认值
• 如果再插入数据时，对默认值的字段赋值，也可以赋值成功
  

事务

• 事务是一个整体，是由一条或者多条SQL语句组成，这些SQL语句要么都执行成功，要么都执行失败
• • 只要有一条SQL出现异常，整个操作就会回滚，整个业务就会执行失败
• • 也就是说将一些不能分开执行的SQL写在一起，例如转账操作。一个账户金额减少，接收账户的金额必须增加，如果一个出错，全部回滚
• 回滚：即在事务运行过程中发生了某些故障，事务不能继续执行
• • 系统将事务中的数据库的所有已完成的操作全部撤销，滚回到事务开始是的状态（在提交之前执行）

事务操作

• 手动提交事务：
• • 开启事务：start transaction; 或者begin;
• • 提交事务：commit
• • 回滚事务：rollback
• 手动提交事务的流程：
• • 开启事务之后，执行多条SQL
• • 如果成功，就提交事务
• • 如果失败，就回滚事务
    
• 如果SQL执行失败，或者关闭cmd命令操作弹窗，数据库会自动回滚
• 自动提交事务：
• • Mysql默认的提交方式
• • 每执行一条DML（增删改操作），都是一个单独的事务
• 登录mysql之后，查看事务的状态，
• • 语法结构：show variables like ' autocommit;'
• • 如果autocommit显示为ON，表示当前的提交方式是自动提交
• 关闭事务的自动提交，语法结构：set @@autocommit=off;
• • 执行之后，再查看事务的状态，autocommit就显示为OFF
• 自动提交的事务关闭之后，执行SQL语句之后，必须commit，数据表中的数值才会发生变动

事务的四大特性

• 原子性：每个事务都是一个整体，不可再拆分，事务中所有的SQL语句要么都执行成功，要么都否执行失败
• 一致性：事务在执行前数据库的状态和执行后数据库的状态保持一致
• 持久性：事务和事务之间互不影响，执行时保持隔离的状态
• 隔离性：一旦事务执行成功，对数据库的修改是持久的，就算关机，数据也是要保存下来的

Mysql事务隔离

• 各个事务之间是隔离，相互独立的
• 但是如果多个事务对数据库的同一批数据进行并发访问，就会引发一些问题
• 可以通过设置不同的隔离级别来解决对应的问题
• 并发访问的问题：
• • 脏读，一个事务读取到了另外一个事务没有提交的数据
• • 不可重复读，一个事务中，两次读取的数据不一致
• • 幻读：一个事务中，一次查询的结果，无法支持后续的业务操作
• • • 例如：做一个插入操作，在此之前确认数据是否存在，没有存在的情况下直接插入，报数据已经存在
• 隔离级别：
• • read uncommitted：读未提交，可以防止哪些问题？无
• • read committed ： 读已提交，可以防止脏读
• • repeatable read：可重复读，可以防止脏读，不可重复读（musql默认的隔离级别）
• • serializable：串行化，可以防止脏读，不可重复读，幻读
• 注意：隔离级别从小到大，安全性是越来越高的，但是效率是越来越低的，根据不同的情况选择对应的隔离级别
• 隔离级别的相关命令：
• • 查看隔离级别：select @@t x_isolation;
• • 设置隔离级别：set global transaction isolation level 级别名称;
• 修改完隔离级别之后，需要重新打开一个服务器命令，执行查询语句即可
  
• 解决并发访问的问题：
• 解决脏读的问题，就是将全局的隔离级别进行提升为read committed
• • 命令：set global transaction isolation level read committed;
• • 然后重启数据库窗口，确认设置是否成功
• 解决不可重复读的问题，就是将全局的隔离级别进行提升为epeatable read
• • 命令：set global transaction isolation level repeatable read;
• • 然后重启数据库窗口，确认设置是否成功
• • 同一个事务中，两次读取的结果不一样的问题，可能导致第一次发给主机的数据和发给客户短信通知的数据不一致的问题，尤其是金钱
• 解决脏读的问题，就是将全局的隔离级别进行提升为serializable
• • 命令：set global transaction isolation level serializable;
• • 然后重启数据库窗口，确认设置是否成功