SQL练习

最新推荐文章于 2023-02-16 18:50:30 发布

「已注销」

最新推荐文章于 2023-02-16 18:50:30 发布

阅读量113

点赞数

本文链接：https://blog.csdn.net/m0_51945027/article/details/113663567

版权

1. 创建表
	t_student(sno,sname,sex);	学生表
	t_course(cno,cname,credit);	课程表
	t_score(sno,cno,score);		成绩表

2. 录入数据
	insert into t_student values(1,'Mary','女');
	insert into t_student values(2,'Lucy','女');
	insert into t_student values(3,'Jack','男');
	insert into t_student values(4,'Tom','男');
	
	insert into t_course values
		(101,'数学',5),
		(102,'语文',4),
		(103,'英语',3);
	
	insert into t_score values(1,101,55);
	insert into t_score values(1,102,70);
	insert into t_score values(1,103,90);
	insert into t_score values(2,101,98);
	insert into t_score values(2,102,86);
	insert into t_score values(2,103,88);
	insert into t_score values(3,101,92);
	insert into t_score values(3,102,70);
	insert into t_score values(3,103,50);
	insert into t_score values(4,101,75);
	insert into t_score values(4,102,55);
	insert into t_score values(4,103,40);

==========================================================

问题1：
	显示完善的成绩信息，结果如下：
	
	+-------+--------+-------+
	| sname | cname  | score |
	+-------+--------+-------+
	| Mary  | 数学   |    55 |
	|...			 |
	| Tom   | 英语   |    40 |
	+-------+--------+-------+
	
----------------------------------------------------------------------------------------------
问题2：
	查询每门课的平均成绩并按从高到低排名，结果如下
	
	+--------+------------+
	| cname  | avg(score) |
	+--------+------------+
	| 数学   |    80.0000 |
	| 语文   |    70.2500 |
	| 英语   |    67.0000 |
	+--------+------------+

----------------------------------------------------------------------------------------------
问题3：
	查询学生 每门课成绩高于当前课程平均成绩的 成绩信息，结果如下：

	+-------+--------+-------+
	| sname | cname  | score |
	+-------+--------+-------+
	| Jack  | 数学   |    92 |
	| Lucy  | 数学   |    98 |
	| Lucy  | 语文   |    86 |
	| Lucy  | 英语   |    88 |
	| Mary  | 英语   |    90 |
	+-------+--------+-------+

----------------------------------------------------------------------------------------------
问题4：
	查询显示每个学生课程成绩的及格和不及格状态，结果如下：

	+-------+--------+-------+-----------+
	| sname | cname  | score | status    |
	+-------+--------+-------+-----------+
	| Jack  | 数学   |    92 | 及格      |
	| Jack  | 语文   |    70 | 及格      |
	| Jack  | 英语   |    50 | 不及格    |
	+-------+--------+-------+-----------+

----------------------------------------------------------------------------------------------
问题5：
	查询显示每门课及格和不及格人数，结果如下：

	+--------+--------+-----------+
	| cname  | 及格   | 不及格    |
	+--------+--------+-----------+
	| 数学   |      3 |         1 |
	| 语文   |      3 |         1 |
	| 英语   |      2 |         2 |
	+--------+--------+-----------+

----------------------------------------------------------------------------------------------
问题6：
	横向显示学生每门课的成绩，总成绩，平均成绩，结果如下

	+-------+--------+--------+--------+-----------+--------------+
	| sname | 数学   | 语文   | 英语   | 总成绩    | 平均成绩     |
	+-------+--------+--------+--------+-----------+--------------+
	| Jack  |     92 |     70 |     50 |       212 |      70.6667 |
	| Lucy  |     98 |     86 |     88 |       272 |      90.6667 |
	| Mary  |     55 |     70 |     90 |       215 |      71.6667 |
	| Tom   |     75 |     55 |     40 |       170 |      56.6667 |
	+-------+--------+--------+--------+-----------+--------------+

----------------------------------------------------------------------------------------------
问题7：
	查询显示学生每门课的成绩及名次，结果如下：

	+-------+--------+-------+---------+
	| sname | cname  | score | ranking |
	+-------+--------+-------+---------+
	| Lucy  | 数学   |    98 |       1 |
	| Lucy  | 语文   |    86 |       1 |
	| Mary  | 英语   |    90 |       1 |
	| Jack  | 数学   |    92 |       2 |
	| Lucy  | 英语   |    88 |       2 |
	| Jack  | 语文   |    70 |       3 |
	| Jack  | 英语   |    50 |       3 |
	| Mary  | 语文   |    70 |       3 |
	| Tom   | 数学   |    75 |       3 |
	| Mary  | 数学   |    55 |       4 |
	| Tom   | 语文   |    55 |       4 |
	| Tom   | 英语   |    40 |       4 |
	+-------+--------+-------+---------+
	
----------------------------------------------------------------------------------------------

* 数据库：
	* 数据存储仓库
	* 存放数据
		* io+file也可以实现数据永久存储(持久化)
	* 数据库可以通过编程实现数据库存取，比io+file存储方式更灵活，更方便，更安全，更有效。
* 数据库应用 (DBMS)：
	* 数据库服务端 ： 用来存放数据，提供对数据的各种操作功能(CRUD)
	* 数据库客户端 ： 与服务端交互，提供数据操作的需求
			  通过sql命令向数据库服务端表达操作需求。
			  cmd窗口，workbench，jdbc
* sql命令：
	* DDL 数据定义语言
		* create database/table/user , alter , drop
	* DML 数据操作语言
		* insert , update , delete
	* DCL 数据控制语言
		* grant 赋予权限
	* DQL 数据查询语言
		* select
	* TCL / TPL 事务控制语言
		* commit , rollback
		* 事务四种特性：
			* 原子性，隔离性，一致性，持久性
* 常见sql操作：
	* 建表
	* CRUD (增删改查)
	* 过滤，排序，分页，子查询，关联查询，分组
* sql应用：
	1. cmd登录数据
	2. 创建database 数据库区域
	3. 选择数据库区域
	4. 建表
	5. 录入数据
	6. 查询测试

* 问题1：
	* 要查询显示成绩
	* 就必须查询成绩表
	* 因为需要显示完善的数据(显示具体的学生名字和课程名字)
	* 成绩表中没有这些内容
	* 就需要从其他表中查询
	* 就需要关联查询。
		* 是否需要关联查询
		* 只需要看所需的数据来自于那些表。
	* 语句：
		select
			sname,
			cname,
			score
		from 
			t_score join t_student on t_score.sno=t_student.sno
				join t_course on t_score.cno=t_course.cno
	
	* 注意：
		* 两个表数据的连接
		* 默认效果是A表的每条记录与B表每条记录横向组成一条新纪录。
		A			B
		1  2  3			1 3 5
		4  5  6			4 6 8
		
		A join B
		1  2  3  1  3  5
		1  2  3  4  6  8
		4  5  6  1  3  5
		4  5  6  4  6  8

		记录数：笛卡尔积
	* 注意：
		两个表数据关联后
		还需要根据逻辑
			* 可以是根据业务分析的
			* 可以是人为规定。
			* 两个表的数据关系就像娱乐圈的绯闻一样。你说有就有，你说没有就没有。
		将没有关系的数据(不应该组成一行的数据)过滤掉
	* 注意：
		* 连接有两种
			* inner join 内连接
				* 只将有关联的数据连接
			* outer join 外连接
				* 有关联的数据连接
				* 关联数据以外的数据也需要。
				* 左外  left outer join
				* 右外  right outer join
* 问题2：
	* 每一个，各个这样的字眼，马上想到分组。
	* 因为是每门课，所以按照课程分组(课程名字，课程编号)
	* 无论按照谁分组，最终显示时需要显示具体的课程名字
	* 课程名与分数在不同的表中，需要关联查询。
		* 通过分析发现，很多问题中都需要关联
		* 所以可以使用视图。
			* 视图可以理解成一个查询语句的封装
			* 将一个经常使用，又比较复杂的查询语句可以封装成一个视图
			* 语法如下：
				create view v_score as 
					select
						sname,
						cname,
						score
					from 
						t_score join t_student on t_score.sno=t_student.sno
							join t_course on t_score.cno=t_course.cno

				select * from v_score ;
	* sql编码:
		select cname,avg(score) from v_score group by cname order by avg(score)  desc ;
* 问题3：
	* 需要使用每个学生成绩与其对应的平均成绩比较。
	* 哪来的平均成绩？
		需要先计算平均成绩(问题2结果)
	* 最终需要普通成绩与平均成绩比较
		普通成绩在v_score
		平均成绩在问题2查询的结果中
			查询结果也可以当成一张表(虚拟表，临时表)
	* 所以需要2个结果关联。
		select
			sname,
			v_score.cname,
			score,
			avgscore
		from 
			v_score join (select cname,avg(score) as avgscore from v_score group by cname order by avg(score)  desc ) as t
			             on v_score.cname = t.cname
		where 
			v_score.score >= t.avgscore;
* 问题4：
	* 显示时内容至少需要来自3张表
	* 显示时除了可知的sname，cname，score以外
	* 还需要多显示一个分数状态
	* 根据逻辑分析，这个分数状态需要根据分数，进行判断获得。
		* if() : 兼容问题
		* case-when-end
	select 
		sname,
		cname,
		score,
		if(score>=60,'及格','不及格') status
	from
		t_score sc inner join t_student st on sc.sno = st.sno
			   inner join t_course c  on sc.cno = c.cno ;
* 问题5：
	* 需要在问题4的基础上实现行列转换
		* 可以将问题4的sql包装成视图。
		create view v_score2 as select4
	* 在行列转换时，需要汇总结果，就需要分组，因为要每门课的信息
		select
			cname,
			sum( if(status='及格',1,0) ) 及格,
			sum( if(status='及格',0,1) ) 不及格
		from
			v_score2
		group by cname
	---------------------------------------
	* 基于问题4的查询结果
	* 首先进行分组
	* 分组后，脑补分组效果
		* 将某一个组中不及格数据过滤掉，剩余的数据count汇总
			select count(status) from v_score2 where status='及格'and cname = '数学';
		* 上述语句中，可以对某一门课及格记录count汇总
		* 但问题是，在整个查询显示过程中，怎么知道是哪一门课
			* 回顾：select查询的内部处理
				* 数据库表中有n条记录
				* 使用select查询时
				* 不是一下子查出来
				* 是一条一条查出来的
				* 查出一条，按照where过滤，符合条件保留，否则去掉。
			* 查询显示时，可以用当前这条记录的某一个字段为条件，配合子查询。
			select 
				cname,
				(select count(status) from v_score2 vi where status='及格' and vi.cname=vo.cname) 及格,
				(select count(status) from v_score2 vi where status='不及格' and vi.cname=vo.cname) 不及格
			from
				v_score2 vo
			group by cname
* 问题6：
	自行解决
* 问题7：
	* 先实现3张表的关联
	* 在sname，cname，score3个显示字段的基础上增加一个名次显示字段
	* 如何获得名次呢？
		* 基于select查询特点
		* 每查询获得一条记录，就可以想办法获得他的名次
		* 只需要以一条记录特点为类，算出名次。就可以获得所有名次了。
		* 假设某一个学生的成绩信息是
			Lucy 数学 98
		* 如何获得名次呢
			* 通过比较获得比Lucy数学分数高的其他同学的数学成绩
			* 比lucy数学成绩高的同学有几个，lucy就是+1那个名次。
			select count(*)+1 from v_score where sname != 'lucy' and cname='数学' and score > 98
select
	sname,
	cname,
	score,
	(
		select 
		  count(*)+1 
		from (
			select sname,cname,score
			from t_score sc1 inner join t_student st1 on sc1.sno=st1.sno
					 inner join t_course c1 on sc1.cno=c1.cno

		) t
		where 
		  t.sname != st.sname and t.cname=c.cname and t.score > sc.score
	) 名次
from
	t_score sc inner join t_student st on sc.sno = st.sno
		   inner join t_course c on sc.cno = c.cno
order by cname , 名次 asc ;