事务

概念

事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。

特点

ACID：

- [Atomicity(原子性)]()

　　　　表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败

- [Consistency(一致性)]()

　　　　表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态

- [Isolation(隔离性)]()

　　　　事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。

- [Durability(持久性)]()

　　　　事务完成之后，它对于系统的影响是永久性的。

事务的使用步骤

隐式（自动）事务：没有明显的开启和关闭，本身就是一条事务可以自动提交，如insert、update、delete。

显式事务：具有明显的开启和结束

使用显示事务：

①开启事务

Set autocommit = 0；

Start trransaction；#可以省略

②编写一组逻辑sql

注意：sql语句支持的是insert、update、delete。

设置回滚点：

Savepoint 回滚名；

③结束事务

提交：commit；

回滚：rollback；

回滚到指定的的地方：rollback to 回滚点名

并发事务

多个事务同时操作同一个数据库的相同数据

①并发问题：

脏读：一个事务读取了其他事务还没有提交的数据

不可重复读：一个事务多次读取，结果不一样

幻读：一个事务去读了其他事务还没有提交的数据，知识读到的是其他事务“插入”的数据

②解决方法：

设置隔离级别来解决并发问题

③隔离级别

                                                  脏读             不可重复读           幻读

Read uncommitted:读未提交       ×                    ×                ×

Read committed: 读已提交          √                    ×               ×

Repeatable read ： 课重复读      √                    √               ×

Serializable：串行化                      √                    √               √

 

视图

MySQL5.1出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成的。

好处：

①简化sql语句

②提搞了sql的重用性

③保护基表的数据，提高了安全

创建

Creat view 视图名

As

查询语句;

修改

方式一：

Creat or replace view 视图名

As

查询语句;

方式二：

Alter view 视图名

As

查询语句；

删除

Drop view 视图1，视图2，。。。;

查看

Desc 视图名;

Show creat view 视图名;

使用

①插入

Insert

②修改

update‘

③删除

Delete

④查看

Select

注意：视图一般用于查询，而不是更新，所以具备以下特点的视图都不允许更新

①包含分组函数，group by’，distinct，having ，union

②join

③常量视图

④where后的子查询用到from中的表

⑤用到了不可更新的表

视图和表的对比

              关键字         是否占用内存                        使用

视图       view           占用较小，只保存sql逻辑     一般用于查询

表            table           保存实际数据                       增删改查

 

常见的约束

Not null：非空，该字段的值必填

Unique：唯一，该字段的值不可重复

Defaault：默认，该字段的值不用手动插入有默认值

Check：检查，mysql不支持

Primary key：主键，该字段的值不可重复并且非空

Foreing key：外键，该字段的值引用了另外的表的字段

主键和唯一的区别：

① 一个表之多有一个主键，但是可有有多个唯一

②主键不允许为空，唯一可以为空

相同点

①都具有唯一性

②都支持组合键，但不推荐

外键：

①用于限制两个表的关系，从表的字段值引用了主表的某字段

②外键列和主表的被动引用列要求类型一致，意义一样，名称无要求

③主表的被引用列要求是一个key（一般是主键）

④插入数据，先插入主表

删除数据，先删除从表在删除主表

可以通过一下两种方式删除主表的记录

方式一：

ALTER TABLE 表名 ADD CONSTRATNT 键名 POREING KEY(majorid) REFERNCES major（id） ON DELETE CASADE;

方式二：

 ALTER TABLE 表名 ADD CONSTRATNT 键名 POREING KEY(majorid) REFERNCES major（id） ON DELETE set null;

创建表时添加约束

Creat table 表名（

       字段名 字段类型 not null ，#非空

       字段名  字段类型 primary key，#主键

     字段名 字段类型 unique，#唯一

字段名 字段类型 default ，#默认

Constraint 约束名 foreign key （字段名） references 主表（被引用列）

）

注意

                       支持类型                  可以起约束名                             

列级约束           除了外键                不可以                               

表级约束           除了非空和默认       可以，但对主键无效

列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求

修改表时添加和删除约束

1. 非空

添加非空

Alter table 表名 modify column【constraint 约束名】 字段名 字段类型 not null；

删除非空

Alter table 表名 modify column 字段名 字段类型；

2. 默认

添加默认

Alter table 表名 modify【constraint 约束名】 column 字段名 字段类型 defult 值；

删除默认

Alter table 表名 modify column 字段名 字段类型；

3.主键

添加主键

Alter table 表名 add【constraint 约束名】 primary key（字段名）；

删除主键

Alter table 表名 drop primary key；

1. 唯一

添加唯一

Alter table 表名 add【constraint 约束名】 unique（字段名）；

删除唯一

Alter table 表名 drop index 索引名；

1. 外键

添加外键

Alter table 表名 add 【constraint 约束名】 foreign key（字段名） reference 主表（被引用列）；

删除外键

Alter table 表名 drop foreign key 约束名；

 自增长列

特点

①不需要手动插入值，可以自动提供序列值，默认从1开始，步长1

Auto_increment_increment

如果要更改起始值：手动插入值

如果要更改步长：更改系统变量

Set auto_inrement_increment = 值

②一个表至多有一个自增长列

③自增长列只能支持数值型

④自增长列必须为一个key

创建表时设置自增长列

Create table 表（

字段名 字段类型 约束 auto_increment

）

修改表时设置自增长列

Alter table 表 modify column 字段名 字段类型 约束 auto_increment

删除自增长列

Alter table 表 modify column 字段名 字段类型 约束

系统变量

全局变量

 

会话变量

 

 

自定义变量

变量是用户自定义的，不是由系统的

用户变量

作用域：针对于当前会话（连接）有效同于会话变量的作用域

应用在任何地方，也就是begin end 里面或外面

 

局部变量

作用域：仅仅在定义它的begin end 中有效

应用位置：begin end 中，必须在第一行

 

对比用户变量和局部变量：

                   作用域         定义的位置                                            语法

用户变量    当前会话       会话中的任何地方                                 必须加@，不限定类型

局部变量    begin end中    只能定义在begin end中，在首行         可不用@，限定类型

存储过程

含义：一组预先编译好的sql语句的集合，批处理语句

①提高代码的重用性

②简化操作

③减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

创建语法

Create procedure 存储过程名（参数列表）

Begin

       存储过程体（一组合法的sql语句）

End

注意：①参数列表包含三部分

参数模式  参数名  参数类型

Rg. In stuname varchar（20）

参数模式

In：该参数可以作为输入，也就是该参数需要调用方可传入值

Out：该参数可以作为输出，也就是该参数的返回值

Inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

②如果存储过程中仅仅只有一句话，begin and 可以省略

存储过程体中的每条sql语句的结尾要求必须加分号

 

存储过程的结尾可以使用delimiter重新设置

语法：delimiter 结束标记

Eg. Delimiteer $

语法调用

Call 存储过程名（实参列表）；