MySQL：03-高级查询

一、模糊查询

LIKE

-- LIKE 结合%(代表0到任意个字符) 
SELECT studentno,studentname FROM student WHERE studentname LIKE '张%';
-- LIKE 结合_(代表1个字符) 
SELECT studentno,studentname FROM student WHERE studentname LIKE '张_';
1
2
3
4

IN

-- 查询1001,1002,1003号学员
SELECT studentno,studentname FROM student WHERE studentno IN (1001,1002,1003);
1
2

二、连表查询

MySQL中没有full outer join操作，有union操作。

join (连接的表) on (判断的条件)
1

1、内联结(Inner join)

SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.subjectno
1
2
3
4

2、外联结(Outer join)

（1）RIGHT JOIN

-- RIGHT JOIN
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno;
1
2
3
4
5

（2）LEFT JOIN

-- LEFT JOIN
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno;
1
2
3
4
5

3、交叉联接(Cross join)

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
-- 或者
SELECT <字段名> FROM <表1>, <表2> [WHERE子句] 
1
2
3

当连接的表之间没有关系时，可以省略掉WHERE 子句，这时返回结果就是两个表的笛卡尔积，返回结果数量就是两个表的数据行相乘。

4、多表查询

三表查询

-- 三表查询
SELECT s.studentno,studentname,subjectname,studentresult
from student s
RIGHT JOIN result r
ON r.subjectno = s.studentno
INNER JOIN `subject` sub
on r.studentno = sub.subjectno;
1
2
3
4
5
6
7

5、自连接

自己的表和自己的表连接，一张表拆分为两张一样的表

-- 查询父子信息：把一张表看为两个一模一样的表
SELECT a.categoryname AS '父栏目',b.categoryname AS '子栏目'
FROM category AS a,category AS b
WHERE a.categoryid = b.pid;
1
2
3
4

三、子查询

where语句中嵌套select语句。

-- 子查询(WHERE中嵌套select)
SELECT studentno,subjectno,studentresult
FROM result
WHERE subjectno = (
		SELECT subjectno FROM `subject`
		WHERE subjectname = '数据库结构-1'
		)
ORDER BY studentresult DESC;
1
2
3
4
5
6
7
8