SQL常用关键字

creat table

    create table department
    (dept_name  varchar(20),
    building varchar(20),
    budget numeric (12,2),
    primary key (dept_name));

insert into
    insert into instructor 
    values(10211,'smith','Boilogy',100000);

delete from
delete from student; 删除所有元祖，但保留关系

drop table
drop table r;删除关系

alert table
alert table r add A D 向关系r中添加属性A，其域是D。

select dept_name
from instructor;

select distinct dept_name   distinct是去重复。
from instructor;

select name
from instructor
where dept_name='Comp.Sci.' and salary>70000;

多关系查询
select name ,instructor.dept_name,building
from instructor,department
where instrcutor.dept_name=department.dept_name

自然连接
select name ,course_id
from instructor natrual join teaches;

select name ,course_id
from instructor,teaches
where instrcutor.ID=department.ID

as 关键字
select distinct T.name
from instructor as T,instructor as S
where T.salary>S.salary and S.dept_name='Biology';

like关键字
select dept_name
from department
where building like '%Waston'; 

like 'ab\%cd%' escape '\'      escape+like 定义转义字符串模式

order by

select name 
from instructor
where dept_name='Physics'
order by name;

between ,and

select name
from instructor
where salary between 9000 and 10000;

并运算uinion
(select course_id
from section
where semester='Fall' and year=2009)
union
(select course_id
from section
where semester='Spring' and year=2010);

union将自动去重复，如果想保留重复，使用union all

交运算intersect
(select course_id
from section
where semester='Fall' and year=2009)
intersect
(select course_id
from section
where semester='Spring' and year=2010);

差运算except

(select course_id
from section
where semester='Fall' and year=2009)
except
(select course_id
from section
where semester='Spring' and year=2010);

平均值 avg

select avg (salary)
from instructor 
where dept_name='Comp.Sci';

count统计次数
select count (*)
from course;

分组group by
select dept_name,avg (salary) as avg_salary
from instructor
group by dept_name;

having 字句限定分组
where字句限定所有元祖
select dept_name,avg (salary) as avg_salary
from instructor
group by dept_name
having avg(salary)>40000;

包含聚集，group by， having子句的查询可通过下述操作序列来定义：
1. 先根据from子句计算一个关系。
2. 如果有where子句，where子句的谓词应用到from的结果关系上。
3. 如果有group by子句，满足where谓词的元组通过group by子句形成分组。如果没有group by，则所有满足where的元祖为同一个分组。
4. 如果有having子句，它将应用到每个分组上，不满足having子句谓词的分组将被抛弃。
5. 最后是聚集函数，avg，count，max，min等。对剩下的分组产生查询结果的元祖。即在每个分组上应用聚集函数来得到单个结果的元祖。

例子：
select course_id,semester,year,sec_id,avg(total_score)
from takes natrual join student
where year =2009
group by course_id,semester,year,sec_id
having count(ID)>=2;

嵌套查询

select course_id
from section
where semester = 'Fall' and year=2009 and 
course_id in (select course_id
from section
where semester = 'Fall' and year=2010 );

delete元组

delete from instructor
where salary between 10000 and 20000;

insert into course(course_id,title,dept_name,credits)
values('CS-437','DataBase System','Comp.Sci',4);
这种方式适合忘记了表中属性顺序被忘记的情况。

集合的比较

some关键字：至少比某一个要大
select name 
from instructor
where salary>some (select salary
                    from instructor
                    where dept_name = 'Biology');

all关键字：比所有的都大

exists关键字
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);

unique

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

select T.course_id
from course as T
where 1>=(select count(R.course_id)
from section as R
where T.course_id=R.course_id and R.year=2009
);

from 嵌套子查询
select max (total_salary)
from (select dept_name,sum(salary)
        from instructor
        group by dept_name)
)

with子句：定义临时关系，只对包含with子句的查询有效
with dept_total(dept_name,value) as
    (select dept_name,sum(salary)
    from instructor
    group by dept_name),
    dept_total_avg(value) as
    (select avg(value)
    from dept_total)
select dept_name
from dept_total,dept_total_avg
where dept_total.value>=dept_total_avg.value;

insert
在查询结果的基础上插入元组
insert into instructor
    select ID,name,dept_name,18000
    from student
    where dept_name='Music' and tot_cred>144;

update
update instructor
set salary=salary*1.5
where salary<(select avg(salary)
from instructor);

case语法：
update instructor
set salary =case
                when salary<=100000 then salary*1.05
                else salay*1.03
            end