一、模糊查询
LIKE
-- LIKE 结合%(代表0到任意个字符)
SELECT studentno,studentname FROM student WHERE studentname LIKE '张%';
-- LIKE 结合_(代表1个字符)
SELECT studentno,studentname FROM student WHERE studentname LIKE '张_';
IN
-- 查询1001,1002,1003号学员
SELECT studentno,studentname FROM student WHERE studentno IN (1001,1002,1003);
二、连表查询
MySQL中没有full outer join操作,有union操作。
join (连接的表) on (判断的条件)
1、内联结(Inner join)
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.subjectno
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;
(2)LEFT JOIN
-- LEFT JOIN
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno;
3、交叉联接(Cross join)
SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
-- 或者
SELECT <字段名> FROM <表1>, <表2> [WHERE子句]
当连接的表之间没有关系时,可以省略掉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;
5、自连接
自己的表和自己的表连接,一张表拆分为两张一样的表
-- 查询父子信息:把一张表看为两个一模一样的表
SELECT a.categoryname AS '父栏目',b.categoryname AS '子栏目'
FROM category AS a,category AS b
WHERE a.categoryid = b.pid;
三、子查询
where语句中嵌套select语句。
-- 子查询(WHERE中嵌套select)
SELECT studentno,subjectno,studentresult
FROM result
WHERE subjectno = (
SELECT subjectno FROM `subject`
WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;