索引及存储过程（mysql数据库 ）

1、什么是索引
一个索引是存储在表中的数据结构，索引在表的列名上创建。索引中包含了一
个列的值，这些值保存在一个数据结构中。数据库使用索引的方式与使用书的
目录很相似：通过搜索索引找到特定的值，然后随指针到达包含该值的行。

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

3、索引的类型
1）普通索引  ==》最基本的索引 没有限制
SHOW  tables;
create table tab13(id int(10) primary key,name VARCHAR(10),class int(10));   ==》创建一个tab13的表
insert into tab13 values(1,'xiaoduan',1833),(2,'xiaoming',1833);     ==》添加数据
select * from tab13;

create index m on tab13(id);    ==》给id字段创建一个普通索引名为m

show index from tab13;      ==》查询taab13这个表有哪些索引

create index t on tab13(name,class);    ==》给name和class字段加上索引名为t

alter table tab13 drop index t;       ==》删除t这个索引


2）唯一索引   ==》与普通索引不同的是唯一索引字段值必须是唯一的（字段值不能重复），值可以为空
create unique index t on tab13(name);

show index from tab13;      ==》查询taab13这个表有哪些索引

alter table tab13  drop index t;


3）主键索引  ==》不可以为空，也不可以重复，和主键约束相辅相成（主键约束就是主键索引）

alter table tab13 drop primary key;     ==》删除主键（删除主键索引）

alter table tab13 change id id int(10) primary key;    ==》加上主键给id

alter table tab13 add primary key (id);     ==》给id字段加上主键索引

show index from tab13;      ==》查询taab13这个表有哪些索引

desc tab13;

注意：添加一个主键约束就是添加了主键索引，反之也然；


面试题：主键和唯一索引的区别？
1）主键是一种约束，唯一索引是一种索引，本质都不一样
2）主键约束创建后一定包含一个唯一索引，唯一索引不一定是主键
3）一个表中只能有一个主键，但是唯一索引可以创建多个
4）主键不能为空，但是唯一索引可以为空


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

缺点：
1）维护起来耗时间
2）占用资源


视图

1、什么是视图
视图是一个虚拟的表，它不在数据库中以存储数据的
形式保存，是在使用视图的时候动态生成。

2、视图的特点
1）视图是由基本表产生的虚表
2）视图的更新和删除会影响基础表
3）基础表的更新和删除也会影响到视图

3、视图的作用
对视图的操作与对基本表的操作都是一样的（包括alter,create,insert
into ,update ,delete ,select）,且二者任意一方的字段值被修改，都会实时影响
到对方（如修改view的字段值，会同步修改table相应的字段值）；但是视图的
创建是基于基本表的，它的作用可以对基本表的敏感信息进行保护；在实际工
作中，出于安全考虑，将用户常用的信息创建成视图给用户调用，避免了直接
操作基本表！


4、创建视图
select * from stu;

create view stu1 as (select id,name,class from stu);    ==》创建一个stu1视图有id name class 字段

show create view stu1;      ==》查看视图的创建结构

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `stu1` AS
(select `stu`.`id` AS `id`,`stu`.`name` AS `name`,`stu`.`class` AS `class` from `stu`)


show tables;     ==》查看表或视图


select* from stu1;      ==》查看视图的所有数据


update stu1 set class = 1830 where id = 1;     ==》修改视图表的数据，基础表的数据也会改变（改视图）

select * from stu;

update stu set class = 1833 where  id = 1;   ==》修改基础表表的数据，视图表的数据也会改变（改基础表）


delete from stu1 where id = 10;       ==》删除基础表或者视图表的数据相互影响

drop view stu1;         ==》删除视图


数据库中的DDL 和DML
DDL （data definition language 数据定义语言）  ==》表结构和表字段操作 ==》create alter table drop

DML （data manipulation language 数据操作语言） ==》表数据操作 ==》select delete update set insert into

外键约束

主键约束   primary KEY
非空约束   not NULL
默认值约束  DEFAULT
自增长约束  auto_increment
外键约束   foreign key     表与表之间的约束


show create table stu;

CREATE TABLE `stu` (
  `id` int(4) NOT NULL,
  `age` int(8) DEFAULT NULL,
  `sex` int(4) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `class` int(4) DEFAULT NULL,
  `math` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8    ==》默认创建数据表的存储引擎是myisam对外键不起作用


create table dcs1(id int(10) primary key,name varchar(10))engine=innodb;
（engine：引擎  innodb：存储引擎）
innodb 是mysql 上面一种提供外键约束的存储引擎

create table dcs2(sid int(10) primary key,sname varchar(10),constraint fk_sid foreign key(sid)
references dcs1(id))engine=innodb;
（constraint：约束  references：引用）


show create table dcs1;     ==》查看dcs1创表语句
show create table dcs2;     ==》查看dcs2创表语句

CREATE TABLE `dcs1` (
  `id` int(10) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


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

解释上面的创表
给子表dcs2创建一个外键，外键名字叫 fk_sid，并且子表的外键字段为sid，对应主表dcs1的主键名
字为id

FK_sid：是外键名 --对应dcs2表中的：fk_sid
foreign key(子表的外键字段) --对应dcs2表中的：sid
references 父表的表名（父表的主键的字段名） ---父表名对应dcs1，父表的主键的
字段名对应dcs1中的id字段
外键之增加数据：
如果表1和表2之间有外键约束 且表1是主表 表2为子表 如果主表不存在的数据 在任何
一张子表是无法插入跟该数据相关的任何数据

insert into dcs2 values(1,'xiaoduan');    ==》给子表dcs2插入一条数据，插入失败，因为主表id没有同样的数据

insert into dcs1 values(1,'xiaoduan');   ==》给主表dcs1插入一条数据，这个时候子表可以插入与id相同的数据


delete from dcs1 where id = 1;    ==》删除主表的数据时，如果子表存在相同的数据，则不能删除，
先删除子表再删除主表

delete from dcs2 where sid = 1;    ==》删除sid为1的数据，删除成功

注意：
如果dcs1和dcs2存在外键关系，dcs1时主表，dcs2是子表
主表不存在的数据，再任意一张子表是无法插入与该数据相关的任何数据，如果要删除主表的数据，则要先
删除与主表相关的任意子表的数据


alter table dcs2 drop foreign key fk_sid;     ==》删除了外键2张表没有关系了，可以任意操作

CREATE TABLE `dcs2` (
  `sid` int(10) NOT NULL,
  `sname` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8     ==》此时没有外键了


alter table dcs2 add constraint fk_sid foreign key (sid) references dcs1(id);
==》添加外键（dcs1和dcs2有外键关系）


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


【存储过程】


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


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


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


begin      ==》存储过程的开始

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


end       ==》存储过程的结束


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


第二步 删除一个存储过程
drop procedure 存储过程名
drop procedure if exists 存储过程名    ==》加强带么健壮性



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


第四步  不用参数查询数据

例子1：不带参数

drop procedure if exists tt;      #如果这个存储过程tt存在，就先删除，再创建 ==》加强带么健壮性
create procedure tt()      #没有分号    创建一个存储过程名为tt

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,66),(3,77),(4,99);

     select * from nn;
     desc nn;
     select count(*) from nn;

end      #存储过程结束


call tt();     #注意接分号  调用存储过程