视图:
一、什么是视图?
视图是一个虚拟表(逻辑表),它不在数据库中以存储数据的形式保存(本身不包含数据),还是在使用视图的是时候动态生成。
二、视图的作用?
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 where dept1=dept2)
(3)查看创建的视图 (包含建表语句,视图名,编码格式)
格式:show create view 视图名;
案例:show create view hz ;
(4)删除视图
格式:drop view 视图名
案例:drop view hz;
(5)查看视图所有内容
格式:select * from 视图名 ;
案例:select * from hz ;
(6)创建视图以后,就不需要重复合表,语句也简单
hz:视图就是合表,可以简化查询语句
案例:
视图:create view hz as (select * from emp ,dept where dept1=dept2)
select * from hz where name="张三"
==============================================
视图的特点:
1、视图是由基本表产生的虚拟表
2、视图的创建和删除不影响基础表
3、视图的数据更新和删除数据直接影响基础表(视图是单表情况下可删除,多表下报错)
4、视图内容来源于多个虚拟表结构不能删除
5、视图不能修改表字段
6、基础表修改数据,视图也修改
7、基础表修改字段,视图报错
==============================================
面试题:
1、数据库会哪些?
数据库的增删改查 ,视图
2、讲下什么视图?
3、视图的作用?
索引
一、索引的介绍
1、什么索引?(重点:索引是一种数据结构)
一个索引在存储的表中的数据结构,
索引是在表的字段字上创建。
索引包含了一个列的值,这个值保存在一个数据结构中
2、索引的作用:
(1)保证数据记录的唯一性
(2)实现表与表之间的参照完整性
(3)减少排序和分组的时间 (例如在使用 order by ,group by 查询语句进行数据检索时)
(4)可以利用索引快速访问数据库中特定信息(重点)
3、索引的缺点:
(1)索引要占物理内存
(2) 对表进行增删改查 ,索引也要动态维护,降低数据的维护速度
4、索引的分类:
(1)普通索引
index mul 最基本的索引 ,没有任何限制
(2)主键索引
primary key pri 是一种唯一索引,不能为空
(3)唯一索引
unique uni 是一种唯一索引,可为空
(4)全文索引
(5)组合索引
(6)单列索引
(7)聚焦索引
(8)非聚焦索引
5、索引的使用
(1)索引查询方法
1.1方法一:
格式:show INDEX from 表名 ;
show INDEX from emp ;
1.2格式:show keys FROM 表名 ;
gshow keys FROM emp ;
(2) 查看表结构
格式:desc 表名 案例:desc emp
(3)创建索引:
3.1建表后建立索引
3.1.1建立普通索引 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);
3.1.2创建唯一索引 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)
3.1.3 创建主键索引 primary key 简写(pri) 唯一,不能为空 ,主键索引一个表中只有一个
格式1:ALTER table 表名 add primary key (字段名)
案例1:ALTER table emp add primary key (sid)
3.2 create 方法创建索引
格式:create index 索引名 on 表名(字段名)
案例:create index jj on emp(dept2)
3.3新建表时建立索引
案例: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" ;
===============================
建表语句:
(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;
创建 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)
===============================
查看外键:
方法一:
格式:show create table 表名 ;
show create table cc ;
===============================
删除外键:
格式: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")
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)
===============================
总结:
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)什么是存储过程?
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)查看单个存储过程的项详情
格式:show create procedure 存储名;
show create procedure hz1 ;
(2)查看所有已经创建的存储过程详情
格式:show PROCEDURE STATUS ;
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 存储过程名(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)
第三种造数:( 在储存中造表,在造数据,根据输入的数据造数)
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 ;
======================================
|
|