--查询列

Select t.sname,t.ssex,t.class from student t       --t 别名;

Select *from student t;   --* 代表查询表内所有数据

Select *from student t where t.sclass='';  --规定判断条件t.sclass='95031'，用where连接

Select t.sname as 姓名,t.ssex 性别,t.class 班级 from student t where t.sclass='';     --将列名翻译成汉文

Select t.sname||t.ssex||t.class from student t where t.sclass='' or t.ssex='女';      --||代表将查询数据连接

--升降续查询

select * from STUDENT t order by sclass;  --order by 升序排列，  默认的加上asc

select * from STUDENT t order by sclass desc;  --order by sclass desc   降序排列，

select * from STUDENT t order by sclass desc,sno asc;  --同时进行多个字段进行排序，先对sclass进行降序，在对sno升续排列

Select distinct t.depart t.prof from teacher t;    --distinct使depart字段与Prof字段不重复列查询出来

--关系查询

Select * from student t where t.sno in (select e.sno from score e where degree between 60 and 80);  --degree between 60 and 80在degree列，取在60到80之间成绩学生信息，

Select * from student t where t.sno in (select e.sno from score e where degree in(85,86,88));   --degree in(85,86,88)  在degree列取85,86,88的成绩学生信息 --in() 子查询

--模糊查询

select * from STUDENT t where sname like '王%';        --查询STUDENT表中性王的人的信息，%代表任意数量   关键字like

select * from STUDENT t where sname not like '王_';        --下划线_代表一个字符，查询姓王的两个字的名字，

--限制返回记录条件

select * from score where rownum=1;                    --rownum=1 伪列，在列表中是隐藏的

select * from (select * from score order by degree desc) where rownum=1;        --from后也可以加结果集，作为虚拟表

--分组查询 group by

--按照课程编号分组，在计算每组平均值

select cno,avg(degree),count(1) from score t group by cno;

--连接查询

--内连接查询

select Sname,cno,degree from score s,student t where t.sno=s.sno;         --将两个表score，student表创建练习。

select Sname,cno,degree from score s join student t on t.sno=s.sno;        --标准内连接格式

--外联接查询

select Sname,cno,degree from score s left join student t on t.sno=s.sno;         --left join 左外联，将左侧表内数据对不上的信息查询出来，

select Sname,cno,degree from score s left join student t on t.sno=s.sno;          --right join右外联，将右侧表内数据对不上的信息查询出来，

--全连接

select Sname,cno,degree from score s full join student t on t.sno=s.sno;          --full join全连接，将左，右两侧表内数据的信息全部查询出来，