【开发】MySql、Sql命令知识汇总 | MYSQL笔记非教学

Mysql启用

windows cmd 管理员执行下列命令

启动mysql服务器

sc start mysql
或者
net start mysql

关闭mysql服务

sc stop mysql
或者
net stop mysql

启动数据库

mysql -u 账户 -p 密码(没有密码自动进入, 或后弹出提示输入密码)
输入密码, 进入mysql

Mysql常用命令

USE 数据库名;			   #切换数据库

show databases;		      #列出数据库列表

show tables;		   	  #显示当前选择数据库的所有数据表

show columns from 数据表;	#显示数据表的属性

desc 数据表;				#查看表结构

quit;	exit;	\q;		  #3种方式退出Mysql

#设置新密码:
update mysql.user set authentication_string = PASSWORD('密码')

#刷新权限:
flush privileges;		  #刷新权限 (修改密码)

# 查看表内容
命令格式： select <字段1, 字段2, ...> from < 表名 > where < 表达式 >;
# 例:
# 查看表 MyClass 中所有数据：
	select * from MyClass;
# 查看表 MyClass 中前2行数据：
	select * from MyClass order by id limit 0,2;

DDL与DML

一、数据库定义语言DDL

1. 数据库的创建

(1) create创建数据库

1. show查看数据库

   show databases;
   

2. create创建数据库

   # create database <数据库名>;
    create database db_1;
   

3. 如果数据库名不存在就创建数据库:

   基本格式:

   # create database [if not exists] <数据库名>; 
   -- 如果数据库名不存在则创建数据库, 否则不创建
   create database if not exists DB_1;
   

SQL语言不区分大小写

(2) 删除数据库

# drop database <数据库名>;
drop databases DB_1;		-- 删除名称为DB_1的数据库 

(3) 使用数据库(进入数据库)

use 数据库名;

2. 数据表的创建

(1) create创建数据表

格式:

create table [if not exists] <表名称>
(
    属性名1 属性类型 [约束...],
    属性名2 属性类型 [约束...],
    ...,
    属性名n 属性类型 [约束...]
)[engine innodb] [charset=utf8];
-- 创建表并设置它的属性名, 数据类型, 存储引擎和字符集

例:

create table if not exists students(
    s_id int, 				-- 学号
    s_name varchar(10), 	-- 姓名
    s_sex varchar(4), 		-- 性别
    s_age int, 				-- 年龄
    s_cid int				-- 班级
);

(2) desc查看表结构

格式:

desc 表名; 
-- 或
show columns from students;

(3) alter修改表结构

1. 增加表中列

   alter table <表名> add <属性名> <数据类型>;		-- 向数据表中添加一个列
   alter table students add s_core float;
   

2. 删除表中列

   alter table <表名> drop column <属性名>;			-- 输出数据表中的某个列
   alter table students drop column s_cid;	
   

3. 修改表中列

   alter table <表名> modify column <属性名> <数据类型>; --修改数据表中某个属性列的数据类型或约束
   alter table students modify column s_name varchar(30);
   

4. 修改列名称

   alter table <表名> change colmun <旧列名> <新列名> <数据类型>;	-- 修改数据表中某个属性的名称
   alter table students change column s_id s_num int; -- 将属性名s_id修改为s_num 
   

(4) drop删除数据表

drop table <表名>; 		-- 删除数据表
drop table students;

(5) show查看数据表

show tables;

(6) rename修改表名称

rename table <原表名> to 新表名;
rename table students to students_tb;

一、数据库操作语言DML

1. insert into数据库的插入

1. 插入所有属性值

   insert into <表名> [value | values];
   insert into student_tb value(1, '小明', '男', 16, 2);	-- 向students_tb表中插入一条数据
   

2. 插入部分属性值

   insert into <表名>(属性名1, 属性名2, ..., 属性名n) [value | values](属性值1, 属性值2, ..., 属性n);
   insert into students_tb(s_id, s_name) value(2, '小花');
   

3. 插入多条数据

   insert into student_tb values(4, '小胖', '男', 17, 3),		-- 多条数据之间用逗号','分隔开
   (5, 'Bob','男', 18, 2), (4, 'C', '女', 15, 1);
   

2. update修改数据(更新数据)

1. 修改所有数据

   update <表名> set<属性名> = <属性值>;	-- 修改数据表中某个属性的取值
   update students_tb set s_sex = '女';	-- 修改students_tb表中s_sex属性的取值为'女'
   

2. 条件修改

   update <表名> set <属性名> = <属性值> [where <条件表达式>];	-- 修改数据表中满足条件的某个属性值
   update students_tb set s_sex = '男' where s_name = '小明';	-- 修改'小明'的性别为'男'
   

3. 多条件修改-

update students_tb set = '男' where s_name = '小胖'
	|| s_name = '小王' or s_name = 'bob'; -- 多个条件满足任意一个
用 '||', 'or', 'OR'(逻辑或)

update students_tb set S_age = 16, s_cid = 1 where s_name = '小花'
	&& s_id = 2;	-- 多个条件同时满足用'&&', 'and', 'AND'(逻辑与)

3.delete删除数据

1. 删除全部数据

   delete from <表名>; -- 删除数据表中所有数据
   delete from students_tb;
   

2. 条件删除

   delete from <表名> where <条件表达式>;	-- 删除数据表中满足条件的数据
   delete from students_tb where s_name = '小花';
   

3. 多条件删除

   多个条件使用逻辑或, 逻辑与相连接判定
   

完整性约束

数据完整性是指存储在数据库中的数据要能正确反应实际情况, 规定输入的数据不能是无效值, 错误值或者乱码等

一.非空约束

关键字: not null

1.非空约束的创建

create table teacher(
    t_id int not null, 	-- 为教师编号添加非空约束
    t_name varchar(20),
    t_age int,
    t_sex varchar(4)
);
insert into teacher(t_name, t_age, t_sex) values('小李', 18, '男');
-- 错误, 非空约束规定插入的属性值不能为空, 如: t_id
insert into teacher values(1, '小玉', 18, '男')

2.非空约束的添加

以修改表的方法添加非空约束

alter table teacher modify column t_name varchar(30) not null;
insert into teacher(t_id, t_age, t_sex) values(3,20,'女');
--	报错:name属性值不能为空

3.查看表约束

desc <表名>;	-- (查看表结构, 结构中包含约束)

4.非空约束的删除

以修改表的方法去掉非空约束

alter table teacher modify column t_name varchar(30);

二.唯一约束

关键字: unique

1.创建唯一约束

创建表时添加唯一约束:

create table teacher(
	t_id int not null unique,	-- 为教师编号添加非空且唯一的约束
    t_name varchar(20),
	t_age int,
	t_sex varchar(4)
);

2.修改表添加唯一约束

alter table teacher add constraint uni_tid unique(t_id);
-- constraint 关键字给约束unique(t_id)取一个别名为 uni_tid

若表中没有规定主键, 那么表中第一个非空且唯一的属性自动添加主键约束

insert into teacher values(1, 'A', 28, '男');

错误: 唯一约束的属性不允许插入相同数据

3.给约束取别名

通过constraint关键字给约束取别名

alter table teacher add constraint uni_tname unique(t_name);

4.删除唯一约束

根据约束名称删除唯一约束:

alter table teacher drop index uni_tid;

根据属性名称删除唯一约束:

alter table teacher drop index t_name;

5.复合唯一约束(联合唯一约束)

1. 创建复合唯一约束

   create table teacher(
       t_id int not null,
       t_name varchar(20),
       t_age int,
       t_sex varchar(4),
       constraint uni_tidname unique(t_id, t_name) -- 对多个字段创建复合唯一约束, 使字段不能同时一致
       -- 就是id可以单独一致, name可以单独一致, 但是两个一起不能同时一致
   );
   

2. 修改表添加复合唯一约束

   alter table teacher add constraint uni_tidname unique(t_id, t_name);
   

   insert into teacher values(1, '小马', 28, "男");
   insert into teacher values(2, '小马', 26, '男');
   insert into teacher values(1, '小张', 26, '男');
   

   复合唯一约束不允许约束的属性列同时相同, 至少有一个不同, 如在id不同的情况下名字可以相同

三. 主键约束

关键字: primary key

1.创建表时添加主键约束

create table teacher(
    t_id int primary key,
    t_name varchar(20),
	t_age int,
	t_sex varchar(4)
);

2.修改表添加主键约束

alter table teacher add primary key(t_id);

由于表中第一个非空且唯一的属性会默认成为主键, 所以创建主键前要先删除这个属性的唯一约束

alter table teacher drop index uni_tidname;

定义主键时, 如果表中数据存在重复或者为空, 则添加主键会失败, 所以应使主键数据不为空

3.删除主键约束

alter table teacher drop primary key;
-- 由于一个表的主键只有一个, 直接丢掉即可, 只要规定表名, 可以不写约束名

4.创建联合主键

alter table teacher add primary key(t_id, t_name);

主键是非空且唯一的, 不允许有重复值或空值存在

四.自增列

关键字: auto_increment

1.创建自增列

创建表时添加自增约束:

create table teacher(
    t_id int primary key auto_increment,	-- 为教师编号创建主键和自增列约束默认从100开始
    t_name varchar(20),
    t_age int,
    t_sex varchar(4)
)auto_increment = 100; -- 设置自增列的默认初始值从100开始

2.添加自增列

修改表添加自增列:

alter table teacher modify column t_id int auto_increment;

insert into teacher(t_name,t_age,t_sex) value('K', 26, '男'),
	('S', 23, '男'), ('M', '20', '女');

3.删除自增列

alter table teacher modify column t_id int;
-- 修改表时不添加自增列就是删除自增列

五.检查约束

关键字: check

检查约束演示示例:

1. 创建表时添加检测约束

   create table student(
       id int primary key,
       s_name varchar(20),
       age int check(age >= 0 and age <= 150)
       -- 约束范围在0-150之间
       sex varchar(2) check(sex='男' or sex='女')
       -- 约束性别的取值范围为'男'或'女'
   );
   

   alter table 表名 add constraint 属性名 check(约束);
   

六. 默认值约束

关键字: default

1.创建表时添加默认值约束

create table teacher(
	t_id int primary key
	t_name varchar(20),
	t_age int default '男',	-- 默认值约束(默认为'男'性)
	t_sex varchar(4)
)

2.修改表添加默认值约束

alter table teacher modify column t_sex varchar(4) default '男';
insert into teacher(t_name,t_age) values('T', 33),('C', 32);

3.删除默认值约束

alter table teacher modify column t_sex varchar(4);
-- 在修改表时不添加默认值约束就是删除默认值约束

insert into teacher(t_name, t_age) values('E', 30),('L', 31);

七.外键约束

关键字: foreign key

1. 创建表添加外键约束

学生, 教师和班级的外键联系

**创建被参考表class: **

create table class(
    class_id int primary key,
    c_name varchar(20) not null,
    c_num int,	-- 班级人数
    c_tid varchar(20) 	-- 外键约束参考与教师表
    constraint for_ctid foreign key(c_tid) references teacher(t_id)
);

insert into class(class_id, c_name) value(1, '数据库'),
	(2,'c++'), (3, 'java'), (4, 'python');

创建参考表student：

create table student(
	s_id int primary key,
	s_name varchar(20) not null,
	s_cid int not null,
	s_age int,
	s_sex varchar(4),
	constraint for_scid foreign key(s_cid) references class(class_id)
);

insert into student(s_id,s_name,s_cid) values(1,'张三',1),
	(2,'李四',3),(3,'王五',2);

学生表中的班级编号参考班级表中的班级编号，学生不能给属于不存在的班级

insert into student(s_id,s_name,s_cid) values(4,'赵六',5); 

2、级联更新/删除

级联更新/删除是指父表中的元组更新或删除等操作对子表的处理方法，其有三种处理方法：
在更新/删除中（on update/on delete）

-- ① cascade：父表中的元组更新或删除，在子表中跟着更新或者删除
# 在外键约束后加上：on update cascade/on delete cascade
-- ② no action：无动作，若子表中有匹配的记录，就不允许更新/删除父表的数据
# 在外键后加上：on update no action/on delete no action
-- ③ set null：设置为空，若子表中有匹配的记录，更新/删除父表中的数据，子表中对应数据设置为
空
# 在外键约束后加上：on update set null/on delete set null

学生表中存在有3号班级的学生，不允许删除3号班级

delete from class where class_id = 3; 

创建参考表student1：

create table student1(
	s_id int primary key,
	s_name varchar(20) not null,
	s_cid int not null,
	s_age int,
	s_sex varchar(4),
	constraint for_scid1 foreign key(s_cid) references class(class_id) on update
	cascade on delete no action -- 级联更新和删除
);

insert into student1(s_id,s_name,s_cid) values(1,'张三',1),
	(2,'李四',3),(3,'王五',2);
delete from class where class_id = 2;
update class set class_id = 6 where class_id = 1;
drop table student;

编码问题解决方案

错误提示：
ERROR 1366 (HY000): Incorrect string value: ‘\xE6\x9D\x8E\xE5\x8B\x87’ for column ‘Sname’ at row 1

**插入中文时错误提示：
中文：**错误1366（hy000）：第1行“sname”列的字符串值不正确：\xe6\x9d\x8e\xe5\x8b\x87

解释：
已建立的表无法插入中文字符串，原因是因为数据表中的内容为latin1字符集，由下图的的资料可知，latin1字符集为8bit，这说明它是不能表示中文的，故而当然会报改错：

创建数据库时:

create database 数据库名 DEFAULT CHARACTER SET utf8;	-- 避免麻烦

解决:

查看数据表的编码：

show create table Student; 

修改表的编码方式：

alter table Student default character set utf8;
该命令用于将表Student的编码方式改为utf8；

修改字段的编码方式：

alter table Student change Sname Sname varchar(20) character set utf8;
该命令用于将表Student中Sname字段的编码方式改为utf8

---

DQL单表查询

一、DQL数据查询语言

用于查询数据库中的数据表数据

1、查询语句

查询关键字：select

查询语句的基本结构：

select <属性名> from <[数据库名.]表名> [where <查询条件>];

2、查询所有字段

select * from <表名>; -- '*'代表所有字段
select * from db_1.student1; -- 数据库名.表名
select * from student1;

3、查询指定字段

select <属性名> from <表名>; 
select s_id,s_name from student1; 

二、条件查询

1、基本条件查询

select <属性名> from <[数据库名.]表名> [where <查询条件>]; 

条件运算符：>、<、=、!=(<>)、>=、<= （不存在==）

select t_id,t_name from teacher where t_id > 3;
select t_id,t_name from teacher where t_id <> 4; -- '<>'为不等于，与'!='类似

2、多条件查询

基本格式：

select <属性名> from <[数据库名.]表名> [where <查询条件1> [&&/or <查询条件2>……]; 

逻辑运算符：&&(and/AND)、||(or/OR)，用于连接两个条件

select t_id,t_name from teacher where t_name != 'C' and t_id <> '4';

3、范围查找

在……范围内查找：between……and……

select t_id,t_name from teacher where t_id between 5 and 8; -- t_id的值在5和8之
间

在……范围外查找：not between……and……

select t_id,t_name from teacher where t_id not between 5 and 8; -- t_id的值在5
和8之外
select t_id,t_name from teacher where t_id not between 5 and 8 or t_id = 6;

4、字符串模糊匹配

模糊匹配：like（用于字符串匹配）

①通配符%：匹配任意0个、1个或多个字符

select t_id,t_name from teacher where t_name like '%小%';
-- 匹配属性t_name中含有'小'字的记录，前后任意多个字符

②通配符’_’：匹配任意一个字符

select t_id,t_name from teacher where t_name like '小_';
-- 匹配属性t_name中以'小'字开头，以任意一个字符结尾的元组，如：小李、小张、小小、小A、
小*等
select t_id,t_name from teacher where t_name like '小_%';
-- 匹配属性t_name中以'小'字开头，以至少一个字符结尾的元组，如：小李、小不点、小ABC等

5、集合范围查找

①在集合范围内查找：in

select t_id,t_name from teacher where t_id in (1,2,5,7,8);
	-- 将属性t_id的取值在集合(1,2,5,7,8)中的元组查询出来
select t_id,t_name from teacher where t_name in ('小李','A','小小小
李','K','Z','X','C');
	-- 将属性t_name的取值在集合('小李','A','小小小李','K','Z','X','C')中的记录查询出
来

②在集合范围外查找：not in

select t_id,t_name from teacher where t_id not in (1,2,5,7,8);
	-- 将属性t_id的取值不在集合(1,2,5,7,8)中的元组查询出来

一般不用in，由于in的内部处理没有where好。

6、空值查询

①查询空值：is null

select * from teacher where t_age is null;
	-- 查询属性t_age取值为空的记录

②查询非空值：is not null

select * from teacher where t_sex is not null;
	-- 查询属性t_sex取值不为空的记录

7、查询去重

去重关键字：distinct，同一个查询语句中只能对一个属性去重

select distinct t_age from teacher;
	-- 查询teacher表中属性t_age不重复的取值

8、分页查询

分页：limit [偏移量n,记录条数m] 是指查询从第n+1条记录开始后m条数据，偏移量n默认为0

select * from teacher limit 3,6;
select * from teacher limit 5;

9、合并查询结果

合并的作用就是将带查询出的两个表放到一个表中显示出来, 合并并非真的改变数据表, 而是产生一个临时的表, 当查询输出完毕内存消失

合并：union(合并时去重)/union all（合并不去重）

临时结果集：select语句查询的结果是一个虚拟表（临时表）

-- union(合并)：去重
select 3,'abcde' union select 5,'12345'
union select 3,'abcde' union select '12345',5;
select 3,'abcde' union select 5,'12345','K';
-- 错误：合并的多个查询必须列的数量相同

-- union all(合并)：不去重，合并所有
select 3,'abcde' union all select 5,'12345'
union all select 3,'abcde' union all select '12345',5;

数据表查询合并：

-- 单独的表数据查询语句
select * from teacher where t_id = 10 || t_name = '小李' ;
select * from teacher where t_id = 6 || t_name like 'K' ;
-- 合并两个查询临时结果集
select * from teacher where t_id = 10 || t_name = '小李'
union
select * from teacher where t_id = 6 || t_name like 'K' ;

10、属性别名

给属性取别名：as

select t_id as 教师编号,t_name as 教师名称 from teacher; 

as可以省略

select t_id 教师编号, t_name 教师名称 from teacher;

11、排序查询

关键词: order by

对查询结果排序（默认升序）

select * from teacher order by t_age; 

降序排列: desc

select * from teacher order by t_age desc;

注意: 有where 時, order必須在where后使用.

12、分组查询

分组: group by

select * from teacher group by t_sex; -- 分组查询出属性t_sex的分类，计算有多少个不同
的t_sex
select * from teacher group by t_age; -- 分组查询出属性t_age的分类，计算有多少个不同
的t_age
-- 条件分组查询 (分组后条件只能用having子句)
select * from teacher group by t_age having t_id > 5;
select * from teacher where t_sex is not null group by t_age having t_id > 5;
-- 普通的分组查询没有太多实际意义，一般用于分组统计

分组统计

select s_cid,count(s_id) from student  group by s_cid having count(s_id)>2;
-- 统计学生人数大于2的班级和学生人数

DQL多表查询

我们在查询表格的过程中，往往需要将多个表组合查询使用，并且要实现多个表之间的数据查询，例
如：查询学生成绩，需要将学生表和成绩表连接起来，查询满足条件的数据，这个时候就需要用到多表
查询了。

前置条件:

# 教师表
	create table teacher(
	t_id int primary key auto_increment,
	t_name varchar(20) not null,
	t_sex varchar(4),
	t_age int
);

# 班级表
create table class(
    c_id int primary key auto_increment,
    c_name varchar(20) not null,
    c_tid int ,
    c_stunum int default 0,
    constraint for_CT foreign key(c_tid) references teacher(t_id)
);
# 学生表
create table student(
    s_id int primary key auto_increment,
    s_name varchar(20) not null,
    s_cid int,
    s_sex varchar(4),
    s_age int,
    constraint for_SC foreign key(s_cdi) references class(c_id)	-- 外键连接
);

然后往里存入数据
...
...

一、交叉连接与内连接

交叉连接与内连接效果是一样的, 所以放到一起说

用途:

假设我有一个学生表和一个班级表, 每个学生只能拥有一个班级, 学生班级与班级表外键连接, 此时我想查询出每一个学生的信息并且附带其所在班级信息.

交叉连接:

在不带where条件下, 交叉连接会产生一个笛卡尔积(一对多交叉相连), 那么就会造成一个查询输出每个学生都对应所有的班级输出的错误结果, 随后通过添加 where条件, 将学生表班级属性, 与班级表id相等的保留下了, 做了一个剔除的操作

# 查询学生及其对应所在的班级
select student.*, class.* from class, student
	where student.s_cid = class.cid;

内连接:

交叉连接产生的笛卡尔积会进行额外的判断和比较,如果要查询较多数据的话，交叉连接查询的效率相当
低下，所以需求一个更高效的一个查询。

语法关键词: inner join

select student.*, class.* from student inner join class
	where student.s_cid = class.c_id;

总结:

交叉连接会产生笛卡尔积，先产生笛卡尔积，再进行条件筛选，效率比较慢。

内连接先判断是否能够匹配, 如果能匹配才会连接表, 避免产生笛卡尔积, 效率要比交叉连接高, 且更节省空间

笛卡尔积：将连接表格的所有元组都相互匹配一次，产生一个包含所有情况的大表。

---

二、外连接

外连接分为左外连接, 右外连接, 全外连接

**左外连接: ** left join , 以左表为基准(左表内容全部正常输出), 左表中数据与右表有关联就输出关联内容, 如果没有就输出null

**右外连接: ** right join, 以右表为基准 … … …, 与左外连接类似, 左外连接和右外连接可以相互替代使用

全外连接: full join, 左外连接, 和右外连接和一起就是全连接,(注意, 左外连接和右外连接都包含重复的关联部分, 要去重, 所以使用union合并)

**注意: ** mysql 不支持full join全连接, 只能通过左连接和右连接合并实现全连接效果

语法: select 表名1.属性1, 表名2.属性2,... from 表名1 left/right join class 表名2 on 条件表达式

-- 用union合并左连接和右连接实现全连接：left join union right join
select student.s_name,class.c_name from student left join class
on student.s_cid = class.c_id
  union
select student.s_name,class.c_name from student right join class
on student.s_cid = class.c_id;
-- left join union left join
select student.s_name,class.c_name from student left join class
on student.s_cid = class.c_id
  union
select student.s_name,class.c_name from class left join student
on student.s_cid = class.c_id;

三、多表连接查询

三个及三个以上的表的连接查询(但也不要超过4个, 过于复杂, 不便于调整)

例如：以学生表为基准，显示所有学生表数据，班级表和教师表只显示匹配数据。

select student.s_name,class.c_name,teacher.t_name
	from student left join class on student.s_cid = class.c_id
 				 left join teacher on c_tid = t_id;
  -- 查询所有学生的姓名和其所对应的班级名称及教师名称

视图和索引

一，视图

视图的作用:

​ 视图相当于给查询语句取别名, 方便查询.

视图关键字: view

1、视图的创建

①创建视图的基本格式:

create view <视图名> as <查询语句>;
-- 视图的本质就是用<视图名>代替<查询语句>. 相当于给查询语句取别名.

②视图的创建举例:

如; 对以下查询语句创建视图

select * from class;

create view v_c as select * from class;
-- 创建了一个视图，名为v_c，用于代替查询语句“select * from class;”。

2、视图的使用

①视图使用的基本格式：

select <属性名> from <视图名>;

如:

select * from v_c;
-- 查询视图v_c，其查询效果与其所代替的查询语句“select * from class;”效果一致

②视图可用于代替复杂的查询语句，如以下多表连接查询：

select S.s_name 学生姓名, C.c_name 班级姓名, T.t_name 教师姓名
	from student S left join class C on S.s_cid = C.c_id 
	left join teacher T on C.c_tid = T.t_id;

可使用视图替代为:

create view v_sctname as select S.s_name 学生姓名, C.c_name 班级名称, T.t_name 教师姓名
	from student S left join class C on S.s_cid = C.c_id
	left join teacher T on C.c_tid = T.t_id;

查询此视图:

select * from v_sctname;

此时这个简单的视图查询语句“select * from v_sctname;”与以上多表连接查询语句的效果一致，但是
相对而言格式要简单得多。

③也可只对视图中的部分属性进行查询

如:

select 学生姓名, 班级名称 from v_sctname;

④视图也支持条件查询

如：

select 学生姓名,班级名称 from v_sctname limit 3;
select 学生姓名,班级名称 from v_sctname where 教师姓名 = '小李';

3、视图的修改

修改视图的基本格式：

alter view <视图名> as <查询语句>;
-- 让视图名代替另一个查询语句

如有以下视图：

create view v_c as select * from class;
修改视图名所代替的查询语句：
alter view v_c as select c_id,c_name from class where c_tid is not null;
查询视图：select * from v_c;

4、视图的删除

删除视图的基本格式：

drop view <视图名>; 

如:

drop view v_c;
-- 这里使用对象删除命令drop删除数据库视图对象v_c。

二、索引

索引用于提高查询的效率，对创建索引的字段，查询效率比普通字段要高。

索引是以一种能够高效获取数据的数据结构来存储数据，不同的存储引擎所使用的数据结构也会有所不
同，一般是使用BTREE、哈希表、红黑树等高效读取数据的数据结构。

索引关键字：index

1、B-Tree索引

B+Tree：每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。

mysql5.5版本以后全部使用InnoDB存储模式,而innoDB存储模式使用的是B-Tree索引

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，很适合查找范围数据。

索引特点:

可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询，但必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。

create table people(
    last_name varchar(50) not null,
    first_name varchar(50) not null,
    dob date not null,
    gender enum('m','f') not null,
    key(last_name, first_name, dob)
)

① 创建普通索引

创建普通索引的基本格式：

create index 索引名 on 表名<字段1[,字段2,...,字段n]>;

如:

create index Ind_Sage on student(s_age);
-- 对studnet表的s_age属性创建了一个普通索引, 索引名为Ind_Sage.

select s_age from student;
-- 对于查询studnet表中s_age这个单个字段, 查询效率会有所提高
-- 由数据表中的数据量较少, 所以看不到查询效率提升的明显效果
-- 数据表中数据越多，数据量越大，索引效果提升会越明显

创建表的时候创建索引

CREATE TABLE People (
  last_name varchar(50) not null,
  first_name varchar(50) not null,
  dob date not null,
  gender enum('m', 'f') not null,
  key(last_name, first_name, dob)`-- key(普通key), 没有约束, 只有索引(index索引), 与index用法相同
);

② 以修改的方式添加索引

基本格式：

alter table <表名> add index <索引名(属性名)>;

如:

alter table student add index Ind_Ssex(s_sex);
-- 为student表中的s_sex属性添加一个名为Ind_Ssex的普通索引

③查看表中所有索引

基本格式:

show index from <表名>;

如:

show index from student;
-- 查看student表中所有索引

思考: 没有建立索引的表存不存在索引?

答: 如果有主键和外键则会有主键索引

④ 删除索引

基本格式:

drop index <索引名> on <表名>;
-- 删除<表名>表格中与<索引名>对应的索引

如:

drop index Ind_Ssex on student;

2、唯一索引

唯一索引关键字：unique index

对添加了唯一索引的字段进行查询，查询效率会提高。

唯一索引和唯一约束相关，给某属性添加唯一索引后，不允许属性的取值重复。

①创建唯一索引

基本格式：

create unique index <索引名> on <表名(属性名)>;
-- 对表格名称为<表名>的表中属性名为<属性名>的属性添加索引名为<索引名>的唯一索引

如:

create unique index Ind_Sage on student(s_name);
-- 对表格名称为student 的表格中的属性s_name 创建索引名为Ind_Sage的唯一索引

可以不用手动创建唯一索引, 给属性添加唯一约束时会自动创建唯一索引

② 以修改的方式添加唯一索引

alter table <表名> add unique(<属性名>);
-- 为表格名称为<表名>的数据表中属性名为<属性名>的属性添加一个唯一索引
-- 创建唯一约束及创建唯一索引(二者必须同时存在)

如:

alter table student add unique(s_name);
-- 为表格名称为student的数据表中属性名为s_name的属性添加一个唯一索引

3、主键索引/外键索引

① 主键索引的基本概念

主键/外键索引关键字：primary key

主键和外键的查询效率比较高，因为主键和外键有主键索引。

② 主键索引的创建

为属性建立主键或外键约束时会自动创建主键索引，或者在创建表格时创建主键索引。

如：

create table 表名(
    id int,
    name varchar(20),
    primary key(id)	-- 为属性id创建一个主键索引
)

③主键索引的添加

基本格式：

alter table <表名> add primary key (<列名>);
-- 以修改的方式添加主键索引

如:

alter table 表名 add primary key(id);

4、全文索引

全文索引关键字：fulltext index

对添加了全文索引的字符串或文本类型的属性进行查询，查询效率会提高。

①全文索引的创建

基本格式：

create fulltext index <索引名> <表名(属性名)>;
-- 为表格名称为<表名>的数据表中属性名为<属性名>的属性创建一个全文索引<索引名>

如:

create fulltext index fullind_tname teacher(t_name);
-- 为表格名称为teacher的数据表中属性名为t_name的属性添加一个全文索引<索引名>

② 以修改的方式添加全文索引

基本格式：

alter table <表名> add fulltext index fullind_<索引名>(<属性名>);
-- 为表格名称为<表名>的数据表中属性名为<属性名>的属性添加一个全文索引<索引名>

如:

alter table teacher add fulltext index fullind_tname(t_name);
-- 为表格名称为teacher 的数据表中属性名为t_name的属性添加一个全文索引fullind_tname

全文索引相对而言较少使用，其只限于对文本、字符串等数据类型添加。

5、空间索引

空间索引是对空间数据类型（点、线、面、立体图形）建立的索引，由于空间索引应用范围不广泛，一
般只在存储地图、模型等相关数据的数据库中有所应用。

---

6、索引的优缺点

① 优点：

通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性。

可以加快数据的检索速度

可以加速表与表之间的连接

在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间

② 缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

索引需要占用物理空间，数据量越大，占用空间越大

会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护

---

7.什么时候需要创建索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找
• 查询中统计或者分组的字段；

8.什么时候不需要创建索引

• 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件
• where条件里用不到的字段，不创建索引；
• 表记录太少，不需要创建索引；
• 经常增删改的表；
• 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引。

索引失效问题:

模型数空运最快

简	解释	说明
模	模糊查询	使用like关键字, 如果开头为%, 会导致索引失效
型	数据类型	数据类型错误会导致索引失效
数	函数	对索引字段使用内部函数会导致索引失效
空	Null	索引不存储Null值, 如果不设置索引列not null, 数据库认为其有可能存在空值, 不会按照索引进行计算
运	运算	对索引列进行加减乘除运算, 会导致索引失效
最	最左原则	如果不是按照索引列最左侧开始顺序查找, 会导致索引失效
快	全表扫描更快	数据库预计使用全表扫描比使用索引更快时, 不会使用索引

存储过程与函数

一、数据库的库函数

1、聚合函数

聚合函数是对一组值进行计算返回单个值

① 统计函数：count

select count() from class;
-- 统计班级个数

条件统计

select count(*) from class where c_stunum > 60;
-- 统计学生人数大于60的班级个数

② 求最大值函数：max

select max(c_stunum) from class;
select * from class where c_stununm = max(c_stunum);	-- 统计学生人数最多的班级

③ 求和函数：sum

select sum(c_stunum) from class;	-- 求所有班级中学生的总和

2、时间日期函数

① 获取当前时间：now

基本格式：now()

如：

insert into 表名(日期时间类型的属性) values now();	-- 将当前日期时间插入表中
now()	可以获取当前日期时间

② 获取日期时间类型数据中的日期：date

select date('2000-1-1 6:30:30');	-- 获取日期
select date(now());					-- 获取当前时间

其他获取日期时间的库函数:

time();		-- 时:分:秒
year();		-- 年;
month();	-- 月;
day();		-- 日;
hour();		-- 小时;
minute();	-- 分钟;
second();	-- 秒钟;

3、常用库函数

① 大小写字母转换：upper/lower

select upper('abc');
select lower('ABC');

② 求字符串字串：substr

select substr('123abc@#$XYZ', 起始字符位置, 长度);
# substring
# 和substr类似但更高级, 它可以返回字符、二进制、文本或图像表达式的一部分

③ 取整函数：round, ceil, floor

# 四舍五入: round(小数, 保留小数位数);
select round(123.654);		-- 只保留整数
select round(123.654,1);	-- 保留1位小数
# 向上取整: ceil(小数);
select ceil(123.654);		-- 不支持保留小数位数
# 向下取整: floor(小数);		-- 不支持保留小数位数

④ 求次方开根函数: power/pow, sqrt

select power(2, 4);		-- 求2的4次方
select power(2, -1);	-- 求2的-1次方;
# 求次方跟
select power(2, 1/2);	-- 求2的0.5次方 == 给2开跟
select sqrt(2)			-- 上面的方式可以用sqrt实现, 但是sqrt只能开2次方跟

⑤ 获取当前数据名: database

select database();

⑥ 获取字符串长度：length

select length("123abc");

⑦ 字符串连接函数，连接多个字符串：concat

select concat('123', '987', 'abc');

组合函数 group by

select 查询内容 from 表名 group by 属性名
将"属性名"的值相同的数据进行組合

我们拥有下面这个 “Orders” 表：

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

现在，我们希望查找每个客户的总金额（总订单）。

我们想要使用 GROUP BY 语句对客户进行组合。

我们使用下列 SQL 语句：

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

结果集类似这样：

Customer	SUM(OrderPrice)
Bush	2000
Carter	1700
Adams	2000

如果省略 GROUP BY 会出现什么情况：

SELECT Customer,SUM(OrderPrice) FROM Orders

结果集类似这样：

Customer	SUM(OrderPrice)
Bush	5700
Carter	5700
Bush	5700
Bush	5700
Adams	5700
Carter	5700

上面的结果集不是我们需要的。

那么为什么不能使用上面这条 SELECT 语句呢？解释如下：上面的 SELECT 语句指定了两列（Customer 和 SUM(OrderPrice)）。“SUM(OrderPrice)” 返回一个单独的值（“OrderPrice” 列的总计），而 “Customer” 返回 6 个值（每个值对应 “Orders” 表中的每一行）。因此，我们得不到正确的结果。不过，您已经看到了，GROUP BY 语句解决了这个问题。

二、自定义函数

数据库中的自定义函数和C语言函数类似，可以自定义函数，编辑函数所执行的代码，使其完成自己想
要的功能。

1、自定义函数的创建

① 创建自定义函数的基本格式：

create function 函数名(函数参数1 数据类型, 函数参数2, 数据类型, ...)
returns 返回值类型
begin
	函数体语句
	return (函数返回值);
end;

如: 创建一个函数求两个数的和

create function sum1(x int, y int)
returns int
	return x + y;

注意: 如果无法创建函数 (创建函数失败或报错) , 则需要设置安全选项:

set global log_bin_trust_function_creators=TRUE; 

如果函数体较为复杂，或函数执行语句较多，需要用到begin end和delimiter

delimiter // -- 把默认语句结束符';'改为'//'
create function sum1(x int,y int)
returns int
begin -- {
函数体：begin……end 可以包含多条语句(复合语句),相当于{……}
return x+y;
end// -- }
delimiter ; -- 把结束符'//'改回';'

注意: 函数里无法使用select、create等动态SQL语句。

2、自定义结束符

MySQL数据库中默认语句结束符为’;'分号，此外MySQL数据库还支持自定义结束符。

自定义结束符关键字：delimiter

自定义结束符的基本格式：

delimiter <字符> -- 将<字符>定义成为结束符 

如:

delimiter , -- 把默认语句结束符';' 改为',', 之后所有SQL语句会以','作为结束符

delimiter $
select sum1(123,987)$ -- 结束符改成$
delimiter ; -- 把结束符//改回;
select sum1(123,987);

3、自定义函数的调用

函数调用的基本格式：

select 函数名(函数参数);

如:

select sum1(11,22);

4、自定义函数的删除

删除自定义函数的基本格式：

drop function 函数名;

如:

drop function sum1;

5、修改自定义函数

可以使用 ALTER FUNCTION 语句来修改自定义函数的某些相关特征。若要修改自定义函数的内容，则需要先删除该自定义函数，然后重新创建。

三、存储过程

存储过程和函数类似，可以理解成为数据库里一种特殊的函数对象，其也可以用于自定义功能，并且功
能更加强大，所以MySQL数据库中一般使用存储过程比使用自定义函数要更方便。

关键词: procedure

创建前判断该存储过程是否已经存在, 存在则删除

DROP PROCEDURE IF EXISTS 存储过程名;

1、创建无参存储过程

delimiter 自定义结束符
create procedure 存储过程名()
begin
	SQL语句1;
	SQL语句2;
	……
	SQL语句n;
end自定义结束符
delimiter ;

如：示例1

create procedure select_stu()
select * from student;	-- 存储过程select_stu完成查询学生表数据的功能
-- 存储过程中执行单条语句可不用begin..end括起来

如：示例2

delimiter //
create procedure select_stu()
begin -- 复合SQL语句
	select s_id from student where s_id > 3; -- 存储过程select_stu完成查询学生表数据的功能
	select s_name from student where s_id > 3;
	select s_cid from student where s_id > 3;
end//
delimiter ;
call select_stu(); -- 执行此存储过程与视图的效果类似

2、存储过程的调用

调用存储过程的基本格式：

call 存储过程名();

如:

call select_stu();	-- 调用无参存储过程
call select_stu;	-- 调用无参存储过程时括号可以省略

3、查看已创建的指定存储过程的相关信息

查看指定存储过程的基本格式:

show create procedere <存储过程名>;

如:

show create procedure select_stu;

4、查看已创建的多有存储过程的相关信息

查看所有存储过程的基本格式:

show procedure status;

5、存储过程的删除

drop procedure <存储过程名>;

如:

drop procedure select_stu;

6、修改存储过程

MySQL 还不支持修改存储过程的代码，只能先将原来的存储过程删除过后，再重新创建。

7、带参存储过程

①创建带参存储过程的基本格式：

delimiter 自定义结束符
create procedure 存储过程名(
	参数类型 参数名1 数据类型,
	参数类型 参数名2 数据类型,……,
	参数类型 参数名n 数据类型)
begin
	SQL语句;
end自定义结束符
delimiter ;

②参数类型

MySQL数据库中存储过程的参数有3种类型，分别为：in、out、inout。

-- in：传入型参数，用于将参数值传输进存储过程中。
-- out：传出型参数，用于将数据从存储过程中带回，类似于函数的返回值。
-- inout：传入传出型参数，既可用于将参数值传输进存储过程中，也可用于将数据从存储过程中带
回。

③带参存储过程的调用

调用带参存储过程的基本格式：

 call 存储过程名(参数1, 参数2,...,参数n);

④带参存储过程的演示示例

# ①运用带参存储过程进行查询操作
-- 查询：根据班级编号查找对应的班级名称
delimiter //
create procedure get_classname(in cid int)
begin
	select c_name from class where c_id = cid;
end//
delimiter ;

call get_classname(1);
	-- 调用存储过程，根据班级编号查询对应的班级名称
call get_classname(3);

# ② 运用带参存储过程进行插入操作
-- 插入：将所给定的数据插入数据表中
delimiter //
create procedure insert_student(in sid int,sname varchar(20),
	scid int,ssex varchar(4),sage int) -- in 可以省略 out inout 不可省略
begin
	insert into student values(sid,sname,scid,ssex,sage);
end//
delimiter ;

call insert_student(null,'小小',2,'女',18);
	-- 调用存储过程向student表中插入数据(null,'小小',2,'女',18)
# ③ 运用带参存储过程进行修改操作
-- 更新/修改：修改指定数据
-- 如：根据所给定的学生学号，修改学生的姓名
delimiter //
create procedure updata_student(in sid int,sname varchar(20))
begin
	update student set s_name = sname where s_id = sid;
end//
delimiter ;

call updata_student(1,'李明');
	-- 调用存储过程将学号为1的学生的姓名修改为'李明'

流程控制

流程控制

一、顺序结构

顺序结构关键字: begin…end, 相当于{…}, 是SQL语言中的复合语句(语句块)。

顺序结构基本格式：

begin
	sql代码1；
	sql代码2；
	...
	sql代码n；
end；

二、数据库变量

1、局部变量

定义局部变量的基本格式：

declare 变量名 数据类型;

变量的初始化赋值:

declare 变量名 数据类型 default 初始值;

给变量赋值:

set 变量名 = 变量值;

注意: 局部变量只能在begin…end之间使用

**例如: **

delimiter //
create procedure sum1(in x int, in y int)
begin
	declare z int default 0;	-- 定义一个局部变量z, 初始化赋值为0
	set z = x + y;
	select z as '两个数的和为';
end//
delimiter ;
call sum1(123, 456);
select z;	-- 报错: 局部变量只能在其定义的begin...end之间使用

2、用户变量

用户变量的一般格式:

set @变量名 = 初始值;

如:

set @a = 10;
select @a;
set @a = 'abc';
select @a;
set @b = '2000-1-1';
select @b;

用户变量是弱类型，没有指定的数据类型，可以支持多种数据类型，可以理解为万用类型
用户变量只能够在当前连接中使用，不同的数据库连接无法共用同一个数据库变量，变量名相同也无法
共用。
用户变量应用举例：

delimiter //
create procedure sum2(in x int, in y int, out z int)	-- 定义了一个用于传出数据的局部变量z(形参)
begin
set z = x + y;
end//
delimiter ;

set @a = 11;
set @b = 22;
call sum2(@a, @b, @c);
select @c;	-- 通过全局变量带回存储过程中的运算结果

3、会话变量

系统自带的, 是用来存放数据库当前会话的默认数据, 当前连接有效, 重新连接后恢复默认值。

使用会话变量的基本格式

@@会话变量名

查看当前连接的会话变量

show session variables;

4、全局变量

系统自带的, 是用来保存数据库当前服务的默认数据, 当前服务有效, 重启服务后恢复默认值。

使用全局变量基本格式:

@@全局变量名

查看当前服务的全局变量:

show global variables;

三、分支选择结构

1、if else 结构

①if语句的基本格式:

if <判断条件> then <语句;>
end if;

判断if之后的条件, 如果条件满足则执行语句, 否则不执行语句。
(注意结束if需要用end if)

②if else 语句的基本格式:

if <判断条件> then <语句1;>
else <语句2;>
end if;

判断if之后的条件, 如果条件满足, 则执行语句1, 否则执行语句2

③if else 嵌套的基本格式:

if <判断条件1> then <语句1;>
elseif <判断条件2> then<语句2;>
[elseif ... then ...;]
end if;

2、case多分支结构

**case when 多分支结构基本格式: **

case 条件
	when 值1 then 语句1;
	when 值2 then 语句2;
	...
	when 值n then 语句n;
	else 语句n+1; # 相当于default
end case;

判断case之后的条件, 如果条件值于其中某个when之后的值相等, 则执行该when中then之后的语句, 如果都不满足则执行else之后的语句。

相当于C语言中的swhich case语句

例如：对成绩（0~100）进行评分(优秀>=90、良好>=80、及格>=60、不及格<60)

delimiter //
create procedure test2(in score int)
begin
if score>100 && score<0
then select '成绩输入有误!';
else
case floor(score/10) # 取整函数floor, score div 10 -- 整除
when 10 then select '满分' as 成绩等级;
when 9 then select '优秀' as 成绩等级;
when 8 then select '良好' 成绩等级;
when 7 then select '及格' 成绩等级;
when 6 then select '及格' 成绩等级;
else select '不及格' 成绩等级;
end case;
end if;
end//
delimiter ;
call test2(100);
call test2(99);
call test2(88);
call test2(78);
call test2(66);
call test2(10);

四、循环结构

1、while…do循环结构

基本格式:

while 条件表达式
do 循环体语句;
end while;

如何while之后的条件满足则循环执行do之后的循环体语句, 直到条件不满足则结束循环。

数据库中的while循环和C语言中的while循环一样

例如：求数字1到10的和

delimiter //
create procedure tset3(out s1 int)
begin
declare i int default 1;
declare s int default 0;
while i<=10
do
set s = s+i; -- SQL语言没有+=、-=、++、--、==等运算符
set i = i+1;
end while;
set s1 = s;
end//
delimiter ;
call tset3(@sum);
select @sum;

2、loop循环

①loop循环基本格式：

loop 循环体语句;
end loop;

注意：这里的loop循环是一个无限循环（死循环），其没有结束条件，所以需要手动添加结束条件。

②给loop循环添加结束条件

循环别名:loop 循环体语句;
if 条件表达式 then leave 循环别名; end if;
end loop;

首先执行loop之后的循环体语句，循环语句中的if条件满足则离开循环，否则继续循环。
这里给循环取别名，通过if语句判断结束条件，leave离开跳出循环（相当于C语言中的break）。

例如：求整数1到n的和

delimiter //
create procedure test4(out s1 int,in n int)
begin
declare i int default 1;
declare s int default 0;
sums:loop
set s = s+i;
set i = i+1;
if i > n then leave sums;
end if;
end loop;
set s1 = s;
end//
delimiter ;
call test4(@sum,10);
select @sum;
call test4(@sum,100);

3、repeat循环

**repeat循环的基本格式: **

repeat 循环体语句;
until 条件表达式
end repeat;

首先执行repeat之后的循环体语句，然后判断until之后的条件，如果条件不满足则继续循环，否则条
件满足则结束循环。

# 注意：repeat循环相当于C语言中的do while循环，都是先执行一次循环体再进行条件判断，但是不
同的是，do while循环是条件不满足时才结束循环，而repeat是条件满足时才结束循环。并且until子
句后不能有’;'分号。

– 例如：求整数n到m的和

delimiter //
create procedure test5(out s1 int,in n int,in m int)
begin
declare k int default n;
if n>m then
set n = m;
set m = k;
end if;
declare i int default n;
declare j int default n+1;
repeat
set i = i+j;
set j = j+1;
until j>m -- 如果j>m则离开repeat循环
end repeat;
set s1 = i;
end//
delimiter ;
set @x = 1;
set @y = 10;
call test5(@sum,@x,@y);
select @sum;
set @x = 5;
set @y = 16;

4、跳出语句

①leave
leave的基本格式：

leave 别名;

离开别名所代表的结构，别名可以代表语句块或循环等。
相当于C语言中的break;用于跳出整个循环，结束整个循环

②iterate
iterate的基本格式：

iterate 循环别名;

相当于C语言中的continue;用于跳出本次循环，执行下一次循环

触发器和事件

一、触发器

1、触发器的基本概念

触发器是和表相关联的一种数据库对象，可以将它看作一种特殊的存储过程，可以理解为不需要人为调用的存储过程。

①触发器关键字：trigger
②基本作用：通过对表进行数据的插入、更新或删除等操作来触发，用于执行某些特定的操作。

2、触发器的创建

可以在Mysql命令界面通过‘？ create trigger’命令查看创建触发器的格式。
创建触发器的基本格式：

CREATE TRIGGER trigger_name #触发器名
trigger_time #触发时机：before|after
trigger_event #触发条件：insert|update|delete
ON tbl_name #表名
FOR EACH ROW #对每一行
trigger_body #执行的操作

总的来说就是对表中的某一行进行插入、删除、更新时触发某一触发器来执行某些操作。

参数解析:

① 触发条件
insert：在插入表的时候触发
update：在更新表的时候触发
delete：在删除表的时候触发

② 触发时机
before：在…之前
after：在…之后

例题1：利用触发器实现检查约束
对表格创建一个触发器，规定年龄属性的取值范围在0~100之间，年龄不能低于0岁，不能高于100岁**

①创建测试表格

create table trigger_test_tb(
	id int primary key auto_increment,
	test_name varchar(20),
	test_age int
);

②创建触发器实现检查约束

delimiter //
create trigger tri_test_insert
before insert on trigger_test_tb for each row
begin
	if new.test_age < 0 then set new.test_age = 0; -- 年龄低于0岁的用0代替
	elseif new.test_age > 100 then set new.test_age = 100; -- 年龄高于100岁的用
100代替
	end if;
end//
delimiter ;

③测试触发器

insert into trigger_test_tb values(null,'张三',18);
insert into trigger_test_tb values(null,'李四',20);
insert into trigger_test_tb values(null,'王五',-10);
insert into trigger_test_tb values(null,'王八',120);
select * from trigger_test_tb;
-- 1	张三	18
-- 2	李四	20
-- 3	王五	0
-- 4	王八	100

3、new对象和old对象

数据库提供了两个数据对象new和old分别记录新值和旧值。
例如：update更新数据时会把新的数据覆盖之前的数据，那new对象就是保存新数据，而old对象是保
存被覆盖之前的数据。
那么相对于insert而言是没有old的，因为插入之前是没有旧数据，相对于delete而言是没有new的，
因为只删除原来存在的数据，不添加新数据。
insert 插入数据时，new 表示插入的数据
update 更新数据时，new 表示新的数据,old表示的是原数据
delete 删除数据时，old 表示的是删除的数据

4、查看数据库中的触发器

查看触发器的基本格式：

show triggers from 数据库名; 

5、查看所有触发器

Mysql触发器都是保存在information_shema.triggers表中的，所以查看触发器需要查询
information_shema数据库中的trigger表。

use information_shema;
select * from triggers; # 查看所有触发器
select * from triggers where trigger_name='触发器名';

6、触发器的使用限制

①触发器中不支持使用select、create和call等动态SQL语句
如：

delimiter //
create trigger tri_test_select
after insert on trigger_test_tb for each row
-- begin -- 触发器只执行一条语句，可以不用begin end
	select * from trigger_test_tb where id = new.id; -- 错误：触发器中不支持使用
select语句
-- end//
delimiter ;

②利用用户变量突破触发器的使用限制
如：

create trigger tri_test_select
after insert on trigger_test_tb for each row
	select id from trigger_test_tb where id = new.id into @id;
	-- 用查询结果给用户变量@id赋值，就可以使用用户变量@id将数据带回

insert into trigger_test_tb values(null,'赵六',-1);
select * from trigger_test_tb;
select @id;
select * from trigger_test_tb where id = @id;

7、删除触发器

drop trigger 触发器名;

二、事件

1、事件的基本概念

与触发器类似，都是在特定的条件执行相应的操作，但是不同的是，触发器是触发时执行某些任务，而
事件是定时执行某些任务。

①事件的关键字： event
②事件的基本作用：让数据库定时执行某些操作。

2、事件的创建

①创建事件的基本格式
可以通过’？ create event’命令查看创建事件的格式。
如：

CREATE EVENT [IF NOT EXISTS] event_name #不存在就创建
ON SCHEDULE schedule #计划任务
(定时操作：AT timestamp 时间点，EVERY interval 间隔多久)
	[ON COMPLETION [NOT] PRESERVE] # 事件到期处理
	(事件执行完之后默认保留，加一个NOT为删除)
	[ENABLE | DISABLE | DISABLE ON SLAVE] #事件的状态
	(开启 | 关闭) #相当于你设置的闹钟是打开还是关闭的
	[COMMENT 'string'] # 这是注释，不用管
	DO event_body; # 事件所执行的操作

②参数解析

schedule: # 任务计划参数解析
AT timestamp [+ INTERVAL interval] ...
# AT 时间点 [+ 时间间隔]
（如：AT 2021-6-6 8:30:10，2021年6月6号8点30分10秒）
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
# EVERY 间隔多久
[开始时间 [+ 时间间隔]]
[结束时间 [+ 时间间隔]]

③例题：用事件实现每6秒钟向表中插入数据

创建事件测试表：

create table event_test_tb(
	id int primary key auto_increment,
	insert_time time
);

创建事件实现每6秒钟向表中插入数据：

create event insert_event
on schedule every 6 second
do
	insert into event_test_tb values(null,second(now()));

查看事件执行的效果：

select * from event_test_tb; -- 查看表中是否每6秒会自动增加一条数据

如果事件没有执行效果，则是事件被关闭或者事件调度器未打开。

3、事件的打开与关闭

①设置事件的状态为disable关闭某个事件

alter event 事件名 disable; 

设置事件的状态为enable开启某个事件

alter event 事件名 enable; 

如果将事件打开后事件仍然没有执行，那一般是事件调度器没有打开。

4、事件调度器的打开与关闭

①查看事件调度器的状态

show variables like '%SCHEDULE%'; 

②关闭事件调度器

set global event_scheduler = off;
-- 或
set global event_scheduler = 0;

③打开事件调度器

set global event_scheduler = on;
-- 或
set global event_scheduler = 1;

④查看进程列表
事件调度器开启后是有由一个进程来执行的。

show processlist; 

5、事件小练习

创建一个清空表事件，5天之后开启事件，每小时清空表数据，一个月后停止事件并不保留事件。

create event delete3_event
on schedule every 1 hour
starts current_timestamp() + interval 5 day -- current_timestamp()获取当前事
件，和now()类似
ends current_timestamp() + interval 1 month
on completion not preserve -- 事件停止后不保留，删除事件
do
truncate table event_test_tb; -- 清空表数据

6、查看所有事件的状态

show events;

7、删除事件

drop event 事件名;

8、查看事件的具体执行语句

show create event 事件名; 

9、数据库中时间单位

数据库中常用的时间单位有：

year、month、 day、 week、 hour、 minute、 second、 microsecond 

事务和锁

一、事务

1、事务的基本概念

什么是事务？
事务是作为单个逻辑单元所执行的一系列操作，是一个整体，不可被拆分。
如：新建一个数据库或表格，对表数据的插入、更新、删除，执行一个存储过程或函数等，都是一个事
务。

2、事物的的特性

事物有ACID四个特性

1. 原子性[Atomicity]

   解释: 事务中的所有一系列操作, 要么都成功, 要么都失败

   用途: 如果在事务中途断电了等情况, 事务执行了一半, 另一半未执行成功, 那么整个事务失效

   样例: 银行转账, A转钱给B, A支出100, B收入100, 两个操作都执行完, 才成功, 否则两个操作失效

2. 一致性[Consistency]

   解释: 事务前后的数据完整性保持一致

   样例: A 转账给 B 200元, A - 200, b + 200. 但是 A 和 B 金额总和前后必须保持一致

3. 隔离性[Isolation]

   解释: 多个事务之间相互隔离互不干扰

   样例: A 给 B转钱, B同时给C转钱, 两个事务相互隔离互补影响

4. 持久性[Durability]

   解释: 事务一旦提交成功, 数据就不会因为其他原因无故改变

   样例: 假设A给B转账, 事务提交成功, 服务器断电了, 重启数据库, 提交的数据也不会产生影响

3、事务的隔离产生的问题

1. 脏读:

   指一个事务读取到另外一个事务未提交的数据

2. 不可重复读:

   在一个事务内读取表中某一行数据, 多次读取结果不同。(这个不一定是错误的, 但是不适用于某些场合)

   (读取其它事务改变的数据)

   前

   A	100
   B	200

   后

   A	100
   B	300

3. 幻读(虚读)

   一个事务内读取到了别的事务插入的数据, 导致前后读取不一致

   前:

   A	100
   B	200

   后:

   A	100
   B	200
   C	300

四种隔离级别

set transaction isolation level 设置事务隔离级别
select @@tx_isolation 查询当前事务隔离级别

设置	描述
Serializable	可避免脏读、不可重复读、虚读情况的发生。（串行化）
Repeatable read	可避免脏读、不可重复读情况的发生。（可重复读）
Read committed	可避免脏读情况发生（读已提交）。
Read uncommitted	最低级别，以上情况均无法保证。(读未提交)

① 读未提交：就是一个用户执行一个事务但没提交，另一个用户可以读到没提交的数据。

② 读提交（大多数数据库默认）：就是一个用户执行一个事务，只有提交了，另一个用户才可以读到提交后的数据。

③ 可重读（Mysql默认）:解决了不可重读的问题，但是会出现新的问题：“幻读”问题。

④ 串行化读：就是根本不允许并发，也就是不允许多个用户同时操作一个表。

4、事务的控制

1. 隐式事务

   隐式事务是不用管理员手动写的，是指单独的 insert 、update 、 delete 等进行数据操作的时候默认
   是隐式事务，由系统自动提交。

2. 显示事务

   显示事务需用户手动提交

5、关闭与打开隐式事务自动提交

隐式事务自动提交是由一个变量来控制的。
可以查看自动提交变量的状态，它默认是打开的。
①查看事务的状态：

show variables like 'autocommit';

②关闭隐式事务自动提交

set autocommit = off;	# 或设置为0

③打开隐式事务自动提交

set autocommit = on;	# 或设置为1

启动一个事务

start transaction

6、回滚

事务在没有提交前对数据改变都可以通过回滚撤销, 但是提交之后无法改变

rollback;

7、提交

默认情况执行事务自动提交, 但是我们想组合多条操作的事务, 就需要将事务的自动提交关闭, 手动提交

commit;

8、显式事务

set autocommit = 1;		#  确认开启隐式事务自动提交

①开启事务的显示控制

start transaction;	# 开始显式事务

②测试数据的删除

delete from orders where id = 2;	# 删除数据

是否删除?

rollback; # 回滚(不删除)
commit;	  # 提交(确认删除)

显式控制就相当于删除文件时会提示"是否确认删除"一样, 显式事务需要手动提交
显式的处理事务, 会暂时停止自动提交, 一次回滚或者提交之后还原成自动提交

9、事务隔离级别的验证

隔离级别有两种：
① 会话级别的隔离（当前连接有效）
查看当前会话级别的隔离：
5.7 版本：

select @@session.tx_isolation;

8.0以上版本：

select @@transaction_isolation;

Mysql默认为‘REPEATABLE-READ’,可重读。

**会话级别隔离的设置: **

# 设置当前会话隔离级别为-读未提交
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;

②全局级别的隔离(当前服务有效)

它是由一个全局变量tx_isolation保存的，Mysql默认为'repeatable-read'，可重读。

查看当前全局级别的隔离：

select @@global.tx_isolation

全局级别隔离的设置:

# 设置当前全局隔离级别为-读未提交
set global transaction isolation level read uncommitted;

# 设置当前全局隔离级别为-读提交
set global transaction isolation level read committed;

# 设置当前全局隔离级别为可重读
set global transaction isolation level repeatable read;

# 设置当前全局隔离级别为-串行化读
set session transaction isolation level serializable;

③隔离级别的验证

以会话级别的隔离举例：
首先创建一个测试表并插入数据。
然后打开两个 cmd 命令界面，进入Mysql，打开数据库，每一个界面都分别代表一个用户。
然后分别设置两个cmd窗口中的会话隔离级别，在两个界面中测试各个级别的隔离效果，是否会出现事
务隔离的问题。
最后研究各种隔离级别的特点和其所出现的问题。

二、锁

1、锁的基本概念

①什么是锁？
锁是计算机协调多个进程或线程并发访问某一资源的机制。锁能够保证数据并发访问的一致性、有效性。锁冲突也是影响数据库并发访问性能的一个重要因素。

②多个用户同时读需要加锁吗？

不需要。只有在用户写时才需要加锁，用于保证数据的一致性。

2、锁的类型

Mysql中不同的存储引擎所使用的锁是不同的。
① 表级锁
一个用户在访问一个表时，对整张表进行加锁，另外一个用户就访问不了这个表了。多个用户不能同时
使用一张表。
MyISAM和MEMORY存储引擎采用表级锁。
② 页面锁
将表分成许多页，只对某一页加锁。页面锁可以精确到表的一部分，只锁表里的一部分数据。
BDB存储引擎采用页面锁；而innodb存储引擎表级锁和行级锁都有采用，默认是行级锁。

3、读锁和写锁

① read读锁（共享锁）
当MySQL的一个进程为某一表加了读锁之后，其他的进程包含自身都没有权利去修改这张表的内容，但
是所有的进程可以读出表里面内容。
例如：事务N在A对象上加了共享锁S，其他事务就不可修改表，但可以查询A，并且也可以加共享锁。
② write写锁（排他锁）
当某一个进程在对某一张表加了写锁，完成某些操作之后，如果不释放写锁，其他的进程连查看这张表
的权限都没有，只有等它解除写锁后，其他的进程才可以完成相应的操作。如果该进程没有对该表进行
更新操作，其他的进程只能做查询操作，但是无法实现更新操作。
例如：事务N在A对象上加了排他锁X，其他事务不可读不可写，也不可以加锁。

4、加锁和解锁

用户读数据时自动加read锁, 修改数据时自动加write锁。

① 加锁的基本格式

Lock tables <表名> <锁类型>; # 锁类型: read/write

② 解锁的基本格式

unlock tables;	# 解除当前锁的限制, 不用给定表名, 直接解除

③ 锁多张表

lock tables 表名1 <表名2 表名3 ... 表名n> 锁类型;

游标和条件处理

一、游标

1、游标的基本概念

①什么是游标？
在查询表时，会获得一个查询结果集，游标就是用来遍历这个结果集中每一条记录的，把查询结果集看作是一个容器，那游标就是类似于迭代器一样的东西。

②游标的作用
游标可以返回结果集中一行或多行数据，结果集是存在数据缓冲区里的，游标可以从数据缓冲区里读取相应的数据。
游标只能用在存储过程和函数中，并且一次只能指向一条记录。
游标类似于C语言中的指针，可以指向一块数据内存，然后通过指针访问数据，也可以理解成c++中的容器的迭代器。

2、游标的使用

游标的基本使用如下：
①声明游标(定义游标)

declare 游标名 cursor for select 查询语句;

②打开定义的游标

open 游标名;

③遍历游标

fetch 游标名 into 值列表(变量);

④使用完释放游标(关闭游标)

close 游标名;

3、游标使用时的注意事项

游标在遍历完成后会指向最后一条记录的下一条, 由于下一条没有数据, 就会出现 no data to fetch 的错误。

为了解决这个错误，就需要定义一个条件处理函数NOT FOUND 来捕获这一个异常。没有异常处理的话就会报错。

declare 游标 cursor for 
	select 属性名 from 表格 where 条件;
# 声明错误处理函数 not found, 来捕获遍历结束的错误
declare continue handler for not found;
# 条件处理, 遇到没有找到数据的错误, 就执行一段命令
# 这条命令要写在定义游标之后

4、游标应用示例

使用游标读取一张成绩表中的内容：

delimiter $$
create procedure cursor_test()
begin
declare id int;	-- 定义三个用于存储数据的变量
declare title varchar(225);
declare c_date date;
declare counts int default 0;	-- 定义统计变量
declare cur_exit bool default false;	-- 定义退出条件的变量
declare cur_test cursor for select * from course_tb;	-- 定义游标
declare continue handler for not found set cur_exit=true;	-- 当游标读到末尾时, 就将退出的条件设置为true
open cur_test;	-- 打开游标
cur:loop
fetch cur_test into id, title, c_date;	-- 循环遍历游标
if cur_exit then leave cur;	-- 退出条件为true就跳出循环
end if;
select id, title,c_date;	-- 将读取出的数据输出
set counts = counts + 1;	-- 统计数量
end loop;
select counts;
close cur_test;	-- 关闭游标
end$$
delimiter ;

二、条件处理

1、条件处理的基本概念

①什么是条件处理？

条件处理就是MySQL数据库中的异常处理（错误处理）。

②条件处理的作用

MySQL通过条件处理来捕获与处理错误和异常。

注意：条件处理只能在存储过程中使用，并且存储过程和条件处理只有 MySQL5.5 版本之后才支持，如果用的是 MySQL5.5 之前的版本就不要用条件处理了。

2、定义条件处理

使用命令行（？ declare handler）语句获取语法格式

①条件处理的基本格式：

declare <handler_action> handler
	for <condition_value [, condition_value] ...>
	statement
# 参数解析：
handler_action:  CONTINUE | EXIT | UNDO # 处理动作名
	-- 捕获后怎么处理？ 继续 | 退出 | 不处理(MySQL不支持)
condition_value: # 错误值
     mysql_error_code | SQLSTATE [VALUE]sqlstate_value
	-- 错误码 | 状态值
	|condition_name|SQLWARNING|NOT FOUND| SQLEXCEPTION
	-- |所有以01开头的|所有以02开头的|除了00、01、02开头的错误

②错误码和状态值：

-- 例如: use 不存在的数据库名;
-- 使用不存在的数据库
use db_6;
ERROR 1049 (42000): Unknown database 'db_6' -- 错误信息
-- 1049为错误码，42000为状态值
-- 声明错误条件不能单独使用，要配合处理函数使用

3、条件处理演示示例

① 无错误处理的存储过程

create procedure p_test1()
begin
	select * from untable;	-- 数据表untable不存在
end;

call p_test1();
-- 出现错误:
ERROR 1146 (42S02): Table 'db_5.untable' doesn t exist
-- 调用存储过程后会报错：错误码 1146 (42S02)数据表不存在
-- 所有mysql中所定义的错误码和状态值都可以在MySQL官网上查找

② 在存储过程中用错误码进行错误处理

create procedure p_test2()
begin
	declare continue handler for 1146	-- 定义条件处理, 使用错误码处理此类错误
		select '此表不存在' as 错误;
	select * from untable;
end;
call p_test2();
-- 调用存储过程后不会报错, 而是按照自己设置的进行处理

③在存储过程中用状态值进行错误处理

create procedure p_test3()
begin
	declare continue handler for sqlstate '45s02'	-- 定义条件处理, 使用状态值处理
		select '此表真的不存在' as 错误;
	select * from untable;
end;
call p_test3();

4、条件处理函数

？ declare condition

错误条件函数的声明（？ declare condition）
	declare condition_name condition for condition_value	-- 声明一个条件处理
	-- 给抛出的异常取一个有意义的名字, 相当于给错误取名
	condition_value:	-- 条件值
	mysql_error_code	-- 错误码
	| sqlstate [value] sqlstate_value

5、条件处理函数演示示例

①在存储过程中用条件处理函数给错误码取别名进行错误处理

create procedure p_test4()
begin
	declare no_have_table condition for 1146;	-- 定义错误处理函数no_have_table处理错误
	declare continue handler for no_have_table
		select '此表还是不存在' as 错误;
	select * from untable;
end;

call p_test4();

②在存储过程中用状态值进行错误处理

create procedure p_test5()
begin
	declare no_have_table conditnion for sqlstate '42s02';
	declare continue handler for no_have_table
		select '说了此表不存在还查' as 错误;
	select * from untable;
end;

call p_test5();