1、查询student表中的所有数据
select * from student;
2、从course表中查询cid、cname、choure、tname;
select cid , cname , chour , tname from course ;
3、对course的列进行重命名
select cname as '课程名称' , tname as '教师名称' from corese;
4、选择前3条记录
select * from student where rownum < 4;
选择后3条记录
select * from student where rownum < 4 order by sid desc;
5、排序
从小到大
select * from course order by char asc;
从大到小
select * from course order by char desc;
6、统计记录数
select count(*) from student;
select count(sid) from student;
select count(ssex) from student where ssex='m';
统计年龄在20岁以下的人数
select count(sage) from student where sage<20;
统计20岁以下及30岁以上的人数
select count(sage) from student where sage<20 or sage>30;
统计20岁到30岁之间的人数
select count(sage) from student where sage>20 and sage<30;
select count(sage) from student where sage between 20 and 30;
7、查询姓名为“宋峰”的老师
select * from course where tname="宋峰";
查询姓“吴”的老师
select * from course where tname="吴%";
查询最后一个字是“红”的老师
select * from course where tname="%红";
查询包含“小”的老师
select * from course where tname="%小%";
查询姓名为3个字的老师
select * from course where tname="_ _ _";
查询姓陈并且后面跟两个字的老师
select * from course where tname=" 陈_ _";
8、聚合函数count、max、min、sum、avg
查询课程的数量
select count(*) from course;
查询年龄最大的学生
select * from student where ( select max(age) from student );
查询年龄最小的学生
select * from student where ( select min(age) from student );
统计课时的平均数
select avg(choure) from course
9、嵌入查询
查询比年龄最小的男生都大的学生
select * from student where age > ( select min(age) from student where six = 'm' );
查询比年龄最大的男生都大的学生
select * from student where age > ( select max(age) from student where six = 'm' );
10、内连接
查询出所有人的订购
select Persons.LastName, Persons.FirstName, Orders.OrderNo
from Persons
inner join Orders
on Persons.Id_P=Orders.Id_P
oyder by Persons.LastName
