[常用SQL命令]SQL学习笔记（一）

索引

数据基本类型 Basic Types

基本模式定义 Basic Schema Definition

SQL基本查询Basic Structure of SQL Queries

字符串运算 String Operations

排列元组的显示次序 Ordering the Display of Tuples

集合运算 Set Operations

聚集函数 Aggregate Functions

{

    基本聚集

    分组聚集 Aggregation with Grouping

}

嵌套子查询 Nested Subqueries

{

    集合成员资格 Set Membership

    集合的比较 Set Comparison

    空关系测试 Test for Empty Relations

    from子句中的子查询 Subqueries in the From Clause

    with子句 The with Clause

    标量子查询 Scalar Subqueries

}

数据库的修改 Modification of the Database

{

    删除 Deletion

    插入 Insertion

    更新 Updates

}

数据基本类型 Basic Types

char(n) --固定长度字符串

varchar(n) --可变长度字符串,最大长度n

int --整数类型,等价于integer

smallint --小整数类型

numeric(p, d) --定点数,共p为数字(含符号位),其中d为在小数点右边

real --浮点数

double --双精度浮点

float(n) --精度至少为n位的浮点数

---

基本模式定义 Basic Schema Definition

create table r

(A1 D1,

 A2 D2,

...

 An Dn,

<完整性约束1>，

<完整性约束k>);--integrity-constraint

---

create table users

(id int,

 email varchar(32),

 name varchar(8),

 password varchar(16)

 primary key(id));

---

SQL基本查询Basic Structure of SQL Queries

--Queries on a Single Relation

select *

from users;

--去除重复查询

select distinct name

from users;

--显示不去重查询

select all name

from users;

--单关系查询

select A1, A2..., An

from r

where P;

--Queries on Multiple Relations

自然连接natural join

--手动筛选笛卡儿积结果

select name, course_id

from instructor, teachers

where instructor.id = teachers.id;

--与下方自然连接等效

--自然连接

select name, course_id

from instructor natural join teachers;

--join...using运算需要给定一个属性名列表

select *

from r1 join r2 using(A1, A2);

--当r1.A1=r2.A1且r1.A2=r2.A2成立的前提下,来自r1与r2的元组就能匹配,即使有r1.A3<>r2.A3也可选出

select name, title

from (instructor natural join teaches) join course using (course_id);

---

字符串运算 String Operations

--模糊匹配

% --匹配任意子串

—— --匹配任意一个字符

select dept_name

from department

where building like '%Watson%';

like 'ab\%cd%' escape '\' --escape声明转义字符,匹配所有以ab%cd开头的字符串

---

排列元组的显示次序 Ordering the Display of Tuples

select *

from instructor

order by salary desc, name asc; --desc表示降序,asc表示升序,默认升序

--Where Clause Predicates

select name

from instructor

where salary between 90000 and 100000;--where salary <= 100000 and salary >= 90000;

---

集合运算 Set Operations

--并运算The Union Operation

select course_id

from section

where semester = 'Fall' and year = 2009

union --union all保留重复

select course_id

from section

where semester = 'Spring' and year = 2010;

--交运算The Intersect Operation

select course_id

from section

where semester = 'Fall' and year = 2009

intersect --intersect all保留重复

select course_id

from section

where semester = 'Spring' and year = 2010;

--差运算The Except Operation

select course_id

from section

where semester = 'Fall' and year = 2009

except --except all保留重复

select course_id

from section

where semester = 'Spring' and year = 2010;

---

聚集函数 Aggregate Functions

：是以一个集合(集或多重集)为输入，返回单个值的函数。

基本聚集

平均值:avg([属性])

最小值:min([属性])

最大值:max([属性])

总和:sum([属性])

计数:count([属性])

select avg(salary) as avg_salary

from instructor

where dept_name = 'Comp.Sci';

select count(distinct ID)

from teachers

where semester = 'Spring" and year = 2010;

分组聚集 Aggregation with Grouping

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

-- 找出每个系的平均工资

select dept_name, avg(salary) as avg_salary

from instructor

group by dept_name;

注意:任何没有出现在group by 子句中的属性如果出现在select字句中的话，它只能出现在聚集函数的内部，错误例子:

select dept_name,ID, avg(salary)

from instructor

group by dept_name;

having 子句 The Having Clause

：对分组的限定条件，having子句中的谓词在形成分组后才起作用。

1.使用from子句来计算出一个关系

2.(如果存在)使用where子句中的谓词来过滤

3.(如果存在)使用group by子句来形成分组

4.(如果存在)使用having子句的谓词来对分组进行过滤

5.select子句在剩下的分组上应用聚集函数来得到单个结果元组

select dept_name, avg(salary) as svg_salary

from instructor

where avg(salary) > 42000;

[练习 3]统计人数小于 4 的部门的平均工资。

SELECT deptno, avg(sal)

FROM emp natural join dept

GROUP BY deptno

having count(empno)<4;

[练习 4]统计各部门的人数，按平均工资排序。

select deptno, count(empno)

from emp natural join dept

group by deptno

order by avg(sal);

---

嵌套子查询 Nested Subqueries

集合成员资格 Set Membership

[元组] in [集合]:测试元组是否是集合中的成员

[元组] not in [集合]:测试元组是否不是集合中的成员

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

select count (distinct ID)

from takes

where (course id, sec id, semester, year) in (select course id, sec id, semester, year

                                              from teaches

                                              where teaches.ID = 10101);

集合的比较 Set Comparison

[属性] > some [表]:至少比某一个要大

注意：

= some 等价于 in

<> some 并不等价于 not in

select name

from instructor

where salary > some (select salary

                     from instructor

                     where dept name = ’Biology’);

[属性] > all [表]:比所有的都大

注意：

<> all 等价于 not in

= all 并不等价于 in

select name

from instructor

where salary > all (select salary

                    from instructor

                    where dept name = ’Biology’);

空关系测试 Test for Empty Relations

exists:测试一个子查询的结果是否存在元组

--找出2009年秋与2010年春同时开课的课程

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

--使用了来自外层查询的一个相关名称的子查询被称为：相关子查询correlated subquery

from子句中的子查询 Subqueries in the From Clause

--该例子在having中也出现过，早先在having中出现的谓词现在出现在了where中

select dept_name, avg_salary

from (select dept_name, avg (salary) as avg_salary

      from instructor

      group by dept_name)

where avg_salary > 42000;

--找出工资总额最大的系

select max (tot_salary)

from (select dept_name, sum(salary)

      from instructor

      group by dept_name) as dept_total (dept_name, tot_salary);

注意：from嵌套子查询不能使用来自from子句其他关系的相关变量，下面例子中若无lateral,子查询就不能使用变量I1

select name, salary, avg_salary

from instructor I1, lateral (select avg(salary) as avg_salary

                             from instructor I2

                             where I2.dept_name= I1.dept_name);

with子句 The with Clause

with子句提供定义临时关系的方法，这个定义只对包含with子句的查询有效

--找出具有最大预算的系

with max_budget (value) as

     (select max(budget)

      from department)

select budget

from department, max_budget

where department.budget = max_budget.value;

--找出系总工资大于所有系的平均总工资的系

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;

标量子查询 Scalar Subqueries

若该子查询只返回包含单个属性的单个元组，则允许子查询出现在返回单个值的表达式能出现的任何地方。

这里还不是很懂P54

--列出所有系名与它们有的教师数

select dept_name,

     (select count(*)

      from instructor

      where department.dept_name = instructor.dept_name)  as num_instructors

from department;

---

数据库的修改 Modification of the Database

删除 Deletion

--删除符合谓词P条件的元组

delete from r

where P;

--删除字段

alter table [表名] drop [字段名];

alter table r drop A;

--删除表r的所有元组

delete from r;

delete from instructor

where dept_name in (select dept_name

                    from department

                    where building = ’Watson’);

--删除所有工资低于平均工资的教师元组

delete from instructor

where salary< (select avg (salary)

               from instructor);

这样写对吗？

插入 Insertion

--为已有关系增加/去掉属性

alter table r add A D;

insert into course values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

或

insert into course (course id, title, dept name, credits) values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

--使用select选出元组集并插入到另一个关系中

insert into instructor

select ID, name, dept_name, 18000

from student

where dept_name = ’Music’ and tot_cred > 144;

注意：此处是先执行完select之后再执行insert操作，若不这样做则可能产生递归插入的错误，像下面这样：

insert into student -- (若无主码约束)

       select *

       from student;

更新 Updates

update instructor

set salary= salary * 1.05;

update instructor

set salary = salary * 1.05

where salary < 70000;

update instructor

set salary = salary * 1.05

where salary < (select avg (salary)

                from instructor);

update instructor

set salary = case

               when salary <= 100000 then salary * 1.05

               else salary * 1.03

             end

case

     when pred1 then result1

     when pred2 then result2

     ...

     when predn then resultn

     else result0

end

select case

          when sum(credits) is not null then sum(credits)

          else 0

       end