oracle学习笔记.docx

1、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 GUOHUAINSERT INTO GUOHUA(NAME,OOP,jvavaSE)VALUES(SDF,34,23)-查看表中内

2、容SELECT * FROM GUOHUA-修改内容UPDATE GUOHUASET BASEC=80WHERE NAME=SDF-删除一行DELETE FROM GUOHUAWHERE BASEC=60*DAY02*VARCHAR2必须指定长度, char可以不指定字节,默认为1.4000长度 2000长度 long在oracle是字符串,内容长度2GB CLOB:存储定长,变长字符串,内容长度4GB *SELECT 语句 查询的意思 SELECT (字段,多字段用逗号隔开) 表明 WHERE 条件 列: SELECT JOB,ENAME,SAL FROM EMP_GH -查看表中的 job

3、,ENAME,SAL WHERE JOB=CLERK-满足job = clerk,也可以是大于等于号 SELECT * FROM EMP_GH WHERE SAL2000*函数 *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

4、_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), INITCA

5、P(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 DU

6、AL *去掉右端的字符,只要在右边参数出现过得字符,都去掉,不分顺序 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

7、 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

8、(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),向上取整,向

9、下取整 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 DUA

10、L 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

11、) 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 e

12、name,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/GR

13、EATEST函数 最小值/最大值 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),

14、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

15、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 *查看每个人的奖金情况, 有奖金就显示有奖金,

16、没有奖金就显示没有奖金 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_GH3:将所有员工的名字以小写形式，与首字母

17、大写形式 查询出来，第一列为小写形式，第二列为首字母大写 SELECT ename FROM EMP_GH WHERE LOWER(eNAME)=ward SELECT ename FROM EMP_GH where INITCAP(ENAME)=Scott4:将字符串aaaaaabaaaaa中左右两边的a去除 SELECT trim(a FROM aaaaaaabbaaaaaa) FROM DUAL5:显示每个员工的名字，要求显示10位，第一列左对齐效果，第二列 右对齐效果 SELECT RPAD(ENAME,10, ) FROM EMP_GH SELECT LPAD(ENAME,10, )

18、 FROM EMP_GH6:截取字符串DOCTOR WHO中的WHO SELECT SUBSTR(DOCTOR WHO,8,3) FROM DUAL7:查看DOCTOR WHO中WHO的位置 SELECT INSTR(DOCTOR WHO,WHO,4) FROM DUAL8:分别查看55.789四舍五入保留小数点后2位，整数位，十位后的 数字，显示成三列。 SELECT ROUND(55.78,2), ROUND(55.78), ROUND(55.78,-1) FROM DUAL9:分别查看55.789截取后保留小数点后2位，整数位，十位后的 数字，显示成三列。 SELECT TRUNC(55

19、.789,2), TRUNC(55.789), TRUNC(55.789,-1) FROM DUAL10：查看每名员工工资百位以下的数字? SELECT ENAME,SAL,MOD(SAL,100) FROM EMP_GH11:查看每名员工从入职到今天一共多少天，若有小数则向上取整。 SELECT ENAME,HIREDATE, CEIL( SYSDATE-HIREDATE) FROM EMP_GH12:查看从2008-08-08号到今天为止一共经历了多少天? SELECT TRUNC(SYSDATE-TO_DATE(2008-08-08,YYYY-MM-DD) FROM DUAL13:将每名

20、员工入职时间以例如: 1981年12月3日 的形式显示 SELECT ENAME,JOB, TO_CHAR(HIREDATE,YYYY-MM-DD) FROM EMP_GH14：查看每个员工入职所在月的月底是哪天? SELECT LAST_DAY(HIREDATE) FROM EMP_GH15:查看每名员工转正日期(入职后3个月) SELECT ADD_MONTHS(HIREDATE,3) FROM EMP_GH16：查看每名员工入职至今共多少个月? SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP_GH17:查看从明天开始一周内的周日是哪天?

21、SELECT NEXT_DAY(SYSDATE,1) FROM DUAL18:查看82年以后入职的员工的入职日期，82年以前的按照 1982年01月01号显示。格式都是DD-MON-RR(默认格式) SELECT ENAME,HIREDATE, GREATEST(HIREDATE,TO_DATE(1982-01-01,YYYY-MM-DD) FROM EMP_GH19:查看每名员工的入职年份? SELECT ENAME,HIREDATE, EXTRACT(YEAR FROM HIREDATE) FROM EMP_GH20:显示每个员工的总收入(工资加奖金)，奖金为NULL的只看工资 SELEC

22、T ENAME,SAL,COMM, SAL+NVL(COMM,0) FROM EMP_GH21:使用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.在表达式后空格加别名,别名不能有空格 SELEC

23、T 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(H

24、IREDATE,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 SAL2000 查部门不是10的信息 SELECT ENAME,SAL,JOB,DEPTNO FROM EMP_GH WHERE DEPTNO

25、10 查找入职时间在1982-1-1之后的人员信息 SELECT ENAME,SAL,JOB FROM EMP_GH WHERE HIREDATE1000 或 DEPTNO10 SELECT ENAME,SAL,DEPTNO FROM EMP_GH WHERE SAL1000 OR DEPTNO10 满足条件 SAL1000 且 DEPTNO10 SELECT ENAME,SAL,DEPTNO FROM EMP_GH WHERE SAL1000 AND DEPTNO10 工资大于1000,job是salesman 和 SELECT ENAME,SAL,JOB FROM EMP_GH WHERE

26、 SAL1000 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和CLE

27、RK的所有员工信息 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):大于列表所有 不能单独使用,要配合,

28、ANY(800,900,1500)-后期再列表中是,表达式,不是直接写结果. *函数,表达式条件 SELECT ENAME,JOB,SAL FROM EMP_GH WHERE SAL*1250000 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为升序,