一 安装mysql [root@kolla ~]# yum install wget -y [root@kolla ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm [root@kolla ~]# yum install ./mysql57-community-release-el7-9.noarch.rpm [root@kolla ~]# yum repolist enabled | grep "mysql.*-community.*" mysql-connectors-community/x86_64 MySQL Connectors Community 14 mysql-tools-community/x86_64 MySQL Tools Community 10 mysql57-community/x86_64 MySQL 5.7 Community Server 40 离线安装[root@kolla ~]# yum install ./mysql-community-libs-5.7.29-1.el7.x86_64.rpm [root@kolla ~]# yum install ./mysql-community-client-5.7.29-1.el7.x86_64.rpm [root@kolla ~]# yum install ./mysql-community-server-5.7.29-1.el7.x86_64.rpm [root@kolla ~]# yum install mysql-community-server -y[root@kolla ~]# yum install mysql-community-server -y [root@kolla ~]# systemctl enable mysqld [root@kolla ~]# systemctl restart mysqld [root@kolla ~]# grep 'A temporary password' /var/log/mysqld.log 2020-01-29T09:56:01.727504Z 1 [Note] A temporary password is generated for root@localhost: _F_<l>pW2kVr 4 [mysqld] 5 skip-grant-tables 6 # 允许最大连接数 7 max_connections=20 8 # 服务端使用的字符集默认为8比特编码的latin1字符集 9 character-set-server=utf8 10 # 创建新表时将使用的默认存储引擎 11 default-storage-engine=INNODB [root@kolla ~]# service mysqld restart [root@kolla ~]# mysql -uroot -p [root@kolla ~]# mysqladmin --version mysqladmin Ver 8.42 Distrib 5.7.29, for Linux on x86_64 [root@kolla ~]# 二 创建数据库 [root@kolla ~]# mysqladmin -u root -p create RUNOOB1 [root@kolla ~]# mysql -uroot -p mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | RUNOOB | | RUNOOB1 | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) 三 删除数据库 [root@kolla ~]# mysql -uroot -p mysql> drop database RUNOOB; Query OK, 0 rows affected (0.00 sec) [root@kolla ~]# mysqladmin -u root -p drop RUNOOB1 Do you really want to drop the 'RUNOOB1' database [y/N] y Database "RUNOOB1" dropped [root@kolla ~]# 四 选择数据库 mysql> use RUNOOB Database changed mysql> 五 数据类型 MySQL中定义数据字段的类型对你数据库的优化是非常重要的。 MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。 类型 大小 范围（有符号） 范围（无符号） 用途 TINYINT 1 字节 (-128，127) (0，255) 小整数值 SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值 MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值 INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值 BIGINT 8 字节 (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值 FLOAT 4 字节 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值 DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值 DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值 日期和时间类型 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。 每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。 TIMESTAMP类型有专有的自动更新特性，将在后面描述。 类型 大小 (字节) 范围 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值 TIMESTAMP 4 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值，时间戳 字符串类型 类型 大小 用途 CHAR 0-255字节 定长字符串 VARCHAR 0-65535 字节 变长字符串 TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串 TINYTEXT 0-255字节 短文本字符串 BLOB 0-65 535字节 二进制形式的长文本数据 TEXT 0-65 535字节 长文本数据 MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215字节 中等长度文本数据 LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295字节 极大文本数据 六 创建数据表 mysql> use RUNOOB Database changed mysql> 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; Query OK, 0 rows affected (0.01 sec) mysql> desc runoob_tbl; +-----------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------+----------------+ | runoob_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | runoob_title | varchar(100) | NO | | NULL | | | runoob_author | varchar(40) | NO | | NULL | | | submission_date | date | YES | | NULL | | +-----------------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> 七 删除数据表 mysql> DROP TABLE runoob_tbl; 八 插入数据 以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法： 插入单条数据 INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ); 插入多条数据 INSERT INTO table_name (field1, field2,...fieldN) VALUES (valueA1,valueA2,...valueAN), (valueB1,valueB2,...valueBN), (valueC1,valueC2,...valueCN)......; 插入例子 INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("学习 PHP", "菜鸟教程", NOW()); INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("学习 MySQL", "菜鸟教程", NOW()); INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("JAVA 教程", "RUNOOB.COM", '2016-05-06'); 九 读取数据库 mysql> select * from runoob_tbl; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 1 | 学习 PHP | 菜鸟教程 | 2020-01-30 | | 2 | 学习 MySQL | 菜鸟教程 | 2020-01-30 | | 3 | JAVA 教程 | RUNOOB.COM | 2016-05-06 | +-----------+--------------+---------------+-----------------+ 3 rows in set (0.00 sec) mysql> 十 查询数据 SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M] 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。 SELECT 命令可以读取一条或者多条记录。 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据 你可以使用 WHERE 语句来包含任何条件。 你可以使用 LIMIT 属性来设定返回的记录数。 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。 十一 WHERE子句 mysql> SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程'; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 1 | 学习 PHP | 菜鸟教程 | 2020-01-30 | | 2 | 学习 MySQL | 菜鸟教程 | 2020-01-30 | +-----------+--------------+---------------+-----------------+ 2 rows in set (0.00 sec) mysql> where：数据库中常用的是where关键字，用于在初始表中筛选查询。它是一个约束声明，用于约束数据，在返回结果集之前起作用。 group by:对select查询出来的结果集按照某个字段或者表达式进行分组，获得一组组的集合，然后从每组中取出一个指定字段或者表达式的值。 having：用于对where和group by查询出来的分组经行过滤，查出满足条件的分组结果。它是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作。 执行顺序 select –>where –> group by–> having–>order by 十二 MySQL UPDATE 更新 UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause] mysql> UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 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 row in set (0.00 sec) mysql> 十三 MySQL DELETE 语句 你可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。 DELETE FROM table_name [WHERE Clause] 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。 你可以在 WHERE 子句中指定任何条件 您可以在单个表中一次性删除记录。 当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。 mysql> use RUNOOB; Database changed mysql> DELETE FROM runoob_tbl WHERE runoob_id=3; Query OK, 1 row affected (0.00 sec) mysql> select * from runoob_tbl; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 1 | 学习 PHP | 菜鸟教程 | 2020-01-30 | | 2 | 学习 MySQL | 菜鸟教程 | 2020-01-30 | +-----------+--------------+---------------+-----------------+ 2 rows in set (0.00 sec) mysql> 十四 MySQL LIKE 子句 SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue' mysql> SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM'; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 6 | JAVA 教程 | RUNOOB.COM | 2016-05-06 | +-----------+--------------+---------------+-----------------+ 1 row in set (0.00 sec) mysql> 十五 MySQL UNION 操作符 MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据 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: 可选，返回所有结果集，包含重复数据。 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 | +----+---------------+---------------------------+-------+---------+ 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 语句从 "Websites" 和 "apps" 表中选取所有不同的country（只有不同的值） SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country; UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country（也有重复的值） SELECT country FROM Websites UNION ALL SELECT country FROM apps ORDER BY country; 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; UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据） UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据） SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称； SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称； 十六 排序 结果将按升序及降序排列。 mysql> SELECT * from runoob_tbl ORDER BY submission_date ASC; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 6 | JAVA 教程 | RUNOOB.COM | 2016-05-06 | | 1 | 学习 PHP | 菜鸟教程 | 2020-01-30 | | 2 | 学习 MySQL | 菜鸟教程 | 2020-01-30 | | 4 | 学习 PHP | 菜鸟教程 | 2020-01-30 | | 5 | 学习 MySQL | 菜鸟教程 | 2020-01-30 | +-----------+--------------+---------------+-----------------+ 5 rows in set (0.00 sec) mysql> SELECT * from runoob_tbl ORDER BY submission_date DESC; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 1 | 学习 PHP | 菜鸟教程 | 2020-01-30 | | 2 | 学习 MySQL | 菜鸟教程 | 2020-01-30 | | 4 | 学习 PHP | 菜鸟教程 | 2020-01-30 | | 5 | 学习 MySQL | 菜鸟教程 | 2020-01-30 | | 6 | JAVA 教程 | RUNOOB.COM | 2016-05-06 | +-----------+--------------+---------------+-----------------+ 5 rows in set (0.00 sec) mysql> 十七 MySQL GROUP BY 语句（分组） GROUP BY 语句根据一个或多个列对结果集进行分组。 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。 SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name; 先将以下数据导入数据库中。 mysql> SET NAMES utf8; mysql> SET FOREIGN_KEY_CHECKS = 0; mysql> 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, `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> BEGIN; mysql> 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'); mysql> COMMIT; mysql> SET FOREIGN_KEY_CHECKS = 1; 导入成功后，执行以下SQL语句 mysql> set names utf8; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM employee_tbl; +----+--------+---------------------+--------+ | id | name | date | singin | +----+--------+---------------------+--------+ | 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) mysql> 下来我们使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录： mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name; +--------+----------+ | name | COUNT(*) | +--------+----------+ | 小丽 | 1 | | 小明 | 3 | | 小王 | 2 | +--------+----------+ 3 rows in set (0.00 sec) mysql> 使用 WITH ROLLUP WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。 例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数 mysql> SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP; +--------+--------------+ | name | singin_count | +--------+--------------+ | 小丽 | 2 | | 小明 | 7 | | 小王 | 7 | | NULL | 16 | +--------+--------------+ 4 rows in set (0.00 sec) 其中记录 NULL 表示所有人的登录次数。 mysql> 我们可以使用 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(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP; +--------------------------+--------------+ | coalesce(name, '总数') | singin_count | +--------------------------+--------------+ | 小丽 | 2 | | 小明 | 7 | | 小王 | 7 | | 总数 | 16 | +--------------------------+--------------+ 4 rows in set (0.00 sec) mysql> 十八 MySQL 连接的使用 多个数据表中读取数据。 MySQL 的 JOIN 在两个或多个表中查询数据 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询 JOIN 按照功能大致分为如下三类： INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。 LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。 RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。 /* Navicat MySQL Data Transfer Source Server : 127.0.0.1 Source Server Version : 50621 Source Host : localhost Source Database : RUNOOB Target Server Version : 50621 File Encoding : utf-8 Date: 04/13/2017 14:25:12 PM */ SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for `runoob_tbl` -- ---------------------------- DROP TABLE IF EXISTS `runoob_tbl`; CREATE TABLE `runoob_tbl` ( `runoob_id` int(11) NOT NULL AUTO_INCREMENT, `runoob_title` varchar(100) NOT NULL, `runoob_author` varchar(40) NOT NULL, `submission_date` date DEFAULT NULL, PRIMARY KEY (`runoob_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `runoob_tbl` -- ---------------------------- BEGIN; INSERT INTO `runoob_tbl` VALUES ('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'); COMMIT; -- ---------------------------- -- Table structure for `tcount_tbl` -- ---------------------------- DROP TABLE IF EXISTS `tcount_tbl`; CREATE TABLE `tcount_tbl` ( `runoob_author` varchar(255) NOT NULL DEFAULT '', `runoob_count` int(11) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `tcount_tbl` -- ---------------------------- BEGIN; INSERT INTO `tcount_tbl` VALUES ('菜鸟教程', '10'), ('RUNOOB.COM ', '20'), ('Google', '22'); COMMIT; SET FOREIGN_KEY_CHECKS = 1; 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.00 sec) mysql> 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; +-----------+---------------+--------------+ | runoob_id | runoob_author | runoob_count | +-----------+---------------+--------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | +-----------+---------------+--------------+ 4 rows in set (0.00 sec) mysql>等价于如下语句 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; +-----------+---------------+--------------+ | runoob_id | runoob_author | runoob_count | +-----------+---------------+--------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | +-----------+---------------+--------------+ 4 rows in set (0.00 sec) mysql> MySQL LEFT JOIN MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。 以 runoob_tbl 为左表，tcount_tbl 为右表，理解 MySQL 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; +-----------+---------------+--------------+ | runoob_id | runoob_author | runoob_count | +-----------+---------------+--------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | 5 | FK | NULL | +-----------+---------------+--------------+ 5 rows in set (0.00 sec) mysql> MySQL RIGHT JOIN MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。 实例 尝试以下实例，以 runoob_tbl 为左表，tcount_tbl 为右表，理解MySQL 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; +-----------+---------------+--------------+ | runoob_id | runoob_author | runoob_count | +-----------+---------------+--------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | NULL | NULL | 22 | +-----------+---------------+--------------+ 5 rows in set (0.00 sec) mysql> 以上实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 runoob_tbl 中没有对应的runoob_author 字段值。 十九 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。 mysql> use RUNOOB; Database changed mysql> create table runoob_test_tbl ( runoob_author varchar(40) NOT NULL, runoob_count INT ); 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.00 sec) mysql> 以下实例中你可以看到 = 和 != 运算符是不起作用的： 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.00 sec) mysql> 查找数据表中 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.00 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.00 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 次 查找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 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员， 你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！ (1)在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。 (2)事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。 (3)事务用来管理 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，用来禁止使用当前会话的自动提交。 ----------------------------------------- MYSQL 事务处理主要有两种方法： 1、用 BEGIN, ROLLBACK, COMMIT来实现 BEGIN 开始一个事务 ROLLBACK 事务回滚 COMMIT 事务确认 2、直接用 SET 来改变 MySQL 的自动提交模式: SET AUTOCOMMIT=0 禁止自动提交 SET AUTOCOMMIT=1 开启自动提交 事务测试 mysql> use RUNOOB; mysql> REATE TABLE runoob_transaction_test( id int(5)) engine=innodb; # 创建数据表 mysql> select * from runoob_transaction_test; mysql> begin; # 开始事务 mysql> insert into runoob_transaction_test value(5); mysql> insert into runoob_transaction_test value(6); mysql> commit; # 提交事务 mysql> select * from runoob_transaction_test; mysql> begin; # 开始事务 mysql> insert into runoob_transaction_test values(7); mysql> rollback; # 回滚 mysql> select * from runoob_transaction_test; # 因为回滚所以数据没有插入 +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.00 sec) 二十二MySQL ALTER命令 我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。 先创建一张表，表名为：testalter_tbl。 mysql> use RUNOOB; mysql> create table testalter_tbl ( i INT, c CHAR(1) ); 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) mysql> 删除，添加或修改表字段 如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段： mysql> ALTER TABLE testalter_tbl DROP i; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) 如果数据表中只剩余一个字段则无法使用DROP来删除字段。 MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型: mysql> ALTER TABLE testalter_tbl ADD i INT; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 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> 如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。 尝试以下 ALTER TABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化： mysql>ALTER TABLE testalter_tbl DROP i; mysql>ALTER TABLE testalter_tbl ADD i INT FIRST; mysql>ALTER TABLE testalter_tbl DROP i; mysql>ALTER TABLE testalter_tbl ADD i INT AFTER c; --------------------------------------------------- 修改字段类型及名称 如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。 例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令: 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> ALTER TABLE testalter_tbl MODIFY c CHAR(10); mysql> SHOW COLUMNS FROM testalter_tbl; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | c | char(10) | YES | | NULL | | | i | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT; mysql> ALTER TABLE testalter_tbl CHANGE j j INT; ALTER TABLE 对 Null 值和默认值的影响 mysql> ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100; mysql> SHOW COLUMNS FROM testalter_tbl; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c | char(10) | YES | | NULL | | | j | bigint(20) | NO | | 100 | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> 修改字段默认值 mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW COLUMNS FROM testalter_tbl; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | i | int(11) | YES | | 1000 | | | c | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 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 | +-------+----------+------+-----+---------+-------+ | i | int(11) | YES | | NULL | | | c | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> 改数据表类型，可以使用 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 Engine: MyISAM Version: 10 Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 9851624184872959 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2020-02-01 19:40:58 Update_time: 2020-02-01 19:40:58 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> 修改表名 如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。 尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl： mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl; 修改存储引擎：修改为myisam mysql> alter table alter_tbl engine=myisam; 删除外键约束：keyName是外键别名 alter table tableName drop foreign key keyName; 修改字段的相对位置：这里name1为想要修改的字段，type1为该字段原来类型，first和after二选一， 这应该显而易见，first放在第一位，after放在name2字段后面 alter table tableName modify name1 type1 first|after name2; 二十三 MySQL索引 MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。 拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。 索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。 上面都在说使用索引的好处，但过多的使用索引将会造成滥用。 因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。 建立索引会占用磁盘空间的索引文件。 23.1普通索引 CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。 23.2修改表结构(添加索引) ALTER table tableName ADD INDEX indexName(columnName) 23.3创建表的时候直接指定 CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); 23.4删除索引的语法 DROP INDEX [indexName] ON mytable; 23.5唯一索引 它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式： 23.6创建索引 CREATE UNIQUE INDEX indexName ON mytable(username(length)) 23.7修改表结构 ALTER table mytable ADD UNIQUE [indexName] (username(length)) 23.8创建表的时候直接指定 CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) ); 23.9使用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; 23.10使用 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，但在删除索引时，你必须知道索引名。 23.11显示索引信息 你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。 尝试以下实例: mysql> SHOW INDEX FROM table_name; \G ........ 二十四临时表 MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。 临时表在MySQL 3.23版本中添加，如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。 MySQL临时表只在当前连接可见，如果你使用PHP脚本来创建MySQL临时表，那每当PHP脚本执行完成后，该临时表也会自动销毁。 如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。 24.1实际例子 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 ); mysql> INSERT INTO SalesSummary (product_name, total_sales, avg_unit_price, total_units_sold) VALUES ('cucumber', 100.25, 90, 2); mysql> * FROM SalesSummary; 当你使用 SHOW TABLES命令显示数据表列表时，你将无法看到 SalesSummary表。 如果你退出当前MySQL会话，再使用 SELECT命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时该临时表已经被销毁了。 24.2 删除MYSQL临时表 默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。 mysql> drop table SalesSummary; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM SalesSummary; ERROR 1146 (42S02): Table 'RUNOOB.SalesSummary' doesn't exist mysql> 二十五 MySQL复制表 如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE ... SELECT 命令，是无法实现的。 如何完整的复制MySQL数据表，步骤如下： 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。 复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。 如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。 25.1获取数据表的完整结构 mysql> SHOW CREATE TABLE runoob_tbl \G; *************************** 1. row *************************** Table: runoob_tbl Create Table: CREATE TABLE `runoob_tbl` ( `runoob_id` int(11) NOT NULL AUTO_INCREMENT, `runoob_title` varchar(100) NOT NULL, `runoob_author` varchar(40) NOT NULL, `submission_date` date DEFAULT NULL, PRIMARY KEY (`runoob_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 25.2修改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; 25.3执行完成第二部，将在数据库中创建新的克隆表 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; 25.4 另一种完整复制表的方法: runoob_tbl CREATE TABLE targetTable LIKE runoob_tbl; INSERT INTO targetTable SELECT * FROM runoob_tbl; 25.5 可以拷贝一个表中其中的一些字段: CREATE TABLE newadmin AS ( SELECT username, password FROM admin ) 可以将新建的表的字段改名: CREATE TABLE newadmin AS ( SELECT id, username AS uname, password AS pass FROM admin ) 可以拷贝一部分数据: CREATE TABLE newadmin AS ( SELECT * FROM admin WHERE LEFT(username,1) = 's' ) 可以在创建表的同时定义表中的字段信息: CREATE TABLE newadmin ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY ) AS ( SELECT * FROM admin ) 25.7 第一、只复制表结构到新表 create table 新表 select * from 旧表 where 1=2 或者 create table 新表 like 旧表 第二、复制表结构及数据到新表 create table新表 select * from 旧表 26 MYSQL元数据 你可能想知道MySQL以下三种信息： 查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。 数据库和数据表的信息： 包含了数据库及数据表的结构信息。 MySQL服务器信息： 包含了数据库服务器的当前状态，版本号等。 以下命令语句可以在 MySQL 的命令提示符使用，也可以在脚本中 使用，如PHP脚本。 命令 描述 SELECT VERSION( ) 服务器版本信息 SELECT DATABASE( ) 当前数据库名 (或者返回空) SELECT USER( ) 当前用户名 SHOW STATUS 服务器状态 SHOW VARIABLES 服务器配置变量 二十七 MySQL 序列使用 MySQL 序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键， 如果你想实现其他字段也实现自动增加，就可以使用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 ); 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'); 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 in set (0.00 sec) MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。 27.2 重置序列 如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。 不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。操作如下所示： mysql> ALTER TABLE insect DROP id; mysql> select * from insect; +-------------+------------+------------+ | name | date | origin | +-------------+------------+------------+ | housefly | 2001-09-10 | kitchen | | millipede | 2001-09-10 | driveway | | grasshopper | 2001-09-10 | front yard | +-------------+------------+------------+ mysql> ALTER TABLE insect -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, -> ADD PRIMARY KEY (id); mysql> select * from insect; +----+-------------+------------+------------+ | id | name | date | origin | +----+-------------+------------+------------+ | 1 | housefly | 2001-09-10 | kitchen | | 2 | millipede | 2001-09-10 | driveway | | 3 | grasshopper | 2001-09-10 | front yard | +----+-------------+------------+------------+ 27.3 设置序列的开始值 一般情况下序列的开始值为1，但如果你需要指定一个开始值100，那我们可以通过以下语句来实现： （1） 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; （2） 或者你也可以在表创建成功后，通过以下语句来实现： mysql> ALTER TABLE insect AUTO_INCREMENT = 100; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from insect; +----+-------------+------------+------------+ | 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 in set (0.00 sec) mysql> INSERT INTO insect (id,name,date,origin) VALUES -> (NULL,'housefly1','2002-09-10','kitchen'); mysql> select * from insect; +-----+-------------+------------+------------+ | id | name | date | origin | +-----+-------------+------------+------------+ | 1 | housefly | 2001-09-10 | kitchen | | 2 | millipede | 2001-09-10 | driveway | | 3 | grasshopper | 2001-09-10 | front yard | | 100 | housefly1 | 2002-09-10 | kitchen | +-----+-------------+------------+------------+ 4 rows in set (0.00 sec) mysql> 二十八 MySQL 处理重复数据 有些 MySQL 数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时候我们也需要删除这些重复的数据。 如何防止数据表出现重复数据及如何删除数据表中的重复数据。 28.1 防止表中出现重复数据 你可以在 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 语句将无法执行成功,并抛出错。 28.2 INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据， 如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。 以下实例使用了 INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据： 例子： mysql> 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) ); mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( 'Jay', 'Thomas'); mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( 'Jay', 'Thomas'); NSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。 28.3 设置数据的唯一性方法是添加一个UNIQUE索引 CREATE TABLE person_tb2 ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), UNIQUE (last_name, first_name) ); 28.4 统计重复数据 以下我们将统计表中 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。 28.5 过滤重复数据 如果你需要读取不重复的数据可以在 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); 28.6删除重复数据 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); 28.7 笔记 select 列名1，count（1） as count from 表名 group by 列名1 having count>1 and 其他条件 select 列名1，列名2，count（1） as count from 表名 group by 列名1，列名2 having count>1 and 其他条件 原理：先按照要查询出现重复数据的列，进行分组查询。count > 1 代表出现 2 次或 2 次以上。 示例： /*查询重复数据*/ select serialnum,cdate,count(*) as count from m_8_customer_temp_20180820bak group by serialnum,cdate having count>1 and cdate>='2018-08-20 00:00:00'; 二十九 MySQL 导出数据 29.1 MySQL中你可以使用SELECT...INTO OUTFILE语句来简单的导出数据到文本文件上。 mysql> SELECT * FROM runoob_tbl INTO OUTFILE '/tmp/runoob.txt'; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement mysql> 解决方案 mysql> show variables like '%secure%'; +--------------------------+-----------------------+ | Variable_name | Value | +--------------------------+-----------------------+ | require_secure_transport | OFF | | secure_auth | ON | | secure_file_priv | /var/lib/mysql-files/ | +--------------------------+-----------------------+ 3 rows in set (0.01 sec) mysql> SELECT * FROM runoob_tbl INTO OUTFILE '/var/lib/mysql-files/runoob.txt'; Query OK, 5 rows affected (0.01 sec) [root@kolla ~]# cd /var/lib/mysql-files/ [root@kolla mysql-files]# ls runoob.txt [root@kolla mysql-files]# cat runoob.txt 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 [root@kolla mysql-files]# 29.2 你可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式： mysql> SELECT * FROM runoob_tbl INTO OUTFILE '/var/lib/mysql-files/runoob.txt' -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' -> LINES TERMINATED BY '\r\n'; [root@kolla mysql-files]# cat runoob.txt "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" [root@kolla mysql-files]# SELECT a,b,a+b INTO OUTFILE '/var/lib/mysql-files/runoob.txt1' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM runoob_tbl; 29.3 导出表作为原始数据 mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。 使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。 以下实例将数据表 runoob_tbl 导出到 /var/lib/mysql-files/ 目录中： [root@kolla ~]# mysqldump -u root -p --no-create-info --tab=/var/lib/mysql-files/ RUNOOB runoob_tbl Enter password: [root@kolla ~]# [root@kolla mysql-files]# cat runoob_tbl.sql [root@kolla mysql-files]# cat runoob_tbl.txt 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 [root@kolla mysql-files]# 29.4 导出 SQL 格式的数据 [root@kolla ~]# mysqldump -u root -p RUNOOB runoob_tbl > dump.txt Enter password: [root@kolla ~]# cat dump.txt -- MySQL dump 10.13 Distrib 5.7.29, for Linux (x86_64) -- -- Host: localhost Database: RUNOOB -- ------------------------------------------------------ -- Server version 5.7.29 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `runoob_tbl` -- DROP TABLE IF EXISTS `runoob_tbl`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `runoob_tbl` ( `runoob_id` int(11) NOT NULL AUTO_INCREMENT, `runoob_title` varchar(100) NOT NULL, `runoob_author` varchar(40) NOT NULL, `submission_date` date DEFAULT NULL, PRIMARY KEY (`runoob_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `runoob_tbl` -- LOCK TABLES `runoob_tbl` WRITE; /*!40000 ALTER TABLE `runoob_tbl` DISABLE KEYS */; INSERT INTO `runoob_tbl` VALUES (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'); /*!40000 ALTER TABLE `runoob_tbl` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2020-02-02 23:36:34 [root@kolla ~]# 29.5 导出整个数据库的数据 [root@kolla ~]# mysqldump -u root -p RUNOOB > database_dump.txt 29.6如果需要备份所有数据库，可以使用如下命令 [root@kolla ~]# mysqldump -u root -p --all-databases > database_dump.txt 29.7 将数据表及数据库拷贝至其他主机 如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。 在源主机上执行以下命令，将数据备份至 dump.txt 文件中: mysqldump -u root -p database_name table_name > dump.txt 如果完整备份数据库，则无需使用特定的表名称。 如果你需要将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建： $ mysql -u root -p database_name < dump.txt password ***** 你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的： $ mysqldump -u root -p database_name \ | mysql -h other-host.com database_name 以上命令中使用了管道来将导出的数据导入到指定的远程主机上。 29.8 将指定主机的数据库拷贝到本地 如果你需要将远程服务器的数据拷贝到本地，你也可以在 mysqldump 命令中指定远程服务器的IP、端口及数据库名。 在源主机上执行以下命令，将数据备份到 dump.txt 文件中： 请确保两台服务器是相通的： mysqldump -h other-host.com -P port -u root -p database_name > dump.txt password **** 三十 MySQL 导入数据 mysql -u用户名 -p密码 < 要导入的数据库数据(runoob.sql) 30.1# mysql -uroot -p123456 < runoob.sql 以上命令将将备份的整个数据库 runoob.sql 导入。 30.2 source 命令导入 source 命令导入数据库需要先登录到数库终端： mysql> create database abc; # 创建数据库 mysql> use abc; # 使用已创建的数据库 mysql> set names utf8; # 设置编码 mysql> source /home/abc/abc.sql # 导入备份数据库 30.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）等。
1430
“相关推荐”对你有帮助么？
-
非常没帮助 -
没帮助 -
一般 -
有帮助 -
非常有帮助
提交
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
