mysql联接查询

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 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,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

搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]‍，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：mysql联接查询

txt文件导入mysql

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

Hi，您需要填写昵称和邮箱！