MySQL学习笔记

一、数据表操作

1、创建数据表

（1）、语法：

CREATE TABLE table_name (column_name column_type);

（2）、举个栗子：

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;

2、删除数据表

（1）、语法：

 DROP TABLE runoob_tbl;

二、数据表的增、删、改、查

1、增加

（1）、语法：

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

（2）、举个栗子：

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');

2、删除

（1）、语法：

DELETE FROM table_name [WHERE Clause]

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

（2）、举个栗子：
以下实例将删除 runoob_tbl 表中 runoob_id 为3 的记录：

 DELETE FROM runoob_tbl WHERE runoob_id=3;

3、更新

（1）、语法：

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

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

（2）、举个栗子：

 UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;

4、更改

4.1、删除、添加或修改表的字段

原始表 **（1）、语法：** 如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段：

ALTER TABLE testalter_tbl  DROP i;

如果数据表中只剩余一个字段则无法使用DROP来删除字段。
MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:

ALTER TABLE testalter_tbl ADD i INT;

（2）、举个栗子：
执行以上命令后，i 字段会自动添加到数据表字段的末尾。

如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。
尝试以下 ALTER TABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化：

FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

4.2、修改字段类型及名称

（1）、语法：
如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。
例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

ALTER TABLE testalter_tbl MODIFY c CHAR(10);

使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

ALTER TABLE testalter_tbl CHANGE i j BIGINT;

ALTER TABLE testalter_tbl CHANGE j j INT;

4.3、ALTER TABLE 对 Null 值和默认值的影响

（1）、语法：
当你修改字段时，你可以指定是否包含值或者是否设置默认值。
以下实例，指定字段 j 为 NOT NULL 且默认值为100 。

ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;

4.4、修改字段默认值

（1）、举个栗子：
你可以使用 ALTER 来修改字段的默认值，尝试以下实例：

你也可以使用 ALTER 命令及 DROP子句来删除字段的默认值，如下实例：

修改数据表类型，可以使用 ALTER 命令及 TYPE 子句来完成。尝试以下实例，我们将表 testalter_tbl 的类型修改为 MYISAM ：
注意： 查看数据表类型可以使用 SHOW TABLE STATUS 语句。

4.5、修改表名

如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。
尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl：

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

5、查询

（1）、语法：

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

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

（2）、举个栗子：

select * from runoob_tbl;

三、子句

1、WHERE子句

​ 以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：
（1）、语法：

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语句中可以有操作符，包括=、！=、>、<、>=、<=。
如果我们想在 MySQL 数据表中读取指定的数据，WHERE 子句是非常有用的。如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。
**注：**多个条件的时候把语句括起来，中间用OR或者AND进行连接。

（2）、举个栗子：
MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程';

2、Like子句（模糊匹配）

有时候我们需要获取 字段含有某个字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。
SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 =的效果是一样的。
（1）、语法：

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 子句来指定条件。

（2）、举个栗子：
栗子1：

SELECT * from runoob_tbl  WHERE runoob_author LIKE '%COM';

栗子2：
Like用来匹配一部分的，% 任何字符出现任何位置区分大小写。
· SELECT * FROM TABLE WHERE col Like ‘%a’; //检索以a结尾的内容。
· SELECT * FROM TABLE WHERE col Like ‘%a%’; //检索包含a的内容。
· SELECT * FROM TABLE WHERE col Like ‘a%’; //检索以a开头的内容。
· SELECT * FROM TABLE WHERE col Like ‘a%b’; //检索以a开头以b结尾的内容。

3、UNION

​ MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
（1）、语法：

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: 可选，返回所有结果集，包含重复数据。

（2）、举个栗子：
· 原始表

第一张数据表

第二张数据表

· UNION后的表

UNION后的数据表（不含重复值）

UNION后的数据表（含重复值）

UNION后的数据表（含WHERE字句）

4、Order By 排序

我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。
（1）、语法：

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

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

（2）、举个栗子：

5、GROUP By 分组

​ GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
​ WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
​ 使用 coalesce 来设置一个可以取代 NUll 的名称。

（1）、语法：

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

select coalesce(a,b,c);

· 如果a == null, 则选择b；如果b == null,则选择c；如果a != null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

（2）、举个栗子：

GROUP By之后的数据表

WITH ROLLUP操作后的数据表

coalesce操作后的数据表

6、连接的使用

​ 在前几章节中，我们已经学会了如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。JOIN 按照功能大致分为如下三类：
· INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
· LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
· RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

原始表

6.1 INNER JOIN（JOIN）

内连接关系表示图

（1）、举个栗子：

内连接之后的表

6.2 LEFT JOIN

​ 以下实例中使用了 LEFT JOIN，该语句会读取左边的数据表 runoob_tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl中 没有对应的 runoob_author 字段值。

左连接关系表示图

（1）、举个栗子：

左连接之后的表

6.3 RIGHT JOIN

​ 以下实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 runoob_tbl 中没有对应的runoob_author 字段值。

右连接关系表示图

（1）、举个栗子：

右连接之后的表

6.4 多表连接

四、其他

1、NULL值处理

​ MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。为了处理这种情况，MySQL提供了三大运算符:
· IS NULL: 当列的值是 NULL,此运算符返回 true。
· IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
· <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。
​ 关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

原始表

（1）、举个栗子：

NULL操作后的表

2、正则表达式

​ 在前面的章节我们已经了解到MySQL可以通过 LIKE …% 来进行模糊匹配。
​ MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
（1）、语法：
​ 以下列表中的正则模式可应用于 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 次。

（2）、举个栗子：
· 查找name字段中以’st’为开头的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^st';

· 查找name字段中以’ok’为结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

· 查找name字段中包含’mar’字符串的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'mar';

· 查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

3、事务

​ MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
· 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
· 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
· 事务用来管理 insert,update,delete 语句
​ 一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
· 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
· 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
· 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
· 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
（1）、语法：
事务控制语句：
· 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 开启自动提交
（2）、举个栗子：

4、函数

参照： https://www.runoob.com/mysql/mysql-functions.html