一、mysql表的连接方式
内连接和外连接的区别:
我把两个表比作集合A、B,其中,内连接是集合A和集合B的交集,而交集的内容在两个表中都存在,即在每一个表的内部;而外连接则是除了交集外,还有另一个表中的内容(左、右、全),所涉及的结果集是两个表中的内容。
二、表的连接方式:
表的连接查询方式有内连接、外连接(左连接、右连接、全连接)、交叉连接。
下面举例中所采用的表:
mysql> select * from Store_Information;
+---------------+-------+------------+
| store_name | Sales | Date |
+---------------+-------+------------+
| Los Angeles | 1500 | 1999-01-05 |
| Los Angeles | 500 | 1999-01-08 |
| Boston | 700 | 1999-01-08 |
| Seven Eleven | 860 | 1999-01-09 |
| Good Neighbor | 1100 | 1999-01-10 |
| Wumark | 3000 | 1999-01-11 |
| Walmark | 3500 | 1998-04-15 |
| Titi | 1100 | 1999-05-15 |
+---------------+-------+------------+
8 rows in set (0.00 sec)
mysql> select * from Geography;
+-------------+-------------+
| region_name | store_name |
+-------------+-------------+
| East | Boston |
| East | New York |
| West | Los Angeles |
| West | San Diego |
+-------------+-------------+
4 rows in set (0.00 sec)
mysql>
1.内连接:
内连接查询操作列出与连接条件匹配的数据航,它使用比较运算符比较被连接列的列值。只有两个表相匹配的行才能出现在结果集中,也就是根据条件得到两个表的交集。
举例说明:
mysql> select * from Store_Information S,Geography G where S.store_name=G.store_name;
+-------------+-------+------------+-------------+-------------+
| store_name | Sales | Date | region_name | store_name |
+-------------+-------+------------+-------------+-------------+
| Los Angeles | 1500 | 1999-01-05 | West | Los Angeles |
| Los Angeles | 500 | 1999-01-08 | West | Los Angeles |
| Boston | 700 | 1999-01-08 | East | Boston |
+-------------+-------+------------+-------------+-------------+
3 rows in set (0.00 sec)
mysql> select * from Store_Information S inner join Geography G on S.store_name=G.store_name;
+-------------+-------+------------+-------------+-------------+
| store_name | Sales | Date | region_name | store_name |
+-------------+-------+------------+-------------+-------------+
| Los Angeles | 1500 | 1999-01-05 | West | Los Angeles |
| Los Angeles | 500 | 1999-01-08 | West | Los Angeles |
| Boston | 700 | 1999-01-08 | East | Boston |
+-------------+-------+------------+-------------+-------------+
3 rows in set (0.00 sec)
mysql> select * from Store_Information S inner join Geography G using(store_name);
+-------------+-------+------------+-------------+
| store_name | Sales | Date | region_name |
+-------------+-------+------------+-------------+
| Los Angeles | 1500 | 1999-01-05 | West |
| Los Angeles | 500 | 1999-01-08 | West |
| Boston | 700 | 1999-01-08 | East |
+-------------+-------+------------+-------------+
3 rows in set (0.00 sec)
mysql>
上面的第一条sql和第二条sql语句是等价的,第三条语句是第二条sql的简写形式,on子句和using子句的替换模式。在下面不再演示using的子句了。
2.外连接:
返回到查询结果集合中的不仅包含符合连接条件的行,而且还包括左表(左外连接时)、右表(右外连接时)或左右两边的表(全外连接)中的所有行。
左外连接:
mysql> select * from Store_Information S left join Geography G on S.store_name=G.store_name;
+---------------+-------+------------+-------------+-------------+
| store_name | Sales | Date | region_name | store_name |
+---------------+-------+------------+-------------+-------------+
| Los Angeles | 1500 | 1999-01-05 | West | Los Angeles |
| Los Angeles | 500 | 1999-01-08 | West | Los Angeles |
| Boston | 700 | 1999-01-08 | East | Boston |
| Seven Eleven | 860 | 1999-01-09 | NULL | NULL |
| Good Neighbor | 1100 | 1999-01-10 | NULL | NULL |
| Wumark | 3000 | 1999-01-11 | NULL | NULL |
| Walmark | 3500 | 1998-04-15 | NULL | NULL |
| Titi | 1100 | 1999-05-15 | NULL | NULL |
+---------------+-------+------------+-------------+-------------+
8 rows in set (0.00 sec)
mysql>
右外连接:
mysql> select * from Store_Information S right join Geography G on S.store_name=G.store_name;
+-------------+-------+------------+-------------+-------------+
| store_name | Sales | Date | region_name | store_name |
+-------------+-------+------------+-------------+-------------+
| Boston | 700 | 1999-01-08 | East | Boston |
| NULL | NULL | NULL | East | New York |
| Los Angeles | 1500 | 1999-01-05 | West | Los Angeles |
| Los Angeles | 500 | 1999-01-08 | West | Los Angeles |
| NULL | NULL | NULL | West | San Diego |
+-------------+-------+------------+-------------+-------------+
5 rows in set (0.00 sec)
mysql>
全连接(mysql中不支持全连接,我们可以用左右合并来解决):
mysql> select * from Store_Information S left join Geography G on S.store_name=G.store_name union (select * from Store_Information S right join Geography G on S.store_name=G.store_name);
+---------------+-------+------------+-------------+-------------+
| store_name | Sales | Date | region_name | store_name |
+---------------+-------+------------+-------------+-------------+
| Los Angeles | 1500 | 1999-01-05 | West | Los Angeles |
| Los Angeles | 500 | 1999-01-08 | West | Los Angeles |
| Boston | 700 | 1999-01-08 | East | Boston |
| Seven Eleven | 860 | 1999-01-09 | NULL | NULL |
| Good Neighbor | 1100 | 1999-01-10 | NULL | NULL |
| Wumark | 3000 | 1999-01-11 | NULL | NULL |
| Walmark | 3500 | 1998-04-15 | NULL | NULL |
| Titi | 1100 | 1999-05-15 | NULL | NULL |
| NULL | NULL | NULL | East | New York |
| NULL | NULL | NULL | West | San Diego |
+---------------+-------+------------+-------------+-------------+
10 rows in set (0.00 sec)
mysql>
3.交叉连接:
交叉连接不带where子句,它返回被连接的两个表所有数据航的笛卡尔积,返回到结果集中的额数据行数等于第一个表中符合查询条件的数据航乘以第二个表中符合查询条件的数据行数。
mysql> select * from Store_Information S cross join Geography G order by S.store_name;
+---------------+-------+------------+-------------+-------------+
| store_name | Sales | Date | region_name | store_name |
+---------------+-------+------------+-------------+-------------+
| Boston | 700 | 1999-01-08 | West | Los Angeles |
| Boston | 700 | 1999-01-08 | East | New York |
| Boston | 700 | 1999-01-08 | East | Boston |
| Boston | 700 | 1999-01-08 | West | San Diego |
| Good Neighbor | 1100 | 1999-01-10 | West | Los Angeles |
| Good Neighbor | 1100 | 1999-01-10 | East | New York |
| Good Neighbor | 1100 | 1999-01-10 | East | Boston |
| Good Neighbor | 1100 | 1999-01-10 | West | San Diego |
| Los Angeles | 1500 | 1999-01-05 | West | Los Angeles |
| Los Angeles | 500 | 1999-01-08 | West | Los Angeles |
| Los Angeles | 1500 | 1999-01-05 | East | New York |
| Los Angeles | 500 | 1999-01-08 | East | New York |
| Los Angeles | 1500 | 1999-01-05 | East | Boston |
| Los Angeles | 500 | 1999-01-08 | East | Boston |
| Los Angeles | 1500 | 1999-01-05 | West | San Diego |
| Los Angeles | 500 | 1999-01-08 | West | San Diego |
| Seven Eleven | 860 | 1999-01-09 | West | Los Angeles |
| Seven Eleven | 860 | 1999-01-09 | East | New York |
| Seven Eleven | 860 | 1999-01-09 | East | Boston |
| Seven Eleven | 860 | 1999-01-09 | West | San Diego |
| Titi | 1100 | 1999-05-15 | West | Los Angeles |
| Titi | 1100 | 1999-05-15 | East | New York |
| Titi | 1100 | 1999-05-15 | East | Boston |
| Titi | 1100 | 1999-05-15 | West | San Diego |
| Walmark | 3500 | 1998-04-15 | West | Los Angeles |
| Walmark | 3500 | 1998-04-15 | East | New York |
| Walmark | 3500 | 1998-04-15 | East | Boston |
| Walmark | 3500 | 1998-04-15 | West | San Diego |
| Wumark | 3000 | 1999-01-11 | West | Los Angeles |
| Wumark | 3000 | 1999-01-11 | East | New York |
| Wumark | 3000 | 1999-01-11 | East | Boston |
| Wumark | 3000 | 1999-01-11 | West | San Diego |
+---------------+-------+------------+-------------+-------------+
32 rows in set (0.00 sec)
mysql>
自然连接是除去重复属性的等值连接。
两者之间的区别和联系如下:
1、自然连接一定是等值连接,但等值连接不一定是自然连接。等值连接不把重复的属性除去;而自然连接要把重复的属性除去。
2、等值连接要求相等的分量,不一定是公共属性;而自然连接要求相等的分量必须是公共属性。
http://blog.163.com/girl_lihuiyue@126/blog/static/1806962120142141165441/
写的比较简单:
相关推荐
30--[视频]31MySQL表连接
示例代码中,我们使用了Python的mysql.connector库来连接MySQL数据库并执行SQL语句。 在实际使用时,你需要将your_username和your_password替换为你的MySQL数据库的用户名和密码,将mydatabase替换为你的数据库名称...
MySQL表连接查询 1 基本格式 select fieldName from tbName1 连接符 tbName2 on 条件 2 笛卡尔乘积【避免】 -- 笛卡尔乘积,没有约束条件,数据库匹配发生相乘关系,结果也不是预期结果 -- 无意义结果 select ...
mysql 多表连接,内连接,外连接,自连接
java连接mysqljava连接mysql
Mysql 连接资源Jar,Mysql连接jar包.
mysql连接驱动jar包
VC6.0下通过MySQL API连接mysql数据库,只需在VC中配置对应环境,代码就能直接运行
设置mysql数据库远程连接: Windows系统 1、 停止mysql的服务。 2、 进入命令窗口,然后进入MySQL的安装目录,比如我的安装目录是c:\mysql,进入c:\mysql\bin 3、 进入mysql数据库服务器 c:\mysql\bin>mysql –u ...
MySQL连接工具 绿色版的 很简单的操作MySQL数据库
解决MYSQL无法远程连接,适合mysql外链。
一般MySQL连接不上,可能有两大原因:1、MyEclipse配置错误 2、MySQL配置不当。 一、我们一般的连接步骤如下: 1、在MyEclipse中连接MySQL数据库:依次点击window–>show view–>other–>MyEclipse Database–>DB ...
mysql全连接、左连接与右连接
mysql .net 连接驱动 ,可以在.net中连接mysql数据库。
3. 掌握多表连接的各种方法,包括内连接、外连接等。 4. 掌握子查询的方法,包括相关子查询和不相关子查询。 实验内容及实验器材: 一、实验内容 分组查询、多表连接查询、子查询,具体内容参见实验指导手册。 二、...
MYSQL数据库远程连接,例程,需要有亦众的支持库才能编译。
1 可以无需安装MYSQL数据库,用SuperMysql连接器可以连接远程MYSQL数据库。 2 远程数据库表直接显示在程序的显示框中,无需手工输入指令。 3 程序绑定了三个MYSQL指令。 增加功能: a 远程更改密码时可以自己定义...
mysql数据连接池
mysql数据库连接器,可以直接查看数据表