sql进阶语句实例

1 多表查询 通过where 将表进行关联

表human，student数据如下图：

将两表通过id字段进行连接 输出

select t1.id,t1.name,t1.age,t1.sex,t2.chinese,t2.math,t2.english from human as t1,student as t2 where t1.id  = t2.id;

2 视图 view 数据只可查看，不可操作

create view view1 as select t1.id,t1.name,t1.age,t1.sex,t2.chinese,t2.math,t2.english from human as t1,student as t2 where t1.id = t2.id;

3 sql语句综合练习（表结构如下图所示(学生表，课程表，分数表)）

3.1 分别查询学生表和课程表中的全部数据；

select *from student;

select * from course;

3.2 查询成绩在70到80之间的学生学号，课程号和成绩；

select id,cno,grade from sroce where grade >=70 and grade <= 80;

or: select id,cno,grade from sroce where grade between 70 and 80;

3.3 查询291号课程成绩最高分数;

select max(grade) from sroce where cno =291;

3.4 查询学生都选修了哪些课程，要求列出课程号；

select  distinct(cno)  from course;

3.5 查询291号课程所有学生的平均成绩，最高成绩，最低成绩;

select avg(grade),max(grade),min(grade) from score where cno = 291;

3.6统计每个系的人数；

select dept,count(dept) from student group by dept;

3.7统计每门课程的修课人数和考试最高分；

select count(id),max(grade) from score group by cno;

3.8统计每个学生的选课门数，并按选课门数递增显示；

select count(id) from score group by id;

3.9 统计选修课的学生总数和考试的平均成绩；

select count(id) ,avg(grade) from score;

3.10 查阅选修课数超过2门的学生的平均成绩；

select avg(grade) ,count(cno)  as count from score group by cno having count >2 ;

3.11列出总成绩超过200分的学生，要求列出学号，总成绩；

select id,sum(grade) as sum_grade from score  group by id having sum_grade >200;

3.12 查询选修了291课程学生的姓名和所在系；

select t1.name,t1.dept from student as t1,score as t2 where t1.id =t2.id  and cno = 291;

3.13 查询成绩80分以上的学生姓名，课程号和成绩，并按成绩降序排列；

select t1.name ,t2.cno,t2.grade from student as t1,sorce as t2 where t1.id = t2.id group by t2.id having grade > 80 order by grade desc;

3.13.1 查询80分以上的学生；

select id ,cno,grade from score where grade > 80;

3.13.2 连接student 表，得到姓名；

select t1.name ,t2.cno,t2.grade  from student as t1, (select id, cno,grade from score where grade > 80) as t2 where t1.id =t2.id;

3.14 查询计算机系男生修了‘数学’的学生姓名，成绩；

select t1.name,t2.grade from student as t1,score as t2 where t1.id = t2.id and t1.dept = ‘computer’  and t1.sex = ‘man’;

3.15 查询哪些学生的年龄相同，要求列出年龄相同的学生姓名和成绩；

select t1.name ,t2.grade from student as t1 ,score as t2 where t1.id = t2.id group by t1.age;

 

3.16 分别查询计算机系和数学系的学生姓名，性别，修课名称，修课成绩；

并要求将这两个查询结果合并成一个结果集；

并以系名，姓名，修课名称，修课成绩的顺序显示各列；

第一步：在student 表中获得计算机系和数学系的学号，姓名，以及性别；

select id,name,sex from student where dept in(‘computer’,’math’);

第二步：与分数表相关联，得到成绩和课程号；

select  grade ,cno,t1.* from score as t1,( select id,name,sex from student where dept  in(‘computer’,’math’) ) as t2 where t1.id = t2.id;

第三步:与course关联得到修课名称；

select cname, t2.* from course as t1, (select  grade ,cno,t1.* from score as t1,( select id,name,sex from student where dept in(‘computer’,’math’) ) as t2 where t1.id = t2.id ) as t2 where t1.cno=t2.cno;

3.17 将计算机系成绩高于80分的学生的修课情况插入到另一张表中，创建一个新表，然后插入数据。

create table temp as select t1.name,t3.cname from student as t1, score as t2,course as t3 where t1.id =t2.id and t1.dept = ‘computer’ and t2.cno = t3.cno and t2.grade > 80 ;

3.18 删除修课成绩小于60分的学生的修课记录；

delete from score where grade < 60;

3.19 将所有选修了“c01”课程的学生的成绩加10分；

update score set grade = grade + 10 where cno =291;