背景
内链接(查询两边都有的数据) 等价于 where语句 等价于 join语句
mysql> select * from student s inner join grade g on s.no=g.no;
+----+------+----+-------+
| no | name | no | grade |
+----+------+----+-------+
| 1 | a | 1 | 90 |
| 2 | b | 2 | 80 |
| 3 | c | 3 | 70 |
+----+------+----+-------+
3 rows in set (0.16 sec)
左连接 (左表中所有数据,右表中对应数据)
mysql> select * from student as s left join grade as
-> g on s.no=g.no;
+----+------+------+-------+
| no | name | no | grade |
+----+------+------+-------+
| 1 | a | 1 | 90 |
| 2 | b | 2 | 80 |
| 3 | c | 3 | 70 |
| 4 | d | NULL | NULL |
+----+------+------+-------+
4 rows in set (0.00 sec)
右连接(右表中所有数据,左表中对应数据)
mysql> select * from student as s right join grade as g on s.no=g.no;
+------+------+----+-------+
| no | name | no | grade |
+------+------+----+-------+
| 1 | a | 1 | 90 |
| 2 | b | 2 | 80 |
| 3 | c | 3 | 70 |
+------+------+----+-------+
3 rows in set (0.00 sec)
Mysql数据库中的EXISTS和NOT EXISTS
- mysql> select * from grade;
- +----+-------+
- | no | grade |
- +----+-------+
- | 1 | 90 |
- | 2 | 80 |
- | 3 | 70 |
- +----+-------+
- 3 rows in set (0.00 sec)
-
mysql> select * from student;
- +----+------+
- | no | name |
- +----+------+
- | 1 | a |
- | 2 | b |
- | 3 | c |
- | 4 | d |
- +----+------+
内链接(查询两边都有的数据) 等价于 where语句 等价于 join语句
mysql> select * from student s inner join grade g on s.no=g.no;
+----+------+----+-------+
| no | name | no | grade |
+----+------+----+-------+
| 1 | a | 1 | 90 |
| 2 | b | 2 | 80 |
| 3 | c | 3 | 70 |
+----+------+----+-------+
3 rows in set (0.16 sec)
左连接 (左表中所有数据,右表中对应数据)
mysql> select * from student as s left join grade as
-> g on s.no=g.no;
+----+------+------+-------+
| no | name | no | grade |
+----+------+------+-------+
| 1 | a | 1 | 90 |
| 2 | b | 2 | 80 |
| 3 | c | 3 | 70 |
| 4 | d | NULL | NULL |
+----+------+------+-------+
4 rows in set (0.00 sec)
- 左链接优化:
- 优化前:
- mysql> SELECT COUNT(c_user.uid) AS tp_count FROM `c_user` LEFT JOIN c_devices on c_user.last_did=c_devices.did LIMIT 1;
+----------+
| tp_count |
+----------+
| 4091879 |
+----------+
1 row in set (16.10 sec)
- 优化后:
- mysql> SELECT COUNT(c_user.uid) AS tp_count FROM `c_user`;
+----------+
| tp_count |
+----------+
| 4091879 |
+----------+
1 row in set (1.28 sec)
右连接(右表中所有数据,左表中对应数据)
mysql> select * from student as s right join grade as g on s.no=g.no;
+------+------+----+-------+
| no | name | no | grade |
+------+------+----+-------+
| 1 | a | 1 | 90 |
| 2 | b | 2 | 80 |
| 3 | c | 3 | 70 |
+------+------+----+-------+
3 rows in set (0.00 sec)
Mysql数据库中的EXISTS和NOT EXISTS
金山面试题
题目如下:
数据库1中存放着a类数据,数据库2中存放着以天为单位划分的表30张(比如table_20110909,table_20110910,table_20110911),总共是一个月的数据。表1中的a类数据中有一个字段userid来唯一判别用户身份,表2中的30张表(每张表结构相同)也有一个字段userid来唯一识别用户身份。如何判定a类数据库的多少用户在数据库2中出现过?
1)各位高手能否说一下这道题的考点是什么
2)能否说一下你们的实现思路或者关键的sql语句
数据库1中存放着a类数据,数据库2中存放着以天为单位划分的表30张(比如table_20110909,table_20110910,table_20110911),总共是一个月的数据。表1中的a类数据中有一个字段userid来唯一判别用户身份,表2中的30张表(每张表结构相同)也有一个字段userid来唯一识别用户身份。如何判定a类数据库的多少用户在数据库2中出现过?
1)各位高手能否说一下这道题的考点是什么
2)能否说一下你们的实现思路或者关键的sql语句
select count(*) from 数据库1.a类数据
where exists (select 1 from 数据库2.table_20110909 where userid = 数据库1.a类数据.userid)
or exists (select 1 from 数据库2.table_20110910 where userid = 数据库1.a类数据.userid)
or exists (select 1 from 数据库2.table_20110911 where userid = 数据库1.a类数据.userid)
where exists (select 1 from 数据库2.table_20110909 where userid = 数据库1.a类数据.userid)
or exists (select 1 from 数据库2.table_20110910 where userid = 数据库1.a类数据.userid)
or exists (select 1 from 数据库2.table_20110911 where userid = 数据库1.a类数据.userid)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29096438/viewspace-1873884/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29096438/viewspace-1873884/