@[TOC](sql server(MsSql)字段命名,表命名,视图命名,SQL语句书写参考)

对我个人来说，字段命名，表命名，视图命名，SQL语句书写都有一套自己的习惯，可以减少维护成本。减少不必要的字段误解，让项目更好交接。说白了就是写SQL不要太辣眼睛、字段不要容易混淆。抛开自动生成的SQL，平常自己写SQL还是要排好格式，免得自己回来维护时头大。

先看个辣眼睛的SQL

不用管这个查询的结果，就单纯的想辣眼睛

SELECT * from ( SELECT tp.id, tp.status, tp.number, tu.user_id, tu.login_name, tub.real_name, tu.phone, tp.resale_price_ask, tp.first_price_loan, tp.unit, tp.deadline, tp.loan_time as loan_time, tr.repayment_date, (CASE tp.[status] WHEN '8' THEN tr.reality_date ELSE NULL END ) AS reality_date, tp.funds_name, ( CASE tp.[status] WHEN '7' THEN (CASE WHEN tr.way IN (2, 3) THEN datediff(now(), tr.repayment_date) ELSE 0 END) WHEN '8' THEN (case tr.way when '4' then datediff(tr.reality_date, tr.repayment_date) ELSE 0 END ) ELSE 0 END ) AS overdueDay, (sum(tr.principal) + sum(tr.interest)) as currentBalance, sum(tr.overdue) as currentOverdue, (sum(tr.principal) + sum(tr.interest) - sum(tr.reality_principal) - sum(tr.reality_interest)+( case tr.way when '2' then sum(tr.overdue) when '3' then sum(tr.overdue) else 0.00 end )-sum(tr.reality_overdue) ) as currentTotal, tr.way, (sum(tr.reality_principal) + sum(tr.reality_interest) + sum(tr.reality_overdue)+ sum(tr.reality_default)) as alreadyBalance, tclo.flow_no FROM t_project tp LEFT JOIN t_user tu ON tp.user_id = tu.user_id LEFT JOIN t_user_basis tub ON tub.user_id = tu.user_id LEFT JOIN t_project_refund tr ON tp.id = tr.project_id LEFT JOIN t_cash_loan_order tclo on tp.id = tclo.project_id where 1=1 and tp.[status] in ('6','7','8','9')) as tb

命名规范

• 字段
  英文单词驼峰写法，外键用：表名_主键写法，表名去除t_，例如 user_id。

• 表命名
  t_英文单词驼峰写法

• 视图名
  v_表名或者英文单词驼峰写法

• SQL语句书写

--简单的
select * from 表名

--查询的字段有点多，而且有链接表
select 
  --字段列表，可以把表A的字段和表B的字段单独写在行里，也可以一行只写一个字段
  --换行在写字段前要缩进，缩进可以按自己习惯来，可以是2空格、3空格、4空格，不建议用t符号
  表A.A, 表A.B,表A.C,
  表B.A, 表B.B,表B.C,

  表C.A,
  表C.B,
  表C.C
from 表A
  left join 表B on 1=1 --链接一张表就换一行，并且缩进
  left join 表C on 1=1
where 1=1 --写条件前也另起一行，不缩进

--以下是稍微复杂的sql，看起来很长，但维护时不至于懵了
SELECT * from 
(
  SELECT 
    tp.id,
    tp.status,
    tp.number,
    tu.user_id,
    tu.login_name,
    tub.real_name,
    tu.phone,
    tp.resale_price_ask,
    tp.first_price_loan,
    tp.unit,
    tp.deadline,
    tp.loan_time as loan_time,
    tr.repayment_date,
    (CASE tp.[status] WHEN '8' THEN tr.reality_date ELSE NULL END) AS reality_date,
    tp.funds_name,
    (CASE 
      tp.[status] 
      WHEN '7' THEN (CASE WHEN tr.way IN (2, 3) THEN datediff(now(), tr.repayment_date) ELSE 0 END) 
      WHEN '8' THEN (CASE tr.way WHEN '4' THEN datediff(tr.reality_date, tr.repayment_date) ELSE 0 END)
      ELSE 0 
    END) AS overdueDay,
    (sum(tr.principal) + sum(tr.interest)) as currentBalance,
    sum(tr.overdue) as currentOverdue,
    (
      sum(tr.principal) + 
      sum(tr.interest) - 
      sum(tr.reality_principal) - 
      sum(tr.reality_interest)+
      (CASE tr.way WHEN '2' THEN sum(tr.overdue) WHEN '3' THEN sum(tr.overdue) else 0.00 end) -
      sum(tr.reality_overdue) 
    ) as currentTotal,
    tr.way,
    (sum(tr.reality_principal) + sum(tr.reality_interest) + sum(tr.reality_overdue)+ sum(tr.reality_default)) as alreadyBalance,
    tclo.flow_no 
  FROM t_project tp 
    LEFT JOIN t_user tu ON tp.user_id = tu.user_id 
    LEFT JOIN t_user_basis tub ON tub.user_id = tu.user_id 
    LEFT JOIN t_project_refund tr ON tp.id = tr.project_id 
    LEFT JOIN t_cash_loan_order tclo on tp.id = tclo.project_id 
  where 1=1 and tp.[status] in ('6','7','8','9')
) as tb

原文链接：http://blog.albsz.cn/2020-10-31-sqlNorm.html

最后

以上就是能干芝麻为你收集整理的sql server(MsSql)字段命名,表命名,视图命名,SQL语句书写参考先看个辣眼睛的SQL命名规范的全部内容，希望文章能够帮你解决sql server(MsSql)字段命名,表命名,视图命名,SQL语句书写参考先看个辣眼睛的SQL命名规范所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。