SQL葵花宝典

获取原文件

链接：https://pan.baidu.com/s/1gp88BCQme_H2pWah40wYtA 提取码：u0qb
复制这段内容后打开百度网盘手机App，操作更方便哦

基本SQL

去重

distinct

创建表

CREATE TABLE book
	(
		bid INT AUTO_INCREMENT PRIMARY KEY,
		bname VARCHAR(100) NOT NULL,
		bauthor VARCHAR(200) NOT NULL,
		bpublic DATETIME NOT NULL
	)

原表添加字段

ALTER TABLE book
ADD bphoto VARCHAR(20);

复制表 跨数据库

CREATE TABLE test.tbldict AS SELECT * FROM exam.tbldict WHERE 1<>1;

注意：复制之后需要手动添加主外键关系和自增列

删除表

DROP TABLE book

清空表数据

TRUNCATE TABLE book

添加主键

ALTER TABLE bookbf ADD CONSTRAINT pk_bid PRIMARY KEY(bid)

添加外键

ALTER TABLE citybf ADD CONSTRAINT fk_cpid FOREIGN KEY(cpid) PEFERENCES provice

查询

SELECT bid,bname,bauthor,bpublic FROM book

SELECT bid,bname,bauthor,bpublic FROM book WHERE bid=1001

统计

SELECT COUNT(bid) FROM book

添加

INSERT INTO book(bname,bauthor,bpublic) 
VALUES("救赎","不求人",new Timestamp(System.currentTimeMillis()))

删除

DELETE FROM book WHERE bid=1001

修改

UPDATE book SET bname="正南",bauthor="柯南" WHERE bid=1001

分页

SELECT bid,bname,bauthor,bpublic FROM book LIMIT 1,5

排序

SELECT bid,bname,bauthor,bpublic FROM book ORDER BY bpublic DESC

表数据备份 或 表的批量添加

综合：综合查询、排序、分页

SELECT bid,bname,bauthor,bpublic FROM book 
		WHERE bname='' AND bpublic >= 2000-01-01 LIMIT 0,5 ORDER BY bpublic ASC

批量处理
一次插入多个：
（1）手动插入多个值

INSERT INTO book 
		SELECT 202,'aa1','bb1','2000-12-12 12:00:00' UNION
		SELECT 203,'aa2','bb2','2000-12-12 12:00:00'

（2）创建和book表一样结构的bookbf表，不需要数据(备份结构，不要数据)
注意：bookbf备份前不存在，

CREATE TABLE bookbf AS SELECT bid,btitle,bauthor,bpublic FROM book WHERE 1<>1

（3）将book表中的数据批量追加到bookbf中，保证查询结构一直

INSERT INTO bookbf
		SELECT bid,btitle,bauthor,bpublic FROM book

一次删除多个

DELETE FROM book WHERE bid IN(1001,1002)

联表查询
多对一（推荐）

SELECT cid,cname,cpname,pid,pname FROM city 
LEFT JOIN provice ON cpid=cid WHERE cid=1001

一对多

SELECT pid,pname,cid,cname,cpid FROM provice 
LEFT JOIN city ON cid=cpid WHERE pid=0101

内联

SELECT pid,pname,cid,cname,cpid FROM provice 
INNER JOIN city ON cid=cpid WHERE pid=0101

Mybatis

DAO层：

public interface BookMapper {
    int count(@Param("cond")Map<String,Object> cond);
    List<BookVO> find(@Param("cond") Map<String,Object> cond,@Param("start") int start,@Param("limit") int limit);
    int insert(BookVO bookVO);
    int update(BookVO bookVO);
    int delete(int id);
    int deletes(@Param("ids") int[] ids);
    BookVO findById(int id);
}

映射（xml）：

<mapper namespace="com.uplooking.dao.BookMapper">.....</mapper>

查询

 <select id="find" resultType="BookVO">
        SELECT bid,btitle,bauthor,bpublic FROM book
 </select>

统计

<select id="count" resultType="int">
    SELECT COUNT(bid) FROM book
</select>

添加

<insert id="insert">
    INSERT INTO book(btitle,bauthor,bpublic) VALUES(#{btitle},#{bauthor},#{bpublic})
        <selectKey order="AFTER" resultType="int" keyProperty="bid">
            SELECT LAST_INSERT_ID()
        </selectKey>
</insert>

删除

<delete id="delete">
    DELETE FROM book WHERE bid=#{id}
</delete>

修改

 <update id="update">
        UPDATE book
            SET btitle=#{btitle},bauthor=#{bauthor},bpublic=#{bpublic}
	    WHERE bid = #{bid}
 </update>

分页

<select id="find" resultType="BookVO">
    SELECT bid,btitle,bauthor,bpublic FROM book
    LIMIT #{start},#{limit}
</select>

排序

<select id="find" resultType="BookVO">
    SELECT bid,btitle,bauthor,bpublic FROM book
    <if test="cond.sort!=null and cond.sort!=''">
        ORDER BY ${cond.sort}
        <if test="cond.order!=null and cond.order!=''">
            ${cond.order}
        </if>
    </if>
</select>

返回自增量的值

<selectKey order="AFTER" resultType="int" keyProperty="bid">
      SELECT LAST_INSERT_ID()
</selectKey>

批量处理
删除：

一次删除多个

<delete id="deletes">
        	DELETE FROM book WHERE bid IN
        	<foreach collection="ids" open="(" close=")" separator="," item="id">
            		#{id}
        	</foreach>
    	</delete>

联表查询：
多对一

		<resultMap id="CityMap" type="CityVO">
        	<result column="cid" property="cid"></result>
        	<result column="cname" property="cname"></result>
        	<result column="cpid" property="cpid"></result>
        	<association property="proviceVO" javaType="ProviceVO">
            		<result column="pid" property="pid"></result>
            		<result column="pname" property="pname"></result>
        	</association>
    	</resultMap>
    	<select id="listAll" resultMap="CityMap">
        	SELECT cid,cname,cpid,pid,pname FROM city LEFT JOIN provice ON cpid=pid
    	</select>

一对多

	<resultMap type="ProviceVO" id="proviceMap">
  		<id property="pid" column="pid"/>
  		<result property="pname" column="pname"/>
  		<collection property="cityVOs" ofType="CityVO">
  			<id property="cid" column="cid"/>
  			<result property="cname" column="cname"/>
  			<result property="cpid" column="cpid"/>
  		</collection>
  	</resultMap>

  <select id="findById" resultMap="proviceMap">
  	SELECT pid,pname,cid,cname,cpid FROM provice LEFT JOIN city ON pid=cpid WHERE pid=#{id}
  </select>
  <select id="listAll" resultMap="proviceMap">
  	SELECT pid,pname,cid,cname,cpid FROM provice LEFT JOIN city ON pid=cpid
  </select>

多表查询

	<select id="userMenu" resultType="int">
		SELECT COUNT(uruid) FROM tbluserrole
			INNER JOIN tblrole ON urrid = rid
			INNER JOIN tblrolemenu ON rid = rmrid
			INNER JOIN tblmenu ON rmmid = MID
				WHERE uruid = #{uid} AND mroute=#{route} AND mtoken = #{token}
	</select>

动态SQL：封装条件

 	<sql id="sqlcond">
        <where>
            <if test="cond.title!=null and cond.title!=''">
                AND btitle LIKE CONCAT('%',#{cond.title},'%')
            </if>
            <if test="cond.author!=null and cond.author!=''">
                AND bauthor LIKE CONCAT('%',#{cond.author},'%')
            </if>
            <if test="cond.begin!=null and cond.begin!=''">
                AND bpublic &gt;= #{cond.begin}
            </if>
            <if test="cond.end!=null and cond.end!=''">
                AND bpublic &lt;= #{cond.end}
            </if>
        </where>
    </sql>

综合：综合查询、排序、分页

<select id="find" resultType="BookVO">
    SELECT bid,btitle,bauthor,bpublic FROM book
    <include refid="sqlcond"></include>
    <if test="cond.sort!=null and cond.sort!=''">
        ORDER BY ${cond.sort}
        <if test="cond.order!=null and cond.order!=''">
            ${cond.order}
        </if>
    </if>
    LIMIT #{start},#{limit}
</select>