- 浏览: 179148 次
- 性别:
- 来自: 上海
文章分类
- 全部博客 (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 886##warning: World-writable con ... -
pagination
2014-03-02 16:53 547http://www.infony.com.cn/201 ... -
MYSQL常见出错代码 mysql error number
2012-02-17 10:49 30851016错误:文件无法打开 ... -
设置mysql在局域网中访问
2012-02-09 10:03 874第一:更改 “mysql” 数据库里的 “user” 表里 ... -
经典sql语句
2012-01-09 12:54 807说明:复制表(只复制结构,源表名:a 新表名:b) ... -
数据库访问优化法则简介
2011-10-31 13:50 763第3章 面向程序员的数据库访问性能优化法则 编者说明:本 ... -
Logging
2011-09-06 17:01 750log all visits to your web s ... -
basis and exercise
2011-08-25 18:19 753select now(); select CURDATE() ... -
mysql数据库设计
2011-06-15 16:59 10001. 适度冗余: 让query尽量 ... -
安装mysql-5.0
2011-04-08 19:31 1318Linux (non RPM packages) downl ... -
mysql grant privileges
2011-03-19 18:40 1049The EXECUTION, FILE, PROCESS, R ... -
mysql import and export
2011-03-19 18:36 2422-u -p指的是连接数据库的用户名和密码 ,不能和os的用户名 ...
相关推荐
这是我自己学习mysql时的学习笔记,每一个知识点都是自己动手写的,涵盖了mysql中的几乎全部的基础知识点,查询,子查询,分组,排序,常用函数,多表连接,视图,变量,存储过程,函数,分支结构,循环结构等等
第12周-第08章节-Python3.5-mysql 连接查询.mp4
这是我自己学习mysql时的学习笔记,每一个知识点都是自己动手写的,涵盖了mysql中的几乎全部的基础知识点,查询,子查询,分组,排序,常用函数,多表连接,视图,变量,存储过程,函数,分支结构,循环结构等等
MySQL连接查询是数据库操作中的重要技能,特别是在处理涉及多个表的数据时。连接查询允许我们将不同表中的相关数据合并成一个单一的结果集,这对于数据分析、报表生成以及数据展示都非常有用。在本例中,我们将通过...
本文实例讲述了mysql连接查询、联合查询、子查询原理与用法。分享给大家供大家参考,具体如下: 本文内容: 连接查询 联合查询 子查询 from子查询 where子查询 exists子查询 首发日期:2018-04-11 连接查询...
MySQL连接查询(多表连接查询:内连接,外连接,交叉连接详解) 6:多表连接查询 笛卡尔乘积:如果连接条件省略或无效则会出现 解决办法:添加上连接条件 连接查询的分类: 1.按年代分类: 1)sql 92标准:仅仅...
mysql基础学习
MySQL连接工具是数据库管理员和开发人员用来与MySQL服务器交互的重要应用程序。绿色版通常指的是免安装、便携式的版本,可以直接运行而无需在计算机上进行正式的安装过程,这对于需要在不同设备间移动工作或者不想...
MySQL连接驱动包是Java应用程序与MySQL数据库进行交互的关键组件,它允许Java开发者通过JDBC(Java Database Connectivity)接口执行SQL语句,管理数据库事务,以及处理数据库结果集。本压缩包包含了两个不同版本的...
是MySQL连接器的类名,通过`Class.forName()`方法加载。 3. 创建数据库连接: ```java String url = "jdbc:mysql://localhost:3306/mydatabase"; String user = "username"; String password = "password"; ...
MySQL连接驱动jar包是Java应用程序与MySQL数据库进行交互的关键组件。在Java中,我们使用JDBC(Java Database Connectivity)API来实现数据库操作,而MySQL连接驱动则是JDBC的一部分,它负责建立Java应用程序与MySQL...
了解MySQL连接查询之前我们先来理解下笛卡尔积的原理。 数据准备 依旧使用上节的表数据(包含classes 班级表和students 学生表): mysql> select * from classes; +---------+-----------+ | classid | classname ...
本模块基于Node.js实现了对MySQL数据库的封装,利用了mysql连接池来优化性能,并且包含了用于测试的百万级数据,确保了在大数据量场景下的稳定性和效率。以下将详细介绍该模块的关键知识点: 1. **Node.js与MySQL**...
在.NET Core中,对于MySQL数据库,我们可以使用`MySql.Data`或`Pomelo.EntityFrameworkCore.MySql`库来实现连接池。 1. 安装依赖库 使用NuGet包管理器安装`Pomelo.EntityFrameworkCore.MySql`或者`MySql.Data`。`...
OPC 服务器软件 KEPServer 实现与 MySQL 连接 OPC 服务器软件 KEPServer 是一种工业自动化领域中的数据交换协议,以实现实时数据交换和远程监控。通过 KEPServer 软件,可以实现与 MySQL 数据库之间的数据交互,...
这个"mysql连接数据库..jar架包"正是这样的一个驱动包,它包含了MySQL JDBC(Java Database Connectivity)驱动,使得Java程序能够通过标准的JDBC API与MySQL服务器进行通信。 JDBC是Java语言中用来规范客户端程序...
由于VB6.0本身并不直接支持MySQL,所以一般需要借助于MySQL提供的ODBC驱动或者第三方组件,例如MySQL Connector/ODBC,来实现连接。 1. 安装MySQL Connector/ODBC驱动 要在系统中安装MySQL的ODBC驱动程序,用户需要...
MySQL连接测试程序是一种基于VC(Visual C++)的软件开发工具,用于通过API接口与MySQL数据库进行交互。在本文中,我们将深入探讨如何利用API方式访问MySQL数据库,并提供查询示例,帮助你理解和掌握相关技术。 ...
本篇文章将详细讨论MySQL8与MySQL5在连接驱动jar包方面的差异,以及如何使用这些驱动来连接Java应用程序。 首先,让我们关注两个不同版本的驱动包:“mysql-connector-java-5.1.30.jar”和“mysql-connector-java-...