1、开篇
之前上一篇的随笔基本上是单表的查询,也是mysql查询的一个基本。接下来我们要看看两个表以上的查询如何得到我们想要的结果。
在学习的过程中我们一起进步,成长。有什么写的不对的还望可以指出。
2、查询
(1)多表之间的查询(笛卡尔积) 100* 100 = 10000 查询的数据量可能非常大
SELECT pj_appoint_order.user_name,pj_user_account.account_name FROM pj_appoint_order,pj_user_account;
给表名取别名
SELECT pa.user_name,pu.account_name FROM pj_appoint_order pa,pj_user_account pu;
(2)关联查询
隐示内连接查询。
(Sql92)
SELECT pa.user_name,pu.account_name FROM pj_appoint_order pa,pj_user_account pu WHERE pa.id = pu.pid;
内连接查询。
(Sql99)
SELECT pa.user_name,pu.account_name FROM pj_appoint_order pa INNER JOIN pj_user_account pu ON pa.id = pu.pid;
left join on
如果我写 left join on 就以左表为主
right join on
如果我写 right join on 就以右表为主
例如:SELECT
t1.id id,
t1.order_id AS designOrderId,
t1.manager_id,
t1.project_name projectName,
t1.user_name userName,
t1.user_phone userPhone,
t1.work_price workPrice,
t1.type_id typeId,
t1.create_time createTime,
t1.update_time updateTime,
t1.project_start_time startTime,
t1.project_end_time endTime,
t1.status status,
t3.meet_time meetTime,
t3.meet_type meetType,
t3.meet_content meetContent,
t5.total totalMain,
t6.total totalSub
FROM pj_project_order t1 LEFT JOIN pj_design_order t2 ON t1.order_id = t2.id
LEFT JOIN pj_order_comm_record t3 ON t2.id = t3.order_id
LEFT JOIN pj_order_design_task t4 ON t2.id = t4.order_id
LEFT JOIN pj_order_design_main_material t5 ON t4 .id = t5.task_id
LEFT JOIN pj_order_design_sub_material t6 ON t4.id = t6.task_id
WHERE 1=1
AND t1.manager_id = #{manager_id}
AND
t1.project_start_time IS NOT NULL
AND
t1.project_end_time IS NULL
(3)子查询 -我们以一条查询结果 做为查询条件这叫做子查询 --------------子查询的效率不高
SELECT * FROM person WHERE id = 1
SELECT pid FROM car WHERE carname='hanma'
SELECT * FROM person WHERE id = ( SELECT pid FROM car WHERE carname='hanma' );
(4)UNION 结合两个查询集
SELECT * FROM pj_appoint_order WHERE id = 1093 UNION SELECT * FROM pj_appoint_order WHERE id = 1094