SQLsever数据库期末复习笔记

SQL 语法
数据库表

数据库通常包含一个或多个表。每个表都有一个名称(例如“客户”或者“订单”)。表包含记录(行)。
以下是“客户”表的选取数据:
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany

上面的表包含6条记录(每个客户一条)和7列(CustomerID、CustomerName、ContactName、地址、城市、邮政编码和国家)。

SQL 语句
在数据库上执行的大多数操作都是用SQL语句完成的。
下面的SQL语句选取了“Customers”表中的所有记录:
示例

SELECT * FROM Customers;

在本教程中，我们将介绍不同的SQL语句。

注意：SQL关键字不区分大小写:select与SELECT相同。在本教程中，所有SQL关键字，我们都将用大写字母。
SQL语句后面的分号?
有些数据库系统需要在每个SQL语句的末尾使用分号。
分号是分隔数据库系统中每个SQL语句的标准方法，这些系统允许在对服务器的同一调用中执行多个SQL语句。
在本教程中，我们将在每个SQL语句的末尾使用分号。

SQL注释
注释可以让代码更易懂，有助于团队协作和后续维护。技术上讲，注释是数据库引擎忽略的文本。
SQL支持单行和多行注释。
单行注释以两个连续的连字符(——)开始。例如:
示例
– Select all the employees
SELECT * FROM employees;
复制
多行注释以斜杠后跟星号(/)开始注释，以星号后跟斜杠(/)结束注释，如下所示:
示例
/* Select all the employees whose
salary is greater than 5000 */
SELECT * FROM employees
WHERE salary > 5000;、

一些最重要的SQL命令
• SELECT – 从数据库中读取数据
• UPDATE – 更新数据库中的数据
• DELETE – 从数据库中删除数据
• INSERT INTO – 将新数据插入数据库
• CREATE DATABASE – 创建一个新数据库
• ALTER DATABASE – 修改数据库
• CREATE TABLE – 创建一个新表
• ALTER TABLE – 修改一个表
• DROP TABLE – 删除一个表
• CREATE INDEX – 创建索引(搜索键)
• DROP INDEX – 删除索引

SQL 创建数据库
在本章教程中，将学习使用SQL创建数据库。
创建数据库
在处理数据之前，我们必须先创建一个数据库。我们假设您已安装好MySQL，如没有请参考SQL 环境搭建。
SQL的CREATE DATABASE语句用于创建数据库。
语法
创建数据库的基本语法如下:
CREATE DATABASE database_name;
复制
下面的SQL语句创建了一个名为demo的数据库:
CREATE DATABASE demo;
复制
创建数据库时，默认并没有把刚创建的数据库切换为当前数据库，因此，我们必须使用USE语句切换。例如，使用USE demo将demo数据库设置为当前数据库。
注意: 在Unix/Linux中，数据库和表名是区分大小写的，因此必须始终将数据库称为demo，而不是Demo、DEMO或其他名称。但是，SQL关键字是不区分大小写的，如CREATE DATABASE和create database是一样的。
在MySQL中创建数据库
让我们使用命令行工具在MySQL中创建一个数据库。

1. 调用MySQL命令行工具
   要执行SQL，我们必须首先登录到MySQL服务器。
   在开始菜单找到MySQL xxx command Line Client，打开后，输入密码登录数据库：
   Enter password: ******
   Welcome to the MySQL monitor. Commands end with ; or \g.
   Your MySQL connection id is 7
   Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright © 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
复制
也可以在命令行终端登录MySQL服务器，执行如下命令：

mysql -u root -p
复制

2. 创建MySQL数据库
   现在，执行以下命令创建名为demo的数据库。
   mysql> CREATE DATABASE demo;
   复制
   如果数据库创建成功，您将看到这样的输出:
   Query OK, 1 row affected (0.03 sec)
   复制
   如果试图创建一个已经存在的数据库，会得到一个错误消息。为了避免错误，检查当数据库不存在时再创建, 可用以下命令创建数据库，:
   mysql> CREATE DATABASE IF NOT EXISTS demo;
   复制
3. 切换当前数据库
   执行以下命令切换当前数据库，成功后打印“Database changed”。现在，demo数据库是当前数据库，所有后续SQL都将在当前数据库执行。
   mysql> USE demo;
   复制
   提示: 如果想查看MySQL服务器上现有数据库的列表，可以执行语句SHOW databases;。
   SQL 创建表
   创建表
   在前一章中，我们学习了如何在数据库服务器上创建数据库。现在将在数据库中创建一些表来实际保存数据。数据库的表只是将信息组织成行和列。
   SQL的CREATE TABLE语句用于创建表。
   语法
   创建表的基本语法如下:
   CREATE TABLE table_name (
   column1_name data_type constraints,
   column2_name data_type constraints,
   …
   );
   复制
   让我们在demo数据库中创建一个表。在MySQL命令行工具上输入以下语句，然后回车:
   示例
   – Syntax for MySQL Database
   CREATE TABLE persons (
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(50) NOT NULL,
   birth_date DATE,
   phone VARCHAR(15) NOT NULL UNIQUE
   );

复制
上面的语句创建了一个名为persons的表，其中包含5个字段id、name、birth_date和phone。注意，每个字段后面都有一个数据类型声明，表示该字段将存储何种类型的数据，例如：整数、字符串、日期等。
某些数据类型可以使用length参数声明，该参数指示可以在字段中存储多少字符。例如，VARCHAR(50)最多可以容纳50个字符。
注意: 字段的数据类型可能因数据库系统的不同而不同。例如，MySQL和SQL Server支持整数的INT数据类型，而Oracle数据库支持NUMBER数据类型。
下表总结了MySQL支持的常用数据类型。
数据类型 描述
INT 存储-2147483648到2147483647范围内的数值
DECIMAL 存储精确精度的十进制值。
CHAR 存储最大长度为255个字符的固定长度字符串。
VARCHAR 存储最大长度为65,535个字符的可变长度字符串。
TEXT 存储最大长度为65,535个字符的字符串。
DATE 以YYYY-MM-DD格式存储日期值。
DATETIME 以YYYY-MM-DD HH:MM:SS格式存储组合的日期/时间值。
TIMESTAMP 存储时间戳值，时间戳值存储为自Unix纪元(‘1970-01-01 00:00:01’ UTC)以来的秒数。
前面示例中的字段设置了一些约束(也称为修饰符)。约束定义了字段值的规则。
• NOT NULL约束确保字段不能接受空值。
• 主键约束将对应的字段标记为表的主键。
• AUTO_INCREMENT属性是MySQL对标准SQL的扩展，它告诉MySQL，通过将前一个值增加1来自动为该字段分配一个值，仅适用于数值字段。
• 唯一约束确保字段值必须唯一。
我们将在下一章学习更多关于SQL约束的知识。
提示: 可以执行命令：DESC table_name;查看MySQL或Oracle数据库中任意表的字段信息，在SQL Server中等价命令：EXEC sp_columns table_name;(用实际的表名替换table_name)。
Create Table If Not Exists
如果你试图创建一个已经存在的表，会得到一个错误消息。在MySQL中，可以使用If Not Exists语句，先检查表是否存在:
示例
– Syntax for MySQL Database
CREATE TABLE IF NOT EXISTS persons (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL UNIQUE
);
复制
如果要查看当前数据库中的所有表，可以执行SHOW tables;。
SQL 约束
2019年5月15日

约束是什么?
约束限制了字段的取值。约束提供了一种标准机制来维护表内数据的准确性和完整性。
SQL中有几种不同类型的约束，包括:
• NOT NULL
• PRIMARY KEY
• UNIQUE
• DEFAULT
• FOREIGN KEY
• CHECK (MySQL不支持)
现在，让我们详细讨论这些约束。
NOT NULL
NOT NULL约束表示字段不接受空值。
这意味着，如果对字段应用NOT NULL约束，插入新行时，该字段必须为非NULL值。
下面的SQL语句创建一个名为persons的表，该表有4个字段，其中3个字段id、name和phone不接受空值。
示例
– Syntax for MySQL Database
CREATE TABLE persons (
id INT NOT NULL,
name VARCHAR(30) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL
);
复制
注意，NULL与zero(0)、blank或诸如””之类的零长度字符串不同，NULL表示没有生成任何条目。
PRIMARY KEY
主键约束表明该字段是主键。主键能唯一地标识表中的每一行。表中的任何两行都不能具有相同的主键值，此外，不能在主键列中输入NULL值。
下面的SQL语句创建一个名为persons的表，并指定id列作为主键。这意味着该字段不允许出现空值或重复值。
示例
– Syntax for MySQL Database
CREATE TABLE persons (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL
);
复制
主键通常由表中的一个字段组成，但是也可以由多个字段组成主键，例如，雇员的电子邮件地址或ID是雇员表的逻辑主键。
UNIQUE
唯一性约束表示一个或多个字段组合在表中不能重复。
尽管唯一性约束和主键约束都强制唯一性，但主键还作为一行记录的标识。
下面的SQL语句创建一个名为persons的表，并将phone列指定为惟一的。这意味着该字段不允许重复。
示例
CREATE TABLE persons (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL UNIQUE
);
复制
可以在表上定义多个唯一性约束，但只能在表上定义一个主键约束。此外，与主键约束不同，唯一性允许空值。
DEFAULT
默认约束指定字段的默认值。
在执行INSERT语句时，没有给字段提供值，字段被设置为默认值。
下面的SQL语句为country字段指定一个默认值。
示例
CREATE TABLE persons (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL UNIQUE,
country VARCHAR(30) NOT NULL DEFAULT ‘Australia’
);
复制
如果您将一个字段定义为NOT NULL，同时为该字段指定了一个默认值，那么在INSERT语句中，不需要显式地为该列指定值。
FOREIGN KEY
外键(FK)是一个字段或字段组合，用于建立两个表中的数据之间的关系。
下面是一个示例图，展示了employees和departments表之间的关系。
如果仔细查看，您会注意到employees表的dept_id列与departments表的主键列匹配。因此，employees表的dept_id列是departments表的外键。

在MySQL中，创建表时可以通过定义外键约束来创建外键，如下所示。下面的语句在employees表的dept_id列上建立一个外键，该外键引用departments表的dept_id列。
示例
CREATE TABLE employees (
emp_id INT NOT NULL PRIMARY KEY,
emp_name VARCHAR(55) NOT NULL,
hire_date DATE NOT NULL,
salary INT,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
复制
CHECK (MySQL不支持)
CHECK约束的作用是，对字段值作出限制。
例如，可以通过创建检查约束：只允许值从3,000到10,000，限制salary字段的值范围。这就防止了薪金超过正常薪金范围。
示例
CREATE TABLE employees (
emp_id INT NOT NULL PRIMARY KEY,
emp_name VARCHAR(55) NOT NULL,
hire_date DATE NOT NULL,
salary INT NOT NULL CHECK (salary >= 3000 AND salary <= 10000),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
复制
注意: MySQL不支持CHECK约束。CHECK子句会被解析，但是MySQL会忽略它。
SQL INSERT
2019年5月15日

在表中插入数据
在前一章中，我们在demo数据库中创建了一个名为persons的表。现在我们在新创建的数据库表中插入一些数据。
INSERT INTO语句用于向数据库表中插入新行。
语法
表中插入数据的基本语法如下:
INSERT INTO table_name (column1,column2,…) VALUES (value1,value2,…);
复制
这里的column1,column2,…表示字段名称，而value1,value2,…表示这些列的对应值。
让我们在persons表中插入一些记录。

1. 查看表结构
   在添加记录之前，最好先搞清楚表结构。在MySQL命令行上执行以下命令。它将显示表中字段信息，即列名、数据类型、约束等。
   mysql> DESCRIBE persons;
   复制
   输出如下：
   ±-----------±------------±-----±----±--------±---------------+
   | Field | Type | Null | Key | Default | Extra |
   ±-----------±------------±-----±----±--------±---------------+
   | id | int(11) | NO | PRI | NULL | auto_increment |
   | name | varchar(50) | NO | | NULL | |
   | birth_date | date | YES | | NULL | |
   | phone | varchar(15) | NO | UNI | NULL | |
   ±-----------±------------±-----±----±--------±---------------+
   复制
2. 将记录添加到表中
   下面的语句在persons表中插入一个新行。
   INSERT INTO persons (name, birth_date, phone)
   VALUES (‘Peter Wilson’, ‘1990-07-15’, ‘0711-020361’);
   复制
   您是否注意到，我们没有为id字段插入任何值。因为，如前面章节提到，id字段用AUTO_INCREMENT标记，它告诉MySQL如果这个字段没有指定值，就自动给它赋一个值。
   注意: 像字符串和日期这样的非数字值必须始终用引号括起来，数字值不能用引号括起来。
   同样，在persons表中插入另一行，如下所示:
   INSERT INTO persons (name, birth_date, phone)
   VALUES (‘Carrie Simpson’, ‘1995-05-01’, ‘0251-031259’);
   复制
   以类似的方式在persons表中再插入一行:
   INSERT INTO persons (name, birth_date, phone)
   VALUES (‘Victoria Ashworth’, ‘1996-10-17’, ‘0695-346721’);
   复制
   现在，执行以下命令，从persons表中选取记录，
   SELECT * FROM persons;
   复制
   输出将如下所示:
   ±—±-------------------±-----------±------------+
   | id | name | birth_date | phone |
   ±—±-------------------±-----------±------------+
   | 1 | Peter Wilson | 1990-07-15 | 0711-020361 |
   | 2 | Carrie Simpson | 1995-05-01 | 0251-031259 |
   | 3 | Victoria Ashworth | 1996-10-17 | 0695-346721 |
   ±—±-------------------±-----------±------------+
   SQL SELECT
   2019年5月15日

从表中选取数据
在前一章中，我们学习了如何向数据库的表中插入数据。现在我们将使用SQL查询语句从现有表中选取数据。
SELECT语句用于从一个或多个表中选取数据。您可以使用此语句一次性检索表中的所有行，也可以只检索满足特定条件的行。
语法
从表中选取数据的基本语法如下:
SELECT column1_name, column2_name, columnN_name FROM table_name;
复制
这里，column1_name, column2_name, columnN_name要获取的字段名称。如果您想获取表中全部字段，可以使用以下语法:
SELECT * FROM table_name;
复制
下面我们将介绍如何从数据库中查询数据。
前面章节在demo数据库中，我们创建了名为persons的表，其中包含以下记录:
±—±------------------±-----------±------------+
| id | name | birth_date | phone |
±—±------------------±-----------±------------+
| 1 | Peter Wilson | 1990-07-15 | 0711-020361 |
| 2 | Carrie Simpson | 1995-05-01 | 0251-031259 |
| 3 | Victoria Ashworth | 1996-10-17 | 0695-346721 |
±—±------------------±-----------±------------+
从表中选取全部数据
下面的语句将返回employees表中的所有行。
示例
SELECT * FROM persons;
复制
执行之后，输出将如下所示:
±—±------------------±-----------±------------+
| id | name | birth_date | phone |
±—±------------------±-----------±------------+
| 1 | Peter Wilson | 1990-07-15 | 0711-020361 |
| 2 | Carrie Simpson | 1995-05-01 | 0251-031259 |
| 3 | Victoria Ashworth | 1996-10-17 | 0695-346721 |
±—±------------------±-----------±------------+
如上所示，返回了persons表中的所有行和列。
提示: 星号(*)是一个通配符，表示全部。。
从表中选取列
如果你不需要所有的数据，你可以选取特定的列，像这样:
示例
SELECT id, name FROM persons;
复制
执行上述语句后，将得到如下输出:
±—±------------------+
| id | name |
±—±------------------+
| 1 | Peter Wilson |
| 2 | Carrie Simpson |
| 3 | Victoria Ashworth |
±—±------------------+
SQL WHERE
2019年5月15日

根据条件选取记录
在前一章中，我们学习了如何从表中获取所有记录。但是，在现实场景中，我们通常只需要选取、更新或删除那些满足特定条件的记录，比如属于特定的年龄组或国家的用户。
WHERE子句用于SELECT、UPDATE和DELETE。
语法
WHERE子句与SELECT语句一起使用可以只选取满足指定条件的记录。基本语法如下:
SELECT column_list FROM table_name WHERE condition;
复制
在这里，column_list是要获取的字段，如名称、年龄、国家等。但是，如果想获取表中所有字段，可以使用以下语法:
SELECT * FROM table_name WHERE condition;
复制
现在，让我们来看一些例子。
前面章节里，在demo数据库中，我们创建了名为persons的表，其中包含以下记录:
±—±------------------±-----------±------------+
| id | name | birth_date | phone |
±—±------------------±-----------±------------+
| 1 | Peter Wilson | 1990-07-15 | 0711-020361 |
| 2 | Carrie Simpson | 1995-05-01 | 0251-031259 |
| 3 | Victoria Ashworth | 1996-10-17 | 0695-346721 |
±—±------------------±-----------±------------+
使用WHERE子句过滤记录
下面的SQL语句将从persons表中返回所有id >= 2的记录。WHERE子句过滤掉了不需要的数据。
示例
SELECT * FROM persons
WHERE id >= 2;
复制
执行之后，输出将如下所示:
±—±------------------±-----------±------------+
| id | name | birth_date | phone |
±—±------------------±-----------±------------+
| 2 | Carrie Simpson | 1995-05-01 | 0251-031259 |
| 3 | Victoria Ashworth | 1996-10-17 | 0695-346721 |
±—±------------------±-----------±------------+
类似地，选取记录时可以指定字段，如下所示:
示例
SELECT id, name FROM persons
WHERE id >= 2;
复制
执行上述语句后，将得到如下输出:
±—±------------------+
| id | name |
±—±------------------+
| 2 | Carrie Simpson |
| 3 | Victoria Ashworth |
±—±------------------+
下面的语句将获取名字包含“Simpson”的记录。
示例
SELECT * FROM persons
WHERE name like ‘%Simpson%’;
复制
该语句将产生以下输出:
±—±---------------±-----------±------------+
| id | name | birth_date | phone |
±—±---------------±-----------±------------+
| 2 | Carrie Simpson | 1995-05-01 | 0251-031259 |
±—±---------------±-----------±------------+
WHERE子句中允许的运算符
SQL支持许多运算符，可以在WHERE子句中使用，常用的运算符总结如下。
运算符 描述 例子
= 等于 WHERE id = 2

大于 WHERE age > 30
< 小于 WHERE age < 18
= 大于等于 WHERE rating >= 4
<= 小于等于 WHERE price <= 100
LIKE 简单的模式匹配 WHERE name LIKE ‘Dav’
IN 检查指定值是否匹配列表或子查询中的任何值 WHERE country IN (‘USA’, ‘UK’)
BETWEEN 检查指定值是否在值的范围内 WHERE rating BETWEEN 3 AND 5
SQL AND 与 OR
2019年5月15日

根据条件选取记录
在前一章中，我们学习了如何使用WHERE子句中的单个条件从表中选取记录。但有时需要根据多个条件过滤记录，比如选择用户年龄大于30岁，国家是美国，选择产品价格低于100美元，评级大于4，等等。
AND 运算符
AND/与 运算符是一个逻辑运算符，它组合了两个条件，只有当两个条件的值都为真时才返回真。AND 运算符通常用于SELECT、UPDATE、DELETE语句的WHERE子句中，作为筛选条件。
SELECT column1_name, column2_name, columnN_name
FROM table_name
WHERE condition1 AND condition2;
复制
现在，让我们来看一些例子。
前面章节里，在demo数据库中，我们创建了名为persons的表，其中包含以下记录:
±—±------------------±-----------±------------+
| id | name | birth_date | phone |
±—±------------------±-----------±------------+
| 1 | Peter Wilson | 1990-07-15 | 0711-020361 |
| 2 | Carrie Simpson | 1995-05-01 | 0251-031259 |
| 3 | Victoria Ashworth | 1996-10-17 | 0695-346721 |
±—±------------------±-----------±------------+
WHERE子句使用AND运算符
下面的SQL语句将从persons表中返回id >= 2，且名字里包含“Simpson”的人。
示例
SELECT * FROM persons
WHERE id >= 2 AND name like ‘%Simpson%’;
复制
执行之后，您将得到如下输出:
±—±---------------±-----------±------------+
| id | name | birth_date | phone |
±—±---------------±-----------±------------+
| 2 | Carrie Simpson | 1995-05-01 | 0251-031259 |
±—±---------------±-----------±------------+
OR 运算符
同样，OR/或运算符也是一个逻辑操作符，它组合了两个条件，当其中一个条件为真时，返回真。
下面的SQL语句将从persons表中返回id >= 2，或名字里包含“Simpson”的人。
示例
SELECT * FROM persons WHERE id >= 2 OR name like ‘%Simpson%’;
复制
这次你会得到这样的输出:
±—±------------------±-----------±------------+
| id | name | birth_date | phone |
±—±------------------±-----------±------------+
| 2 | Carrie Simpson | 1995-05-01 | 0251-031259 |
| 3 | Victoria Ashworth | 1996-10-17 | 0695-346721 |
±—±------------------±-----------±------------+
组合使用 AND 与 OR 运算符
您还可以组合使用AND和OR来创建复杂的条件表达式。
下面的SQL语句将返回返回id >= 2，且名字里包含“Simpson”或者“Ashworth”的人。
示例
SELECT * FROM persons
WHERE id >= 2 AND (name like ‘%Simpson%’ OR name like ‘%Ashworth%’);
复制
这次你会得到这样的输出:
±—±------------------±-----------±------------+
| id | name | birth_date | phone |
±—±------------------±-----------±------------+
| 2 | Carrie Simpson | 1995-05-01 | 0251-031259 |
| 3 | Victoria Ashworth | 1996-10-17 | 0695-346721 |
±—±------------------±-----------±------------+
SQL IN 与 BETWEEN
2019年5月15日

范围和成员条件
在查询数据时，有时需要查询在一定范围内的值，或者属于某个集合成员的值。
IN和BETWEEN运算符允许您定义范围或成员条件。
IN 运算符
IN操作符是逻辑操作符，用于检查某值是否存在于一组值中。其基本语法如下:
SELECT column_list FROM table_name
WHERE column_name IN (value1, value1,…);
复制
在这里，column_list是要获取的字段，如名称、年龄、国家等。
我们来看一些例子。
假设我们的数据库中有一个employees表，其中包含以下记录:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
±-------±-------------±-----------±-------±--------+
如在demo数据库中，没有这个employees数据表，可按如下sql语句创建：
CREATE TABLE IF NOT EXISTS employees (
emp_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(50) NOT NULL,
hire_date DATE,
salary INT,
dept_id INT
);
INSERT INTO employees (emp_name, hire_date, salary, dept_id) VALUES (‘Ethan Hunt’, ‘2001-05-01’, 5000, 4);
INSERT INTO employees (emp_name, hire_date, salary, dept_id) VALUES (‘Tony Montana’, ‘2002-07-15’, 6500, 1);
INSERT INTO employees (emp_name, hire_date, salary, dept_id) VALUES (‘Sarah Connor’, ‘2005-10-18’, 8000, 5);
INSERT INTO employees (emp_name, hire_date, salary, dept_id) VALUES (‘Rick Deckard’, ‘2007-01-03’, 7200, 3);
INSERT INTO employees (emp_name, hire_date, salary) VALUES (‘Martin Blank’, ‘2008-06-24’, 5600);
复制
下面的SQL语句返回dept_id为1或3的员工。
示例
SELECT * FROM employees WHERE dept_id IN (1, 3);
复制
执行查询后，您将得到如下结果集:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
±-------±-------------±-----------±-------±--------+
类似地，可以使用NOT IN运算符，它与IN正好相反。下面的SQL语句将返回除dept_id是1或3之外的所有员工。
示例
SELECT * FROM employees
WHERE dept_id NOT IN (1, 3);
复制
执行查询后，这一次您将得到如下结果集:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
±-------±-------------±-----------±-------±--------+
BETWEEN 运算符
有时，需要选取字段值在一定范围内的行，这种条件在处理数值时很常见。
要执行这种查询，可以使用BETWEEN运算符。它是一个逻辑运算符，允许您指定字段值范围，如下所示:
SELECT column1_name, column2_name, columnN_name
FROM table_name
WHERE column_name BETWEEN min_value AND max_value;
复制
定义数值范围
下面的SQL语句将从employees表中返回工资在7000和9000之间的员工。
示例
SELECT * FROM employees
WHERE salary BETWEEN 7000 AND 9000;
复制
执行之后，您将得到如下输出:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
±-------±-------------±-----------±-------±--------+
定义日期范围
当使用带日期或时间值的BETWEEN运算符时，使用CAST()函数显式地将值转换为所需的数据类型，以获得最佳结果。例如，如果在与日期的比较中使用’2016-12-31’之类的字符串，请将该字符串转换为日期，如下所示:
下面的SQL语句查询了2006年1月1日(即“2006年01月01日”)至2016年12月31日(即“2016-12-31”)期间雇佣的所有员工:
示例
SELECT * FROM employees WHERE hire_date
BETWEEN CAST(‘2006-01-01’ AS DATE) AND CAST(‘2016-12-31’ AS DATE);
复制
执行之后，您将得到如下输出:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
±-------±-------------±-----------±-------±--------+
定义字符串范围
虽然日期和数字范围是最常见的，但是您也可以构建字符串范围条件。下面的SQL语句查询全部名字以字母“O”和“Z”之间的任意一个字母开头的员工:
示例
SELECT * FROM employees
WHERE emp_name BETWEEN ‘O’ AND ‘Z’;
复制
执行之后，您将得到如下输出:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
±-------±-------------±-----------±-------±--------+
SQL ORDER BY
2019年5月15日

对结果集进行排序
通常，使用SELECT语句获取数据时，结果集是没有经过排序的。如果希望对结果集排序，可以在语句末尾指定order BY子句，默认是升序排序。
语法
使用ORDER BY子句可按升序或降序对查询结果进行排序。这个子句的基本语法如下:
SELECT column_list FROM table_name ORDER BY column_name ASC|DESC;
复制
在这里，column_list是要获取的字段，如name、age、country等，column_name是要排序的字段。让我们来看一些例子。
假设我们有一个employees表，包含以下记录:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
±-------±-------------±-----------±-------±--------+
单列排序
下面的SQL语句选取全部employee，结果按emp_name字段升序排序。
示例
SELECT * FROM employees
ORDER BY emp_name ASC;
复制
ASC可省略，因为默认排序就是升序/ASC:
类似地，可使用DESC选项按降序执行排序。下面的语句对查询结果，按salary字段降序排序。
SELECT * FROM employees
ORDER BY salary DESC;
复制
这次，您将得到这样的结果集:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 |
±-------±-------------±-----------±-------±--------+
多个列排序
还可以在排序时指定多个列。
单列排序时，如果排序字段有重复值，查询结果中某些行的顺序就不确定，此时如果引入第二个排序字段，就可对这些顺序不确定的行进行二次排序。
为了更好地理解多列排序，我们假设数据库中有一个名为trainees的表，其中记录如下:
±—±-----------±-----------±------------±-------+
| id | first_name | last_name | birth_date | gender |
±—±-----------±-----------±------------±-------+
| 1 | Peter | Parker | 1998-03-04 | M |
| 2 | Harry | Potter | 2001-08-30 | M |
| 3 | Peter | Pan | 2004-09-19 | M |
| 4 | Alice | Kingsleigh | 1999-07-02 | F |
| 5 | John | Connor | 2002-01-15 | M |
±—±-----------±-----------±------------±-------+
如果仔细看表，你会发现有一些重复的值。学员“Peter Parker”和“Peter Pan”的姓不同，但名是一样的。
现在执行以下命令，该命令按名/first_name字段对结果集进行排序。
示例
SELECT * FROM trainees
ORDER BY first_name;
复制
执行之后，您将得到如下输出:
±—±-----------±-----------±------------±-------+
| id | first_name | last_name | birth_date | gender |
±—±-----------±-----------±------------±-------+
| 4 | Alice | Kingsleigh | 1999-07-02 | F |
| 2 | Harry | Potter | 2001-08-30 | M |
| 5 | John | Connor | 2002-01-15 | M |
| 1 | Peter | Parker | 1998-03-04 | M |
| 3 | Peter | Pan | 2004-09-19 | M |
±—±-----------±-----------±------------±-------+
现在执行这个语句，它按first_name和last_name列对结果集进行排序。
示例
SELECT * FROM trainees
ORDER BY first_name, last_name;
复制
±—±-----------±-----------±------------±-------+
| id | first_name | last_name | birth_date | gender |
±—±-----------±-----------±------------±-------+
| 4 | Alice | Kingsleigh | 1999-07-02 | F |
| 2 | Harry | Potter | 2001-08-30 | M |
| 5 | John | Connor | 2002-01-15 | M |
| 3 | Peter | Pan | 2004-09-19 | M |
| 1 | Peter | Parker | 1998-03-04 | M |
±—±-----------±-----------±------------±-------+
你注意到以前的成绩和现在的成绩有什么不同了吗？这次，学员Peter Parker的成绩排在了Peter Pan之后。
由于两个学员的名字相同，都是“Peter”，所以在last_name列为这两个学员执行了第二级排序，这就是为什么学员“Peter Parker”的记录在“Peter Pan”之后。
注意: 当指定多个排序字段时，结果集最初按第一个字段排序，然后按第二个字段排序，依此类推。
SQL TOP或LIMIT
2019年5月15日

结果集限制
在某些情况下，您可能只需排在前面的查询结果，例如，您只想查询成绩前3名的学生。
要处理这种情况，可以在SELECT语句中使用SQL的TOP或LIMIT子句。
TOP子句用在SQL Server和MS Access里，LIMIT子句用在MySQL里，而Oracle提供了等价的ROWNUM子句。
SQL TOP 语法
SQL TOP子句用于限制返回的行数。其基本语法是:
SELECT TOP number | percent column_list FROM table_name;
复制
在这里，column_list是要查询的字段，多个字段由逗号分隔。让我们看一些例子。
假设我们的数据库中有一个employees表，其中包含以下记录:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
±-------±-------------±-----------±-------±--------+
下面的语句从employees表中返回前三位薪酬最高的员工。
示例
– Syntax for SQL Server Database
SELECT TOP 3 * FROM employees
ORDER BY salary DESC;
复制
返回的结果集将如下所示:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
±-------±-------------±-----------±-------±--------+
如果只想检索行数的百分比而不是固定行数，可以在TOP子句中的固定值后面选择使用PERCENT关键字。小数将四舍五入(例如1.5四舍五入到2)。
下面的语句返回收入最高的雇员的前30%。
示例
– Syntax for SQL Server Database
SELECT TOP 30 PERCENT * FROM employees
ORDER BY salary DESC;
复制
上述查询返回的结果集如下:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
±-------±-------------±-----------±-------±--------+
MySQL LIMIT 语法
MySQL的LIMIT子句与SQL TOP子句执行相同的工作。其基本语法是:
SELECT column_list FROM table_name LIMIT number;
复制
下面的语句从employees表中返回前三位薪酬最高的员工。
示例
– Syntax for MySQL Database
SELECT * FROM employees
ORDER BY salary DESC LIMIT 3;
复制
执行之后，您将得到如下输出:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
±-------±-------------±-----------±-------±--------+
在LIMIT子句中设置行偏移量
LIMIT子句设置偏移量，需传入两个参数，第一个指定返回的首行偏移量，即起始点，第二个指定返回行数。偏移量从0开始(不是1)。
所以，如果你想找出薪资第三高的员工，你可以这样做:
示例
– Syntax for MySQL Database
SELECT * FROM employees
ORDER BY salary DESC LIMIT 2, 1;
复制
执行以上命令后，结果集中只有一条记录:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
±-------±-------------±-----------±-------±--------+
SQL DISTINCT
2019年5月15日

检索时去重
从数据库表获取数据时，结果集可能包含重复的行或值。如果要删除这些重复的值，可以在SELECT关键字后面指定DISTINCT关键字。
语法
DISTINCT子句用于从结果集中删除重复的行:
SELECT DISTINCT column_list FROM table_name;
复制
在这里，column_list是要查询的字段，多个字段由逗号分隔。
让我们来看一些例子。
假设我们的数据库中有一个customer表，其中包含以下记录:
±--------±-------------------±----------±------------+
| cust_id | cust_name | city | postal_code |
±--------±-------------------±----------±------------+
| 1 | Maria Anders | Berlin | 12209 |
| 2 | Fran Wilson | Madrid | 28023 |
| 3 | Dominique Perrier | Paris | 75016 |
| 4 | Martin Blank | Turin | 10100 |
| 5 | Thomas Hardy | Portland | 97219 |
| 6 | Christina Aguilera | Madrid | 28001 |
±--------±-------------------±----------±------------+
现在执行下面的语句，该语句返回该表city列中的所有行。
示例
SELECT city FROM customers;
复制
执行之后，您将得到如下输出:
±----------+
| city |
±----------+
| Berlin |
| Madrid |
| Paris |
| Turin |
| Portland |
| Madrid |
±----------+
如果您仔细查看输出，您会发现“Madrid”这个城市在我们的结果中出现了两次。
去除重复数据
下面的语句查询时使用了DISTINCT。
示例
SELECT DISTINCT city FROM customers;
复制
执行上述命令后，将得到如下输出:
±----------+
| city |
±----------+
| Berlin |
| Madrid |
| Paris |
| Turin |
| Portland |
±----------+
正如您这次看到的，我们的结果集中没有重复的值。
注意：如果对具有多个空值的列使用SELECT DISTINCT语句，那么SQL将保留一个空值，并从结果集中删除其他空值，因为DISTINCT将所有空值视为相同的值。
SQL UPDATE
2019年5月15日

更新数据
在前几章中，我们介绍了如何插入数据，如何根据不同的条件从数据库表中查询数据。本章我们将介绍如何更新数据库中的现有数据。
语法
UPDATE语句用于更新表中的现有数据。
UPDATE table_name
SET column1_name = value1, column2_name = value2,…
WHERE condition;
复制
在这里，column1_name, column2_name,…是要更新的字段名。可以通过设置WHERE条件指定要更新的记录。
警告: UPDATE语句中的WHERE子句指定应该更新哪些记录。如果没有WHERE子句，所有记录都会更新
让我们来看一些例子。
假设我们的数据库中有一个employees表，它有以下记录:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 1 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 5 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 3 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 4 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
±-------±-------------±-----------±-------±--------+
更新单列
下面的SQL语句将更新employees表的emp_name字段，并设置一个新值，要更新的是emp_id等于3的那一行。
示例
UPDATE employees SET emp_name = ‘Sarah Ann Connor’
WHERE emp_id = 3;
复制
执行之后，结果表将如下所示:
±-------±-----------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-----------------±-----------±-------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 1 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 5 |
| 3 | Sarah Ann Connor | 2005-10-18 | 8000 | 3 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 4 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
±-------±-----------------±-----------±-------±--------+
更新多个列
类似地，可以使用逗号分隔的字段和值列表更新多个字段。下面的示例将更新employee表中emp_id为5的现有员工的salary和dept_id字段。
示例
UPDATE employees
SET salary = 6000, dept_id = 2
WHERE emp_id = 5;
复制
执行之后，结果表将如下所示:
±-------±-----------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-----------------±-----------±-------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 1 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 5 |
| 3 | Sarah Ann Connor | 2005-10-18 | 8000 | 3 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 4 |
| 5 | Martin Blank | 2008-06-24 | 6000 | 2 |
±-------±-----------------±-----------±-------±--------+
SQL DELETE
2019年5月15日

从表中删除数据
正如使用INSERT语句将记录插入表中一样，也可以使用DELETE语句从表中删除记录。
语法
DELETE语句用于从表中删除一行或多行记录。
DELETE FROM table_name WHERE condition;
复制
警告: DELETE语句中的WHERE子句指定应该删除哪些记录。如果忽略或忘记WHERE子句，所有记录将永久删除。
让我们删除persons表中的一些记录。
假设我们的person表目前有以下记录:
±—±-------------------±-----------±------------+
| id | name | birth_date | phone |
±—±-------------------±-----------±------------+
| 1 | Peter Wilson | 1990-07-15 | 0711-020361 |
| 2 | Carrie Simpson | 1995-05-01 | 0251-031259 |
| 3 | Victoria Ashworth | 1996-10-17 | 0695-346721 |
| 4 | George Bailey | 1993-03-05 | 0897-034214 |
| 5 | Norman Bates | 1999-08-25 | 0522-556721 |
±—±-------------------±-----------±------------+
根据条件删除记录
下面的语句将删除persons表中id大于3的行。
示例
DELETE FROM persons WHERE id > 3;
复制
执行查询后，persons表将如下所示:
±—±-------------------±-----------±------------+
| id | name | birth_date | phone |
±—±-------------------±-----------±------------+
| 1 | Peter Wilson | 1990-07-15 | 0711-020361 |
| 2 | Carrie Simpson | 1995-05-01 | 0251-031259 |
| 3 | Victoria Ashworth | 1996-10-17 | 0695-346721 |
±—±-------------------±-----------±------------+
删除所有数据
类似地，如上所述，如果您没有在DELETE语句中指定WHERE子句，那么表中的所有行都将被删除。但是，目标表本身不会被删除，这意味着表结构、属性和索引将保持不变。
以下语句将从persons表中删除所有记录:
示例
DELETE FROM persons;
复制
现在，如果您尝试从persons表中查询数据，您将得到一个空的结果集。
SQL TRUNCATE
2019年5月15日

删除表数据
TRUNCATE TABLE从表中删除所有行，比DELETE更快。从逻辑上讲，TRUNCATE TABLE类似于没有WHERE的DELETE语句。
TRUNCATE TABLE语句从表中删除所有行，但表结构及其列、约束、索引等保持不变。要删除表定义及其数据，可以使用DROP TABLE语句。
语法
TRUNCATE TABLE的基本语法如下:
TRUNCATE TABLE table_name;
复制
让我们看一些例子。
假设我们的数据库中有一个employees表，其中包含以下记录:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
±-------±-------------±-----------±-------±--------+
下面的命令从employees表中删除所有行:
示例
TRUNCATE TABLE employees;
复制
现在，在执行上述SQL语句之后，如果尝试从employees表中选取记录，将得到一个空的结果集。
TRUNCATE 对比 DELETE
虽然DELETE和TRUNCATE表似乎具有相同的效果，但它们的工作方式不同。以下是这两种语句的一些主要区别:
• TRUNCATE TABLE语句删除数据，自动递增值被重置为其初始值，通常为1。
• DELETE允许根据可选的WHERE子句筛选要删除的行，而TRUNCATE TABLE不支持WHERE子句，它只删除所有行。
• TRUNCATE TABLE比DELETE速度更快，使用更少的系统资源。
提示: 如果想删除所有行并重新创建整个表，请使用TRUNCATE TABLE。如果希望根据特定条件删除有限数量的行，或者不希望重置自动递增值，请使用DELETE。
SQL DROP
2019年5月15日

从数据库中删除表
可以使用DROP TABLE语句轻松删除不再需要的数据库表。DROP TABLE语句永久删除表中的所有数据，以及数据字典中定义的表的元数据。
语法
DROP TABLE删除一个或多个表。语法如下:
DROP TABLE table1_name, table2_name, …;
复制
这里，table1_name, table2_name, …是要删除的表名。
警告: 删除数据库或表是不可逆转的。因此，在使用DROP语句时要小心，因为数据库系统通常不会显示诸如“确定吗?”之类的警告。它会立即删除数据库或表及其所有数据。
让我们尝试使用DROP TABLE语句删除数据库表。
前面章节中，我们在demo数据库中创建了一个表格persons，下面的语句将从数据库中永久删除该表。
示例
DROP TABLE persons;
复制
在执行上述命令之后，如果您试图对persons表执行任何操作，比如从其中选择记录，您将得到一条错误消息。
删除数据库
类似地，您可以使用DROP DATABASE语句删除数据库。下面的命令将从数据库服务器中永久删除演示数据库。
示例
DROP DATABASE demo;
复制
如果你想用USE demo选中demo数据库，将得到一个错误消息：“未知数据库”或“数据库不存在”。
SQL 连接表
2019年5月16日

SQL Join 基础
到目前为止，您看到的所有查询都集中在一个表上。但在现实情况中，经常需要查询多个表，然后合并结果。在技术上这被称为join/连接，join根据多个表之间的一个共同字段(外键)来创建新的视图数据。
为了更容易理解这一点，让我们看一下下面的employees和departments表。employees表的dept_id列是departments表的外键。因此，可以将这两个表连接起来来获得组合的数据。
employees

±-------±-------------±-----------±--------+
| emp_id | emp_name | hire_date | dept_id |
±-------±-------------±-----------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
±-------±-------------±-----------±--------+

departments

±--------±-----------------+
| dept_id | dept_name |
±--------±-----------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
±--------±-----------------+
注意: 为了连接表，用于连接表的字段数据应该匹配，字段名没必要一样。
Join/连接类型
当连接表时，不同连接类型会影响查询结果。有以下几种连接类型:
Inner join/内连接
仅将两个表中满足连接条件的行组合起来作为结果集。
Outer join/外连接
在内连接的基础上，还包含不符合条件的数据行，在不匹配的表列填写NULL
有三种类型的外部连接:
• 左外部连接(或左连接)
• 右外部连接(或右连接)
• 全外部连接(或全连接)
Cross join/交叉连接
将两个表的所有行进行组合，连接后的行数为两个表的乘积数（笛卡尔积）。
SQL INNER JOIN (内连接)
2019年5月16日

内连接是最常见的连接类型，它只返回两个表的匹配行，内连接可以理解为交集，如下图所示。

让我们看一些例子。
假设我们有employees和departments表。
employees

±-------±-------------±-----------±--------+
| emp_id | emp_name | hire_date | dept_id |
±-------±-------------±-----------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
±-------±-------------±-----------±--------+

departments

±--------±-----------------+
| dept_id | dept_name |
±--------±-----------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
±--------±-----------------+
现在我们需要检索各个部门员工的id、名称、雇佣日期和部门名称。
你可以看到，employees表中有个字段dept_id，对应于departments表中的字段dept_id，从技术角度讲，即employees表的字段dept_id是departments表的外键。因此我们将使用这个字段作为这两个表之间的桥梁。
下面是一个示例，通过使用公共的字段dept_id将employees和departments表连接在一起，检索员工的id、姓名、雇佣日期和部门。那些不属于任何部门的员工不会包含。
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 INNER JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_id;
复制
连接表时，在每个字段名前面加上表名(例如employee.dept_id、departments.dept_id或t1.dept_id、t2.dept_id(如果使用表别名))，以免不同表的字段同名时出现错误。
可以使用表别名代替长表名，例如，employees表别名t1，使用t1更简短：t1.dept_id。
执行上述命令后，得到的结果集如下:
±-------±-------------±-----------±----------------+
| emp_id | emp_name | hire_date | dept_name |
±-------±-------------±-----------±----------------+
| 1 | Ethan Hunt | 2001-05-01 | Human Resources |
| 2 | Tony Montana | 2002-07-15 | Administration |
| 3 | Sarah Connor | 2005-10-18 | Sales |
| 4 | Rick Deckard | 2007-01-03 | Finance |
±-------±-------------±-----------±----------------+
结果集只包含dept_id值在employees与departments两张表都存在的员工，员工Martin Blank没有部门，没有被列出来。
SQL LEFT JOIN (左连接)
2019年5月16日

左连接返回左表中的所有行，以及满足连接条件的右表中的行。左连接是外部连接的一种类型，也被称为左外部连接。其他两类外部连接是右连接和全连接。
左连接的工作原理如下图所示。

为了更清楚地理解这一点，让我们看看下面的employees和departments表。
employees

±-------±-------------±-----------±--------+
| emp_id | emp_name | hire_date | dept_id |
±-------±-------------±-----------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
±-------±-------------±-----------±--------+

departments

±--------±-----------------+
| dept_id | dept_name |
±--------±-----------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
±--------±-----------------+
现在，假设您想检索所有员工的id、姓名和雇佣日期，以及他们所在部门的名称，而不管他们有没有分配到部门。我们可以使用左连接来查询。
下面的语句使用公共字段dept_id将employees和departments表连接在一起，来检索员工的id、姓名、雇佣日期和部门名称。没有部门的员工也被包含进来。
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_id;
复制
出现在JOIN子句左边的表即是左表，右边的是右表。
执行上述命令后，将得到如下输出:
±-------±-------------±-----------±----------------+
| emp_id | emp_name | hire_date | dept_name |
±-------±-------------±-----------±----------------+
| 1 | Ethan Hunt | 2001-05-01 | Human Resources |
| 2 | Tony Montana | 2002-07-15 | Administration |
| 3 | Sarah Connor | 2005-10-18 | Sales |
| 4 | Rick Deckard | 2007-01-03 | Finance |
| 5 | Martin Blank | 2008-06-24 | NULL |
±-------±-------------±-----------±----------------+
可以看到，结果集包含了左表employees中的所有行，不管department表中的dept_id字段是否匹配。
左表的行，如果右表中没有匹配行，则连接后的结果行中右表的字段填空值NULL。
SQL RIGHT JOIN (右连接)
2019年5月16日

右连接与左连接完全相反。它返回右表中的所有行，以及满足连接条件的左表中的行。
右连接是一种外部连接，也被称为右外部连接。其他类型外部连接是左连接和全连接。下图说明了右连接工作原理。

为了更清楚地理解这一点，让我们看看下面的employees和departments表。
employees

±-------±-------------±-----------±--------+
| emp_id | emp_name | hire_date | dept_id |
±-------±-------------±-----------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
±-------±-------------±-----------±--------+

departments

±--------±-----------------+
| dept_id | dept_name |
±--------±-----------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
±--------±-----------------+
现在，假设您想检索所有部门的名称及其下属员工的详细信息。实际情况中，可能有些部门目前没有员工。
下面的语句通过使用公共字段dept_id将employees和departments表连接在一起，检索所有部门的名称及其下属员工的id、名称、雇佣日期。
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 RIGHT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY dept_name;
复制
出现在JOIN子句左边的表即是左表，右边的是右表。
执行上述命令后，将得到如下输出:
±-------±-------------±-----------±-----------------+
| emp_id | emp_name | hire_date | dept_name |
±-------±-------------±-----------±-----------------+
| 2 | Tony Montana | 2002-07-15 | Administration |
| NULL | NULL | NULL | Customer Service |
| 4 | Rick Deckard | 2007-01-03 | Finance |
| 1 | Ethan Hunt | 2001-05-01 | Human Resources |
| 3 | Sarah Connor | 2005-10-18 | Sales |
±-------±-------------±-----------±-----------------+
结果集中包含了departments表的所有行，不管employee表中的dept_id字段是否匹配。
可以看到，即使部门“Customer Service”中没有emplpoyee，也包含进来了。
右表的一行，如果左表中没有匹配行，则连接后的结果行中左表的字段填空值NULL。
SQL FULL JOIN (全连接)
2019年5月16日

全连接返回连接表中的所有行，不管它们是否匹配，也就是说，全连接组合了左连接和右连接的功能。全连接是一种外部连接，也被称为完全外部连接。
下图说明了全连接工作原理。

为了更清楚地理解，让我们看看下面的employees和departments表。
employees

±-------±-------------±-----------±--------+
| emp_id | emp_name | hire_date | dept_id |
±-------±-------------±-----------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
±-------±-------------±-----------±--------+

departments

±--------±-----------------+
| dept_id | dept_name |
±--------±-----------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
±--------±-----------------+
现在，假设您想检索所有员工的名称和可用部门的名称，而不管它们在另一个表中是否有对应的行，可以使用全连接查询。
下面的语句使用公共字段dept_id将employees和departments表连接在一起，来检索所有部门以及所有员工的详细信息。
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 FULL JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_name;
复制
有些数据库，如Oracle、MySQL不支持全连接。可以使用UNION ALL语句来组合左连接和右连接，如下所示:
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id
UNION ALL
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 RIGHT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_name;
复制
执行上述命令后，将得到如下输出:
±-------±-------------±-----------±-----------------+
| emp_id | emp_name | hire_date | dept_name |
±-------±-------------±-----------±-----------------+
| NULL | NULL | NULL | Customer Service |
| 1 | Ethan Hunt | 2001-05-01 | Human Resources |
| 1 | Ethan Hunt | 2001-05-01 | Human Resources |
| 5 | Martin Blank | 2008-06-24 | NULL |
| 4 | Rick Deckard | 2007-01-03 | Finance |
| 4 | Rick Deckard | 2007-01-03 | Finance |
| 3 | Sarah Connor | 2005-10-18 | Sales |
| 3 | Sarah Connor | 2005-10-18 | Sales |
| 2 | Tony Montana | 2002-07-15 | Administration |
| 2 | Tony Montana | 2002-07-15 | Administration |
±-------±-------------±-----------±-----------------+
结果集包括了department和employees表中的所有行。
出现在JOIN子句左边的表即是左表，右边的是右表。
当执行外连接时，当某个表中的行在另一个表中没有匹配行时，结果行中的对应字段会填空NULL，表示数据不存在。
SQL CROSS JOIN (交叉连接)
2019年5月16日

如果在连接两个表时没有指定连接条件，则数据库系统将第一个表中的每一行与第二个表中的每一行组合起来。这种类型的连接称为交叉连接或笛卡尔积。
下图说明了交叉连接的工作原理。

为了更容易理解这一点，让我们看一下下面的employees和departments表。
employees

±-------±-------------±-----------±--------+
| emp_id | emp_name | hire_date | dept_id |
±-------±-------------±-----------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
±-------±-------------±-----------±--------+

departments

±--------±-----------------+
| dept_id | dept_name |
±--------±-----------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
±--------±-----------------+
交叉连接中的行数是每个表中的行数的乘积。下面是一个交叉连接的简单示例。
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 CROSS JOIN departments AS t2;
复制
交叉连接创建一个笛卡尔积，即一个表中的所有行与另一个表中的所有行相乘。例如，如果一个表有5行，另一个表有10行，交叉连接查询生成50行，即5和10的乘积。
执行上述命令后，得到的结果集如下:
±-------±-------------±-----------±-----------------+
| emp_id | emp_name | hire_date | dept_name |
±-------±-------------±-----------±-----------------+
| 1 | Ethan Hunt | 2001-05-01 | Administration |
| 2 | Tony Montana | 2002-07-15 | Administration |
| 3 | Sarah Connor | 2005-10-18 | Administration |
| 4 | Rick Deckard | 2007-01-03 | Administration |
| 5 | Martin Blank | 2008-06-24 | Administration |
| 1 | Ethan Hunt | 2001-05-01 | Customer Service |
| 2 | Tony Montana | 2002-07-15 | Customer Service |
| 3 | Sarah Connor | 2005-10-18 | Customer Service |
| 4 | Rick Deckard | 2007-01-03 | Customer Service |
| 5 | Martin Blank | 2008-06-24 | Customer Service |
| 1 | Ethan Hunt | 2001-05-01 | Finance |
| 2 | Tony Montana | 2002-07-15 | Finance |
| 3 | Sarah Connor | 2005-10-18 | Finance |
| 4 | Rick Deckard | 2007-01-03 | Finance |
| 5 | Martin Blank | 2008-06-24 | Finance |
| 1 | Ethan Hunt | 2001-05-01 | Human Resources |
| 2 | Tony Montana | 2002-07-15 | Human Resources |
| 3 | Sarah Connor | 2005-10-18 | Human Resources |
| 4 | Rick Deckard | 2007-01-03 | Human Resources |
| 5 | Martin Blank | 2008-06-24 | Human Resources |
| 1 | Ethan Hunt | 2001-05-01 | Sales |
| 2 | Tony Montana | 2002-07-15 | Sales |
| 3 | Sarah Connor | 2005-10-18 | Sales |
| 4 | Rick Deckard | 2007-01-03 | Sales |
| 5 | Martin Blank | 2008-06-24 | Sales |
±-------±-------------±-----------±-----------------+
交叉连接不如我们在前几章中介绍的其他连接有用，由于查询没有指定连接条件，所以employees表中的每一行都与departments表中的每一行相结合。因此，除非您确定要使用笛卡尔积，否则不要使用交叉连接。
SQL UNION
2019年5月16日

UNION / 合并操作符
UNION操作符用于将两个或多个SELECT查询结果合并到一个结果集中。UNION操作不同于JOIN(连接)，UNION操作将两个源表中的所有行放在一个结果表中，单行之间并不合并。
以下是使用UNION合并两个SELECT查询结果集的基本规则:
• 结果集中的字段数量和顺序必须相同
• 对应合并字段的数据类型必须兼容
当符合这些标准时，这些表可以用UNION合并:
语法
UNION的基本语法如下:
SELECT column_list FROM table1_name
UNION SELECT column_list FROM table2_name;
复制
让我们来看一下实例。
假设我们有下面两张表：
employees
±—±-----------±----------±-------+
| id | first_name | last_name | salary |
±—±-----------±----------±-------+
| 1 | Ethan | Hunt | 5000 |
| 2 | Tony | Montana | 6500 |
| 3 | Sarah | Connor | 8000 |
| 4 | Rick | Deckard | 7200 |
| 5 | Martin | Blank | 5600 |
±—±-----------±----------±-------+
customers
±—±-----------±----------±---------+
| id | first_name | last_name | city |
±—±-----------±----------±---------+
| 1 | Maria | Anders | Berlin |
| 2 | Fran | Wilson | Madrid |
| 3 | Dominique | Perrier | Paris |
| 4 | Martin | Blank | Turin |
| 5 | Thomas | Hardy | Portland |
±—±-----------±----------±---------+
让我们执行一个UNION操作来组合两个查询结果。
下面的语句返回所有客户和员工的姓和名:
SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM customers;
复制
执行上述语句后，结果集将如下所示:
±--------------±-------------+
| first_name | last_name |
±--------------±-------------+
| Ethan | Hunt |
| Tony | Montana |
| Sarah | Connor |
| Rick | Deckard |
| Martin | Blank |
| Maria | Anders |
| Fran | Wilson |
| Dominique | Perrier |
| Thomas | Hardy |
±--------------±-------------+
默认情况下，UNION操作从合并结果集中消除重复行。这就是为什么上面的查询只返回9行，因为名字“Martin Blank”同时出现在employees和customers表中。
但是，如果您想保留重复的行，可以使用ALL关键字，如下所示:
SELECT first_name, last_name FROM employees
UNION ALL
SELECT first_name, last_name FROM customers;
在SQL中，也可以使用LIKE操作符执行部分匹配或模式匹配。
LIKE操作符允许为一个或多个字符串指定通配符。可以使用以下两个通配符:
• 百分号(%) – 匹配任意数量的字符，甚至是0个字符。
• 下划线() – 只匹配一个字符
下面是一些示例，演示如何将LIKE操作符与通配符一起使用。
语句 意义 返回值
WHERE name LIKE ‘Da%’ 查找以Da开头的名称 David, Davidson
WHERE name LIKE ‘%th’ 找出以th结尾的名字 Elizabeth, Smith
WHERE name LIKE ‘%on%’ 查找包含on的名称 Davidson, Toni
WHERE name LIKE 'Sa’ 查找以Sm开头的名称，并且名称后面最多只有一个字符 Sam
WHERE name LIKE ‘oy’ 查找以oy结尾的名称，并且名称前面最多只有一个字符 Joy, Roy
WHERE name LIKE ‘an’ 查找包含an的名称，并且开头和结尾最多只有一个字符 Dana, Hans
WHERE name LIKE '%ar’ 查找包含ar的名称，以任意数量的字符开头，最多以一个字符结尾 Richard, Karl
WHERE name LIKE ‘_ar%’ 查找包含ar的名称，最多以一个字符开始，以任意数量的字符结尾 Karl, Mariya
让我们看一些例子。
假设我们的数据库中有一个employees表，其中包含以下记录:
±-------±-----------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-----------------±-----------±-------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
| 6 | simons bistro | 2009-04-01 | 6000 | 1 |
±-------±-----------------±-----------±-------±--------+
现在，假设你想找出所有名字以S开头的员工。
SELECT * FROM employees
WHERE emp_name LIKE ‘S%’;
复制
执行查询后，您将得到如下输出:
±-------±-----------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-----------------±-----------±-------±--------+
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 6 | simons bistro | 2009-04-01 | 6000 | 1 |
±-------±-----------------±-----------±-------±--------+
在MySQL中，非二进制字符串(CHAR、VARCHAR、TEXT)比较，默认情况下是不区分大小写的，而二进制字符串(binary、VARBINARY、BLOB)比较是区分的。
这意味着，如果使用’S%'进行搜索，就会得到以S或s开头的所有列值(如您所见，我们同时拥有”Sarah”和”simons”)。但是，如果你想使这个搜索区分大小写，可以使用二进制操作符，如下所示:
– Syntax for MySQL Database
SELECT * FROM employees
WHERE BINARY emp_name LIKE ‘S%’;
复制
现在，只返回名字以大写字母S开头的员工:
±-------±-----------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-----------------±-----------±-------±--------+
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
±-------±-----------------±-----------±-------±--------+
注意: 如果希望始终以区分大小写的方式处理字段，可在创建数据库或表时，使用区分大小写或二进制排序规则（collation），以避免任何性能问题。
SQL ALTER TABLE
2019年5月16日

修改现有的表
在创建表之后，经常会需要修改表，如新增字段、修改约束、表名等，可以使用ALTER TABLE语句来修改。
假设我们的数据库中有一个shippers表，其结构如下:
±-------------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-------------±------------±-----±----±--------±---------------+
| shipper_id | int | NO | PRI | NULL | auto_increment |
| shipper_name | varchar(60) | NO | | NULL | |
| phone | varchar(60) | NO | | NULL | |
±-------------±------------±-----±----±--------±---------------+
添加新列
向现有表添加新列的基本语法如下:
ALTER TABLE table_name ADD column_name data_type constraints;
复制
下面的语句向shippers表添加了一个新字段fax(传真)。
ALTER TABLE shippers ADD fax VARCHAR(20);
复制
在执行上述语句之后，可使用DESCRIBE shippers;命令查看表结构。
在MySQL命令行，它看起来如下:
±-------------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-------------±------------±-----±----±--------±---------------+
| shipper_id | int | NO | PRI | NULL | auto_increment |
| shipper_name | varchar(60) | NO | | NULL | |
| phone | varchar(60) | NO | | NULL | |
| fax | varchar(20) | YES | | NULL | |
±-------------±------------±-----±----±--------±---------------+
当向表添加新字段时，如果没有指定NULL或NOT NULL，默认为指定NULL。
默认情况下，MySQL在末尾添加新字段。但是，如果想在特定字段之后添加新字段，可以使用AFTER子句，如下所示:
ALTER TABLE shippers ADD fax VARCHAR(20) AFTER shipper_name;
复制
MySQL提供了另一个FIRST子句，可以在表的开头添加新字段。只需把前面示例中的AFTER替换为FIRST子句，就会把字段fax添加到shippers表的开头。
改变列的位置
在MySQL中，如果已经创建好了一个表，想调整字段位置，可用以下语法更改:
ALTER TABLE table_name
MODIFY column_name column_definition AFTER column_name;
复制
下面的语句将shippers表中的字段fax放在shipper_name之后。
ALTER TABLE shippers MODIFY fax VARCHAR(20) AFTER shipper_name;
复制
添加约束
shippers表phone字段应该是唯一的，但目前没有唯一性约束。
给字段添加唯一性约束的基本语法如下:
ALTER TABLE table_name ADD UNIQUE (column_name,…);
复制
下面的语句为phone字段添加了唯一性约束。
ALTER TABLE shippers ADD UNIQUE (phone);
复制
执行此语句后，如果插入一个重复的电话号码，将报错。
类似地，如果表没有主键，可以添加一个:
ALTER TABLE table_name ADD PRIMARY KEY (column_name,…);
复制
下面的语句将主键约束添加到shipper_id列(如果没有定义)。
ALTER TABLE shippers ADD PRIMARY KEY (shipper_id);
复制
删除字段
从表中删除字段的基本语法如下:
ALTER TABLE table_name DROP COLUMN column_name;
复制
下面的语句将从shippers表中删除字段fax。
ALTER TABLE shippers DROP COLUMN fax;
复制
执行了上面语句之后，表结构如下:
±-------------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-------------±------------±-----±----±--------±---------------+
| shipper_id | int | NO | PRI | NULL | auto_increment |
| shipper_name | varchar(60) | NO | | NULL | |
| phone | varchar(20) | NO | UNI | NULL | |
±-------------±------------±-----±----±--------±---------------+
更改字段的数据类型
在SQL Server中，可以使用ALTER COLUMN子句修改列的数据类型，如下所示:
ALTER TABLE table_name ALTER COLUMN column_name new_data_type;
复制
MySQL不支持ALTER COLUMN语法，而是支持等价的MODIFY子句，您可以使用该子句修改字段数据类型，如下所示:
ALTER TABLE table_name MODIFY column_name new_data_type;
复制
下面的语句将shippers表中phone字段的数据类型，从VARCHAR改为CHAR，并将长度从20改为15。
ALTER TABLE shippers MODIFY phone CHAR(15);
复制
类似地，MySQL中，可以用MODIFY子句修改字段，例如：设置是否允许空值，如下所示:
ALTER TABLE shippers MODIFY shipper_name CHAR(15) NOT NULL;
复制
重命名表
MySQL中重命名现有表的基本语法如下:
ALTER TABLE current_table_name RENAME new_column_name;
复制
以下语句重命名shippers表为shipper。
ALTER TABLE shippers RENAME shipper;
复制
也可以用RENAME TABLE语句达到同样效果，如下所示:
RENAME TABLE shippers TO shipper;
SQL Alias(别名)
2019年5月16日

本章介绍在SQL语句中，如何为表或字段添加简短别名。
定义表的别名
当在一个查询语句中有多个表时，需要在字段前面加表名，以避免不同表中的同名字段冲突。但是，如果表名很长，并且出现多次，编写查询语句将很繁琐。
可以为每个表定义简短的别名，使用别名简化查询语句。
让我们看看下面的employees和departments表。
employees
±-------±-------------±-----------±--------+
| emp_id | emp_name | hire_date | dept_id |
±-------±-------------±-----------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
±-------±-------------±-----------±--------+
departments
±--------±-----------------+
| dept_id | dept_name |
±--------±-----------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
±--------±-----------------+
下面的查询，通过使用公共dept_id字段将employees和departments表连接在一起，检索员工的id、名称和部门名称。
SELECT employees.emp_id, employees.emp_name, departments.dept_name
FROM employees LEFT JOIN departments
ON employees.dept_id = departments.dept_id ORDER BY emp_id;
复制
以下是使用表别名后的精简版本:
SELECT t1.emp_id, t1.emp_name, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_id;
复制
如果您执行这些语句中的任何一条，将得到相同的输出，如下所示:
±-------±----------------±-------------------+
| emp_id | emp_name | dept_name |
±-------±----------------±-------------------+
| 1 | Ethan Hunt | Human Resources |
| 2 | Tony Montana | Administration |
| 3 | Sarah Connor | Sales |
| 4 | Rick Deckard | Finance |
| 5 | Martin Blank | NULL |
±-------±----------------±-------------------+
可以看到，通过使用表别名，可以节省很多输入工作。
请查看SQL join部分，以了解更多关于表连接的信息。
为字段定义别名
在MySQL中，有时查询结果中的字段名称不适合人类阅读，可以为这些字段指定别名。
考虑下面的查询，其中使用了一个表达式来重新格式化hire_date字段中的日期:
– Syntax for MySQL Database
SELECT emp_name, DATE_FORMAT(hire_date, ‘%M %e, %Y’) FROM employees;
复制
如果你执行上面的语句，会得到这样的输出:
±-------------±------------------------------------+
| emp_name | DATE_FORMAT(hire_date, ‘%M %e, %Y’) |
±-------------±------------------------------------+
| Ethan Hunt | May 1, 2001 |
| Tony Montana | July 15, 2002 |
| Sarah Connor | October 18, 2005 |
| Rick Deckard | January 3, 2007 |
| Martin Blank | June 24, 2008 |
±-------------±------------------------------------+
最后一列的名称很长，可以为它指定别名，如下所示:
– Syntax for MySQL Database
SELECT emp_name, DATE_FORMAT(hire_date, ‘%M %e, %Y’) AS hire_date
FROM employees;
复制
如果您执行上述语句，将得到更加简洁易读的输出，如下所示:
±-------------±-----------------+
| emp_name | hire_date |
±-------------±-----------------+
| Ethan Hunt | May 1, 2001 |
| Tony Montana | July 15, 2002 |
| Sarah Connor | October 18, 2005 |
| Rick Deckard | January 3, 2007 |
| Martin Blank | June 24, 2008 |
±-------------±-----------------+
注意: GROUP BY、ORDER BY、HAVING子句可以使用别名，WHERE子句不能使用别名。
SQL GROUP BY
2019年5月16日

对行分组
GROUP BY子句与SELECT语句一起使用，使用聚合函数按某个字段对结果集中的行分组。
让我们看下面的employees和departments表。
employees

±-------±-------------±-----------±--------+
| emp_id | emp_name | hire_date | dept_id |
±-------±-------------±-----------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
±-------±-------------±-----------±--------+

departments
±--------±-----------------+
| dept_id | dept_name |
±--------±-----------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
±--------±-----------------+
现在，我们要查询每个部门的员工总数。
可以使用GROUP BY子句和SELECT语句查询，如下所示:
SELECT t1.dept_name, count(t2.emp_id) AS total_employees
FROM departments AS t1 LEFT JOIN employees AS t2
ON t1.dept_id = t2.dept_id
GROUP BY t1.dept_name;
复制
如果你执行上面的语句，你会得到这样的输出:
±------------------±----------------+
| dept_name | total_employees |
±------------------±----------------+
| Administration | 1 |
| Customer Service | 0 |
| Finance | 1 |
| Human Resources | 1 |
| Sales | 1 |
±------------------±----------------+
注意:
注意: 在SELECT语句中，GROUP BY子句必须出现在FROM和WHERE子句之后，ORDER BY子句之前。
SQL HAVING
2019年5月16日

本章将学习如何过滤GROUP by子句返回的组。
根据条件过滤组
HAVING子句通常与GROUP BY子句一起使用，以指定组的筛选条件。HAVING子句必须与SELECT语句一起使用。
让我们看一下下面的employees和departments表。
employees

±-------±-------------±-----------±--------+
| emp_id | emp_name | hire_date | dept_id |
±-------±-------------±-----------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
±-------±-------------±-----------±--------+

departments
±--------±-----------------+
| dept_id | dept_name |
±--------±-----------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
±--------±-----------------+
现在，我们要找出那些没有员工的部门。
可以用HAVING子句与GROUP BY子句，就像这样:
SELECT t1.dept_name, count(t2.emp_id) AS total_employees
FROM departments AS t1 LEFT JOIN employees AS t2
ON t1.dept_id = t2.dept_id
GROUP BY t1.dept_name
HAVING total_employees = 0;
复制
如果你执行上面的语句，会得到这样的输出:
±-----------------±----------------+
| dept_name | total_employees |
±-----------------±----------------+
| Customer Service | 0 |
±-----------------±----------------+
HAVING子句类似于WHERE子句，但只适用于整个组，而WHERE子句适用于单独的行。
SELECT查询可以同时包含WHERE和HAVING子句，但是在这种情况下，WHERE子句必须出现在GROUP BY子句之前，而HAVING子句必须出现在GROUP BY子句之后，ORDER BY子句之前。
SQL 视图
2019年5月16日

本章将学习如何使用SQL创建、更新和删除视图。
创建视图以简化表的访问
视图是一个虚拟表，它的定义存储在数据库中。但是，与表不同，视图实际上不存储任何实际数据。视图提供了一种在数据库中存储常用复杂查询的途经。
可以使用SELECT语句访问视图，就像访问普通表一样。
视图还可以用作一种安全机制，允许用户通过视图访问数据，而不是让他们直接访问表。
语法
视图是使用CREATE VIEW语句创建的。
CREATE VIEW view_name AS select_statement;
复制
让我们看看下面的employees和departments表。
employees

±-------±-------------±-----------±--------+
| emp_id | emp_name | hire_date | dept_id |
±-------±-------------±-----------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
±-------±-------------±-----------±--------+

departments
±--------±-----------------+
| dept_id | dept_name |
±--------±-----------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
±--------±-----------------+
假设您需要检索员工的id、姓名、部门名称，那么您需要执行LEFT JOIN操作，如下所示:
SELECT t1.emp_id, t1.emp_name, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;
复制
执行上述查询后，将得到如下输出:
±-------±-------------±----------------+
| emp_id | emp_name | dept_name |
±-------±-------------±----------------+
| 1 | Ethan Hunt | Human Resources |
| 2 | Tony Montana | Administration |
| 3 | Sarah Connor | Sales |
| 4 | Rick Deckard | Finance |
| 5 | Martin Blank | NULL |
±-------±-------------±----------------+
但是，当您想要访问这些记录时，需要再次键入整个查询。如果经常执行这样的操作，会非常不方便。
可以创建一个视图，使查询结果更容易被访问，如下所示:
CREATE VIEW emp_dept_view AS
SELECT t1.emp_id, t1.emp_name, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;
复制
现在您可以使用视图emp_dept_view访问相同的记录，如下所示:
SELECT * FROM emp_dept_view;
复制
可以看到视图很方便。
视图总是显示最新的数据。每次查询视图时，数据库引擎都会再次执行与视图关联的SQL查询，重新加载数据。
替换现有视图
在MySQL中，如果想替换已有视图，当然可以删除该视图并创建新的视图，但也可以在CREATE VIEW语句中使用OR REPLACE子句来达到同样效果，如下所示:
CREATE OR REPLACE VIEW view_name AS select_statement;
复制
当在CREATE VIEW语句中使用OR REPLACE子句时，如果视图不存在，它将创建新视图，否则将替换现有视图。
下面的SQL语句，将通过向视图emp_dept_view添加新的列salary，来更改视图定义。
– Syntax for MySQL Database
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT t1.emp_id, t1.emp_name, t1.salary, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;
复制
更新视图后，如果执行以下语句:
SELECT * FROM emp_dept_view ORDER BY emp_id;
复制
您将在结果输出中看到新增字段salary，如下所示:
±-------±-------------±-------±----------------+
| emp_id | emp_name | salary | dept_name |
±-------±-------------±-------±----------------+
| 1 | Ethan Hunt | 5000 | Human Resources |
| 2 | Tony Montana | 6500 | Administration |
| 3 | Sarah Connor | 8000 | Sales |
| 4 | Rick Deckard | 7200 | Finance |
| 5 | Martin Blank | 5600 | NULL |
±-------±-------------±-------±----------------+
SQL Server不支持OR REPLACE子句，要替换视图，需删除该视图然后创建新视图。
通过视图更新数据
理论上，除了SELECT语句外，还可以对视图执行INSERT、UPDATE和DELETE。然而，并不是所有的视图都是可更新的，也就是说，能够修改底层源表的数据。
视图是否可更新有一些限制。一般而言，如果一个视图包含下列任何一项，则该视图不可更新:
• 有DISTINCT、GROUP BY、HAVING子句
• 聚合函数，如AVG()、COUNT()、SUM()、MIN()、MAX()等
• 有UNION、UNION ALL、CROSS JOIN、EXCEPT或INTERSECT运算符
• WHERE子查询中引用了FROM子句中的表
下面的语句将更新emp_id等于1的员工的工资。
UPDATE emp_dept_view SET salary = ‘6000’
WHERE emp_id = 1;
复制
对于可插入性，视图必须包含源表中没有默认值的所有字段。类似地，为了可更新，视图中的每个可更新字段必须对应于源表中的一个可更新字段。
删除视图
如果不再需要视图，可以使用DROP view语句将其从数据库中删除，如下所示:
DROP VIEW view_name;
复制
下面的命令将从数据库中删除视图emp_dept_view。
DROP VIEW emp_dept_view;
SQL INDEX(索引)
2019年5月17日

本章将学习如何在表上创建索引，以提高数据库的性能。
索引是什么
索引是与表关联的数据结构，它根据一个或多个列(索引键)中的值，提供对表中行的快速访问。
假设，您的数据库中有一个customers表，要找出名称以字母A开头的所有客户：
SELECT cust_id, cust_name, address FROM customers
WHERE cust_name LIKE ‘A%’;
复制
执行查询时，数据库引擎必须逐行扫描customers表，检查cust_name列的内容。如果表包含数百万行，这个查询可能耗费很长时间。
可以通过对表添加索引来加快查询速度。
创建索引
可以使用CREATE INDEX语句创建索引:
CREATE INDEX index_name ON table_name (column_name);
复制
例如，要在customers表的cust_name列上创建索引，可以使用:
CREATE INDEX cust_name_idx ON customers (cust_name);
复制
默认情况下，索引允许条目重复，并按升序对条目进行排序。如果要生成唯一性的索引，可在CREATE后跟UNIQUE，如下所示:
CREATE UNIQUE INDEX cust_name_idx
ON customers (cust_name);
复制
在MySQL中，可以查看某个表的索引，如下所示:
SHOW INDEXES FROM customers \G
复制
注意: 用”\G”而不是”;”的作用是：当结果太宽不适合当前窗口，则垂直显示结果而不是以通常的表格形式显示。
创建多列索引
还可以创建跨越多个列的索引。
例如，假设数据库中有一个名为users的表，包含first_name和last_name字段，这些字段经常被查询，那么可以在这两个字段上创建一个多列索引来提高性能，如下所示:
CREATE INDEX user_name_idx ON users (first_name, last_name);
复制
数据库索引可以理解为书籍的目录，可以帮助你快速定位书中的某个章节。
索引的负面效应
创建索引应该小心。因为，每次从表中添加、更新或删除一行时，必须修改该表上的所有索引。因此，索引越多，服务器需要做的工作就越多，最终导致性能下降。
以下是创建索引时，可以遵循的一些基本准则:
• 为频繁检索的字段创建索引
• 不要为很少检索的字段创建索引
• 为用于连接的字段创建索引，以提高连接查询性能
• 包含太多空值的字段不宜建索引
此外，小表不需要索引。对于小表，数据库服务器扫描表通常比先查看索引更快。
大多数数据库系统，如MySQL、SQL server等，在创建表时自动为主键和唯一性字段创建索引。
删除索引
下面语句删除索引。
DROP INDEX index_name ON table_name;
复制
下面语句将从customers表中删除索引cust_name_idx。
DROP INDEX cust_name_idx ON customers;
复制
此外，如果删除一个表，那么所有相关的索引也将被删除。
警告: 在删除索引之前，你应该清楚在干什么。一般来说，永远不要盲目地创建或删除索引。
SQL 日期与时间
2019年5月17日

本章将学习如何使用SQL处理日期和时间。
日期和时间操作
除了字符串和数字，通常还需要在数据库中存储日期和时间，例如：用户的出生日期、员工的雇佣日期等等。
这种类型的数据称为时态数据，每个数据库引擎都有存储它们的默认存储格式和数据类型。下表显示了MySQL中日期和时间相关的数据类型。
类型 默认格式 取值范围
DATE YYYY-MM-DD 1000-01-01 to 9999-12-31
TIME HH:MM:SS or HHH:MM:SS -838:59:59 to 838:59:59
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00 to 2037-12-31 23:59:59
YEAR YYYY 1901 to 2155
• DATE值采用YYYY-MM-DD格式，其中YYYY表示年份(4位)，MM和DD分别表示月份和日期部分(2位以0开头)。
• TIME值通常采用HH:MM:SS格式，其中HH、MM和SS分别表示时、分、秒。
下面的语句演示了如何在表中插入日期值:
INSERT INTO employees (emp_name, hire_date, salary)
VALUES (‘Adam Smith’, ‘2015-06-24’, 4500);
复制
跟踪行创建时间与修改时间
在处理大型应用程序的数据库时，经常需要存储某一行的创建时间或最后修改时间，例如，存储用户注册时间，或用户最后访问时间，等等。
在MySQL中，可以使用NOW()函数插入当前时间戳，如下所示:
– Syntax for MySQL Database
INSERT INTO users (name, birth_date, created_at)
VALUES (‘Bilbo Baggins’, ‘1998-04-16’, NOW());
复制
但是，如果不希望手动插入当前时间，则可以简单使用TIMESTAMP和DATETIME数据类型的自动初始化和自动更新属性。
要自动生成时间戳，在相关字段定义中设置默认值为CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP，如下所示:
– Syntax for MySQL Database
CREATE TABLE users (
id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL UNIQUE,
birth_date DATE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
复制
DATETIME的自动初始化和自动更新只在MySQL 5.6.5或更高版本中支持。旧版本请使用TIMESTAMP。
提取部分日期或时间
某些情况下，你可能只想获取部分日期或时间，例如：年份、月份等。MySQL中，可以使用一组专用函数提取部分时间值，例如YEAR()、MONTH()、DAYOFMONTH()、MONTHNAME()、DAYNAME()、HOUR()、MINUTE()、SECOND()等。
下面的SQL语句将提取birth_date列值的年份部分，例如，用户的birth_date是1987-01-14，则YEAR(birth_date)将返回1987。
mysql> SELECT name, YEAR(birth_date) FROM users;
复制
类似地，您可以使用函数DAYOFMONTH()来获取日期，例如，用户的birth_date是1986-10-06，则DAYOFMONTH(birth_date)将返回6。
mysql> SELECT name, DAYOFMONTH(birth_date) FROM users;
复制
格式化日期或时间
如果希望结果集中的日期格式更具描述性和可读性，可以使用DATE_FORMAT()和TIME_FORMAT()函数来格式化。
下面的SQL语句使用易读格式格式化users表的birth_date字段，例如：1987-1-14变为1987年1月14日。
mysql> SELECT name, DATE_FORMAT(birth_date, ‘%M %e, %Y’) FROM users;
SQL 复制表
2019年5月17日

本章将学习如何创建表的副本。
完整复制表
有时想创建一个表的精确副本，以便在不影响原始表的情况下，测试或执行某些操作。
下面将介绍完整复制表的步骤。
步骤1: 创建一个空表
根据原始表的定义创建一个空表，除了复制字段，还复制了字段属性和索引:
CREATE TABLE new_table LIKE original_table;
复制
步骤2: 将数据插入表中
用原始表中的数据填充空表:
INSERT new_table SELECT * FROM original_table;
复制
让我们使用MySQL命令行工具演示一下表的完整复制。
假设数据库中有一个employees表，其中包含以下记录:
±-------±-------------±-----------±-------±--------+
| emp_id | emp_name | hire_date | salary | dept_id |
±-------±-------------±-----------±-------±--------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
±-------±-------------±-----------±-------±--------+
执行以下SQL语句，它将基于现有employees数据库表的定义，创建一个空表employees_clone。
mysql> CREATE TABLE employees_clone LIKE employees;
复制
现在，执行另一条SQL语句，它将employees表中的所有记录插入employees_clone表。
mysql> INSERT employees_clone SELECT * FROM employees;
复制
执行后，您将得到employees_clone表，它是employees表的精确副本。
简单复制
如果您只是想简单复制一个表，无需复制字段属性和索引，可以使用简单的一行语句:
CREATE TABLE new_table SELECT * FROM original_table;
复制
下面的命令创建employees表的简单副本。
mysql> CREATE TABLE employees_dummy SELECT * FROM employees;
复制
使用CREATE TABLE … SELECT语法简单复制表，只复制表结构和数据。
SQL 临时表
2019年5月17日

本章将学习如何使用SQL创建临时表。
创建临时表
临时表是仅对当前会话可见的表，并且在创建它的会话关闭时自动删除。
由于临时表不是永久存储在数据库中，当只需要一个用于短时测试的表时，可以用临时表。
语法
CREATE TEMPORARY TABLE语句用于创建临时表。
CREATE TEMPORARY TABLE table_name (column definitions);
复制
除了加TEMPORARY关键字，其他与创建普通表的操作一样。
创建表的临时副本
当只想测试SQL查询而不想影响数据库时，临时表非常有用。让我们在MySQL数据库中创建一个现有表的临时副本。
在MySQL命令提示符下执行以下命令:
mysql> CREATE TEMPORARY TABLE persons SELECT * FROM persons;
复制
上面的语句从源表persons的结果集中创建一个名为persons的临时表。由于它是persons表的临时副本，您可以执行任何操作，比如插入、更新或删除，而不必担心影响原表persons。
临时表可以与原表同名。如果临时表原表同名，则原表将被隐藏，直到临时表被删除为止。
由于临时表是绑定会话的，两个不同的会话可以使用相同的临时表名，不会有冲突。
删除临时表
当数据库连接/会话关闭时，会话中的临时表将自动删除。如果想手动删除临时表，可以使用DROP TEMPORARY TABLE语句，如下所示:
mysql> DROP TEMPORARY TABLE persons;
复制
上述语句将删除临时表persons，删除后，原表person就变得可见了。
SQL 子查询
2019年5月17日

本章将学习如何将查询嵌入到另一个查询中。
什么是子查询?
子查询，也称为嵌套查询或子选取，是嵌入在另一个查询的WHERE或HAVING子句中的SELECT查询。子查询返回的数据，由外部语句使用时，使用方式与普通值相同。
子查询提供了一种简单而有效的方法，从一个查询结果集中再进行查询。子查询与普通查询基本相同，有一些要注意的地方:
• 子查询必须始终出现在括号内。
• 子查询通常只返回一个字段。这意味着不能在子查询中使用SELECT *，除非表只有一列。如果目的是行比较，子查询可以返回多个字段。
• 使用像IN、NOT IN这样的多值操作符时，子查询必须返回多行。
• 子查询中不能使用UNION，只允许有一个SELECT语句。
子查询最常与SELECT语句一起使用，但是也可以在INSERT、UPDATE或DELETE语句中使用。
SELECT语句使用子查询
下面的语句，要从订单表中，返回订单值超过5000美元的客户详细信息。子查询中使用了DISTINCT关键字，消除查询结果中重复的cust_id值。
SELECT * FROM customers
WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders
WHERE order_value > 5000);
复制
外部SELECT、INSERT、UPDATE、DELETE语句的WHERE或HAVING子句中可以嵌入子查询，一个子查询中也可以嵌入子查询。
INSERT语句使用子查询
子查询也可以与INSERT语句一起使用。请看例子:
INSERT INTO premium_customers
SELECT * FROM customers
WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders
WHERE order_value > 5000);
复制
上面的语句使用子查询返回的数据，将高级客户的记录插入到premium_customers表中。这里的高级客户是订购价值超过5000美元的客户。
INSERT … SELECT语句可参考SQL 复制表
UPDATE语句使用子查询
子查询可以与UPDATE语句一起使用，如下所示:
UPDATE orders
SET order_value = order_value + 10
WHERE cust_id IN (SELECT cust_id FROM customers
WHERE postal_code = 75016);
复制
上述语句，对于邮政编码为75016的客户，将他们的订单值增加10美元。
DELETE语句中使用子查询
同样，可以在DELETE语句中使用子查询，如下:
DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM order_details
WHERE product_id = 5);
复制
上面语句，对于product_id为5的产品订单，将其从orders表中删除。
SQL 注入
2019年5月17日

本章将学习如何避免常见的数据库漏洞。
什么是SQL注入?
SQL注入是一种攻击，当web服务器从浏览器接受用户输入时，如果web应用程序有漏洞，攻击者有可能注入恶意SQL代码。
SQL注入攻击可能引起敏感信息暴露，如用户的联系电话、电子邮件地址、信用卡信息等。利用它，攻击者甚至能绕过身份验证，访问整个数据库。
SQL注入的工作原理
考虑下面的SQL语句，当web应用程序中的用户登录时，验证用户名和密码。
SELECT * FROM users WHERE username=‘username_val’ AND password=‘password_val’;
复制
这里，username_val和password_val分别表示用户输入的用户名和密码。如果用户输入“john”作为用户名，“123”作为密码，那么结果语句将是:
SELECT * FROM users WHERE username=‘john’ AND password=‘123’;
复制
但是假设用户是攻击者，输入了类似于’ OR ‘x’=‘x的值
在这种情况下，上面的SQL查询将被构造为:
SELECT * FROM users WHERE username=’’ OR ‘x’=‘x’ AND password=’’ OR ‘x’=‘x’;
复制
这个语句是一个有效的SQL语句，因为’x’='x’总是成立，所以查询将返回users表中的所有行。可以看到攻击者使用一个小技巧，就可以轻松地访问数据库的所有敏感信息。
如果users表非常大，包含数百万行数据，这条语句还可能导致拒绝服务攻击(DDoS攻击)，方法是消耗系统资源，使你的应用程序不能正常提供服务。
警告: 如果DELETE或UPDATE语句被注入了，后果更严重，这将导致数据被删除或修改。
防止SQL注入
始终验证用户输入。永远不要直接使用用户输入的内容来构建SQL语句。如果使用PHP和MySQL，可以使用mysqli_real_escape_string()函数创建一个合法的SQL字符串。
下面是一个使用PHP和MySQL进行用户身份验证的简单示例，演示了如何在获取用户输入的同时防止SQL注入。

<?php // Starting session session_start(); /* Attempt MySQL server connection. Assuming you are running MySQL server with default setting (user 'root' with no password) */ $link = mysqli_connect("localhost", "root", "", "demo"); // Check connection if($link === false){ die("ERROR: Could not connect to database."); } // Escape user inputs for security $username_val = mysqli_real_escape_string($link, $_POST['username']); $password_val = mysqli_real_escape_string($link, $_POST['password']); if(isset($username_val, $password_val)){ // Attempt select query execution $sql = "SELECT * FROM users WHERE username='" . $username_val . "' AND password='" . $password_val . "'"; if($result = mysqli_query($link, $sql)){ if(mysqli_num_rows($result) == 1){ // User is authenticated do your stuff here $row = mysqli_fetch_array($result); /* Holding values in session variable so that it can be accessed later within the same session reference */ $_SESSION['user_id'] = $row['user_id']; $_SESSION['first_name'] = $row['first_name']; header('Location: welcome.php'); } else{ echo "ERROR: Invalid username or password."; } } else{ echo "ERROR: Something went wrong. Please try again."; } } // Close connection mysqli_close($link); ?>

SQL 函数
2019年5月17日

SQL有许多内置函数，可以用来操作数据。
SQL聚合函数
SQL聚合函数对一组值执行计算并返回一个值。下表总结了一些有用的聚合函数:
函数 描述
AVG() 返回所有值的平均值
SUM() 返回所有值的和
COUNT() 返回结果集中的行数
MAX() 返回最大值
MIN() 返回最小值
SQL 字符串函数
SQL 字符串函数对字符串执行操作，并返回字符串或数值。下表总结了一些有用的字符串函数:
函数 描述
CONCAT() 通过连接两个或多个字符串值返回一个字符串。
CONCAT_WS() 通过将两个或多个字符串值与分隔符连接，返回一个字符串。
FORMAT() 返回使用指定格式格式化的值。
LOWER() 将字符串转换为小写。
UPPER() 将字符串转换为大写。
TRIM() 从字符串中删除前导和尾随空格。
REVERSE() 返回字符串值的逆序。
SUBSTRING() 从字符串返回子字符串。
SQL日期函数
日期函数用于操作日期值。
MySQL日期函数
下表总结了MySQL一些最重要的内置日期函数:
函数 描述
NOW() 返回当前日期和时间。
CURDATE() 返回当前日期。
CURTIME() 返回当前时间
DATE() 提取日期时间表达式的日期部分。
DAY() 返回几号(0-31)。
DAYNAME() 返回星期几。
MONTH() 返回指定日期的月份(1-12)。
MONTHNAME() 返回月份名称。
YEAR() 返回年份
DATE_FORMAT() 以指定格式显示日期和时间值。
EXTRACT() 提取日期的一部分。
DATE_ADD() 将指定的时间间隔添加到日期值。
DATE_SUB() 从日期值中减去指定的时间间隔。
DATEDIFF() 返回两个日期之间的天数
这些函数返回的日期和时间是运行数据库的计算机的日期和时间。
SQL Server日期函数
下表总结了一些最重要的SQL Server内置日期函数:
函数 描述
GETDATE() 返回当前日期和时间。
DATEPART() 返回日期的指定部分，例如datepart(year，’2016-10-25′)返回2016。
DAY() 返回每个月的日期(0-31)。
MONTH() 返回指定日期的月份(0-12)。
YEAR() 返回指定日期的年份。
DATEADD() 从日期中添加或减去指定的时间间隔。
DATEDIFF() 返回两个指定日期之间的日期或时间。
CONVERT() 以其他格式显示日期和时间值。