50条常用的MySQL命令汇总
50条常用的MySQL命令汇总
一、数据库操作
创建数据库
CREATE DATABASE database_name;
创建数据库并指定字符集
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
查看所有数据库
SHOW DATABASES;
选择/使用数据库
USE database_name;
查看当前使用的数据库
SELECT DATABASE();
删除数据库
DROP DATABASE database_name;
删除数据库（如果存在）
DROP DATABASE IF EXISTS database_name;
修改数据库字符集
ALTER DATABASE database_name CHARACTER SET utf8mb4;
查看数据库创建语句
SHOW CREATE DATABASE database_name;
二、表操作
创建表
CREATE TABLE table_name (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
查看所有表
SHOW TABLES;
查看表结构
DESCRIBE table_name;
-- 或
DESC table_name;
-- 或
SHOW COLUMNS FROM table_name;
查看表的创建语句
SHOW CREATE TABLE table_name;
重命名表
RENAME TABLE old_table_name TO new_table_name;
添加列
ALTER TABLE table_name ADD COLUMN column_name VARCHAR(100);
删除列
ALTER TABLE table_name DROP COLUMN column_name;
修改列定义
ALTER TABLE table_name MODIFY COLUMN column_name INT NOT NULL;
修改列名
ALTER TABLE table_name CHANGE old_column_name new_column_name VARCHAR(100);
添加主键
ALTER TABLE table_name ADD PRIMARY KEY (id);
删除主键
ALTER TABLE table_name DROP PRIMARY KEY;
添加唯一索引
ALTER TABLE table_name ADD UNIQUE (column_name);
添加普通索引
ALTER TABLE table_name ADD INDEX idx_name (column_name);
添加复合索引
ALTER TABLE table_name ADD INDEX idx_composite (col1, col2);
删除索引
DROP INDEX index_name ON table_name;
删除表
DROP TABLE table_name;
清空表（删除所有数据）
TRUNCATE TABLE table_name;
查看表索引
SHOW INDEX FROM table_name;
三、数据操作
插入单条数据
INSERT INTO table_name (col1, col2) VALUES ('value1', 'value2');
插入多条数据
INSERT INTO table_name (col1, col2) VALUES
('v1', 'v2'),
('v3', 'v4');
插入数据（忽略重复）
INSERT IGNORE INTO table_name (col1, col2) VALUES ('value1', 'value2');
插入或更新（存在则更新）
INSERT INTO table_name (id, name) VALUES (1, 'Alice') ON DUPLICATE KEY UPDATE name='Alice';
更新数据
UPDATE table_name SET column_name = 'new_value' WHERE id = 1;
更新多列
UPDATE table_name SET col1 = 'v1', col2 = 'v2' WHERE condition;
删除数据
DELETE FROM table_name WHERE id = 1;
删除所有数据（不重置自增）
DELETE FROM table_name;
查询所有数据
SELECT * FROM table_name;
查询指定列
SELECT col1, col2 FROM table_name;
带条件查询
SELECT * FROM table_name WHERE age > 18;
模糊查询
SELECT * FROM table_name WHERE name LIKE '%张%';
范围查询
SELECT * FROM table_name WHERE age BETWEEN 18 AND 30;
IN 查询
SELECT * FROM table_name WHERE id IN (1, 2, 3);
排序查询
SELECT * FROM table_name ORDER BY age DESC;
分页查询
SELECT * FROM table_name LIMIT 10 OFFSET 0;
-- 或
SELECT * FROM table_name LIMIT 0, 10;
去重查询
SELECT DISTINCT column_name FROM table_name;
聚合函数查询
SELECT COUNT(*), AVG(age), MAX(age), MIN(age), SUM(age) FROM table_name;
分组查询
SELECT department, COUNT(*) FROM employees GROUP BY department;
分组后筛选（HAVING）
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;
多表连接查询（INNER JOIN）
SELECT u.name, o.order_id FROM users u INNER JOIN orders o ON u.id = o.user_id;
左连接查询（LEFT JOIN）
SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id;
子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
————————————————
版权声明：本文为CSDN博主「Mr_戴先森」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_44024436/article/details/151404054
