双表的增删改查-sql语句

<?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.lzl.dao.DoctorDao">

<select id="findAll" resultType="doctor">

select d.*,c.pname as sname, cc.pname as cname,
ccc.pname as xname, de.kname as kname,
DATEDIFF(d.birthday,d.rzdate) as gl, GROUP_CONCAT(s.sname) as scname
from doctor d left JOIN city c on d.province=c.id
LEFT JOIN city cc ON d.town=cc.id
LEFT JOIN city ccc ON d.county=ccc.id
LEFT JOIN department de on d.desk=de.id
LEFT JOIN guanxi g on d.adept=g.mid
left JOIN shanchang s on g.fid=s.id

<where>
1=1
<!-- 姓名模糊查询 -->
<if test="name!='' and name!=null">
AND d.name like '%${name}%'
</if>
<!-- 性别下拉框查询 -->
<if test="grader!=0">
AND d.grader =#{grader}
</if>
<!-- 省市区三级联动查询 -->
<if test="province!=0">
AND d.province = #{province}
</if>
<if test="town!=0">
AND d.town = #{province}
</if>
<if test="county!=0">
AND d.county = #{county}
</if>
<!-- 根据科室id查找 -->
<if test="desk!=0">
AND d.desk = #{desk}
</if>
<!-- 年龄从小到大查询 -->
<if test="MinAge!=0">
AND d.age >= #{MinAge}
</if>
<if test="MaxAge!=0">
AND d.age &lt;= #{MaxAge}
</if>
</where>
GROUP BY d.id
</select>
<select id="findProvince" resultType="doctor">

SELECT id,pname as sname from city WHERE pid=0

</select>
<select id="findDepart" resultType="doctor">

select * from department

</select>
<select id="findTown" resultType="doctor" >
SELECT id,pname as sname from city WHERE pid= #{id}
</select>
<select id="findById" resultType="doctor" >
select d.*,c.pname as sname,
cc.pname as cname,
ccc.pname as xname,
de.kname as kname,DATEDIFF(d.birthday,d.rzdate) as gl,
GROUP_CONCAT(s.sname) as scname
from doctor d left JOIN city c on d.province=c.id
LEFT JOIN city cc ON d.town=cc.id
LEFT JOIN city ccc ON d.county=ccc.id
LEFT JOIN department de on d.desk=de.id
LEFT JOIN guanxi g on d.adept=g.mid
left JOIN shanchang s on g.fid=s.id where d.id=#{id}
</select>
<select id="findByShanChang" resultType="doctor" >
select * from shanchang
</select>
<delete id="deleteGuanXi">
delete from guanxi where mid=#{adept}
</delete>
<insert id="addGuanXi">
insert into guanxi values(#{adept},#{i})
</insert>
<update id="update">
update doctor set name=#{name},`rzdate`=#{rzdate},
birthday=#{birthday},grader=#{grader},province=#{province},
town=#{town},county=#{county},desk=#{desk},adept=#{adept},
touxiang=#{touxiang} where id=#{id}
</update>
<insert id="add" useGeneratedKeys="true" keyColumn="id" keyProperty="id" >
insert into doctor(`name`,`rzdate`,`birthday`,`grader`,`province`,`town`,`county`,`desk`,`touxiang`,`sfz`,`telephone`,`age`) values(#{name},#{rzdate},#{birthday},#{grader}
,#{province},#{town},#{county},#{desk},#{touxiang},#{sfz},#{telephone},DATEDIFF(#{birthday},#{rzdate}))
</insert>
<update id="addAdept">
update doctor set adept=#{id} where id=#{id}
</update>
</mapper>