当前位置:   article > 正文

mysql sql字段切割_[sql]sql的select字符串切割

sql切割字段

查看表结构

show create table

desc table

show full columns from test1;

like语句

1. where id > 1 and name != 'maotai' order by id desc

2. where name like '%sre%' # %任意长度 _任意单个字符,如 '_a_' //三位且中间字母是a的

3. where id not in (11,22,33)

4. where id between 3 and 8

5. 查询空值

6. 通过正则

select * from user where gender regexp '^(m|f)';

7, limit offset

SELECT * FROM t1 WHERE

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR

(key1 < 'bar' AND nonkey = 4) OR

(key1 < 'uux' AND key1 > 'z');

b282d14b15c9b4f35b4ce10b530be00e.png

mysql字段值长度为0和null的区别

88d2d738449a6dbf7a98dd0506e68885.png

702ac61e7237a26da17da1c051307d90.png

常用函数

mysql> select max(age),min(age),sum(age),avg(age),count(age) from user;

+----------+----------+----------+----------+------------+

| max(age) | min(age) | sum(age) | avg(age) | count(age) |

+----------+----------+----------+----------+------------+

| 29 | 24 | 159 | 26.5000 | 6 |

+----------+----------+----------+----------+------------+

1 row in set (0.00 sec)

concat 字符串拼接

left: 保留字符串的前两位

mysql> select left('age',2);

+---------------+

| left('age',2) |

+---------------+

| ag |

+---------------+

1 row in set (0.00 sec)

分组

mysql> select count(gender),gender,class from user group by class,gender;

+---------------+--------+-------+

| count(gender) | gender | class |

+---------------+--------+-------+

| 1 | female | 1 |

| 1 | male | 1 |

| 1 | female | 2 |

| 1 | male | 2 |

| 1 | female | 3 |

| 1 | male | 3 |

+---------------+--------+-------+

select if条件表达式

4d8b0d2a6c7d82ff286c66b42cc150ee.png

sql切割字符串

SELECT SPLIT_STR('a|bb|ccc|dd', '|', 3) as third;

+-------+

| third |

+-------+

| ccc |

+-------+

str = 'www.baidu.com';

SELECT substring_index('www.baidu.com','.', 1);    #www

SELECT substring_index('www.baidu.com','.', 2);    #www.baidu

SELECT substring_index('www.baidu.com','.', -1);   #com

SELECT substring_index('www.baidu.com','.', -2);   #baidu.com

SELECT substring_index(substring_index('www.baidu.com','.', -2), '.', 1);  #baidu

---------------------

作者:来了就走下去

来源:CSDN

原文:https://blog.csdn.net/u012009613/article/details/52770567

版权声明:本文为博主原创文章,转载请附上博文链接!

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/weixin_40725706/article/detail/268472
推荐阅读
相关标签
  

闽ICP备14008679号