软件测试入门自学笔记（3）数据库

一、基本概念

1.RDBMS、表

表是数据库中存储数据的基本单位。

RDBMS（relational database management system）关系型数据库管理系统

表头、数据航、字段。表头有多个字段名，代表每一列数据的含义。一个数据库中可存多个表，表包含多行数据，每一行数据有多个字段值。

2.SQL类别

SQL（structured query language 结构化查询语言）是RDBMS（MySQL、Oracle、sqlite等）操作数据的语言，不区分大小写，主要分为：

类别	作用	核心指令
数据查询语言（DQL）	进行数据查询	SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY
数据定义语言（DDL）	定义数据结构与数据库对象	CREATE、ALTER、DROP
数据操纵语言（DML）	对数据库其中的对象和数据进行访问工作	INSERT、UPDATE、DELETE
数据控制语言（DCL）	控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权	GRANT（授权）、REVOKE（撤权）
指针控制语言（CCL）	对一个或多个表单独行操作	DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT
事务处理语言（TPL）	确保被DML语句影响的表的所有行及时得以更新	BEGIN TRANSACTION，COMMIT和ROLLBACK

二、MySQL安装与使用

Linux Centos

查看是否开启的命令：ps ajx | grep mysql

root操作：service mysqld start/stop/restart 启动/停止/重启

客户端使用（Navicat）

进入命令行客户端：mysql -u root

退出命令行客户端：ctrl+d / quit / exit

Mysql安装在Windows中 ，注意路径为英文。

三、数据类型及约束类型

整数int（4个字节，32位，最大数为2^32-1）；

小数decimal（0~4294967295） decimal(5,3)表示小数占3位，整数2位

字符串varchar（0~65533）

日期时间datetime

查看数据类型的使用，在mysql命令客户端（如help tinyint）

约束：约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性，主要类型为以下6种

类型	定义	特征
非空（not null）	确保当前列的值不为空值	所有类型的值都可以是null
唯一性（unique）	指定table的列或列组合不能重复，确保数据唯一	不允许重复值，但可以为多个null；同一个表可以有多个唯一约束，多个列组合的约束；可以多表创建组合唯一约束
主键（primary key）	代表一条记录的唯一标识	相当于唯一+非空的组合，不允许重复、空值；每个表最多一个主键，可在列级别也可在表级别创建约束；创建时，系统会默认在所在列或列组合上建立对应的唯一索引
外键（foreign key）	加强两个表（主表与从表）之间的联系，保证参照完整性	先定义主表的主键，再定义从表的外键，即只有主表的主键才能被从表用来作为外键使用，被约束的从表中的列可以不是主键，主表限制了从表更新和插入的操作
默认值（default）	没有指定数据时的值	如果没有设置默认值，则系统默认为null
自增长（auto increment）	约束任一字段的自增长	主键约束的主键字段，一定是自增字段，反过来不一定。当插入第一条记录时，给自增字段一个具体值，那么以后插入的记录在此自增字段上的值，就在第一条记录该自增字段的值的基础上每次增加1。

四、基础操作（数据库、数据表、数据的增删改查）

数据库设计

E-R（entry-relationship 实体-联系）模型：（实体、联系和属性）对应（表、对应关系和字段）

笛卡尔积 = 两个表数据个数相乘

内连接、左连接、右连接、自关联、子查询

1.数据库

查看所有数据库   show databases;

使用数据库    use 数据库名;

查看当前使用的数据库    select database();

创建数据库    create database ceshi charset=utf8;

删除数据库    drop database ceshi;

备份：

       1、以管理员身份运行cmd程序

       2、进入到mysql安装目录下的bin目录

       cd C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin

       3、备份某个数据库到某个文件中

       mysqldump –uroot –p tt > ceshi.sql

       输入mysql的密码

恢复：

       1、以管理员身份运行cmd程序

       2、进入到mysql安装目录下的bin目录

       3、先创建新的数据库 ceshi_back

       4、把前面备份的文件恢复到ceshi_back数据库中

       mysql -uroot –p ceshi_back < ceshi.sql

       输入mysql的密码

2.表

删除然后建表：

drop table if exists students;
create table students(id int unsigned primary key auto_increment,
name varchar(10),age tinyint unsigned,height decimal(5,2));

查看当前数据库中所有表  show tables;

查看表结构    desc 表名;

查看表的创建语句   show create table students;

3.数据

插入

   Insert into students values(‘亚瑟’,20) ;

   insert into students values (0,'老夫3',20),(0,'老夫4',20),(0,'老夫5',20);

   insert into students(age,name) values(30,'亚瑟2');

   insert into students(id,name) values (0,'老夫3'),(0,'老夫4'),(0,'老夫5');

修改

   update students set age=age+3 where name=’亚瑟3’;

删除

   物理删除—delete from students where name=’亚瑟3’;

   逻辑删除—

          1、-- 添加字段，标识数据是否被删除 is_delete      

          --  默认设置为0，代表数据没有被删除

          2、-- update students set is_delete=0

          3、-- 删除一条数据，只是修改了这条数据的is_delete 改为1

          -- update students set is_delete=1 where name='老夫子6'

          4、-- 查询所有学生时，不显示删除的学生

          select * from students where is_delete=0

查询

Ps:可给表或字段起别名（as，可省）

   Select * from stu where sex=’男’

   select distinct * from students    （不重复项）

   比较运算（< > <>或者！=）：

       select * from students where age<20

       select * from students where hometown<>'北京'

   逻辑运算（and or not）：

       select * from students where not hometown='天津'

   模糊查询（like % _）:

       select * from students where name like '%白%'

   范围查询（in与not in与between and）:

       select * from students where hometown not in ('北京','上海','广东')

       select * from students where age between 18 and 20        【闭区间】

   判空:

       select * from students where card is not null

       select * from students where card=''

   排序（order by默认asc可省略，降序desc）：

       select * from students order by age desc,studentNo

   分组（group by,having用于分组后过滤）:

       select age,count(*) from students group by age

   连接查询（等值连接，左连接，右连接，自关联）：

• 如果一个 JOIN 至少有一个公共字段并且它们之间存在关系，则该 JOIN 可以在两个或多个表上工作。

• 连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

• JOIN 保持基表（结构和数据）不变。

select 列1、列2... from 表名 where ……

select stu.name,sc.score from students as stu ,scores as sc 
where stu.studentNo=sc.studentNo;

select stu.sex,stu.name,sc.score,cs.name from students stu inner join 
scores sc on stu.studentNo=sc.studentNo inner join 
courses cs on sc.courseNo=cs.courseNo 
where sex=’男’ order by sc.score desc limit 1;

select * from areas p,areas c where p.aid=c.pid and p.atitle='河南省';

组合（union）

• UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行。

• UNION 基本规则

  • 所有查询的列数和列顺序必须相同。

  • 每个查询中涉及表的列的数据类型必须相同或兼容。

  • 通常返回的列名取自第一个查询。

• 默认会去除相同行，如果需要保留相同行，使用 UNION ALL。

• 只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

• 应用场景

  • 在一个查询中从不同的表返回结构数据。

  • 对一个表执行多个查询，按一个查询返回数据。

组合查询示例：

SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'Fun4All';

 JOIN vs UNION

JOIN 中连接表的列可能不同，但在 UNION 中，所有查询的列数和列顺序必须相同。UNION 将查询之后的行放在一起（垂直放置），但 JOIN 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。

子查询（标量子查询，行级子查询，列级子查询，表级子查询）

   标量子查询：

       select * from students where age>(select avg(age) from students);

   行级子查询：

       select sex,age from students where sex='男' order by age desc limit 1;        （limit x, y  x为起始位索引，默认为0，y为取的行数）

   列级子查询（结果返回一行多列）：

       select * from scores where studentNo in(select studentNo from students where age=18);

   表级子查询：

       select * from scores sc inner join (select * from courses where name in (‘数据库’,’系统测试’) c on sc.courseNo=c.courseNO;

五、数据库高级

1.流程控制 case语法

select left(name,1) ,sex,
case sex
when '男' then concat(left(name,1),'帅哥')
when '女' then concat(left(name,1),'美女')
else '保密'
end as res
from students;

2.函数

2.1 内置函数

常用（见）内置函数表：

类别		说明	示例
聚合函数	count	返回某列的行数	select count(name) from students;
	max/min	返回某列的最大值/最小值
	sum/avg	求和/求平均数
字符串函数	concat(str1,str2)	拼接字符串	select name,sex,hometown,concat(name,'是',hometown,'的',sex,'生') as des from students;
	length(str)	字符串长度
	left(str,len) right(str,len) substring(str,pos,len)	截取字符串
	ltrim(str) right(str)	去除空格
	lower(str) upper(str)	大小写转换
数学函数	round(n,d)	求四舍五入值，n表原数，d表小数位置（默为0）	select round(1.6,1);
	pow(x,y)	x的y次幂
	PI()	圆周率
	rand()	0-1.0的随机浮点数	select * from stu order by rand() limit 1;
日期时间函数	current_date() current_time() now() date_format(date,format)	当前日期 当前时间 日期时间 日期格式化

2.2 自定义函数

1、新建查询，创建自定义函数
create function my_trim(aaa varchar(100)) returns varchar(100)
begin
return rtrim(ltrim(aaa));
end

2、使用自定义函数
select my_trim('   name    ')

3.存储过程

• 存储过程可以看成是对一系列 SQL 操作的批处理；

• 使用存储过程的好处

  • 代码封装，保证了一定的安全性；

  • 代码复用；

  • 由于是预先编译，因此具有很高的性能。

• 创建存储过程

  • 命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

  • 包含 in、out 和 inout 三种参数。

  • 给变量赋值都需要用 select into 语句。

  • 每次只能给一个变量赋值，不支持集合的操作。

1.创建存储过程：
DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, 
IN b int, OUT sum int)
BEGIN
    DECLARE c int;
    if a is null then set a = 0;
    end if;
    if b is null then set b = 0;
    end if;
    set sum  = a + b;
END
;;
DELIMITER ;

2.使用存储过程：
set @b=5;
call proc_adder(2,@b,@s);
select @s as sum;

4.视图

• 定义

  • 视图是基于 SQL 语句的结果集的可视化的表。

  • 视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。对视图的操作和对普通表的操作一样。

• 作用

  • 简化复杂的 SQL 操作，比如复杂的连接；

  • 只使用实际表的一部分数据；

  • 保证数据安全性。可以隐藏真正的表结构，比如比较重要的数据（如银行里的金额），只让人访问视图，没有权限使用真正的表；

  • 更改数据格式和表示。

1.创建视图：
create view v_stu_score_course as
select stu.name,stu.class,sc.score,cs.name as a from students stu
INNER JOIN scores sc on stu.studentNo=sc.studentNo
INNER JOIN courses cs on cs.courseNo=sc.courseNo;

2.使用视图：
select * from v_stu_score_course;

3.删除视图：
drop view v_stu_score_course;

5.事物

• 不能回退SELECT语句；也不能回退CREATE和DROP语句。

• MySQL 默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

• 通过 set autocommit=0 可以取消自动提交，直到 set autocommit=1 才会提交；autocommit 标记是针对每个连接而不是针对服务器的。

• 指令

  • START TRANSACTION - 指令用于标记事务的起始点。

  • SAVEPOINT - 指令用于创建保留点。

  • ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到 START TRANSACTION 语句处。

  • COMMIT - 提交事务。

  理解事务的过程与原理：

一、提交
两个命令行客户端 左边客户端 右边客户端
1、左边客户端：查询学生信息
select * from students;
2、右边客户端：开启事务，插入数据
begin;
insert into students(studentNo,name) values ('013','abc');
3、右边客户端：查询数据，此时有新增的数据
select * from students;
4、左边客户端：查询数据，发现并没有新增的数据
select * from students;
5、右边客户端：完成提交
commit;
6、左边客户端：查询，发现有新增的数据
select * from students;

二、回滚
两个命令行客户端 左边客户端 右边客户端
1、左边客户端：查询学生信息
select * from students;
2、右边客户端：开启事务，插入数据
begin;
insert into students(studentNo,name) values ('014','aaa');
3、右边客户端：查询数据，此时有新增的数据
select * from students;
4、左边客户端：查询数据，发现并没有新增的数据
select * from students;
5、右边客户端：回滚
rollback;
6、左边客户端：查询，发现没有新增的数据
select * from students;
7、右边客户端：查询，发现没有新增的数据
select * from students;

6.索引

• 作用

  • 通过索引可以更加快速高效地查询数据。

  • 用户无法看到索引，它们只能被用来加速查询。

• 注意

  • 更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

• 唯一索引

  • 唯一索引表明此索引的每一个索引值只对应唯一的数据记录。

创建测试表test_index
create table test_index(title varchar(10));

创建存储过程，在navicat查询中，执行下面sql语句
begin
declare i int default 0;
while i<100000 do
insert into test_index(title) values(concat('test',i));
set i=i+1;
end while;
end

调用存储过程，向表中添加数据：call proc_test()

开启运行时间监测：set profiling=1;

查找第1万条数据test10000：select * from test_index where title='test10000';

查看执行的时间：show profiles;

为title列创建索引：create index title_index on test_index(title(10));

执行查询语句：select * from test_index where title='test10000';

再次查看执行的时间：show profiles;

分析查询：
       explain
       select * from test_index where title='test10000'

7.外键

从表添加、修改数据时，受主表的约束；主表删除数据时，如果已经被从表依赖了，不能删除；
创建表时，对于主键和unique字段，自动创建索引
       create table create_index(
       id int primary key,
       name varchar(10) unique,
       age int,
       key (age)
       );

添加— alter table 从表名 add foreign key(从表字段) references 主表（主表字段）;
两个字段类型要保持一致。

查看— show create table 表名

删除— eg. alter table goods drop foreign key goods_ibfk_2

8.游标

• 游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。

• 在存储过程中使用游标可以对一个结果集进行移动遍历。

• 游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

• 使用游标的四个步骤：

  • 声明游标，这个过程没有实际检索出数据；

  • 打开游标；

  • 取出数据；

  • 关闭游标；

DELIMITER $
CREATE  PROCEDURE getTotal()
BEGIN
    DECLARE total INT;
    -- 创建接收游标数据的变量
    DECLARE sid INT;
    DECLARE sname VARCHAR(10);
    -- 创建总数变量
    DECLARE sage INT;
    -- 创建结束标志变量
    DECLARE done INT DEFAULT false;
    -- 创建游标
    DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;
    -- 指定游标循环结束时的返回值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    SET total = 0;
    OPEN cur;
    FETCH cur INTO sid, sname, sage;
    WHILE(NOT done)
    DO
        SET total = total + 1;
        FETCH cur INTO sid, sname, sage;
    END WHILE;

    CLOSE cur;
    SELECT total;
END $
DELIMITER ;

-- 调用存储过程
call getTotal();

9.触发器

触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。

可以使用触发器来进行审计跟踪，把修改记录到另外一张表中。

MySQL 不允许在触发器中使用 CALL 语句 ，也就是不能调用存储过程。

begin与end

当触发器的触发条件满足时，将会执行 BEGIN 和 END 之间的触发器执行动作。

new和old

MySQL 中定义了 NEW 和 OLD 关键字，用来表示触发器的所在表中，触发了触发器的那一行数据。

在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；

在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；

使用方法：NEW.columnName （columnName 为相应数据表某一列名）

1.创建触发器
CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON table_name
FOR EACH ROW
BEGIN
  trigger_statements
END;

trigger_name：触发器名
trigger_time: 触发器的触发时机。取值为 BEFORE 或 AFTER。
trigger_event: 触发器的监听事件。取值为 INSERT、UPDATE 或 DELETE。
table_name: 触发器的监听目标。指定在哪张表上建立触发器。
FOR EACH ROW: 行级监视，Mysql 固定写法，其他 DBMS 不同。
trigger_statements: 触发器执行动作。是一条或多条 SQL 语句的列表，列表内的每条语句
都必须用分号 ; 来结尾。

2.查看触发器
SHOW TRIGGERS;

3.删除触发器
DROP TRIGGER IF EXISTS trigger_insert_user;

10.密码与权限

修改密码

   Eg. update user set password = password(‘1234’);

       Flush privileges

   若忘记root的密码——

       Windows—my.ini à mysqld   skip-grant-tables（改完密码后删掉此句）

       Centos—my.cnf

   Mysql 日志开启：

       show variables like ‘general%’

       set global general_log = 1

权限控制

GRANT 和 REVOKE 可在几个层次上控制访问权限：整个服务器，使用 GRANT ALL 和 REVOKE ALL；整个数据库，使用 ON database.*；特定的表，使用 ON database.table；特定的列；特定的存储过程。

新创建的账户没有任何权限。账户用 username@host 的形式定义，username@% 使用的是默认主机名。创建账户并赋予权限：

MySQL 的账户信息保存在 mysql 这个数据库中。
USE mysql;
SELECT user FROM user;

创建账户
CREATE USER myuser IDENTIFIED BY 'mypassword';

修改账户名
UPDATE user SET user='newuser' WHERE user='myuser';
FLUSH PRIVILEGES;

删除账户
DROP USER myuser;

查看权限
SHOW GRANTS FOR myuser;

授予权限
GRANT SELECT, INSERT ON *.* TO myuser;

删除权限
REVOKE SELECT, INSERT ON *.* FROM myuser;

更改密码
SET PASSWORD FOR myuser = 'mypass';