MySQL 部分基础知识

MySQL

数据库语法

创建数据库，使用 create 命令创建数据库，语法如下：CREATE DATABASE db_mame;。

选择数据库，在你能执行任意数据库操作前，需要选择一个数据库。为此，可使用 USE 关键字。语法如下：USE db_name。

删除数据库，在删除数据库过程中，务必要十分谨慎，因为在执行删除命令后，所有数据将会消失。drop 命令删除数据库：DROP database db_name;。

显示可用的数据库列表。语法：SHOW DATABASES;。

数据表语法

创建数据表，创建 MySQL 数据表需要以下信息：表名，表字段名，定义每个表字段。以下为创建 MySQL 数据表的 SQL 通用语法：CREATE TABLE table_name (column_name column_type);。

显示数据表，SHOW TABLES; 返回当前选择的数据库内可用表的列表。

SHOW 也可以用来显示表列：SHOW COLUMNS FROM table_name;，它对每个字段返回一行，行中包含字段名、数据类型、是否允许 NULL、键信息、默认值以及其他信息。

• MySQL 支持用 DESCRIBE 作为 SHOW COLUMNS FROM 的一种快捷方式。
• 所支持的其他SHOW语句还有：
  • SHOW STATUS，用于显示广泛的服务器状态信息；
  • SHOW CREATE DATABASE 和 SHOW CREATETABLE，分别用来显示创建特定数据库或表的MySQL语句；
  • SHOW GRANTS，用来显示授予用户（所有用户或特定用户）的安全权限；
  • SHOW ERRORS 和 SHOW WARNINGS，用来显示服务器错误或警告消息。

重命名数据表，使用 RENAME TABLE 语句可以重命名一个表：ALTER TABLE customers1 RENAME TO customers。

删除数据表，MySQL 中删除数据表是非常容易操作的， 但是你再进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。删除 MySQL 数据表的通用语法：DROP TABLE table_name;。

• MySQL 删除表的几种情况：
  • DROP TABLE table_name;: 删除表全部数据和表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM。
  • truncate table table_name;：删除表全部数据，保留表结构，立刻释放磁盘空间 ，不管是 Innodb 和 MyISAM。
  • DELETE FROM table_name：删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间

插入数据

MySQL 表中使用 INSERT INTO SQL 语句来插入数据。

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

如果数据是字符型，必须使用单引号或者双引号，如：“value”。

查询数据

MySQL 数据库使用 SQL SELECT 语句从表中检索一个或多个数据列。为了使用SELECT检索表数据，必须至少给出两条信息——想选择什么，以及从什么地方选择。在 MySQL 数据库中查询数据通用的 SELECT 语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

• 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用 WHERE 语句来设定查询条件。
• SELECT 命令可以读取一条或者多条记录。
• 可以使用星号（*）来代替其他字段，SELECT 语句会返回表的所有字段数据。
• 可以使用 WHERE 语句来包含任何条件。
• 可以使用 LIMIT 属性来设定返回的记录数。
• 可以通过 OFFSET 指定 SELECT 语句开始查询的数据偏移量。默认情况下偏移量为0。

以下语句将返回数据表 table_name 的所有记录：select * from table_name;。

检索不同的行：使用 DISTINCT 关键字，顾名思义，此关键字指示 MySQL 只返回不同的值。DISTINCT 关键字应用于所有列而不仅是前置它的列。

WHERE 子句

只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN
FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2...

• 查询语句中你可以使用一个或者多个表，表之间使用逗号","分割，并使用 WHERE 语句来设定查询条件。
• 可以在 WHERE 子句中指定任何条件。
• 可以使用 AND 或者 OR 指定一个或多个条件。
• WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
• WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

WHERE 子句操作符如下：=，<>，!=，<，<=，>，>=，BETWEEN。

为了检查某个范围的值，可使用 BETWEEN 操作符。其语法与其他 WHERE 子句的操作符稍有不同，因为它需要两个值，即范围的开始值和结束值。这两个值必须用 AND 关键字分隔。

SELECT 语句有一个特殊的 WHERE 子句，可用来检查具有 NULL 值的列。这个 WHERE 子句就是 IS NULL 子句。

一个实例：SELECT * from table_name WHERE prod_name='phone';。

组合 WHERE 子句，AND 和 OR 操作符

为了进行更强的过滤控制，MySQL 允许给出多个 WHERE 子句。这些子句可以两种方式使用：以 AND 子句的方式或 OR 子句的方式使用。

为了通过不止一个列进行过滤，可使用 AND 操作符给 WHERE 子句附加条件。OR 操作符与 AND 操作符不同，它指示 MySQL 检索匹配任一条件的行。

计算次序

WHERE 可包含任意数目的 AND 和 OR 操作符。允许两者结合以进行复杂和高级的过滤。但是，组合 AND 和 OR 带来了一个有趣的问题。SQL（像多数语言一样）在处理 OR 操作符前，优先处理 AND 操作符。由于 AND 在计算次序中优先级更高，操作符被错误地组合了。此问题的解决方法是使用圆括号明确地分组相应的操作符。任何时候使用具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号明确地分组操作符。

SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

IN 操作符

圆括号在 WHERE 子句中还有另外一种用法。IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN 取合法值的由逗号分隔的清单，全都括在圆括号中。

SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;

使用 IN 操作符的优点：

• 在使用长的合法选项清单时，IN 操作符的语法更清楚且更直观。
• 在使用 IN 时，计算的次序更容易管理（因为使用的操作符更少）。
• IN 操作符一般比 OR 操作符清单执行更快。
• IN 的最大优点是可以包含其他 SELECT 语句，使得能够更动态地建立 WHERE 子句。

NOT 操作符

WHERE 子句中的 NOT 操作符有且只有一个功能，那就是否定它之后所跟的任何条件。

SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;

MySQL 支持使用 NOT 对 IN、BETWEEN 和 EXISTS 子句取反。

UPDATE 更新

如果需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。

• 更新表中特定行；
• 更新表中所有行。

基本的 UPDATE 语句由 3 部分组成，分别是：

• 要更新的表；
• 列名和它们的新值；
• 确定要更新行的过滤条件。

以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

• 可以同时更新一个或多个字段。
• 可以在 WHERE 子句中指定任何条件。
• 可以在一个单独表中同时更新数据。

一个例子：

UPDATE customers
SET cust_email = 'tom@gmail.com'
WHERE cust_id - 10001;

UPDATE 语句总是以要更新的表的名字开始。SET 命令用来将新值赋给被更新的列。在更新多个列时，只需要使用单个 SET 命令，每个“列=值”对之间用逗号分隔（最后一列之后不用逗号）。

DELETE 语句

可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。可以两种方式使用 DELETE：

• 从表中删除特定的行；
• 从表中删除所有行。

以下是 SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法：

DELETE FROM table_name [WHERE Clause]

• 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
• 可以在 WHERE 子句中指定任何条件。
• 可以在单个表中一次性删除记录。

当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。

一个例子：

DELETE FROM customers
WHERE cust_id = 10002;

DELETE FROM 要求指定从中删除数据的表名。WHERE 子句过滤要删除的行。如果省略 WHERE 子句，它将删除表中每个客户。DELETE 不需要列名或通配符。DELETE 删除整行而不是删除列。为了删除指定的列，请使用 UPDATE 语句。DELETE 语句从表中删除行，甚至是删除表中所有行。但是，DELETE 不删除表本身。

LIKE 子句

利用通配符可创建比较特定数据的搜索模式。

百分号（%）通配符

SQL LIKE 子句中使用百分号 % 字符来表示任意字符。如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• 可以在 WHERE 子句中指定任何条件。
• 可以在 WHERE 子句中使用LIKE子句。
• 可以使用 LIKE 子句代替等号 =。
• LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
• 可以使用 AND 或者 OR 指定一个或多个条件。
• 可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

一个例子：

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';

此例子使用了搜索模式’jet%’。在执行这条子句时，将检索任意以 jet 起头的词。通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。除了一个或多个字符外，% 还能匹配 0 个字符。% 代表搜索模式中给定位置的 0 个、1 个或多个字符。虽然似乎 % 通配符可以匹配任何东西，但有一个例外，即 NULL。

下划线（_）通配符

下划线的用途与 % 一样，但下划线只匹配单个字符而不是多个字符。

UNION

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。这些组合查询通常称为并（union）或复合查询（compound query）。

有两种基本情况，其中需要使用组合查询：

• 在单个查询中从不同的表返回类似结构的数据；
• 对单个表执行多个查询，按单个查询返回数据。

UNION 的使用很简单。所需做的只是给出每条 SELECT 语句，在各条语句之间放上关键字 UNION。MySQL UNION 操作符语法格式：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

• expression1, expression2, … expression_n: 要检索的列。
• tables: 要检索的数据表。
• WHERE conditions: 可选， 检索条件。
• DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
• ALL: 可选，返回所有结果集，包含重复数据。

一个例子：

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);

UNION 指示 MySQL 执行两条 SELECT 语句，并把输出组合成单个查询结果集。

UNION 规则

• UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔。
• UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
• 列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以隐含地转换的类型。

对组合查询结果排序

SELECT 语句的输出用 ORDER BY 子句排序。在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须出现在最后一条 SELECT 语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条 ORDER BY 子句。但实际上 MySQL 将用它来排序所有 SELECT 语句返回的所有结果。

排序

MySQL 检索出的数据并不是以纯粹的随机顺序显示的。如果不排序，数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序。为了明确地排序用 SELECT 语句检索出的数据，可使用 ORDER BY 子句。ORDER BY 子句取一个或多个列的名字，据此对输出进行排序。为了进行降序排序，必须指定 DESC 关键字。DESC 关键字只应用到直接位于其前面的列名。与 DESC 相反的关键字是 ASC（ASCENDING），在升序排序时可以指定它。但实际上，ASC 没有多大用处，因为升序是默认的（如果既不指定 ASC 也不指定 DESC，则假定为 ASC）。

以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

• 可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
• 可以设定多个字段来排序。
• 可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
• 可以添加 WHERE…LIKE 子句来设置条件。

在按多个列排序时，排序完全按所规定的顺序进行。在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于 WHERE 之后，否则将会产生错误

分组

GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG 等函数。分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

一个例子：

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

使用了 GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY 子句指示 MySQL 分组数据，然后对每个组而不是整个结果集进行聚集。

在具体使用GROUP BY子句前，需要知道一些重要的规定。

• GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
• 如果在 GROUP BY 子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
• GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名。
• 除聚集计算语句外，SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
• 如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
• GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

除了能用 GROUP BY 分组数据外，MySQL 还允许过滤分组，规定包括哪些分组，排除哪些分组。HAVING 子句用于过滤分组。

连接的使用

使用 MySQL 的 JOIN 在两个或多个表中查询数据。可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接，或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

联结的创建非常简单，规定要联结的所有表以及它们如何关联即可。如下对比：

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id

NULL 值处理

MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

为了处理这种情况，MySQL提供了三大运算符:

• IS NULL: 当列的值是 NULL,此运算符返回 true。
• IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
• <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

正则表达式

MySQL 同样也支持其他正则表达式的匹配， MySQL WHERE 子句 中使用 REGEXP 操作符来进行正则表达式匹配。

REGEXP 操作符：^，$，.，[...]，[^...]，p1|p2|p3，*，+，{n}，{n,m}

ALTER 命令

需要修改数据表名或者修改数据表字段时，就需要使用到 MySQL ALTER 命令。为了使用 ALTER TABLE 更改表结构，必须给出下面的信息：

• 在 ALTER TABLE 之后给出要更改的表名（该表必须存在，否则将出错）；
• 所做更改的列表。

ALTER TABLE vendors
ADD vend_phone CHAR(20);

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

• 用新的列布局创建一个新表；
• 使用 INSERT SELECT 语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
• 检验包含所需数据的新表；
• 重命名旧表（如果确定，可以删除它）；
• 用旧表原来的名字重命名新表；
• 根据需要，重新创建触发器、存储过程、索引和外键。

索引

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

普通索引

创建索引：CREATE INDEX indexName ON table_name (column_name);。

如果是 CHAR，VARCHAR 类型，length 可以小于字段实际长度；如果是 BLOB 和 TEXT 类型，必须指定 length。

添加索引：ALTER table tableName ADD INDEX indexName(columnName);。

创建表的时候直接指定：

CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);

删除索引：DROP INDEX [indexName] ON mytable;。

唯一索引

创建索引：CREATE UNIQUE INDEX indexName ON mytable(username(length));。

添加索引：ALTER table mytable ADD UNIQUE [indexName] (username(length));。

创建表的时候直接指定：

CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);

函数

字符串函数，数字函数，日期函数，高级函数。

• 返回字符串 s 的字符数：SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;。
• 求平均值：SELECT AVG(Price) AS AveragePrice FROM Products;。
• 返回当前日期和时间：SELECT CURRENT_TIMESTAMP();。
• 返回 x 的二进制编码：SELECT BIN(15);。

参考文献

[1] Ben Forta. MySQL必知必会[M]. 刘晓霞，钟鸣. 北京：人民邮电出版社. 2009.
[2] 菜鸟教程. https://www.runoob.com/mysql/mysql-tutorial.html