基础sql语句

聚合函数
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)