mysql以及在mybatis中的一些应用

mysql链表

1.mysql 连表查询

	1.1  inner jion on  或者 默认的 两边都有

	1.2  left outter jion on  左边为主,不为null

	1.3 right outter jion on  右边为主，不为null

2.mysql的子查询


	2.1. where型子查询：把内层查询的结果作为外层查询的比较条件

		SELECT goods_id,goods_name,shop_price FROM goods WHERE goods_id = (SELECT MAX(goods_id) FROM goods);

		SELECT  playerno,town,sex
		    -> FROM  PLAYERS
		    -> WHERE (town,sex) = ((SELECT town FROM PLAYERS WHERE playerno=7),
		    ->                     (SELECT sex FROM PLAYERS WHERE playerno=44));

		select *
		    -> from COMMITTEE_MEMBERS
		    -> where (begin_date,end_date) in
		    -> (
		    ->   select begin_date,end_date
		    ->   from COMMITTEE_MEMBERS
		    ->   where position='Secretary'
		    -> );

		select  a.app_name, a.zanpkg_version, a.zone, a.created_at 
				from activity a, ( select MAX(id) AS mid 
				    from  activity 
				    where zone = 'qa' AND status = 2 AND zanpkg_version != ''          
				    AND namespace = 'qa' 
				    group by app_name) b
				where  a.id = b.mid limit 500;
             
             

        2.2 from型子查询：把内层的查询结果当成临时表，供外层sql再次查询。查询结果集可以当成表看待。临时表要使用一个别名。

	　　 SELECT goods_id,goods_name,cat_id,shop_price FROM(SELECT goods_id,goods_name,cat_id,shop_price FROM goods ORDER BY cat_id ASC,goods_id DESC) AS tmp GROUP BY cat_id;

	2.3  exists型子查询：把外层sql的结果，拿到内层sql去测试，如果内层的sql成立，则该行取出。内层查询是exists后的查询。

		SELECT c.cat_id,c.cat_name FROM category c WHERE EXISTS (SELECT 1 FROM goods g WHERE g.cat_id = c.cat_id);

	2.4  any, in 子查询
     
	SELECT c.cat_id,c.cat_name FROM category c WHERE c.cat_id IN (SELECT g.cat_id FROM goods g GROUP BY g.cat_id)


        2.5 group by

    	这就是我们需要注意的一点，如果在返回集字段中，这些字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。

    	SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region

        2.6 having

    	 having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。having子句限制的是组，而不是行。where子句中不能使用聚集函数，而having子句中可以。



3.mybaits中的实践


	3.1  一对一

		<select id="queryById" parameterType="int" resultMap="userMap">
    		SELECT u.user_name,u.age,c.card_id,c.card_num,c.address
   				 FROM tb_user u,tb_card c
    		WHERE u.card_id=c.card_id
    		AND
    		u.user_id=#{userId}
		</select>

		<resultMap type="com.ck.smm.entity.User" id="userMap">
		   <result property="userName" column="user_name"/>
		   <result property="age" column="age"/>
		   <association property="card" javaType="com.ck.smm.entity.Card">
		      <id property="cardId" column="card_id"/>
		      <result property="cardNum" column="card_num"/>
		      <result property="address" column="address"/>
		    </association>
		</resultMap> 

    3.2 一对多
        
                <resultMap id="resultDeptAndEmp" oftype="com.yyc.platform.user.model.Dept">
                    <id column="d_id" property="id"/>
                    <result column="dept_name" property="deptName"/>
                    <collection property="emps" ofType="com.yyc.platform.user.model.Emp">
                        <id column="e_id" property="id"/>
                        <result column="emp_name" property="empName"/>
                        <result column="email" property="email"/>
                        <result column="sex" property="sex"/>
                    </collection>
                </resultMap>

mybatis注入：

1.用# 不用 $$是拼接不会转义

2.order by

3.in