DBA常用运维SQL

DBA常用运维SQL

基础数据查询

查询所有列

SELECT * FROM 表名;
SELECT id, name, age FROM users;

去重查询

 SELECT DISTINCT 列名 FROM 表名;
 SELECT DISTINCT city FROM customers;

条件查询

SELECT * FROM 表名 WHERE 列名 = 值;
SELECT * FROM products WHERE category = 'Electronics';

BETWEEN（包含边界值）
SELECT * FROM orders WHERE total_amount BETWEEN 100 AND 500;

>, <, >=, <=
> SELECT * FROM employees WHERE salary > 8000;

% 匹配任意字符（包括空字符）

 SELECT * FROM customers WHERE name LIKE '张%';  以“张”开头
 SELECT * FROM products WHERE name LIKE '%手机%'; 包含“手机”
 SELECT * FROM employees WHERE manager_id IS NULL; NULL值查询

升序（ASC，默认）

 SELECT * FROM products ORDER BY price ASC;

降序（DESC）

 SELECT * FROM employees ORDER BY salary DESC;

多列排序

 SELECT * FROM students ORDER BY grade DESC, age ASC;

分页查询（LIMIT）

 SELECT * FROM orders ORDER BY create_time DESC LIMIT 10 OFFSET 20;

简写形式（跳过20条，取10条）

 SELECT * FROM orders ORDER BY create_time DESC LIMIT 20, 10;

SQL Server 语法（使用 OFFSET-FETCH）

 SELECT * FROM orders ORDER BY create_time DESCOFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

计数（COUNT）

统计行数

 SELECT COUNT(*) FROM users;

统计非NULL值

 SELECT COUNT(email) FROM customers; 忽略email为NULL的行 

求和/平均值（SUM/AVG）

 SELECT SUM(sales) FROM monthly_reports WHERE year = 2023;
 SELECT AVG(salary) FROM employees WHERE department = 'IT';

最大值/最小值（MAX/MIN）

 SELECT MAX(price) FROM products WHERE category = 'Laptop';
 SELECT MIN(stock) FROM warehouse WHERE status = 'active';

分组统计（GROUP BY）

按部门统计员工数

 SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department;

多列分组

 SELECT year, month, SUM(sales) AS total_sales FROM sales_data GROUP BY year, month;

分组后筛选（HAVING）

筛选销售额超过100万的部门

 SELECT department, SUM(sales) AS total_sales FROM sales_records GROUP BY department HAVING SUM(sales) > 1000000;

数据库基础信息查询

查看MySQL版本

 SELECT VERSION();

查看当前数据库

 SELECT DATABASE();

查看所有数据库

 SHOW DATABASES;

查看当前用户权限

 SHOW GRANTS;

查看当前连接用户

 SELECT USER();

表结构相关查询

查看数据库中的所有表

 SHOW TABLES;

查看表结构

DESCRIBE 表名;
 或
 SHOW COLUMNS FROM 表名;

查看建表语句

 SHOW CREATE TABLE 表名;

查看表的索引

 SHOW INDEX FROM 表名;

查看表状态信息

 SHOW TABLE STATUS LIKE '表名';

性能相关查询

查看当前所有连接

 SHOW PROCESSLIST;

查看InnoDB状态

 SHOW ENGINE INNODB STATUS;

查看系统变量

 SHOW VARIABLES;

查看特定变量

 SHOW VARIABLES LIKE '%buffer%';

查看状态变量

 SHOW STATUS;

查看特定状态

 SHOW STATUS LIKE '%Connections%';

查看慢查询日志是否开启

 SHOW VARIABLES LIKE 'slow_query_log';

查看慢查询阈值(秒)

 SHOW VARIABLES LIKE 'long_query_time';

存储引擎相关

查看支持的存储引擎

 SHOW ENGINES;

查看默认存储引擎

 SHOW VARIABLES LIKE 'storage_engine';

查看表使用的存储引擎

 SHOW TABLE STATUS LIKE '表名';

复制相关查询

查看主从复制状态

 SHOW MASTER STATUS;  主库
 SHOW SLAVE STATUS\G  从库

查看二进制日志状态

 SHOW VARIABLES LIKE 'log_bin';

查看二进制日志文件列表

 SHOW BINARY LOGS;

维护操作

优化表

 OPTIMIZE TABLE 表名;

分析表

 ANALYZE TABLE 表名;

检查表

 CHECK TABLE 表名;

修复表

 REPAIR TABLE 表名;

刷新权限

 FLUSH PRIVILEGES;

刷新日志

 FLUSH LOGS;

用户和权限管理

查看所有用户

 SELECT User, Host FROM mysql.user;

查看用户权限

 SHOW GRANTS FOR '用户名'@'主机';

创建用户

 CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';

授予权限

 GRANT ALL PRIVILEGES ON 数据库.* TO '用户名'@'主机';

撤销权限

 REVOKE ALL PRIVILEGES ON 数据库.* FROM '用户名'@'主机';

删除用户

 DROP USER '用户名'@'主机';

数据库大小查询

查看所有数据库大小

 SELECT table_schema "数据库名",  SUM(data_length + index_length)/1024/1024 "大小(MB)" FROM information_schema.TABLES GROUP BY table_schema;
 
 
 SELECT table_schema "test",  SUM(data_length + index_length)/1024/1024 "大小(MB)" FROM information_schema.TABLES GROUP BY table_schema;

查看特定数据库中表的大小

 SELECT table_name "表名",
 ROUND(data_length/1024/1024,2) "数据大小(MB)", 
 ROUND(index_length/1024/1024,2) "索引大小(MB)", 
 ROUND((data_length + index_length)/1024/1024,2) "总大小(MB)"FROM information_schema.TABLES WHERE table_schema = "数据库名"ORDER BY (data_length + index_length) DESC;

 SELECT table_name "test",
 ROUND(data_length/1024/1024,2) "数据大小(MB)", 
 ROUND(index_length/1024/1024,2) "索引大小(MB)", 
 ROUND((data_length + index_length)/1024/1024,2) "总大小(MB)"FROM information_schema.TABLES WHERE table_schema = "数据库名"ORDER BY (data_length + index_length) DESC;

锁相关查询

查看当前锁情况

 SHOW OPEN TABLES WHERE In_use > 0;

查看InnoDB锁信息

 SELECT * FROM information_schema.INNODB_TRX;
 SELECT * FROM information_schema.INNODB_LOCKS;
 SELECT * FROM information_schema.INNODB_LOCK_WAITS;

事务相关

查看当前运行的事务

 SELECT * FROM information_schema.INNODB_TRX;

查看事务隔离级别

 SELECT @@transaction_isolation;
 或
 SELECT @@tx_isolation;  旧版本