一 、 数据定义语句(DDL)
1、表的定义
Create table R
(
att1 type1 Primary Key,
att2 type2 Unique,
att3 type3 NOT Null
)
注意事项:
- 不同属性之间用逗号隔开,最后一个属性后面没有逗号
- 定义语句在小括号里面(容易和大括号弄混)
定义每条语句的格式为
属性名 属性类型 约束
a、数据类型
对于大多数的DBMS包含以下的数据类型:
字符型:Char(n) Varchar(n)
整型:integer
浮点型:float
时间:Date Time //分别格式为yyyy-mm-dd hh:mm:ss.sssss
布尔型:boolean (True,False,Unknown)
b、约束
NOT NULL 不允许为空
UNIQUE 唯一的值
DEFAULT + 默认值/curdate()
auto_increment 比最大的加一
c、主键声明
#单一主键的时候使用
Create table salesitem
(
orderno char(10) primary key,
lineno char(4)
)
#多个主键的时候使用
Create table salesitem
(
orderno char(10),
lineno char(4),
price int, //注意这里有逗号
Primary key(orderno,lineno)
)
2、对于表的相关操作
删除表
Drop talbe T; //此时将删除T中的所有数据
修改关系
Alter Table R add column-name datatype Not Null //为已建立的表添加属性 Alter Table R drop column-name //删除表中某属性 Alter table R Rename s // 修改关系名 Alter table R Rename column-name1 to column-name2 //修改属性名
3、约束
a.键约束
违背操作:insert,update
- 两种定义键的方法
- 定义候选键使用unique
- 如果一个键是单一的,那么他们默认都是unique;如果一个键包含多个属性,那么他们的所有属性都不是unique
- primary key 可以为多个属性,但是unique只能是一个属性
b.外键约束
违背操作:
- 参照表:update,insert
- 被参照表:update,delete
1、定义方式
# 方法一
<attr1> 类型 references R(attr2)
Create table salesitem
(
orderno char(10),
lineno char(4) references product(lineno),
price int,
Primary key(orderno,lineno)
);
# 方法二
foreign key <att1> references R(att2);
Create table salesitem
(
orderno char(10),
lineno char(4),
price int,
Primary key(orderno,lineno),
foreign key lineno references product(lineno)
);
- 外键可以自我参照
- 外键可以为空值
2、定义处理方式
# 缺省策略——拒绝
# 级联策略: 同时修改被参考表 (cascade)
# 置空策略: 将被参考表置空 (set null)
# 定义语法: on <operate> 策略
Create table salesitem
(
orderno char(10),
lineno char(4),
price int,
Primary key(orderno,lineno),
foreign key lineno references product(lineno) # 无逗号
on delete set null
on update cascade
);
c. check约束
检查时间:update,insert
基于属性的check约束
custid INT check(custid>0)
基于元组的check约束
check(auditdate>=signdate)
当属性约束和元组约束同时存在时,属性约束首先被检查
d.修改约束
为约束命名
gender int constraint NoAndro Check(gender in(0,1))
修改约束
Alter table R drop constraint ... # 删除约束 alter table R add constraint ... # 添加约束
4、触发器
# 基本语法
create trigger trigger_name trigger_time trigger_event #其中的event包含insert,delete,update
on table_name
Referencing
old row as old_row
new row as new_row # 定义新旧数据的名称
for each {row/statement} # 定义是每次语句作用一次还是每行作用一次
statement # 作用语句
5、视图的定义
- 视图定义
Create view name(name1,name2) as # 此处可以用于属性重命名
statement
## 例子
create view paramountMovie as
Select title,year
from movies
where studioName = 'paramount';
二、数据控制语言(DCL)
1、授权
grant <权限列表> on <关系名称> To <用户列表>
grant select,insert on Movies To Jack with grant option; # 对Jack用户在movie关系中加以select和insert的权限
2、撤销
revoke <权限列表> on <关系名称> from <用户列表>
# cascade:级联收回权限
# restrict:级联无效
三、数据操纵语言(DML)
1、基本语法
# 基本语法
Select [ALL/Distinct] select-list
From R #关系名称
Where cond
Order by [ASC/Desc]
a、select
select * from R #搜素所有元组
select att from R #搜索att属性
select custid id from R #改名
select unitpirce*quantity total from R #表达式
select att,"const" as "const-name" from R # 添加常量
b、from
注意子查询
c、where
= <> > >= < <= is #基本的符号(注意boolean不可用=)
#字符比较采用Ascii
select * from R where name like "wang%"
# 模式识别
## %:匹配0或多个字符
## _:匹配单个字符
# 对于时间的比较
select * from R where signdate < '2007-01-1'
select * from R where signdate between '2007-10-1' And '2007-10-31'
d、order by
select * from R order by signdate Desc,orderno; #默认为primary key Asc正序,可以设定为任意属性顺序
2、Union
多数DBMS只支持Union,不支持Intersect和Except
注意在 我们不使用all时,运算就会使左右同时存在的合并,及包上面的运算
select name,phone,from customer
Union All
select name,phone from salesman;
3、子查询
a. any和all
select name,phone from customer
where custid = any(
select custid from salesorder where empid="A0043" #注意这里没有分号
);
any: 在该子查询中的任意一个值
all: 在子查询中的所有值(可以用于求最大值)
b. exists和in
select name,phone from customer
where custid in(
select custid from salesorder where empid="A0043";
);
select name from student
where exists(select * from enrollment where cid='c1' and sid= student.sid);
in : = any(subquery)
not in : <> all(subquery)
exists: 当之后的不为空,那么结果就为真,这里用到了外关系
c.差与交的替代
差: R-S : R<>all(S) 或者 R Not in (s)
交:R =Any (S)或者 R in (S)
4、连接
注意连接条件on 和选择条件where 的区别
a.笛卡尔连接
select * from R1 Cross join R2 on cond where cond
b.自然连接
select * from R1 Natural join R2
c. theta- join
select * from R1 join R2
d.外连接
所有的连接默认是inner join,在这样的条件下,会出现悬浮元组,所以我们可以用outer join将悬浮元组去除
left outer join #所以左边的悬浮元组出现在结果中,右边用NULL代替
right outer join #所以右边的悬浮元组出现在结果中,左边用NULL代替
full outer join #所有悬浮元组都会出现在结果中
5、聚合运算和group by
a.聚合算子
sum(C):null如果存在会忽略不计,只有null结果为null
AVG(C):null如果存在会忽略不计,只有null结果为null
Min(C):null如果存在会忽略不计,只有null结果为null
MAX(C) : 注意在有多个值的时候只保留一个值,null如果存在会忽略不计,只有null结果为null
Count(*/C):null不计入总值,但是结果不为null
Select Count(*) from salesman where signdate between '2015-10-1' and '2016-10-1';
select sum(unitprice*quantity) as amount from salesitem where orderno = 4001;
b. Group by
作用:例如一个关系中有多个产品,我们想要知道每个产品的销售总价格,那么可以用group by 对结果进行分类
select itemid,sum(unitprice*quantity) as amount from item
group by itemid;
注意,当使用group by时,能在select 子句出现的只能是聚合算子和group by中的内容
c. having
作用:用于筛选在分组后的组
例如: 查询所有产品中销售额高于1000的组的销售金额
select itemid,sum(unitprice*quantity) from item group by itemid having sum(unitprice*quantity)>1000;
where子句中无法加入聚合运算,having子句可以加入聚合
6、数据操作(Insert,update,delete)
a. insert
Insert into R(A1,A2,A3...) values (V1,V2,V3...); #若缺失某个属性会自动补上缺省值,若不带参表就会按照定义格式给出
Insert into R(A1,A2,A3...) SELECT B1,B2,B3... from ... where.... # 添加通过子查询
b. delete
delete from R where cond;
c. update
update R set A1= V1,A2=V2,....where cond;