数据库基础

一 、 数据定义语句(DDL)

1、表的定义

Create table R
(
    att1 type1 Primary Key,
    att2 type2 Unique,
    att3 type3 NOT Null
)

注意事项:

  1. 不同属性之间用逗号隔开,最后一个属性后面没有逗号
  2. 定义语句在小括号里面(容易和大括号弄混)
  3. 定义每条语句的格式为

    属性名  属性类型  约束
    
    

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、对于表的相关操作

  1. 删除表

    Drop talbe T;   //此时将删除T中的所有数据
  2. 修改关系

    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、视图的定义

  1. 视图定义
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;


标签:暂无标签
版权属于:Jtripper 所有,转载请注明文章来源。

本文链接: https://www.jtripperbacaf.com/index.php/archives/30/

赞 (0)

评论区

发表评论

13+46=?

暂无评论,要不来一发?

回到顶部