mysql多表连接

一、数据库

数据库名都是小写（mysql忽视大小写）

还没安全的命令，改数据库名称（表备份，删除，新建）

二、表，视图，字段

null无效值，empty string用于字符：空值

数值默认值为0（null为0），varchar默认长度为255，char的最大长度为255

三、字段的数据类型和字段的约束

id int primary key：id字段自增，则id不能为0。如果记录中有0则不能设置主键。

四、多表连接

进行多表连接查询:

两张表:内连接，外连接(左连接，右连接)

 内连接是取两表间键值相等的记录，左连接是取表一中的全部记录，右连接是取表二中的全部记录

  SELECT t1.cn1,t2.cn1 FROM table_name1 AS t1 INNER JOIN table_name2 AS t2 ON[WHERE] t1.foreignkey=t2.primarykey;

  SELECT t1.cn1,t2.cn1 FROM table_name1 AS t1 LEFT OUTER JOIN table_name2 AS t2 ON t1.foreignkey=t2.primarykey;

  SELECT t1.cn1,t2.cn1 FROM table_name1 AS t1 RIGHT OUTER JOIN table_name2 AS t2 ON t1.foreignkey=t2.primarykey;

三张表或三组以上:进行嵌套

  SELECT t1.cn1,t2.cn1 t3.cn1 ((FROM table_name1 AS t1 INNER JOIN table_name2 AS t2 ON t1.foreignkey=t2.primarykey) INNER JOIN table_name3 AS t3 ON t1[t2].foreignkey=t3.primarykey

 

表t1                       表t2

SELECT t1.id AS "t1",t2.id AS "t2" FROM t1,t2 ORDER BY t1.id;//表t1中的每一条和t2中的所有记录连接

SELECT t1.id AS "1",t2.id AS "2" FROM t1,t2 WHERE t1.id=t2.id;

SELECT t1.id AS "1",t2.id AS "2" FROM t1 INNER JOIN t2 WHERE t1.id=t2.id;//在所有记录中筛选

SELECT t1.id AS "1",t2.id AS "2" FROM t1 LEFT OUTER JOIN t2 ON t1.id=t2.id;

//在所有记录中，以t1一条一条的筛选出满足条件的

SELECT t1.id AS "1",t2.id AS "2" FROM t1 RIGHT OUTER JOIN t2 ON t1.id=t2.id;

//在所有记录中，以t2一条一条的筛选出满足条件的

SELECT t1.id AS "1",t2.id AS "2" FROM t1,t2 WHERE t1.id=1;

SELECT t1.id AS "1",t2.id AS "2" FROM t1 LEFT OUTER JOIN t2 ON t1.id=t2.id WHERE t1.id=1;

 

1，两表连接后的记录可以出现：在主键列相同的记录

2，左连接，右连接中ON是必须的

3，先查到结果后，再进行多表连接

 

实例：

SELECT type AS "流程类型", saleName AS "渠道名称",saleNum AS "渠道编码", result2.operTime AS "时间", changeApplicant AS "申请人", state as "流程状态(2流程已走完，其他未结束)" FROM (

SELECT * FROM channel_basic WHERE EXISTS (SELECT * FROM workflow_pid WHERE  channel_basic.id = workflow_pid.businessId AND operTime>="2016-06-00 00:00:00" AND type IN("渠道申请流程","渠道变更流程","渠道关闭流程"))

) AS result1 

INNER JOIN (

SELECT * FROM workflow_pid WHERE operTime>="2016-06-00 00:00:00" AND type IN("渠道申请流程","渠道变更流程","渠道关闭流程")

)AS result2 

WHERE result1.id=result2.businessId ORDER BY type,result2.operTime;