Oracle--DML: 数据操纵语言
1.insert：添加数据到数据库中
--1.带字段名插入
NSERT INTO emp(empno, ename, job, deptno) VALUES(1001, '张三', 'clerk', 30);
--2.省略字段名插入
INSERT INTO emp VALUES(1002, '王五', 'clerk', 7698, SYSDATE, 2000, NULL, 40);
--3.通过序列完成插入
INSERT INTO emp(empno, ename, job, deptno) VALUES(empsequence.nextval, '张三', 'clerk', 30);
--隐式方式: 在列名表中省略该列的值。
INSERT INTO departments (department_id, department_name )
VALUES (30, 'Purchasing');
--显示方式: 在VALUES 子句中指定空值
INSERT INTO departments
VALUES(100, 'test', NULL, NULL);
-- rownum(行和) 伪列
select rownum, e.* from emp_bak e where rownum<=5;
向EMP表中一次性插入多条数据
--union all 关键字
insert into test3
select 1,'王五'，'北京' from dual
union all
select 2,'王六'，'北京' from dual
union all
select 2,'王六'，'北京' from dual;
--union 关键字 过滤重复
insert into test3
select 2,'王小七'，'深圳' from dual
union
select 2,'王小七'，'深圳' from dual
union
select 2,'王小七'，'深圳' from dual;
2.update：修改数据库中的数据
·1.可以更新数据表中全部数据。
·2.更新数据表中满足条件的数据。
--update 更改整列数据
update emp_bak set sal=5000;
--update 更改,更新表中满足条件数据
update emp_bak set sal=10000 where job='ANALYST';
3.delect：删除数据库中的数据
1.可以删除数据表全部数据。
2.删除数据表中满足条件的数据。
基本语法:
DELETE FROM 表名 WHERE 条件;
--删除表
delete from book;
--删除数据(整列数库/满足条件数据)
delete from book where adds='武汉市';
4.select：选择（查询）数据）
SQL语言的基础，最为重要。数据查询语言，用来查询数据的，包含select。
select是SQL语言中使用频率最高的语句，是SQL语言的灵魂。
select语句具有强大的查询功能。
select允许从一个表或多个表中选择满足给定条件的一个或多个行或列
基本语法：
select *| 具体的列名 别名 from 表名;
SELECT：标识选择哪些列,
FROM**：标识从哪个表中选择,
*代表全部列;
--dql:查询语言select SELECT * 具体的列名 别名 FROM 表名;
--查询整个表数据
select * from emp_bak;
--查询整个表的sal,job列
select sal,job from emp_bak ;
--查询整个表job条件为'ANALYST'的sal列;区分大小写
select sal from emp_bak where job= 'ANALYST';
-给列取名
--1.
select ename 姓名 ， sal 工资 from emp_bak;
--2.as关键字
select ename as 姓名 ， sal as 工资 from emp_bak;
--3."" 别名字符串形式,可加空格
select ename "姓名" ， sal "工资" from emp_bak;
--3."" 外加关键字as 别名字符串形式,可加空格
select ename as "姓名" ， sal as "工资" from emp_bak;
--distinct(区别的)关键字 去除重复
select distinct sal , job from emp_bak;
--连接字符||
select ename ||' is a '|| job as remark from emp_bak;
--查询员工姓名、月基本工资增长10%
select ename as 姓名,sal as 月基本工资,sal*1.1 as 增幅后的工资 from emp_bak;
--is not null不为空值 is null 为空值
--0或者空格不是空值
select * from emp_bak where comm is not null;
select * from emp_bak where comm is null;
insert into emp_bak（empno,comm）values(15,0');
--and or not（取反）
select * from emp_bak where ename='SMITH' or sal>1500;
select * from emp_bak where comm is not null and sal>=1500;
select * from emp_bak where not (sal>1500);
--between and
select * from emp_bak where sal>=1500 and sal<=3000;
select * from emp_bak where sal between 1500 and 3000;
--in
select * from emp_bak where ename='ALLEN' or ename='JONES' or ename='BLAKE';
select * from emp_bak where ename in('ALLEN','JONES', 'BLAKE');
--like模糊查询 : % 代表零或多个字符, _ 代表一个字符。
--第二个字符为'A'
select *from emp_bak where ename like '_A%';
--含字符为'A'的数据
select *from emp_bak where ename like '%A%';
--排序 desc升 asc降
select * from emp_bak order by sal desc;
select * from emp_bak order by sal asc;
--排序两个条件 --前面的为主要排序,后面为次要排序
select * from emp_bak order by deptno asc ，sal asc;
-- rownum(行和) 伪列
-- e为表emp_bak别名
select rownum, e.* from emp_bak e where rownum<=5;
--别名查询列empno
select e.empno from emp_bak e;
select empno from emp_bak;
1.交叉联接
语法：
SELECT ｛DISTINCT｝ * | 字段名1 别名1...
FROM 表名1 别名1 , 表名2 别名2，...
{WHERE 条件表达式}
{ORDER BY 排序字段1 ASC｜DESC，...}
1.笛卡尔积：
两个集合中的每一个成员，都与对方集合中的任意一个成员有关联。即第一个表的行数乘以第二个表的行数等于笛卡尔积大小。 列数据相乘
select * from emp;
select * from dept;
select e.* ,d.*
from dept d,emp e;
2.等值联接：
列数据相等的取出来
select e.* ,d.*
from dept d,emp e
where d.deptno = e.deptno;
3.自连接
select * from emp;
select e.*,m.* --e.ename , e.sal , m.ename ,m.sal
from emp e , emp m --e的数值属于 , m的数据属于n
where e.mgr=m.empno; --e表的经理(mgr)等于m表的 注意不能反
4.非等值联接联
select * from salgrade;
select e.empno , e.ename , e.sal , s.*
from emp e , salgrade s
where e.sal between s.losal and s.hisal;
练习：查询出每个雇员的姓名，工资，部门名称，工资在公司的等级（salgrade），及领导姓名及工资所在公司的等级
select * from emp; --员工工资表
select * from dept; --部门表
select * from salgrade; --工资等级表
select e.ename 员工名称 , e.sal 员工工资 , d.dname 部门名称 , s.grade 工资等级,
m.ename 经理 , m.sal 经理工资 , sm.grade 经理工资级
from emp e , salgrade s , dept d ,emp m , salgrade sm --求经理工资等级需重新取salgrade表
where d.deptno = e.deptno --等值连接部门表取经理名字
and e.sal between s.losal and s.hisal --取员工工资等级
and e.mgr = m. empno --自连接取经理工资
and m.sal between sm.losal and sm.hisal --取经理工资等级
decode函数：
select *from emp;
select e.ename,e.sal ,s.grade,decode(s.grade,
1,'一等',
2,'二等',
3,'三等',
4,'四等',
5,'五等'
) 工资等级
from emp e,salgrade s
where e.sal between s.losal and hisal;
case表达式
select * from salgrade;
select s.*,case s.grade
when 1 then '一等级'
when 2 then '二等级'
when 3 then '三等级'
when 4 then '四等级'
when 5 then '五等级'
else ''
end 工资等级
from salgrade s;
2.内联接
含义:内连接只返回满足连接条件的数据。
内连接中是以具有关联关系（相同名字）的列为连接条件的；
可以使用ON 子句指定额外的连接条件；
l这个连接条件是与其它条件分开的；
ON 子句使语句具有更高的易读性
语法:
SELECT table1.column, table2.column
FROM table1
[INNER] JOIN table2
ON (table1.column_name = table2.column_name)
[[INNER] JOIN table3
ON (table2.column_name = table3.column_name)
…];
--内连接 跟等值连接类似
select * from employees; --员工表
select * from departments; --部门表
select * from locations; --位置表
select e. first_name , d.department_name,l.city
from employees e
join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id;
3.外联接
两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外联接。 两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ，这种连接称为满外联接。
外联接分类:
左连接（左外连接）LEFT JOIN、右连接（右外连接）RIGHT JOIN、满外联接（全外连接）FULL JOIN
--左外连接 --返回满足条件的数据,外加左边(d)表 不满足条件的数据
select * from emp;
select * from dept;
select * from dept d
left outer join emp e
on e.deptno=d.deptno;
--右外连接 --返回满足条件的数据,外加右边(e)表 不满足条件的数据
select * from emp;
select * from dept;
insert into emp(empno,ename,sal) values(8000,'JAMES',4300)
select * from emp e
left outer join dept d
on e.deptno=d.deptno;
--全外连接 --返回满足条件的数据,外加右边(e)表,左边(d)表所有不满足条件的数据 (排列:左不满足+满足+右不满足,)
select * from emp;
select * from dept;
select * from emp e
full outer join dept d
on e.deptno=d.deptno;
4.聚合函数
--聚合函数
--count ()求总数
select count（*） from emp;
--max()最大数
select max（sal） from emp;
--min()最大数
select min（sal） from emp;
--avg()平均数
select avg（sal） from emp;
--sum求和
select sum（sal） from emp;
--求某部分工资综合 nvl函数 nvl(列名,0)>>当列值为null的时候,判断为0;(null和数值相加为null)
select * from emp;
select sum（sal+nvl（comm，0）） from emp where emp.deptno=20;
5.分组查询 group b
GROUP BY意为“根据（by）一定的规则进行分组（Group）”。
其作用是通过一定的规则将一个数据集划分为若干个小的区域，然后针对若干个小区域进行统计汇总。
注意
1.分组查询的列只能由两部分构成，一是 GROUP BY中出现的列，另外是分组函数，除此之外，其它内容不能放到select后。
2.如果不使用分组，则只能单独的使用组函数。
3.使用分组函数的时候，不能出现分组函数和分组条件以外的字段。
4.分组函数只能在分组中使用，不允许出现在WHERE语句之中。使用having，对分组后的数据进行过滤。
SELECT *｜列名
FROM 表名
WHERE 条件表达式
GROUP BY 分组条件
ORDER BY 排序列 ASC|DESC
select * from emp;
select * from dept;
select e.deptno 部门编码, count(e.deptno) 总员工,sum(e.sal) 部门总工资, avg(sal) 平均工资
from emp e,dept d
group by e.deptno;
/*显示非销售人员工作名称及从事同一工作雇员的月工资总和，
并且要满足从事同一工作的雇员的月工资合计大于5000，输出结果按月工资的合计升序排列。*/
select * from emp;
select job,sum(sal) --列只能为 group 列 和分组函数
from emp
where job <>'salesman' --不能是分函数,分组函数在having中 '<>'等值于运算符 'is not'
group by job
having sum(sal)>50000
order by sum(sal) asc;
6.子查询
在一个查询的内部还包括另外一个查询，此时这个内部**查询的语句称为子查询，外部的查询语句称为主查询;
子查询 (内查询) 在主查询之前一次执行完成;
子查询的结果被主查询使用 (外查询);
注：子查询出现的位置: FROM后或WHERE后。
基本语法:
SELECT 查询列表
FROM 表名
WHERE 列名 运算符
(SELECT select_list
FROM table);
--思路1.求出詹姆斯的数据ename='JAMES'，再求列sal 2.查询sal>('JAMES的工资')
select * from emp
where sal>(select sal from emp
where ename='JAMES')
子查询可以返回的数据类型一共分为四种：
1.单行单列：返回的是一个具体列的内容，可以理解为一个单值数据,出现几率最高
操作符 ： = 、> 、>= 、< 、<= 、<>
select * from emp e
where e.sal=(select min(sal) from emp)
2.单行子查询：返回多个列，有可能是一条完整的记录。
操作符 ： in 、=any(类似in)、>any 、< any 、>all 、<all （any其中一个数据，all所有的数据）
select * from emp e
where e.sal in ( select min(sal) from emp
group by deptno)
-->ANY 大于最小值
select * from emp
where sal <ANY ( select sal from emp
where deptno=10
)
--<ANY 大于最大值
select * from emp
where sal >ANY ( select sal from emp
where deptno=10
)
4.多行多列：查询返回的结果是一张临时表；
多行子查询
EXISTS 操作符
set操作符(交集,并集,差集)
----分页查询 1.查询工资最高的前5名的员工. 思路:先工资排序,再将表用rownum取前五
select * from employees order by salary desc;
select rownum , e.* --- rownum这是属于伪列 只能等于1或者<=某个数 ，不能 between and 两个间值
from (select * from employees order by salary desc) e
where rownum<=5;
--2..查询工资最高的第6到第12条员工
--方法一
select * from employees order by salary desc;
select * from (select rownum rn , e.*
from(
select * from employees order by salary desc) e) em
where em.rn between 6 and 12;
--方法二
select * from (select rownum rn , e.*
from(
select * from employees order by salary desc) e) em
where em.rn >=6 and em.rn <=12;
--方法三 先求伪劣12行以内 ,再再外面加表
select * from employees order by salary desc;
select * from (select rownum rn,e. * from (
select * from employees order by salary desc) e
where rownum<=12) --伪列不能用别名,在外层可以用别名
where rn>=5
--利用in查询表中是部门经理的员工
select * from emp where empno in (
select mgr from emp
)
--利用exists(存在的量词)查询表中是部门经理的员工 --not exists不是部门经理的员工
select * from employees e
where exists (
select 'X'
from departments d
where e.employee_id = d.manager_id) --内外量表相互查
--set操作符
select * from employees;
select * from departments;
--union 并集 (不除重复)
select department_id from employees group by department_id
union
select department_id from departments group by department_id;
--union all 并集 (除重复)
select department_id from employees group by department_id
union all
select department_id from departments group by department_id;
--intersect --交集
select department_id from employees group by department_id
intersect
select department_id from departments group by department_id;
--minus --差集 返回上面减下面后,上边剩下的数据
select department_id from employees group by department_id
minus
select department_id from departments group by department_id;
-查询部门名称，部门员工数，部门平均工资，部门的最低收入雇员的姓名
select * from dept;
select * from emp;
/*思路 1.先求部门编码e.deptno.,部门员工数 e.c，部门平均工资 e.a，部门的最低收入e.es
(select deptno ,count(*), avg(sal),min(sal)
from emp
group by deptno;) e
2.等值连接dept和e表，d.deptno=e.deptno 得出部门名称d.dname
3.等值连接emp em 和e表 ， e.es=em.sal（工资值相等） 得出最低工资（e.es）员工 em.ename
*/
--方法一
select d.dname , e.c , e.a , e. es , em.ename
from dept d,（select deptno ,count(*) c , avg(sal) a , min(sal) es
from emp
group by deptno) e
,emp em
where d.deptno=e.deptno
and e.es=em.sal
--等同方法一
select d.dname,e.c,e.a,e.mi,em.ename
from (select deptno,count(*) c,avg(sal) a, min(sal) mi
from emp
group by deptno) e , dept d,emp em
where e.deptno=d.deptno
and e.mi=em.sal;
--方法二 --将dept表 加入e表
select * from dept;
select * from emp;
select e.dna , e.c , e.a , e. es , em.ename
from （select d.dname dna ,count(*) c , avg(sal) a , min(sal) es
from emp,dept d
where emp.deptno=d.deptno
group by d.dname) e
,emp em
where e.es=em.sal
