MYSQL连接查询,虽然觉得已经是很普遍的用法,但还是记录下来,以供他人查询及方便自己复制粘贴:)
字段值 | 属性 | 长度 |
id | varchar | 10 |
birthday | char | 8 |
wangwu | 19890101 |
zhangsan | 19870501 |
lisi | 19910101 |
字段值 | 属性 | 长度 |
auther | varchar | 10 |
book_name | varcahr | 20 |
end | int | 5 |
zhangsan | actou | 100 |
zhangsan | pitcut | 5 |
zhangsan | tushi | 89 |
wangwu | php | 1000 |
wangwu | mysql | 1000 |
zhangsan | java | 177 |
select * from table1 left join table2 on table1.id=table2.auther where 1<2
id | birthday | auther | book_name | end |
wangwu | 19890101 | wangwu | php | 1000 |
wangwu | 19890101 | wangwu | mysql | 1000 |
zhangsan | 19870501 | zhangsan | actou | 100 |
zhangsan | 19870501 | zhangsan | pitcut | 5 |
zhangsan | 19870501 | zhangsan | tushi | 89 |
zhangsan | 19870501 | zhangsan | java | 177 |
lisi | 19910101 | null | null | null |
*left join 就是从左边的表记录中对应在右边中查询,也称呼为左外连接。其结果就是左表的值U(左表的值∩右表的值)
select * from table1,table2 where table1.id=table2.auther
id | birthday | auther | book_name | end |
wangwu | 19890101 | wangwu | php | 1000 |
wangwu | 19890101 | wangwu | mysql | 1000 |
zhangsan | 19870501 | zhangsan | actou | 100 |
zhangsan | 19870501 | zhangsan | pitcut | 5 |
zhangsan | 19870501 | zhangsan | tushi | 89 |
zhangsan | 19870501 | zhangsan | java | 177 |
*上面这种连接也称为内连接,其结果为两个表的交集。
select * from table1 natual left join table2
*称为自然连接。一般较少使用。(自然连接为联合两个表中具有相同列名、相同类型和相同值的行)
但我测试发现结果是table1*table2.
查询设置条件
where if(length(month)=1,concat(year,"0",month),concat(year,month))>='200508' and if(length(month)=1,concat(year,"0",month),concat(year,month))<='200603'
通过length判断字段长度来决定字段处理的原则