`

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














分享到:
评论

相关推荐

    07mysql连接查询

    这是我自己学习mysql时的学习笔记,每一个知识点都是自己动手写的,涵盖了mysql中的几乎全部的基础知识点,查询,子查询,分组,排序,常用函数,多表连接,视图,变量,存储过程,函数,分支结构,循环结构等等

    第12周-第08章节-Python3.5-mysql 连接查询.mp4

    第12周-第08章节-Python3.5-mysql 连接查询.mp4

    08mysql连接查询(sql99)

    这是我自己学习mysql时的学习笔记,每一个知识点都是自己动手写的,涵盖了mysql中的几乎全部的基础知识点,查询,子查询,分组,排序,常用函数,多表连接,视图,变量,存储过程,函数,分支结构,循环结构等等

    经典mysql连接查询例题

    MySQL连接查询是数据库操作中的重要技能,特别是在处理涉及多个表的数据时。连接查询允许我们将不同表中的相关数据合并成一个单一的结果集,这对于数据分析、报表生成以及数据展示都非常有用。在本例中,我们将通过...

    mysql连接查询、联合查询、子查询原理与用法实例详解

    本文实例讲述了mysql连接查询、联合查询、子查询原理与用法。分享给大家供大家参考,具体如下: 本文内容: 连接查询 联合查询 子查询 from子查询 where子查询 exists子查询 首发日期:2018-04-11 连接查询...

    二、MySQL连接查询学习笔记(多表连接查询:内连接,外连接,交叉连接详解)

    MySQL连接查询(多表连接查询:内连接,外连接,交叉连接详解) 6:多表连接查询 笛卡尔乘积:如果连接条件省略或无效则会出现 解决办法:添加上连接条件 连接查询的分类: 1.按年代分类: 1)sql 92标准:仅仅...

    mysql连接查询.md

    mysql基础学习

    MySQL连接工具 绿色版

    MySQL连接工具是数据库管理员和开发人员用来与MySQL服务器交互的重要应用程序。绿色版通常指的是免安装、便携式的版本,可以直接运行而无需在计算机上进行正式的安装过程,这对于需要在不同设备间移动工作或者不想...

    mysql连接驱动包

    MySQL连接驱动包是Java应用程序与MySQL数据库进行交互的关键组件,它允许Java开发者通过JDBC(Java Database Connectivity)接口执行SQL语句,管理数据库事务,以及处理数据库结果集。本压缩包包含了两个不同版本的...

    Mysql 连接资源Jar

    是MySQL连接器的类名,通过`Class.forName()`方法加载。 3. 创建数据库连接: ```java String url = "jdbc:mysql://localhost:3306/mydatabase"; String user = "username"; String password = "password"; ...

    mysql连接驱动jar包

    MySQL连接驱动jar包是Java应用程序与MySQL数据库进行交互的关键组件。在Java中,我们使用JDBC(Java Database Connectivity)API来实现数据库操作,而MySQL连接驱动则是JDBC的一部分,它负责建立Java应用程序与MySQL...

    MySQL 连接查询的原理和应用

    了解MySQL连接查询之前我们先来理解下笛卡尔积的原理。 数据准备 依旧使用上节的表数据(包含classes 班级表和students 学生表): mysql&gt; select * from classes; +---------+-----------+ | classid | classname ...

    nodejs封装好的mysql数据库模块,带mysql连接池以及百万测试数据

    本模块基于Node.js实现了对MySQL数据库的封装,利用了mysql连接池来优化性能,并且包含了用于测试的百万级数据,确保了在大数据量场景下的稳定性和效率。以下将详细介绍该模块的关键知识点: 1. **Node.js与MySQL**...

    OPC服务器软件KEPServer实现与MySQL连接

    OPC 服务器软件 KEPServer 实现与 MySQL 连接 OPC 服务器软件 KEPServer 是一种工业自动化领域中的数据交换协议,以实现实时数据交换和远程监控。通过 KEPServer 软件,可以实现与 MySQL 数据库之间的数据交互,...

    mysql连接数据库。。jar架包

    这个"mysql连接数据库..jar架包"正是这样的一个驱动包,它包含了MySQL JDBC(Java Database Connectivity)驱动,使得Java程序能够通过标准的JDBC API与MySQL服务器进行通信。 JDBC是Java语言中用来规范客户端程序...

    VB6.0连接MySQL数据库

    由于VB6.0本身并不直接支持MySQL,所以一般需要借助于MySQL提供的ODBC驱动或者第三方组件,例如MySQL Connector/ODBC,来实现连接。 1. 安装MySQL Connector/ODBC驱动 要在系统中安装MySQL的ODBC驱动程序,用户需要...

    MYSQL连接测试程序

    MySQL连接测试程序是一种基于VC(Visual C++)的软件开发工具,用于通过API接口与MySQL数据库进行交互。在本文中,我们将深入探讨如何利用API方式访问MySQL数据库,并提供查询示例,帮助你理解和掌握相关技术。 ...

    mysql8和mysql5的连接驱动jar包

    本篇文章将详细讨论MySQL8与MySQL5在连接驱动jar包方面的差异,以及如何使用这些驱动来连接Java应用程序。 首先,让我们关注两个不同版本的驱动包:“mysql-connector-java-5.1.30.jar”和“mysql-connector-java-...

    MySQL连接工具.zip

    为了方便用户管理和操作MySQL数据库,出现了各种MySQL连接工具,本压缩包提供的“MySQL连接工具”就是这样一款实用的应用。 首先,MySQL连接工具的主要功能包括: 1. **数据库连接**:它允许用户通过输入服务器...

Global site tag (gtag.js) - Google Analytics