SQL学习笔记

最近因为参加阿里巴巴大叔据竞赛,还算顺利的进入了第二轮，跟第一轮不同，官方给出的数据量大增，需要用到的技术也从单纯的机器学习算法扩展到 hadoop , sql等技术的使用，只好把曾经学过的数据库捡一捡，整理了一下我在竞赛中常用到的一些命令，相信这些命令对于常见的查询任务已经足够了。

首先明确几个概念：

关系模式下表是一个department关系，列表示属性，每一行是一个元组，也叫关系实例。 dept_name | building |budget --------- | ----- |------ Biology | Watson |90000 Comp.Sci | Taylor |100000 History | Painter |800000

department关系模式可以表示为：

department(dept_name,building,budget)

主码一个或多个属性的集合，使得在关系中能唯一的标识一个元组。并且它的任意真子集都不能唯一的标识一个元组。码代表的是整个关系的性质，而不是单个元组的性质。
参照关系一个关系模式r1可能在它的属性中包括另外一个关系r2的主码，这个属性在r1上称为参照r2的外码，关系r1成为外码依赖的参照关系，r2成为外码的被参照关系。例如有instructor关系：

ID	name	dept_name	salary
10100	Wu	History	40000
10002	Taylor	Comp.Sci	100000
12232	Peter	History	800000
instructor的dept_name属性在instructor上是外码，它参照department，因为dept_name是department的主码。参照关系有一些特殊的性质，从instructor关系中任取一个元组，在department关系中肯定存在一个元组使得在dept_name上的取值相同。

笛卡儿乘积从两个输入关系中输出所有元组对，不论他们在共同属性上的取值时候相同。
自然连接从两个输入关系中输出这样的元组对：他们在具有相同名字的所有属性上取值相同。

显然自然连接是笛卡儿连接的子集。

sql的基本数据类型

char（n）：固定长度的字符串，n由用户指定。
varchar（n）：可变长的字符串，最大长度为n。
int
numeric（p，d）：p为位数，d为小数点右边的位数。如numeric（3,2）可以存储4.44
float
double precision

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;删除关系r

向关系中新增属性

alert table

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

查询语句：

select-from-where

查询语句返回关系作为结果。每个子句的作用是：

select 子句用于列出查询结果中所需要的属性。
from 子句是一个查询求值中需要访问的关系列表。
where子句是一个作用在from子句中关系的属性上的谓词。

于是，一个SQL查询的含义可以这么理解：

为from子句中列出的关系产生笛卡儿积
在笛卡儿积的结果上应用where子句中指定的谓词。
在上面2步的结果中的每个元组，输出select子句中指定的属性，或表达式的结果。

单关系查询
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

自然连接

natrual join

select name ,course_id
from instructor natrual join teaches;

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

as 关键字

as关键字可以用来更名，适用于关系名字或属性名字。

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

like+escape关键字

like关键字提供模式匹配的方法，sql是大小写敏感的。sql提供两个特殊字符来描述模式：

%:匹配任意子串，如'intro%'匹配任意以intro开头的字符串，'%intro%'匹配任意包含intro的字符串。
_:匹配任意一个字符，如'___'匹配只含三个字符的子字符串。

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

like 'ab\%cd%' escape '\'      escape定义转义字符串模式,使第一个%变成普通字符

order by关键字

排列元组的显示次序。desc表示降序，asc表示升序。

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

between ,and关键字

select name
from instructor
where salary between 9000 and 10000;

union 并运算

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

null 空值

sql把涉及null的比较运算视为unknown。

select name
from instructor
where salary is null

聚集函数

sum 求和

select sum(salary)
from instructor;

avg 平均值

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

count 统计次数经常用count计算一个关系中元组的个数。也经常与distinct结合使用。

select count (*)
from course;

select count(distinct ID)
from teaches;

group by 用一个或多个属性来构造分组，在group子句中的所有属性上取值相同的元组将被分到同一个组里。

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

having 子句 having子句步针对单个元组，而是针对group by子句构成的分组。having子句中的谓词在形成分组之后才会起作用。 having ,where子句的区别：

having 字句限定分组。 where字句限定所有元祖。

select dept_name,avg (salary) as avg_salary
from instructor
group by dept_name
having avg(salary)>40000;

包含聚集，group by， having子句的查询可通过下述操作序列来定义：

先根据from子句计算一个关系。

如果有where子句，where子句的谓词应用到from的结果关系上。

如果有group by子句，满足where谓词的元组通过group by子句形成分组。如果没有group by，则所有满足where的元祖为同一个分组。

如果有having子句，它将应用到每个分组上，不满足having子句谓词的分组将被抛弃. 5.select子句利用剩下的分组产生出查询结果中的元组,即在每个分组上应用聚集函数，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;
//对于在2009年讲授的每个课程,如果该课程有至少两个学生选课,找出选修该课程的所有学生的总学分的平均值.

嵌套子查询

嵌套查询的依据是:任何select-from-where表达式返回的结果都是关系,因而可以被插入到另一个select-from-where中任何关系可以出现的位置.

where子句嵌套连接词in测试元组是否是集合中的成员,集合是由select子句产生的一组值构成的.对应有not in.

例1 在单属性关系中测试成员资格.
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 );

例2 在其他关系中测试成员资格,只要是属性能对上就行.
select distinct(ID)
from takes
where (course_id,sec_id,semester,year) in(select                                                                          course_id,sec_id,semester,year
                                          from teaches
                                          where teaches.ID=10010);
)

from 嵌套子查询

例1 
select max (total_salary)
from (select dept_name,sum(salary)
        from instructor
        group by dept_name) as dept_total(dept_name,total_salary);

例2
select dept_name avg_salary
from (select dept_name ,avg(salary) as avg_salary
        from instructor
        group by dept_name)
where avg_salary > 40000;

delete 元组

delete from instructor
where salary between 10000 and 20000;

delete from instructor 
where dept_name in (select dept_name
                    from department 
                    where building ='Waston');

insert 元组

这种方式适合忘记了表中属性顺序被忘记的情况。

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关键字：比所有的都大

select dept_name
from instructor
group by dept_name
having avg(salary) >= all (select avg(salary)
                            from instructor
                            group by dept_name);

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

这个例子也说明来自外层查询的一个相关名称可以用在where子句的子查询中.

unique关键字

如果作为参数的子查询结果中没有重复的元组,unique将返回true.

例1 找出所有在2009年最多开设一次的课程.如果某门课程不在2009年开设,那么子查询会返回一个空的结果,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);

例2 例1的等价解法.            
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);

with子句

定义临时关系，只对包含with子句的查询有效.

with dept_total(dept_name,value) as //定义两个关系dept_total,dept_total_avg
    (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

在查询结果的基础上插入元组,即用select选出一个元组集合.insert和select执行的顺序很重要.先select出元组,再insert.

insert into instructor
    select ID,name,dept_name,18000
    from student
    where dept_name='Music' and tot_cred>144;

update 更新

如果我们希望在不改变整个元组的情况下改变部分属性的值,可以使用update+set.

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

case 关键字

语法:

case 
    when pred1 then result1
    when pred2 then result2
    ...
    else result0
end

例1
update instructor
set salary = case
                when salary<=100000 then salary*1.05
                else salay*1.03
            end