一.sql ------>结构性查询语言 分类： 1.DQL 数据查询语言 select 主要讲： 简单sql 限制排序 函数 子查询 多表查询 1.1简单sql selcet *(匹配所以列)|co
一.sql ------>结构性查询语言
分类：
1.DQL 数据查询语言 select
主要讲： 简单sql 限制排序 函数 子查询 多表查询
1.1简单sql
selcet *(匹配所以列)|column（）|alias|express from <tab |view>;
查看当前用户有什么表：select table_name from user_tables;| select * from tab;
描述表结构：desc dept；
查看表内容： select * from emp;
select enmae,deptno,sal*1.15|-|/|+ tab_name;
将两个表内容显示在一起：select ename | | job from emp;或者select concat(ename,job) from emp;
select ename||'is work'||job from emp;或者：select concat(concat(ename'is work'),job from emp;
转义：select ename ||q'(is work)'|| job from emp #将“ ' ”进行转义。
别名：select ename as name from emp;
小写：select ename as "name" from emp;#别名是用双引号 字符性单引号
1.2限制排序：
语法：select * from <>;
select *select ename,sal from emp; from <> where <>;
查询部门编号=10： select ename,sal from emp where deptno=10;
日期型：select * from emp where hiredate='xxxxx';
不等于 ！= | <> |^=
范围比较： select * from emp where sal 【not】between 3000 and 5000;
显示查询：select * from emp where ename 【not】in ('SCOOT','KING');
显示薪水为2000，3000，5000:select * from emp where sal(2000,3000,5000);
显示大于最小一个：select * from emp where sal>any(2000,3000);
显示大于最大的一个：select * from emp where sal>all(2000,3000);
like(匹配查询):转义字符不能使用“%”
显示S开头的：select ename from emp where ename like 'S%'; %表示匹配0个或者多个，下划线表示一个
显示中有特殊字符需要使用转义： select ename from emp where like 'd/%%' escape '/';
逻辑查询：and or
select ename,sal,deptno from emp where deptno=10 and | or sal=3000;
找空值：select ename,comm from emp where comm is (not) null;
空值算数运算：select sal,comm,sal+nvl(comm,0) from emp
排序：
语法：select * from <> where <> order by <>;
降序排序：select ename,sal from emp order by sal desc;
生序：select ename,sal from emp order by sal asc;
多个排序：select * from emp order by deptno ,sal desc;
去重：distinct
select distinct deptno from emp;
select unique(deptno) from emp;
select unique deptno from emp;
1.3函数：
单行函数：用户输入一行数据 每一行返回一个结果
分类：
字符函数：
1.upper(大写): select upper('abcde') from dual;
2.lower（小写):select lower('SHJKSKLNKL') from dual;
3.initcap(首字母大写)：select initcap('hjksdjkhjk') from dual;
4.concat(连接字符)：select concat('hello','word') from dual;
5.length (按字符统计字符串长度)：select length('jkldsklsdl') from dual;
6.lengthb(按字节统计字符串长度)
7.lengthc(按U码统计字符串长度)
8.substr（截取函数）：
语法：substr(dname,m,n) m:从M开始截取可用负数 n:截取多个个字符
例：select substr(dname,1,3) from dept;
9.instr（显示某个字符在哪个位置）：
语法：instr(dname,m,n) m:为查找条件字符 n：字符第几次出现
例：select ename,instr(ename,'S',1) from emp;
10.trim（截断函数（10g））：
语法：trim（leading|trailing'字符' form 字符串）
例： select trim('s' from 'ascsbs') from dual;
11.ltrim(左截断11g)：select ltrim('abcdabcd','a') from dual;
12.rtrim（右截断11g）： select rtrim('afabccbacab','abc') from dual;
13.replace(替换函数)： replace(danme,'a','b') 将‘a’替换成‘b’
例：select ename,replace(ename,'A','B') from emp;
14.lpad(左填充函数)：
lpad(deptno,m,c) m:总共多少字符 c:表示用什么字符填充，默认空格
例：select lpad(deptno,10,'#') from dept;
15.rpad(右填充函数)：
数值函数:
1.abs(绝对值)：select abs(1),abs(-1),abs(0) from dual;
2.mod(取余)：select mod(3,5) from dual;
3.trunc(取整)：select trunc(-123.1453,2) from dual;
4.round(四舍五入)： select round(234.15645,1) from dual;
5.ceil(取整，大于或等于中这个的最小整数) ： select ceil(234.12),ceil(-234.12) from dual;
6.floor（取整，小于或等于它的最大的整数）：select floor(234.12) ,floor(-234.12) from dual;
日期函数:
1.sysdate(系统日期):select sysdate from dual;
2.months_between(距某个日期相隔几个月) :select ename,hiredate,moths_between(sysdate,hiredate) from emp;
3.next_day:（距当前时间下一个星期几是哪天）：
select next_day(sysdate,'fri') from dual;
4.add_moths(x,n): x:当前时间 n：指定时间
select hiredate,add_moths(hiredate,3) from emp;
5.last_day:(一个月的最后一天)
select last_day(sysdate) from dual;
当年的第一天： select trunc(sysdate,'yyyy') from dual;
年：yyyy|yy|rr
月:mon|mm
日:dd|dy
yyyy-mm-dd hh24:mi:ss
转换函数：
1.to_char 转为字符：to_char(date,'yyyy|yy|mm|dd') select ename,hiredate from emp where to_char(hiredate,'yyyy')='1982';
二进制转换十进制：select to_char(101,'xxxx') from dual;
2.to_number转为数字:
日期转为数字：select ename,hiredate from emp where to_number(to_char(hiredate,'yyyy'))=1981;
十六进制转十进制：select to_number('af','xxxx') from dual;
3.to_date转为日期：
数字转日期：select ename,hiredate from emp where to_date(to_char(hiredate,'yyyy'),'yyyy-mm-dd'));
通用函数:
1.nvl(空值函数) ：nvl (x,y)
2.nvl2 :nvl(x,y,z): x为空返回z的值，x不为空为y的值： select comm,nvl2(comm,1,2) from emp;
3.nullif:nullif(x,y):xy相等时返回空值，不一样返回x值：select nullif(1,2) from dual;
分支语句:
1.decode():语法：decode(deptno,x,y,z):如果x等于deptno返回y，如果x不等于deptno返回z； #x可为字符型
例：等值
select ename,deptno,sal,decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*1.3) from emp;
2.case..when..then...end case:
例：等值
select ename,deptno,sal,
case deptno
when 10 then sal*1.1
when 20 then sal*1.2
when 30 then sal*1.3
end casefrom emp;
例：不等值
select ename,deptno,sal,
case
when sal<1000 then sal*1.1
when sal between 1000 and 2000 then sal*1.2
when sal>2000 then sal*1.3
else sal
end case from emp;
select max(ename),min(ename) from emp;
正则表达式函数
1.regexp_substr--->substr(截取)：
语法： regexp_substr(原字符，匹配字符，m,n)
例：select ename,regexp_substr(ename,'*B*',1,1) from emp;
2.regexp_instr---->instr
语法：regexp-instr(原字符串，匹配字符串，m，n)
例;select ename from emp where regexp_instr(ename,'S',1,1)>0; 或 select ename,regexp_instr(ename,'S',1,1) from emp
3.regexp_like--->like:
语法：regexp_like(原字符串，'^|.|$|？|', 'i|c|') i:不区分大小写 c：区分大小写
例：select ename from emp where regexp_like(ename,'^s','i');
4.regexp_count-->count
例：select ename,sal from emp where regexp_count(ename,'A')=1; #显示一个A字符的员工的名字，薪水
多行函数（聚合函数 分组函数）：输入多行数据 返回一行结果
count(计数)：
select count(*) from emp; #有多少个员工
select count(comm) from emp;
avg(平均值)
max()
min()
sum()
group by（分组）：
select deptno,max(sal) from emp group by deptno; #每个部门最高薪水
select ename from emp where sal in(select max(sal) from emp group by deptno);#查询大于组平均工资的人名字
having （过滤）：select deptno,max(sal) from emp group by deptno having max(sal)>3000;
select deptno,max(sal) from emp where deptno <>10 group by deptno having max(sal)>3000;
rollup （滚动分组）：
rollup(n): ----->rollup（n）=（group by n+1） + （group by n）
select deptno,avg(sal) from emp group by rollup(deptno);#查看部门及公司工资的平均值
cube （） :----->cube(n)=group by+0 group n#分组为n的平方
grouping():检测是否参与分组，参与为显示0否则为1：
select deptno,job,avg(sal),grouping(job) from emp group by grouping sets(deptno,job);
grouping sets:(分组后合并在一起)
select deptno,job,avg(sal) from emp group by grouping sets(deptno,job);
1.4子查询：
执行顺序：从右到左
特点：1.子查询可以出现在语句的任何位置
2.通常需要放在括号里面“（）”
3.先执行子查询的结果，传递给主查询
4.在子查询中不要使用order by
分类：
1.单行子查询：返回的查询结果是一行，语法：=
例：select ename,sal from emp where sal=(select sal from emp where ename='SCOTT') and ename<>'SCOTT';
2.多行子查询：返回的查询结果是多行，语法：in > < = <= >= any all
例：select ename,sal from emp where sal>(select min(sal) from emp where deptno=20);
select ename,sal,(select avg(sal) from emp where deptno=e.deptno) from emp e；
分析函数（排名）：row_number() ：语法： row_number() over(需要操作的列)
例：select ename,sal,row_number() over(order by sal desc) from emp;
rank() :语法：rank() over(需要操作的列)
例：select ename,sal,rank() over(order by sal desc) from emp;
dense_rank()：语法：dense_rank() over（需要操作的列）
例：select ename,sal,dense_rank() over(order by sal desc) from emp;
分组：partition by 语法： row_number() over(partition by +分组内容+需要操作的列)
例：select ename,sal,row_number() over(partition by deptno order by sal desc) from emp;
3.关联子查询：语法：exists 出现in时用exists代替，出现not in 用not exists 代替
4.多表查询：数据来源不再统一张表而是多张表
步骤：
1.确定数据来源
2.找有关联的字段建立等值链接
3.书写查询语句
4.1自然连接
natural join export NLS_LANG='simplified chinse_china.al32utf8'
条件：必须列名相同，数据类型相同，等值连接的列不能跟上表的前缀
例：select ename,dname from emp natural join dept
join using()
条件：创建等值链接的列，列名相同，数据类型可以不相同
例：select ename,dname from emp join dept using(deptno);
join on()
条件：创建'等值连接的列，列名可以不相同，数据类型可以不相同
例：select ename,sal,grade from emp join salgrade on(sal between losal and hisal);
set操作：
union---将两个或者多个结果合并在一起，去重复的行，并排序
例：select deptno from emp where deptno in(10,20) union select deptno from emp where deptno in(10,30);
union all ：'将两个或者多个结果合并在一起，不去除重复，不排序
例：select deptno from emp where deptno in(10,20) union all select deptno from emp where deptno in(10,30);
intersect:--->交集：将两个或者多个结果相同的部分显示出来
例：select deptno from emp where deptno in(10,20) intersect select deptno from emp where deptno in(10,30);
minus:---->差集：第一个结果减去第二个结果所得到的结果
例：select deptno from emp where deptno in(10,20) minus select deptno from emp where deptno in(10,30);
2.DML 数据操作语言 主要用来改变数据库中的数据进行操作 insert update delete
查询当前数据插入进去放在哪个数据文件：
select a.*,dbms_rowid.rowid_block_number(rowid)rb,dbms_rowid.rowid_relative_fno(rowid)rf from dept a;
insert:语法
单行插入：insert into+表明（列名，列名） values(内容)
例：insert into dept1（deptno,dname,loc） values(12,'job','sc');
多行插入：insert into 表名 select .........
例：insert into dept1 select * from dept;
update语法：更改
update 表名 set 列名=要修改后的值 where 条件()
例：update dept set deptno=20 where loc='xxxx';
update 表名 set 列名 =（select...）
例：update dept set dname=(select ename from emp where ename='SCOTT');
delete语法： 在进行delete删除时，实际上是没有进行磁盘回收的，需要进行alter table <> shrink space; 操作后才会真正的回收磁盘空间
delete 表名
delete 表名 where 条件
merge into:使数据库中存在的语句进行更新，不存在的数据进行插入，
语法：
merge into <表名1>
using <表名2> on 条件
when matched then
update set 列名1=列名2
when not matched then
insert values (列值)
例： 1 merge into test a
2 using emp b on（.empno=b.empn）
3 when matched then
4 update set sal=sal+1
5 when not matched then
6 insert values(b.ename,b.deptno,b.sal,b.empno)
3.DDL 数据定义语言 定义构成数据的结构 create drop alter truncate
对象：表，视图，系统，同义词，索引，过程，函数，触发器
1.表：堆表，索引组织表，对象表，分区表，簇表，外部表
create table table_name(必须字母开头a-z A-Z 0-9 _ # $除这些符号意外的必须加上双引号) +列（datatype|default|constraint） tablespace <> pctused<> pctfree<>
字符类型：
固定字符char 最长2000个
可变长字符类型：varchar2() 最长4000个
大对象字符类型：clob 最长4g-1个
数字型：number 最长30个
日期型：date timestamp(时间戳)
二进制字符类型：blob 存储多媒体 长度4g-1
不长用：
rowid：存放列编号
long：
约束：
查询约束创建在哪个表的：desc user_constriants;
产寻约束创建在哪个列的：desc user_cons_columns;
静用约束：alter table <> disable constraint <列名>
启用约束：alter table <> enable constraint <>
删除约束：alter table <> drop constraint <>
1.not null:非空
例：create table test (name varchar2(20),id number not null);
建表前未加约束：alter table test modify (name varchar2(20) not null);
2.unique：唯一性
例： 列级 cetate table test (name varchar2(20) unique);
列级：给约束取名：create table test (name varchar2(20) constraint u_name unique);
表级：create table test(name varchar2(20),unique(name));
表级：给约束取名：create table test(name varchar2(20),constraint u_name unique(name));
3.primary key：主建（唯一，非空）
例：cteate table test (name varchar2(20) primary key )
4.foreign key：外建约束
例：cteate table test (name varchar2(20) foreign key )
主键和外建在同一张表：create table test (name varchar2(20) primary key,name)
增加emp1外键约束并关联到dept（deptno）上：alter table emp1 modify(deptno references dept1(deptno));
5.chcek：检查约束
例：create table test (name varchar2(20),mail varchar2(30) check(mail like '%_%@%_%.%_%'));
列级约束：跟在列的后面
表级约束：用“，”分开
pctused<>:表示使用空间的百分比：insert操作
pctfree<> ：表示剩余表空间的百分比update操作
2.修改表结构及 表名：
增加列：alter table <> add(column datatype)
修改长度： alter table <> modify(column datatype);
修改列名：alter table <> rename column <old> to <new>;
修改表名：alter table <old> rename to <new>;
3.删除表结构：
alter table <> drop column <>;
4.表只读模式（read only）：
alter table <> read only;主读模式
alter table <> read write;打开读写
表主读模式下可以左一下操作：
alter table <> move : 移动表
alter table <> enable row movement;打开数据行移动
alter table <> shrink space;清理表数据
alter table <> disable row movement;关闭数据行移动
5.删除表：
drop table <>;默认放在回收站里面，并给表重命名。
查看回收站是否打开： show parameter recyclebin;------>sys用户
查看回收站内内容：show recyclebin;
恢复回收站某个文件：flashback table <> to before drop ;
恢复回收站某个文件并改名：flshback table <> to bafore droop rename to <>;
删除表不经过回收站：drop table <> purge;
清除回收站内某个表：purge table <> ;
清空回收站内所有内容：purge recyclebin;
关闭回收站功能：alter session set recyclebin=off;
6.建表（ctas）：
create table <> as select ........
要表结构和表内容： create table <> as select * from <>;
要表结构不要表内容： create table ,<> as select * from <> where 0=1; 0=1:给一个不成例的条件
2.试图：查询语句的别名
作用：1.隐藏数据来源，保证数据安全
2.简化语句的书写
3.只显示需要的信息
分类：1.简单试图：数据来源一张表
创建简单视图语法：create [or replace] [force] view <> as select.....[with check option|with read only]
[or replace]:表示试图存在就替换它
[force]：强制创建视图（基图不存在）
[with check option|with read only]：表示创建的视图必须遵循check，表示创建的试图只是只读的视图
注意：在创建视图时使用了group by| distinct | 算数表达式 时 视图不能进行dml操作
例：create or replace view as select ename,deptno from emp where deptno=10;
遵循的checkj规则只是需遵循where条件后的规则：create or replace vier as select ename,deptno from emp where deptno=10 with check option;
创建变量：create or replace view as select ename,deptno from emp where deptno=&abc;
查看当前有哪些视图：select * from user_ views;
删除视图：drop view <>
2.复杂试图：数据来源多张表desc user_ views;
3.物化试图：
3.序列：一组自动增长的唯一值（最大值10的16次方）主要用于primary key,
查看序列的详细信息： desc user_sequences;
删除序列：drop sequence <>
语法：create sequence <>;
create sequence <> increment by <步长> 默认是1
start with <初始值> 默认1(设置完成后是不能修改的，其他可以改，但是初始值必须大于最小值，)
minvalue <最小值> 默认1
maxvalue <最大值> 10的16次方
cycle |nocycle <打开循环> （一般不用）
cache | nocache <是否打开缓存> （一般不用）
调用序列：
currval:当前值
nextval：下一个值
例:create sequence s1 increment by 2 start with 1 minvalue 1;
insert into aa values(s1,nextval,'aa','bb');
. 4.同义词：对象的别名
查询当前用户有哪些同义词：select * from user_synonyms;
查询dba用户同义词：select * from dba_synonyms;
语法：create [or replace |public ] synonym <> for <对象名>；
创建：例：create sysnonym e for emp;
删除：drop public synonym <>------->dba用户
drop synonym <> ------>当前用户
特点：可以处理应用程序
分类：1.私有同义词：只有当前的用户可以访问；允许与对象同名
私有同义词权限：creacte synonyn
2.共有同义词：所有的用户都可以访问，前提是需要用户访问权限
共有同义词权限：create public sysnonym
5.索引：提高查询语句的效率，
查看当前用户有哪些索引：select * from user_indexes;| desc user_indexes;
需要建立索引：
1.经常查询的语句或者
2.查询的时候返回的行数占用表的2%以下
不需要建立索引：
1.经常做dml操作：索引需要后台维护
2.查询量少的表
分类：
树形索引：语法 ：create index <> on tablename(column);
例：创建树形索引：create index ind_dept on dept(deptno);
创建组合索引：create index ind_det_name on dept(deptno,dname);
创建函数索引：create index ind_fun_name on dept(to_number(deptno));
创建唯一型索引：create unqiue index <> on dept(column);
创建反建索引：create reverse index <> on dept(column);
创建位图索引：create bitmap index bit_emp on emp(ename);
单列索引：索引创建一个列
组合索引：（多列索引）---多个列创建一个索引
唯一性索引：创建索引的这个列值是唯一的 create [unqiue ] index <> on tablename(column);
函数索引：创建的这个列在查询语句中以函数的形式出现，需要创建一个函数索引
反建索引：避免热点块的读取，需要创建反建索引
位图索引：数据量十分大，查询的语句非常多，这样的列有or 出现，这时需要创建位图索引 create bitmap index <> on tablename(column);
分区索引：在分区表中创建的索引 分为：本地索引（在分区表中每一个分区创建一个索引）和全局索引（在分区表中只有一个索引）本地索引：create index <> on 分区表名字(column)locai; 全局索引：create index <> on tablename(column)global
索引重建：alter index <> rebuild; （空闲时候做，一般写成脚本自动执行。）
1.有人在左dml操作，事务未完成会导致索引重建不成功
2.当在重建索引时候，有用户在做dml操作时会锁表
删除索引：drop index <>;
4.DCL 数据控制语言 控制用户访问数据库或者是访问对象的某种权限 grant revoke
1.系统权限
查看当前系统具有哪些权限：select * from system_privilege_map（sys用户）
某用户具有哪些权限：select * from user_sys_privs | dba_sys_privs;
查看当前用户会话权限：select * from session_privs;
级联权限：授予级联权限：grant 系统权限，。。。 to 用户名 with admin option;注意：dba用户回收权限时级联出去的权限不会一并被回收
级联权限回收权限：revoke 权限 on 方案名.对象名 to 用户名
语法： 授权：grant 系统权限，。。。 to 用户名；
例：创建用户：create user 帐号 identfied by 密码；
授权： grant session，create view to 帐号；
revoke:回收权限：
语法： revoke 权限 from 用户；
例：revoke create table from test;
2.对象权限：
查看当前系统具有哪些对象权限：select * from table_privilege_map;
查看当前用户对某个对象下的哪个列有什么权限：select * from user_col_privs;
查看dba用户具有哪些对象权限：select * from dba_tab_privs;
查看当前用户具有哪些对象权限：select * from user_tab_privs;
grant 对象权限 on 方案名.对象名 to 用户名；
例：grant select on test.t1 to scott;
给某个用户一个更新表列的权限：grant update(ename) on scott.emp to teset;
级联权限：grant 对象权限 on 方案名.对象名 to 用户名; 注意：对象权限在dba用户回收时 会一并回收级联出去的权限
例：授权：grant select on scott.emp to test;
回收：revoke select on scott.emp from test；
3.角色权限：是系统权限或者select * from user_sys_privs;对象权限的打包
查看当前系统具有哪些角色：select * from role_sys_privs;
查看当前用户具有哪些角色：select * from user_role_privs;
语法：cerate role 角色名 identified by 密码;
grant 系统权限..... to 角色名；
grant 对象权限，.. on 方案名.对象名 to 用户名；
例：create role r1; #创建用户
grant session to r1#授系统权限
grant select on scott.emp to r1;#授对象权限；
角色授权给角色：
角色给角色授权：grant 角色名1 to 角色2# 角色名1的权限给角色名2
查看角色给角色的权限：select * from role_role_privs;
回收角色给角色的权限：revoke 角色名 from 用户名
5.TCL 事务处理语言 事物的完成和事物回馈 commit rollback savepoint
事务是如何产生？ 当执行dml（需要人为结束事物）,ddl,dcl时就产生了事务
事务的结束：commit
事务的操作主要针对dml
commit----->事物完成
rollback----->回滚到最后一次执行commit后
savepoint--->设置保存点 ----回滚：-> rollback to xx;
