连接查询:如需要多张数据表的数据进行调查,则可通过连接运算符实现多个查询
分类:内连接(inner join):等值的非等值的连接查询自身连接查询
外连接(out join):左连接(left join)、右连接(right join)2.内连接查询:等值和非等值的连接查询
与单表查询类似,都是select语句
把多个表放在from后,并用逗号隔开
可使用as关键字取别名,便于引用
如无重名查询字段,则可省略数据表的指定
#内连接 写法1
SELECT g.gradeName AS '年级',s.subjectName '课程'
FROM SUBJECT AS s INNER JOIN grade AS g ON s.grade_id =g.id
#内连接 写法2
SELECT g.gradeName AS '年级',s.subjectName '课程'
FROM SUBJECT AS s INNER JOIN grade AS g
WHERE s.grade_id =g.id
3.外连接:
左连接:从左表(table_1)中返回所有的记录,即便在右(table_2)中没有匹配的行
select 字段1……from table_1
left [outer] join table_2 on table_1.字段x=table_2.字段y;
#左外连接
SELECT s.subjectName '课程', g.gradeName AS '年级'
FROM SUBJECT AS s LEFT JOIN grade AS g ON s.grade_id =g.id
右连接:从右表(table_2)中返回所有的记录,即便在左(table_1)中没有匹配的行
select 字段1……from table_1
right [outer] join table_2 on table_1.字段x=table_2.字段y;
#右外连接
SELECT s.subjectName '课程', g.gradeName AS '年级'
FROM SUBJECT AS s RIGHT JOIN grade AS g ON s.grade_id =g.id
4.不同的SQL join对比
操作符名称 | 描述 |
inner join(join) | 如果表中有至少一个匹配,则返回行 |
left join | 即使右表中没有匹配,也从左表中返回所有的行 |
right join | 即使左表中没有匹配,也从右表中返回所有的行 |
5.自连接查询
自身连接查询:数据表与自身进行连接
#自连接
SELECT a.categoryName AS '父栏目',b.categoryName '子栏目'
FROM category AS a INNER JOIN category AS b ON a.categoryId = b.pid
6.order by 排序:
对select语句查询得到的结果,按某些字段进行排序
用DESC或ASC搭配使用,默认为ASC升序
#order by 排序
SELECT *
FROM SUBJECT
ORDER BY classHour DESC #降序
7.子查询:在查询语句中的where条件子句中,又嵌套另外一个查询语句
注:嵌套查询可由多个子查询组成,求解的方式是由里到外
子查询返回的结果一般都是集合,故而建议使用in关键字
#子查询——等值查询
SELECT id ,stuName
FROM student
WHERE id = (
SELECT b.stuNo
FROM SUBJECT AS a INNER JOIN results AS b ON b.stuNo = a.id
WHERE a.subjectName = 'Logic Java' AND b.resule >= 60)
#子查询——in
SELECT id ,stuName
FROM student
WHERE id IN (
SELECT b.stuNo
FROM SUBJECT AS a INNER JOIN results AS b ON b.stuNo = a.id
WHERE a.subjectName = 'Logic Java' AND b.resule >= 60)