sql基础

**

sql基础（ORACLE）

**

1. 数据库登录

在sqlplus内：
(1)普通用户
1，输入cmd-sqlplus-账号/密码
2，输入cmd-sqlplus 账号/密码
注意，正规登录数据库时，需要以下几点：账号，密码，IP地址，SID，端口号
3，输入cmd-sqlplus /nolog （回车) conn 账号/密码@IP/SID：1521
4，输入cmd sqlplus 账号/密码@IP/SID：1521
5.端口号可以不写
6，输入cmd-sqlplus /nolog (回车) conn 账号/密码@SID
6，输入cmd-sqlplus 账号/密码@SID
数据库账号和密码分别为AAA，aaa,IP地址为：192.168.3.102，数据库实例名为ORAL,端口号为1522
连接代码：sqlplus AAA/aaa@192.168.3.102/oral:1522
（2）管理员用户
1，输入cmd-sqlplus-账号/密码 as sysdba/syspoer
2，输入cmd-sqlplus / as sysdba
注意：无论账号和密码是什么，后面如果加 sysdba,则登录用户一定为sys

exexit:退出当前用户

在plsql内：
show user:展示当前用户
conn 账号/密码 切换用户

2.简单查询

简单查询：

select col_name1,col_name2  from  table_name.
emp:员工信息表
select  ename,sal,job from emp
select ename,job,sal from emp 

*: 通配符 表示所有的内容,工作中慎用

select * from emp 
select sal,* from emp

select 后不能同时跟*和列名！！！！！
起别名（列别名、表别名）

select ename from emp 
select ename as ygxm from emp

起别名职位为了显示方便，不改变表内的任何数据（select 不改变数据）

select col_name1 [as] bieming,col_name2 [as] bieming2... from table_name
as 可以省略不写

给列起别名：

select sal gongzi from emp
select ename,sal,job from emp  
select ename sal,job from emp 
select ename sal from emp 
select ename sal_job from emp

给表起别名

select ename,sal from emp as e
select col_name1,col_name2.. from table_name bieming

3.条件查询

条件查询：

select col_name1,col_name2... from table_name where 
select :查询的关键字
col_name:字段名
from :后面跟目标表
table_name:目标表
where :过滤的关键字

1：关系（运算符）

=，>,<,>=,<=,<>/!=

求工资大于1500的员工姓名，工资

select ename,sal  from emp where  sal >1500

求ALLEN得入职日期

select hiredate from emp  where ename='ALLEN'
select hiredate from emp where ename='allen'
select sal,ename from emp where 'SMITH'>'ALLEN'
select sal,ename from emp where 1>0
select sal,ename from emp where 'SMITH'<'ALLEN'
select sal,ename from emp where 1=0

字符串比较大小，按字母的ASCII码比较大小
2：and ，or, not
求工资大于1000并且是10号部门的员工信息

select * from emp where sal>1000 and deptno=10

求20号部门的或者工作为SALESNAN的员信息

select from emp where deptno=20 or job='SALESMAN'

求10号的SALESMAN和20号部门的MANAGER

select*  from emp where deptno=10 and job='SALESMAN' or deptno=20  and job='MANAGER'

求不是10号部门的SALESMAN 或者 不是20号部门的MANAGER-----------?

select deptno,job  from emp where deptno<>10 and  job ='SALESMAN' or deptno<>20 and job='MANAGER'---错误 ，出来结果只能是SALESMAN 或者 MANAGER
select * from emp 
select deptno,job  from emp where deptno<>10 and  job ='SALESMAN' and deptno<>20 and job='MANAGER'----没有结果

select * from emp where (deptno<>10 or deptno<>20) and (job<>'SALESMAN' or job  <>'MANAGER')

3:in (not in 注意空值)
in 后面跟集合 表示 等于集合里的某个值

select ename,deptno from emp where deptno in (10,20)
select ename,deptno from emp where deptno not in (10,20)
select job,deptno from emp where (deptno,job) not in ((10,'CLERK'),(20,'MANAGER'))

求工资不是1000,2000,3000的员信息

select * from emp where sal not in (1000,2000,3000)

select * from emp

4:null:表示空值(任意值)
求没有佣金的员工信息

 select * from emp where comm is null
 select * from emp where comm =null
 select sal,sal*12,sal+comm,(sal+comm)*12 from emp

注：任何值和空值做运算，结果仍为空

select * from emp where sal  in (select comm from emp)
 有值：is not null
 select * from emp where sal not in (select comm from emp where comm is not null)-----去除空值

5:like 模糊查询

%：表示任意长度的字符
_:表示一个长度的字符
查询以S开头的员工姓名

select ename from emp where ename like 'S%'

查询以H结尾的员工姓名

select ename from emp where ename like '%H'

查询包含M的员工姓名

select ename from emp where ename like '%M%'

求第二位是M的员工姓名

select ename from emp where ename like '_M%'

求倒数第二位是T的员工姓名

select ename from emp where ename like '%T_'

求第二位是A，倒数第三位是L的员工姓名

select ename from emp where ename like '_A%L__'

求长度是五位的员工姓名

select ename from emp where ename like '_____'

求长度比五位多的员工姓名

select ename from emp where ename like '%______'

escape 转义符
求第三位是%的员工信息

select * from emp where ename like '__/%%' escape '/'
select * from emp for update

求第四位是下划线的员工信息

select * from emp where ename like '___*_%' escape '*'

求第一位是%，第四位是下划线的员工信息

select * from emp where ename like '!%__!_%' escape '!'

求第二位是%，三、四位是_,五位是% 的员工信息

select * from emp where ename like '_#%#_#_#%%' escape '#'

求姓名中包含H 和R的员工信息，并且，H在R前面

select * from emp where ename like '%H%R%'

求姓名中包含H 和R的员工信息

select * from emp where ename like '%H%R%' OR ename like '%R%H%'
select * from emp where ename like '%H%' and ename like '%R%'

求姓名中第五位是H的员工信息

select * from emp where eame like '____H%'

求姓名中第五十位是H的员工信息-------？ substr
求姓名中包含两个L的员工姓名

select ename from emp where ename like '%L%L%'

求姓名中包含两百个L的员工姓名-----？ instr

6 between and 全闭区间，两边都包括
求工资在1000 到3000 之间的员工信息

select * from emp where sal>=1000 and sal <=3000
select * from emp where sal between 1000 and 3000
select *  from emp where sal between  3000 and 1000
between A  and B  A<=B
select * from emp where ename between 'ALLEN' and 'SMITH'
select * from emp where ename between 'ALLEN' and 50---报错

7 exists

exists用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
EXISTS 指定一个子查询，检测 行 的存在。
–将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。
如果20号部门中有员工工资大于2000，则输出全部员工工资

SELECT SAL FROM EMP WHERE EXISTS (SELECT 1 FROM EMP WHERE SAL > 2000);

假设有两张表A1,A2，有相同的列（CJ），判断两张表中相同成绩的输出结果（CJ)

SELECT CJ FROM A1 WHERE CJ IN (SELECT A1 FROM A2)
SELECT CJ FROM A1 WHERE EXISTS (SELECT 1 FROM A2 WHERE A1.CJ = A2.CJ);

求emp和dept中相同的部门编号

SELECT DEPTNO FROM EMP WHERE EXISTS (SELECT 1 FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO);
SELECT * FROM EMP WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL);

求dept表中特有的部门编号

SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL);
SELECT DEPTNO FROM DEPT WHERE NOT EXISTS (SELECT 1 FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO);
SELECT DEPTNO FROM DEPT WHERE NOT EXISTS (SELECT 1 FROM EMP WHERE EMP.DEPTNO <> DEPT.DEPTNO)

;–返回与子条件相反的值

--EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用：
--IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
--exists （sql 返回结果集为真） 
--not exists (sql 不返回结果集为真）

4.排序查询

排序查询

select col_name1,col_name2... from table_name.. where ... order by col_name [asc]/desc 

order by :排序的关键字

asc:升序（默认）
desc:降序

查询员工信息，按照工资升序排列

select * from emp order by sal asc

查询10号部门工资小于1500的员工信息，按照员工编号降序排列

select * from emp where deptno=10 and sal<1500 order by empno desc

查询员工工资，姓名，部门编号，按照部门编号升序，工资降序排列

select sal,ename,deptno from emp order by deptno,sal desc
select * from emp order by empno,sal
select ename e,sal s from emp order by e
select e from emp order by ename e
select ename,sal,deptno from emp order by 3,2 desc
select * from emp order by 4,5,6
null:空值的大小
select comm from emp order by comm nulls first
select comm from emp order by comm desc nulls last

rownum:伪列 将查询结果自动生成从1开始，依次递增的自然数

select rownum from emp
select e.*,rownum from emp e
select e.*,rownum from emp e where deptno=10

注意：1，rownum与数据没有对应关系

select * from emp where rownum=1
select * from emp where rownum=2

2,rownum 不能大于等于比1大的数

select* from emp  where sal is not null and rownum<=3 order by sal desc
select * from emp where sal is not null order by sal desc 
select * from (select * from emp where sal is not null order by sal desc ) where rownum<=33,

3,不能直接order by 之后用 rownum 做对比
rowid:系统自动生成的18位字母随机构成的永不重复的字符串

select rowid from emp
select sys_guid() from dual
select length('8B9D202E80A14191812C7FDE788C8CCB') from dual

select e.*,rownum,rowid from emp e 

rowid 只能系统生成，无法手动加入

select * from emp where rowid='AAASsQAAEAAAAKrAAU'
select * from emp order by rowid

5.分组查询

分组查询：

select col_name1,col_name2... from table_name group by col_name

group by :分组的关键字
by:后面跟列名

聚合函数（组函数）：
max() 最大值
min() 最小值
avg() 平均值
sum() 求和
count() 求个数-----关键

求公司的最高工资

select max(sal) from emp 

求20号部门的平均工资

select avg(sal) from emp where deptno=20

求员工的平均佣金

select avg(comm) from emp
select comm from emp 

求十号部门的总工资

select sum(sal) from emp where deptno=10

求二十号部门有多少人

select * from emp where deptno=20
select count(empno) from emp where deptno=20
select count(ename) from emp where deptno=20
select count(job) from emp where deptno=20
select count(deptno) from emp where deptno=20
select count(*) from emp where deptno=20
select count(*) from emp
select count(0) from emp

求每个部门的最高工资

select max(sal) from emp group by deptno
select deptno from emp
select deptno,max(sal) from emp group by deptno
select ename,deptno,max(sal) from emp group by deptno
select max(sal) from emp 
select ename,max(sal) from emp 

解决：

select ename,deptno,max(sal) from emp group by deptno，ename

子查询：

select * from emp where (deptno,sal) in(select deptno,max(sal) from emp group by deptno)
select * from emp e where sal in (select max(sal) from emp where e.deptno=deptno group by deptno )
select count(empno),deptno,job from emp group by deptno,job order by deptno
select deptno from emp group by deptno
select job from emp group by job
select deptno,job from emp order by deptno

求每个部门的工资和

select sum(sal) from emp group by deptno

求工资和大于9500的部门编号

select deptno from emp where sum(sal)>9500 group by deptno 
having:表示过滤
select deptno,sum(sal) from emp group by deptno having sum(sal)>9500

where 与having 的区别
求不是20号部门且最高工资超过2000的部门编号

select max(sal),deptno from emp where deptno!=20 group by deptno having max(sal)>2000
select max(sal),deptno from emp   group by deptno having max(sal)>2000 and deptno!=20
select max(sal),deptno from emp   having max(sal)>2000 and deptno!=20-----报错

1：where是对原始数据做第一次的过滤，having是对分组之后的数据进行过滤
2:having的出现必须伴随着group by 的出现，而where与group 没有任何关系
3，理论上讲where后面的所有过滤条件均可放到having,工作中禁止将非聚合函数的过滤条件放到having后（优化）

select A from B where C group by D having E order by F

B-C-D-E-A-F
练习：
求每个部门的最高工资

select max(sal) from emp group by deptno 

求每个部门的最高工资,查询结果按照最大工资降序排列

select max(sal) from emp group by deptno order by max(sal) desc

求每个部门的最高工资及对应的部门编号,查询结果按照最大工资降序排列

select max(sal) m,deptno from emp group by deptno order by m desc

求每个部门的最高工资及对应的部门编号及姓名,查询结果按照最大工资降序排列

select max(sal),deptno,ename from emp group by deptno order by 1 desc------错误

求每个部门的最高工资及对应的部门编号,查询结果按照最大工资降序排列，并且不要20号部门信息

select deptno, max(sal)
  from emp
 where deptno <> 20
 group by deptno
 order by 2 desc

6.函数

1.length(str):返回一个字符串的长度(字符)

select length('ename') from emp
select length('ename') from dual
select length(ename) from emp
select ename,length('enam') from emp;
select ename,4 from emp

select length(job),ename,job from emp where ename='SMITH'
SELECT ename from emp where length(ename)=6

—lengthb()(字节)

 select lengthb('aaa') from dual---3
  select lengthb('哎哎哎') from dual----6（utf-8）9(GBK)

distinct:去重
emp--------去重是将所有指定的列的数据都相等的值去除

select distinct job from emp
select job,distinct deptno from emp----------distinct只能放在所有列之前
select  distinct job,ename from emp where length(job)=5 
select job,ename from emp where length(job)=5 group by job,ename

求姓名长度是50 位的姓名

select ename from emp where length(ename)=50

2.concat(str1,str2)：字符串连接函数,它和||实现的功能一样。

select concat('aaa','bbb') from dual;
select concat('姓名是:','ename') from emp
select concat(ename,sal) from emp;
select concat('aaa','bbb','ccc') from dual
select concat(concat('aaa','bbb'),'ccc') from dual
select concat('concat(aaa,bbb)','ccc') from dua

l-------当一个函数作为另一个函数的参数的时候，不要给这个函数加引号
select concat(‘ename’,‘bbb’) from emp;
将SMITH姓名的长度和KING姓名的长度拼接起来

select concat(length('SMITH'),length('KING')) from dual
select 'sss'||'ename' from emp 
select 'sss'||'aaa'||'bbb' from dual

求员工的工资，以 姓名是：smith,工资是：800 的形式显示

select '姓名是：'||ename||',工资是: '|| sal from emp
select '姓名是：'||ename ,  ',工资是：'||sal  from emp

select concat(concat('姓名是：',ename),concat('，工资是：',sal)) from emp

3.chr():将一个ASCII码转换成字符

select chr(97) from dual;
select chr(10) from dual;

将工资与姓名之间以空格的形式显示

select sal||chr(10) ||ename from emp;
select sal||' '||ename from emp 
select chr(52946)||chr(45230)||chr(50403) a from dual

select 'aaaaa'''||'bbbb' from dual;
select 'aaaaa'||chr(39)||'bbbb' from dual;

4.ascii(字符)：将一个字符转换成ASCII码值

select chr(ascii('a')) from dual;
select ascii('我') from dual;
select ascii('a') from dual;

5.instr(str,字符,ind,n):表示在字符串str中查找字符，从ind位置开始，n表示查询第n次出现

select instr('abcdefghigkasdfqwerqewrqwe','a') from dual;
select instr('abcdefghigkasdfqwerqewrqwe','f') from dual;
select instr('abcdefghigkasdfqwerqewrqwe','o') from dual;
select instr('abcidefghigkasdfqwerqewrqwe','i',5) from dual;
select instr('abcidefghigkasdfqwerqewrqwe','i',4) from dual;
select instr('abcidefghigkasdfqwerqewrqwe','e',-7) from dual;
select instr('abcdedcba','c',-4) from dual
select instr('abcdedcba','cd',-4) from dual
select instr('abcdedcba','bc',-4) from dual
select instr('abcdedcba','cb',-4) from dual

select instr('abcdedcba','a',0) from dual-------c是0，则最终显示为0
select instr('abcdefghigkasdfqwerqewrqwe','f',1) from dual;

select instr('abcidaefghigkasdfqwerqaewrqwe','a',1,2) from dual
select instr('abcidaefghigkasdfqwerqaewrqwe','a',2,2) from dual
select instr('abcidaefghigkasdfqwerqaewrqwe','a',2,5) from dual------如果D比目标字符出现的次数多，那么输出值为0，不循环
select instr('abcidaefghigkasdfqwerqaewrqwe','a',2,-2) from dual
select instr('abcidaefghigkasdfqwerqaewrqwe','a',2,0) from dual
select instr('abcidaefghigkasdfqwerqaewrqwe','a',50000) from dual------如果C超出字符串长度，则输出值为0


select ename,instr( ename,'L',2) from emp;
select instr('aaabbb','a',3,1) from emp
select instr('aaabbb','c') from emp

强调：
1，无论怎样查找，最终位置从左往右。从第一位开始数
2,c是负数，从后往前数位置，并且从后往前找目标字符串
求第五位是H的员工姓名

select ename from emp where ename like '____H%'

求第五十位是H的员工姓名

select ename from emp where instr(ename,'H',50,1)=50
select instr(ename,'H',50,1) from emp

求包含了50个H的员工姓名

select ename from emp where instr(ename,'H',1,50)>=50
select ename from emp where instr(ename,'H',1,50)=50---前50位都是H，则满足
select ename from emp where instr(ename,'H',1,50)>0

求H只出现了50次的员工姓名

select ename from emp where instr(ename,'H',1,50)>0 and instr(ename,'H',1,51)=0

求前一百位没有W的姓名

select ename from emp where instr(ename,'W',100,1)=0----错误
select ename from emp where instr(ename,'W',1,1)>100 or instr(ename,'W',1,1)=0

求只在姓名的一百位到一百一十位之间有M的姓名

select ename from emp where instr(ename,'M',1,1)>=100 and instr(ename,'M',111,1)=0

求姓名中带有三个L的姓名

select ename from emp where ename like '%L%L%L%'

    求姓名中带有三十个L的姓名

select ename
  from emp
 where instr(ename, 'L', 1, 30) > 0
   and instr(ename, 'L', 1, 31) = 0

求只有10位H出现的姓名

   select ename from emp where instr(ename,'H',1,10)=10 and length(ename)=10

    
select length('abcdefghigkasdfqwerqewrqwe') from dual

6.substr(str,ind,len):表示从ind位置开始截取字符串str，截取的长度为len

select substr('aa|bb|cc|dd|ee',5,1) from dual;

select substr('aa|bb|cc|dd|ee',3) from dual;
select substr('aa|bb|cc|dd|ee',-3,2) from dual;
select substr('aa|bb|cc|dd|ee',-6,3) from dual;

select substr('aa|bb|cc|dd|ee',0,3) from dual;
select substr('aa|bb|cc|dd|ee',50,3) from dual;
select substr('aa|bb|cc|dd|ee',1,-3) from dual;
select substr('aa|bb|cc|dd|ee',1,0) from dual;------c为负数或者0，结果都为空
select substr('aa|bb|cc|dd|ee',1,50) from dual;
select substr('123456',-1,2) from dual

求姓名中第三位是I的姓名

select ename from emp where ename like '__I%'

求姓名中第三十位是I的姓名

select ename from emp where substr(ename,30,1)='I'

JIGUAN
山东省青岛市
山东省即墨区
黑龙江省大庆市
黑龙江省齐齐哈尔市
新疆省乌鲁木齐市
1,求a表中所有人员的籍贯例如：山东，黑龙江
2，求籍贯内省后面的所有内容

create table AA(jg varchar2(20));
alter table AA add constraint p_k primary key (jg);
insert into AA values('山东省青岛市');
insert into AA values('山东省即墨区');
insert into AA values('黑龙江省大庆市');
insert into AA values('黑龙江省齐齐哈尔市');
insert into AA valu es('新疆省乌鲁木齐市');
select * from AA
select substr(jg,1,instr(jg,'省')-1) from aa

instr(jg,'省')
select substr(jg,instr(jg,'省')+1) from aa


select substr('aa|bb|cc|dd|ee',
              instr('aa|bb|cc|dd|ee', '|', 1, 2) + 1,
              instr('aa|bb|cc|dd|ee', '|', 1, 3) -
              instr('aa|bb|cc|dd|ee', '|', 1, 2) - 1)
  from dual;

强调：无论b的正负，c永远从前往后截c位

7.trim(str)函数：如果只跟一个参数，表示去除字符串两端的空格

 ltrim(str,):去掉左边空格
  rtrim(str):去掉右边空格
select trim('         adsf          ') from dual;
select ltrim('         adsf          ') from dual;
select rtrim('         adsf          ') from dual;

select trim('$' from '$$$adsfqwe$$$') from dual;
select ltrim('$$$adsfqwe$$$','$') from dual;
select rtrim('$$$adsfqwe$$$','$') from dual;

pad填充函数

select lpad('aaabbb',10,'$') from dual
select rpad('aaabbb',10,'@') from dual
select rpad(lpad('aaabbb',10,'#'),15,'^')from dual
select lpad('aaabbb',7,'龙') from dual
select lpad('aaabbb',8,'龙') from dual
select lpad('aaabbb',9,'龙') from dual
select lpad('aaabbb',10,'龙') from dual
select rpad('aaabbb',10,'彩虹') from dual
select rpad('aaabbb',11,'彩虹') from dual
select rpad('aaabbb',12,'彩虹') from dual

select lpad('aaabbb',5,'^') from dual
select rpad('aaabbb',5,'^') from dual

将员工姓名以十位输出。长度不足用空格补齐

select rpad(ename,10,chr(10) ) from emp

8.initcap(str)：将字符串的英文单词首字母大写,其余全部小写(以空格来区分单词的)

select initcap(ename) from emp;
select initcap('hello world') from dual

9.lower/upper():大小写转换函数

lower(str)：将所有字符转换成小写
upper(str):将所有字符转换成大写
select lower('AER1') from dual;
select upper('Hello World') from dual;

select lower(substr(ename,1,1))||upper(substr(ename,2)) from emp
select substr(upper(ename),1,1)||substr(lower(ename),2) from emp
select upper(substr(ename, 1, length(ename) - 2)) ||
       lower(substr(ename, -2, 1)) || upper(substr(ename, -1))
  from emp

10.replace(str,s,d)：字符串替换函数，将字符串str中的s字符替换成字符d

select replace('asdfasdf','B','A') from dual;
select replace('aaabbb','b','') from dual

求01010001110000011111 中0有几个？

select replace('01010001110000011111',1,null) from dual
select replace('01010001110000011111',1,'') from dual
select replace('01010001110000011111',1,' ') from dual---错误
select length(replace('01010001110000011111',1,null)) from dual
select length(replace(10001010001110000011111,1,null)) from dual

select replace(ename,substr(job,2,1),substr(ename,3,2)) from emp 
select ename,
       replace(ename, substr(ename, instr(ename, 'A', 1, 1), 1), 'B')
  from emp
select replace('aaaaa','b','a') from dual

11.round()：四舍五入函数
round(num,精度)

select round(4.834859,2) from dual;

select round(4.834859,3) from dual;

select round(4.834859,0) from dual;
select round(4.834859) from dual;

select round(45678.834859,-3) from dual;
select round(45678.834859,-7) from dual;

select round(-0.6) from dual;
补充：
abs() 绝对值
select abs(6) from dual
select abs(-6) from dual

12.floor():向下取整

select floor(1234.838134) from dual;
select floor(1234.838134，2) from dual;----报错

select floor(-166.1235) from dual;

13.ceil():向上取整

select ceil(1234.1234) from dual;

select ceil(-1234.1234) from dual;

14.mod()：求余函数
a/b=c…d

select mod(12,5) from dual;
select * from emp where mod(empno,2)=0
select * from emp where mod(empno,2)=1

select ename,empno from emp where mod(empno,sal)=0
select empno from emp where mod(sal,empno)<>0

15.to_number():将字符串转换成数字
$它是一个货币
L本地货币符号
0:表示必须有一位长度
9：表示可有（可没有的一位长度）

select to_number('$977.9890','$999000.0000000') from dual;

select to_number('97.9898','99.9999') from dual;

select to_number('￥85.123','L99.999') from dual;
select * from emp for update

16.power()：幂指函数

select power(3,3) from dual;
select power(3,2) from dual;
select power(27,1/3) from dual

17.sqrt()：平方根

select sqrt(4) from dual;

select sqrt(3) from dual;

18.to_date():将字符串转换成日期

yyyy表示4位的年
MM表示2位的月
dd表示2位日
hh24表示24小时制时间
mi:表示分钟
ss：表示秒
day:表示星期

select to_date('1999','yyyy') from dual;
select to_date('03','mm') from dual;
select to_date('03','dd') from dual;
select to_date('16','hh24') from dual;
select to_date('16','mi') from dual;

select to_date('1999-09-09 22:56:18','yyyy-MM-dd hh24:mi:ss') from dual;
insert into emp(empno,ename,hiredate) values(0000,'AAA', to_date('1999-09-09 22:56:18','yyyy-MM-dd hh24:mi:ss'))
select * from emp where empno=0000
select to_date('1999/09/09 22:56:18','yyyy/MM/dd hh24:mi:ss') from dual;

19.to_char():字符串转换函数，可以将数字，日期等转换成字符串

to_date('2019-10-24','yyyy-mm-dd')
select to_char(to_date('2019-10-24','yyyy-mm-dd'),'yyyymmdd') from dual
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'year') from dual;
select to_char(sysdate,'mm') from dual;
select to_char(sysdate,'month') from dual;
select to_char(sysdate,'dd') from dual;
select to_char(sysdate,'hh24') from dual;
select to_char(sysdate,'mi') from dual;
select to_char(sysdate,'ss') from dual;
select to_char(sysdate,'day') from dual;
select to_char(sysdate,'q') from dual;
select to_char(sysdate,'am') from dual;
select to_char(sysdate,'cc') from dual;
select to_char(sysdate,'yyy') from dual;
select to_char(sysdate,'yy') from dual;
select to_char(sysdate,'y') from dual;
select to_char(sysdate,'yyyyy') from dual;
select to_char(sysdate,'yyyyyy') from dual;
select to_char(sysdate,'ddd') from dual;
select to_char(sysdate,'d') from dual;
select to_char(sysdate,'dddd') from dual;
select to_char(sysdate,'E') from dual;
select to_char(sysdate,'yyddccamday') from dual;
select to_char(sysdate,'yy/dd/cc:am{day^') from dual;

20.sysdate 系统当前日期

select sysdate from dual;

21.last_day(日期):取当前日期月的最后一天

select last_day(sysdate) from dual;
select * from emp where hiredate=last_day(hiredate)-2
select last_day(sysdate)+3 from dual

next_day 求下一个周几是几号

select next_day(sysdate,'星期日') from dual;
select next_day(sysdate,7) from dual;

求下下一个周五的日期

select next_day( next_day(sysdate,6),6) from dual

22.add_months(日期,月)：给一个日期加上若干个月

select add_months(sysdate,1) from dual;

select add_months(sysdate,-12*3) from dual;
select sysdate+2 from dual
select sysdate-hiredate from emp

23.months_between(date1,date2)取两个日期相差的月数

select hiredate,months_between(sysdate,hiredate) from emp;
select hiredate,sysdate,months_between(hiredate,sysdate) from emp
select hiredate,abs(months_between(hiredate,sysdate)) from emp;

select abs(-5) from dual

select (sysdate-hiredate)/365 from emp 
select (sysdate-hiredate)/365,months_between(sysdate,hiredate)/12 from emp
select sysdate-hiredate,months_between(sysdate,hiredate)*30 from emp
select sysdate-hiredate from emp

24.trunc():截断函数，可以将数字，日期等截断

select trunc(1234.8934,1) from dual;
select trunc(1234.8934) from dual;
select trunc(1234.8934,-3) from dual;

select trunc(-1234.8934) from dual;

select trunc(1234.8934,1) from dual;

select trunc(-1234.8934,-3) from dual;
select round(12345,-6) from dual

select trunc(sysdate,'day') from dual; --截断日期后返回的是一个日期，并不是一个字符串 --按星期截取日期，返回当前日期所在星期的星期日
select sysdate from dual
select trunc(sysdate,'yyyy') from dual;

日期的直接加减一个数字，加的是天

select trunc(sysdate,'year') from dual;--取当年的第一天

select trunc(sysdate,'month') from dual;--取当月第一天
select trunc(sysdate,'dd') from dual;--取当日第一时刻（0时0分0秒）
select trunc(sysdate,'hh') from dual;--取当前时间的0分0秒
select trunc(sysdate,'mi') from dual;--取当前时间的0秒
select trunc(sysdate,'ss') from dual;--最小到分钟

补充：
select round(sysdate,'month') from dual
select round(sysdate,'yyyy') from dual
select round(sysdate,'dd') from dual
select round(sysdate,'hh') from dual
select round(sysdate,'mi') from dual
select round(sysdate,'day') from dual

case when :
select sal from emp
case when 

    select  case when tiaojian then  
   when   tiaojian then 
     when  tiaojian then 
       else 
         end 

给工资进行分类，低于1000的为low.1000-2000的为middle，2000以上的为high,其余情况为 暂无

select sal,case when sal<1000 then 'low' 
            when sal>=1000 and sal<2000 then 'middle'
              when sal>=2000 then 'high'
                else '暂无'
                  end gongzifenbu
                  from emp
   

给员工的部门分类，10号部门为第一部门，20号部门为第二部门，
三十号部门为第三部门，其余部门为第四部门

select deptno,case when deptno=10 then '第一部门'
               when deptno=20 then '第二部门'
                 when deptno=30 then '第三部门'
                   else '第四部门'
                     end bumenfenlei
                     from emp

select deptno,case deptno
when 10 then '十号部门'
  when 20 then '二十号部门'
    when 30 then '三十号部门'
      else '四十号部门'
        end gzfb
        from emp
  select * from emp 

  select ceil(dbms_random.value(1,58)) from dual

将员工编号进行分类，员工编号为奇数的 命名为奇数编号，员工编号为偶数的，命名为偶数编号

 select empno,case mod(empno,2)
  when 0 then '偶数编号'
    else '奇数编号'
      end bianhaofenlei
      from emp
      
      
        select empno,case mod(empno,2)
  when 0 then '偶数编号'
   when 1 then  '奇数编号'
      end bianhaofenlei
      from emp
 
 
 select sex,case sex
 when '0' then '女'
   else '男'
     end 
     from emp 
 
 select * from emp
 alter table emp add (sex  number)
 select * from emp for update

求班里同学的男生跟女生的人数。

select sex,count(empno) from emp group by sex
 
select sex,case sex
when 0 then '女'
  when 1 then '男'
    end from  emp
    sum(case when):行转列
    

 select sex,
    sum(case when sex=0 then 1 else 0 end ) 女生人数,
    sum(case when sex=1 then 1 else 0 end) 男生人数
    from emp group by sex
    
    
     select sex, 
  (case when sex=0 then 1 else 0 end ) 女生人数,
    (case when sex=1 then 1 else 0 end) 男生人数
    from emp 

求每个部门的人数

 select deptno，count(empno) from emp group by deptno
 
 select
 max(case when deptno =10 then 2  else 5 end) 十号部门人数,
 max(case when deptno =20 then 2  end) 二十部门人数,
 max(case when deptno =30 then 2 end) 三十部门人数,
 max(case when deptno not in (10,20,30) then 2  end) 其余部门人数 
 from emp
 select * from s
 
 select xingming,
 sum(case when kemu='yuwen' then chengji end) yuwen,
  sum(case when kemu='shuxue' then chengji end ) shuxue,
 sum(case when kemu='yingyu' then chengji end ) yingyu from s group by xingming

select deptno,count(empno) from emp group by deptno
select sal,decode(sal,800,'low',2000,'middle',3000,'high') from emp

select deptno,decode(deptno,10,‘10部门’,20,‘20号部门’,30,‘30号部门’,‘其他部门’) from emp

select sal from emp 
select sex,decode(sex,0,'女','男') from emp
select 
sum(decode(deptno,10,1)) shihao,
sum(decode(deptno,20,1)) ershihao,
sum(decode(deptno,30,1)) sanshihao,
sum(decode(deptno,40,1)) sishihao
from emp 
select * from a
select year,sum(case when month=1 then amount end) m1,
sum(case when month=2 then amount end) m2,
sum(case when month=3 then amount end) m3,
sum(case when month=4 then amount end) m4
from a group by  year

select year,sum(case when year=1991 and month=1 then 1.1  when year=1992 and month=1 then 2.1 end ) m1 from a group by year
select year,sum(case when year=1991 and month=1 then 1.1 end) m1 from a group by year

开窗函数:
开窗函数不改变行数，显示某个值在某一特定分组下的具体地位

（开窗聚合函数）：

select ename，max（sal） from emp 
select ename,max(sal)over() from emp 
select deptno,ename,max(sal)over(partition by deptno  order by sal ) from emp
select deptno,max(sal)over(partition by deptno  order by sal desc) from emp
select deptno,max(sal)over(partition by deptno  order by sal ) from emp
select deptno,min(sal)over(partition by deptno order by sal) from emp
select deptno,ename,min(sal)over(partition by deptno  order by sal desc ) from emp
select deptno,ename,min(sal)over(  order by sal  ) from emp
select deptno,ename,max(sal)over(  order by sal desc  ) from emp
select deptno,ename,sal,avg(sal)over(  partition by deptno order by sal   ) from emp
select deptno,ename,sal,avg(sal)over(  partition by deptno order by deptno  ) from emp
select deptno,ename,sal,count(sal)over(partition by deptno order by sal) from emp 
select deptno,ename,sal,count(sal)over(partition by deptno ) from emp 
select deptno,ename,sal,job,count(sal)over(partition by job) from emp 
select deptno,ename,sal,job,sum(sal)over(partition by deptno order by sal) from emp
select deptno,ename,sal,job,sum(sal)over(partition by deptno order by sal desc) from emp

over(partition by 分组列 order by 排序列)：over子句中的分组partition by和group by的分组不同，它不会把数据聚合成一条，在over子名中可以省略
开窗函数（分析函数）：

row_number():必须和over形容函数联用，排序的序号和rownum伪列相同，连续序号，不考虑值相等的情况（值相同序号不相同）
rownum不支持>, >=, =, !=, between…and…这几个运算符，只能用符号(<、<=)
dense_rank():可以用来做排序，它序号连续，考虑重复数据，如果值相等序号就相同
rank():可以用来做排序，它序号不连续，考虑重复数据，如果值相等序号相同
ntile(N):对表里的数据进行分组之后分配指定范围内的生成序号 (总行数/总组数)+1

select sal,row_number()over(order by sal desc) from emp    --12345678
select sal,dense_rank()over(order by sal desc) from emp    --1234456678
select sal,rank()over(order by sal desc) from emp  --11145668     
select deptno,sal,ntile(4)over( partition by deptno order by sal) from emp   
delete from emp where sal is null
select * from emp 

select deptno,sal,row_number()over(partition by deptno order by sal desc) from emp 
select deptno,sal,dense_rank()over(partition by deptno order by sal desc) from emp 
select deptno,sal,rank()over(partition by deptno order by sal desc)  from emp  

over(partition by 分组列 order by 排序列)：over子句中的分组partition by和group by的分组不同，它不会把数据聚合成一条，在over子名中可以省略

select e.*,row_number()over(order by empno desc) r from emp e where  ;

select e.*,dense_rank()over( order by sal desc) r from emp e;

select e.*,rank()over(order by sal desc) r from emp e;

求每个部门工资第二高的员工姓名（不考虑相等的情况）

select ename from emp where sal is not null and  row_number()over(partition by deptno order by sal desc desc)=2

select ename
  from (select sal,ename,
               row_number() over(partition by deptno order by sal desc) d
          from emp where sal is not null) e
 where e.d = 2

求工资第五位的员工信息，不考虑重复

select sal from emp where sal is not null  order by sal desc
select e.*,rownum from (select sal from emp where sal is not null  order by sal desc) e 
select *
  from (select e.*, rownum r
          from (select sal from emp where sal is not null order by sal desc) e)
 where r = 5
 select e.*,row_number()over( order by sal desc) a  from emp e where sal is not null
 select * from ( select e.*,row_number()over( order by sal desc) a  from emp e where sal is not null) where a=5

例子：

select e.*,row_number()over(partition by deptno order by sal desc) r from emp e;

select e.*,dense_rank()over(partition by deptno order by sal desc) r from emp e;

select e.*,rank()over(partition by deptno order by sal desc) r from emp e;

使用row_number(),rank(),dense_rank()的分页sql,只需要一层子查询

select 
sum(case when sex=1 then 1  end) 女,
sum(case when sex=0 then 1  end) 男 from emp
  select job,count(empno) from emp group by job
  select 
  sum(case when job='CLERK' then 1 else 0 end ) CLERK,
  sum(case when job='SALESMAN' then 1 else 0 end ) SALESMAN,
  sum(case when job='PRESIDENT' then 1 else 0 end) PRESIDENT,
  sum(case when job='MANAGER' then 1 else 0 end) MANAGER,
  sum(case when job='ANALYST' then 1 else 0 end) ANALYST
  from emp 

nvl:
nvl(str,s) 转换空值

select comm,nvl(comm,0) from emp
select deptno,nvl(deptno,2) from emp
select sal,comm,sal+comm from emp---任何值与空值计算，结果仍为空
select sal,comm,sal+nvl(comm,0) from emp，
select deptno,nvl(deptno,'没有部门') from emp
select ename,nvl(ename,'2') from emp

select comm,nvl2(comm,‘1’,‘aaa’) from emp------- 两个参数必须是同一类型
判断员工的提成，如果有提成，则输出 薪资是： ，如果没有提成，输出 没有提成
select sal,comm,nvl2(comm,‘薪资是：’||(sal+comm),‘没有提成’) from emp

7.子查询

子查询：在一个查询语句中（主句）的某些位置嵌套多个查询语句（子句）
某些位置：select a from b where c group by d having e order by f
在a,b,c,d,e,f中，？？？可以放子句
嵌套：子句一定是完整的查询语句，在特定情况（相关子查询和非相关子查询）下，可以单独运行
特定情况—注：非相关子查询指子句和主句之间没有数据传参，此时子句可单独运行
—相关子查询指子句和主句之间有数据传参，此时子句不可单独运行，需要主句传递参数
多个查询语句：一个主句不止一个子句，理论上讲，一个主句最多可有255个子句
行、列

单行单列：select ename from emp where empno=7369
多行单列：select ename from emp 
单行多列：select ename,deptno,sal from emp where empno=7369
多行多列：select ename,deptno,sal from emp

1，在 C（where）后面

单行单列：select * from emp where ename=(select ename from emp where empno=7369)
select * from emp where job=(select ename from emp where empno=7369)--可
select * from emp where sal=(select ename from emp where empno=7369)--不可
多行单列：select * from emp where ename in (select ename from emp )
单行多列：select * from emp where (ename,deptno,sal) in (select ename,deptno,sal from emp where empno=7369)
select 2 from dual where (1,2,3) in((1,2,3),(1,2,4),(2,3,4),(2,1,3),(3,2,1))
多行多列：select* from emp where (ename,deptno,sal)in(select ename,deptno,sal from  emp)

2,在 E（having）后面
因为having后面加聚合函数的过滤条件，所以having后面一般跟返回值为单行单列的子句
3.在 B（from）后面
任何返回结果都可以放在from后面作为子句
4.在 A（select）后面
单行单列：select ‘姓名是’||ename from emp
select ‘姓名是’ from dual
select ename,(select ename from emp where empno=7369) from emp
多行单列：select (select ename from emp ),sal from emp ----非相关子查询不可
select (select ename from emp where empno=e.empno),sal from emp e—用相关子查询将多行单列的值转换为 单行单列
select (select ename from emp where deptno=e.deptno) ,sal from emp e
单行多列：select mgr,(select ename,deptno,sal from emp where empno=7369) from emp–不可
多行多列：-------不可

1，求公司最高工资的员工姓名及工资
1）判断是否有子句，条件是否够用
2）先写子句
3）再写主句
1)select ename,sal from emp where sal=max(sal)–判断，需要子句
2）select max(sal) from emp–先写子句
3）select ename,sal from emp where sal =(select max(sal) from emp)–再写主句

2,求在DALLAS工作的员工姓名
1）判断是否有子句，条件是否够用
2）先写子句
3）再写主句
1）select ename from emp where loc=‘DALLAS’–判断，需要子句
2) select deptno from dept where loc=‘DALLAS’–先写子句
3) select ename from emp where deptno=(select deptno from dept where loc=‘DALLAS’)–再写主句

求每个部门最高工资的员工姓名及工资

select max(sal) from emp group by deptno
select deptno,ename,sal from emp where (sal,deptno) in (select max(sal),deptno from emp group by deptno)
select deptno,ename,sal from emp e where sal in (select max(sal) from emp where e.deptno=deptno group by deptno)

有两张表，emp和dept,求两个表里面都有的部门编号
第一：求一张表中的所有部门编号
第二：求另外一张表中的在第一张表中的部门编号
第三：求两张表的特有部门编号

求工资前五名的员工姓名
求工资五到十名的员工姓名

dense_rank
select deptno,dense_rank()over(partition by deptno order by sal desc) from emp

普通查询？

select count(empno)+ 1 from emp where sal>e.sal
select sal,
       deptno,
       (select count(empno) + 1
          from emp
         where sal > e.sal
           and deptno = e.deptno)
  from emp e
 order by e.deptno, e.sal desc
rank
select deptno,sal,rank()over(partition by deptno order by sal desc) from emp

普通查询？

select sal,
       deptno,
       (select count(1) + 1
          from  (select distinct deptno,sal from emp)
         where sal > e.sal
           and deptno = e.deptno )
  from emp e
  
 order by e.deptno, e.sal desc

8.联合查询（多表查询）

联合查询(多表查询)：

select col_name1,col_name2... from table_name1,table_name2... where ...
select ename from emp
select e.ename from emp e
select ename,dname from emp,dept
select ename,d.deptno from emp e,dept d
select * from emp e，dept d----56  14*4
select * from emp---14
select * from dept---4
select * from dept d,emp e----56  14*4

笛卡尔积：当多个表进行联合查询时,A(m),B(n),C(l),必定会产生mnl条查询结果，无法消除笛卡尔积
消除笛卡尔积带来的影响：
求所有员工信息和他们所在部门的信息

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

用where或者having 可以消除笛卡尔积带来的影响
表连接：将所有的目标表通过特定连接方式，连接成为一张临时表，在临时表内做查询

内连接：A inner join B on 
外连接：A outer join B on
左外：A left outer join B on
右外：A right outer join B on 
全外：A full outer join B on

insert into emp(empno,ename) values(1112,'sss')
select * from emp---15
select * from dept---4
内连接：A inner join B on       select * from emp e join dept d  on 

e.deptno=d.deptno(14条数据，emp的1112，deptde40部门消失)
外连接：A outer join B on
左外：A left outer join B on    select * from emp e left join dept d on e.deptno=d.deptno（15条，empde1112存在，deptde40部门消失）
右外：A right outer join B on   select * from emp e right join dept d on e.deptno=d.deptno（15条，empde1112消失，deptde40部门存在）
全外：A full outer join B on    select * from emp e  full join dept d on e.deptno=d.deptno （16条，empde1112，deptde40部门都存在）

写sql时，不要着急判断是左外、右外、全外，先用内连接写，之后根据提议判断是否丢失数据，如果丢失数据，则按照题目要求选择左外、右外、全外

natural join  
select * from emp natural join dept
select * from emp natural join emp ---60 15*4

select * from emp e join dept d on e.deptno=d.deptno
select deptno from (select * from emp e join dept d on e.deptno=d.deptno)

优势：能够自动消除重复列，查询结果跟内连接一致

using:
select * from emp join dept using(deptno,empno)
select * from emp e join emp using(e.deptno)

using只适用于不同表之间的连接，不能做自连接

cross join :笛卡尔连接
select* from dept cross join emp

自连接：
求员工姓名和其领导姓名

select e1.ename,e2.ename from emp e1 join emp e2 on e1.mgr=e2.empno
select * from emp

不等值连接：
员工求比SCOTT工资高的姓名及工资
select e2.ename, e2.sal, e1.sal from emp e2, emp e1 where e1.ename=‘SCOTT’ and e2.sal > e1.sal ;
select * from salgrade—查询工资等级

求所有员工的工资及工资等级

select a.ename, a.sal 员工工资, b.grade 工资等级 from emp a, salgrade b where a.sal between b.losal and b.hisal;

9.建表

lll/DDL:数据的定义语言
create drop alter
DML:数据的操作语言
insert into delete update (select)
JAVA: C(create) R(read) U(update) D(delete)
DQL:数据的查询语言
select from where group by having order by
DCL:控制语言 grant revoke
TCL:事务操纵语言
commit rollback savepoint
oracle的存储结构
数据块：数据块是oracle中最小的存储单位，它是操作系统最小单位的整数倍，8k,16k
数据区:它是由若干个数据块组成,新建一张表，在没有插入数据情况下，oracle给它分配的存储大小就是一区的大小
数据段：它是由若干个数据区组成，它是oracle中存储实体对象的最小单位
表空间：表空间是由若干个数据段组成
存放表，视图，索引等
一个表只能属于一个表空间，
一个表空间可以放任意多个表
一个表空间至少有一个存储文件.dbf文件，可以有多个，而且这多个数据文件可以在不同位置

一个用户有一个默认的表空间，一个用户可以在默认表空间外的其它表空间建表
create table 表名(
)tablespace 表空间名;
如果不加tablespace，建表时会将表建在用户的默认表空间中

表空间文件在:
d:/app/administrator/oradata/orcl/ *.dbf
查询表空间文件目录：
select * from dba_data_files;

创建表空间：
create tablespace 表空间名 datafile ‘数据文件路径’ size 初始大小（2G） autoextend on next 每次扩展的大小(100M) maxsize 最大容量（unlimited）;

create tablespace tabspace datafile ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS02.DBF’ size 2G autoextend on next 100M maxsize unlimited;

创建临时表空间：
create temporary tablespace 临时表空间名 tempfile ‘数据文件路径’ size 初始大小（2G） autoextend on next 每次扩展的大小(100M) maxsize 最大容量（unlimited）;

create temporary tablespace tabtemp tempfile ‘/u01/app/oracle/oradata/bow/tabtemp.dbf’ size 2G autoextend on next 100M maxsize unlimited;
drop tablespace tabspace
创建用户：
create user 用户名 identified by 密码 default tablespace 表空间名 temporary tablespace 临时表空间名;

default后面的语句是为用户指定默认表空间和临时表空间，如果不指定，默认的表空间是users表空间，临时表空间是temp
create user bw2 identified by bw default tablespace UNDOTBS1 temporary tablespace tabtemp;
create user sss identified by aaa
grant create user to scott
给用户赋权限：
grant 权限 to 用户名;
create role “session”
grant resource,connect to “session”; --连接权限和资源权限
grant “session” to sss;–连接权限和资源权限

grant create any table to sss; --建表权限

grant select any table to sss; --只读权限
select * from scott.emp

grant create any view to bw; --创建视图权限
grant dba to aca; --管理员权限
create user aca identified by 111

1.以管理员的身份创建表空间和临时表空间
2.创建用户
3.给用户赋权限

select table_name from user_tables;
select * from emp
建表
1.建表
数据类型：
数字类型：
number(l,s)：l表示长度，默认1，最长38，s表示精度
number(6,2)
234.56
44456.00 number(7,2)
insert into emp(empno,ename,comm) values(7674,‘A’,771237.5)
create table jjj(aa number)
select * from jjj
insert into jjj values(1.875422)
select * from emp
int integer float double
字符串类型：
varchar2(l):变长字符串类型，最多个可以存放4000个字符（英文字符），默认长度1
char(l):定长字符串类型，l表示长度 例如：char(8) 存放,这个字符串的长度就是8
aaa varchar2(10) aaa char(10) aaa
long:它可以存放2g的字符串（不建议使用）
string
日期类型：
date:日期类型，可以存放世纪、纪元、年、月、日、时、分、秒
timestamp:时间戳类型,可以存放世纪、纪元、年、月、日、时、分、秒还可以存放秒后9位
CLOB/BLOB:
CLOB:大文本文件类型，可以存放word,excel等文档
BLOB:二进制文件类型，可以存放视频、音频等
建表语法：
create table 表名(
列名 类型 约束 默认值，
列名 类型 约束 默认值，
…
列名 类型 约束 默认值，
表级约束
)tablespace 表空间;
create table emppp as select * from emp where 1=0
select * from emppp

create table DEREGU-ARB_003_01 as
select DKSJFFRQ, MXKMMC, DKLX, KHTYBH, JKJE, XDJJH
from DGXDYWJJ
where DYSJFFRQ between to_date((“201506”), “yyyymm”) and to_date(("201606"“yyyymm”)
and (MXKMMC = “%贴现%” or DKLX = “贴现”)

create table emmp(
empno number(4) not null ,
ename varchar2(10)
constraint emp_empno_un1que unique(empno))
select * from emmp

约束：
行级约束：紧跟在列的声名后面的约束叫行级约束
表级约束：在所列声名后面的约束叫表级约束，所有使用alter语句添加的约束基本都是表级约束
1.非空约束
列名 类型 not null --行级约束

非空约束只能是行级约束
2.唯一约束
列名 类型 unique --行级约束
constraint 约束名 unique(列) --表级约束语法
constraint emp_ename_un unique(ename)
3.主键约束
主键约束是not null和unique合体
列名 类型 primary key (PK) --等级约束
constraint 约束名 primary key(列名,列名) --表级约束

create table y(a number,b number, constraint y_a_b_pk primary key (a,b))

create table yy(a number primary key,b number primary key )

select * from y for update
4.检查约束
列名 类型 check(条件表达式) --等级约束
sex char(1) check(sex in (0,1))
constraint 约束名 check(条件表达式) --表级约束，注意，检查约束一个约束只能约束一列
5.外键约束
列名 类型 references 主表(主键列) --等级约束，外键列的值只能从主表中主键列的值中取

 constraint 约束名 foreign key(外键列) references 主表(主键列)   --表级约束
drop table persion;
insert into emp(empno,ename,deptno) values(1234,'aa',70)

create table persion(
   id  number(11) primary key,
   name varchar2(30) not null,
   age number(3) check(age between 0 and 150),
   addr varchar2(255)
);
create table employee(
   id number(11) primary key,
   parentid number(11) constraint fk_emp references persion(id),
   job varchar2(30),
   hiredate date,
   sal number(15,2),
   mgr number(10),
   code number(10) unique,
   constraint hiredate_check check(hiredate>to_date('19770101','yyyyMMdd'))
);  

create table cjb(
xingming varchar2(10)  not null,
xuhao   char(5) primary key,
xingbie  number(1) default 1 check (xingbie in (0,1)) ,
kssj  date not null)
insert into cjb3 (xingming,xuhao,kssj) values('AAA',233,sysdate)
insert into cjb3 (xingming,xuhao,xingbie,kssj) values('AAA',213,default,sysdate)

select * from cjb3

drop table persion

('AA',333,1,sysdate,20)

select * from course
select * from cjb
drop table cjb
drop table employee;

标识符命令规范：
标识符：表名、视图名、索引名等对象名，约束名、变量名、存储过程函数触发器包等的名字都是标识符
最大长度不能超过30个英文字符，
一般以英文字母开头，不能以特殊字符和数字开头，除了$,#
如果标识符中有特殊字符要用双引号引起来
标识中还可以有数字，单词和单词之间一般以_连接的 start_time

插入数据：

insert into emp values(,,,,,,,,,,,)
insert into emmp(empno,ename) values(1111,'AAA')
insert into emmp  select empno,sal,deptno  from emp where empno=7369
select 222 ,'BB' from dual
insert into emmp(empno,ename) values(333,'cc')
select * from emmp
insert into emmp 
select 111,'aa' from dual
union all
select 222,'bb' from dual
union all 
select 333,'cc' from dual
select * from emmp

删除数据：

delete from emmp where empno=5544
delete from emmp where sex is null

修改数据：

update emmp set sal=444
update emmp set empno=555 where ename='bb'
update emmp set job='' where empno=7499
select * from emmp where job is null
update emmp set job=null where empno=7369
update emmp set empno=null
update emp set empno=(select empno from emp where ename='ALLEN') where ename='SMITH'

将king的工资赋予ALLEN

update emmp set sal=(select sal from emmp where ename='KING') where ename='ALLEN'
commit--提交
rollback--回滚
select * from emp 

drop:
drop table emmp
select * from emmp

TRUNCATE
修改表和约束
select * from cjb
添加列

alter table 表名 add 列名 类型 [约束 默认值]; --给表中添加一个新的列
alter table cjb5 add dizhi varchar2(12) default 'xuexiao' 
alter table cjb add dizhi varchar2(255) 
select * from cjb

删除列

alter table 表名 drop column 列名;   --删除表中的列
alter table cjb drop column dizhi

修改列

alter table 表名 modify 列名 类型 [约束 默认值]; --修改表中的列
alter table cjb modify dizhi  char(10)

alter table 表名 modify 列名 类型 null; --去掉列的非空约束
alter table 表名 modify 列名 类型 not null; --给列添加非空约束
alter table cjb modify xingming not  null
insert intoy 'xuexiao' ()
select * from emp
alter table emp modify empno char(4)
insert into cjb values('sss',20,1,sysdate,'qq')
insert into cjb values('',30,1,sysdate,'qq')

添加约束
alter table 表名 add 表级约束语法; --给表添加表级约束

select * from cjb
alter table emp add constraint emp_job_unique unique(job)
alter table cjb add constraint cjb_dizhi_unique unique(dizhi)
update cjb set dizhi='qqq' where xingming='sss'

删除约束

alter table 表名 drop constraint 约束名;
alter table cjb drop constraint  cjb_dizhi_unique
insert into cjb values('zzz',40,1,sysdate,'qq')

–删除一个约束

修改表名

alter table 表名 rename  to 新表名;
alter table cjb rename to cjb5
create table cjb  as select * from cjb5 
create table cjb like cjb5

select * from cjb5

修改列名

alter table cjb5  rename column xuehao1 to xuehao2
alter table cjb5 rename column xuhao to xuehao

如何将一列有数据的类型修改为其他类型
（将emp的empno的number改为varchar2）
1,创建新的列（列名无所谓，注意类型）
alter table emp add empno1 varchar2(4)
2,将原始列的数据导入至新列（insert 与 update区别）

update emp e set empno1=(select empno from emp  where e.empno=empno)

3,删除原始列

alter table emp drop column empno

4,修改列名

alter table emp rename column empno1 to empno
select * from emp 

修改用户

alter user scott account unlock/lock 解锁
alter user scott identified by 修改密码
alter user scott account lock;
alter user scott account unlock;
alter user scott identified by 123

添加注解

comment on table 表名 is '注解';
comment on table persion is '基础信息表'; 
select * from persion
comment on table persion is '信息表'

列添加注解

comment on column 表名.列名 is '注解';
comment on column persion.name is '姓名';
select * from persion


create table z (id char(32) primary key,n varchar2(255))
insert into z values(sys_guid(),'a')
select * from z
create table zz (id char(32) primary key,z_id char(32) references z(id))
insert into zz values(sys_guid(),'501BEA2F5146486993369526CFEB35EC')
select * from zz