mysql 操作篇
MySQL 创建数据库
我们可以在登陆 MySQL 服务后，使用 create 命令创建数据库，语法如下:
CREATE DATABASE 数据库名;
以下命令简单的演示了创建数据库的过程，数据名为 RUNOOB:
[root@host]# mysql -u root -p Enter password:****** # 登录后进入终端 mysql> create DATABASE RUNOOB;
MySQL 删除数据库
使用普通用户登陆 MySQL 服务器，你可能需要特定的权限来创建或者删除 MySQL 数据库，所以我们这边使用 root 用户登录，root 用户拥有最高权限。
在删除数据库过程中，务必要十分谨慎，因为在执行删除命令后，所有数据将会消失。
drop 命令删除数据库
drop 命令格式：
drop database <数据库名>;
例如删除名为 RUNOOB 的数据库：
mysql> drop database RUNOOB;
MySQL 选择数据库
在你连接到 MySQL 数据库后，可能有多个可以操作的数据库，所以你需要选择你要操作的数据库。
从命令提示窗口中选择MySQL数据库
在 mysql> 提示窗口中可以很简单的选择特定的数据库。你可以使用SQL命令来选择指定的数据库。
实例
以下实例选取了数据库 RUNOOB:
[root@host]# mysql -u root -p Enter password:****** mysql> use RUNOOB; Database changed mysql>
执行以上命令后，你就已经成功选择了 RUNOOB 数据库，在后续的操作中都会在 RUNOOB 数据库中执行。
MySQL 创建数据表
创建MySQL数据表需要以下信息：
- 表名
- 表字段名
- 定义每个表字段
语法
以下为创建MySQL数据表的SQL通用语法：
CREATE TABLE table_name (column_name column_type);
以下例子中我们将在 RUNOOB 数据库中创建数据表runoob_tbl：
CREATE TABLE IF NOT EXISTS `runoob_tbl`( `runoob_id` INT UNSIGNED AUTO_INCREMENT, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` DATE, PRIMARY KEY ( `runoob_id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8;
实例解析：
- 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
- AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
- PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
- ENGINE 设置存储引擎，CHARSET 设置编码。
通过命令提示符创建表
通过 mysql> 命令窗口可以很简单的创建MySQL数据表。你可以使用 SQL 语句 CREATE TABLE 来创建数据表。
实例
以下为创建数据表 runoob_tbl 实例:
root@host# mysql -u root -p Enter password:******* mysql> use RUNOOB; Database changed mysql> CREATE TABLE runoob_tbl( -> runoob_id INT NOT NULL AUTO_INCREMENT, -> runoob_title VARCHAR(100) NOT NULL, -> runoob_author VARCHAR(40) NOT NULL, -> submission_date DATE, -> PRIMARY KEY ( runoob_id ) -> )ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.16 sec) mysql>
MySQL 删除数据表
MySQL中删除数据表是非常容易操作的，但是你在进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。
语法
以下为删除MySQL数据表的通用语法：
DROP TABLE table_name ;
在命令提示窗口中删除数据表
在mysql>命令提示窗口中删除数据表SQL语句为 DROP TABLE ：
实例
以下实例删除了数据表runoob_tbl:
root@host# mysql -u root -p Enter password:******* mysql> use RUNOOB; Database changed mysql> DROP TABLE runoob_tbl; Query OK, 0 rows affected (0.8 sec) mysql>
MySQL 插入数据
MySQL 表中使用 INSERT INTO SQL语句来插入数据。
你可以通过 mysql> 命令提示窗口中向数据表中插入数据，或者通过PHP脚本来插入数据。
语法
以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法：
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
如果数据是字符型，必须使用单引号或者双引号，如："value"。
通过命令提示窗口插入数据
以下我们将使用 SQL INSERT INTO 语句向 MySQL 数据表 runoob_tbl 插入数据
实例
以下实例中我们将向 runoob_tbl 表插入三条数据:
root@host# mysql -u root -p password; Enter password:******* mysql> use RUNOOB; Database changed mysql> INSERT INTO runoob_tbl -> (runoob_title, runoob_author, submission_date) -> VALUES -> ("学习 PHP", "菜鸟教程", NOW()); Query OK, 1 rows affected, 1 warnings (0.01 sec) mysql> INSERT INTO runoob_tbl -> (runoob_title, runoob_author, submission_date) -> VALUES -> ("学习 MySQL", "菜鸟教程", NOW()); Query OK, 1 rows affected, 1 warnings (0.01 sec) mysql> INSERT INTO runoob_tbl -> (runoob_title, runoob_author, submission_date) -> VALUES -> ("JAVA 教程", "RUNOOB.COM", '2016-05-06'); Query OK, 1 rows affected (0.00 sec) mysql>
注意： 使用箭头标记 -> 不是 SQL 语句的一部分，它仅仅表示一个新行，如果一条SQL语句太长，我们可以通过回车键来创建一个新行来编写 SQL 语句，SQL 语句的命令结束符为分号 ;。
在以上实例中，我们并没有提供 runoob_id 的数据，因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。 所以，该字段会自动递增而不需要我们去设置。实例中 NOW() 是一个 MySQL 函数，该函数返回日期和时间。
接下来我们可以通过以下语句查看数据表数据：
读取数据表：
select * from runoob_tbl;
输出结果：
MySQL 查询数据
MySQL 数据库使用SQL SELECT语句来查询数据。
你可以通过 mysql> 命令提示窗口中在数据库中查询数据，或者通过PHP脚本来查询数据。
语法
以下为在MySQL数据库中查询数据通用的 SELECT 语法：
SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M]
- 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
- SELECT 命令可以读取一条或者多条记录。
- 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
- 你可以使用 WHERE 语句来包含任何条件。
- 你可以使用 LIMIT 属性来设定返回的记录数。
- 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
通过命令提示符获取数据
以下实例我们将通过 SQL SELECT 命令来获取 MySQL 数据表 runoob_tbl 的数据：
实例
以下实例将返回数据表 runoob_tbl 的所有记录:
读取数据表：
select * from runoob_tbl;
输出结果：
MySQL WHERE 子句
我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。
如需有条件地从表中选取数据，可将 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 子句中。
下表中实例假定 A 为 10, B 为 20
| 操作符 | 描述 | 实例 |
|---|---|---|
| = | 等号，检测两个值是否相等，如果相等返回true | (A = B) 返回false。 |
| <>, != | 不等于，检测两个值是否相等，如果不相等返回true | (A != B) 返回 true。 |
| > | 大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
| < | 小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
| >= | 大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
| <= | 小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
如果我们想在 MySQL 数据表中读取指定的数据，WHERE 子句是非常有用的。
使用主键来作为 WHERE 子句的条件查询是非常快速的。
如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。
从命令提示符中读取数据
我们将在SQL SELECT语句使用WHERE子句来读取MySQL数据表 runoob_tbl 中的数据：
实例
以下实例将读取 runoob_tbl 表中 runoob_author 字段值为 Sanjay 的所有记录：
SQL SELECT WHERE 子句
SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程';
输出结果：
MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
如下实例:
BINARY 关键字
mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author='runoob.com'; Empty set (0.01 sec) mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author='RUNOOB.COM'; +-----------+---------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+---------------+---------------+-----------------+ | 3 | JAVA 教程 | RUNOOB.COM | 2016-05-06 | | 4 | 学习 Python | RUNOOB.COM | 2016-03-06 | +-----------+---------------+---------------+-----------------+ 2 rows in set (0.01 sec)
实例中使用了 BINARY 关键字，是区分大小写的，所以 runoob_author='runoob.com' 的查询条件是没有数据的。
MySQL UPDATE 更新
如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。
语法
以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
- 你可以同时更新一个或多个字段。
- 你可以在 WHERE 子句中指定任何条件。
- 你可以在一个单独表中同时更新数据。
当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。
通过命令提示符更新数据
以下我们将在 SQL UPDATE 命令使用 WHERE 子句来更新 runoob_tbl 表中指定的数据：
实例
以下实例将更新数据表中 runoob_id 为 3 的 runoob_title 字段值：
SQL UPDATE 语句：
mysql> UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3; Query OK, 1 rows affected (0.01 sec) mysql> SELECT * from runoob_tbl WHERE runoob_id=3; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 3 | 学习 C++ | RUNOOB.COM | 2016-05-06 | +-----------+--------------+---------------+-----------------+ 1 rows in set (0.01 sec)
从结果上看，runoob_id 为 3 的 runoob_title 已被修改。
MySQL DELETE 语句
你可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。
你可以在 mysql> 命令提示符或 PHP 脚本中执行该命令。
语法
以下是 SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法：
DELETE FROM table_name [WHERE Clause]
- 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
- 你可以在 WHERE 子句中指定任何条件
- 您可以在单个表中一次性删除记录。
当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。
从命令行中删除数据
这里我们将在 SQL DELETE 命令中使用 WHERE 子句来删除 MySQL 数据表 runoob_tbl 所选的数据。
实例
以下实例将删除 runoob_tbl 表中 runoob_id 为3 的记录：
DELETE 语句：
mysql> use RUNOOB; Database changed mysql> DELETE FROM runoob_tbl WHERE runoob_id=3; Query OK, 1 row affected (0.23 sec)
MySQL LIKE 子句
我们知道在 MySQL 中使用 SQL SELECT 命令来读取数据， 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。
WHERE 子句中可以使用等号 = 来设定获取数据的条件，如 "runoob_author = 'RUNOOB.COM'"。
但是有时候我们需要获取 runoob_author 字段含有 "COM" 字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。
SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, 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 子句来指定条件。
在命令提示符中使用 LIKE 子句
以下我们将在 SQL SELECT 命令中使用 WHERE...LIKE 子句来从MySQL数据表 runoob_tbl 中读取数据。
实例
以下是我们将 runoob_tbl 表中获取 runoob_author 字段中以 COM 为结尾的的所有记录：
SQL LIKE 语句：
mysql> use RUNOOB; Database changed mysql> SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM'; +-----------+---------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+---------------+---------------+-----------------+ | 3 | 学习 Java | RUNOOB.COM | 2015-05-01 | | 4 | 学习 Python | RUNOOB.COM | 2016-03-06 | +-----------+---------------+---------------+-----------------+ 2 rows in set (0.01 sec)
MySQL UNION 操作符
本教程为大家介绍 MySQL UNION 操作符的语法和实例。
描述
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
语法
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: 可选，返回所有结果集，包含重复数据。
演示数据库
在本教程中，我们将使用 RUNOOB 样本数据库。
下面是选自 "Websites" 表的数据：
mysql> SELECT * FROM Websites;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
下面是 "apps" APP 的数据：
mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name | url | country |
+----+------------+-------------------------+---------+
| 1 | QQ APP | http://im.qq.com/ | CN |
| 2 | 微博 APP | http://weibo.com/ | CN |
| 3 | 淘宝 APP | https://www.taobao.com/ | CN |
+----+------------+-------------------------+---------+
3 rows in set (0.00 sec)
SQL UNION 实例
下面的 SQL 语句从 "Websites" 和 "apps" 表中选取所有不同的country（只有不同的值）：
实例
SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country;
执行以上 SQL 输出结果如下：
注释：UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！
SQL UNION ALL 实例
下面的 SQL 语句使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country（也有重复的值）：
实例
SELECT country FROM Websites UNION ALL SELECT country FROM apps ORDER BY country;
执行以上 SQL 输出结果如下：
带有 WHERE 的 SQL UNION ALL
下面的 SQL 语句使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据（也有重复的值）：
实例
SELECT country, name FROM Websites WHERE country='CN' UNION ALL SELECT country, app_name FROM apps WHERE country='CN' ORDER BY country;
执行以上 SQL 输出结果如下：
MySQL 排序
我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。
如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。
语法
以下是 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 子句
以下将在 SQL SELECT 语句中使用 ORDER BY 子句来读取MySQL 数据表 runoob_tbl 中的数据：
实例
尝试以下实例，结果将按升序及降序排列。
SQL 排序
mysql> use RUNOOB; Database changed mysql> SELECT * from runoob_tbl ORDER BY submission_date ASC; +-----------+---------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+---------------+---------------+-----------------+ | 3 | 学习 Java | RUNOOB.COM | 2015-05-01 | | 4 | 学习 Python | RUNOOB.COM | 2016-03-06 | | 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 | | 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 | +-----------+---------------+---------------+-----------------+ 4 rows in set (0.01 sec) mysql> SELECT * from runoob_tbl ORDER BY submission_date DESC; +-----------+---------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+---------------+---------------+-----------------+ | 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 | | 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 | | 4 | 学习 Python | RUNOOB.COM | 2016-03-06 | | 3 | 学习 Java | RUNOOB.COM | 2015-05-01 | +-----------+---------------+---------------+-----------------+ 4 rows in set (0.01 sec)
读取 runoob_tbl 表中所有数据并按 submission_date 字段的升序排列。
MySQL GROUP BY 语句
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
GROUP BY 语法
SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
实例演示
本章节实例使用到了以下表结构及数据，使用前我们可以先将以下数据导入数据库中。
SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for `employee_tbl` -- ---------------------------- DROP TABLE IF EXISTS `employee_tbl`; CREATE TABLE `employee_tbl` ( `id` int(11) NOT NULL, `name` char(10) NOT NULL DEFAULT '', `date` datetime NOT NULL, `signin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `employee_tbl` -- ---------------------------- BEGIN; INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2'); COMMIT; SET FOREIGN_KEY_CHECKS = 1;
导入成功后，执行以下 SQL 语句：
mysql> set names utf8; mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name | date | signin |
+----+--------+---------------------+--------+
| 1 | 小明 | 2016-04-22 15:25:33 | 1 |
| 2 | 小王 | 2016-04-20 15:25:47 | 3 |
| 3 | 小丽 | 2016-04-19 15:26:02 | 2 |
| 4 | 小王 | 2016-04-07 15:26:14 | 4 |
| 5 | 小明 | 2016-04-11 15:26:40 | 4 |
| 6 | 小明 | 2016-04-04 15:26:54 | 2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)
接下来我们使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录：
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name | COUNT(*) |
+--------+----------+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+--------+----------+
3 rows in set (0.01 sec)
使用 WITH ROLLUP
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：
mysql> SELECT name, SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name | signin_count |
+--------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| NULL | 16 |
+--------+--------------+
4 rows in set (0.00 sec)
其中记录 NULL 表示所有人的登录次数。
我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：
select coalesce(a,b,c);
参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。
以下实例中如果名字为空我们使用总数代替：
mysql> SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | signin_count |
+--------------------------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)
MySQL 连接的使用
在前几章节中，我们已经学会了如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。
本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。
你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类：
- INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
- LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
- RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
本章节使用的数据库结构及数据下载：runoob-mysql-join-test.sql。
在命令提示符中使用 INNER JOIN
我们在RUNOOB数据库中有两张表 tcount_tbl 和 runoob_tbl。两张数据表数据如下：
实例
尝试以下实例：
测试实例数据
mysql> use RUNOOB; Database changed mysql> SELECT * FROM tcount_tbl; +---------------+--------------+ | runoob_author | runoob_count | +---------------+--------------+ | 菜鸟教程 | 10 | | RUNOOB.COM | 20 | | Google | 22 | +---------------+--------------+ 3 rows in set (0.01 sec) mysql> SELECT * from runoob_tbl; +-----------+---------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+---------------+---------------+-----------------+ | 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 | | 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 | | 3 | 学习 Java | RUNOOB.COM | 2015-05-01 | | 4 | 学习 Python | RUNOOB.COM | 2016-03-06 | | 5 | 学习 C | FK | 2017-04-05 | +-----------+---------------+---------------+-----------------+ 5 rows in set (0.01 sec) 接下来我们就使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值： INNER JOIN mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; +-------------+-----------------+----------------+ | a.runoob_id | a.runoob_author | b.runoob_count | +-------------+-----------------+----------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | +-------------+-----------------+----------------+ 4 rows in set (0.00 sec)
接下来我们就使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值：
INNER JOIN
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; +-------------+-----------------+----------------+ | a.runoob_id | a.runoob_author | b.runoob_count | +-------------+-----------------+----------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | +-------------+-----------------+----------------+ 4 rows in set (0.00 sec)
以上 SQL 语句等价于：
WHERE 子句
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author; +-------------+-----------------+----------------+ | a.runoob_id | a.runoob_author | b.runoob_count | +-------------+-----------------+----------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | +-------------+-----------------+----------------+ 4 rows in set (0.01 sec)
MySQL LEFT JOIN
MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
实例
尝试以下实例，以 runoob_tbl 为左表，tcount_tbl 为右表，理解 MySQL LEFT JOIN 的应用：
LEFT JOIN
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; +-------------+-----------------+----------------+ | a.runoob_id | a.runoob_author | b.runoob_count | +-------------+-----------------+----------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | 5 | FK | NULL | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)
以上实例中使用了 LEFT JOIN，该语句会读取左边的数据表 runoob_tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl中 没有对应的 runoob_author 字段值。
MySQL RIGHT JOIN
MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。
实例
尝试以下实例，以 runoob_tbl 为左表，tcount_tbl 为右表，理解MySQL RIGHT JOIN的应用：
RIGHT JOIN
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; +-------------+-----------------+----------------+ | a.runoob_id | a.runoob_author | b.runoob_count | +-------------+-----------------+----------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | NULL | NULL | 22 | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)
以上实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 runoob_tbl 中没有对应的runoob_author 字段值。
MySQL 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 运算符。
注意：
select * , columnName1+ifnull(columnName2,0) from tableName;
columnName1，columnName2 为 int 型，当 columnName2 中，有值为 null 时，columnName1+columnName2=null， ifnull(columnName2,0) 把 columnName2 中 null 值转为 0。
在命令提示符中使用 NULL 值
以下实例中假设数据库 RUNOOB 中的表 runoob_test_tbl 含有两列 runoob_author 和 runoob_count, runoob_count 中设置插入NULL值。
实例
尝试以下实例:
创建数据表 runoob_test_tbl
root@host# mysql -u root -p password; Enter password:******* mysql> use RUNOOB; Database changed mysql> create table runoob_test_tbl -> ( -> runoob_author varchar(40) NOT NULL, -> runoob_count INT -> ); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('RUNOOB', 20); mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('菜鸟教程', NULL); mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('Google', NULL); mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('FK', 20); mysql> SELECT * from runoob_test_tbl; +---------------+--------------+ | runoob_author | runoob_count | +---------------+--------------+ | RUNOOB | 20 | | 菜鸟教程 | NULL | | Google | NULL | | FK | 20 | +---------------+--------------+ 4 rows in set (0.01 sec)
以下实例中你可以看到 = 和 != 运算符是不起作用的：
mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count = NULL; Empty set (0.00 sec) mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count != NULL; Empty set (0.01 sec)
查找数据表中 runoob_test_tbl 列是否为 NULL，必须使用 IS NULL 和 IS NOT NULL，如下实例：
mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL; +---------------+--------------+ | runoob_author | runoob_count | +---------------+--------------+ | 菜鸟教程 | NULL | | Google | NULL | +---------------+--------------+ 2 rows in set (0.01 sec) mysql> SELECT * from runoob_test_tbl WHERE runoob_count IS NOT NULL; +---------------+--------------+ | runoob_author | runoob_count | +---------------+--------------+ | RUNOOB | 20 | | FK | 20 | +---------------+--------------+ 2 rows in set (0.01 sec)
MySQL 正则表达式
在前面的章节我们已经了解到MySQL可以通过 LIKE ...% 来进行模糊匹配。
MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
如果您了解PHP或Perl，那么操作起来就非常简单，因为MySQL的正则表达式匹配与这些脚本的类似。
下表中的正则模式可应用于 REGEXP 操作符中。
| 模式 | 描述 |
|---|---|
| ^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。 |
| $ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。 |
| . | 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式。 |
| [...] | 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。 |
| [^...] | 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。 |
| p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。 |
| * | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。 |
| + | 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。 |
| {n} | n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。 |
| {n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
实例
了解以上的正则需求后，我们就可以根据自己的需求来编写带有正则表达式的SQL语句。以下我们将列出几个小实例(表名：person_tbl )来加深我们的理解：
查找name字段中以'st'为开头的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
查找name字段中以'ok'为结尾的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
查找name字段中包含'mar'字符串的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';
MySQL 事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
-
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
-
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
-
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
-
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
事务控制语句：
-
BEGIN 或 START TRANSACTION 显式地开启一个事务；
-
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
-
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
-
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
-
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
-
ROLLBACK TO identifier 把事务回滚到标记点；
-
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
事务测试
mysql> use RUNOOB; Database changed mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb; # 创建数据表 Query OK, 0 rows affected (0.04 sec) mysql> select * from runoob_transaction_test; Empty set (0.01 sec) mysql> begin; # 开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into runoob_transaction_test value(5); Query OK, 1 rows affected (0.01 sec) mysql> insert into runoob_transaction_test value(6); Query OK, 1 rows affected (0.00 sec) mysql> commit; # 提交事务 Query OK, 0 rows affected (0.01 sec) mysql> select * from runoob_transaction_test; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql> begin; # 开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into runoob_transaction_test values(7); Query OK, 1 rows affected (0.00 sec) mysql> rollback; # 回滚 Query OK, 0 rows affected (0.00 sec) mysql> select * from runoob_transaction_test; # 因为回滚所以数据没有插入 +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec)
MySQL ALTER命令
当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。
开始本章教程前让我们先创建一张表，表名为：testalter_tbl。
root@host# mysql -u root -p password; Enter password:******* mysql> use RUNOOB; Database changed mysql> create table testalter_tbl -> ( -> i INT, -> c CHAR(1) -> ); Query OK, 0 rows affected (0.05 sec) mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | i | int(11) | YES | | NULL | | | c | char(1) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
删除，添加或修改表字段
如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段：
mysql> ALTER TABLE testalter_tbl DROP i;
如果数据表中只剩余一个字段则无法使用DROP来删除字段。
MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:
mysql> ALTER TABLE testalter_tbl ADD i INT;
执行以上命令后，i 字段会自动添加到数据表字段的末尾。
mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。
尝试以下 ALTER TABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化：
ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT FIRST; ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT AFTER c;
FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。
修改字段类型及名称
如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。
例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
如果你现在想把字段 j="" 从="" bigint="" 修改为="" int，sql语句如下：
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;
ALTER TABLE 对 Null 值和默认值的影响
当你修改字段时，你可以指定是否包含值或者是否设置默认值。
以下实例，指定字段 j 为 NOT NULL 且默认值为100 。
mysql> ALTER TABLE testalter_tbl -> MODIFY j BIGINT NOT NULL DEFAULT 100;
如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。
修改字段默认值
你可以使用 ALTER 来修改字段的默认值，尝试以下实例：
mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | 1000 | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
你也可以使用 ALTER 命令及 DROP子句来删除字段的默认值，如下实例：
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT; mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) Changing a Table Type:
修改数据表类型，可以使用 ALTER 命令及 TYPE 子句来完成。尝试以下实例，我们将表 testalter_tbl 的类型修改为 MYISAM ：
注意：查看数据表类型可以使用 SHOW TABLE STATUS 语句。
mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM; mysql> SHOW TABLE STATUS LIKE 'testalter_tbl'\G *************************** 1. row **************** Name: testalter_tbl Type: MyISAM Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 25769803775 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2007-06-03 08:04:36 Update_time: 2007-06-03 08:04:36 Check_time: NULL Create_options: Comment: 1 row in set (0.00 sec)
修改表名
如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。
尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl：
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
ALTER 命令还可以用来创建及删除MySQL数据表的索引，
MySQL 索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高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)));
使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引：
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
- ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
以下实例为在表中添加索引。
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX c;
使用 ALTER 命令添加和删除主键
主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
你也可以使用 ALTER 命令删除主键：
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。
显示索引信息
你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
尝试以下实例:
mysql> SHOW INDEX FROM table_name\G
MySQL 临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
临时表在MySQL 3.23版本中添加，如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。
MySQL临时表只在当前连接可见，如果你使用PHP脚本来创建MySQL临时表，那每当PHP脚本执行完成后，该临时表也会自动销毁。
如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。
实例
以下展示了使用MySQL 临时表的简单实例，以下的SQL代码可以适用于PHP脚本的mysql_query()函数。
mysql> CREATE TEMPORARY TABLE SalesSummary(-> product_name VARCHAR(50) NOT NULL ->, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00->, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00->, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0);Query OK,0 rows affected (0.00 sec) mysql> INSERT INTO SalesSummary->(product_name, total_sales, avg_unit_price, total_units_sold)-> VALUES ->('cucumber',100.25,90,2); mysql> SELECT * FROM SalesSummary;+--------------+-------------+----------------+------------------+| product_name | total_sales | avg_unit_price | total_units_sold |+--------------+-------------+----------------+------------------+| cucumber |100.25|90.00|2|+--------------+-------------+----------------+------------------+1 row inset(0.00 sec)
当你使用 SHOW TABLES命令显示数据表列表时，你将无法看到 SalesSummary表。
如果你退出当前MySQL会话，再使用 SELECT命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时该临时表已经被销毁了。
删除MySQL 临时表
默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。
以下是手动删除临时表的实例：
mysql> CREATE TEMPORARY TABLE SalesSummary(-> product_name VARCHAR(50) NOT NULL ->, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00->, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00->, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0);Query OK,0 rows affected (0.00 sec) mysql> INSERT INTO SalesSummary->(product_name, total_sales, avg_unit_price, total_units_sold)-> VALUES ->('cucumber',100.25,90,2); mysql> SELECT * FROM SalesSummary;+--------------+-------------+----------------+------------------+| product_name | total_sales | avg_unit_price | total_units_sold |+--------------+-------------+----------------+------------------+| cucumber |100.25|90.00|2|+--------------+-------------+----------------+------------------+1 row inset(0.00 sec) mysql> DROP TABLE SalesSummary; mysql> SELECT * FROM SalesSummary; ERROR 1146:Table'RUNOOB.SalesSummary' doesn't exist
MySQL 复制表
如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE ... SELECT 命令，是无法实现的。
本章节将为大家介绍如何完整的复制MySQL数据表，步骤如下：
- 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
- 复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
- 如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。
实例
尝试以下实例来复制表 runoob_tbl 。
步骤一：
获取数据表的完整结构。
mysql> SHOW CREATE TABLE runoob_tbl \G;***************************1. row ***************************Table: runoob_tbl CreateTable: CREATE TABLE `runoob_tbl`(`runoob_id`int(11) NOT NULL auto_increment,`runoob_title` varchar(100) NOT NULL default'',`runoob_author` varchar(40) NOT NULL default'',`submission_date` date default NULL, PRIMARY KEY (`runoob_id`), UNIQUE KEY `AUTHOR_INDEX`(`runoob_author`)) ENGINE=InnoDB1 row inset(0.00 sec) ERROR:No query specified
步骤二：
修改SQL语句的数据表名，并执行SQL语句。
mysql> CREATE TABLE `clone_tbl`(->`runoob_id`int(11) NOT NULL auto_increment,->`runoob_title` varchar(100) NOT NULL default'',->`runoob_author` varchar(40) NOT NULL default'',->`submission_date` date default NULL,-> PRIMARY KEY (`runoob_id`),-> UNIQUE KEY `AUTHOR_INDEX`(`runoob_author`)->) ENGINE=InnoDB;Query OK,0 rows affected (1.80 sec)
步骤三：
执行完第二步骤后，你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用 INSERT INTO... SELECT 语句来实现。
mysql> INSERT INTO clone_tbl (runoob_id,-> runoob_title,-> runoob_author,-> submission_date)-> SELECT runoob_id,runoob_title,-> runoob_author,submission_date -> FROM runoob_tbl;Query OK,3 rows affected (0.07 sec)Records:3Duplicates:0Warnings:0
执行以上步骤后，会完整的复制表的内容，包括表结构及表数据。
mysql> SHOW INDEX FROM table_name\G
MySQL 元数据
你可能想知道MySQL以下三种信息：
- 查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。
- 数据库和数据表的信息： 包含了数据库及数据表的结构信息。
- MySQL服务器信息： 包含了数据库服务器的当前状态，版本号等。
在MySQL的命令提示符中，我们可以很容易的获取以上服务器信息。 但如果使用Perl或PHP等脚本语言，你就需要调用特定的接口函数来获取。 接下来我们会详细介绍。
获取查询语句影响的记录数
PERL 实例
在 DBI 脚本中， 语句影响的记录数通过函数 do( ) 或 execute( )返回：
# 方法 1
# 使用do( ) 执行 $query
my $count = $dbh->do ($query);
# 如果发生错误会输出 0
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);
# 方法 2
# 使用prepare( ) 及 execute( ) 执行 $query
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);
PHP 实例
在PHP中，你可以使用 mysqli_affected_rows( ) 函数来获取查询语句影响的记录数。
$result_id = mysqli_query ($conn_id, $query);
# 如果查询失败返回
$count = ($result_id ? mysqli_affected_rows ($conn_id) : 0);
print ("$count 条数据被影响\n");
数据库和数据表列表
你可以很容易的在MySQL服务器中获取数据库和数据表列表。 如果你没有足够的权限，结果将返回 null。
你也可以使用 SHOW TABLES 或 SHOW DATABASES 语句来获取数据库和数据表列表。
PERL 实例
# 获取当前数据库中所有可用的表。
my @tables = $dbh->tables ( );
foreach $table (@tables ){
print "表名 $table\n";
}
PHP 实例
以下实例输出 MySQL 服务器上的所有数据库：
查看所有数据库
<?php $dbhost = 'localhost'; // mysql服务器主机地址 $dbuser = 'root'; // mysql用户名 $dbpass = '123456'; // mysql用户名密码 $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('连接失败: ' . mysqli_error($conn)); } // 设置编码，防止中文乱码 $db_list = mysqli_query($conn, 'SHOW DATABASES'); while ($db = mysqli_fetch_object($db_list)) { echo $db->Database . "<br />"; } mysqli_close($conn); ?>
获取服务器元数据
以下命令语句可以在 MySQL 的命令提示符使用，也可以在脚本中 使用，如PHP脚本。
| 命令 | 描述 |
|---|---|
| SELECT VERSION( ) | 服务器版本信息 |
| SELECT DATABASE( ) | 当前数据库名 (或者返回空) |
| SELECT USER( ) | 当前用户名 |
| SHOW STATUS | 服务器状态 |
| SHOW VARIABLES | 服务器配置变量 |
MySQL 序列使用
MySQL 序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键， 如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。
本章我们将介绍如何使用MySQL的序列。
使用 AUTO_INCREMENT
MySQL 中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义序列。
实例
以下实例中创建了数据表 insect， insect 表中 id 无需指定值可实现自动增长。
mysql> CREATE TABLE insect ->(-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,-> PRIMARY KEY (id),-> name VARCHAR(30) NOT NULL,# type of insect-> date DATE NOT NULL,# date collected-> origin VARCHAR(30) NOT NULL # where collected);Query OK,0 rows affected (0.02 sec) mysql> INSERT INTO insect (id,name,date,origin) VALUES ->(NULL,'housefly','2001-09-10','kitchen'),->(NULL,'millipede','2001-09-10','driveway'),->(NULL,'grasshopper','2001-09-10','front yard');Query OK,3 rows affected (0.02 sec)Records:3Duplicates:0Warnings:0 mysql> SELECT * FROM insect ORDER BY id;+----+-------------+------------+------------+| id | name | date | origin |+----+-------------+------------+------------+|1| housefly |2001-09-10| kitchen ||2| millipede |2001-09-10| driveway ||3| grasshopper |2001-09-10| front yard |+----+-------------+------------+------------+3 rows inset(0.00 sec)
获取AUTO_INCREMENT值
在MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。
在PHP或PERL脚本中也提供了相应的函数来获取最后的插入表中的自增列的值。
PERL实例
使用 mysql_insertid 属性来获取 AUTO_INCREMENT 的值。 实例如下：
$dbh->do("INSERT INTO insect (name,date,origin) VALUES('moth','2001-09-14','windowsill')");my $seq = $dbh->{mysql_insertid};
PHP实例
PHP 通过 mysql_insert_id ()函数来获取执行的插入SQL语句中 AUTO_INCREMENT列的值。
mysql_query ("INSERT INTO insect (name,date,origin) VALUES('moth','2001-09-14','windowsill')", $conn_id); $seq = mysql_insert_id ($conn_id);
重置序列
如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。 不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。操作如下所示：
mysql> ALTER TABLE insect DROP id; mysql> ALTER TABLE insect -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,-> ADD PRIMARY KEY (id);
设置序列的开始值
一般情况下序列的开始值为1，但如果你需要指定一个开始值100，那我们可以通过以下语句来实现：
mysql> CREATE TABLE insect ->(-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,-> PRIMARY KEY (id),-> name VARCHAR(30) NOT NULL,-> date DATE NOT NULL,-> origin VARCHAR(30) NOT NULL )engine=innodb auto_increment=100 charset=utf8;
或者你也可以在表创建成功后，通过以下语句来实现：
mysql> ALTER TABLE t AUTO_INCREMENT =100;
MySQL 处理重复数据
有些 MySQL 数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时候我们也需要删除这些重复的数据。
本章节我们将为大家介绍如何防止数据表出现重复数据及如何删除数据表中的重复数据。
防止表中出现重复数据
你可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。
让我们尝试一个实例：下表中无索引及主键，所以该表允许出现多条重复记录。
CREATE TABLE person_tbl ( first_name CHAR(20), last_name CHAR(20), sex CHAR(10));
如果你想设置表中字段 first_name，last_name 数据不能重复，你可以设置双主键模式来设置数据的唯一性， 如果你设置了双主键，那么那个键的默认值不能为 NULL，可设置为 NOT NULL。如下所示：
CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name));
如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。
INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE INTO 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。
以下实例使用了 INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据：
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)-> VALUES('Jay','Thomas');Query OK,1 row affected (0.00 sec) mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)-> VALUES('Jay','Thomas');Query OK,0 rows affected (0.00 sec)
INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。
另一种设置数据的唯一性方法是添加一个 UNIQUE 索引，如下所示：
CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), UNIQUE (last_name, first_name));
统计重复数据
以下我们将统计表中 first_name 和 last_name的重复记录数：
mysql> SELECT COUNT(*)as repetitions, last_name, first_name -> FROM person_tbl -> GROUP BY last_name, first_name -> HAVING repetitions >1;
以上查询语句将返回 person_tbl 表中重复的记录数。 一般情况下，查询重复的值，请执行以下操作：
- 确定哪一列包含的值可能会重复。
- 在列选择列表使用COUNT(*)列出的那些列。
- 在GROUP BY子句中列出的列。
- HAVING子句设置重复数大于1。
过滤重复数据
如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。
mysql> SELECT DISTINCT last_name, first_name -> FROM person_tbl;
你也可以使用 GROUP BY 来读取数据表中不重复的数据：
mysql> SELECT last_name, first_name -> FROM person_tbl -> GROUP BY (last_name, first_name);
删除重复数据
如果你想删除数据表中的重复数据，你可以使用以下的SQL语句：
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex); mysql> DROP TABLE person_tbl; mysql> ALTER TABLE tmp RENAME TO person_tbl;
当然你也可以在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：
mysql> ALTER IGNORE TABLE person_tbl -> ADD PRIMARY KEY (last_name, first_name);
MySQL 及 SQL 注入
如果您通过网页获取用户输入的数据并将其插入一个MySQL数据库，那么就有可能发生SQL注入安全的问题。
本章节将为大家介绍如何防止SQL注入，并通过脚本来过滤SQL中注入的字符。
所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。
我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。
以下实例中，输入的用户名必须为字母、数字及下划线的组合，且用户名长度为 8 到 20 个字符之间：
if(preg_match("/^\w{8,20}$/", $_GET['username'], $matches)){ $result = mysqli_query($conn,"SELECT * FROM users WHERE username=$matches[0]");}else{ echo "username 输入异常";}
让我们看下在没有过滤特殊字符时，出现的SQL情况：
// 设定$name 中插入了我们不需要的SQL语句 $name ="Qadir'; DELETE FROM users;"; mysqli_query($conn,"SELECT * FROM users WHERE name='{$name}'");
以上的注入语句中，我们没有对 $name 的变量进行过滤，$name 中插入了我们不需要的SQL语句，将删除 users 表中的所有数据。
在PHP中的 mysqli_query() 是不允许执行多个 SQL 语句的，但是在 SQLite 和 PostgreSQL 是可以同时执行多条SQL语句的，所以我们对这些用户的数据需要进行严格的验证。
防止SQL注入，我们需要注意以下几个要点：
- 1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
- 2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
- 3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
- 4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
- 5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
- 6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。
防止SQL注入
在脚本语言，如Perl和PHP你可以对用户输入的数据进行转义从而来防止SQL注入。
PHP的MySQL扩展提供了mysqli_real_escape_string()函数来转义特殊的输入字符。
if(get_magic_quotes_gpc()){ $name = stripslashes($name);} $name = mysqli_real_escape_string($conn, $name); mysqli_query($conn,"SELECT * FROM users WHERE name='{$name}'");
Like语句中的注入
like查询时，如果用户输入的值有"_"和"%"，则会出现这种情况：用户本来只是想查询"abcd_"，查询结果中却有"abcd_"、"abcde"、"abcdf"等等；用户要查询"30%"（注：百分之三十）时也会出现问题。
在PHP脚本中我们可以使用addcslashes()函数来处理以上情况，如下实例：
$sub = addcslashes(mysqli_real_escape_string($conn,"%something_"),"%_");// $sub == \%something\_ mysqli_query($conn,"SELECT * FROM messages WHERE subject LIKE '{$sub}%'");
addcslashes() 函数在指定的字符前添加反斜杠。
语法格式:
addcslashes(string,characters)
| 参数 | 描述 |
|---|---|
| string | 必需。规定要检查的字符串。 |
| characters | 可选。规定受 addcslashes() 影响的字符或字符范围。 |
MySQL 导出数据
MySQL中你可以使用SELECT...INTO OUTFILE语句来简单的导出数据到文本文件上。
使用 SELECT ... INTO OUTFILE 语句导出数据
以下实例中我们将数据表 runoob_tbl 数据导出到 /tmp/runoob.txt 文件中:
mysql> SELECT * FROM runoob_tbl -> INTO OUTFILE '/tmp/runoob.txt';
你可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'-> LINES TERMINATED BY '\r\n';
在下面的例子中，生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;
SELECT ... INTO OUTFILE 语句有以下属性:
- LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT ... INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。
- SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。
- 输出不能是一个已存在的文件。防止文件数据被篡改。
- 你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。
- 在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。
导出表作为原始数据
mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。
使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。
以下实例将数据表 runoob_tbl 导出到 /tmp 目录中：
$ mysqldump -u root -p --no-create-info \ --tab=/tmp RUNOOB runoob_tbl password ******
导出 SQL 格式的数据
导出 SQL 格式的数据到指定文件，如下所示：
$ mysqldump -u root -p RUNOOB runoob_tbl >dump.txt password ******
以上命令创建的文件内容如下：
--MySQLdump8.23----Host: localhost Database: RUNOOB -----------------------------------------------------------Server version 3.23.58----Table structure for table `runoob_tbl`-- CREATE TABLE runoob_tbl ( runoob_id int(11) NOT NULL auto_increment, runoob_title varchar(100) NOT NULL default'', runoob_author varchar(40) NOT NULL default'', submission_date date default NULL, PRIMARY KEY (runoob_id), UNIQUE KEY AUTHOR_INDEX (runoob_author)) TYPE=MyISAM;----Dumping data for table `runoob_tbl`-- INSERT INTO runoob_tbl VALUES (1,'Learn PHP','John Poul','2007-05-24'); INSERT INTO runoob_tbl VALUES (2,'Learn MySQL','Abdul S','2007-05-24'); INSERT INTO runoob_tbl VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');
如果你需要导出整个数据库的数据，可以使用以下命令：
$ mysqldump -u root -p RUNOOB > database_dump.txt password ******
如果需要备份所有数据库，可以使用以下命令：
$ mysqldump -u root -p --all-databases > database_dump.txt password ******
--all-databases 选项在 MySQL 3.23.12 及以后版本加入。
该方法可用于实现数据库的备份策略。
将数据表及数据库拷贝至其他主机
如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。
在源主机上执行以下命令，将数据备份至 dump.txt 文件中:
$ mysqldump -u root -p database_name table_name >dump.txt password *****
如果完整备份数据库，则无需使用特定的表名称。
如果你需要将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建：
$ mysql -u root -p database_name <dump.txt password *****
你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：
$ mysqldump -u root -p database_name \ | mysql -h other-host.com database_name
以上命令中使用了管道来将导出的数据导入到指定的远程主机上。
MySQL 导入数据
本章节我们为大家介绍几种简单的 MySQL 导入数据命令。
1、mysql 命令导入
使用 mysql 命令导入语法格式为：
mysql -u用户名-p密码<要导入的数据库数据(runoob.sql)
实例：
# mysql -uroot -p123456 < runoob.sql
以上命令将将备份的整个数据库 runoob.sql 导入。
2、source 命令导入
source 命令导入数据库需要先登录到数库终端：
mysql> create database abc;# 创建数据库 mysql>use abc;# 使用已创建的数据库 mysql>set names utf8;# 设置编码 mysql> source /home/abc/abc.sql # 导入备份数据库
3、使用 LOAD DATA 导入数据
MySQL 中提供了LOAD DATA INFILE语句来插入数据。 以下实例中将从当前目录中读取文件 dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中。
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。
你能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。
两个命令的 FIELDS 和 LINES 子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在 LINES 子句之前。
如果用户指定一个 FIELDS 子句，它的子句 （TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可选的，不过，用户必须至少指定它们中的一个。
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl -> FIELDS TERMINATED BY ':'-> LINES TERMINATED BY '\r\n';
LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。
如，在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：
mysql> LOAD DATA LOCAL INFILE 'dump.txt'-> INTO TABLE mytbl (b, c, a);
4、使用 mysqlimport 导入数据
mysqlimport 客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口。mysqlimport 的大多数选项直接对应 LOAD DATA INFILE 子句。
从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令：
$ mysqlimport -u root -p --local mytbl dump.txt password *****
mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下：
$ mysqlimport -u root -p --local--fields-terminated-by=":" \ --lines-terminated-by="\r\n" mytbl dump.txt password *****
mysqlimport 语句中使用 --columns 选项来设置列的顺序：
$ mysqlimport -u root -p --local--columns=b,c,a \ mytbl dump.txt password *****
mysqlimport的常用选项介绍
| 选项 | 功能 |
|---|---|
| -d or --delete | 新数据导入数据表中之前删除数据数据表中的所有信息 |
| -f or --force | 不管是否遇到错误，mysqlimport将强制继续插入数据 |
| -i or --ignore | mysqlimport跳过或者忽略那些有相同唯一 关键字的行， 导入文件中的数据将被忽略。 |
| -l or -lock-tables | 数据被插入之前锁住表，这样就防止了， 你在更新数据库时，用户的查询和更新受到影响。 |
| -r or -replace | 这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录。 |
| --fields-enclosed- by= char | 指定文本文件中数据的记录时以什么括起的， 很多情况下 数据以双引号括起。 默认的情况下数据是没有被字符括起的。 |
| --fields-terminated- by=char | 指定各个数据的值之间的分隔符，在句号分隔的文件中， 分隔符是句号。您可以用此选项指定数据之间的分隔符。 默认的分隔符是跳格符（Tab） |
| --lines-terminated- by=str | 此选项指定文本文件中行与行之间数据的分隔字符串 或者字符。 默认的情况下mysqlimport以newline为行分隔符。 您可以选择用一个字符串来替代一个单个的字符： 一个新行或者一个回车。 |
mysqlimport 命令常用的选项还有 -v 显示版本（version）， -p 提示输入密码（password）等。
MySQL内置函数
太多了，放另一篇：
MySQL内置函数：https://www.cnblogs.com/jyf2018/p/15311869.html
MySQL运算符
MySQL运算符：https://www.cnblogs.com/jyf2018/p/15311899.html
MySQL 有很多内置的函数
浙公网安备 33010602011771号