第4章 中级SQL

第4章 中级SQL

考试范围： 4.1-4.7

考试题型： 计算题

考试内容：

连接类型（与第3章合并考察）

视图的定义与使用

事务（与17-19章合并考察）

完整性的概念

SQL中如何定义、修改各类完整性(Primary key, foreign key, check, unique, not null, default,添加、删除字段、添加删除约束、添加删除索引等)

SQL数据类型和模式（会用）

授权（grant\revoke\查看授权情况）

连接类型（与第3章合并考察）

• Natural Join

  • 自然联接匹配所有公共属性具有相同值的元组，并且仅保留每个公共列的一个副本

    select * from student join takes on student.ID  = takes.ID
    

• Outer Join

  • 计算联接，然后将一个关系中与另一个关系中的元组不匹配的元组添加到联接结果
    

  

  

视图的定义与使用

• 视图由查询定义

  • create view v_name as < query expression >;
    

  • 视图定义导致表达式的保存

• 视图是一种“虚拟关系”

  --没有薪水的教师视图
  create view faculty as
  select ID, name, dept_name
  from instructor;
  
  --创建部门工资总额视图
  create view departments_total_salary(dept_name, total_salary) as
     select dept_name, sum (salary)
     from instructor
     group by dept_name ;
  
  --删除视图
  drop view departments_total_salary;
  
  
  --使用其他视图定义的视图
  create view physics_fall_2017 as
  select course.course_id, sec_id, building, room_number
  from course, section
  where course.course_id = section.course_id
        and course.dept_name = 'Physics'
        and section.semester = 'Fall'
        and section.year = '2017';
        
  create view physics_fall_2017_watson as
    select course_id, room_number
    from physics_fall_2017
    where building= 'Watson';
  
  

• Materialized Views（实体化视图）

  • 某些数据库系统允许存储视图关系，但它们确保，如果视图定义中使用的实际关系发生更改，视图将保持最新。

    --视图的更新:没有薪水的教师视图
    create view faculty as
    	select ID, name, dept_name
    	from instructor ;
    
    insert into faculty values ('30765', 'Green', 'Music');
    results in:
    insert  ('30765', 'Green', 'Music', null) into instructor,
    or this insertion is rejected
    
    

事务（与17-19章合并考察）

完整性的概念

• Integrity constraints ensure that authorized changes to the database do not violate consistency 
  完整性约束确保对数据库的授权更改不会违反一致性
  

• Types of integrity constraints:
  primary key 
  foreign key
  not null
  unique
  check (< predicate >)
  assertion
  
  完整性约束的类型：
  主键
  外键
  不为空
  独特
  检查（<谓词>）
  断言
  

• 示例

  --创建表中的完整性约束
  create table student (
      ID            char(5),
      name          varchar(20) not null,
      dept_name     varchar(20),
      tot_cred      numeric(3,0),
      primary key (ID),
      foreign key (dept_name) references department) ;
  
  
  
  

SQL中如何定义、修改各类完整性(Primary key, foreign key, check, unique, not null, default,添加、删除字段、添加删除约束、添加删除索引等)

--Primary key, foreign key
create table takes (
    ID              varchar(5),
    course_id       varchar(8),
    sec_id          varchar(8),
    semester        varchar(6),
    year            numeric(4,0),
    grade           varchar(2),
    primary key (ID, course_id, sec_id, semester, year),
    foreign key (ID) references  student,
    foreign key (course_id, sec_id, semester, year) references section ) ;


--检查子句：确保学期是秋季，冬季，春季或夏季之一
create table section (
    course_id varchar (8),
    sec_id varchar (8),
    semester varchar (6),
    year numeric (4,0),
    building varchar (15),
    room_number varchar (7),
    time slot id varchar (4), 
    primary key (course_id, sec_id, semester, year),
    check (semester in (’Fall’, ’Winter’, ’Spring’, ’Summer’)));


--完整性中的级联操作：当违反参照完整性约束时，正常过程是拒绝导致冲突的操作。
create table course (
    …
    dept_name varchar(20),
    foreign key (dept_name) references department
    on delete cascade
    on update cascade,
    . . . 
) ;

--为约束指定名称：约束名称在我们要删除约束时很有用，系统自动分配的约束名称可以在Oracle系统表USER_CONSTRAINTS中找到

	salary numeric(8,2), constraint minsalary check(salary>29000),
	--使用 ALTER 语句删除约束
	alter table instructor drop constraint minsalary;


--禁用约束
	ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
--启用约束
	ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;


--Assertions 断言：断言是一个谓词，表示我们希望数据库始终满足的条件
	create assertion <assertion-name> check <predicate>
	
create assertion credits_earned_constraint check
   (not exists (
         select ID
         from student
         where tot_cred <> (select coalesce(sum(credits),0)
			        from takes natural join course
            		where student.ID = takes.ID
                	and grade is not null 
                    and grade <> ‘F’) ;

• SQL 中的索引定义

  • 关系属性上的索引是一种数据结构，它允许数据库系统有效地查找关系中具有该属性指定值的那些元组，而无需扫描关系的所有元组

  • 索引创建

    create index <index-name> on <relation-name> (<attribute-list>);
    
    --示例
    create table student	
    (ID varchar (5),
     name varchar (20) not null,
     dept_name varchar (20),
     tot_cred numeric (3,0) default 0,
     primary key (ID))
     
    create index studentID_index on student(ID);
    The query:
    select * from student where ID = '12345’;
    --可以通过使用索引查找所需记录来执行，而无需查看学生的所有记录
    
    

SQL数据类型和模式（会用）

• 时间数据类型：date、time、timestamp（时间戳）
  
  类型转换函数：cast
  	select cast(ID as numeric(5)) as inst_id from instructor order by inst_id;
  	
  合并函数：接受任意数量的参数，所有这些参数都必须属于同一类型，并返回第一个非空参数
  	select ID, coalesce(salary, 0) as salary from instructor;
  	
  大对象类型：binary large object (blob)、character large object (clob)
  	E.g. image blob(10MB)、book_review clob(10KB)
  
  用户定义类型
  create type Dollars as numeric (12,2) final;
  create table department(dept_name varchar (20),building varchar (15),budget Dollars);
  
  
  
  

授权（grant\revoke\查看授权情况）

• -数据库各部分的授权形式：
  	读取授权
  	插入授权
  	更新授权
  	删除授权
  
  -修改数据库架构的授权形式：
  	索引授权 - 允许创建和删除索引
  	资源授权 - 允许创建新关系
  	更改授权 - 允许在关系中添加或删除属性
  	删除授权 - 允许删除关系
  
  

• SQL 中授予/撤销权限

--授权语句
grant <privilege list> on <relation or view > to <user list>


--撤销语句
revoke <privilege list> on <relation or view> from <user list>

--<user list> is:
--a user-id
--public, which allows all valid users the privilege granted
--A role


--示例：
grant select on department to Amit, Satoshi ;
grant update(budget) on department to Amit, Satoshi ;
revoke select on department from Amit, Satoshi ;
revoke update(budget) on department from Amit, Satoshi ;

• 角色

  --可以向用户以及其他角色授予角色
  create role <name> --创建角色
  grant  <role> to <users> --将“用户”分配给角色
  
  	create role teaching_assistant;
  	grant teaching_assistant to instructor;
  	--教师继承teaching_assistant的所有权限
  
  

• 特权转让

  • 使用授权选项(with grant option)：允许被授予权限的用户将权限传递给其他用户。

    grant select on department to Amit with grant option ;