mysql基础——视图、索引、外键、存储过程

mysql基础——视图、索引、外键、存储过程
视图
一、什么是视图
视图是一个虚拟表（逻辑表），他不在数据库中以存储数据的形式保存（本身不包含数据），而是在使用视图的时候动态生成的

二、视图的使用
1、数据库中的数据查询非常复杂，例如多表，子查询，编写语句较多，并且这种查询常被重复使用，那么我们就可以创建视图，避免每次写sql语句出现错误，提高了查询的效率。
2、某些企业有些字段为保密字段，为了安全数据，通过创建视图，限制对保密字段的操作。

三、视图的优缺点
优点：
1、提高查询效率
在数据库中非常复杂的数据可以通过创建视图可避免多次编写sql语句出现的错误
2、安全
企业某些保密字段可以通过创建视图来限制用户对保密字段进行操作
3、简单
不需要关系后面对应的表结构

缺点：
1、性能差
把视图查询结果转换成对表的查询
2、修改限制
修改视图，必须把它转换为对基础表的修改
脱敏测试也叫加密测试
通常情况下，数据库是敏感的，（脱敏测试）不是每个用户都能进行访问，但是某些用户需要读取表中的部分数据时，这就可以指定条件创建视图来限制用户只能访问指定的数据，并提供一个视图接口进行访问。

四、视图的使用
1、show tables;查看所有的表（先查看数据库中的基本表）

2、创建设视图
格式：create view 视图名 as (sql语句);
案例：create view hz as (select * from emp,dept from where dept1=dept2);

3、查看视图
格式：show create view 视图名
案例：show create view hz;

4、删除视图
格式：drop view 视图名
案例：drop view hz1

5、查看视图所有内容
格式：select * from 视图名；
案例：select * from hz；

6、创建视图后就不需要重复合表，语句也简便
案例：create view hz as (select * from emp,dept where dept1=dept2 )
select * from hz where name='张三'；
视图就是合表，可以简化查询语句

7、视图特点
（1）视图是由基本表产生的虚拟表
（2）视图的创建和删除不影响基础表
（3）视图的更新和删除数据直接影响基础表
update hz set name='孙宝' where name='张三'；
delet from hz where name='孙宝'；（错误注意：合表不能删除）
视图是单表情况下可删除，多表则报错
（4）当视图内容来源于多个虚拟表结构，不能删除这个视图
alter table hz2 drop dept1;
alter table hz2 add id int（10）;
（5）视图不能修改表字段
（6）基础表修改数据，视图也同样修改
update dept set dept1=105 where dept1=104;
（7）基础表修改字段，视图报错，可以增加字段
alter table dept change dept 1  dept3 varchar;

总结：
1、数据库会哪些
数据库增删改查、视图、索引、外键、存储



索引
一、什么是索引（重点：索引是一种数据结构）
一个索引在存储的表中的数据结构，
索引是在表的字段名上创建的
索引包含了一个列的值，这个值保存在一个数据结构中

二、索引的作用
1、保证数据记录的唯一性
2、实现表与表之间的参照完整性
3、减少排序和分组的时间（例如：使用order by,group by 查询语句进行数据检索时）
4、可以利用索引快速访问数据库中特定的信息（重点）

三、索引的缺点
1、索引要占用物理内存
2、对表进行增删改查，索引也要动态维护，降低数据的维护速度


四、索引的分类
1、普通索引
index （简写为mul ） 最基本的索引，没有任何限制
2、主键索引
primary key （简写为pri ） 是一种唯一索引，不能为空
3、唯一索引
unique （简写为uni ）是一种唯一索引，可为空
4、全文索引
5、组合索引
6、单列索引
7、聚焦索引
8、非聚焦索引

五、索引的使用
1、查看索引（先通过查看表）
第一种方法：
格式：show index from 表名;
案例;show index from emp;
第二种方法：
格式：show keys from 表名；
案例：show keys from emp

查看表结构
格式：desc 表名；
案例：desc hz;

2、创建索引
（1）建表后创建索引
建立普通索引 index
格式：alter table 表名 add index 索引名(字段名)  简写为mul（索引名与字段名不同）
案例：alter table emp add index aa(age)

如果不写索引名，则默认字段名和索引名相同
格式2：alter table表名 add index (字段名)   （索引名与字段名相同）
案例：alter table emp add index (name)

创建唯一索引  unique 简写为uni，唯一索引可以为空
格式1：altre table emp add unique 索引名（字段名）
案例1：altre table emp add unique  
格式2：altre table emp add unique （字段名）
altre table emp add unique sid


创建主键索引  primary key 简写为（pri）唯一，不能为空，一个表中可以有多个唯一索引，主键索引只有一个
格式1：alter table 表名 add index primary key  （索引名）
案例1：alter table emp add index primary key  （sid）

唯一索引与主键索引的区别：
1、一张表中唯一索引可以有几个，而主键索引只有一个，唯一索引可以为空，主键索引不能为空
2、删除方式不同：
删除主键索引：alter table 表名 drop primary key
删除普通索引和唯一索引：alter table 表名 drop index 索引名


删除索引
第一种方法：删除普通索引和唯一索引
alter table 表名 drop index 索引名
alter table emp drop index sid
第二种方法：删除主键索引
格式：alter table 表名 drop primary key
案例：alter table emp drop primary key

（2）创建索引
格式：create index索引名 on 表名 (字段名)；
案例：create index jj on emp (dept2)
同时创建多个索引
格式：create index jj on emp (sid,incoming,)
案例：create index jj on emp (sid,incoming)
（3）新建表时创建索引
格式：create table 表名(字段1 字符类型1(字符长度) primary key,字段2 字符类型2(字符长度),字段3 字符类型3(字符长度) unique);
案例：create table hz1010(id int(10) primary key,name varchar(10),carno int(30) unique);


面试题
1、你熟悉数据库语句吗
增删改查、视图、索引

2、索引有什么作用
保证数据记录的唯一性
实现表与表之间的参照完整性
减少排序和分组的时间（例如：使用order by,group by 查询语句进行数据检索时）
可以利用索引快速访问数据库中特定的信息


3、创建索引的时间
建表时创建索引：
格式：create table 表名(字段1 字符类型1(字符长度) primary key,字段2 字符类型2(字符长度),字段3 字符类型3(字符长度) unique);
建表后创建索引
格式1：alter table 表名 add index 索引名(字段名)
格式2：alter table表名 add index (字段名)


外键
一、什么是外键
让一张表记录的数据不要太过于冗余，在数据库中对表的关系进行解耦，尽量让表的数据单一化。

二、外键的作用
外键的作用：保证数据的一致性和完整性

三、mysqlz数据库中的存储引擎
innodd(外键要使用innodb存储引擎)
myisam（默认）

四、查看存储引擎
格式：show table status from 库名 where name='表名';
案例：show table status from hz10 where name='emp';

1、若存储方式不一样要建表
建表语句
(1)通过建表语句来创建主键

mm表（父表）
create table mm (id int(10) primary key ,name varchar(10))ENGINE=innodb ;
nn表（子表）
create table nn (sid int(10) primary key ,sname varchar(10))ENGINE=innodb ;
create  table  nn (sid  int(10) PRIMARY key ,sname  varchar(20)  , constraint  fk_wj  foreign  key(sid)  REFERENCES   mm(id) )engine=innodb ;

contraint 外键名   指定外键
foreign key  (子表)  指定子表字段
references 父表  （父表字段） 引用外部表的主键

(2)直接创建一个外键（先建表，再建主键）
创建ss表：（父表）
create table mm (id int(10) primary key ,name varchar(10)ENGINE=innodb ;
创建cc表：（子表）
create table mm (sid int(10) primary key ,sname varchar(10)ENGINE=innodb ;
创建外键
格式：alter table cc add constraint 外键名 foreign key  (子表字段) references 父表(父表字段);
案例：alter table cc add constraint ss_key foreign key  (sid) references ss(id);


查看外键
方法一：
C:\Users\懒猫的卷发\AppData\Local\YNote\data\weixinobU7Vjn4yMNDRmRjnHXxm3n0WJOU\470d4ca6b3de4c86a3ae9cd506020fa8\clipboard.png
方法二：
show create table cc;

外键不能修改只能删除
删除外键
格式：alter table 表名 drop foreign key 外键名
案例：alter table cc drop foreign key ss_key

五、子表和父表的关系
1、在子表插入数据，父表不存在的数据无法插入
案例：
报错（父表不存在的数据，子表无法插入）
insert into cc values(1,'aa')
2、在子表中插入数据，父表存在的数据才能插入
insert into ss values(1,'wq');
insert into cc values(2,'jm')
3、当父表和子表都为空时，子表无法插入数据，父表可以插入数据
4、父表中删除数据需要先删除子表数据，父表和子表存在外键时
（1）子表和父表数据都存在，直接删除父表报错，无法删除
delet from ss where id=1;时报错
（2）先删除子表，才能删除父表
delete from cc where sid=1;先删除子表
delet from ss where id=1;再删除父表
5、外键名可以修改
alter table cc add constraint ss foreign key(sid) references ss(id);


总结：
1、如何创建外键
两种：
（1）、建表时创建 父表作为普通表，在子表中设置外键
格式：create  table  nn (sid  int(10) PRIMARY key ,sname  varchar(20)  , constraint  fk_wj  foreign  key(sid)  REFERENCES   mm(id) )engine=innodb ;
（2）、建表后创建
格式：alter table 子表表名 add constraint 外键名 foreign key (子表字段) references 父表（父表字段）
外键都要用engine=innodb

2、父表与子表的关系
（1）父表不存在的数据，子表无法插入
（2）两表为空时，子表不能插入数据，父表可插入
（3）当父表删除数据需要先删除子表数据
3、公司为什么用外键
（1）保证数据的一致性和完整性
（2）企业数据量庞大，造成数据重复，新旧表维护成本大，使用外键让数据进行分类和关联，让基础数据放在主表上，子表单一化，可以避免数据的冗余。


存储过程
一、存储的介绍
1、什么是存储过程
是实现某一个特定功能的sql语句的集合，编译后的存储过程会保存在数据库中，通过存储过程的名称可以反复的调用执行

2、存储过程的优点
（1）存储创建后就可以反复调用和使用，不用重新写复杂的语句
（2）创建、修改存储过程不会对数据库有任何影响
（3）存储过程可以通过输入参数和返回值
（4）存储过程可以通过加入控制语句加强sql语句的功能性和灵活性
（5）对于单个的增删改查语句，可以直接封装在一个函数体中，或者封装在一个集合中，存储过程一经创建就可以直接调用，而且可以重复使用
（6）单个sql语句每次执行都要数据库进行编译，而存储过程创建只需要编译一次，后续就可以循环调用
（7）创建存储过程可以重复进行调用，减少开发人员的工作量
（8）防止sql注入
（9）造数据

二、存储的基本格式：
delimiter // (分隔符，定格符)
create procedure hz1 ()#存储名称（in或out或inout）
begin(开始)
select * from emp;（执行语句1）
select * from dept;（执行语句2）
end （结束）
//(分隔符)
先执行创建存储，再调用使用call

调用存储：call 存储名 （）；

基本使用语句：
查看存储
（1）查看单个存储过程的详情
格式：show create procedure 存储名；
show create procedure hz1;

（2）查看所有已创建的存储过程详情
show  procedure status;
（3）查询数据库创建的存储过程
格式：show  procedure status where db='数据库名';
show  procedure status where db='hz10';
（4）删除存储
格式：drop procedure 存储名称;
drop procedure hz2;


三、存储过程的编写
1、无参数的存储过程
格式
delimiter //
create procedure 存储名称()
begin
语句
end
//
call 存储名称() #调用存储

案例
delimiter //
create procedure aa()
begin
select * from emp
end
//
调用存储过程：call aa()

2、in输入参数的存储过程
格式：
delimiter //
create procedure 存储名称(in x int)
begin
select * from 表名 where 字段名=x
end
//
调用存储过程：call bb(101)
案例
delimiter //
create procedure bb(in x int)
begin
select * from emp where dept2=x
end
//
调用存储过程：call bb(101)


3、out输出参数的存储过程
格式：
delimiter //
create procedure 存储名称(out y int)
begin
select sid into y from 表名 where name='张三'；
end
//
调用存储过程：call bb(@y)
select @y

4、in,out 同时存在
格式：
delimiter //
create procedure 存储名称(out y int，in x int)
begin
select sid into y from 表名 where sid=x；
end
//
调用存储过程：call bb(@y,1789)
select @y

5、inout输入和输出

delimiter //
create procedure 存储名称(inout n int)
begin
set n:=n+1
end
//
set @n=2
调用存储过程：call bb(@y,1789)
select @n

5、用户变量 定义语法
方法一
set @变量名
赋值的语法：
（1）set @ 变量名 ：=值
（2）set @ 变量名 =值
（3）select @ 变量名: =值;
方法二：通过查询结果为变量赋值
select 字段|表达式 into 变量名 from 表名 where 条件

2、声明变量
declare i  int default #declare 声明变量，i是变量  数据类型 int 默认值

第一种：造数：（先建表，再造数据，写死的数据）
create table abc(id int(10));
delimiter //
drop procedure if exists hh  #增强存储的健壮性
create procedure hh()
begin
declare i int default 0; #声明一个变量，i为变量，int为类型  条件
while(i<10) do
insert into abc values(i)
set i=i+1;
end while;
select * from abc;
end
//
调用存储过程：call hh()

第二种造数：根据输入的数据造数，再存储外总数
create table abc(id int(10));
delimiter //
drop procedure if exists hh  #增强存储的健壮性
create procedure hh(in x int)
begin
declare i int default 0; #声明一个变量，i为变量，int为类型  条件
while(i<x) do
insert into abc values(i);
set i=i+1;
end while;
select * from abc;
end
//
调用存储过程：call hh(20)

第三种造数：在存储中造表再造数
判断有没有表：drop table if exists abc;
drop table abc ;
create  table  abc (id  int(10)) ;

delimiter //
DROP  table if  EXISTS  abc;
DROP PROCEDURE  if  exists hh ; #增强存储的健壮性，判断存在删除
create   procedure  hh(in  x int)
BEGIN
  declare  i  int  default  0 ; #声名一个变量， i变量 ，int数据类型  条件
  DROP  table if  EXISTS  abc;
  create  table  abc (id  int(10)) ;
  while (i<x) DO
     INSERT  into  abc VALUES (i);
     set i=i+1;
  end WHILE ;
  select * from  abc ;

END
//
call hh(100)

循环语句
数据库中有三种
while..(条件).do..（执行语句）...end while
loop...end loop
repeat....until end repeat

判断语句：if
if  条件  then
执行sql1
else
执行sql1
end if;

如果是多分枝条件语句
if  条件1  then
执行sql1
else if 条件2
执行sql2
end if 条件3
执行sql3
else
执行语句4
end if;
end if;
end if;