小虾米最近在找工作, 笔试题少不了 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);
来源: http://www.bubuko.com/infodetail-2833421.html