1.候选键、主键和外键约束：
create table Employee(
id int primary key,
name char(20),
birthday Date,
address varchar(30),
city varchar(10),
sex char(2),
salary numeric(10,2),
dno int,
pno int
unique(name,birthday)
);
create table Employee(
id int,
name char(20),
birthday Date,
address varchar(30),
city varchar(10),
sex char(2),
salary numeric(10,2),
dno int Reference Department(deNumber),//外键约束
pno int
unique(name,birthday)//候选键
primary key(id)//员工表的主键
);
外部键约束增加了关系数据库表的关联完整性，可以较好的保证数据库表之间的关联完整性。主键所在的表成为父表，为主控方，对应的外部键所在的表为子表，为被控方。
dno int Reference Department(deNumber)工作人员所属的部门号(dno)是相对于Department部门表的外部键。这样定义以后若想添加一个工作人员的记录，添加的记录中dno列的值必须在Department记录中存在与其相同的值，否则就无法添加记录。
也可以用另一种方式添加外键，若对应的主键是多个列的组合，那么外部键的定义只能放在所有列之后。只能是这种定义：
create table Employee(
id int,
name char(20),
birthday Date,
address varchar(30),
city varchar(10),
sex char(2),
salary numeric(10,2),
dno int，
pno int，
unique(name,birthday)//候选键
primary key(id)//员工表的主键
foreign key(dno) references Department(deNumber)
);
在对子表中的外部键或对父表的主键进行更新时（可能会产生的关联完整性的问题的四种情况）总结：
1.在子表中添加记录：
在子表Employee中添加一个记录，其dno列的值必须与Department表中dNumber列中的一个值相同。或者添加dno列值为NULL，否则将破坏数据库的完整性。但是向父表Department中添加新记录不会产生这个问题。
2.更新子表外部键的值：用update更新后的值必须与父表中的一个主键相匹配，或者更新为NULL，否则将产生错误。
3.删除父表中的记录：从父表中删除一个记录，子表中对应该值的外部键的记录不在与父表中的任何一个主键值相匹配。例如从父表Department表中删除dNumber列值为3的记录，那么子表Employee表中所有dno列值为3的记录（即员工所在部门为3号）的记录都将产生没有匹配主键值的问题。然而从子表中删除记录并不会产生这个问题。
4.更新父表中主键的值：父表Department中更新一个主键的值后，子表Employee中与原来值相对应的外部键的值也将不再与父表中任一主键的值相匹配。
下面是解决上面问题的方法：
1.使用Restrict关键字：父表中的主键值在子表中有许多具有该值的外部键的记录时，若在约束中指定Restrict关键字，则企图删除父表中的语句将被拒绝，企图更新父表中记录的主键值也将被拒绝。
2.CASCADE关键字：当父表中一个记录被删除时，cascade可以使其子表中所有与该记录的主键值对应的记录的（例如父表Department中的dNumber为3的值被删除，在子表中外键dno为3的记录都将被删除）都将从子表中删除。而更新父表中一个主键值时，CASCADE可以使其子表中对应外部键的值也自动被修改。
3.SET NULL：当父表中一个记录被删除时，SET NULL可以使其子表中所有与该记录的主键值对应的外部键的值将自动被赋值为NULL。更新同理。
4.SET DEFAULT： 当父表中一个记录被删除时，SET DEFAULT可以使其子表中的所有与该记录的主键值对应的外部键的值都将被赋值为缺省值。
2. NULL 和NOT NULL约束：
NULL值并不表示0值或空值，而是表示一个丢失、不知道，不可用的值。
举例子说明该约束：例如，Employee表中，如果没有name就无法知道工作人员的名字，一般来说公司都要记录工作人员的名字，因此name的值可以规定为NOT NULL,而工作人员的生日，地址，等并不是一定要有的，也允许其丢失，这些列值允许为NULL。
作为区分每个表的主键的值，不可以为空值，而对于外部键来说，NULL值是允许存在的。
举例来说明：在Company数据库中，Employee表中pno列是相对于Project的外部键，并不是公司的一个员工都参与一个工程项目的工作，如总裁等。因此Employee表中的pno允许有NULL值存在，（员工表Employee的总裁的外部键pno可以为NULL）。
3. 校验约束
举例来说明：在Employee增加校验约束条件
Salary NUMERIC（10,2）CHECK（Salary >1000.00 ）后Employee表中拒绝小于等于1000的所有记录。
用UPDATE Employee Set Salary=900.00 where id = 2002，该语句产生错误。
在员工表中sex列一般限制男和女两个值，可以增加一个检验约束来实现对sex列的限制。
create table Employee(
id int,
name char(20),
birthday Date,
address varchar(30),
city varchar(10),
sex char(2) check(sex in('男'，'女')),
salary numeric(10,2),
dno int Reference Department(deNumber),//外键约束
pno int
unique(name,birthday)//候选键
primary key(id)//员工表的主键
Constraint sex_check CHECK(sex in('男','女'))，
Constraint salary_check CHECK(salary > 1000.00)，
);
使用Constraint关键字进行检验约束的定义。这样我们可以在以后必要的时候对定义的约束进行激活和撤销。
5.默认值（default）
举例：salary numeric(10,2) default 3000.00，当对表添加记录时，若对设有默认值的列没有指定明确的值，系统自动会进行默认的添加。
Insert into Employee values ('peter','2012-02-20','','北京','男',1004,1,3);
6.Company数据库表的创建
1.Employee 表：
create table Employee(
id int primary key,
name char(15) NOT NULL, /*员工名字*/
birthday Date, /*员工生日*/
address varchar(30), /*员工住址*/
city char(10), /*员工所在城市*/
sex char(2) check(sex in('男'，'女')) default '男',
salary numeric(10,2) , /*员工工资*/
dno int Reference Department(dnumber), /*外键约束 部门号*/
pno int,
foreign key(dno) references Department(pnumber), /*外键约束 项目号*/
--unique(name,birthday)//候选键
--primary key(id)//员工表的主键
Constraint sex_check CHECK(sex in('男','女'))，
Constraint salary_check CHECK(salary > 1000.00),
);
2.Department部门表
Create table Department(
dnumber int primary key, /*主键约束 部门号*/
dname char(20) unique, /*公司中每个部门的名称应该是不同的*/
mgrid int, /*部门负责人的id号*/
mgrdate DATE /*负责人上任日期*/
);
3.Project项目表的
Create table Project(
pnumber int primary key, /*项目号*/
pname varchar(20) unique, /*项目名称*/
pmgrid int,
budget NUMRIC(10,2), /*项目预算*/
Gross NUMRIC(10,2), /*预计总收入*/
pstartime Date, /*项目开始时间*/
pendtime Date /*项目结束时间*/
);
4.Paccpeter项目接收方表：
说明：由于不同项目的接收方可以相同，因此以pnumber列作为表的主键。同时pnumber列是Paccpeter表相对于Project表的外部键。若父表Project不存在的项目号在子表Paccpeter也不能存在。
Create table Paccpeter(
pnumber int primary key, /*项目号*/
accepter char(20) NOT NULL, /*项目接收方不允许为空*/
city char(10),
acceptdate Date, /*接收日期*/
pnumber reference Project(pnumber) /*外键pnumber*/
);
5.Dep_Pro部门和项目连接表的创建：
说明：表中只包含两列：记录部门号的dnumber和记录项目号的pnumber，该表中以二者的组合作为主键。同时dnumber是相对于Department表的外部键，而pnumber列是相对于Project表的外部键。
CREATE TABLE Dep_Pro(
dnumber INT ,
pnumber INT ,
PRIMARY KEY(dnumber,dnumber),
dnumber REFERENCES Department(dnumber),
pnumber REFERENCES Department(pnumber),
);
7创建索引：
1.在单列上创建索引：
在员工表id列上创建索引：
CREATE INDEX id_index ON Employee(id);
Select name,sex,id,salary from Employee;
查询的结果会按照索引的顺序进行排列。
2.在多列上创建索引：
CREATE INDEX name_salary_index ON Employee(name,salary);
在多列上创建索引时将按照列出的顺序进行排列。先将按照name进行索引，然后在按照salary进行索引。
3.创建唯一索引：
唯一索引可以在单列也可以在多列上创建，唯一索引可以避免相同值的多个记录的出现，唯一索引是指两个记录中没有同一个索引值。
CREATE UNIQUE INDEX id_index ON Employee(id);
8.修改数据库：
1.向表中添加列：
ALTER TABLE Tablename
ADD columnname datatype
[DEFAULT expression]
[REFERENCES Tablename(columnname)]
[CHECK constraint]
例如：如果要在 Department 表中添加city 列,默认部门所在城市为北京。
Alter table Department add city char（10） default '北京';
也可以添加新列的约束：例如，公司新规定每个项目的预计总收入不得少于10000.00元，
则可以为Project表中添加一个约束：
ALTER TABLE Project CONSTRAINT gross_check (gross > 10000.00);
2.修改已有列
ALTER TABLE Tablename
MODIFY columnname [datatype]
[DEFAULT expression]
[REFERENCES Tablename(columnname)]
[CHECK constraint]
例如，将Employee表中的name列的长度变成30个字符长：
ALTER TABLE Employee MODIFY NAME CHAR(30) ;
不能修改数据类型，即不能从int改到 char，可以减少字符串的长度，然而，
不能长度少于已有数据中长度最大的值。
也可以修改列所具有的约束：
例如，将 Employee表中sex列的默认值从“男”修改成“女”：
ALTER TABLE Employee MODIFY sex DEFAULT '女';
在SQL 中，也可以使用 ALTER TABLE 语句删除表中已有的列。
例如从Employee表中删除birthday：
ALTER TABLE Employee DROP birthday;
9.删除索引：
Drop index indexname;
使用索引的原因总结：
使用索引是为了提高检索数据的速度，创建索引后，我们就可以通过索引来查找数据，而不必扫描每个记录。这好比书的目录一样，使我们很快就会定位到我们要找的那一页。但是我们不能对每一列进行索引。因为，创建和维护索引需要占用数据库的时间和空间的。
1.适合使用索引的情况：
（1）大量值：表中的记录在该列上含有大量不同的值时，为表建立索引可以起到很好的作用。
（2）经常在查询中使用：某列在查询中使用的越多，在该列上创建的索引所能起到的加快速度的作用越明显。
（3）查询返回记录相对较少时：当返回的记录大大少于表中的记录总量的时候，索引能很好的加快查询速度。若总是返回大量的记录，由于索引本身的开销，索引并不能很好的提高检索的速度。
（4）用于两个表连接的操作：由于索引使每个表中的记录按顺序排列，所以使用被索引的列进行连接操作，可以提高连接速度。
2.不适和建立索引的情况：
(1)小型表：对于小型表，由于表中的记录不多，而索引自身也会增加开销，通过使用索引并不能提高性能，所以小型表不使用索引。
(2)只有很少值的列不适合建立索引。
(3)用户查询方式经常变化的表不适合建立索引。
(4) 在具有较多的NULL值的列不适合创建索引。
(5) 定期更新或者修改的列不断的更新或者修改导致索引页需要进行不断的更新，额外的消耗过多。
10.多表连接查询：
1.什么是连接？
定义：连接是将多个表的数据结合到一起的查询，即连接操作可以在一个select语句中完成从多个表中查找和处理数据。使用连接时可以使用相同名字的列，例如可以使用pnumber连接Project表与Paccpter表；也可以使用不同名字的列进行连接，例如使用Employee表中的id列与Department的mgrid列进行连接。进行连接时用于连接的必须是可连接的列，即要求它们具有相同的数据类型。
一般来说，连接的语法可以分为二种：
（1） 传统的连接语法：select from/where包含多个表的连接查询
传统的FROM/WHERE 连接语法基本格式如下：
SELECT select_list
FROM Table1name, [ Table2name, ...]
WHERE [Table1name.]column operator [Table2name.]column
用上面的表：
例如：查询部门负责人的负责的部门号、负责人名字、id 号、性别及工资。
分析：需要连接连个表：Employee表和Department表
原因：在Employee表中并没有存储谁是什么部门的负责人，负责人的id 号存储在Department 表中。（参照上面的表）
Select dnumber,name,id,sex,salary from Employee, Department where id=mgrid;
（2） SQL连接语法：join关键字来实现连接的操作JOIN CROSS JOIN及NATURAL
SELECT select_list
FROM Table1name [CROSS | NATURAL] JOIN Table2name
[ON [Table1name.]column operator [Table2name.]column]
[WHERE conditions]
● 第一行中的select_list仍然是选择的列的名称，但是需要注意的是，如果使用两
个表中有相同名称的列，则必须限定是哪个表中的列；
● 第二行中关键字CROSS与NATURAL 是可选项，可以使用也可以不使用。如果
选择了CROSS或NATURAL 关键字，那么就不能使用ON关键字；
● 单独使用JOIN 关键字时，需要使用ON关键字来设定连接的条件；
● 使用CROSS JOIN 时，不能使用关键字ON，因此必须使用WHERE子句设定连
接的条件。因为如果不设定连接条件，将返回表之间的每一种可能组合(即笛卡
尔积)，连接结果将非常庞大；
● NATURAL JOIN 只 有在两个表有相同名称的列时才能使用，将在同名列上进行连
接。因此，不必使用关键字 ON 或 WHERE 子句设定连接条件。当然，使用 WHERE
子句限制查找的行仍然是可以的。
例如查询：部门负责人的负责的部门号、负责人名字、id 号、性别及工资。
用join连接：
Select dnumber,name,sex,id,salary from Employee join Department on mgrid = id;
Results
dnumber name sex id salary
———— ————— ——— —— ————
6 魏成 男 6001 5000.00
1 林志千 男 1001 6000.00
2 陈广海 男 2001 5000.00
3 张宇 男 3001 5000.00
4 张峰 男 4001 7000.00
5 李志深 男 5001 5000.00
用cross join连接：
SELECT dnumber, name, sex, id, salary FROM Employee CROSS JOIN Department
WHERE id = mgrid
结果是与上面相同的。
但是这里不能用NATURAL JOIN连接。虽然Employee的id列和Department表mgrid列的数据类型和存储的内容相同，但是名称不相同，所以不能用自然连接。
Project的表和Paccpter表中有两个相同的列pnumber，则可以对这两个表进行NATURAL JOIN连接。
Select pnumber,accepter,budget from Project NATURAL JOIN Paccpter;
Results
pnumber accepter budget
———— ———————— —————
101 蓝科通讯公司 110000.00
102 华夏技术有限公司 140000.00
103 汉升咨询公司 150000.00
104 神州发展有限公司 120000.00
105 四海贸易有限公司 100000.00
201 化天通讯公司 200000.00
202 日胜公司 220000.00
203 华田汽修公司 250000.00
204 东胜有限公司 240000.00
301 科华贸易公司 300000.00
302 新安有限公司 320000.00
303 华田汽修公司 360000.00
也可以用传统的from/where循环代替：
Select pnumber,accepter,budget from Project，Paccpter where Project.pnumber = Paccpter.pnumber;
用join on代替：
Select pnumber,accepter,budget from Project join Paccpter on Project.pnumber = Paccpter.pnumber;
2处理连接：
两个表有意义的连接到一起，必须有公共数据。
Project表：
SELECT pnumber, budget FROM Project;
Results
pnumber budget
———— —————
101 110000.00
102 140000.00
103 150000.00
104 120000.00
105 100000.00
201 200000.00
202 220000.00
203 250000.00
204 240000.00
301 300000.00
302 320000.00
303 360000.00
Paccepter 表:
SELECT pnumber, accepter FROM Paccepter;
Results
pnumber accepter
———— ———————
101 蓝科 通讯公司
102 华夏 技术有限公司
103 汉升 咨询公司
104 神州 发展有限公司
105 四海 贸易有限公司
201 化天通 讯公司
202 日胜公司
203 华田汽 修公司
204 东胜有 限公司
301 科华贸 易公司
302 新安有 限公司
303 华田汽 修公司
在理想的情况下，对数据库中的表进行连接时使用表的主键与相应外部键。因为，设计数据库时，主键与外部键在逻辑上就是互相联系的，并且主键与外部键之间具有一致性，会保持主键列与外部键列在值上的一致。主键与外部键可以很好的保持数据库完整性。
较好的连接应该符合以下要求：
● FROM 子句中必须包含用于连接的所有表，不能仅包含一部分用于连接的表；
● 进行连接的列名称可以不同，除非要使用NATURAL JOIN，如果需要使用相同
名称的列必须为其加上表名作为限制，不然会产生混淆；
● 用于连接的列必须有相同的数据类型，或可以自动进行类型转换。不同类型的数
据是不能进行连接的。例如，不能使用Employee表中的birthday 列与Department
表中的dname 列进行连接；
● 用于连接的列必须具有相同的意义，这是最重要的一点，没有相同意义的列的连
接是毫无用处的。例如，虽然Employee表中的name列与Department 表中的dname
列数据类型相同，但是这两个列毫不相干，连接是没有意义的。
查询的时候必须指定查询条件：
例如：查找正工作在与项目接收方所在城市相同的工作人员。
方法一：Select id,name,sex,city from Employee,Paccepter where Employee.city = Paccepter.city;
方法二： select id,name,sex,city from Employee join Paccepter where Employee.city = Paccepter.city;
如果没有连接条件，查询的结果将是两个表的笛卡尔积。结果行数将大的惊人。
3．笛卡尔积
表一：
Results
A B C
—— —— ——
a b c
d e f
b c d
表二：
Results
D E
—— ——
d e
e f
则表一和表二的笛卡尔积为：
Results
A B C D E
—— —— —— —— ——
a b c d e
a b c e f
d e f d e
d e f e f
b c d d e
b c d e f
最后可以有：3*2=6条记录。
用于指定连接的表越多，其笛卡尔积的结果就越惊人，越需要很好的指定连接条件。
由于连接过程中使用了笛卡尔积，因此连接操作会导致性能的下降。
使用连接时应该遵循一定的原则：
● 用于连接的列已经创建了索引。因为索引会单独保存在磁盘上，且将数据按照一
定顺序进行了排列，索引的使用可以加快访问的速度；
● 用于连接的列具有相同的数据类型，包括是否允许空值。如果需要系统自动进行
类型转换是需要花费较多时间的，特别是在表中记录很多时，类型转换所花费的
时间将会很多.
4.连接中运算符号的使用：
例如：项目Project 表中的项目完成时间penddate 列的值大于Paccepter 表中的项目
预计接收时间acceptdate列的值。
Select pnumber, accepter, penddate, acceptdate from Project,Paccepter where penddate > acceptdate AND Project.pnumber = Paccepter.pnumber;
Results:
pnumber accepter penddate accedate
———— —————— —————— ——————
201 化天通讯公司 2001-12-20 2001-12-15
303 华田汽修公司 2002-04-25 2002-04-15
[2rows]
例如：Project 表中的项目完成时间penddate 列的值不等于Paccepter 表中的项目预
计接收时间accedate 列的值。
Select pnumber, accepter, penddate, acceptdate from Project,Paccepter where penddate <> acceptdate AND Project.pnumber = Paccepter.pnumber;
Results
pnumber accepter penddate accedate
———— —————— —————— ——————
201 化天通讯公司 2001-12-20 2001-12-15
204 东胜有限公司 2002-08-01 2002-08-06
303 华田汽修公司 2002-04-25 2002-04-15
[3rows]
在进行连接的时候还可以使用where子句限制查找到的行。
例如，只需要查看项目号小于201 的所有项目的项目编号、接收方及项目成本。
方法一:
Select pnumber, accepter, budget from Project, Paccpeter where Project.pnumber = Paccpeter.pnumber AND Project.pnumber < 201;
方法二：
Select pnumber, accepter, budget from Project join Paccpeter on Project.pnumber = Paccpeter.pnumber where Project.pnumber < 201;
Results
pnumber accepter budget
———— —————— —————
101 科华贸易公司 110000.00
102 华夏技术有限公司 140000.00
103 科华贸易公司 150000.00
104 神州发展有限公司 120000.00
105 四海贸易有限公司 100000.00
[5rows]
5．多表的连接：
需要知道工作人员本身以及其所属的部门和所参与的项目的详细信息，
Employee表、Department 表、Project 表这三个表以得到所需的信息。
但是多个表连接或者多个表连接时引起的性能下降更加严重。
例如：有三个表，每个表都包含100行记录，则三个表连接将产生100*100*100行的组合表。处理这样的组合表要花费的时间将会长的多。
例如：将返回工作人员本身以及其所属的部门和所参与的项目的详细信息：
分析：需要连接三个表：Employee表，Department表，Project表。
Select id,name,dnumber,dname,mgrid,pnumber,pname,gross from Employee, Department, Project where dno = dnumber AND pno = pnumber;
数据库设计时介绍了三种关系和数据库连接的关系：
（1）一对一的关系：
例如：项目Project表和项目接收Paccepter表时一对一的关系：
可以通过项目号pnumber进行连接：
方法一：Select pnumber,accepter,budget from Project join Paccepter on Project.pnumber = Paccepter.pnumber where Project.pnumber = 102;
方法二：Select pnumber,accepter,budget from Project,Paccepter where Project.pnumber = Paccepter.pnumber AND Project.pnumber = 102;
Result：
pnumber accepter budget
———— —————— ————
102 华夏技术有限公司 140000.
[1row]
（2）一对多的关系：
一个表的一条记录可以对应另一个表的若干条记录：
查询的时也可以对两个表进行连接：例如：部门和工作人员之间就是一对多的关系。
希望由工作人员记录得出其所属的部门的信息就需要进行连接：
Select id，name，dnumber, mgrid from Employee,Department where dno = dnumber AND name = ‘lipeng’;
Select id,name,dnumber,mgrid from Employee join Department on dno = dnumber where name = ‘lipeng’;
Results
name id dnumber mgrid
————— —— ———— ———
李明 1002 1 1001
[1row]
(3) 多对多的关系：
无法对多对多的两个表进行连接，则必须使用创建数据库时专门为多对多的关系创建的连接表来实现。因此需要包含三个表的组合来连接数据。
例如：一个部门可能同时进行一个或者多个项目，一个项目可能由一个或者多个部门来配合才能很好的完成。
部门与项目间存在着多对多的关系。可以通过连接表来实现部门与项目的连接。
SELECT dnumber, mgrid, pnumber FROM Department JOIN Dep_pro
ON Department.dnumber = Dep_pro.dnumber Project JOIN Dep_pro ON Project.pnumber = Dep_pro.pnumber WHERE Project.pnumber = 301
SELECT dnumber, mgrid, pnumber FROM Department,Project,Dep_pro
WHERE Department.dnumber = Dep_pro.dnumber AND
Project.pnumber = Dep_pro.pnumber AND
Project.pnumber = 301
