面试系列-mysql基础语法

2022-10-27 15:55:29 浏览数 (1)
  1. 复制表-只复制表结构：create table 表名 like 被复制的表名;

代码语言：javascript复制
exp：mysql> create table test12 like test11;
  1. 复制表结构 数据 ：create table 表名 [as] select 字段,... from 被复制的表 [where 条件];
代码语言：javascript复制
exp：mysql> create table test13 as select * from test11;
  1. 修改表名 ：alter table 表名 rename [to] 新表名;
  2. 添加列 ：alter table 表名 add column 列名 类型 [列约束];
代码语言：javascript复制
exp：alter table test14 add column b int not null default 0 comment'字段b';
  1. 修改列

alter table 表名 modify column 列名 新类型 [约束]; 或者

alter table 表名 change column 列名 新列名 新类型 [约束];

2种⽅式区别：modify不能修改列名，change可以修改列名

代码语言：javascript复制
exp：alter table test14 change column c d varchar(10) not null default '' comment '字段d';
  1. 删除列

alter table 表名 drop column 列名;

代码语言：javascript复制
exp：alter table test14 drop column d;
  1. drop，truncate，delete区别：

drop (删除表)：删除内容和定义，释放空间，简单来说就是把整个表去掉，以后要新增数据是不可能的，除⾮新增⼀个表。

drop语句将删除表的结构被依赖的约束（constrain），触发器（trigger）索引（index），依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。如果要删除表定义及其数据，请使⽤ drop table 语句。

truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)，与drop不同的是，只是清空表数据⽽已。

注意：truncate不能删除具体⾏数据，要删就要把整个表清空了。

delete (删除表中的数据)：delete 语句⽤于删除表中的⾏。delete语句执⾏删除的过程是每次从表中删除⼀⾏，并且同时将该⾏的删除操作作为事务记录在⽇志中保存，以便进⾏进⾏回滚操作。

truncate与不带where的delete ：只删除数据，⽽不删除表的结构（定义）truncate table 删除表中的所有⾏，但表结构及其列、约束、索引等保持不变。

对于由foreign key约束引⽤的表，不能使⽤truncate table ，⽽应使⽤不带where⼦句的delete语句。由于truncate table 记录在⽇志中，所以它不能激活触发器。

delete语句是数据库操作语⾔(dml)，这个操作会放到 rollback segement 中，事务提交之后才⽣效；如果有相应的 trigger，执⾏的时候将被触发。

truncate、drop 是数据库定义语⾔(ddl)，操作⽴即⽣效，原数据不放到 rollbacksegment 中，不能回滚，操作不触发 trigger。

如果有⾃增列，truncate⽅式删除之后，⾃增列的值会被初始化，delete⽅式要分情况（如果数据库被重启了，⾃增列值也会被初始化，数据库未被重启，则不变）如果要删除表定义及其数据，请使⽤ drop table 语句

安全性：⼩⼼使⽤ drop 和 truncate，尤其没有备份的时候，否则哭都来不及删除速度，⼀般来说: drop> truncate > delete

  1. where和having的区别：

where是在分组（聚合）前对记录进⾏筛选，⽽having是在分组结束后的结果⾥筛选，最后返回整个sql的查询结果。可以把having理解为两级查询，即含having的查询操作先获得不含having⼦句时的sql查询结果表，然后在这个结果表上使⽤having条件筛选出符合的记录，最后返回这些记录，因此，having后是可以跟聚合函数的，并且这个聚集函数不必与select后⾯的聚集函数相同。

代码语言：javascript复制
SELECT
    user_id ⽤户id, count(id) 下单数量
FROM
    t_order t
WHERE
    t.the_year = 2018
    GROUP BY user_id
HAVING 下单数量>=2;
  1. where & group by & having & order by & limit语法：
代码语言：javascript复制
select 列 from 
表名
where [查询条件]
group by [分组表达式]
having [分组过滤条件]
order by [排序条件]
limit [offset,] count;
  1. MySQL 数值型函数
代码语言：javascript复制
abs:求绝对值
函数 ABS(x) 返回 x 的绝对值。正数的绝对值是其本⾝，负数的绝对值为其相反
数，0 的绝对值是 0。
mysql> select abs(5),abs(-2.4),abs(-24),abs(0);
 -------- ----------- ---------- -------- 
| abs(5) | abs(-2.4) | abs(-24) | abs(0) |
 -------- ----------- ---------- -------- 
| 5 | 2.4 | 24 | 0 |
 -------- ----------- ---------- -------- 
1 row in set (0.00 sec)
    
sqrt:求⼆次⽅跟（开⽅）
函数 SQRT(x) 返回⾮负数 x 的⼆次⽅根。负数没有平⽅根，返回结果为 NULL。
mysql> select sqrt(25),sqrt(120),sqrt(-9);
 ---------- -------------------- ---------- 
| sqrt(25) | sqrt(120) | sqrt(-9) |
 ---------- -------------------- ---------- 
| 5 | 10.954451150103322 | NULL |
 ---------- -------------------- ---------- 
1 row in set (0.00 sec)
    
mod:求余数
函数 MOD(x,y) 返回 x 被 y 除后的余数，MOD() 对于带有⼩数部分的数值也起作
⽤，它返回除法运算后的余数。
mysql> select mod(63,8),mod(120,10),mod(15.5,3);
 ----------- ------------- ------------- 
| mod(63,8) | mod(120,10) | mod(15.5,3) |
 ----------- ------------- ------------- 
| 7 | 0 | 0.5 |
 ----------- ------------- ------------- 
1 row in set (0.00 sec)
    
ceil和ceiling:向上取整
函数 CEIL(x) 和 CEILING(x) 的意义相同，返回不⼩于 x 的最⼩整数值，返回值转
化为⼀个 BIGINT。
mysql> select ceil(-2.5),ceiling(2.5);
 ------------ -------------- 
| ceil(-2.5) | ceiling(2.5) |
 ------------ -------------- 
| -2 | 3 |
 ------------ -------------- 
    
1 row in set (0.00 sec)
floor:向下取整
[loor(x) 函数返回⼩于 x 的最⼤整数值。
mysql> select floor(5),floor(5.66),floor(-4),floor(-4.66);
 ---------- ------------- ----------- -------------- 
| floor(5) | floor(5.66) | floor(-4) | floor(-4.66) |
 ---------- ------------- ----------- -------------- 
| 5 | 5 | -4 | -5 |
 ---------- ------------- ----------- -------------- 
1 row in set (0.00 sec)
 
rand:⽣成⼀个随机数
⽣成⼀个0~1之间的随机数，传⼊整数参数是，⽤来产⽣重复序列
mysql> select rand(), rand(), rand();
 -------------------- -------------------- -------------------- 
| rand() | rand() | rand() |
 -------------------- -------------------- -------------------- 
| 0.5224735778965741 | 0.3678060549942833 | 0.2716095720153391 |
 -------------------- -------------------- -------------------- 
1 row in set (0.00 sec)
mysql> select rand(1),rand(2),rand(1);
 --------------------- -------------------- --------------------- 
| rand(1) | rand(2) | rand(1) |
 --------------------- -------------------- --------------------- 
| 0.40540353712197724 | 0.6555866465490187 | 0.40540353712197724 |
 --------------------- -------------------- --------------------- 
1 row in set (0.00 sec)
mysql> select rand(1),rand(2),rand(1);
 --------------------- -------------------- --------------------- 
| rand(1) | rand(2) | rand(1) |
 --------------------- -------------------- --------------------- 
| 0.40540353712197724 | 0.6555866465490187 | 0.40540353712197724 |
 --------------------- -------------------- --------------------- 
1 row in set (0.00 sec)
 
round:四舍五⼊函数
返回最接近于参数 x 的整数；ROUND(x,y) 函数对参数x进⾏四舍五⼊的操作，返
回值保留⼩数点后⾯指定的y位。
mysql> select round(-6.6),round(-8.44),round(3.44);
 ------------- -------------- ------------- 
| round(-6.6) | round(-8.44) | round(3.44) |
 ------------- -------------- ------------- 
| -7 | -8 | 3 |
 ------------- -------------- ------------- 
1 row in set (0.00 sec)
mysql> select
round(-6.66,1),round(3.33,3),round(88.66,-1),round(88.46,-2);
 ---------------- --------------- ----------------- ----------------- 
| round(-6.66,1) | round(3.33,3) | round(88.66,-1) | round(88.46,-2) |
 ---------------- --------------- ----------------- ----------------- 
| -6.7 | 3.330 | 90 | 100 |
 ---------------- --------------- ----------------- ----------------- 
1 row in set (0.00 sec)
 
sign:返回参数的符号
返回参数的符号，x 的值为负、零和正时返回结果依次为 -1、0 和 1。
mysql> select sign(-6),sign(0),sign(34);
 ---------- --------- ---------- 
| sign(-6) | sign(0) | sign(34) |
 ---------- --------- ---------- 
| -1 | 0 | 1 |
 ---------- --------- ---------- 
1 row in set (0.00 sec)
 
pow 和 power:次⽅函数
POW(x,y) 函数和 POWER(x,y) 函数⽤于计算 x 的 y 次⽅。
mysql> select pow(5,-2),pow(10,3),pow(100,0),power(4,3),power(6,-3);
 ----------- ----------- ------------ ------------
 ---------------------- 
| pow(5,-2) | pow(10,3) | pow(100,0) | power(4,3) | power(6,-3)
|
 ----------- ----------- ------------ ------------
 ---------------------- 
| 0.04 | 1000 | 1 | 64 |
0.004629629629629629 |
 ----------- ----------- ------------ ------------
 ---------------------- 
1 row in set (0.00 sec)
 
sin:正弦函数
SIN(x) 返回 x 的正弦值，其中 x 为弧度值。
mysql> select sin(1),sin(0.5*pi()),pi();
 -------------------- --------------- ---------- 
| sin(1) | sin(0.5*pi()) | pi() |
 -------------------- --------------- ---------- 
| 0.8414709848078965 | 1 | 3.141593 |
 -------------------- --------------- ---------- 
1 row in set (0.00 sec)
注：PI() 函数返回圆周率（3.141593）
  1. MySQL 字符串函数
代码语言：javascript复制
length:返回字符串直接⻓度
mysql> select length('javacode2018'),length('路⼈甲Java'),length('路⼈');
 ------------------------ ------------------------- ------------------
 
| length('javacode2018') | length('路⼈甲Java') | length('路⼈') |
 ------------------------ ------------------------- ------------------
 
| 12 | 13 | 6
|
 ------------------------ ------------------------- ------------------
 
1 row in set (0.00 sec)
    
concat:合并字符串
CONCAT(sl，s2，...) 函数返回结果为连接参数产⽣的字符串，或许有⼀个或多个参数。
若有任何⼀个参数为 NULL，则返回值为 NULL。若所有参数均为⾮⼆进制字符串，则结果为
⾮⼆进制字符串。若⾃变量中含有任⼀⼆进制字符串，则结果为⼀个⼆进制字符串。
mysql> select concat('路⼈甲','java'),concat('路⼈甲',null,'java');
 ---------------------------- --------------------------------- 
| concat('路⼈甲','java') | concat('路⼈甲',null,'java') |
 ---------------------------- --------------------------------- 
| 路⼈甲java | NULL |
 ---------------------------- --------------------------------- 
1 row in set (0.00 sec)
    
insert:替换字符串
INSERT(s1，x，len，s2) 返回字符串 s1，⼦字符串起始于 x 位置，并且⽤ len 个
字符长的字符串代替 s2。x的值从1开始，第⼀个字符的x=1，若 x 超过字符串长度，则返回值为原始字符
串。
假如 len 的长度⼤于其他字符串的长度，则从位置 x 开始替换。若任何⼀个参数为 NULL
，则返回值为 NULL。
mysql> select
-> insert('路⼈甲Java', 2, 4, '**') AS col1,
-> insert('路⼈甲Java', -1, 4,'**') AS col2,
-> insert('路⼈甲Java', 3, 20,'**') AS col3;
 --------- --------------- ---------- 
| col1 | col2 | col3 |
 --------- --------------- ---------- 
| 路**va | 路⼈甲Java | 路⼈** |
 --------- --------------- ---------- 
1 row in set (0.00 sec)
    
lower:将字⺟转换成⼩写
LOWER(str) 可以将字符串 str 中的字母字符全部转换成⼩写。
mysql> select lower('路⼈甲JAVA');
 ------------------------ 
| lower('路⼈甲JAVA') |
 ------------------------ 
| 路⼈甲java |
 ------------------------ 
1 row in set (0.00 sec)
    
upper:将字⺟转换成⼤写
UPPER(str) 可以将字符串 str 中的字母字符全部转换成⼤写。
mysql> select upper('路⼈甲java');
 ------------------------ 
| upper('路⼈甲java') |
 ------------------------ 
| 路⼈甲JAVA |
 ------------------------ 
1 row in set (0.00 sec)
    
leb:从左侧截取字符串
LEFT(s，n) 函数返回字符串 s 最左边的 n 个字符，s=1表⽰第⼀个字符。
mysql> select left('路⼈甲JAVA',2),left('路⼈甲JAVA',10),left('路⼈甲
JAVA',-1);
 ------------------------- --------------------------
 -------------------------- 
| left('路⼈甲JAVA',2) | left('路⼈甲JAVA',10) | left('路⼈甲
JAVA',-1) |
 ------------------------- --------------------------
 -------------------------- 
| 路⼈ | 路⼈甲JAVA | |
 ------------------------- --------------------------
 -------------------------- 
1 row in set (0.00 sec)
                                                
right:从右侧截取字符串
RIGHT(s，n) 函数返回字符串 s 最右边的 n 个字符。
mysql> select right('路⼈甲JAVA',1),right('路⼈甲JAVA',10),right('路⼈甲
JAVA',-1);
 -------------------------- ---------------------------
 --------------------------- 
| right('路⼈甲JAVA',1) | right('路⼈甲JAVA',10) | right('路⼈甲JAVA',-1) |
 -------------------------- ---------------------------
 --------------------------- 
| A | 路⼈甲JAVA |
|
 -------------------------- ---------------------------
 --------------------------- 
1 row in set (0.00 sec)
                                                           
trim:删除字符串两侧空格
TRIM(s) 删除字符串 s 两侧的空格。
mysql> select '[ 路⼈甲Java ]',concat('[',trim(' 路⼈甲Java'),']');
 ----------------------- ---------------------------------------------
 
| [ 路⼈甲Java ] | concat('[',trim(' 路⼈甲Java '),']') |
 ----------------------- ---------------------------------------------
 
| [ 路⼈甲Java ] | [路⼈甲Java] |
 ----------------------- ---------------------------------------------
 
1 row in set (0.00 sec)
                                                           
replace:字符串替换
REPLACE(s，s1，s2) 使⽤字符串 s2 替换字符串 s 中所有的字符串 s1。
substr 和 substring:截取字符串
substr(str,pos)
substr(str from pos)
substr(str,pos,len)
substr(str from pos for len)
                                                           
substr()是substring()的同义词。
没有len参数的形式是字符串str从位置pos开始返回⼀个⼦字符串。
带有len参数的形式是字符串str从位置pos开始返回长度为len的⼦字符串。
使⽤FROM的形式是标准的SQL语法。
也可以对pos使⽤负值，在这种情况下，⼦字符串的开头是字符串末尾的pos字
符，⽽不是开头。在这个函数的任何形式中pos可以使⽤负值。
对于所有形式的substring()，从中提取⼦串的字符串中第⼀个字符的位置被认为是1。
/** 第三个字符之后的⼦字符串：inese **/
SELECT substring('chinese', 3);
/** 倒数第三个字符之后的⼦字符串：ese **/
SELECT substring('chinese', -3);
/** 第三个字符之后的两个字符：in **/
SELECT substring('chinese', 3, 2);
/** 倒数第三个字符之后的两个字符：es **/
SELECT substring('chinese', -3, 2);
/** 第三个字符之后的⼦字符串：inese **/
SELECT substring('chinese' FROM 3);
/** 倒数第三个字符之后的⼦字符串：ese **/
SELECT substring('chinese' FROM -3);
/** 第三个字符之后的两个字符：in **/
SELECT substring('chinese' FROM 3 FOR 2);
/** 倒数第三个字符之后的两个字符：es **/
SELECT substring('chinese' FROM -3 FOR 2);
                                                           
reverse:反转字符串
REVERSE(s) 可以将字符串 s 反转，返回的字符串的顺序和 s 字符串的顺序相反。
mysql> select reverse('路⼈甲Java');
 -------------------------- 
| reverse('路⼈甲Java') |
 -------------------------- 
| avaJ甲⼈路 |
 -------------------------- 
1 row in set (0.00 sec)
  1. MySQL ⽇期和时间函数
代码语言：javascript复制
curdate 和 current_date:两个函数作⽤相同，返回当前系统的⽇期值
CURDATE() 和 CURRENT_DATE() 函数的作⽤相同，将当前⽇期按照“YYYY-MM-
DD”或“YYYYMMDD”格式的值返回，具体格式根据函数⽤在字符串或数字语境中
⽽定，返回的 date 类型。
mysql> select curdate(),current_date(),current_date() 1;
 ------------ ---------------- ------------------ 
| curdate() | current_date() | current_date() 1 |
 ------------ ---------------- ------------------ 
| 2019-09-17 | 2019-09-17 | 20190918 |
 ------------ ---------------- ------------------ 
1 row in set (0.00 sec)
    curdme 和 current_dme:获取系统当前时间
CURTIME() 和 CURRENT_TIME() 函数的作⽤相同，将当前时间以“HH：MM：
SS”或“HHMMSS”格式返回，具体格式根据函数⽤在字符串或数字语境中⽽定，
返回 time 类型。
mysql> select curtime(),current_time(),current_time() 1;
 ----------- ---------------- ------------------ 
| curtime() | current_time() | current_time() 1 |
 ----------- ---------------- ------------------ 
| 16:11:25 | 16:11:25 | 161126 |
 ----------- ---------------- ------------------ 
1 row in set (0.00 sec)
now 和 sysdate:获取当前时间⽇期
NOW() 和 SYSDATE() 函数的作⽤相同，都是返回当前⽇期和时间值，格式为
“YYYY-MM-DD HH：MM：SS”或“YYYYMMDDHHMMSS”，具体格式根据函数⽤在
字符串或数字语境中⽽定，返回 datetime 类型。
mysql> select now(),sysdate();
 --------------------- --------------------- 
| now() | sysdate() |
 --------------------- --------------------- 
| 2019-09-17 16:13:28 | 2019-09-17 16:13:28 |
 --------------------- --------------------- 
1 row in set (0.00 sec)
unix_dmestamp:获取UNIX时间戳
UNIX_TIMESTAMP(date) 若⽆参数调⽤，返回⼀个⽆符号整数类型的 UNIX 时间
戳（'1970-01-01 00:00:00'GMT之后的秒数）。
mysql> select
unix_timestamp(),unix_timestamp(now()),now(),unix_timestamp('2019-09-1
7 12:00:00');
 ------------------ ----------------------- ---------------------
 --------------------------------------- 
| unix_timestamp() | unix_timestamp(now()) | now() |
unix_timestamp('2019-09-17 12:00:00') |
 ------------------ ----------------------- ---------------------
 --------------------------------------- 
                                                            | 1568710893 | 1568710893 | 2019-09-17 17:01:33 |
1568692800 |
 ------------------ ----------------------- ---------------------
 --------------------------------------- 
1 row in set (0.00 sec)
from_unixdme:时间戳转⽇期
FROMUNIXTIME(unixtimestamp[,format]) 函数把 UNIX 时间戳转换为普通格式的
⽇期时间值，与  UNIX_TIMESTAMP () 函数互为反函数。
有2个参数：
unix_timestamp：时间戳（秒）
format：要转化的格式 ⽐如“”%Y-%m-%d“” 这样格式化之后的时间就是
2017-11-30
                                                            month:获取指定⽇期的⽉份
MONTH(date) 函数返回指定 date 对应的⽉份，范围为 1～12。
mysql> select month('2017-12-15'),month(now());
 --------------------- -------------- 
| month('2017-12-15') | month(now()) |
 --------------------- -------------- 
| 12 | 9 |
 --------------------- -------------- 
1 row in set (0.00 sec)
monthname:获取指定⽇期⽉份的英⽂名称
MONTHNAME(date) 函数返回⽇期 date 对应⽉份的英⽂全名。
mysql> select monthname('2017-12-15'),monthname(now());
 ------------------------- ------------------ 
| monthname('2017-12-15') | monthname(now()) |
 ------------------------- ------------------ 
| December | September |
 ------------------------- ------------------ 
1 row in set (0.00 sec)
dayname:获取指定⽇期的星期名称
DAYNAME(date) 函数返回 date 对应的⼯作⽇英⽂名称，例如 Sunday、Monday
等。
mysql> select now(),dayname(now());
 --------------------- ---------------- 
| now() | dayname(now()) |
 --------------------- ---------------- 
| 2019-09-17 17:13:08 | Tuesday |
 --------------------- ---------------- 
1 row in set (0.00 sec)
                                                            dayofweek:获取⽇期对应的周索引
DAYOFWEEK(d) 函数返回 d 对应的⼀周中的索引（位置）。1 表⽰周⽇，2 表⽰
周⼀，……，7 表⽰周六。这些索引值对应于ODBC标准。
mysql> select now(),dayofweek(now());
 --------------------- ------------------ 
| now() | dayofweek(now()) |
 --------------------- ------------------ 
| 2019-09-17 17:14:21 | 3 |
 --------------------- ------------------ 
1 row in set (0.00 sec)
week:获取指定⽇期是⼀年中的第⼏周
WEEK(date[,mode]) 函数计算⽇期 date 是⼀年中的第⼏周。WEEK(date,mode)
函数允许指定星期是否起始于周⽇或周⼀，以及返回值的范围是否为 0～52 或 1
～53。
WEEK函数接受两个参数：
• date 是要获取周数的⽇期。
• mode 是⼀个可选参数，⽤于确定周数计算的逻辑。它允许您指定本周是从星期
⼀还是星期⽇开始，返回的周数应在 0 到 52 之间或 0 到 53 之间。
如果忽略 mode 参数，默认情况下 WEEK 函数将使⽤ default_week_format 系统变
量的值。
要获取 default_week_format 变量的当前值，请使⽤ SHOW VARIABLES 语句如
下：
mysql> SHOW VARIABLES LIKE 'default_week_format';
 --------------------- ------- 
| Variable_name | Value |
 --------------------- ------- 
| default_week_format | 0 |
 --------------------- ------- 
1 row in set, 1 warning (0.00 sec)
在我们的服务器中， default_week_format 的默认值为 0 ，下表格说明了 mode 参数如何
影响 WEEK 函数：
dayofyear:获取指定⽇期在⼀年中的位置
DAYOFYEAR(d) 函数返回 d 是⼀年中的第⼏天，范围为 1～366。
mysql> select now(),dayofyear(now()),dayofyear('2019-01-01');
 --------------------- ------------------ ------------------------- 
| now() | dayofyear(now()) | dayofyear('2019-01-01') |
 --------------------- ------------------ ------------------------- 
| 2019-09-17 17:22:00 | 260 | 1 |
 --------------------- ------------------ ------------------------- 
1 row in set (0.00 sec)
dayofmonth:获取指定⽇期在⼀个⽉的位置
DAYOFMONTH(d) 函数返回 d 是⼀个⽉中的第⼏天，范围为 1～31。
mysql> select now(),dayofmonth(now()),dayofmonth('2019-01-01');
 --------------------- ------------------- -------------------------- 
| now() | dayofmonth(now()) | dayofmonth('2019-01-01') |
 --------------------- ------------------- -------------------------- 
| 2019-09-17 17:23:09 | 17 | 1 |
 --------------------- ------------------- -------------------------- 
1 row in set (0.00 sec)
                                                            year:获取年份
YEAR() 函数可以从指定⽇期值中来获取年份值。
mysql> select now(),year(now()),year('2019-01-02');
 --------------------- ------------- -------------------- 
| now() | year(now()) | year('2019-01-02') |
 --------------------- ------------- -------------------- 
| 2019-09-17 17:28:10 | 2019 | 2019 |
 --------------------- ------------- -------------------- 
1 row in set (0.00 sec)
dmetosec:将时间转换为秒值
TIMETOSEC(time) 函数返回将参数 time 转换为秒数的时间值，转换公式为“⼩时
×3600  分钟 ×60  秒”。
mysql> select time_to_sec('15:15:15'),now(),time_to_sec(now());
 ------------------------- --------------------- -------------------- 
| time_to_sec('15:15:15') | now() | time_to_sec(now()) |
 ------------------------- --------------------- -------------------- 
| 54915 | 2019-09-17 17:30:44 | 63044 |
 ------------------------- --------------------- -------------------- 
1 row in set (0.00 sec)
sectodme:将秒值转换为时间格式
SECTOTIME(seconds) 函数返回将参数 seconds 转换为⼩时、分钟和秒数的时间
值。
mysql> select sec_to_time(100),sec_to_time(10000);
 ------------------ -------------------- 
| sec_to_time(100) | sec_to_time(10000) |
 ------------------ -------------------- 
| 00:01:40 | 02:46:40 |
 ------------------ -------------------- 
1 row in set (0.00 sec)
date_add和adddate:向⽇期添加指定时间间隔
DATE_ADD(date,INTERVAL expr type)
date：参数是合法的⽇期表达式。expr 参数是您希望添加的时间间隔。
date_sub和subdate:⽇期减法运算
DATE_SUB(date,INTERVAL expr type)
date：参数是合法的⽇期表达式。expr 参数是您希望添加的时间间隔。
type的类型和date_add中的type⼀样。
mysql> select date_sub('2019-01-01',INTERVAL 10
day),subdate('2019-01-01 16:00:00',interval 100 SECOND);
 ----------------------------------------
 ---------------------------------------------------- 
| date_sub('2019-01-01',INTERVAL 10 day) | subdate('2019-01-01
16:00:00',interval 100 SECOND) |
 ----------------------------------------
                                                    ---------------------------------------------------- 
| 2018-12-22 | 2019-01-01 15:58:20
|
 ----------------------------------------
 ---------------------------------------------------- 
1 row in set (0.00 sec)
mysql> select date_sub('2019-01-01',INTERVAL -10
day),subdate('2019-01-01 16:00:00',interval -100 SECOND);
 -----------------------------------------
 ----------------------------------------------------- 
| date_sub('2019-01-01',INTERVAL -10 day) | subdate('2019-01-01
16:00:00',interval -100 SECOND) |
 -----------------------------------------
 ----------------------------------------------------- 
| 2019-01-11 | 2019-01-01 16:01:40
|
 -----------------------------------------
 ----------------------------------------------------- 
1 row in set (0.00 sec)
adddme:时间加法运算
ADDTIME(time,expr) 函数⽤于执⾏时间的加法运算。添加 expr 到 time 并返回
结果。
其中：time 是⼀个时间或⽇期时间表达式，expr 是⼀个时间表达式。
mysql> select addtime('2019-09-18 23:59:59','0:1:1'),
addtime('10:30:59','5:10:37');
 ----------------------------------------
 ------------------------------- 
| addtime('2019-09-18 23:59:59','0:1:1') |
addtime('10:30:59','5:10:37') |
 ----------------------------------------
 ------------------------------- 
| 2019-09-19 00:01:00 | 15:41:36
|
 ----------------------------------------
 ------------------------------- 
1 row in set (0.00 sec)
                                                    subdme:时间减法运算
SUBTIME(time,expr) 函数⽤于执⾏时间的减法运算。
函数返回 time。expr 表⽰的值和格式 time 相同。time 是⼀个时间或⽇期时间表
达式， expr 是⼀个时间。
mysql> select subtime('2019-09-18
23:59:59','0:1:1'),subtime('10:30:59','5:12:37');
 ----------------------------------------
 ------------------------------- 
| subtime('2019-09-18 23:59:59','0:1:1') |
subtime('10:30:59','5:12:37') |
 ----------------------------------------
 ------------------------------- 
| 2019-09-18 23:58:58 | 05:18:22
|
 ----------------------------------------
 ------------------------------- 
1 row in set (0.00 sec)
datediff:获取两个⽇期的时间间隔
DATEDIFF(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数。
date1 和 date2 为⽇期或 date-and-time 表达式。计算时只⽤到这些值的⽇期部
分。
mysql> select datediff('2017-11-30','2017-11-29') as col1,
datediff('2017-11-30','2017-12-15') as col2;
 ------ ------ 
| col1 | col2 |
 ------ ------ 
| 1 | -15 |
 ------ ------ 
1 row in set (0.00 sec)
date_format:格式化指定的⽇期
DATE_FORMAT(date，format) 函数是根据 format 指定的格式显⽰ date 值。
DATE_FORMAT() 函数接受两个参数：
date：是要格式化的有效⽇期值format：是由预定义的说明符组成的格式字符
串，每个说明符前⾯都有⼀个百分⽐字符(%)。
format：格式和上⾯的函数 from_unixtime 中的format⼀样，可以参考上⾯的。
mysql> select date_format('2017-11-30','%Y%m%d') as col0,now() as
col1, date_format(now(),'%Y%m%d%H%i%s') as col2;
 ---------- --------------------- ---------------- 
| col0 | col1 | col2 |
 ---------- --------------------- ---------------- 
| 20171130 | 2019-09-17 17:56:12 | 20190917175612 |
 ---------- --------------------- ---------------- 
1 row in set (0.00 sec)
weekday:获取指定⽇期在⼀周内的索引位置
WEEKDAY(date) 返回date的星期索引(0=星期⼀，1=星期⼆, ……6= 星期天)。
mysql> select now(),weekday(now());
 --------------------- ---------------- 
| now() | weekday(now()) |
 --------------------- ---------------- 
| 2019-09-17 18:01:34 | 1 |
 --------------------- ---------------- 
1 row in set (0.00 sec)
mysql> select now(),dayofweek(now());
 --------------------- ------------------ 
| now() | dayofweek(now()) |
 --------------------- ------------------ 
| 2019-09-17 18:01:34 | 3 |
 --------------------- ------------------ 
1 row in set (0.00 sec)
  1. if:判断 ：IF(expr,v1,v2) 当 expr 为真是返回 v1 的值，否则返回 v2
代码语言：javascript复制
mysql> select if(1<2,1,0) c1,if(1>5,'√','×')
c2,if(strcmp('abc','ab'),'yes','no') c3;
 ---- ---- ----- 
| c1 | c2 | c3 |
 ---- ---- ----- 
| 1 | × | yes |
  1. ifnull:判断是否为空：IFNULL(v1,v2)：v1为空返回v2，否则返回v1。
代码语言：javascript复制
mysql> select ifnull(null,'路⼈甲Java'),ifnull('⾮空','为空');
 ------------------------------ --------------------------- 
| ifnull(null,'路⼈甲Java') | ifnull('⾮空','为空') |
 ------------------------------ --------------------------- 
| 路⼈甲Java | ⾮空 |
 ------------------------------ --------------------------- 
1 row in set (0.00 sec)
  1. case:搜索语句,类似于java中的if..else if..else，类似于java中的if..else if..else，有2种写法

⽅式1：

CASE <表达式>

WHEN <值1> THEN <操作>

WHEN <值2> THEN <操作>

...

ELSE <操作>

END CASE;

⽅式2：

CASE

WHEN <条件1> THEN <命令>

WHEN <条件2> THEN <命令>

...

ELSE commands

END CASE;

代码语言：javascript复制
SELECT
    t.name 姓名,(
        CASE t.sex
            WHEN 1
                THEN '男'
            WHEN 2
                THEN '⼥'
            ELSE '未知' 
        END) 性别
FROM t_stu t;

SELECT
    t.name 姓名,(
        CASE
            WHEN t.sex = 1
                THEN '男'
            WHEN t.sex = 2
                THEN '⼥'
            ELSE '未知' 
        END) 性别
FROM t_stu t;
sql having mysql table truncate

0 人点赞