MySql笔记

1.多表查询并统计多表的总数

SELECT
(SELECT count(1) FROM table_1 WHERE a_state='01')
+
(SELECT count(1) FROM table_2 WHERE b_state='01') 
from 
dual

其中 dual是一个虚拟表，用来构成select的语法规则

2.多表关联子查询以及统计其中某个字段的最小值

SELECT
	    app_id AS aId,
	    app_name AS aName,
	    app_type AS aType,
	    app_number AS aNumber
        FROM
	    app_info
        WHERE
	    app_state = '01'
        UNION ALL
	    SELECT
		b.bala_id AS aId,
		b.bala_name AS aName,
		'QYB' AS aType,
		(
			SELECT
				min(e.app_number)
			FROM
				app_info e,
				bala_info g,
				app_bala_rel r
			WHERE
				e.app_id = r.app_id
			AND g.bala_id = r.bala_id
			AND g.bala_id = b.bala_id
		) AS aNumber
	    FROM
		bala_info b
	    WHERE
		b.bala_state = '01'

这条sql中总共三张表app_info 、bala_info 、app_bala_rel ，
关系：

1. app_bala_rel 为关联表，
2. bala_info和app_info是一对多的关系，

需求： 现在我们要查询到一个bala_info中所关联的app_info中最小的app数量，即app_number，那么就考虑到了多表关联子查询的方法
整个sql拆分下：
1.查询单个app的信息

		SELECT
	    app_id AS aId,
	    app_name AS aName,
	    app_type AS aType,
	    app_number AS aNumber
        FROM
	    app_info
        WHERE
	    app_state = '01'

2.查询一个巴拉下面关联的所有app信息

	    SELECT
		e.bala_id AS aId,
		e.bala_name AS aName,
		'QYB' AS aType,
		e.app_number AS aNumber
	    FROM
			bala_info e,
			bala_info g,
			app_bala_rel r
	    WHERE
	    	e.app_id = r.app_id
			AND 
			g.bala_id = r.bala_id
			AND
			g.bala_state = '01'

3.取最小的app数量（子查询）

			SELECT
				min(e.app_number)
			FROM
				app_info e,
				bala_info g,
				app_bala_rel r
			WHERE
				e.app_id = r.app_id
			AND g.bala_id = r.bala_id
			AND g.bala_state = '01'

4.用union关联起来，就是刚开始时候的sql了

3. 用foreach便利list集合

<foreach collection="idList" item="aId" index="index" 
	open="(" separator="," close=")">
            #{aId}
</foreach>