测试技能提升HM-数据库

数据库简介

数据库：
指长期存储在计算机内、有组织的数据集合
数据库的分类
关系型数据库：
oracle：大型项目中使用，例如：银行、电信项目
MySQL：web项目中使用最广泛的关系型数据库
microsoft SQL Server:在微软中项目使用
SQLite：轻量级数据库，主要引用在移动平台
非关系型数据库：
redis
mongodb
hbase

关系型数据库：表格方式展示

非关系型数据库：以键值对方式展示

关系型数据库核心要素和SQL分类

关系型数据库核心要素：
数据行
数据列
数据表
数据库

SQL的分类：SQL是一个结构化的查询语言，通过SQL能够对数据库进行相关的操作
DQL：数据查询语言，用于对数据进行查询，例如：select
data query language
DML:数据操作语言，对数据进行增加、修改、删除。例如： insert̵ update̵ delete
data manipulation language
TPL事务处理语言，对事务进行处理：begin transaction̵ commit̵ rollback
transaction processing language
DCL：数据控制语言，进行授权与权限回收： grant̵ revoke
data control language
DDL数据定义语言，进行数据库，表的管理等，create̵ drop
data definition language
CCL：指针控制语言，通过控制指针完成表的操作： declare cursor
cursor control language

MySQL

MySQL组成和命令连接数据库

MySQL的组成
MySQL服务器服务器：
存储数据并解析编译后的SQWL语句，将执行结果返回给客户端
MySQL客户端：
下发用户要执行的SQL语句，并显示服务器返回的执行结果
命令连接MySQL数据库：
前置条件：确定MySQL数据库ip地址
可以通过ifconfig来确认
确认MySQL数据库服务是否开启：
netstat -anptu |grep 3306
连接命令：

mysql - 数据库ip - P 端口号 -u 数据库登录用户名 -p 数据库登录密码

-h 不加时则表示为本机
-P 不加时则默认端口为3306

工具连接数据库

navicate操作数据库

新建数据库

编辑或修改数据库

刷新

通过navicate操作表

新建表


修改、删除表

通过navicate操作数据

数据类型与约束

数据约束

主键（primary key）:物理上存储的顺序
外键（foreign key）:维护两个表之间的关联关系

数据库的备份和还原

备份：


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

命令行客户端

前置条件：
命令行先连接数据库
mysql -uroot -p123456

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

命令行操作数据库表

SQL语句

数据库表操作

创建数据库表的语法：

创建学生表：

删除数据库表

数据操作

简单查询

添加一行数据

添加多行数据

修改数据

注意：修改数据时一定要添加条件，否则修改的是所有记录的值

删除数据

数据查询操作

查询的基本操作

查询部分字段的值

取别名

去重

条件查询

语法

条件查询：比较运算符

条件查询：逻辑运算符

条件查询：模糊查询

条件查询：范围查询
in：查询非连续范围内的数据

between…and ：查询连续范围内的数据（用来数值型字段中）


条件查询：为空判断
null 和空字符串不一样
空判断：is null

非空判断：is not null

排序

语法：

分组和聚合

聚合函数

count（*）

分组查询

分页查询

连接查询

内连接

左连接：

右连接：

数据准备：

内连接

左连接



右连接

自关联

自关联实现

子查询

将一条SQL语句嵌入其他的SQL语句中，被嵌入的SQL语句称为自子查询，其他的SQL称为主查询

子查询充当条件

子查询充当数据源

子查询-特定关键字



案例练习：
数据准备：

/*
**创建部门表
*/
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