MySQL系列(五)：DQL

一、基本查询

select */字段列表 from 表名 [where条件];
select [select选项] 字段列表[字段别名]|* from 数据源 [where条件子句] [group by子句] [having子句] [order by子句] [limit 子句];

二、连接查询

• 连接查询简义：将多张表(可以大于2张)进行记录的连接(按照某个指定的条件进行数据拼接)。
• 连接查询结果：记录数有可能变化，字段数一定会增加(至少两张表的合并)。
• 连接查询意义：在用户查看数据的时候,需要显示的数据来自多张表.
• 左表：在join关键字左边的表
• 右表：在join关键字右边的表

1.交叉连接

cross join，从一张表中循环取出每一条记录，每条记录都去另外一张表进行匹配。匹配一定保留(没有条件匹配)，而连接本身字段就会增加(保留)，最终形成笛卡尔积。

 1、左表 cross join 右表;
 2、from 左表,右表;

交叉连接的存在可以保证连接这种结构的完整性。

select * from a cross join b;
select * from a,b;

2.内连接

[inner] join，从左表中取出每一条记录，去右表中与所有的记录进行匹配。匹配必须是某个条件在左表中与右表中相同最终才会保留结果，否则不保留。

1、左表 [inner] join 右表 on 左表.字段 = 右表.字段

2、左表 [inner] join 右表 where 左表.字段 = 右表.字段    --没有on效率高

3、左表,右表 where 左表.字段 = 右表.字段                            --没有on效率高

• 内连接如果不使用条件，就形成笛卡尔乘积。
• 若表名或字段名太长，可以使用表别名或字段别名。 

select * from abc inner join def on abc.cid = def.id;
select * from abc join def on cid = id;
select * from a join b where a.cid = b.id;
select * from a,b where a.cid = b.id;
select * from abc as a inner join def on a.cid = def.id;

3.外连接

outer join，以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留。能匹配正确保留，不能匹配的话其他表的字段都置空NULL。

left join：左外连接(左连接)，以左表为主表。返回左表全部数据，没有出现在左表的字段置null。

right join：右外连接(右连接)，以右表为主表。返回右表的全部数据，没有出现在右表的字段置null。

左表 left/right [outer] join 右表 on 左表.字段 = 右表.字段;

-- 左连接
select s.*,c.name as cname,c.room from student as s left join class as c on s.cid = c.id;


-- 右连接
select s.*,c.name as cname,c.room student as s right join class as c on s.cid = c.id;

4.自然连接

natural join，自然连接，就是自动匹配连接条件。系统以字段名字作为匹配模式(同名字段就作为条件，多个同名字段都作为条件)。

自然内连接: 左表 natural join 右表;

自然外连接: 左表 natural left/right join 右表;

-- 自然内连接
select * from a natural join b;

-- 自然左外连接
select * from a natural left join b;

5.多表连接

多表连接：A表 inner join B表 on 条件 left join C表 on条件...

执行顺序：A表内连接B表，得到一个二维表，左连接C表形成二维表...
 

三、联合查询

将多次查询(多条select语句)，在记录上进行拼接(字段不会增加)。

每一条select语句获取的字段数必须严格一致(但是字段类型无关)。

select 语句1 union [union选项] select语句2...

union选项，all为保留所有，distinct为去重（默认）

-- 联合查询
select * from a union select * from b;
select * from a union all select * from b;
select id,cname,room from a union all select name,number,id from b;

• 查询同一张表，但是需求不同时使用。如：查询学生信息，男生身高升序，女生身高降序。
• 多表查询，多张表的结构是完全一样的，保存的数据(结构)也是一样的时候使用。
• 在联合查询中，order by不能直接使用，需要对查询语句使用括号才行。
• 若要order by生效，必须搭配limit。limit使用限定的最大数即可。

(select * from student where sex = '男' order by age asc limit 9999999)
union
(select * from student where sex = '女' order by age desc limit 9999999);

四、子查询

1.分类

按位置分类：子查询(select语句)在外部查询(select语句)中出现的位置

• from子查询：子查询跟在from之后
• where子查询：子查询出现where条件中
• exists子查询：子查询出现在exists里面

按结果分类：根据子查询得到的数据进行分类

• 标量子查询：子查询得到的结果是一行一列。在where之后。
• 列子查询：子查询得到的结果是一列多行。在where之后。
• 行子查询：子查询得到的结果是多列一行(多行多列)。在where之后。
• 表子查询：子查询得到的结果是多行多列。在from之后。

2.标量子查询

-- 标量子查询
select * from student where cid = (select id from class where cname = 'xxx');

3.列子查询

-- 列子查询
select * from student where cid in(select id from class);


-- any,some,all
select * from student where cid =any(select id from class);
select * from student where cid =some(select id from class);
select * from student where cid =all(select id from class);

select * from student where cid !=any(select id from class);     -- 所有结果(null除外)
select * from student where cid !=some(select id from class);    -- 所有结果(null除外)
select * from student where cid !=all(select id from class);     -- 2(null除外)


select * from student where age = (select max(age) from student)
and
height = (select max(height) from student);

4.行子查询

需要构造行元素，行元素由多个字段构成。

-- 行子查询，(age,height)是行元素
select * from student where
(age,height) = (select max(age),max(height) from student);

5.表子查询

 子查询返回的结果是多行多列的二维表。

-- 表子查询
select * from student group by cid order by height desc;
--from子查询，得到的结果作为from的数据源
select * from (select * from student order by height desc) as student group by cid;

6.exists子查询

exists子查询用来判断某些条件是否满足(跨表)， exists接在where之后，它的返回结果只有0和1。

-- exists子查询
select * from student where exists(select * from class where id = 1);

 

转载于:https://my.oschina.net/programs/blog/1789524