视图，索引，外键，存储

索引

一、索引的介绍

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 表名；

show keys from emp；

（2）查看表结构

格式：desc 表名

案例：desc  emp

（3）创建索引：

3.1建表后建立索引：

3.1.1建立普通索引   index mul(简写)

备注（索引名和字段名不同）

file:///C:\Users\admin\Documents\Tencent Files\673263061\Image\Group2\XA\1N\XA1N~RD%24%7BCT(YSUOF6(ZQ5W.png

格式:;alter table表名add index索引名(字段名）;

案例: alter table emp add index aa(age);

file:///C:\Users\admin\Documents\Tencent Files\673263061\Image\Group2\CQ\44\CQ44%5BZZ(L54Q0KLE~(U11AJ.png

备注（索引名和字段名相同)

格式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新建表时建立索引：

=================================

删除索引：

方法一：（只能删除普通索引和唯一索引）

格式:ALTER table 表名drop index索引名;

案例:ALTER tableemp drop index name;

file:///C:\Users\admin\Documents\Tencent Files\673263061\Image\Group2\DT\5)\DT5)4M_736UX%40%60EP%40V%258Q89.png

方法二：（只删除主键索引）

格式:ALTER table表名drop PRIMARY key;

ALTER table emp drop PRIMARY key;

3.2 create方法创建索引

格式: create index索引名on表名(字段名)

案例: create index jj on emp(dept2)

=============================

面试题：

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:///C:\Users\admin\Documents\Tencent Files\673263061\Image\Group2\(S\@T\(S%40TO%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;

创建cc表子表)

create table cc (sid int(10) primary key , sname varchar(10))ENGINE=innodb

格式：

案例：alter  table cc add  CONSTRAINT  ss_key  foreign  key (sid) REFERENCES ss(id)

方法一：

file:///C:\Users\admin\Documents\Tencent Files\673263061\Image\Group2\%1\2V\%2512V5%7DC%5BLJRH2Y%40%5B)XXMU%5BG.png

方法二：

格式：show create table  表名；

show create tables cc；

==========================================

删除外键：

格式：alter  table  表名  drop   foreign  key     外键名   

案例：alter  table  cc drop  foreign key  ss_key ;

=========================================================

一、子表和父表之间关系：

1、在子表插入数据，父表不存在的数据无法插入

案例：

报错(父表不存在的数据，子表无法插入)

Cannot add or update a child row. a foreign key constraint fails ( hz10 . cc , CONSTRAINT `'ss_key FOREIG KEY (sid`)REFERENCES 'ss` (id`))

2、在子表插入数据，父表存在的数据才能插入

3、父表和子表都为空数据，子表无法插入数据，父表可以插入数据

4、存在外键时：父表删除数据，需要先删除子表数据

4.1子表和父表数据都能存在，直接删除父表，报错，无法删除（直接先删除子表，再删除父表数据）

DELETE from cc where sid=1;先删除子表数据

DELETE from ss 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

父表(父表字段)

二、父表和子表的关系？

(1)当父表不存在的数据，子表无法插入

(2）当两表为空时，子表无法插入数据，父表可插入

(3）当父表删除数据，需要先删除子表数据

3、公司为什么用外键?

(1）保存数据的一致性和完整性

(2)公司数据量大，造成数据重复，新旧表维护成本大，使用外键让公司数据进行分类和管理，让基础数据放在主表上，子表单一化，这样避免数据的冗余

多测师肖sir___软件测试___mysql之外键_wx6039f25744874的技术博客_51CTO博客

外键笔记

====================================================

存储过程

一、存储的介绍

（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语句块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 @

（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 @

===============================================================

1、用户变量 定义语法：

set @变量名

赋值的语法：

（1）set @变量名：=值

（2）set @变量名=值

（3）select @变量名：=值；’

方法二：通过查询结果为变量赋值

select  字段|表达式  into 变量名 from  表名  where  条件

2、declare  i  int   default  #declare  声明变量  ii变量 数型类型  int  默认值

=================================================================

第一种造数

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

执行sql2end if ;

if语句的多分支:if 条件1 then

执行sql1else if条件2执行sql2else if 条件3

执行sql语句3

else

执行语句4

end if;

end if ;

end if