文章目录
- 1 建库语句
- 2 建表语句
- 3 插入测试数据
- 4 常见查询问题
1 建库语句
create database if not exists studentinfo character set utf8mb4;
2 建表语句
一个学生可以选多门课程，一个课程下也对应多个学生，所以需要一个关系表（student_course）。为了方便，除了将学生id和课程id放入student_course中，还将成绩一起放到关系表中。另外一门课程对应一个老师。
(1) 学生表（student）
create table student(s_id int,sname varchar(20),sage int,sgender varchar(8)
)
(2) 课程表（course）
create table course(c_id int,cname varchar(20),t_id int
)
(3) 关系表（student_course）—— 学生和课程之间的关系表
create table student_course(s_id int, # 学生idc_id int, # 课程idscore int # 成绩
)
(4) 教师表（teacher）
create table teacher(t_id int, #老师idtname varchar(20) # 老师姓名
)
3 插入测试数据
（1）插入学生数据
insert into student select 1,N'刘一',18,N'男' union allselect 2,N'钱二',19,N'女' union allselect 3,N'张三',17,N'男' union allselect 4,N'李四',18,N'女' union allselect 5,N'王五',17,N'男' union allselect 6,N'赵六',19,N'女' ;
注意：
- N表示后续的字符串是Unicode类型的字符。
- Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
- Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
- 注意使用insert插入时可以使用select（需要保证查询出的字段和插入字段一致就可以了）
insert into student values(1,’张三’,1203);
insert into test_user(name, email) select name, qq_mail from student;
（2）插入教师数据
insert into teacher select 1,N'叶平' union allselect 2,N'贺高' union allselect 3,N'杨艳' union allselect 4,N'周磊';
（3）插入课程数据
insert into course select 1,N'语文',1 union allselect 2,N'数学',2 union allselect 3,N'英语',3 union allselect 4,N'物理',4;
（4）插入关系表数据
insert into student_course select 1,1,56 union all select 1,2,78 union all select 1,3,67 union all select 1,4,58 union all select 2,1,79 union all select 2,2,81 union all select 2,3,92 union all select 2,4,68 union all select 3,1,91 union all select 3,2,47 union all select 3,3,88 union all select 3,4,56 union all select 4,2,88 union all select 4,3,90 union all select 4,4,93 union all select 5,1,46 union all select 5,3,78 union all select 5,4,53 union all select 6,1,35 union all select 6,2,68 union all select 6,4,71;
4 常见查询问题
(1) 查询id为“001”的课程比“002”成绩高的所有学生的学号；
select a.s_id FROM(select s_id,score from student_course where c_id = '001') a,(select s_id,score from student_course where c_id = '002') bwhere a.score > b.score and a.s_id = b.s_id;
| s_id |
+------+
| 3 |
(2) 查询平均成绩大于60分的学号和平均成绩；
select s_id,avg(score)from student_coursegroup by s_idhaving avg(score)>60;
| s_id | avg(score) |
+------+------------+
| 1 | 64.7500 |
| 2 | 80.0000 |
| 3 | 70.5000 |
| 4 | 90.3333 |
(3) 查询所有学生的学号、姓名、选课数、总成绩；
select s.s_id,s.sname, count(sc.c_id),sum(sc.score) from
student s left join student_course sc on s.s_id = sc.s_id
group by s.s_id, s.sname;
| s_id | sname | count(student_course.c_id) | sum(score) |
+------+--------+----------------------------+------------+
| 1 | 刘一 | 4 | 259 |
| 2 | 钱二 | 4 | 320 |
| 3 | 张三 | 4 | 282 |
| 4 | 李四 | 3 | 271 |
| 5 | 王五 | 3 | 177 |
| 6 | 赵六 | 3 | 174 |
(4) 查询姓“叶”的老师的个数；
select count(*) from teacher where tname like '叶%';
| count(*) |
+----------+
| 1 |
(5) 查询没学过“叶平”老师课的同学的学号、姓名；
select s.s_id, s.sname from student s where s.s_id not in (select sc.s_id from student_course sc, course c, teacher t where sc.c_id = c.c_id and c.t_id=t.t_id and t.tname='叶平');
| s_id | sname |
+------+--------+
| 4 | 李四 |
(6) 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select s.s_id, s.sname from student s,student_course sc where
sc.s_id = s.s_id and sc.c_id = '001'
and exists (select * from student s1, student_course sc1where s1.s_id=sc1.s_id and sc1.c_id='002' );
| s_id | sname |
+------+--------+
| 1 | 刘一 |
| 2 | 钱二 |
| 3 | 张三 |
| 6 | 赵六
注：
exists对应的子查询实际上并不返回任何数据，而是返回值True或False，EXISTS 指定一个子查询，主要用来检测 行 的存在。
(7) 查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select s.s_id,s.sname from student s where s.s_id in (select sc.s_id from student_course sc,course c, teacher t where sc.c_id = c.c_id and c.t_id = t.t_id and t.tname ='叶平');
| s_id | sname |
+------+--------+
| 1 | 刘一 |
| 2 | 钱二 |
| 3 | 张三 |
| 5 | 王五 |
| 6 | 赵六 |
(8) 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select a.s_id,a.sname from
(select sc.s_id,sc.score,s.sname from student_course sc,student s
where sc.s_id=s.s_id and sc.c_id='001')a,
(select sc.s_id,sc.score from student_course sc,student s
where sc.s_id=s.s_id and sc.c_id='002') b
where a.s_id=b.s_id and a.score>b.score;
| s_id | sname |
+------+--------+
| 3 | 张三 |
+------+--------+
(9) 查询所有课程成绩小于60分的同学的学号、姓名；
select s.s_id,s.sname from student s
where s.s_id in (select sc.s_id from student_course sc
where sc.score<60);
s_id | sname |
+------+--------+
| 1 | 刘一 |
| 3 | 张三 |
| 5 | 王五 |
| 6 | 赵六 |
(10) 查询没有学全所有课的同学的学号、姓名；
select s.s_id,s.sname from student s, student_course sc where sc.s_id = s.s_id group by s.s_id,s.sname having count(sc.c_id)<(select count(c_id) from course);
| s_id | sname |
+------+--------+
| 4 | 李四 |
| 5 | 王五 |
| 6 | 赵六 |
注意：
where子句中不能使用count等聚合函数，where 子句的作用是在分组之前过滤数据，having在分组之后过滤数据，条件中经常包含聚合函数。
(11) 查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
select distinct s.s_id,s.sname from student s,student_course sc
where s.s_id=sc.s_id and sc.c_id in
(select c.c_id from student s, course c, student_course scwhere s.s_id = sc.s_id and s.s_id = '1');
| s_id | sname |
+------+--------+
| 1 | 刘一 |
| 2 | 钱二 |
| 3 | 张三 |
| 4 | 李四 |
| 5 | 王五 |
| 6 | 赵六 |
(12) 对学生表的sname的最后两个个字母，按照升序进行排列
select sname from student order by substr(sname,length(sname)-1,2);
substr的用法：
substr(string string,num start,num length);string为字符串；start为起始位置；length为长度。
参考文章：http://www.manongjc.com/article/41690.html
