数据库查询语句
1.创建三张表,学生表、选课表、成绩表
CREATE TABLE student
(Sno varchar(20) NOT NULL,
Sname varchar(20) NOT NULL,
sex varchar(20) NOT NULL,
age INT NOT NULL,
dept varchar(20) NOT NULL, PRIMARY KEY (Sno)
);
CREATE TABLE course
(Cno varchar(20) NOT NULL,
Cname varchar(20) NOT NULL,
hours VARCHAR(20) NOT NULL,
PRIMARY KEY (Cno)
);
CREATE TABLE SC
(Sno varchar(20) NOT NULL,
Cno varchar(20) NOT NULL,
grade INT ,
PRIMARY KEY (Sno,Cno)
);
2.在三张表中分别添加数据
INSERT INTO student VALUES ('9512101','李勇','男',19,'计算系');
INSERT INTO student VALUES ('9512102','刘晨','男',20,'计算系');
INSERT INTO student VALUES ('9512103','王敏','女',20,'计算系');
INSERT INTO student VALUES ('9521101','张立','男',22,'信息系');
INSERT INTO student VALUES ('9521102','吴宾','女',21,'信息系');
INSERT INTO student VALUES ('9521103','张海','男',20,'信息系');
INSERT INTO student VALUES ('9531101','钱小力','女',18,'数学系');
INSERT INTO student VALUES ('9531102','王大力','男',19,'数学系');
INSERT INTO course VALUES ('C01','计算机文化学','70');
INSERT INTO course VALUES ('C02','VB','90');
INSERT INTO course VALUES ('C03','计算机网络','80');
INSERT INTO course VALUES ('C04','数据库基础','108');
INSERT INTO course VALUES ('C05','高等数学','180');
INSERT INTO course VALUES ('C06','数据结构','72');
INSERT INTO SC VALUES ('9512101','C01',90);
INSERT INTO SC VALUES ('9512101','C02',86);
INSERT INTO SC VALUES ('9512101','C06',NULL);
INSERT INTO SC VALUES ('9512102','C02',78);
INSERT INTO SC VALUES ('9512102','C04',66);
INSERT INTO SC VALUES ('9521102','C01',82);
INSERT INTO SC VALUES ('9521102','C02',75);
INSERT INTO SC VALUES ('9521102','C04',92);
INSERT INTO SC VALUES ('9521102','C05',50);
INSERT INTO SC VALUES ('9521103','C02',68);
INSERT INTO SC VALUES ('9521103','C06',NULL);
INSERT INTO SC VALUES ('9531101','C01',80);
INSERT INTO SC VALUES ('9531101','C05',95);
INSERT INTO SC VALUES ('9531102','C05',85);
commit;
3.通过SQL语句对表中的数据进行查询
(1)分别查询学生表和学生修课表中的全部数据
select * from student;
select * from course;
(2)查询成绩在70-80分之间的学生的学号、课程号和成绩。
select * from sc where grade >=70 and grade <=80;
(3)查询C01号课程成绩最高的分数(max()求最大值)、(group by 通过与聚合函数共同使用,实现数据分组
select max(grade) from sc where cno='C01' group by cno;
(4)查询学生都选修了哪些课程,要求列出课程号。(group by实现对查询出来的数据进行分组)
select cno from sc group by cno
(5)查询C02号课程的所有学生的平均成绩、最高成绩和最低成绩。
select max(grade),min(grade),avg(grade) from sc where cno='C02' group by cno;
(6)统计每个系的学生人数
select dept, count(*) as '人数' from student group by dept;
(7)统计每门课程的修课人数和考试最高分
select count(*) as '人数',max(grade) from sc group by cno;
(8)统计每个学生的选课门数,并按选课门数的递增顺序显示结果
select count(*) from sc group by sno order by count(*);
(9)统计选修课的学生总数和考试的平均成绩
select count(*),avg(grade) from sc group by sno ORDER BY avg(grade) DESC;
(10)查询选课门数超过2门的学生的平均成绩和选课门数(having只用于group by语句;having可以用于聚合函数,但是where不能;where只用于初始表中帅选查询,having用于在where和group by结果中查询)。
select count(*),avg(grade) from sc group by sno having count(*)>2;
(11)列出总成绩超过200分的学生,要求列出学号、总成绩
select sno, sum(grade) from sc group by sno having sum(grade)>200;
(12)查询选修了C02号课程的学生的姓名和所在系
SELECT student.Sname,student.dept,SC.Cno
FROM student join SC ON student.Sno = SC.Sno
WHERE Cno = 'C02'
子查询法
select Sname,dept from student where sno in(select sno from sc where cno='C02');
(13).查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。(排序使用order by,DESC代表逆序,不使用为默认顺序排序)
select student.Sname,sc.Cno,sc.grade
from student JOIN sc on student.Sno=sc.Sno
where sc.grade>80 order by sc.grade DESC;
(14)查询计算机系男生修了‘数据库基础’的学生姓名、性别、成绩
select student.Sname,student.sex,sc.grade
from student join sc on student.Sno=sc.Sno
join course on sc.Cno=course.Cno
where course.Cname='数据库基础';
(15)查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。
(16)查询哪些课程没有人选,要求列出课程号和课程名。
SELECT Cno,Cname
FROM course
WHERE Cno NOT in (SELECT Cno
FROM sc
GROUP BY Cno)
(17)查询有考试成绩的所有学生的姓名、修课名称及考试成绩。
要求将查询结果放在一张新的永久表(假设新表名为new-sc)中
CREATE TABLE new_sc AS -- 将查询结果放在新表中的方法。
SELECT S.Sname,course.Cname,sc.grade
FROM student AS S inner JOIN sc ON S.Sno = sc.Sno
INNER JOIN course ON course.Cno = sc.Cno
WHERE grade IS NOT NULL
(18)分别查询信息系和计算机系的学生姓名、性别、修课名称、修课成绩(联合查询使用union)
SELECT S.Sname,S.sex,course.Cname,sc.grade
FROM student AS S inner JOIN sc ON S.Sno = sc.Sno
INNER JOIN course ON course.Cno = sc.Cno
WHERE S.dept = '信息系'
UNION
SELECT S.Sname,S.sex,course.Cname,sc.grade
FROM student AS S inner JOIN sc ON S.Sno = sc.Sno
INNER JOIN course ON course.Cno = sc.Cno
WHERE S.dept = '计算机系'