中级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 ‘\’//匹配所有以“ab\cd”开头的字符串

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