数据库基础指令必背

–注释 只是为了给人来阅读,编译器会忽略的文本
create user lisi identified by 123456 account unlock;
–创建用户lisi,密码为123456,账户未锁定,代表可以登录. 这里会登录失败,因为没有权限
grant connect,resource to lisi;
–给lisi用户连接数据库,使用资源的权限.
/*
多行注释
多行注释
*/
alter user lisi identified by 666666;
–修改用户lisi的密码为6个6. 这个操作可以是system用户,也可以是lisi用户
alter user lisi account lock;
– 修改用户lisi,将账号锁定. 例如游戏公司锁账号
alter user lisi account unlock;
–解锁lisi用户
revoke connect,resource from lisi;
–从lisi用户回收连接数据库,使用资源权限
drop user lisi cascade;
–删除用户lisi. 并且删除它下面的所有表,视图等数据.

–练习
– 新建一个用户 tom，密码设置为 love
create user tom identified by love account unlock;
– 给用户 tom 授权登录，访问资源的权限
grant connect,resource to tom;
– 锁定 tom 用户的账户，使其不能登录
alter user tom account lock;
– 给 tom 用户解锁
alter user tom account unlock;
– 收回 tom 用户的登录、访问资源的权限
revoke connect,resource from tom;
– 删除用户 tom
drop user tom cascade;

–为什么使用数据库,而不是excel来存储数据?
–因为excel使用起来效率低下,所以使用数据库

–数据库存储数据,必须先建表,才能插入数据.
create table students(
sno number(4), --数字类型,长度为4 0000~9999
sname varchar2(9), --字符类型,长度为9 9个因为字符或3个中文字符
ssex varchar2(3),
sage number(3)
);

insert into students values(1001,‘张三’,‘男’,20);
–插入一条数据到学生表. 字符类型必须使用单引号
insert into students values(1002,‘李四’,‘女’,18);

select * from students;
– 从students表,查询所有字段

rename students to stu2;
–将students修改名字为stu2

alter table stu2 add ( score number(3), money number(4) );
–修改stu2表,添加score和money字段. 没有数据那么它的内容就是NULL

alter table stu2 drop column score;
–修改stu2表,删除score字段(列)

drop table stu2;
–删除stu2表

–约束:为了保证数据的有效性,完整性而添加的限制. 例如,不能存在两条完全相同的数据
–约束并不是互斥的,可以共存.
create table students(
sno number(4) primary key, --主键约束. 每个表只能有一个主键. 这个字段的值不能重复
name varchar2(10) not null, --非空约束. 这个字段必须有值.
sex varchar2(3) check(sex=‘男’ or sex=‘女’), --检查约束. 该字段只能取这个2个值
seat number(4) unique, --唯一约束. 这个唯一可以作用于多个字段
address varchar2(50) default ‘深圳市’ --默认约束. 如果该字段没有给值,那么就使用默认值
);

insert into students values(1001,‘张三’,‘男’,100,‘北京市’);
insert into students(sno,name,sex,seat) values(1002,‘李四’,‘女’,101);
–如果插入的字段值的数和表格字段的个数不等,则不能省略字段
select * from students;

–创建从表
create table scores(
sno number(4) not null references students(sno), --这个sno作为外键,引用自students表中的sno
course varchar2(10),
score number(3)
);
insert into scores values(1001,‘语文’,80);
insert into scores values(1001,‘数学’,85);
insert into scores values(1002,‘语文’,90);
insert into scores values(1002,‘数学’,77);
–1.如果值不存在与主键中,则不能添加到从表
insert into scores values(1003,‘数学’,77);
–2.如果值已经在从表中使用了,那么无法从主表中删除
delete from students where sno = 1001;

select * from scores;

–先有表,再添加约束
alter table scores add constraint ck_scores_score check( score>=0 and score<=100 );
– 修改scores表,添加约束.约束名为ck_scores_score, 内容是score值要大于等于0,小于等于100
alter table scores add constraint fk_scores_sno foreign key(sno) references students(sno);
–修改scores表,添加约束.约束名为fk_scores_sno, 内容是外键sno,引用自students的sno

–对于null 和 default约束就是修改状态
alter table scores modify score not null;
– 修改scores表,score字段不允许为空
alter table scores modify score default 0;
– 修改scores表,score字段默认值为0

alter table scores drop constraint ck_scores_score;
–修改表,移除名为ck_scores_score的约束.

–练习
– 创建表 class_info
/*
字段名 类型 长度 可选值
c_id 字符串 20 主键
c_type 字符串 6 UI or 测试 or 开发
c_position 字符串 20 非空
start_time 日期(date)
c_status 字符串 6 在读 or 毕业
*/
create table class_info(
c_id varchar2(20) primary key,
c_type varchar2(6) check( c_type=‘UI’ or c_type=‘测试’ or c_type=‘开发’ ),
c_position varchar2(20) not null,
start_time date,
c_status varchar2(6) check(c_status=‘在读’ or c_status=‘毕业’)
);

select * from scott.emp;
– 查询scott用户下的emp表

–查询语法 select 字段,字段(或者*) from 表.
select * from emp;
–查询emp表的所有字段.
select ename,sal from emp;
–只查询姓名和薪水字段
select e.ename,e.sal from emp e;
–给emp表取别名为e. 那么前面的字段就可以使用这个别名
select ename 姓名,sal as 工资 from emp;
– 给ename和sal字段取别名.

–where 条件. 满足条件的记录会被查询出来
select * from emp where sal>=2000;

/*操作符
算数运算：+ - * /
关系运算：= > < <= >= != <>
逻辑运算：and or not
字符串连接操作符：||
/
select ename,sal2 from emp; --工资翻倍
select * from emp where job != ‘SALESMAN’; --查询工作不是SALESMAN的员工
select * from emp where sal>=1500 and sal<=3000; --查询工资在1500到3000的员工
select * from emp where not job = ‘SALESMAN’; --查询工作不是SALESMAN的员工

–order by 排序
select * from emp order by sal asc; --以工资排序.默认为升序
select * from emp order by sal desc; --以工资降序

select * from emp where sal>2000 order by sal desc;
– 先查询出2000以上工资的员工,再降序

select ‘hello’||ename from emp;
– 在每个ename前面连接一个hello字符

–distinct 消除重复行
select distinct job from emp;
–查询所有工作. 并且去重

–对null类型的判断,不能使用等于.
select * from emp where comm is null; --查询没有奖金的员工
select * from emp where comm is not null; --查询有奖金的员工

–oracle数据库,数据库名,表名,字段名都不区分大小写. 但是字段的值区分.
select * from emp where job = ‘SALESMAN’;
SELECT * FROM EMP WHERE JOB = ‘SALESMAN’;

–in 如果一个字段有多个取值,那么可以用in,替代or
select * from emp where job=‘CLERK’ or job = ‘SALESMAN’ or job = ‘MANAGER’;
select * from emp where job in (‘CLERK’,‘SALESMAN’,‘MANAGER’);
–查询工作为CLERK,SALESMAN,MANAGER的员工

select * from emp where job not in (‘CLERK’,‘SALESMAN’,‘MANAGER’);
–查询工作不为CLERK,SALESMAN,MANAGER的员工

–between and 在…之间. 用于简化区间. 包含两边边界
select * from emp where sal>=1500 and sal<=3000;
select * from emp where sal between 1500 and 3000;
–查询工资在1500到3000的员工

–like 模糊查询
–% 匹配任意个字符. 0~无穷大
–_ 匹配1个任意字符
–escape 后面会修饰一个符号. 跟在这个符号后面的统配符,不在拥有通配符效果

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 ‘_L%’;
–查询名字第2位为L的员工

select * from emp where ename like ‘%/%%’ escape ‘/’;
–查询名字中包含%的员工
select * from emp where ename like ‘%/_%’ escape ‘/’;
–查询名字中包含_的员工

– 显示薪水大于 2000，且工作类别是 MANAGER 的雇员信息
select * from emp where sal>2000 and job= ‘MANAGER’;
– 显示年薪大于 30000，工作类别不是 MANAGER 的雇员信息
select * from emp where sal12>30000 and job!= ‘MANAGER’;
– 显示薪水在 1500 到 3000 之间，工作类别以“M”开头的雇员信息
select * from emp where sal between 1500 and 3000 and job like ‘M%’;
– 显示奖金为空并且部门号为 20 或 30 的雇员信息
select * from emp where comm is null and deptno in(20,30);
– 显示奖金不为空或者部门号为 20 的雇员信息，要求按照薪水降序排列
select * from emp where comm is not null or deptno=20 order by sal desc;
– 显示年薪大于 30000 工作类别不是 MANAGER，且部门号不是 10 和 40 的雇员信息，要求按照雇员姓名进行排序
select * from emp where sal12>30000 and job != ‘MANAGER’ and deptno not in(10,40) order by ename;
– 选择在部门 30 中员工的所有信息
select * from emp where deptno = 30;
– 列出职位为（MANAGER）的员工的编号，姓名
select empno,ename from emp where job = ‘MANAGER’;
– 找出部门 10 中的经理 (MANAGER) 和部门 20 中的普通员工 (CLERK)
select * from emp where deptno=10 and job=‘MANAGER’ or deptno=20 and job=‘CLERK’;
– 找出部门 10 中既不是经理也不是普通员工，而且工资大于等于 2000 的员工
select * from emp where deptno=10 and job not in(‘MANAGER’,‘CLERK’) and sal>=2000;
– 找出没有奖金或者奖金低于 500 的员工
select * from emp where comm is null or comm<500;
– 显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select ename,hiredate from emp order by hiredate;
– 找出有奖金的员工的不同工作
select distinct job from emp where comm is not null;
– 找出姓名中不带 R 这个字母的员工
select * from emp where ename not like ‘%R%’;
– 显示所有员工，按工作降序排序，若相同，则按工资升序排序
select * from emp order by job desc,sal asc;
– 查找出不属于任何部门的员工
select * from emp where deptno is null;

–集合:可以把数据库的表当做集合,做交集,并集等运算
select deptno from dept --10 20 30 40
intersect
select deptno from emp; --10 20 30 null
–求交集

select deptno from dept --10 20 30 40
union
select deptno from emp; --10 20 30 null
–求并集

select deptno from dept --10 20 30 40
union all
select deptno from emp; --10 20 30 null
–求并集,不去重

select deptno from dept --10 20 30 40
minus
select deptno from emp; --10 20 30 null
–求补集. 减去相同,留下独有

select deptno from emp --10 20 30 null
minus
select deptno from dept; --10 20 30 40
–求补集

–多表连接查询
–交叉连接: 求笛卡尔积
/*
A(a,b,c) B(1,2,3)
a1 a2 a3
b1 b2 b3
c1 c2 c3
*/
select * from emp,dept;

–内连接
–等值连接. 将deptno值相同的合并,不同的就抛弃
select * from emp ,dept where emp.deptno = dept.deptno;
select ename,e.deptno,dname from emp e,dept d where e.deptno = d.deptno; --使用别名. 只获取想要的字段
select ename,e.deptno,dname from emp e join dept d on e.deptno = d.deptno; --专门用于连表的语法
–查询员工所在的部门名称

select ename,loc from emp e join dept d on e.deptno = d.deptno;
–练习: 查询员工所在的部门位置

–非等值连接: 一般就用于求等级. 求工资等级,学生成绩等级
select * from emp;
select * from salgrade;
select * from emp e join salgrade s on sal between losal and hisal; --如果字段没有出现在两个表中,则不需要写表名
–求员工的薪水等级

–自连接: 表格自己连自己.
select * from emp;
–员工的经理编号 = 经理的员工编号
select * from emp 员工表 join emp 领导表 on 员工表.mgr = 领导表.empno;
select 员工表.ename,领导表.ename from emp 员工表 join emp 领导表 on 员工表.mgr = 领导表.empno;
–查询员工的领导姓名

–外连接
–左外 left outer: 以左表为准.左表的内容需要全部显示,右表对应不上的数据,就表示为null
select * from emp e left join dept d on e.deptno = d.deptno;

–右外 right outer:以右表为准.右表的内容需要全部显示,左表对应不上的数据,就表示为null
select * from emp e right join dept d on e.deptno = d.deptno;

–满外 full outer: 两个表的数据都会全部显示,不会抛弃
select * from emp e full join dept d on e.deptno = d.deptno;

–子查询: 查询中还有查询
select * from emp;
select * from dept;
–查询出销售部（SALES）下面的员工信息
–1.使用连表完成
select * from emp e join dept d on e.deptno=d.deptno and dname=‘SALES’;
–2.使用子查询完成
–先找出销售部的部门编号
–再找这个部门编号下的员工
select * from emp where deptno =
(select deptno from dept where dname=‘SALES’);

–查询工作地点(LOC)在NEW YORK工作的所有员工
select * from emp e join dept d on e.deptno=d.deptno and loc =‘NEW YORK’;

select * from emp where deptno =
(select deptno from dept where loc = ‘NEW YORK’);

–any 任何一个
– <any 10 20 30 小于最高 可以确定,一定比30小
– >any 10 20 30 大于最小 可以确定,一定比10大
– =any 10 20 30 等于任意一个 可以确定他的值是10或20或30
–ex:查询emp表中比销售员（SALESMAN）最高工资低的员工信息
–先找出所有销售的工资
–然后在比最高的低
select * from emp where sal<any
(select sal from emp where job = ‘SALESMAN’);

–求工资等于scott的所有员工
select * from emp where sal =any
(select sal from emp where ename =‘SCOTT’);

select * from emp where sal in
(select sal from emp where ename =‘SCOTT’);
–总结: 如果子查询只返回一个结果,那么使用=. 如果返回多个结果,那么可以使用in或者 =any

–all 所有
– >all 大于最大 10 20 30 这个数一定比30大
– <all 小于最小 10 20 30 这个数一定比10小
–ex:查询比所有销售员薪水都高的员工信息
select * from emp where sal>all
(select sal from emp where job = ‘SALESMAN’);

– 创建一查询，显示与 BLAKE 在同一部门工作的雇员的姓名和受雇日期、部门编号，但是 BLAKE 不包含在内。
select ename,hiredate,deptno from emp where deptno =
(select deptno from emp where ename =‘BLAKE’)
and ename !=‘BLAKE’;
– 显示位置在 DALLAS 的部门内的雇员姓名、工作。
select ename,job from emp where deptno =
(select deptno from dept where loc=‘DALLAS’);
– 显示被 KING 直接管理的雇员的姓名以及薪水。
select ename,sal from emp where mgr=
(select empno from emp where ename=‘KING’);
– 创建一查询，显示能获得与 SCOTT 一样薪水的其他雇员的姓名、受雇日期以及薪水。
select * from emp where sal in
(select sal from emp where ename=‘SCOTT’)
and ename !=‘SCOTT’;
– 查找出工资等级不为 4 级的员工的员工名字，部门名字，部门位置
select * from emp e join salgrade on sal between losal and hisal
join dept d on e.deptno = d.deptno
where grade != 4;

–总结:查询语句的书写
– select 字段 from 表1 join 表2 on 表1连接表2条件
–join 表3 on 连表条件
–join 表4 on 连表条件
–where 筛选条件
–oder by 排序字段

–对于数据就4个操作. 增删改查

– 快速创建表. 通过查询语句,可以快速创建表
create table emp2 as select * from emp where sal>2000;
select * from emp2;

–复制表结构(表头). 通过永远不成立的条件,返回空表. 然后通过这个空表,创建emp3
create table emp3 as select * from emp where 1=2;
select * from emp3;

select * from dept;
–增加 insert
–1.插入时,不写字段. 则意味着值的个数,全等于表中字段个数
insert into dept values(50,‘国防部’,‘北京市’);
–2.插入时,写字段. 前面有几个字段,后面就给几个值
insert into dept(deptno,dname) values(60,‘教育部’);
–3.插入时,后面使用的是查询语句. 需要保证emp和emp3字段完全相同
select * from emp3;
insert into emp3 select * from emp where sal >=3000;

–修改 update (更新)
update emp3 set sal = 8000,comm=2000 where ename=‘KING’; --where代表条件
update emp3 set sal = 8000,comm=2000; --修改的时候,如果没有条件,则代表修改整个表

–删除 delete. 直接删除整条记录
select * from emp2;
delete from emp2 where ename=‘CLARK’; --delete后面没有字段!!
delete from emp2; --删除所有记录

–truncate 彻底删除,不能恢复
truncate table emp2;

–给销售部的所有员工加薪200块 sal = sal+200
drop table emp2;
create table emp2 as select * from emp;
select * from emp2;

update emp2 set sal = sal+200 where deptno =
( select deptno from dept where dname = ‘SALES’ );

–删除销售部所有部门信息.包含部门表和员工表
–先删除从表
delete from emp where deptno = (
select deptno from dept where dname = ‘SALES’
);
–在删主表
delete from dept where dname = ‘SALES’;

rollback;
–将 EMP 表中工资大于 2000 的员工数据，插入到 BONUS 表中
select * from BONUS;
insert into BONUS select ename,job,sal,comm from emp where sal>2000;

–事务:数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位
–可以应用于银行转账业务
/*

1. update table1 set account = account - 10000 where name=xxx
2. update table1 set account = account + 10000 where name=yyyy
3. rollback or commit
   这两个操作要么一起成功,要么一起失败
   */

–事务隐式开始于一个DML(insert,delete,update)语句.

–事务结束于 1.rollback 回滚. 撤销所有增删改操作
– 2.commit 提交. 认可这一系列操作.写入数据库.写死
–事务隐式提交. DDL(create,alter,drop)/DCL(grant,revoke)

select * from emp2;
delete from emp2; --对表进行删除操作,这个时候会开启一个事务
rollback; --通过回滚,结束事务. 撤销了所有修改操作

update emp2 set sal=10000; --再次开启一个新事务.
commit; --认可这个操作,进行提交. 写死到数据库. 不能再回滚
rollback; --无效. 因为上面的事务已经结束了

update emp2 set sal = 5000; --再开启一个事务

drop table emp3; --隐式提交,事务结束
create table emp3 as select * from emp where sal>2000; --这里创建一个表,但是会隐式执行commit,事务结束
rollback; --上面的事务已经结束,无法回滚

update emp2 set sal = 6000; --第一次修改. 开启事务
–命名规则: 只能由字母,数字,下划线组成,且开头不能是数字,也不能是关键字
savepoint A1; --设置存储点(存档)
update emp2 set sal = 8000;
savepoint A2;
update emp2 set sal = 10000;
savepoint A3;

rollback to A2; --回到A2这个存储点
rollback to A1; --可以回滚到前面的点. 允许回到过去
rollback to A3; --不能再返回后面的点. 不能重返未来

–函数: 提供具体功能的一串代码
–abs 绝对值
select abs(100),abs(-100),abs(’-56’) from dual; --100 100 56
–ceil 天花板 向上取整 大于等于该数的最小整数
select ceil(10),ceil(10.2),ceil(-10.2),ceil(‘10.2’) from dual; --10 11 -10 11
–floor 地板 向下取整 小于等于该数的最大整数
select floor(10),floor(10.2),floor(-10.2),floor(‘10.2’) from dual; --10 10 -11 10
–round 四舍五入取整
–round(5555.6666,2.1) 第二个参数如果是小数,则只取整数位. 这个整数位如果为正,代表保留几位小数进行四舍五入
– 这个整数位如果为负,代表需要对小数点左移几位进行四舍五入
select round(5555.6666), round(5555.6666,2.1),round(5555.6666,-2.1) from dual; --5556 5555.67 5600
–trunc 截取. 规则和round一致,就是不进位
select trunc(5555.6666), trunc(5555.6666,2.1),trunc(5555.6666,-2.1) from dual; --5555 5555.66 5500

–字符函数
/*
concat 字符连接
instr 查找子字符串在父字符串中的位置
length 求字符长度
lower 转小写
upper 转大写
initcap init:初始化 cap:帽子 每个单词首字母大写,其余小写
ltrim 左侧删除指定字符,默认为空格
rtrim 右侧删除指定字符,默认为空格
trim 两侧删除指定字符,默认为空格
replace 替换
substr 截取字符串
*/
select concat(‘hello’,ename),‘hello’||ename from emp; --字符串拼接
select instr(‘hello world’,‘w’) from dual; --求第二个字符串,在第一个字符里面的位置. 找不到就是0
select ename,instr(ename,‘S’) from emp; --查找每个姓名中,s的位置
select ename,length(ename) from emp; --求每个名字的字符长度. 中文的长度也是1
select ename,lower(ename) from emp; --将每个字符串转小写,非英文字符,忽略
select ename,upper(ename) from emp ;–将每个字符串转大写,非英文字符,忽略
select initcap(‘today is a nice day’) from dual; --每个单词首字母大写,其余小写
select ltrim(’ abcdefg’),ltrim(‘xxxxhelloxworldxxxxx’,‘x’) from dual; --去除字符左边的特定字符,默认为空格
select rtrim(’ abcdefg ‘) as 结果,rtrim(‘xxxxhelloxworldxxxxx’,‘x’) from dual; --去除字符右边的特定字符,默认为空格
select trim(’ abcdefg ') as 结果,trim( ‘x’ from ‘xxxxhelloxworldxxxxx’) from dual; --去除字符两边的特定字符,默认为空格
–replace(待处理字符,老字符,新字符)
select replace(‘hello world’,‘l’,‘6’) from dual; --使用新字符,替换老字符
–substr(待处理字符,起始位置,截取长度)
select ename,substr(ename,1,2) from emp; --从1开始,截取2位

–日期函数
–sysdate 当前日期
–last_day 当前日期本月的最后一天
–add_months 加月
–months_between 相隔的月数

select sysdate from dual; --查询当前日期
select last_day(sysdate) from dual; --查询本月的最后一天
select sysdate+1 from dual; --1代表1天
select sysdate+1/24 from dual; --1/24代表1小时
select sysdate+1/24/60 from dual; --1/24/60 代表1分钟
select add_months(sysdate,3) from dual; --加3个月
select months_between(to_date(‘2021-05-20’,‘yyyy-mm-dd’),sysdate ) from dual; --求相隔的月数

–oracle数据库中 存在数字类型(number),字符类型(varchar2),日期类型(date). 他们可以进转换
–to_char 转字符. 如果字符+数字 = 数字
–to_date 转日期
–to_number 转数字

select ‘5’+2 from dual; --支持字符+数字 = 数字
select ‘5’+‘2’ from dual; --数字类型字符 + 数字类型字符 = 数字
select ‘a’+‘b’ from dual; --非数字类型,不允许相加
select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual; --日期类型转字符类型
select to_char(123456789,‘L999,999,999’) from dual; --将数组转本地货币字符

select to_date(‘2021-07-08 14:22:33’,‘yyyy-mm-dd hh24:mi:ss’) from dual; --将字符串转日期

select to_number(‘456’) from dual; --字符456转数字456
select to_number(‘￥123,456,789’,‘L999,999,999’) from dual; --将货币字符,转回数字

–null类型处理函数. 因为聚合函数(sum,avg,count)运算时,会忽略null值

–nvl(表达式1,表达式2) 如果表达式1为null,则返回表达式2的值. 否则使用表达式1
–ex：对薪水是2000以下的员工，如果没有奖金，每人奖金100元
select emp.,nvl(comm,100) from emp where sal<2000;
–ex：对薪水是2000以下的员工，如果没有奖金，每人奖金100元，如果有奖金，则在原有基础上扣50
select emp.,nvl(comm-50,100) from emp where sal<2000;

–nvl2(表达式1,表达式2,表达式3) 如果表达式1为null,则返回表达式3,否则使用表达式2
–ex：对薪水是2000以下的员工，如果没有奖金，每人奖金100元
select emp.,nvl2(comm,comm,100) from emp where sal<2000;
–ex：对薪水是2000以下的员工，如果没有奖金，每人奖金100元，如果有奖金，则在原有基础上扣50
select emp.,nvl2(comm,comm-50,100) from emp where sal<2000;

select * from emp;
– 找出每个月倒数第三天受雇的员工（如：2009-5-29）
select * from emp where hiredate = last_day(hiredate) -2;
– 找出 25 年前雇的员工
select * from emp where months_between(sysdate,hiredate)/12 >=25;
– 所有员工名字前加上 Dear , 并且名字首字母大写
select concat(‘Dear’,initcap(ename) ) from emp;
– 找出姓名为 5 个字符的员工
select * from emp where length(ename) =5;
– 显示所有员工的姓名的第一个字
select substr(ename,1,1) from emp;
– 找到 2 月份受雇的员工
select * from emp where to_char(hiredate,‘mm’) =2;
– 对薪水是 2000 元以下的员工进行查询，如果没有发奖金，每人奖金 100 元。
select ename,comm,nvl(comm,100) from emp where sal<2000;
– 对 EMP 表中薪水为 2000 元以下的员工进行查询，如果没有奖金，则显示奖金为 200 元，如果有奖金，则在原来的奖金基础上加 100 元。
select ename,comm,nvl2(comm,comm+100,200) from emp where sal<2000;

–聚合函数又称分组函数. 它们都会忽略null
/*
avg 平均值
sum 求和
min 最小值
max 最大值
count 计数
*/

select count(comm),avg(comm),sum(comm) from emp;
–统计有奖金的人数,求平均奖金,求总奖金. 他们都会忽略null
select avg( nvl(comm,0)) from emp;
– 如果comm为null,那么换成0,来做平均. 这样就会被忽略了

select sum(sal),avg(sal),min(sal),max(sal),count(sal),count(*) from emp;
–统计工资总和,平均工资,最小工资,最多工资,有工资的人, 记录数

–聚合函数不能在where后面使用. 聚合函数只能使用在select后面和having后面
–求emp表中最高工资的员工
select * from emp where sal =
( select max(sal) from emp );

–group by 分组. 将字段的相同值归为一组. 用什么分组,那么select后面就只能跟分组字段+聚合函数
– 求各部门的工资总和
select deptno,sum(sal) from emp group by deptno;

–ex：求各部门的平均薪水
select deptno,avg(sal) from emp group by deptno;
– 求各工作的平均工资
select job,avg(sal) from emp group by job;
– 求被king所管理的员工的平均工资
–1.求出king的员工编号
–2.通过mgr进行分组.然后在通过having筛选king的mgr
select mgr,avg(sal) from emp group by mgr having mgr=
(select empno from emp where ename = ‘KING’);

–having 分组后筛选. having后面可以使用聚合函数
–ex：求各部门的平均薪水大于1700的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>1700;

–ex：求除了部门20之外，各部门平均薪水大于1700的部门
–1.先排除20号部门,再求平均
select deptno,avg(sal) from emp where deptno != 20 group by deptno having avg(sal)>1700;

–2.先求平均,再排除20
select deptno,avg(sal) from emp group by deptno having avg(sal)>1700 and deptno != 20;

–sql命令的书写顺序: select ->from-> join on-> where ->group by->having ->order by
–sql命令的执行顺序: from-> join on-> where ->group by->having ->select ->order by

– 列出至少有一个员工的所有部门信息.
select * from emp
select * from dept
select dname from dept where deptno in (select deptno from emp group by deptno having count(deptno)>=1);
– 查询出 KING 所在部门的工作年限最大的员工名字
select deptno from emp group by MGR having HIREDATE =
(select ename from emp where ename = ‘KING’);
– 算出部门 30 中得到最多奖金的员工姓名
select ename from emp where deptno = 30 and comm= (select max(comm) from emp where deptno = 30);
– 统计各部门下平均工资大于 500 的部门
select deptno from emp group by deptno having avg(sal) > 500
– 统计各工作下平均工资大于 500 的工作
select job,avg(sal) from emp group by job having avg(sal)>= 500;

select avg(sal) from scott.emp where sal > 500 group by deptno