武汉3班第19天 索引与视图 及存储过程练习题

索引：
类似目录得功能，为了就是方便查找
创建索引的目录是为了提高查询得效率
索引其实就是一种特殊结构得查询表，数据库可以利用它加速
对数据得搜索。

1.一个字段可以创建多个索引位
2.唯一索引如果有相同得值则创建失败，列得值必须唯一

优点：
创建索引可以大大提高系统得性能，可以加快查询速度
创建索引得时候，它是占用一定得资源。没必要得字段不需要进行
创建索引

在工作当中索引使用得场景：
在工作当中都是有数据库管理员或者相应得开发进行创建索引
我们测试人员只负责查询就行。




创建一个普通索引：（就是我创建表得时候给这表增加了
主键，那么他会自动生成一个索引）
create index aa on user(opens_id) 单个字段创建索引

create index bb on user(due_date,money) 多个字段增加索引

show index from sub;

create unique index sub on sub(openid)

删除一个索引
alter table sub drop openid


视图也是一个表：

show tables：查询所有得表以及视图得表

create view dcs3 as(select avg,max from wuhan3) #给对应得
字段 创建一个视图表#
select * from dcs3
select * from wuhan3

drop view dcs3 删除 一个视图
# 修改视图表里得内容，原始表也会进行修改，修改原始
#表那么相应得视图也会进行修改

视图是什么？
视图是一个虚拟表，根据原始表进行创建，创建后可以进行对
视图表得操作，增删改查

视图优缺点？
1.可以选取想要得数据，过滤不需要得字段
2.用户可以使用简单得查询就可以得到结果
3.相同数据可以创建多个视图，独立性强，安全



mysql 当中常见得约束类型：
外键约束：FOREIGN KEY
主键约束: PRIMARY KEY
非空约束: not nall
默认约束:default
自增长约束 aoto_increment

外键？
创建外键得目的，就是为了增强数据得一致性。

create table wuhan1(id int(4)PRIMARY KEY ,name varchar(20))engine =innodb; #改变mysql中引擎为innodb  MYISAMmysq当中得默认引擎

create table wuhan2(sid int(4)primary key ,aname varchar(20)
,constraint wuhan foreign key(sid) references wuhan1(id))engine=innodb;

select * from wuhan1;#武汉1主表
select * from wuhan2;#武汉2子表
delete from wuhan1 where id = 1;


1,mysql现在版本当中默认引擎为myisam他是补课创建外键，所以
改成innodb。

constraint wuhan
取一个外键名称为 武汉
foreign key(sid)
设置外键字段得一个字段名
references wuhan1(id)
参照武汉1表里id字段设置外键

外键：
一般在设计数据得结构时就已经存在，都是由开发人员/数据库人员
进行创建
作用：
保证了数据得一致性，对数据库的资源进行管理
可以防止数据以外删除，没有了对应的数据


存储：
存储其实就是一款可以进行编译的函数，可以以封
装特定的功能

那么使用insert into进行插入数据效率太低，那么就可以
用存储过程来写一个方法，进行插入数据，而且还可以重复
使用。
存储特点：
1.可以增强sql语句的灵活性
2.执行速度更快
3.适用性强
4.分布式工作
5.可以防止sql注入
6.可以用外部程序进行调用（如java程序）

存储过程和函数的区别？


存储过程：
1.用于sql当中进行使用的，完成特定任务
2.程序的头部是procedure
3.sql语句当中select语句不能调用存储过程

函数：
1.作用与特定的数据
2.程序头是function
3.不能独立执行，作为表达式的一部分
4.函数是通过return 语句返回一个个特定值







drop procedure if exists ergouzi; #判断这个ergouzi存储
#过程是否存在，如果存在，那就进行删除这个存储过程
create procedure ergouzi(n int)#存储过程的名称()实际传输的参数
begin #存储过程的开始


declare i int(4) default(SELECT count(*) from uuid); #i就等于1

drop table if exists uuid; #如果这个表存在那么就删除这个表
create table uuid(id int(4),name varchar(20));
insert into uuid(id,name)values(1,'hh');
#存储过程里面的判定语句，if表示
if n > i THEN   #如果n大于5那么就 查询表里面的全部内容
SELECT * from uuid;
else if n = i then#如果n等于5那么就 查询表里面的仅为id字段内容
select id from uuid;
else #否则的意思，其他条件，就查询统计表里面的长度
select count(*) from uuid;

end if;
  end if;#使用了几个if判断就需要加几个end if


end #存储过程的结束

call ergouzi(2) #调用这个存储过程，执行  (填入的数字)其就是n的实际参数


drop table if exists username; #如果这个表存在那么就删除这个表
create table username(id int(4),name varchar(20));
insert into username(id,name)values(1,'hh');

drop procedure if exists wuhan;
create procedure wuhan(n int)
begin #存储过程的开始


declare i int(4) default(SELECT count(*) from uuid);

if n < i THEN   
SELECT * FROM username;
else
while i<n do   # i 是1  n 是10
insert into  username(id,name)values(2,'qq');
set i = i+1;  # 是1 每执行一次i+1  
end while;
select * from username;

select concat('user','name');

end if;

end
call wuhan(10)



存储过程实战演练 存储过程题目：现在有一个user用户表,需要往user表中插入1000个登录 用户要求如下： 1、在插入用户前先判断是否存在1000个登录用户,如果存在则统计表中实 际的行数、如若不存在则自动补齐剩余的数据 2、表名为user,存储过程名字随意取,表中字段有id user_name user_pwd verify 格式如下(1,user1,123456,W4E38J),且id、用户名不能重复,verify 验证码字段为随机生成6位数验证码 CONCAT函数 可以把2个字符串进行连接。
select left(uuid(),6)

substring(MD5(RAND()),1,6)

drop procedure if exists userr;drop table if exists userr;create table userr(id int(4),user_name varchar(20),user_pwd varchar(20),verify varchar(20));insert into userr(id,user_name,user_pwd,verify)values(1,'user1','user22','5sd5ds');insert into userr(id,user_name,user_pwd,verify)values(2,'user3','user33','8sdsds');insert into userr(id,user_name,user_pwd,verify)values(3,'user4','user44','4sd6ds');create procedure userr(n int)BEGINdeclare i int(4) default(select count(*) from userr);declare q varchar(20) default'';  declare w varchar(20) default'';if i >= n thenselect count(*) from userr;elsewhile i < n doset q = (select left(uuid(),6));set w = (select concat('user',i));insert into userr(id,user_name,user_pwd,verify)values(id+1,w,q,q);set i = i+1;end while;select * from userr;end if;endcall userr(1000)