|
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);
| 
