软件测试_mysql之视图-索引-外键-存储过程

视图
一、视图定义
    是一个虚拟表（逻辑表），在数据库中不以存储数据的形式保存，在使用视图的时候动态生成
二、作用
  1.避免了每次写sql语句会出错，提高了查询的效率
  2.为了安全，在公司中有限字段为保密字段，可以用视图显示对某些字段的操作。
三、优缺点
  优点：1.提高查询效率
  2.安全
  3.简单
缺点：1.性能差
  2.修改限制 （修改视图数据，必须把它转化为对基础表的修改）

脱敏测试（加密测试）
通常情况下，数据库是敏感，（脱敏测试）不是每一个用户都能进行访问，但是某写用户需要读取表中的部分数据，这时就可以指定条件创建视图来限制用

户只能访问指定的数据，并提供一个视图接口进行访问。

四、视图的使用
（1）查看数据库中的基本表show tables；
（2）创建视图
   格式：create view 视图名 as （sal语句）
    eg：create view st as (select * from dept1,emp1 where deptbm=deptbm1);
（3）查看创建的视图（包括建表语句，视图名，编码格式）
   eg： show create view st;
（4）删除视图
   eg：DROP view st；
（5）查看试图所有内容
  eg：select * from st;
（6）创建视图后就不需要复合表，语句也简单
五、试图的特点
（1）视图是由基本表产生的虚拟表
（2）视图的创建和删除不影响基础表
（3）视图的数据更新和删除数据直接影响基础表
（4）视图内容来源于多个虚拟表，结构不能删除
（5）视图不能修改表字段
（6）基础表修改数据，视图也修改
（7）基础表修改字段，视图报错

=============================================================
索引
一、定义
索引是一种数据结构，是在表的字段上创建的。

二、作用及优缺点
1、作用：
   （1）保证数据记录的唯一性
   （2）实现表与表之间的参照完整性
  （3）减少排序和分组的时间（例如在使用order by ，group by 查询语句进行数据检索时）
  （4）可以利用索引快速访问数据库中特定信息
2、缺点
  （1）索引要占用物理内存
  （2）对表进行增删改查，索引也要动态维护，降低数据的维护速度
三、索引的分类、使用
1、分类
  （1）普通索引  index   显示mul ，最基本的索引，没有任何限制
  （2）主键索引 primary key  显示pri，唯一不能为空
  （3）唯一索引 unique  显示uni ，可为空
  （4）全文索引
  （5）组合索引
  （6）单列索引（了解）
  （7）聚焦索引（了解）
  （8）非聚焦索引（了解）
2、使用
  （1）索引的查询方法
      方法一：show index from 表名；
      方法二：show keys from 表名；
  （2）查看表结构
      格式：desc 表名
  （3）创建索引
     a.建表后建立索引
        a.1 建立普通索引   index   mul
              alter table 表名 add index 索引名（字段名）；
           eg：alter table emp add index sy (job)；
                  alter table emp add index  (job)；索引名和字段名一致
        a.2创建唯一索引 unique 简写uni 唯一索引，可以为空，一个表中有多个唯一索引
              alter table 表名 add unique  索引名（字段名）
            eg：alter table emp add unique sy1(ename)；
                    alter table emp add unique (ename)；索引名和字段名都是ename
        a.3创建主键索引 primary key 简写 pri 唯一，不能为空，主键索引一个表中只有一个
               alter table 表名 add primary key （字段名）；
            eg：ALTER table emp add primary key (empno)；
     b。create方法创建索引
        格式：create index 索引名 on 表名（字段名）；
            eg：create index sy2 on emp(sal)；
     c。新建表时建立索引
        eg：create table sy(id int (10)primary key)；
  （4）删除索引
      方法一:（删除普通索引和唯一索引）
          alter table 表名 drop index 索引名；
          eg：alter table emp drop index sy2
      方法二：（只删除主键索引）
          alter table 表名 drop primary key；
         eg：alter table emp DROP primary key;
四、面试题

1、你熟悉数据库语句吗？

2、你说下索引有哪些？

3、索引主要用来做什么？

4、创建索引的时间？

5、主键索引和唯一索引的区别？

==================================================================

外键
一、外键介绍
（1）定义：让一张表记录的数据金莲更不要太过冗余，尽量让表数据单一化
（2）作用：保持数据的一致性和完整性
（3）mysql数据库中存储引擎？
        innodb（外键要使用这个）
        myisam（默认）
（4）查看引擎
       格式：show  table status from 库名 where name=“表名”；
          eg：show table status from cs where name="emp";

二、建表语句
（1）通过建表语句来创建外键
    创建（父表）mm表：create table mm (id int(10)primary key ,name char(20)unique)engine=innodb;
    创建（子表）nn表：create table nn (id int(10)primary key ,name varchar(20) unique,constraint wj5 foreign key (id) references mm(id))engine=innodb;
        constraint 外键名----指定外键
        foreign key （子表字段）------指定子表字段
        references 父表名（父表主键字段）----注意这里引用的外部表字段必须是主键
  （2）直接创建外键
     创建（父表）ss：create table ss (id int(10)primary key,name varchar(20)unique )engine=innodb;
    创建（子表）dd：create table dd (id int(10) primary key ,name varchar(20)
   创建外键：alter table 子表名 add constraint 外键名 foreign key （子表字段）references 父表名（主键字段）；
             alter table dd add constraint wj6 foreign key (id) references ss(id);
  （3）查看外键的方法
      方法一：直接在Navicat软件上查看
     方法二：show create table 表名； eg：show create table dd;
  （4）删除外键
          alter table 表名 drop foreign key 外键名；
          eg：alter table dd drop foreign KEY wj6;

三、子表和父表直接的关系
（1）在子表插入数据，父表不存在的数据无法插入（报错）
      INSERT into dd values (1,"nh");（报错）
（2）父表有数据前提下，在子表可以插入数据
       INSERT into dd values (1,"nh");
      注意：在子表中插入数据，插入的和父表关联的数据必须是父表主键数据中存在的；
               比如在父表内有（1，“nn”；2“mm”）在建立子表时关联id字段只能是1或者2.
（3）存在外键是：父表删除数据没需要先删除子表数据
        a。子表和父表都有数据，直接删除父表，报错无法删除

        b。子表和父表都有数据，先删除子表，再删除父表
                 delete from dd ;父表
                 delete from ss；子表
（4）外键名可以修改
        alter table dd add constraint wj8 foreign key (id) references ss(id);相当于重新创建外键
四、公司为什么要用外键
（1）为了保存数据的一致性和完整性
（2）公司数据量大，造成数据重复，新旧表维护成本大，使用外键让公司数据进行分类和管理，让基础数据放在主表上，子表单一化，这样避免数据的冗余

===============================================================
存储
一、介绍
  （1）什么是存储过程？
     存储过程是实现某个特定功能的sql语句的集合，编译后的存储过程会保存在数据库中，通过存储过程的名称可以反复调用执行
  （2）存储过程的优点
         1.存储创建后，就可以反复调用和使用，不需要重新写复杂的sql语句
         2.创建、修改存储过程不会对数据有任何影响
         3.存储过程可以通过参数和返回值
         4.存储过程可以通过加入控制语句，加强sql语句的功能性和灵活性
         5.对于的那个的增删改查语句，可以直接封装在一个函数体当中，或者封装在一个集合当中，存储过程一旦创建就可以直接调用，而且可以重复调用
         6.单个sql语句每次执行都要数据库进行编译，而存储过程创建只需要编译一次，后续就可调用
         7.创建存储过程，可以重复进行调用，可以减少开发人员的工作量
         8.防止sql注入
         9.造数据
二、存储的基本格式
delmiter // #分隔符/定各符
create procedure   存储名称（in/out/inout）
begin #开始
       执行语句1；
       执行语句2；
end #结束
//#分隔符


先执行创建存储，再调用使用call
call 存储名（）------- 调用存储
eg：
delimiter //
create procedure cc1()
begin
        select * from emp1;
END
//
call cc1();


基本使用的语句：
（1）查看单个存储过程的详情

     格式：show create procedure 存储名；
           eg：SHOW create procedure cc1；
（2）查看所有已经创建的存储过程详情
     格式：show procedure staus;

（3）查询数据创建的存储过程
     格式： show procedure status where db="cs";
（4）删除存储
     格式：drop procedure 存储名
           DROP procedure cc1；
三、存储过程的编写
（1）无参数的存储过程
    delimiter //
     create procedure 存储名（）
     begin
          语句；
     end
     //
（2）in输入参数的存储
    delimiter //
     create procedure 存储名（in x int）
     begin
          select * from emp where 字段名=x；
     end
     //

eg：delimiter //
create procedure cc2(in x int)
begin
select * from emp1 where deptbm1=x;
END
//
call cc2(102);
（3）out 输出参数
delimiter//
create procedure cc3(out y int)
begin
select * into y from emp1 where ename ="荣七"；
end
//
eg:
delimiter //
create procedure cc3(out y int)
begin
select eage into y from emp1 where ename = "荣七";
END
//
call cc3(@y); #将语句中得出的数据执行给y
select @y; #将得到的y数据显示出来

（4）in，out
delimiter //
create procedure cc4(out y int,in x int)
bigen
  select eage into y from emp1 where id=x;
end
//
eg:
delimiter //
create procedure cc4(out y int,in x int)
begin
select eage into y from emp1 where id=x;
END
//
call cc4(@y,1789);
select @y;

（5）inout 输入和输出delimiter //
create procedure cc5(inout z int)
begin
set z:=z+1;
end
//
set @n=1;
call cc5(@z);
select @n;
eg
:
delimiter //
create procedure cc5(inout z int)
BEGIN
  set z:=z+1;
END//
show create  procedure cc5;
set @z=1;
call cc5(@z);
select @z;

四、用户变量、声明变量
（1）用户变量
       set @变量名
   赋值的语法：
       1.set @ 变量名：=值；
       2.set @变量名 = 值；
       3.select @变量名：=值；
      方法二：通过查询结果为变量赋值
     select 字段| 表达式  into 变量名 from 表名 where 条件
（2）声明变量
delare i int default 0   #declare 声明变量，i变量  数据类型 int  默认值
   第一种早数：（在存储外先建表，在造数据，写死的数据）
   create table abc(id int(10));
   delimiter //
   drop procedure if exisits cc6;
   create procedure cc6()
   begin
    declare i int default 0;
    while(i<10)do
    insert into abc values(i);
   set i=i+1;
end while;
select * from abc;
end
//
eg:delimiter //create procedure cc6()
begin
  declare i int default 0 ;
  while(i<10)do
   insert into abc values (i);
   set i=i+1;
    end while ;
        select * from abc;
END
//
call cc6();
第二种造数（在存储外仙剑表，在造数，根据输入的数据造数）

delimiter //
drop procedure if exists cc7;
create procedure cc7(in x int)
BEGIN
declare i int default 0;
while(i<x)do
   insert into abc values (i);
   set i=i+1;
    end while ;
        select * from abc;
END
//
第三种造数：（在存储表中造数，再造数据，根据输入的数据造数）

delimiter //
drop table if exists abc ;
drop procedure if exists cc9;
create procedure cs9(in x int)
BEGIN
declare i int default 0;
drop table if exists abc;
create table abc (id int(10));
while(i<x)do
   insert into abc values (i);
   set i=i+1;
    end while ;
        select * from abc;
END
//
call cs9(10);

拓展：
循环语句：
1.while.......条件。。。do 。。。。执行语句块。。。end whileloop 。。。。。end loop
repeat 。。。。until end repeat

2.if判断句的单分支：
if 条件  then
     执行sql
else
    执行sql1
end if
3.if多分支
if 条件 1 then
  执行sql1
else if 条件2
  执行mysql 2
else if 条件3
   执行mysql3
else
end if
end if
end if