一、基本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](https://www.oschina.net/action/GoToLink?url=http%3A%2F%2Fmybatis.org%2Fdtd%2Fmybatis-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);
根据对象全链接分页查询出多条记录