在写 SQL 时, 经常灵活运用一些 SQL 语句编写的技巧, 可以大大简化程序逻辑. 减少程序与数据库的交互次数, 有利于数据库高可用性, 同时也能显得你的 SQL 很牛 B, 让同事们眼前一亮.
目录
实用的 SQL
1. 插入或替换
2. 插入或更新
3. 插入或忽略
4.SQL 中的 if-else 判断语句
5. 指定数据快照或备份
6. 写入查询结果集
7. 强制使用指定索引
实用的 SQL
1. 插入或替换
如果我们想插入一条新记录(INSERT), 但如果记录已经存在, 就先删除原记录, 再插入新记录.
情景示例: 这张表存的每个客户最近一次交易订单信息, 要求保证单个用户数据不重复录入, 且执行效率最高, 与数据库交互最少, 支撑数据库的高可用.
此时, 可以使用 "REPLACE INTO" 语句, 这样就不必先查询, 再决定是否先删除再插入.
"REPLACE INTO" 语句是基于唯一索引或主键来判断唯一 (是否存在) 的.
"REPLACE INTO" 语句是基于唯一索引或主键来判断唯一 (是否存在) 的.
"REPLACE INTO" 语句是基于唯一索引或主键来判断唯一 (是否存在) 的.
注意事项: 如下 SQL 所示, 需要在 username 字段上建立唯一索引(Unique),transId 设置自增即可.
-- 20 点充值
- REPLACE INTO last_transaction (transId,username,amount,trans_time,remark)
- VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '会员充值');
-- 21 点买皮肤
- REPLACE INTO last_transaction (transId,username,amount,trans_time,remark)
- VALUES (null, 'chenhaha', 100, '2020-06-11 21:00:00', '购买盲僧至高之拳皮肤');
若 username='chenhaha'的记录不存在, REPLACE 语句将插入新记录(首次充值), 否则, 当前 username='chenhaha'的记录将被删除, 然后再插入新记录.
id 不要给具体值, 不然会影响 SQL 执行, 业务有特殊需求除外.
2. 插入或更新
如果我们希望插入一条新记录(INSERT), 但如果记录已经存在, 就更新该记录, 此时, 可以使用 "INSERT INTO ... ON DUPLICATE KEY UPDATE ..." 语句:
情景示例: 这张表存了用户历史充值金额, 如果第一次充值就新增一条数据, 如果该用户充值过就累加历史充值金额, 需要保证单个用户数据不重复录入.
这时可以使用 "INSERT INTO ... ON DUPLICATE KEY UPDATE ..." 语句.
注意事项: 同上,"INSERT INTO ... ON DUPLICATE KEY UPDATE ..." 语句是基于唯一索引或主键来判断唯一 (是否存在) 的. 如下 SQL 所示, 需要在 username 字段上建立唯一索引(Unique),transId 设置自增即可.
-- 用户陈哈哈充值了 30 元买会员
- INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark)
- VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '充会员')
- ON DUPLICATE KEY UPDATE total_amount=total_amount + 30, last_transTime='2020-06-11 20:00:20', last_remark ='充会员';
-- 用户陈哈哈充值了 100 元买瞎子至高之拳皮肤
- INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark)
- VALUES (null, 'chenhaha', 100, '2020-06-11 20:00:20', '购买盲僧至高之拳皮肤')
- ON DUPLICATE KEY UPDATE total_amount=total_amount + 100, last_transTime='2020-06-11 21:00:00', last_remark ='购买盲僧至高之拳皮肤';
若 username='chenhaha'的记录不存在, INSERT 语句将插入新记录, 否则, 当前 username='chenhaha'的记录将被更新, 更新的字段由 UPDATE 指定.
3. 插入或忽略
如果我们希望插入一条新记录(INSERT), 但如果记录已经存在, 就啥事也不干直接忽略, 此时, 可以使用 INSERT IGNORE INTO ... 语句: 情景很多, 不再举例赘述.
注意事项: 同上,"INSERT IGNORE INTO ..." 语句是基于唯一索引或主键来判断唯一 (是否存在) 的, 需要在 username 字段上建立唯一索引(Unique),transId 设置自增即可.
-- 用户首次添加
- INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time)
- VALUES (null, 'chenhaha', '男', 12, 0, '2020-06-11 20:00:20');
-- 二次添加, 直接忽略
- INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time)
- VALUES (null, 'chenhaha', '男', 12, 0, '2020-06-11 21:00:20');
若 username='chenhaha'的记录不存在, INSERT 语句将插入新记录, 否则, 不执行任何操作.
4.SQL 中的 if-else 判断语句
众所周知, if-else 判断在任何地方都很有用, 在 SQL 语句中,"CASE WHEN ... THEN ... ELSE ... END" 语句可以用在增删改查各类语句中.
给个情景: 妇女节大回馈, 2020 年注册的新用户, 所有成年女性账号送 10 元红包, 其他用户送 5 元红包, 自动充值.
示例语句如下:
-- 送红包语句
- UPDATE users_info u
- SET u.balance = CASE WHEN u.sex ='女' and u.age> 18 THEN u.balance + 10
- ELSE u.balance + 5 end
- WHERE u.create_time>= '2020-01-01'
* 情景 2: 有个学生高考分数表, 需要将等级列出来, 650 分以上是重点大学, 600-650 是一本, 500-600 分是二本, 400-500 是三本, 400 以下大专;
原测试数据如下:
查询语句:
- SELECT *,case when total_score>= 650 THEN '重点大学'
- when total_score>= 600 and total_score <650 THEN '一本'
- when total_score>= 500 and total_score <600 THEN '二本'
- when total_score>= 400 and total_score <500 THEN '三本'
- else '大专' end as status_student
- from student_score;
5. 指定数据快照或备份
如果想要对一个表进行快照, 即复制一份当前表的数据到一个新表, 可以结合 CREATE TABLE 和 SELECT:
-- 对 class_id=1(一班)的记录进行快照, 并存储为新表 students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM student WHERE class_id=1;
新创建的表结构和 SELECT 使用的表结构完全一致.
6. 写入查询结果集
如果查询结果集需要写入到表中, 可以结合 INSERT 和 SELECT, 将 SELECT 语句的结果集直接插入到指定表中.
例如, 创建一个统计成绩的表 statistics, 记录各班的平均成绩:
- CREATE TABLE statistics (
- id BIGINT NOT NULL AUTO_INCREMENT,
- class_id BIGINT NOT NULL,
- average DOUBLE NOT NULL,
- PRIMARY KEY (id)
- );
然后, 我们就可以用一条语句写入各班的平均成绩:
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
确保 INSERT 语句的列和 SELECT 语句的列能一一对应, 就可以在 statistics 表中直接保存查询的结果:
- SELECT * FROM statistics;
- +----+----------+--------------+
- | id | class_id | average |
- +----+----------+--------------+
- | 1 | 1 | 475.5 |
- | 2 | 2 | 473.33333333 |
- | 3 | 3 | 488.66666666 |
- +----+----------+--------------+
- 3 rows in set (0.00 sec)
7. 强制使用指定索引
在查询的时候, 数据库系统会自动分析查询语句, 并选择一个最合适的索引. 但是很多时候, 数据库系统的查询优化器并不一定总是能使用最优索引. 如果我们知道如何选择索引, 可以使用 FORCE INDEX 强制查询使用指定的索引. 例如:
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;
指定索引的前提是索引 idx_class_id 必须存在.
心得体会:
MySQL 路漫漫, 其修远兮. 永远不要眼高手低, 一起加油, 希望本文能对你有所帮助.
来源: https://segmentfault.com/a/1190000022927261