Mysql中的Limit和左连接、右连接

limit

select * from user limit num1,num2   查询第num1条到第num2条数据
select * from user limit num2        当num1为0或者不写时 默认查前num2条数据

左连接、右连接

左连接：在 LEFT JOIN 左边的表里面数据全被全部查出来，右边的数据只会查出符合ON后面的符合条件的数据，不符合的会用NULL代替。

select student.id,student.name,student.t_id,teacher.id,teachaer.name from student left join  
        teacher on student.t_id = teacher.id
//这种查询方式查询出来的结果用json形式是每个学生都是一个对象，每个学生有一个老师

关键字： association 
mapper.xml：

 <select id="getStudent1" resultMap="studentTeacher1">
        select student.id,student.Sname,student.sex,student.age,student.t_id,teacher.Tname from student left JOIN teacher on student.t_id = teacher.id

    </select>
    <resultMap id="studentTeacher1" type="com.qcby.entity.Student">
        <result property="id" column="id"/>
        <result property="Sname" column="Sname"/>
        <result property="sex" column="sex"/>
        <result property="age" column="age"/>
        <result property="t_id" column="t_id"/>
        <association property="teacher" javaType="com.qcby.entity.Teacher">
            <result property="Tname" column="Tname"/>
        </association>
    </resultMap>

右连接：与 LEFT JOIN 正好相反，RIGHT JOIN右边的数据会会全部查出来，左边只会查出ON后面符合条件的数据，不符合的会用NULL代替。

select student.id,student.name,student.t_id,teacher.id,teachaer.name from student right                 
         join teacher on student.t_id = teacher.id
这种查询方式查询出来的结果用json形式是每个老师都是一个对象，每个老师有多个学生

关键字： collection 
mapper.xml：

<select id="findTeacherAndStudent" resultMap="reuslt1">
        SELECT teacher.id,teacher.Tname,student.Sname,student.age,student.sex,student.t_id FROM teacher right JOIN student on  teacher.id = student.t_id
    </select>
    <resultMap id="reuslt1" type="com.qcby.entity.Teacher">
        <result property="id" column="id"/>
        <result property="Tname" column="Tname"/>
        <collection property="students" ofType="com.qcby.entity.Student">
            <result property="t_id" column="t_id"/>
            <result property="Sname" column="Sname"/>
            <result property="age" column="age"/>
            <result property="sex" column="sex"/>
        </collection>
    </resultMap>

内连接：相当于左连接与右连接的合并，去掉所有含NULL的数据行，剩下的就是查询出来的数据了。其实就是两边的表都必须满足条件。

select student.id,student.name,student.t_id,teacher.id,teachaer.name from student right                 
         join teacher on student.t_id = teacher.id