Oracle数据库Sql语句详解大全课件.ppt

1、2021/2/422021/2/43v 写一条SELECT查询语句v 在查询中使用表达式、运算符v 对空值的处理v 对查询字段起别名v 查询字段的连接2021/2/44SELECT FROM 请查询出s_emp表中所有的员工信息:2021/2/45 请查询出s_emp表中所有的员工的部门ID，工资:2021/2/46 请查询出s_emp表中所有的员工的年薪:2021/2/47 括号可以改变运算符运算的优先顺序:2021/2/48 请查询出s_emp表中所有的员工的姓名:请查询出s_emp表中所有的员工的姓名:2021/2/49 请查询出s_emp表中所有的员工的工资:2021/2/410 请查

2、询出s_dept表的部门名称:2021/2/411 请查询出s_emp表中所有的员工的部门ID及职称:2021/2/412v 查询表的全部记录v 查询指定的列v 给列起别名v NVL函数及字符串连接符v 重复行的处理Distinct2021/2/4132021/2/4142021/2/415v WHERE条件查询v 在查询中使用表达式、运算符v 使用LIKE、BETWEEN、IN进行模糊查询2021/2/416v对员工表中信息进行查询，具体要求如下：1.查询s_emp表表要求输出员工姓名(firs_name、last_name)和实际工资(基本工资+提成)：2021/2/417SELECT F

3、ROM WHERE 列名称列名称SELECT*表名表名FROMS_emp过滤条件过滤条件WHEREdept_id=41 v请查询出s_emp表中dept_id为41的员工信息:2021/2/418 请查询出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=502021/2/419 请查询出s_emp表中工资在1500到2000之间的员工信息：SE

4、LECT*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)2021/2/420 请查询出s_emp表中姓中含有字母a的员工信息：SELECT*FROM s_emp WHERE last_name like%a%请查询出当前用户下所有以s_开头的表：SELECT table_name FROM user_tables WHERE table_name like S_%escape 请查询出s_emp表姓中第二

5、个字母为a的员工信息：SELECT*FROM s_emp WHERE last_name like _a%2021/2/421v查询出s_emp表中非销售职位的员工信息：SELECT*FROM s_emp WHERE commission_pct is null 2021/2/422v 已建立好的S_emp表，对此表中的数据进行模糊查询，具体要求如下：查询一名last_name以“M”开头的员工，他的dept_id好像是3X 查询工资在1200至1500之间的员工 查询来自部门ID为(41，42，43)的员工2021/2/423v查询出s_emp表将部门ID为41的员工的工资按从高到低排列显示

6、出来：SELECT*FROM s_emp WHERE dept_id=41 ORDER BY salary DESC 2021/2/424v Where条件查询v Betweenand&In&Like 模糊查询v 对查询结果排序2021/2/4252021/2/426v 熟悉各种类型单行函数的使用v 掌握转换函数的使用2021/2/427 单行函数 Character Number Date Conversion 多行函数 Group2021/2/428vLOWER将字符串转换成小写 vUPPER将字符串变为大写 vINITCAP将字符串的第一个字母变为大写 vCONCAT拼接两个字符串，与|

7、相同 vSUBSTR取字符串的子串 vLENGTH以字符字符给出字符串的长度 vNVL以一个值来替换空值 2021/2/429v LOWER(SQL Course)sql coursev UPPER(SQL Course)SQL COURSEv INITCAP(SQL Course)Sql Course SELECT*FROM s_emp WHERE last_name=PATEL SELECT*FROM s_emp WHERE UPPER(last_name)=PATEL2021/2/430CONCAT(Good,String)GoodStringSUBSTR(String,1,3)StrL

8、ENGTH(String)62021/2/431vROUND(value,precision)按precision 精度4舍5入vTRUNC(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;

9、TRUNC1 TRUNC(124.16666,2)-100 124.162021/2/432ROUND(45.923,2)45.92ROUND(45.923,0)46ROUND(45.923,-1)50TRUNC(45.923,2)45.92TRUNC(45.923)45TRUNC(45.923,-1)402021/2/433vMONTHS_BETWEEN(date2,date1)给出 Date2-date1的月数vADD_MONTHS增加或减去月份vNEXT_DAY(date,day)给出日期date之后下一天的日期vLAST_DAY(date)返回日期所在月的最后一天 2021/2/434

10、vMONTHS_BETWEEN(01-SEP-95,11-JAN-94)19.774194vADD_MONTHS(11-JAN-94,6)11-JUL-94vNEXT_DAY(01-SEP-95,FRIDAY)08-SEP-95vLAST_DAY(01-SEP-95)30-SEP-952021/2/435vROUND(25-MAY-95,MONTH)01-JUN-95vROUND(25-MAY-95,YEAR)01-JAN-95vTRUNC(25-MAY-95,MONTH)01-MAY-95vTRUNC(25-MAY-95,YEAR)01-JAN-952021/2/436 用下列格式显示字符为

11、数字：9 代表一个数字.0 显示前缀零.$根据本地语言环境显示货币.L 采用当地货币符号.打印一个小数点.,千位计算法显示.2021/2/4372021/2/4382021/2/439v查询员工表中入职日期在7月份的员工信息：2021/2/4402021/2/441 2021/2/442Step 1=Result 1Step 2=Result 2Step 3=Result 32021/2/443 查询员工表中为空的员工查询出来，并将空列的值置为“No Manager”：2021/2/444v 字符函数v 日期函数v 数值函数v 转换函数2021/2/4452021/2/446v 在一张或多张表

12、中使用等值或非等值连接v 使用外连接查询v 自连接查询2021/2/447v 等值连接v 非等值连接v 外连接v 自连接2021/2/4482021/2/449 查询员工表中为Biri的员工的last_name与部门名称查询出来：SELECT FROM WHERE 2021/2/4502021/2/4512021/2/452 查询员工表中为Biri的员工的last_name及其部门经理名称查询出来：2021/2/4532021/2/4542021/2/455v 等值连接v 非等值连接v 外连接v 自连接2021/2/4562021/2/457v 定义及有效的使用组函数v 使用Group By对

13、查询数据分组v 使用HAVING子句对分组后的数据进行过滤2021/2/4582021/2/459vAVG(DISTINCT|ALL|n)vCOUNT(DISTINCT|ALL|expr|*)vMAX(DISTINCT|ALL|expr)vMIN(DISTINCT|ALL|expr)vSUM(DISTINCT|ALL|n)2021/2/460 查询s_emp表中所有员工的平均工资：查询s_emp表中各个部门员工的平均工资及部门名称：2021/2/461 查询s_emp表中31部门一共有多少员工：查询s_emp表中销售人员的数量(提成率不为空的记录个数)：2021/2/462 查询s_emp表中

14、各个部门员工的平均工资,工资总和及部门名称并按照工资总和排序：2021/2/463 查询s_emp表中除41部门以外的部门员工的平均工资,工资总和及部门名称并按照工资总和排序：2021/2/464 求平均工资高于1500的部门的工资总和，最高工资，最低工资：2021/2/465 求不以“VP”开头职位的，各个职位中工资总和大于5000的职位及工资总和，并按工资总和排序：2021/2/4662021/2/4672021/2/468 查询s_emp表中工资最低的员工的姓名：2021/2/469 查询s_emp表中平均工资低于32部门的部门ID：2021/2/470 查询s_emp表中平均工资低于3

15、2部门的部门ID及名称：2021/2/4712021/2/4722021/2/4732021/2/474v 了解系统开发的步骤v 数据关系的定义v 理解实体关系映射图（E-R图）2021/2/475StrategyandAnalysisDesignBuildandDocumentTransitionProduction2021/2/476Model of systemin clients mindEntity model ofclients modelTable model ofentity modelTables on diskServer2021/2/477v Entity A thing

16、 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 association between two entities Examples:orders and items,customers and sa

17、les representatives2021/2/478v Create an entity relationship diagram from business specifications or narratives.vScenario.Assign one or more customers to a sales representative.Some sales representatives do not yet have assigned customers.CUSTOMER#*id*nameophoneEMPLOYEE#*id*last nameofirst nameassig

18、ned tothe sales rep to2021/2/479v 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 direction and a degree of one and only one in the other direction.Are very common.Example:passengers and plane.v Many-

19、to-many Have a degree of one or more in both directions.Are resolved with an intersection entity.Example:Employees and skills.2021/2/480v 数据库中的每一列都是不可再分的基本数据项，同一列中不能有多个值v 数据库表中不存在非关键字段对任何候选关键字段的部分依赖v 数据库表中不存在非关键字段对任何候选关键字段的传递引用2021/2/481v PK Primary Key 唯一且非空v FK Foreign Key 外键约束，值引用另一张表 已经存在的数据v UK

20、Unique Key 唯一且可为空v NOT NULL 非空2021/2/482Primary KeyForeign KeyS_EMP TableS_DEPT TablePrimary Key2021/2/4832021/2/484v 掌握创建表的语法v Oracle的数据类型v 使用约束2021/2/485v 一个Oracle数据库包含下列几种数据结构：Table 存储数据 View 从一个表或多个表的数句中得到的子集 Sequence 生成主键值 Index 提高查询性能2021/2/4862021/2/487v Char(size)定长字符型，字符长度不够自动在右边右边加空格符号 v V

21、archar2(size)可变长字符型，大小必须指定 v Number(m,n)数字型，可存放实数和整数 v Date 日期类型v Blob 2进制大对象其最大大小为4 GB 此数据类型映射到 Byte 类型的 Array。v Clob 2进制大对象其最大大小为4 GB 此数据类型映射到 String 2021/2/488v 必须以字母开头v 1-30个字符长度v 只允许包含AZ,az,09,_,$,and#v 在一个数据库保证命名的唯一v 不能使用Oracle内部的关键字2021/2/4892021/2/4902021/2/4912021/2/4922021/2/4932021/2/494

22、CONSTRAINT_TYPE C -check P -primary key R -forgien key U -unique2021/2/4952021/2/4962021/2/4972021/2/498v 在已创建表中插入新的数据v 修改已经存在的数据v 删除表中的数据v 理解事物控制及其重要性2021/2/499CommandINSERTUPDATEDELETECOMMITSAVEPOINTROLLBACK2021/2/41002021/2/41012021/2/41022021/2/41032021/2/4104v Contain one of the following state

23、ments: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 command executes(automatic commit)Errors,exit,or system crash2021/2/41052021

24、/2/4106COMMITROLLBACKINSERTUPDATEINSERTDELETESavepointMarker ASavepointMarker BROLLBACKROLLBACK to AROLLBACK to B2021/2/41072021/2/4108v 增加或者修改列v 添加删除约束v 删除表v 删除表所有的数据2021/2/41092021/2/4110 向下表mytest添加名为age，类型为number的一列：再向下表mytest添加名为salary,类型为number(9,1)缺省值为8888的一列：2021/2/41112021/2/4112 将表mytest的s

25、alary列删除：2021/2/41132021/2/4114 将表mytest的age列由原来的number类型更改为number（4，2）类型：2021/2/41152021/2/4116 将表mytest的id列添加主键约束：将表child的p_id列添加外键约束：2021/2/41172021/2/41182021/2/41192021/2/4120CommandCREATE TABLEALTER TABLEDROP TABLERENAMETRUNCATE2021/2/41212021/2/4122v 掌握如何使用sequencesv 创建sequencesv 修改sequencesv

26、 删除sequences2021/2/41232021/2/41242021/2/4125 利用创建好的Sequence向mytest表中添加数据：查看当前序列的值：2021/2/41262021/2/41272021/2/41282021/2/4129v 掌握如何使用视图、索引v 创建视图、索引v 修改视图、索引v 删除视图、索引2021/2/4130ID LAST_NAME FIRST_NAME TITLE DEPT_ID-1 Velasquez Carmen President 50 2 Ngao LaDoris VP,Operations 41 3 Nagayama Midori VP

27、,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 Representat

28、ive 3112 Giljum Henry Sales Representative 3213 Sedeghi Yasmin Sales Representative 3314 Nguyen Mai Sales Representative 3415 Dumas Andre Sales Representative 3516 Maduro Elena Stock Clerk 4117 Smith George Stock Clerk 4118 Nozaki Akira Stock Clerk 4219 Patel Vikram Stock Clerk 4220 Newman Chad Stoc

29、k Clerk 4321 Markarian Alexander Stock Clerk 4322 Chang Eddie Stock Clerk 4423 Patel Radha Stock Clerk 3424 Dancs Bela Stock Clerk 4525 Schwartz Sylvie Stock Clerk 45ID LAST_NAME TITLE -10 Havel Warehouse Manager24 Dancs Stock Clerk25 Schwartz Stock ClerkS_EMP TableEMPVU45 View2021/2/41312021/2/4132

30、2021/2/4133一下情况下适合建立索引：1.查询多，增删少2.数据量大2021/2/4134NguSedeghiVelasquezGiljumNgaoID LAST_NAME-1 Velasquez 2 Ngao 3 Nagayama 4 Quick-To-See 5 Ropeburn 6 Urguhart 7 Menchu 8 Biri 9 Catchpole10 Havel11 Magee12 Giljum13 Sedeghi14 Nguyen15 Dumas16 Maduro17 Smith18 Nozaki19 Patel20 Newman21 Markarian22 Chang23 Patel24 Dancs25 SchwartzBiriGiljumMenchuNgaoNguyenSedeghiUrguhartVelasquez2021/2/41352021/2/4136