1.MySQL 常用语句

原创于 2022-09-21 23:40:27 发布 · 2.7k 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #sql

MySQL 专栏收录该内容

11 篇文章

订阅专栏

– concat() 函数
– 【作用】拼接两个字符串,中间用逗号隔开

SELECT concat('123','456');   -- '123456'

– 其中一个参数不可以为null,有一个为null则全部为null

select concat('123','456',null);  -- NULL

– 如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串

select concat(123,456);

– substring() 函数
– 截取字符串函数用来截取字符串的一部分
– 后面两个参数从第一个数字开始，截取几个字符；第一个字符开始不是0 而是1

select substring('123456789',1,2);  -- 12

– substr()
– 截取字符串和substring()一样

select SUBSTR(123456,2);

– right()函数
– RIGHT(s，n) 函数返回字符串 s 最右边的 n 个字符。

select right('MYSQL',2);

– left()函数与right函数相反

select left('123456',3);

– 判断是否为null
– 使用is null 与 is not null 不是使用 = null

select 1 is null;

select 1 is not null;

– not exists 与 exists
– 判断结果是否存在

select not exists(select 1);

select 1 from t_order where exists(select 1);

– 不相等 <>
– 不相等建议使用 <>

select 1<>3;

– sum()
– 求和函数

select sum(1+1+1);

– max() min()
– 最大值函数可以和group by 配合使用
– 最小值函数可以和group by 配合使用

select max(12);

select min(12);

– abs()
– 绝对值函数取绝对值负数可以取正

select abs(-123);

– ROUND()
– round函数用于数据的四舍五入，它有两种形式：
– round(x,d) ,x指要处理的数，d是指保留几位小数
– round(x) ,其实就是round(x,0),也就是默认d为0；
– https://blog.youkuaiyun.com/chinabestchina/article/details/72799826
– 四舍五入保留3为小数

select round(5.12356,3);

– 四舍五入保留0位小数

select round(5.1235);

– length()
– 查询长度,可以放入字段

select length('123456');

– TEMPORARY关键字
– 创建一个临时表

CREATE TEMPORARY TABLE tempOrder;

– show create table
– 展示创表语句

show create table t_order;

– 查询数据表里的所有字段信息

SHOW COLUMNS FROM consumer.t_consumer;

– 查看数据库后台正在运行语句

SHOW PROCESSLIST;

show full processlist;

– 删掉正在执行的语句

kill 1331415;

– cast()函数
– 改变格式字符串变成

select cast('123' as SIGNED);

– decimal格式
– DECIMAL(16,4) 总共能存16位数字，末尾4位是小数（小数点不算在长度内）

select cast(100.123456 as decimal(16,4));

– drop table
– drop table是数据库中用于删除一个或多个数据表的命令，具体格式“DROP TABLE [IF EXISTS] 表名列表”；如果想要同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。

drop t_order;

– Select Distinct的意思是查找重复的意思，在SQL语句中的作用是过滤结果集中的重复值。

Select distinct id from t_order;

– UNIX_TIMESTAMP() 函数
– 将时间转换为时间戳支持中间是横杠’-‘与斜线’/’ 两种格式
– 中间是横杠’-’

select UNIX_TIMESTAMP('2021-10-25 16:00:00'); -- 1635177600

– 中间是斜线’/’ 后面加上三位是毫秒格式，这样转换的结果也是毫秒格式

select UNIX_TIMESTAMP('2021/10/25 16:00:00.111'); -- 1635177600.111

– FROM_UNIXTIME() 函数
– 将时间戳转换为日期时间格式

select FROM_UNIXTIME('1635177600'); -- 2021-10-25 16:00:00

– DATEDIFF()函数
– 函数返回两个日期之间的天数
– https://www.w3school.com.cn/sql/sql_dates.asp

SELECT DATEDIFF('2008-11-30','2008-10-20');

– date_sub()
– 函数从日期减去指定的时间间隔
– 减去2天

select date_sub('2008-12-27 16:25:46',interval 2 day);

– 减去2个月

select date_sub('2008-12-27 16:25:46',interval 2 month);

– 减去2年

select date_sub('2008-12-27 16:25:46',interval 2 year);

– 减去2个小时

select date_sub('2008-12-27 16:25:46',interval 2 hour);

– DATE_ADD()
– 函数向日期添加指定的时间间隔
– 当前时间加2天

select DATE_ADD(now(),INTERVAL 2 day);

– 当前时间加2小时

select date_add(now(),interval 2 hour);

– TIMESTAMPDIFF(单位，开始，结束);
– TIMESTAMPDIFF函数返回begin-end的结果，其中begin和end是DATE或DATETIME表达式。
– 也就是返回中间两个日期的时间差第一个参数是时间差的单位

-- 常用单位
-- MICROSECOND  微秒
-- SECOND  秒
-- MINUTE  分钟
-- HOUR  小时
-- DAY  天
-- WEEK  周
-- MONTH  月份
-- QUARTER 
-- YEAR  年份

SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2018-06-01') ;

– 返回两个时间相差几个小时

select TimeStampDiff(hour,'2008-12-27 13:25:46','2008-12-27 16:25:46');

– 也可以用它计算年龄

select timestampdiff(year,'2008-01-03',now());

– current_date
– 获取date 格式的日期

select current_date;

– convert()
– convert()函数和cast()函数一样都是类型转换函数

-- 可以用的类型
-- 字符型,可带参数 : CHAR()     
--  日期 : DATE     
--  时间: TIME     
--  日期时间型 : DATETIME     
--  浮点数  : DECIMAL      
--  整数 : SIGNED     
--  无符号整数 : UNSIGNED 

select convert('2008-12-27 16:25:46',DATETIME);

– SQL从‘＞’左边操作字段索引就会失效
– 计算两个时间的间隔(部分SQL版本支持)

SELECT date_diff('MONTH','2022-07-28 10:53:17',now()) FROM t_consumer tc ;

– 当前时间减去60天

select current_date() - interval '60' day;

– between and
– BETWEEN AND关键字可以判断某个字段的值是否在指定的范围内。

select 10 between 0 and 2;

– 分组排序查询
– 我们取每组的第一名

select rank() over (partition by customer_id order by created_time desc) as rn where rn = 1;

– uuid
– 获取一个随机的uuid

select uuid();

-- @ 符号 变量
-- 设置变量
set @namerr = '100';

-- 获取变量
select @namerr;

– 查询出的结果直接复制给变量

select @max_time := (select 100);

select @max_time;

– 在数据导入过程中，为提高导入效率，可以先禁用外键检查，导入完后再开启。

-- 禁用外键检查
-- 外键约束
-- MYSQL 对外键约束只支持即时检查(immediate check)， 与SQL SERVER 相同。
-- ORACLE 对外键约束支持延时检查(deferred check)。
-- 在创建外键约束时，MYSQL 默认为外键约束列添加索引，以避免外键约束带来的死锁问题。 
-- https://blog.youkuaiyun.com/weixin_30877755/article/details/99965607
SET @@foreign_key_checks=0;

– 启用外键检查

SET @@foreign_key_checks=1;

– case when 1<2 then 1 end
– 选择语句

select case when 1<2 then 1 END;

select case when 1>0 then 1 when 1>2 then 3 when 1>3 then 4 END;

select * from tsr_order where id is not null and others is not NULL;

– replace()
– 语法 REPLACE (string_expression,string_pattern,string_replacement)

-- string_expression 要搜索的字符串表达式。string_expression 可以是字符或二进制数据类型。
-- string_pattern 是要查找的子字符串。string_pattern 可以是字符或二进制数据类型。string_pattern 不能是空字符串 ('')。
-- string_replacement 替换字符串。string_replacement 可以是字符或二进制数据类型。
-- REPLACE(String,from_str,to_str) 即：将String中所有出现的from_str替换为to_str。
-- 将字符串中的1换成一
select replace('123456','1','一');

– 查询md5加密后的数据

SELECT md5('456545333');

– 转换当前时间格式(部分SQL版本支持)

select cast(current_date as timestamp);

– 添加索引

ALTER TABLE `tsr_order_detail_shuyun0210_null` ADD INDEX index_name (order_detail_id);
ALTER TABLE `tsr_order_detail_shuyun0210_null` ADD INDEX index_name_return(return_order_detail_id);

– JSON

select json_extract('{"id":"sss","name":"xxx","sex":"man"}','$[0].id');

– JSON中的值

select json_extract('{"id":"sss","name":"xxx","sex":"man","love":[{"ball":"basketBall"}]}','$[0].id');

– PRIMARY KEY ( id ) 设置id为主键
– ENGINE 设置存储引擎
– CHARSET 设置编码
– COMMENT 注释
– 创建一个member表

CREATE TABLE IF NOT EXISTS `m_member` ( 
	`m_id` INT(12) UNSIGNED AUTO_INCREMENT COMMENT '会员id',
	`m_name` VARCHAR (255) NULL COMMENT '会员姓名', 
	`m_age` INT (12) COMMENT '会员年龄', 
	`m_email` varchar(255) null comment '会员邮箱',
	`m_birthday` datetime null comment '会员生日',
	PRIMARY KEY ( `m_id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;

– explain关键字
– 【作用】查看SQL是否使用索引，前面加上explain即可

– https://www.cnblogs.com/tufujie/p/9413852.html

explain select * from t_order;

– explain SQL的执行计划
– 查看是否命中索引

-- type:type是是否命中索引的标志
-- type=const 表示索引命中了唯一性的索引,只返回一行数据,速度比较快;最好;
-- type=eq_ref表示了唯一性索引的所有部分被连接使用,最多返回一行数据;第二好;
-- type=ref 表示了索引命中了不是唯一性索引可能会查出多行;次好;
-- type=ref_or_null 和ref类似但是可以搜索值为null的行;
-- type=range 范围扫描,通常在 in(),between,>,<等操作(这些操作符号左边必须是索引列[索引列不能在公式里]才会命中);[使用了部分索引]
-- type=index 和ALL差不多,比ALL稍微好一点;扫描了所有的索引,但[没有使用到索引] 
-- type=ALL全表扫描[没有使用到索引]
-- possible_keys:可以使用的索引
-- key:命中的索引[实际使用的索引]
-- rows:表示扫描了多少条数据[不是查出多少条数据]
-- 不建议使用or更建议使用and
-- 注意:主键/唯一键/外键 都有索引,尽量使用;
explain select id from m_member where id > 4;