更新

1
UPDATE tb1 SET t_id='1' WHERE id=3;

查询获取行号rownum

1
2
3
4
5
6
7
8
SELECT
s.*,
@ROW := @ROW + 1 AS row_num
FROM
( SELECT s.khjl FROM yhhx_khjl s WHERE s.erji = 'xxx' GROUP BY s.khjl ) s,(
SELECT
@ROW := 0
) t

函数

拼接函数 CONCAT

1
2
3
4
5
6
7
8
9
-- CONCAT(str1,str2…) 
-- concat 拼接 str1和str2字符串, 省略号....代表可以多个字符串拼接
SELECT CONCAT("how","are");
SELECT CONCAT("how","are","you","?");
-- CONCAT_WS(separator,str1,str2…)
-- 解释:concat_ws 拼接str1,str2,并且将separator【指定的字符串】分割拼接,....代表多个字符串拼接
SELECT CONCAT_WS(" ","hello","word");
SELECT CONCAT_WS(",","hello","word");
SELECT CONCAT_WS(" ","how","are","you","?");

日期函数

mysql 按年月查询

查询2020的数据:

1
select * from table where year(column)='2020';

查找月份为12的数据:

1
select * from table where month(column)='12';

查找天数为本年第二天的数据:

1
select * from table where dayofyear(column)='2';

year,month,dayofyearmysql的函数,分别是取得年,月,和当前时间在本年是第几天的3个函数

查询当月数据:

1
select * from table where date_format(column,'%Y-%m')=date_format(now(),'%Y-%m'

字符准换函数

mysql 中对应 oracle 中的 to_char()to_number() 函数

1
2
3
4
5
TO_CHAR(): 
CAST(123 AS CHAR(3))

TO_NUMBER():
cast( '123 ' as SIGNED INTEGER)

group_concat 多行合并

一个聚合函数,在group语句中使用,可以将多行的字符串按分组整合成一个字符串,注意:使用 GROUP_CONCAT()函数必须对源数据进行分组,否则所有数据会被合并成一行

1
2
3
4
5
SELECT id,group_concat(dept SEPARATOR ',') FROM table_a GROUP BY dept;

SELECT GROUP_CONCAT(CONCAT('\'',ENIN_NAME,'\'')) FROM sys_c_en_entityinfo;

SELECT t.names AS '姓名',GROUP_CONCAT(t.course_name ORDER BY t.course_name SEPARATOR ',') AS '课程' FROM course t GROUP BY t.names

需要注意,GROUP_CONCAT函数默认的最大可连接字符串的长度是1024,如果连接的字符串长度超过1024的话会被截断,不过我们可以通过设置group_concat_max_len的值来修改GROUP_CONCAT的最大长度。
例如:

1
SET SESSION group_concat_max_len= 99999;

mysql中数据累加的方法

  • 方法一:通过自定义变量实现

    1
    2
    3
    4
    5
    6
    7
    select date,sales,

    @cum_sales:=@cum_sales+sales as cum_sales

    from sales,(select @cum_sales:=0)c

    order by date asc;
  • 方法二:通过将聚合函数sum作为窗口函数实现(mysql8.0及以上版本可用)

1
2
3
4
5
6
7
select date,sales,

sum(sales)over(order by date) as cum_sales

from sales

order by date asc;

聚合函数sum作为窗口函数的使用方法:
sum(求和列)over([partition by 分区列]order by 排序列 asc/desc)

分区列和排序列可以不在select列表中,但必须在数据源中,order by只对所在分区中的数据进行排序,与select语句中的排序无关,[partition by 分区列]可省略,若未省略则表示分组累计求和。

mysql 根据某一列进行区间统计

mysql 中可以利用 eltinterval 函数来实现此类需求。
进行统计,小于100的,100500的,5001000的,1000以上的,这各个区间的id数利用interval划出4个区间,再利用elt函数将4个区间分别返回一个列名。

1
2
3
select elt(interval(d.yb,0, 100, 500, 1000), 'less100', '100to500', '500to1000', 'more1000') as yb_level, count(d.id) as cnt
from k1 d
group by yb_level;

mysql 判断ip是否在某个ip网段

  • ip:想要查找的ip对应的十进制数
  • start_ip:网段开始地址
  • end_ip:网段结束地址
  • INET_ATON:mysql中将ip转化为十进制数的函数
    1
    SELECT * FROM ip_cz where ip BETWEEN INET_ATON(`start_ip`) AND INET_ATON(`end_ip`)