存在以下四个数据库表
学生表:student1(sid,sname) Sid学号,sname学生姓名
教师表:teacher(tid,tname) tid教师编号,tname教师名字
课程表:course(cid,cname,tid) cid课程编号,cname课程姓名,tid教师编号
成绩表:cj(sid,cid,score) sid学号,cid选修课程编号,score 成绩
--1、查询所有同学的学号、姓名、选课数、总成绩
查询:select 字段1,字段2[*] from 表名
SELECT student1.sid,student1.sname,count(cj.cid),SUM(cj.score) FROM student1,cj WHERE student1.sid=cj.sid GROUP BY cj.sid,student1.sname
--2、查询平均成绩大于60分的同学的学号和平均成绩
max() 求最大
min() 求最小值
avg() 求平均值
sum() 求和
count()求个数
select sid,SUM(cj.score)/COUNT(cj.cid) from cj group by sid having max(score)>60;
--3、查询选修的课程不少于3门的每个学生的编号,学生姓名
select student1.sid,student1.sname,COUNT(cj.cid) from student1,cj WHERE student1.sid=cj.sid group by student1.sid,student1.sname having COUNT(cj.cid)>=3;
--4、查询选修了'数学'并且及格的姓名,课程名,成绩
and : 结果集同时满足所有条件
or: 结果集只要满足其中一个条件即可
not 非 一般情况跟 not like, not between..and not in 一起使用
SELECT student1.sname,cj.cid,cj.score FROM student1,cj WHERE student1.sid=cj.sid GROUP BY student1.sname,cj.cid,cj.score HAVING cj.cid=2 AND cj.score>60;
--5、查询每个学生至少选修了2门看起来像'语'的课程的学生姓名。
like 看起来像 模糊查询 当知晓某个字段部分值
匹配符:% 匹配0或者多个任意字符
SELECT student1.sname,course.cname,cj.cid,COUNT(cj.cid) FROM student1,course,cj WHERE student1.sid=cj.sid AND course.cid=cj.cid AND course.cname LIKE '%语' GROUP BY student1.sname,course.cname,cj.cid HAVING COUNT(cj.cid)>2;
--6、查询不及格的课程超过2门的学生编号和姓名
SELECT student1.sid,student1.sname,cj.score FROM student1,cj WHERE student1.sid=cj.sid HAVING score<60;
--7、查询选择的课程中"语文"的成绩比"数学"的成绩高的学生编号和姓名
单行子查询:子查询返回单行多列或者单行多列数据 跟> < = >= <=一起使用
SELECT * FROM 表名 WHERE 字段名=(
查询1)
SELECT student1.sid,student1.sname FROM (student1, (SELECT * FROM cj WHERE cid=( SELECT cid FROM course WHERE cname='语文'))AS temp1, (SELECT * FROM cj WHERE cid=( SELECT cid FROM course WHERE cname='数学'))AS temp2) WHERE student1.sid = temp1.sid AND temp1.sid = temp2.sid AND temp1.score>temp2.score;
--8、查询没有选择看起来像“老”的老师的课程的学生编号和姓名
not like
distinct 去重复
select distinct 字段名 from 表名
as 临时表名/集名
-- SELECT student1.sid,student1.sname -- FROM (student1, (SELECT * FROM cj WHERE cid in( -- SELECT cid FROM course WHERE course.cname NOT LIKE '%老'))AS temp3) -- WHERE student1.sid=temp3.sid #sid和sname会有重复数据 -- select distinct 字段名 from 表名 #去重 SELECT DISTINCT student1.sid,student1.sname FROM (student1, (SELECT * FROM cj WHERE cid in( SELECT cid FROM course WHERE course.cname NOT LIKE '%老'))AS temp3) WHERE student1.sid=temp3.sid
--9、查询选修了课程的学员人数
-- SELECT COUNT(sid) FROM (
-- SELECT * FROM student1 WHERE sid in(SELECT sid FROM cj))
SELECT COUNT(sid) FROM ( SELECT * FROM student1 WHERE sid in(SELECT sid FROM cj))as temp4; #Err] 1248 - Every derived table must have its own alias 每个派生出来的表都必须有一个自己的别名,所以在其后面加上 as temp4即可