MySQL 常用 SQL 语句大全

原创

mb6724370248d98 2024-12-31 09:04:26 博主文章分类：后端 ©著作权

文章标签 数据库字段慢查询 文章分类 MySQL 数据库 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者mb6724370248d98的原创作品，请联系作者获取转载授权，否则将追究法律责任

基本查询查询所有记录和字段

SELECT * FROM table_name; 1 查询特定字段

SELECT column1, column2 FROM table_name; 1 查询并限制结果

SELECT column1, column2 FROM table_name LIMIT 10; 1 条件查询

SELECT column1, column2 FROM table_name WHERE condition; 1 模糊匹配

SELECT column1 FROM table_name WHERE column2 LIKE 'pattern%'; 1 匹配多个值

SELECT column1 FROM table_name WHERE column2 IN (value1, value2); 1 不匹配某些值

SELECT column1 FROM table_name WHERE column2 NOT IN (value1, value2); 1 2. 数据插入插入单条记录

INSERT INTO table_name (column1, column2) VALUES (value1, value2); 1 插入多条记录

INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4); 1 从另一个表插入数据

INSERT INTO table1 (column1, column2) SELECT column1, column2 FROM table2 WHERE condition; 1 2 3. 数据更新更新单条记录

UPDATE table_name SET column1 = value1 WHERE condition; 1 批量更新

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; 1 基于其他表的更新(关联更新)

UPDATE table1 JOIN table2 ON table1.common_column = table2.common_column

SET table1.column1 = table2.column2 WHERE table2.condition; 1 2 3 4 5 4. 数据删除删除单条记录

DELETE FROM table_name WHERE condition; 1 批量删除

DELETE FROM table_name WHERE condition; 1 删除表中的所有记录(也叫截断表)

TRUNCATE TABLE table_name; 1 5. 表操作创建表

CREATE TABLE table_name ( ? ? id INT AUTO_INCREMENT PRIMARY KEY, ? ? column1 VARCHAR(255) NOT NULL, ? ? column2 INT DEFAULT 0, ? ? created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 1 2 3 4 5 6 修改表结构

添加列

ALTER TABLE table_name ADD column_name VARCHAR(255); 1 删除列

ALTER TABLE table_name DROP COLUMN column_name; 1 修改列类型

ALTER TABLE table_name MODIFY column_name INT; 1 重命名列

ALTER TABLE table_name CHANGE old_column_name new_column_name VARCHAR(255); 1 删除表

DROP TABLE table_name; 1 删除数据库

DROP DATABASE database_name; 1 6. 索引管理创建索引

CREATE INDEX index_name ON table_name (column_name); 1 创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column_name); 1 删除索引

DROP INDEX index_name ON table_name; 1 7. 表连接内连接（INNER JOIN）

SELECT * FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column; 1 2 左连接（LEFT JOIN）

SELECT * FROM table1 LEFT JOIN table2 ON table1.common_column = table2.common_column; 1 2 右连接（RIGHT JOIN）

SELECT * FROM table1 RIGHT JOIN table2 ON table1.common_column = table2.common_column; 1 2 全连接（FULL JOIN）

SELECT * FROM table1 LEFT JOIN table2 ON table1.common_column = table2.common_column UNION SELECT * FROM table1 RIGHT JOIN table2 ON table1.common_column = table2.common_column; 1 2 3 4 5 8. 聚合函数计数

SELECT COUNT(*) FROM table_name; 1 平均值

SELECT AVG(column_name) FROM table_name; 1 总和

SELECT SUM(column_name) FROM table_name; 1 最大值

SELECT MAX(column_name) FROM table_name; 1 最小值

SELECT MIN(column_name) FROM table_name; 1 9. 分组与排序分组

SELECT column1, COUNT(*) FROM table_name GROUP BY column1; 1 2 排序

SELECT * FROM table_name ORDER BY column1 ASC;

SELECT * FROM table_name ORDER BY column1 DESC; 1 2 3 4 5 分页

SELECT * FROM table_name LIMIT 10 OFFSET 20; 1 2 10. 子查询在 WHERE 子句中

SELECT * FROM table_name WHERE column1 = (SELECT MAX(column1) FROM table_name); 1 2 在 FROM 子句中

SELECT * FROM (SELECT column1, column2 FROM table_name) AS subquery WHERE condition; 1 2 11. 条件表达式使用 CASE

SELECT column1, ? ? CASE ? ? ? ? WHEN condition1 THEN 'result1' ? ? ? ? WHEN condition2 THEN 'result2' ? ? ? ? ELSE 'default' ? ? END AS new_column FROM table_name; 1 2 3 4 5 6 7 12. 事务管理

开始事务

START TRANSACTION; 1 提交事务

COMMIT; 1 回滚事务

ROLLBACK; 1 13. 备份与恢复

备份

mysqldump -u username -p database_name > backup_file.sql 1 恢复

mysql -u username -p database_name < backup_file.sql 1 14. 用户管理创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 1 授予权限

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host'; 1 撤销权限

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host'; 1 删除用户

DROP USER 'username'@'host'; 1 15. 表和数据库信息

查看表结构