mysql--视图,索引,外键,存储过程。
本帖最后由 杭州10期_戴孙宝 于 2021-8-16 19:37 编辑视 图
一、视图定义:
视图是一个虚拟表(逻辑表),它 不在数据库中以存储数据的形式保存(本身不包含数据),还是在使用视图的时候动态生成。
二、视图的作用:
1、数据库中的数据查询非常复杂,例如多表、子查询。编写语句较多、并且这种查询常被重复使用,那么我们就可以创建视图,避免每次谢sql语句会错误,页提高了查询的效率。
2、为了安全,在公司中,有些字段为保密字段,那么可以创建视图,限制对某些字段的操作。
三、视图的优缺点?
优点:
1、提高查询效率
在数据库中非常复杂的数据,可以通过创建视图避免多次编写sql语句出现错误
2、安全
公司有些保密字段,可以通过创建视图来限制用户对某些字段进行操作
3、简单
不需要关系后面对应的表结构
缺点:
1、性能差
把视图查询结构转换成对表的查询
2、修改限制
修改视图数据,必须把它转化为对基础表的修改
脱敏测试(就叫加密测试)
通常情况下,数据库就是敏感的,(脱敏测试)不是每一个用户都能进行访问,但是某些用户需要读取表中的部分数据,这是就可以指定条件创建视图来限制用户只能访问指定的数据,并提供一个视图接口进行访问。
=============================================================================
视图的使用
[*]show tables ; 显示数据库中的表(先查看数据库中的基本表)
[*]创建视图;
格式: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、索引的作用:
[*]保证数据记录的唯一性
[*]实现表与表之间的参照完整性
[*]减少排序和分组的时间(例如在使用order by,groupby 查询语句进行数检索时)
[*]可以利用索引快速访问数据库中特定的信息(重点)
3、索引的缺点:
[*]索引要占物理内存
[*]对表进行增删改查,索引也要动态维护,降低数据的维护速度
4、索引的分类:
[*]普通索引
index mul(缩写) 最基本的索引,没有任何的限制
2. 主键索引
primary key pri (缩写) 是一种唯一索引,不能为空
3.唯一索引
unique uin (缩写) 是一种唯一索引,可为空
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.索引的作用
4.创建表前,建表后
5.主键索引一个表就一个,唯一索引一个表可以有多个
删除方法也不一样
====================================================
外 键
一、外键的介绍
1、外键的定义:
让一张表记录的数据不要太过于冗余,在数据库中对表的关系进行解耦,尽量让表的数据单一化。
2、外键的作用
外键的作用:保持数据的一致性和完整性
3、mysql数据库中存储引擎?
innodb(外键要存储使用innodb存储引擎)
mysiam(默认)
4、查看存储引擎
格式:showtablestatusfrom 库名where name="表名";
案例:showtablestatusfrom hz10where name="emp";
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjq3-2KvgG3w0cSuxO9Yi7s0\c44d2944757445a6a9a60727066790c0\f568fc86cc77.png
===============================
建表语句:
(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)
=============================================================
查看外键:
方法一:
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjq3-2KvgG3w0cSuxO9Yi7s0\1c28f6c3ce7e4b5196d0e38669bfe340\5e8e83dc72ea.png
方法二:
格式:showcreatetable 表名 ;
showcreatetablecc ;
、
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjq3-2KvgG3w0cSuxO9Yi7s0\976f8be838214bfcad679287d5d28da7\e441916786a9.png
===============================
删除外键:
格式: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")
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjq3-2KvgG3w0cSuxO9Yi7s0\a38a9434e0e24278872761785606a7be\6e559b3ffa9b.png
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)
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjq3-2KvgG3w0cSuxO9Yi7s0\aa1c9e2b5fd34ac9a3ae2a271f1fc5e8\b0598e1e7291.png
====================================================================
总结:
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.什么是存储过程?
存储过程是实现某个特定功能的sql语句的集合,编译后的存储过程会保存在数据库中,通过存储过程的名称可以反复的调用执行。
2.存储过程的优点?
1、存储创建后,就可以反复非调用和使用,不需要重新写复杂的sql语句
2、创建、修改存储过程通过不会对数据有任何的影响
3、存储过程可以通过参数和返回值
4、存储过程可以通过加入控制语句,加强sql语句的功能性和灵活性
5、对于的那个的增删改查语句,可以直接封装在一个函数当中,或者封装在一个集合当中,存储过程一旦创建就可以直接调取用,而且可以重复调用
6、单个sql语句每次执行都要数据库进行编辑,而存储过程创建只需要编译一次,后续就可调用
7、创建存储过程,就可以重复进行调用,可以减少开发人员的工作量。
8、防止sql注入。
9、造数据。
======================================================
二、存储的基本格式: (例子)
delimiter //#分隔符/定格符
create procedure hz1()
BEGIN#开始
select* from emp ;
select*fromdept ;#执行语句块
end #结束
// #分隔符
先执行创建存储,在调用使用call
call存储名() 调用存储
===============================================================
基本的使用语句
1.查看单个存储过程的详情
格式:showcreate procedure存储名;
showcreate procedurehz1 ;
2、查看所有已经创建的存储过程
格式:showPROCEDURESTATUS ;
showPROCEDURESTATUS ;
3、查询数据库创建的存储过程
格式:showPROCEDURESTATUS wheredb="数据库名" ;
showPROCEDURESTATUS wheredb="hz10" ;
4、删除存储
格式:drop procedure 存储名称;
drop procedure 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]