1. 数据库中的roleId字段(varchar类型)，现在根据roleId字段来计算统计serviceId的种类。ServiceId是通过roleId%10000得到，即roleId的后四位。表名为cbgdonedealinfo_copy.数据库为mysql数据库
本题的需求通过下面的方法很容易实现
方法1：直接运算
SELECT DISTINCT roleId %10000 as serviceidFROM cbgdonedealinfo_copy;
借此机会再应用mysql的几个基本函数来实现一下。
方法2：应用CAST函数
CAST(expr AS type)函数介绍：expr待转化的表字段或是字段具体内容，type待转化的类型，这个类型是有范围的，不是可以转为所有的类型。
Type可以转换的类型是有限制的。这个类型可以是以下值其中的一个：
二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型: DATETIME
浮点数: DECIMAL
整数: SIGNED
无符号整数 : UNSIGNED
其中SIGNED是转为有符号类型而UNSIGNED是转为无符号类型，在查询结果显示时转为SIGNED时在表格中是以右对齐方式显示，而UNSIGNED是以左对齐方式显示。
本题需求应用CAST函数sql语句如下：
SELECT DISTINCT CAST(roleId asUNSIGNED)%10000 as serviceId from cbgdonedealinfo_copy;
方法3：应用CONVERT函数
CONVERT函数的用法介绍：CONVERT(expr,type)，其中type可取类型同CAST函数可取类型一样，也不是所有的类型都可以转换。
本题需求应用CONVERT函数sql语句如下：
SELECT DISTINCTCONVERT(roleId,UNSIGNED)%10000 as serviceId from cbgdonedealinfo_copy;
方法4：应用SUBSTRING函数
SUBSTRING(str,pos)介绍：str待传化的表字段或是字段具体内容，pos是位置，当pos值为正数，表示从正向截取从pos位置到字段结束的字符，若pos是负数则表示从返向即从字符末尾开始数到长度为pos的位置，也即倒数第pos个位置开始到字段结束。所以上述需求中要得到roleId的后四位则pos值取-4，sql语句如下：
SELECT DISTINCT SUBSTRING(roleId,-4) FROM cbgdonedealinfo_copy ;
若想做一个排序，可以将转换后的值取一个别名，然后按这个字段排序
SELECT DISTINCT SUBSTRING(roleId,-4) asserviceId FROM cbgdonedealinfo_copy ORDER BY serviceId ;
根据参数的不同，SUBSTRING函数还有以下几种：
SUBSTRING(str FROM pos FOR len)
SUBSTRING(str FROM pos)
SUBSTRING(str,pos,len)
SUBSTRING函数也可用简化的SUBSTR函数代替，参数SUBSTRING函数一样。
SUBSTR(str FROM pos FOR len)
SUBSTR(str FROM pos)
SUBSTR(str,pos)
SUBSTR(str,pos,len)
*特别注意：字符串中的第一个位置始终为1。
还有一个很重要的截取函数
SUBSTRING_INDEX(str,delim,count)，str待截取的表字段或是字段具体内容，delim截取的分隔符，count表示分隔符出现的次数，若count为正数表示返回从遇到第count个分隔符开始之前的所有字符，而count为负数时表示返回从遇到第count个分隔符开始其后的所有字符。如本表中还有一个字段ipArea中符中间是以逗号分隔的，现在想取出第二个逗号之前的内容，sql语句如下：
SELECT DISTINCTSUBSTRING_INDEX(ipArea,',',2) from cbgdonedealinfo_copy
若想取得第二个逗号之后的内容，sql语句如下：
SELECT DISTINCTSUBSTRING_INDEX(ipArea,',',-2) from cbgdonedealinfo_copy
对于本题需求，应用上面的函数实现的sql语句如下：
SELECT DISTINCT SUBSTRING(roleId FROM -4 FOR4) as serviceId FROM cbgdonedealinfo_copy ORDER BY serviceId ;
SELECT DISTINCT SUBSTRING(roleId FROM -4) asserviceId FROM cbgdonedealinfo_copy ORDER BY serviceId ;
SELECT DISTINCT SUBSTRING(roleId,-4,4) asserviceId FROM cbgdonedealinfo_copy ORDER BY serviceId ;
另如何查询某个字段为空的所有记录：
SELECT * FROM cbgdonedealinfo_copy WHEREroleId IS NULL;
当然不为空的查询语句即为：
SELECT * FROM cbgdonedealinfo_copy WHEREroleId IS NOT NULL;
上面的应用若roleId为空的话，则转化后的值就为空，所以可以在将roleId为空的过滤掉不进行转化，sql语句如下：
SELECT DISTINCT SUBSTRING(roleId,-4) asserviceId FROM cbgdonedealinfo_copy WHERE roleId IS NOT NULL ORDER BY serviceId;
方法5：应用RIGHT函数
RIGHT(str,len)函数介绍：str待截取的字段或是字段具体内容，len需要截取字符长度，此函数的功能是从字段str右端开始截取长度为len的字符，即取后len位。
本题需求是取后四位，用RIGHT函数实现SQL语句如下：
SELECT DISTINCT RIGHT(roleId,4) as serviceIdfrom cbgdonedealinfo_copy;
若想取前四位则不是将len置为负数，而是对应的LEFT函数。
LEFT(str,len)截取字符前len位，用法同RIGHT(str,len)函数。
利用上面的函数可以实现批量对某个字段的值进行替换，如现在有一张表名为ServiceList的名，表结构如下：
| Id | serviceName |
| 1 | 笑傲江虎(五湖四海) |
| 2 | 天下无双(五湖四海) |
| 3 | 雄霸天下(五湖四海) |
现在想把”(五湖四海)”去掉，而只保留前面的内容,可借助前面介绍的函数来实现。
UPDATE Servicelist SETserviceName=SUBSTRING_INDEX(serviceName,"(",1) WHERE serviceName LIKE'%五湖四海%'
或：UPDATE Servicelist SET serviceName=SUBSTR(serviceName,1,4) WHEREserviceName LIKE '%猛龙过江%'
*特别注意：字符串中的第一个位置始终为1,所以这里是1而不是0。
/
在上面介绍CAST和CONVERT函数的type时提到BINARY，下面将简短的介绍下BINARY。
首先BINARY不是函数，是类型转换运算符，它用来将他后面的字符或是字段强制转为一个二进制字符串，促使逐字节而不是逐字符进行列比较，可以理解为在字符串比较的时候区分大小写。如下面的查询：
Mysql> SELECT BINARY 'a' = 'A' ASCOLUMN1,'a' = 'A' AS COLUMN2;
结果如下：
| COLUMN1 | COLUMN2 |
| 0 | 1 |
可以看到加BINARY时区分大小写。
下面再通过一个我们经常遇到的场景来进一步介绍BINARY的用法。
首先创建一张名为binaryTest的表：
Mysql-> create TABLE testBinary (
id int(9) unsigned NOT NULL auto_increment,
userName varchar(50),
primary key (id)
)
接着，向表中插入数据：
insert into testBinary (userName) VALUES('小庄');
insert into testBinary (userName) VALUES('老炮');
insert into testBinary (userName) VALUES('高中队');
insert into testBinary (userName) VALUES('喜娃');
insert into testBinary (userName) VALUES('卫生员');
insert into testBinary (userName) VALUES('daen');
insert into testBinary (userName) VALUES('DAEN');
然后，通过like查询userName 含有daen字符的记录。
SELECT userName FROM testBinary WHEREuserName LIKE '%daen%';
得到的结果如下：
| userName |
| daen |
| DAEN |
这显示不是我们想要的，然而通过BINARY就能够很好解决该问题。
修改表让userName为BINARY，语句如下:
ALTER TABLE testBinary MODIFY userNameVARCHAR(50) BINARY;
然后再次做上面的查询：
SELECT userName FROM testBinary WHEREuserName LIKE '%daen%';
得到的结果为：
| userName |
| daen |
至此，如果我们想让某个字段在比较时区分大小则在建表将该字段后加BINARY进行修饰。
create TABLE testBinary (
id int(9) unsigned NOT NULL auto_increment,
userName varchar(50) BINARY not NULL DEFAULT'',
primary key (id)
)
BINARY str 是CAST(str AS BINARY)的缩略形式。即将str强制转为二进制字符串，在比较时区分大小写。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
