--rownum伪列
select rownum ,empno,ename,sal
from emp
where deptno=20;
--查询某表中的前五条数据（TOP N查询）
select rownum ,e.*
from emp e
where rownum<=5;
--rowid伪列
select rowid,rownum,e.*
from emp e;
--单行函数
select ename,upper(ename),lower(ename)
from emp;
--多行函数
select count(ename) from emp;
/*
字符函数
*/
--1.lower,upper:转换大小写
select lower(ename),upper(ename) from emp;
--2.initcap:单词首字母大写，其余小写
select initcap('this is a book') from dual;--This Is A Book
--3.concat(字符串1，字符串2)：拼接字符串
select concat('hello','world') from dual;--helloworld
--4.substr(字符串，起始位置1[,截取个数])：截取一个字符串的子串,其实位置可以为负数，右数第n位
select substr ('abcdefg',3) from dual;--cdefg
select substr ('abcdefg',3,2) from dual;--cd
select substr('abcdefg',-3) from dual;--efg
select substr ('abcdefg',-3,2) from dual;--ef
--5.length:返回字符串长度
select length('abcd') from dual;--4
select length('') from dual;--null
--6.instr(字符串，查找子串[,起始位置[，第几次出现]])：查找字符串中子串的起始位置，如果找不到返回0
--起始位置也可以是负数（从右向左反向搜索）
select instr('abcdef','cd') from dual;--3
select instr ('abcdef','cdf') from dual;--0
select instr('ancdcdcd','cd') from dual;--3
select instr('ancdcdecd','cd',1) from dual;--3
select instr('ancdcdecd','cd',-1) from dual;--8
select instr('ancdcdecd','cd',-2) from dual;--8
select instr('ancdcdecd','cd',-1,2) from dual;--5
select instr('ancdcdecd','cd',1,2) from dual;--5
select instr('123456','34',1) from dual;--3
--7.lpad(字符串，固定长度，填充字符)：左填充
-- rpad:(字符串，固定长度，填充字符)：右填充
select lpad('abcd',7,'*') from dual;--***abcd
select lpad('abcd',3,'*') from dual;--abc
select rpad('abcd',7,'*') from dual;--abc***
select rpad('abcd',3,'*') from dual;--abc
select lpad('',3,'*') from dual;--null
select lpad(' ',3,'*') from dual;--*
select lpad('',3,'*') from dual;--null
select lpad('abcd',0.9,'*') from dual;--null
--8.trim(关键字 from 字符串)：修剪字符串两边的关键字。
select trim ('a' from 'aabsdgbaa') from dual;--bsdgb
select 123 || ltrim (' aabsdgbaa ')||'123' from dual;--123aabsdgbaa 123
select 123 || rtrim (' aabsdgbaa ')||'123' from dual;--123 aabsdgbaa123
select ltrim ('aabsdgbaa','a') from dual;--bsdgbaa
select rtrim ('aabsdgbaa','a') from dual;--aabsdgb
--9.replace(字符串，查找字符串[,替换字符串])：替换字符串中的子串，默认是替换为空字符串。
select replace('abcdecde','cd') from dual;--abee
select replace('abcdecde','cd','*') from dual;--ab*e*e
--10.chr:把编码转换为字符。
select chr(97) from dual;--a
--11.ascii:把字符转换为编码
select ascii('A') from dual;--65
/*
数学函数
*/
--1.round(数字[,小数位数])：按照指定小数位数，四舍五入，默认保留到整数位。
select round(3.1415927) from dual;--3
select round(3.5415927) from dual;--4
select round(3.5415927,2) from dual;--3.54
--2.trunc(数字[,小数位数])：截断到指定位数，不四舍五入，默认保留到整数位。
select trunc(3.14158) from dual;--3
select trunc(3.5415927) from dual;--3
select trunc(3.5415927,2) from dual;--3.54
select trunc(-3.5) from dual;-- -3
--3.floor(数字)：返回不大于本身的最大整数。
select floor(3.4) from dual;--3
select floor(-3.4) from dual;-- -4
--4.ceil(数字)：进位取整。
select ceil(3.4) from dual;-- 4
select ceil(-3.4) from dual;-- -3
--5.mod(被除数，除数)：求模
select mod(12,6) from dual;--0
select mod(12,5) from dual;--2
/*
日期函数
*/
--1.sysdate:返回当前系统的日期时间
SELECT SYSDATE FROM dual;--2017-11-13 19:06:12
--2.日期类型和数字类型可以做加减运算：一个日期加减一个数字返回的还是一个日期（单位是天）
SELECT SYSDATE+3 FROM dual;--2017-11-16 19:06:26
SELECT SYSDATE-3 FROM dual;--2017-11-10 19:06:26
SELECT SYSDATE+1/24/60*25 FROM dual;--25分钟之后
--3.一个日期减去另一个日期返回的是两个日期间隔的天数
select hiredate,trunc(sysdate-hiredate) 间隔天数 from emp;
--4.months_between(日期1，日期2)：返回两个日期间隔多少个月
--5.add_months(日期，n):给一个日期加减若干个月，返回一个新日期，n为正数表示加，负数为减
select add_months(sysdate,3) from dual;--2018-02-13 19:13:46
select add_months(sysdate,-3) from dual;--2017-08-13 19:14:02
--6.next_day(日期，星期几)：返回以指定日期为准，下一个最近的星期几的日期
select next_day(sysdate,'星期六') from dual;--2017-11-18 19:15:55
--7.可以用数字1-7代表星期日-星期六
select next_day(sysdate,7) from dual;--2017-11-18 19:15:55
--8.last_day(日期)：返回指定日期的月最后一天的日期
select last_day(sysdate) from dual;--2017-11-30 19:19:27
--9.round(日期1，日期单位)：对日期进行四舍五入。
select round(sysdate) from dual;--2017-11-14
select round(sysdate,'month') from dual;--2017-11-01
select round(sysdate,'year') from dual;--2018-01-01
--10.trunc(日期[,日期单位])：对日期进行截断。
select trunc(sysdate) from dual;--2017-11-13
select trunc(sysdate,'month') from dual;--2017-11-01
select trunc(sysdate,'year') from dual;--2017-01-01
/*
转换函数
*/
--1.to_char(日期\数字，‘模式’):把一个日期或者数字按照指定模式转换为字符串。
select '现在时间：' || sysdate from dual;--现在时间：13-11月-17
select '现在时间：' || to_char(sysdate,'yyyy-mm-dd') from dual;--现在时间：2017-11-13
select '现在时间：' || to_char(sysdate,'yyyy/mm/dd') from dual;--现在时间：2017/11/13
select '现在时间：' || to_char(sysdate,'mm/dd/yyyy') from dual;--现在时间：11/13/2017
select '现在时间：' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') from dual;--现在时间：11/13/2017 19:41:16
select '现在时间：' || to_char(sysdate,'yyyy-mm-dd day') from dual;--现在时间：2017-11-13 星期一
select '现在时间：' || to_char(sysdate,'yyyy-mm-dd d') from dual;--现在时间：2017-11-13 2
select '现在时间：' || to_char(sysdate,'yyyy-month-ddspth day') from dual;--现在时间：2017-11月-thirteenth 星期一
select '现在时间：' || to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;--现在时间：2017年11月13日（双引号转义非法日期分隔符）
select '现在时间：' || to_char(sysdate,'fmyyyy-mm-dd') from dual;--现在时间：2017-11-13(fm去掉多余的前导0或者空格)
select '数字：' || to_char(&input,'fm9990.99') from dual;--最大4位整数，2位小数，个位数必须有一个数字
select '数字：' || to_char(&input,'fm9990.0099') from dual;--最大4位整数，2位小数，个位数必须有一个数字，小数必须有两位
select '数字：' || to_char(&input,'fmL9990.0099') from dual;--本地货币符号
select '数字：' || to_char(&input,'fmL9,990.0099') from dual;--千分位
--2.to_date(日期字符串，‘模式’)：把一个日期字符串按照指定模式解析为一个日期数。
--3.to_number(数字字符串，‘模式’)：把一个字符串解析为一个数字型。
select * from emp
where sal>to_number('$5,600','$9,999');
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'YYYY') year, TO_CHAR(SYSDATE,'mm') month, TO_CHAR(SYSDATE,'dd') day FROM dual;
SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd') day FROM dual;--把当前日期按照指定格式输出，结果2017-11-7
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') day FROM dual;--2017-11-07
SELECT TO_DATE('1989/09/12','yyyy/mm/dd') FROM dual;--把当前日期按照指定格式输出，结果1989/9/12
SELECT TO_DATE('1989/09/12','fmyyyy/mm/dd') FROM dual;--1989/9/12
SELECT TO_CHAR(89078907890,'L999,999,999,999,999') FROM dual;--按指定格式输出，L代表人民币，结果￥89,078,907,890
SELECT TO_NUMBER('1') + TO_NUMBER('2') FROM dual;--转为数字进行计算，结果3
SELECT '1'+'2' FROM dual;--隐式转换，结果3
SELECT concat('1','2') FROM dual;--拼接。结果12
--TO_DATE(c[,fmt[,nls]]) c则必须为大于0并小于5373484的正整数
--把数字转为日期
SELECT TO_DATE(5373484, 'J') FROM DUAL;
/*
通用函数
*/
--1.nvl(参数1，参数2)，若参数1不为null，返回参数1，参数1为空，返回参数2
--2.nvl2(参数1，参数2，参数3)，若参数1不为null，返回参数2，参数1为空，返回参数3
SELECT ename,sal,comm,nvl(comm,0),nvl2(comm,0,1) FROM emp;
select nvl(1,2),nvl('',11) from dual;--结果1 11
select nvl2('',1,2),nvl2('a',1,2) from dual;--结果2 1
--3.nullif(参数1，参数2)：参数1=参数2，返回null，若参数1不等于参数2，返回参数1
SELECT ename,sal,comm,nullif(comm,null),nullif(comm,300) FROM emp;
select nullif(1,2),nullif(1,1) from dual;--结果1 null
SELECT ename,sal,comm,(sal+comm)*12 FROM emp;
SELECT ename,sal,comm,(sal+NVL(comm,0))*12 FROM emp;
--4.coalesce（参数1，参数2,。。。参数n）取参数中第一个不为空的值,若都为空，就返回空
SELECT ename,comm,sal,coalesce(comm,sal) FROM emp;
SELECT ename,comm,sal,coalesce(sal,comm) FROM emp;
select coalesce(1,2,3),coalesce('','1','2'),coalesce('','a','11'),coalesce('','') from dual;--结果1 1 a 空
/*
条件分支判断
*/
/*
1.用case表达式做等值判断：
case 表达式
when 值1 then 返回值1
when 值2 then 返回值2
.......
[else 默认返回值]
end
*/
--查看员工编号、姓名、工资、部门编号、部门名称
/*
部门编号：
10 A
20 B
30 C
其他 ICSS
*/
select empno,ename,sal,deptno,
case deptno
when 10 then 'A'
when 20 then 'B'
when 30 then 'C'
else 'D'
end as dname
from emp;
/*
2.用case表达式做非等值判断：
case
when 表达式1 then 返回值1
when 表达式2 then 返回值2
.......
[else 默认返回值]
end
*/
--查看员工编号、姓名、工资、工资级别、部门编号
/*
部门编号：
>=5000 A
>=3000 B
>=1000 C
其他 D
*/
select empno,ename,sal,
case
when sal>=5000 then 'A'
when sal>=3000 then 'B'
when sal>=1000 then 'C'
else 'D'
end as 工资级别,
deptno
from emp;
/*
decode做等值判断：
decode(表达式，值1，返回值1，值2，返回值2......[,默认返回值])
*/
--查看员工编号、姓名、工资、部门编号、部门名称
/*
部门编号：
10 A
20 B
30 C
其他 ICSS
*/
select empno,ename,sal,deptno,decode(deptno,10,'A',20,'B',30,'C','D')as dname
from emp;
/*
关系表概述：
1.主键列：不能为空，数据必须唯一。
2.外键列：允许为空，允许有重复值，列值必须是引用主表中的主键中存在的值。
表和表之间三种关系：
1.一对一：一个表中的一条记录匹配另一个表中的一条记录。
2.一对多：一个表中的一条记录匹配另一个表中的多条记录。
3.多对多：一个表中的多条记录匹配另一个表中的多条记录。（大多是由两个一对多关系间接形成）
*/
--多表查询(n个表至少需要n-1个连接条件)
--SQL1992连接查询语法
--1.查询员工编号、姓名、部门名称
select empno,ename,dname
from emp,dept
where emp.deptno=dept.deptno;
--2.查询员工编号、姓名、部门编号、部门名称(表明前缀)
select empno,ename,dept.deptno,dname
from emp,dept
where emp.deptno=dept.deptno;
--3.查询员工编号、姓名、部门编号、部门名称（表别名，只能用空格，不能用as）
select empno,ename,d.deptno,dname
from emp e,dept d
where e.deptno=d.deptno;
--内连接：查询仅返回满足连接条件的记录。
--外连接：不仅返回满足条件的记录，不满足条件的也返回。
--1.查询员工编号、姓名、部门编号、部门名称（外连接，没有部门的员工也返回）
select e.empno,e.ename,d.deptno,d.dname
from emp e,dept d
where e.deptno=d.deptno(+);
--2.查询员工编号、姓名、部门编号、部门名称（外连接，没有员工的部门也返回）
select e.empno,e.ename,d.deptno,d.dname
from emp e,dept d
where e.deptno(+)=d.deptno;
--自连接
--1.查询员工编号、姓名、员工管理者编号、员工管理者姓名（自连接,没有管理者的员工也返回）
select e.empno,e.ename,m.empno,m.ename
from emp e,emp m
where e.mgr=m.empno(+);
--2.查询谁的工资比BLAKE高
select e1.empno,e1.ename,e1.sal
from emp e1,emp e2
where e1.sal>e2.sal
and e2.ename='BLAKE';
--SQL1999连接查询语法
--1.查询员工编号、姓名、部门编号、部门名称（叉集）
select e.empno,e.ename,d.deptno,d.dname
from emp e
cross join dept d;
--内连接 inner join
--外连接：
--左外连接：left outer join
--右外连接： right outer join
--满外连接：full outer join
--1.查询员工编号、姓名、部门编号、部门名称（内连接）
select e.empno,e.ename,d.deptno,d.dname
from emp e
inner join dept d
on e.deptno=d.deptno
order by e.sal;
--2.查询员工编号、姓名、部门编号、部门名称（左外连接,没有部门的员工也返回）
select e.empno,e.ename,d.deptno,d.dname
from emp e
left outer join dept d
on e.deptno=d.deptno
order by e.sal;
--3.查询员工编号、姓名、部门编号、部门名称（右外连接,没有员工的部门也返回）
select e.empno,e.ename,d.deptno,d.dname
from emp e
right outer join dept d
on e.deptno=d.deptno
order by e.sal;
--4.查询员工编号、姓名、部门编号、部门名称（满外连接,没有员工的部门也返回）
select e.empno,e.ename,d.deptno,d.dname
from emp e
full outer join dept d
on e.deptno=d.deptno
order by e.sal;
--组函数：avg/sum/max/min/count
--1.查询所有工资的总和
select sum(sal) from emp;
--2.
select avg(sal),sum(sal),max(sal),min(sal),count(sal)
from emp;
--3.所有组函数都忽略空值
select count(comm) from emp;
--4.查询所有没有奖金的人数
select count(*)-count(comm) from emp;
select count(*) from emp where comm is null;
--5.查询部门的总数（统计不重复的计数）
select count(distinct deptno) from emp;
--分组查询
--1.查询每个部门的编号，员工工资总和,最高工资
select deptno,sum(sal) salsum,max(sal)
from emp
group by deptno
order by salsum desc;
--2.多个分组表达式
select deptno,job,sum(sal)
from emp
group by deptno,job;
--3.查询每个部门的部门名称，人数
select d.dname,count(e.empno)
from dept d
inner join emp e
on d.deptno=e.deptno
group by d.dname;
--4.统计每年入职的人数：年份、人数
select to_char(hiredate,'yyyy') 年份,count(*) 人数
from emp
group by to_char(hiredate,'yyyy')
order by 1 desc;
--过滤查询
--1.where条件：过滤分组之前的数据
--2.having条件：过滤分组之后的数据
--1.统计每年入职的人数：年份、人数（仅返回入职不少于2人的年份的数据）
select to_char(hiredate,'yyyy') 年份,count(*) 人数
from emp
group by to_char(hiredate,'yyyy')
having count(*)>=2
order by 1 desc;
--2.查询每个部门的编号，员工工资总和(工资总和大于2000)
select deptno,sum(sal) salsum
from emp
group by deptno
having sum(sal)>2000
order by salsum desc;
--3查询每个部门的编号，员工工资总和(只统计员工编号大于7600的)
select deptno,sum(sal) salsum
from emp
where empno>7600
group by deptno
order by salsum desc;
--4.查询每个部门的编号，员工工资总和
select deptno,sum(sal) salsum
from emp
where empno>7600
group by deptno
having sum(sal)>2000
order by salsum desc;
--分析函数：
--1.练习OVER排序,partition by进行分组
SELECT empno,deptno,ename,sal,
SUM(sal)OVER (ORDER BY ename) over1,
SUM(sal)OVER () over2,
SUM(sal)OVER (ORDER BY deptno) over3,
SUM(sal)OVER (PARTITION BY deptno) over4,
SUM(sal)OVER (PARTITION BY deptno ORDER BY ename) over5
FROM emp;
--2.练习row_number()排序--做自然排序
select row_number()over(partition by deptno order by sal),emp.*
from emp;
--3.练习dense_rank()排序，--做并列排序
select dense_rank()over(partition by deptno order by sal),emp.*
from emp;
--4.练习rank()排序，--先做自然排序，在做并列排序
select rank()over(partition by deptno order by sal),emp.*
from emp;
--5.综合rank(),dense_rank(),row_number()
select row_number()over(partition by deptno order by sal) row_number,
dense_rank()over(partition by deptno order by sal) dense_rank,
rank()over(partition by deptno order by sal) rank,
deptno,sal
from emp;
--子查询
--基本子查询
--1.查询谁的工资比BLAKE高
select e1.empno,e1.ename,e1.sal
from emp e1,emp e2
where e1.sal>e2.sal
and e2.ename='BLAKE';
select * from emp
where sal>(select sal from emp
where ename='BLAKE');
--2.查询工资最低的员工
select * from emp
where sal=(select min(sal) from emp );
--3.查询和7369号员工同一个部门的其他员工
select * from emp
where deptno=(select deptno from emp where empno=7369)
and empno<>7369;
--子查询嵌套多层
--4.查询比7369号员工的领导的工资还要高的员工
select * from emp
where sal>(select sal from emp
where empno=(select mgr from emp where empno=7369));
--5.查询员工编号、姓名、工资、工资总和（在查询列表中使用子查询，只能是单行单列）
select empno,ename,sal,(select sum(sal) from emp ) as 工资总和
from emp;
--6.查询工资最高的前5名员工（临时视图，内敛视图）
select * from (select * from emp order by sal desc)
where rownum<=5;
--7.查询员工表中第6道第12条数据
select * from (select rownum rnum,e.* from emp e )
where rnum between 6 and 12;
--推荐下面写法，效率高
select * from (select rownum rnum,e.* from emp e where rownum<=12 )
where rnum>=6;
--8.查询工资最高的第6到第12名员工
select * from (select rownum rnum,e.* from(
select * from emp order by sal desc) e
where rownum<=12)
where rnum>=6;
--多行子查询（in,all,any）
--1.查询所有是领导的员工(多行子查询)
select * from emp
where empno in (select mgr from emp);
--2.查询所有不是领导的员工(多行子查询)
select * from emp
where empno not in (select mgr from emp where mgr is not null);
--any逻辑或比较
--4.查询大于20号部门任意一个员工工资的员工
select * from emp
where sal>any(select sal from emp where deptno=20);
--all逻辑与比较
--5.查询大于20号部门所有员工工资的员工
select * from emp
where sal>all(select sal from emp where deptno=20);
--6.查询所有员工人数不少于3人的部门
select * from dept
where deptno in (select deptno from emp group by deptno
having count(*)>=3);,
--内外交互式相关子查询
--1.查询员工编号、姓名、部门编号、工资、本部门的工资共和
select empno,ename,deptno,sal,
(select sum(sal) from emp where deptno=e.deptno)
from emp e;
--2.查询所有工资超过本部门平均工资的员工
select * from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno);
--3.查询是本部门中入职最早的但不是部门经理的员工
select * from emp e
where hiredate=(select min(hiredate) from emp where deptno=e.deptno)
and empno not in (select mgr from emp where mgr is not null);
--exists查询练习
select * from emp
where exists(select 1 from dept where 1=1);
--查询所有是部门经理的员工（可代替in语法）
select * from emp e
where exists (select 11 from emp d where e.empno=d.mgr);
--查询所有不是部门经理的员工
select * from emp e
where not exists (select 'x' from emp d where e.empno=d.mgr);
/*
--查询所有换过职务的员工
select * from emp e
where exists (select '' from job_history j where e.empno=j.empno);
*/
--with语句练习
--1.查询工资最高的前5名员工
with newemp as (select * from emp order by sal desc)
select * from newemp where rownum<=5;
--2.
WITH dept_costs AS (
SELECT d.dname, SUM(e.sal) AS dept_total
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total >
(SELECT dept_avg
FROM avg_cost)
ORDER BY dname;
--DML
/*
--1.插入数据insert into表名[(列1，列2，...)] values(值1，值2...);
--2.省略列列表，默认就是表中的所有列
--3.列和值必须要个数，顺序，类型相同
*/
--创建实验表newdept,newemp
create table newdept
as select * from dept;
create table newemp
as select * from emp;
--1.增加一个新部门
insert into newdept(deptno,dname,loc)
values(55,'tt','t1');
--2.增加一个新部门
insert into newdept
values(56,'t2','t2');
--3.增加一个新部门
insert into newdept
values(57,'t2',null);
--4.增加一个新部门
insert into newdept(deptno,dname)
values (58,'t3');
--5.插入多行.insert into 表名[(列1，列2.。。)] 子查询
insert into newdept
(select * from dept);
insert into newdept
(select * from dept where deptno=10);
/*
更新数据 update 表名 set 列1=值，列2=值。。。[where 子句]
*/
--1.修改员工的姓名为张三(全部修改)
update newemp set ename='张三';
--2.修改员工7566的姓名为张三(只改一个)
update newemp set ename='张三'
where empno=7566;
--3.修改10号部门员工的工资上浮300元
update newemp set sal=sal+300 where deptno=10;
--4.修改7566号员工的工资和‘King’的工资相同
update newemp set sal=
(select sal from newemp where ename='KING')
where empno=7566;
/*
删除数据 delete from 表名[where 子句]
*/
--1.删除7499员工
delete from newemp where empno=7499;
--2.删除20号部门的员工
delete from newemp where deptno=20;
--3.删除部门名称为‘SALES’的部门的员工
delete from newemp
where deptno=(select deptno from dept where dname='SALES');
--查询部门
select * from newdept;
--查询员工
select * from newemp;
/*
1.合并语句merge：两个表的主键列不能有重复值，主键类不能更新
merger into 副表表名 别名
using 主表表名 别名 on（主表.主键=副表.主键）
when matched then
更新语句
when not matched then
插入语句
*/
--创建两个表
create table emp1
as(select empno,ename,sal from emp);
create table emp2
as(select empno,ename,sal from emp);
--合并数据
merge into emp2 e2
using emp1 e1
on(e1.empno=e2.empno)
when matched then
update set e2.ename=e1.ename,e2.sal=e1.sal
when not matched then
insert values (e1.empno,e1.ename,e1.sal);
/*
事务的特性：ACID
1.原子性：任何事务都是一个整体，要么全部成功，要么全部失败。
2.一致性：必须从一个正确状态直接切换到另一个正确的状态。
3.隔离性：一个会话看不到另一个未提交的事务操作。
4.持久性：提交事务后，事务操作会被持久化到数据库中，不可回退，
别的会话也可看到事务操作的结果。
TCL事务控制语言：
1.commit:提交
2.rollbake:回滚
事务的生命周期：以任意的DML语句开始，以提交或回退结束。
1.提交事务：COMMIT,DDL或DCL,正常退出会话
2.回退事务：ROLLBACK,异常退出会话。
*/
/*
数据库锁机制：主要作用就是控制并发访问资源，限制用户动作
锁的分类：
1.按照力度的划分：行锁和表锁。
2.按加锁的时机：显示锁（手动加锁）、隐式锁（自动加锁）。
3.按类型划分：
共享锁：如果一个资源（表或行）加了共享锁，那么其他会话也可以给这个资源加共享锁，但是不能加独占锁。
独占锁：如果一个资源（表或行）加了独占锁，那么其他会话不可以给这个资源加任何锁。
释放锁：事务结束，锁会被释放。
SQL语句自动加锁的情况：
1.查询语句：默认不加任何锁。
2.DML语句：行独占锁，表共享锁。
3.DDL语句：表独占锁。
查询加锁：查询语句 FOR UPDATE 行独占锁 ，表共享锁
手动加锁：
lock table 表 in share | exclusive mode;
share模式：禁止其他会话对表做DML操作，但是允许其他会话也对表加share锁。
exclusive模式：禁止其他会话对表做DML操作，也禁止其他会话对表加任何锁。
*/
--DDL
--1.创建学生表
create table student1
(
stu_id number(5),
stu_name varchar2(50),
stu_sex char(2),
stu_hiredate date
);
--删除学生表
drop table student1;
--2.创建学生表(带列默认值)
create table student1
(
stu_id number(5),
stu_name varchar2(50),
stu_sex char(2) default '男',
stu_hiredate date default sysdate
);
--3.利用子查询创建表
create table new_emp1
as select empno,ename
from emp;
--常用数据字典
select * from user_tables;
select * from user_objects;
select * from user_catalog;
--修改学生表
--1.追加列
alter table student1
add (phone varchar2(50),address varchar2(100) );
--2.修改列
alter table student1
modify (address varchar2(200) );
--3.删除列
alter table student1
drop (phone);
--4.删除多个列
alter table newemp
drop (job,mgr);
--重命名
rename student1 to stu;
--清空表
--1.用DML语句请空表
delete from stu;
select * from stu;
--2.用DDL语句
truncate table stu;
/*
delete 和truncate的区别：
1.事务 可回退 不可回退，自动提交
2.记录日志 记录 不记录
3.释放空间 不释放空间 释放空间
*/
--表注释
comment on table stu is '这是我的学生表';
--列注释
comment on column stu.stu_name is'学生姓名';
comment on column stu.stu_id is '学生编号';
/*
回收站
*/
drop table stu;
--回收站的数据字典
select * from user_recyclebin;
--从回收站还原表
flashback table stu to before drop;
--清空回收站
purge recyclebin ;
--删除表，直接删除，回收站也没有
drop table student1 purge;
--创建事务的临时表，数据仅在一个事务中存在
--事务结束后就结束
create global temporary table temp1
(
id number(4),
name varchar2(20)
)
on commit delete rows;
--创建会话的临时表，数据仅在一个会话中存在
--在当前会话中可以查询，在另一个会话查不到，当当前会话关闭后就不存在了
create global temporary table temp2
(
id number(4),
name varchar2(20)
)
on commit preserve rows;
/*
insert into temp2 values (11,'aa');
insert into temp2 values (22,'bb');
select * from temp2;
*/
/*
五种约束:
1.not null 非空约束
2.primary key 主键约束
3.unique 唯一约束
4.cherk 检查约束
5.foreign key 外键约束
*/
--约束可以建表的同时创建，也可以在建表之后创建
--创建约束实验表
create table newdept
as
select * from dept;
create table newemp
as
select * from emp;
--1.非空约束：限制列值不允许为空
--修改ename列为非空
alter table newemp
modify (ename not null);
--取消非空约束
alter table newemp
modify (ename null);
--2.主键约束：限制列值不能重复，且不能为空
--设置主键约束
alter table newemp
add constraints newemp_empno_pk primary key(empno);
--删除约束
alter table newemp
drop constraints newemp_empno_pk;
--复合主键（多列组成一个主键）
alter table newemp
add constraints newemp_enmno_pk primary key(empno,ename);
--3.unique约束：限制列值不能重复，不限制空值，一个表中可以有多个唯一值约束
--设置唯一性约束
alter table newemp
add constraints newemp_email_uk unique(email);
--4.检查约束：限制自定义条件，忽略空值
--限制工资不能少于400
alter table newemp
add constraints newemp_sal_chk check(sal>=400);
--5.外键约束：限制外键列值必须是引用的主表中的主键列存在的列值，不限制空值
--前置条件：引用的主表中的主键列必须已经设置主键约束
--部门主键的主键约束
alter table newdept
add constraints newdept_dempno_pk primary key(deptno);
--员工外键约束
alter table newemp
add constraints newemp_deptno_fk foreign key (deptno)
references newdept(deptno);
--级联删除
alter table newemp
add constraints newemp_deptno_fk foreign key(deptno)
references newdept(deptno)
on delete cascade;
--级联置空
alter table newemp
add constraints newemp_deptno_fk foreign key(deptno)
references newdept(deptno)
on delete set null;
--创建表的同时增加约束(约束没有名字)
create table emp2
(
empno number(5) ,
ename varchar2(20) not null,
eemail varchar2(12) unique,
sal number(4,2) check(sal>=400),
deptno number(6) references newdept(deptno)
);
--创建表的同时增加约束(约束有名字)
create table emp3
(
empno number(5),
ename varchar2(20) not null,
eemail varchar2(12),
sal number(4,2),
deptno number(6),
constraints emp3_empno_pk primary key(empno),
constraints emp3_eemail_uk unique (eemail),
constraints emp3_sal_chk check(sal>=400),
constraints emp3_deptno_fk foreign key(deptno)
references newdept(deptno)
);
--无效化约束（暂时使约束失效）
alter table emp3
disable constraints emp3_sal_chk;
--激活约束
alter table emp3
enable constraints emp3_sal_chk;
/*
视图：虚拟表，作用是保存查询结果，仅保存查询语句，而不保存真实数据（物化视图除外）
*/
--创建视图语法（如果查询语句中有衍生列，必须起别名）
create [or replace] view 视图名称
as
查询语句
--查询员工表中20号部门的员工的员工编号、姓名、工资
create or replace view v_emp
as
select empno,ename,sal
from emp
where deptno=20;
--查询视图
select * from v_emp;
--复杂视图
--创建视图v_dept：查询每个部门的编号，名称，员工人数，员工工资总和
create or replace view v_dept
as
selete d.deptno,d.dname,
count(e.empno) emp_count,
sum(sal) sal_sum
from dept d
join emp e on d.deptno=e.deptno
order by d.deptno,d.dname;
--查询视图
select * from v_dept where emp_count>=3;
--创建只读视图
create or replace view v_emp
as selete empno,ename,sal
from emp
where deptno=20
with read only;
--删除视图
drop view v_emp;
/*
序列
*/
--创建序列
create sequence stu_seq
start with 100 --起始值，默认是1
increment by 10 --递增值，默认是1
nomaxvalue --最大值，默认无最大值
nocycle --假如设置了最大值，到达最大值之后，是否从头开始，默认nocycle
cache 10 --缓存数量，默认20
;
--删除序列
drop sequence stu_seq;
--创建序列（使用默认值）
create sequence stu_seq;
--使用序列
select stu_seq.nextval from dual;
select stu_seq.currval from dual;
--在插入语句中使用序列
insert into stu values (stu_seq.nextval,'tom','男',sysdate);
/*
索引：作用加快查询速度，但是会降低DML速度
索引的分类：
B-TREE索引：默认，适合列值基数比较高，冗余数据很少时
位图索引：适合列值基数比较低，冗余数据很多时
*/
--创建实验表
create table new_emp
as select * from emp;
--创建唯一性索引
alter table new_emp
add constraints newemp_empno_pk primary key(empno);
--创建普通索引
create index newemp_ename_idx on new_emp (ename);
--删除索引
drop index newemp_ename_idx;
--创建基于函数的索引
create index newemp_ename_idx on new_emp(Lower(ename));
--创建位图索引
create bitmap index newemp_job_idx on new_emp(job);
--查询实验
select * from new_emp where empno=7369;
select * from new_emp where ename='KING';
select * from new_emp where Lower(ename)='KING';
select * from new_emp where job='xxxx';
/*
适合创建B-TREE索引的时机：
1.列值基数比较高（重复率比较低）。
2.不以空值做条件。
3.列经常出现在条件字句中。
4.表很大。
5.经常做查询操作，而不是DML操作。
6.满足条件的数据不超过总记录数的15%。
7.模糊查询不走索引，如果模糊查询想使用索引，必须用全文检索技术。
*/
--同义词
select * from user_tables;
select * from tabs;
--授权
grant create synonym to scott;
--创建同义词
create synonym e for emp;
--使用同义词
select * from e;
--删除同义词
drop synonym e;
/*用户和权限 */
--1.创建新用户
create user YAN identified by YAN;
--DCL授权语句
--GEANT 权限1，权限2...TO 用户；
--DCL收权语句
--REVOKE 权限1，权限2...FROM 用户；
--2.授予创建会话的权限给YAN
grant create session to YAN;
--3.收回权限
revoke create session from YAN;
--4.授予开发人员权限
grant connect ,resource to YAN;
grant create view to YAN;
--5.修改密码
alter user YAN identified by YANZZ;
--6.解锁用户
alter user YAN account unlock;
--7.授予对象访问权限
grant select on scott.emp to YAN;
grant all on scott.emp to YAN;
--8.回收对象访问权限
revoke all on scott.emp from YAN;
--9.删除用户
drop user YAN cascade;
/*
集合运算：把多个查询结果以并集，交集和补集的形式合并成一个查询结果。
*/
--创建实验表1
CREATE TABLE set1
(
id number(6),
name varchar2(12)
);
--创建实验表2
CREATE TABLE set2
(
id number(6),
name varchar2(12)
);
--1.并集union（自动消除重复行）
select id,name from set1
union
select id,name from set2;
--2.并集union all
select id,name from set1
union all
select id,name from set2;
--3.交集intersect
select id,name from set1
intersect
select id,name from set2;
--4.补集minus
select id,name from set1
minus
select id,name from set2;
select id,name from set2
minus
select id,name from set1;
--5.混合运算
select id,name from set1
union
select id,name from set2
minus
select id,name from set1;
