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 | name?? | name?????? |
+----+--------+------------+
|? 1 | Woson? | ClassTwo?? |
|? 2 | Tom??? | ClassOne?? |
|? 3 | Tom??? | ClassTwo?? |
|? 4 | Simon? | ClassThree |
|? 5 | Leo??? | ClassTwo?? |
|? 6 | Leo??? | ClassTwo?? |
|? 7 | Edon?? | NULL?????? |
|? 8 | Yonson | ClassTwo?? |
+----+--------+------------+
右外连接：
select s.id,s.name,c.name from students as s right? (outer) join classes as c on 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 |
| NULL | NULL?? | ClassFour? |
+------+--------+------------+
全外连接：
两个表中class_id的并集
1. select s.id,s.name,c.name from students as s full? (outer) join classes as c on s.class_id=c.id? ;
mysql5.0.x不支持全外连接
2. select s.id,s.name,c.name from students as s left? (outer) join classes as c on s.class_id=c.id? union? select s.id,s.name,c.name from students as s right? (outer) join classes as c on s.class_id=c.id;
+------+--------+------------+
| id?? | name?? | name?????? |
+------+--------+------------+
|??? 1 | Woson? | ClassTwo?? |
|??? 2 | Tom??? | ClassOne?? |
|??? 3 | Tom??? | ClassTwo?? |
|??? 4 | Simon? | ClassThree |
|??? 5 | Leo??? | ClassTwo?? |
|??? 6 | Leo??? | ClassTwo?? |
|??? 7 | Edon?? | NULL?????? |
|??? 8 | Yonson | ClassTwo?? |
| NULL | NULL?? | ClassFour? |
+------+--------+------------+
mysql> select c.name,sum(s.score) as total_score from students as s inner join classes as c on s.class_id=c.id group by s.class_id ;
+------------+-------------+???????????????????
| name?????? | total_score |???????????????????
+------------+-------------+???????????????????
| ClassOne?? |????????? 88 |???????????????????
| ClassTwo?? |???????? 397 |???????????????????
| ClassThree |????????? 93 |???????????????????
+------------+-------------+???????????????????
3 rows in set (0.00 sec)???????????????????????
mysql> select c.name,sum(s.score) as total from students as s inner join classes as c on s.class_id=c.id group by s.class_id having total < 300;
+------------+-------+
| name?????? | total |
+------------+-------+
| ClassOne?? |??? 88 |
| ClassThree |??? 93 |
+------------+-------+
mysql> select c.name,sum(s.score) as total from students as s inner join classes asc on s.class_id=c.id group by s.class_id having total < 300 order by total desc limit 1;
+------------+-------+
| name?????? | total |
+------------+-------+
| ClassThree |??? 93 |
+------------+-------+
mysql> select c.name,avg(s.score) as average from students as s inner join classes as c on s.class_id=c.id group by s.class_id ;???????????????????????????????????????
+------------+---------+???????????????????????????????????????????????????????????
| name?????? | average |???????????????????????????????????????????????????????????
+------------+---------+???????????????????????????????????????????????????????????
| ClassOne?? | 88.0000 |???????????????????????????????????????????????????????????
| ClassTwo?? | 79.4000 |???????????????????????????????????????????????????????????
| ClassThree | 93.0000 |???????????????????????????????????????????????????????????
+------------+---------+???????????????????????????????????????????????????????????
3 rows in set (0.00 sec)???????????????????????????????????????????????????????????
mysql> select c.name,max(s.score) as max_score from students as s inner join classes as c on s.class_id=c.id group by s.class_id ;
+------------+-----------+?????????????????????????????????????????????????????????
| name?????? | max_score |?????????????????????????????????????????????????????????
+------------+-----------+
| ClassOne?? |??????? 88 |
| ClassTwo?? |??????? 99 |
| ClassThree |??????? 93 |
+------------+-----------+
3 rows in set (0.00 sec)
mysql> select c.name,min(s.score) as min_score from students as s inner join classes as c on s.class_id=c.id group by s.class_id ;
+------------+-----------+
| name?????? | min_score |
+------------+-----------+
| ClassOne?? |??????? 88 |
| ClassTwo?? |??????? 55 |
| ClassThree |??????? 93 |
+------------+-----------+
---------------------------------------------------------------------------
mysql> select * from users;
+--------+????????????????
| qq_no? |????????????????
+--------+????????????????
| 123456 |????????????????
| 123457 |????????????????
| 123458 |????????????????
| 123459 |????????????????
| 123460 |
+--------+
mysql> select * from users2;
+--------+
| qq_no? |
+--------+
| 123458 |
| 123459 |
| 123460 |
| 123461 |
| 123462 |
| 123463 |
| 123464 |
+--------+
users - users2:
mysql> select qq_no from users where qq_no not in (select qq_no from users2);+--------+??????????????????????????????????????????????????????????????????
| qq_no? |??????????????????????????????????????????????????????????????????
+--------+??????????????????????????????????????????????????????????????????
| 123456 |??????????????????????????????????????????????????????????????????
| 123457 |??????????????????????????????????????????????????????????????????
+--------+
mysql> select users.qq_no from users left join users2 on users.qq_no=users2.qq_no where users2.qq_no is null;
+--------+
| qq_no? |
+--------+
| 123456 |
| 123457 |
+--------+
users2 - users:
mysql> select qq_no from users2 where qq_no not in (select qq_no from users);
+--------+??????????????????????????????????????????????????????????????????
| qq_no? |??????????????????????????????????????????????????????????????????
+--------+??????????????????????????????????????????????????????????????????
| 123461 |??????????????????????????????????????????????????????????????????
| 123462 |??????????????????????????????????????????????????????????????????
| 123463 |??????????????????????????????????????????????????????????????????
| 123464 |??????????????????????????????????????????????????????????????????
+--------+
mysql> select users2.qq_no from users2 left join users on users2.qq_no=users.qq_no where users.qq_no is null;??????????????????????????????????????????????????????????
+--------+?????????????????????????????????????????????????????????????????????????
| qq_no? |?????????????????????????????????????????????????????????????????????????
+--------+
| 123461 |
| 123462 |
| 123463 |
| 123464 |
+--------+
----------------------------------------------------------------
mysql> select * from student;??????????????????????????????????????????????????????
+----+--------+-------+----------+
| 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 name,score,class_id from student into outfile "/home/simon/student_bak.txt" lines terminated by "\r\n";
ERROR 1 (HY000): Can't create/write to file '/home/simon/student_bak.txt' (Errcode:13)
???????????? So I read again the documentation of MySQL, and I found this:
The SELECT ... INTO OUTFILE? 'file_name'? form? of?? SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax.
mysql> select name,score,class_id from student into outfile "student_bak.txt" lines?? terminated by "\r\n";
Query OK, 8 rows affected (0.00 sec)
(LINUX) By default, if you don't specify absolute path for OUTFILE in? select ... into OUTFILE "..."
INSTALL_DIR = "/usr/local/mysql"
It creates the file in "INSTALL_DIR/data/<database_name>"
Make sure current user has (NOT) a write permission in that directory.
Records: 9? Deleted: 0? Skipped: 0? Warnings: 5
如何在mysql从多个表中组合字段然后插入到一个新表中，通过一条sql语句实现。具体情形是：有三张表a、b、c，现在需要从表b和表c中分别查几个字段的值插入到表a中对应的字段。对于这种情况，我们可以使用如下的语句来实现：
?INSERT INTO db1_name(field1,field2) SELECT field1,field2 FROM db2_name
????? 当然，上面的语句比较适合两个表的数据互插，如果多个表就不适应了。对于多个表，我们可以先将需要查询的字段join起来，然后组成一个视图后再select from就可以了：
?INSERT INTO a(field1,field2) SELECT * FROM(SELECT f1,f2 FROM b JOIN c) AS tb
????? 其中f1是表b的字段，f2是表c的字段，通过join查询就将分别来自表b和表c的字段进行了组合，然后再通过select嵌套查询插入到表a中，这样就满足了我们这个场景了，如果需要不止2个表，那么可以多个join的形式来组合字段。需要注意的是嵌套查询部分最后一定要有设置表别名，如下：
SELECT * FROM(SELECT f1,f2 FROM b JOIN c) AS tb
????? 即最后的as tb是必须的（当然tb这个名称可以随意取），即指定一个别名，否则在mysql中会报如下错误：
ERROR 1248 (42000): Every derived TABLE must have its own alias