-- method1
select t.tno,tn,cno
from t,tc
where (t.tno=tc.tno) and (tn='XX') -- 连接条件、查询条件
-- method2
select t.tno,tn,cno -- 前缀指名tno为t表属性
from t inner join tc -- inner join 内连接关键字
on t.tno=tc.tno -- 连接条件
where (tn='XX')
-- method3
select r1.tno,r2.tn,r1.cno -- 授课关系 教师号、教室关系 姓名、授课关系 课程号
from
(select tno,cno from tc) as r1
inner join
(select tno,tn from t where tn='XX') as r2
on r1.tno=r2.tno

select *
from tb_student inner join tb_score -- from 表1名称 连接类型(内连接 inner join) 表2名称
on tb_student.sno=tb_score.sno -- 同名属性需要加表名称(否则会出错,系统无法识别)

select *
from tb_student sc inner join tb_score s -- 给表起别名，简化代码量
on sc.sno=s.sno

select *
from tb_student s,tb_score sc
where s.sno=sc.sno

select sno,sc.cno,cn,score
from tb_score sc, tb_course c
where sc.cno=c.cno

select s.sno,sn,cn,score
from tb_student s join tb_score sc
on (s.sno=sc.sno) join tb_course c
on (sc.cno=c.cno)

select s.sno,sn,cn,score
from tb_student s,tb_score sc,tb_course c
where s.sno=sc.sno and sc.sno=c.cno

-- left join
select * from tb_student s left join tb_score sc
on (s.sno=sc.sno)

-- right join
select * from tb_score sc right join tb_student s
on (sc.sno=s.sno)

-- full join
select * from tb_student s full join tb_score sc
on (s.sno=sc.sno)

-- method1 
select x.tn,x.sal as sal_a,y.sal as sal_b
from t as x,t as y -- self connect
where x.sal > y.sal and y.tn='XXX'
-- method2 inner join1
select x.tn.x.sal,y.sal
from t as x,t as y
where (x.sal>y.sal) and y.tn='XXX' -- where ... and
-- method3 inner join2
select x.tn,x.sal,y.sal
from t as x inner join t as y
on x.sal > y.sal and y.tn='XXX' -- inner join ... on
-- method4 inner join3
select r1.tn,r1.sal,r2.sal
from
(select tn,sal from t) as r1
inner join
(select tn,sal from t where tn='XXX') as r2
on r1.sal > r2.sal -- inner join ... on

select tno,tn
from t
where prof=(select prof
      from t
      where tn='XXX')

select tno,tn
from t
where prof=‘子查询 prof 值’

select tn -- 姓名
from t 
where (tno = ANY (select tno -- 教师号
          from tc
          where cno='c5')) -- 课程号

select tn -- 姓名
from t 
where (tno IN (select tno -- 教师号
          from tc
          where cno='c5')) -- 课程号

-- 使用 ALL
select tn,sal -- 姓名、工资
from t
where (sal > ALL (select sal
            from t
            where dept='计算机'))
    and (dept <> '计算机') -- 父级条件限制 “其他系” 也即为：不属于 ”计算机系”
-- 使用聚合函数 MAX()
select tn,sal 
from t
where (sal > (select MAX(sal)
        from t
        where dept='计算机'))
    and (dept <> '计算机') 

select sno,sum(score)
from tb_a
where(sno='001')
group by sno
UNION
select sno,sum(score)
from tb_b
where(sno='002')
group by sno

selelct sno,sum(score) -- 学号,总成绩
into tb_b 
from tb_a
group by sno -- 按照学号分组