Mysql常用的sql语句_mysql sql语句状态

扩展：
MySQL45道面试题及答案

1、清空数据表中的所有的数据：

TRUNCATE TABLE 表名;
1

2、设置自增ID从1开始：

alter table  表名  AUTO_INCREMENT =1;
1

3、显示用户正在运行的线程,分析sql当前的运行状态：

show processlist;
1

显示的信息都是来自MySQL系统库 information_schema 中的 processlist 表，所以使用下面的查询语句可以获得相同的结果：

    select * from information_schema.processlist;
    select * from information_schema.processlist where `time` > 500 ORDER BY ID desc;
    select concat('KILL ',id,';') from information_schema.processlist where `time` > 500 ORDER BY ID desc;
1
2
3

参考：mysql管理性能状态查看命令

4、Mysql5.7查看安装时的默认密码

cat /root/.mysql_secret
1

5、常用的数据库命令

6、查询某个字段长度最大的记录长度

SELECT max(length(message)) FROM apple;
1

7、数据库的备份与恢复

备份： mysqldump -u用户名 -p密码 数据库名称 > /root/BackupSql.sql
恢复： create database 数据库名称;
	  use 数据库名称；
	  source /root/BackupSql.sql;
1
2
3
4

8、Mysql中常用的函数

9、根据某一个字段查重（单表查重）

select id,account,user_nicename from cmf_users where user_nicename in (select user_nicename from  cmf_users group by user_nicename having count(user_nicename) > 1) ORDER BY user_nicename,id;
1

9.1、 根据多字段查重并删除(保留id最小的一条数据)

SELECT area, mark, create_time, COUNT(*) AS count FROM 表名 where create_time = "2024-03-28" GROUP BY area, mark, create_time HAVING COUNT(*) > 1;

DELETE t1
FROM 表名 t1
JOIN 表名 t2 ON t1.area = t2.area
    AND t1.mark = t2.mark
    AND t1.create_time = t2.create_time
    AND t1.id > t2.id;
1
2
3
4
5
6
7
8

10、查询指定格式的表结构，获取更改表结构的SQL

SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',TABLE_COLLATION '原排序规则',CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', TABLE_NAME, ' COLLATE=utf8mb4_unicode_ci;') '修正SQL' FROM information_schema.`TABLES`   where TABLE_SCHEMA = 'peanutminivideo' and TABLE_COLLATION = 'utf8mb4_0900_ai_ci';
1

11、Mysql5.7添加更改账号密码

mysql -u用户名 -p密码
use mysql;
#### 添加
flush privileges;
GRANT USAGE ON *.* TO '用户名'@'localhost' IDENTIFIED BY '密码' WITH GRANT OPTION; //添加用户密码
GRANT all privileges on 用户名DB.* to 用户名@localhost identified by '密码'; //授权用户拥有数据库的所有权限
GRANT all privileges on *.* to '用户名'@'%' identified by '密码'; // 所有ip都可登陆
flush privileges;
#### 更新
flush privileges;
UPDATE mysql.user SET authentication_string=PASSWORD('密码') WHERE User='用户名' AND Host='localhost';
flush privileges;
1
2
3
4
5
6
7
8
9
10
11
12

12、格式化查询时间

时间戳转化成格式化时间：

select DATE_FORMAT(FROM_UNIXTIME(last_login),"%Y-%m-%d") AS last_login_times from user  limit 10
1

13、sum(if())、count(if())、count((currency = 5 AND fee > 0) OR NULL)

参考：
mysql sum(if())和count(if())用法

SELECT SUM(if(category=1,size,0)) ,COUNT(if(category=1,true,null)) FORM t_file; 
1

解析：

sum（if(category=1,size,0))
1

sum函数返回一个值类型的数值，如果category=1,则返回size,如果category不等于1就返回0。

count(if(category=1,true,null))
1

count函数返回一个布尔值类型的数值，如果category=1,返回true,如果category不等于1返回null，如果写成count(If(category=1,1,0) 则返回的全是true,也就是说全都会计数，而count()间断内容是true还是null,如果不是null就计数，
如果是null就不计数。

count(if())的写法应该是count(if(表达式表达式，true,null));

count((currency = 5 AND fee > 0) OR NULL)
1

count在值是NULL时不统计数，第一个表达式是true就是不执行or后面的表达式，第一个表达式是false 执行or后面的表达式 。

14、慢日志

查询：

select CAST(sql_text as char) from slow_log
1