sql基础3多表查询

主键 外键

主键：能够唯一表示数据表中每个记录的字段，唯一识别一个表，不能为空值，作用是将记录和存放在其他表中的数据进行关联，一般没有具体意义，如身份证号
外键：表A、B，字段C是A的主键，C也在表B中，则C是B的外键。

union

union会把重复值删掉，union all把所有记录返回，且效率高于union
select id , name,age from student
union
select name,id,age from teacher;

在联合查询中，当使用order by的时候，需要对select语句添加括号，并且与LIMIT结合使用才生效
表1：classid为1的学生id，姓名，年龄，按照年龄降序排列
(select classid,id,name,age from student where classid=1 order by age desc LIMIT 2)
UNION
(select classid,id,name,age from student where classid=2);

内连接

**连接两个不同的表,**连接表emplyee和dept，两表共有字段deptno
select * from employee join dept ON employee.deptno=dept.deptno;
select * from employee join dept using (deptno);

自连接同一张表
提取employee表id和count_id相同的员工
select * from employee m join employee n ON m.id=n.count_id;
select * from employee m ,employee n where m.id = n.count_id;

三张表连接
select * from employee a inner join employee b on a.id=b.count_id inner join dept c on b.deptno=c.deptno;

外连接

select field1,…fieldn from table [left|right] [outer] join table2 on join_condition;
select * from employee left join on dept using (deptno);
select * from employee f RIGHT OUTER JOIN employee j on f.id=j.count_id;

子查询

当一个查询是另一个查询的条件时，这个查询就是子查询

单行单列
查询比smith薪资高的员工信息
select * from employee where sal > ( select sal from employee where name=“smith” )
第一步：查询smith的薪资

单行多列
查询员工中职位和工资与smith一样的全部员工信息
select nam,sal,job from employee where job,sal= (select job,sal from eployee where name=“smith”);
#第一步 查询smith的工资和职位

多行单列
从employee表中查询dept表中存在的部门编号deptno
select * from emloyee where deptno in ( select deptno from dept);

从employee表部门编号deptno,要求该编号不在dept表中
select * from employee where deptno not in (select deptno from dept);

查询工资最高的员工信息
select * from employee where sal = (select MAX(sal) from employee )

**=any : 和in关键字一样

any：比子查询中返回数据记录中的最小还要大的数据
<any：比子查询中返回数据记录中的最大还要小的数据**

查询员工的姓名和工资，这些雇员的工资不低于职位为manager的工资
select name,sal from employee where sal >any(select sal from employee where job=“manager”);

all关键字 >all大于所有 <all小于所有
查询雇员的姓名和工资，这些雇员的工资高于职位为manager的工资
select nam,sal from employee where sal >all( select sal from employee where job = “manager”);

练习

1、学生表 student(sid,sname,sage,ssex)
create table student (sid varchar(10), sname varchar(10), sage datetime, sex varchar(10));
insert into student values( “01” , “张三”, “1998-09-09”,“男”)
2、课程表 course(cid, cname, tid)
create table course ( cid varchar(10) , cname varchar(10) , tid varchar(10));
insert into course values( “01” , “语文”,“03”);
3,教师表teacher(tid,tname)
create table teacher( tid varcha(10) , tname varchar(10))
insert into teacher values( “01” , " 张四")
4,成绩表sc(sid, cid, score)
create table sc ( sid varchar(10), cid varchar(10), score (float) );
insert into sc values(“01”, “01”,80);