-- 建表
-- 课程表
DROP TABLE IF EXISTS course;
CREATE TABLE course (
cid VARCHAR(10) NOT NULL,
cname VARCHAR(10) DEFAULT NULL,
tid INT(20) DEFAULT NULL,
PRIMARY KEY (cid)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 成绩表
DROP TABLE IF EXISTS sc;
CREATE TABLE sc (
sid VARCHAR(10) NOT NULL,
cid VARCHAR(10) NOT NULL,
score INT(10) DEFAULT NULL,
PRIMARY KEY (sid,cid)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 学生表
DROP TABLE IF EXISTS student;
CREATE TABLE student (
sid VARCHAR(10) NOT NULL,
sname VARCHAR(20) DEFAULT NULL,
sage DATETIME DEFAULT ‘1980-10-12 23:12:36‘,
ssex VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (sid)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 教师表
DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher (
tid INT(10) DEFAULT NULL,
tName VARCHAR(10) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入数据
-- 课程表
INSERT INTO course(cid,cname,tid) VALUES (‘001‘,‘企业管理‘,3),(‘002‘,‘马克思‘,3),(‘003‘,‘java‘,2),(‘004‘,‘python‘,1),(‘005‘,‘英语‘,1);
-- 成绩表
INSERT INTO sc(sid,cid,score) VALUES (‘1001‘,‘001‘,73),(‘1001‘,‘002‘,63),(‘1001‘,‘003‘,75),(‘1001‘,‘004‘,56),(‘1001‘,‘005‘,50),(‘1002‘,‘001‘,73),(‘1002‘,‘002‘,63),(‘1003‘,‘001‘,30),(‘1003‘,‘002‘,55),(‘1003‘,‘004‘,100),(‘1004‘,‘001‘,73),(‘1004‘,‘002‘,63),(‘1004‘,‘003‘,60),(‘1005‘,‘001‘,73),(‘1005‘,‘003‘,60),(‘1005‘,‘005‘,90),(‘1006‘,‘001‘,50),(‘1007‘,‘001‘,73),(‘1007‘,‘002‘,40),(‘1008‘,‘001‘,73),(‘1008‘,‘004‘,58);
-- 学生表
INSERT INTO student(sid,sname,sage,ssex) VALUES (‘1001‘,‘张三丰‘,‘1980-10-12 23:12:36‘,‘男‘),(‘1002‘,‘张无极‘,‘1995-10-12 23:12:36‘,‘男‘),(‘1003‘,‘李奎‘,‘1992-10-12 23:12:36‘,‘女‘),(‘1004‘,‘李元宝‘,‘1980-10-12 23:12:36‘,‘女‘),(‘1005‘,‘李世明‘,‘1981-10-12 23:12:36‘,‘男‘),(‘1006‘,‘赵六‘,‘1986-10-12 23:12:36‘,‘男‘),(‘1007‘,‘田七‘,‘1981-10-12 23:12:36‘,‘女‘),(‘1008‘,‘张三丰‘,‘1986-01-06 20:10:10‘,‘男‘);
-- 教师表
INSERT INTO teacher(tid,tName) VALUES (1,‘李老师‘),(2,‘何以琛‘),(3,‘叶平‘),(4,‘胡老师‘);
-- 1、查询“001”课程比“002”课程成绩高的所有学生的学号；
SELECT a1.sid
FROM (SELECT sid,score FROM sc WHERE cid=001) AS a1,(SELECT sid,score FROM sc WHERE cid=002) AS a2
WHERE a1.sid=a2.sid AND a1.score>a2.score
-- 2、查询平均成绩大于60 分的同学的学号和平均成绩；
SELECT sid,AVG(score)
FROM sc
GROUP BY sid
HAVING AVG(score)>60
-- 3、查询所有同学的学号、姓名、选课数、总成绩；
SELECT st.sid,sname,COUNT(1),SUM(score)
FROM sc,student st
WHERE sc.sid=st.sid
GROUP BY sid
-- 4、查询姓“刘”的老师的个数；
SELECT COUNT(1)
FROM teacher
WHERE tName="%刘%"
-- 5、查询没学过“李老师”课的同学的学号、姓名；
SELECT sid,sname
FROM student
WHERE sid NOT IN(
SELECT sid
FROM sc
WHERE cid IN
(SELECT cid
FROM course c,teacher t
WHERE c.tid=t.tid AND tName=‘李老师‘))
-- 6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
SELECT st.sid,sname
FROM sc,student st
WHERE sc.sid=st.sid AND sc.cid=‘001‘ AND sc.sid IN
(SELECT sid
FROM sc
WHERE cid=‘002‘)
-- 7、查询学过“李老师”所教的所有课的同学的学号、姓名；
SELECT st.sid,sname
FROM sc,student st
WHERE sc.sid=st.sid AND sc.cid IN
SELECT cid
FROM course c,teacher t
WHERE c.tid=t.tid AND tName=‘李老师‘)
-- 8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
SELECT st.sid,st.sname
FROM (SELECT sid,score FROM sc WHERE cid=001) AS a1,(SELECT sid,score FROM sc WHERE cid=002) AS a2,student st
WHERE a1.sid=a2.sid AND st.sid=a1.sid AND a1.score>a2.score
-- 9、查询所有课程成绩小于60 分的同学的学号、姓名；
SELECT sid,sname
FROM student
WHERE sid NOT IN(
SELECT sid
FROM sc
WHERE score>60)
-- 10、查询没有学全所有课的同学的学号、姓名
SELECT st.sid,sname
FROM sc,student st
WHERE sc.sid=st.sid
GROUP BY sid
HAVING COUNT(1) NOT IN(
SELECT COUNT(1)
FROM course)
-- GROUP_CONCAT 把内容横放