sql练习-1

-- 创建表

-- CREATE TABLE `course` (

-- `cid` int(3) NOT NULL AUTO_INCREMENT COMMENT '课程编号',

-- `cname` varchar(10) DEFAULT NULL COMMENT '课程名',

-- `tid` int(3) DEFAULT NULL COMMENT '教师编号',

-- PRIMARY KEY (`cid`))

-- ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

 

-- CREATE TABLE `sc` (

-- `sid` int(11) NOT NULL COMMENT '学号',

-- `cid` int(11) NOT NULL COMMENT '课程编号',

-- `score` int(11) DEFAULT NULL COMMENT '分数',

-- PRIMARY KEY (`sid`,`cid`))ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

 

-- CREATE TABLE `student` (

-- `sid` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',

-- `sname` varchar(10) DEFAULT NULL COMMENT '学生姓名',

-- `sage` int(3) DEFAULT NULL COMMENT '学生年龄',

-- `ssex` varchar(1) DEFAULT NULL COMMENT '学生性别',

-- `birthday` datetime(6) DEFAULT NULL COMMENT '学生生日',

-- PRIMARY KEY (`sid`)

-- ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

--

--

-- CREATE TABLE `teacher` (

-- `tid` int(3) unsigned NOT NULL AUTO_INCREMENT COMMENT '教师编号',

-- `tname` varchar(10) DEFAULT NULL COMMENT '教师姓名',

-- PRIMARY KEY (`tid`)

-- ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- 创建数据

-- INSERT INTO `student` VALUES (1, '刘一', 23, '男','2000-05-01 09:31:25');

-- INSERT INTO `student` VALUES (2, '钱二', 25, '女','1998-03-05 09:31:44');

-- INSERT INTO `student` VALUES (3, '张三', 21, '男','2002-01-01 09:32:08');

-- INSERT INTO `student` VALUES (4, '李四', 22, '女','2001-02-08 15:32:43');

-- INSERT INTO `student` VALUES (5, '王五', 32, '男','1991-04-18 18:33:05');

-- INSERT INTO `student` VALUES (6, '赵六', 34, '女','1989-01-14 23:33:22');

--

--

--

-- INSERT INTO `teacher` VALUES (1, '叶平');

-- INSERT INTO `teacher` VALUES (2, '贺高');

-- INSERT INTO `teacher` VALUES (3, '杨艳');

-- INSERT INTO `teacher` VALUES (4, '周磊');

--

--

-- INSERT INTO `course` VALUES (1, '语文', 1);

-- INSERT INTO `course` VALUES (2, '数学', 2);

-- INSERT INTO `course` VALUES (3, '英语', 3);

-- INSERT INTO `course` VALUES (4, '物理', 4);

-- INSERT INTO `course` VALUES (5, '化学', 5);

--

--

--

-- INSERT INTO `sc`(sid,cid,score) VALUES (1,1,50);

-- INSERT INTO `sc`(sid,cid,score) VALUES (1,2,60);

-- INSERT INTO `sc`(sid,cid,score) VALUES (1,3,70);

-- INSERT INTO `sc`(sid,cid,score) VALUES (1,4,80);

-- INSERT INTO `sc`(sid,cid,score) VALUES (1,5,90);

--

-- INSERT INTO `sc`(sid,cid,score) VALUES (2,1,90);

-- INSERT INTO `sc`(sid,cid,score) VALUES (2,2,80);

-- INSERT INTO `sc`(sid,cid,score) VALUES (2,3,70);

-- INSERT INTO `sc`(sid,cid,score) VALUES (2,4,88);

--

-- INSERT INTO `sc`(sid,cid,score) VALUES (3,1,32);

-- INSERT INTO `sc`(sid,cid,score) VALUES (3,2,82);

-- INSERT INTO `sc`(sid,cid,score) VALUES (3,3,53);

-- INSERT INTO `sc`(sid,cid,score) VALUES (3,4,89);

-- INSERT INTO `sc`(sid,cid,score) VALUES (3,5,99);

--

-- INSERT INTO `sc`(sid,cid,score) VALUES (4,1,92);

-- INSERT INTO `sc`(sid,cid,score) VALUES (4,2,82);

-- INSERT INTO `sc`(sid,cid,score) VALUES (4,3,93);

-- INSERT INTO `sc`(sid,cid,score) VALUES (4,4,89);

-- INSERT INTO `sc`(sid,cid,score) VALUES (4,5,99);

--

--

-- INSERT INTO `sc`(sid,cid,score) VALUES (5,1,93);

-- INSERT INTO `sc`(sid,cid,score) VALUES (5,2,83);

-- INSERT INTO `sc`(sid,cid,score) VALUES (5,3,84);

-- INSERT INTO `sc`(sid,cid,score) VALUES (5,4,86);

-- INSERT INTO `sc`(sid,cid,score) VALUES (5,5,100);

--

--

-- INSERT INTO `sc`(sid,cid,score) VALUES (6,1,55);

-- INSERT INTO `sc`(sid,cid,score) VALUES (6,2,46);

-- INSERT INTO `sc`(sid,cid,score) VALUES (6,3,80);

-- INSERT INTO `sc`(sid,cid,score) VALUES (6,4,60);

-- INSERT INTO `sc`(sid,cid,score) VALUES (6,5,70);

--

1、查询所有学生信息
select * from student;

2、查询年龄大于23岁的学生学号、姓名和性别
select sid,sname,ssex from student;

3、查询年龄在19-27岁之间的学生信息
select * from student where sage between sage 19 and 27;
4、查询出张三、王五、赵六的学生信息
select * from student where sname in ("张三","王五","赵六");

5、查询出性别为“男”，且年龄大于25的学生信息
select * from student where ssex = "男" and sage > 25;

6、查询出姓“王”的学生的所有课程的分数
select sc.cid,sc.score from student
join sc on student.sid = sc.sid where student.sname like "王%"

7、查询出每个学生的平均分且排行
select student.sname,avg(sc.score) from student
join sc on student.sid = sc.sid group by sc.sid order by avg(sc.score) desc

8、查询出“语文”成绩的最高分
select max(sc.score) from sc
join course on sc.cid = course.cid where course.cname = "语文"

9、查询出总分为第四名的学生信息
select student.*,sum(sc.score) from student
join sc on student.sid = sc.sid
GROUP BY student.sid order by sum(sc.score) desc limit 3,1

10、查询出哪个老师所带的课程总分最高
select teacher.tname,sum(sc.score) from sc
join course on sc.cid = course.cid join teacher on course.tid = teacher.tid
GROUP BY sc.cid order by sum(sc.score) desc limit 1

11、查询出总成绩高于张三的每个学生的总分且成绩排行
select student.sname, sum(sc.score) from student
join sc on student.sid = sc.sid group by sc.sid
having sum(sc.score) >
(select sum(sc.score) from student
join sc on student.sid = sc.sid where student.sname = "张三")

 

12、查询出选修课程数量小于5门课的学生信息
select student.* from student
join sc on student.sid = sc.sid group by sc.sid HAVING count(sc.cid) <5

13、查询出总成绩最低的学生姓名、学号和年龄
select student.sname,student.sid,student.sage,sum(sc.score) from student
join sc on student.sid = sc.sid
group by sc.sid order by sum(sc.score) asc limit 1

14、查询出哪些学生的平均成绩高于75分
select student.sname,avg(sc.score) from student
join sc on student.sid = sc.sid
group by sc.sid having avg(sc.score)>75

 

15、查询出成绩第一名的学生信息、和各科分数
select student.*,course.cname,sc.score from student
join sc on student.sid = sc.sid join course on sc.cid = course.cid
where student.sid =
(select sc.sid from student
join sc on student.sid = sc.sid join course on sc.cid = course.cid
GROUP BY sc.sid ORDER BY sum(sc.score) desc limit 1)

 

16、查询出男生和女生各多少人
SELECT ssex,count(ssex) from student group by ssex

17、查询出女生的平均分、总分
select AVG(sc.score),sum(sc.score) from student
join sc on student.sid = sc.sid
where student.ssex = "女"

 

18、查询出姓名一样的人的有几个
select sname,count(sname) from student group by sname having count(sname) >1;

19、查询出没有成绩的学生有哪些人
select student.sid,student.sname from student
left join sc on student.sid = sc.sid where sc.sid is null

20、查询出哪一个老师没有任何课程
select teacher.tid,teacher.tname from teacher
left join course on teacher.tid = course.tid
where course.tid is null