Mysql常用的sql语句

扩展：
MySQL45道面试题及答案

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

TRUNCATE TABLE 表名;

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

alter table  表名  AUTO_INCREMENT =1;

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

show processlist;

显示的信息都是来自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;

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

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

cat /root/.mysql_secret

5、常用的数据库命令

作用	命令
显示所有的库名	show databases;
选择数据库	use databasename;
显示所有的表	show tables;
显示表的结构	desc tabl_name;
更改表中的字段名	alter table 库.表 change 改之后的字段名称 改之前的字段名称；
查看索引	show index from 表名;
添加索引	ALTER TABLE 表名ADD INDEX 字段 (字段);
添加全文索引	ALTER TABLE 表名ADD FULLTEXT 字段(字段);
添加唯一索引	ALTER TABLE 表名 ADD UNIQUE INDEX 字段(字段) USING BTREE;;
添加字段	alter table 表名 add 字段名 varchar(254);
创建数据库	create database 数据库名称;
创建mysql用户	create user 'username'@'host' identified by 'password';
删除表中的字段	ALTER table 表名 drop column 字段1,drop column字段2;
表添加注释	alter table 表名 comment '表的注释';
字段添加注释（MODIFY 修改字段信息）	alter table 表名 modify column 字段名 int comment '字段注释';
克隆表t4结构,表名为t5,不带数据	create table if not exists t5 like t4;
查看mysql.slow_log的sql_text	SELECT CAST(sql_text AS char) FROM mysql.slow_log

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

SELECT max(length(message)) FROM apple;

7、数据库的备份与恢复

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

8、Mysql中常用的函数

函数名称	作用	案例
length	返回字符串str的长度，以字节为单位。 多字节字符计为多个字节。	SELECT length('我我我'); //9
char_length	返回字符串str的长度，以字符为单位。 多字节字符算作单个字符。	SELECT char_length('我我我'); // 3
exists	当子查询返回为true时，则外层查询语句将进行查询。当子查询返回为false时，外层查询语句将不进行查询或者查询不出任何记录。	SELECT * FROM userWHERE exists (SELECT * FROMorder WHERE user.id = order.user_id)
concat	连接字段	SELECT id,concat(name,'-',id) as truename FROM table
LEFT	显示指定长度	SELECT id,LEFT(title,7) as sub_title FROM table

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;

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;

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

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;

12、格式化查询时间

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

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

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; 

解析：

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

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

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

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)

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

14、慢日志

查询：

select CAST(sql_text as char) from slow_log