连接查询
- 等值与非等值连接
/*连接两表中emp_id相等的元组显示查询结果*/
SELECT
a.*, b.*
FROM
ssm.t_work_card AS a,
ssm.t_employee_task AS b
WHERE
a.emp_id = b.emp_id;
/*连接查询添加其他限定条件*/
SELECT
a.*, b.*
FROM
ssm.t_work_card AS a,
ssm.t_employee_task AS b
WHERE
a.emp_id = b.emp_id
AND a.real_name = 'lp'
AND b.task_name = '任务';
- 自身连接
一个表与其自己进行连接
比如:
SELECT
f.cno, s.Cpno
FROM
pricource f,
pricource s
WHERE
f.Cpno = S.Cno;
- 外连接
分为左外连接和右外连接,合并两表匹配列,其中,左外连接指的是以左表为主表内容,如果条件不匹配无右表内容(为null),右外连接类似。【mysql不支持全外连接】
比如:
SELECT
a.emp_id, real_name, mobile, b.task_name, b.note
FROM
ssm.t_work_card a
LEFT JOIN
ssm.t_employee_task b ON (a.emp_id = b.emp_id);
嵌套连接【子查询也可以出现在from子句中,子查询临时表叫临时派生表】
- 使用in
SELECT
real_name
FROM
ssm.t_work_card
WHERE
emp_id IN (SELECT
emp_id
FROM
ssm.t_employee_task
WHERE
task_id = 1);
- 使用比较运算进行子查询
SELECT
Sno, Cno
FROM
SC x
WHERE
Grade >= (SELECT
AVG(Grade)
FROM
SC y
WHERE
y.Sno = x.Sno);
- 带有ANY(SOME)或ALL谓语的子查询
SELECT
Sname, Sage
FROM
Student
WHERE
Sage < ANY (SELECT
Sage
FROM
Student
WHERE
Sdept = 'CS')
AND Stept <> 'CS';
- 带有EXISTS谓语的子查询
SELECT
Sname
FROM
Student
WHERE
EXISTS( SELECT
*
FROM
SC
WHERE
Sno = Student.Sno AND Cno = '1');
集合查询
并操作UNION:查询语句1 UNION 查询语句2
差操作EXCEPT
交操作INTERSECT