SQL Server高级教程

1.SELECT TOP

SELECT TOP 子句用于规定要返回的记录的数目。

SELECT TOP 子句对于拥有数千条记录的大型表来说，是非常有用的。

--选取数据库前两行的country,alexa列并显示
SELECT TOP 2 country,alexa FROM websites;
--选取数据库前两行的所有列并显示
SELECT TOP 2 * FROM websites;
--选取数据库前60%行的所有列并显示
SELECT TOP 60 PERCENT * FROM websites;
--选取数据库前60%行的country,alexa列并显示
SELECT TOP 60 PERCENT country FROM websites;

2.LIKE 操作符

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

--选择所有列，限定条件为name那一列字母 "G" 开始的一行内容
SELECT * FROM websites WHERE name LIKE 'G%';
--选择alexa那一列，限定条件为country那一列字母D结尾的内容
SELECT alexa FROM websites WHERE country LIKE '%D';
--选取name包含模式"oo"的所有客户
SELECT * FROM websites WHERE name LIKE '%oo%';
--选取name不包含模式"oo"的所有客户
SELECT * FROM websites WHERE name NOT LIKE '%oo%';

3.通配符

--使用SQL%通配符
SELECT * FROM websites WHERE name LIKE 'G%';
--使用SQL_通配符
SELECT * FROM websites WHERE name LIKE '_oogle'; 
--[charlist] 通配符

4.IN BETWEEN 以及别名

--选取 name 为 "Google" 或 "菜鸟教程" 的所有网站
SELECT * FROM Websites
WHERE name IN ('Google','菜鸟教程'); 

--选取 alexa 介于 1 和 20 之间的所有网站
SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20; 

--选择表websites并显示一列的别名
SELECT name AS a, country AS b FROM websites;

--将其中的三行合成一行，并设置行名为site_info
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;

5.连接

1）JOIN或者INNER JORN

--选择websites id与access_log site_id相同的行，显示的行数据包括SELECT内容，递增排序
SELECT websites.id,websites.name,access_log.count,access_log.date From websites 
INNER JOIN access_log ON websites.id=access_log.site_id ORDER BY id; 

不同的 SQL JOIN

在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型：

• INNER JOIN：如果表中有至少一个匹配，则返回行

• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

• FULL JOIN：只要其中一个表中存在匹配，则返回行

 2)LEFT JOIN 

--websites为左表，access_log为右表，从左表（Websites）返回所有的行，
--即使右表（access_log）中没有匹配。也会显示右表的内容，显示为NULL
SELECT Websites.id, Websites.name, access_log.count, access_log.date 
FROM Websites 
LEFT JOIN access_log 
ON Websites.id=access_log.site_id 
ORDER BY id DESC;  

3）RIGHT JOIN或者RIGHT OUTER JOIN

--把 Websites 作为左表，access_log 作为右表
--从右表（access_log）返回所有的行，即使左表（Websites）中没有匹配。左表没有匹配的为NULL
SELECT websites.name, access_log.count, access_log.date
FROM websites
RIGHT JOIN access_log
ON access_log.site_id=websites.id
ORDER BY access_log.count DESC;

4）FULL JOIN或者FULL OUTER JOIN

--先看LEFT JOIN再看RIGHT JOIN
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
FULL OUTER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC; 

A inner join B 取交集。

A left join B 取 A 全部，B 没有对应的值为 null。

A right join B 取 B 全部 A 没有对应的值为 null。

A full outer join B 取并集，彼此没有对应的值为 null。

5）UNION

注意：

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

--选取不同的country（只有不同的值）
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country; 

--选取所有的country（包括重复的值）
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country; 

--从"Websites"和"apps"表中选取所有的中国(CN)的数据（有重复的值）
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country; 

6.SELECT INTO复制表数据到新表

从一个表复制数据，然后把数据插入到另一个新表中。

--创建 Websites 的备份复件：
SELECT *
INTO WebsitesBackup2016
FROM Websites;

--只复制一些列插入到新表中：
SELECT name, url
INTO WebsitesBackup2016
FROM Websites;

--只复制中国的网站插入到新表中：
SELECT *
INTO WebsitesBackup2016
FROM Websites
WHERE country='CN';

--复制多个表中的数据插入到新表中：
SELECT Websites.name, access_log.count, access_log.date
INTO WebsitesBackup2016
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id;

--提示：SELECT INTO 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可：
SELECT *
INTO newtable
FROM table1
WHERE 1=0;

7.INSERT INTO SELECT复制表数据到已存在的表

select into from 和 insert into select 都是用来复制表

两者的主要区别为： select into from 要求目标表不存在，因为在插入时会自动创建；insert into select from 要求目标表存在。

--将apps的列app_name, country数据复制到Websites的列
INSERT INTO Websites (name,country) 
SELECT app_name, country FROM apps;

--只复制id为1的列
INSERT INTO Websites (name,country) 
SELECT app_name, country FROM apps
WHERE id=1;

8.CREATE TABLE创建表及添加约束Constraints

--创建表
CREATE TABLE Persons
(
	PersonID int,
	LastName varchar(255),
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255)
);

在 SQL 中，我们有如下约束：

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

9.约束详解

1）NOT NULL非空

--创建表时设定
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

--更改表WebsitesBackup2016中的列name的属性可为空（SQL Server写法）
ALTER TABLE WebsitesBackup2016
ALTER COLUMN name char(20) NULL;

--My SQL写法
ALTER TABLE Persons
MODIFY Age int NOT NULL;

2）UNIQUE 约束

数据唯一性的保证，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

--定义单个列的UNIQUE约束
CREATE TABLE Persons
(
	P_Id int NOT NULL UNIQUE,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255)
)
--命名 UNIQUE 约束，并定义多个列的 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)
)
--为表的P_Id列创建UNIQUE约束
ALTER TABLE Persons
ADD UNIQUE (P_Id)
--命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
--根据别名移除约束
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

3)PRIMARY KEY 约束

一行一个主键（学号)：不能有重复的值，一个值确定一行数据
两行一个主键：两行数据组合确定一行数据，假设列one有数据2，2；列two有数据A，B；则2，A和2，B分别确定一行

--设置单行主键
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
--命名PRIMARY KEY约束，并定义多个列的PRIMARY KEY约束
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)
)
--添加主键P_Id
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
--定义多列的主键约束，并命名为pk_PersonID
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
--撤销PRIMARY KEY 约束
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID