MySQL
MySQL 相关语法
MySQL 注释符号
MySQL 支持单行注释和多行注释两种形式,具体规则如下:
- 单行注释
#
:从#
开始到行尾的内容均为注释,属于 MySQL 特有注释方式。--
:ANSI 标准注释符,从--
开始到行尾为注释。- 示例:
SELECT * FROM users; # 单行注释(MySQL特有) SELECT * FROM users; -- 单行注释(ANSI标准)
- 多行注释
/* */
:可跨越多行,注释内容需包裹在/*
和*/
之间。- 示例:
/* 多行注释 可跨越多行 */
数据库操作
创建数据库
- 基本语法:
CREATE DATABASE [IF NOT EXISTS] database_name [CHARACTER SET charset_name] [COLLATE collation_name];
- 参数说明:
IF NOT EXISTS
:可选,避免因数据库已存在而执行命令时报错。CHARACTER SET charset_name
:可选,指定数据库的字符集,如utf8mb4
。COLLATE collation_name
:可选,指定数据库的排序规则,如utf8mb4_general_ci
。
- 示例:
-- 指定字符集和排序规则创建数据库 CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;-- 避免数据库已存在报错 CREATE DATABASE IF NOT EXISTS mydatabase;
删除数据库
- 基本语法:
DROP DATABASE <database_name>; -- 直接删除数据库,不检查是否存在 DROP DATABASE [IF EXISTS] <database_name>; -- 检查数据库是否存在,存在则删除
- 参数说明:
IF EXISTS
:可选,避免因数据库不存在而执行删除命令时报错。database_name
:需删除的数据库名称。
- 示例:
-- 直接删除数据库,不检查存在性 DROP DATABASE mydatabase;-- 检查数据库存在后删除 DROP DATABASE IF EXISTS mydatabase;
选择数据库
- 基本语法:
USE database_name;
- 参数说明:
database_name
为要切换使用的数据库名称,执行该命令后,后续操作将默认针对该数据库进行。
数据类型
MySQL 支持多种数据类型,可分为数值、日期/时间、字符串、枚举和集合、空间数据五大类,各类别详细信息如下:
数值类型
MySQL 支持标准 SQL 数值类型及扩展整数类型,具体存储大小、用途如下表:
类型 | 大小(bytes) | 用途 |
---|---|---|
TINYINT | 1 | 小整数值 |
SMALLINT | 2 | 大整数值 |
MEDIUMINT | 3 | 大整数值 |
INT 或 INTEGER | 4 | 大整数值 |
BIGINT | 8 | 极大整数值 |
FLOAT | 4 | 单精度浮点数值 |
DOUBLE | 8 | 双精度浮点数值 |
DECIMAL(M, N) | M 为最大位数(1-65,默认10),D 为小数位(0-30,不大于M,默认0) | 小数值(精确存储) |
- 说明:
INT
是INTEGER
的同义词,DEC
是DECIMAL
的同义词;BIT
类型可保存位字段值,支持 MyISAM、MEMORY、InnoDB 和 BDB 表。
日期和时间类型
用于表示时间值,各类型的存储大小、范围、格式及用途如下:
类型 | 大小(bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01~9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59'~'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901~2155 | YYYY | 年份值 |
DATETIME | 8 | '1000-01-01 00:00:00'~'9999-12-31 23:59:59' | YYYY-MM-DD hh:mm:ss | 混合日期和时间值 |
TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC~'2038-01-19 03:14:07' UTC(北京时间 2038-01-19 11:14:07) | YYYY-MM-DD hh:mm:ss | 混合日期和时间值(带自动更新特性) |
- 说明:各时间类型有有效值范围和“零”值,当指定不合法的时间值时,将使用“零”值填充。
字符串类型
包含定长、变长字符串及二进制、文本类型,具体信息如下:
类型 | 大小(bytes) | 用途 |
---|---|---|
CHAR | 0-255 | 定长字符串 |
VARCHAR | 0-65535 | 变长字符串 |
TINYBLOB | 0-255 | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 | 短文本字符串 |
BLOB | 0-65535 | 二进制形式的长文本数据 |
TEXT | 0-65535 | 长文本数据 |
MEDIUMBLOB | 0-16777215 | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16777215 | 中等长度文本数据 |
LONGBLOB | 0-4294967295 | 二进制形式的极大文本数据 |
LONGTEXT | 0-4294967295 | 极大文本数据 |
- 说明:
CHAR(n)
和VARCHAR(n)
中n
代表字符数,而非字节数;CHAR
是定长存储,VARCHAR
是变长存储,二者在尾部空格保留规则上也有差异。BINARY
和VARBINARY
类似CHAR
和VARCHAR
,但存储二进制字符串,无字符集,排序和比较基于字节数值。BLOB
和TEXT
按存储大小分不同级别,需根据数据量选择合适类型。
枚举和集合类型
- ENUM:枚举类型,用于存储单一值,值需从预定义的集合中选择。
- SET:集合类型,用于存储多个值,值需从预定义的集合中选择。
空间数据类型
包括 GEOMETRY
、POINT
、LINESTRING
、POLYGON
、MULTIPOINT
、MULTILINESTRING
、MULTIPOLYGON
、GEOMETRYCOLLECTION
,主要用于存储空间数据(如地理信息、几何图形等)。
数据表操作
创建数据表
- 创建要求:需指定表名、表字段名及每个字段的数据类型,可额外设置约束(如主键、自增长、非空等)、字符集和排序规则。
- 基本语法:
CREATE TABLE table_name (column1 datatype [constraint],column2 datatype [constraint],... ) [CHARACTER SET charset_name] [COLLATE collation_name];
- 参数说明:
table_name
:要创建的表名。column1, column2...
:表中的列名。datatype
:各列的数据类型。constraint
:可选约束,如PRIMARY KEY
(主键)、AUTO_INCREMENT
(自增长)、NOT NULL
(非空)、DEFAULT
(默认值)等。
- 示例:
-- 创建用户表 users CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, -- 自增长主键username VARCHAR(50) NOT NULL, -- 非空用户名email VARCHAR(100) NOT NULL, -- 非空邮箱birthdate DATE, -- 生日(日期类型)is_active BOOLEAN DEFAULT TRUE -- 激活状态(默认true) );-- 指定字符集和排序规则创建表 CREATE TABLE mytable (id INT PRIMARY KEY,name VARCHAR(50) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
删除数据表
- 基本语法:
DROP TABLE table_name; -- 直接删除表,不检查是否存在 DROP TABLE [IF EXISTS] table_name; -- 检查表存在后删除
- 参数说明:
IF EXISTS
可选,避免因表不存在而报错;table_name
为要删除的表名。 - 清空表数据(保留表结构):
TRUNCATE TABLE table_name;
- 注意事项:
- 删除表前需备份重要数据,避免数据丢失。
- 若表与其他表存在外键约束,需先处理外键关系(如删除约束),再执行删除操作。
临时表
临时表仅在当前连接可见,连接关闭时自动删除,适合存储会话内的临时数据。
- 创建语法:
-- 直接定义字段创建 CREATE TEMPORARY TABLE temp_table_name (column1 datatype,column2 datatype,... );-- 基于查询结果创建 CREATE TEMPORARY TABLE temp_table_name AS SELECT column1, column2, ... FROM source_table WHERE condition;
- 数据操作:
- 插入数据:
INSERT INTO temp_table_name (column1, column2, ...) VALUES (value1, value2, ...);
- 查询数据:
SELECT * FROM temp_table_name;
- 修改表结构(与普通表类似):
ALTER TABLE temp_table_name ADD COLUMN new_column datatype;
- 手动删除(可选,连接关闭会自动删除):
DROP TEMPORARY TABLE IF EXISTS temp_table_name;
- 插入数据:
- 示例:
-- 创建临时表(基于orders表2023年及以后的数据) CREATE TEMPORARY TABLE temp_orders AS SELECT * FROM orders WHERE order_date >= '2023-01-01';-- 插入数据到临时表 INSERT INTO temp_orders (order_id, customer_id, order_date) VALUES (1001, 1, '2023-01-05');-- 查询临时表 SELECT * FROM temp_orders;-- 手动删除临时表 DROP TEMPORARY TABLE IF EXISTS temp_orders;
复制表
需完整复制表结构(含索引、约束等)和数据,步骤如下:
- 获取原表完整结构:通过
SHOW CREATE TABLE
命令获取创建原表的 SQL 语句(含结构、索引、引擎等信息)。SHOW CREATE TABLE score;
- 创建复制表(克隆表结构):复制步骤1中获取的 SQL 语句,修改表名后执行,创建与原表结构一致的新表。
CREATE TABLE copy_score (id int NOT NULL,jz double NOT NULL,java double NOT NULL,class int NOT NULL,PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
- 复制表数据:使用
INSERT INTO ... SELECT
语句将原表数据插入复制表。INSERT INTO copy_score (id, jz, java, class) SELECT id, jz, java, class FROM score;
数据操作
插入数据
- 基本语法:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
- 参数说明:
table_name
:要插入数据的表名。column1, column2...
:要插入数据的列名(可选,若省略则需按表结构顺序插入所有列数据)。value1, value2...
:要插入的具体值,字符型数据需用单引号或双引号包裹。
- 示例:
-- 指定列插入单行数据 INSERT INTO users (username, email, birthdate, is_active) VALUES ('test', 'test@qq.com', '1990-01-01', true);-- 省略列名插入单行数据(自增长列用NULL占位) INSERT INTO users VALUES (NULL, 'test', 'test@qq.com', '1990-01-01', true);-- 插入多行数据 INSERT INTO users (username, email, birthdate, is_active) VALUES ('test1', 'test1@qq.com', '1985-07-10', true), ('test2', 'test2@qq.com', '1988-11-25', false), ('test3', 'test3@qq.com', '1993-05-03', true);
查询数据
查询数据是 MySQL 核心操作之一,支持单表查询、多表查询、去重查询、子查询等多种方式,具体如下:
单表查询
- 基本语法:
SELECT column1, column2, ... FROM table_name [WHERE condition] [ORDER BY column_name [ASC | DESC]] [LIMIT number];
- 参数说明:
column1, column2...
:要查询的列名,*
表示查询所有列。table_name
:数据来源表名。WHERE condition
:可选,过滤数据的条件(如is_active = TRUE
)。ORDER BY column_name [ASC | DESC]
:可选,按指定列排序,ASC
为升序(默认),DESC
为降序。LIMIT number
:可选,限制返回的行数。
- 示例:
-- 查询所有列的所有行 SELECT * FROM users;-- 查询指定列(用户名、邮箱)的所有行 SELECT username, email FROM users;-- 带条件查询(查询激活状态为true的用户) SELECT * FROM users WHERE is_active = TRUE;-- 排序查询(按生日升序) SELECT * FROM users ORDER BY birthdate;-- 排序+限制行数(按生日降序,返回前10条) SELECT * FROM users ORDER BY birthdate DESC LIMIT 10;-- 条件+模糊匹配(用户名以j开头且激活的用户) SELECT * FROM users WHERE username LIKE 'j%' AND is_active = TRUE;
去重查询
通过 DISTINCT
关键字去除查询结果中的重复数据,语法如下:
SELECT DISTINCT column1, column2, ...
FROM table_name;
- 示例:查询用户表中不重复的邮箱(若存在同一邮箱多次存储的情况)
SELECT DISTINCT email FROM users;
多表查询
多表查询用于关联多个表的数据,常见类型包括内连接、外连接、自连接,具体如下:
内连接(INNER JOIN)
取两个表的交集数据,仅返回两表中匹配条件的行。
- 语法:
-- 方式1:逗号分隔表,WHERE指定连接条件 SELECT column1, ... FROM table1 [other_name], table2 [other_name] WHERE condition;-- 方式2:INNER JOIN 关键字,ON指定连接条件(推荐) SELECT column1, ... FROM table1 [other_name] [INNER] JOIN table2 [other_name] ON condition;
- 示例(查询学生及其所属班级信息):
-- 带表别名,简化SQL SELECT s.id, s.name AS student_name, c.name AS class_name FROM student s INNER JOIN class c ON s.class_id = c.id;
外连接
外连接分为左外连接和右外连接,可保留其中一个表的所有数据,另一个表无匹配数据时用 NULL
填充。
- 左外连接(LEFT JOIN):以左表为基准,保留左表所有数据,右表无匹配则填充
NULL
。SELECT column1, ... FROM table1 LEFT [OUTER] JOIN table2 ON condition;
- 右外连接(RIGHT JOIN):以右表为基准,保留右表所有数据,左表无匹配则填充
NULL
。SELECT column1, ... FROM table1 RIGHT [OUTER] JOIN table2 ON condition;
- 示例:
-- 左外连接(查询所有学生,含无考试成绩的学生) SELECT s.name, sc.score FROM student s LEFT JOIN score sc ON s.student_id = sc.student_id;-- 右外连接(查询所有班级,含无学生的班级) SELECT c.name AS class_name, s.name AS student_name FROM student s RIGHT JOIN class c ON s.class_id = c.id;
自连接
将表自身视为两个表进行连接,实现行与行之间的比较(如查询员工及其领导信息)。
- 语法:
SELECT column1, ... FROM table [other_name1] JOIN table [other_name2] ON condition;
- 示例(查询员工及其领导姓名):
-- 员工表emp含id、name、manager_id(领导id)字段 SELECT a.name AS employee_name, b.name AS manager_name FROM emp a LEFT JOIN emp b ON a.manager_id = b.id;
子查询
子查询是嵌套在主查询中的 SELECT
语句,按返回结果可分为单行子查询、多行子查询、多列子查询。
- 单行子查询:返回单行数据,主查询中用
=
匹配。-- 查询与“许仙”同班级的同学(排除许仙) SELECT name FROM student WHERE class_id = (SELECT class_idFROM studentWHERE name = '许仙' ) AND name != '许仙';
- 多行子查询:返回多行数据,主查询中用
IN
匹配。-- 查询语文、英文课程的成绩 SELECT * FROM score WHERE course_id IN (SELECT course_idFROM courseWHERE name = '语文' OR name = '英文' );
- 多列子查询:返回多列数据,主查询中用多列匹配。
-- 查询重复的分数记录(同一学生、同一课程、同一分数) SELECT * FROM score WHERE (student_id, course_id, score) IN (SELECT student_id, course_id, scoreFROM scoreGROUP BY student_id, course_id, scoreHAVING COUNT(*) > 1 );
合并查询(UNION)
用 UNION
或 UNION ALL
合并多个 SELECT
语句的结果,需保证各 SELECT
列数和对应数据类型一致。
- 语法:
SELECT column1, ... FROM table1 [WHERE condition1] UNION [ALL] SELECT column1, ... FROM table2 [WHERE condition2] [ORDER BY column1, ...];
- 区别:
UNION
:自动去除合并结果中的重复行。UNION ALL
:不去除重复行,性能优于UNION
。
- 示例:
-- 合并客户表和供应商表的城市(去重) SELECT city FROM customers UNION SELECT city FROM suppliers ORDER BY city;-- 合并客户表和供应商表的城市(不去重) SELECT city FROM customers UNION ALL SELECT city FROM suppliers ORDER BY city;
更新数据
- 基本语法:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
- 参数说明:
table_name
:要更新数据的表名。column1 = value1...
:要更新的列及对应新值。WHERE condition
:可选,指定更新的行范围,若省略则更新表中所有行(需谨慎)。
- 示例:
-- 更新单个列(员工ID为101的薪资改为60000) UPDATE employees SET salary = 60000 WHERE employee_id = 101;-- 更新多个列(订单ID为1001的状态改为“已发货”,发货日期设为2023-03-01) UPDATE orders SET status = 'Shipped', ship_date = '2023-03-01' WHERE order_id = 1001;-- 基于表达式更新(电子产品类别价格上涨10%) UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';
删除数据
- 基本语法:
DELETE FROM table_name WHERE condition;
- 参数说明:
table_name
:要删除数据的表名。WHERE condition
:可选,指定删除的行范围,若省略则删除表中所有行(需谨慎)。
- 示例:
-- 删除指定条件数据(2021届毕业生) DELETE FROM students WHERE graduation_year = 2021;-- 删除所有数据(保留表结构) DELETE FROM orders;-- 基于子查询删除(删除2023年1月1日前有订单的客户) DELETE FROM customers WHERE customer_id IN (SELECT customer_idFROM ordersWHERE order_date < '2023-01-01' );
查询进阶功能
WHERE 子句
WHERE
子句用于过滤查询、更新、删除操作的数据,支持多种条件运算符,具体如下:
比较运算符
操作符 | 描述 | 实例(A=10,B=20) |
---|---|---|
= | 等于,检测两值是否相等,相等返回true | (A = B) → false |
<>, != | 不等于,检测两值是否不相等,不相等返回true | (A != B) → true |
> | 大于,检测左值是否大于右值,是则返回true | (A > B) → false |
< | 小于,检测左值是否小于右值,是则返回true | (A < B) → true |
>= | 大于等于,检测左值是否大于等于右值,是则返回true | (A >= B) → false |
<= | 小于等于,检测左值是否小于等于右值,是则返回true | (A <= B) → true |
常见条件示例
-- 等于条件(查询用户名为test的用户)
SELECT * FROM users WHERE username = 'test';-- 不等于条件(查询用户名不是runoob的用户)
SELECT * FROM users WHERE username != 'runoob';-- 大于条件(查询价格大于50的商品)
SELECT * FROM products WHERE price > 50.00;-- 小于条件(查询2023年1月1日前的订单)
SELECT * FROM orders WHERE order_date < '2023-01-01';-- 逻辑运算符(AND:电子产品且价格大于100)
SELECT * FROM products WHERE category = 'Electronics' AND price > 100.00;-- 模糊匹配(LIKE:名字以J开头的客户)
SELECT * FROM customers WHERE first_name LIKE 'J%';-- IN条件(查询国家代码为US、CA、MX的国家)
SELECT * FROM countries WHERE country_code IN ('US', 'CA', 'MX');-- BETWEEN条件(查询2023年全年的订单)
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';-- NULL值判断(查询部门为空的员工)
SELECT * FROM employees WHERE department IS NULL;
LIKE 子句
LIKE
子句用于模糊匹配字符串,常与通配符 %
(匹配零个或多个任意字符)、_
(匹配单个任意字符)搭配使用。
- 基本语法:
SELECT column1, ... FROM table_name WHERE column_name LIKE pattern;
- 示例:
-- %通配符(查询姓氏以S开头的客户) SELECT * FROM customers WHERE last_name LIKE 'S%';-- _通配符(查询产品名第二个字符为a的产品) SELECT * FROM products WHERE product_name LIKE '_a%';-- 组合通配符(查询用户名以a开头、含o、最后一个字符任意的用户) SELECT * FROM users WHERE username LIKE 'a%o_';
ORDER BY 语句
ORDER BY
用于对查询结果排序,支持单列、多列排序,可指定排序方向。
- 基本语法:
SELECT column1, ... FROM table_name ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;
- 示例:
-- 单列排序(产品名升序) SELECT * FROM products ORDER BY product_name ASC;-- 多列排序(部门ID升序,雇佣日期降序) SELECT * FROM employees ORDER BY department_id ASC, hire_date DESC;-- 按表达式排序(按折扣后价格降序) SELECT product_name, price * discount_rate AS discounted_price FROM products ORDER BY discounted_price DESC;-- NULL值排序(MySQL 8.0.16+,价格降序,NULL值放最后) SELECT product_name, price FROM products ORDER BY price DESC NULLS LAST;
GROUP BY 语句
GROUP BY
按一个或多个列对结果集分组,常与聚合函数(COUNT
、SUM
、AVG
、MAX
、MIN
)结合使用,实现数据汇总。
- 基本语法:
SELECT column1, aggregate_function(column2) FROM table_name [WHERE condition] GROUP BY column1;
- 参数说明:
column1
:分组依据列。aggregate_function(column2)
:对分组后的数据执行的聚合操作(如SUM(order_amount)
计算每组订单总金额)。
- 示例(按客户ID分组,计算每个客户的订单总金额):
SELECT customer_id, SUM(order_amount) AS total_amount FROM orders GROUP BY customer_id;
WITH ROLLUP
WITH ROLLUP
可在分组统计基础上,额外生成汇总统计行(如所有组的总计)。
- 示例:
-- 按姓名分组统计登录次数,加汇总行 SELECT name, SUM(signin) AS signin_count FROM employee_tbl GROUP BY name WITH ROLLUP;-- 用COALESCE替换汇总行的NULL(将NULL改为“总数”) SELECT COALESCE(name, '总数'), SUM(signin) AS signin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
- COALESCE 函数:用于替换
NULL
值,语法COALESCE(a, b, c)
,返回第一个非NULL
的值(若全为NULL
则返回NULL
)。
NULL 值处理
NULL
表示缺失或未知数据,处理时需使用专用运算符和函数,避免用 =
或 !=
判断。
- 核心运算符:
IS NULL
:列值为NULL
时返回true
。IS NOT NULL
:列值不为NULL
时返回true
。<=>
:严格比较,两值相等或都为NULL
时返回true
。
- 处理示例:
-- 检查NULL值(查询部门为空的员工) SELECT * FROM employees WHERE department_id IS NULL;-- 替换NULL值(COALESCE:库存为空时返回0) SELECT product_name, COALESCE(stock_quantity, 0) AS actual_quantity FROM products;-- 替换NULL值(IFNULL:MySQL特有,库存为空时返回0) SELECT product_name, IFNULL(stock_quantity, 0) AS actual_quantity FROM products;-- 聚合函数处理NULL(AVG:薪资为空时视为0,计算平均薪资) SELECT AVG(COALESCE(salary, 0)) AS avg_salary FROM employees;
正则表达式
MySQL 用 REGEXP
或 RLIKE
运算符支持正则表达式匹配,二者功能相同,可实现复杂字符串查询。
常用正则模式
模式 | 描述 |
---|---|
^ | 匹配输入字符串的开始位置 |
$ | 匹配输入字符串的结束位置 |
. | 匹配除 \n 外的任意单个字符 |
[...] | 字符集合,匹配其中任意一个字符(如 [abc] 匹配 a /b /c ) |
[^...] | 负值字符集合,匹配不在集合中的任意字符 |
p1|p2|p3 | 匹配 p1、p2 或 p3(如 z|food 匹配 z 或 food ) |
* | 匹配前面的子表达式零次或多次(如 zo* 匹配 z 、zo 、zoo ) |
+ | 匹配前面的子表达式一次或多次(如 zo+ 匹配 zo 、zoo ,不匹配 z ) |
匹配前面的子表达式 n 次(如 o{2} 匹配 oo ) |
|
匹配前面的子表达式最少 n 次、最多 m 次 |
示例
-- 匹配以st开头的姓名
SELECT name FROM person_tbl WHERE name REGEXP '^st';-- 匹配以ok结尾的姓名
SELECT name FROM person_tbl WHERE name REGEXP 'ok$';-- 匹配含mar的姓名
SELECT name FROM person_tbl WHERE name REGEXP 'mar';-- 区分大小写匹配(BINARY:匹配含apple的产品名,区分大小写)
SELECT * FROM products WHERE product_name REGEXP BINARY 'apple';-- 匹配多个条件(姓氏为Smith或Johnson的员工)
SELECT * FROM employees WHERE last_name REGEXP 'Smith|Johnson';
事务
事务是一组不可分割的 SQL 操作,要么全部执行,要么全部回滚,仅 InnoDB 引擎支持事务。
事务特性(ACID)
- 原子性(Atomicity):事务中的操作要么全成功,要么全失败(失败则回滚到初始状态)。
- 一致性(Consistency):事务前后数据库完整性不变(如数据约束、逻辑规则未被破坏)。
- 隔离性(Isolation):多事务并发执行时,相互不干扰,避免数据不一致(有4个隔离级别)。
- 持久性(Durability):事务提交后,数据修改永久有效,即使系统故障也不丢失。
事务控制语句
语句 | 功能 |
---|---|
BEGIN 或 START TRANSACTION | 显式开启事务 |
COMMIT | 提交事务,使修改永久生效 |
ROLLBACK | 回滚事务,撤销未提交的修改 |
SAVEPOINT identifier | 创建事务保存点(可回滚到指定点,不影响保存点前的操作) |
RELEASE SAVEPOINT identifier | 删除保存点 |
ROLLBACK TO identifier | 回滚到指定保存点 |
SET TRANSACTION | 设置事务隔离级别(如 SET TRANSACTION ISOLATION LEVEL READ COMMITTED ) |
事务示例
-- 开启事务(转账:用户1余额减100,用户2余额加100)
START TRANSACTION;-- 执行SQL操作
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;-- 条件判断(假设通过自定义逻辑判断是否提交)
IF (/* 转账成功条件 */) THENCOMMIT; -- 提交事务
ELSEROLLBACK; -- 回滚事务
END IF;-- 带保存点的事务示例
BEGIN;
INSERT INTO test_transaction_test VALUE(5); -- 操作1
SAVEPOINT sp1; -- 创建保存点
INSERT INTO test_transaction_test VALUE(6); -- 操作2
ROLLBACK TO sp1; -- 回滚到保存点(操作2被撤销,操作1保留)
COMMIT; -- 提交事务(仅操作1生效)
ALTER 命令
ALTER
命令用于修改数据库、表、索引等对象的结构,支持添加/删除列、修改列类型、添加约束等操作。
常用 ALTER 操作
操作 | 语法 | 示例 |
---|---|---|
添加列 | ALTER TABLE table_name ADD COLUMN new_column_name datatype; | ALTER TABLE employees ADD COLUMN birth_date DATE; |
修改列数据类型 | ALTER TABLE table_name MODIFY COLUMN column_name new_datatype; | ALTER TABLE employees MODIFY COLUMN salary DECIMAL(10,2); |
修改列名 | ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name datatype; | ALTER TABLE employees CHANGE COLUMN old_name new_name VARCHAR(255); |
删除列 | ALTER TABLE table_name DROP COLUMN column_name; | ALTER TABLE employees DROP COLUMN birth_date; |
添加主键 | ALTER TABLE table_name ADD PRIMARY KEY (column_name); | ALTER TABLE employees ADD PRIMARY KEY (employee_id); |
添加外键 | ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent_table (col); | ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id); |
修改表名 | ALTER TABLE old_table_name RENAME TO new_table_name; | ALTER TABLE employees RENAME TO staff; |
索引
索引是提高查询效率的重要数据结构,类似书籍目录,可快速定位数据,但会占用存储空间且影响增删改性能,需合理设计。
索引类型
- 普通索引:最基础的索引,无唯一性约束,仅用于加速查询。
- 唯一索引:索引值唯一,避免数据重复(如邮箱唯一索引)。
索引操作
创建索引
方式 | 语法 | 示例 |
---|---|---|
CREATE INDEX | CREATE INDEX index_name ON table_name (column1 [ASC | DESC], ...); |
ALTER TABLE | ALTER TABLE table_name ADD INDEX index_name (column1 [ASC | DESC], ...); |
创建表时定义 | CREATE TABLE table_name (..., INDEX index_name (column1, ...)); | CREATE TABLE students (id INT PRIMARY KEY, age INT, INDEX idx_age (age)); |
创建唯一索引
方式 | 语法 | 示例 |
---|---|---|
CREATE UNIQUE INDEX | CREATE UNIQUE INDEX index_name ON table_name (column1, ...); | CREATE UNIQUE INDEX idx_email ON employees (email); |
ALTER TABLE | ALTER TABLE table_name ADD CONSTRAINT idx_name UNIQUE (column1, ...); | ALTER TABLE employees ADD CONSTRAINT idx_email UNIQUE (email); |
创建表时定义 | CREATE TABLE table_name (..., CONSTRAINT idx_name UNIQUE (column1, ...)); | CREATE TABLE employees (id INT PRIMARY KEY, email VARCHAR(100), CONSTRAINT idx_email UNIQUE (email)); |
删除索引
方式 | 语法 | 示例 |
---|---|---|
DROP INDEX | DROP INDEX index_name ON table_name; | DROP INDEX idx_age ON employees; |
ALTER TABLE | ALTER TABLE table_name DROP INDEX index_name; | ALTER TABLE employees DROP INDEX idx_age; |
索引效果对比
- 无索引:查询
empno=998877
的员工,耗时 6.44 秒。SELECT * FROM EMP WHERE empno=998877; -- 耗时6.44sec
- 有索引:为
empno
列添加索引后,查询耗时 0.01 秒。ALTER TABLE EMP ADD INDEX(empno); -- 创建索引 SELECT * FROM EMP WHERE empno=998877; -- 耗时0.01sec
元数据
元数据是关于数据库及对象(表、列、索引等)的信息,存储在 information_schema
系统数据库中,可通过查询系统表获取。
常用元数据查询
需求 | SQL 语句 |
---|---|
查看所有数据库 | SHOW DATABASES; |
查看数据库中所有表 | USE database_name; SHOW TABLES; |
查看表结构 | DESC table_name; 或 SHOW COLUMNS FROM table_name; |
查看表索引 | SHOW INDEX FROM table_name; |
查看表创建语句 | SHOW CREATE TABLE table_name; |
查看列信息 | SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='db_name' AND TABLE_NAME='table_name'; |
查看外键信息 | SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='db_name' AND TABLE_NAME='table_name' AND REFERENCED_TABLE_NAME IS NOT NULL; |
information_schema 核心表
表名 | 用途 |
---|---|
SCHEMATA | 存储数据库信息(如数据库名、字符集) |
TABLES | 存储表信息(如表名、引擎、行数) |
COLUMNS | 存储列信息(如列名、数据类型、是否允许NULL) |
STATISTICS | 存储索引统计信息(如索引名、列名、唯一性) |
KEY_COLUMN_USAGE | 存储外键信息(如外键名、关联表) |
序列
MySQL 无内置序列类型,通过 AUTO_INCREMENT
属性模拟序列,实现列值自动递增(常用于主键)。
- 示例:
-- 创建含自增列的表 CREATE TABLE example_table (id INT AUTO_INCREMENT PRIMARY KEY, -- 自增主键name VARCHAR(50) );-- 插入数据(无需指定id,自动生成) INSERT INTO example_table (name) VALUES ('John');
处理重复数据
通过约束、特殊插入语句或查询/删除操作,可预防、识别和删除重复数据。
预防重复数据
- 主键约束:设置单一或复合主键(如
PRIMARY KEY (last_name, first_name)
),确保组合值唯一。CREATE TABLE person_tbl (first_name CHAR(20) NOT NULL,last_name CHAR(20) NOT NULL,sex CHAR(10),PRIMARY KEY (last_name, first_name) -- 复合主键 );
- 唯一索引:设置唯一索引,避免指定列重复。
CREATE TABLE person_tbl (first_name CHAR(20) NOT NULL,last_name CHAR(20) NOT NULL,sex CHAR(10),UNIQUE (last_name, first_name) -- 唯一索引 );
插入时处理重复
- INSERT IGNORE INTO:插入重复数据时,忽略操作(不报错,返回警告)。
INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES ('Jay', 'Thomas'); -- 首次插入成功,重复插入忽略
- REPLACE INTO:插入重复数据时,先删除原记录,再插入新记录。
查询重复数据
-- 统计重复记录数(按姓名分组,重复数>1)
SELECT COUNT(*) AS repetitions, last_name, first_name
FROM person_tbl
GROUP BY last_name, first_name
HAVING repetitions > 1;-- 查询不重复数据(DISTINCT 或 GROUP BY)
SELECT DISTINCT last_name, first_name FROM person_tbl;
SELECT last_name, first_name FROM person_tbl GROUP BY last_name, first_name;
删除重复数据
-- 方式1:创建临时表存储不重复数据,替换原表
CREATE TABLE tmp
SELECT last_name, first_name, sex
FROM person_tbl
GROUP BY last_name, first_name, sex;
DROP TABLE person_tbl;
ALTER TABLE tmp RENAME TO person_tbl;-- 方式2:添加唯一索引删除重复(ALTER IGNORE)
ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);
MySQL 及 SQL 注入
SQL 注入是攻击者通过恶意输入插入 SQL 命令,欺骗服务器执行非法操作,需重点防范。
注入示例
假设有登录验证 SQL:
SELECT * FROM users WHERE username = 'input_username' AND password = 'input_password';
攻击者输入用户名 ' OR '1'='1'; --
,SQL 变为:
SELECT * FROM users WHERE username = '' OR '1'='1'; --' AND password = 'input_password';
此时 1='1
恒为真,且 --
注释后续内容,将返回所有用户数据,导致登录绕过。
防范措施
- 参数化查询/预编译语句:将输入数据与 SQL 语句分离,避免恶意注入(如 Java 的
PreparedStatement
)。 - 输入验证与转义:验证用户输入格式,用
mysqli_real_escape_string
等函数转义特殊字符。 - 最小权限原则:数据库用户仅授予必要权限(如查询权限,无删除/修改权限)。
- 使用 ORM 框架:如 Hibernate、Sequelize,抽象 SQL 操作,降低注入风险。
- 禁用详细错误:生产环境不显示数据库错误信息,避免泄露结构信息。
导出/导入数据
数据导出
SELECT ... INTO OUTFILE
将查询结果导出到文本文件,可指定分隔符。
- 语法:
SELECT column1, ... INTO OUTFILE 'file_path' FIELDS TERMINATED BY '分隔符' LINES TERMINATED BY '换行符' FROM table_name WHERE condition;
- 示例:导出用户表 id、name、email 到 CSV 文件(逗号分隔)。
SELECT id, name, email INTO OUTFILE '/tmp/user_data.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM users;
mysqldump 工具
命令行工具,导出数据库为 SQL 脚本(含表结构和数据)。
- 语法:
mysqldump -u username -p password -h hostname database_name > output_file.sql
- 参数说明:
-u
:MySQL 用户名。-p
:提示输入密码。-h
:MySQL 主机名。database_name
:要导出的数据库名。output_file.sql
:导出文件路径。
数据导入
mysql 命令
- 语法:
mysql -u username -p -h your_host -P your_port -D your_database < input_file.sql
source 命令(MySQL 终端内)
- 步骤:
CREATE DATABASE abc; -- 创建目标数据库 USE abc; -- 切换到目标数据库 SET NAMES utf8; -- 设置编码 SOURCE /home/abc/abc.sql; -- 导入SQL文件
LOAD DATA INFILE
将文本文件数据导入表中。
- 语法:
LOAD DATA LOCAL INFILE 'file_path' INTO TABLE table_name FIELDS TERMINATED BY '分隔符' LINES TERMINATED BY '换行符' [COLUMNS (column1, column2, ...)]; -- 列顺序不匹配时指定
- 示例:
-- 导入dump.txt到mytbl表(冒号分隔,Windows换行符) LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl FIELDS TERMINATED BY ':' LINES TERMINATED BY '\r\n';-- 列顺序不匹配时指定 LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl (b, c, a);
函数
MySQL 提供丰富的内置函数,按功能可分为字符串函数、数字函数、日期函数、高级函数等。
字符串函数
函数 | 描述 | 实例 |
---|---|---|
ASCII(s) | 返回字符串 s 第一个字符的 ASCII 码 | SELECT ASCII(CustomerName) AS NumCode FROM Customers; |
CHAR_LENGTH(s) | 返回字符串 s 的字符数 | SELECT CHAR_LENGTH("RUNOOB") AS Len; -- 6 |
CONCAT(s1,s2,...) | 合并多个字符串 | SELECT CONCAT("SQL ", "Runoob") AS ConcatStr; -- SQL Runoob |
CONCAT_WS(x,s1,...) | 合并字符串,用 x 分隔 | SELECT CONCAT_WS("-", "SQL", "Tutorial") AS Str; -- SQL-Tutorial |
FIND_IN_SET(s1,s2) | 返回 s1 在 s2(逗号分隔字符串)中的位置 | SELECT FIND_IN_SET("c", "a,b,c,d"); -- 3 |
FORMAT(x,n) | 格式化数字 x,保留 n 位小数 | SELECT FORMAT(250500.5634, 2); -- 250,500.56 |
INSERT(s1,x,len,s2) | 用 s2 替换 s1 中 x 位置开始、长度为 len 的字符 | SELECT INSERT("google.com", 1, 6, "runoob"); -- runoob.com |
LOCATE(s1,s) | 返回 s1 在 s 中的开始位置 | SELECT LOCATE('b', 'abc'); -- 2 |
LCASE(s)/LOWER(s) | 字符串转为小写 | SELECT LCASE('RUNOOB'); -- runoob |
UCASE(s)/UPPER(s) | 字符串转为大写 | SELECT UPPER('runoob'); -- RUNOOB |
LEFT(s,n)/RIGHT(s,n) | 返回 s 的前 n 个/后 n 个字符 | SELECT LEFT('runoob',2); -- ru |
LPAD(s1,len,s2)/RPAD(s1,len,s2) | 用 s2 填充 s1 至长度 len(左/右填充) | SELECT LPAD('abc',5,'xx'); -- xxabc |
LTRIM(s)/RTRIM(s)/TRIM(s) | 去除 s 开头/结尾/首尾空格 | SELECT TRIM(' RUNOOB '); -- RUNOOB |
MID(s,n,len)/SUBSTR(s,n,len) | 从 s 的 n 位置截取 len 个字符 | SELECT MID("RUNOOB",2,3); -- UNO |
REPLACE(s,s1,s2) | 用 s2 替换 s 中的 s1 | SELECT REPLACE('abc','a','x'); -- xbc |
REVERSE(s) | 反转字符串 | SELECT REVERSE('abc'); -- cba |
SUBSTRING_INDEX(s,d,n) | 按分隔符 d 截取 s 的第 n 部分 | SELECT SUBSTRING_INDEX('abc','',2); -- ab |
数字函数
函数 | 描述 | 实例 |
---|---|---|
ABS(x) | 返回 x 的绝对值 | SELECT ABS(-1); -- 1 |
AVG(expr) | 返回 expr 的平均值 | SELECT AVG(Price) AS AvgPrice FROM Products; |
CEIL(x)/CEILING(x) | 返回大于等于 x 的最小整数 | SELECT CEIL(1.5); -- 2 |
COUNT(expr) | 返回记录总数 | SELECT COUNT(ProductID) AS Count FROM Products; |
FLOOR(x) | 返回小于等于 x 的最大整数 | SELECT FLOOR(1.5); -- 1 |
GREATEST(expr1,...) | 返回列表中的最大值 | SELECT GREATEST(3,12,34); -- 34 |
LEAST(expr1,...) | 返回列表中的最小值 | SELECT LEAST(3,12,34); -- 3 |
MAX(expr)/MIN(expr) | 返回 expr 的最大/最小值 | SELECT MAX(Price) AS MaxPrice FROM Products; |
MOD(x,y) | 返回 x 除以 y 的余数 | SELECT MOD(5,2); -- 1 |
PI() | 返回圆周率(3.141593) | SELECT PI(); -- 3.141593 |
POW(x,y)/POWER(x,y) | 返回 x 的 y 次方 | SELECT POW(2,3); -- 8 |
RAND() | 返回 0-1 的随机数 | SELECT RAND(); -- 如 0.930993 |
ROUND(x[,y]) | 四舍五入 x 到 y 位小数 | SELECT ROUND(345.156,2); -- 345.16 |
SIGN(x) | 返回 x 的符号(-1/0/1) | SELECT SIGN(-10); -- -1 |
SQRT(x) | 返回 x 的平方根 | SELECT SQRT(25); -- 5 |
SUM(expr) | 返回 expr 的总和 | SELECT SUM(Quantity) AS Total FROM OrderDetails; |
TRUNCATE(x,y) | 截断 x 到 y 位小数(不四舍五入) | SELECT TRUNCATE(1.23456,3); -- 1.234 |
日期函数
函数 | 描述 | 实例 |
---|---|---|
CURDATE()/CURRENT_DATE() | 返回当前日期 | SELECT CURDATE(); -- 2024-05-20 |
CURTIME()/CURRENT_TIME() | 返回当前时间 | SELECT CURTIME(); -- 15:30:45 |
NOW()/CURRENT_TIMESTAMP() | 返回当前日期时间 | SELECT NOW(); -- 2024-05-20 15:30:45 |
DATE(d) | 提取日期部分 | SELECT DATE("2024-05-20 15:30:45"); -- 2024-05-20 |
DATEDIFF(d1,d2) | 计算 d1 到 d2 的天数差 | SELECT DATEDIFF('2024-05-20','2024-05-10'); -- 10 |
DATE_ADD(d,INTERVAL expr type) | 给 d 加指定时间间隔 | SELECT DATE_ADD("2024-05-20", INTERVAL 10 DAY); -- 2024-05-30 |
DATE_SUB(d,INTERVAL expr type) | 给 d 减指定时间间隔 | SELECT DATE_SUB("2024-05-20", INTERVAL 10 DAY); -- 2024-05-10 |
DATE_FORMAT(d,f) | 按格式 f 显示 d | SELECT DATE_FORMAT('2024-05-20','%Y年%m月%d日'); -- 2024年05月20日 |
DAY(d)/DAYOFMONTH(d) | 返回 d 的日期(当月第几天) | SELECT DAY("2024-05-20"); -- 20 |
DAYNAME(d) | 返回 d 的星期名称 | SELECT DAYNAME("2024-05-20"); -- Monday |
MONTH(d)/MONTHNAME(d) | 返回 d 的月份(数字/名称) | SELECT MONTH("2024-05-20"); -- 5 |
YEAR(d) | 返回 d 的年份 | SELECT YEAR("2024-05-20"); -- 2024 |
TIMESTAMPDIFF(unit,d1,d2) | 计算 d2-d1 的时间差(按 unit) | SELECT TIMESTAMPDIFF(MONTH,'2024-01-01','2024-05-20'); -- 4 |
高级函数
函数 | 描述 | 实例 |
---|---|---|
BIN(x) | 返回 x 的二进制 | SELECT BIN(15); -- 1111 |
CASE expr WHEN c1 THEN r1 ... ELSE r END | 条件判断 | SELECT CASE WHEN 1>0 THEN '1>0' ELSE '1<0' END; -- 1>0 |
CAST(x AS type) | 数据类型转换 | SELECT CAST("2024-05-20" AS DATE); -- 2024-05-20 |
COALESCE(expr1,...) | 返回首个非 NULL 值 | SELECT COALESCE(NULL, 'runoob', NULL); -- runoob |
CONV(x,f1,f2) | 进制转换(f1→f2) | SELECT CONV(15,10,2); -- 1111 |
IF(expr,v1,v2) | expr 为真返回 v1,否则 v2 | SELECT IF(1>0,'正确','错误'); -- 正确 |
IFNULL(v1,v2) | v1 不为 NULL 返回 v1,否则 v2 | SELECT IFNULL(NULL,'Hello'); -- Hello |
ISNULL(expr) | 判断 expr 是否为 NULL(是则1,否则0) | SELECT ISNULL(NULL); -- 1 |
LAST_INSERT_ID() | 返回最近的 AUTO_INCREMENT 值 | SELECT LAST_INSERT_ID(); -- 如 6 |
VERSION() | 返回 MySQL 版本 | SELECT VERSION(); -- 如 5.6.34 |
MySQL 8.0 新增函数
函数 | 描述 | 实例 |
---|---|---|
JSON_OBJECT() | 生成 JSON 对象 | SELECT JSON_OBJECT('key1','val1','key2','val2'); -- |
JSON_ARRAY() | 生成 JSON 数组 | SELECT JSON_ARRAY(1,2,'three'); -- [1,2,"three"] |
JSON_EXTRACT() | 提取 JSON 中的值 | SELECT JSON_EXTRACT('{"name":"John"}','$.name'); -- "John" |
ROW_NUMBER() | 为行分配唯一序号 | SELECT ROW_NUMBER() OVER(ORDER BY id) AS rn, name FROM users; |
RANK() | 为行分配排名(相同值排名相同) | SELECT RANK() OVER(ORDER BY score DESC) AS rk, name FROM students; |
运算符
MySQL 支持算术、比较、逻辑、位四类运算符,用于数据计算和条件判断。
算术运算符
运算符 | 作用 | 说明 |
---|---|---|
+ | 加法 | 如 1+2=3 |
- | 减法 | 如 5-3=2 |
* | 乘法 | 如 2*3=6 |
/ 或 DIV | 除法 | 如 6/2=3 ,除数为0返回 NULL |
% 或 MOD | 取余 | 如 7%3=1 ,除数为0返回 NULL |
比较运算符
运算符 | 描述 | 备注 |
---|---|---|
= | 等于 | 判断两值是否相等 |
<>, != | 不等于 | 判断两值是否不相等 |
> | 大于 | 判断左值是否大于右值 |
< | 小于 | 判断左值是否小于右值 |
>= | 大于等于 | 判断左值是否大于等于右值 |
<= | 小于等于 | 判断左值是否小于等于右值 |
BETWEEN ... AND ... | 在两值之间 | 等价于 >=min AND <=max |
NOT BETWEEN ... AND ... | 不在两值之间 | 等价于 <min OR >max |
IN | 在集合中 | 如 IN (1,2,3) |
NOT IN | 不在集合中 | 如 NOT IN (1,2,3) |
<=> | 严格比较 NULL | 两值都为 NULL 或相等时返回1,否则0 |
LIKE | 模糊匹配 | 搭配 % 、_ 使用 |
REGEXP/RLIKE | 正则匹配 | 支持正则模式 |
IS NULL | 为空 | 判断值是否为 NULL |
IS NOT NULL | 不为空 | 判断值是否不为 NULL |
逻辑运算符
运算符 | 描述 | 示例(A=1,B=0) |
---|---|---|
NOT 或 ! | 逻辑非 | NOT A → 0 |
AND | 逻辑与 | A AND B → 0 |
OR | 逻辑或 | A OR B → 1 |
XOR | 逻辑异或 | A XOR B → 1(两值不同时为1) |
位运算符
位运算符对二进制数操作,先将操作数转为二进制,计算后转回十进制。
运算符 | 描述 | 示例(A=6→110,B=3→011) |
---|---|---|
& | 按位与 | A&B → 010 → 2 |
| | 按位或 | A|B → 111 → 7 |
^ | 按位异或 | A^B → 101 → 5 |
~ | 按位取反 | ~A → -7(二进制补码) |
<< | 左移 | A<<1 → 1100 → 12 |
>> | 右移 | A>>1 → 011 → 3 |
MySQL 命令大全
整理常用基础命令,涵盖连接、数据库、表、数据、事务等操作场景。
基础连接与用户操作
操作 | 命令 |
---|---|
连接 MySQL | mysql -u 用户名 -p |
查看当前用户 | SELECT USER(); |
创建用户 | CREATE USER '用户名'@'主机' IDENTIFIED BY '密码'; |
授权用户 | GRANT 权限 ON 数据库名.* TO '用户名'@'主机'; |
刷新权限 | FLUSH PRIVILEGES; |
退出 MySQL | EXIT; |
数据库操作
操作 | 命令 |
---|---|
查看所有数据库 | SHOW DATABASES; |
创建数据库 | CREATE DATABASE 数据库名; |
创建数据库(防重复) | CREATE DATABASE IF NOT EXISTS 数据库名; |
删除数据库 | DROP DATABASE 数据库名; |
删除数据库(防不存在) | DROP DATABASE IF EXISTS 数据库名; |
选择数据库 | USE 数据库名; |
查看数据库详情 | SHOW CREATE DATABASE 数据库名; |
修改数据库编码 | ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 编码 COLLATE 排序规则; |
数据表操作
操作 | 命令 |
---|---|
查看数据库中所有表 | SHOW TABLES; |
查看表结构 | DESCRIBE 表名; 或 SHOW COLUMNS FROM 表名; |
创建表 | CREATE TABLE 表名 (列1 类型 [约束], 列2 类型 [约束], ...); |
删除表 | DROP TABLE 表名; |
删除表(防不存在) | DROP TABLE IF EXISTS 表名; |
清空表数据(保留结构) | TRUNCATE TABLE 表名; |
查看表创建语句 | SHOW CREATE TABLE 表名; |
查看表索引 | SHOW INDEX FROM 表名; |
查看表统计信息 | SHOW TABLE STATUS LIKE '表名'; |
数据操作
操作 | 命令 |
---|---|
插入数据 | INSERT INTO 表名 (列1, ...) VALUES (值1, ...); |
查询数据 | SELECT 列1, ... FROM 表名 [WHERE 条件]; |
更新数据 | UPDATE 表名 SET 列1=值1, ... [WHERE 条件]; |
删除数据 | DELETE FROM 表名 [WHERE 条件]; |
事务操作
操作 | 命令 |
---|---|
开始事务 | START TRANSACTION; 或 BEGIN; |
提交事务 | COMMIT; |
回滚事务 | ROLLBACK; |
回滚到保存点 | ROLLBACK TO 保存点名称; |
创建保存点 | SAVEPOINT 保存点名称; |
锁定表 | LOCK TABLES 表名 WRITE;(写锁)或 LOCK TABLES 表名 READ;(读锁) |
释放锁 | UNLOCK TABLES; |
设置隔离级别 | SET TRANSACTION ISOLATION LEVEL 级别;(如 READ COMMITTED) |
查看事务状态(InnoDB) | SHOW ENGINE INNODB STATUS; |