MySQL（索引、外键、存储）

索引

一、索引的介绍

1、什么是索引？

（1）定义：索引是一种数据结构

一个索引在存储的表中约束数据结构；

（2）索引是在表的字段上创建的

（3）索引包含了一列值，这个值保存在一个数据结构中

2、索引作用？

（1）保证数据记录的唯一性

（2）实现表与表之间的参照性

（3）减少排序和分组的时间（例如在使用order by ，group  by  查询语句中进行数据检索）

（4）可以使用索引快速访问数据库中指定信息

3、索引的缺点？

（1）索引要占物理内存

（2）索引对表进行增删改查，索引要动态维护，降低数据的维护速度

4、索引的分类

（1）普通索引

index    简称        mul     最基本的索引，没有任何限制

（2）主键索引

primary key   简称 pri   是一种唯一索引，不能为空

（3）唯一索引

unique    简称  uni     是一种唯一索引，可为空，一个表中可以有多个唯一索引

5、详解讲解

（1）查看索引

方法1:

show INDEX  from  student ;

方法2：

show keys from  student ;

（2）通过查看表结构，是否有索引

desc  student ;

（3）创建普通索引

a、创建普通索引 的简写是mul

索引名和字段名不一样

格式：

alter  table 表名  add INDEX  索引名(字段名)

案例：alter  table student  add INDEX  sy(stu_name)

b、创建索引索引名和字段一致

格式：

alter  table 表名  add INDEX  (字段名)

案例：alter  table student  add INDEX  (stu_name)

（4）删除普通索引

格式：

ALTER  table  表名  drop  index  索引名;

案例：ALTER  table 

 student  drop  index  sy;

（5）、创建主键索引

主键简写：pri

格式：ALTER  table 表名  add  PRIMARY key(字段名)

案例：ALTER  table student  add  PRIMARY key(stu_no)

（6）、删除主键索引

格式：

ALTER  table  表名  drop  PRIMARY key

案例

ALTER  table  student  drop  PRIMARY key

（7）、添加唯一索引

unique 唯一

唯一索引简写：uni

添加唯一索引：索引名和字段名不一致

格式：

ALTER  table  表名   add unique  索引名(字段名)

案例：ALTER  table  student   add unique  yy(age)

添加唯一索引：索引名和字段名一致

ALTER  table  student   add unique  (edit)

6、创建表的时候创建索引

create  table student1 (id  int(10) PRIMARY key  ,name  varchar(20) UNIQUE ,fs int(20))

外键

一、外键的介绍

1、外键的定义

让一张表记录的数据不要太过于冗余，在数据库中对表的关系进行解耦，尽量让表的数据单一化。
 

2、外键的作用

保持数据的一致性和完整性

3、msyql  数据库中的存储引擎？

myisam  （默认）

innodb  （外键需要用到innodb存储格式）
 

4、查看存储引擎

格式：show  table   status    from  库名  where  name='表名' ;

 案例：show  table   status    from  hh where  name='student' 

   5、存储类型：myisam  （默认）
 

 6、查看外键方法：

（1）在navicat中查看
 

（2）查看外键

 

 

 

 

二、外键运用

（一）建表时创建外键

（1）创建  engine=INNODB  格式

格式：

CREATE table  表名  (字段名   字段类型(字符长度) PRIMARY key ,  字段名      字符类型(字符长度) ,constraint   外键名   FOREIGN key(子表字段)

REFERENCES    父表(父表字段)) engine=INNODB ;

案例：

#父表

create table ss (id int(10) PRIMARY key, name varchar(20) )ENGINE=INNODB ;

#子表

CREATE table cc (cid int(10) PRIMARY key ,cname VARCHAR(20) ,constraint wj FOREIGN key(cid)

REFERENCES ss(id)) engine=INNODB ;

constraint     外键 名         （指定外键名）

FOREIGN key   子表字段       指定的字段

 REFERENCES     父表（父表字段）    引用外部表的主键

（二）建表以后再添加外键

格式：

alter TABLE 子表   add CONSTRAINT   外建名    FOREIGN key (子表字段)    REFERENCES   父表(父表字段)

（1）新建两个表

#父表

create table xx (id int(10) PRIMARY key ,name varchar(20))engine=INNODB ;

#子表

create table yy (yid int(10) PRIMARY key ,yame varchar(20))engine=INNODB ;

alter TABLE yy add CONSTRAINT wjm FOREIGN key (yid) REFERENCES xx(id)

 

格式：show   create    table   表名；

案例：show    create   table    cc

（三）删除外键  

格式:

ALTER TABLE    表名  drop   FOREIGN key   外键名;

案例：

ALTER TABLE    yy  drop   FOREIGN key  wj1;

（四）外键的特点

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

2、父表中存在的数据，子表就可以插入数据（插入数据）

3、删除数据，子表中存在的数据，直接删除父表是无法删除 （要先删除子表数据，再删除父表数据）

4、删除父表的数据，先删除子表，再删除父表

存储

一、存储过程介绍

1、什么是存储过程？

存储过程是实现某个特点功能的sql语句的集合，编译后的存储过程会保存在数据中，通过存储过程的名称反复的调用执行。

2、存储过程的优点？

（1）存储过程创建后，就可以反复地调用和使用，不需要重新写复杂的语句

（2）创建，修改存储过程不会对数据有任何的影响

（3）存储过程可以通过输入参数返回输出值

（4）通过存储过程中加入控制语句，可以加强sql语句的功能性和灵活性

（5）对于单个语句增删改查，可以直接封装一个集合中，存储过程一旦创建就可以直接调用，且可以重复调用

（6）单个sql语句每一次执行都需要对数据进行编译，而存储过程被创建只需要编译一次，后续即可调用

（7）创建的存储过程，可以重复进行调用，可以减少数据库开发人员的工作量

（8）防止sql 注入

（9）造数据（重点）

3、mysql5.0版本之后就支持存储过程，存储过程是由sql语句和控制语句组成的

二、存储的格式

1、基本格式（不带参数）：

delimiter //  分隔符

drop （删除）  PROCEDURE （存储） if EXISTS（判断是否存在）  cc1  存储;

create创建   PROCEDURE存储  cc1()  存储名称

BEGIN 开始

   select  * from  emp ;  存储体1

   select  *  from  dept ;存储体2

END  结束存储

//  分割符

call  cc1()  调用   存储名称

案例：

delimiter //

drop  PROCEDURE if EXISTS  cc1 ;

create   PROCEDURE  cc1()

BEGIN

   select  * from  emp ;

   select  *  from  dept ;

END

//

call  cc1()

2、带传入参数（in）

格式：

delimiter //

drop  PROCEDURE if EXISTS  存储名称;

create   PROCEDURE  存储名称( in x int)

BEGIN

  语句1

END

//

call  存储名(实际参数)

案例：

delimiter //

drop  PROCEDURE if EXISTS  cc2 ;

create   PROCEDURE  cc2( in x int)

BEGIN

   select  * from  emp  where  dept2=x;

END

//

call  cc2(105)

3、in输入值、out 返回值

select  * from emp ;

desc emp ;

delimiter //

drop  PROCEDURE if EXISTS  cc2 ;

create   PROCEDURE  cc2( in x int,out y int)

BEGIN

   select  age  into y from  emp  where  dept2=x;

END

//

call  cc2(105,@y)

select  @y

4、inout  一个变量是输入值也是输出值

案例：

delimiter //

drop  PROCEDURE if EXISTS  cc2 ;

create   PROCEDURE  cc2( inout x int)

BEGIN

   set x=x+1;

END

//

set @x=1

call  cc2(@x)

select  @x

5、设置变量方法：

set     @ 变量名 

赋值的方法：

（1）方式一

set   @ 变量名：=值  设置一个变量值

select  @变量名  显示所有数据

（2）方式二

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

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

6、循环语句

while  循环语句：

格式

while   条件  do

sql语句

end while

7、造数据场景

（1）传入固定值

create table m1 (id int(10) PRIMARY key ,name varchar(20));

delimiter //

drop  PROCEDURE if EXISTS  cc2 ;

create   PROCEDURE  cc2( )

BEGIN

  INSERT  into  m1(id)  VALUES(2);

  SELECT  *  from m1;

END

//

call  cc2()

（2）出入变量值的数据

案例

delimiter //

drop  table if EXISTS m1;

create table m1 (id int(10) PRIMARY key ,name varchar(20));

drop  PROCEDURE if EXISTS  cc2 ;

create   PROCEDURE  cc2(in  x  int )

BEGIN

DECLARE  i int  DEFAULT 0 ;

WHile  (i<x) do

     INSERT  into  m1(id)  VALUES(i);

     set i=i+1;

end while;

  SELECT  *  from m1;

END

//

call  cc2(10)

（3）先统计总数量，再插入传入的值

delimiter //

drop  table if EXISTS m1;

create table m1 (id int(10) PRIMARY key ,name varchar(20));

drop  PROCEDURE if EXISTS  cc2 ;

create   PROCEDURE  cc2(in  x  int )

BEGIN

DECLARE  i int  DEFAULT (select  count(*) from m1) ;

WHile  (i<x) do

     INSERT  into  m1(id)  VALUES(i);

     set i=i+1;

end while;

  SELECT  *  from m1;

END

//

call  cc2(10)

（4）每次删除表，再重新插入数据

delimiter //

drop  PROCEDURE if EXISTS  cc2 ;

create   PROCEDURE  cc2(in  x  int )

BEGIN

DECLARE  i int  DEFAULT 0 ;

drop  table if EXISTS m1;

create table m1 (id int(10) PRIMARY key ,name varchar(20));

WHile  (i<x) do

     INSERT  into  m1(id)  VALUES(i);

     set i=i+1;

end while;

  SELECT  *  from m1;

END

//

call  cc2(3)