数据库系统概念第三章--SQL

3.1 SQL查询语言概览

SQL包括以下几个部分：

• 数据定义语言（DDL），提供关系模式，删除关系以及修改关系模式的命令
• 数据操纵语言（DML），提供从数据库中查询信息以及在数据库中插入元组，删除元组，修改元组的能力
• 完整性：定义完整性约束命令。
• 视图定义：DDL包括定义视图的命令
• 事务控制：定义事务开始和结束的命令
• 嵌入式SQL与动态SQL：定义SQL插入编程语言的方式如,c,java
• 授权：定义对关系和视图的访问权限

3.2 SQL数据定义

• 每个关系的模式
• 每个属性的取值类型
• 完整性约束
• 每个关系维护的索引集合
• 每个关系的安全性和权限信息
• 每个关系在磁盘上的物理存储结构

3.2.1 基本类型

char 存储固定字长的字符串，不够则添加空格
varchar 存储字符串，变动的
int smallint（小整数类型） numeric（定点数） real,double precision 浮点数和双精度浮点数

3.2.2 基本模式定义

create table table_name 
		(属性1 类型（长度），
		属性2 类型（长度），
		...
		primary key(属性1)，
		<完整性约束1>，
		<完整性约束2>,
		...
		）:     ##primary key 声明主键

• primary key: 声明主码
• foreign key references: foreign key声明表示关系中任意元组在属性上的取值必须对应于关系s中某元组在主码属性上的取值。例如foreign key（dept_name） references department。这个外码的声明对每个课程元组来说，元组代表的属性，这个元组表示的系名必定存在于department表的属性即dept_name中。
• not null:不允许空值

插入：
insert into table1
		values('www',dd,1111);
		
删除(保留表)：
delete from table;

删除表（删除关系）：drop table r;

增加属性（给表增加列）：alter table r add name_2; #给表r增加name_2列
删除属性：alter table r drop name_1;#把表r中name_1列删除

3.3 SQL查询的基本结构

基本是由select,from,where构成。

3.3.1单关系查询

例子1：找出学生表中所有学生的名字

select name from student;

例子2：找出表中所有学生所在的学院（系）

select dept_name from student;

这个时候得到的是一个有重复的表，因为很多学生在同一个学院。
去除重复：

select distinct dept_name from student;

指明不去除重复：

select all dept_name from student;

例子3：select 句子可以带含有运算符号的算术表达式

select ID,name salary*1.1 from teacher;

返回的工资属性是原来的1.1倍，但是对原来的表属性不好造成影响。

例子4：where子句允许选出在结果中满足特定谓词的元组。
找出所有在CS院系并且工资超过10k的老师的名字。

select from teacher
where dept_name='CS' and salary >10000;		

在where中可以使用and,or,not。

3.3.2 多关系查询

例子1：找出所有教师名字以及他们的学院和学院所在的教学楼。
这个例子需要从departmnet表中获取building属性，并且teacher表和department表的dept_name属性要匹配。

select from name,dept_name,building from department,teacher
where teacher.dept_name = department.dept_name;

理解运算的以运算顺序来考察各个句子：首先from,然后where,最后select。
简单来说就是：将teacher表与department表作笛卡尔乘积，teacher表的属性1与department表所有属性都要匹配一次，重复之后。如果teacher表有n个元组,department有m个，则得到mn个新元组。使用where选择出teacher表中的dept_name与department的dept_name匹配（相等）的那些元组。最后使用select从剩下的新元组中返回我们需要的属性。

for each 元组t1 in 关系r1
	for each 元组t2 in 关系r2
		for each 元组t3 in 关系r3
		...
			for each 元组tm in 关系rm
			把t1,t2...tm连接为单个元组t
			把t加入结果关系中

但值得注意的是，这个查询只返回有教学楼的院系。如果有一个院系没有教学楼，则不会输出没有教学楼的院系的老师。

3.3.3 自然连接

natural join自然连接不同于笛卡尔乘积，它只考虑在两个关系只都出现的属性上取值相同的元组。及上述的例子中，teacher表的dept_name=deparment.dept_name。
自然连接不会重复列出相同的属性列，即在新元组中dept_name只出现一次。
所以上述例子1可以写为：结果是一致的。

select from name,dept_name,building from department,teacher
where teacher natural join teacher;

自然连接的表也可以放在from中。
例子1：查询教师的名字以及他们所教授的课程的名称。

select name,title
from teacher natural join teaches,course
where teacher.course_id = course.course_id;

之所以这样写是因为
teacher自然连接teaches结果为(ID,name,dept_name,salary,course_id,sec_id)。
course(course_id,title,dept_name,credits)。
例子2：如果写为：

select name,title
from teacher natural join teaches natural join course;

teacher natural join teaches 再自然连接course，需要这两个输入元组在dept_name上相同，并且在course_id上取值也要相同。如果一个老师教授的课程不是他所在学院的课程，那么这样的（name,title）不会被查询出。
为了避免这种情况，除了使用例子1之外，自然连接可以指定哪些列相等。

select name,title
from(teacher natural join teaches) join course using (course_id);

3.4 附加基本运算

3.4.1 更名运算

把A更名为B。as子句可以出现在select子句中也可以出现在from子句中。

A  as B

被用来重命名表的标识符在SQL标准中被称为相关名称，通常也称为表别名，或者相关变量。或者元组变量。

3.4.2 字符串运算

用单引号来标识字符串，当字符串里含有单引号时，用两个单引号来标识。

it's ok 表示为 it''s ok

SQL标准对大小写敏感，但是在一些sql数据库中可能不区分，这种方式可以在数据库中修改。
SQL允许字符串上运行多种函数，例如串联使用“||”，大小写转换upper(),lower等。这个根据数据库的不同而不同。
字符串上可以使用like实现模式匹配。

 - %：匹配任意字符
 - _：匹配任意一个字符
 - 'Intro%'匹配任何以“Intro”打头的字符串
 - '% Intro%'匹配任何含有“Intro”的字符串。
 - ‘_ _ _’匹配只含有3个字符的字符串
 - ‘_ _ _%’匹配至少含有3给字符的字符串

转义字符：使特殊字符被当成普通字符。在like中使用escape关键词来定义转义字符。举个例子定义’'为转义字符。

 - like 'ab\%cd%' escape '\' 匹配所有以ab%cd开头的字符串
 - like 'ab\\cd%' escape '\'匹配以所有以“ab\cd”开头的字符串

3.4.3 select子句中的属性说明

*代表所有属性

3.4.4 排列元组中的显示次序

order by 子句中，默认升序。降序添加desc，asc表示升序。
在下面这个例子中，将老师表所有的属性按照工资升序，名字升序来表示。

select *
from teacher
order by salary,name asc;

3.4.5 where子句谓词

使用between比较运算符来说明一个值是小于还是等于某个值。可以取代<= =>。
也可以使用not between。
可以使用元组运算符，例如（a1,a2)<=(b1,b2)在a1<=b1,a2<=b2时为真。
当两个元组在所有属性上相等时，他们是相等的。

3.5 集合运算

union — 并集
intersect — 交集
except — 差集
例子1： 2009年球季学期开始的课程
select course_id
from section
where semester = ‘fall’ and year = 2009

3.5.1 并运算

union运算自动去除重复。想保留重复使用union all。

3.5.2 交运算

intersect运算自动去除重复。想保留重复使用intersect all。

3.5.3 差运算

except执行运算之前，自动去除第一个输入中的重复。它输出出现在第一个输入而不在第2个输入中的元组在Oracle中，用minus代替except。保留重复使用except all，这个时候结果中的重复元组数为输入1中重复数减去输入2中重复数。
例如 数学1在秋季学期开4个课段，在春季是2个。秋季-春季那么结果是2给数学1 的元组。当输入1中重复元组数（例如数学1）比2少时，结果不存在数学1。

3.6 空值

算术表达式任何一个输入是空，那么结果也是空。包括加减乘除。和null 的and nor not运算结果都是unkown。
null用来测试空值。注意null = null 返回是unkown而不是true。

select name from teacher where salary is null.

3.7 聚集函数

avg,min,max,sum,count.

3.7.1 基本聚集

计算均值时会保留重复的元组。

select avg(salary) as 'avg_salary'
from teacher
where dept_name = 'CS';

想删除重复元组时，可以在聚集表达式中使用distinct。教师交了几个课程段，他应该只被计算一次。

select count(*)
from course;

select count(distinct ID)
from teaches
where semester = 'Spring' and year = 2010;

SQL中count(*)不允许使用distinct，可以使用all说明保留重复，但all是默认的。
max,min可以使用distinct。

3.7.2 分组聚集

group by中所有的属性取值相同的元组会被分在一个组中。
查出每个学院的平均工资。

select dept_name,avg(salary) as avg_salary
from teacher
group by dept_name;

注意的是：select中没有被聚集的属性必须出现在group子句中。因为group by之后的分组中每一位教师都有一个不同的id，select使每个分组只输出啊一个元组。

3.7.3 having子句

针对分组的限定条件。只能在形成分组之后使用。
出现在having中，但是没有被聚集的属性必须出现在group by中。

3.7.4 对空值和布尔值的聚集

除了count(*)之外的所有聚集函数都忽略输入集合中的空值。空集中的count运算时0，其他聚集运算在输入值集合为空集使返回空值。
布尔类型：true,false,unkonown，两个聚集函数some,every。

3.8嵌套子查询

子查询嵌套在where子句中。

3.8.1 集合成员资格

in 测试元组是否是集合中的成员，集合是select子句产生的一组值构成。not in 测试不是。也可以用于枚举集合。
查询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)

3.8.2 集合比较

至少比集合中的一个大可以使用>some表示。
找出一些教师至少比Biology系中某一个教师的工资要高。

select distinct name
from teacher 
where salary > some(select salary 
								from teacher 
								where dept_name = 'Biology');

• =some 等价in
• <> some与not in 不等价
• >all 比所有的都大
• <> all 等价 not in
• =all 不等价in

3.8.3 空关系测试

查询一个子查询的结果中是否存在元组。
exists 在非空中返回true。
找出同时在2009秋与2010春开设的课程。

 select course_id
 from  section as S
 where semester = 'Fall' and year = 2009 and exists
 (select *
 from section as T
 where semster = 'Spring' and year =2010 and S.course_id = T.course_id);

not exists 模拟包含操作。
A包含B写成 not exists（B except A)

3.8.4 重复元组存在性测试

unique 在有重复元组时返回true。经常用于查询最多一次。
where unique (…)
还可以使用1 >= (count())

3.8.5 with

with max_budget(value)
as 
(select max(budget)
from department)
select budget
from department , max_budget
where department.budget = max.budget.value;

3.8.6 标量子查询

子查询中返回单个值的表达式。只要该子查询返回包含单个属性的单个元组。
可以出现在select,where,having中。

3.9 数据库修改

• 删除：delete from 关系r where p； 只能删除元组。不写where就删除全部元组，保留关系。
• 插入：Insert into course values(abuhiuhidhwi,iiji,io9oi11);如果要插入子查询的结果，最好是先执行完select子句。即insert into r values(select …);
• 更新：update table r set a = 1;为了避免更新次序问题，可以使用case。

 update r 
 set a = case
 		when c < 0 then a *2
 		else a *4
 	end


case 
	when p1 then result1
	when p2 then result 2
...
	else relsultn
end