软件测试-学习记录-06-mysql的单表查询和多表查询,一些mysql函数的用法

本文链接：https://blog.csdn.net/qq_59747131/article/details/125781619

文章目录

前言
一、单表查询
1.sql:查询-万能公式
2.以ranzhi项目的成员列表（sys_user）为例，练习单表查询（表头如下）

二、多表查询
1. 用法
2.多表查询练习

三、一些mysql函数的用法

前言

使用mysql数据库练习

一、单表查询

1.sql:查询-万能公式

select 要查询的字段
from 要查询的表
where 要查询的条件
group by 分组
having 分组后过滤
order by 不加desc是升序（asc）,加desc是降序
limit 前几条
distinct 不重复的数据

2.以ranzhi项目的成员列表（sys_user）为例，练习单表查询（表头如下）

1.查询成员的所有记录

	select *
	from ranzhi.sys_user;
	# *表示所有字段
	# 这里如果选中了ranzhi数据库的话，则可以为"from sys_user;"省略ranzhi

2.查询编号2的记录

	select *
	from sys_user
	where id-2;

3.查询成员列表的编号，真实姓名，用户名

	select id,realname,account
	from ranzhi.sys_user;

4.查询真实姓名为‘张三’且性别为‘男’的成员的编号和用户名

	# and:且，A和B这两个条件要同时满足才成立
	select id,account
	from ranzhi.sys_user
	where realname='张三' and gender='m';

5.查询编号2，3，4这三个成员的编号及真实姓名

	# or:或   AorB:只有任意一个条件满足则成立
	select id,realname
	from ranzhi.sys_user
	where id=2 or id=3 or id=4;
	#in
	select id,realname
	from ranzhi.sys_user
	where id in(2,3,4);

6.查询编号2，3，4这三个成员的所有男生的编号及真实姓名

	# in
	select id,realname
	from ranzhi.sys_user
	where id in(2,3,4) and gender='m';
	#
	select id,realname
	from ranzhi.sys_user
	where (id=2 or id=3 or id=4) and gender='m';

7.查询编号2以外的所有成员的编号

	# !=,<> ：不等于
	select id,realname
	from ranzhi.sys_user
	where id!=2;

	select id,realname
	from ranzhi.sys_user
	where id<>2;

	select id,realname
	from ranzhi.sys_user
	where not id=2;

8.查询所有姓张的编号及真实姓名

	# %:模糊查询
	select id,realname
	from ranzhi.sys_user
	where realname like 张%';

9.查询所有姓张且后面跟两个字的人的编号及真实姓名

	# _:一个_代表一个字符
	select id,realname
	from ranzhi.sys_user
	where realname like '张__';

10.几个常用函数

	#count() :统计个数 统计所有字段的行数
		select count(*)
		from sys_user;
	# max(id):字段id中最大的编号
		select max(id)
		from sys_user;
	# min(id) :字段id中最小的编号
		select min(id)
		from sys_user;
	# sum(id) :字段id中编号之和
		select sum(id)
		from sys_user;
	# avg(id) :字段id中编号平均值
		select avg(id)
		from sys_user;
	# round() :保留几位小数
		select round(avg(id),1)
		from sys_user;

11.分别统计男女的人数

	select  gender,count(*)
	from sys_user
	where gender<>'u'
	group by gender;

12.分别统计男女的人数,其中人数少于5个的性别不用显示

	SELECT gender,COUNT(*) rs
	FROM sys_user
	WHERE gender <> 'u'
	GROUP BY gender
	HAVING rs >= 5;

13.分别统计男女的人数,其中人数少于2个的性别不用显示,并按照人数从多到少排序显示

	SELECT gender,COUNT(*) rs
	FROM sys_user
	WHERE gender <> 'u'
	GROUP BY gender
	HAVING rs >= 2
	ORDER BY rs DESC;

14.统计男女的人数,人数少于2个的性别不用统计,只显示人数最多的性别及其人数

	SELECT gender,COUNT(*) rs
	FROM sys_user
	WHERE gender <> 'u'
	GROUP BY gender
	HAVING rs >= 2
	ORDER BY rs DESC
	LIMIT 1;

二、多表查询

1. 用法

1.1正常连接
- 1.1.1

	select 字段名
	from 表名1，表名2
	where 表名1.相应字段名=表名2.相应字段名;

- 1.1.2 join on

	select 字段名
	from 表名1  join 表名2 on  表名1.相应字段名=表名2.相应字段名
	where 条件;

1.2左链接(左边的表为主体，左边的字段有，右边的字段没有，则右边的字段为null)

	select 字段名
	from 表名1 left join 表名2 on  表名1.相应字段名=表名2.相应字段名
	where 条件;

3.右链接(左边的表为主体，左边的字段没有，右边的字段有，则左边的字段为null)

	select 字段名
	from 表名1 right join 表名2 on  表名1.相应字段名=表名2.相应字段名
	where 条件;

2.多表查询练习

2.1表数据

	USE test;
	
	CREATE TABLE test.Student(Sid VARCHAR(10),Sname VARCHAR(10),Sage DATETIME,Ssex NVARCHAR(10));
	INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男');
	INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');
	INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男');
	INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');
	INSERT INTO Student VALUES('05' , '孙风' , '1991-12-01' , '男');
	INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女');
	INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
	INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '女');
	
	CREATE TABLE test.Course(Cid VARCHAR(10),Cname VARCHAR(10),Tid VARCHAR(10));
	INSERT INTO Course VALUES('01' , '语文' , '02');
	INSERT INTO Course VALUES('02' , '数学' , '01');
	INSERT INTO Course VALUES('03' , '英语' , '03');
	
	CREATE TABLE test.Teacher(Tid VARCHAR(10),Tname VARCHAR(10));
	INSERT INTO Teacher VALUES('01' , '张三');
	INSERT INTO Teacher VALUES('02' , '李四');
	INSERT INTO Teacher VALUES('03' , '王五');
	
	CREATE TABLE test.SC(Sid VARCHAR(10),Cid VARCHAR(10),score DECIMAL(18,1));
	INSERT INTO SC VALUES('01' , '01' , 80);
	INSERT INTO SC VALUES('01' , '02' , 90);
	INSERT INTO SC VALUES('01' , '03' , 99);
	INSERT INTO SC VALUES('02' , '01' , 70);
	INSERT INTO SC VALUES('02' , '02' , 60);
	INSERT INTO SC VALUES('02' , '03' , 80);
	INSERT INTO SC VALUES('03' , '01' , 80);
	INSERT INTO SC VALUES('03' , '02' , 80);
	INSERT INTO SC VALUES('03' , '03' , 80);
	INSERT INTO SC VALUES('04' , '01' , 50);
	INSERT INTO SC VALUES('04' , '02' , 30);
	INSERT INTO SC VALUES('04' , '03' , 20);
	INSERT INTO SC VALUES('05' , '01' , 76);
	INSERT INTO SC VALUES('05' , '02' , 87);
	INSERT INTO SC VALUES('06' , '01' , 31);
	INSERT INTO SC VALUES('06' , '03' , 34);
	INSERT INTO SC VALUES('07' , '02' , 89);
	INSERT INTO SC VALUES('07' , '03' , 98);

2.2练习

	# 1) 查询科目1比科目2成绩高的所有学生的姓名
		SELECT s.`Sname`
		FROM Student s,SC s1,SC s2
		WHERE s.`Sid`=s1.`Sid` AND 
			s.`Sid`=s2.`Sid` AND
			 s1.`Cid`='01' AND 
			 s2.`Cid`='02' AND 
			 s1.`score` > s2.`score`;
	
	# 2) 查询所有的姓名及其总分；
		SELECT  s.`Sname`,SUM(c.`score`)
		FROM Student s LEFT JOIN SC c
			ON s.`Sid`=c.`Sid`
		GROUP BY  s.Sid;
	
	# 3) 查询学过李四老师课程的所有学生的姓名
		SELECT s.`Sname`
		FROM Student s,SC ,Course,Teacher
		WHERE s.`Sid`=SC.`Sid` AND
			SC.`Cid`=Course.`Cid` AND
			Course.`Tid`=Teacher.`Tid` AND
			Teacher.`Tname`='李四';
	
	# 4) 查询没有学过张三老师课程的所有学生的姓名
		SELECT s.`Sname`
		FROM Student s,SC ,Course,Teacher
		WHERE s.`Sid`=SC.`Sid` AND
			SC.`Cid`=Course.`Cid` AND
			Course.`Tid`=Teacher.`Tid` AND
			Teacher.`Tname`<>'张三'
			GROUP BY s.`Sid`;
	
	# 5) 查询科目1的课程名称及其平均分
		SELECT Course.`Cname`,AVG(SC.`score`)
		FROM Course,SC
		WHERE SC.`Cid`='01' AND
			Course.`Cid`=SC.`Cid`;
	
	# 6) 查询语文成绩最高分的学生姓名及其语文成绩
		SELECT st.`Sname`,SC.`score`
		FROM Student st,SC,Course co
		WHERE st.`Sid`=SC.`Sid` AND co.`Cid`=SC.`Cid`
			AND SC.`score`=
				(SELECT MAX(SC.`score`)
				 FROM Course co,SC
				 WHERE co.`Cid`=SC.`Cid` AND
					co.`Cname`='语文');
	
	# 7) 查询同名同性别的学生姓名及其人数
		SELECT Sname,COUNT(*)
		FROM Student
		GROUP BY Sname,Ssex
		HAVING COUNT(*)>1;
	
	# 8) 查询1990年出生的所有学生的姓名
		SELECT	Student.`Sname`
		FROM Student
		WHERE Student.`Sage` LIKE '1990%';
	
	# 9) 查询1990年出生的所有学生的姓名及总分
		SELECT	Student.`Sname`,SUM(SC.`score`)
		FROM Student LEFT JOIN SC
		ON Student.`Sid`=SC.`Sid`
		WHERE Student.`Sage` LIKE '1990%'
		GROUP BY Student.`Sid`;

三、一些mysql函数的用法

1.year(date) 截取年份
2.case when 条件判断
- 2.1 用法

	case + 字段 + when +判断的条件 + then +条件成立的结果  +  when+判断的条件 + then +条件成立的结果 + else 前面不成立的结果 end
	
	case when 判断 then 结果 when  判断 then 结果  else 结果 end

2.2练习

	# 查询每门课的编号，课程名称以及课程在分数段[90,100],(80,90),[70,80],(60,70),[0,60]的人数
	/*
	cid cname [90,100] (80,90) [70,80] (60,70) [0,60]
	01  语文    1        2          2    1       0
	02  数学    1        2          2    1       0
	03  英语    1        2          2    1       0
	*/
	# case when 条件判断
	SELECT co.cid,co.cname,
	SUM(CASE WHEN sc.score BETWEEN 90 AND 100 THEN 1 ELSE 0 END) AS '[90,100]',
	SUM(CASE WHEN sc.score < 90 AND sc.score > 80 THEN 1 ELSE 0 END) AS '(80,90)',
	SUM(CASE WHEN sc.score BETWEEN 70 AND 80 THEN 1 ELSE 0 END) AS '[70,80]',
	SUM(CASE WHEN sc.score < 70 AND sc.score > 60 THEN 1 ELSE 0 END) AS '(60,70)',
	SUM(CASE WHEN sc.score <= 60 THEN 1 ELSE 0 END) AS '[0,60]'
	FROM course co,sc
	WHERE co.cid = sc.cid
	GROUP BY sc.cid ;
	
	SELECT SUM(90>80)
	FROM DUAL ;
	SELECT co.cid,co.cname,
	SUM(sc.score BETWEEN 90 AND 100) AS '[90,100]',
	SUM(sc.score < 90 AND sc.score > 80) AS '(80,90)',
	SUM(sc.score BETWEEN 70 AND 80) AS '[70,80]',
	SUM(sc.score < 70 AND sc.score > 60) AS '(60,70)',
	SUM(sc.score <= 60) AS '[0,60]'
	FROM course co,sc
	WHERE co.cid = sc.cid
	GROUP BY sc.cid ;

3.limit用法练习
- 3.1 用法

	limit 1 # 表示限制只能一个数据
	limit 2,4 # 表示从第3个数据开始，输出4个数据

3.2 练习

	#查询1990年出生的所有学生的姓名和总分 且按总分从高到低的顺序排序
	#student  sc
	SELECT st.sname,SUM(score)
	FROM student st LEFT JOIN sc
	ON st.sid=sc.sid
	WHERE YEAR(st.sage)=1990
	GROUP BY sc.sid
	ORDER BY SUM(score) DESC;
	
	# 查询选修所有课程且平均分排第2的学生姓名及其平均分
	-- 1 2 3 4 -> a b c -> b
	# limit 1,1
	SELECT st.sname,AVG(sc.score) pjf
	FROM student st,sc
	WHERE st.sid = sc.sid
	GROUP BY sc.sid
	HAVING COUNT(sc.cid) = (SELECT COUNT(*) FROM course)
	ORDER BY pjf DESC
	LIMIT 1,1 ;
	
	# 请问在 MySQL 数据库中怎么实现翻页查找功能？ limit
	# a表中总共有69条记录，每页显示10条，查询第3页的第5,6,7,8 这4条记录（sql语句实现)
	/*
	1-10
	11-20
	25 26 27 28
	*/
	SELECT * FROM a LIMIT 24,4 ;