mysql 语句--索引、视图、存储过程

索引
1、重点：索引是一种数据结构

2、索引的作用 可以利用索引快速访问数据库中的特定信息.

3、索引分类
3.1 普通索引
3.2 唯一索引
3.3 主键索引

3、创建一个普通索引  ==》最基本的索引，没有限制
create table bb (id int(10) primary key,name varchar(10),class int(10));  #创建一个bb表

insert into bb VALUES (1,'duan',1833),(2,'he',1833),(3,'li',1833);   #往bb表插入数据

select * from bb;   

create index tt on bb(name);         #给bb表字段name上创建一个普通索引tt
create index yy on bb(name,class);   #给bb表字段name class 创建索引yy

show index from bb;                  #查询已创建的索引
alter table bb drop index yy;        #删除yy索引

4、创建一个唯一索引  ==》与普通索引不同时索引值必须唯一，但是可以为空
create unique index yy on  bb(class);   #不能创建有相同字段值的字段

create unique index yy on  bb(id);      #给bb表中字段id创建一个唯一索引yy
show index from bb;                     #查询已创建的索引
alter table bb DROP index yy;           #删除yy索引

5、创建主键索引  ==》不可以空值且唯一，与主键相辅相成
alter table bb drop primary key;        #删除主键

alter table bb add PRIMARY KEY (id);    #创建主键索引，同时也是创建主键

show index from bb;                     #查询已创建的索引


注意：添加一个主键就是添加了主键索引，添加主键索引也就是添加了主键

【面试题】：主键和唯一索引的区别？
1）主键时一种约束，唯一索引时索引，本质就是不一样
2）创建了主键后一定包含了一个唯一索引，唯一索引不一定时主键
3）一个表只能由一个主键，但是唯一索引可以多个
4）主键更适合哪些不容易改变的唯一标识


总结：
优点：
1）可以保证数据的唯一性
2）加快查询效率
3）提高性能

缺点：
1）维护耗时间
2）占用物理内存



视图
select  * from  stu;    #查询基础表的数据
create view stu1 as (select id,name,age,sex from stu);   #创建一个视图stu1给到id name age sex

show tables;           #查询视图

select * from stu1;   #查询视图的数据

update stu1 set age = 30 where id = 1;    #把视图stu1中id为1的数据年龄改为30，同时基础表的数据也会变化

update stu  SET age = 25 where id = 1;    #把基础stu中id为1的数据年龄改为25，同时视图的数据也会变化

drop view stu1;                   #删除视图


数据库中的DDL 和DML
DDL  (data definition language 数据定义语言) ==》alter table  create drop


DML (data manipulation language 数据操作语言)  ==》select  DELETE UPDATE  insert into


【外键约束】

数据库的约束
主键约束   ==》primary key
非空约束   ==》not NULL  
默认值约束  ==》default
自增长约束  ==》auto_increment
外键约束    ==》foreign key    表与表之间的约束

show create table dcs;      #查询创表语句和结构
CREATE TABLE `dcs` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `score` float(10,2) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `phone` bigint(10) DEFAULT '13977778888',
  `time` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8


create table dcs3(id int(10) primary key,name varchar(10))engine=INNODB;  #主表
#(engine :引擎 innodb：存储引擎 )

show create table dcs3;     #查询创表语句和结构
CREATE TABLE `dcs3` (
  `id` int(10) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

create table dcs4(sid int(10) primary key,sname varchar(10),constraint fk_sid foreign KEY
(sid) references dcs3(id))engine=INNODB;
给子表dcs4创建一个外键，外键名字叫fk_sid 并且子表的外键字段为sid，对应主表dcs3的主键名字为id



FK_***：是外键名 --对应dcs4表中的：fk_sid foreign key(子表的外键字段)
--对应dcs4表中的：sid references 父表的表名（父表的主键的字段名）
---父表名对应dcs1，父表的主键的 字段名对应dcs3中的id字段 外键之增加数据：

总结：如果表1和表2之间有外键约束 且表1是主表 表2为子表 如果主表不存在的数据 在任何
一张子表是无法插入跟该数据相关的任何数据，如果要删除主表的数据，先删除与主表
相关的任何子表的数据
show create table dcs4;

CREATE TABLE `dcs4` (
  `sid` int(10) NOT NULL,
  `sname` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  CONSTRAINT `fk_sid` FOREIGN KEY (`sid`) REFERENCES `dcs3` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into dcs4 VALUES(1,'duan');    #主表dcs3 没有数据，子表dcs4不能插入数据

insert into dcs3 VALUES(1,'duan');    #给主表插入数据

delete from dcs3 where id = 1;        #当前子表dcs4由数据，不能删除主表的数据
Cannot delete or update a parent row: a foreign key constraint
fails (`dcs9`.`dcs4`, CONSTRAINT `fk_sid` FOREIGN KEY (`sid`) REFERENCES `dcs3` (`id`))

delete from dcs4 where sid = 1;     #删除了子表的数据就是可以删除主表的数据


alter table dcs4 DROP foreign key fk_sid;    #删除外键fk_sid

show create table dcs4;
CREATE TABLE `dcs4` (
  `sid` int(10) NOT NULL,
  `sname` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


alter table dcs4 add constraint fk_sid foreign key dcs4(sid) references dcs3(id);
#给dcs4这个表字段sid添加一个外键fk_sid
show create table dcs4;
CREATE TABLE `dcs4` (
  `sid` int(10) NOT NULL,
  `sname` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  CONSTRAINT `fk_sid` FOREIGN KEY (`sid`) REFERENCES `dcs3` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


存储过程


1、什么是存储过程 存储过程是完成特定功能的sql语句集合。通过编译后存储在数据 库中，
通过指定的存储过程名称调用执行它。 存储过程=sql语句集合+控制语句

2、使用存储过程的优点
1）存储过程创建可以多次调用，不需要重新编写存储过程语句。
2）假如要往表中插入大量的测试数据，用insert into语法执行速 度太慢，效率较低，
那么这个时候可以通过编写存储过程，把需要 插入的数据sql集合放入存储过程的函数体当中，
创建存储过程， 下次即可调用
3）存储过程加快程序的运行速度
4）存储过程增加sql语句的功能和灵活性、创建的存储过程可以重复使用。

3、第一步 创建一个存储过程
create procedure 存储过程名（参数 数据类型）

BEGIN

存储过程体（sql语句集合）

END

#begin....end   ==》代表的是存储过程的开始和结束


call 存储过程名();    ==》调用存储过程


4、第二步  删除一个存储过程
drop procedure 存储过程名

DROP procedure if EXISTS 存储过程名  ==》加强代码的健壮性

5、第三步
drop table if exists 表名    ==》增强存储的健壮性

6、 第四步 不用参数查询语句

例子1：不带参数
drop procedure if exists dcs88;     #如果存在dcs88这个存储过程先删除，加强代码的健壮性
create procedure dcs88()            #创建一个存储过程dcs88

begin     #存储过程的开始



     drop table if exists nn;      #如果nn这个表存在就删除 增强存储的健壮性
     create TABLE nn(id int(10) primary key auto_increment,score int(10));
     insert into nn VALUES(1,88),(2,99),(3,89),(4,98);
     select * from nn;
     desc nn;
end



call dcs88();    #调用存储过程dcs88


7、在定义存储的时候带参数，用参数查询

drop procedure if exists dcs88;        #如果存在dcs88这个存储过程先删除，加强代码的健壮性
create procedure dcs88(t int)            #创建一个存储过程dcs88 t表示形式参数 int整型

begin     #存储过程的开始



     drop table if exists nn;      #如果nn这个表存在就删除 增强存储的健壮性
     create TABLE nn(id int(10) primary key auto_increment,score int(10));
     insert into nn VALUES(1,88),(2,99),(3,89),(4,98);
     select * from nn;
     desc nn;
end



call dcs88(10);