[sql]sql的select字符串切割
可以经常看看 mysql的refman,写的很棒
查看表结构
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
版权声明：本文为博主原创文章，转载请附上博文链接！
