SQL 高级用法 约束,Alter,Create,Drop
SQL PRIMARY KEY 约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表应该都一个主键，并且每个表只能有一个主键。
SQL PRIMARY KEY Constraint on CREATE TABLE
下面的 SQL 在 "Persons" 表创建时在 "Id_P" 列创建 PRIMARY KEY 约束：
MySQL:
CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (Id_P) )
SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( Id_P int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
如果需要命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束，请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT uc_PersonID PRIMARY KEY (Id_P,LastName) )SQL PRIMARY KEY Constraint on ALTER TABLE
如果在表已存在的情况下为 "Id_P" 列创建 PRIMARY KEY 约束，请使用下面的 SQL：
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD PRIMARY KEY (Id_P)
如果需要命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束，请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
注释：如果您使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）。
撤销 PRIMARY KEY 约束
如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：
MySQL:
ALTER TABLE Persons DROP PRIMARY KEY
SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT pk_PersonID
SQL FOREIGN KEY 约束
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
让我们通过一个例子来解释外键。请看下面两个表：
"Persons" 表：
| Id_P | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Avenue | New York |
| 3 | Carter | Thomas | Changan Street | Beijing |
"Orders" 表：
| Id_O | OrderNo | Id_P |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
请注意，"Orders" 中的 "Id_P" 列指向 "Persons" 表中的 "Id_P" 列。
"Persons" 表中的 "Id_P" 列是 "Persons" 表中的 PRIMARY KEY。
"Orders" 表中的 "Id_P" 列是 "Orders" 表中的 FOREIGN KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
SQL FOREIGN KEY Constraint on CREATE TABLE
下面的 SQL 在 "Orders" 表创建时为 "Id_P" 列创建 FOREIGN KEY：
MySQL:
CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, Id_P int, PRIMARY KEY (O_Id), FOREIGN KEY (Id_P) REFERENCES Persons(Id_P) )
SQL Server / Oracle / MS Access:
CREATE TABLE Orders ( O_Id int NOT NULL PRIMARY KEY, OrderNo int NOT NULL, Id_P int FOREIGN KEY REFERENCES Persons(Id_P) )
如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束，请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, Id_P int, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P) )SQL FOREIGN KEY Constraint on ALTER TABLE
如果在 "Orders" 表已存在的情况下为 "Id_P" 列创建 FOREIGN KEY 约束，请使用下面的 SQL：
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束，请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P) 撤销 FOREIGN KEY 约束
如需撤销 FOREIGN KEY 约束，请使用下面的 SQL：
MySQL:
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access:
ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders
SQL CHECK 约束
CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。
SQL CHECK Constraint on CREATE TABLE
下面的 SQL 在 "Persons" 表创建时为 "Id_P" 列创建 CHECK 约束。CHECK 约束规定 "Id_P" 列必须只包含大于 0 的整数。
My SQL:
CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CHECK (Id_P>0) )
SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( Id_P int NOT NULL CHECK (Id_P>0), LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束，请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes') )SQL CHECK Constraint on ALTER TABLE
如果在表已存在的情况下为 "Id_P" 列创建 CHECK 约束，请使用下面的 SQL：
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CHECK (Id_P>0)
如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束，请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes') 撤销 CHECK 约束
如需撤销 CHECK 约束，请使用下面的 SQL：
SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT chk_Person
SQL DEFAULT 约束
DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值，那么会将默认值添加到所有的新纪录。
SQL DEFAULT Constraint on CREATE TABLE
下面的 SQL 在 "Persons" 表创建时为 "City" 列创建 DEFAULT 约束：
My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' )
通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：
CREATE TABLE Orders ( Id_O int NOT NULL, OrderNo int NOT NULL, Id_P int, OrderDate date DEFAULT GETDATE() )
SQL DEFAULT Constraint on ALTER TABLE
如果在表已存在的情况下为 "City" 列创建 DEFAULT 约束，请使用下面的 SQL：
MySQL:
ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'
SQL Server / Oracle / MS Access:
ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'SANDNES'
撤销 DEFAULT 约束
如需撤销 DEFAULT 约束，请使用下面的 SQL：
MySQL:
ALTER TABLE Persons ALTER City DROP DEFAULT
SQL Server / Oracle / MS Access:
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT
CREATE INDEX 语句用于在表中创建索引。
在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。
索引
您可以在表中创建索引，以便更加快速高效地查询数据。
用户无法看到索引，它们只能被用来加速搜索/查询。
注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。
SQL CREATE INDEX 语法
在表上创建一个简单的索引。允许使用重复的值：
CREATE INDEX index_name ON table_name (column_name)
注释："column_name" 规定需要索引的列。
SQL CREATE UNIQUE INDEX 语法
在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。
CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE INDEX 实例
本例会创建一个简单的索引，名为 "PersonIndex"，在 Person 表的 LastName 列：
CREATE INDEX PersonIndex ON Person (LastName)
如果您希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：
CREATE INDEX PersonIndex ON Person (LastName DESC)
假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：
CREATE INDEX PersonIndex ON Person (LastName, FirstName)
通过使用 DROP 语句，可以轻松地删除索引、表和数据库。
SQL DROP INDEX 语句
我们可以使用 DROP INDEX 命令删除表格中的索引。
用于 Microsoft SQLJet (以及 Microsoft Access) 的语法:
DROP INDEX index_name ON table_name
用于 MS SQL Server 的语法:
DROP INDEX table_name.index_name
用于 IBM DB2 和 Oracle 语法:
DROP INDEX index_name
用于 MySQL 的语法:
ALTER TABLE table_name DROP INDEX index_name
SQL DROP TABLE 语句
DROP TABLE 语句用于删除表（表的结构、属性以及索引也会被删除）：
DROP TABLE 表名称
SQL DROP DATABASE 语句
DROP DATABASE 语句用于删除数据库：
DROP DATABASE 数据库名称
SQL TRUNCATE TABLE 语句
如果我们仅仅需要除去表内的数据，但并不删除表本身，那么我们该如何做呢？
请使用 TRUNCATE TABLE 命令（仅仅删除表格中的数据）：
TRUNCATE TABLE 表名称
ALTER TABLE 语句
ALTER TABLE 语句用于在已有的表中添加、修改或删除列。
SQL ALTER TABLE 语法
如需在表中添加列，请使用下列语法:
ALTER TABLE table_name ADD column_name datatype
要删除表中的列，请使用下列语法：
ALTER TABLE table_name DROP COLUMN column_name
注释：某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN column_name)。
要改变表中列的数据类型，请使用下列语法：
ALTER TABLE table_name ALTER COLUMN column_name datatype
原始的表 (用在例子中的)：
Persons 表:
| Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Avenue | New York |
| 3 | Carter | Thomas | Changan Street | Beijing |
SQL ALTER TABLE 实例
现在，我们希望在表 "Persons" 中添加一个名为 "Birthday" 的新列。
我们使用下列 SQL 语句：
ALTER TABLE Persons ADD Birthday date
请注意，新列 "Birthday" 的类型是 date，可以存放日期。数据类型规定列中可以存放的数据的类型。
新的 "Persons" 表类似这样：
| Id | LastName | FirstName | Address | City | Birthday |
|---|---|---|---|---|---|
| 1 | Adams | John | Oxford Street | London | |
| 2 | Bush | George | Fifth Avenue | New York | |
| 3 | Carter | Thomas | Changan Street | Beijing | |
改变数据类型实例
现在我们希望改变 "Persons" 表中 "Birthday" 列的数据类型。
我们使用下列 SQL 语句：
ALTER TABLE Persons ALTER COLUMN Birthday year
请注意，"Birthday" 列的数据类型是 year，可以存放 2 位或 4 位格式的年份。
DROP COLUMN 实例
接下来，我们删除 "Person" 表中的 "Birthday" 列：
ALTER TABLE Person DROP COLUMN Birthday
Persons 表会成为这样:
| Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Avenue | New York |
| 3 | Carter | Thomas | Changan Street | Beijing |
Auto-increment 会在新纪录插入表中时生成一个唯一的数字。
AUTO INCREMENT 字段
我们通常希望在每次插入新纪录时，自动地创建主键字段的值。
我们可以在表中创建一个 auto-increment 字段。
用于 MySQL 的语法
下列 SQL 语句把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键：
CREATE TABLE Persons ( P_Id int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) )
MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。
默认地，AUTO_INCREMENT 的开始值是 1，每条新纪录递增 1。
要让 AUTO_INCREMENT 序列以其他的值起始，请使用下列 SQL 语法：
ALTER TABLE Persons AUTO_INCREMENT=100
要在 "Persons" 表中插入新纪录，我们不必为 "P_Id" 列规定值（会自动添加一个唯一的值）：
INSERT INTO Persons (FirstName,LastName) VALUES ('Bill','Gates')
上面的 SQL 语句会在 "Persons" 表中插入一条新纪录。"P_Id" 会被赋予一个唯一的值。"FirstName" 会被设置为 "Bill"，"LastName" 列会被设置为 "Gates"。
用于 SQL Server 的语法
下列 SQL 语句把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键：
CREATE TABLE Persons ( P_Id int PRIMARY KEY IDENTITY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
MS SQL 使用 IDENTITY 关键字来执行 auto-increment 任务。
默认地，IDENTITY 的开始值是 1，每条新纪录递增 1。
要规定 "P_Id" 列以 20 起始且递增 10，请把 identity 改为 IDENTITY(20,10)
要在 "Persons" 表中插入新纪录，我们不必为 "P_Id" 列规定值（会自动添加一个唯一的值）：
INSERT INTO Persons (FirstName,LastName) VALUES ('Bill','Gates')
上面的 SQL 语句会在 "Persons" 表中插入一条新纪录。"P_Id" 会被赋予一个唯一的值。"FirstName" 会被设置为 "Bill"，"LastName" 列会被设置为 "Gates"。
用于 Access 的语法
下列 SQL 语句把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键：
CREATE TABLE Persons ( P_Id int PRIMARY KEY AUTOINCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
MS Access 使用 AUTOINCREMENT 关键字来执行 auto-increment 任务。
默认地，AUTOINCREMENT 的开始值是 1，每条新纪录递增 1。
要规定 "P_Id" 列以 20 起始且递增 10，请把 autoincrement 改为 AUTOINCREMENT(20,10)
要在 "Persons" 表中插入新纪录，我们不必为 "P_Id" 列规定值（会自动添加一个唯一的值）：
INSERT INTO Persons (FirstName,LastName) VALUES ('Bill','Gates')
上面的 SQL 语句会在 "Persons" 表中插入一条新纪录。"P_Id" 会被赋予一个唯一的值。"FirstName" 会被设置为 "Bill"，"LastName" 列会被设置为 "Gates"。
用于 Oracle 的语法
在 Oracle 中，代码稍微复杂一点。
您必须通过 sequence 对创建 auto-increment 字段（该对象生成数字序列）。
请使用下面的 CREATE SEQUENCE 语法：
CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10
上面的代码创建名为 seq_person 的序列对象，它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能。CACHE 选项规定了为了提高访问速度要存储多少个序列值。
要在 "Persons" 表中插入新纪录，我们必须使用 nextval 函数（该函数从 seq_person 序列中取回下一个值）：
INSERT INTO Persons (P_Id,FirstName,LastName) VALUES (seq_person.nextval,'Lars','Monsen')
上面的 SQL 语句会在 "Persons" 表中插入一条新纪录。"P_Id" 的赋值是来自 seq_person 序列的下一个数字。"FirstName" 会被设置为 "Bill"，"LastName" 列会被设置为 "Gates"。
SQL Date 数据类型
MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：
- DATE - 格式 YYYY-MM-DD
- DATETIME - 格式: YYYY-MM-DD HH:MM:SS
- TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
- YEAR - 格式 YYYY 或 YY
SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：
- DATE - 格式 YYYY-MM-DD
- DATETIME - 格式: YYYY-MM-DD HH:MM:SS
- SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS
- TIMESTAMP - 格式: 唯一的数字
SQL 日期处理
如果不涉及时间部分，那么我们可以轻松地比较两个日期！
假设我们有下面这个 "Orders" 表：
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | computer | 2008-12-26 |
| 2 | printer | 2008-12-26 |
| 3 | electrograph | 2008-11-12 |
| 4 | telephone | 2008-10-19 |
现在，我们希望从上表中选取 OrderDate 为 "2008-12-26" 的记录。
我们使用如下 SELECT 语句：
SELECT * FROM Orders WHERE OrderDate='2008-12-26'
结果集：
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | computer | 2008-12-26 |
| 3 | electrograph | 2008-12-26 |
现在假设 "Orders" 类似这样（请注意 "OrderDate" 列中的时间部分）：
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | computer | 2008-12-26 16:23:55 |
| 2 | printer | 2008-12-26 10:45:26 |
| 3 | electrograph | 2008-11-12 14:12:08 |
| 4 | telephone | 2008-10-19 12:56:10 |
如果我们使用上面的 SELECT 语句：
SELECT * FROM Orders WHERE OrderDate='2008-12-26'
那么我们得不到结果。这是由于该查询不含有时间部分的日期。
提示：如果您希望使查询简单且更易维护，那么请不要在日期中使用时间部分！
| Text | 用于文本或文本与数字的组合。最多 255 个字符。 | |
| Memo |
Memo 用于更大数量的文本。最多存储 65,536 个字符。 注释：无法对 memo 字段进行排序。不过它们是可搜索的。 |
|
| Byte | 允许 0 到 255 的数字。 | 1 字节 |
| Integer | 允许介于 -32,768 到 32,767 之间的数字。 | 2 字节 |
| Long | 允许介于 -2,147,483,648 与 2,147,483,647 之间的全部数字 | 4 字节 |
| Single | 单精度浮点。处理大多数小数。 | 4 字节 |
| Double | 双精度浮点。处理大多数小数。 | 8 字节 |
| Currency |
用于货币。支持 15 位的元，外加 4 位小数。 提示：您可以选择使用哪个国家的货币。 |
8 字节 |
| AutoNumber | AutoNumber 字段自动为每条记录分配数字，通常从 1 开始。 | 4 字节 |
| Date/Time | 用于日期和时间 | 8 字节 |
| Yes/No |
逻辑字段，可以显示为 Yes/No、True/False 或 On/Off。 在代码中，使用常量 True 和 False （等价于 1 和 0） 注释：Yes/No 字段中不允许 Null 值 |
1 比特 |
| Ole Object | 可以存储图片、音频、视频或其他 BLOBs (Binary Large OBjects) | 最多 1GB |
| Hyperlink | 包含指向其他文件的链接，包括网页。 | |
| Lookup Wizard | 允许你创建一个可从下列列表中进行选择的选项列表。 | 4 字节 |
MySQL 数据类型
在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。
Text 类型：
| 数据类型 | 描述 |
|---|---|
| CHAR(size) | 保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。 |
| VARCHAR(size) |
保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。 注释：如果值的长度大于 255，则被转换为 TEXT 类型。 |
| TINYTEXT | 存放最大长度为 255 个字符的字符串。 |
| TEXT | 存放最大长度为 65,535 个字符的字符串。 |
| BLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。 |
| MEDIUMTEXT | 存放最大长度为 16,777,215 个字符的字符串。 |
| MEDIUMBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。 |
| LONGTEXT | 存放最大长度为 4,294,967,295 个字符的字符串。 |
| LONGBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。 |
| ENUM(x,y,z,etc.) |
允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。 注释：这些值是按照你输入的顺序存储的。 可以按照此格式输入可能的值：ENUM('X','Y','Z') |
| SET | 与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。 |
posted on 2010-04-12 20:14 lano chan 阅读(3140) 评论(0) 编辑 收藏 举报
