MySql子查询！！面试官：你查找一个学生表（有name和age属性）中年龄第二大的姓名吧！我™竟然一下子写不出来，所以写下这条博客，仔细学习MySql子查询

查询数据库中第二大的记录的方法

表数据

方法1

SELECT 
	NAME
FROM 
	USER 
WHERE 
	age NOT IN (SELECT MAX(age) FROM USER) 
ORDER BY age DESC
LIMIT 1;

方法2

SELECT 
	NAME
FROM 
	USER 
WHERE 
	age < (SELECT MAX(age) FROM USER) 
ORDER BY age DESC
LIMIT 1;

子查询

表数据

• orderlist表数据
  
• user表
  

单行单列

-- 结果是单行单列的
-- 查询年龄最高的用户姓名
SELECT NAME,age FROM USER WHERE age=(SELECT MAX(age) FROM USER);

多行多列

-- 结果是多行多列的
-- 查询张三1和张三2的订单信息
SELECT * FROM orderlist WHERE uid IN (1,2);

SELECT id FROM USER WHERE NAME IN ('张三1','张三2');

SELECT * FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME IN ('张三1','张三2'));

-- 查询订单表中id大于4的订单信息和所属用户信息
SELECT * FROM orderlist WHERE id > 4;

SELECT 
	u.name,
	o.number
FROM
	USER u,
	(SELECT * FROM orderlist WHERE id > 4) o
WHERE
	u.id = o.uid;