数据库系统概念3

SQL

SQL语言

SQL：结构化查询语言。包含以下几个部分：

• 数据定义语言（DDL）：提供定义关系模式、删除关系以及修改关系模式的命令
• 数据操纵语言（DML）：提供从数据库中查询信息，以及在数据库中插入元组、删除元组、修改元组的能力
• 完整性：SQL DDL中包括定义完整性约束的命令，数据库中的数据必须满足所定义的完整性约束。破坏完整性约束的更新不被允许。
• 视图定义：SQL DDL包括定义视图的命令
• 事务控制：SQL包括定义事务的开始与结束的命令
• 嵌入式SQL和动态SQL：关于嵌入式和动态SQL定义SQL语句如何嵌入到通用编程语言，如C、C++和Java中。
• 授权：SQL DDL包括定义对关系和视图的访问权限的命令。

数据定义

SQL标准支持的固有类型包括：

• char(n)：固定长度的字符串
• varchar(n)：可变长度的字符串
• int：整数类型
• smallint：小整数类型
• numeric(p,d)：定点数，精度由用户定义。该数有p位数字，其中d位数字在小数点右边。
• real, double precision：浮点数与双精度浮点数
• float(n)：精度至少为n位的浮点数
• ...

基本模式定义

create table student (
ID              varchar(5),
name            varchar(20) not null,
dept_name          varchar(20),
tot_cred        numeric(3,0),
primary key (ID),
foreign key (dept_name) references department) );

涉及的完整性约束包括：

• primary key (A,B,C,D...)：主码，唯一性且不为空。也可以直接写在某个属性后面。
• foreign key (A,B,C,D...) references R：这里(A,B,C,D...)的取值必须对应于关系R中某元组在主码属性上的取值
• not null：不为空

mysql中的外键写法有一些不一样foreign key (A,B,C,D...) references R(r_key)

增删操作

• insert into instructor values (‘10211’, ’Smith’, ’Biology’, 66000);：插入数据

• drop table student：删除表及其结构

• delete from student：删除表的内容，但是表的结构还在

• alter table

  • alter table r add A D：增加一列，增加的时候为空
  • alter table r drop A：删除一列

基本查询

select A1, A2, ...,
from r1, r2, ..., 
where P

对大小写不敏感：Name ≡ NAME ≡ name

关于select

查询结果是允许重复的：

• distinct：把重复的去掉

  select distinct dept_name
  from instructor

• all：与默认情况下一样，显示全部

  select all dept_name
  from instructor

关于where

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

在where中使用between：

mysql> select * from emp where sal between 3000 and 5000;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)

关于from

select * 
from dept,emp;

显示的是两个表的笛卡尔积。

关于join

自然连接：

select ename,dname from emp natural join dept;

只有当两个表的公共属性相同时，才保留。比如下面的例子，两个表是根据deptno来连接的。

需要注意的是：mysql支持natural join，但是sql server不支持。

mysql> select * from emp natural join dept;
+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+
| DEPTNO | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DNAME      | LOC      |
+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+
|     20 |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL | RESEARCH   | DALLAS   |
|     30 |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 | SALES      | CHICAGO  |
|     30 |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 | SALES      | CHICAGO  |
|     20 |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL | RESEARCH   | DALLAS   |
|     30 |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 | SALES      | CHICAGO  |
|     30 |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL | SALES      | CHICAGO  |
|     10 |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL | ACCOUNTING | NEW YORK |
|     20 |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL | RESEARCH   | DALLAS   |
|     10 |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL | ACCOUNTING | NEW YORK |
|     30 |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 | SALES      | CHICAGO  |
|     20 |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL | RESEARCH   | DALLAS   |
|     30 |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL | SALES      | CHICAGO  |
|     20 |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL | RESEARCH   | DALLAS   |
|     10 |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL | ACCOUNTING | NEW YORK |
+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+
14 rows in set (0.07 sec)

字符串运算

• 百分号：匹配任意子串
• 下划线：匹配任意一个字符
• ！！这里是大小写敏感的

mysql> select * from emp where HIREDATE like '1981%';
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
10 rows in set, 1 warning (0.00 sec)

关于order

对job升序排序，之后对ename降序排序：

mysql> select * from emp order by job, ename desc;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

并、交、差

mysql> select * from emp where HIREDATE like '1980%'
    -> union
    -> select * from emp where sal = 3000;
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK   | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
3 rows in set, 1 warning (0.01 sec)

遗憾的是，mysql不支持intersect和except操作。SQL Server应该可以

mysql> select * from emp where HIREDATE like '1981%'
    -> intersect
    -> select * from emp where sal = 3000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'intersect
select * from emp where sal = 3000' at line 2

mysql> select * from emp where HIREDATE like '1981%'
    -> except
    -> select * from emp where sal = 3000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'except
select * from emp where sal = 3000' at line 2

关于null

注意，判断是否为null时，使用关键字is，而不是等号

mysql> select ename,sal,comm from emp where comm is null;
+--------+---------+------+
| ename  | sal     | comm |
+--------+---------+------+
| SMITH  |  800.00 | NULL |
| JONES  | 2975.00 | NULL |
| BLAKE  | 2850.00 | NULL |
| CLARK  | 2450.00 | NULL |
| SCOTT  | 3000.00 | NULL |
| KING   | 5000.00 | NULL |
| ADAMS  | 1100.00 | NULL |
| JAMES  |  950.00 | NULL |
| FORD   | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
+--------+---------+------+
10 rows in set (0.00 sec)

mysql> select ename,sal,comm from emp where comm is not null;
+--------+---------+---------+
| ename  | sal     | comm    |
+--------+---------+---------+
| ALLEN  | 1600.00 |  300.00 |
| WARD   | 1250.00 |  500.00 |
| MARTIN | 1250.00 | 1400.00 |
| TURNER | 1500.00 |    0.00 |
+--------+---------+---------+
4 rows in set (0.00 sec)

三值逻辑：

• true
• false
• unknown

关于unknown需要注意：

• 如果逻辑子句被判断为false和unknown时，则不会加入结果集中。

• 判断一个子句是不是unknown可以使用is unknown或is not unknown

• 当出现null = null时，返回的也是unknown

聚集函数

关键字	作用
avg	average value
min	minimum value
max	maximum value
sum	sum of values
count	number of values

mysql> select avg(sal) as avg_sal from emp;
+-------------+
| avg_sal     |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.01 sec)

小练习：找出所有大于平均工资的员工

mysql> select * from emp where sal > (select avg(sal) from emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
6 rows in set (0.01 sec)

where 后面不能用聚集函数，所以先使用子查询获取平均数值

关于group by

该子句用于对一个或多个属性构造分组。常结合聚集函数来使用。

需要注意的是：使用了group by之后，出现在select中的没有被聚集的属性只能是出现在group by子句中的属性。比如说下面的例子中，job一定是要出现在group by后面的。

找出每个工作的平均工资：

mysql> select job,avg(sal) as avg_sal from emp group by job;
+-----------+-------------+
| job       | avg_sal     |
+-----------+-------------+
| ANALYST   | 3000.000000 |
| CLERK     | 1037.500000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |
| SALESMAN  | 1400.000000 |
+-----------+-------------+
5 rows in set (0.00 sec)

关于having

having子句是在形成分组之后才起作用的，所以可以使用聚集函数。

与上面强调的类似：出现在having中的只能是：被聚集的属性或出现在group by子句中的属性。

小练习：找出每一种工作大于当前工作平均工资的员工

mysql> select ename,e1.job,sal
    -> from emp as e1, (select job,avg(sal) as avg_sal from emp group by job) as e2
    -> where e1.job = e2.job and e1.sal > e2.avg_sal;
+--------+----------+---------+
| ename  | job      | sal     |
+--------+----------+---------+
| ALLEN  | SALESMAN | 1600.00 |
| JONES  | MANAGER  | 2975.00 |
| BLAKE  | MANAGER  | 2850.00 |
| TURNER | SALESMAN | 1500.00 |
| ADAMS  | CLERK    | 1100.00 |
| MILLER | CLERK    | 1300.00 |
+--------+----------+---------+
6 rows in set (0.01 sec)

嵌套子查询

where中的嵌套

子查询嵌套在where子句中，通常用于对集合的成员资格、集合的比较以及集合的基数进行检查。

some

<some,<=some,>=some,=some,<>some
其中=some等价于in，<>some不等价于not in

找出工资大于某个clerk的所有员工的名字和工资：

mysql> select ename,sal from emp
    -> where sal > some(select sal from emp where job = 'clerk');
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.00 sec)

all

其中=all不等价于in，<>all等价于not in

找出工资大于所有clerk的所有员工的名字和工资：

mysql> select ename,sal from emp 
    -> where sal > all(select sal from emp where job='clerk');
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| FORD   | 3000.00 |
+--------+---------+
8 rows in set (0.00 sec)

exists

要求后面的查询语句的结果不为空。

找出2009年秋季学期和2010年春季学期同时开课的所有课程：

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

这里嵌套的子查询里面用到的S是外层查询的相关名称，所以称为相关子查询(correlated subquery)

unique（重复性检测）

判断后面的查询语句的结果是否有重复元组.

找出所有在2009年最多开设一次的课程：

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

mysql貌似无法使用。

from中的嵌套

SQL允许在from子句中使用子查询表达式

with

找出具有最大预算值得系：

with max_budget(value) as
( select max(budget) 
    from department)
select budget
from department, max_budget
where department.budget = max_budget.value;

mysql貌似无法使用。

标量子查询

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

sql语句执行的基本顺序

select            5
...
from            1
...
where            2
...
group by        3
...
having            4
...
order by        6
...

修改数据库

删除

• 从instructor关系中删除Finance系的教师

delete form instructor
where dept_name = 'Finance';

• 从instructor关系中删除所有这样的教师元组，他们位于Watson大楼的系工作

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

插入

• 按属性顺序插入元组(也可以置为null)

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

• 不按照顺序

insert into course (course_id, title, depta_name, credits)
    values('CS-437','Database System', 'Comp. Sci.', 4);

• 在查询结果的基础上插入元组

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.03
where salary > 100000;

update instructor
set salary = salary * 1.05
where salary <= 100000;

上述两条语句的顺序如果改变，少于10000元的工资可能会涨两次。

当然，有更好的写法，可以避免更新顺序引起的问题。那就是使用case子句

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