数据库笔记4

索引

1.索引index:通过搜索索引找到特定的值，然后随指针到达包含该值的行。

*注意：索引是一种数据结构

2.索引的作用：可以利用索引快速访问数据库中的特定信息，*注意：不能对表中全部字段加索引

3.索引分类 ：普通索引、唯一索引、主键索引，查询速度：普通索引<唯一索引<主键索引

普通索引index MUL：一张表中可以创建多个普通索引，普通索引是最基本的索引，它没有任何限制

select * from dcs;

create index tt on dcs(sid); //给sid创建索引；

create index yy on dcs(sid,class); //同时创建两个字段的索引

show index from dcs //查索引

alter table dcs drop index tt; //删除索引

唯一索引unique index：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值

create unique index aa on dcs(sid); //给sid创建索引

show index from dcs; //查索引

alter table dcs drop index aa;// 删除索引

主键索引primary key：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引

alter table dcs add primary key(id);// 给sid创建索引

show index from dcs; //查索引

alter table dcs drop PRIMARY key; //删除索引

主键索引和主键约束其实是绑在一起的，有主键索引，那么必会产生主键约束，或者说主键约束是主键索引的一个特征，要删除主键索引就要先删除主键约束。

视图

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

2.视图的特点 （同步修改，互相影响）

2.1视图是由基础表产生的虚表

2.2视图的更新和删除会影响基础表

2.3基础表的更新和删除也会影响到视图

3.视图的作用 对视图的操作与对基本表的操作都是一样的，但是视图的创建是基于基本表的，二者同步修改，互相影响；数据库中有些查询是非常复杂的，但是这些查询是经常使用的，使用视图可以直接查看这些频繁使用的查询语句；由于它不是直接对基本表进行操作所以对基本表的敏感信息也有保护作用。

相关语句：

create view yy as (select id,name fromdcs); //将查询结果集创建为视图yy

show CREATE VIEW yy（视图名）//也能查看到视图

Show tables //查看视图，在表的下面

drop view 视图名; //删除视图

数据库中的DDL和DML：

DDL //数据库定义语言 （对表结构和表字段进行操作） createalter table drop

DML //数据库操作语言 （对表数据进行操作） insert intodelete select update

外键（也称外码）

*注意：总的原则为对于插入和删除，父（主）表要先有数据（从）子表才能有。

外键定义与作用：外键   ( foreign key )  是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键。为了一张表记录的数据不要太过冗余。这和软件project的模块化思想类似，仅仅只是在数据库中是对表关系进行解耦（分解表），尽量让表记录的数据单一化。外键的主要作用是：保持数据的一致性、完整性

表内约束：

主键约束：不能为空，不能重复，是唯一的

非空约束：不能为空

自增长约束：通常是结合主键约束一起使用的，在插入数据的时候，不对定义了自增长约束的字段进行数据插入，会直接的在该字段最大的值进行+1插入

默认值约束：对列值定义一个默认值，在不对默认约束的列插入数据时，会直接插入一个默认值

外键约束，也叫外码(foreign key)：约束的是表与表之间的关系

主要作用：保持数据的一致性、完整性

1，创建两个表dcs1、dcs2,设计时，给dcs1表加入一个外键，这个外键加在dcs2的sid字段上

创建主表dcs1:

create table dcs1(id int(4) primary key,namevarchar(10)) engine=innodb;

创建从表dcs2:

create table dcs2(sid int(4) primarykey,sname varchar(10),constraint fk_sid foreign key(sid) references dcs1(id))engine=innodb;

-- 创建表 dcs2 包含字段 sid 类型为int(4) 主键，sname 类型为varchar(10),创建约束 命名为fk_sid 外键定义在sid字段上，引用dcs1表中的id字段，数据引擎为innodb

解释：

dcs1 ==>主表（父表）

dcs2 ==>从表（子表）

constraint fk_sid ：约束的名称为fk_sid

foreign key(sid) :外键定义在sid字段上

references dcs1(id)：引用的是dcs1表的id字段

engine=innodb ：在mysql建表，默认的数据引擎是MyISAM，在使用外键的表都要使用innodb的数据引擎类型

存储过程

存储过程：完成特定功能的sql语句集合。通过编译后存在库中，用时直接用call调用执行。

存储过程=sql语句集合+控制语句

特点：可多次调用，不需重新编写存储过程，程序运行速度快效率高。

创建存储过程：

Create procedure 存储过程名称（参数，数据类型）

Begin

  存储过程体；

End；

存储过程体插入if语句（有多少if对应有多少end）：

if 条件 THEN

执行sql

else if 条件 THEN 执行sql

else if 条件 THEN 执行sql.............

else 执行sql

end if；

end if；

end if；

存储过程中插入while语句（循环语句）：

while 条件 do

执行循环体（sql）

end while；

调用存储过程：

Call 存储过程名称（参数）；

*注意有形参必须输入实参，且实参与形参需一一对应

删除存储过程：

Drop procedure存储过程名称；

加强代码健壮性：

Drop procedureif exists 存储过程名称；

Drop tableif exists 表名称；

自动统计数据行数：

Declare nint default (select count(*) from 表名称)；

具体例子：

存储过程题目：现在有一个user用户表,需要往user表中插入1000个登录

用户

要求如下：

1、在插入用户前先判断是否存在1000个登录用户,如果存在则统计表中实

际的行数、如若不存在则自动补齐剩余的数据

2、表名为user,存储过程名字随意取,表中字段有id user_name user_pwd

verify 格式如下(1,user1,123456,W4E38J),且id、用户名不能重复,verify

验证码字段为随机生成6位数验证码

CONCAT函数 可以把2个字符串进行连接。

用到函数：

concat('user',n);#把字符user与n连接起来，usern

substring(md5(rand()),1,6);#随机生成六位验证码

#创建表

createtable user(id int(10) primary key auto_increment,user_name varchar(10),user_pwdint(10) default 123456,verify varchar(10));

insertinto user values(1,'user1',123456,'sf124s');

select*from user;

#创建存储过程

dropprocedure if exists uu;

createprocedure uu(i int)

begin

   declare n int default(select count(*) fromuser);

   if n>=i then

          select count(*) from user;

          else

             while n<i do

                              set n=n+1;

insertinto user(user_name,verify)values(concat('user',n),substring(md5(rand()),1,6));

                             end while;

          end if;

         select *from user;

end;

#调用存储过程

calluu(1000);