SQL 语句规范

为什么80%的码农都做不了架构师？>>>   

一、基本T-SQL 语句

程序中一般使用的基本sql语句

	模式
Inset	Insert  into  表名  （列名1,列名2,列名3,...） values   (值1,值2,值3,...)
delete	Delete from where 列名1 = 值1 and 列名2 = 值2 and 列名3 = 值3...
update	Update 表名  set  列名1 = 值1 ,列名2 = 值2 ,列名3 = 值3…
Select	Select 列名1,列名2,列名3...  from 表名  where 列名1 = 值1 and 列名2 = 值2 and 列名3 = 值3...

示例：以mybatis为例。

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.apps.sys.mapper.ArticleMapper"> <sql id="tableName"> tb_Article </sql> <sql id="keyId"> id </sql> <sql id="Columns"> type_id,user_id,sitemodules_id,status,bigTile,smallTitle,content,source,author,publishTime </sql> <sql id="selectColumns"> <if test="id !=null">and id=#{id} </if> <if test="typeId !=null">and type_id=#{typeId} </if> <if test="userId !=null">and user_id=#{userId} </if> <if test="sitemodulesId !=null">and sitemodules_id=#{sitemodulesId} </if> <if test="bigTile !=null">and bigTile=#{bigTile} </if> <if test="smallTitle !=null">and smallTitle=#{smallTitle} </if> <if test="author !=null">and author=#{author} </if> <if test="publishTime !=null">and publishTime=#{publishTime} </if> <if test="source !=null">and source=#{source} </if> <if test="content !=null">and content=#{content} </if> </sql>   <sql id="updateColumns"> <if test="typeId !=null">, type_id=#{typeId} </if> <if test="userId !=null">, user_id=#{userId} </if> <if test="sitemodulesId !=null">, sitemodules_id=#{sitemodulesId} </if> <if test="bigTile !=null">, bigTile=#{bigTile} </if> <if test="smallTitle !=null">, smallTitle=#{smallTitle} </if> <if test="author !=null">, author=#{author} </if> <if test="publishTime !=null">, publishTime=#{publishTime} </if> <if test="source !=null">, source=#{source} </if> <if test="content !=null">, content=#{content} </if> </sql>   <insert id="insertOne" parameterType="Article" useGeneratedKeys="true" keyProperty="id"> Insert  <include refid="tableName"/> (<include refid="Columns"/>) values (#{typeId},#{userId},#{sitemodulesId},#{bigTile},#{smallTitle},#{content},#{source},#{author},#{publishTime}) </insert>   <delete id="deleteOne" parameterType="int"> delete from <include refid="tableName"/> where id=#{id} </delete>   <update id="updateByColumns" parameterType="Article"> Update  <include refid="tableName"/>  set id=#{id} <include refid="updateColumns"/>  where id=#{id} </update>   <select id="selectOneById" parameterType="int" parameterType="Article"> select <include refid="keyId" />,<include refid="Columns"/>  from <include refid="tableName"/> where id=#{id} </select>   <select id="listPageAll" resultMap="ArticleResultMap"> select * from <include refid="tableName"/> </select>   <select id="listPageByColumns" parameterType="Article" resultMap="ArticleResultMap"> select <include refid="keyId" />,<include refid="Columns"/>  from <include refid="tableName"/>   where 1=1 <include refid="selectColumns"/> </select>   <select id="selectByColumns" parameterType="Article" resultMap="ArticleResultMap"> select <include refid="keyId" />, <include refid="Columns"/>  from <include refid="tableName"/>  where 1=1 <include refid="selectColumns"/> </select> </mapper>

 

二、查询的方法

public Integer insertOne(Role role);	根据字段是否为空添加相应的字段
public Integer deleteOneById(String Id);	根据id删除一条记录
public Integer updateOne(Role role);	根据字段是否为空更新相应的字段
public Role selectOneById(String id);	根据id查询一条记录
public List<Role> selectListByObj(Role role);	根据对象单表查询出多条记录
public List<Role> selectListrefLJByObj(Role role);	根据对象左链接查询出多条记录
public List<Role> selectListrefRJByObj(Role role);	根据对象右链接查询出多条记录
public List<Role> selectListrefFJByObj(Role role);	根据对象全链接查询出多条记录
public List<Role> listPageByObj(Role role);	根据对象单表分页查询出多条记录
public List<Role> listPageRefLJByObj(Role role);	根据对象左链接分页查询出多条记录
public List<Role> listPageRefRJByObj(Role role);	根据对象右链接分页查询出多条记录
public List<Role> listPageRefFJByObj(Role role);	根据对象全链接分页查询出多条记录

转载于:https://my.oschina.net/jimiao/blog/746215