Mysql备忘录

子查询

分类(按是否与外部查询相关)

• 关联子查询:每行外部查询都会触发一次关联子查询.
• 非关联子查询:子查询可以独立运行,在整个查询中只查询一次.

分类(按位置)

• select 位置的子查询:要求该子查询必须返回单个值(单行+单列).
• from 位置的子查询:有视图的意味在里面,相当于返回一张新表.
• where 位置的子查询:最复杂.

demo

user表

order表

1.查询2015-07-08那天,购买总额最大的用户id.
1.1 先写一个自查询,根据用户id聚组,求每个用户的购买总额.
select sum(money) sm, uid from where create_time = '2015-07-08' order group by uid;
1.2 将子查询应用.
//分别在from与where中应用子查询,这里注意在where中不能直接应用SOrder进行条件过滤.
select uid from 
(select sum(money) sm, uid from order  where create_time = '2015-07-08' group by uid) SOrder
 where sm = 
(select max(sm) from 
(select sum(money) sm, uid from order  where create_time = '2015-07-08' group by uid) ss);

连接

♦ natural join最严格,要求在默认的同名属性上取等值连接
♦ join using其次,在using指定属性上取等值连接
♦ join on最宽松,可以在任意属性上取任意条件

join && natural join

# 查询用户名与所购买的物品.
# 使用using指定迪卡尔乘积后,用于匹配相等的列(o.uid == u.oid).
1. select u.name,o.name from user u join orders o using uid;
# 直接使用自然连接,指定相等的列为列名相同的列
2. select u.name,o.name from user u natural join orders o;
# 通过on指定条件,留存相应的列
3. select u.name o.name from user u join orders o on u.uid = o.uid;

外连接

表A

表B

• left outer join

# 将左边(A)未匹配的值,全部加入结果集中,相应的右属性置null;
select * from A left outer natural join B;
结果如下 

result

• right outer join

# 将右边(B)未匹配的值,全部加入结果集中,相应的左属性置null;
select * from A right outer natural join B;
结果如下 

result

• full outer join

# 将两边未匹配的值,全部加入结果集中,相应的对方属性置null;
select * from A full outer natural join B;
结果如下 

result

集合

course表

• union,并操作.

# 查询那些上了c1/c2课程的学生id
(select sid from course where course_name = c1)
union
(select sid from course where course_name = c2);

• except,差操作.

# 查询那些上了c1但没上c2课程的学生id
(select sid from course where course_name = c1)
union
(select sid from course where course_name = c2);

• intersect,交操作

# 查询那些上了c1又上了上c2课程的学生id
(select sid from course where course_name = c1)
intersect
(select sid from course where course_name = c2);

tips

• is null / is not null
• 涉及到null的比较都返回false

//并不是真正意义上包含所有学生,因为gpa可能为null
select * from stu where gpa > 2 or gpa <= 2;
//所有学生
select * from stu where gpa > 2 or gpa <= 2 or gpa is null;

• 查询前10条数据

select top 10 * from user;

• B树索引相对与哈希索引的好处在于,其通过中序遍历得到的序列是有序的(不用hash处理,不失真),查找前驱/后继的复杂度也只是O(logn),所以适用于范围查询.而hash索引上,是无法快找出一个范围的.