聚合函数
COUNT(*)计算元组个数
COUNT(列名)对一列中的值计算个数
SUM(列名)求某一列值的总合
AVG(列名)求某一列值的平均值
MAX(列名)
MIN(列名)
--按照本门号从高到底,工资从高到底查询员工表
select * from personnel order by section desc,sal desc;
--求各个部门工资的平均值
select s.name, avg(p.sal)
from personnel p
inner join section s
on p.section = s.id
group by s.name
--求各个部门工资的和
select s.name, sum(p.sal)
from personnel p
inner join section s
on p.section = s.id
group by s.name
--求工资大于财务部平均工资的员工的信息
select *
from personnel
where sal > (select avg(sal)
from personnel
where section = (select id from section where name = '财务部'))
--查询寻所有部门4的工作岗位,在根据工作岗位查询所有在这些工作呢岗位工作呢的员工
select p.name,p.sal,j.name, s.name
from personnel p
inner join job j
on p.job = j.id
inner join section s
on p.section = s.id
where p.job in (select job from personnel where section = 4 group by job)
--各部门工资大于部门平均工资的员工信息
select *
from personnel p
where sal > (select avg(sal) from personnel where p.section = section);
--各部门工资大于部门平均工资的员工数量和部门编号
select count(*),p.section
from personnel p
where sal > (select avg(sal) from personnel where p.section = section) group by p.section;
--排序
select * from personnel order by id desc
--插入
insert into section (id,name) values(5,'市场部');
--删
delete section where id = 5;
--改
update section set name = '尼玛' where id = 5;
--分页
select *
from (select e1.*, rownum r
from (select * from employee) e1
where rownum < 5)
where r > 2
--别名
select s.name as "部门",avg(sal) as "平均工资"
from personnel p
inner join section s
on p.section = s.id
group by s.name
--建表
create table text(
id number(6) primary key,
name varchar2(20) not null,
age number(6) not null,
manager number(6)
)tablespace users;
--插入数据
insert into text (id,name,age,manager) values (106,'A',30,104);
insert into text (id,name,age,manager) values (109,'B',19,104);
insert into text (id,name,age,manager) values (104,'A',20,111);
insert into text (id,name,age,manager) values (107,'D',35,109);
insert into text (id,name,age,manager) values (112,'E',25,120);
insert into text (id,name,age,manager) values (119,'F',45,null);
//选修了java课程的学生的id和姓名
select s.studentid, s.name
from student s
inner join sc x
on s.studentid = x.studentid
where x.courseid = (select courseid from course where coursename = 'Java');
--查看熊敏选修了那几门课程
select c.coursename
from course c
inner join sc x
on c.courseid = x.courseid
where x.studentid = (select studentid from student where name = '熊敏');
--打印选课数大于3的学生的id和姓名
select stu.studentid, stu.name
from student stu
where (select count(*) from sc where studentid = stu.studentid) > 3;
--:列出所有年龄比所属主管年龄大的人的ID 和名字?
select em.id,em.name from text em where em.age > (select age from text where em.manager = id);
--删除相同数据
delete from text t where t.id > (select min(x.id) from text x where t.name = x.name);
--不用distinct去重复的写法
select * from employee e where id in(
select min(id) from employee where e.name = name and e.job = job
)
--用exists代替in
select * from employee e where e.section in (select id from section) order by id;
select * from employee e where exists (select id from section s where e.section = s.id) order by id;
--exists与in比较
select * from 表A where exists(select * from 表B where 表B.id=表A.id)
--这句相当于
select * from 表A where id in (select id from 表B)