SQL(上)

2018-07-08  本文已影响0人  我好菜啊_

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


上一篇下一篇

猜你喜欢

热点阅读