SQLSever基本命令使用复习

  • Z时代
  • 2024-01-10
  • 分类：综合

database

因为数据库原理的专业老师要在五一假期后进行考试，所以尝试写一个笔记，便于复习和分享。

这篇文章介绍了教材上第三章全部内容。

SQL Sever 基本命令使用复习

目录

  • SQL Sever 基本命令使用复习
    • 前言
    • 虚拟机的安装
    • 命令和操作
      • 命令语法基础
      • 大小写敏感
        • SQL 关键字和函数名
        • 列名和索引名
        • 别名
          • 太长不看？
      • 空格
    • 数据库的连接
      • SSMS 的连接
      • 命令行的连接
    • 数据库操作
      • 创建
      • 删除
    • 约束
      • 完整性约束
      • 常用约束
        • NOT NULL
        • UNIQUE
        • PRIMARY KEY
        • FOREIGN KEY
        • DEFAULT
        • CHECK
    • 表操作
      • 数据类型
      • 创建表
      • 删除表
      • 修改表中的列
    • 查询
      • 基础语法
        • AS
        • DISTINCT
        • IS NULL
        • GROUP BY
          • HAVING
        • ORDERED BY
      • 常用函数
      • WHERE
        • 运算符概览
        • != 和 <>
        • BETWEEN ... AND ...
        • IN
        • LIKE
          • 通配符
          • 匹配模式
      • 连接
      • INNER JOIN 内连接
      • LEFT JOIN 左连接
      • RIGHT JOIN 右连接
        • 小总结
      • FULL OUTER JOIN 全连接
    • 子查询
      • 普通子查询
        • ANY
        • ALL
        • IN
      • 相关子查询
    • 其他类型的查询
      • UNION 集合运算查询
        • UNION
        • UNION ALL
        • 小总结
      • SELECT INTO 结果入表
    • 数据操纵
      • INSERT INTO 添加数据
      • UPDATE 修改数据
      • DELETE 删除数据
    • 视图
      • 视图的意义
      • CREATE VIEW 创建视图
      • ALTER VIEW 修改视图
      • DROP VIEW 删除视图
      • 查询视图
      • 操纵/更新视图
    • 索引
      • 索引的分类
        • 聚集索引
        • 非聚集索引
          • 聚集和非聚集的区别
        • 唯一索引
        • 视图索引
        • 全文索引
        • XML 索引
      • 索引的操作
        • 创建索引
        • 修改索引
        • 删除索引
    • 大总结
      • 你能用 ALTER 干啥？
      • 其他
    • 后记
    • 参考

前言

因为数据库原理的专业老师要在五一假期后进行考试，所以尝试写一个笔记，便于复习和分享。

操作环境：Hyper-v 虚拟机 运行 Windows Sever 2012 R2 + SQL Sever Express 2012 SP2

使用教材：数据库原理及应用教程 第4版 微课版 主编 陈志泊

虚拟机的安装

如果你是纯理论派的话，不装也不是不行，可以跳到下一节。

Express 是 SQL Sever 的精简版，可以免费使用。

SQL Server Express 2005（以下简称 SQLExpress） 是由微软公司开发的 SQL Server 2005（以下简称 SQL2005）的缩减版，这个版本是免费的，它继承了 SQL Server 2005 的多数功能与特性，如：安全性设置、自定义函数和过程、Transact-SQL、SQL、CLR 等，还免费提供了和它配套的管理软件 SQL Server Management Studio Express。

SQLExpress 有几个主要的限制：

  1. 仅允许本地连接。

  2. 数据库文件的最大尺寸为4GB，此限制只对数据文件（后缀名为 mdf），日志文件（后缀名为 ldf）不受此限。

  3. 只使用一个CPU来运算，不能充分利用多CPU服务器的性能。

  4. 可使用的记忆体量最高只有1GB。

  5. 没有 SQL Agent，若要做排程服务必须自己编写。

因此它是 SQL Server 产品系列中面对低端的产品，是面对桌面型应用，或者小型的内部网络应用的。

笔者在此选择安装 Microsoft® SQL Server® 2012 Service Pack 2 (SP2) Express 64位 with Tools （安装包名：SQLEXPRWT_x64_CHS.exe）的版本。Express即使全部安装也不会大到哪里去，不像原版的 SQL Sever 有4GB的安装包大小……作为练习，足够用了。点击上面的文字就可以跳转到下载链接。

笔者遇到的困难只有，Windows Sever 2012 R2 不自带 .NET 3.5 环境，所以需要在“添加角色和功能”界面自行添加，之后才能顺利安装 SQL Sever Express。

尽管考试内容只有命令，但我仍然推荐不取消安装GUI（用户交互界面，也就是SQL Server Management Studio，简称 SSMS），以防止在输入错误命令的时候无法纠错或重建数据库。

安装教程很多，请自行搜索，不再赘述。

命令和操作

命令语法基础

实在看不懂的话，或者你自己觉得这没什么必要，那随便看看就行，到时候不知道再回来看嘛。

在描述命令行参数的时候，对其格式有些约定俗成的写法，各系统之间也有差别。一般采用的格式如下：

命令 <必选参数1|必选参数2> [-option {必选参数1|必选参数2|必选参数3}] [可选参数…] {(默认参数)|参数|参数}

命令格式中常用的几个符号含义如下：

  • 尖括号 < >：必选参数，实际使用时应将其替换为所需要的参数。

  • 大括号 { }：必选参数，内部使用，包含此处允许使用的参数。

  • 方括号 [ ]：可选参数，在命令中根据需要加以取舍。

  • 小括号 ( )：指明参数的默认值，只用于 { } 中。

  • 竖线 |：用于分隔多个互斥参数，含义为“或”，使用时只能选择一个。

  • 省略号 …：任意多个参数。

大括号和尖括号的区别为：大括号中只能选择所列举的必选参数（或之一），尖括号中却需要根据实际替换必选参数

示例一：

  • 命令语法：git help <name>

  • 实际使用：git help config 或 git help branch 或 ... （name被替换为实际的内容）

示例二：

  • 命令语法：git stash {apply | pop}

  • 实际使用：git stash apply 或 git stash pop （只能在必选参数apply或pop中选一个）

大小写敏感

这一段主要是让你写命令写的更随意一点用的。

SQL 关键字和函数名

SQL 的关键字和函数名都不区分大小写，例如，下面这些语句都是等价的：

SELECT NOW();
select now();
sElEcT nOw();

列名和索引名

在 MySQL 里，列名和索引名都不区分大小写。例如，下面这些语句都是等价的：

SELECT name FROM student;
SELECT NAME FROM student;
SELECT nAmE FROM student;

别名

默认情况下，表的别名要区分大小写。SQL 语句中可以使用任意的大小写（大写、小写或大小写混用）来指定一个别名。如果需要在同一条语句里多次用到同一个别名，则必须让它们的大小写保持一致。

Linux下区分大小写的情况：

项目

Linux

数据库名

是

表名

是

表别名

是

列名

否

列别名

否

变量名

是

在Windows下，都不区分大小写。

太长不看？

各种东西的名字建议区分大小写（数据库名，列名，表名等）；SQL 关键字和函数不用区分（create、select、from等）。在 Windows 下，则完全看你心情。

空格

SQL 语句忽略多余的空格。因此，你有足够的自由空间去整理你的代码结构和风格。

数据库的连接

SSMS 的连接

使用GUI（也就是SSMS），在启动时可能遇到服务器名称为空，无法链接的问题。这个时候，可以点击下拉框下的“查看更多”尝试查找，或者运行 services.msc，查找所有显示名称如 SQL Server (数据库名称) 的服务。

比如，在我的机器上，有一个显示名称为 SQL Server (SQLEXPRESS)的服务，实际服务名称为 MSSQL$SQLEXPRESS，那么这个SQL服务器实例的名称就为 SQLEXPRESS，可以在服务器名称的框内输入 ./SQLEXPRESS来登录。

之后你理应在对象资源管理器中看到你刚刚登录的数据库。

点击“新建查询”，即可进行在 SSMS 下的命令输入、编辑、调试和运行。

命令行的连接

在 cmd 或 powershell 中输入 sqlcmd -S localhost -U sa -P 123456 来进行命令行的连接。

以下为 sqlcmd 工具的命令语法，仅供参考即可。

PS C:UsersAdministrator> sqlcmd -?
Microsoft (R) SQL Server 命令行工具
版本 11.0.2100.60 NT x64
版权所有 (c) 2012 Microsoft。保留所有权利。
用法: Sqlcmd            [-U 登录 ID]          [-P 密码]
[-S 服务器]            [-H 主机名]          [-E 可信连接]
[-N 加密连接][-C 信任服务器证书]
[-d 使用数据库名称] [-l 登录超时值]     [-t 查询超时值]
[-h 标题]           [-s 列分隔符]      [-w 屏幕宽度]
[-a 数据包大小]        [-e 回显输入]        [-I 允许带引号的标识符]
[-c 命令结束]            [-L[c] 列出服务器[清除输出]]
[-q "命令行查询"]   [-Q "命令行查询" 并退出]
[-m 错误级别]        [-V 严重级别]     [-W 删除尾随空格]
[-u unicode 输出]    [-r[0|1] 发送到 stderr 的消息]
[-i 输入文件]         [-o 输出文件]        [-z 新密码]
[-f <代码页> | i:<代码页>[,o:<代码页>]] [-Z 新建密码并退出]
[-k[1|2] 删除[替换]控制字符]
[-y 可变长度类型显示宽度]
[-Y 固定长度类型显示宽度]
[-p[1] 打印统计信息[冒号格式]]
[-R 使用客户端区域设置]
[-K 应用程序意向]
[-M 多子网故障转移]
[-b 出错时中止批处理]
[-v 变量 = "值"...]  [-A 专用管理连接]
[-X[1] 禁用命令、启动脚本、环境变量[并退出]]
[-x 禁用变量替换]
[-? 显示语法摘要]

可知，-S 参数用于指定连接 localhost 本地服务器，使用 sa 用户登录，密码为 123456。这是一个内置的管理员账户。

若命令行窗口左边出现 1> 字样，则代表连接成功，可以开始输入指令。注意，用分号结束语句的惯例并没有效果，需要用 go 命令进行执行。

标识符

效果

GO

执行最后一个 GO 命令之后输入的所有语句

RESET

清除已输入的所有语句

ED

调用编辑器

!! Command

执行操作系统命令

QUIT 或 EXIT

直接退出 sqlcmd

CTRL+C

不退出 sqlcmd 而结束查询

仅当 命令终止符 GO（默认）、RESET、ED、!!、EXIT、QUIT 和 CTRL+C 出现在一行的开始（紧跟提示符）时才可以被识别。sqlcmd 忽视同一行中这些关键字后输入的任何内容。

之后我将假设我们在命令行交互的方式下进行操作。若直接在交互窗口下编辑复杂命令显得有些困难，可以在其他地方编辑好后直接复制过来，或选择上面的在 SSMS 中调试指令。

数据库操作

创建

使用 CREATE DATABASE 语句创建数据库。

语法：

CREATE DATABASE <数据库名称>;

在命令行交互窗口中，你输入的应该是这样的：

1> create database my_db;
2> go

然后命令行窗口重新显示 1> ，操作成功完成。

其他的参数可能不那么重要，详情可参考教材 P77。

随后，输入 use 数据库名 进入数据库操作。

删除

使用 DROP DATABASE 语句。

DROP DATABASE <数据库名称>;

不说你也应该知道——谨慎使用。

约束

约束的前置知识是数据表的创建，如果对于表创建的规则不熟悉，建议先跳到创建表章节，然后再回来继续。

SQL约束用于指定表中数据的规则。

约束有列级和表级之分，列级约束作用于单一的列，而表级约束作用于整张数据表。

完整性约束

完整性约束用于保证关系型数据库中数据的精确性和一致性。对于关系型数据库来说，数据完整性由参照完整性（referential integrity，RI）来保证。

有很多种约束可以起到参照完整性的作用，这些约束包括主键约束（Primary Key）、外键约束（Foreign Key）、唯一性约束（Unique Constraint）以及下面提到的其他约束。

不用太纠结于这个概念。

常用约束

下面是 SQL 中常用的约束：

约束名

解释

NOT NULL 约束

保证列中数据不能有 NULL 值

DEFAULT 约束

提供该列数据未指定时所采用的默认值

UNIQUE 约束

保证列中的所有数据各不相同

主键约束

唯一标识数据表中的行/记录

外键约束

唯一标识其他表中的一条行/记录

CHECK 约束

此约束保证列中的所有值满足某一条件

索引

用于在数据库中快速创建或检索数据

如果想要指定约束名称，可以在原先列定义约束的位置加上 CONSTRAINT <约束名>，替换掉原来的约束名称。这是可选的。加上约束名的好处之一是，以后对这个约束进行修改或删除可能会更加方便。

NOT NULL

在默认的情况下，表的列接受 NULL 值，NOT NULL 约束强制列不接受 NULL 值。该约束使字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

UNIQUE

UNIQUE 约束唯一标识数据库表中的每条记录，UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证，PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

PRIMARY KEY

PRIMARY KEY 约束唯一标识数据库表中的每条记录，主键必须包含唯一的非NULL值。

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

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 约束用于预防破坏表之间连接的行为，FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

DEFAULT

DEFAULT 约束用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新记录。在定义时，在 default 后写入默认值即可。

CHECK

CHECK 约束用于限制列中的值的范围，可以提高程序的 鲁棒性。

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

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

例子：

  • 列约束：

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

  • 表约束

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    Name varchar(255) NOT NULL,
    City varchar(255),
    CONSTRAINT chk_Person CHECK (P_Id>0 AND City="Sandnes")
)

表操作

数据类型

最常用的数据类型：

数据类型

描述

integer(size)
int(size)
smallint(size)
tinyint(size)

仅容纳整数。在括号内规定数字的最大位数。

decimal(size,d)
numeric(size,d)

容纳带有小数的数字。
"size" 规定数字的最大位数。"d" 规定小数点右侧的最大位数。

char(size)

容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。
在括号中规定字符串的长度。

varchar(size)

容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。
在括号中规定字符串的最大长度。

date(yyyymmdd)

容纳日期。

文本类（char和text），在类型名前面加n，就是相应的储存Unicode字符的数据类型。

无需全部记住，只需认识即可。点此可参考全部数据类型。

创建表

使用 CREATE TABLE 语句创建表。

语法：

CREATE TABLE 表名称
(
    <列名1> <数据类型> [DEFAULT] [{列约束}],
    <列名2> <数据类型> [DEFAULT] [{列约束}],
    ...,
    [表约束]
);

例子：

CREATE TABLE courses (
    course_id   INT         AUTO_INCREMENT,
    course_name VARCHAR(50) NOT NULL,
    start_date  DATE,
    PRIMARY KEY (employee_id , course_id)
);

关于约束的更多介绍，可以向上到约束章节回看。

表约束和列约束不同。即使是在设置主键上，实际意义也略有不同，不过不深究的话，两者有时功能相似，如何实现全看现实情况和你的需求，不用太过纠结。

删除表

使用 DROP TABLE 语句。语法：

DROP TABLE 表名

修改表中的列

修改表中的列，使用 ALTER TABLE 语句。

若要向表中添加列，请使用以下语法：

ALTER TABLE table_name         
ADD column_name datatype

若要删除表中的列，请使用以下语法：

ALTER TABLE table_name
DROP COLUMN column_name

若要更改表中列的数据类型，请使用以下语法：

ALTER TABLE table_name
ALTER COLUMN column_name datatype

SQL 语言作为一种 DDL 语言，可见命令的语法结构还是非常直接的。

大体流程如下：

  1. 首先确定要修改的表，ALTER TABLE 要更改的数据表名

  2. 然后选择以下三个可能的列操作：

    • 若是要增加列，则使用 ADD 列名 数据类型 关键字（可以以逗号分隔输入多行）

    • 若是要删除列，则使用 DROP COLUMN 要删除的数据表名 关键字

    • 若是更改表，则输入 ALTER 要更改的列名 新名字 新类型 命令。

查询

假设有此表：

u_id

u_name

u_age

u_sex

1

王一

18

男

2

润二

19

女

3

张三

21

女

4

李四

21

男

5

赵五

22

女

6

雷六六

19

男

7

期肆一

20

男

s_id

amount

u_id

0

7300

2

1

7100

1

2

6400

4

3

1700

3

4

5000

5

5

6000

5

6

2900

1

这是公司职员的两张表，上面的是详细信息，下面的是发放工资的记录表。

这两张数据表将会作为以下语法和连接的示例。

基础语法

SELECT <列名>
FROM <表名>
[WHERE <查询条件>]

可以用 * 代表全部列。

AS

使用AS给查询结果取别名(也可省略as)：

SELECT
u_name as "myName",
u_age as "myAge"
FROM staff;

注：如果需要小写字母或别名含有空格则需要加上单引号，否则会被解析成大写字母。

结果：

1> SELECT
2> u_name as "myName",
3> u_age as "myAge"
4> FROM staff;
5> go
myName myAge
------ -----------
王一              18
润二              19
张三              21
李四              21
赵五              22
雷六六             19
期肆一             20
(7 行受影响)

DISTINCT

只输出有区别的元素。例：

SELECT DISTINCT u_age
FROM staff;

IS NULL

IS NULL 是在 Where 分句中判空的唯一方法，不能使用如 XX=NULL 形式的查找条件。

GROUP BY

现在，我们希望查找每个职员的被发放的总金额（总工资），可以使用 GROUP BY 语句对职员ID进行组合：

SELECT u_id, SUM(amount) FROM salary
GROUP BY u_id;

结果：

1> SELECT u_id, SUM(amount) FROM salary
2> GROUP BY u_id;
3> go
u_id
----------- -----------
          1        7100
          2        7300
          3        1700
          4        6400
          5       11000
          9        2900
         11        6300
(7 行受影响)

可以看到，工资被按组排序出来加和输出了。顺带一提，如果列没有被命名，那么查询出来的结果中，列的名字将会是空白的。

但若是没有 GROUP BY 呢？

1> SELECT u_id, SUM(amount) FROM salary;
2> go
消息 8120，级别 16，状态 1，服务器 SQLEXPRESS，第 1 行
选择列表中的列 "salary.u_id" 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

嗯，报错了。如果没有报错的话，输出的结果应该会是类似于这样的：

u_id

0

36400

1

36400

2

36400

3

36400

4

36400

5

36400

6

36400

这肯定不是我们所想要的结果。

HAVING

​HAVING​ 子句使你能够指定过滤条件，从而控制查询结果中哪些组可以出现在最终结果里面。

​WHERE ​子句对被选择的列施加条件，而 ​HAVING ​子句则对 ​GROUP BY​ 子句所产生的组施加条件。

书里没有 HAVING 子句的出现，在此不做详细介绍。

ORDERED BY

ORDER BY 语句用于对结果集进行排序。可以在后面指定 ASC 升序或 DESC 降序。默认升序。例：

SELECT * FROM staff ORDER BY u_age ASC

常用函数

函数名称

功能

AVG

求平均值

SUM

求和

MAX

求最大值

MIN

求最小值

COUNT

统计个数

用在 SELECT 后面，可以搭配 AS 使用。例：

SELECT * FROM staff
WHERE u_age = (
    SELECT MAX(u_age) FROM staff
);

使用 MAX 函数和嵌套查询来获取年龄最大的那条记录。

WHERE

运算符概览

下面的运算符可在 WHERE 子句中使用：

操作符

描述

=、<>、!=、>、<、>=、<=

比较大小

AND、OR、NOT

多条件

BETWEEN ... AND ...

设定范围

IN

设定集合

LIKE

字符模糊查找

IS NULL

为空

!= 和 <>

在某些版本的 SQL SEVER 中，有 =! 运算符，与 <> 相同，都表示两个值不相等。

BETWEEN ... AND ...

操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

SELECT * FROM staff
WHERE u_name
BETWEEN "润二" AND "赵五";

结果：

1> SELECT * FROM staff
2> WHERE u_name
3> BETWEEN "润二" AND "赵五";
4> go
u_id        u_name u_age       u_sex
----------- ------ ----------- -----
          1 王一             18   男
          2 润二             19   女
          3 张三             21   女
          5 赵五             22   女
(4 行受影响)

可见，当操作符被输入文本时，按照音序来排列，并返回相应结果。

注意：在某些 DBMS 数据库管理系统中，这个操作符不包含等号或只包含起始的等号，在 SQL Sever 中，这个操作符包含两端的等号。

IN

语法：

IN (value1,value2,...)

例子：

1> SELECT u_name, u_age FROM staff
2> WHERE u_age IN (18, 21);
3> go
u_name u_age
------ -----------
王一              18
张三              21
李四              21
(3 行受影响)

LIKE

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

通配符

在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。

SQL 通配符必须与 LIKE 运算符一起使用。

在 SQL 中，可使用以下通配符：

通配符

描述

%

替代一个或多个字符

_

仅替代一个字符

[charlist]

字符列中的任何单一字符

[^charlist] 或 [!charlist]

不在字符列中的任何单一字符

匹配模式

易得，在 u_name 字段中查询，

如 %一 的匹配模式，能够查找到表中的 “王一” 和 “期肆一” 条目。

如 _一 的匹配模式，能够查找到表中的 “王一” 条目。

连接

我们经常使用4种连接类型：

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

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

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

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

我们将继续使用上面提到的两个数据表作为例子。

INNER JOIN 内连接

INNER JOIN 关键字在表中存在至少一个匹配时返回行。

这是最常见的连接方式。举例：

SELECT u.u_id, u.u_name, s.amount 
FROM staff as u
INNER JOIN salary as s
ON u.u_id = s.u_id
ORDER BY u.u_id;

注释：u 即 user，s 即 salary。INNER JOIN 和 JOIN 关键字是相同的。

as 关键字指定别名，ON 子句指定连接条件，最后结果使用 ORDER BY 排序。

结果：

1> SELECT u.u_id, u.u_name, s.amount
2> FROM staff as u
3> INNER JOIN salary as s
4> ON u.u_id = s.u_id
5> ORDER BY u.u_id;
6> go
u_id        u_name amount
----------- ------ -----------
          1 王一          7100
          2 润二          7300
          3 张三          1700
          4 李四          6400
          5 赵五          5000
          5 赵五          6000
(6 行受影响)

注释：INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "staff" 表中的行在 "salary" 中没有匹配，则不会列出这些行。

有时，我们可以省略 INNER JOIN 连接符。下面的语句和上面是等效的。

SELECT u.u_id, u.u_name, s.amount 
FROM staff as u, salary as s
WHERE u.u_id = s.u_id
ORDER BY u.u_id;

通过在 FROM 子句中指定多个数据表，SQL 会将他们通过输入的条件（在这里是 WHERE）自动进行内连接。省略连接符后，就不能使用 ON 子句了。

LEFT JOIN 左连接

注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

以下实例中我们把 salary 作为左表，staff 作为右表，返回所有员工的工资和姓名。

SELECT s.u_id, s.amount, u.u_name
FROM salary as s
LEFT JOIN staff as u
ON u.u_id = s.u_id
ORDER BY s.u_id;

结果：

1> SELECT s.u_id, s.amount, u.u_name
2> FROM salary as s
3> LEFT JOIN staff as u
4> ON u.u_id = s.u_id
5> ORDER BY s.u_id;
6> go
u_id        amount      u_name
----------- ----------- ------
          1        7100 王一
          2        7300 润二
          3        1700 张三
          4        6400 李四
          5        5000 赵五
          5        6000 赵五
          9        2900 NULL
         11        6300 NULL
(8 行受影响)

可见，左连接匹配，左表中即使有右表没有的数据，也会返回左表的记录，右表返回空。

RIGHT JOIN 右连接

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

右链接是左连接查询的反向操作。

以下实例中我们把 salary 作为左表，staff 作为右表，返回所有员工的工资和姓名。

SELECT s.u_id, s.amount, u.u_name
FROM salary as s
RIGHT JOIN staff as u
ON u.u_id = s.u_id
ORDER BY s.u_id;

结果：

1> SELECT s.u_id, s.amount, u.u_name
2> FROM salary as s
3> RIGHT JOIN staff as u
4> ON u.u_id = s.u_id
5> ORDER BY s.u_id;
6> go
u_id        amount      u_name
----------- ----------- ------
       NULL        NULL 雷六六
       NULL        NULL 期肆一
          1        7100 王一
          2        7300 润二
          3        1700 张三
          4        6400 李四
          5        5000 赵五
          5        6000 赵五
(8 行受影响)

小总结

可见，LEFT JOIN 和 RIGHT JOIN 仅仅是连接符换了一下而已，从代码上看，剩下的什么都没变。FROM 后是左表，XX JOIN 后面是右表，左右表连接切换的只是哪个表的数据要被完全显示，哪个表的数据如果没有就填空。

左表连接就完全显示左表，右表连接就完全显示右表。

FULL OUTER JOIN 全连接

我似乎没有看到可以省略 OUTER。如果懒得记的话，就把除了 INNER JOIN 的连接符中间全写上 OUTER 得了。

同样的例子：

SELECT s.u_id, s.amount, u.u_name
FROM salary as s
FULL OUTER JOIN staff as u
ON u.u_id = s.u_id
ORDER BY s.u_id;

结果：

1> SELECT s.u_id, s.amount, u.u_name
2> FROM salary as s
3> FULL OUTER JOIN staff as u
4> ON u.u_id = s.u_id
5> ORDER BY s.u_id;
6> go
u_id        amount      u_name
----------- ----------- ------
       NULL        NULL 雷六六
       NULL        NULL 期肆一
          1        7100 王一
          2        7300 润二
          3        1700 张三
          4        6400 李四
          5        5000 赵五
          5        6000 赵五
          9        2900 NULL
         11        6300 NULL
(10 行受影响)

一目了然。

子查询

就是嵌套查询。上面已经稍微的演示过嵌套查询的实例了。

有递归思想的话，会很快熟悉这一节的内容，因此介绍会简略一些。

普通子查询

不介绍返回一个值的嵌套子查询，直接开始说明返回一组数据的子查询。

ANY

ANY 意为一组数据中的任意一个。

查询单词收入在3000以上的人的姓名，例子：

SELECT DISTINCT u_name
FROM staff
WHERE u_id = ANY (
    SELECT u_id
    FROM salary
    WHERE amount > 3000
);

结果：

1> SELECT DISTINCT u_name
2> FROM staff
3> WHERE u_id = ANY (
4>     SELECT u_id
5>     FROM salary
6>     WHERE amount > 3000
7> );
8> go
u_name
------
李四
润二
王一
赵五
(4 行受影响)

ANY 的运算表如下：

条件

表示含义

x = ANY (…)

x列中的值必须与集合中的一个或多个值匹配。

x != ANY (…)

x列中的值不能与集合中的一个或多个值匹配。

x > ANY (…)

x列中的值必须大于集合中的最小值。

x < ANY (…)

x列中的值必须小于集合中的最大值。

x >= ANY (…)

x列中的值必须大于或等于集合中的最小值。

x <= ANY (…)

x列中的值必须小于或等于集合中的最大值。

ALL

ALL 和 ANY 是同一类的运算符。

条件

描述

c > ALL(…)

c列中的值必须大于集合中的最大值。

c >= ALL(…)

c列中的值必须大于或等于集合中的最大值。

c < ALL(…)

c列中的值必须小于集合中的最小值。

c <= ALL(…)

c列中的值必须小于集合中的最小值。

c <> ALL(…)

c列中的值不得等于集合中的任何值。

c = ALL(…)

c列中的值必须等于集合中的任何值。

大概理解一下，按照实际情况使用即可。ANY 以最低标准判断，ALL 以最高标准判断。

IN

IN 和 = ANY 等价。

NOT IN 和 <> ALL 等价。

相关子查询

子查询中引用父查询信息的查询就叫相关子查询。不用太纠结于这个概念，用的时候把表的名字标清楚就行。

其他类型的查询

UNION 集合运算查询

UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

为了演示，我们再新建一个 staff2 表，这是第二个部门的职员名单。

u_id

u_name

u_age

u_sex

1

刘五四

18

男

2

张建国

19

女

3

陈冠希

21

女

4

张三

21

男

列出两个部门所有不同的雇员名：

SELECT u_name FROM staff
UNION
SELECT u_name FROM staff2;

结果：

1> SELECT u_name FROM staff
2> UNION
3> SELECT u_name FROM staff2;
4> GO
u_name
------
陈冠希
雷六六
李四
刘五四
期肆一
润二
王一
张建国
张三
赵五
(10 行受影响)

注释：这个命令无法列出所有雇员。在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。

UNION ALL

UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。

同样是上面的例子，只不过这次我们使用 UNION ALL：

SELECT u_name FROM staff
UNION ALL
SELECT u_name FROM staff2

结果：

1> SELECT u_name FROM staff
2> UNION ALL
3> SELECT u_name FROM staff2
4> GO
u_name
------
王一
润二
张三
李四
赵五
雷六六
期肆一
刘五四
张建国
陈冠希
张三
(11 行受影响)

小总结

区分 JOIN 连接 和 UNION 集合，想象 JOIN 是以某种方式把两张表横向的连接在一起然后进行操作，而 UNION 是简单的把两个表水平方向上的“接上”，是一个简单的相加/并集操作。

SELECT INTO 结果入表

SELECT INTO 语句和普通的 SELECT 语句无异，只不过多加了一个 INTO 子句，命令的返回结果也不是显示在交互窗口中，而是新建一个表来储存查询返回的数据。

按照这个特性，我们可以用这个命令来复制/备份表。例子：

SELECT *
INTO staff_backup
FROM staff

同样，能够在 SELECT 语句中使用的子句也同样可以使用，如 WHERE、JOIN 等，在此不过多赘述。

数据操纵

INSERT INTO 添加数据

INSERT INTO 语句用于向数据表中插入新的行。

语法：

INSERT INTO 表名称 VALUES (值1, 值2,....)

若要指定想要插入数据的列：

INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....)

这使得我们可以向特定的列中插入数据，不必提供所有列的数据。

但若不指定列，那么 VALUES 应该与表中的字段一一对应，不应缺少数据项或输入类型与相应字段不匹配。记得把字符型的数据加上单引号哦。

例子：

INSERT INTO staff (u_id, u_name) VALUES (8, "临时工");

如上，staff 表中将会新建一个只有 u_id 和 u_name 字段不为空的记录。

UPDATE 修改数据

Update 语句用于修改表中的数据。

语法：

UPDATE 表名称 SET 列名称 = 新值 [, 列名称2 = 新值, ...]
WHERE 列名称 = 某值

有点抽象对吧？看例子，如果我们发现润二同学是个女装大佬，他的性别不是女，而是男的话，我们需要更新该人在数据表中的条目：

UPDATE staff SET u_sex = "男" WHERE u_name = "润二"

首先输入要更新的记录所在的表名，然后输入记录所在的列名，然后使用 WHERE 子句查找到该记录，并成功把该值修改。

DELETE 删除数据

DELETE 语句用于删除表中的行。

语法：

DELETE FROM 表名称 WHERE 列名称 = 值

用 FROM 确定表， WHERE 确定要删除的记录。

润二同学被发现是女装大佬以后，竟然开始被公司里年龄最大的赵五歧视，欺负。怎么能有如此不和谐的事情出现呢！为了维护世界的爱与和平，赵五被公司辞退了。为此，我们需要删除赵五的记录。如下：

DELETE FROM staff WHERE u_name = "赵五";

以后，如果有更多的人歧视润二同学，你也可以更改 WHERE 的条件，使其匹配更多记录，一并删除。

你真棒！作为 DBA，你又一次维护了宇宙中一块小小地方的爱与和平！请继续努力吧！

视图

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

注释：数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

个人理解，视图就是一张完全以其他真实表的一部分作为元素所组成的虚拟表。

视图的意义

  • 简化了操作，把经常使用的数据定义为视图，可以将复杂的SQL查询语句进行封装。

    如在实际工作中，不同的人员只关注与其相关的数据，而与他无关的数据，对他没有任何意义。根据这一情况，可以专门为其创建一个视图，定制用户数据，聚焦特定的数据。此后当他查询数据时，只需 select * from view_name; 就可以了。

  • 安全性，用户只能查询和修改能看到的数据。

    视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以不通过视图将基表中重要的字段信息给用户，可以保证数据的安全性。方便了权限管理，让用户对视图有权限而不是对底层表有权限进一步加强了安全性。

  • 逻辑上的独立性，屏蔽了真实表的结构带来的影响。

    视图的存在: 主要是为了对外提供数据支持(外部系统)；隐藏了基表字段(隐私)；保证了数据库的数据安全(保护数据库内部的数据结构)；可以灵活的控制对外的数据: 保证针对每个接口都有一个单独的数据支持，增强了用户友好性。

CREATE VIEW 创建视图

基础语法：

CREATE VIEW 视图名(列名1, 列名2, ...) AS
SELECT 列1, 列2.....
FROM 表名
WHERE 条件;

要创建视图，你要先选择你要从哪个表里抽调出哪个列哪些记录作为视图的组成元素。其中，视图的列名是可选的。本质上是在 AS 后面构建了一个查询语句。你依旧可以自由的像是使用正常的 SELECT 语句那样使用这个命令，而且不要忘记那个 AS 哦。

完整的视图创建命令还有一些诸如 WITH CHECK OPTION 之类的参数，不过我们先不研究这么深入，了解他的基本使用即可。

你看，一个简单的抽调出所有 staff 名字和年龄的视图只需要这么写：

CREATE VIEW staff_all(name, age) AS
(
    SELECT u_name, u_age FROM staff
    UNION
    SELECT u_name, u_age FROM staff2
)

新建的视图有两列，一列名为 name，一列名为 age。而其他的数据——这个视图的被提供者被设计成并不需要担心，也不应该操纵其他的数据项。

ALTER VIEW 修改视图

语法：

ALTER VIEW <视图名>[(视图列表)] AS
子查询

书上没写太多，不过这基本已经是把一个视图全部重新构建的等级了。可以考虑把 ALTER VIEW 命令看成把一个已经存在的视图替换成一个新视图的过程，之后的命令用于定义那个新视图。

DROP VIEW 删除视图

你懂的。

DROP VIEW <视图名>

查询视图

把视图当成一个普通的表一样写在 FROM 后面。本质上，视图是一系列查询的结果，而对视图的查询是一个系统借由视图自动生成的嵌套查询。

操纵/更新视图

使用同样的 INSERT、UPDATE、DELETE 语句，把视图当成表一样进行操作。

在实际操作中，需要注意的是，由于视图可能是多个表以某种规则联系在一起的，所以有一些队视图的操作并不能成功的进行——因为他们的原表有规则限制，不允许单独添加某个数据项。

索引

索引是一种特殊的查询表，可以被数据库搜索引擎用来加速数据的检索。一般索引使用 B+树、Hash索引等方式建立数据结构，但在这里，我们只需要知道有一个索引表的概念即可。

索引的分类

以下都是概念的介绍，只考虑 SQL 语句的话，下面的略读、大概理解或者直接跳过即可。视图索引、全文索引、XML索引 基本不会考到。

聚集索引

Clustered Index 聚集索引。在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。

非聚集索引

Non-clustered Index 非聚集索引。如果不是聚集索引，表中各行的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引有更快的数据访问速度。

聚集和非聚集的区别

对于两者概念分辨不清的，可以考虑看下面的参考：

（一）深入浅出理解索引结构

实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别：

其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。

我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。

我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。

进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。

唯一索引

唯一索引不允许两行具有相同的索引值。

如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此数据。

视图索引

顾名思义，对视图的索引。视图作为引用实表的虚表，每次都动态运算，开销很大。因此，对于视图建立索引减少性能和时间开销是必要的。

然而，若频繁更新基本表数据，则反复自动重建视图索引的开销可能大于视图索引带来的性能收益。请斟酌使用。

全文索引

全文索引可以提高数据的搜索速度。在 SQL Sever 2012 中，全文索引被允许创建在每个表最多一个，且只对于文本、二进制、XML 数据类型的列的条件下。

XML 索引

顾名思义，对 XML 进行索引。

索引的操作

顺便来学习一下英文吧

cluster

英 [ˈklʌstə(r)]　　美 [ˈklʌstər]

n. 群；簇；丛；串

vi. 群聚；丛生

vt. 使聚集；聚集在某人的周围

n. (Cluster)人名；(英)克拉斯特

创建索引

资料和教材上似乎有些出入，这里选用教材上的主要语法：

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX 索引名
ON 数据表名 (列名, ...);

若指定了 (NON) CLUSTERED，那么将创建一个（非）聚集索引。若指定 UNIQUE，则将创建一个唯一索引。

例子：

CREATE UNIQUE CLUSTERED INDEX index_staff
ON staff(u_id, u_name);

这个命令创建了一个唯一聚集索引，为 u_id 和 u_name 两列的复合索引。

修改索引

索引一般由 DBMS 自动维护，没有特殊目的的话，我真的不知道我干嘛要改它。

ALTER INDEX <索引名 或 ALL>
ON 表名
{ REBUILD | DISABLE | REORGANIZE | SET }

  1. REBUILD：重新生成索引。

  2. DISABLE：禁用索引。

  3. SET：设置索引的某些 FLAG。

  4. 剩下的我看不明白，书上也没说，一个例子都没给。

  5. 一个例子都没有，大概不会考吧。

删除索引

有两种语法可以删除索引：

DROP INDEX 表名.索引名

DROP INDEX 索引名 ON 表名

很简单，对吧？

大总结

你能用 ALTER 干啥？

  1. 修改表列。

  2. 修改视图。

  3. 修改索引。

可见，修改表列最有用。

其他

其实我也不太清楚要总结什么，就先放这里吧。

后记

感谢大家查看我使用几天假期做出来的劳动成果。

祝各位五一快乐，考试顺利。

写博客真的非常非常非常的耗时……

当然，也可能是我太菜了，不能手到拈来。欢迎各位大神前来指教。

没了。

参考

[1] 命令和操作：转载自 https://www.cnblogs.com/uakora/p/11809501.html

[2] 约束：大部分转载自 https://www.w3cschool.cn/sql/vgh71oyq.html

[3] 视图的意义：转载自 https://zhuanlan.zhihu.com/p/80183774

以上是 SQLSever基本命令使用复习 的全部内容， 来源链接： utcz.com/z/535588.html

回到顶部