MySQL核心命令详解与实战，一文掌握MySQL使用

文章简介

本文主要介绍了MySQL核心命令的介绍和使用方法。

演示库表

mmall_learning库表结构。

部分命令中涉及的其他库表，均为简单说明，可不必在意具体表结构。

创建数据库表

CREATE DATABASE 数据库名;

CREATE DATABASE mmall_learning;

列出 MySQL 数据库管理系统的数据库列表。

SHOW DATABASES;

结果如下：

选择数据库

USE 数据库名;

USE mmall_learning;

显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。
SHOW TABLES:

显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。

SHOW COLUMNS FROM 数据表;

例如：展示购物车的数据表结构

SHOW COLUMNS FROM mmall_cart;

结果如下：

删除数据库

1.直接删除数据库，不检查是否存在

DROP DATABASE 数据库;

2.删除数据库，如果存在的话

DROP DATABASE IF EXISTS 数据库;

创建表

CREATE TABLE table_name (column1 datatype,column2 datatype,...
);

示例：

CREATE TABLE `mmall_cart` (`id` int(11) NOT NULL AUTO_INCREMENT,`user_id` int(11) NOT NULL,`product_id` int(11) DEFAULT NULL COMMENT '商品id',`quantity` int(11) DEFAULT NULL COMMENT '数量',`checked` int(11) DEFAULT NULL COMMENT '是否选择,1=已勾选,0=未勾选',`create_time` datetime DEFAULT NULL COMMENT '创建时间',`update_time` datetime DEFAULT NULL COMMENT '更新时间',PRIMARY KEY (`id`),KEY `user_id_index` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=146 DEFAULT CHARSET=utf8;

解释：
~如果你不想字段为空可以设置字段的属性为 NOT NULL，如上实例中的 id 与 user_id 字段，在操作数据库时如果输入该字段的数据为空，就会报错。
~AUTO_INCREMENT 定义列为自增的属性，一般用于主键，数值会自动加 1。
~PRIMARY KEY 关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号 , 分隔。
~ENGINE 设置存储引擎，CHARSET 设置编码。
~UNIQUE KEY 唯一约束，是指所有记录中字段的值不能重复出现。例如，为 user_id 字段加上唯一性约束后，每条记录的 user_id 值都是唯一的，不能出现重复的情况。

删除表

1.删除表，如果存在的话

DROP TABLE IF EXISTS 数据表;

2.直接删除表，不检查是否存在

DROP TABLE 数据表;

向表中插入数据

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

table_name 是你要插入数据的表的名称。
column1, column2, column3, … 是表中的列名。
value1, value2, value3, … 是要插入的具体数值。

示例：
向以下表mmall_cart中再插入一行数据

INSERT INTO `mmall_cart` VALUES ('126', '21', '26', '1', '1', '2017-04-13 21:27:06', '2017-04-13 21:27:06');

更新数据

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

示例：

将上表中的user_id/product_id均改为20

UPDATE mmall_cart SET user_id = '20', product_id = '20' WHERE id = 126;

结果如下：

删除数据

DELETE FROM table_name
WHERE condition;

删除id为127的数据

DELETE FROM mmall_cart WHERE id = 127;

结果为：

查询数据

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT number];

简单示例：

-- 选择所有列的所有行
SELECT * FROM users;-- 选择特定列的所有行
SELECT username, email FROM users;-- 添加 WHERE 子句，选择满足条件的行
SELECT * FROM users WHERE is_active = TRUE;-- 添加 ORDER BY 子句，按照某列的升序排序
SELECT * FROM users ORDER BY birthdate;-- 添加 ORDER BY 子句，按照某列的降序排序
SELECT * FROM users ORDER BY birthdate DESC;-- 添加 LIMIT 子句，限制返回的行数
SELECT * FROM users LIMIT 10;-- 使用 AND 运算符和通配符
SELECT * FROM users WHERE username LIKE 'j%' AND is_active = TRUE;-- 使用 OR 运算符
SELECT * FROM users WHERE is_active = TRUE OR birthdate < '1990-01-01';-- 使用 IN 子句
SELECT * FROM users WHERE birthdate IN ('1990-01-01', '1992-03-15', '1993-05-03');

WHERE 操作符

有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。WHERE 子句用于在 MySQL 中过滤查询结果，只返回满足特定条件的行。
示例：

SELECT * FROM users WHERE username = 'test';

常搭配的逻辑符号有

说明	符号
不等于	<>, !=
等于	=
大于	>
小于	<
大于等于	>=
小于等于	<=

聚合函数

语法：

SELECT 聚合函数(字段列表) FROM 表名;

示例表：

COUNT计数，结果为4

SELECT COUNT(quantity) FROM mmall_cart;

MAX求最大值，结果为9

SELECT MAX(quantity) FROM mmall_cart;

MIN求最小值，结果为1

SELECT MIN(quantity) FROM mmall_cart;

AVG求平均值，结果为4.25

SELECT AVG(quantity) FROM mmall_cart;

SUM求和，结果为17

SELECT SUM(quantity) FROM mmall_cart;

LIKE 子句

LIKE 子句是在 MySQL 中用于在 WHERE 子句中进行模糊匹配的关键字。
示例：

SELECT * FROM customers WHERE last_name LIKE 'S%';

以上 SQL 语句将选择所有姓氏以 ‘S’ 开头的客户。

分组 GROUP BY + HAVING

GROUP BY 语句根据一个或多个列对结果集进行分组。一般配合聚合函数和HAVING使用。
WHERE和HAVING的区别是HAVING可以对分组后的数据进行过滤。

语法：

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

示例：
查询年龄小于45的员工，并根据工作地址分组，获取同一地址员工数量大于等于3的工作地址

SELECT workaddress, COUNT(*) FROM emp WHERE age < 45 GROUP BY workaddress HAVING COUNT(*) >= 3;

ORDER BY(排序) 语句

ORDER BY(排序) 语句可以按照一个或多个列的值进行升序（ASC）或降序（DESC）排序。
语法：

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

示例：

SELECT * FROM mmall_cart ORDER BY user_id;

数据就会按给定字段排序，结果如下：

LIMIT 操作符 分页查询

语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数

示例：
查询第一页员工数据，每页展示十条记录

SELECT * FROM emp LIMIT 10;

多表查询-联合查询 UNION 操作符

UNION：连接数据集关键字，可以将两个查询结果集拼接为一个，会过滤掉相同的记录
UNION ALL：连接数据集关键字，可以将两个查询结果集拼接为一个，不会过滤掉相同的记录
注意：对于联合查询的多张表列数必须保持一致，字段类型也需要保持一致。
作用简单描述就是：表A有a/b两列20条数据，表B有a/b两列30条数据，通过UNION ALL以后可以得到a/b两列50条数据。使用UNION后，会获得不重复的a/b两列数据

语法：

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

示例，查询 product_id>15，quantity<5的数据

SELECT * FROM mmall_cart WHERE product_id > 18
UNION
SELECT * FROM mmall_cart WHERE quantity > 6;

查询结果为

说明，第一条SELECT可以查询出后两条数据，第二条SELECT语句可以查询出第二条数据，拼一起后即为原表后三条数据。

多表查询-连接的使用-JOIN

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

---

---

以下 以员工表emp，部门表dept为例来简单说明。每个部门有对应的多个员工，属于1对N。

内连接

语法：

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

内连接分为隐式和显示连接。对于显示内连接，inner可以省略。

---

---

左连接和右连接
语法：

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

---

---

自连接
自连接是表与自身表相连接

---

---

语句编写和底层执行顺序

语句编写顺序

示例：

SELECT DISTINCT column1, column2
FROM table1
JOIN table2 ON table1.id = table2.foreign_id
WHERE column1 > 100
GROUP BY column1
HAVING COUNT(*) > 1
ORDER BY column1 DESC
LIMIT 10;

MySQL语句底层的执行顺序如下：

1. FROM子句：选择数据来源的表或视图。
2. ON子句：连接条件（如果有的话，例如在JOIN操作中）。
3. JOIN子句：如果有的话，执行连接操作。
4. WHERE子句：过滤条件，只返回符合条件的记录。
5. GROUP BY子句：分组统计的字段。
6. HAVING子句：分组后的过滤条件。
7. SELECT子句：选取特定的列。
8. DISTINCT子句：去除重复数据。
9. ORDER BY子句：结果集的排序顺序。
10. LIMIT子句：限制结果集的数量。