【SQL】不会写SQL ? 一篇文章搞定常见 SQL

0，写在前面

以下内容主要来自于笔者的作业练习，应该会涉及到大部分的SQL语法。配合这篇文章 数据库理论 食用更佳。

1，准备工作

创建:学生表student，课程表course，选修表course。并插入数据。

create table if not exists student(
    sid char(6) not null primary key,
    name varchar(25) not null,
    sex int(1) not null,
    age int(3) not null,
    academy varchar(10) not null,
    major varchar(10) not null
    );
create table if not exists course(
    cid char(3) not null primary key,
    name varchar(10) not null,
    hour int(2) not null,
    property varchar(5) not null
);
create table if not exists take(
    sid char(6) not null ,
    cid char(3) not null ,
    constraint fk_s foreign key (sid) references student(sid),
    constraint fk_c foreign key (cid) references course(cid),
    grade int(3) not null
);

2，要求：

1，查询选修了《数据库》课程的学生的姓名：

select student.name from student,take,course 
	where course.name = '数据库' and course.cid = take.cid and take.sid = student.sid;
#优化,使用自然连接相当于做了个过滤，省去了一个and条件
select s.name from student s where s.sid in (
	select t.sid from course c natural join take t where c.name = '数据库';
)
#使用in，可见in可用于连接多个语句
select student.name from student where student.sid in(
	select take.sid from take where take.cid in(
    	select take.cid from course where course.name = '数据库'
    ) 
);

2，查询《数据库》课程的成绩，并按降序排序

select grade from take,course 
	where course.name = '数据库' and course.cid = take.cid 
	order by grade desc;
#优化，asc/desc 升、降序
select grade from take t natural join course c
	where c.name = '数据库' order by grade desc;

3，查询没有选修任何课程的学生的学号

select student.sid from student 
	where sid not in (select sid from take);

4，查询 c01 号课程成绩最高的分数

 select max(take.grade) from take,course where course.cid ='c01'; #0.032ms
 #优化 0.011ms（4条数据情况下测试的）
 select max(take.grade) from take natural join course where course.cid ='c01'; 

5，将计算机学院学生的选修记录清空

delete from take where take.sid = any 
	(select student.sid from student where student.academy='计算机学院');

6，将所有必修课的学时数增加 16

update course set course.hour = course.hour + 16 
	where course.property = '必修';

7，创建一个视图，用于查询每门课的平均成绩

create view avg_view as
    select course.name,take.cid,avg(grade) as 平均成绩 from course,take
    where take.cid = course.cid
    group by take.cid;
#视图直接进行查询
select * from ava_view;

8，创建一个存储过程，当输入一个学号时返回其选修的课程总数

create procedure pro_select_total(in id char(6))
begin
    select count(take.cid) from take  where take.sid = id;
end;

#调用存储过程
call pro_select_total(202055);

9，创建一个触发器，当删除一个学生时，同时删除其所有选课信息

create trigger trigger_del after delete
    on student for each row
    begin
        delete from take where sid not in
        (select sid from student);
    end;

在实际执行过程中，需要通过set foreign_key_checks = 0;关闭外键约束检查，否则会报错。

然后执行delete from student where student.sid = '202033';语句测试即可。

第二次作业：

2，要求

1 . 设计至少10个查询（含聚集、连接等，单表查询≤3个）;

1,查询修得学分在100到110之间的学生姓名；（单表，函数）

select name from student where tot_cred in (100,110);

2，找出工资最高的10为老师，并按照工资降序排列；（**单表，降序）**

select name,salary from instructor  order by salary desc limit 10;

3，查询姓名中第二个字母为c的学生人数； （单表，模糊查询）

select count(student.name) from student where student.name like '_c%';

4，查询学生和教师是否有同名的情况，并显示名字信息；（多表）

select instructor.name,student.name from student,instructor where student.name = instructor.name;

5，查询课程image Processing的成绩在A以上的人数；(多表,自然连接)

select count(takes.grade) from takes 
	natural join course where course.title = 'image Processing'
	and (takes.grade = 'A+' or takes.grade = 'A');

6，查询名为Lembr的老师教授了哪些课程；（多表，先自然连接，再笛卡尔积）

select name, title from teaches 
	natural join instructor,course 
	where teaches.course_id = course.course_id and instructor.name = 'Lembr';

也可以这样写：（限定第二次仅以course_id做自然连接，避免dept_name也要相等时，漏掉了不在自己系的课程）

select name, title from teaches 
	natural join instructor join course useing (course_id)
	where instructor.name = 'Lembr';

7，查询2009年秋和2010年春同时开设的课程；（多表，交运算，mysql只支持union并运算，在并运算中取重复的数据可实现交运算）

select course_id,title from course where course_id = any (
 (select course_id from section where semester = 'Fall' and year = '2009') 		union all
	(select course_id from section where semester = 'Spring' and year = '2010')
	)group by course_id having count(*) > 1;

8，计算计算机系老师的平均工资；（多表，聚集函数，平均值）

select avg(salary) as 平均工资 from instructor where dept_name = 'Comp. Sci.';

9，查询808教室会上哪些课程；（多表，二次筛选或自然连接）

select title from course where course_id = 
	any(select course_id from section where room_number = '808');

select title from course natural join section where room_number = '808';

10，查询World History课程上课的楼栋、教室编号、老师、所属学院、具体时间，开设学期等；（多表）

select * from course natural join section 
	join time_slot using (time_slot_id) where title = 'World History' ;

2，创建至少1个视图，并通过视图查看数据；

1,创建一个视图，其中执行语句为：查询id为10076的学生选的所有课程；

create view view_1 as
 	select student.name,title from takes natural join 
     	student join course using (course_id) where ID = '10076' ;

3，创建至少1个索引，并分析索引创建前后的数据查询效率

创建一个索引，优化查询效率。

create index index_grade on takes(grade);

实验环境：连接远程数据库情况下，测试样本记录约为30000条。

执行单表查询语句：select takes.ID from takes where grade = 'A';

创建前平均耗时59ms，创建后56ms。

执行多表查询语句：select name from student,takes where grade = 'A';

创建前平均耗时59ms，创建后56ms。

结论：索引的建立能在一定程度上提高查询效率，但可能测试次数过少或是样本过少，效果并不明显，但索引也不能过多使用，索引的执行本身就会有资源开销。

4，创建至少1个存储过程，并通过调用查看数据

创建通过id查询学生姓名的存储过程

create procedure pro_select(in id vachar(5))
begin
	select name from student where ID = id;
end;

调用：call pro_select(10705);

5，创建至少1个触发器，观察触发器应用结果

创建一个索引器，在删除学生后，删除其对应的选课信息

create trigger trigger_delete after delete
on student for each row
begin
  delete from takes where takes.ID not in
  (select ID from student);
end;

执行delete from student where ID = '1000';触发器生效。

6，用ER图给出University数据库模式对应的概念模型

数据库描述：整个数据库共有11张表，其中time_slot是记录时间信息的，与其他表没有关联关系。student表和instructor分别记录学生和老师信息，course记录课程信息，它通过选课表takes和授课表teaches分别与学生和老师表建立关联。section是记录分段授课表，它会关联classroom,couorse,dapartment表。而dapartment表也有学生、课程、老师表的关联信息。另外，先修表prereq记录了某个课程的先修课程信息；advisor则记录了老师和学生之间的指导关系。

首先使用数据库工具生成关系图。

然后据此绘制ER图。

ER图的另一种画法：