@[TOC]

MYSQL

本文整理自 菜鸟教程
希望查看原文的请移步 菜鸟教程

安装 MYSQL MYSQL 下载

连接 MYSQL

---

打开控制台, 输入以下命令回车, 会叫我们填写密码, 填写好密码回车, 如果没有密码就直接回车

mysql -u root -p

退出 MYSQL 可用 exit 命令

mysql> exit

如图是连接成功界面

连接成功

create 命令创建数据库

---

我们可以在连接好 MYSQL 后, 使用 create 命令创建数据库, 语法如下 :

CREATE DATABASE 数据库名;

CREATE DATABASE databasename;

如图是成功创建界面

创建数据库成功

drop 命令删除数据库

---

语法 :

DROP DATABASE 数据库名

DROP DATABASE me;

如图

删除数据库

use 命令选择数据库

---

语法 :

USE 数据库名;

USE me;

如图

选择数据库

执行完 use 命令后我们就选择了数据库, 后续操作都会在我们选择的这个数据库中执行

创建数据表

---

语法 :

CREATE TABLE table_name (column_name column_type);

CREATE TABLE IF NOT EXISTS `table01` (
    id INT AUTO_INCREMENT,
    title VARCHAR(100),
    PRIMARY KEY (id)
);

如图

创建数据表

解析 :

• AUTO_INCREMENT 定义列为自增属性, 通常用于主键, 会自动加一
• PRIMARY KEY 关键字, 用于定义列为主键, 可以用多列来定义主键, 列之间用逗号隔开
• IF NOT EXISTS 是判断表是否存在, 如果确定表不存在可以省略

MYSQL 数据类型 请移步

MySQL 删除数据表

---

语法 :

DROP TABLE table_name;

让我们来把上面我们创建好的 table01 删了吧

DROP TABLE table01;

如图

删除数据表

查看表结构的几种方式

语法 :

SHOW COLUMNS FROM table_name;

DESC table_name;

--- show
SHOW COLUMNS FROM table04;

--- desc
DESC table04;

如图

删除数据表

MySQL 插入数据

---

语法 :

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

参数

field1, field2...
这些是列名

value1, value2...
这些是列的值

现在, 让我们把 table01 创建回去, 再往里面的 title 列添加一些数据吧

--- 创建 table01 数据表
CREATE TABLE IF NOT EXISTS `table01` (
    id INT AUTO_INCREMENT,
    title VARCHAR(100),
    PRIMARY KEY (id)
);

--- 向 table01 数据表里的 title 列添加一行数据
INSERT INTO table01 (title) VALUES ('tom');

--- 查询 table01 数据表, 看下我们插入的数据
SELECT * FROM table01;

如图

插入数据

MySQL 查询数据

---

语法 :

SELECT column_name, column_name
FROM table_name
[WHERE Clause][limit n][ OFFSET M]

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

为了演示该语法, 让我们再去添加一些数据吧

--- 添加更多的数据
INSERT INTO table01 (title) VALUES ('tony');
INSERT INTO table01 (title) VALUES ('lily');
INSERT INTO table01 (title) VALUES ('lucy');
INSERT INTO table01 (title) VALUES ('jack');

--- 查询所有
SELECT * FROM table01;

--- 只查询 title 列
SELECT title FROM table01;

--- 只查询 title 为 tony 的列
SELECT * FROM table01 WHERE title = 'tony';

--- 限制查询的数量
SELECT * FROM table01 LIMIT 2;

--- 设置查询数据的偏移量
SELECT * FROM table01 LIMIT 2 OFFSET 1;

如图

查询数据

查询数据

查询数据

MySQL 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 子句可以使用常用的比较运算符, 借图 :

  
  WHERE 子句

SELECT title FROM table01 WHERE title = "tome" AND title = "tony";
SELECT title FROM table01 WHERE title = "tome" OR title = "tony";

如图

WHERE 子句

MySQL UPDATE 更新

---

语法 :

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

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

--- 看下表结构先
SELECT * FROM table01;

--- 修改 title = tome 的字段, 将 tome 改为 newtome
UPDATE table01 SET title = "newtome" WHERE id = 1;

--- 查看修改结果
SELECT * FROM table01;

如图

UPDATE 更新

MySQL DELETE 语句

---

语法 :

DELETE FROM table_name [WHERE Clause]

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

--- 查看表内容
SELECT * FROM table01;

--- 删除 id = 1 的这一行数据
DELETE FROM table01 WHERE id = 1;

--- 再次查看表内容
SELECT * FROM table01;

如图

DELETE 语句

MySQL LIKE 子句

---

SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于 UNIX 或正则表达式中的星号 *。

如果没有使用百分号 %, 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 子句来指定条件。
• 该字段可用于做模糊查询

--- 查询 title 中带有 y 字符的行
SELECT * FROM table01 WHERE title LIKE "%y";

如图

LIKE 子句

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

--- 再建一个表 table02
CREATE TABLE table02 ( id INT AUTO_INCREMENT, title VARCHAR(100), PRIMARY KEY (id) );

--- 往 table02 中插入几条数据, 数据中包含几个和 table01 重复的数据
INSERT INTO table02 (title) VALUES ("tony");
INSERT INTO table02 (title) VALUES ("lily");
INSERT INTO table02 (title) VALUES ("lucy");
INSERT INTO table02 (title) VALUES ("tome");
INSERT INTO table02 (title) VALUES ("haha");

--- 用 union 整合数据
SELECT * FROM table01 UNION DISTINCT SELECT * FROM table02;
SELECT title FROM table01 UNION DISTINCT SELECT title FROM table02;

如图

UNION 操作符 UNION 操作符

注意 : 如图, select 必须要指定检索的列, 否则不能去重

MySQL 排序

---

语法 :

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

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

--- 将 table01 的数据以 id 降序来查询
SELECT * FROM table01 ORDER BY id DESC;

如图

排序

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;

• function(column_name) 是 COUNT, SUM, AVG 等函数, column_name 是要操作的列名

• 用 SELECT 查询出来的列可以用 AS 来重命名

• WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）

• 使用 coalesce 来设置一个可以取代 NUll 的名称

  语法

  select coalesce(a,b,c);

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

--- 将 table01 中的数据以 title 来分组
SELECT title, COUNT(*) FROM table01 GROUP BY title;

--- 用 as 将查询的数据重命名
SELECT title, COUNT(*) AS title_count FROM table01 GROUP BY title;

--- 用 WITH ROLLUP 对数据进行再次统计
SELECT title, COUNT(*) AS title_count FROM table01 GROUP BY title WITH ROLLUP;

--- 用 coalesce 处理 NULL
SELECT COALESCE(title, "total"), COUNT(*) AS title_count FROM table01 GROUP BY title WITH ROLLUP;

如图

分组

分组

MySQL 连接的使用

---

本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。

你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

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

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

--- 使用 INNER JOIN (也可以省略 INNER 使用 JOIN，效果一样)
SELECT a.id, b.title FROM table01 a INNER JOIN table02 b ON a.id = b.id;

--- 使用 LEFT JOIN 获取左表所有记录，即使右表没有对应匹配的记录。
SELECT a.id, b.title FROM table01 a LEFT JOIN table02 b ON a.id = b.id;

--- 使用 RIGHT JOIN 获取右表所有记录，即使左表没有对应匹配的记录
SELECT a.id, b.title FROM table01 a RIGHT JOIN table02 b ON a.id = b.id;

如图

连接的使用 连接的使用

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

--- 创建 table03
CREATE TABLE table03 (id INT AUTO_INCREMENT, title VARCHAR(100), age INT, PRIMARY KEY (id));

--- 添加 5 条数据给 table03
INSERT INTO table03 (title) VALUES ("TOMS");
INSERT INTO table03 (title, age) VALUES ("ALPHA", 16);
INSERT INTO table03 (title, age) VALUES ("BLOON", 18);
INSERT INTO table03 (title, age) VALUES ("CRITR", 20);
INSERT INTO table03 (title) VALUES ("DIRROO");

--- 查询 age 为空的数据
SELECT * FROM table03 WHERE age IS NULL;

--- 查询 age 不为空的数据
SELECT * FROM table03 WHERE age IS NOT NULL;

--- 把 id 和 age 相加
SELECT *, id + ifnull(age, 0) FROM table03;

如图

NULL 值处理

MySQL 正则表达式

---

借图

正则表达式

SELECT column1, column2... FROM table_name WHERE column1 REGEXP regexp, column...;

--- 筛选 title 中带 y 的数据
SELECT title FROM table01 WHERE title REGEXP 'y';

--- 筛选 title 中带 o 的数据
SELECT title FROM table01 WHERE title REGEXP 'o';

如图

正则表达式

MySQL 事务

---

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

事务控制语句 ：

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 开启自动提交

这里就只举例事务的 BEGIN, ROLLBACK, COMMIT, 其他的下次另起一文探讨

--- 创建数据表并连续插入3条数据
CREATE TABLE table04 (id INT AUTO_INCREMENT, title VARCHAR(100), PRIMARY KEY (id));
--- 开始事务
BEGIN;
INSERT INTO table04 (title) VALUES ("HTML");
INSERT INTO table04 (title) VALUES ("CSS");
INSERT INTO table04 (title) VALUES ("JAVASCRIPT");
--- 提交事务
COMMIT;
SELECT * FROM table04;

--- 开始事务
BEGIN;
INSERT INTO table04 (title) VALUES ("JAVASE");
INSERT INTO table04 (title) VALUES ("JAVAEE");
--- 回滚事务
ROLLBACK;
--- 提交事务
COMMIT;
SELECT * FROM table04;

如图

事务

事务

MySQL ALTER 命令

---

当我们需要修改数据表名或者修改数据表字段时，就需要使用到 MySQL ALTER 命令。

数据表的更改 及 数据表字段的增删改, 下文 type 是数据类型:

增 : MySQL 在 ALTER 命令中使用 ADD 子句来向数据表中添加列

ALTER TABLE table_name ADD column_name type;

删 : MySQL 在 ALTER 命令中使用 DROP 子句来删除原本表中存在的字段

ALTER TABLE table_name DROP column_name;

改 : MySQL 在 ALTER 命令中使用 MODIFY 或 CHANGE 子句 来修改字段类型及名称

• 使用 MODIFY 子句更改字段类型

  ALTER TABLE table_name MODIFY column_name type;

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

  ALTER TABLE table_name CHANGE old_column_name new_column_name type;

ALTER TABLE 对 Null 值和默认值的影响, 当你修改字段时，你可以指定是否包含值或者是否设置默认值

如果你不设置默认值，MySQL 会自动设置该字段默认为 NULL

ALTER TABLE table_name MODIFY column_name type NOT NULL DEFAULT type_value;

使用 ALTER 来修改字段的默认值

ALTER TABLE table_name ALTER column_name SET DEFAULT type_value;

使用 ALTER 命令及 DROP 子句来删除字段的默认值

ALTER TABLE table_name ALTER column_name DROP DEFAULT;

修改表名

ALTER TABLE table_name RENAME TO new_table_name;

--- 查看 table4 的表结构
SHOW COLUMNS FROM table04;

--- 往 table04 中添加一列, 列名为 sex, 类型为 varchar(2)
ALTER TABLE table04 ADD sex varchar(2);

--- 再次查看 table4 的表结构, 新增了 sex 列
SHOW COLUMNS FROM table04;

--- 删除刚刚创建的列 sex
ALTER TABLE table04 DROP sex;

--- 再次查看 table4 的表结构, 明显没有了 sex 列
SHOW COLUMNS FROM table04;

--- 更改 table4 中 title 字段的类型为 varchar(50)
ALTER TABLE table04 MODIFY title VARCHAR(50);

--- 再次查看 table4 的表结构, title 字段的 varchar(100) 变成了 varchar(50)
SHOW COLUMNS FROM table04;

--- 更改 table04 中 title 字段名为 name
ALTER TABLE table04 CHANGE title name VARCHAR(50);

--- 再次查看 table4 的表结构, title 字段名变成了 name
SHOW COLUMNS FROM table04;

如图

ALTER 命令

ALTER 命令

ALTER 命令

ALTER 命令

MySQL 临时表

---

如果你使用了其他 MySQL 客户端程序连接 MySQL 数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁

语法 : 与普通的表操作一样, 只不过创建的时候要在 TABLE 前加 TEMPORARY

CREATE TEMPORARY TABLE table_name (column_name1 type, column_name2 type, ...);

CREATE TEMPORARY TABLE temp (id INT PRIMARY KEY, name VARCHAR(20));

MySQL 复制表

---

语法 :

CREATE TABLE clone_table (column1 type, column2 type...);

INSERT INTO clone_table (column1, column2...) SELECT column1, column2... FROM table_name;

CREATE TABLE targetTable LIKE sourceTable;

INSERT INTO targetTable SELECT * FROM sourceTable;

创建表的同时定义表中的字段信息, 新表列必须是原表里有的

CREATE TABLE new_table (column1 type, column2...) AS (SELECT * FROM sourceTable);

拷贝一个表中其中的一些字段

CREATE TABLE new_table AS (SELECT column_name1, column_name2 FROM sourceTable);

可以将新建的表的字段改名

CREATE TABLE new_table AS (SELECT column_name1, column_name2 AS new_column_name2... FROM sourceTable);

--- 方式一
CREATE TABLE table05 (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO table05 (id, name) SELECT id, name FROM table04;

--- 方式二
CREATE TABLE table06 LIKE table05;
INSERT INTO table06 SELECT * FROM table05;

--- 方式三
CREATE TABLE table07 (id INT) AS (SELECT * FROM table06);

如图

复制表

MySQL 元数据

获取服务器元数据

命令	描述
SELECT VERSION( )	服务器版本信息
SELECT DATABASE( )	当前数据库名 (或者返回空)
SELECT USER( )	当前用户名
SHOW STATUS	服务器状态
SHOW VARIABLES	服务器配置变量

MySQL 序列使用

---

MySQL 序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键， 如果你想实现其他字段也实现自动增加，就可以使用 MySQL 序列来实现

语法 :

使用 AUTO_INCREMENT

CREATE TABLE table_name ( id INT PRIMARY KEY AUTO_INCREMENT );

重置序列

如果你删除了数据表中的多条记录，并希望对剩下数据的 AUTO_INCREMENT 列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。 不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。

ALTER TABLE table_name DROP id;

ALTER TABLE table_name ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);

--- 我们来重置一下 table05 的序列吧
SELECT * FROM table05;
ALTER TABLE table05 DROP id;
ALTER TABLE table05 ADD id INT PRIMARY KEY AUTO_INCREMENT;

--- 如果需要 id 在第一列显示, 在 add 命令中加 first 字段即可
ALTER TABLE table05 ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST;

如图

序列使用

MySQL 处理重复数据

---

在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。

_ 如果你设置了双主键，那么那个键的默认值不能为 NULL，可设置为 NOT NULL_

CREAT TABLE table_name (coumn type PRIMARY KEY);

CREATE TABLE table_name ( column1 type NOT NULL, column2 type NOT NULL, PRIMARY KEY (column1, column2) );

用 INSERT IGNORE INTO 代替 INSERT INTO 来插入数据

INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。

REPLACE INTO 代替 INSERT INTO

REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录

CREATE TABLE table08 (id INT AUTO_INCREMENT, title VARCHAR(20), age INT UNIQUE, PRIMARY KEY (id, title));

INSERT INTO table08 (title, age) VALUES ("tom", 25);
INSERT IGNORE INTO table08 (title, age) VALUES ("tom", 25);
REPLACE INTO table08 (title, age) VALUES ("tom", 20);
REPLACE INTO table08 (title, age) VALUES ("tony", 20);

如图, 用 REPLACE INTO 插入的数据对 INSERT INTO 插入的数据不敏感

处理重复数据