数据库笔记
1.SQL语言
》数据定义语言（DDL）：定义关系模式、删除关系、修改关系模式
》数据操纵语言（DML）：查询信息、插入元组、删除元组、修改元组
》完整性：SQL DDL包括定义完整性约束的命令
》视图定义：SQL DDL包括定义视图的命令
》事务控制：SQL包括定义事务的开始和结束的命令
》嵌入式SQL和动态SQL：定义SQL语句如何嵌入到通用编程语言，如C、C++、JAVA中
》授权：SQL DDL包括定义对关系和视图的访问权限的命令
2.基本数据类型
》char(n):固定长度n的字符串，长度不够时自动加空格；
》varchar(n):可变长度的字符串，最大长度为n，不够时不会自动加空格；
》int:整数类型；
》smallint:小整数类型；
》numeric(p,d):定点数，p位数字（包括小数位），其中d位为小数位；
》real，double precision：浮点数与双精度浮点数；
》float(n):精度至少为n位的浮点数。
》date:日历日期，包括年（四位）、月、日；
》time:一天中的时间，包括小时、分和秒，time(p)指定秒后面小数位的位数（默认0）；
》timestamp:date 和time 的组合，timestamp(p)指定秒后面小数位的位数（默认6）
3.SQL语法
3.1创建关系表
通用格式：
create table r r关系名
(A1 D1, Ai属性名，Di属性AI的域
A2 D2,
...,
An Dn,
<完整性约束1>，
...,
<完整性约束1>);
部分完整性约束：
》primary key(Aj1,Aj2,...,Ajn):声明属性Aj1,Aj2,...,Ajn构成主码。主码：必须唯一且非空；
》foreign key(Aj1,Aj2,...,Ajn) references s:表示关系中任意元组的属性（Aj1,Aj2,...,Ajn）上
的取值必须对应于关系s中某一元组在主码上的取值。
》not null：表明该属性上不允许空值。
eg：
create table teaches
(
ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
tel numeric(11,0) not null,
primary key(ID,course_id,sec_id,semester,year),
foreign key(course_id,sec_id,semester,year) references section,
foreign key(ID) references instructor
);
删除表（关系）：
drop table r；
增加属性，新属性上的值为null：
alter table r add A D;
去掉属性：
alter table r drop A;
3.2查询
单关系查询：
eg:
select ID ,distinct name,salary*1.1 from instructor as inst
where inst.dept_name ="" and salary > 7000;
》distinct:去除重复，all：保留重复，默认是保留重复；
》子句可以含有+、-、*、/运算符的算术表达式；
》where子句：and、or、not 、<、<=、>、>=、=和<>
多关系查询：
eg：
select name,instructor.dept_name,building from instructor , department
where instructor.dept_name = department.dept_name
3.3连接
3.3.1 内连接
from instructor natural join teaches :instructor和teaches中相同属性上值相同的元组组成的关系
from instructor join teaches using(ID) :instructor和teaches中相同属性中ID属性上值相同的元组组成的关系
from student join takes on student.ID = tekes.ID :instructor和teaches中相同属性中ID属性上值相同的元组组成的关系
eg：
*instructor和teaches中有相同属性ID和name，
那么natural join 就是由ID和name都相同元组组成的关系；
那么join...using(ID) 就是不管name,由ID相同元组组成的关系(括号里面可以带多个属性用，隔开)；
* on条件可以表示任何SQL谓词
3.3.2 外连接
(1)左连接(left outer join):保留左连接运算之前的关系，之后的关系并无内容的以null补足；
(2)右连接(right outer join)：保留右连接运算之后的关系，之前的关系并无内容的以null补足；
(3)全连接(full outer join)：保留全连接运算两边的关系，无内容的关系以null补足；
eg:
select * from student left outer join takes:
select * from takes right outer from student:
--上面结果一样，都是显示全部的student关系，如果takes中无对于关系，则以null补足
select * from students full outer join takes:
--显示全部的student和takes关系，如果有谁无对应关系，自动以null补足
3.3.3on与where的区别
eg:
select * from student left outer join takes on student.ID=takes.ID;
--on字句是外连接的一部分，以ID为连接条件，进行连接，若无对应关系，则以null补足
select * from student left outer join takes on true where student.ID=takes.ID;
--where不是外连接的一部分，先进行外连接，再进行where的甄选。
连接类型 连接条件
inner join natural
left outer join on <predicate>
right outer join using (A1,A2,A3...)
full outer join
3.4基本运算
更名运算：as 在from 和select后面子句都可以用
字符串运算：
upper(s):转换成大写 lower(s):转换成小写 trim(s):去除后面空格 等等...
字符匹配：like not like
>百分号(%):匹配任意子串
>下划线(_):匹配任意一个字符
在like中用escape定义转义字符
eg：
select dept_name from department where building like 'ab\%cd%' escape '\' :匹配所有以ab%cd开头的字符串
显示次序：order by
eg:
select * from instructor order by salary desc,name asc;
:按薪水降序排序显示，在薪水相同时按姓名升序排 （默认是升序）
where子句谓词：
where salary<=10000 and salary >=8000 salary between 8000 and 10000 (not between)
where (instructor.ID,dept_name)=(teaches.ID,'Biology')
集合运算：并(union) 交(intersect) 差( except)
eg：
(select course_id from section where semester='Fall' = year=2009)
union
(select course_id from section where semester='Spring' = year=2010)
默认是自动去除重复的
如要保留重复 用union all intersect all except all
3.5空值
SQL涉及空值的任何比较运算的结果都是为unknown
布尔类型：true false unknown
and : * and unknown = unknown
or : true or unknown = true false or unknown = unknown unknown or unknown = unknown
not : not unknown = unknown
测试是否是空值：salary is null salary is not null
3.6聚集函数 分组 having子句
>平均值：avg
>最小值:min
>最大值:max
>总和:sum
>计数:count
sum和avg输入必须是数字集
eg：
select avg(salary) as avg_salary from instructor where dept_name='Comp.Sci';
分组：group by
eg：
select dept_name ,avg(salary) as avg_salary from instructor group by dept_name;
:先对instructor根据dept_name进行分组，再在每个组内进行select
*需要保证出现在select语句中但没有被聚集的属性只能出现在group by中
having子句：
eg：
select dept_name ,avg(salary) as avg_salary from instructor group by dept_name having avg(salary)>4200;
:先对instructor根据dept_name进行分组,再对每一组进行having筛选，之后进行select
*需要保证出现在having语句中但没有被聚集的属性只能出现在group by中
空值和布尔值的聚集：
除count外所有聚集函数都忽略空值。
规定空集的count为0,其他所有聚集运算在输入为空集的情况下返回一个空值。
3.7嵌套子查询
in：测试是否是集合中的成员
eg:
select 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' );
用于枚举：
select name from instructor where name not in('Mozart','Einstein');
多属性：
select count(distinct ID) from takes where (course_id,sec_id,semester,year)
in (select course_id,sec_id,semester,year from teaches where teaches.ID =10101);
集合比较：
some:某一个
>some:比某一个大 >=some：大于或等于某一个
<some:小于某一个 <=some：小于或等于某一个
=some:等于某一个 <>some: 小于或大于某一个
all：所有
>all:大于所有 >=all:大于或等于所有
<all:小于所有 <=all:小于或等于所有
=all:等于所有 <>=all:大于或小于所有
exist：是否存在 (not exist)
eg:
select course_id from section as S where semester='Fall' and year='2009'
and exist(select * from section as T where semester='Spring' and year='2010' and S.course_id=T.course_id);
重复元组测试：unique/not unique
eg:
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);
有重复：false
无重复：true
from子句的子查询：
eg:
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>4200;
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,dept_total_avg where dept_total.value>=dept_total_avg.valur;
3.8修改
删除：
delete from instructor where P;
插入：
insert into course values('CS-437','DataBase','Com.Sci',4) :数据按关系属性顺序排列
insert into course(course_id,title,dept_name,credits) values('CS-437','DataBase','Com.Sci',4) :
insert into instructor select ID,name,dept_name,18000 from student where dept_name='Music' and tit_cred>144;
更新：
update instructor set salary = case when salary<=10000 then salary*1.05 else salary*1.03 end;
case的一般格式：
case
when pred1 then result1
when pred2 then result2
...
when predn then resultn
else result0
end
