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
