SQLServer-Day6

UNIQUE 约束

UNIQUE约束唯一表示数据库中的每条记录

UNIQUE和PRIMARY KEY 约束均为列或列集合提供了唯一性的保证

PRIMARY KEY拥有自动定义的UNIQUE约束

**Tips：**每个表可以有多个UNIQUE约束，但是每个表只能有一个PRIMARY KEY约束

SQL UNIQUE Constraint on CREATE TABLE

例1：下面的SQL在“Persons”表创建时在“Id_P”列创建UNIQUE约束

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

SQL Server / Oracle / MS Access:

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

如果需要命名UNIQUE约束，以及为多个列定义UNIQUE约束，需要使用下面的SQL语句。

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

SQL UNIQUE Constraint on ALTER TABLE

当表已被创建时，如需在“Id_P”列创建UNIQUE约束是，可以这样：

1 2	ALTER TABLE Persons ADD UNIQUE(Id_P)

如需要命名UNIQUE约束，并定义多个列的UNIQUE约束，这样：

1 2	ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P, LastName)

撤销UNIQUE约束

MySQL:

1 2	ALTER TABLE Persons DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access

1 2	ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

PRIMARY KEY

PRIMARY KEY约束唯一标识数据库中的每条记录

主键必须包含唯一的值

主键列不能包含NULL值

每个表都应该有一个主键，并且每个表只能有一个主键

用法同上，不赘述了

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

例：在创建”Orders”表时为“Id_P”列创建FOREIGN KEY

CREATE TABLE Persons
(
Id_O int NOT NULL, 
OrderNo int NOT NULL, 
Id_P int, 
PRIMARY KEY (Id_O), 
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY, 
OrderNo int NOT NULL, 
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)

如果需要命名FOREIGN KEY约束，以及为多个列定义FOREIGN KEY约束，需要使用以下SQL语法：

CREATE TABLE Orders
(
Id_O int NOT NULL, 
OrderNo int NOT NULL, 
Id_P int, 
PRIMARY KEY (Id_O), 
CONSTRAINT fk_PreOrders FOREIGN KEY (Id_P) 
REFERENCE Person(Id_P)  
)

在ALTER TABLE的时候，与之前的样例相似，不赘述了。

CHECK 约束

CHECK约束用于限制列中的值的范围

如果对单个列定义CHECK约束，那么该列只允许特定的值

如果对一个表定义CHECK约束，那么此约束会在特定的列中对值进行限制。

SQL CHECK Constraint on CREATE TABLE

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)
)

DEFAULT 约束

DEFAULT约束用于向列中插入默认值

如果没有规定其他的值，那么会将默认值添加到所有的新纪录

SQL DEFAULT Constraint on CREATE TABLE

下面的 SQL 在 “Persons” 表创建时为 “City” 列创建 DEFAULT 约束：

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

MySQL:

1 2	ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'

SQL Server / Oracle / MS Access：

1 2	ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'SANDNES'

撤销DEFAULT约束

MySQL：

1 2	ALTER TABLE Persons ALTER City DROP DEFAULT

SQL Server / Oracle / MS Access：

1 2	ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT

CREATE INDEX 语句

用于在表中创建索引

在不读取整个表的情况下，索引使数据库应用程序可以发更快地查找数据

索引:

我们可以在表中创建索引，以便更加快速高效地查询数据

用户无法看到索引，它们只能被用来加速搜索/查询

**Tips：**更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

SQL CREATE INDEX语法

CREATE INDEX index_name

ON table_name (column_name)

Tips：“column_name”规定需要索引的列

SQL CREATE UNIQUE INDEX语法

在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX index_name

ON table_name(column_name)

例：创建一个简单的索引，名为“PersonIndex”，在Person表的LastName列：

1 2	CREATE INDEX PersonIndex ON Person (LastName)

如果希望以降序索引某个列中的值，可以在列名称之后添加保留字DECS

1 2	CREATE INDEX PersonIndex ON Person (LastName DESC)

如果希望索引不止一个列，可以在括号中列出这些列的名称，用逗号隔开：

1 2	CREATE INDEX PersonInde, ON Person(LastName, FirstName)

182. 查找重复的电子邮箱

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

根据以上输入，你的查询应返回以下结果：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

我写的：

1
2
3

SELECT DISTINCT A.Email 
FROM Person AS A, Person AS B
WHERE A.Id <> B.Id AND A.Email = B.Email

参考别人的：

方法一：使用GROUP BY和临时表

SECECT Email FROM 
(
SELECT Email, COUNT(Email) as num 
FROM Person
GROUP BY Email
) as statistic
WHERE num > 1

方法二：使用GROUP BY和HAVING条件

SELECT Email
FROM Person
GROUP BY Email 
HAVING COUNT (Email) > 1

176. 第二高的薪水

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

方法一：先去掉最高的，然后查找剩余中最高的，同时解决了如果在表中找不到第二高的薪水的情况。

SELECT max(Salary) AS SecondHighestSalary 
FROM Employee 
WHERE Salary <> 
(
    SELECT max(Salary) 
    FROM Employee
)

方法二：按不同的薪资降序排序，然后使用LIMIT 子句获得的二高的薪资

SELECT 
    (SELECT DISTINCT Salary 
    FROM Employee
    ORDER BY Salary DESC
    LIMIT 1 OFFSET 1) AS SecondHighestSalary；

方法二：使用IFNULL和LIMIT子句

解决NULL问题的另一个方法就是使用IFNUL函数

SELECT IFNULL(
    (SELECT DISTINCT Salary 
     FROM Person 
     ORDER BY Salary DESC
     LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary

620. 有趣的电影

某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。

作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数的影片，结果请按等级 rating 排列。

例如，下表 cinema:

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   1     | War       |   great 3D   |   8.9     |
|   2     | Science   |   fiction    |   8.5     |
|   3     | irish     |   boring     |   6.2     |
|   4     | Ice song  |   Fantacy    |   8.6     |
|   5     | House card|   Interesting|   9.1     |
+---------+-----------+--------------+-----------+

对于上面的例子，则正确的输出是为：

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

Code：

SELECT *
FROM cinema 
WHERE mod(id, 2) = 1 AND description != 'boring'
ORDER BY rating DESC