SQL(上)
SQL查询中的子句顺序
select,from,where,group by,having,order by
前两个是必须
SQL结构化查询语言,即是数据操作语言又是数据定义语言。
创建表
create table movies(
title char(100),
year int,
……
)
删除表
DROP TABLE <表名> [RESTRICT | CASCADE];
若选择RESTRICT,则要删除的基本表不能被其他表的约束所引用(如CHECK,FOREIGN KEY等),不能有视图、触发器、存储过程等,否则将不能删除该表。缺省默认为RESTRICT。
若选择CASCADE,则将无条件地删除该表,而且在删除该基本表的同时,相关的依赖对象(如视图)将被一起删除。
drop table movies
修改表
ALTER TABLE <表名>�
[ADD <新列名> <数据类型> [完整性约束]]�
[ADD <完整性约束>]
[DROP <COLUMN 列名>]�
[DROP <完整性约束名>]�
[ALTER COLUMN <列名> <数据类型>];
alter table movieStar add phone char(16)
//每个元组的该字段会自动置为空值或默认值
alter table movieStar drop phone
//增加name属性唯一的完整性约束
alter table movieStar add unique(name)
默认值
一个常量、系统函数以及NULL都可以作为默认值。
//create table中
phone char(16) default 'unlisted'
//修改表时
alter table movieStar add phone char(16) default 'unlisted
声明键
1.primary key
2.unique(允许null,两个元组不能在S的所有属性上具有完全相同的值,除非其中
有一个是null值)
//1
name char(30) primary key
//2
primary key(title,year)
外键
foreign key(bookid) references notebook(id)
注意两个属性类型要相同
查询
基本语句
select title AS name,length*0.016667 AS lengthInHours,'hrs.' AS inHours
from movies
where year=1990 and studioName='Disney'
第一句是个扩展投影
AS重命名,也可用空格或等号
可以用属性名,表达式,甚至一个常量如'hrs.'
除了加减乘除这些运算符,还可以使用||进行字符串的连接
逻辑运算符优先级
NOT>AND>OR
WHERE Sage BETWEEN 20 AND 23;
WHERE Sage NOT BETWEEN 20 AND 23;
WHERE Sdept IN (‘计算机’,’经管’) ;
位串的表示
二进制:B'011'
十六进制:X'7ff'
模式匹配
s Like p
s是字符串,p是模式
%:可以与s中任意长度(包括0长度)字符串匹配
_:可与s中的任意一个字符匹配
[ ]: 指定范围内的任意单个字符
[ ^]:不在指定范围内的任意单个字符
LIKE ‘[CK]%’ 第1个是C或K
LIKE ‘[^A-D]%’ 第1个不是A-D之间
s NOT Like p
!!注意SQL中,用连两个连续的单引号表示一个单引号
若需要在p中直接使用%,_需要用ESCAPE指定转义字符,跟在转义字符后面的%就是%本身
s Like 'x%%x%' ESCAPE 'x'(将x指定为转义字符)
日期和时间
DATA'1983-06-17'
TIMA'15:00:02.5' 采用24小时制
TIMESTAMP'1948-05-14 12:00:00'
可以使用比较运算符,<表示前一个比后一个早
空值与涉及空值的比较
NULL
未知值,不适用的值,保留的值
1.对NULL和任何值(包括NULL)进行算术运算,结果仍是空值
x为NULL,x+3也为NULL,但NULL+3是不合法的表达式
2.比较运算结果为unknown
x=3是unknown,但NULL=3不合法
0*NULL为NULL,x-x为NULL
布尔值unknown
令true为1,unknown为1/2,false为0
1.两个布尔值AND取两者之间小的那个
2.两个布尔值OR取两者之间大的那个
3.布尔值v的非是1-v
where length<=120 or length>120
直观上看是返回所有元组
但如果有个元组的length为null,unknown or unknown就是unknown,该元组不会被返回
就变成了返回所有length字段非空的元组
输出排序
order by <list of attributes>
order by A,B,C+D DESC
默认升序ASC,加了DESC就是倒降序
排序位于所有子句之后,在from,where和别的子句的结果上进行排序,排序后在应用select的投影,所以排序用到的属性不属于投影属性也没关系
多关系查询
movies(title,year,length,genre,studioName,producerC#)
movieExec(name,address,cert#,netWorth)
select name
from movies,movieExec
where title='star wars' and producerC#=cert#
当有属性重名的时候可以用R.A
元组变量
若查询中使用到同一个关系的多个元组,可在from中将关系R列出任意次,为每次出现的R定义一个别名 R (AS)NR
select star1.name,star2.name
from movieStar star1,movieStar star2
where star1.address=star2.address and star1.name<star2.name
查询并,交,差
应用在多个查询结果上,这些查询结果提供的关系具有相同的属性和属性类型列表(模式相同)
交
(select name,address from movieStar where gender='F')
intersect
(select name,address from movieExec where netWorth>10000000)
差
(select name,address from movieStar)
except
(select name,address from movieExec)
并
(select title,year from movie)
union
(select movieTitle as title,movieYear as year from starsIn)
注意这里的重命名
子查询
某个查询是另一个查询的一部分
1.返回单个常量,在where子句中与另一个常量进行比较
注意是单个!!
注:一个能成为元组字段值的原子值称为标量
select name
from movieExec
where cert#=
(select producerC#
from movies
where title='star wars')
2.子查询返回关系在where中使用
条件表达式
s为单个的值,R为一元关系
exist R:当R非空时为真 not exist R
s in R:s等于R中的某个值
s not in R:s不等于R中的任何一个值
s > all R:s大于R中的任何一个值 not s > all R
s > any R:s至少大于R中的某个值 not s > any R
t为元组,R为关系
应用上面的条件表达式时是将t与R中的元组进行比较
select name
from movieExec
where cert# in
(select producerC#
from movies
where (title,year) in
(select movieTitle,movieYear //并没有要求同名
from starsIn
where starName='tom'
)
)
本质上等价于
select name
from movieExec,movies,starsIn
where cert#=producerC# and
title=movieTitle and
year=movieYear and
starName='tom'
不同的是后者的结果中可能会有重复的值
关联的子查询
简单的子查询只进行一次,查询的结果用于高层查询
复杂的子查询根据查询中某项赋值的不同对子查询进行多次
每次赋给查询中的某项来自子查询外部的某个元组变量的值
找出被两部及以上电影使用过的电影名
movies(title,year,length,genre,studioName,producerC#)
select title
from movies old
where year < any
(select year
from movies
where title=old.title
)
对于该关系的每一个元组,在子查询中找是否有一部电影与它具有相同的名字并且年份比它大(因为where也是一个一个元组带进去比的嘛)
每次根据old的不同进行了多次查询
3.子查询返回关系在from中使用
要为子查询的结果取一个元组变量别名
select name
from movieExec,(select producerC#
from movies,starsIn
where title=movieTitle and
year=movieYear and
starName='tom'
)Prod
where cert#=Prod.producerC#
SQL中的连接表达式
通过将不同的连接运算符作用在两个关系上创建新的关系
交叉连接——>笛卡儿积
R cross join S
θ连接
R join S on C
在积运算RXS的基础上,在根据条件C进行选择
自然连接
对两个关系中具有相同名字且值相同的属性作连接
两个等值的属性只投影一个
R natural join S
外连接
填充悬浮元组,显示在结果中
自然外连接
R natural full outer join S
R natural left outer join S
R natural right outer join S
自然θ连接
R full outer join S on C
R left outer join S on C
R right outer join S on C