MySQL常用语句 – Jason's Blog

目录隐藏

1 提取身份证的出生日期

2 提取身份证的性别

10 order by field（自定义排序）

13.1 方法一（FIND_IN_SET）

13.2 方法二

提取身份证的出生日期

select cast(substring(身份证,7,8) as date) as 出生日期  from 表

提取身份证的性别

SELECT * from 表 where (SUBSTR(身份证,17,1))%2=0   #女

去除字段中的回车、换行

UPDATE 表名 SET  字段名 = REPLACE(REPLACE(字段名, CHAR(10), ''), CHAR(13), '');

查询数据总量

SELECT sum(TABLE_ROWS) FROM `information_schema`.`tables`  where table_schema = '数据库名'

查询数据库中每张表的行数

select table_rows,table_name from information_schema.tables where table_schema = '数据库名' order by table_rows desc

查询表及数据库占用的容量体积

SELECT
  TABLE_NAME,
  DATA_LENGTH,
  INDEX_LENGTH,
  (DATA_LENGTH + INDEX_LENGTH) AS LENGTH,
  TABLE_ROWS,
  CONCAT (ROUND ((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 3), 'MB') AS total_size
FROM
  information_schema.TABLES
WHERE TABLE_SCHEMA = 'db_name' -- 库名
  AND table_name = 'table_name' -- 表名
ORDER BY LENGTH DESC;

生成随机数

-- 生成 3 位的随机数
SELECT CEILING(RAND()*900+100);
-- 生成 4 位的随机数
SELECT CEILING(RAND()*9000+1000);
-- 生成 5 位的随机数
SELECT CEILING(RAND()*90000+10000);

授权root用户远程访问

create user 'root'@'%' identified with mysql_native_password by '123456';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

批量更改MyISAM为Innodb

转换引擎的SQL语句为：

alter table 表名 engine=innodb;

批量生成ALTER TABLE语句：

SELECT CONCAT('ALTER TABLE `',table_name,'` ENGINE=InnoDB;') FROM information_schema.tables WHERE table_schema="数据库名" AND ENGINE="MyISAM";

注意：如果执行alter table 表名 engine=innodb;语句时提示：ERROR 1031 (HY000): Table storage engine for '#sql-xxx' doesn't have this option的错误，通常是由于这张表的ROW_FORMAT=FIXED造成的，只需要将ROW_FORMAT设置为default或DYNAMIC即可。

order by field（自定义排序）

id字段按3,5,2,1这样的顺序排序

select * FROM `表名` order by field(`id`,'3','5','2','1')

find_in_set

指定范围查找

SELECT * FROM `表名` where find_in_set(id,'36,25,32,31');   //等同于 where id in (36,25,32,31)

指定顺序排序

id字段按36,25,32,31这样的顺序排序

SELECT * FROM `表名` order by find_in_set(id,'36,25,32,31');  //等同于 order by field(`id`,'36','25','32','31')

支持使用变量

相对于order by field，find_in_set支持先定义变量，再嵌入变量的方式使用

set @str = '字符串1,字符串2';
select * from `表名` where find_in_set(`subject`, @str) order by find_in_set(subject,@str);

批量更改表引擎为innodb

SET @database = '数据库名';
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @database AND ENGINE != 'InnoDB';

执行后，将结果保存并执行即可。

在逗号分隔的字段查询

方法一（FIND_IN_SET）

SELECT * FROM `表名` where FIND_IN_SET('查找值', 字段名);

方法二

SELECT * FROM 表名 WHERE CONCAT(',', 字段名, ',') LIKE '%,查找值,%' ORDER BY orders asc;