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
2	Svendson	Tove	Borgvn 23
3	Pettersen	Kari	Storgt 20
添加一个名为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