软件测试人员常用的sql语句（二）

MySQL数据库

1. 数据准备

学生表（students）

• 创建学生表（students）并向表中插入数据

/*
如果学生表students存在，就删除学生表students
*/
DROP TABLE IF EXISTS students;

/* 
创建学生表students
*/
CREATE TABLE students(
	studentNo VARCHAR(10) PRIMARY KEY,
	`name` VARCHAR(10),
	sex VARCHAR(1),
	hometown VARCHAR(20),
	age TINYINT UNSIGNED,
	class VARCHAR(10),
	card VARCHAR(20)
);

/*
向学生表students中插入数据
*/
INSERT INTO students VALUES
('001', '王昭君', '女', '北京', '30', '1班','110101199003157654'),
('002', '诸葛亮', '男', '上海', '29', '2班','310102199104262354'),
('003', '张飞', '男', '南京', '30', '3班', '320102199003047654'),
('004', '白起', '男', '安徽', '35', '4班', '340202198605177654'),
('005', '大乔', '女', '天津', '28', '3班', '120101199204067654'),
('006', '孙尚香', '女', '河北', '25', '1班','130502199506137654'),
('007', '百里玄策', '女', '山西', '39', '2班','140102198107277654'),
('008', '小乔', '女', '河南', '25', '3班', 'null'),
('009', '百里守约', '男', '湖南', '31', '1班', ''),
('010', '妲己', '女', '广东', '24', '2班', '440701199607147654'),
('011', '李白', '男', '北京', '30', '4班', '110202199005017754'),
('012', '孙膑', '男', '新疆', '36', '3班', '650102198401297655');

2. 别名与重复记录

2.1 查询所有字段

SELECT * FROM students;

2.2 查询指定字段

SELECT name, sex FROM students;

2.3 字段的别名

• 通过 字段 as 别名 给字段起一个别名
• as可以省略

SELECT name as 姓名 FROM students;
SELECT name 姓名 FROM students;

2.4 表的别名

• 通过 表名 as 别名 给表起一个别名
• as可以省略

SELECT name 姓名 FROM students as stu;
SELECT name 姓名 FROM students stu;

2.5 distinct过滤重复记录

• 通过select distinct 字段名， 字段名 from 表名 来过滤select查询结果中重复的记录

SELECT DISTINCT sex, class from students;

3. 条件查询

3.1 where子句

• where 后面跟一个条件，实现一个有选择的查询
• select * from 表名 where 条件;

-- 例1：在students表中查找age小于30的记录
SELECT * FROM students WHERE age < 30;
-- 例2：在students表中查找age等于30的name和clas两个字段的记录
SELECT name, class from students WHERE age = '30';

3.2 select查询的基本规律

• select * 或者select 字段名 控制了查询返回什么样的字段（列）
• where 条件 控制了查询返回什么样的记录 （行）

3.3 比较运算符

• < 小于 / <= 小于等于 / > 大于 / >= 大于等于 / !=和<> 不等于

SELECT name, class from students WHERE age > '30';
SELECT age from students WHERE name = '小乔';
SELECT * FROM students WHERE age < 30;
SELECT * FROM students WHERE hometown != '北京';
SELECT card FROM students WHERE studentNo = '007';
SELECT * FROM students WHERE class != '1班';

3.4 逻辑运算符

• and 并且
  • 条件1 and 条件2
  • 两个添加同时满足
• or 或
  • 条件1 or 条件2
  • 两个条件只要满足一个即可
• not 非
  • not 条件
  • 条件成立，not以后就不成立；条件不成立，not以后条件成立；

SELECT * FROM students WHERE sex = '女' AND age < 30;
SELECT * FROM students WHERE sex = '女' OR class = '1班';
SELECT * FROM students WHERE NOT hometown = '天津';
SELECT * FROM students WHERE NOT hometown != '天津';
SELECT * FROM students WHERE hometown = '河南'
 OR hometown = '河北';
SELECT * FROM students WHERE hometown = '北京' 
AND class = '1班';
SELECT * FROM students WHERE not age = 30;

3.5 模糊查询

• like实现模糊查询
• %代表任意多个字符
• _代表任意一个字符
• 字段名like ‘字符%’

- 指定字符开始后面任意多个字符
SELECT * from students WHERE name like '孙%';
SELECT * from students WHERE name like '孙_';
SELECT * from students WHERE name like '%乔';
SELECT * from students WHERE name like '%白%';
SELECT * from students WHERE name like '%白%';
SELECT * from students WHERE name like '白%'
 AND age > 30;
SELECT * from students WHERE studentNo like '%1';

3.6 范围查找

• in （值，值，值）
  • 非连续范围查找
• between 开始值 and 结束值
  • 连续范围查找，包含 开始值 包含 结束值

SELECT * FROM students WHERE hometown in 
('北京', '上海', '广东');
SELECT * FROM students WHERE age BETWEEN 25 AND 30;
SELECT * FROM students WHERE age in (20, 25, 30) AND 
sex = '女';

3.7 空判断

• null不是0，也不是‘ ’，null在sql中代表空，什么也没有
• null不能用比较运算符的判断
• is null （是否为null）
• is not null （是否不为null）

## 不可以使用字段名 = null或者字段名!= null
select * from students where card is not null;
select * from student where card is null;

3.8 where 子句可以用在update和delete语句的后面

update students set class = '2班' where age = 25 and 
name = '孙尚香';
delete from students where (age between 20 and 30) and
sex = '男';

4. order by（排序）

4.1 升序排列

• 语法：order by 字段 asc

SELECT * FROM students ORDER BY age ASC;

4.2 降序排列

• 语法：order by 字段 desc

SELECT * FROM students ORDER BY age DESC;

4.3 字段排序中的优先级

• 字段排序是有优先级的（当年龄相同时按学号排序）
• 语法：select * from 表名 order by 字段1，字段2;

SELECT * FROM students ORDER BY age DESC, studentNo ASC;

4.4 当一条select语句出现了where和order by时

• 语法：select * from 表名 where 条件 order by 字段1，字段2;

SELECT * FROM students where sex = '男' ORDER BY 
age DESC, studentNo ASC;

5. 聚合函数

5.1 count 求select返回的记录总数

• count(字段名/*)

SELECT count(name) from students;
SELECT count(sex) from students WHERE sex = '女';
SELECT count(*) from students;
SELECT count(DISTINCT class) FROM students;

5.2 查询最大值

• max(字段名)
• max(字段)求此字段中最大值
• 聚合函数不能用到where后面的条件里

SELECT MAX(age) FROM students;
-- 查找students表中女生最大的年龄
SELECT max(age) FROM students WHERE sex = '女';

5.3 查询最小值

• min(字段名)
• 查询指定字段的最小值

SELECT min(age) FROM students;
SELECT MIN(age) FROM students WHERE sex = '女';

5.4 sum求和

• sum(字段名)
• 指定字段的值求和

SELECT sum(age) from students;
SELECT sum(age) from students WHERE sex = '女';
SELECT sum(age) from students WHERE class = '1班';

5.5 avg求平均数

• avg(字段名)
• 指定字段的平均值
• avg的字段中如果有null值，null不作为分母计算平均值

SELECT avg(age) from students;
SELECT avg(age) FROM students WHERE sex = '男';

-- 课堂练习
-- 练习1：查询所有学生的最大年龄、最小年龄、平均年龄
select max(age), min(age), avg(age) from students;
-- 练习2：查询一班共有多少个学生
select count(*) from students where class = '1班';
-- 练习3：查询3班中年龄小于30岁的同学有几个
select count(*) from students where class = '3班' and age < 30;

6. 数据分组

6.1 分组

• group by 字段名
• 语法：select 聚合函数 from 表名 where 条件 group by 字段
• select 聚合函数 from 表名 group by 字段
• group by 就是配合聚合函数使用的

select count(*) from students where sex = '男';
select count(*) from students where sex = '女';
select sex, count(*) from students group by sex;
SELECT age, count(*) from students GROUP BY age;

• where 与 group by（先where后group by）

-- 练习：用数据分组方法，统计各个班级学生总数、平均年龄、最大年龄和最小年龄
select class, count(*), avg(age), max(age), min(age) from students group by class
-- 练习：统计各个班级学生总数、平均年龄、最大年龄和最小年龄，但不统计3班，统计结果按班级名称从大到小排序
SELECT class, count(*), avg(age), max(age), min(age) from students WHERE class != '3班' GROUP BY class ORDER BY class DESC;

• where 和 group by 和 order by 的顺序
• select * from 表名 where 条件 group by 字段 order by 字段

6.2 分组聚合之后的数据筛选

• having 子句
• 总是出现在group by之后
• select * from 表名 group by 字段 having 条件

-- 用where查询男生总数
-- where先筛选符合条件的记录，然后在聚合统计
select count(*) from students where sex = '男';

-- 用having查询男生总数
-- having先分组聚合统计，在统计的结果中筛选
select count(*) from students group by sex having sex = '男';

6.3 having配合聚合函数的使用

• where后面条件不能使用聚合函数，having可以使用聚合函数

-- 求班级人数大于3人的班级
SELECT class, count(*) from students GROUP BY class having count(*) > 3;

6.4 对比having与where筛选的区别

• where是对from后面表的原始数据进行筛选
• having是对group by 之后已经分过组的数据进行筛选
• having可以使用聚合函数，where不可以使用聚合函数

-- 练习：查询班级总人数大于2人的班级名称以及班级对应的总人数
select class, count(*) from students group by class having count(*) > 2;
-- 练习：查询平均年龄大于30岁的班级名称和班级总人数
select class, count(*), avg(age) from students group by class having avg(age) > 30;  

7. 数据分页显示

• m 每页显示多少条记录
• n 第几页
• (n-1)*m , n
• 把计算的结果卸载limit后面

-- 每页显示4条记录，第三页的结果
select * from students limit 8, 4;
-- 每页显示4条记录，第二页的结果
select * from students limit 4, 4;

• 已知每页记录数，求一张表需要几页显示完
  • 求总页数
  • 总页数/每页显示的记录数
  • 如果结果是整数，那么就是总页数，如果结果有小数，那么就在结果的整数上+1