虽然我们项目有sequelize的orm框架,但是避免不了使用sql,毕竟有些例如跨库查询这种,没法用框架解决,在项目中,我用到得sql语句,我都记录下来,后续也会补上,自己能想到得逻辑,用sql如何表示不用质疑对错,我是一边写一边运行的.哈哈哈哈
sql不区分大小写哦~不过一般都是用大写
1. 用sql查询某个用户的全部信息以及某个信息
a.查询某个表所有的信息(这里我取名叫做用户表user)
SElECT * FROM user
b.查询某个用户(字段)所有的信息(例如我想查询id为100001的用户)
SElECT * FROM hx_user where id = 100001
2.如果字段返回的数据为空或者为null,用0返回
a.当我们想要拿到具体数值的时候,后端建表字段设定无可能为null,但是汇总数据这种肯定需要具体数值
SElECT IFNULL(sum, 0) AS sumData FROM user
如果这里的sum为null,返回的sumData是0, 如果不是null的话,sumData返回的是sum原本的值,这里最好是用一个别名来代替.
3.合计 sum与count
a. 假设我要计算套餐id(packet_id)的总条数
select count(packet_id) from XXXX
如果要去重(先去重后汇总)
select count(Distinct(packet_id)) from XXXX
b. 假设我要知道月卡数量(quantity)月卡总数
select sum(quantity) from XXXX
以上说明, count是对条数的汇总,例如我想汇总多少个用户,就是count(user_id),SUM是字段数据的合计,总和,比如一条数据里面的amount是多少,我把每个的amount相加起来SUM(amount)
4 where查询的使用
a. 直接查固定的存在某个值的数据
select * from xxx where id = 1
b. 查询部分数据要求的数据,例如我只需要查询id为1, 16的数据,往往这些限定非常有用,节省性能
select * from xxxx where id in (1, 16)
c. 多个条件用and连接
select * from xxxx where packet_id = 10 and id = 1
d. 对时间区间的where使用
select * from xxxx where receive_time BETWEEN :startTime AND :endTime
e. 模糊查询
const searchSql = xxx
select * from xxxx where user_id like `%${searchSql}%`
f. where or and的使用
假设我要查询 不管是packet_id 为10 还是为2 但是nickname必须是165****9338的用户
如果我写下面
那么得到的结果是不对的 因为sql将or解析成了与and同级，所以一定要加上括号,下面是正确的写法
select packet_id AS packetId, nickname from A表
INNER JOIN B表 ON A表.user_id = B表.user_id where nickname = '165****9338'
And (packet_id = 10 OR packet_id = 2)
5. Round 保留多少位小数
保留2位小数
select Round(amount, 2) from xxxx
round() 遵循四舍五入把原值转化为指定小数位数
round(1.45,0) = 1;round(1.55,0)=2
floor()向下舍入为指定小数位数 如：floor(1.45,0)= 1;floor(1.55,0) = 1
ceiling()向上舍入为指定小数位数 如：ceiling(1.45,0) = 2;ceiling(1.55,0)=2
6. DATE_FORMAT的使用
秒转成年月日 (毫秒的应该也可以用这个)
select FROM_UNIXTIME(audit_time, '%Y-%m-%d') from hx_withdraw_audit
7. 条件 case when
假设我只需要汇总购买月卡数大于2的用户的汇总
select SUM(case when quantity > 2 then 1 else 0 end) AS 'quantities' from XXX AS test
8. grounp by 按照什么进行分类
比如喜欢打游戏的人应该知道,游戏厉害和不厉害的等级是不一样的,那么如果我要计算每个等级的不重复的总人数是多少,是不是应该按照等级进行分类
select COUNT(Distinct user_id) AS users from xxxx group by level
有空再补-_-
9. having的使用
where 是group by之前进行筛选，having是group by 之后进行统计的筛选，一般having会和group by一起使用，结合聚合函数，统计之后进行筛选
假设下面有个套餐表,我要统计每个用户优质套餐数,不用返回没有优质套餐的用户,并且a个套餐数对应的人是多少,假设packet_id= 10就是优质套餐
SELECT p.superPacket, count(*) AS userNum From (
SELECT user_id,
SUM(case when packet_id = 10 then 1 else 0 end) as superPacket
FROM xxxxx
GROUP BY user_id
HAVING superPacket > 0
) p GROUP BY p.superPacket
这里的p是括号里面数据的别称,当成一个表数据 HAVING主要是group by分类好了之后,对数据再进行处理
10. ORDER BY的使用
select * from xxxx ORDER BY quantity DESC
DESC 倒序 从大到小 / ASC 顺序 从小到大
11. 反斜杠转义符号
因为like是关键词,这里必须要转一下变成不是关键词
在字符串里面
'select \`like\` from xxxx'
12. 联表查询
假设A表没有nickName,但是a表有user_id,B表有user_id,有nickName,那么我想A表拿到nickName是不是和B表关联就可以
select packet_id AS packetId,
v.quantity,
v.user_id AS userId,
b.nickname As nickName
from xxxx AS v LEFT OUTER JOIN xxxx AS b
on b.user_id = v.user_id
这里要注意的是: 当有两个表的时候,应该给表取一个别名,越简单越好,因为有时候a,b表有相同的字段,比如两个表都有共同的id,所以拿字段的时候应当指定从哪个表拿哪个字段v.user_id AS userId
on这里表示通过两个表哪个字段进行关联
13.分页
const limit = 2
const offset = 1
const sql = `select * from xxxx limit ${offset}, ${limit}`
offset第一个数表示要跳过的数量，后一位表示要取的数量limit 假设前端传过来的数据是page是1, size是2,表示我要去第一页的数据,每页两条 应该这样去处理成limit,offset, offset是从0开始的
const offset = (page -1)*size
const limit = +size
14.SQL_CALC_FOUND_ROWS 与 FOUND_ROWS() 的使用 (使用较少)
如果我们要做分页,一般是要写两份sql,第一就是先拿到符合这个条件的总条数,如果我们直接在count后面写limit,那么得到的是先count 有了结果, 再对这个进行limit,所以是不对的,必须要分开
因为我们数据要返回total
然后再返回当前页多少size的数据返回
select count(*) from xxxx WHERE user_id = 1
select * from xxxx where user_id = 1 limit 1,1
我们换一种方式
select SQL_CALC_FOUND_ROWS* from xxxx WHERE user_id = 1 limit 0, 1;
SELECT FOUND_ROWS();
SQL_CALC_FOUND_ROWS可以拿到数据,FOUND_ROWS可以拿到上个条件的条数
