小试牛刀【MySQL】练习

学习完了MySQL我们来练习一下吧。

题目描述：

Mysql中现有四张表分别为学生表、课程表、成绩表、教师表。表结构如下

学生表student：

字段	类型	说明
sid	Varchar(10)	学号
sName	Varchar(20)	姓名
sAge	datetime	年龄
sGender	Varchar(10)	性别

课程表course：

字段	类型	说明
cid	Varchar(10)	课程编号
cName	Varchar(20)	课程名称
tid	Int(20)	授课老师id

成绩表sc：

字段	类型	说明
sid	Varchar(10)	学生学号
cid	Varchar(20)	课程编号
score	Int(10)	分数

教师表Teacher：

字段	类型	说明
tid	Int(10)	教师id
tName	Varchar(20)	教师姓名

请按照以下要求完成需求：

1.在mysql数据库中分别创建四张表，并添加数据建表sql学员自行创建，添加数据sql已经给出，请参考init.sql。

//创建学生表
CREATE TABLE student(
  sid  VARCHAR(10) ,
	sName VARCHAR(20),
	sAge  DATETIME,
	sSex VARCHAR(10)
	);
	
	
//创建课程表
CREATE TABLE course(
cid VARCHAR(10),
cName VARCHAR(20),
tid INT(20)
);


//创建成绩表
CREATE TABLE sc(
  sid VARCHAR(10),
	cid VARCHAR(20),
	score INT(10)
	);


//创建教师表
CREATE TABLE Teacher(
	tid INT(10),
	 tName VARCHAR(20)
	);

//给各表添加数据
INSERT INTO student(sid,sName,sAge,sSex) VALUES ('1001','张三丰',STR_TO_DATE('1980-10-12 23:12:36','%Y-%m-%d %H:%i:%s'),'男');
INSERT INTO student(sid,sName,sAge,sSex) VALUES ('1002','张无极',STR_TO_DATE('1995-10-12 23:12:36','%Y-%m-%d %H:%i:%s'),'男');
INSERT INTO student(sid,sName,sAge,sSex) VALUES ('1003','李奎',STR_TO_DATE('1992-10-12 23:12:36','%Y-%m-%d %H:%i:%s'),'女');
INSERT INTO student(sid,sName,sAge,sSex) VALUES ('1004','李元宝',STR_TO_DATE('1980-10-12 23:12:36','%Y-%m-%d %H:%i:%s'),'女');
INSERT INTO student(sid,sName,sAge,sSex) VALUES ('1005','李世明',STR_TO_DATE('1981-10-12 23:12:36','%Y-%m-%d %H:%i:%s'),'男');
INSERT INTO student(sid,sName,sAge,sSex) VALUES ('1006','赵六',STR_TO_DATE('1986-10-12 23:12:36','%Y-%m-%d %H:%i:%s'),'男');
INSERT INTO student(sid,sName,sAge,sSex) VALUES ('1007','田七',STR_TO_DATE('1981-10-12 23:12:36','%Y-%m-%d %H:%i:%s'),'女');



INSERT INTO course(cid,cName,tid)
VALUES ('001','企业管理','3'),('002','马克思','3');
INSERT INTO course(cid,cName,tid)
VALUES ('004','数据库','1'),('005','英语','1');


INSERT INTO sc(sid,cid,score)
VALUES('1001','001',80);
INSERT INTO sc(sid,cid,score)
VALUES('1001','002',60);
INSERT INTO sc(sid,cid,score)
VALUES('1001','003',70);
INSERT INTO sc(sid,cid,score)
VALUES('1002','001',85);
INSERT INTO sc(sid,cid,score)
VALUES('1002','002',70);
INSERT INTO sc(sid,cid,score)
VALUES('1003','004',90);
INSERT INTO sc(sid,cid,score)
VALUES('1003','001',90);
INSERT INTO sc(sid,cid,score)
VALUES('1003','002',99);
INSERT INTO sc(sid,cid,score)
VALUES('1004','002',65);
INSERT INTO sc(sid,cid,score)
VALUES('1004','003',50);
INSERT INTO sc(sid,cid,score)
VALUES ('1005','005',80);
INSERT INTO sc(sid,cid,score) 
VALUES ('1005','004',70);
INSERT INTO sc(sid,cid,score) 
VALUES ('1003','003',10);
INSERT INTO sc(sid,cid,score) 
VALUES('1003','005',10);


INSERT INTO teacher(tid,tName) 
VALUES (1,'李老师'),(2,'何以琛'),(3,'叶平');

 

1.查询“001”课程比“002”课程成绩高的所有学生的学号;

-- 查询“001”课程比“002”课程成绩高的所有学生的学号;
SELECT s.sid
FROM(
SELECT sid,score AS score_001
FROM sc
WHERE cid = '001'
)s
JOIN(
SELECT sid,score AS score_002
FROM
sc
WHERE cid = '002'
)s1 ON s.sid =s1.sid
WHERE
s.score_001 > s1.score_002;

2.查询平均成绩大于60分的同学的学号和平均成绩;

查询平均成绩大于60分的同学的学号和平均成绩;
SELECT 
a.sid,
avg
FROM
student a,(
SELECT
sid,
AVG(score)AS avg
FROM
sc 
GROUP BY sid
HAVING AVG(score) >60
) c
WHERE a.sid = c.sid;

3.查询所有同学的学号、姓名、选课数、总成绩;

--4)	查询所有同学的学号、姓名、选课数、总成绩;
SELECT a.sid,a.sName,COUNT(b.cid),SUM(score)
FROM student a
LEFT JOIN 
    sc b ON a.sid = b.sid
GROUP BY a.sid;

4.查询姓“李”的老师的个数;

--查询姓“李”的老师的个数;
SELECT
 COUNT(*) AS '姓李的老师个数'
FROM
teacher
WHERE 
tName LIKE '李%';

5.查询没学过“叶平”老师课的同学的学号、姓名;

-- 查询没学过“叶平”老师课的同学的学号、姓名;
SELECT sid,sName
FROM
student a
WHere sid NOT IN (
SELECT DISTINCT sc.sid FROM sc WHERE sc.cid in (
SELECT cid FROM course c ,teacher t WHERE c.tid = t.tid AND tName = "叶平")
);

6.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

-- 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
SELECT sid,sName FROM student WHERE sid IN (
SELECT t1.sid FROM 
   (SELECT *FROM sc WHERE cid ='001')t1,
	 (SELECT * FROM sc WHERE cid ='002')t2
	 WHERE t1.sid = t2.sid AND t1.score>t2.score);

7.查询没有学全所有课的同学的学号、姓名;

--	查询没有学全所有课的同学的学号、姓名;
SELECT a.sid,a.sName,COUNT(b.cid)AS "科目数"
FROM
student a
LEFT JOIN sc b ON a.sid = b.sid
GROUP BY a.sid
HAVING
COUNT(b.cid)<4;

8.按各科平均成绩从低到高和及格率的百分数从高到低排序

-- 按各科平均成绩从低到高和及格率的百分数从高到低排序
SELECT  
    cid,  
    AVG(score) AS "平均成绩",  
    SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS "及格率百分比"  
FROM  
    sc 
GROUP BY  
    cid  
ORDER BY  
    "平均成绩" ASC,  
    "及格率百分比" DESC;

9.查询同名同性学生名单，并统计同名人数

-- 查询同名同性学生名单，并统计同名人数
SELECT *,COUNT(1)
FROM student
GROUP BY sName,sSex
HAVING COUNT(1)>1;

10.查询每门功课成绩最好的前两名

-- 查询每门功课成绩最好的前两名
SELECT cid,score
FROM
sc 
WHERE (SELECT COUNT(1)
FROM sc AS s
WHERE sc.cid = s.cid
AND sc.score <s.score)<2
ORDER BY cid,score DESC;

11.检索至少选修两门课程的学生学号

-- 	检索至少选修两门课程的学生学号
SELECT sid AS "学号"
FROM
sc 
GROUP BY sid
HAVING
COUNT(cid)>=2;