[sql]sql的select字符串切割

最新推荐文章于 2023-11-30 21:41:39 发布

weixin_30938149

最新推荐文章于 2023-11-30 21:41:39 发布

阅读量367

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/iiiiiher/p/10083912.html

版权

可以经常看看 mysql的refman,写的很棒

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');

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

常用函数

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条件表达式

sql切割字符串

https://www.cnblogs.com/qiaoyihang/p/6270165.html

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 
版权声明：本文为博主原创文章，转载请附上博文链接！

转载于:https://www.cnblogs.com/iiiiiher/p/10083912.html