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子句的查询可通过下述操作序列来定义:

  1. 先根据from子句计算一个关系。
  2. 如果有where子句,where子句的谓词应用到from的结果关系上。
  3. 如果有group by子句,满足where谓词的元组通过group by子句形成分组。如果没有group by,则所有满足where的元祖为同一个分组。
  4. 如果有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

Comments !