数据库概论笔记第四章（高级SQL）

高级SQL

一、数据类型和模式

1.数据类型

1.日期date：yyyy-mm-dd

2.时间time: hh:mm:ss

3.time(i):由小时：分钟：秒加上i个附加数字秒分数09:00:30.75’

4.timestamp时间戳:日期加上时间‘2005-7-7 09:00:30’

5.interval间隔:一段时间。

将一个日期或时间或时间戳相减得到的值。可以添加到日期或时间或时间戳的值中。

6.从日期或时间或时间戳提取部分值

extract (year from r.starttime) 

7.字符串类型强制转换

cast <string-valued-expression> as date
cast <string-valued-expression> as time

2.类型创建

final：不能再用dollar定义其他类型

创建一个2位小数的12位数字叫dollars

create type Dollars as numeric (12,2) final
create type pounds as numeric (12,2) final

创建域：

create domain person_name char(20) not null

数值应该有不同的域（美元和人民币）

创建域的好处：不用将dollar和pound比较

域可以用已经创建过的类型创建。

create domain Dollars numeric(12, 2)
create domain Pounds numeric(12,2)

3.大对象类型（照片、视频）

blob：二进制大对象

clob：字符大对象

大对象类型有的很大有的很小，一般用指针存储

当要返回一个大对象时，返回的是一个指针

4完整性约束

1.not null

branch_name char(15) not null
create domain Dollars numeric(12,2) not null

2.unique

候选码，允许为空。

unique(A1…)

3.PK

主码、unique、not null

4.check

check§，P是判断条件

create table branch
	(branch_name     char(15),
	 branch_city      char(30),
	 assets           integer,
	 primary key (branch_name),
	 check (assets >= 0))

check clause子句：

确保hourly-wage中只存在大于某个值的情况

create domain hourly_wage numeric(5,2)
	constraint value_test check(value > = 4.00)

value_test：用于指示违反什么约束

5.参照完整性

确保出现在一个关系中的值也在另一关系中出现。

primary key子句列出主码的属性

unique子句列出候选码的属性

foreign key子句列出外码的雏形和外码引用关系的名称（默认外码引用被引用表的主码）

create table account
(account_number		char(10),
branch_name			char(15),
balance	            integer,
primary key (account_number), 
foreign key (branch_name) references branch )

create table depositor
(customer_name	char(20),
account_number	char(10),
primary key (customer_name, account_number),
foreign key (account_number ) references account,
foreign key (customer_name ) references customer )

6.断言assertion

强制要求数据库做到的条件

create assertion <assertion-name> 
                    check <predicate>

当一个断言被提出时，系统测试它的有效性，并且在每一个可能违反断言的更新上再次测试它，但是可能会产生开销。

check后只能使用not exist

每个loan都至少有一个贷款者有一个至少1000的账户

create assertion balance_constraint  check
(not exists (
select * 
   from loan
   where not exists ( 
   select *
   from borrower, depositor, account
   where loan.loan_number = borrower.loan_number        
   and borrower.customer_name = depositor.customer_name
   and depositor.account_number = account.account_number
   and account.balance >= 1000)))

7.权限

数据库权限的格式：1.Read：可读不可改

2.insert：允许插入数据但不可修改

3.update：允许修改不许删除

4.delete：删除数据

数据库模式修改的权限格式：

1.index：创建和删除索引

2.resources：创建新关系

3.alteration：添加或删除关系中的属性

4.drop：删除关系

权限的赋予：

grant <privilege list>
on <relation name or view name> to <user list>

user list里可以是public（所有用户）

1.select：查询权限

grant select on branch to U1, U2, U3

U1（人）连接到数据库

2.insert：插入元组的能力

update：更新

delete：删除元组

all privileges：所有允许特权

3.revoke：撤销权限

revoke <privilege list>
on <relation name or view name> 
from <user list>

revoke select on branch  from U1, U2, U3

只能取消授予他的权限，不能取消别人的权限。

允许赋予别人权限：

with grant option

8.嵌入式Embedded SQL

将SQL加入到C、java等语言（host language（宿）主语言）中。

SQL语言添加形式：

EXEC SQL 
<embedded SQL statement>         
END_EXEC

EXEC SQL
declare c cursor for 							//存储一个查询结果的区域，游标区域叫c
select depositor.customer_name, customer_city
from depositor, customer, account
where depositor.customer_name=customer.customer_name        
and depositor account_number=account.account_number
and account.balance > :amount					//:变量就是主变量（主语言中的变量）
END_EXEC

open：打开

EXEC SQL open c END_EXEC

fetch：取，从c中取出第一行的两个元素给cn和cc

EXEC SQL fetch c into :cn, :cc END_EXEC

close：关闭游标

EXEC SQL close c END_EXEC

变量SQLSTATE从SQL通信区域（SQLCA）中，当值为‘02000’时，就结束了（SQLCA.SQLSTATE）

update：通过游标更新

declare c cursor for
	select *
	from account
	where branch_name = ‘Perryridge’
for update

更新当前游标的元组：

update account
set balance = balance + 100
where current of c

9.动态Dynamic SQL

char *  sqlprog = “update account 
set balance = balance * 1.05
where account_number = ?”
EXEC SQL prepare dynprog  from :sqlprog;
char account [10] = “A-101”;
EXEC SQL execute dynprog using :account;

？就是暂时的缺失值，using ：account就是把A-101交给？