sql语句讲解

select * from user_users;


create table employee
(
       empId number primary key,
       empName varchar2(30) not null,
       empSalary number not null,
       deptId number not null,
       empAge int not null,
       empSex char(3) not null
);


create table department
(
       deptId number primary key,
       deptName varchar2(30) not null
);


/*
   create sequence 序列名称
          start with 初始值
          increment by 增长值
          minvalue 指定最小值(不能小于初始值)
          maxvalue 指定最大值(最大值不能小于最小值)
          cache 指定初始化序列时候创建序列缓存数
          cycle 循环序列;
*/


-- 创建部门序列
create sequence dept_seq
       start with 1001
       increment by 1
       maxvalue 999999999
       cache 10
       cycle;
-- 创建员工序列
create sequence emp_seq
       start with 1001
       increment by 1
       maxvalue 999999999
       cache 10
       cycle;




alter table employee 
      add constraint EMP_DEPTID foreign key(deptId) references department;
      
-- 插入测试数据
insert into department values(dept_seq.nextval,'开发部');
insert into department values(dept_seq.nextval,'商务部');
insert into department values(dept_seq.nextval,'教质部');
insert into department values(dept_seq.nextval,'财务部');
insert into department values(dept_seq.nextval,'市场部');
commit;


insert into employee values(emp_seq.nextval,'张三1',1300,1001,24,'男');
insert into employee values(emp_seq.nextval,'张三2',4500,1001,32,'女');
insert into employee values(emp_seq.nextval,'张三3',8000,1001,40,'男');
insert into employee values(emp_seq.nextval,'张三4',4800,1001,31,'女');
insert into employee values(emp_seq.nextval,'张三5',2400,1001,50,'男');


insert into employee values(emp_seq.nextval,'貂蝉1',2500,1002,24,'男');
insert into employee values(emp_seq.nextval,'貂蝉2',7800,1002,32,'女');
insert into employee values(emp_seq.nextval,'貂蝉3',9000,1002,40,'男');
insert into employee values(emp_seq.nextval,'貂蝉4',10000,1002,31,'女');
insert into employee values(emp_seq.nextval,'貂蝉5',5800,1002,50,'男');


insert into employee values(emp_seq.nextval,'关羽1',4000,1003,24,'男');
insert into employee values(emp_seq.nextval,'关羽2',2500,1003,31,'女');
insert into employee values(emp_seq.nextval,'关羽3',4200,1003,43,'男');
insert into employee values(emp_seq.nextval,'关羽4',5100,1003,21,'女');
insert into employee values(emp_seq.nextval,'关羽5',3800,1003,28,'男');


insert into employee values(emp_seq.nextval,'小花1',4000,1004,24,'男');
insert into employee values(emp_seq.nextval,'小花2',2500,1004,31,'女');
insert into employee values(emp_seq.nextval,'小花3',4200,1004,43,'男');
insert into employee values(emp_seq.nextval,'小花4',5100,1004,21,'女');
insert into employee values(emp_seq.nextval,'小花5',3800,1004,28,'男');
commit;


-- 给1004号部门所工资低于4000的员工涨200块钱
update employee set empSalary=empSalary+200 where deptId=1004 and empSalary<4000;


-- 删除员工信息
delete from employee where deptId=1004


-- 查询
/*
   单表
   查询所有字段:select * from 表名
   查询指定字段:select 字段名,.... from 表名
   根据条件查询:select 字段名,.... from 表名 where 条件
   分组条件查询:select 分组字段,sum(),count(),avg(),min(),max() from 表名 where 条件 group by 分组字段 having(sum,max,min,avg,count)
   去重查询:select distinct(去重字段) from 表名
*/
--  查询所有
select * from employee;
-- 查询所有员工姓名及薪资
select empName 姓名,empSalary 工资 from employee;
-- 查询年龄大于40岁的所有员工
select * from employee where empAge>40;
-- 查询每个部门的员工的最高，最低，平均，总工资，员工数
select deptId 部门编号,
       sum(empSalary) 总工资,
       max(empSalary) 最高工资,
       min(empSalary) 最低工资,
       avg(empSalary) 平均工资,
       count(*) 部门人数
       from employee group by deptId;
-- 查询部门平均工资低于2500部门编号
select deptId 部门编号,
       sum(empSalary) 总工资,
       max(empSalary) 最高工资,
       min(empSalary) 最低工资,
       avg(empSalary) 平均工资,
       count(*) 部门人数
       from employee group by deptId
       having(avg(empSalary)<=4000);
-- 查询所有员工所在的部门编号
select distinct(deptId) from employee order by deptId desc;
/*
   子查询
   子查询作为列:select (select 字段 from 表名),字段 from 表名
   子查询作为条件:select * from 表名 where 字段 [in|=]
   子查询作为表:select * from (select * from 表名) a;
*/
-- 查询员工所在部门名称
select distinct (select dept.deptName from department dept where dept.deptid=emp.deptId) from employee emp;
-- 查询没有员工部门
select * from department where deptId not in (select distinct deptId from employee);
-- 做分页(伪列[职位小于等于某个值])
select * from (select rownum rn,emp.* from employee emp where rownum<=3*5) a where rn>(3-1)*5;


/*
   多表
   左连接:select * from 表名 left join 表名 on 连表条件
   右连接:select * from 表名 right join 表名 on 连表条件
   内容连接:select * from 表名 inner join 表名 on 连表条件
*/
-- 左连接(以左边表作为参照)
select * from employee e left join department d on e.deptId = d.deptId;
select * from employee e,department d where  e.deptId = d.deptId(+);
-- 左连接(以右边表作为参照)
select * from employee e right join department d on e.deptId = d.deptId;
select * from employee e,department d where  d.deptId=e.deptId(+);
-- 全连接(两张表的数据之积)
select * from employee,department;
-- 内连接
select * from employee e inner join department d on  e.deptId = d.deptId;
select * from employee e,department d where  e.deptId = d.deptId;