MySQL学习笔记任务二

任务二：MySQL单表、约束和事务

DQL排序查询

sqlyang进行新建数据库

在新建的数据库db2上复制表

排序操作，使用order by 子句

语法结构

select 字段名 from 表名 【where 字段名 = 值】 order by 字段名

asc 升序排序

desc 降序排序

若无声明 则默认为升序排序

单列排序

select * from 表名 order by salary；

进行升序或降序排序

组合排序

select * from 表名 【where 条件】order by salary desc，eid desc；

组合排序的特点：如果第一个字段的值相同，则按照第二个字段的值来进行排序。

DQL聚合函数

聚合函数作用：将一列数据看做一个整体，进行纵向的计算

常用的聚合函数：

count（字段）用于统计个数

sum（字段）用于求和

max（字段）用于求最大值

min（字段）用于求最小值

avg（字段）用于求平均值

语法格式：

select 聚合函数 （列名或字段名） from 表名 【where 条件】；

查询员工的总个数

select count （*）from 表名；

count函数在统计的时候会忽略控制；

注意事项：不要把含有空值的字段去进行聚合函数count操作。

查看薪水

查询薪水大于4000的员工的个数

select count (*) from 表名 where salary > 4000;

查询部门为‘教学部’的所有员工的个数

select count （*） from 表名 where dept_name = ‘教学部’；

查询部门为‘市场部’的所有员工的平均薪水

select avg （salary）from 表名 where dept_name = ‘市场部’；

DQL分组查询

分组查询，使用 group by 子句

语法格式：

select 分组字段/聚合函数 from 表名 group by 分组字段

分组过程

将分组完成的第一条数据进行返回

分组的目的：

为了统计操作，一般分组会和聚合函数一起使用，才有意义，另外，查询的时候也要查询分组字段

通过性别分组，求各组的平均薪资

select sex ， avg（salary） from 表名 group by sex；

查询所有部门信息

select dept_name as ‘部门信息’ from 表名 group by dept_name；

查询每个部门的平均薪资

select dept_name，avg（salary）from 表名 group by dept_name；

查询每个部门的平均薪资，部门名称不能为null

select

dept_name，avg（salary）

from 表名

where dept_name is not null

group by dept_name；

查询平均薪资大于6000的部门

第一步：先分组查询，将所有部门的平均薪资查询出来

第二步：使用having关键字，将分组查询出的数据进行筛选

select

dept_name，

avg（salary）

from 表名

where dept_name is not null

group by dept_name

having avg（salary）> 6000；

where和having的区别：

limit关键字

limit关键字作用 ：指定查询的条数，行数

语法格式：

select 字段 from 表名 limit offset，length；

查询表中的前5条数据

select * from 表名 limit 0，5；

select * from 表名 limit 5；

查询表中，从第四条开始查询，共查询六条

select * from 表名 limit 3,6；

分页操作

分页公式：起始行数 = （当前页码-1）* 每页显示条数

约束的介绍

约束介绍

主键约束

主键约束：

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

作用：用来表示数据库中的每一条记录，让其合理化

方式一：创建一个带主键的表

查看表的结构，使用DDL语言，desc来操作

方式二：创建表，指定字段为主键

方式三：先创建表，在使用DDL语句去添加主键

删除主键

使用DDL语句

alter table 表名 drop primary key；

主键自增

使用auto_increment 关键字，实现主键的自动增长，注意字段类型必须是整数类型

创建主键自增的表

添加数据，观察主键变化

修改自增起始值，也就是自定义主键的起始值

在表的创建字段后的括号外添加auto_increment = 自定义起始值

添加数据，观察主键变化

DELETE和TRUNCATE对自增长的影响

delete和truncate对自增的影响

delete方式删除所有数据

对自增没有影响，删除过后在进行数据插入，则主键从上一个数值继续自增

truncate删除所有数据

对自增有影响，在进行数据插入时，自增从1开始

非空约束

非空约束介绍

语法格式

唯一约束

唯一约束

特点：表中的某一列不能够重复（但对null值，不做唯一判断）

语法格式： 字段名 字段类型 unique

创建表，对ename添加唯一约束

添加数据进行测试唯一约束

主键约束和唯一约束的区别：

1. 主键约束：是唯一的且不能为null值
2. 唯一约束：是唯一的但是可以为null值

1. 一个表中只能有一个主键，但是可以有多个唯一约束

默认值

默认值：用来指定某一列的默认值

语法格式：字段名 字段类型 defaullt 默认值

创建表，将性别设置为默认值：女

添加信息，查看，性别为女

不适用默认值进行添加，则会覆盖默认值

事务的基本概念&转账操作演示

事务的基本概念：是一个由一条或多条SQL组成的一份整体，事务中的操作要么全部成功，要么全部失败

由转账操作来进行演示，将整个转账过程封装为一个事务。

使用数据表来进行演示

执行转账操作，操作成功

操作失败，报错，事务回滚，撤销tom账户执行的命令。

mysql手动提交事务

手动提交事务操作

dos命令行中进行操作转账

开启事务

执行转账操作

执行转账操作

commit提交事务

若没有commit提交事务，则事务未运行完，数据表中将不会有相关反应（账户余额在commit提交事务前无任何反应）

若事务执行中出错，则会回滚，撤销上一步操作。

mysql自动提交事务

自动提交事务，为mysql的默认方式

每执行一条DML语句，都是一个单独的事务

事务的开始和提交自动运行，不在需要手动。

执行转账操作

默认自动提交，并没有commit，就能看到数据变化

修改mysql的提交方式

修改为手动后，操作执行完，需手动commit操作

数据变化

mysql的四大特征

事务的四大特性

原子性：每个事务都是一个整体，不可以在拆分，事务中的所有SQL语句，要么都成功，要么都失败。

一致性：数据库的状态在事务的执行前后要保持一致，比如转账操作中，事务执行前salary中的总数为2000，则事务执行后的金额总数也为2000.

隔离性：事务与事务之间不应该相互影响，执行时要保证隔离状态。

持久性：事务一旦执行成功，对数据的修改是持久的，关机啥的，不会影响数据。

mysql事务隔离级别

事务的隔离级别介绍

并发访问的问题：脏读、不可重复读、幻读。

设置隔离级别来防止并发访问的问题

read uncommitted 读未提交 无法防止问题

read committed 读已提交 可防止脏读

repeatable reaf 可重复读 可防止脏读、不可重复读。

serializable 串行化 可防止脏读、不可重读读、幻读。

注意：

隔离级别越大，安全性越高，但效率低

总结

隔离级别相关命令

隔离级别相关命令

查看隔离级别

设置隔离级别

设置修改完后，需新建数据库连接，即可查看修改后的结果。

脏读演示与解决

脏读演示

将事务的隔离级别设置为最低级别

重新打开数据库查看事务隔离级别

新建数据库窗口，代表两个事务

事务a进行数据修改，但不提交

事务b进行数据读取查看

事务a进行数据修改，但并没有commit提交，但同时在事务b中对数据的读取发生了变化

若此时事务a执行回滚操作，事务b读取数值也随着变化。

事务隔离级别设置为 read committed

演示效果：

事务a执行的转账操作未提交时，事务b查询结果无变化。

不可重复读演示及解决

不可重读演示

事务a 执行修改操作提交

事务b对数据的查询前后不一致

导致问题：同一事务中，对同一数据的查询，前后两次不一致

解决方法：将事务的隔离级别修改为：repeatable read

效果如下

幻读演示及解决

幻读介绍

打开a，b窗口

事务a执行查询，事务b执行操作

事务a在想插入数据，则会报错

解决方法，将事务隔离级别提升至 serializable

解决效果：

注意：当事务b执行插入操作时，不会立即完成，要等待事务a操作完毕

所以，这事务隔离级别效率低。