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.

txt文件导入mysql

mysql高速导入导出大容量TXT文本

mysql> load data infile "student_bak.txt" into table student;
Query OK, 9 rows affected, 6 warnings (0.02 sec)            
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