数据库笔记

select empno 员工编号, ename "员工姓名", 
job as 工作, mgr as "领导编号"  from emp

select empno || '的姓名是' || ename as "信息" from emp;

--单字段去重
select distinct job from emp


--多字段去重,两个字段的值都相同才去除
select distinct job mgr from emp

--单字段排序
select * from emp order by empno asc


--多字段排序
--先按照之前的字段排序，之前的字段值相同的时候再按照之后的排序
select * from emp order by ename, job desc

--查询出来的工资加100， 工资加奖金两个字段值相加
select ename, sal+100，sal+comm from emp

select * from emp where ename like '%S%' --包含S的
select * from emp where ename like 'S%' --以开头是S的
select * from emp where ename like '_S%' --第二个字符是S的
select * from emp where ename like '%/_%' escape '/'
--包含_的, 变为转义字符

--查询平均工资
select avg(sal) from emp;


--查询公司有多少工作种类
select count(distinct job) from emp;

--to_number(数值类型的字符) 把字符转换为数值
select to_number('123')+2 from emp

--查询不同部门的最高工资
select deptno, max(sal) from emp group by deptno;

--查询不同部门不同工作的人数
select deptno, job, count(*) from emp 
group by deptno,job

--查询不同部门不同工作的人数并且人数大于1的信息
select deptno, job, count(*) from emp 
group by deptno,job having count(*) > 1;

select * from emp, dept;

select * from emp e,dept d where e.depno = d.depno

--查询员工工资等级
select * from emp e, salgrade s 
where e.sal >= s.losad and e.sal <= s.hisal

--查询上级领导姓名，查询不能为*
select e1.name from emp e1, emp e2 
where e1.mgr = e2.empno

select * from emp cross join dept;

--先做笛卡尔积，然后按照所有同名同值字段进行等值筛选。
select * from emp natural join dept;

select * from emp inner join dept using(deptno);

select * from emp inner(可以省略) join dept 
on emp.deptno = dept.deptno;
--on关键字仅仅来设置等值条件，尽量不要添加其他的筛选条件

--左外连接
--查询员工姓名，工作，部门名称以及没有部门的员工信息
select * from emp e left join 
dept d on e.deptno = d.deptno

--右外连接
--查询员工姓名，工作，部门名称以及没有员工的部门信息
select * from emp e right join 
dept d on e.deptno = d.deptno

--查询员工姓名，工作，部门名称以及
--没有员工的部门信息和没有部门的员工信息
select * from emp e full outer join
 dept d on e.deptno = d.deptno

select * from emp e,dept d,city c where 
(e.deptno = d.deptno and d.loc = 
c.loc and sal > 2000)
 or (e.deptno = d.deptno and 
 d.loc = c.loc and e.comm is not null)
--方便书写，阅读困难

select * from emp e
inner join dept p on e.deptno = p.deptno
inner join city c on d.loc = c.loc
where e.sal > 2000 or e.comm is not nuill
--书写麻烦，关键词多,阅读方便

--查询所有比雇员“clack”工资高的员工信息
select * from emp where sal > 
(select sal from emp where name = "clack")


--查询工资高于平均工资的员工信息
select * from emp where sal > 
(select avg(sal) from emp)


--查询和soctt属于同一个部门并且工资低于他的员工
select * from emp where deptno = 
(select deptno from emp where name = "soctt") 
and sal < (select sal from emp where name = "soctt")


--查询工资最高的员工
select * from emp where sal = 
(select max(sal) from emp)

--查询工资高于任意一个clerr的所有员工
select * from emp where sal > 
any (select sal from emp where job = "clerr")


--相当于 select * from emp where sal > 
(select min(sal) from emp where job = "clerr")

--查询所有工资高于saleman的员工信息
select * from emp where sal > 
all(select sal from emp where name = "saleman") 


--相当于 select * from emp where sal > 
(select max(sal) from emp where name = "saleman")

--列出所有部门的详细信息和部门人数
select * from dept d, 
(select deptno, count(e.empno) from emp e, dept d 
where e.deptno(+) = d.deptno group by deptno) s 
where d.deptno = s.deptno


--平均工资大于2000的部门的详细信息
select * from dept d, 
(select deptno, avg(sal) from emp group by deptno) s 
where d.deptno = s.deprno and sal > 2000

--查询员工信息的6-10条数据，第二页
select rownum, t.* from 
(select rownum r, e.* from emp e 
where rownum <= 10) t where r > 5


--每页显示m条数据，查询第n页
select * from (select rownum r, e.* from 表名 e 
where rownum <= m*n) t where r > m*n-m


--分页查询员工信息按照工排序,显示第二页数据
select * from (select rownum r, t.* from 
(select * from emp order by sal) t where 
rownum <= 10) where r > 5

create table students(
  sno number(10) primary key, --主键约束
  sname varchar2(100) not null, --非空约束
  sage number(3) check(sage <150 and sage > 0), --检查约束
  ssex char(4) check(ssex='男' or ssex='女')
  sqq varchar2(30) unique, --唯一约束
  cno number(10) references clazz(cno) --外键约束，防止添加不存在班级编号
  --constraints pk_students_sno primary key(sno) 
  --constraints ck_students_sname check(sname is not null)
  --constraints ck_students_sage check(sage<150 and sage>0)
  --constraints un_students_sqq unique(sqq)
  --constraints fk_students_cid foreign key(cno) reference clazz(cno)
)