MySQL数据库操作——存储过程、视图、事务、索引、数据库备份

1.数据准备

-- 创建student库：student
create database student default character set utf8 collate utf8_general_ci;
/*
创建学生表：students，
学生号为Sid，数据类型为varchar(10)，主键，
姓名为name，数据类型为varchar(10)，
性别为sex，数据类型为varchar(1)，
年龄为age，数据类型为int，
班级为class，数据类型为varchar(10)，
身份证号为card，数据类型为varchar(20)，
城市为city，数据类型为varchar(20)
*/
create table students(
	Sid varchar(10) primary key,
	name varchar(10),
	sex varchar(1),
	age int,
	class varchar(10),
	card varchar(20),
	city varchar(20)
);
-- 向学生表插入数据
insert into students values
('001','王昭君','女',30,'3班','634101199003157654','北京'),
('002','诸葛亮','男',29,'2班','110102199104262354','上海'),
('003','鲁班大师','男',30,'1班','820102199003047654','南京'),
('004','白起','男',35,'4班','840202198505177654','安徽'),
('005','大乔','女',28,'3班','215301199204067654','天津'),
('006','孙尚香','女',25,'1班','130502199506137654','河北'),
('007','百里玄策','男',39,'2班','140102198107277654','山西'),
('008','小乔','女',25,'3班',null,'河南'),
('009','百里守约','男',31,'1班','','湖南'),
('010','妲己','女',24,'2班','440701199607147654','广东'),
('011','廉颇','男',30,'1班','110202199005017754','北京'),
('012','孙膑','男',36,'3班','650102198401297655','新疆');
/*
创建课程表：courses
课程号为Cid，数据类型为varchar(10)，主键，
课程名为Cname，数据类型为varchar(10)
*/
create table courses(
	Cid varchar(10) primary key,
	Cname varchar(10)
);
-- 向课程表插入数据
insert into courses values
('01','语文'),
('02','数学'),
('03','英语'),
('04','生物'),
('05','政治'),
('06','地理'),
('07','物理');
/*
创建分数表：scores，
学生号为Sid，数据类型为varchar(10)，学生表外键，
课程号为Cid，数据类型为varchar(10)，课程表外键，
分数为score，数据类型为tinyint
*/
create table scores(
	Sid varchar(10),
	Cid varchar(10),
	score tinyint,
	foreign key (Sid) references students(Sid),
	foreign key (Cid) references courses(Cid)
);
-- 向分数表插入数据
insert into scores values
('001','01',49),
('001','02',75),
('001','03',54),
('001','04',66),
('001','05',59),
('001','06',94),
('002','01',58),
('002','02',57),
('002','03',74),
('002','04',90),
('002','05',55),
('002','06',64),
('003','01',83),
('003','02',73),
('003','04',50),
('003','05',67),
('003','06',87),
('004','01',97),
('004','02',50),
('004','03',56),
('004','04',69),
('004','05',72),
('004','06',51),
('005','01',54),
('005','02',66),
('005','03',61),
('005','05',49),
('005','06',86),
('006','01',97),
('006','02',77),
('006','03',66),
('006','04',76),
('007','01',49),
('007','02',40),
('007','03',99),
('007','04',86),
('007','05',78),
('007','06',55),
('008','01',82),
('008','02',72),
('008','03',98),
('008','05',47),
('008','06',68),
('009','01',84),
('009','02',56),
('009','03',74),
('009','04',64),
('009','05',79),
('009','06',97),
('010','02',85),
('010','03',85),
('010','04',90),
('010','05',93),
('010','06',61),
('011','01',85),
('011','02',63),
('011','03',71),
('011','04',58),
('011','05',80),
('011','06',66);

2.存储过程

定义：存储过程是具有名字的一段sql代码，它是由一条或者多条SQL语句的组成的集合，用来共同完成一个特定的功能
创建存储过程
sqlyog语法：

delimiter $$
create procedure 存储过程名称(参数,参数)
begin
sql语句1;
sql语句2;
end$$
delimiter ;

说明：
delimiter $$意思为定义$$为SQL语句结束符；
存储过程名称后面的参数如果没有可以不写参数

Navicat语法：

create procedure 存储过程名称(参数,参数)
begin
sql语句1;
sql语句2;
end

练习1：

-- 创建存储过程stu_pro，内容为查询students表的所有数据（Navicat）
create procedure stu_pro()
begin
select * from students;
end;

查询所有数据库的存储过程
语法：

show procedure status;

练习1：

-- 查询所有数据库的存储过程
show procedure status;

查询某个数据库的存储过程
语法：

show procedure status where db='数据库名称';

练习1：

-- 查询student数据库的存储过程
show procedure status where db='student';

查询存储过程详细的定义信息
语法：

show create procedure 数据库名称.存储过程名称;

练习1：

-- 查询存储过程名称为stu_pro的详细的定义信息
show create procedure student.stu_pro;

调用存储过程
语法：

call 存储过程名称();

练习1：

-- 调用存储过程stu_pro
call stu_pro();

删除存储过程
语法：

drop procedure 存储过程名称;

注意：删除存储过程时不需要写存储过程名称后面的括号
练习1：

-- 删除存储过程stu_pro
drop procedure stu_pro;

3.视图

定义：视图是基于SQL语句的结果集的可视化的虚拟表
创建视图
语法：

create view 视图名称 as sql语句;

练习1 ：

-- 创建视图名为stu_nan，内容为查询students表男生的所有信息
create view stu_nan as
select * from students where sex='男';

使用视图
语法：

select * from 视图名称;

说明：可以把视图当做一个表来处理，但是对数据只有只读权限（只能查，不能增、改、删）
练习1：

-- 使用视图stu_nan
select * from stu_nan;

删除视图
语法：

drop view 视图名称;

练习1：

-- 删除视图stu_nan
drop view stu_nan;

练习2：

-- 如果视图stu_nan存在则删除
drop view if exists stu_nan;

4.事务

定义：事务是一个对数据库操作的序列,是一个不可分割的工作单位,要么这个序列里面的操作全部执行,要么全部不执行
特点：
原子性：事务中的全部操作在数据库中是不可分割的，要么全部执行，要么全部不执行；
一致性：事务是按照时序串行执行，在事务开始和结束之间的中间状态不会被其他事务所看到；
隔离性：如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
持久性：事务完成后，对数据库数据的修改被持久化存储
语法：

-- 开始事务
begin;
--sql执行语句
sql语句;
-- 回滚事务
rollback;
-- 提交事务
commit;

注意：如果只开始了事务，但没有进行回滚事务或者提交事务，则默认回滚事务
练习1：

-- 开始事务
begin;

-- 更改王昭君的年龄为35，并且更改王昭君的语文成绩为95
update students set age=35 where name='王昭君';
update scores set score=95 where Sid in (select Sid from students where name='王昭君')
and Cid in (select Cid from courses where Cname='语文');

-- 回滚事务
rollback;

-- 提交事务
commit;

理解：一个事务进行需要先开始事务（begin），告诉sql我要开始一个事务了，然后执行insert、update、delete语句，执行的结果会放在缓存中（这时查询王昭君的年龄和语文成绩则使用的是缓存中的数据），但是你直接打开表查看数据的话会发现没有任何更改，因为还未提交，这时你提交事务的话，才真正修改表，否则sql会自动回滚到事务开始之前，也就是没有发生任何改变。

5.索引

定义：索引是帮助MySQL高效获取数据的数据结构。通俗来讲就是索引好比书本的目录，大大加快数据库的查询速度
优缺点：
优点：大大提高select语句的查询速度
缺点：会降低更新表的速度，例如：insert、update、delete操作
创建索引
语法：

create index 索引名称 on 表名(字段名(长度));

注意：
如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致；
如果字段类型不是字符串，可以不用填写长度部分；
对于主键，系统会自动建立索引，也就是主键索引
练习1：

-- 给students表的name字段创建索引name_index
create index name_index on students(name(10));

查看索引
语法：

show index from 表名;

练习1：

-- 查看students表的所有索引
show index from students;

删除索引
语法：

drop index 索引名称 on 表名;

练习1：

-- 删除students表的索引name_index
drop index name_index on students;

说明：创建索引需要用到字段名，如果一张表有索引，那么只要你查询的条件中使用到了这个字段，则查询时它就会自动使用索引

6.数据库备份

命令：

mysqldump -u用户名 -p密码 -A > /要存放的文件路径/文件名称.sql