MySQL视图、索引、外键、存储过程
视图:一、什么是视图?
视图是一个虚拟表(逻辑表),它不在数据库中以存储数据的形式保存(本身不包含数据),还是在使用视图的是时候动态生成。
二、视图的作用?
1、数据库中的数据的查询非常复杂,例如多表、子查询、编写语句较多、并且这种查询常被重复使用,那么我们就可以创建视图,避免每次写sql语句会错误,也提高了查询的效率。
2、为了安全,在公司中,有些字段为保密字段,那么可以创建视图,限制对某些字段的操作。
三、视图的优缺点?
优点:
1、提高查询效率
在数据库中非常复杂的数据,可以通过创建视图避免多次编写sql语句出现的错误
2、安全
公司有些保密字段,可以通过创建视图来限制用户对某些字段进行操作
3、简单
不需要关系后面对应的表结构
缺点:
1、性能差
把视图查询结果转换成对表的查询
2、修改限制
修改视图数据,必须把它转化为对基础表的修改
脱敏测试(就叫加密测试)
通常情况下,数据库是敏感,(脱敏测试)不是每一个用户都能进行访问,但是某写用户需要读取表中的部分数据,这时就可以指定条件创建视图来限制用
户只能访问指定的数据,并提供一个视图接口进行访问。
==============================================
视图的使用:
(1)showtables;显示所有的表(先查看数据库中的基本表)
(2)创建视图:
格式:createview视图名 as (sql语句)
案例:createviewhz as (select*fromemp,deptwheredept1=dept2)
(3)查看创建的视图(包含建表语句,视图名,编码格式)
格式:showcreateview视图名;
案例:showcreateviewhz ;
(4)删除视图
格式:drop view 视图名
案例:drop view hz;
(5)查看视图所有内容
格式:select* from 视图名 ;
案例:select* from hz ;
(6)创建视图以后,就不需要重复合表,语句也简单
hz:视图就是合表,可以简化查询语句
案例:
视图:createviewhz as (select*fromemp,deptwheredept1=dept2)
select* from hz where name="张三"
==============================================
视图的特点:
1、视图是由基本表产生的虚拟表
2、视图的创建和删除不影响基础表
3、视图的数据更新和删除数据直接影响基础表(视图是单表情况下可删除,多表下报错)
4、视图内容来源于多个虚拟表结构不能删除
5、视图不能修改表字段
6、基础表修改数据,视图也修改
7、基础表修改字段,视图报错
==============================================
面试题:
1、数据库会哪些?
数据库的增删改查 ,视图
2、讲下什么视图?
3、视图的作用?
索引
一、索引的介绍
1、什么索引?(重点:索引是一种数据结构)
一个索引在存储的表中的数据结构,
索引是在表的字段字上创建。
索引包含了一个列的值,这个值保存在一个数据结构中
2、索引的作用:
(1)保证数据记录的唯一性
(2)实现表与表之间的参照完整性
(3)减少排序和分组的时间 (例如在使用orderby,groupby 查询语句进行数据检索时)
(4)可以利用索引快速访问数据库中特定信息(重点)
3、索引的缺点:
(1)索引要占物理内存
(2) 对表进行增删改查 ,索引也要动态维护,降低数据的维护速度
4、索引的分类:
(1)普通索引
index mul 最基本的索引,没有任何限制
(2)主键索引
primarykey pri 是一种唯一索引,不能为空
(3)唯一索引
unique uni 是一种唯一索引,可为空
(4)全文索引
(5)组合索引
(6)单列索引
(7)聚焦索引
(8)非聚焦索引
5、索引的使用
(1)索引查询方法
1.1方法一:
格式:show INDEX from 表名;
show INDEX fromemp ;
1.2格式:show keysFROM 表名 ;
gshow keysFROM emp ;
(2) 查看表结构
格式:desc 表名案例:desc emp
(3)创建索引:
3.1建表后建立索引
3.1.1建立普通索引 indexmul(简写)
备注(索引名和字段名不同)
格式1:altertable表名 add index索引名(字段名) ;
案例1:altertableemp add indexaa(age) ;
备注(索引名和字段名相同)
格式2:altertable表名 add index(字段名);
案例2:altertableemp add index(name);
3.1.2创建唯一索引 unique 简写(uni)唯一索引,可以为空,一个表中有多个唯一索引
备注(索引名和字段名不同)
格式1:alter table 表名 add unique 索引名(字段名)
案例1:ALTERtable emp add unique hzsid(sid)
备注(索引名和字段名相同)
格式2:alter table 表名 add unique 字段名)
案例2:ALTERtable emp add unique (sid)
3.1.3 创建主键索引 primarykey 简写(pri)唯一,不能为空 ,主键索引一个表中只有一个
格式1:ALTER table表名 addprimarykey(字段名)
案例1:ALTER tableemp addprimarykey(sid)
3.2create 方法创建索引
格式:createindex索引名 on表名(字段名)
案例:createindexjj onemp(dept2)
3.3新建表时建立索引
案例:create tablehz1010(id int(10) PRIMARY key , name varchar(10), carno int(30) unique )
==================
删除索引;
方法一:(只能删除普通索引和唯一索引)
格式:ALTERtable表名 dropindex 索引名;
案例:ALTERtable emp dropindex name ;
方法二:(只删除主键索引)
格式:ALTERtable表名drop PRIMARYkey;
ALTERtableempdrop PRIMARYkey;
==================
面试题;
1、你熟悉数据库语句吗?
2、你说下索引有哪些?
3、索引主要用来做什么?
4、创建索引的时间?
5、主键索引和唯一索引的区别?
外键
一、外键的介绍
1、外键的定义:
让一张表记录的数据不要太过于冗余,在数据库中对表的关系进行解耦,尽量让表的数据单一化。
2、外键作用
外键的作用:保持数据的一致性和完整性
3、mysql 数据库中存储引擎?
innodb(外键要使用innodb存储引擎)
myisam(默认)
4、查看存储引擎
格式:showtablestatusfrom 库名where name="表名";
案例:showtablestatusfrom hz10where name="emp";
===============================
建表语句:
(1)通过建表语句来创建主键
mm表
nn表
mm表如下创建:
createtablemm(idint(10) primarykey, name varchar(10))ENGINE=innodb ;(父表)
nn表 如下创建:
createtablenn (sidint(10) PRIMARY key ,snamevarchar(20), constraintfk_wjforeignkey(sid)REFERENCES mm(id) )engine=innodb ; (子表)
constraint 外键名 指定外键
foreignkey (子表)指定字段
REFERENCES父表(父表字段) 引用外部表的主键
(2)直接创建键(表先建好)
创建ss 表: (父表)
createtable ss (idint(10) primarykey, name varchar(10))ENGINE=innodb;
创建cc 表:(子表)
createtablecc(sidint(10) primarykey, sname varchar(10))ENGINE=innodb
格式:altertable 子表 addCONSTRAINT外键名foreignkey (子表字段名) REFERENCES 父表(父表的字段名)
案例:altertable cc addCONSTRAINTss_keyforeignkey (sid) REFERENCES ss(id)
===============================
查看外键:
方法一:
格式:showcreatetable 表名 ;
showcreatetablecc ;
===============================
删除外键:
格式:altertable表名drop foreignkey 外键名
案例:altertablecc dropforeign keyss_key ;
===============================
一、子表和父表之间关系:
1、在子表插入数据,父表不存在的数据无法插入(报错)
案例:INSERTintocc VALUES (1,"aa")
报错(父表不存在的数据,子表无法插入)
Cannot add or update a child row: a foreign key constraint fails (`hz10`.`cc`, CONSTRAINT `ss_key` FOREIGN KEY (`sid`) REFERENCES `ss` (`id`))
2、在子表插入数据,父表存在的数据才能插入
案例:
INSERTintoss VALUES (1,"wq") ;先插入父表
INSERTintocc VALUES (1,"aa")
3、父表和子表都为空数据,子表无法插入数据,父表可以插入数据
4、存在外键时:父表删除数据,需要先删除子表数据
4.1子表和父表数据都存在,直接删除父表,报错,无法删除
案例:DELETE fromss where id=1 ;直接删除父表
Cannot delete or update a parent row: a foreign key constraint fails (`hz10`.`cc`, CONSTRAINT `ss_key` FOREIGN KEY (`sid`) REFERENCES `ss` (`id`))
4.1子表和父表数据都存在,直接先删除子表 ,在删除父表
DELETE fromcc where sid=1 ;先删除子表数据DELETE fromss where id=1 ;在删除父表数据
4、主键名可以修改
altertable cc addCONSTRAINTssforeignkey (sid) REFERENCES ss(id)
===============================
总结:
1、如何创建外键?
两种方法:1、建表时创建 父表 在子表设置外键
格式:createtable表名(sidint(10) PRIMARY key ,snamevarchar(20), constraintfk_wjforeignkey(sid)REFERENCES mm(id) )engine=innodb ; (子表)
2、建表后创建
格式:altertable子表表名addCONSTRAINT外键名foreignkey (子表字段) REFERENCES 父表(父表字段) engine=innodb ;
2、父表和子表的关系?
(1)当父表不存在的数据,子表无法插入
(2)当两表为空时,子表无法插入数据,父表可插入
(3)当父表删除数据,需要先删除子表数据
3、公司为什么用外键?
(1)保存数据的一致性和完整性
(2)公司数据量大,造成数据重复,新旧表维护成本大,使用外键让公司数据进行分类 和管理,让基础数据放在主表上,子表单一化,这样避免数据的冗余。
存储过程
一、存储的介绍
(1)什么是存储过程?
h存储过程是实现某个特定功能的sql 语句的集合,编译 后的存储过程会保存 在数据库中,通过存储过程的名称可以反复的调用执行。
(2)存储过程的优点?
1、存储创建后,就可以反复非调用和使用,不需要重新写复杂的sql语句
2、创建、修改存储过程不会对数据有任何的影响
3、存储过程可以通过参数和返回值
4、存储过程可以通过加入控制语句,加强sql语句的功能性和灵活性
5、对于的那个的增删改查语句,可以直接封装在一个函数体当中,或者封装在一个集合当中,存储过程一旦创建就可以直接调用,而且可以重复调用
6、单个sql语句每次执行都要数据库进行编译,而存储过程创建只需要编译一次,后续就可调用
7、创建存储过程,可以重复进行调用,可以减少开发人员的工作量
8、防止sql注入
9、造数据
============================================
二、存储的基本格式:
delimiter //#分隔符/定格符
create procedure 存储名称(in或out 或 inout)
BEGIN#开始
执行语句块1
执2语句块1
end #结束
// #分隔符
先执行创建存储,在调用使用call
call 存储名() 调用存储
============================================
基本使用的语句:
(1)查看单个存储过程的项详情
格式:showcreate procedure存储名;
showcreate procedurehz1 ;
(2)查看所有已经创建的存储过程详情
格式:showPROCEDURESTATUS ;
showPROCEDURESTATUS ;
(3)查询数据库创建的存储过程
格式:showPROCEDURESTATUS wheredb="数据库名" ;
showPROCEDURESTATUS wheredb="hz10" ;
(4)删除存储
格式:dropprocedure 存储名称 ;
dropprocedure hz2 ;
============================================
存储过程的编写
(1)无参数的存储过程
格式:
delimiter //
create procedure存储名称()
BEGIN
语句;
END
//call存储名称() #调用存储过程例:
案例:delimiter //
create procedureaa()
BEGIN
select*from emp;
END
//
call aa()
(2)in输入参数的存储
格式:
delimiter //
create procedure 存储名称( in x int)
BEGIN
select*from 表名where 字段名=x;
END
//
call 存储过程名(103)
案例:
delimiter //
create procedurebb( in x int)
BEGIN
select*from empwhere dept2=x;
END
//
call bb(103)
(3)out输出参数
格式:
delimiter //
create procedureee( out y int )
BEGIN
selectsid into y from empwhere name="sunbao";
END
//
call ee(@y)
select @y
(4)in ,out
案例
delimiter //
create procedureff( out y int ,inx int)
BEGIN
selectage into y from empwhere sid=x;
END
//
call ff(@y,1789)
select @y
(5)inout输入和输出
delimiter //
create procedurehh( inoutn int)
BEGIN
setn:=n+1;
END
//
set @n=2
call hh(@n)
select @n
============================================
1、用户变量定义语法:
set @变量名
赋值的语法:
(1)set@ 变量名:=值
(2)set@ 变量名 =值
(3)select@变量名:=值 ;
方法二:通过查询结果为变量赋值
select 字段| 表达式 into 变量名 from表名 where条件
2、声明变量
declare iintdefault0 #declare声明变量 ,i i 变量 数据类型int 默认值
============================================
(1)第一种造数:( 在存储外先建表,在造数据,写死的数据)
delimiter //
DROP PROCEDUREifexists hh ; #增强存储的健壮性,判断存在删除
create procedurehh()
BEGIN
declareiintdefault0 ; #声名一个变量, i变量 ,int数据类型条件
while (i<10) DO
INSERTintoabc VALUES (i);
set i=i+1;
end WHILE ;
select * fromabc ;
END
call hh()
(2)第二种造数:( 在存储外先建表,在造数据,根据输入的数据造数)
delimiter //
DROP PROCEDUREifexists hh ; #增强存储的健壮性,判断存在删除
create procedurehh(inx int)
BEGIN
declareiintdefault0 ; #声名一个变量, i变量 ,int数据类型条件
while (i<x) do="" INSERTintoabc VALUES (i);
set i=i+1;
end WHILE ;
select * fromabc ;
END
call hh(1000)
第三种造数:( 在储存中造表,在造数据,根据输入的数据造数)
drop table abc ;
createtableabc (idint(10)) ;
delimiter //
DROPtable ifEXISTSabc;
DROP PROCEDUREifexists hh ; #增强存储的健壮性,判断存在删除
create procedurehh(inx int)
BEGIN
declareiintdefault0 ; #声名一个变量, i变量 ,int数据类型条件
DROPtable ifEXISTSabc;
createtableabc (idint(10)) ;
while (i<x) do="" INSERTintoabc VALUES (i);
set i=i+1;
end WHILE ;
select * fromabc ;
END
//
call hh(100)
======================================
循环语句:
数据库中有三种:
while ......条件...do....执行语句块....endwhile
loop........endloop
repeat ........until endrepeat
======================================
if判断语句
if语句的单分支:
if条件 then
执行sql1
else
执行sql2
endif ;
======================================
if判断语句
if语句的多分支:
if条件 1then
执行sql1
else if 条件2
执行sql2
elseif条件3
执行sql语句3
else
执行语句4
endif ;
end if ;
endif ;
======================================
页:
[1]