养成这些 SQL 习惯 是一笔财富!
逆锋起笔
共 5792字,需浏览 12分钟
·
2021-12-24 20:25
以下内容来自公众号逆锋起笔,关注每日干货及时送达
Select A.ID, A.col1, B.col2
-- Select A.ID, col1, col2 –不要这么写,不利于将来程序扩展
from table1 A inner join table2 B on A.ID=B.ID Where …
WHERE EMP_ID=' VPA30890F'
UPDATE EMPLOYEE SET LNAME='YANG'
WHERE EMP_ID=' VPA30890F'
WHERE EMP_ID=' VPA30890F'
WHERE PUB_ID NOT IN
(SELECT PUB_ID FROM TITLES
WHERE TYPE = 'BUSINESS')
--可以改写成:
SELECT A.PUB_NAME FROM PUBLISHERS A
LEFT JOIN TITLES B ON B.TYPE = 'BUSINESS' AND A.PUB_ID=B. PUB_ID
WHERE B.PUB_ID IS NULL
WHERE NOT EXISTS
(SELECT TITLE_ID FROM SALES
WHERE TITLE_ID = TITLES.TITLE_ID)
可以改写成:
SELECT TITLE
FROM TITLES
LEFT JOIN SALES ON SALES.TITLE_ID = TITLES.TITLE_ID
WHERE SALES.TITLE_ID IS NULL
WHERE PUB_ID IN
(SELECT PUB_ID FROM TITLES
WHERE TYPE = 'BUSINESS')
可以改写成:
SELECT A.PUB_NAME --SELECT DISTINCT A.PUB_NAME
FROM PUBLISHERS A
INNER JOIN TITLES B
ON B.TYPE = 'BUSINESS' AND A.PUB_ID=B. PUB_ID
C、
IN
的相关子查询用EXISTS代替,比如
WHERE PUB_ID IN
(SELECT PUB_ID FROM TITLES WHERE TYPE = 'BUSINESS')
--可以用下面语句代替:
SELECT PUB_NAME FROM PUBLISHERS
WHERE EXISTS
(SELECT 1 FROM TITLES
WHERE TYPE = 'BUSINESS' AND PUB_ID= PUBLISHERS.PUB_ID)
D、不要用
COUNT
(*)的子查询判断是否存在记录,最好用
LEFT
JOIN
或者EXISTS,比如有人写这样的语句:
WHERE (SELECT COUNT(*) FROM EMPLOYEE
WHERE JOB_ID=JOBS.JOB_ID)=0
--应该改成:
SELECT JOBS.JOB_DESC FROM JOBS
LEFT JOIN EMPLOYEE ON EMPLOYEE.JOB_ID=JOBS.JOB_ID
WHERE EMPLOYEE.EMP_ID IS NULL
SELECT JOB_DESC FROM JOBS
WHERE (SELECT COUNT(*) FROM EMPLOYEE
WHERE JOB_ID=JOBS.JOB_ID)<>0
--应该改成:
SELECT JOB_DESC FROM JOBS
WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)
应改为:
SELECT ID FROM T WHERE NUM=100*2
SELECT ID FROM T WHERE NUM/2=NUM1
如果NUM有索引应改为:
SELECT ID FROM T WHERE NUM=NUM1*2
如果NUM1有索引则不应该改。
应该改为:
SELECT 年,月,金额 FROM 结余表 WHERE 年=2010 AND月=10
WHERE CONVERT(VARCHAR(10), 日期字段,120)='2010-07-15'
应该改为
WHERE日期字段〉='2010-07-15' AND 日期字段<'2010-07-16'
ISNULL转换的例子:
WHERE ISNULL(字段,'')<>''应改为:WHERE字段<>''
WHERE ISNULL(字段,'')=''不应修改
WHERE ISNULL(字段,'F') ='T'应改为: WHERE字段='T'
WHERE ISNULL(字段,'F')<>'T'不应修改
应改为: WHERE NAME LIKE 'ABC%'
日期查询的例子:
WHERE DATEDIFF(DAY, 日期,'2010-06-30')=0
应改为:WHERE 日期>='2010-06-30' AND 日期 <'2010-07-01'
WHERE DATEDIFF(DAY, 日期,'2010-06-30')>0
应改为:WHERE 日期 <'2010-06-30'
WHERE DATEDIFF(DAY, 日期,'2010-06-30')>=0
应改为:WHERE 日期 <'2010-07-01'
WHERE DATEDIFF(DAY, 日期,'2010-06-30')<0
应改为:WHERE 日期>='2010-07-01'
WHERE DATEDIFF(DAY, 日期,'2010-06-30')<=0
应改为:WHERE 日期>='2010-06-30'
WHERE FAME+ '. '+LNAME='HAIWEI.YANG'
应改为:
WHERE FNAME='HAIWEI' AND LNAME='YANG'
考虑联接优先顺序: (1)INNER JOIN (2)LEFT JOIN (注:RIGHT JOIN 用 LEFT JOIN 替代) (3)CROSS JOIN
--startof 查询在职人数 sql语句 --end of
UPDATE a SET 字段=''
ROLLBACK
--事务回滚开始
--检查报错
IF ( @@ERROR > 0 )
BEGIN
--回滚操作
ROLLBACK TRANSACTION
RAISERROR('删除工作报告错误', 16, 3)
RETURN
END
--结束事务
COMMIT TRANSACTION
逆锋起笔
专注于程序员圈子,你不但可以学习到java
、python
等主流技术干货,还可以第一时间获悉最新技术动态
、内测资格
、BAT大佬的经验
、精品视频教程
、副业赚钱
经验,微信搜索readdot
关注!
图解 SQL 基础知识 SQL 中去重的三种方法 MySQL 8.0 可以操作 JSON 了!! SQL 中如何给指定数据库创建只读用户? MySQL 大批量插入,如何过滤掉重复数据? 优秀的读者“点赞”传统美德
评论