Oracle数据库Sql语句详解大全

1、NameDateOracleSQL本章目标本章目标写一条SELECT查询语句在查询中使用表达式、运算符对空值的处理对查询字段起别名查询字段的连接SELECT查询基本语法查询基本语法SELECT FROM 请查询出s_emp表中所有的员工信息:查询指定列查询指定列 请查询出s_emp表中所有的员工的部门ID，工资:运用算数表达式运用算数表达式 请查询出s_emp表中所有的员工的年薪:运用算数表达式运用算数表达式 括号可以改变运算符运算的优先顺序:列别名列别名 请查询出s_emp表中所有的员工的姓名: 请查询出s_emp表中所有的员工的姓名:空值的处理空值的处理 请查询出s_emp表中所有的员工的

2、工资:去掉重复行去掉重复行 请查询出s_dept表的部门名称:去掉多列重复行去掉多列重复行 请查询出s_emp表中所有的员工的部门ID及职称:小结小结查询表的全部记录查询指定的列给列起别名NVL函数及字符串连接符重复行的处理Distinct引言引言本章目标本章目标WHERE条件查询在查询中使用表达式、运算符使用LIKE、BETWEEN、IN进行模糊查询第一章内容回顾第一章内容回顾v对员工表中信息进行查询，具体要求如下： 1. 查询s_emp表表要求输出员工姓名(firs_name、last_name)和实际工资(基本工资+提成)：查询基本语法查询基本语法SELECT FROM WHERE 列名

3、称列名称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条件查询条件查询-BETWEEN&IN 请查询出s_emp表中工资在1500到2

4、000之间的员工信息：SELECT * FROM s_emp WHERE salary between 1500 and 2000 请查询出s_dept表中region_id为1，3的部门信息：SELECT * FROM s_dept WHERE region_id in (1,3)WHERE条件查询条件查询-like 请查询出s_emp表中姓中含有字母a的员工信息：SELECT * FROM s_emp WHERE last_name like %a% 请查询出当前用户下所有以s_开头的表： SELECT table_name FROM user_tables WHERE table_nam

5、e like S_% escape 请查询出s_emp表姓中第二个字母为a的员工信息：SELECT * FROM s_emp WHERE last_name like _a%空值的查询空值的查询v查询出s_emp表中非销售职位的员工信息： SELECT * FROM s_emp WHERE commission_pct is null 课堂练习课堂练习已建立好的S_emp表，对此表中的数据进行模糊查询，具体要求如下：查询一名last_name以“M”开头的员工，他的dept_id好像是3X查询工资在1200至1500之间的员工查询来自部门ID为(41，42，43)的员工查询结果排序查询结果排序

6、v查询出s_emp表将部门ID为41的员工的工资按从高到低排列显示出来： SELECT * FROM s_emp WHERE dept_id=41 ORDER BY salary DESC 小结小结Where条件查询Betweenand & In & Like 模糊查询对查询结果排序本章目标本章目标熟悉各种类型单行函数的使用掌握转换函数的使用两种两种SQL函数函数单行函数 Character Number Date Conversion多行函数 Group字符函数字符函数LOWER将字符串转换成小写 UPPER将字符串变为大写 INITCAP将字符串的第一个字母变为大写 CONCAT拼接两个字

7、符串，与 | 相同 SUBSTR取字符串的子串 LENGTH以字符字符给出字符串的长度 NVL以一个值来替换空值 字符函数举例字符函数举例LOWER(SQL Course) sql courseUPPER(SQL Course) SQL COURSEINITCAP(SQL Course)Sql Course SELECT * FROM s_emp WHERE last_name=PATEL SELECT * FROM s_emp WHERE UPPER( last_name)=PATEL字符操作函数字符操作函数CONCAT(Good, String)GoodStringSUBSTR(Strin

8、g,1,3)StrLENGTH(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(

9、124.16666,2) FROM dual; TRUNC1 TRUNC(124.16666,2)- - 100 124.16Round&Trunc函数函数ROUND (45.923, 2)45.92ROUND (45.923, 0)46ROUND (45.923, -1)50TRUNC (45.923, 2)45.92TRUNC (45.923)45TRUNC (45.923, -1)40日期函数日期函数MONTHS_BETWEEN(date2,date1) 给出 Date2 - date1的月数ADD_MONTHS增加或减去月份NEXT_DAY ( date,day) 给出日期date之后

10、下一天的日期LAST_DAY(date)返回日期所在月的最后一天 日期函数日期函数MONTHS_BETWEEN(01-SEP-95,11-JAN-94)19.774194ADD_MONTHS(11-JAN-94,6)11-JUL-94NEXT_DAY(01-SEP-95,FRIDAY)08-SEP-95LAST_DAY(01-SEP-95)30-SEP-95日期函数日期函数ROUND(25-MAY-95,MONTH)01-JUN-95ROUND(25-MAY-95 ,YEAR)01-JAN-95TRUNC(25-MAY-95 ,MONTH)01-MAY-95TRUNC(25-MAY-95 ,Y

11、EAR)01-JAN-95转换函数转换函数 用下列格式显示字符为数字：9 代表一个数字.0 显示前缀零.$ 根据本地语言环境显示货币.L 采用当地货币符号. 打印一个小数点., 千位计算法显示.日期格式日期格式DY星期几的缩写Mon,Tue,.DAY星期几的全拼Monday,Tuesday,.D一周的星期几，星期天=1，星期六=71,2,3,4,5,6,7DD一月的第几天，1311，2，. 31W一个月的第几周，1 51,2,3,4,5WW,IW一年的第几周，一年的ISO的第几周1,2,3,4,. 52MM两为数的月01,02,03,.12MON月份的缩写Jan,Feb,Mar ,.DecMO

12、NTH月份的全拼January,February,.YYYY,YYY,YY,Y四位数的年，三位数的年1999,999,99,9YEAR年的全拼Nineteen Ninety-nineRR当前年份的后两位数字01代表2001年HH,HH1212小时制，1121,2,3,.12HH2424小时制，0230,1,2,3,.23MI一小时中的第几分，0590,1,2,3.59SS一分中的第几秒，0590,1,2,3,.59To-char举例举例To-char举例举例v查询员工表中入职日期在7月份的员工信息：RR 日期格式日期格式转换函数转换函数 转换函数的嵌套转换函数的嵌套Step 1 = Resul

13、t 1Step 2 = Result 2Step 3 = Result 3转换函数嵌套举例转换函数嵌套举例 查询员工表中为空的员工查询出来，并将空列的值置为“No Manager”：小结小结字符函数日期函数数值函数转换函数本章目标本章目标在一张或多张表中使用等值或非等值连接使用外连接查询自连接查询等值连接的种类等值连接的种类等值连接非等值连接外连接自连接表间的关系表间的关系简单关联查询的语法简单关联查询的语法 查询员工表中为Biri的员工的last_name与部门名称查询出来：SELECT FROM WHERE 非等值连接非等值连接自连接自连接自连接自连接 查询员工表中为Biri的员工的las

14、t_name及其部门经理名称查询出来：外连接外连接内连接内连接小结小结等值连接非等值连接外连接自连接本章目标本章目标定义及有效的使用组函数使用Group By对查询数据分组使用HAVING子句对分组后的数据进行过滤使用使用Group By的查询语法的查询语法常用组函数常用组函数vAVG (DISTINCT|ALL|n)vCOUNT (DISTINCT|ALL|expr|*)vMAX (DISTINCT|ALL|expr)vMIN (DISTINCT|ALL|expr)vSUM (DISTINCT|ALL|n)应用举例应用举例 查询s_emp表中所有员工的平均工资： 查询s_emp表中各个部门员

15、工的平均工资及部门名称：应用举例应用举例 查询s_emp表中31部门一共有多少员工： 查询s_emp表中销售人员的数量(提成率不为空的记录个数)：应用举例应用举例 查询s_emp表中各个部门员工的平均工资,工资总和及部门名称并按照工资总和排序：应用举例应用举例 查询s_emp表中除41部门以外的部门员工的平均工资,工资总和及部门名称并按照工资总和排序：应用举例应用举例 求平均工资高于1500的部门的工资总和，最高工资，最低工资： 课堂练习课堂练习 求不以“VP”开头职位的，各个职位中工资总和大于5000的职位及工资总和，并按工资总和排序：小结小结子查询子查询 查询s_emp表中工资最低的员工的

16、姓名：子查询子查询 查询s_emp表中平均工资低于32部门的部门ID：子查询子查询 查询s_emp表中平均工资低于32部门的部门ID及名称：子查询举例子查询举例小结小结本章目标本章目标了解系统开发的步骤数据关系的定义理解实体关系映射图（E-R图）系统开发步骤系统开发步骤StrategyandAnalysisDesignBuildandDocumentTransitionProduction数据模型数据模型Model of systemin clients mindEntity model ofclients modelTable model ofentity modelTables on di

17、skServerEntity Relationship Modeling Conceptsv Entity A thing of significance about which information needs to be known Examples: customers, sales representatives, ordersv Attribute Something that describes or qualifies an entity Examples: name, phone, identification numberv Relationship An associat

18、ion between two entities Examples: orders and items, customers and sales representativesEntity Relationship Modelv Create an entity relationship diagram from business specifications or narratives.vScenario. . . Assign one or more customers to a sales representative . . . . . Some sales representativ

19、es do not yet have assigned customers . . .CUSTOMER#* id*nameophoneEMPLOYEE#* id*last nameofirst nameassigned tothe sales rep toRelationship Typesv One-to-one Have a degree of one and only one in both directions. Are rare. Example: Husband and wife.v Many-to-one Have a degree of one or more in one d

20、irection and a degree of one and only one in the other direction. Are very common. Example: passengers and plane.v Many-to-many Have a degree of one or more in both directions. Are resolved with an intersection entity. Example: Employees and skills.数据库数据库3范式范式数据库中的每一列都是不可再分的基本数据项，同一列中不能有多个值数据库表中不存在非

21、关键字段对任何候选关键字段的部分依赖数据库表中不存在非关键字段对任何候选关键字段的传递引用约束类型约束类型PK Primary Key 唯一且非空FK Foreign Key 外键约束，值引用另一张表 已经存在的数据UKUnique Key 唯一且可为空NOT NULL 非空约束举例约束举例Primary KeyForeign KeyS_EMP TableS_DEPT TablePrimary Key本章目标本章目标掌握创建表的语法Oracle的数据类型使用约束数据结构数据结构一个Oracle数据库包含下列几种数据结构： Table 存储数据 View 从一个表或多个表的数句中得到的子集 Se

22、quence 生成主键值 Index 提高查询性能建表语法建表语法Oracle的数据类型的数据类型Char(size) 定长字符型，字符长度不够自动在右边右边加空格符号 Varchar2(size) 可变长字符型，大小必须指定 Number(m,n) 数字型，可存放实数和整数 Date 日期类型Blob 2进制大对象其最大大小为4 GB 此数据类型映射到 Byte 类型的 Array。 Clob 2进制大对象其最大大小为4 GB 此数据类型映射到 String 命名规范命名规范必须以字母开头1-30个字符长度只允许包含AZ, az, 09, _, $, and #在一个数据库保证命名的唯一不能

23、使用Oracle内部的关键字建表举例建表举例建表使用约束举例建表使用约束举例建表主键约束举例建表主键约束举例建表外键约束举例建表外键约束举例约束命名约束命名查看表的约束查看表的约束 CONSTRAINT_TYPE C -check P -primary key R -forgien key U -unique级联删除级联删除子查询创建表子查询创建表本章目标本章目标在已创建表中插入新的数据修改已经存在的数据删除表中的数据理解事物控制及其重要性DML命令命令CommandINSERTUPDATEDELETECOMMITSAVEPOINTROLLBACKInsert插入语法插入语法插入举例插入举例更

24、新语法更新语法删除语法删除语法Database Transactionsv Contain one of the following statements: DML commands that make up one consistent change to the data One DDL command One DCL commandv Begin when the first executable SQL command is executed.v End with one of the following events: COMMIT or ROLLBACK DDL or DCL co

25、mmand executes (automatic commit) Errors, exit, or system crash还原点还原点Controlling TransactionsCOMMITROLLBACKINSERTUPDATEINSERTDELETESavepointMarker ASavepointMarker BROLLBACKROLLBACK to AROLLBACK to B本章目标本章目标增加或者修改列添加删除约束删除表删除表所有的数据添加列添加列添加列举例添加列举例 向下表mytest添加名为age，类型为number的一列： 再向下表mytest添加名为salary,

26、类型为number(9,1)缺省值为8888的一列：删除列删除列删除列举例删除列举例 将表mytest的salary列删除：修改列修改列修改列举例修改列举例 将表mytest的age列由原来的number类型更改为number（4，2）类型：添加约束添加约束添加约束举例添加约束举例 将表mytest的id列添加主键约束： 将表child的p_id列添加外键约束：删除约束举例删除约束举例删除表及表的重命名删除表及表的重命名删除表数据删除表数据小结小结CommandCREATE TABLEALTER TABLEDROP TABLERENAMETRUNCATE本章目标本章目标掌握如何使用sequen

27、ces创建sequences修改sequences删除sequences创建创建sequences语法语法创建序列举例创建序列举例序列举例序列举例 利用创建好的Sequence向mytest表中添加数据： 查看当前序列的值：修改修改sequences语法语法删除删除sequences语法语法本章目标本章目标掌握如何使用视图、索引创建视图、索引修改视图、索引删除视图、索引ID LAST_NAME FIRST_NAME TITLE DEPT_ID- - - - - 1 Velasquez Carmen President 50 2 Ngao LaDoris VP, Operations 41 3

28、Nagayama Midori VP, Sales 31 4 Quick-To-See Mark VP, Finance 10 5 Ropeburn Audry VP, Administration 50 6 Urguhart Molly Warehouse Manager 41 7 Menchu Roberta Warehouse Manager 42 8 Biri Ben Warehouse Manager 43 9 Catchpole Antoinette Warehouse Manager 4410 Havel Marta Warehouse Manager 4511 Magee Colin Sales Representative 3112 Giljum Henry Sales Representative 3213 Sedeghi Yasmin