SQL终极基础思想训练+视图+索引

视图:CREATE VIEW 视图名 AS 查询语句

1.-- 查询前五条学生数据

SELECT * FROM student LIMIT 0,5;
CREATE VIEW stu_5 AS SELECT * FROM student LIMIT 0,5;

– 查询第三十五页(pageSize*(pageIndex-1))
解析:SQL中范围展示是不允许携带运算(SELECT * FROM student LIMIT (35-1)*5,5;)是不行的

SELECT * FROM student LIMIT 170,5;

2.-- 查询每个地区的学校信息

SELECT s.a_id,a.a_name,s.sc_id,s.sc_name FROM area a INNER JOIN school s ON a.a_id = s.a_id ;

– 分页查询 所有学生的 基本信息以及所属院校和所属地区 分页展示 每页10条 展示第1页

SELECT stu.*,sc_name,a_name FROM student stu 
INNER JOIN school s ON stu.sc_id = s.sc_id 
INNER JOIN area a ON s.a_id = a.a_id  LIMIT 0,10;

3.-- 查询语文考试的前十名成绩

SELECT s_name,su_name,r_score FROM `subject` s 
INNER JOIN result st ON s.su_id = st.su_id 
INNER JOIN student stu ON st.s_id = stu.s_id 
WHERE su_name = '语文' ORDER BY r_score ;

– 查询总分前十名的成绩
解析:成绩内连接科目表以su_id为外键，分组，每组相加排序，取前十条

SELECT r.s_id 学生考号,sum(r.r_score) 总分 FROM result r 
INNER JOIN `subject` s ON r.su_id = s.su_id 
GROUP BY r.s_id ORDER BY SUM(r.r_score) DESC LIMIT 0,10 ;

– 显示上面SQL的考生姓名

SELECT stu.s_name 姓名,r.s_id 学生考号,sum(r.r_score) 总分 FROM result r 
INNER JOIN `subject` s ON r.su_id = s.su_id 
INNER JOIN student stu ON r.s_id = stu.s_id 
GROUP BY r.s_id ORDER BY SUM(r.r_score) DESC LIMIT 0,10 ;

– 显示上面SQL的考生所在学校和所属地区（SQL标准写法）

SELECT
stu.s_name 姓名,
r.s_id 学生考号,
sum(r.r_score) 总分,
sc.sc_name 校名,
a.a_name 地区
FROM
result r
INNER JOIN `subject` s ON r.su_id = s.su_id
INNER JOIN student stu ON r.s_id = stu.s_id
INNER JOIN school sc ON stu.sc_id = sc.sc_id
INNER JOIN area a ON sc.a_id = a.a_id
GROUP BY
r.s_id
ORDER BY
SUM(r.r_score) DESC
LIMIT 0,
10;

4.-- 统计一共有多少人参加高考,过一本线(大于等于550分),二本线(大于等于450分),专科线(大于等级250分的)分别有多少人;

SELECT
COUNT(s_id) 总人数,
SUM(
	CASE
	WHEN za >= 550 THEN
		1
	ELSE
		0
	END
) 一本人数,
SUM(
	CASE
	WHEN za < 550
	AND za >= 450 THEN
		1
	ELSE
		0
	END
) 二本人数,
SUM(
	CASE
	WHEN za < 450
	AND za >= 250 THEN
		1
	ELSE
		0
	END
) 专科人数
FROM
(
	SELECT
		s_id,
		SUM(r_score) za
	FROM
		result r
	GROUP BY
		s_id
) a
;

5.-- 每个区参数考试人数

SELECT a_name 区,COUNT(总成绩) 参考人数 FROM zcj 
INNER JOIN student stu ON zcj.s_id = stu.s_id 
INNER JOIN school s ON stu.sc_id = s.sc_id
INNER JOIN area a ON s.a_id = a.a_id
GROUP BY a_name;

– 每个区的录取情况
解析:先求出每个区的参考人数，在求出比率，再求出各个分数线录取情况

 SELECT  FROM (SELECT s_id,sum(r_score) FROM result GROUP BY s_id)

SELECT 区,ROUND(参考人数/39137*100,1) 百分占比 FROM qckrs GROUP BY 百分占比 DESC;

– 索引
创建

CREATE INDEX 索引名 ON 表明(列明)

查看

SHOW INDEX FROM student

删除

DROP INDEX 索引名 on 表明(列明);