DQL基本语法2(查询)
下面的查询基于的表有:
- "Websites" 表:
- access_log表:
- students表:
1、多表查询
1.1、基本查询(笛卡尔积)
SELECT查询不但可以从一张表查询数据,还可以从多张表同时查询数据。
查询多张表的语法:
select * from 表1, 表2; # 示例: SELECT * FROM websites, access_log;
查询结果类似:
上面查询出来的将是 access_log 和 websites 表的笛卡尔积,也就是 access_log 表的每个记录分别跟 websites 表的所有记录的组合。 结果集的列数是 access_log 表和 websites 表的列数之和,行数是 access_log 表和 websites 表行数之积。比如 a 表有 2 条数据,每条数据有 3 个字段,b 表有 4 条数据,每条数据有 5 个字段,则笛卡尔积的结果将有 2*4 = 8 条数据,并且每条数据将有 3+5=8 个字段。
在多表查询时,不同表之间可能有相同的列名称,此时我们应该通过 “表名.列名” 的方式来区别不同表之间的列,或者也可以直接给表起别名,通过 “表别名.列名” 的方式来区分:
SELECT w.id, w.NAME, a.aid, a.site_id FROM websites w, access_log a;
查询结果类似:
JOIN的含义就如英文单词“join”一样,连接两张表,大致分为内连接,外连接,右连接,左连接,自然连接。
1.2、内连接(交集、INNER JOIN、JOIN)
内连接INNER JOIN是最常用的连接操作,从数学的角度讲就是求两个表的交集。实际上就是在两个表的笛卡尔积中,通过 on 条件过滤结果。
select 字段列表 from 表1 [inner] join 表2 on 条件 -- inner可省略不写
示例:
SELECT a.id, a.name, b.aid, b.date FROM websites a INNER JOIN access_log b ON a.id = b.site_id; -- 内连接语法可以用where替代,等价于上面语句,可以称为隐式内连接 SELECT a.id, a.name, b.aid, b.date FROM websites a, access_log b WHERE a.id = b.site_id;
查询结果:
1.3、左连接(LEFT JOIN)
1.3.1、左外连接(A LEFT JOIN B ON A.KEY = B.KEY )
左外连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。实际上就是从两个表的笛卡尔积中,先通过 on 条件过滤结果,然后再加上左表中所有剩余的记录即可。(也就是说,最终会将左表中不符合 on 条件的记录也一并加上,实际上也就是左表中的所有记录都会存在于最终结果中)
select 字段列表 from 左表 left join 右表 on 条件;
示例,下面以 access_log 为左表,websites 为右表:
SELECT * FROM access_log LEFT JOIN websites ON access_log.aid=websites.id;
查询结果:
1.3.2、左连接(A LEFT JOIN B ON A.KEY = B.KEY WHERE B.KEY IS NULL)
左连接LEFT JOIN 是左外连接中,只保留那些不属于两个表的交集的数据。实际上就是从两个表的笛卡尔积中,先通过 on 条件过滤结果,然后再加上左表中所有剩余的记录,然后再去掉两表中交集的部分。
也就是上面的左外查询中只保留红框框起来的数据:
1.4、右连接(RIGHT JOIN)
1.4.1、右外连接(A RIGHT JOIN B ON A.KEY = B.KEY )
同理,右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。实际上就是从两个表的笛卡尔积中,先通过 on 条件过滤结果,然后再加上右表中所有剩余的记录即可。(也就是说,最终会将右表中不符合 on 条件的记录也一并加上,实际上也就是右表中的所有记录都会存在于最终结果中)
select 字段列表 from 左表 right join 右表 on 条件
示例,下面以 websites 为左表,access_log 为右表:
SELECT * FROM websites RIGHT JOIN access_log ON access_log.aid=websites.id;
查询结果:
1.4.2、右连接(A RIGHT JOIN B ON A.KEY = B.KEY WHERE A.KEY IS NULL)
右连接RIGHT JOIN 是右外连接中,只保留那些不属于两个表的交集的数据。实际上就是从两个表的笛卡尔积中,先通过 on 条件过滤结果,然后再加上右表中所有剩余的记录,然后再去掉两表中交集的部分。
也就是上面的左外查询中只保留红框框起来的数据:
1.5、全外连接
全外连接:左表和右表都不做限制,所有的记录都显示,两表不足的地方用null 填充。全外连接= 左表全部记录+右表全部记录+相关联结果 = 左外连接+右外连接-相关联结果(即去除重复)
(请注意,图中 full outer join 语法在 mysql 中是不支持使用的)
在 mysql 里全外连接的sql语句就是 union 左外连接和右外连接的结果。请注意不是 union all,而是使用了 union,union 会自动去除重复结果。
SELECT * FROM table1 LEFT JOIN table2 ON (table1.id = table2.id) UNION SELECT * FROM table1 RIGHT JOIN table2 ON (table1.id = table2.id)
示例:
SELECT * FROM access_log LEFT JOIN websites ON access_log.aid=websites.id UNION SELECT * FROM access_log RIGHT JOIN websites ON access_log.aid=websites.id;
查询结果如下:(下面我额外给websites表添加了一条 id为15的记录,以便观察右外连接的结果)
1.6、两表独有的数据集
(请注意,图中 full outer join 语法在 mysql 中是不支持使用的)
在mysql中就是union了左连接和右连接
SELECT * FROM table1 LEFT JOIN table2 ON (table1.id = table2.id) WHERE table2.id IS NULL UNION SELECT * FROM table1 RIGHT JOIN table2 ON (table1.id = table2.id) WHERE table1.id IS NULL
示例:
SELECT * FROM access_log LEFT JOIN websites ON access_log.aid=websites.id where websites.id is null UNION SELECT * FROM access_log RIGHT JOIN websites ON access_log.aid=websites.id where access_log.aid is null;
查询结果如下:(下面额外给websites表添加了一条 id为15的记录,以便观察右连接的结果)
2、子查询
子查询允许把一个查询嵌套在另一个查询当中。
2.1、where型子查询
where 型子查询把内层查询结果当作外层查询的比较条件。
如果内层查询返回一个标量值(就一个值),那么外部查询就可以使用比较符,比如:=、>、<、>=、<=和<>符号进行比较判断。
比如下面查询 count 值最大的相关信息:
SELECT *
FROM access_log
WHERE count = (SELECT MAX(access_log.count) FROM access_log);
查询结果:
如果子查询返回的不是一个标量值,而是一个N行单列的结果集,但外部查询使用了比较符和子查询的结果集进行比较,此时会抛出异常。
如果子查询返回的不是一个标量值,而是一个N行单列的结果集,此时可以使用 IN、ANY、SOME 和 ALL 操作符,不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。
SELECT * FROM websites WHERE id IN (SELECT id from websites WHERE country = 'CN');
2.2、from型子查询
from 型子查询把内层的查询结果当成临时表,供外层sql再次查询。当子查询返回的结果是多行多列时,子查询的结果集可以当成表看待,一般要给这个临时表起一个别名,否则临时表没有名称则无法访问临时表中的字段。
SELECT * FROM (SELECT id,`name`,alexa FROM websites WHERE country = 'CN') temp WHERE temp.alexa > 15;