-- 第四章:
-- SQL查询基本结构
-- 集合运算
-- 空值
-- 聚集函数
-- 嵌套子查询
-- 数据库修该
--
SQL不区分大小写
SQL查询基本结构:
SQL的查询由3部分构成:
SELECT A1, A2, ... , An
FROM r1, r2, rm
WHERE P
查询的代数表达式
eg:
找出所有教师的名字
SELECT name
FROM instructor;
SELECT 子句:
SQL允许出现重复元组想要强行去除,可在select后加入关键词distinct
eg:
找到关系终中的所有系名, 并去除重复
SELECT distinct dept_name
From instructor;
可以使用关键词 all 来显示指明不去除重复(SQL默认状态)
eg:
SELECT ALL dept_name
FROM instructor;
"*"在SQL中可以用来表示所有属性
eg:
select *
from instructor
select 句子可以含有 +、-、*、/ 运算符的表达式,对象可以是元组或者是常数
eg:
select ID, name, salary * 1.05
from instructor;
WHERE 子句:
允许选出 在 from 句子中结果满足特定谓词 P 的元组
eg:
找出所有在Computer Science系 and 工资超过 70 000 美元的教师姓名
select name
from instructor
where dept_name = 'Comp.Sci.' and salary > 70000;
SQL允许在where子句中使用逻辑连词 and, or, not, 也可以使用 between
指定范围查询寻。还有可以使用 < , <=, > ,>= ,= , <> 的表达式
eg:
找出工资在 90 000 到 100 000 的教师的姓名
select name
from instructor
where salary <= 100000 and salary >= 90000;
或者:
select name
from instructor
where salary between 90000 and 100000;
FROM 子句:
查询值中需要访问的列表,通过from子句定义了该子句中所列出关系的笛卡尔积
eg:
找出 关系 instructor 和 teacher 的 笛卡尔积
select *
from instructor, teacher;
eg:
找出所有在Computer Science系的教师名和课程标识
select name, course_id
from instructor, teachers
where instructor.ID = teachers.ID
and instructor.dept_name = 'Comp. Sci.';
as子句 {更名运算}:
SQL提供可为关系和属性重命名的机制
Form: old_name as new_name
ps: as 可以出现在select子句中,也可以出现在from子句中
eg:
找出所有在Computer Science系的教师名和课程标识
并重命名为instructor为instructor_name
select name as instructor_name, course_id
from instructor, teachers
where instructor.dept_name = 'Comp. Sci.'
and instructor.ID = teachers.ID;
使用更名运算对关系重命名
eg:
找出所有教师,以及他们所受课程的标识
select T.name, S.course_id
from instructor as T, teachers as S
where T.ID = S.ID
eg:
找出所有教师名,他们的工资至少比Biology某一个教师工资要高
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';
like 操作符:{字符串操作}
like 通过 '%' 和 '_' 进行模式匹配
'%':匹配任意字符串
'_':匹配任意一个字符
eg:
找出所在建筑名称中含有‘Waston’的所有系名
select dept_name
from department
where building like '%Waston%';
ps:需要提取含“%”或“_”或“\”的字符可以使用
escape 关键词'\' 进行转义
eg:
like 'ab\%cd%' escape '\' 匹配所有以“ab%cd”开头的字符串
SQL提供了大量的string 函数
order by 子句:{默认升序}
可以让查询结果中元组安排列序显示
eg:
按字母排序列出在 Physics 系的所有教师
select name
from instructor
where dept_name = 'Physics'
order by name;
排序:{desc:降序; asc:升序}
eg:
按salary降序列出整个instructor的关系,存在相同则按字典排序
select *
from instructor
order by salary desc, name asc;
SQL重复的定义:
集合运算:
union: 并集
intersect: 交集
except: 差集
由于符号关系上图
eg:
ps:这三个操作会去重,如果想不去重需要加上关键词all
聚集函数:
平均值: avg
最小值: min
最大值: max
值的综合: sum
值的数量: count
ps: sum 和 avg 必须用在数字集
group by:{分组聚集}
在 group by 句子中给出一个或多个属性用来构造分组,
group by 子句中的所有属性都会被分到一个分组
having 子句:
作用于分组,(where 作用于元组限定条件)
having 子句中的谓词在形成分组后才起作用
eg:
找出 Computer Science系教师的平均工资
select avg(salary) as avg_salary
from instructor
where dept_name = 'Comp. Sci.';
eg:
找出每个系的平均工资:
select dept_name avg(salary) as avg_salary
from instructor
group by department;
ps: 任何没有出现在 group by 子句中的属性
出现在 select 中,只能出现在聚集函数内部
eg:
找出教师平均工资高于42 000的系
sele dept_name avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;
ps:任何没有出现在 having 子句中没有被聚集的属性
必须出现在 group by 中
空值:
可以作用于属性的值,表示属性值缺失
可以使用 is null 对谓词的值进行 空检测
或者使用 is not null ,进行非空检测
ps:count(*) 比较奇葩,它会统计所有的属性包括空值
eg:
找出 instructor 中元组在属性salary上取空值的教师名
select name
from instructor where salary is null
聚集函数会忽略空值
嵌套子查询:
集合成员资格:
in:
用于测试 元组 是否 是 select 产生的集合 的成员
not in:
用于测试 元组 是否 不是 select 产生的集合 的成员
eg:
找出在 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)
eg:
找出(不同的)学生总数, 他们选修了ID为10101的教师所讲授的课程
select count(distinct ID)
from takes
where (course_id, sec_id, semester, year)
in (select course_id, sec_id, semester, year
from teachers
where teachers.ID = 10101);
集合的比较:
some 子句的定义:
eg:
找出所有教师名,他们的工资至少比Biology某一个教师工资要高
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';
思考这个例子的新写法
select distinct name
from instructor
where salary > some (select salary
from instructor
where dept_name = 'Biology');
ps:some类似于或关系,由于整个语法左边是值
右边是集合,some还有内部应该实现了迭代器
all 子句的定义:
eg:
找出所有教师名,他们的工资比Biology每一个教师工资要高
select name
from instructor
where salary > all (select salary
from instructor
where dept_name = 'Biology');
空关系测试:
exists: 非空测试
not exists: 空测试
eg:
改写"找出在 2009 年秋季 和 2010 年春季同时开课的所有课程"
select course_id
from section as S
where semester = 'Fall' and year = 2009 and
exists (select *
from section as T
where semester = 'Spring' and year = 2010 and
S.course_id = T.course_id);
PS:该查找效率极低,每次查询都会执行一次内部子查询,
原因:引用了外部变量
eg:
找出选修了Biology系开设的所有课程的学生
select distinct 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));
unique:{重复元组存在测试}
作为参数的子查询中没有重复元组unique将返回true
eg:
找出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)
PS: unique 可以被换位 1 >=
from 子句中的嵌套子查询:
说白了就是把基本查询套入到from中,相当于局部视图
eg:
找出所有系中总工资额最大的系
select max(tot_salary)
from (select dept_name, sum(salary)
from instructor
group by dept_name) as dept_name (dept_name, tot_salary);
with 子句:
提供定义临时 关系 的方法 --注意是关系
生命周期:
在 with 查询语句内有效
eg:
找出工资总额大于平均值的系
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 A, dept_total_avg B
where A.value >= B.value;
数据库的修改:
删除:
delete from r
where P;
ps:其中P代表一个谓词,r代表一个关系、
eg:
删除位于Watson大楼的老师
delete from instructor
where dept_name in (select department
from department
where building = 'Watson');
eg:
删除工资低于平均工资的教师记录
delete from instructor
where salary < (select avg(salary)
from instructor)
插入:
insert into r[(c1,c2,...)]
value (e1, e2)
insert into r[(c1,c2,...)]
select e1, e2, ..., from ...
eg:
在 Computer Science系 里插入 Database systems 的 课程CS——437,学分为 4
insert into course
value('CS_437', 'Database systems', 'Comp. Sci.', '4');
或者
insert into course(course_id, title, dept_name, credits)
value('CS_437', 'Database systems', 'Comp. Sci.', '4');
更新:
update r
set <c1=e1, [c2=e2, ...]>
[where <condition>];
SQL查询语句顺序:
from -> where -> group (aggregate) -> having -> select -> order by