mysql的基本数据操作(最详细版本)

目录

引文

1、什么是数据库

2、从mysql入门sql语言

3、DDL、DML数据库的表操作语句

 3、表字段的列类型

4、约束的总结

5、表视图的概念

6、存储过程

7、事物处理

结言：

---

引文

本篇介绍mysql的组基本操作语句和代码演示，包括数据操作、表操作、查询操作、事物管理和存储过程，从0到1手把手的入门操作讲解。

1、什么是数据库

        数据库（Database，DB）指的是以一定格式存放、能够实现多个用户共享、与应用程序彼此独立的数据集合。

 数据库分为关系型数据库和非关系型数据库。

• 关系型数据库

关系型数据库模型是将复杂的数据结构用较为简单的二元关系（二维表）来表示，如下图所示。在该类型数据库中，对数据的操作基本上都建立在一个或多个表格上，我们可以采用结构化查询语言（SQL）对数据库进行操作。关系型数据库是目前主流的数据库技术，其中具有代表性的数据库管理系统有：Oracle、DB2、SQL Server、MySQL等。

• 非关系型数据库(NOSQL)

NOSQL（Not Only SQL）泛指非关系型数据库。关系型数据库在超大规模和高并发的web2.0纯动态网站已经显得力不从心，暴露了很多难以克服的问题。NOSQL数据库的产生就是为了解决大规模数据集合多重数据种类带来的挑战，尤其是大数据应用难题。常见的非关系型数据库管理系统有Memcached、MongoDB，redis，HBase等。 

2、从mysql入门sql语言

        本篇以关系型数据库mysql为例对sql语言展开进一步的基础讲解。

        什么是sql语言？

SQL（Structured Query Language）是结构化查询语言的简称，它是一种数据库查询和程序设计语言，同时也是目前使用最广泛的关系型数据库操作语言。在数据库管理系统中，使用SQL语言来实现数据的存取、查询、更新等功能。SQL是一种非过程化语言，只需提出“做什么”，而不需要指明“怎么做”。

        sql语言的五种组成部分：

• 数据查询语言DQL（Data Query Language）

DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。

• 数据操作语言DML（Data Manipulation Language）

DML主要用于对数据库中的数据进行增加、修改和删除的操作，其主要包括：

   1) INSERT：增加数据

   2) UPDATE：修改数据

   3) DELETE：删除数据

• 数据定义语言DDL（Data Definition Language） 

DDL主要用针对是数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）进行创建、修改和删除操作。其主要包括：

   1) CREATE：创建数据库对象

   2) ALTER：修改数据库对象

   3) DROP：删除数据库对象

•  数据控制语言DCL（Data Control Language）

DCL用来授予或回收访问 数据库的权限，其主要包括：

   1) REVOKE：回收授予的某种权限

   2) GRANT：授予用户某种权限

• 事务控制语言TCL（Transaction Control Language）

        TCL用于数据库的事务管理。其主要包括：

   1) START TRANSACTION：开启事务

   2) COMMIT：提交事务

   3) ROLLBACK：回滚事务

   4) SET TRANSACTION：设置事务的属性 

3、DDL、DML数据库的表操作语句

表（Table）是数据库中数据存储最常见和最简单的一种形式，数据库可以将复杂的数据结构用较为简单的二维表来表示。二维表是由行和列组成的，分别都包含着数据，如表所示

话不多说，创建一张t_student的学生表，建表语句如下：

/*

建立一张用来存储学生信息的表

字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息

*/

-- 创建数据库表：

create table t_student(

        sno int(6), -- 6显示长度

        sname varchar(5), -- 5个字符

        sex char(1),

        age int(3),

        enterdate date,

        classname varchar(10),

        email varchar(15)

);

查看表结构的语句：

-- 查看表的结构：展示表的字段详细信息

desc t_student;

 查看建表语句：

-- 查看建表语句：

show create table t_student;

 3、表字段的列类型

数据库表的列类型主要包括：

• 整数类型

MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示

主键自增：不使用序列，通过auto_increment，要求是整数类型

• 浮点数类型

需要注意的是与整数类型不一样的是，浮点数类型的宽度不会自动扩充。 score double(4,1)

 score double(4,1)--小数部分为1位，总宽度4位，并且不会自动扩充。

• 字符串类型

CHAR和VARCHAR类型相似，均用于存于较短的字符串，主要的不同之处在于存储方式。CHAR类型长度固定，VARCHAR类型的长度可变。

因为VARCHAR类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段具体需要多少字符时推荐使用VARCHAR类型，这样可以大大地节约磁盘空间、提高存储效率。

CHAR和VARCHAR表示的是字符的个数，而不是字节的个数

• 日期和时间类型

TIMESTEMP类型的数据指定方式与DATETIME基本相同，两者的不同之处在于以下几点：

(1) 数据的取值范围不同，TIMESTEMP类型的取值范围更小。

(2) 如果我们对TIMESTAMP类型的字段没有明确赋值，或是被赋与了NULL值，MySQL会自动将该字段赋值为系统当前的日期与时间。

(3) TIMESTEMP类型还可以使用CURRENT_TIMESTAMP来获取系统当前时间。

(4) TIMESTEMP类型有一个很大的特点，那就是时间是根据时区来显示的。例如，在东八区插入的TIMESTEMP数据为2017-07-11 16:43:25，在东七区显示时，时间部分就变成了15:43:25，在东九区显示时，时间部分就变成了17:43:25。

DML_添加数据：

-- 在t_student数据库表中插入数据：

insert into t_student values (1,'张三','男',18,'2022-5-8','软件1班','123@126.com');

insert into t_student values (10010010,'张三','男',18,'2022-5-8','软件1班','123@126.com');

insert into t_student values (2,'张三','男',18,'2022.5.8','软件1班','123@126.com');

insert into t_student values (2,"张三",'男',18,'2022.8','软件1班','123@126.com');

insert into t_student values (7,"张三",'男',18,now(),'软件1班','123@12com');

insert into t_student values (9,"易烊千玺",'男',18,now(),'软件1班','123@126.com');

insert into t_student (sno,sname,enterdate) values (10,'李四','2023-7-5')

注意事项：

• int  宽度是显示宽度，如果超过，可以自动增大宽度 int底层都是4个字节
• 时间的方式多样  '1256-12-23'  "1256/12/23"  "1256.12.23"
• 字符串不区分单引号和双引号
• 如何写入当前的时间  now() , sysdate() , CURRENT_DATE()
• char varchar 是字符的个数，不是字节的个数，可以使用binary，varbinary表示定长和不定长的字节个数。 
• 如果不是全字段插入数据的话，需要加入字段的名字

DML_修改，删除数据:

-- 修改表中数据

update t_student set sex = '女' ;

update t_student set sex = '男' where sno = 10 ;

UPDATE T_STUDENT SET AGE = 21 WHERE SNO = 10;

update t_student set CLASSNAME = 'java01' where sno = 10 ;

update t_student set CLASSNAME = 'JAVA01' where sno = 9 ;

update t_student set age = 29 where classname = 'java01';

-- 删除操作：

delete from t_student where sno = 2;

注意事项：

1.关键字，表名，字段名不区分大小写

2.默认情况下，内容不区分大小写

3.删除操作from关键字不可缺少

4.修改，删除数据别忘记加限制条件

DDL_修改，删除数据库表

-- 修改表的结构：

-- 增加一列：

alter table t_student add score double(5,2) ; -- 5:总位数  2：小数位数

update t_student set score = 123.5678 where sno = 1 ;

-- 增加一列（放在最前面）

alter table t_student add score double(5,2) first;

-- 增加一列（放在sex列的后面）

alter table t_student add score double(5,2) after sex;

-- 删除一列：

alter table t_student drop score;

-- 修改一列：

alter table t_student modify score float(4,1); -- modify修改是列的类型的定义，但是不会改变列的名字

alter table t_student change score score1 double(5,1); -- change修改列名和列的类型的定义

-- 删除表：

drop table t_student;

表的完整性约束：

为防止不符合规范的数据存入数据库，在用户对数据进行插入、修改、删除等操作时，MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据的准确性和一致性，这种机制就是完整性约束。

MySQL中主要支持以下几种种完整性约束，如表所示。 其中Check约束是MySQL8中提供的支持。

约束条件	约束描述
PRIMARY KEY	主键约束，约束字段的值可唯一地标识对应的记录
NOT NULL	非空约束，约束字段的值不能为空
UNIQUE	唯一约束，约束字段的值是唯一的
CHECK	检查约束，限制某个字段的取值范围
DEFAULT	默认值约束，约束字段的默认值
AUTO_INCREMENT	自动增加约束，约束字段的值自动递增
FOREIGN KEY	外键约束，约束表与表之间的关系

非外键约束的语句展示(外键约束后面单独讲)

/*

建立一张用来存储学生信息的表

字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息

约束：

建立一张用来存储学生信息的表

字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息

【1】学号是主键 = 不能为空 +  唯一 ，主键的作用：可以通过主键查到唯一的一条记录【2】如果主键是整数类型，那么需要自增

【3】姓名不能为空

【4】Email唯一

【5】性别默认值是男

【6】性别只能是男女

【7】年龄只能在18-50之间

*/

-- 创建数据库表：

create table t_student(

        sno int(6) primary key auto_increment,

        sname varchar(5) not null,

        sex char(1) default '男' check(sex='男' || sex='女'),

        age int(3) check(age>=18 and age<=50),

        enterdate date,

        classname varchar(10),

        email varchar(15) unique

);

-- 添加数据：

--  1048 - Column 'sname' cannot be null 不能为null

-- 3819 - Check constraint 't_student_chk_1' is violated. 违反检查约束

insert into t_student values (1,'张三','男',21,'2023-9-1','java01班','zs@126.com');

-- 1062 - Duplicate entry '1' for key 't_student.PRIMARY' 主键重复

-- > 1062 - Duplicate entry 'ls@126.com' for key 't_student.email' 违反唯一约束

insert into t_student values (2,'李四','男',21,'2023-9-1','java01班','ls@126.com');

insert into t_student values (3,'露露','男',21,'2023-9-1','java01班','ls@126.com');

-- 如果主键没有设定值，或者用null.default都可以完成主键自增的效果

insert into t_student (sname,enterdate) values ('菲菲','2029-4-5');

insert into t_student values (null,'小明','男',21,'2023-9-1','java01班','xm@126.com');

insert into t_student values (default,'小刚','男',21,'2023-9-1','java01班','xg@126.com');

-- 如果sql报错，可能主键就浪费了，后续插入的主键是不连号的，我们主键也不要求连号的

insert into t_student values (null,'小明','男',21,'2023-9-1','java01班','oo@126.com');

        约束的分类，主要分为两种，列级约束和表级约束：

•  表级约束：可以约束表中任意一个或多个字段。与列定义相互独立，不包含在列定义中；与定义用‘，’分隔；必须指出要约束的列的名称；

• 列级约束：包含在列定义中，直接跟在该列的其它定义之后 ，用空格分隔；不必指定列名；

-- 创建数据库表：

create table t_student(

        sno int(6) auto_increment,

        sname varchar(5) not null,

        sex char(1) default '男',

        age int(3),

        enterdate date,

        classname varchar(10),

        email varchar(15),

        constraint pk_stu primary key (sno),  -- pk_stu 主键约束的名字

        constraint ck_stu_sex check (sex = '男' || sex = '女'),

        constraint ck_stu_age check (age >= 18 and age <= 50),

        constraint uq_stu_email unique (email)

);

-- 添加数据：

insert into t_student values (1,'张三','男',21,'2023-9-1','java01班','zs@126.com');

-- > 3819 - Check constraint 'ck_stu_sex' is violated.

-- > 3819 - Check constraint 'ck_stu_age' is violated.

-- > 1062 - Duplicate entry 'zs@126.com' for key 't_student.uq_stu_email'

insert into t_student values (3,'李四','男',21,'2023-9-1','java01班','zs@126.com');

创建表之后追加约束：

-- 创建数据库表：

create table t_student(

        sno int(6),

        sname varchar(5) not null,

        sex char(1) default '男',

        age int(3),

        enterdate date,

        classname varchar(10),

        email varchar(15)

);

-- > 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

-- 错误的解决办法：就是auto_increment去掉

-- 在创建表以后添加约束：

alter table t_student add constraint pk_stu primary key (sno) ; -- 主键约束

alter table t_student modify sno int(6) auto_increment; -- 修改自增条件

alter table t_student add constraint ck_stu_sex check (sex = '男' || sex = '女');

alter table t_student add constraint ck_stu_age check (age >= 18 and age <= 50);

alter table t_student add constraint uq_stu_email unique (email);

4、约束的总结

1.主键约束  

主键约束（PRIMARY KEY，缩写PK），是数据库中最重要的一种约束，其作用是约束表中的某个字段可以唯一标识一条记录。因此，使用主键约束可以快速查找表中的记录。就像人的身份证、学生的学号等等，设置为主键的字段取值不能重复（唯一），也不能为空（非空），否则无法唯一标识一条记录。

主键可以是单个字段，也可以是多个字段组合。对于单字段主键的添加可使用表级约束，也可以使用列级约束；而对于多字段主键的添加只能使用表级约束。

2.非空约束  

非空约束（NOT NULL，缩写NK）规定了一张表中指定的某个字段的值不能为空（NULL）。设置了非空约束的字段，在插入的数据为NULL时，数据库会提示错误，导致数据无法插入。

无论是单个字段还是多个字段非空约束的添加只能使用列级约束（非空约束无表级约束）

为已存在表中的字段添加非空约束

 alter   table student8 modify stu_sex varchar(1) not null;

使用ALTER TABLE语句删除非空约束

alter  table student8 modify stu_sex varchar(1) null;  

3. 唯一约束

唯一约束（UNIQUE，缩写UK）比较简单，它规定了一张表中指定的某个字段的值不能重复，即这一字段的每个值都是唯一的。如果想要某个字段的值不重复，那么就可以为该字段添加为唯一约束。

无论单个字段还是多个字段唯一约束的添加均可使用列级约束和表级约束

4. 检查约束

检查约束（CHECK）用来限制某个字段的取值范围，可以定义为列级约束，也可以定义为表级约束。MySQL8开始支持检查约束。

5. 默认值约束  

默认值约束（DEFAULT）用来规定字段的默认值。如果某个被设置为DEFAULT约束的字段没插入具体值，那么该字段的值将会被默认值填充。

 默认值约束的设置与非空约束一样，也只能使用列级约束。

6. 字段值自动增加约束

自增约束（AUTO_INCREMENT）可以使表中某个字段的值自动增加。一张表中只能有一个自增长字段，并且该字段必须定义了约束（该约束可以是主键约束、唯一约束以及外键约束），如果自增字段没有定义约束，数据库则会提示“Incorrect table definition; there can be only one auto column and it must be defined as a key”错误。

由于自增约束会自动生成唯一的ID，所以自增约束通常会配合主键使用，并且只适用于整数类型。一般情况下，设置为自增约束字段的值会从1开始，每增加一条记录，该字段的值加1。

为已存在表中的字段添加自增约束

/*为student11表中的主键字段添加自增约束*/

alter   table student11 modify stu_id int(10) auto_increment;

-- 使用ALTER TABLE语句删除自增约束

alter   table studen11 modify stu_id int(10);

7、外键约束

外键约束（FOREIGN KEY，缩写FK）是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。

外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。

举个例子：如果想要表示学生和班级的关系，首先要有学生表和班级表两张表，然后学生表中有个字段为stu_clazz（该字段表示学生所在的班级），而该字段的取值范围由班级表中的主键cla_no字段（该字段表示班级编号）的取值决定。那么班级表为主表，学生表为从表，且stu_clazz字段是学生表的外键。通过stu_clazz字段就建立了学生表和班级表的关系。

主表（父表）：班级表  -  班级编号 - 主键

从表（子表）：学生表 - 班级编号 - 外键

主从表相关sql的演示：

-- 先创建父表：班级表：

create table t_class(

        cno int(4) primary key auto_increment,

        cname varchar(10) not null,

        room char(4)

)

-- 添加班级数据：

insert into t_class values (null,'java001','r803');

insert into t_class values (null,'java002','r416');

insert into t_class values (null,'大数据001','r103');

-- 可以一次性添加多条记录：

insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');

-- 查询班级表：

select * from t_class;

-- 学生表删除：

drop table t_student;

-- 创建子表,学生表：

create table t_student(

        sno int(6) primary key auto_increment,

        sname varchar(5) not null,

        classno int(4)  -- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。

);

-- 添加学生信息：

insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2);

-- 查看学生表：

select * from t_student;

-- 出现问题：

-- 1.添加一个学生对应的班级编码为4：

insert into t_student values (null,'丽丽',4);

-- 2.删除班级2：

delete from t_class where cno = 2;

-- 出现问题的原因：

-- 因为你现在的外键约束，没用语法添加进去，现在只是逻辑上认为班级编号是外键，没有从语法上定义

-- 解决办法，添加外键约束：

-- 注意：外键约束只有表级约束，没有列级约束：

create table t_student(

        sno int(6) primary key auto_increment,

        sname varchar(5) not null,

        classno int(4),-- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。

                                constraint fk_stu_classno foreign key (classno) references t_class (cno)

);

create table t_student(

        sno int(6) primary key auto_increment,

        sname varchar(5) not null,

        classno int(4)

);

-- 在创建表以后添加外键约束：

alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno)

-- 上面的两个问题都解决了：

-- 添加学生信息：

-- > 1452 - Cannot add or update a child row: a foreign key constraint fails (`mytestdb`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2);

-- 删除班级1：

-- 2.删除班级2：

insert into t_student values (null,'张三',3),(null,'李四',3),(null,'王五',3);

-- > 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mytestdb`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

delete from t_class where cno = 3;

 存在外键的表在删除和表操作上会有关联关系，下面说一下，相关的外键处理策略：

-- 学生表删除：

drop table t_student;

-- 班级表删除：

drop table t_class;

-- 注意：先删除从表，再删除主表。

-- 先创建父表：班级表：

create table t_class(

        cno int(4) primary key auto_increment,

        cname varchar(10) not null,

        room char(4)

)

-- 可以一次性添加多条记录：

insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');

-- 添加学生表，添加外键约束：

create table t_student(

        sno int(6) primary key auto_increment,

        sname varchar(5) not null,

        classno int(4),-- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。

                                constraint fk_stu_classno foreign key (classno) references t_class (cno)

);

-- 可以一次性添加多条记录：

insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2),(null,'朱六',3);

-- 查看班级表和学生表：

select * from t_class;

select * from t_student;

-- 删除班级2：如果直接删除的话肯定不行因为有外键约束：

-- 加入外键策略：

-- 策略1：no action 不允许操作

-- 通过操作sql来完成：

-- 先把班级2的学生对应的班级 改为null

update t_student set classno = null where classno = 2;

-- 然后再删除班级2：

delete from t_class where cno = 2;

-- 策略2：cascade 级联操作：操作主表的时候影响从表的外键信息：

-- 先删除之前的外键约束：

alter table t_student drop foreign key fk_stu_classno;

-- 重新添加外键约束：

alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete cascade;

-- 试试更新：

update t_class set cno = 5 where cno = 3;

-- 试试删除：

delete from t_class where cno = 5;

-- 策略3：set null  置空操作：

-- 先删除之前的外键约束：

alter table t_student drop foreign key fk_stu_classno;

-- 重新添加外键约束：

alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update set null on delete set null;

-- 试试更新：

update t_class set cno = 8 where cno = 1;

-- 注意：

-- 1. 策略2 级联操作  和  策略3 的  删除操作  可以混着使用：

alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete set null ;

 Tips:一些其他的DDl和DML语句补充

-- 添加一张表：快速添加：结构和数据跟t_student 都是一致的

create table t_student2

as

select * from t_student;

select * from t_student2;

-- 快速添加，结构跟t_student一致，数据没有：

create table t_student3

as

select * from t_student where 1=2;

select * from t_student3;

-- 快速添加：只要部分列，部分数据：

create table t_student4

as

select sno,sname,age from t_student where sno = 2;

select * from t_student4;

-- 删除数据操作 :清空数据

delete from t_student;

truncate table t_student;

• delete和truncate的区别：

从最终的结果来看，虽然使用TRUNCATE操作和使用DELETE操作都可以删除表中的全部记录，但是两者还是有很多区别的，其区别主要体现在以下几个方面：

• (1)DELETE为数据操作语言DML；TRUNCATE为数据定义语言DDL。
• (2) DELETE操作是将表中所有记录一条一条删除直到删除完；TRUNCATE操作则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表。因此，TRUNCATE操作的效率更高。
• (3)DELETE操作可以回滚；TRUNCATE操作会导致隐式提交，因此不能回滚（在第十章中会讲解事务的提交和回滚）。
• (4)DELETE操作执行成功后会返回已删除的行数（如删除4行记录，则会显示“Affected rows：4”）；截断操作不会返回已删除的行量，结果通常是“Affected rows：0”。DELETE操作删除表中记录后，再次向表中添加新记录时，对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增；TRUNCATE操作则会重新从1开始自增。

5、表视图的概念

什么是视图：

视图（view）是一个从单张或多张基础数据表或其他视图中构建出来的虚拟表。同基础表一样，视图中也包含了一系列带有名称的列和行数据，但是数据库中只是存放视图的定义，也就是动态检索数据的查询语句，而并不存放视图中的数据，这些数据依旧存放于构建视图的基础表中，只有当用户使用视图时才去数据库请求相对应的数据，即视图中的数据是在引用视图时动态生成的。因此视图中的数据依赖于构建视图的基础表，如果基本表中的数据发生了变化，视图中相应的数据也会跟着改变。

PS:视图本质上就是：一个查询语句，是一个虚拟的表，不存在的表，你查看视图，其实就是查看视图对应的sql语句

视图的优点：

简化用户操作：视图可以使用户将注意力集中在所关心地数据上，而不需要关心数据表的结构、与其他表的关联条件以及查询条件等。

对机密数据提供安全保护：有了视图，就可以在设计数据库应用系统时，对不同的用户定义不同的视图，避免机密数据（如，敏感字段“salary”）出现在不应该看到这些数据的用户视图上。这样视图就自动提供了对机密数据的安全保护功能

-- 创建/替换单表视图：

create or replace view myview01

as

select empno,ename,job,deptno

from emp

where deptno = 20

with check option;

-- 查看视图：

select * from myview01;

-- 在视图中插入数据：

insert into myview01 (empno,ename,job,deptno) values (9999,'lili','CLERK',20);

insert into myview01 (empno,ename,job,deptno) values (8888,'nana','CLERK',30);

insert into myview01 (empno,ename,job,deptno) values (7777,'feifei','CLERK',30);

-- > 1369 - CHECK OPTION failed 'mytestdb.myview01'

-- 创建/替换多表视图：

create or replace view myview02

as

select e.empno,e.ename,e.sal,d.deptno,d.dname

from emp e

join dept d

on e.deptno = d.deptno

where sal > 2000 ;

select * from myview02;

-- 创建统计视图：

create or replace view myview03

as

select e.deptno,d.dname,avg(sal),min(sal),count(1)

from emp e

join dept d

using(deptno)

group by e.deptno ;

select * from myview03;

-- 创建基于视图的视图：

create or replace view myview04

as

select * from myview03 where deptno = 20;

select * from myview04;

6、存储过程

什么是存储过程：

通过前面章节的学习，我们已经知道SQL是一种非常便利的语言。从数据库抽取数据，或者对特定的数据集中更新时，都能通过简洁直观的代码实现。

但是这个所谓的“简洁”也是有限制，SQL基本是一个命令实现一个处理，是所谓的非程序语言。

在不能编写流程的情况下，所有的处理只能通过一个个命令来实现。当然，通过使用连接及子查询，即使使用SQL的单一命令也能实现一些高级的处理，但是，其局限性是显而易见的。例如，在SQL中就很难实现针对不同条件进行不同的处理以及循环等功能。

这个时候就出现了存储过程这个概念，简单地说，存储过程就是数据库中保存(Stored)的一系列SQL命令（Procedure）的集合。也可以将其看作相互之间有关系的SQL命令组织在一起形成的一个小程序。

存储过程的优点：

• 提高执行性能。存储过程执行效率之所高，在于普通的SQL语句，每次都会对语法分析，编译，执行，而存储过程只是在第一次执行语法分析，编译，执行，以后都是对结果进行调用。

• 减轻网络负担。使用存储过程，复杂的数据库操作也可以在数据库服务器中完成。只需要从客户端(或应用程序)传递给数据库必要的参数就行，比起需要多次传递SQL命令本身，这大大减轻了网络负担。

•  可将数据库的处理黑匣子化。应用程序中完全不用考虑存储过程的内部详细处理，只需要知道调用哪个存储过程就可以了

存储过程的创建和使用：

-- 定义一个没有返回值 存储过程

-- 实现：模糊查询操作：

select * from emp where ename like '%A%';

create procedure mypro01(name varchar(10))

begin

        if name is null or name = "" then

                select * from emp;

        else

    select * from emp where ename like concat('%',name,'%');

        end if; 

end;

-- 删除存储过程：

drop procedure mypro01;

-- 调用存储过程：

call mypro01(null);

call mypro01('R');

-- 定义一个  有返回值的存储过程：

-- 实现：模糊查询操作：

-- in 参数前面的in可以省略不写

-- found_rows()mysql中定义的一个函数，作用返回查询结果的条数

create procedure mypro02(in name varchar(10),out num int(3))

begin

        if name is null or name = "" then

                select * from emp;

        else

    select * from emp where ename like concat('%',name,'%');

        end if; 

        select found_rows() into num;

end;

-- -- 调用存储过程：

call mypro02(null,@num);

select @num;

call mypro02('R',@aaa);

select @aaa;

7、事物处理

 什么是事物：

事务（Transaction）是用来维护数据库完整性的，它能够保证一系列的MySQL操作要么全部执行，要么全不执行。

事务（Transaction）指的是一个操作序列，该操作序列中的多个操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位，由DBMS（数据库管理系统）中的事务管理子系统负责事务的处理。

目前常用的存储引擎有InnoDB（MySQL5.5以后默认的存储引擎）和MyISAM（MySQL5.5之前默认的存储引擎），其中InnoDB支持事务处理机制，而MyISAM不支持。

事物的特性：

事务处理可以确保除非事务性序列内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的序列，可以简化错误恢复并使应用程序更加可靠。

但并不是所有的操作序列都可以称为事务，这是因为一个操作序列要成为事务，必须满足事务的原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这四个特性简称为ACID特性。

1)原子性

原子是自然界最小的颗粒，具有不可再分的特性。事务中的所有操作可以看做一个原子，事务是应用中不可再分的最小的逻辑执行体。

使用事务对数据进行修改的操作序列，要么全部执行，要么全不执行。通常，某个事务中的操作都具有共同的目标，并且是相互依赖的。如果数据库系统只执行这些操作中的一部分，则可能会破坏事务的总体目标，而原子性消除了系统只处理部分操作的可能性。

2) 一致性

一致性是指事务执行的结果必须使数据库从一个一致性状态，变到另一个一致性状态。当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。一致性是通过原子性来保证的。

例如：在转账时，只有保证转出和转入的金额一致才能构成事务。也就是说事务发生前和发生后，数据的总额依然匹配。

3) 隔离性

隔离性是指各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务，都是隔离的。也就是说：并发执行的事务之间既不能看到对方的中间状态，也不能相互影响。

例如：在转账时，只有当A账户中的转出和B账户中转入操作都执行成功后才能看到A账户中的金额减少以及B账户中的金额增多。并且其他的事务对于转账操作的事务是不能产生任何影响的。

4)持久性

持久性指事务一旦提交，对数据所做的任何改变，都要记录到永久存储器中，通常是保存进物理数据库，即使数据库出现故障，提交的数据也应该能够恢复。但如果是由于外部原因导致的数据库故障，如硬盘被损坏，那么之前提交的数据则有可能会丢失。

事务的sql演示：

-- 创建账户表：

create table account(

        id int primary key auto_increment,

        uname varchar(10) not null,

        balance double

);

-- 查看账户表：

select * from account;

-- 在表中插入数据：

insert into account values (null,'丽丽',2000),(null,'小刚',2000);

-- 丽丽给小刚 转200元：

update account set balance = balance - 200 where id = 1;

update account set balance = balance + 200 where id = 2;

-- 默认一个DML语句是一个事务，所以上面的操作执行了2个事务。

update account set balance = balance - 200 where id = 1;

update account set balance = balance2 + 200 where id = 2;

-- 必须让上面的两个操作控制在一个事务中：

-- 手动开启事务：

start transaction;

update account set balance = balance - 200 where id = 1;

update account set balance = balance + 200 where id = 2;

-- 手动回滚：刚才执行的操作全部取消：

rollback;

-- 手动提交：

commit;

-- 在回滚和提交之前，数据库中的数据都是操作的缓存中的数据，而不是数据库的真实数据

事务的并发现象脏读、不可重复读和幻读：

脏读（Dirty read） 

当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。  

不可重复读 

（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

幻读 

（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复度和幻读区别： 

不可重复读的重点是修改，幻读的重点在于新增或者删除。

解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表 

事务隔离级别：

事务的隔离级别用于决定如何控制并发用户读写数据的操作。数据库是允许多用户并发访问的，如果多个用户同时开启事务并对同一数据进行读写操作的话，有可能会出现脏读、不可重复读和幻读问题，所以MySQL中提供了四种隔离级别来解决上述问题。

    事务的隔离级别从低到高依次为READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ以及SERIALIZABLE，隔离级别越低，越能支持高并发的数据库操作。

-- 查看默认的事务隔离级别  MySQL默认的是repeatable read 

select @@transaction_isolation; 

-- 设置事务的隔离级别   （设置当前会话的隔离级别）

set session transaction isolation level read uncommitted; 

set session transaction isolation level read committed; 

set session transaction isolation level repeatable read; 

set session transaction isolation level serializable; 

start transaction ;

select * from account where id = 1;

 

结言：

本篇从数据库的概念讲起，以mysql数据库为例，对sql语言的表操作语句、数据查询语句、数据管理语句、约束的创建、视图、事务和存储过程详细的进行了解读和测试，本篇的所有sql语句均可直接用于场景的测试。欢迎小伙伴分享讨论。