categories

Mysql50句练习下

前言: 故不积跬步，无以至千里

安装地址

https://www.runoob.com/mysql/mysql-install.html

scrpit:

#!/bin/sh

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

rpm -ivh mysql-community-release-el7-5.noarch.rpm

sudo yum update -y
sudo yum install mysql-server -y

sudo chown mysql:mysql -R /var/lib/mysql

mysqld --initialize

systemctl start mysqld

systemctl status mysqld

sql 答案参考这里

 25、查询各科成绩前三名的记录
 26、查询每门课程被选修的学生数 
 27、查询出只有两门课程的全部学生的学号和姓名 
 28、查询男生、女生人数 
 29、查询名字中含有"风"字的学生信息
 30、查询同名同性学生名单，并统计同名人数 
 31、查询1990年出生的学生名单
 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 
 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 
 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数 
 35、查询所有学生的课程及分数情况； 
 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数； 
 37、查询不及格的课程
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名； 
 39、求每门课程的学生人数 
 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
 42、查询每门功成绩最好的前两名 
 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  
 44、检索至少选修两门课程的学生学号 
 45、查询选修了全部课程的学生信息 
46、查询各学生的年龄
 47、查询本周过生日的学生
 48、查询下周过生日的学生
 49、查询本月过生日的学生
 50、查询下月过生日的学生

– 26、查询每门课程被选修的学生数

SELECT
	cs.*,
	COUNT( DISTINCT s_id ) 
FROM
	Course cs
	LEFT JOIN Score sc ON cs.c_id = sc.c_id 
GROUP BY
	sc.c_id

– 27、查询出只有两门课程的全部学生的学号和姓名

SELECT
	st.* 
FROM
	Student st,
	Score sc 
WHERE
	st.s_id = sc.s_id 
GROUP BY
	s_id 
HAVING
	COUNT( c_id ) = 2

– 28、查询男生、女生人数

SELECT
	COUNT( s_id ) 
FROM
	Student 
WHERE
	s_sex = '男' UNION ALL
SELECT
	COUNT( s_id ) 
FROM
	Student 
WHERE
	s_sex = '女'

select st.s_sex,count(1) from Student st group by st.s_sex
这样的写法会更好，对student进行分类，再进行求count聚合

– 29、查询名字中含有”风”字的学生信息

SELECT
	* 
FROM
	Student 
WHERE
	s_name LIKE '%风%'

– 30、查询同名同性学生名单，并统计同名人数

SELECT
	a.*,
	COUNT( a.s_id ) AS '同名同性人数' 
FROM
	Student a,
	Student b 
WHERE
	a.s_name = b.s_name 
	AND a.s_id <> b.s_id 
	AND a.s_sex = b.s_sex

select st.*,count(1) from Student st group by st.s_name,st.s_sex having count(1)>1
答案处用的还是分组的思想，我用的是比较的方法，结果相同，需要分析性能

– 31、查询1990年出生的学生名单

SELECT
	* 
FROM
	Student 
WHERE
	s_birth LIKE '1990-%%-%%' --

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT
	c_id,
	avg( s_score ) AS avg 
FROM
	Score 
GROUP BY
	c_id 
ORDER BY
	avg DESC

– 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT
	st.*,
	AVG( sc.s_score ) AS avg 
FROM
	Student st,
	Score sc 
WHERE
	st.s_id = sc.s_id 
GROUP BY
	sc.s_id 
HAVING
	avg > 85

– 34、查询课程名称为”数学”，且分数低于60的学生姓名和分数

SELECT
	st.*,
	sc.s_score 
FROM
	Student st,
	Course cs,
	Score sc 
WHERE
	st.s_id = sc.s_id 
	AND cs.c_id = sc.c_id 
	AND c_name = '数学' 
	AND s_score < 60

– 35、查询所有学生的课程及分数情况；

SELECT
	* 
FROM
	( SELECT * FROM Student st, Course cs ) a
	LEFT JOIN Score sc ON a.s_id = sc.s_id 
	AND a.c_id = sc.c_id

– 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

SELECT
	st.s_name,
	cs.c_name,
	sc.s_score 
FROM
	Student st,
	Score sc,
	Course cs 
WHERE
	s_score > 70 
	AND st.s_id = sc.s_id 
	AND sc.c_id = cs.c_id 
GROUP BY
	st.s_id

– 37、查询不及格的课程(存疑)

SELECT
	cs.* 
FROM
	Course cs,
	Score sc 
WHERE
	cs.c_id = sc.c_id 
	AND s_score < 60 
-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
SELECT
	st.s_id,
	st.s_name 
FROM
	Student st,
	Score sc 
WHERE
	st.s_id = sc.s_id 
	AND sc.c_id = '01' 
	AND sc.s_score >= 80

– 39、求每门课程的学生人数

SELECT
	cs.c_name,
	COUNT( sc.s_id ) AS count 
FROM
	Course cs
	LEFT JOIN Score sc ON sc.c_id = cs.c_id 
GROUP BY
	sc.c_id

– 40、查询选修”张三”老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT
	st.*,
	sc.s_score 
FROM
	Teacher tc,
	Score sc,
	Course cs,
	Student st 
WHERE
	st.s_id = sc.s_id 
	AND tc.t_id = cs.t_tid 
	AND cs.c_id = sc.c_id 
	AND tc.t_name = '张三' 
ORDER BY
	sc.s_score DESC 
	LIMIT 1

– 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT
	* 
FROM
	Score a,
	Score b 
WHERE
	a.s_id = b.s_id 
	AND a.c_id <> b.c_id 
	AND a.s_score = b.s_score 
GROUP BY
	a.s_id

– 42、查询每门功成绩最好的前两名

SELECT
	* 
FROM
	( SELECT * FROM Score WHERE c_id = '01' ORDER BY s_score DESC LIMIT 2 ) a UNION ALL
SELECT
	* 
FROM
	( SELECT * FROM Score WHERE c_id = '02' ORDER BY s_score DESC LIMIT 2 ) b UNION ALL
SELECT
	* 
FROM
	( SELECT * FROM Score WHERE c_id = '03' ORDER BY s_score DESC LIMIT 2 ) c 
```  
难道每次都要自己去设置好010203？  
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT
a.*
FROM
( SELECT c_id, COUNT( s_id ) AS count FROM Score GROUP BY c_id HAVING count >= 5 ORDER BY c_id DESC ) AS a,
( SELECT c_id, COUNT( s_id ) AS count FROM Score GROUP BY c_id HAVING count >= 5 ORDER BY count DESC ) AS b
WHERE
a.c_id = b.c_id

1	-- 44、检索至少选修两门课程的学生学号

SELECT
s_id
FROM
Score
GROUP BY
s_id
HAVING
COUNT( s_score ) >= 2

1	-- 45、查询选修了全部课程的学生信息

SELECT
st.*
FROM
Student st,
Score sc
WHERE
st.s_id = sc.s_id
GROUP BY
sc.s_id
HAVING
COUNT( s_score ) >= 3

1
2
3


-- 46、查询各学生的年龄
`

SET @dt = DATE_FORMAT( NOW(), ‘%Y-%m-%d’ ); –设置当前时间为dt
SELECT
s_name,
TIMESTAMPDIFF(
YEAR,
s_birth,
DATE_FORMAT( NOW(), ‘%Y-%m-%d’ ))
FROM
Student;

1	-- 47、查询本周过生日的学生

– 获取一周的日子

SET @bd = DAYOFWEEK( @dt );

SET @startmonth = MONTH (
DATE_ADD( @dt, INTERVAL - @bd + 1 DAY ));

SET @startday = DAY (
DATE_ADD( @dt, INTERVAL - @bd + 1 DAY ));

SET @endmonth = MONTH (
DATE_ADD( @dt, INTERVAL 7 - @bd DAY ));

SET @endday = DAY (
DATE_ADD( @dt, INTERVAL 7 - @bd DAY ));
SELECT
Student.*
FROM
Student
WHERE
MONTH ( s_birth ) >= @startmonth
AND MONTH ( s_birth ) <= @endmonth AND DAY ( s_birth ) >= @startday
AND DAY ( s_birth ) <= @endday

1	-- 48、查询下周过生日的学生

SET @bd = DAYOFWEEK( @dt );

SET @startmonth = MONTH (
DATE_ADD( @dt, INTERVAL 7 - @bd + 1 DAY ));

SET @startday = DAY (
DATE_ADD( @dt, INTERVAL 7 - @bd + 1 DAY ));

SET @endmonth = MONTH (
DATE_ADD( @dt, INTERVAL 15 - @bd DAY ));

SET @endday = DAY (
DATE_ADD( @dt, INTERVAL 15 - @bd DAY ));
SELECT
Student.*
FROM
Student
WHERE
MONTH ( s_birth ) >= @startmonth
AND MONTH ( s_birth ) <= @endmonth AND DAY ( s_birth ) >= @startday
AND DAY ( s_birth ) <= @endday

1	-- 49、查询本月过生日的学生

SELECT
st.*
FROM
Student st
WHERE
MONTH ( @dt ) = MONTH ( st.s_birth )

1	-- 50、查询下月过生日的学生

SELECT
st.*
FROM
Student st
WHERE
MONTH (
DATE_FORMAT( NOW(), ‘%Y-%m-%d’ )) + 1 = MONTH ( st.s_birth );
````