Mysql__视图:
1、是什么?
视图是一个虚拟表(逻辑表),它不在数据库中以存储的形式保存(本身不包含数据),还是在使用视图的时候动态生成。
2、有什么用?
- 数据库中的数据查询是非常复杂的,例如多表、子查询、编写语句较多、并且这种查询常被重复使用,那么我们就可以创建视图,避免每次写sql语句会错误,也提高了查询的效率;
- 为了安全,在公司中,有些字段为保密字段,那么可以创建视图,限制对某些字段的操作。
3、视图的优缺点:
adv.
- 提高查询效率:在数据库中非常复杂的数据,可以通过创建视图避免多次编写sql语句出现的错误
- 安全:公司中有些保密的字段,可以通过创建视图来限制用户对某些字段进行操作
- 简单:不需要关系后面对应的表结构
disadv.
- 性能差:把试图查询结果转换成对表的查询
- 修改限制:修改视图数据,必须把它转化为对基础表的修改
脱敏测试(又称加密测试)
通常情况下,数据库是敏感的,(脱敏测试)不是每一个用户都能进行访问,但是有些用户需要读取表中的部分数据,这时就可以指定条件创建视图来限制用户只能访问指定的数据,并提供一个视图接口进行访问。
===============================================================================
视图的使用:
- show tables ;显示所有的表(先查看数据库中的基础表)
- 创建视图:
格式:create view 视图名 as (sql语句)
案例:create view hz as (select * from emp ,dept where dept1=dept2)
格式:show create view 视图名
案例:show create view hz ;
格式:drop view 视图名;
案例:drop view hz;
格式:select * from 视图名;
案例:select * from hz;
hz:视图即合表,可以简化查询语句
案例:
视图:create view hz as (select * from emp , dept where dept1=dept2 )
select * from hz where name="张三"
===============================================================================
视图的特点:
- 视图是由基本表产生的虚拟表
- 视图的创建和删除不影响基础表
- 视图的数据更新和删除数据直接影响基础表
- 视图内容来源于多个虚拟表结构不能删除(视图是单表情况下可删除,多表下报错)
- 视图不能修改表字段
- 基础表修改字段,试图也修改
- 基础表修改字段,试图报错(不是基础表)
===============================================================================
Mysql之索引
一、索引的介绍:
1、什么是索引?(重点:索引是一种数据结构)
一个索引在存储的表中的数据结构,索引是在表的字段字上创建,
- 索引包含了一个列的值,这个值保存在一个数据结构中。
2、索引的作用?
- 保证数据记录的唯一性;
- 实现表与表之间的参照完整性;
- 减少排序和分组的时间(例如在使用 order by , group by 查询语句进行数据检索时);
- 可以利用索引快速访问数据库中特定的信息(重点)
3、索引的缺点;
- 索引要占用物理内存
- 对表进行增删改查时,索引也要动态维护,降低数据的维护速度
4、索引的分类
(1)普通索引
index mul 最基本的索引,没有任何限制
(2)主键索引
primary key pri 是一种唯一索引,不能为空
(3)唯一索引
unique uni 是一种唯一索引,可以为空
(4)全文索引
(5)组合索引
(6)单例索引
(7)聚焦索引
(8)非聚焦索引
5、索引的使用
(1)索引的查询方法
格式:show index from 表名 ;
案例:show index from emp ;
格式:show keys from 表名 ;
案例:show keys from emp ;
(2)查看表结构:
格式:desc 表名
案例:desc emp
(3)创建索引:
①建立普通索引 index mul(简写)
格式1:alter table 表名 add index 索引名(字段名);
案例1:alter table emp add index aa(age)
备注:索引名和字段名不同
格式2:alter table 表名 add index (字段名);
案例2:alter table emp add index (name);
备注:索引名和字段名相同
②创建唯一索引 unique 简写(uni)
备注:索引名和字段名不同
格式1:alter table 表名 add unique 索引名(字段名)
案例1:ALTER table emp add unique hzsid(sid)
备注:索引名和字段名相同
格式2:alter table 表名 add unique (字段名)
案例2:alter table emp add unique (sid)
③创建主键索引 primary key 简写( pri )唯一,不能为空,主键索引一个表只有一个
格式:ALTER table 表名 add primary key (字段名)
案例:ALTER table emp add primary key (sid)
格式:create index 索引名 on 表名(字段名)
案例:create index jj on emp(dept2)
案例:
create table hz1010(id int(10) PRIMARY key , name varchar(10), carno int(30) unique )
===============================================================================
===============================================================================
删除索引:
方法一:(仅能删除普通索引和唯一索引)
格式 :ALTER table 表名 drop index 索引名;
案例:ALTER table emp drop index name ;
方法二:(只删除主键索引)
格式:ALTER table 表名 drop PRIMARY key;
案例:ALTER table emp drop PRIMARY key;
===============================================================================
面试题:
面试题;
1、你熟悉数据库语句吗?
2、你说下索引有哪些?
3、索引主要用来做什么?
4、创建索引的时间?
5、主键索引和唯一索引的区别?
===============================================================================
外键
一、外键的介绍
1、外键的定义:让一张表记录的数据不要太过于冗余,在数据库中对表的关系进行解耦,尽量让表的数据单一化。
2、外键的作用:保持数据的一致性和完整性
3、mysql数据库中的存储引擎?
innodb (外键要使用innodb存储引擎)
myisam (默认)
4、查看存储引擎
格式:show table status from 库名 where name="表名" ;
案例:show table status from hz10 where name="emp" ;
file:///E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/5f89601b17774174af305b2bc4636b75/%28s@to%5Dhggndh_v%24c8%25kwtux.png
===============================================================================
建表语句:
(1)通过建表语句来创建主键
mm表 nn表
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), constraint fk_wj foreign key(sid) references mm(id))engine=innodb ; (子表)
constraint 外键名 指定外键
foreign key (子表)指定字段
REFERENCES 父表(父表字段) 引用外部表的主键
(2)直接创建键(表先建好)
创建 ss 表: (父表)
create table ss (id int(10) primary key , name varchar(10))ENGINE=innodb;
创建 cc 表:(子表)
create table cc (sid int(10) primary key , sname varchar(10))ENGINE=innodb
格式:alter table 子表 add CONSTRAINT 外键名 foreign key (子表字段名) REFERENCES 父表(父表的字段名)
案例:alter table cc add CONSTRAINT ss_key foreign key (sid) REFERENCES ss(id)
===============================================================================
查看外键:
方法一:
file:///E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/f215694e24ff43a2aae0f68187c5c851/5e8e83dc72ea.png
方法二:
格式:show create table 表名 ;
show create table cc ;
file:///E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/dbaf0131106c4fbeb76922f35502bbc7/e441916786a9.png
===============================
删除外键:
格式:alter table 表名 drop foreign key 外键名
案例:alter table cc drop foreign key ss_key ;
===============================
一、子表和父表之间关系:
1、在子表插入数据,父表不存在的数据无法插入(报错)
案例:INSERT into cc 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、在子表插入数据,父表存在的数据才能插入
案例 :
INSERT into ss VALUES (1,"wq") ; 先插入父表
INSERT into cc VALUES (1,"aa")
file:///E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/4a176a9233c64b049e6d4162202fcf8d/6e559b3ffa9b.png
3、父表和子表都为空数据,子表无法插入数据,父表可以插入数据
4、存在外键时:父表删除数据,需要先删除子表数据
4.1子表和父表数据都存在,直接删除父表,报错,无法删除
案例:DELETE from ss 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 from cc where sid=1 ; 先删除子表数据
DELETE from ss where id=1 ; 在删除父表数据
4、主键名可以修改
alter table cc add CONSTRAINT ss foreign key (sid) REFERENCES ss(id)
file:///E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/412073b572bd4a0c9823c95cea7b3bc3/b0598e1e7291.png
====================================================================
总结:
1、如何创建外键?
两种方法:1、建表时创建 父表 在子表设置外键
格式:create table 表名(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、存储创建后,就可以反复的调用和使用,不需要重新写复杂的 sql 语句;
2、创建、修改存储过程不会对数据有任何的影响;
3、存储过程可以通过参数和返回值;
4、存储过程可以通过加入控制语句,加强 sql 语句的功能性和灵活性;
5、对于单个的增删改查语句可以直接封装在一个函数体当中,或者封装在一个集合当中,存储过程一旦创建就可以直接调用,而且可以重复调用;
6、单个 sql 语句每次执行都需要对数据库进行编译,而存储过程被创建只需要编译一次,后续就可调用;
7、创建存储过程,可以重复进行调用,可以减少开发人员的工作量;
8、防止 sql 注入;
9、造数据。
===============================================================================
二、存储的基本格式
delimiter // #分隔符/定格符
create procedure 存储名称( in或out或inout)
BEGIN #开始
select * from emp ;
select * from dept ; #执行语句块
end #结束
// #分隔符
先执行创建,再调用使用 call
call 存储名() 调用存储
===============================================================================
基本使用的语句:
(1)查看单个存储过程的详情
格式:show create procedure 存储名 ;
案例:show create procedure hz1 ;
(2)查看所有已经创建的存储过程详情
格式:show procedure status ;
(3)查看数据库创建的存储过程
格式; show proceduer status where db="数据库名" ;
案例: show proceduer status where db="hz10" ;
(4)删除存储
格式:drop procedure 存储名称 ;
案例:drop procedure hz10;
===============================================================================
存储过程的编写
(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 存储过程名(103)
案例:
delimiter //
create procedure bb( in x int)
BEGIN
select * from emp where dept2=x;
END
//
call bb(103)
(3)out 输出参数
格式:
delimiter //
create procedure ee( out y int )
BEGIN
select sid into y from emp where name="sunbao";
END
//
call ee(@y)
select @y
(4)in ,out
案例
delimiter //
create procedure ff( out y int ,in x int)
BEGIN
select age into y from emp where sid=x;
END
//
call ff(@y,1789)
select @y
(5)inout 输入和输出
delimiter //
create procedure hh( inout n int)
BEGIN
set n:=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 i int default 0 #declare 声明变量 ,i i 变量 数据类型 int 默认值
============================================
(1)第一种造数 在存储外先建表,在造数据,写死的数据)
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()
(2)第二种造数 在存储外先建表,在造数据,根据输入的数据造数)
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(1000)
(3)第三种造数:( 在储存中造表,再造数据,根据输入的数据造数)
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语句的单分支:
if 条件 then
执行sql1
else
执行sql2
end if ;
======================================
if判断语句
if语句的多分支:
if 条件 1 then
执行sql1
else if 条件2
执行sql2
else if 条件3
执行sql语句3
else
执行语句4
end if ;
end if ;
end if ;
======================================
|
|