MySQL：常规操作示例

删除：DELETE、DROP、TRUNCATE

• DELETE

删除表内数据。

DELETE FROM 表名 [WHERE 删除的条件]

示例：

DELETE FROM student WHERE name = 'LOLITA0164'

• DROP

删除表，包括表数据和表结构。

DROP TABLE 表名

示例：

DROP TABLE student

• TRUNCATE

清除表内所有数据，保留表结构。

TRUNCATE TABLE 表名

示例：

TRUNCATE TABLE student

---

插入：INSERT INTO

INSERT INTO 表名 ( field1, field2, ... filedN ) VALUES ( value1, value2, ... valueN ) 

示例：

INSERT INTO student
( name, age, sex)
VALUES
( 'LOLITA0164', 26, 'male')

---

查询

• SELECT

基本的查询语句

SELECT 字段1,字段2,... 
FROM 表名
[WHERE 查询条件]
[LIMIT N] [OFFSET M]

LIMIT：限制数量
OFFSET：偏移量

示例：

SELECT id,name,age,sex
FROM student
WHERE name IS NOT NULL
LIMIT 2 OFFSET 1

• WHERE ：限制条件

SELECT 字段1, 字段2, ... , 字段N
FROM 表1, 表2, ... 
[WHERE 条件1 [AND [OR]] 条件2 ...

示例：

SELECT name,math,english,physical
FROM student,grade
WHERE student.number = grade.number
AND student.name IS NOT NULL

注：WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。

• ORDER BY：排序

排序

SELECT 字段1, 字段2, ... 字段N 
FROM 表1, 表2...
[WHERE 条件1 [AND [OR]] 条件2 ...
ORDER BY 字段1, [字段2...] [ASC [DESC]]

示例：

SELECT s.name, g.math, g.english, g.physical
FROM student as s, grade as g
WHERE s.number = g.number
ORDER BY s.number DESC

• GROUP BY：结果分组

SELECT 字段1, function(字段名)
FROM 表名
[WHERE 条件1 [AND [OR]] 条件2 ...
GROUP BY 用于分组的字段

注：group by 可以实现一个最简单的去重查询。

示例：

SELECT count(*) as total, sex
FROM student
GROUP BY sex

分组后的限定条件使用 HAVING 来设置，WHERE 则是对原始数据进行条件限制。几个关键字的使用顺序为 where、group by、having、order by：

SELECT count(*) as total, sex
FROM student
WHERE age > 10
GROUP BY sex
HAVING total > 1
ORDER BY name DESC

• NULL：处理空值，过滤或者选择

a. IS NULL: 当列的值是 NULL,此运算符返回 true
b. IS NOT NULL: 当列的值不为 NULL, 运算符返回 true
c. <=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true

示例：

SELECT *
FROM student
WHERE sex IS [NOT] NULL

在 MySQL 中，可以使用 ifnull 来处理空值的情况
示例：

SELECT name, IFNULL(sex, '未知') as sex
FROM student

注：IFNULL 第一个参数表示匹配的字段，如果该字段为 NULL 时，则使用第二个字段替换

• LIKE：匹配/模糊匹配

SELECT 字段1, 字段2, ... 字段N 
FROM 表名
WHERE 字段 LIKE 条件1 [AND [OR]] 条件2 ...

示例：

SELECT *
FROM student
WHERE student.name like 'xiao%'

like 会与 % 和 _ 结合使用

‘%a’ ：以a结尾的数据
‘a%’ ：以a开头的数据
‘%a%’ ：含有a的数据
‘a’ ：三位且中间字母是a的
‘_a’ ：两位且结尾字母是a的
‘a_’ ：两位且开头字母是a的

注：你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

• UNION：合并查询

用于从多个 SELECT 语句的结果集合并到同一个结果集中，查询的字段必须相同

SELECT 字段1, 字段2, ... 字段N
FROM 表1
[WHERE 条件]
UNION [ALL | DISTINCT]
SELECT 字段1, 字段2, ... 字段N
FROM 表2
[WHERE 条件]

示例：

SELECT s.number
FROM student as s
UNION ALL
SELECT g.number
FROM grade as g

ALL：可选，返回所有结果集，包含重复数据。
DISTINCT：可选，删除结果集中重复的数据，默认可不写。

• JOIN：连接查询

多张表查询

SELECT 字段1,字段2,... 
FROM 表1
[INNER、LEFT、RIGHT] JOIN 表2
ON 连接查询条件

示例：

SELECT name,math,english,physical
FROM student
INNER JOIN grade
ON student.number = grade.number

a. INNER JOIN (内连接)：获取两张表中字段匹配关系的记录，等同于 JOIN

b. LEFT JOIN (左连接)：左边为主表，无伦右表有无对应的数据

c. RIGHT JOIN (右连接)：和左连接相反

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

---

修改：ALTER

• 修改表名

ALTER TABLE 表名 RENAME TO 新表名

示例：

ALTER TABLE student RENAME TO Student

• 新增、删除、修改表结构

新增字段

ALTER TABLE 表 ADD 字段名 类型 ... 其他约束

示例：

ALTER TABLE student ADD address VARCHAR(45) FIRST
ALTER TABLE student ADD height INT AFTER name

删除

ALTER TABLE 表 DROP 字段 

示例：

ALTER TABLE student DROP address 

修改

a. 修改字段类型及名称 : MODIFY 和 CHANGE

MODIFY：关键字之后直接跟上字段和类型

ALTER TABLE student MODIFY sex VARCHAR(11)

CHANGE：关键字之后紧跟着的是你要修改的字段名，然后指定新字段名及类型

ALTER TABLE student CHANGE sex SEX VARCHAR(10)

b. 修改字段默认值

ALTER TABLE student ALTER sex SET DEFAULT '未知'

---

索引

• 显示索引信息

SHOW INDEX FROM 表名

• 主键：特殊的索引

ALTER TABLE 表名 ADD PRIMARY KEY (column_list)

• 普通索引：索引值可出现多次

ALTER TABLE 表名 ADD INDEX 索引名 (column_list)

示例：

ALTER TABLE student ADD INDEX name_index (name)

• 唯一索引：索引的值必须是唯一的

ALTER TABLE 表名 ADD UNIQUE 索引名 (column_list)

• 全文索引：FULLTEXT

ALTER TABLE 表名 ADD FULLTEXT 索引名 (column_list)

• 删除索引

ALTER TABLE 表名 DROP PRIMARY KEY		// 删除主键
DROP INDEX [字段名] ON 表名	       			 		// 删除索引

---

复制表数据

• 只复制表结构

CREATE TABLE 新表 LIKE 旧表 

• 复制表结构和数据

CREATE TABLE 新表 LIKE 旧表 
INSERT INTO 新表 SELECT 字段1, 字段2, ... FROM 旧表
[WHERE 条件]

---

重复数据处理

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

• 统计重复数据

SELECT COUNT(*) as total, age
FROM student
GROUP BY age
HAVING total > 1	

注：在 GROUP BY 之后的限制条件使用 HAVING 关键字。

• 过滤重复的数据

SELECT DISTINCT age
FROM student

也可以使用上面提到的 GROUP BY 来实现：

SELECT age
FROM student
GROUP BY age

• 删除重复数据

a. 我们提取出旧表中的不重复的数据新建表，将旧表删除，再重新命名为新表。

CREATE TABLE tmp LIKE student					// 创建新表，只保留表结构
INSERT INTO tmp SELECT * FROM student GROUP BY name	// 选择不重复数据填充新表
DROP TABLE student			// 删除旧表
ALTER TABLE tmp RENAME TO student		// 新表更名为旧表

b. 我们提取出重复的数据，在保留最小序列号的数据下删除其他数据。

DELETE FROM student 
WHERE name 
IN 		// 条件1：重复的部分
(
	SELECT a.name FROM (
		SELECT name FROM student GROUP BY name HAVING COUNT(*) > 1  // 重复数据
	) as a
)
AND id NOT IN 		// 条件2：序列号最小
(
	SELECT b.id FROM (
		SELECT MIN(id) as id FROM student GROUP BY name HAVING count(*) > 1  // 重复数据中最小序列号
	) as b
)