MySQL基础操作指南

连接客户端

使用命令mysql [-hlocalhost] -uroot -p12345678连接MySQL客户端。其中，-h指定主机，本地可省略；-u指定用户名；-p指定密码，密码可不直接输入，且-p和密码间不能有空白字符，输入命令后回车不是结束，可换行书写。

数据类型

数值类型

整数类型

类型	占用存储空间(字节)	无符号数取值范围	有符号数取值范围	含义
TINYINT	1	0~255	-128~127	非常小的整数
SMALLINT	2	0~65535	-32768~32767	小的整数
MEDIUMINT	3	0~16777215	-8388608~8388607	中等大小的整数
INT(别名:INTEGER)	4	0~4294967295	-2147483648~2147483647	标准的整数
BIGINT	8	0~18446744073709551615	-9223372036854775808~9223372036854775807	大整数

浮点数类型

类型	占用存储空间(字节)	绝对值最小非0值	绝对值最大非0值	含义
FLOAT	4	1.175494351E-38	+3.402823466E+38	单精度浮点数
DOUBLE	8	+2.2250738585072014E-308	+1.7976931348623157E+308	双精度浮点数

定点数类型

DECIMAL(M,D)，占用存储空间取决于M和D，取值范围也取决于M和D。

无符号数值类型表示

在数值类型后加UNSIGNED，如INT UNSIGNED表示无符号整数。

日期和时间类型

类型	存储空间要求	取值范围	含义
YEAR	1字节	1901~2155	年份值
DATE	3字节	'1000-01-01'~'9999-12-31'	日期值
TIME	3字节	'-838:59:59'~'838:59:59'	时间值
DATETIME	8字节	'1000-01-01 00:00:00'~'9999-12-31 23:59:59'	日期加时间值
TIMESTAMP	4字节	'1970-01-01 00:00:01'~'2038-01-19 03:14:07'	时间戳

字符串类型

类型	最大长度	存储空间要求	含义
CHAR(M)	M个字符	M×W个字节	固定长度的字符串
VARCHAR(M)	M个字符	L + 1或L + 2个字节	可变长度的字符串
TINYTEXT	255个字节	L + 1个字节	非常小型的字符串
TEXT	65535个字节	L + 2个字节	小型的字符串
MEDIUMTEXT	16777215个字节	L + 3个字节	中等大小的字符串
LONGTEXT	4294967295个字节	L + 4个字节	大型的字符串

ENUM和SET类型

• ENUM('str1', 'str2', 'str3' ⋯)：枚举类型，取值为枚举值中的一个。
• SET('str1', 'str2', 'str3' ⋯)：集合类型，取值可为集合中的0个或多个值。

二进制类型

BIT、BINARY(M)、VARBINARY(M)。

数据库基本操作

1. 查看数据库列表：SHOW DATABASES
2. 创建数据库：CREATE DATABASE <数据库名>；判断是否存在再创建：CREATE DATABASE IF NOT EXISTS <数据库名>
3. 切换数据库：USE <数据库名>
4. 查看当前数据库：SELECT DATABASE()
5. 登录并选择数据库：mysql -h localhost -u root -p123456 [数据库名]
6. 删除数据库：DROP DATABASE <数据库名>；存在即删除：DROP DATABASE IF EXISTS <数据库名>

表的基本操作

1. 查看表列表：SHOW TABLES

2. 创建表：

   CREATE TABLE 表名 (
       列名1 数据类型 [列的属性],
       列名2 数据类型 [列的属性],
       ……
       列名n 数据类型 [列的属性]
   );

   添加注释：

   CREATE TABLE 表名 (
       各个列的信息 ……
   ) COMMENT '表的注释信息';

3. 删除表：DROP TABLE <表1, 表2, ..., 表n>

4. 查看表结构：DESC <表名>

5. 修改表

   • 修改表名：ALTER TABLE 旧表名 RENAME TO 新表名；修改多个表名：RENAME TABLE 旧表名1 TO 新表名1, 旧表名2 TO 新表名2, ... 旧表名n TO 新表名n
   • 转移表：RENAME TABLE 数据库名1.表名1 TO 数据库名2.表名2
   • 增加列：ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [列的属性]
   • 删除列：ALTER TABLE 表名 DROP COLUMN 列名
   • 修改列：ALTER TABLE 表名 MODIFY 列名 新数据类型 [新属性]；ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型 [新属性]

简单操作

简单查询

1. 查询：SELECT 列名 FROM 表名
2. 列的别名：SELECT 列名 [AS] 列的别名 FROM 表名
3. 查询多个列：SELECT 列名1, 列名2, ... 列名n FROM 表名

去重

1. 去除单列重复结果：SELECT DISTINCT 列名 FROM 表名
2. 去除多列重复结果：SELECT DISTINCT 列名1, 列名2, ... 列名n FROM 表名

限制查询结果条数

LIMIT 开始行, 限制条数；只写一个参数时代表限制行数，默认开始行为第0行。

排序

ORDER BY 列名 ASC|DESC，ASC可省略，默认升序。

搜索条件

1. 简单搜索条件：包括=、<>、!=、<、<=、>、>=、BETWEEN、NOT BETWEEN等操作符。
2. 匹配列表中的元素：IN（在列表中）、NOT IN（不在列表中）。
3. 匹配NULL：IS NULL（值为NULL）、IS NOT NULL（值不为NULL）。
4. 多个搜索条件查询：AND（且）、OR（或），可组合使用。
5. 通配符：%代表任意一个字符串；_代表任意一个字符。

函数

文本处理函数

描述	示例结果	调用示例	名称
拼接字符串	-	CONCAT('学号为', number, '的学生在《', subject, '》课程的成绩是:', score)	CONCAT
去除字符串右侧空格	-	RTRIM('abc ')	RTRIM
去除字符串左侧空格	-	LTRIM(' abc')	LTRIM
将字符串转大写	-	UPPER('abc')	UPPER
将字符串转小写	-	LOWER('ABC')	LOWER
获取字符串长度	-	LENGTH('abc')	LENGTH
获取字符串右侧指定长度子串	-	RIGHT('abc', 2)	RIGHT
获取字符串左侧指定长度子串	-	LEFT('abc', 2)	LEFT

日期和时间处理函数

名称	调用示例	示例结果	描述
返回当前日期和时间	NOW()	2019-08-16 17:10:43	-
返回当前日期	CURDATE()	2019-08-16	-
返回当前时间	CURTIME()	17:10:43	-
提取日期	DATE('2019-08-16 17:10:43')	2019-08-16	-
添加时间间隔	DATE_ADD('2019-08-16 17:10:43', INTERVAL 2 DAY)	2019-08-18 17:10:43	-
减去时间间隔	DATE_SUB('2019-08-16 17:10:43', INTERVAL 2 DAY)	2019-08-14 17:10:43	-
计算日期差值	DATEDIFF('2019-08-16', '2019-08-17')	-1	-
格式化日期和时间	DATE_FORMAT(NOW(), '%m-%d-%Y')	08-16-2019	-

数值处理函数

名称	调用示例	示例结果	描述
取绝对值	ABS(-1)	1	-
返回圆周率	PI()	3.141593	-
返回角度的余弦	COS(PI())	-1	-
返回e的指定次方	EXP(1)	2.718281828459045	-
返回除法的余数	MOD(5, 2)	1	-
返回随机数	RAND()	0.7537623539136372	-
返回角度的正弦	SIN(PI()/2)	1	-
返回数的平方根	SQRT(9)	3	-
返回角度的正切	TAN(0)	0	-

聚集函数

函数名	描述
COUNT	返回某列的行数
MAX	返回某列的最大值
MIN	返回某列的最小值
SUM	返回某列值之和
AVG	返回某列的平均值

COUNT有COUNT(*)（统计所有行）和COUNT(列名)（统计特定列，忽略NULL值行）两种用法。聚集函数可搭配DISTINCT使用，如COUNT(DISTINCT major)。

分组查询

1. 创建分组：GROUP BY，如SELECT subject, AVG(score) FROM student_score GROUP BY subject。
2. 带有WHERE子句的分组：先过滤再分组，如SELECT subject, AVG(score) FROM student_score WHERE score >= 60 GROUP BY subject。
3. 分组的过滤条件：HAVING，分组后过滤，如SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING AVG(score) > 73。
4. 分组和排序：结合ORDER BY，如SELECT subject, AVG(score) AS avg_score FROM student_score GROUP BY subject ORDER BY avg_score DESC。
5. 嵌套分组：如SELECT department, major, COUNT(*) FROM student_info GROUP BY department, major。

查询语句中子句顺序：SELECT [DISTINCT] 查询列表 [FROM 表名] [WHERE 布尔表达式] [GROUP BY 分组列表 ] [HAVING 分组过滤条件] [ORDER BY 排序列表] [LIMIT 开始行, 限制条数]。

子查询

子查询需用小括号括起来，包括标量子查询（代表一个值）、列子查询（查询一个列）、行子查询等，还有EXISTS和NOT EXISTS关键字，以及相关子查询和同一个表的子查询。

连接查询

连接查询会生成笛卡尔积，包括内连接和外连接（左外连接、右外连接）。WHERE子句和ON子句在连接查询中有不同语义，内连接中二者等价，外连接中ON子句用于处理驱动表记录在被驱动表无匹配记录的情况。

组合查询

1. 单表组合查询：UNION用于合并多个查询结果，会去除重复行。
2. 多表组合查询：可合并多个表的查询结果。
3. 去除重复的行：UNION ALL可保留所有行，不进行去重。

数据的插入、删除和更新

1. 插入：INSERT INTO表名VALUES(列1的值),(列2的值),….(列n的值)；INSERT ON DUPLICATE KEY UPDATE用于处理插入重复键的情况。
2. 删除数据：DELETE FROM 表名 [WHERE 表达式]
3. 更新数据：UPDATE 表名 SET 列1=值1, 列2=值2, ..., 列n=值n [WHERE 布尔表达式]

视图

1. 创建视图：CREATE VIEW 视图名 AS 查询语句
2. 查看和删除视图：SHOW TABLES可查看视图；DROP VIEW 视图名可删除视图。

自定义变量

使用SET赋值，如SET @a = 1；也可通过查询赋值，如SELECT n1 FROM t1 LIMIT 1 INTO @b，还能同时赋值多个变量。

存储程序

存储函数

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN
    函数体内容
END

可使用SHOW FUNCTION STATUS [LIKE 需要匹配的函数名]查看函数状态，SHOW CREATE FUNCTION 函数名查看函数定义，DROP FUNCTION 函数名删除函数。

存储过程

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    需要执行的语句
END

使用CALL 存储过程([参数列表])调用，通过SHOW PROCEDURE STATUS[LIKE需要匹配的存储过程名称]查看状态，SHOW CREATE PROCEDURE存储过程名称查看定义，DROP PROCEDURE存储过程名称删除存储过程。

游标

用于标记结果集中正在访问的记录。创建游标语法：DECLARE 游标名称 CURSOR FOR 查询语句；使用时需打开、获取记录、关闭游标。