日期:2014-05-16  浏览次数:20703 次

mysql连接查询

Sql语句中where,group by,order by及limit的顺序
where xxx,group by xxx,order by xxx,limit xxx



mysql> select * from students;
+----+--------+-------+----------+
| id | name?? | score | class_id |
+----+--------+-------+----------+
|? 1 | Woson? |??? 90 |??????? 2 |
|? 2 | Tom??? |??? 88 |??????? 1 |
|? 3 | Tom??? |??? 77 |??????? 2 |
|? 4 | Simon? |??? 93 |??????? 3 |
|? 5 | Leo??? |??? 99 |??????? 2 |
|? 6 | Leo??? |??? 55 |??????? 2 |
|? 7 | Edon?? |??? 84 |??????? 0 |
|? 8 | Yonson |??? 76 |??????? 2 |
+----+--------+-------+----------+



mysql> select * from classes;
+----+------------+
| id | name?????? |
+----+------------+
|? 1 | ClassOne?? |
|? 2 | ClassTwo?? |
|? 3 | ClassThree |
|? 4 | ClassFour? |
+----+------------+



students自连接:


1. select distinct a.* from students as a inner join students as b on a.id<>b.id and a.name = b.name ;


2. select distinct a.* from students? a , students b where a.id<>b.id and a.name = b.name ;



+----+------+-------+----------+
| id | name | score | class_id |
+----+------+-------+----------+
|? 3 | Tom? |??? 77 |??????? 2 |
|? 2 | Tom? |??? 88 |??????? 1 |
|? 6 | Leo? |??? 55 |??????? 2 |
|? 5 | Leo? |??? 99 |??????? 2 |
+----+------+-------+----------+



内连接:

两个表中class_id的交集


1. select s.id,s.name,c.name from students as s inner join classes as c on s.class_id=c.id? ;


2. select s.id,s.name,c.name from students? s , classes? c where s.class_id=c.id? ;




+----+--------+------------+
| id | name?? | name?????? |
+----+--------+------------+
|? 2 | Tom??? | ClassOne?? |
|? 1 | Woson? | ClassTwo?? |
|? 3 | Tom??? | ClassTwo?? |
|? 5 | Leo??? | ClassTwo?? |
|? 6 | Leo??? | ClassTwo?? |
|? 8 | Yonson | ClassTwo?? |
|? 4 | Simon? | ClassThree |
+----+--------+------------+




左外连接:


select s.id,s.name,c.name from students as s left? (outer) join classes as c on s.class_id=c.id? ;


+----+--------+------------+
| id | na