oracle学习笔记

---TEST day01 --查看系统时间

SELECT SYSDATE FROM dual --DDL语句 数据定义语言 CREATE TABLE GUOHUA(

NAME VARCHAR2(21) NOT NULL,

basec NUMBER(3) DEFAULT 60 ,--在赋值过程中体现 oop NUMBER(3) , jvavaSE NUMBER(3) )

---查看表结构 查看表属性,及属性的值得类型 DESC GUOHUA ---删除表

DROP TABLE GUOHUA

INSERT INTO GUOHUA(NAME,OOP,jvavaSE) VALUES('SDF',34,23) --查看表中内容

SELECT * FROM GUOHUA

--修改内容 UPDATE GUOHUA SET BASEC=80 WHERE NAME='SDF' --删除一行

DELETE FROM GUOHUA WHERE BASEC=60

******************DAY02********************

VARCHAR2必须指定长度, char可以不指定字节,默认为1. 4000长度 2000长度 long在oracle是字符串,内容长度2GB CLOB:存储定长,变长字符串,内容长度4GB

************SELECT 语句 查询的意思 SELECT (字段,多字段用逗号隔开) 表明 WHERE 条件 列:

SELECT JOB,ENAME,SAL FROM EMP_GH --查看表中的 job,ENAME,SAL WHERE JOB='CLERK'--满足job = clerk,也可以是大于等于号

SELECT * FROM EMP_GH

WHERE SAL>2000

****************函数

***CONCAT(CHAR, CHAR) 函数,用于连接字符串,变成一个字符串 SELECT CONCAT(ENAME,JOB) FROM EMP_GH **可以将字符连接

SELECT CONCAT(ENAME,'傻逼') FROM EMP_GH

**CONCAT可以进行嵌套,现将两个字符串连接成为一个字符,在使用concat将下一个进行连接

SELECT CONCAT(ENAME,CONCAT(':',JOB)) FROM emP_GH

*** || 可以进行字符串连接,与java中的+号一样,更常用!!! SELECT ENAME||':'||job from EMP_GH

*** 字符串长度 LENGTH( VARCHAR2 )

SELECT ENAME,LENGTH(ENAME) FROM EMP_GH

***UPPER , LOWER, INITCAP 全大写 , 全小写, 首字母大写

DUAL 是伪表,是用来测试函数的,能查询出一条记录,查询的东西与任何字段无关的使用伪表

SELECT

UPPER('nh') , LOWER('HELLO'),

INITCAP('hello WORLK'), INITCAP('HELLOWORLK'), INITCAP('HELLO WORLK') FROM DUAL

**实例运用 查找emp_gh表中的scott的人的信息所有信息 SELECT * FROM EMP_GH

WHERE ENAME=UPPER('scott')

***TRIM / LTRIM / RTRIM

去掉字符 去掉左端的字符串 去掉右端的字符串

**去掉两段相同的字符,只能是单个字符,不能出现字符串, SELECT TRIM ( 'l' FROM 'llllldfsfdlllllll' ) FROM DUAL

**去掉左端的字符,只要在右边参数出现过得字符,都去掉,不分顺序 SELECT LTRIM('SDDSSDDSDSDSLITE','SD') FROM DUAL

**去掉右端的字符,只要在右边参数出现过得字符,都去掉,不分顺序 SELECT RTRIM('LITESDDSSSDDDSDSDD','SD') FROM DUAL

**LPAD, RPAD 补位函数

SELECT LPAD(SAL,9,'s') FROM emp_gh

SELECT RPAD(SAL,9,'s') from emp_gh

SELECT LPAD(SAL,5,'') FROM EMP_GH

***SUBSTR 截取字符串,下标开始是1,且第二个函数是个数,取都是从左往右取. 若第2个参数超过了界限,则就代表将后面全部取完 **从第十个位置开始取2个, SELECT

SUBSTR('thinking in java',10,2) FROM DUAL

**从倒数第7个开始取2个, SELECT

SUBSTR('thinking in java',-7,2) FROM DUAL SELECT

SUBSTR('thinking in java',-7,100) FROM DUAL

****INSTR 查找字符的位置

查找In在thinking in java 中从第四个位置开始,出现2次后的位置,如果没有则返回0

SELECT

INSTR ('THINKING IN JAVA','IN',4,2) FROM DUAL

**ROUND 函数,四舍五人

如果第二个参数是0 ,可以省略.参数为负表示小数点前 SELECT

ROUND(55.12545,2), ROUND(55.12545,0), ROUND(55.12545), ROUND(55.12545,-1), ROUND(55.12545,-2) FROM DUAL

**TRUNC 函数,数值得截取,不做四舍五人 SELECT

TRUNC(45.2552,2), TRUNC(45.2552,0), TRUNC(45.2552,-1), TRUNC(45.2552,-2) FROM DUAL

**MOD(M,N),求余数,m/n的余数 SELECT

ENAME,SAL,MOD(SAL,1000) FROM EMP_GH

***CEIL(N),FLOOR(N),向上取整,向下取整 SELECT CEIL(45.2) FROM DUAL//46 SELECT FLOOR(45.2) FROM DUAL//45*

***时间戳 TIMESTAMP 11个字节

**SYSDATE:返回一个当前时间date类型 **SYSTIMESTAMP:返回一个当前时间戳 SELECT SYSDATE,SYSTIMESTAMP FROM DUAL

******转换函数

**TO_DATE()时间转换函数,日期格式字符串中除了字母数值和符号之外,其他的字符串都

要使用双引号括起来

SELECT

TO_DATE('2012-12-20 5:45:5','YYYY-MM-DD HH24:MI:SS') FROM DUAL

SELECT

TO_DATE('2012年12月20日 5:45:5','YYYY\年\月\日\ FROM DUAL

***TO_CHAR()将制定格式的时间变成字符串 SELECT

TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL

***DATE可以进行计算,

对DATE加减一个数值,等同于加减天数, 两个DATE之间相减,相当于相差的天数 DATE也可以比较大小,越晚越大

查看明天日期 SELECT SYSDATE+1 FROM DUAL 查看日值时间 SELECT

ENAME,TRUNC( SYSDATE-HIREDATE,0) FROM EMP_GH

** 输入自己生日看活了多少天 SELECT

TRUNC(SYSDATE - TO_DATE('1930-02-21','YYYY-MM-DD')) FROM DUAL

****LAST_DAY(DATE) 查看给定日期的月底是哪天 SELECT

LAST_DAY(SYSDATE) FROM DUAL

***ADD_MONTHS(DATE,I)给定date加i个月,如果i为负就是减去月 查看员工转正日期,在入职日期上面加上一个3月. SELECT

ENAME,HIREDATE,ADD_MONTHS(HIREDATE,3) FROM EMP_GH

查看员工20周年的纪念日 SELECT

ename,ADD_MONTHS(HIREDATE,12*20) FROM EMP_GH

***MONTHS_BETWEEN(DATE1,DATE2)

返回date1余date2之间相差多少个月 *** 查看入职多少个月了 SELECT

MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP_GH **NEXT_DAY(DATE,i)

查看指定日期之后一天开始的周(i-1),列,5代表周4 今天周六 SELECT

NEXT_DAY(SYSDATE,1)//明天 FROM DUAL SELECT

NEXT_DAY(SYSDATE,7)//下周六 FROM DUAL

*****LEAST/GREATEST函数 最小值/最大值 SELECT

LEAST(SYSDATE,SYSDATE+5)--最小值 FROM DUAL

SELECT

GREATEST(SYSDATE,SYSDATE+5)--最大值 FROM DUAL

****EXTRACT() 获取指定时间分量的值 查看满足1981年入职的员工 SELECT

ENAME,HIREDATE FROM EMP_GH

WHERE EXTRACT(YEAR FROM HIREDATE)=1981 *******************null的含义

**创建表student

CREATE TABLE STUDENT_GH( ID NUMBER(4),

NAME VARCHAR2(20), GENDER CHAR(1) )

**查看

DESC STUDENT_GH **插入

INSERT INTO STUDENT_GH

VALUES(1000,'李莫愁','F'); INSERT INTO STUDENT_GH

VALUES(1001,'林平之',NULL);--显示插入 INSERT INTO STUDENT_GH(ID,NAME)--隐身插入 VALUES(1002,'张无忌'); **查看内容

SELECT * FROM STUDENT_GH **修改性别

UPDATE STUDENT_GH SET GENDER =NULL COMMIT

**删gender为null的

判定是否为null是使用 is null 或 is not null DELETE STUDENT_GH WHERE GENDER IS NULL ***null的计算

null与字符串a拼接,结果是字符串a null与数字运算,结果是null **查看emp表中的工资加奖金 SELECT

ENAME,SAL,COMM,SAL+COMM FROM EMP_GH **空值函数

NVL(A1,A2)

若A1为null,则返回A2值,该函数作用就是将null值转变成非null值A2 **查看工资

SELECT ENAME,SAL,COMM,SAL+NVL(COMM,0) FROM EMP_GH **查看每个人的奖金情况,

有奖金就显示有奖金,没有奖金就显示没有奖金

NVL2(A1,A2,A3),如果A1不为null 返回 A2 ,为null返回 A3 SELECT

ENAME,COMM,

NVL2(COMM,'有奖金','没有奖金')

FROM EMP_GH

***TEST*********************

1:查询emp表中数据，列出一列，内容为名字与职位 显示格式：ename:JOB SELECT

ename||':'||JOB FROM EMP_GH SELECT

CONCAT(ENAME,CONCAT(':',JOB)) FROM EMP_GH

2:查看每个员工职位的字符个数 SELECT

JOB,LENGTH(JOB) FROM EMP_GH

3:将所有员工的名字以小写形式，与首字母大写形式 查询出来，第一列为小写形式，第二列为首字母大写 SELECT ename FROM EMP_GH

WHERE LOWER(eNAME)='ward' SELECT ename FROM EMP_GH

where INITCAP(ENAME)='Scott'

4:将字符串'aaaaaabaaaaa'中左右两边的a去除 SELECT

trim('a' FROM 'aaaaaaabbaaaaaa') FROM DUAL

5:显示每个员工的名字，要求显示10位，第一列左对齐效果，第二列 SELECT

RPAD(ENAME,10,' ') FROM EMP_GH SELECT

LPAD(ENAME,10,' ') FROM EMP_GH

6:截取字符串'DOCTOR WHO'中的'WHO' SELECT

SUBSTR('DOCTOR WHO',8,3) FROM DUAL

7:查看'DOCTOR WHO'中'WHO'的位置 SELECT

INSTR('DOCTOR WHO','WHO',4) FROM DUAL

右对齐效果 8:分别查看55.789四舍五入保留小数点后2位，整数位，十位后的 数字，显示成三列。 SELECT

ROUND(55.78,2), ROUND(55.78), ROUND(55.78,-1) FROM DUAL

9:分别查看55.789截取后保留小数点后2位，整数位，十位后的 数字，显示成三列。 SELECT

TRUNC(55.789,2), TRUNC(55.789), TRUNC(55.789,-1) FROM DUAL

10：查看每名员工工资百位以下的数字? SELECT

ENAME,SAL,MOD(SAL,100) FROM EMP_GH

11:查看每名员工从入职到今天一共多少天，若有小数则向上取整。 SELECT

ENAME,HIREDATE,

CEIL( SYSDATE-HIREDATE) FROM EMP_GH

12:查看从2008-08-08号到今天为止一共经历了多少天? SELECT

TRUNC(SYSDATE-TO_DATE('2008-08-08','YYYY-MM-DD')) FROM DUAL

13:将每名员工入职时间以例如: 1981年12月3日 的形式显示 SELECT

ENAME,JOB,

TO_CHAR(HIREDATE,'YYYY-MM-DD') FROM EMP_GH

14：查看每个员工入职所在月的月底是哪天? SELECT

LAST_DAY(HIREDATE) FROM EMP_GH

15:查看每名员工转正日期(入职后3个月) SELECT

ADD_MONTHS(HIREDATE,3) FROM EMP_GH

16：查看每名员工入职至今共多少个月? SELECT

MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP_GH

17:查看从明天开始一周内的周日是哪天? SELECT

NEXT_DAY(SYSDATE,1) FROM DUAL

18:查看82年以后入职的员工的入职日期，82年以前的按照 1982年01月01号显示。格式都是DD-MON-RR(默认格式) SELECT

ENAME,HIREDATE,

GREATEST(HIREDATE,TO_DATE('1982-01-01','YYYY-MM-DD')) FROM EMP_GH

19:查看每名员工的入职年份? SELECT

ENAME,HIREDATE,

EXTRACT(YEAR FROM HIREDATE) FROM EMP_GH

20:显示每个员工的总收入(工资加奖金)，奖金为NULL的只看工资 SELECT

ENAME,SAL,COMM, SAL+NVL(COMM,0) FROM EMP_GH

21:使用NVL2实现20题的需求 SELECT

ENAME,SAL,COMM,

NVL2(COMM,SAL+COMM,SAL) FROM EMP_GH

*************************day03***********查询语******************************** 字句就是关键字+xx 列 SELECT enama SELECT 字段,表达式,函数 ****别名

在使用表达式或函数时,字段变长,可以使用除关键字以外的作为别名

添加方式,1.AS 别名 TO_CHAR(HIREDATE,'YYYY-MM-DD') AS HIERDATE

2.在表达式后空格加别名,别名不能有空格 TO_CHAR(HIREDATE,'YYYY-MM-DD') HIERDATE

3.要求区分大小写,使用双引号,且别名中间可以用空格 SELECT TO_CHAR(HIREDATE,'YYYY-MM-DD') \

句SELECT SELECT

SELECT ENAME,SAL*12 AS SAL,TO_CHAR(HIREDATE,'YYYY-MM-DD') AS HIREDATE FROM EMP_GH

SELECT ENAME,SAL*12 SAL,TO_CHAR(HIREDATE,'YYYY-MM-DD') HIREDATE FROM EMP_GH

SELECT ENAME,SAL*12 \ FROM EMP_GH

***WHERE 条件语句 >, < , >=,<=,<>(不等于),= 查找10号员工的信息, SELECT * FROM EMP_GH WHERE DEPTNO=10; 查找 销售人员的信息

SELECT ENAME,SAL,JOB FROM EMP_GH WHERE JOB='SALESMAN' 查工资高于2000

SELECT ENAME,SAL,JOB FROM EMP_GH WHERE SAL>2000

查部门不是10的信息

SELECT ENAME,SAL,JOB,DEPTNO FROM EMP_GH WHERE DEPTNO<>10

查找入职时间在1982-1-1之后的人员信息 SELECT ENAME,SAL,JOB FROM EMP_GH

WHERE HIREDATE

****AND OR 关键字 ,and的优先级高于or ,及先计算and 在计算or 满足条件 SAL>1000 或 DEPTNO<>10 SELECT ENAME,SAL,DEPTNO FROM EMP_GH WHERE SAL>1000 OR DEPTNO<>10 满足条件 SAL>1000 且 DEPTNO<>10 SELECT ENAME,SAL,DEPTNO FROM EMP_GH WHERE SAL>1000 AND DEPTNO<>10 工资大于1000,job是salesman 和 SELECT ENAME,SAL,JOB FROM EMP_GH

WHERE SAL>1000 AND (JOB = 'SALESMAN' OR JOB = 'CLERK') ****LIKE 条件

可以进行模糊匹配字符串,支持两个通用符 %:0-多个字符串; _:单一的字符 查看第二个字符为A的

SELECT ENAME,SAL,JOB FROM EMP_GH WHERE ENAME LIKE '_A%' ***IN 和 NOT IN

IN(LIST),NOT IN(LIST)

JOB中包含'MANAGER'和'CLERK'的所有员工信息 SELECT ENAME,JOB,SAL FROM EMP_GH WHERE JOB IN ('MANAGER','CLERK')

JOB中不包含'MANAGER'和'CLERK'的所有员工信息 SELECT ENAME,JOB,SAL FROM EMP_GH WHERE JOB NOT IN('MANAGER','CLERK')

***BETWEEN AND 在两者之间,小的在前,大的在后(包含上下线的一个范围 [1500,3000],如果

大的在前,就会出现无结果) SELECT ENAME,SAL,JOB FROM EMP_GH

WHERE sal BETWEEN 1500 AND 3000--[1500,3000] ***IS NULL IS NOT NULL

***ANY 和 ALL条件

ANY(LIST):大于列表其中之一即可 ALL(LIST):大于列表所有

不能单独使用,要配合>,<,=等使用,一般在子查询中使用 SELECT ENAME,SAL FROM EMP_GH

WHERE SAL>ANY(800,900,1500)--后期再列表中是,表达式,不是直接写结果. ***函数,表达式条件

SELECT ENAME,JOB,SAL FROM EMP_GH WHERE SAL*12>50000

SELECT ENAME,SAL,JOB FROM EMP_GH WHERE UPPER(ENAME) = UPPER('SCOTT') ***DISTINCT 去除重复

查看公司中有哪些职位,单字段去重 SELECT DISTINCT JOB FROM EMP_GH

多字段去重,保证,多字段的组合没有重复的 SELECT DISTINCT JOB,DEPTNO FROM EMP_GH *******排序 ORDER BY 语句

排序,ASC为升序,默认的,可以省略 DESC降序 ORDER BY语句只能出现在最后, 分为单字段排序和多字段排序,

**多字段排序是先按照第一个字段排序,如果出现相同情况下按照第二个字段进行排序,以此类推

*** null在排序的时候被认为最大值 工资按照升序排列

SELECT ENAME,SAL FROM EMP_GH ORDER BY SAL 工资按照降序排列

SELECT ENAME,SAL FROM EMP_GH ORDER BY SAL DESC 多字段排序

SELECT ENAME,DEPTNO,SAL FROM EMP_GH

ORDER BY DEPTNO DESC,SAL ASC --DEPTNO按照倒序先排,重复的在按SAL正序排列 NULL在排序中被认为最大

SELECT ENAME,COMM FROM EMP_GH ORDER BY COMM

******聚合函数/多行函数/分组函数 忽略null值

将结果集按照指定的字段进行统计然后得到一条记录 *** MAX(),MIN()指定字段的最大值,最小值 SELECT MAX(SAL),MIN(SAL) FROM EMP_GH

*** AVG() 和 SUM()

统计指定字段的平均值和总和

SELECT TRUNC(AVG(SAL),2) SAL,SUM(SAL) FROM EMP_GH

忽略null值,集合函数忽略null,在进行总体的统计时,需要将null值转化为0 SELECT AVG(NVL(COMM,0)) FROM EMP_GH *** COUNT()

统计指定字段不为NULL的记录的总数

忽略null就只统计不为null值得字段总数 SELECT COUNT(COMM) FROM EMP_GH

**查看一张表中的记录数常数:count(*) SELECT COUNT(*) FROM EMP_GH ***分组函数 GROUP BY

在集合函数中出现了其他字段,那么其他字段必须出现在GROUP BY 中,表示以该字段分组来进行统计

查看每个部门的平均工资 SELECT AVG(SAL),DEPTNO FROM EMP_GH GROUP BY DEPTNO

SELECT DEPTNO FROM EMP_GH GROUP BY DEPTNO

每个职位的工资总和

SELECT SUM(SAL),JOB FROM EMP_GH GROUP BY JOB

**多字段分组原则 GROUP BY

将这些字段值得组合相同的看做成成一组,

查看每个部门中每个职位的平均工资 SELECT AVG(SAL),DEPTNO,JOB FROM EMP_GH

GROUP BY DEPTNO,JOB

***WHERE 中不允许使用集合函数进行过滤条件 原因:时机不对

WHERE 是在查询表中每条数据时就进行过滤的,所以,where决定着那条数据被查

询出来

二分组统计是在表中数据查询出来后基于结果集进行的,所以根据分组统计的结果作为过滤条

件是不能再where中使用的 ***HAVING 字句

HAVING字句不能独立存在,必须跟在group BY 字句之后,HAVING 可以使用集合函数作为过滤

条件,它是用来根据统计结果决定保留哪些分组的 查看部门高于仨个人的部门平均工资 SELECT AVG(SAL),DEPTNO FROM EMP_GH GROUP BY DEPTNO HAVING COUNT(*)>3

最低工资高于1000的每种职位的人数,

SELECT COUNT(*) ,JOB FROM EMP_GH GROUP BY JOB

HAVING AVG(SAL)>1000 查询语句的语句,

***************************关联查询 where中写关联条件

SELECT EMP_GH.ENAME,DEPT_GH.DNAME FROM EMP_GH,DEPT_GH

WHERE EMP_GH.DEPTNO = DEPT_GH.DEPTNO

表名也可以使用别名 SELECT E.ENAME,D.DNAME

FROM EMP_GH E,DEPT_GH D WHERE E.DEPTNO = D.DEPTNO

查看sales部门的员工信息,可以将查询条件和关联条件放在一起

SELECT E.ENAME,E.SAL,D.DNAME FROM EMP_GH E,DEPT_GH D

WHERE E.DEPTNO = D.DEPTNO AND D.DNAME = 'SALES'

查看在new yourk工作的都有谁 SELECT E.ENAME

FROM EMP_GH E,DEPT_GH D

WHERE E.DEPTNO = D.DEPTNO AND D.LOC ='NEW YOUK'

查看每个地区工作的人数 SELECT COUNT(*),D.LOC FROM EMP_GH E,DEPT_GH D WHERE E.DEPTNO = D.DEPTNO

GROUP BY D.LOC

如果不进行关联条件,则进行笛卡尔积,

在关联查询中,若不指定连接条件,则会产生笛卡尔积,该结果集 会将每张表中的每条记录分别连接一次,

组成一条记录,开销巨大,通常是一个没有意义的结果集,结果集是表中数据的乘积, 有N张表就应当有N-1个连接条件

******************内部连接****************************** ***JOIN内关联的

SELECT E.ENAME,D.DNAME

FROM EMP_GH E JOIN DEPT_GH D ON E.DEPTNO = D.DEPTNO WHERE D.DNAME='SALES'

***无论关联查询还是内部查询,都忽略不满足连接条件的记录

*******************外链接

外链接可以在关联条件不满足的数据进行查询,分为: 左外连接:LEST OUTER JOIN 以join左侧的表作为驱动表(主要显示数据的表),该表中的数据全部进行查询,当不满足条件

的数据,在右侧表中都会补充NULL值 右外链接:RIGHT OUTER JOIN 全外链接:FULL OUTER JOIN

SELECT E.ENAME,D.DNAME

FROM EMP_GH E LEFT OUTER JOIN DEPT_GH D ON E.DEPTNO = D.DEPTNO

SELECT E.ENAME ,D.DNAME

FROM EMP_GH E RIGHT OUTER JOIN DEPT_GH D ON E.DEPTNO = D.DEPTNO

SELECT E.ENAME,D.DNAME

FROM EMP_GH E FULL OUTER JOIN DEPT_GH D ON E.DEPTNO = D.DEPTNO

***普通的外链接可以用以下 +号在那边,那边补null 右外链接,

SELECT E.ENAME,D.DNAME FROM EMP_GH E,DEPT_GH D

WHERE E.DEPTNO(+) = D.DEPTNO 做外链接

SELECT E.ENAME,D.DNAME FROM EMP_GH E,DEPT_GH D

WHERE E.DEPTNO = D.DEPTNO(+) ************自连接

用于解决相同类型数据,但是又存在上下级关系的树状结构的储存,

自连接,当前表的记录与当前表的其他记录有对应的关系

查看员工的名字及领导的名字 SELECT E.ENAME,M.ENAME FROM EMP_GH E,EMP_GH M WHERE E.MGR=M.EMPNO(+)

查看每个员工的领导在哪儿工作

分析,员工,领导,工作地三张表进行关联查询 SELECT E.ENAME,M.ENAME,D.LOC FROM EMP_GH E JOIN EMP_GH M ON E.MGR=M.EMPNO0 JOIN DEPT_GH D

ON M.DEPTNO = D.DEPTNO 或

SELECT E.ENAME,M.ENAME,D.LOC FROM EMP_GH E,EMP_GH M,DEPT_GH D

WHERE E.MGR=M.EMPNO AND M.DEPTNO=D.DEPTNO

*****************************TEST************************* 1:查看工资高于2000的员工 SELECT ENAME FROM EMP_GH WHERE SAL>2000

2:查看不是\职位的员工 SELECT ENAME FROM EMP_GH WHERE JOB<>'CLERK'

3:查看工资在1000-2500之间的员工 SELECT ENAME FROM EMP_GH

WHERE SAL BETWEEN 1000 AND 2500 4:查看名字是以K结尾的员工 SELECT ENAME FROM EMP_GH WHERE ENAME LIKE '%K' 5:查看20,30号部门的员工

SELECT ENAME,DEPTNO FROM EMP_GH WHERE DEPTNO=20 OR DEPTNO=30 6:查看奖金为NULL的员工

SELECT ENAME,COMM FROM EMP_GH

WHERE COMM IS NULL--NULL不能用=号,要用is null 7:查看年薪高于20000的员工 SELECT

ENAME,SAL*12

FROM EMP_GH

WHERE SAL*12>20000 8:查看公司共有多少种职位 SELECT

DISTINCT JOB FROM EMP_GH

9:按部门号从小到大排列查看员工 SELECT

ENAME,DEPTNO FROM EMP_GH

ORDER BY DEPTNO

10:查看每个部门的最高，最低，平均工资，和工资总和 SELECT

MAX(SAL),MIN(SAL),AVG(SAL),SUM(SAL),DEPTNO FROM EMP_GH GROUP BY DEPTNO

11:查看平均工资高于2000的部门的最低薪水 SELECT

DEPTNO,MIN(SAL) FROM EMP_GH GROUP BY DEPTNO

HAVING AVG(SAL)>2000

12:查看在NEWYORK工作的员工 SELECT

E.ENAME,D.LOC

FROM EMP_GH E JOIN DEPT_GH D ON E.DEPTNO=D.DEPTNO WHERE D.LOC='NEW YOUK'

13:查看所有员工及所在部门信息，若该员工没有部门，则 部门信息以NULL显示 SELECT

E.ENAME,D.JOB

FROM EMP_GH E LEFT OUTER JOIN EMP_GH D ON E.EMPNO = D.EMPNO 14:查看ALLEN的上司是谁 SELECT

E.ENAME, M.ENAME

FROM EMP_GH E JOIN EMP_GH M ON E.MGR=M.EMPNO

WHERE E.ENAME='ALLEN' TTS-TEST

1、查询职员表中，在20和30号部门工作的员工姓名和部门号。 SELECT ENAME,DEPTNO FROM EMP_GH

WHERE DEPTNO=20 OR DEPTNO=30

2、查询职员表中，没有管理者的员工姓名及职位，并按职位排序。 SELECT ENAME,JOB FROM EMP_GH WHERE MGR IS NULL ORDER BY JOB ASC

3、查询职员表中，有绩效的员工姓名、薪资和绩效，并按工资倒序排列。 SELECT ENAME,SAL,COMM FROM EMP_GH

WHERE COMM IS NOT NULL ORDER BY SAL DESC

4、查询职员表中，员工姓名的第三个字母是A的员工姓名。 SELECT ENAME FROM EMP_GH WHERE ENAME LIKE '__A%'

5、查询职员表中的职员名字、职位、薪资，并显示为如图－3所示效果： SELECT ENAME || ','||JOB||','||SAL OUT_PUT FROM EMP_GH 提示：列之间用逗号连接，列头显示成OUT_PUT。

6、查询职员表中员工号、姓名、工资，以及工资提高百分之20%后的结果。 SELECT EMPNO,ENAME,SAL,SAL*1.2 FROM EMP_GH

7、查询员工的姓名和工资，条件限定为：工资必须大于1200，并对查询结果按入职时间进行排列，早入职排在前面，晚入职排在后面。 SELECT ENAME,SAL ,HIREDATE FROM EMP_GH WHERE SAL>1200 ORDER BY HIREDATE

8、查询ACCOUNT部门以外的其他部门的编号、名称以及所在地。 SELECT D.DEPTNO,D.DNAME,D.LOC FROM EMP_GH E JOIN DEPT_GH D ON E.DEPTNO=D.DEPTNO WHERE D.DNAME<>'ACCOUNT'

1、查询每个部门中每个职位的最高薪水。 SELECT MAX(SAL),DEPTNO,JOB FROM EMP_GH GROUP BY JOB , DEPTNO

SELECT A.ename, A.sal, A.deptno, b.maxsal

FROM emp a,(SELECT deptno, max(sal) maxsal FROM emp GROUP BY deptno) b WHERE A.deptno = b.deptno AND a. sal < b.maxsal; 3、假设员工表中，员工和管理者中间只有一个层级，也就是说，每个员工最多只有一个上级，作为管理者的员工不再有上级管理者，并且，上级管理者相同的员工，他们属于同一个部门。找出EMP 中那些工资高于他们所在部门的管理者工资的员工。 SELECT E.ENAME

FROM EMP_GH E JOIN EMP_GH M ON E.MGR = M.EMPNO

WHERE E.SAL>M.SAL

4、找出EMP 中那些工资高于他们所在部门普通员工平均工资的员工。 SELECT E.ENAME FROM EMP_GH E JOIN

(SELECT DEPTNO,AVG(SAL) A FROM EMP_GH GROUP BY DEPTNO ) M

ON E.DEPTNO=M.DEPTNO WHERE E.SAL>M.A

SELECT classid,AVG(MONTHS_BETWEEN(SYSDATE,entertime)) FROM student

WHERE AVG( MONTHS_BETWEEN (SYSDATE, entertime))>12 GROUP BYclassid

ORDER BY AVG(MONTHS_BETWEEN (SYSDATE, entertime));

有职员表emp，若需列出所有薪水高于平均薪水值的员工信息，则有 SQL语句如下：

SELECT ENAME ,E.SAL,M.A FROM EMP_GH E ,(

SELECT AVG(SAL) A FROM EMP_GH ) M

WHERE E.SAL>M.A

编写一条SQL 语句，查询出每门课都大于80 分的学生姓名。 SELECT ENAME FROM

WHERE KECHEGN A>80 AND KECHENG B>80

9．select LPAD('TARDIS', 3, '*') from dual;

***********************************day04**************************************** ***子查询 WHERE 之后

子查询是嵌套在查询语句之中的作用是为了外层SQL语句提供数据,子查询常用dql,但是也可以用于DML和DDL之中

子查询的结果不同可以进行分类:

**单行单列子查询:使用< ,>, <> ,等比较

** 多行单列子查询:使用 ALL,ANY,IN,NOT IN 进行比较 ** 多行多列子查询: 被看做一张表使用

** 其中单行单列与多列单列常用语where 和 HAVING 中作为过滤条件使用 而多行多列(包括单行多列)常常当做一张表使用

谁的工资比CLARK高

SELECT ENAME,SAL FROM EMP_GH

WHERE SAL>(SELECT SAL FROM emp_gh WHERE ENAME = 'CLARK') 或

SELECT ENAME ,SAL

FROM EMP_GH E , (SELECT SAL A FROM EMP_GH WHERE ENAME = 'CLARK') B WHERE E.SAL>B.A

*** 和Jones同部门的 SELECT ENAME,DEPTNO FROM EMP_GH

WHERE DEPTNO=(SELECT DEPTNO FROM EMP_GH WHERE ENAME='JONES')

SELECT ENAME,DEPTNO

FROM EMP_GH E,(SELECT DEPTNO D FROM EMP_GH WHERE ENAME='JONES') B WHERE E.DEPTNO=B.D

*** 谁高于公司平均工资 SELECT ENAME ,SAL FROM EMP_GH

WHERE SAL>(SELECT AVG(SAL) FROM EMP_GH)

SELECT E.ENAME,E.SAL,S.A

FROM EMP_GH E ,(SELECT AVG(SAL) A FROM EMP_GH) S WHERE E.SAL>S.A

*** 查看部门的平均工资,前提是该部门的平均工资高于30号部门的平均工资 SELECT DEPTNO,AVG(SAL) FROM EMP_GH E GROUP BY DEPTNO HAVING AVG(SAL)>(

SELECT AVG(SAL) FROM EMP_GH GROUP BY DEPTNO HAVING DEPTNO =30)

*** 查看和cleark职位同部门的员工 SELECT ENAME ,DEPTNO FROM EMP_GH

WHERE DEPTNO IN(SELECT DEPTNO FROM EMP_GH WHERE JOB ='CLERK') AND JOB<>'CLERK'

** 比20号部门所有人工资都高的人 SELECT ENAME ,SAL FROM EMP_GH

WHERE SAL>ALL(SELECT SAL FROM EMP_GH WHERE DEPTNO=20)

******EXISTS 语句

***EXISTS后面跟子查询,进行过滤,可放在where后 如果子查询有结果则返回 TRUE 没有结果 返回 false

查看有员工的部门信息 SELECT DEPTNO ,DNAME FROM DEPT_GH D WHERE EXISTS (

SELECT ENAME FROM EMP_GH E WHERE E.DEPTNO=D.DEPTNO) *****子查询 FROM 之后

***当子查询是多行多列时,就可以当成一张表进行关联 查看比自己部门平均工资高的员工 SELECT E.ENAME,E.SAL

FROM EMP_GH E,(SELECT AVG(SAL) A ,DEPTNO FROM EMP_GH GROUP BY DEPTNO) D WHERE E.DEPTNO=D.DEPTNO AND E.SAL>D.A

每个部门最低工资s是谁

SELECT E.ENAME,E.SAL,E.DEPTNO

FROM EMP_GH E,(SELECT MIN(SAL) A,DEPTNO FROM EMP_GH GROUP BY DEPTNO ) D WHERE E.DEPTNO= D.DEPTNO AND E.SAL=d.A

********子查询 在 SELECT 之后

****作为一个字段值在外层查询中集中显示 SELECT E.ENAME,E.SAL,

(SELECT D.DNAME FROM DEPT_GH D WHERE D.DEPTNO=E.DEPTNO) DNAME FROM EMP_GH E

*******分页查询

当一个结果集数据过多时采用分页查询: 原理:将数据分批查询出来,

好处:1.提高客服端到服务端的相应速度 2.减少网络间的传输,降低资源消耗

缺陷:分页由于在SQL中没有定义,所以不同的数据库分页语句不一样

ROWNUM:是一个伪列

rownum不存在任何一张表中,但是每张表都可以查询该字段,该字段的值为结果集

中每条

记录的行号,ROWNUM字段的值是在查询过程中动态生成的,只要从列表中查数据,rownum就会

从1开始进行记录行号

SELECT ROWNUM,ENAME,JOB,SAL FROM EMP_GH WHERE ROWNUM>=1 AND ROWNUM<=10

在使用rownum中对结果集进行编号过程中,不能通过rownum大于1及以上数字判断,否则

查不出任何结果

SELECT *

FROM(SELECT ROWNUM RN,ENAME,SAL FROM EMP_GH) WHERE RN between 5 and 10

** 工资排名第六到第10位 嵌套2层子查询 SELECT *--有行号的浏览表 FROM

(SELECT ROWNUM R,SAL--对表加行号 FROM

(SELECT SAL FROM EMP_GH ORDER BY SAL DESC))--排序 WHERE R BETWEEN 6 AND 10 --增加浏览过滤条件

若在分页中有序排列需求,那么先应该排序,因为排序优先级最低

***分页经典 优化

SELECT *

FROM (SELECT ROWNUM RN,T.*

FROM ( SELECT ENAME,SAL,DEPTNO FROM EMP_GH ORDER BY SAL DESC) T WHERE ROWNUM<=10) WHERE RN>=6

***pageSize:每页显示的条目数 page:第几页

根据上述两个参数,计算结果的集范围: START:(page-1)*pageSize+1 END:pageSize*page

****DECODE函数,处理分支业务 给不同职位的人员涨工资: MANAGER:20% ANALYST:10% SALESMAN:5%

DECODE(JOB, 'MANAGER',SAL*1.2)当第一个参数等于第二个参数,则返回第三个参数 SELECT ENAME,JOB,SAL,

DECODE(JOB, 'MANAGER',SAL*1.2, 'ANALYST',SAL*1.1, 'SALESMAN' ,SAL*1.05, SAL ) BOUNS FROM EMP_GH CASE 语句

SELECT ENAME,SAL,

CASE JOB WHEN 'MANAGER' THEN SAL*1.2 WHEN 'ANALYST' THEN SAL*1.1 WHEN 'SALESMAN' THEN SAL*1.05 ELSE SAL END BOUNDS FROM EMP_GH 可以指定范围.

CASE WHEN JOB >0 THEN SAL*1.2

WHEN JOB >0 THEN SAL*1.1 WHEN JOB >0 THEN SAL*1.05 ELSE sal END BOUNDS

******查看MANAGER,ANALYST部门的总人数和其他部门的总人数 SELECT count(*),DECODE(JOB,'MANAGER','VIP', 'ANALYST','VIP', 'OTHER') BB FROM EMP_GH

GROUP BY DECODE(JOB,'MANAGER','VIP',

'ANALYST','VIP', 'OTHER')

**排序 SELECT DEPTNO ,DNAME,LOC FROM DEPT_GH

ORDER BY DECODE(DNAME,'OPERATIONS',1,'ACCOUNTING',2,'SALES',3)

*****排序函数:

可以根据结果集,按照指定的字段分组,在组内进行排序,生成组内编号 ****ROW_NUMBER:**************连续,唯一的 ROW_NUMBER() OVER(

PARTITION BY DEPTNO--按照部门号分组 ORDER BY SAL DESC--按照工资进行排名

)

查看每个部门工资的排名

SELECT ENAME,SAL,ROW_NUMBER() OVER( PARTITION BY DEPTNO ORDER BY SAL DESC) BB FROM EMP_GH

*****RANK() OVER(---生成不连续,不唯一的数,会出现并列排名情况 PARTITION BY deptno ORDER BY sal DESC )

SELECT ENAME,SAL,RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) FROM EMP_GH

******DENSE_RANK() OVER()--组内生成连续的不唯一的编号,就是出现并列后,不跳序号 SELECT ENAME,SAL,DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) BB

FROM EMP_GH ******

CREATE TABLE SALES_GH( YEAR_ID NUMBER NOT NULL, MONTH_ID NUMBER NOT NULL, DAY_ID NUMBER NOT NULL,

SALES_VALUE NUMBER(10,2) NOT NULL );

INSERT INTO SALES_GH

SELECT TRUNC(DBMS_RANDOM.VALUE(2010,2012)) AS YEAR_ID, TRUNC(DBMS_RANDOM.VALUE(1,13)) AS MONTH_ID, TRUNC(DBMS_RANDOM.VALUE(1,32)) AS DAY_ID,

ROUND(DBMS_RANDOM.VALUE(1,100),2) AS SALES_VALUE FROM DUAL

CONNECT BY LEVEL<=1000;

DESC SALES_GH

SELECT * FROM SALES_GH

******集合操作

集合操作的字段顺序,结果必须一样 ******并集,UNION ,全并UNION ALL SELECT ENAME,SAL,JOB

FROM EMP_GH

WHERE JOB='MANAGER' UNION

SELECT ENAME,SAL,JOB FROM EMP_GH WHERE SAL>2000

SELECT ENAME,SAL,JOB FROM EMP_GH

WHERE JOB='MANAGER' UNION ALL

SELECT ENAME,SAL,JOB FROM EMP_GH WHERE SAL>2000

*********交集

INTERSECT:共有的

SELECT ENAME,SAL,JOB FROM EMP_GH

WHERE JOB='MANAGER' INTERSECT

SELECT ENAME,SAL,JOB FROM EMP_GH WHERE SAL>2000 *******差集

MINUS 一边有,其他没有的,

就是第一个查询语句减去第二个查询语句中的 SELECT ENAME,SAL,JOB FROM EMP_GH WHERE SAL>3000 MINUS

SELECT ENAME,SAL,JOB FROM emp_gh

WHERE JOB='MANAGER'

**************高级分组函数

*****ROLLUP(A,B,C...n) 参数在逐个递减可以使用rollup进行合并,实现了n+1次分组 SELECT YEAR_ID,MONTH_ID,DAY_ID,SUM(SALES_value) FROM sales_gh

GROUP BY ROLLUP(YEAR_ID,MONTH_ID,DAY_ID) ORDER BY YEAR_ID,MONTH_ID,DAY_ID

**** CUBE (A,B,C,...N) 每个参数都进行匹配,2^n个组合 SELECT YEAR_ID,MONTH_ID,DAY_ID,SUM(SALES_VALUE) FROM SALES_GH

GROUP BY CUBE(YEAR_ID,MONTH_ID,DAY_ID) ORDER BY YEAR_ID,MONTH_ID,DAY_ID

***GROUPING SETS(A,B,C,..)按照指定的分组方法进行分组 SELECT YEAR_ID,MONTH_ID,DAY_ID,SUM(SALES_VALUE) FROM SALES_GH

GROUP BY GROUPING SETS((YEAR_ID,MONTH_ID,DAY_ID),(YEAR_ID,MONTH_ID)) ORDER BY YEAR_ID,MONTH_ID,DAY_ID

***********test**************************** 1:查看与CLARK相同职位的员工 SELECT ENAME FROM EMP_GH

WHERE JOB=(SELECT JOB FROM EMP_GH WHERE ENAME='CLARK') 2:查看低于公司平均工资的员工 SELECT ENAME,SAL FROM EMP_GH

WHERE SAL<(SELECT AVG(SAL) FROM EMP_GH ) 3:查看与ALLEN同部门的员工 SELECT ENAME,DEPTNO FROM EMP_GH

WHERE DEPTNO=(SELECT DEPTNO FROM EMP_GH WHERE ENAME='ALLEN') 4:查看平均工资低于20号部门平均工资的部门平均工资 SELECT DEPTNO,AVG(SAL) FROM EMP_GH GROUP BY DEPTNO

HAVING AVG(SAL)<(SELECT AVG(SAL) FROM EMP_gh GROUP BY DEPTNO HAVING DEPTNO=20) 5:查看低于自己所在部门平均工资的员工 SELECT ENAME

FROM EMP_GH E,(SELECT AVG(SAL) S,DEPTNO FROM EMP_GH GROUP BY DEPTNO) D WHERE E.DEPTNO=D.DEPTNO AND E.SAL

6:查看公司工资排名的第1-5名 SELECT *

FROM (SELECT ROWNUM,T.* FROM ( SELECT ENAME,SAL FROM EMP_GH

ORDER BY SAL DESC) T) WHERE ROWNUM BETWEEN 1 AND 5

7:查看CLERK职位的人数和其他职位的总人数各多少? SELECT COUNT(*),DECODE(JOB,'CLERK','CLERK', 'OTHER') M FROM EMP_GH

GROUP BY DECODE(JOB,'CLERK','CLERK', 'OTHER')

8:查看每个职位的工资排名--在组内排序

SELECT ENAME,SAL,JOB,ROW_NUMBER() OVER(PARTITION BY JOB ORDER BY SAL DESC) MM FROM EMP_GH

9:查看每个职位的工资排名，若工资一致，排名一致

SELECT ENAME,SAL,JOB,RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) MM FROM EMP_GH

10:查看每个职位的工资排名，若工资一致，排名一致，不跳名次。

SELECT ENAME,SAL,JOB ,DENSE_RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) MM FROM EMP_GH

11:分别查看:同部门同职位，同职位，以及所有员工的工资总和 SELECT DEPTNO,JOB,SUM(SAL) FROM EMP_GH

GROUP BY ROLLUP(DEPTNO,JOB)

12:分别查看:同部门同职位，同职位，同部门以及所有员工的工资总和 SELECT DEPTNO,JOB,SUM(SAL) FROM EMP_GH

GROUP BY CUBE(DEPTNO,JOB)

13:分别查看同部门同职位和同职位的员工的工资总和 SELECT DEPTNO,JOB,SUM(SAL) FROM EMP_GH

GROUP BY GROUPING SETS((DEPTNO,JOB),JOB)

**********************************DAY05************************************** ****视图(VIEW)

***创建视图,视图名字规则,V_EMP_GH_DEPTNO_10

*** 以v开头_表名字_视图内容信息,在数据库中,不能出现相同的名字 视图不是正在存在的一张表,而是通过SQL语句查询后的到的 虚表 CREATE VIEW V_EMP_GH_DEPTNO_10

AS (SELECT ENAME,SAL,EMPNO,DEPTNO FROM EMP_GH WHERE DEPTNO=10)

SELECT * FROM V_EMP_GH_DEPTNO_10

*** 工作原理:先看from后面是表还是视图,如果是视图,就会找到相关的select语句,并执行,在执行

**** 原来的SELECT 语句,进行查询.

查结构 b

DESC V_EMP_GH_DEPTNO_10

*******视图分为简单视图,复杂视图

简单视图:该视图对应的SELECT语句不含有加工的操作,比如,avg,分组等

**可以进行DML操作,但实际上对视图的DML操作,就是对该视图数据来源的基础表

进行操作

复杂视图:除简单视图之外的视图都是复杂视图

**不能进行DML操作

***对视图进行修改

OR REPLACE,如果没有相关视图则创建一个新视图,如果有则在该视图上修改

CREATE OR REPLACE VIEW V_EMP_GH_DEPTNO_10 AS

SELECT EMPNO ID,ENAME NAME,SAL SALARY,DEPTNO FROM EMP_GH WHERE DEPTNO=10 *******简单视图

********插入 INSERT INTO

**再插入过程中,字段使用别名,如果 使用基础表中的要出现错误 INSERT INTO V_EMP_GH_DEPTNO_10(ID,NAME,SALARY,DEPTNO) VALUES(1021,'SB',9000,10)

SELECT * FROM EMP_GH

*******修改 ***UPDATE

修改过程中,将修改基础表, UPDATE V_EMP_GH_DEPTNO_10 SET SALARY=6000 WHERE NAME='SB'

***dml操作可能会对基础表进行数据污染, **通过视图表插入,但是,在视图内不可见

INSERT INTO V_EMP_GH_DEPTNO_10 VALUES(1021,'SB',2500,20) 或

UPDATE V_EMP_GH_DEPTNO_10 SET DEPTNO=20

上视图在插入中,deptno =20,不在该视图可是范围(deptno=10),造成数据污染 SELECT * FROM V_EMP_GH_DEPTNO_10 ******删除

在进行视图删除是,就是对基础表进行删除 DELETE FROM

DELETE FROM V_EMP_GH_DEPTNO_10

DELETE FROM V_EMP_GH_DEPTNO_10 WHERE DEPTNO=20

********对视图添加检查选项后,可以避免视图对基础表污染, 在创建视图最下面加 WITH CHECK OPTION

CREATE OR REPLACE VIEW V_EMP_GH_DEPTNO_10 AS

SELECT EMPNO ID,ENAME NAME,SAL SALARY,DEPTNO FROM EMP_GH WHERE DEPTNO=10 WITH CHECK OPTION

**修改

修改失败,视图是10部门的 UPDATE V_EMP_GH_DEPTNO_10 SET DEPTNO=20 **插入

插入失败,视图是10部门的,视图不可见 INSERT INTO V_EMP_GH_DEPTNO_10 VALUES(1020,'SB',1245,20) ***只读选项语句

WITH READ ONLY,只读

加入该句后,就可以让视图只能进行查询,不能进行DML操作,

CREATE OR REPLACE VIEW V_EMP_GH_DEPTNO_10 AS

SELECT EMPNO ID,ENAME NAME,SAL SALARY,DEPTNO FROM EMP_GH WHERE DEPTNO=10 WITH READ ONLY *****数据字典 -user_objects -user_views -user_tables

**查看数据库中视图名

SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='VIEW' **查看视图中的试图名

SELECT VIEW_NAME FROM USER_VIEWS **查看表里面有哪些表明

SELECT TABLE_NAME FROM USER_TABLES ***查看视图中的创建视图的select语句 SELECT TEXT FROM USER_VIEWS

*********复杂视图

** 创建复杂视图,每个部门的部门编号,名字,及该部门员工的工资最大值,最小值,平均

值及工资总和

CREATE VIEW V_DEPT_GH AS

SELECT D.DEPTNO,D.DNAME, MAX(E.SAL) MAN_SAL, MIN(E.SAL) MIN_SAL, AVG(E.SAL) SUM_SAL FROM EMP_GH E,DEPT_GH D WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DEPTNO,D.DNAME

SELECT ENAME,SAL

FROM EMP_GH E ,V_DEPT_GH V WHERE E.DEPTNO = V.DEPTNO AND E.SAL

***删除视图

DROP VIEW V_EMP_10

************序列

***序列创建后,就可以用nextval进行数据填充,

** 序列也是数据库对象之一,作用是可以按照指定的方式生成一系列数字,最常用于为表的主键提供的数据 **创建

CREATE SEQUENCE SEQ_EMP_GH_ID START WITH 1--从1开始 INCREMENT BY 1--步进为1

序列支持两个伪列,以获取该序列的数字:

NEXTVAL:使序列生成下一个数字,若刚创建的序列,就将start WITH 指定的数字返回,以后就是

根据步长计算下一个数字后返回.序列不可以发生回退 CURRVAL:获取当前序列,最后生成的数字,不会发生步进效果 **在调用nextval过后才能调用currval,否则会出现错误 SELECT SEQ_EMP_GH_ID.CURRVAL FROM DUAL

INSERT INTO EMP_GH(EMPNO,ENAME,SAL,DEPTNO) VALUES(SEQ_EMP_GH_ID.NEXTVAL,'NB',5200,10)

SELECT * FROM EMP_GH

DELETE FROM EMP_GH WHERE ENAME='NB'

****删除序列

DROP SEQUENCE SEQ_EMP_GH_ID

**可以缓存,但是可能发生不连续的数字 ****UUID

gUID是一个32位不重复字符串 SELECT SYS_gUID() FROM DUAL

*****索引

加快查询效率,数据库自动调用

CREATE INDEX IDX_EMP_GH_ENAME ON EMP_GH(ENAME)--在emp_gh上的ename加索引

创建多列索引

CREATE INDEX IDX_EMP_GH_JOB_SAL ON EMP(JOB,SAL) SELECT EMPNO,ENAME,SAL,JOB FROM EMP ORDER BY JOB,SAL

select * FROM EMP_GH

**创建索引函数

CREATE INDEX EMP_GH_ENAME_UPPER_INDEX ON EMP_GH(UPPER(ENAME))

当做下面的查询时,会自动用于刚刚建立的索引: SELECT * FROM EMP

WHERE UPPER(ENAME)='KING' ****修改索引

从新整理索引

ALTER INDEX IDX_EMP_ENAME REBUILD ***删除索引

DROP INDEX EMP_GH_ENAME_UPPER_INDEX *********************约束 *****NOT NULL非空约束 NN

CONSTRAINT + 名 + NOT NULL 全写 属于列级约束,就是要修改该列同时进行 CREATE TABLE EMPLYEE_GH( ID NUMBER(6),

NAME VARCHAR2(30) NOT NULL,--简写,系统分配名字 SALARY NUMBER(7,2),

HIREDATE DATE CONSTRAINT EMPLYEE_HIREDATE_GH NOT NULL--全写,自己定义名字 )

DESC EMPLYEE_GH

****添加非空约束,必须在修改该列的情况下添加 ALTER TABLE EMPLYEE_GH

MODIFY (ID NUMBER(6) NOT NULL)

****唯一性约束 uk null除外

可以进行列级约束或表及约束(就是写完所有列,最后增加), CREATE TABLE EMPLYEE11( EID NUMBER (6) UNIQUE, NAME VARCHAR2(30), EMAIL VARCHAR2(50), SALARY NUMBER(7,2), HIREDATE DATE,

CONSTRAINT EMPLY1_EMAIL_UK UNIQUE(EMAIL) )

DESC EMPLYEE11

INSERT INTO EMPLYEE11(EID,NAME,EMAIL) VALUES(1,'SB','SB.QOM')

插入失败,因为id,email具有唯一性,null除外 INSERT INTO EMPLYEE1(EID,NAME,EMAIL) VALUES(1,'SB','SB.QOM')

INSERT INTO EMPLYEE11(EID,NAME,EMAIL) VALUES(NULL,'SB',NULL)

SELECT * FROM EMPLYEE11

***添加非空约束(表及约束),如果表上有相同的值,就不能添加唯一性约束 ALTER TABLE EMPLYEE11

ADD CONSTRAINT EMPLYEE11_NAME_UK UNIQUE(NAME)

*****主键约束 PRIMARY KEY

**用于唯一标示用的一列,一个表中只有一个主键, ** 就是唯一约束,非空约束的结合 CREATE TABLE EMPLYEE22( EID NUMBER(6) PRIMARY KEY, NAME VARCHAR2(30), EMAIL VARCHAR2(50), salary NUMBER(7,2), HIREDATE DATE )

DESC EMPLYEE22

***每一次插入数据,主键必须添加

INSERT INTO EMPLYEE22(NAME) VALUES('SB')

****一个表的外键,就是关联表的另一张的主键 ALTER TABLE EMPLYEE22

ADD CONSTRAINT EMPLYEE22_SAL_CHECK CHECK(SALARY>2000)

INSERT INTO EMPLYEE22(EID,NAME,SALARY) VALUES(1500,'SB',1000)

INSERT INTO EMPLYEE22(EID,NAME,SALARY) VALUES(1500,'SB',NULL)--不得行 SELECT * FROM EMPLYEE22 *************test

1:创建一个视图，包含20号部门的员工信息，字段:empno,ename,sal,JOB,deptno CREATE VIEW V_EMP_GH_20 AS

SELECT EMPNO,SAL,JOB,DEPTNO FROM EMP_GH WHERE DEPTNO=20

DROP VIEW V_EMP_GH_20

2:创建一个序列seq_emp_no,从10开始，步进为10 CREATE SEQUENCE SEQ_EMP_NO_GH START WITH 10 INCREMENT BY 10

SELECT SEQ_EMP_NO_GH.NEXTVAL FROM DUAL SELECT SEQ_EMP_NO_GH.CURRVAL FROM DUAL DROP SEQUENCE SEQ_EMP_NO_GH

3:编写SQL语句查看seq_emp_no序列的下一个数字

4:编写SQL语句查看seq_emp_no序列的当前数字 5:为emp表的ename字段添加索引:idx_emp_ename CREATE INDEX IDX_EMP_ENAME ON EMP(ENAME)

6:为emp表的LOWER(ename)字段添加索引:idx_emp_lower_ename CREATE INDEX IDFNHSI ON EMP(UPPER(ENAME)) DROP INDEX IDX_EMP_ENAME

7:为emp表的sal,comm添加多列索引

CREATE INDEX IDX_EMP_SAL_COMM ON EMP_GH(SAL,COMM) 8:创建myemployee表，字段: id NUMBER(4) , nameVARCHAR2(20), birthday DATE,

telephone VARCHAR2(11) scoreNUMBER(9,2)

其中id作为主键，name要求不能为空,telephone需要唯一,score值必须>=0

CREATE TABLE MYEMPLYEE_GH( ID NUMBER(4) PRIMARY KEY, NAME VARCHAR2(20) not null, birthday DATE,

telephone VARCHAR2(11) UNIQUE, scroe number(9,2) check(scroe>=0) )

DESC myemplyee_gh

SELECT * FROM MYEMPLYEE_GH

INSERT INTO myemplyee_gh

VALUES(1,'sb',to_date('2015-1-5','YYYY-MM-DD'),'12025143925',53)

INSERT INTO myemplyee_gh(ID,NAME) VALUES(12,'SB')

INSERT INTO myemplyee_gh(ID,NAME,TELEPHONE,SCROE) VALUES(132,'NB','12025163925',-2)

本文来源：https://www.bwwdw.com/article/hoo3.html