数据库SQL语句练习-打卡第一篇

创建

• ⭐创建db_student数据库⭐

CREATE DATABASE db_student CHARACTER SET='utf8';

• ⭐创建student表，数据自填⭐

CREATE TABLE student(
	sno CHAR(7) PRIMARY KEY COMMENT '学号',
	sname CHAR(10) NOT NULL COMMENT '姓名',
	ssex 	ENUM('男','女'),
	sage SMALLINT,
	sdept VARCHAR(20)  
);

• ⭐创建course表⭐

CREATE TABLE course(
	cno CHAR(10) PRIMARY KEY COMMENT '课程号',
	cname VARCHAR(20) NOT NULL COMMENT '课程名',
	ccredit SMALLINT,
	semster SMALLINT,
	period SMALLINT
);

• ⭐创建sc表⭐

CREATE TABLE sc(
	sno CHAR(10),
	cno CHAR(10),
	grade SMALLINT,
	PRIMARY KEY(sno,cno),
	FOREIGN KEY(sno) REFERENCES student(sno),
	FOREIGN KEY(cno) REFERENCES course(cno)
);

查询

• ⭐查询全体学生的信息⭐

SELECT *FROM student

• ⭐查询“信息系”学生的学号，姓名和年龄⭐

SELECT sno,sname,sage FROM student
WHERE sdept='信息系';

• ⭐查询考试不及格的学生的学号⭐

SELECT sno FROM sc
WHERE grade<60;

• ⭐查询无考试成绩的学生的学号和相应的课程号⭐

SELECT sno,cno FROM sc
WHERE grade is NULL;

• ⭐将学生按年龄升序排序⭐

排序 :
ORDER BY sage ASC 按照年龄升序
ORDER BY sage DESC 按照年龄降序

SELECT *FROM student
ORDER BY sage ASC  

• ⭐查询选修了课程的学生的学号和姓名⭐

（1）连接查询：

SELECT DISTINCT sc.sno,sname FROM student,sc 
WHERE student.sno=sc.sno;

（2）嵌套子查询：

SELECT sno,sname  FROM student
WHERE sno IN(
	SELECT DISTINCT sno FROM sc
)

（3）嵌套子查询：

SELECT sno,sname FROM student 
WHERE sno= ANY(
	SELECT DISTINCT sno FROM sc
);

• ⭐查询年龄在19-20岁之间的学生的系，姓名，年龄，按照系升序排序⭐

SELECT sdept,sname,sage FROM student
WHERE sage BETWEEN 19 AND 20
ORDER BY sdept 

SELECT sdept,sname,sage FROM student
WHERE sage>=19 AND sage<=20
ORDER by sdept;

• ⭐查询选修了“计算机网络”或者“数据库基础”课程的学生的学号，姓名⭐

（1）连接查询：

SELECT DISTINCT  student.sno,sname 
FROM student,sc,course
WHERE student.sno=sc.sno AND course.cno=sc.cno
	AND (cname='计算机网络' OR cname='数据库基础');

（2）嵌套子查询：

SELECT sno,sname FROM student WHERE sno IN(
	SELECT sno FROM sc WHERE cno IN(
		SELECT cno FROM course 
		WHERE cname='计算机网络' OR cname='数据库基础')
);

• ⭐查询选修了“计算机网络”和“数据库基础”课程的学生的学号⭐

SELECT sno FROM sc,course  
WHERE sc.cno=course.cno AND cname='计算机网络'
AND sno IN(
	SELECT sno FROM sc,course 
	WHERE sc.cno=course.cno AND cname='数据库基础'
	);

SELECT sno FROM student 
WHERE NOT EXISTS(
	SELECT * FROM course 
	WHERE cname IN ('计算机网络','数据库基础')
	AND NOT EXISTS(
		SELECT * FROM sc 
		WHERE sno=student.sno 
			AND cno =course .cno
		)
	);

• ⭐查询姓“暖”的学生的基本信息⭐

SELECT *FROM student WHERE sname LIKE '暖%';

• ⭐查询学生的选课情况，要求输出学号，姓名，课程门数，课程名列表（用逗号分隔），按照学号升序排序⭐

SELECT student.sno,sname,COUNT(*) as 选修课程数,
GROUP_CONCAT(cname ORDER BY cname SEPARATOR',')as 课程列表
FROM student,sc,course
WHERE student.sno=sc.sno AND sc.cno=course.cno
GROUP BY student.sno
ORDER BY student.sno;

• ⭐查询选修了课程的学生的总人数⭐

（1）嵌套子查询的谓词EXISTS

SELECT COUNT(DISTINCT sno)'选课人数' FROM sc 
WHERE  EXISTS (SELECT sno FROM sc);

（2）嵌套子查询的谓词IN

SELECT COUNT(DISTINCT sno)'选课人数' FROM sc 
WHERE sno IN (SELECT sno FROM sc);

• ⭐统计各门课程选修人数，要求输出课程代号，课程名，选修人数，有成绩人数（grade不为NULL），最高分，最低分，平均分，按课程号排序⭐

SELECT course.cno,cname,
COUNT(*) as 选修人数,
COUNT(grade)  as 有成绩人数,
MAX(grade) as 最高分,
MIN(grade) as 最低分,
AVG( grade) as 平均分
FROM course,sc WHERE course.cno=sc.cno
GROUP BY course.cno
ORDER BY course.cno;

• ⭐统计各门课程的重修人数（包括grade为NULL），要求输出课程代号，重修人数⭐

SELECT cno,COUNT(*)FROM sc 
WHERE grade<60 OR grade IS NULL
GROUP BY cno;

• ⭐查询选修通过2门以上的学生的信息，输出学号、选修通过数、平均成绩，按门数降序排序，若门数相同，按照成绩降序⭐

SELECT sno,COUNT(*)  as 人数,
AVG(grade)  as 平均成绩
FROM sc 
WHERE  grade>=60
GROUP BY sno
HAVING COUNT(*)>=3
ORDER BY COUNT(*)DESC,AVG(grade) DESC;  # 🌂

• ⭐查询与“暖羊羊”同一个系的学生的基本信息⭐

SELECT *FROM student 
WHERE sname !='暖羊羊' AND sdept IN(
	SELECT DISTINCT sdept FROM student 
	WHERE sname='暖羊羊'
	);

• ⭐查询每个学生高于他自己选修平均分的那门课程的成绩，输出学号，课程号，课程成绩，他所有课程的平均分，并按学号升序排列⭐

SELECT sc.sno,cno,grade, avg_p FROM sc,
(SELECT sno, AVG(grade) avg_p FROM sc GROUP BY sno) AS avg      # 🌂
WHERE sc.sno=avg.sno AND sc.grade>avg.avg_p
ORDER BY sc.sno;

• ⭐查询没有同时选修“计算机导论”和“计算机网络”两门课的学生的学号，姓名⭐

SELECT sno,sname 
FROM student
WHERE sno not in(
	SELECT sno 
	FROM sc,course 
	WHERE sc.cno=course.cno 
	AND cname='计算机导论'
	)
OR sno NOT in(
	SELECT sno 
	FROM sc,course
	WHERE sc.cno=course.cno
	AND cname='计算机网络'
	);

• ⭐查询选修了全部课程的学生的学号，姓名，系名⭐

SELECT sno,sname,sdept FROM student
WHERE sno in(
	SELECT sno FROM sc 
	GROUP BY sno 
	HAVING COUNT(*)=(SELECT COUNT(*) FROM course  )    # 🌂
    );

• ⭐输出“高等数学”成绩前三名的学生的学号，姓名，系名⭐

SELECT student.sno,sname,sdept 
FROM student,sc,course
WHERE student.sno=sc.sno 
	AND sc.cno=course.cno 
	AND cname='高等数学'
ORDER BY grade DESC LIMIT 3 ;      # 🌂

• ⭐查询选修通过门数超过1门，且平均分在70以上的学生成绩信息⭐

SELECT sno,COUNT(*),AVG(grade) FROM sc
WHERE grade>60
GROUP BY sno
HAVING COUNT(*)>=2 AND AVG(grade)>70    # 🌂
ORDER BY 3 DESC;