视图
一,什么是视图
定义:视图是一个虚拟表(逻辑表),它不在数据库中以存储数据库的形式保存(本身不包含数据),还是在使用视图的时候动态生成。
二,视图的作用
1数据库中的数据的查询非常的复杂,例如多表,子查询,编写语句较多,并且这种查询常被重复使用,那么我们就可以创建视图,避免每次写sql语句会错误,也提高了查询的效率。
2为了安全,在公司中有些字段为保密字段,那么可以创建视图,限制对某些字段的操作。
三、视图的优缺点
优点
1、提高查询效率
在数据库中非常复杂的数据,可以通过创建视图避免多次编写sql语句出现的错误
2、安全
公司有些保密字段,可以通过创建视图来限制用户对某些字段进行操作
3、简单
不需要关系后面对应的表结构(不影响表结构)
缺点
1性能差
把视图查询结果转换成对表的查询
2、限制修改
修改视图数据,必须把它转化为对基础表的修改
脱敏测试(加密测试)
通常情况下,数据库是敏感,(脱敏测试)不是每一个用户都能进行访问,但是某些用户需要读取表中的部分数据,这时就可以指定条件创建视图来限制用户只能访问指定的数据,并提供一个视图接口进行访问。
============================================================================
视图的使用:
(1)show tables; 查看所有表(先查看数据库中的基本表)
C:/Users/Administrator/AppData/Local/YNote/data/weixinobU7Vjp8N9B-UYSDrAgFp2cOyS1Q/f4459e4bf68b4b31a5f1617d291735da/m%7Bs1oe%24z1gg%5D%252a%7Et2%29w3w1.png
(2)创建视图
格式:create view 视图名 as(sql语句);
案例:create view hz as(select * from emp2,dept2 where dept2.dept1=emp2.dept2);
create view hz1 as(select * from emp2,dept2 where dept2.dept1=emp2.dept2 and dept1=101);
(3)查看创建的视图(包含建表语句,视图名,编码格式)
格式:SHOW create view 视图名;
案例:SHOW create view hz;
C:/Users/Administrator/AppData/Local/YNote/data/weixinobU7Vjp8N9B-UYSDrAgFp2cOyS1Q/4fe151830b75459a9285441899c50d0d/d4d38811100b4c4db2cb39822e1f3c0c.jpg
(4)删除视图
格式:drop view 视图名;
案例:drop view hz1;
(5)查看视图所有内容
格式:select * from 视图名;
案例:select * from hz;
C:/Users/Administrator/AppData/Local/YNote/data/weixinobU7Vjp8N9B-UYSDrAgFp2cOyS1Q/d0b2f8e516ad4484907f3a3bf89dae9f/927a5616bee1436ba8c0d873f18806aa.jpg
(6)创建视图后,就不需要重复合表,语句也简单
hz:视图就是合表,可以简化查询语句
案例:create view hz as(select * from emp2,dept2 where dept2.dept1=emp2.dept2);
select * from hz where name="张三";
C:/Users/Administrator/AppData/Local/YNote/data/weixinobU7Vjp8N9B-UYSDrAgFp2cOyS1Q/f52e5616dfa546c7ba167b30a74c2545/c4a433cc7ac54211845a3bb61c7a62e7.jpg
===========================================================================
视图的特点
1视图是由基本表产生虚拟表
2视图的创建和删除不影响基础
3视图的更新和删除数据直接影响基础表(视图是单表情况下可删除,多表下报错)
4视图内容来源于多个虚拟表结构,不能删除
5视图不能修改表字段
6基础表修改数据,视图也修改
7基础表修改字段,视图不显示 报错
update hz set name="孙宝" where name="张三";
delete from hz where name="孙宝";(无法删除,因为多表连接,涉及两个表的数据,视图是单表可以删除)
C:/Users/Administrator/AppData/Local/YNote/data/weixinobU7Vjp8N9B-UYSDrAgFp2cOyS1Q/58decc2e4f6f40b1aeb449f54c28dd2f/7%28hp_y%7Dxe%5D515%7Bt%28n%7Dez%28fa.png
===========================================================================
面试题
1数据库会哪些?
数据库的增删改查
2讲下什么是视图?
3视图的作用
============================================================================
mysql 之索引
索引
一,索引介绍(重点:索引是一种数据结构)
1什么是索引
一个索引在储存的表中的数据结构
索引是在表的字段上创建
索引包含了一个列的值,这个值保存在一个数据结构中
2索引的作用
(1)保证数据记录的唯一性
实现表与表之间的参照完整性
减少排序和分组的时间(例如在使用 order by 查询语句 进行数据检索时)
可以利用索引快速访问数据库中的特定信息(重点)
3、索引的缺点
(1)索引要占物理内存
(2)对表进行曾删改查,索引也要动态维护,降低数据的速度
4、索引的分类
(1)普通索引
index 简写:mul 最基本的索引,没有任何限制
(2)主键索引
primary key pri 是一种唯一索引,不能为空
(3)唯一索引
unique uni 是一种唯一索引,可为空
(4)全文索引
(5)组合索引
(6)单列索引
(7)聚焦索引
(8)非聚焦索引
5、索引的使用
(1)索引查询方法
1.1方法1(常见)
格式:show index from 表名
案例:SHOW index from emp2;
1.2方法2
show keys from emp2;
(2)查看表结构
格式:desc 表名
案例:desc emp2
(3)创建索引
3.1建表后建立索引
3.1.1建立普通索引 index mul(简写)
备注:索引名和字段名不同
格式1:alter table 表名 add index 索引(字段名)
案例:alter table emp2 add index aa(age)
C:/Users/Administrator/AppData/Local/YNote/data/weixinobU7Vjp8N9B-UYSDrAgFp2cOyS1Q/67b1963a9daa4d588d01b45dda9cf804/xa1n%7Erd%24%7Bct%28ysuof6%28zq5w.png
备注:索引名和字段名相同
格式2:alter table 表名 add index (字段名)
案例:alter table emp2 add index (name)
C:/Users/Administrator/AppData/Local/YNote/data/weixinobU7Vjp8N9B-UYSDrAgFp2cOyS1Q/b4f04c6402a94dafa1d51deeb6bafc36/cq44%5Bzz%28l54q0kle%7E%28u11aj.png
3.1.2创建唯一索引 unique 简写 唯一索引 可以为空 一个表中有多个唯一索引
备注:索引名和字段名不同
格式:alter table 表名 add unique 索引名 (字段名)
案例1:
备注:索引名和字段名相同
格式:alter table 表名 add unique (字段名)
alter table emp2 add unique (sid)
3.1.3创建主键索引 primary key 简写pri 唯一不能为空 主键索引一个表中只有一个
格式1:alter table 表名 add primary key (字段名)
案例1:alter table emp2 add primary key (sid)
3.2create方法创建索引
格式:creat table 索引名 on 表名(字段名)
案例:creat table jj on emp2(dept2)
3.3新建表时建立索引
create table hz1010(id int(10)primary key,
======================================================================
删除索引
方法1:删除普通索引和唯一索引
格式:alter table 表名 drop index 索引名;
C:/Users/Administrator/AppData/Local/YNote/data/weixinobU7Vjp8N9B-UYSDrAgFp2cOyS1Q/aa784192573e4ee19ff6289ec45b2869/dt5%294m_736ux@%60ep@v%258q89.png
方法2:删除主键索引:
格式:alter table 表名 drop primary key
============================================================================面试题
1你熟悉数据库语句吗
增删改查,索引搜索
索引有哪些
唯一,普通,主键
1你说下索引有什么作用
加快查询速度,减少时间(order by)等
创建索引时间
建表前建表后
主键索引和唯一索引区别
一个,多个
删除方法
=========================================================================
外键
1定义:
让一张表记录的数据不要太过冗余,在数据库中对表的关系进行解,尽量让表的数据单一简化
2作用:
保持数据的一致性和完整性
3mysql数据库中的存储引擎:
innodb(外键要使用innodb存储引擎)
myisam(默认)
4查看存储引擎
格式:show table status from 库名 where name="表名";
案例:show table status from hz10 where name="emp2";
C:/Users/Administrator/AppData/Local/YNote/data/weixinobU7Vjp8N9B-UYSDrAgFp2cOyS1Q/2f3f493722f54fc48ee60df99666a47c/%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(20) ,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;
创建表(子表)
create table cc(sid int(10)primary key,name varchar(10))engine=innodb;
创建外检
alter table 子表 add CONSTRAINT 外键名 foreign key (子表字段) REFERENCES 父表(父表字段名)
alter table cc add CONSTRAINT ss_key foreign key (sid) REFERENCES ss(id)
查看外键的方法
方法1
C:/Users/Administrator/AppData/Local/YNote/data/weixinobU7Vjp8N9B-UYSDrAgFp2cOyS1Q/675760f09cc24e298f9f8f6af612c4ce/%2512v5%7Dc%5Bljrh2y@%5B%29xxmu%5Bg.png
方法2
show create table cc;
C:/Users/Administrator/AppData/Local/YNote/data/weixinobU7Vjp8N9B-UYSDrAgFp2cOyS1Q/19d1989d5a8845c5bead917c7d066790/6528c83a3e944c5f9300187eda5004c2.jpg
=========================================================================
删除外键
格式:alter table 表名 drop foreign key 外键名
案例:alter table cc drop foreign key ss_key;
==========================================================================
一、子表和父表之间的关系
1在子表插入,父表不存在的数据时,无法插入(报错)
案例:insert into cc values(1,"aa");
报错:
[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`hz10`.<result 2 when explaining filename '#sql-af3_7'>, CONSTRAINT `ss_key` FOREIGN KEY (`id`) REFERENCES `ss` (`id`))
2在子表插入,父表存在的数据时,才能插入
insert into ss values(1,"aa"); 先插入父表
insert into cc values(2,"wq"); 才可以插入子表
3父表和子表都为空时,子表无法插入数据,父表可以插入
4父表删除数据,需要先删除子表数据
4.1子表和父表数据都存在,直接删除父表,报错,无法1删除
案例:delete from ss where id=1;直接删除父表
报错:
4.2子表和父表都存在,直接先删除子表,再删除父表
delete from where id=1;
delete from where id=1;
5修改主键名
alter table cc add constraint ss foreign key(sid)references ss(id)
=======================================================================
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 #开始
执行语句块1
执行语句块2
end #结束
// #分隔符
先执行创建储存,再调用使用call
call 储存名 () #调用存储过程
例:delimiter // #分隔符/定格符
create procedure hz1()
BEGIN #开始
select * from emp ;
select * from dept ; #执行语句块
end #结束
// #分隔符
=====================================================================
基本使用语句:
(1)查看单个存储过程的详情
格式:show creat 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 aa()
begin
select * from emp2;
end
//
call aa()
(2)in 输入参数的存储
格式:
案例:delimiter //
create procedure bb(in x int)
begin
select * from emp2 where dept2=x;
end
//
call bb(103)
(3)out 输出参数
格式:
案例:delimiter //
create procedure ee(out y int)
begin
select sid into y from emp2 where name="张三";
end
//
call ee(@y) @y设置变量
select @y
(4)in,out
案例:delimiter //
create procedure ff(out y int,in x int)
begin
select age into y from emp2 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 先执行(传入2)
call hh(@n)
select @n
===========================================================================
1用户变量 定义语法
set @变量名
赋值语法:
(1)set @变量名:=值
(2)set @变量名=值
(3)select @变量名:=值;
方法2:通过查询条件结果为变量赋值
select 字段|表达式 into 变量名 from 表名 where 条件
2 声明变量
declare i int default 0 #declare 声明变量,i 变量 int数据类型 declare 0 默认值
=======================================================================
(1)第一种造数 (先建表,再造数据,写死的数据)
create table abc(id int(10));
delimiter //
drop procedure if exists hh; #增强存储的健壮性,判断存在删除
create procedure hh()
begin
declare i int default o; #声明一个变量, 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 ;
======================================
|
|