背景

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语句

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)

ITPub博客

mysql 左连接，右连接，内链接，exists等

金山面试题

最新文章