数据库数据库

查询“Introduction to Comp. Sci.”课程每种成绩的学生数。
select grade, count(id)
from takes natural join course where title='Introduction to Comp. Sci.'
group by grade

查询每门课程的学生人数与不及格的人数，并计算及格率
select course_id, count(ID) num, sum(case when grade='F' then 1 else 0 end) fail,
   sum(case when grade<>'F' then 1 else 0 end)/sum(case when grade is not null t hen 1 else 0 end) 
from takes
group by course_id

查询上课人数最多的教学班
select course_id, sec_id, semester, year from 
(select course_id, sec_id, semester, year count(id) num from takes group by course_id, sec_id, semester, year) T where num=(select max num from (select course_id, sec_id, semester, year count(id) num from takes group by course_id, sec_id, semester, year))

查询不及格人次数最多的系
select dept_name from takes natural join student where grade='F' group by dept_name order by count(takes.id) desc limit 1;
注意：这个写法有风险，如果有并列最多的，只能筛选出一个系

有些课程会被不同系的学生学习，查询上课学生所属的系个数最多的课程。
select course_id from 
(select course_id, count(distinct dept_name) num from takes inner join student group by course_id) where num=(select max(num) from (select count(distinct dept_name) num from takes inner join student group by course_id)

查询学了课程号为CS-101的课程的学生的平均绩点（tot_cred的平均值）。
select avg(tot_cred) from student where ID in (select id from takes where course_id='CS-101')

创建一个数据库，数据库名为MyLab

使用这个数据库

在这个数据库中创建一张表mylab，包含以下字段和数据：(注意选用合适的数据类型)

删除数据表 mylab

删除数据库 MyLab

重新创建数据库MyLab

导入数据DDL.sql, smallRelationsInsertFile.sql

列出所有学生(student)的学号(ID)、姓名(name)和系(dept_name)

select Id,name,dept_name from student;

列出所有计算机系绩点在50以上的学生姓名和绩点

 select name,tot_cred from student where dept_name="Comp. Sci." and tot_cred>50;

列出所有计算机系的课程

select title from course where dept_name="Comp. Sci.";

select * from course where dept_name="Comp. Sci.";

列出所有介绍性课程（课程名以Intro开头）

select *from course where title like "Intro%";

列出所有学了CS-101但没有学CS-190的学生学号

select distinct ID from takes where course_id='CS-101' and ID not in(select distinct ID from takes where course_id='CS-190');

查询所有学生每一门课程的成绩，打印出学号、课程号和成绩，并按学号排序

 select ID,course_id,grade from takes  order by ID;

查询所有学生每一门课程的成绩，打印出学号、学生姓名、课程号和成绩，无需排序

select student.ID,name,course_id ,grade from student,takes where student.ID=takes.ID;

查询所有学生每一门课程的成绩，打印出学号、学生姓名、课程名称和成绩，并首先按课程名排序，再按学号排序

select student.ID,name,title ,grade from student,takes,course  

where student.ID=takes.ID and course.course_id=takes.course_id

order by title,student.ID;

创建一个视图，内容包含所有课程成绩合格（成绩不为F或空）的学生姓名、课程名称、成绩

create view v as ( select name,title,grade from (student natural join takes) join course using (course_id) where ID not in (select ID from takes where grade ='F' and grade is null) );

在上面的视图中查找所有至少有一门课程成绩为A或A-的学生姓名

select distinct name from v where grade in ('A','A-');

删除上面的视图

drop view v;

按以下规则修改学生的tot_cred：

如果学生的tot_cred>100, 则加5分，否则减5分

update student set tot_cred=case when tot_cred>100  then tot_cred+5 else  tot_cred-5 end where  tot_cred>4;

将student表恢复原状

即如果学生tot_cred>100,减5分，否则加5分

update student set tot_cred=case when tot_cred>100  then tot_cred-5 else  tot_cred+5 end where  tot_cred>4;

（选做）按以下规则修改学生tot_cred:

如果学生tot_cred<100,则加5分，但是增加后的分数不能超过100分

 update student set tot_cred=case when tot_cred<95  then tot_cred+5 when tot_cred<100 then 100 else tot_cred end ;

在student表中增加一列birthday，数据类型为date

alter table student add birthday date;

将所有学生的生日都设置为1990年1月1日

update student set birthday=date'1990-1-1';

查看student表里的所有数据

select * from student;

删除列birthday

alter table student drop birthday;

导入update.sql文件

source D:/update.sql;

查找7月份出生的学生学号、姓名和所在系

select ID,name,dept_name from student where month(birthday)='7';

select ID,name,dept_name from student where birthday like '_____07%';

查找生日相同的学生和老师，打印学生和老师的姓名以及他们的出生年月日

 select S.name,S.birthday,T.name,T.birthday from student as S,instructor as T where  month(S.birthday) =month(T.birthday) and day(S.birthday) =day(T.birthday) ;

 

新建一个用户abc，并授权他查询student表，密码可任意设置

 create user 'abc'@'localhost' identified by '123456';

grant select on mylab.student to abc @localhost;

使用用户abc登录mysql

mysql -u abc -p

123456

查看表student的内容

use mylab;

select * from student;

查看表takes的内容

select * from takes;

删除表student里的一条记录

delete from student where ID='00128';

使用root登录mysql并授权abc查询takes表

mysql -u root -p

grant select on mylab.takes to abc @localhost;

使用abc登录mysql并重复以上实验

使用root登录mysql并取消abc查询student表的权限

revoke select on mylab.student from abc @localhost;

列出所有学了CS-101但没有学CS-190的学生学号

select distinct ID from takes where course_id="CS-101" and ID not in (select ID from takes where course_id="CS-190");

分别用IN和EXISTS查询上了CS-101和CS-190两门课的所有学生学号和姓名

select takes.ID,name from student,takes where student.ID=takes.ID and course_id="CS-101" and takes.ID in ( select takes.ID from student,takes where student.ID=takes.ID and course_id="CS-190");

select student.ID,student.name from student natural join takes t1 where exists(select student.ID from student natural join takes t2 where t1.course_id="CS-101" and t2.course_id="CS-190" and t1.ID=t2.ID);

查询上了“计算机科学导论”（Intro. to Computer Science）但没上“图像处理”（Image Processing）的所有学生的姓名

 select name from student,takes,course where student.ID=takes.ID and takes.course_id=course.course_id and title="Intro. to Computer Science" and name not in (select name from student,takes,course where student.ID=takes.ID and takes.course_id=course.course_id and title="Image Processing" );

查询所有计算机系学生的平均绩点(tot_cred)

select avg(tot_cred) from student where dept_name="Comp. Sci.";

查询所有上了“计算机科学导论”（Intro. to Computer Science）课程的学生的平均绩点(tot_cred)

 select avg(tot_cred) from student where ID in in(select ID from takes where title="Intro. to Computer Science" );

查询每个系的平均成绩，只打印出平均成绩大于80的系的名称和平均成绩，并按平均成绩从大到小排序

 select dept_name, avg(tot_cred) as avg_cred from student group by dept_name having  avg_cred>80 order by avg_cred desc ;