SQL基本语句
小虾米原创作品，转载请注明出处：https://www.cnblogs.com/shrimp-can/p/9898735.html
小虾米最近在找工作，笔试题少不了SQL语句，几年没有碰过数据库都忘记了，复习了一下，将常用的简单的SQL语句以例子的形式系统的整理一下。
此文章根据书《数据库系统概念》第三章整理而成。
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
贯穿整个例子的关系有如下5个，最前面是关系名字，‘：’后面是该关系的属性
1.建筑department：系名dept_name, 建筑building, 预算budget
2.课程course：课程号course_id，课程名称title，课程所属的系名dept_name，学分credits
3.教师信息instructor：教师号ID，教师名字name，教师所在的系dept_name，薪资salary
4.开课信息section：课程号course_id，课程段标识sec_id，学期semester，年份year，课程所在建筑building，课程所在教室号room_number，时间档序号time_slot_id
5.教学信息teaches：教室号ID，课程号course_id，课程信息序号sec_id，学期semester，年份year
6.选课信息takes：学生学号ID，课程标识course_id，课程段标识sec_id，学期semester，年份year，成绩grade
7.学生信息student：学生学号ID，学生姓名name，学生所在系dept_name，总学分tot_cred
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
一、关系的创建与删除create、delete
1.表的创建create table
例1.1：创建一个department关系
create table department
(dept_name varchar(20),
building varchar(15),
budget numeric(12,2),
primary key (dept_name)); //主码
2.表的删除drop table
例1.2：删除关系department
drop table department;
二、单关系简单查询select
1.基本的单关系查询
例2.1：找出所有教师的名字
select name from instructor;
例2.2：找出所有教师所在的系名
select dept_name from instructor;
注：因为instructor关系中多个老师可以在一个系中，所以上述查询结果中会有重复的系名
2.去除重复distinct
例2.3：找出所有教师所在的系名，去除重复
select distinct dept_name from instructor;
3.显式指明不去除重复all
例2.4：例2中指明不去除重复
select all dept_name from instructor;
4.算数表达式+、-、*、/运算
例2.5：将教师的薪资增长10%
select ID, name, dept_name, salary*1.1 from instructor;
5.where子句
选出满足特定条件的元组，可以使用逻辑连词and、or 和not。可以使用运算符<、<=、>、>=、=和<>
例2.6：找出所有在Comuter Science系并且工资超过70000美元的教师的姓名
select name from instructor where dept_name='Computer Science' and salary>70000;
三、多关系简单查询
1.简单查询
例3.1：找出所有教师的姓名，以及他们所在系的名称和系所在建筑的名称
select name, instructor.dept_name, building from instructor, department where instructor.dept_name=department.dept_name;
例3.2：找出Computer Science系的教师名和课程标识
select name, course_id from instructor, teaches where instructor.ID=teaches.ID and dept_name='Computer Science';
2.自然连接natural join
自然连接作用于两个关系，并产生一个关系作为结果，只考虑在关系模式中出现相同属性的取值相同的情况
例3.3：例2可以用自然连接改为
select name, course_id from instructor natural join teaches;
例3.4：列出教师的名字和他们所讲授的课程的名称
select name, title from instructor natural join teaches, course where course.course_id=teaches_course_id;
3.连接中指定哪些列相等join...using
例3.5：例4可以改为
select name, title from (instructor natural join teaches) join course using (course_id);
四、基本运算
1.更名运算as
例4.1：对于所有讲授课程的教师，找出他们的姓名以及所讲授的课程标识，将查找结果的教师名字改为instructor_name
select name as instructor_name, course_id from instructor, teaches where instructor.ID=teaches.ID;
例4.2：将例1中的instructor关系和teaches关系重新命名为T和S
select T.name, S.course_id from instructor as T, teaches as S where T.ID=S.ID
例4.3：找出满足下面条件的所有教师的姓名，他们的工资至少比Biology系某一个教师的工资要高(去除重复的教师姓名)
select distinct T.name from instructor as T, instructor as S where T.salary>S.salary and S.dept_name='Biology';
2.字符串运算
2.1.字符串匹配like %/_
%：匹配任意字符串
_：匹配任意一个字符
如
Intro%：匹配任意以"Intro"开头的字符串
%Comp%：匹配任意包含"Comp"子串的字符串
_ _ _：匹配只含有三个字符的字符串
_ _ _%：匹配至少含有三个字符的字符串
例4.4：找出所在建筑名称中包含子串‘Watson’的所有系名
select dept_name from department where building like '%Watson%'；
2.2.转义字符的处理escape '\'
like 'ab\%cd%' escape '\'：匹配所有以'ab%cd'开头的字符串
like 'ab\\cd%' escape '\'：匹配所有以ab\cd开头的字符串
3.select中选择所有的属性*
例4.5：找出所有讲授了课程的教师信息
select instructor.* from instructor, teaches where instructor.ID=teaches.ID;
4.排列查询结果中元组的显示次序order by
order by默认升序，desc表示降序，asc表示升序
例4.6：按字母顺序列出Physics系的所有教师名字
select name from instructor where dept_name='Physics' order by name;
例4.7：按salary的降序列出整个instrutor关系，如果几位教师的工资相同，就按他们的名字升序排列
select * from instructor order by salary desc, name asc;
5.where子句的谓词between... and...
例4.8：找出工资在90000美元和100000美元之间的教师的姓名
select name from instructor where salary between 90000 and 100000;
它与此SQL语句等价
select name from instructor where salary>=90000 and salary<=100000;
例4.9：查找Biology系讲授了课程的所有教师的姓名和他们所讲授的课程标识
select name, course_id from instructor and teaches where (instructor.ID, dept_name)=(teaches.ID, 'Biology');
它与下面的SQL语句等价
select name, course_id from instructor and teaches where instructor.ID=teaches.ID and dept_name='Biology';
五、集合运算
1.并运算union
union会自动去除重复，要想保留所有的重复信息，用union all替代union
例5.1：找出在2009年秋季开课或者在2010年春季开课或者两个学期都开课的所有课程标识
(select course_id from section where year=2009 and semester='Fall') union
(select course_id from section where year=2010 and semester='Spring');
2.交运算intersect
intersect也会自动去除重复，要想保留所有的重复，用intersect all替代intersect
例5.2：找出在2009年秋季和2010年春季同时开课的所有课程序号的集合
(select course_id from section where year=2009 and semester='Fall') intersect
(select course_id from section where year=2010 and semester='Spring');
3.差运算except
将会在第一个关系中减去所有在关系2中出现的元祖，最后的结果将不会有重复的元组出现。如果想保留重复，用except all替代except
例5.3：找出在2009年秋季开课但不在2010年春季开课的所有课程序号
(select course_id from section where year=2009 and semester='Fall') except
(select course_id from section where year=2010 and semester='Spring');
六、聚集函数
1.基本聚集
平均值：avg
最小值：min
最大值：max
总和：sum
计数：count
例6.1：找出Computer Science系教师的平均工资
select avy(salary) from instructor where dept_name='Comouter Science';
例6.2：例1中给平均工资赋个新名称avg_salary
select avy(salary) as avg_salary from instructor where dept_name='Comouter Science';
例6.3：找出在2010年春季学期讲授课程的教师总数
select count (distinct ID) from teaches where semester='Spring' and year=2010;
注意：一个教师可能讲授几个课程段，但是在这里只应被计算一次，因此需要去除重复
例6.4：计算课程信息关系中的元祖个数
select count (*) from course;
2.分组聚集group by
group by将在该属性取值相同的元组划分为一组
例6.5：找出每个系的平均工资
select avy(salary) from instructor group by dept_name;
例6.6：找出每个系在2010年春季讲授了课程的教师人数
select dept_name, count(distinct ID) from teaches natural join instructor where semester='Spring' and year=2010 group by dept_name;
3.having子句
对group by子句构成的分组限定条件
例6.7：找出教师平均工资超过42000美元的系
select dept_name, avg(salary) as avg_salary from instructor group by dept_name having avg(salary)>42000;
例6.8：对于在2009年讲授的每个课程段，如果该课程段有至少2名学生选课，找出选修该课程段的所有学生的总学分的平均值
select course_id, year, semester, sec_id, avg(tot_cred) from takes natural join student group by course_id, sec_id, semester, year having count(ID)>=2;
七、嵌套子查询
1.集合成员的资格in, not in
例7.1：找出在2009年秋季开课，同时也在2010年春季开课的所有课程
select distinct course_id from section where semester='Fall' and year=2009 and course_id in
(select course_id from section where semester='Spring' and year=2010);
例7.2：找出在2009年秋季开课，但是不在2010年春季开课的所有课程
select distinct course_id from section where semester='Fall' and year=2009 and course_id not in
(select course_id from section where semester='Spring' and year=2010);
例7.3：查找既不叫‘Mpzart’也不叫'Einstein'的教师姓名
select distinct name from instructor where name not in ('Mozart', 'Einstein');
例7.4：找出不同的学生总数，他们选修了ID为10101的教师所讲授的课程
select count(sidtinct ID) from takes where course_id, sec_id, semester, year in (select course_id, sec_id, semester, year from teaches where teaches.ID=10101);
2.集合的比较
至少比某一个要大：>some
此外还有：<some，<=some，>=some，=some（等价于in），<>some
比所有的都大：>all
此外还有：<all，<=all，>=all，=all，<>all（等价于not in）
例7.5：找出满足下面条件的所有教师的姓名，他们的工资至少比Biology系某一个教师的工资要高
select name from instructor where salary >some (select salary from instructor where dept_name='Biology');
例7.6：找出满足下面条件的所有教师的姓名，他们的工资比Biology系每个教师的工资都高
select name from instructor where salary >all (select salary from instructor where dept_name='Biology');
例7.7：找出平均工资最高的系
select dept_name from instructor where avg(salary) >=all (select avg(salary) from instructor group by dept_name);
3.空关系测试exists, not exists
关系A包含关系B写成 not exists (B except A)
例7.8.找出在2009年秋季学期和2010年春季学期同时开课的所有课程标识
select course_id from section as S where year=2009 and semester='Fall' and exists
(select * from section as T where year=2010 and semester='Spring' and S.course_id=T.course_id);
例7.9：找出选修了Biology系开设的所有课程的学生
select S.ID, S.name from student as S where not exists ((select course_id from course where dept_name='Biology') except (select T.course_id from takes as T where S.ID=T.ID));
4.重复元组存在性测试unique, not unique
例7.10：找出所有在2009年最多开设一次的课程
select T.course_id from course as T where unique (select R.course_id from section as R where T.course_id=R.course_id and R.year=2009);
或者
select T.course_id from course as T where 1 >= (select count(R.course_id) from section as R where T.course_id=R.course_id and R.year=2009);
例7.11：找出所有在2009年最少开设两次的课程
select T.course_id from course as T where not unique (select R.course_id from section as R where T.course_id=R.course_id and R.year=2009);
5.from子句中的子查询
例7.12：找出系平均工资超过42000美元的那些系中教师的平均工资
select dept_name, avg_salary from (select dept_name, avg(salary) as avg_salary from instructor group by dept_name) where avg_salary > 42000;
例7.13：可以给from子句中的查询结果关系进行命名，并对其属性进行命名
select dept_name, avg_salary from (select dept_name, avg(salary) from instructor group by dept_name) as dept_avg (dept_name, avg_salary) where avg_salary > 42000;
例7.14：找出所有系中工资总额最大的系
select dept_name, max(tot_salary) from (select dept_name, sum(salary) from instructor group by dept_name) as dept_total (dept_name, tot_salary);
6.with子句
with子句提供定义临时关系的方法
例7.15：找出具有最大预算值的系
with max_budget (value) as (select max(budget) from department)
select budget from department, max_budget where departmen.budget=max_budget.value;
例7.16：查找所有工资总额大于所有系平均工资总额的系
with dept_total (dept_name, value) as (select dept_name, sum(salary) from instructor group by dept_name),
dept_total_avg(value) as (select avg(value) from dept_total);
select dept_name from dept_total, dept_total_avg where dept_total.value > dept_total_avg.value;
7.标量子查询
子查询出现在返回单个值的表达式能够出现的任何地方。
例7.17：列出所有的系和他们拥有的教师数
select dept_name, (select count(*) from instructor where department.dept_name=instructor.dept_name) as num_instructors from department;
八、数据库的修改
1.删除delete
删除整个关系前面我们已经讲了，用drop table，这里是删除关系中的元组
例8.1：从instructor关系中删除与Finance系教师相关的所有元组
delete from instructor where dept_name='Finance';
例8.2：删除所有工资在13000美元到15000美元之间的教师
delete from instructor where salary between 13000 and 15000;
例8.3：从instructor关系中删除所有这样的教师元组，他们在位于Watson大楼的系工作
delete from instructor where dept_name in (select dept_name from department where building='Watson');
例8.4：删除工资低于大学平均工资的教师记录
delete from instructor where salary < (select avg(salary) from instructor);
2.插入insert into
例8.5：插入的信息是Comouter Science系开设的名为'Database System'的课程CS-437，它有4个学分
insert into course values('CS-437', 'Database System', 'Computer Science', 4);
例8.6：例5中指定属性插入
insert into course (course_id, title, dept_name, credits) values('CS-437', 'Database System', 'Computer Science', 4);
例8.7：我们想让Music系每个修满144学分的学生成为Music系的教师，其工资为18000美元
insert into instructor select ID, name, dept_name, 18000 from student where dept_name='Music' and tot_cred>=144;
3.更新update
例8.8：将所有教师的工资增长5%
update instructor set salary=salary*1.05;
例8.9：给工资低于70000美元的教师工资增长5%
update instructor set salary=salary*1.05 where salary<70000;
例8.10：对工资低于平均数的教师涨5%的工资
update instructor set salary=salary*1.05 where salary<(select avg(salary) from instructor);
例8.11：给工资超过100000美元的教师涨3%的工资，其余教师涨5%
update instructor set salary=salary*1.03 where salary>100000;
update instructor set salary=salary*1.05 where salary<=100000;
例8.12：将例11用case结构
update instructor
set salary = case
when salary <=100000 then salary*1.05
else salary*1.03
end
例8.13：把每个student元组的tot_cred属性值设为该学生成功学完的课程学分的总和；假设学生在某门课程上的成绩既不是'F'也不是空，那么他成功学完了这门课程
update student set tot_cred = (select sum(credits) from takes natural join course where student.ID=takes.ID and takes.grade <>'F' and takes.grade is not null);
