sql语法基础(2)

sql语法(2)

一. 约束

1.1 建表约束

在create table或者alter table中创建约束

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

有如下几种约束:
NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。

1.2 NOT NULL

NOT NULL约束该列不接受null值，在下面的table中，P_Id和LastName列不接受NULL值。

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

1.3 UNIQUE

UNIQUE也是唯一索引，约束表中的该列具有唯一性，PRIMARY KEY也约束唯一

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

可以定义多个列的UNIQUE约束

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

表已经被创建，使用ALTER TABLE添加UNIQUE约束

ALTER TABLE Persons
ADD UNIQUE (P_Id)

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

撤销UNIQUE约束

ALTER TABLE Persons
DROP INDEX uc_PersonID

1.4 PRIMARY KEY

PRIMARY KEY又称主键，该列必须唯一，并且不能包含null
每个表都应该有一个主键，表主键只能有一个

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

命名并且定义多个列的主键

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

表已经被创建，使用ALTER TABLE添加PRIMARY KEY约束
添加的列必须在创建时就不包含Null

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

撤销 PRIMARY KEY 约束

ALTER TABLE Persons
DROP PRIMARY KEY

1.5 FOREIGN KEY

又称外键，一个表的FOREIGN KEY指向另一个表的PRIMARY KEY
Persons表

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Orders表

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	2
4	24562	1

"Orders" 表中的 "P_Id" 列指向 "Persons" 表中的 "P_Id" 列
"Persons" 表中的 "P_Id" 列是 "Persons" 表中的 PRIMARY KEY
"Orders" 表中的 "P_Id" 列是 "Orders" 表中的 FOREIGN KEY
FOREIGN KEY可预防破坏表之间的连接行为，也能防止非法数据插入外键列，只能插入外键指向的那个表的列值之一。
创建表时设置外键

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

命名 FOREIGN KEY 约束

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

修改表时

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

撤销 FOREIGN KEY

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

1.6 CHECK约束

限制表中值的范围，单个列定义CHECK该列只能取特定的值，对一个表定义CHECK约束会基于行中其他列值在特定的列中对值进行限制
创建表时，规定 "P_Id" 列必须只包含大于 0 的整数

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

多个列的 CHECK 约束

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

更改表时

ALTER TABLE Persons
ADD CHECK (P_Id>0)

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

撤销

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

1.7 DEFAULT 约束

插入时没有其他值，会插入默认值

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

也可以插入系统值

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

修改表时

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

撤销

ALTER TABLE Persons
ALTER City DROP DEFAULT

二. CREATE INDEX

更新一个有索引的表需要花费更多时间，因此仅在被搜索的列上创建索引
简单索引，允许重复

CREATE INDEX index_name ON table_name (column_name)

唯一索引，不允许重复

CREATE UNIQUE INDEX index_name ON table_name (column_name)

联合索引

CREATE INDEX PIndex ON Persons (LastName, FirstName)

三. drop

删除索引

ALTER TABLE table_name DROP INDEX index_name

删除表

DROP TABLE table_name

删除数据库

DROP DATABASE database_name

删除数据，但是不删除表本身

TRUNCATE TABLE table_name

四. ALTER TABLE

在表中添加、删除或修改列
添加列

ALTER TABLE table_name ADD column_name datatype

删除列

ALTER TABLE table_name DROP COLUMN column_name

更改表中列的数据类型

ALTER TABLE table_name MODIFY COLUMN column_name datatype

例子：persons表如下

|P_Id |LastName |FirstName| Address City|
|-|-|-|-|
|1 |Hansen |Ola |Timoteivn 10 |Sandnes|
|2 |Svendson |Tove |Borgvn 23 |Sandnes|
|3 |Pettersen |Kari |Storgt 20 |Stavanger|
添加一个名为DateOfBirth的列

ALTER TABLE Persons ADD DateOfBirth date

改变DateOfBirth的数据类型,year类型可以存2位或4位的年份

ALTER TABLE Persons ALTER COLUMN DateOfBirth year

删除DateOfBirth

ALTER TABLE Persons DROP COLUMN DateOfBirth

五. AUTO INCREMENT

会在新记录插入表中时生成一个唯一的数字,一般用于自动创建主键的值

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

开始值是1，每条记录增加1，如果需要以其他值开始，可以使用如下语句

ALTER TABLE Persons AUTO_INCREMENT=100

六. 视图

视图就像一个真实的表，可以对视图进行 SQL 函数、WHERE 以及 JOIN 语句，呈现数据等
视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据
CREATE VIEW

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

例子：
视图 "Current Product List" 会从 "Products" 表列出所有正在使用的产品（未停产的产品）。这个视图使用下面的 SQL 创建：

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

我们可以像这样查询上面这个视图：

SELECT * FROM [Current Product List]

Northwind 样本数据库的另一个视图会选取 "Products" 表中所有单位价格高于平均单位价格的产品：

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

我们可以像这样查询上面这个视图：

SELECT * FROM [Products Above Average Price]

Northwind 样本数据库的另一个视图会计算在 1997 年每个种类的销售总数。请注意，这个视图会从另一个名为 "Product Sales for 1997" 的视图那里选取数据：

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

我们可以像这样查询上面这个视图：

SELECT * FROM [Category Sales For 1997]

我们也可以向查询添加条件。现在，我们仅仅需要查看 "Beverages" 类的销售总数：

SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'

更新视图CREATE OR REPLACE VIEW

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

例子：

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

撤销视图DROP VIEW

DROP VIEW view_name