/*中级操作 中级操作主要讲一些数据库管理，如何更加方便建立一个数据库*/

/*1. join on, nature join, left outer join, full outer join, right outer join, inner join*/
/*一般 nature left outter join 指的是左边的保留，与右边的相同的求笛卡尔积 */
/*join 这部分就是看两个表怎么交在一起的*/

/*2. is   仅用在以下两种情况*/
is not NULL
is NULL

/*3. join USING*/
/*在 关系的连接中 可以使用 join ... using 来指定一个连接属性的列表*/
select name, title
from (instructor natural join teaches) join course using (course_id);   
/*用了course_id 来 使course这个关系连接到前边的关系*/

/*4. view 视图*/
/*View 作为视图，可以在不修改源数据库的情况下，进行操作，view能进行什么操作，。完全是看root给了什么权限*/
/*物化视图与视图，mysql的视图并不是实际储存起来，而是通过查询，并不保存数据，是一个虚拟表格，这样方便看，但是性能不优*/
/*物化视图是Certain database systems allow view relations to be physically stored.。但需要更新视图，毕竟当关系改变的时候，查询结果就不对了*/
create view K as <query expression>;/*一般create view 的表达式*/
/*如*/
create view physics_fall_2009 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 ='2009';
/*如*/
create view departments_total_salary(dept_name , total_salary ) as
select dept_name , sum(salary)
from instructor
group by dept_name;

/*5. with check option*/
CREATE VIEW table_name（row_name1, row_name2） AS SELECT (query expression) [WITH CHECK OPTION];
/*WITH CHECK OPTION 表示对UPDATE、INSERT和DELETE操作时保持更新、插入或删除的行满足视图定义的谓词条件（即子查询中的条件表达式*/
/*也就是一个在视图操作中，检查查询是否满足条件*/

/*6. 事务 rollback work, commit work*//*
若把 AUTOCOMMIT 设置为 ON ，则在插入、修改、删除语句执行后，
系统将自动进行提交，这就是自动提交。其格式为： SQL>SET AUTOCOMMIT=ON；
*//*
DCL（Data Control Language） 用来授予或回收访问数据库的某种特权，并控制 数据库操纵事务发生的时间及效果，对数据库实行监视等；
GRANT--- 授权。
ROLLBACK---回滚。
COMMIT--- 提交。
*/           /*
所有的 DML 语句都是要显式提交的，也就是说要在执行完DML语句之后，执行 COMMIT 。
而其他的诸如 DDL语句的，都是隐式提交的。也就是说，在运行那些非 DML 语句后，数据库已经进行了隐式提交，
例如 CREATETABLE，在运行脚本后，表已经建好了，并不在需要你再进行显式提交
在提交事务（commit）之前可以用rollbacl回滚事务
*/

/*主键约束primary key 定义 ，候选键约束 unique 定义，完整性约束， 用户定义完整性，更新完整性约束*/
/*ALTER TABLE <表名> ADD [CONSTRAINT<约束名>] UNIQUE KEY(字段名); */
/* 完整性约束 ， 可通过alter table table_name add constraint 添加   not null, unique, check*/
CONSTRAINT<symbol>
{PRIMARY KEY (主键字段列表)
|UNIQUE (候选键字段列表)
|FOREIGN KEY (外键字段列表) REFERENCES tb_被参照关系(主键字段列表)
|CHECK(约束条件表达式)}

/*check在创建表的时候进行检查*/
create table section （
course_id varchar (8),
sec_id varchar (8),
semester varchar (6),
year numeric (5,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'))
);

/* 参照完整性 在外键中使用， on delete cascade, on update cascade, not action等等*/
/*
no action 表示不做任何操作；
set null 表示在外键表中将相应字段设置为null；置空，当父表更新、删除的时候，字表会把外键字段变为null，所以这个时候设计表的时候该字段要允许为null，否则会出错
set default 表示设置为默认值；
Restrict 同no action, 都是立即检查外键约束 ,父表在删除和更新记录的时候，要在子表中检查是否有有关该父表要更新和删除的记录，如果有，则不允许删除个更改
cascade 表示级联操作，就是说，如果为on update cascade，主键表中被参考字段更新，外键表中对应行相应更新；
                           如果为on delete cascade，主键表中的记录被删除，外键表中对应行相应删除。当父表更新、删除，子表会同步更新和删除
*/
create table course(
. . .
dept_name varchar (20)
foreign key (dept_name) references department
   on delete cascade
   on update cascade,
. . .
);

/*复杂的check p135*/
check (time_slot_id in select (time_slot_id from time_slot))/*这个在上边的用户定于约束有展示*/

/*在断言中的应用*/
Create assertion credits_earned_constraint check
(not exists(select ID
from student
where tot_cred <>(select sum(credits))
from takes natural join course
where student.ID = takes.ID and grade is not null and grade <> ‘F’)))
/* assertion, 断言 create assertion <assertion-name> check <predicate>*/
/*断言，就是可以指定一般性的约束，断言创建以后，任何对断言中所涉及关系的操作都会触发关系数据库管理系统对断言的检查，任何是断言不为真的操作都会被拒绝*/


/*7. date, time, timestamp, current_date, current_time, localtime等*/ 

date '2005 7 27' /*Dates, containing a (4 digit) year, month and date*/

time '09:00:30' /*Time of day, in hours, minutes and seconds.*/

timestamp '2005 7 27 09:00:30.75' /*: date plus time of day*/

interval '1' day  /*period of time*/

/*8. extract from EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。*/
/* 语法： EXTRACT(unit FROM date)*/
SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
EXTRACT(DAY FROM OrderDate) AS OrderDay 
FROM Orders
WHERE OrderId=1 

/* 缺省值 default*/

/* 比较大的数据类型 blob（2GB）二进制大对象类型, clob（10MB）字符大对象类型
blob : binary large object object is a large collection of uninterpreted binary data 
       (whose interpretation is left to an application outside of the database system)
clob : character large object object is a large collection of character data
*/

/*9. cast*/
/*CAST()函数，把一个字段转成另一个字段，比起orcale，MySQL相比之下就简单得多了，只需要一个Cast()函数就能搞定。
其语法为：Cast(字段名 as 转换的类型 )，其中类型可以为：
CHAR[(N)] 字符型 
DATE  日期型
DATETIME  日期和时间型
DECIMAL  float型
SIGNED  int
TIME  时间型
例如表table1
date
2015-11-03 15:31:26*/
select cast(date as signed) as date from  table1;

/*10. domain and type   create domain construct in SQL 92 creates user defined domain types        */
create domain person_name char (20) not null

create type Dollars as numeric (12,2) final;/*用户定义的数据类型*/
/*Types and domains are similar. Domains can have constraints, such as not null , specified on them.*/
create domain degree_level varchar (10)
constraint degree_level_test
check (value in ('Bachelors', 'Masters', 'Doctorate'));

/*11. index 索引是一种数据结构，用于加快对具有指定索引属性值的记录的访问*/
create index <name> on <relation name> (attribute);
/* 如create index studentID_index on student(ID)*/

/*12. GRANT*/
grant <privilege> on <relation_name or view_name> to <user_list>;
/*
特权包括：
Read   allows reading, but not modification of data.
Insert allows insertion of new data, but not modification of existing data.
Update allows modification, but not deletion of data.
Delete allows deletion of data.
Index  allows creation and deletion of indices.
Resources allows creation of new relations.
Alteration allows addition or deletion of attributes in a relation.
Drop allows deletion of relations.

select allows read access to relation,or the ability to query using the view
Example: grant users U1 , U2 , and U3 select authorization on the instructor relation:
grant select on
instructor to U1 , U2 , U3
insert : the ability to insert tuples
update : the ability to update using the SQL update statement
delete : the ability to delete
all privileges : used as a short form for all the allowable privileges
*/
/* user_list 包括
a user id   
public , which allows all valid users the privilege granted
A role (more on this later)*/

/*13. REVOKE  */
/*在 MySQL 中，可以使用 REVOKE 语句删除某个用户的某些权限（此用户不会被删除），在一定程度上可以保证系统的安全性。*/
/*要使用 REVOKE 语句，必须拥有 MySQL 数据库的全局 CREATE USER 权限或 UPDATE 权限*/
REVOKE priv_type [(column_list)]...
ON database.table
FROM user [, user]...
/*或者*/
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

/*14. role*/
/*所谓Role，可以认为是一个权限的集合，这个集合有一个统一的名字，就是Role名，
你可以为多个账户赋予统一的某个Role的权限，而权限的修改可以直接通过修改Role来实现，
而无需每个账户逐一GRANT权限，大大方便了运维和管理。*/
create role priv_t1;
grant select on testdb.t1 to 'priv_t1';
grant 'priv_t1' to 'rw_user1'@'%'；/*将priv_t1的权限给user1*/

/* grant select on deptment to Amit with grant option 
   grant references (dept_name) on department to Mariano
*/
/* 级联撤销特权 p150*/

/*16. flush 的用法 , 清除或者重载内部缓存  flush flush_option;
flush option 最常用的有
1. HOSTs 清空主机缓存
2. TABLEs 关闭所有打开的表 flush tables with read lock 关闭所有打开的表，同时对于所有数据库的表都加一个读锁，直到显示地执行unlock tables.
该操作常用于数据备份的时候。
3. privileges 每当重新赋权后，为了以防万一，让新权限立即生效
*/

/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/