- 浏览: 172365 次
- 性别:
- 来自: 上海
-
文章分类
- 全部博客 (174)
- rails (25)
- js (15)
- ruby (30)
- webserver (5)
- mysql (13)
- security (5)
- thinking (5)
- common sense (2)
- linux (18)
- android (26)
- web browser (1)
- config and deploy (1)
- mac (5)
- css (2)
- db (8)
- version manager (1)
- editor (1)
- job (1)
- OOA (1)
- php (1)
- apache (2)
- mongrel (1)
- Mongodb (1)
- facebook (1)
- 架构 (1)
- 高并发 (1)
- twitter (1)
- Erlang (1)
- Scala (1)
- Lua (1)
- ubuntu (3)
- cache (1)
- 面试题 (2)
- android layout (2)
- android控件属性 (2)
- java (5)
- customize view (1)
- advanced (2)
- python (2)
- 机器学习 (5)
最新评论
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
发表评论
-
warning: World-writable config file my.cnf is ignored
2017-09-03 16:41 852##warning: World-writable con ... -
pagination
2014-03-02 16:53 515http://www.infony.com.cn/201 ... -
MYSQL常见出错代码 mysql error number
2012-02-17 10:49 30201016错误：文件无法打开 ... -
设置mysql在局域网中访问
2012-02-09 10:03 843第一：更改 “mysql” 数据库里的 “user” 表里 ... -
经典sql语句
2012-01-09 12:54 775说明：复制表(只复制结构,源表名：a 新表名：b) ... -
数据库访问优化法则简介
2011-10-31 13:50 735第3章 面向程序员的数据库访问性能优化法则 编者说明：本 ... -
Logging
2011-09-06 17:01 722log all visits to your web s ... -
basis and exercise
2011-08-25 18:19 724select now(); select CURDATE() ... -
mysql数据库设计
2011-06-15 16:59 9661. 适度冗余: 让query尽量 ... -
安装mysql-5.0
2011-04-08 19:31 1285Linux (non RPM packages) downl ... -
mysql grant privileges
2011-03-19 18:40 1018The EXECUTION, FILE, PROCESS, R ... -
mysql import and export
2011-03-19 18:36 2391-u -p指的是连接数据库的用户名和密码 ，不能和os的用户名 ...
相关推荐
这是我自己学习mysql时的学习笔记，每一个知识点都是自己动手写的，涵盖了mysql中的几乎全部的基础知识点，查询，子查询，分组，排序，常用函数，多表连接，视图，变量，存储过程，函数，分支结构，循环结构等等
第12周-第08章节-Python3.5-mysql 连接查询.mp4
这是我自己学习mysql时的学习笔记，每一个知识点都是自己动手写的，涵盖了mysql中的几乎全部的基础知识点，查询，子查询，分组，排序，常用函数，多表连接，视图，变量，存储过程，函数，分支结构，循环结构等等
MySQL连接查询相信大家都有所了解，连接查询是在数据库查询操作的时候经常用到的，下面就为您介绍MySQL连接查询 mysql连接查询：支持多表连接 对同一张表可以重复连接多次(别名在多次连接同一张表时很重要) 例题1： ...
本文实例讲述了mysql连接查询、联合查询、子查询原理与用法。分享给大家供大家参考，具体如下： 本文内容： 连接查询 联合查询 子查询 from子查询 where子查询 exists子查询 首发日期：2018-04-11 连接查询...
MySQL连接查询（多表连接查询：内连接，外连接，交叉连接详解） 6：多表连接查询 笛卡尔乘积：如果连接条件省略或无效则会出现 解决办法：添加上连接条件 连接查询的分类： 1.按年代分类： 1）sql 92标准:仅仅...
此系列资源适用于MySQL数据库的学习，以及高级SQL语句
mysql基础学习
文章目录2、MySql连接查询2.1、内连接(sql92 和sql99)2.1.1、sql92`1、等值连接`2、 为表起别名6、可以加排序吗？7、可以实现三表连接吗？2、非等值连接3、自连接2.1.2、sql99标准1、等值连接2、非等值连接3、 自...
主要介绍了MySQL连接查询,结合实例形式总结分析了mysql表的创建、内连接、左连接、右连接、复合条件连接查询等相关操作技巧,需要的朋友可以参考下
主要介绍了mysql连接查询（左连接，右连接，内连接）的相关资料,这里举例说明如果使用及附实例代码，需要的朋友可以参考下
Mysql 连接资源Jar,Mysql连接jar包.
了解MySQL连接查询之前我们先来理解下笛卡尔积的原理。 数据准备 依旧使用上节的表数据（包含classes 班级表和students 学生表）： mysql> select * from classes; +---------+-----------+ | classid | classname ...
MySQL连接工具 绿色版的 很简单的操作MySQL数据库
nodejs封装好的mysql数据库模块，使用mysql连接池以及附带百万mysql测试数据，实现mysql数据库的增删改查功能，使用方法很详细。
mysql连接驱动jar包