1、Oracle超详细学习笔记-员工培训- -1.最简单的查询 -例 SELECT * FROM employees; DESC employees; SET linesize 600; SET PAGESIZE 50; -例2 SELECT table_name FROM user_tables;-查询数据库中所有表名 -2.查询特定的列 -例 SELECT employee_id,fisrt_name,last_name.salary FROM employees; -3.在SELECT子句中使用直接量 -例 SELECT 5 FROM employees; - SELECT 5 FROM d
2、ual;-这是Oracle提供的伪表 -例2: SELECT Hello Oracle SQL-在Oracle中如何表示字符串常量 FROM employees; SELECT Hello Oracle SQL-在Oracle中如何表示字符串常量 FROM dual; -例3: SELECT Hello Oracles SQL-用两个连续单引号进行单引号转义 FROM employees; -4在SELECT子句中使用算术表达式 -例1:查询员工的编号、姓名和年薪 SELECT employee_id,fist_name,last_name,salry,salary*12 FROM emplo
3、yees; -例2: SELECT 5/2 FROM dual; -5.给例定义别名 -例1: SELECT employee_id AS id FROM employees; -例2: SELECT employee_id AS Id-双引号中的字符原样显示 FROM employees; -例3: SELECT employee_id,fist_name,last_name,salry,salary*12 AS annual_salary FROM employees; -例4: SELECT employee_id,fist_name,last_name,salry,salary*12
4、AS annual salary FROM employees; -如果别名有特殊字符必须使用双引号 -6.字符串的链接运算符 -例: SELECT employee_id|fist_name,last_name,salry,salary*12 AS annual_salary FROM employees; -7.过滤重复记录 -例1: SELECT DISTINCT department_id FROM employees; -例2: SELECT DISTINCT department_id,job_id FROM employees;-第二章 过滤查询和结果集排序 -1.使用WHERE
5、子句过滤记录 -例1: SELECT * FROM employees WHERE salary 8000; -例2: SELECT employee_id,first_name,last_name,salary FROM employees WHERE salary = 17000; -例3: SELECT employee_id,first_name,last_name,salary FROM employees WHERE salary 17000;- != -比较运算符号:,=,=,=,!=或 -例4 SELECT employee_id,first_name,last_name,sa
6、lary FROM employees WHERE salary =2500 AND salary WHERE-SELECT -5).结果集排序 -例: SELECT employee_id,first_name,last_name ,salaty FROM employees ORDER BY salary DESC; -默认为升序排列(ASC),降序需要在字段后使用DESC -例2: SELECT employee_id,first_name,last_name FROM employees ORDER BY salary -例3: SELECT employee_id,first_nam
7、e,last_name ,salaty FROM employees ORDER BY 4;-是字段在SELECT子句中的索引 -例4: SELECT employee_id,first_name,last_name ,salaty FROM employees WHERE salary 6000 ORDER BY salary; -例5:查询员工的编号、姓名和年薪、并按年薪排序 SELECT employee_id,first_name,last_name ,salaty*12 FROM employees ORDER BY salary*12; - SELECT employee_id,f
8、irst_name,last_name ,salaty*12 AS annual FROM employees ORDER BY annual; -语句的执行顺序FROM-WHERE-SELECT-ORDER BY -例6 SELECT employee_id,first_name,last_name ,salaty*12 AS annual Salary FROM employees ORDER BY annual Salary; -别名在双引号中,是大小写敏感的 -例7 SELECT employee_id,first_name,last_name ,salaty FROM employe
9、es ORDER BY job_id,salary; - SELECT employee_id,first_name,last_name ,salaty FROM employees ORDER BY job_id,salary DESC;-只约束salary -第三章 单行函数 -LOWER/UPPER/INITCAP -例1:将查到的列的字符串全部转换成小写输出 SELECT LOWER(last_name) FROM employees; -例2:将将查到的列的字符串全部转换成大写输出 SELECT UPPER(last_name) FROM employees; -例3:将每个字符串的
10、首字母变为大写 SELECT INITCAP(last_name HELLO dog) FROM dual; -CONCAT/LPAD/RPAD -例1:链接字符串 SELECT first_name | | last_name AS fullname FROM employees; - SELECT CONCAT( first_name,CONCAT(,last_name) AS fullname FROM employees; -例2:在检索到的结果前循环加上第三个参数所表示字符串 -第二个参数为输出字符串长度,若参数1得长度大于限制从右边舍去 SELECT LPAD(employee_i
11、d,8,123) FROM employees; - -在检索到的结果后循环加上第三个参数所表示字符串 -第二个参数为输出字符串长度,若参数1得长度大于限制从右边舍去 SELECT RPAD(employee_id,2,123) FROM employees; -SUBSTR -1:(使用2个参数)从第二个参数所表示的值开始截取字符串 SELECT SUBSTR(ABC D 1234 234 NARS na,4) FROM dual; SELECT SUBSTR(last_name,4),last_name FROM employees; -INSTR -例1:查找第二参数的首字母在第一个参数
12、中的位置 -若不匹配,则返回0 SELECT INSTR(SQL allows for dynamic DB changes,all) FROM dual; -指定起始位置(只返回按要求第一次出现位置) SELECT INSTR(SQL allows for dynamic DB changes,a,6) FROM dual; -指定起始位置并指明第几次出现 SELECT INSTR(SQL allows for dynamic DB changes,a,6,2) FROM dual; -5)TRIM -例1:去掉字符串两端空格 SELECT TRIM( VFBAPSTAK ) FROM du
13、al; -例2:去掉字符串前端指定的字符 SELECT TRIM(LEADINGAFROMAAABA VFBAPSTAK ) FROM dual; -例3:去掉字符串末端指定的字符 SELECT TRIM(TRAILINGAFROMAAABA VFBAPSTAK ABAA) FROM dual; -例4:去掉字符串两端指定的字符 SELECT TRIM(AFROMAAABA VFBAPSTAK ABAA) FROM dual; -6)REPLACE -例1:用第三个参数替换第二个参数 SELECT REPLACE(SQL*PLUS supports loops or if statements
14、., supports, does not support) FROM dual; -7)LENGTH -例1:统计参数字符串长度 SELECT LENGTH(SQL lets you supports loops or if statements. ) FROM dual; -2。数值函数 -1)ROUND/TRUNC -例1:保留参数2所表示的有效位小数,TRUNC不四舍五入 SELECT ROUND(168.888,2),TRUNC(168.888,2) FROM dual; - SELECT ROUND(168.888,0),TRUNC(168.888,0) FROM dual; -
15、-无参数取整 SELECT ROUND(168.888),TRUNC(168.888) FROM dual; - -从个位开始取0运算 SELECT ROUND(168.888,-2),TRUNC(168.888,-2) FROM dual; -2)MOD -例:求余运算 SELECT MOD(9,4) FROM dual; -3)CEIL/FLOOR -例:向上向下取整 SELECT CEIL(34.5),FLOOR(34.5) FROM dual; -3)日期函数- 1)SYSDATE -例:查询当前系统时间 SELECT SYSDATE FROM dual; -Oracle默认日期格式:
16、DD-MON-YY -例2:7天后的日期 SELECT SYSDATE + 7 FROM dual; -例3:100小时之后的时间 SELECT SYSDATE + 100/24 FROM dual; -2)MONTHS_BETWEEN -例1:计算两个日期之间相隔几个月 SELECT MONTHS_BETWEEN(01-12月-2010,31-1月-2010) FROM dual; -3)ADD_MONTHS -例:在指定日期后加上参数二指定的月份 SELECT ADD_MONTHS(SYSDATE,3) FROM dual; -4)NEXT_DAY -例:当前日期之后的星期一 SELECT
17、 NEXT_DAY(SYSDATE,星期一) FROM dual; -系统时间为中文 SELECT NEXT_DAY(SYSDATE,MONDAY) FROM dual; - SELECT NEXT_DAY(SYSDATE,MON) FROM dual; -系统时间为英文 SELECT NEXT_DAY(SYSDATE,1) FROM dual; -使用数字,1代表周日 -5)LAST_DAY -例:返回当前月份的最后一天 SELECT LAST_DAY(SYSDATE) FROM dual; -4.字符串。数字和日期之间的转换函数 -准备: -Oracle日期类型DATE数据的内部存储格式:
18、世纪、年。月。日。时。分。秒 -日期的缺省输入和输出的格式:DD-MON-RR -1)TO_CHAR -例1:以默认日期格式输出:DD-MON-RR SELECT first_name,last_name,TO_CHAR(hire_date) FROM employees; -例2: SELECT first_name ,last_name, TO_CHAR(hire_date,YYYY-MM-DD DY HH24:MI:SS) FROM employees; -第一个参数必须是日期类型,不能是以字符串形式表示的日期直接量 -格式描述: -年:YYYY、YY、RR -月:MM、MON、MONTH -日:DD -星期:DY、DAY -小时:HH24、HH -分:MI -秒:SS -例3:借助TO_CHAR函数获取日期中存储的特殊值 SELECT TO_CHAR(SYSDATE,DY) FROM dual; -例4:(数值格式化) SELECT first_name,last_name,TO_CHAR(salary*1.6,$999,999.99) FROM employees; -格式描述 /* 9:一位数字 0:一位数字,但会保留前导0 $:显示美元符号 L:显示本地货币符号 .:显示小数点 ,:显示千分位 */ -2)TO_DATE -例1:使用缺省格式化描述 SELEC
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1