写在前面:
转眼毕业快一年了,回顾过去一年在代码技术上的进步显而易见。学到了蛮多的东西,平时多积累,哪怕很陌生看着很难的东西,也随着见得多了用的多了也就慢慢理解了其中的奥秘。
最近,在改造去年做的一个项目中的几个迭代需求。去年这个项目也算是我第一个负责的项目,做的挺用心,注释啥的写的很详细,尽管隔了大半年多时间还是能参照注释回忆起当初是怎么写的。当然,也能看到当初写的代码有多愚蠢,一个列表查询接口需要好几秒的时间等待。一方面,可能由于当初项目时间紧,没来得及顾及太多东西。二来,技术经验能力也差点火候没能很好写出有质量的代码。
这次迭代升级,除了把产品提的几个需求给做了,顺带也把之前写的“烂代码”也给改了。在这之前,也深刻了解体会到了sql在代码中的重要性。很多地方,接口优化都从sql下手,sql查询速度直接影响了接口的响应速度。
趁此契机,做一个日常项目中的常见sql汇总。方便日后调用。文末,附数据库设计规范指南。
1. MYSQL在默认的情况下查询是不区分大小写的
默认情况下,查询叫‘you’的,you和YOU都被查询出来了。
如果想让MYSQL知道你输入的字母是大写还是小写的。
解决方法1
修改表:
ALTER TABLE t1 change name name varchar(255)BINARY;
mysql> select * from t1 where name = 'you';
+----+------+
| id | name |
+----+------+
| 1 | you |
+----+------+
解决方法2
在sql语句中实现:
mysql> select * from t1 where name = binary 'you';
+----+------+
| id | name |
+----+------+
| 1 | you |
+----+------+
Ps:无法通过配置my.ini或者my.cnf文件中的lower_case_table_names参数来设置查询区分大小写。
2.增加字段 | 修改字段
*实际项目中,业务需求迭代中,经常遇到 增加业务字段,修改字段类型情况。高频。
增加字段
Alter table 表名 add 具体字段名 字段类型 comment ‘注释’;
例如:alter table tax_data add fixed_assets_deduction decimal(11,2) comment "固定资产进项抵扣(万元)";
修改字段名称及类型
Alter table 表名 modify 具体字段名 字段类型 comment ‘注释’;
或者
ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
例如:alter table ent_adjust_accounts MODIFY `point` decimal(11,4) COMMENT '综合得分';
或者
ALTER TABLE t1 CHANGE name nameNew varchar(100);
3.自增id恢复到从1开始
*把客户给到的数据导入到数据表中,有时表已经事先被处理过,默认自增id已经不是从1开始。
ALTER TABLE 表名AUTO_INCREMENT=1;
mysql> select * from t1;
+----+---------+
| id | nameNew |
+----+---------+
| 3 | 小三 |
| 4 | 小四 |
+----+---------+
mysql> delete from t1;
Query OK, 2 rows affected (0.03 sec)
mysql> insert into t1(nameNew)values('小五');
Query OK, 1 row affected (0.04 sec)
mysql> select * from t1;
+----+---------+
| id | nameNew |
+----+---------+
| 5 | 小五 |
+----+---------+
删除了原有的2条数据,再加入一条数据,发现刚加入的数据id为5,并不是从1开始。
mysql> delete from t1;
Query OK, 2 rows affected (0.03 sec)
mysql> alter table t1 auto_increment = 1;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t1(nameNew)values('小六');
Query OK, 1 row affected (0.05 sec)
mysql> select * from t1;
+----+---------+
| id | nameNew |
+----+---------+
| 1 | 小六 |
+----+---------+
可以看到 新加的小六id就从1开始了。
Ps:可使用命令直接进行,数据已清空(前提)。
4.对数据进行类型转换(保留X位小数)
*在mysql操作中我们经常需要对数据进行类型转换。此时我们应该使用的是cast()或convert()。
两者的对比
相同点:都是进行数据类型转换,实现的功能基本等同
不同点:两者的语法不同,cast(value as type) 、 convert(value,type)
mysql> SELECT cast(10/3 as decimal(11,2)) AS castType;
+----------+
| castType |
+----------+
| 3.33 |
+----------+
mysql> SELECT convert(10/3,decimal(11,2)) AS convertType;
+-------------+
| convertType |
+-------------+
| 3.33 |
+-------------+
5.行转列 Case函数
*当我们在查询数据的时候,我们可能希望对于一些数字的枚举值展示出其实际的文案值。比如:性别1我们想显示男,2我们想显示女。
简单函数
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
case `gender`
when 1 then '男'
when 2 then '女'
else '未知'
End
搜索函数
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
case
when gender = 1 then '男'
when gender = 2 then '女'
else '未知'
End
例,course表如上,【id,学号,课程,分数】。查询 数学比语文分数高的学生。
No1将行转为列,语文数学分数变为字段。
SELECT
sid,
CASE course WHEN 'yuwen' THEN score ELSE 0 END 语文分数,
CASE course WHEN 'shuxue' THEN score ELSE 0 END 数学分数
FROM
course
No2根据学号id,获取其对应最大的语文和数学分数
SELECT
temp.sid,
max(temp.语文分数) as 最大语文分数,
max(temp.数学分数) as 最大数学分数
FROM
(
SELECT
sid,
CASE course WHEN 'yuwen' THEN score ELSE 0 END 语文分数,
CASE course WHEN 'shuxue' THEN score ELSE 0 END 数学分数
FROM
course
) as temp
GROUP BY temp.sid
No3创建一个view,用来存储No2的结果,查询数学分数大于语文分数的数据
create view tempLast as
SELECT
temp.sid,
max(temp.语文分数) as 最大语文分数,
max(temp.数学分数) as 最大数学分数
FROM
(
SELECT
sid,
CASE course WHEN 'yuwen' THEN score ELSE 0 END 语文分数,
CASE course WHEN 'shuxue' THEN score ELSE 0 END 数学分数
FROM
course
) as temp
GROUP BY temp.sid
SELECT * from tempLast where 最大数学分数>最大语文分数
6. union操作符
*UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。Union All会选取重复的值。适用于,根据不同状态下,列表反显数据排列顺序。
例如,一张政策表policy,有【id,policy政策名,status发布状态,create_time,update_time】字段。现在列表反显要求,优先展示已发布且更新时间最新的政策,其次再展示未发布但创建时间最早的政策。
SELECT * FROM
(select * from policy
WHERE status = 1
ORDER BY update_time DESC) AS t1
UNION
SELECT * FROM
(select * from policy
WHERE status = 0
ORDER BY create_time DESC) AS t2
7. concat以及group_concat的用法
一、concat()函数
1. 功能:将多个字符串连接成一个字符串。
2. 语法:concat(str1, str2,...)
返回结果为连接参数产生的字符串,如果有任何一个参数为null,则返回值为null。
3、举例:
例1:select concat (id, policy, status) as concat from policy;
最后有一行为null是因为policy表中加了一行policy值为null。
最后有一行为null是因为policy表中加了一行policy值为null。
在上面的结果中三个字段id,policy,status的组合没有分隔符,我们可以加一个逗号作为分隔符:
看着更顺眼一些了,but三个字段需要输入两次逗号,如果10个字段,要输入九次逗号...麻烦死了啦,有没有什么简便方法呢?——于是可以指定参数之间的分隔符的concat_ws()来了!!!
二、concat_ws()函数
1. 功能:和concat()一样,将多个字符串连接成一个字符串,但是可以一次性指定分隔符~(concat_ws就是concat with separator)
2. 语法:concat_ws(separator, str1, str2, ...)
说明:第一个参数指定分隔符。需要注意的是分隔符不能为null,如果为null,则返回结果为null。
3、举例:
例3:我们使用concat_ws()将 分隔符指定为逗号,达到与例1相同的效果:
Ps:使用concat_ws不同于concat,哪怕中间字段有为null的,也能展示其余非null的字段。但把分隔符指定为null,结果全部变成了null。
三、group_concat()函数
*前言:在有group by的查询语句中,select指定的字段要么就包含在group by语句的后面,作为分组的依据,要么就包含在聚合函数中。
在上面的政策表中又加了几条政策数据,【id,policy政策名,status发布状态,create_time,update_time】方便后面进行展示。
上面查询了不同政策下对应的政策id是哪些。
但是这样同一个政策名字出现多次,看上去非常不直观。有没有更直观的方法,既让每个名字出现一次,又能显示所有政策id呢?——使用group_concat()
1. 功能:将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
2. 语法:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
说明:通过使用distinct可以排除重复值;如果希望对结果中的值进行排序,可以使用order by子句;separator是一个字符串值,缺省为一个逗号。
3. 举例:
SELECT policy,group_concat(id) as group_concat from policy GROUP BY policy
例:将上面的id号从大到小排序,且用'_'作为分隔符:
SELECT policy,group_concat(id ORDER BY id desc SEPARATOR '_') as group_concat from policy
GROUP BY policy
想看前面几期文章 请点击下列图片
浙政钉2.0应用埋点对接——稳定性监控代码(Emas)和流量分析代码(A+)