数据库-高级SQL语句

#4

SQL中的内置数据类型
1.date:以yyyy-mm-dd格式由year-month-day组成

‘2005-07-27’

从日期/时间/时间戳中提取单个字段的值

extract (year from r.starttime)

可以将字符串类型转换为日期/时间/时间戳

cast <string-valued-expression> as date

2.Time:构成小时:分:秒的格式hh:mm:ss

‘09:00:30’

3.Time(i):由小时:分钟:秒+额外的第二种数字格式指定分数hh: mm: ss: ii…

‘09:00:30.75’

4.timeStamp:日期+一天

‘2005-7-27 09:00:30’

5.Interval:一段时间

用户定义数据类型
1.create type:SQL中的构造创建用户定义的类型

create type Dollars as numeric (12,2) final 

2.create domain:创建用户定义的域类型

create domain person_name char(20) not null

Domain Constraints：域约束是完整性约束的最基本形式。它们测试插入数据库的值，并测试查询以确保比较有意义。我们不能将类型美元的值与类型英镑的值进行赋值或比较。

Integrity Constraints：完整性约束通过确保对数据库的授权更改不会导致数据一致性的丢失，从而防止意外损坏数据库。举例来说就是支票帐户的余额必须大于$10,000.00。

Large-Object Types：大型对象类型
1.Blob：二进制大对象——对象是大量未解释的二进制数据的集合(其解释留给数据库系统之外的应用程序)。
2.Clob：字符大对象,对象是一个大量的字符数据查询时返回一个大对象,返回一个指针,而不是对象本身。

单个关系的约束
1.not null

create domain Dollars numeric(12,2) not null

2.Primary key

unique ( A1, A2, …, Am)

3.Unique
4.Check（P）,where p is a predicate,例如要确保assets非空

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

又比如

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

域有一个约束，确保hourly_wage大于4.00
子句约束value_test是可选的;用于指示更新违反了哪个约束。

Referential Integrity：确保在一个关系中为给定属性集出现的值也在另一个关系中为特定属性集出现，如果“Perryridge”是帐户关系中的一个元组中出现的分支名称，则分支“Perryridge”的分支关系中存在一个元组。主键、候选键和外键可以作为SQL create table语句的一部分指定。主键子句列出了组成主键的属性。unique子句列出了组成候选键的属性。外键子句列出了组成外键的属性和外键引用的关系的名称。（默认情况下，外键引用引用表的主键属性。）

create table branch
	(branch_name        char(15),
	branch_city	        char(30),
	assets	            numeric(12,2),
	primary key (branch_name ))

以及

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 )

Assertions:一种我们希望数据库一直满足的一种东西（断言）
示例1

create assertion <assertion-name> check <predicate>

示例2

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)))

示例3

create assertion sum_constraint check (not exists 
  	(select *
   	from branch
    where (select sum(amount )
          from loan
               	where loan.branch_name = branch.branch_name )
             	>= (select sum (balance ) 
           		from account
              	where account.branch_name = branch.branch_name )))

Authorization：修改权限
1.Read: allows reading, but not modification of data.
2.Insert: allows insertion of new data, but not modification of existing data.
3.Update: allows modification, but not deletion of data.
4.Delete: allows deletion of data.
5.Index - allows creation and deletion of indices.
6.Resources - allows creation of new relations.
7.Alteration - allows addition or deletion of attributes in a relation.
8.Drop - allows deletion of relations.

Grant：用于授予授权，对视图授予特权并不意味着对底层关系授予任何特权，特权授予者必须已经拥有指定项上的特权(或者是数据库管理员)。

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

1.Select

grant select on branch to U1, U2, U3(授予用户U1、U2和U3选择分支关系上的授权)

2.Insert
3.Update
4.Delete
5.All privileges

Revoking Authorization:撤销授权，依赖于被撤销的特权的所有特权也将被撤销，同一权限被不同的管理员让人两次授予同一用户的，撤销后该用户可以保留该权限。

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

撤销选择权限

revoke select on branch  from U1, U2, U3

Embedded SQL：嵌入式sql。SQL标准用各种编程语言(如C、Java和Cobol)定义SQL的嵌入。嵌入SQL查询的语言称为宿主语言，宿主语言中允许的SQL结构包含嵌入式SQL。
1.从宿主语言中，查找某些帐户中拥有超过可变金额美元的客户的名称和城市。

EXEC SQL
declare c cursor for 
	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

2.open语句使查询得到计算

EXEC SQL open c END_EXEC

3.fetch语句将查询结果中的一个元组的值放在宿主语言变量上。

EXEC SQL fetch c into :cn, :cc END_EXEC

4.close语句导致数据库系统删除保存查询结果的临时关系。

EXEC SQL close c END_EXEC

Cursors：通过声明cursor 是用于更新的，可以更新游标获取的元组

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

然后更新

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

Dynamic SQL:允许程序在运行时构造和提交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;