发现了几年前在某机构的学习记录，特此在博客备份一份，以备不时之需，也给需要的码友提供参考。
---TEST day01
--查看系统时间
SELECT SYSDATE FROM dual
--DDL语句 数据定义语言
CREATE TABLE xiaoming(
NAME VARCHAR2(21) NOT NULL,
basec NUMBER(3) DEFAULT 60 ,--在赋值过程中体现
oop NUMBER(3) ,
jvavaSE NUMBER(3)
)
---查看表结构 查看表属性,及属性的值得类型
DESC xiaoming
---删除表
DROP TABLE xiaoming
INSERT INTO xiaoming(NAME,OOP,jvavaSE)
VALUES('SDF',34,23)
--查看表中内容
SELECT * FROM xiaoming
--修改内容
UPDATE xiaoming
SET BASEC=80
WHERE NAME='SDF'
--删除一行
DELETE FROM xiaoming
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"年"MM"月"DD"日" HH24:MI:SS')
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 别名 SELECT TO_CHAR(HIREDATE,'YYYY-MM-DD') AS HIERDATE
2.在表达式后空格加别名,别名不能有空格 SELECT TO_CHAR(HIREDATE,'YYYY-MM-DD') HIERDATE
3.要求区分大小写,使用双引号,且别名中间可以用空格 SELECT TO_CHAR(HIREDATE,'YYYY-MM-DD') "HIERD ATE"
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 "sal",TO_CHAR(HIREDATE,'YYYY-MM-DD') "HIREDATE"
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<TO_DATE('1982-1-1','YYYY-MM-DD')
****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:查看不是"CLERK"职位的员工
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
******查看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<D.S
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<v.man_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)
579
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
