一、数据定义语句（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;

一、数据定义语句（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;

数据库基础 2021年 12月 05 日

一 、 数据定义语句（DDL）

1、表的定义

a、数据类型

b、约束

c、主键声明

2、对于表的相关操作

3、约束

a.键约束

b.外键约束

c. check约束

d.修改约束

4、触发器

5、视图的定义

二、数据控制语言（DCL）

1、授权

2、撤销

三、数据操纵语言（DML）

1、基本语法

a、select

b、from

c、where

d、order by

2、Union

3、子查询

a. any和all

b. exists和in

c.差与交的替代

4、连接

a.笛卡尔连接

b.自然连接

c. theta- join

d.外连接

5、聚合运算和group by

a.聚合算子

b. Group by

c. having

6、数据操作(Insert,update,delete)

a. insert

b. delete

c. update

数据库基础

一 、 数据定义语句（DDL）

1、表的定义

a、数据类型

b、约束

c、主键声明

2、对于表的相关操作

3、约束

a.键约束

b.外键约束

c. check约束

d.修改约束

4、触发器

5、视图的定义

二、数据控制语言（DCL）

1、授权

2、撤销

三、数据操纵语言（DML）

1、基本语法

a、select

b、from

c、where

d、order by

2、Union

3、子查询

a. any和all

b. exists和in

c.差与交的替代

4、连接

a.笛卡尔连接

b.自然连接

c. theta- join

d.外连接

5、聚合运算和group by

a.聚合算子

b. Group by

c. having

6、数据操作(Insert,update,delete)

a. insert

数据库基础
2021年 12月 05 日

一、数据定义语句（DDL）

一、数据定义语句（DDL）