NameDateOracleSQL第一章第一章SELECTSELECT查询查询本章目标 写一条SELECT查询语句在查询中使用表达式、运算符 对空值的处理 对查询字段起别名 查询字段的连接SELECT查询基本语法SELECT FROM SQL SQL SELECT SELECT * * FROM s_emp FROM s_emp 请查询出s_emp表中所有的员工信息:查询指定列SQL SQL SELECT SELECT dept_id , salarydept_id , salary FROM s_emp FROM s_emp 请查询出s_emp表中所有的员工的部门ID，工资:运用算数表达式SQL SQL SELECT SELECT salary*12salary*12 FROM s_emp FROM s_emp 请查询出s_emp表中所有的员工的年薪:运用算数表达式 括号可以改变运算符运算的优先顺序:SQL SELECT SQL SELECT last_name, salary, 12 * salary + 100last_name, salary, 12 * salary + 1002 FROM 2 FROM s_emp;s_emp;. .Velasquez 2500 30100Velasquez 2500 30100SQL SELECT SQL SELECT last_name, salary, 12 * (salary + 100)last_name, salary, 12 * (salary + 100)2 FROM 2 FROM s_emp;s_emp;. .Velasquez 2500 31200Velasquez 2500 31200列别名 请查询出s_emp表中所有的员工的姓名:SQL SQL SELECT SELECT firname_name | last_namefirname_name | last_name FROM s_emp FROM s_empSQL SQL SELECT firname_name | last_name SELECT firname_name | last_name “ “姓名姓名” ” FROM s_empFROM s_emp 请查询出s_emp表中所有的员工的姓名:空值的处理 请查询出s_emp表中所有的员工的工资:SQL SELECTSQL SELECT last_name, last_name, salary*commission_pct/100salary*commission_pct/100 “ “工资工资” ”2 2 FROM FROM s_emp;s_emp;SQL SQL SELECT SELECT last_name , salary+salary*last_name , salary+salary*NVL(commission_pct,0)NVL(commission_pct,0)/100/1002 2 FROM FROM s_emp;s_emp;去掉重复行 请查询出s_dept表的部门名称:SQL SELECTSQL SELECTnamename2 FROM 2 FROM s_dept;s_dept;SQL SELECTSQL SELECTDISTINCTDISTINCT name name2 FROM 2 FROM s_dept;s_dept;去掉多列重复行SQL SELECTSQL SELECT DISTINCTDISTINCT dept_id, title dept_id, title2 FROM 2 FROM s_emp;s_emp; 请查询出s_emp表中所有的员工的部门ID及职称:小结查询表的全部记录 查询指定的列 给列起别名 NVL函数及字符串连接符 重复行的处理DistinctSELECTSELECT DISTINCT *,columnalias,.DISTINCT *,columnalias,. FROMFROM table;table;引言第二章第二章 条件查询条件查询本章目标 WHERE条件查询在查询中使用表达式、运算符 使用LIKE、BETWEEN、IN进行模糊查询第一章内容回顾v对员工表中信息进行查询，具体要求如下：1. 查询s_emp表要求输出员工姓名(firs_name、 last_name)和实际工资(基本工资+提成)：查询基本语法SELECT FROM WHERE 列名称 SELECT*表名 FROMS_emp过滤条件 WHEREdept_id= 41 v请查询出s_emp表中dept_id为41的员工信息:WHERE条件查询 请查询出s_emp表中last_name为Smith的员工的信息:SELECT * FROM s_emp WHERE last_name = Smith 请查询出s_emp表中部门ID为50并且工资大于1500的员 工的信息:SELECT * FROM s_emp WHERE salary1500 and dept_id=50WHERE条件查询-BETWEENdept_id, salary DESC;小结Where条件查询 Betweenand column, expr, alias ASC|DESC;第三章第三章 单行函数单行函数本章目标熟悉各种类型单行函数的使用 掌握转换函数的使用两种SQL函数单行函数 Character Number Date ConversionSINGLE-ROWSINGLE-ROW FUNCTIONFUNCTION多行函数 GroupMULTI-ROWMULTI-ROW FUNCTIONFUNCTION字符函数LOWER将字符串转换成小写 UPPER将字符串变为大写 INITCAP将字符串的第一个字母变为 大写 CONCAT拼接两个字符串，与 | 相同 SUBSTR取字符串的子串 LENGTH以字符给出字符串的长度 NVL以一个值来替换空值 字符函数举例LOWER(SQL Course) sql course UPPER(SQL Course) SQL COURSE INITCAP(SQL Course)Sql CourseSELECT * FROM s_emp WHERE last_name=PATELSELECT * FROM s_emp WHERE UPPER( last_name)=PATEL字符操作函数CONCAT(Good, String)GoodString SUBSTR(String,1,3)Str LENGTH(String)6数字函数ROUND(value,precision) 按precision 精度4舍5 入 TRUNC(value,precision) 按precision 截取valueSQL SELECT round(55.5),round(-55.5),trunc(55.5),trunc(-55.5) FROM dual; round(55.5) round(-55.5) trunc(55.5) trunc(-55.5) - - - -56 -56 55 -55SQL SELECT TRUNC (124.16666, -2) trunc1, trunc(124.16666,2) FROM dual;TRUNC1 TRUNC(124.16666,2) - -100 124.16Roundstart_date LIKE %91;SQL SELECTSQL SELECT Order Order | | TO_CHAR(id)TO_CHAR(id) | |2 2 was filled for a total of was filled for a total of | | TO_CHAR(total,fm$9,999,999)TO_CHAR(total,fm$9,999,999)3 3 FROM FROM s_ords_ord4 4 WHERE WHERE ship_date = 21-SEP-92;ship_date = 21-SEP-92;To-char举例v查询员工表中入职日期在7月份的员工信息 ：SELECT * FROM s_emp WHERE SELECT * FROM s_emp WHERE to_char(start_date,mm)=07to_char(start_date,mm)=07RR 日期格式Current YearCurrent Year 19951995 19951995 20012001 20012001Specified DateSpecified Date 27-OCT-9527-OCT-95 27-OCT-1727-OCT-17 27-OCT-1727-OCT-17 27-OCT-9527-OCT-95RR FormatRR Format 19951995 20172017 20172017 19951995YY FormatYY Format 19951995 19171917 20172017 20952095If the specified two-digit year isIf the specified two-digit year isIf two If two digits digits of the of the current current year year areare0-490-490-490-4950-9950-9950-9950-99The return The return date is in the date is in the current current century.century.The return The return date is in date is in the century the century after the after the current one.current one.The return The return date is in the date is in the century century before the before the current one.current one.The return The return date is in the date is in the current current century.century.转换函数TO_TO_NUMBERNUMBER( ( StringString ) ) 转换字符串到数字转换字符串到数字 TO_TO_DATEDATE( ( StringString ) ) 转换字符串到日期格式转换字符串到日期格