软件测试基础篇三之数据库

操作数据前一定要记得先备份！！！

一、数据库

测试人员要掌握：
1.数据库校验（查询）
2、适当准备和清理数据

1、存储数据的仓库

2、分为关系型数据库和非关系型数据库。

关系型数据库：数据库表形式存储在表格
Oracle：在大型项目中使用，例如：银行、电信等项目。收费
MySQL：Web 项目中使用最广泛的关系型数据库
Microsoft SQL Server：在微软的项目中使用
SQLite：轻量级数据库，主要应用在移动平台

非关系型数据库：键-值对 存储
例如：Redis、Mongodb 、Hbase

3、关系型数据库的核心要素
数据行(一条记录)、数据列(字段)、数据表(数据行的集合)、数据库(数据表的集合)

4、SQL语言

DQL:数据查询语言，查询
DML:数据操作语言，增，改，删除
DDL:数据定义语言，数据库，数据表

二、MySQL

客户端连接mysql两种方式：

1、命令行模式连接

前置条件：
通过ifconfig确认数据库ip地址
确认mysql数据库服务开启netsat -anptu |grep 3306

连接命令：mysql –h host –u 用户名 –p 密码 –P端口号
host表示服务器的IP地址或者域名,不填则表示为本机。-P端口号不加时则表示默认3306端口

2、可视化工具navicat连接(重点)

（1）数据库

常见数据库操作命令：
查看所有数据库：show databases;
使用数据库：use 数据库名;
查看当前使用数据库：select database();
创建数据库：create database 数据库名 charset=utf8;
删除数据库：drop database 数据库名;

（2）数据表：切换到设计表 ctrl+d

查看当前数据库所有的表：show tables;
查看表结构：desc 表名;
查看表的创建语句：show create table 表名;

navicat查看表创建语句ddl ---选中表-对象属性

（3）数据类型与约束


（4）数据库的备份与还原

注意：要还原的数据库不存在时，需要先创建数据库。

三、命令行客户端（知道）

1、命令行操作数据库

2、命令行操作数据库表

四、SQL语句

1.知道数据库表的创建、删除操作
2.掌握数据的增删改查
3.掌握数据的各种查询方法

1、数据库表操作（知道）

2、数据更新操作

（1）添加一行语句（重点）

– 主键自增长，可以用0或Null代替

insert into 表名 values(…)
insert into students values(0, ‘亚瑟’, 22, 177.56);
insert into 表名(字段1,字段2,…) values(值1,值2, …)
insert into students(name) values (‘老夫子’);

（2）添加多行数据（知道）

（3）修改数据（知道）

（4）删除数据（知道）

（5）简单查询（重点）

select * from 表名;
select * from students;

3、数据查询操作

（1）数据准备（操作）

drop table if exists students;
create table students (
studentNo varchar(10) primary key,
name varchar(10),
sex varchar(1),
hometown varchar(20),
age tinyint(4),
class varchar(10),
card varchar(20)
);
insert into students values
('001', 'ሴญށ','ঀ' ,' ۹Ղ', '20', '1ቔ', '340322199001247654'),
('002', '᧘៭Մ', 'ካ', 'Ӥၹ', '18', '2ቔ', '340322199002242354'),
('003', 'ୟᷢ', 'ካ', 'ܖՂ', '24', '3ቔ', '340322199003247654'),
('004', 'ጮ᩸', 'ካ', 'ਞ஝', '22', '4ቔ', '340322199005247654'),
('005', 'य़ԗ', 'ঀ', 'ॠၐ', '19', '3ቔ', '340322199004247654'),
('006', 'ਃ੢ḕ', 'ঀ', 'မ۹', '18', '1ቔ', '340322199006247654'),
('007', 'ጯ᯾ሱᒽ', 'ካ', 'ઊᥜ', '20', '2ቔ', '340322199007247654'),
('008', 'ੜԗ', 'ঀ', 'မܖ',' 15',' 3ቔ', null),
('009', 'ጯ᯾ਝᕅ', 'ካ', 'დܖ',' 21',' 1ቔ', ''),
('010', 'ঞ૩', 'ঀ', 'ଠӳ', '26', '2ቔ', '340322199607247654'),
('011', '๫ጮ', 'ካ', '۹Ղ', '30', '4ቔ', '340322199005267754'),
('012', 'ਃᚿ', 'ካ', 'ෛዅ', '26', '3ቔ', '340322199000297655')

（2）数据的基本操作（知道）

（3）条件查询（重点）

语法格式

比较运算符（>、<、=、>=、<=、！=）

逻辑运算符（and、or、not）

模糊查询（%任意、_单个）

范围查询(in、between.and)

为空判断(is null)

（4）排序（知道）

（5）分组和聚合（知道）

聚合

分组

（6）分页查询（知道）

4、连接查询（知道）

（1）概述

• 内连接: 连接两个表时，取的是两个表中都存在的数据。（取交集）
• 左连接:连接两个表时，取的是左表中特有的数据，对于右表中不存在的数据，用null来填充。
• 右连接：连接两个表时，取的是右表中特有的数据，对于左表中不存在的数据，用null来填充
  
  
  
  数据准备：

drop table if exists courses;
create table courses (
courseNo int(10) unsigned primary key auto_increment,
name varchar(10)
);
insert into courses values ('1', '数据库'), ('2', 'qtp'), ('3', 'linux'),
('4', '系统测试'), ('5', '单元测试'), ('6', '测试过程');
drop table if exists scores;
create table scores (
id int(10) unsigned primary key auto_increment,
courseNo int(10),
studentno varchar(10),
score tinyint(4)
);
insert into scores values ('1', '1', '001', '90'), ('2', '1', '002', '75'),
('3', '2', '002', '98'),('4', '3', '001', '86'),('5', '3', '003', '80'),
('6', '4', '004', '79'),('7', '5', '005', '96'),('8', '6', '006', '80');

• 案例

-- 例1： 查询学生信息及学生的成绩
select * from students inner join scores on students.studentNo = scores.studentNo;
-- students起别名为stu, scores起别名为sc
select * from students as stu inner join scores as sc on stu.studentNo = sc.studentNo;
-- 起别名可以不写as
select * from students stu inner join scores sc on stu.studentNo = sc.studentNo;
select * from students stu, scores sc where stu.studentNo = sc.studentNo;
-- 例2： 查询课程信息及课程的成绩
select * from courses cs inner join scores sc on cs.courseNo = sc.courseNo;
-- 例3： 查询王昭君的成绩， 要求显示姓名、 课程号、 成绩
select stu.name, sc.courseNo, sc.score from students stu inner join scores sc on
stu.studentNo = sc.studentNo where stu.name = '王昭君';
-- 1、 查询学生信息及学生的课程对应的成绩
select * from students
inner join scores on students.studentNo=scores.studentNo
inner join courses on scores.courseNo=courses.courseNo
-- 2、 查询所有学生的数据库成绩， 要求显示姓名、 课程名、 成绩
select students.name, courses.name, scores.score from students
inner join scores on students.studentNo=scores.studentNo
inner join courses on scores.courseNo=courses.courseNo
-- 3、 查询王昭君的数据库成绩， 要求显示姓名、 课程名、 成绩
select students.name, courses.name, scores.score from students
inner join scores on students.studentNo=scores.studentNo
inner join courses on scores.courseNo=courses.courseNo where students.name='王昭君'
-- 4、 查询男生中最高成绩， 要求显示姓名、 课程名、 成绩
select students.name, courses.name, scores.score from students
inner join scores on students.studentNo=scores.studentNo
inner join courses on scores.courseNo=courses.courseNo
where students.sex='男'
order by scores.score desc
limit 0, 1 -- 等价于 limit 1

（2）内链接（重点）

（3）左链接

#### （4）右链接

5、自关联（知道）

（1） 介绍

• 自连接的应用场景
  省、市、区的信息，一般不会分开放在不同的表里面进行存储，而是放在同一个表当中。
  
• 数据准备

drop table if exists areas;
create table areas(aid int primary key, atitle varchar(20),pid int);
insert into areas values ('130000', '河北省', NULL), ('130100', '石家庄市','130000'),
('130400','邯郸市','130000'), ('130600', '保定市', '130000'),('130700','张家口市', '130000'),
('130800', '承德市', '130000'),('410000', '河南省', NULL), ('410100', '郑州市', '410000'),
('410300', '洛阳市', '410000'),('410500', '安阳市', '410000'),('410700', '新乡市', '410000'),
('410800', '焦作市', '410000'),('410101', '中原区', '410100'),('410102', '二七区', '410100'),
('410301', '洛龙区', '410300');

（2）实现

6、子查询（知道）

• 将一条SQL查询的语句嵌入在其他的SQL语句中，被嵌入的SQL语句称之为子查询，其他的SQL称之为主查询
• 子查询select语句，要么是充当条件，要么充当数据源
• 子查询语句是一条完整的select语句，且可以单独执行
  

（1）子查询充当条件

-- 例1：查询王昭君的成绩，要求显示成绩（标量子查询）
select studentNo from students where name='王昭君';
select * from scores where studentNo=(select studentNo from students where name='王昭君');
-- 例2：查询18岁的学生的成绩，要求显示成绩（列子查询）
select studentNo from students where age=18;
select score from scores where studentNo in (select studentNo from students where age=18);
-- 例3：查询和王昭君同班、同龄的学生信息（行子查询）
select class, age from students where name='王昭君';
select * from students where (class, age)=(select class, age from students where name='王昭
君');

（2）子查询充当数据源

-- 例1：查询数据库和系统测试的课程成绩
select * from scores as sc inner join (
select * from courses where name in ('数据库','系统测试')
) as co on sc.courseNo=co.courseNo;

-- 例2：查询18岁的学生的成绩，要求显示成绩（列子查询）
select studentNo from students where age=18;
select score from scores where studentNo in (select studentNo from students where age=18);
-- =any和in等价
select score from scores where studentNo = any(select studentNo from students where age=18);
-- SOME:是ANY的别称,很少用
select score from scores where studentNo = some(select studentNo from students where age=18);
-- != all 和 not in 等价
select score from scores where studentNo not in (select studentNo from students where age=18);
select score from scores where studentNo != all(select studentNo from students where age=18);

练习：

-- 1、 查询大于平均年龄的学生
select avg(age) from students;
select * from students where age > (select avg(age) from students);
-- 2、 查询年龄在18-20之间的学生的成绩
select * from students where age between 18 and 20;
select * from scores as sc inner join (
select * from students where age between 18 and 20
) as stu on sc.studentNo=stu.studentNo;

7、练习

练习1：

• 数据准备

/*
**创建部门表
*/
drop table if exists departments;
create table departments (
deptid int(10) primary key,
deptname varchar(20) not null -- 部门名称
);
insert into departments values ('1001', '市场部');
insert into departments values ('1002', '测试部');
insert into departments values ('1003', '开发部');
/*
**创建员工表
*/
drop table if exists employees;
create table employees (
empid int(10) primary key,
empname varchar(20) not null, -- 姓名
sex varchar(4) default null, -- 性别
deptid int(20) default null, -- 部门编号
jobs varchar(20) default null, -- 岗位
politicalstatus varchar(20) default null, -- 政治面貌
leader int(10) default null
);
insert into employees values ('1', '王昭君', '女', '1003', '开发', '群众', '9');
insert into employees values ('2', '诸葛亮', '男', '1003', '开发经理', '群众', null);
insert into employees values ('3', '张飞', '男', '1002', '测试', '团员', '4');
insert into employees values ('4', '白起', '男', '1002', '测试经理', '党员', null);
insert into employees values ('5', '大乔', '女', '1002', '测试', '党员', '4');
insert into employees values ('6', '孙尚香', '女', '1001', '市场', '党员', '12');
insert into employees values ('7', '百里玄策', '男', '1001', '市场', '团员', '12');
insert into employees values ('8', '小乔', '女', '1002', '测试', '群众', '4');
insert into employees values ('9', '百里守约', '男', '1003', '开发', '党员', '9');
insert into employees values ('10', '妲己', '女', '1003', '开发', '团员', '9');
insert into employees values ('11', '李白', '男', '1002', '测试', '团员', '4');
insert into employees values ('12', '孙膑', '男', '1001', '市场经理', '党员', null);
/*
**创建工资表
*/
drop table if exists salary;
create table salary (
sid int(10) primary key,
empid int(10) not null,
salary int(10) not null -- 工资
);
insert into salary values ('1', '7', '2100');
insert into salary values ('2', '6', '2000');
insert into salary values ('3', '12', '5000');
insert into salary values ('4', '9', '1999');
insert into salary values ('5', '10', '1900');
insert into salary values ('6', '1', '3000');
insert into salary values ('7', '2', '5500');
insert into salary values ('8', '5', '2000');
insert into salary values ('9', '3', '1500');
insert into salary values ('10', '8', '4000');
insert into salary values ('11', '11', '2600');
insert into salary values ('12', '4', '5300');

-- 1、列出总人数大于4的部门号和总人数。(要统计所有部门的人数，需要使用分组, 同时也要使用聚合函数)
select deptid, count(*) from employees group by deptid having count(*)>4;
-- 2、列出开发部和和测试部的职工号、姓名
select deptid from departments where deptname in ('开发部','测试部');
select empid, empname from employees where deptid in (
select deptid from departments where deptname in ('开发部','测试部')
);
-- 3、求出各部门党员的人数，要求显示部门名称。
select * from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
where emp.politicalstatus='党员';
select dep.deptname from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
where emp.politicalstatus='党员';
select dep.deptname from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
where emp.politicalstatus='党员'
group by emp.deptid;
-- 4、列出市场部的所有女职工的姓名和政治面貌。
select * from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
where emp.sex='女' and dep.deptname='市场部';
select emp.empname, emp.politicalstatus from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
where emp.sex='女' and dep.deptname='市场部';
-- 5、显示所有职工的姓名、部门名和工资数。
select * from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid;
select emp.empname, dep.deptname, sa.salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid;
-- 6、显示各部门名和该部门的职工平均工资。
select dep.deptname,avg(sa.salary) from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
group by emp.deptid;
-- 7、显示工资最高的前3名职工的职工号和姓名。
select emp.empname, dep.deptname, sa.salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
order by sa.salary desc limit 3 -- limit 3 等价于 limit 0, 3
-- 8、列出工资在1000－2000之间的所有职工姓名。
select emp.empname, dep.deptname, sa.salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
where sa.salary between 1000 and 2000;
-- 9、列出工资比王昭君高的员工。(首先查询王昭君的工资)
-- 查询王昭君的工资
select sa.salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
where emp.empname='王昭君'
select emp.empname, sa.salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
where sa.salary > (
select sa.salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
where emp.empname='王昭君'
);
-- 10、列出每个部门中工资小于本部门平均工资的员工信息。(首先查询出每个部门的平均工资)
-- 查询出每个部门的平均工资
select emp.deptid, avg(sa.salary) as avg_salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
group by emp.deptid
select emp.deptid, emp.empname, sa.salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
inner join (
select emp.deptid, avg(sa.salary) as avg_salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
group by emp.deptid
) as c on emp.deptid=c.deptid
where sa.salary < c.avg_salary;

练习2：

补充：linux更新数据库密码的命令

连接服务器，进入数据库中操作

对于MySQL 5.7.6及更高版本：
sql

ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘NEW_PASSWORD’;

命令详解：

• ALTER USER: 这是SQL命令的开始，它告诉MySQL服务器你要修改一个用户账号。
• ‘root’@‘localhost’: 这部分指定了要修改的用户账号。在这里，你正在修改名为root的用户，该用户只能从localhost（即MySQL服务器本身）进行连接。如果你想要修改可以从任何主机连接的用户，你可以使用’root’@‘%’。
• IDENTIFIED BY: 这个关键词后面跟的是新密码，它告诉MySQL服务器你希望为这个用户设置的新密码是什么。
• ‘NEW_PASSWORD’: 这里NEW_PASSWORD是你想要设置的新密码。在实际使用中，你应该替换NEW_PASSWORD为你实际想要设置的密码。

对于MySQL 5.7.5及更早版本：
sql

SET PASSWORD FOR ‘root’@‘localhost’ = PASSWORD(‘NEW_PASSWORD’);