数据表说明:
-
学生表Student
sid:学生id,sname:学生姓名,sage:学生出生日期,ssex:学生性别
-
课程表Course
cid:课程id,cname:课程名,tid:教师id
-
教师表Teacher
tid:教师id,tname:教师姓名
-
成绩表SC
sid:学生id,cid:课程id,score:成绩
创建上述各表,并且向表中插入数据:
Student和SC表通过学生id(sid)来连接,Course和Teacher表通过教师id(tid)连接,SC和Course表通过课程id(cid)来连接。
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values
('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-03-01' , '女'),
('07' , '郑竹' , '1989-07-01' , '女'),
('08' , '王菊' , '1990-01-20' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values
('01' , '语文' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values
('01' , '张三'),
('02' , '李四'),
('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values
('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);
执行顺序:from --> where --> group by --> having --> select --> order by–>limit
常用聚合函数有:
count():计数 count(distinct…) 去重计数
sum(): 求和
avg(): 平均数
max(): 最大值
min(): 最小值
注意:聚合函数不可以互相嵌套!!!
习题及其解答:
1.1 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
思路:先找出选修了01课程和02课程的学生id和课程成绩,然后通过学生id连接两表,筛选出01课程成绩比02课程成绩高的学生id和课程成绩,最后将筛选结果与学生表student连接,来筛选出相应学生的信息。
select * from student s right join
(select t1.sid,class1,class2 from
(select sid, score as class1 from sc WHERE cid = '01') t1,
(select sid, score as class2 from sc WHERE cid = '02') t2
where t1.sid <