SQL 语法笔记

➪SQL

    ➪基本类型 char / varchar / int / smallint / numeric / real, double precision / float

    ➪数据定义

create table department//定义SQL关系 

(dept_name varchar(20),//属性名 域

building varchar(15),

budget numeric(12,2),

primary key(dept_name));//完整性约束 primary key, foreign key, not null

insert into insturctor//加载数据到关系中

 values(10211, ‘smith’,’Biology’,66000);

delete from student;//从关系中删除元组

drop table r;//去掉关系

alter table r add A D;//为已有关系增加属性

alter table drop r;//从已有关系去掉属性

    ➪关系查询

select (all) name from instructor;//从关系找出某属性

select distinct dept_name from instructor;//强行删除重复

select salary*1.1 from instructor;//select子句可带有+,-,*,/运算符的算术表达式

select name from instructor where dept_name = ‘Comp.Sci.’ and salary>7000 //where子句允许从from子句再筛选，逻辑连词and, or, not，比较运算符<, <=, >, >=, =, < >

select name, instructor.dept_name,building from instructor,department

where instructor.dept_name = department.dept_name//从多关系中找出多个属性

select name,course_id from instructor natural join teaches//关系的自然连接，from子句可为from E1,E2…En，Ei可以是单个关系或一个包含自然链接的表达式，where子句可以缩减属性的个数

// = select name,course_id from instructor, teaches where instructor.ID=teaches.ID

select name,title from (instructor natural join teaches) join course using (course_id);//join…using运算中需要给定一个属性名列表，其两个输入中都必须有指定名称的属性

    ➪附加的基本运算

select T.name, S.course_id from instructor as T,teaches as S where T.ID=S.ID;//as子句用于重命名结果关系中的属性，可出现在select和from子句中

select dept_name from department where building like ‘%Waston%’;//like操作符实现模式匹配，用%, _描述模式，not like搜索不匹配项

like ‘ab\%cd%’ escape ‘’//匹配所有以“ab%cd”开头的字符串

like ‘ab\cd%’ escape ‘’//匹配所有以“abcd”开头的字符串

select instructor.*//*可以用在select子句中表示所有属性

select name from instructor where dept_name = ‘Physics’ order by name  (desc/asc);//order by让查询结果中元组按排列顺序显示，desc表示降序，asc表示升序，默认升序

where salary between 90000 and 100000;//between, not between可简化where子句

where (instructor.ID,dept_name) = (teaches.ID, ‘biology’);//(a1,a2..an)+比较运算符+(b1,b2...bn)可简化where子句

(select course_id from section

where semester = ‘Fall’ and year = 2009)

union (all) / intersect (all) / except (all)

(select course_id from section

where semester = ‘Spring’ and year = 2010); //集合的交、并、差运算，all用来保留重复

    ➪空值 is null / is not null / unknown

    ➪聚集函数 avg / min / max / sum / count

select avg(salary) as avg_salary from instructor where dept_name = ‘Comp.Sci’;

select count(distinct ID) from teaches where semester = ‘Spring’ and year = 2010;

select count(*) from course;//count(*)计算一个关系中元组的个数

select dept_name, avg(salary) as avg_salary from instructor group by dept_name;//group by将聚集函数作用到一组元组集上

select dept_name ,count (distinct ID) as instr_count from instructor natural join teaches

where semester = ‘Spring’ and year =2010 group by dept_name;//p78待理解

select dept_name,avg(salary) as arg_salary from instructor group by dept_name having avg(salary) > 42000;//having子句对针对group by子句构成的分组限定条件

    ➪嵌套子查询

select distinct course_id from section where semester = ‘Fall’ and year = 2009

and course_id in (select course_id from section where semester = ‘Spring and year = 2010);//连接词in，not in测试元组是否集合中的成员

select distinct name from instructor where name not in (‘Mozart’,’Einstein’);//in,not in可用于枚举集合

select name from instructor where salary > some (select salary from instructor where dept_name = ‘Biology’);// > some表示至少比某一个要大，类似的有< some, <=some, >=some, < >some, =some 等价于in，< >some不等价于not in

select name from instructor group by dept_name having avg(salary) 

>=all (select avg(salary) from instructor group by dept_name);// > all表示比所有的都大，类似的有< all, <=all, >=all, < >all, < >all 等价于not in，= all不等价于in

select course_id from section as S where semester = ‘Fall’ and year = 2009 and

exists(select * from section as T where semester = ‘Spring’ and year = 2010 and S.course_id = T.course_id);//使用了来自外层查询相关名称的子查询被称作相关子查询，exists结构在作为参数的子查询非空时返回true，not exist测试子查询结构集中是否存在元组，not exists(B except A)表示关系A包含关系B

select T.course_id from course as T where unique (select R.course_id from section as R where T.course_id = R.course_id and R.year = 2009);unique, not unique测试子查询结果中是否存在重复元素，where unique等价于where 1>=

select dept_name,avg_salary from(select dept_name, avg(salary) from instructor group by dept_name) as dept_avg (dept_name,avg_salary)) where avg_salary > 4000;//from子句中的子查询，＊ lateral的使用

with max_budget(value) as (select max(budget) from department) select budget from department, max_budget where department.budget = max_budget.value;//with语句提供定义临时关系的方法

select dept_name(select count(*) from instructor where department.dept_name = instructor.dept_name) as num_instructors from department;//标量子查询

    ➪数据库的修改

delete from instructor where salary < (select avg (salary) from instructor);//delete语句从关系中找出使where语句后条件为真的元组并将其删除

insert into course(course_id,title,dept_name,credits) values(‘CS-437’,’Database Systems’,’Comp.Sci.’,4);//允许在单个元组的插入请求insert中指定属性

insert into instructor select ID,name,dept_name,18000 from student where dept_name = ‘Music’ and tot_cred >144;//select选出一个元组集合用于插入

update instructor set salary = salary*1.05 where salary<(select avg(salary) from instructor);//update语句改变元组的部分属性的值

update instructor set salary = case when salary <=100000 then salary * 1.05

else salary * 1.03 end//case结构在update语句中执行若干种更新避免更新次序引发问题

update student S set tot_cred = (select sum(credits) from takes natural join course where S.ID = takes.ID and takes.grade < > ‘F’ and takes.grade is not null);//标量子查询在sql更新语句中的使用用在set子句中

➪中级SQL

    ➪连接表达式

select student.ID as ID, name, dept_name, tot_cred, course_id, sec_id, semester, year, grade from student join takes on student.ID = takes.ID;//on条件允许在参与连接的关系上设置通用的谓词，与using条件一样，与where子句类似

select * from student natural left outer join takes;//左外连接只保留出现在左外连接运算之前（左边）的关系中的元组

select * from takes natural right outer join student;//右外连接使得右侧关系中的不匹配左侧关系任何元组的元组被补上空值

select * from(select * from student where dept_name = ‘Comp.Sci.’) natural full outer join (select * from takes where semester = ‘Spring’ and year = 2009);//全外连接结合了左外连接与右外连接

select * from student left outer join takes on student.ID = takes.ID;//on子句可以和外连接共用

select * from student left outer join takes on student.ID = takes.ID’//on子句和where子句在外连接中的使用不相同

    ➪视图

create view faculty as 

select ID, name, dept_name from instructor;//定义视图

create view physics_fall_2009_watson as 

select course_id, room_number from physics_fall_2009 where building = ‘Waston’;//一个视图可能被用到定义另一个视图的表达式中

    ➪事务

Commit work//提交当前事务

Rollback work//回滚当前事务

    ➪完整性约束

name varchar(20) not null//not null限定属性的域来排除空值

unique(Aj1,Aj2…Ajm)//unique声明指出属性Aj1,Aj2…Ajm形成了一个候选码，即在关系中没有两个元组能在所有列出的属性上取值相同

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’)));//check子句保证属性值满足指定的条件

foreign key(dept_name) references department//外码声明表示在每个元组中指定的属性必须在references后的关系中存在

dept_name varchar(20) references department//使用简写形式作为属性定义的一部分，并声明该属性为外码

create table course

(…

  foreign key(dept_name) references department

     on delete cascade,

     on update cascade,

…);//允许foreign key子句指明动作

create assertion credits_earned_constraint check 

    (note exists (select ID

        from student 

           where tot_cred <  > (select sum (credit) ) 

                                           from takes natural join course

                                          where student.ID=takes.ID

                                          and grade is not null and grade <  > ‘F’);//断言assertion表达希望数据库总能满足的一个条件，域约束和参照完整性约束是断言的特殊形式 

    ➪SQL的数据类型与模式

date / time / timestamp   current_date / current_time / current_timestamp / localtime / current_timestamp / localtimestamp

tot_cred numeric(3,0) default 0 //default为属性指定默认值

create index studentID_index on student(ID);// 创建索引

book_review clob(10KB)// 字符数据的大对象数据类型

image blob(10MB) //二进制数据的大对象数据类型

create type Dollars as numeric(12,2) final; //独特类型为了检测赋值或比较错误

cast(department.budget to numeric (12,2)) //一种类型的数值转化到另一个域

create domain DDollars as numeric(12,2) not null; //域可以在基本类型上施加完整性约束

create domain YearlySalary numeric(8,2)

constraint salary_value_test check( value >=29000.00); //当把check子句用到域上时，允许模式设计者指定一个域词

creat domain degree_level varchar(10)

constraint degree_level_test check(value in (‘Bachelors’, ‘Masters’, or ‘Doctorate’)); //使用in子句可以限定一个域只包含指定的一组值

create table temp_instructor like instructor; //创建与现有表模式相同的表

create table t1 as (select * from instructor where dept_name = ‘Music’) with data;

把查询结果存储成一个新表

    ➪授权

grant <权限列表> on <关系名或视图名> to <用户 / 角色列表>; //grant语句用来授予权限，权限包括select, update, insert, delete

grant select on department to Amit, Satoshi; // 关系上的select权限用于读取关系中的元组

grant update (budget) on department to Amit, Satoshi; //update权限允许用户修改关系中的任意元组

revoke <权限列表> on <关系名或视图名> from <用户 / 角色列表>; //revoke语句收回权限

create role instructor; //创建角色

grant dean to Amit; create role dean; grant instructor to dean; grant dean to Satoshi; //角色可像用户一样被授予权限，角色可以授予给用户或者其他角色

create view geo_instructor as (select * from instructor where dept_name = ‘Geology’); //创建视图以授权

grant references (dept_name) on department to Mariano; //references权限允许用户在创建关系时声明外码

grant select on department to Amit with grant option; //在grant命令后附加with grant option允许接收者把所得到的权限传递给其他用户

revoke select on department from Amit, Satoshi restrict; //revoke语句可以申明restrict来防止级联收回（cascade替换restrict表示需要级联收回，可缺省）

在授权语句后加granted by current_role子句以在授权时将授权人设置为一个会话所关联的当前角色

➪高级SQL
---------------------
参考：https://wenku.baidu.com/view/128225b91711cc7930b71651.html

https://www.cnblogs.com/taidou/p/sqlbasis.html

https://wenku.baidu.com/view/6ff6fcf3f61fb7360b4c653d.html?rec_flag=default&sxts=1550934761711

4.1 连接表达式

    4.1.1 连接条件

        on是另一种形式的连接，其中可以指定任意的连接条件。on条件允许在参与连接的关系上设置通用的谓词，该谓词的写法与where子句谓词类似。

1 select*
2 from student join takes on student.ID = takes.ID;

        on条件有两个优点，首先，被称为外连接的这类连接来说，on条件的表现与where条件是不同的。其次，如果在on子句中指定连接条件，并在where子句中出现其余的条件，这样的SQL查询通常更容易懂。

    4.1.2 外连接

        实际上有三种形式的外连接：

• 左外连接（left outer join）只保留出现在左外连接运算之前（左边）的关系中的元组。
• 有外连接（right outer join）只保留出现在右外连接运算之前（右边）的关系中的元组。
• 全外连接（full outer join）保留出现在两个关系中的元组。

        不保留未匹配元组的连接运算被称为内连接运算。

        右外连接和左外连接是对称的。如来自右侧关系中的不匹配左侧关系任何元组的元组被补上空值，并加入到有外连接的结果中。

1 select *
2 from takes natural right outer join student;
3 
4 select *
5 from takes natural left outer join student;

        得到的结果是一样的，只不过结果中属性出现的顺序不同。

        全外连接是左外连接和右外连接类型的的组合。

        on和where在外连接中的表现是不同的，其原因是外连接只为那些对那些对相应内连接结果没有贡献的元组补上空值并加入结果。

        on条件是外连接声明的一部分，但where子句却不是。

    4.1.3连接类型和条件

        为了区分常规连接和外连接，SQL中把常规连接称作内连接。用inner join来代替outer join，说明使用的是常规连接，然而关键字inner是可选的。

        类似地，natural join等价于natural inner join。

4.2 视图

    SQL允许通过查询来定义“虚关系”，它在概念上包含查询的结果。虚关系并不是预先计算并存储，而是在使用虚关系的时候才通过执行查询被计算出来。

    任何像这种不是逻辑模型的一部分，但作为虚关系对用户可见的关系称为视图。在任何给定的实际关系集合上能够支持大量视图。

    4.2.1 试图定义

         create view命令定义视图。

create view v as <query expression>

    4.2.2 SQL查询中使用视图

        一旦定义了一个视图，我们就可以用视图名指代该视图生成的虚关系。一个视图可能被用到定义另一个视图的表达式中。

1 create view departments_total_salary(dept_name, total_salary) as
2     select dept_name, sum(salary)
3     from instructor
4     group by dept_name;

    4.2.3 物化视图

       特定的数据库系统允许存储视图关系，但是如果用于定义视图的实际视图的实际关系改变，视图也跟着修改，这样的视图被称为物化视图。

       保持物化视图一直在最新状态的过程称为物化视图维护，或者通常简称视图维护。

    4.2.4 视图更新

        尽管对查询而言，视图是一个有用的工具，但如果我们用它们来表达更新，插入或删除，它们可能带来严重的问题。困难在于，用视图表达的数据库修改必须被翻译为对数据库逻辑模型中实际关系的修改。　　

        除了一些有限的情况之外，一般不允许对视图关系进行修改。

        一般来说，如果定义视图的查询对下列条件都能满足，我们称SQL视图是可更新的：

• from子句中只有一个数据库关系。
• select子句中只包含关系的属性名，不包含任何表达式、聚集或distinct声明。
• 任何没有出现在select子句中的属性可以取空值；即这些属性上没有not null约束，也不构成主码的一部分。
• 查询中不包含group by或having子句。

        在默认情况下，SQL允许执行上述更新，但是可以通过在视图定义的末尾包含with check option子句的方式来定义视图。这样，如果向视图中插入一条不满足视图的where子句条件的元组，数据库系统将拒绝该插入操作。

4.3 事务

    事务由查询和更新语句的序列组成。SQL标准规定当一条SQL语句被执行，就隐式地开始了一个事务。下列SQL语句之一会结束一个事务：

• Commit work：提交当前事务，也就是将事务所做的更新在数据库中持久保存。在事务被提交后，一个新的事务自动开始。
• Rollback work：回滚当前事务，即撤销该事务中所有SQL语句对数据库的更新。

    关键词work在两条语句中都是可选的。

    一旦某事务执行了commit work，它的影响就不能用rollback work来撤销了。

    在很多SQL实现中，默认方式下每个SQL语句自成一个事务，且一执行完就提交。如果一个事务要执行多条SQL语句，就必须关闭单独SQL语句的自定提交。如何关闭自动提价也依赖于特定的SQL实现。

    SQL1999标准允许多条SQL语句包含在关键字begin atomic…end之间。所有在关键字之间的语句构成了一个单一事务。

4.4 完整性约束

    完整性约束通常被看成是数据库模式设计过程的一部分，它作为用于创建关系的create table命令的一部分被声明。然而，完整性约束也可以通过使用alter table table-name add constraint命令施加到已有关系上，其中constraint可以是关系上的任意约束。当执行上述命令时，系统首先保证关系满足指令约束。如果满足，那么约束被施加到关系上；如果不满足，则拒绝执行上述命令。

    4.4.1 单个关系上的约束

        允许的完整性约束包括：

• not null
• unique
• check(<谓词>)

    4.4.2 not null约束

　　　not null声明禁止在该属性上插入空值。

    4.4.3 unqiue约束

        unqiue声明指出属性A_j1, A_j2, ... A_jm形成了一个候选码；即在关系中没有两个元组能在所有列出的属性上取值相同。

    4.4.4 check子句

        当应用于关系声明时，check(P)子句指定一个谓词P，关系中的每个元组都必须满足谓词P。根据SQL标准，check子句中的谓词可以包括子查询在内的任意谓词。

    4.4.5 参照完整性

        外码声明：foreign key(dept_name) references department。

        当参照完整性约束时，通常的处理是拒绝执行导致完整性破坏的操作。但是，在foreign key子句中可以指明：如果被参照关系上的删除或更新动作违反了约束，那么系统必须采取一步步骤通过修改参照关系中的元组来恢复完整性约束，而不是拒绝这样的动作。

1 create table course
2     {...
3     forgein key (dept_name) references department
4         on delete cascade
5         on update cascade,
6     ...};

        由于有了与外码声明相关联的on delete cascade子句，如果删除department中的元组导致此参照完整性的约束被违反，则删除并不被系统拒绝，而是对course关系做“级联”删除，即删除参照了被删除系的元组。类似地，如果更新被参照字段时时违反了约束，，则更新操作并不被系统拒绝，而是将course中的参照元组的dept_name字段改为新值。SQL还允许forgein key子句指明除cascade以外的其他动作，如果约束被违反：可将参照域(这里是dept_name)置为null（用set null代替cascade），或者置为域的默认值（用set default）。

        如果存在涉及多个关系的外码依赖链，则在链一端所做的删除或更新可能传至整个链。如果一级联更新或删除导致的对约束的违反不能通过进一步的级联操作解决，则系统终止该事务。于是，该事务所做的所有改变及级联动作将被撤销。

    4.4.6 事务中对完整性约束的违反

        事务可能包含几个步骤，在某一步之后完整性约束也许会暂时被违反，但是后面的某一步也许就会消除这个违反。

        为了处理这样的情况，SQL标准允许将initially deferred子句加入到约束声明中；这样完整性约束不是在事务的中间步骤上检查，而是在事务结束时检查。一个约束可以被指定为可延迟的。对于声明为可延迟的约束，执行set constraints constraint_list deferred语句作为事物的一部分，会导致对指定约束的检查被延迟到该事务结束时执行。

    4.4.7 复杂check条件与断言

        SQL标准所定义，check子句中的谓词可以是包含子查询的任意谓词。如果一个数据库实现支持在check子句中出现子查询，我们就可以在关系section上声明如下参照完整性约束： check (time_slot_id in (select time_slot_id from time_slot))

        一个断言就是一个谓词，它表达了我们希望数据库总能满足的一个条件。域约束和参照完整性约束都是断言的特殊形式。断言形式如下：

        create assertion <assertion-name> check <predicate>;

1 create assertion credits_earned_constraint check
2     (not exists (select ID
3         from student
4         where tot_cred < > (select sum (credits)
5                                     from takes natural join course
6                                     where student.ID = takes.ID
7                                     and grade is not null and grade < > 'F')))

4.5 SQL的数据类型与模式

    4.5.1 SQL中的日期和时间类型

• date：日历日期，包括年（四位）、月和日。
• time：包括小时、分和秒。可以用time(p)表示秒的小数点后的数字位数。通常指定time with timezone，还可以把时区信息连同时间一起存储。
• timestamp：date和time的组合。可以用变量timestamp(p)来表示秒的小数点后的数字位数(默认6位)。如果指定with timezone，则时区信息也会被存储。

1 date '2001 - 04 - 25'
2 time '09:30:00'
3 timestamp '2001 - 04 - 25 10:29:01.45'

    4.5.2 默认值

        SQL允许为属性指定默认值。(tot_cred)

1 create table student
2     (ID varchar(5),
3     name varchar(20) not null,
4     dept_name varchar(20),
5     tot_cred numeric(3,0) default 0,
6     primary key(ID));

    4.5.3 创建索引

        create index studentID_index on student(ID);

    4.5.4 大对象类型

        SQL提供字符数据的大对象类型（clob）和二进制数据类型的大对象数据类型（blob）。

1 book_review clob (10KB)
2 image blob (10MB)
3 movie blob (2GB)

    4.5.5 用户定义的类型

        SQL支持两种形式的用户定义数据类型。第一种称为独特类型，另一种称为结构化数据类型。

        一个好的类型系统应该能够检测出这类赋值或比较，为了支持这种检测，SQL提供了独特类型的概念。可以用create type子句来定义新类型。如：create type Dollars asnumeric(12, 2) final;

        一种类型的数值可以被转换（也即cast）到另一个域，cast(department.budget to numeric(12, 2))

        SQL提供drop type和alter type子句来删除或修改以前创建过的类型。

    4.5.6 create table的扩展

        应用常常要求创建于现有的某个表的模式相同的表。SQL提供一个create table like的扩展来支持： create table tempp_instructor like instructor;

        当书写一个复杂查询时，把查询的结果存储成一个新表通常是很有用的；这个表通常是临时的。SQL2003提供了一种更简单的技术来创建包含查询结果的表。

1 create table tl as
2     (select*
3      from instructor
4      where dept_name = 'Music')
5 with data;

        SQL2003标准定义，如果忽略with data子句，表会被创建，但不会载入数据。

    4.5.7 模式、目录与环境

        默认目录和模式是为每个连接建立的SQL环境的一部分。环境还包括用户标识。所有通常的SQL语句，包括DDL和DML语句，都在一个模式的环境中运行。

        可以用create schema和drop schema语句来创建和删除模式。

4.6 授权

    4.6.1 权限的授予与收回

        SQL标准包括select、insert、update和delete权限。grand语句用来授权，基本形式：

1 grant<权限列表>
2 on <关系名或视图名>
3 to <用户/角色列表>
4 
5 grant select on department to Amit， Satoshi；

         revoke语句来收回权限。

1 revoke <权限列表>
2 on <关系名或视图名>
3 to <用户/角色列表>
4 
5 revoke seleect on department from Amit， Satoshi；

    4.6.2 角色

        在数据库中建立一个角色集，可以给角色授予权限，就和给每个用户授权的方式完全一样。每个数据库用户被授予一组他有权扮演的角色（也可能是空的）。

1 create role instructor；
2 
3 grant select on takes
4 to instructor；
5 
6 grant dean to Amit；
7 create role dean；
8 grant instructor to dean；
9 grant dean to Satoshi；

    4.6.3 视图的授权

        在函数或过程上可以授权execute权限，以允许用户执行该函数或过程。在默认情况下，和视图类似，函数和过程具有其创建者拥有的所有权限。在效果上，该函数或过程的运行就像其被创建者调用那样。

        尽管此行为在很多情况下是恰当的，但是它并不总是恰当。从SQL2003开始，如果函数定义有一个额外的sql security invoker子句，那么它就在调用该函数的用户的权限下执行，而不是在函数定义者的权限下执行。这就允许创建的函数库能够与调用者相同的权限下运行。

    4.6.4 模式的授权

        SQL提供了一种references权限，允许用户在创建关系时声明外码。SQL的reference权限可以与update权限类似的方式手遇到特定属性上。

            grant references(dept_name) on department to Mariano;

    4.6.5 权限的转移

        如果我们在授权时允许接受者把得到的权限在传递给其他用户，我们可以在相应的grant命令后面附加with grant option子句。

        grant select on department to Amit with grant option；

    4.6.6 权限的收回

        从一个用户/角色那里收回权限可能导致其他用户/角色也失去该权限，这一行为称作级联收回。在大多数的数据库系统中，级联时默认行为。然而，revoke语句可以申请restrict来防止级联收回： revoke select on department from Amit， Satoshi restrict；

        可以用关键字cascade来替换restrict，以表示需要级联收回；然而cascade可以省略，是默认的。下面的revoke语句仅仅收回grant option，而不是真正收回select权限：revoke grant option for select on department from Amit；

        如果要在授权时将授权人设置为一个会话所关联的当前角色，并且当前角色不为空的话，可以在授权语句后面加上：grant by current_role

        假设将角色instructor授予Amit是用grant by current_role子句实现的，当前角色被设置为dean而不是授权人（用户Satoshi），那么，从Satoshi处收回角色/权限（包括角色dean）就不会导致收回角色dean作为授予的权限，即使Satoshi是执行该授权的用户；这样，即使Satoshi额权限被收回，Amit仍然能够保持instructor角色。