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 阅读(3133) 评论(0) 编辑 收藏 举报