数据库笔记（SQL基础之DDL与DML）

1. SQL的基本部分

SQL内容基本分为以下几部分：
1. 数据定义语言（DDL）
2. 数据控制语言（DML）
3. 完整性
4. 视图定义
5. 事务控制
6. 嵌入式SQL
7. 权限

2. 数据定义语言（DDL）

数据定义语言DDL的操作包括表的创建、修改与删除。

2.1 数据类型

SQL的数据类型并不是定死的，其支持的数据类型由数据库决定。但基本的数据类型有以下几种：
- char(n)：由n个字符组成的定长的字符串，
- varchar(n)：最大长度为n的可变长度的字符串
- int：整型
- smallint：短整型
- numeric(p,d)：一个用户定义精度的小数，该小数存在p个数字，并且精确到小数点后d位。
- real, double precision：浮点型以及双精度浮点型。
- float(n)：有效数字为n位的浮点型

2.2 表创建

创建表包括表明声明、属性声明以及完整性约束声明。其格式为：

create table tablename
(AttributeName DataType,
AttributeName1 DataType1,
……,
integrity-constraint1,
integrity-constraint2);

其中约束语句是可选项，但一般来说主键声明primary key是必要的。基本示例如下：

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

创建表中SQL的基本完整性约束有以下几个：

• Not null(a)：该约束要求表中的属性a的值不能为NULL。
• Unique(a)：该约束要求表中的属性a的值不能重复出现，primary key约束默认包含该约束。
• Primary key(a，b)：主键约束，表示属性a和属性b为主键。
• Foreign key(a) reference t(b)：外键约束，表示属性a为参照表t的b属性的外键。
• Check(expression)：断言约束，表示括号内的表达式一定要满足。注意：由于数据库的语法差异，具体使用方法根据数据的不同而不同。

2.3 删除表

删除表使用drop table关键词进行。

drop table instructor;

删除表操作与删除行操作不同，删除表操作不但会将该表内的内容全部删除，还会将该表的结构也一并删除，删除后该表无法访问，但若使用删除行操作删除整张表，该表依然可以访问且表结构依然保留。

注意，删除表不一定能成功。若该表的主键是某表的外键，删除表操作可能无法进行。

2.4 修改表

修改表操作是该表表结构的操作，包括删除表的某属性或增加表的属性。该操作使用alter table关键词进行。

增加表的属性如下所示，该语句为instructor表增加属性AD，类型为int：

alter table instructor add AD int;

删除表的属性如下所示，该语句删除instructor表中的属性AD：

alter table instructor drop column AD;

改变表的类型如下所示，该语句将instructor表中的属性AD的类型改成varchar(25)：

alter table instrcutor alter column AD varchar(25);

3. 数据控制语言（DML）

数据控制语言（DML）包括数据的查询、插入、更新、删除。

3.1 数据查询

3.1.1 基本select语句

SQL使用select关键词进行数据的查询操作。使用select进行查询时，需要声明查询的属性名以及查询的表，若查询多个列则使用（,）隔开。查询instrctor表的所有dep_name以及given_name属性的值语句如下：

select dep_name, given_name 
from instructor;

此时，该查询的结果如下所示：

dep_name	name
Comp. Sci.	Wu
Music	Eitein
Physics	Alan
History	Sharn
Physics	Alex
Comp. Sci.	Mercer
History	Jack
Finance	Rin

若要表下的所有属性可以用星号代替属性名进行查询，以下语句表示查询instructor表下的所有属性。

select *
from instructor;

注意到dep_name查询结果中存在重复的值。由于SQL不会将重复的结果消除，表中的某列存在重复的属性的值，查询出来的结果依然会保留这种重复的的值。此时可以使用distinct关键词消除重复的值：

select distinct dep_name
from instructor;

此时查询出来的结果中每一种值只会出现一次。

3.1.2 Where子句

若想要查询表中满足某些条件的值，可以使用where关键词限定查询的记录的条件。以下语句用于查询instructor表中salary大于1000且小于2000的所有记录。

select *
from instructor
where salary > 8000 and salary < 10000;

where子句中可以使用的条件关系包括大于（>）、小于（<）、大于等于（>=）、小于等于（<=）、等于（=）、不等于（<>）等，另外还可以使用between和like关键词分别表示范围限定以及字符串模糊查找。注意到SQL中的等于和不等于与一般的编程语言中不同，不要搞混。另外，如例子所示，where语句可以将使用逻辑关系运算将多种条件进行结合。可进行的逻辑运算有and，or，not等。

3.1.3 Order by子句

若希望查询到的结果以某种顺序进行排序，可使用order by关键词来进行结果的排序。基本示例如下，该示例查询instructor表中的dep_name以及budget属性，并满足salary大于8000且小于10000，结果以salary的大小降序排列：

select dep_name, salary
from instructor
where salary > 8000 and salary < 10000;
order by salary desc

关键字desc指明排序的大小顺序，表示降序排列，升序则使用关键词asc。若使用字符串进行排序，大部分数据库中，英文是首字母进行排序，若相同则以下一个字母排序，一次类推；中文字符串默认依次以每个字的拼音声母排序。

Order by子句也支持多个属性同时排序，多个属性用逗号隔开，按照order by的属性声明顺序依次排序。先使用第一个属性顺序排序，若排序结果存在顺序相同，则在相同顺序的结果中使用第二个属性排序，以此类推。譬如以下示例，结果如下所示：

原表：

Company	OrderNumber
IBM	3532
W3School	2356
Apple	4698
W3School	6953

查询语句：

select Company, OrderNumber 
from Orders 
order by Company DESC, OrderNumber ASC;

结果：

Company	OrderNumber
W3School	2356
W3School	6953
IBM	3532
Apple	4698

可见由于在company中存在两个相同的值W3School，则两个记录之间又以OrderNumber的大小进行升序排序。

3.1.4 多表查询

SQL支持同时对多张表的进行结合查询。具体就是对多张表进行连接操作（见笔记关系数据库篇）。其示例如下所示：

select given_name, instructor.dep_name, building
from instructor, department 
where instructor.dep_name = department.dep_name;

在结果上看，该语句是将instructor关系和department关系做了自然连接，where子句中的相等条件确定了自然连接的规则。再选择了属性name、dep_name、building。但对于数据库来说，该语句先将from子句中的表进行笛卡儿乘操作，选取了满足where子句的行，最后选择其中的属性name、dep_name、building。

注意到两个表存在相同名字的属性则需要使用表名区别开。select子句中的instructor.dep_name就声明了是instructor表的属性。

该自然连接操作还可以使用关键词natural join关键词在from子句就对两表进行操作。示例如下：

select given_name, building 
from instructor natural join department

此时数据库会将所有名字相同的属性对两表进行自然连接操作。natrual关键词表示将两个表中的相同属性进行连接操作。若要指定进行连接的属性，可使用using关键词：

select given_name, building 
from instructor join department using (dep_name);

除了Join以外，还有其他的连接方式：
- Inner join: 等同与Join。
- Left outer join: 即使右表中没有匹配，也从左表返回所有的行且右表的属性的值都为null。
- Right outer join: 即使左表中没有匹配，也从右表返回所有的行且左表的属性的值都为null。
- Full outer join: 只要其中一个表中存在匹配，就返回行

3.2 插入行

插入语句示例如下：

insert into instructor
values (10211, ’Smith’, ’Biology’, 66000);

注意，插入的值的顺序要和表定义语句中的顺序相同。若希望插入的值是空值，需要将相应地方留空。

若希望按照自己的顺序插入数据，可以自己声明插入的列的名字，没有声明的列则为NULL值。以下为示例语句：

insert into Persons (LastName, Address) 
values ('Wilson', 'Champs-Elysees')；

3.3 删除行

SQL使用delete from关键词队某表的所有行进行删除操作：

delete from instructor；

若只想删除某些特定的行，可增加where语句进行删除内容的限定。

delete from instructor where salary > 5000;

3.4 更新行

SQL使用update关键词来对行的内容进行更新。其基本语法如下：

update instructor
set salary = salary * 1.03 ;

该语句将表instructor中所有的salary的值乘以1.03。与删除行同样，想要更新特定的行，可以增加where子句来限定更新的条件，如下所示：

update instructor
set salary = salary * 1.03 
where salary > 100000;

另外，更新操作还可使用case关键词来进行条件判断更新，该语句减少了对同一个属性使用多个update的情况。示例如下：

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