日常项目中SQL汇总整理附数据库设计规范

写在前面：

转眼毕业快一年了，回顾过去一年在代码技术上的进步显而易见。学到了蛮多的东西，平时多积累，哪怕很陌生看着很难的东西，也随着见得多了用的多了也就慢慢理解了其中的奥秘。

最近，在改造去年做的一个项目中的几个迭代需求。去年这个项目也算是我第一个负责的项目，做的挺用心，注释啥的写的很详细，尽管隔了大半年多时间还是能参照注释回忆起当初是怎么写的。当然，也能看到当初写的代码有多愚蠢，一个列表查询接口需要好几秒的时间等待。一方面，可能由于当初项目时间紧，没来得及顾及太多东西。二来，技术经验能力也差点火候没能很好写出有质量的代码。

这次迭代升级，除了把产品提的几个需求给做了，顺带也把之前写的“烂代码”也给改了。在这之前，也深刻了解体会到了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+)

浙政钉2.0免登