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

@[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

原文链接：/2020-10-31-sqlNorm.html

打赏