CAOracle数据库Sql语句详解大全.ppt

资源描述

《CAOracle数据库Sql语句详解大全.ppt》由会员分享，可在线阅读，更多相关《CAOracle数据库Sql语句详解大全.ppt（136页珍藏版）》请在金锄头文库上搜索。

1、NameDateOracleSQL第一章第一章SELECT查询查询本章目标本章目标写一条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，工资:运用算数表

2、达式运用算数表达式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 + 100 2 FROM 2 FROM s_emp;s_emp; .Velasquez 2500 30100Velasquez 2500 30100SQL SELECT SQL

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

4、ast_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 , salar

5、y+salary*last_name , salary+salary*NVL(commission_pct,0)NVL(commission_pct,0)/100/100 2 2 FROM FROM s_emp;s_emp;去掉重复行去掉重复行请查询出s_dept表的部门名称:SQL SELECTSQL SELECTnamename 2 FROM 2 FROM s_dept;s_dept;SQL SELECTSQL SELECTDISTINCTDISTINCT name name 2 FROM 2 FROM s_dept;s_dept;去掉多列重复行去掉多列重复行SQL SELECTSQL S

6、ELECT DISTINCTDISTINCT dept_id, title dept_id, title 2 FROM 2 FROM s_emp;s_emp;请查询出s_emp表中所有的员工的部门ID及职称:小结小结查询表的全部记录查询指定的列给列起别名NVL函数及字符串连接符重复行的处理DistinctSELECTSELECT DISTINCT *,columnalias,.DISTINCT *,columnalias,.FROMFROM table;table;引言引言第二章第二章条件查询条件查询本章目标本章目标WHERE条件查询在查询中使用表达式、运算符使用LIKE、BETWEEN、I

7、N进行模糊查询第一章内容回顾第一章内容回顾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

8、_emp表中部门ID为50并且工资大于1500的员工的信息:SELECT * FROM s_emp WHERE salary1500 and dept_id=50WHERE条件查询条件查询-BETWEEN&IN请查询出s_emp表中工资在1500到2000之间的员工信息：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表中姓中含有字

9、母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表姓中第二个字母为a的员工信息：SELECT * FROM s_emp WHERE last_name like _a%空值的查询空值的查询v查询出s_emp表中非销售职位的员工信息：SELECT * FROM s_emp WHERE commission_pct is null 课堂练习课堂练习已建

10、立好的S_emp表，对此表中的数据进行模糊查询，具体要求如下：查询一名last_name以“M”开头的员工，他的dept_id好像是3X查询工资在1200至1500之间的员工查询来自部门ID为(41，42，43)的员工查询结果排序查询结果排序v查询出s_emp表将部门ID为41的员工的工资按从高到低排列显示出来：SELECT * FROM s_emp WHERE dept_id=41 ORDER BY salary DESC SQL SELECTSQL SELECTlast_name, dept_id, salarylast_name, dept_id, salary 2 FROM 2 FRO

11、M s_emps_emp 3 3 ORDER BYORDER BYdept_id, salary DESC;dept_id, salary DESC;小结小结Where条件查询Betweenand & In & Like 模糊查询对查询结果排序SELECTSELECTDISTINCT *, column alias, .DISTINCT *, column alias, .FROM FROM tabletableWHEREWHEREcondition(s)condition(s)ORDER BYORDER BYcolumn, expr, alias ASC|DESC;column, expr,

12、 alias ASC|DESC;第三章第三章单行函数单行函数本章目标本章目标熟悉各种类型单行函数的使用掌握转换函数的使用两种两种SQL函数函数单行函数CharacterNumberDateConversionSINGLE-ROWSINGLE-ROWFUNCTIONFUNCTION多行函数GroupMULTI-ROWMULTI-ROWFUNCTIONFUNCTION字符函数字符函数LOWER将字符串转换成小写 UPPER将字符串变为大写 INITCAP将字符串的第一个字母变为大写 CONCAT拼接两个字符串，与 | 相同 SUBSTR取字符串的子串 LENGTH以字符字符给出字符串的长度 NV

13、L以一个值来替换空值字符函数举例字符函数举例LOWER(SQL Course) sql courseUPPER(SQL Course) SQL COURSEINITCAP(SQL Course)Sql CourseSELECT * FROM s_emp WHERE last_name=PATELSELECT * FROM s_emp WHERE UPPER( last_name)=PATEL字符操作函数字符操作函数CONCAT(Good,String)GoodStringSUBSTR(String,1,3)StrLENGTH(String)6数字函数数字函数ROUND(value,preci

14、sion) 按precision 精度4舍5入TRUNC(value,precision) 按precision 截取valueSQLSELECTround(55.5),round(-55.5),trunc(55.5),trunc(-55.5)FROMdual;round(55.5) round(-55.5) trunc(55.5) trunc(-55.5)- - - - 56 -56 55 -55SQLSELECTTRUNC(124.16666,-2)trunc1,trunc(124.16666,2)FROMdual; TRUNC1 TRUNC(124.16666,2)- - 100 124

15、.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之后下一天的日期LAST_DAY(date)返回日期所在月的最后一天日期函数日期函数MONTHS_BETWEEN(01-SEP-95,11-JA

16、N-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 ,YEAR)01-JAN-95转换函数转换函数TO_CHAR(date, fmt)TO_CHAR(date, fmt) 转换日期格式到字符串转换日

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

18、份的全拼January,February,.YYYY,YYY,YY,Y四位数的年，三位数的年1999,999,99,9YEAR年的全拼NineteenNinety-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举例举例SQL SELECTSQL SELECT last_name, last_name, TO_CHAR(start_date,TO_CHAR(start_date, 2 2

19、 fmDdspth offmDdspth of Month YYYY Month YYYY fmHH:MI:SS AM) HIREDATEfmHH:MI:SS AM) HIREDATE 3 3 FROM FROMs_emps_emp 4 4 WHERE WHERE start_date LIKE %91;start_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_

20、CHAR(total,fm$9,999,999)TO_CHAR(total,fm$9,999,999) 3 3 FROM FROM s_ords_ord 4 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 YearCurren

21、t Year19951995199519952001200120012001Specified DateSpecified Date27-OCT-9527-OCT-9527-OCT-1727-OCT-1727-OCT-1727-OCT-1727-OCT-9527-OCT-95RR FormatRR Format19951995201720172017201719951995YY FormatYY Format19951995191719172017201720952095If the specified two-digit year isIf the specified two-digit y

22、ear 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.

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

24、ATE( ( StringString ) ) 转换字符串到日期格式转换字符串到日期格式转换字符串到日期格式转换字符串到日期格式SELECT SELECT to_date(to_date( 2009-09-222009-09-22 , , yyyy-mm-ddyyyy-mm-dd ) ) FROM dual FROM dual转换函数的嵌套转换函数的嵌套F3(F2(F1(col,arg1),arg2),arg3)F3(F2(F1(col,arg1),arg2),arg3)Step 1 = Result 1Step 2 = Result 2Step 3 = Result 3转换函数嵌套举例转换函

25、数嵌套举例SQL SELECTSQL SELECTlast_name,last_name, 2 2 NVL(TO_CHAR(manager_id),No Manager)NVL(TO_CHAR(manager_id),No Manager) 3 FROM 3 FROMs_emps_emp 4 WHERE 4 WHERE manager_id IS NULL;manager_id IS NULL;查询员工表中manager_idmanager_id为空的员工查询出来，并将空列的值置为“NoManager”：小结小结字符函数日期函数数值函数转换函数第四章第四章关联查询关联查询本章目标本章目标在一

26、张或多张表中使用等值或非等值连接使用外连接查询自连接查询等值连接的种类等值连接的种类等值连接非等值连接外连接自连接S_EMP TableS_EMP TableID LAST_NAME ID LAST_NAME DEPT_IDDEPT_ID- - - - - 1 Velasquez 1 Velasquez5050 2 Ngao 2 Ngao4141 3 Nagayama 3 Nagayama3131 4 Quick-To-See 4 Quick-To-See1010 5 Ropeburn 5 Ropeburn5050 6 Urguhart 6 Urguhart4141 7 Menchu 7 Me

27、nchu4242 8 Biri 8 Biri4343 9 Catchpole 9 Catchpole444410 Havel10 Havel454511 Magee11 Magee313112 Giljum12 Giljum323213 Sedeghi13 Sedeghi333314 Nguyen14 Nguyen343415 Dumas15 Dumas353516 Maduro16 Maduro4141表间的关系表间的关系S_DEPT TableS_DEPT TableID NAME REGION_IDID NAME REGION_ID- - - - -30 Finance 130 Fina

28、nce 131 Sales 131 Sales 132 Sales 232 Sales 243 Operations 343 Operations 350 Administration 150 Administration 1S_REGION TableS_REGION TableID NAMEID NAME- - - 1 North America 1 North America 2 South America 2 South America 3 Africa / Middle East 3 Africa / Middle East 4 Asia 4 Asia 5 Europe 5 Euro

29、pe简单关联查询的语法简单关联查询的语法查询员工表中last_namelast_name为Biri的员工的last_name与部门名称查询出来：SELECT table.column, table.columntable.column, table.columnFROMtable1, table2table1, table2WHERE table1.column1 = table2.column2table1.column1 = table2.column2SQL SELECTSQL SELECT e.last_name e.last_name , , d.name d.name 2 2 FR

30、OMFROM s_emps_emp e , s_dept d e , s_dept d 3 3 WHEREWHERE e.dept_id = d.ide.dept_id = d.id and e.last_name = Biri and e.last_name = Biri非等值连接非等值连接SQL SELECT e.ename, e.job, e.sal, s.gradeSQL SELECT e.ename, e.job, e.sal, s.grade2 2FROM emp e, salgrade sFROM emp e, salgrade s3 3WHERE WHERE e.sal BET

31、WEEN s.losal AND s.hisal;e.sal BETWEEN s.losal AND s.hisal;自连接自连接S_EMP (WORKER)S_EMP (WORKER)S_EMP (MANAGER)S_EMP (MANAGER)LAST_NAME LAST_NAME MANAGER_IDMANAGER_ID IDID LAST_NAMELAST_NAME- - -NgaoNgao1 11 1 VelasquezVelasquezNagayamaNagayama1 11 1 Velasquez Velasquez RopeburnRopeburn1 11 1 Velasquez

32、 Velasquez UrguhartUrguhart2 22 2 NgaoNgaoMenchuMenchu2 2 2 2 Ngao Ngao BiriBiri2 2 2 2 Ngao Ngao MageeMagee3 33 3 NagaymaNagaymaGiljumGiljum3 3 3 3 Nagayma Nagayma .ServerServer自连接自连接查询员工表中last_namelast_name为Biri的员工的last_name及其部门经理名称查询出来：SQL SELECTSQL SELECT worker.last_name| works for |manager.las

33、t_nameworker.last_name| works for |manager.last_name 2 2 FROM FROM s_emp worker, s_emp managers_emp worker, s_emp manager 3 3 WHERE WHERE worker.manager_id = manager.id;worker.manager_id = manager.id;外连接外连接SQL SELECTSQL SELECT worker.last_name| works for |manager.last_nameworker.last_name| works for

34、 |manager.last_name 2 2 FROM FROM s_emp worker, s_emp managers_emp worker, s_emp manager 3 3 WHERE WHERE worker.manager_id = manager.idworker.manager_id = manager.id （+ +）; ;SQL SELECTSQL SELECT worker.last_name| works for |manager.last_nameworker.last_name| works for |manager.last_name 2 2 FROM FRO

35、M s_emp worker, s_emp managers_emp worker, s_emp manager 3 3 WHERE WHERE worker.manager_idworker.manager_id（+ +） = manager.id;= manager.id;SQL SELECTSQL SELECT worker.last_name| works for |manager.last_nameworker.last_name| works for |manager.last_name 2 2 FROM FROM s_emp workers_emp worker left out

36、er joinleft outer join s_emp managers_emp manager 3 3 on on worker.manager_id= manager.id;worker.manager_id= manager.id;内连接内连接SQL SELECTSQL SELECT e.last_name e.last_name , , d.name d.name 2 2 FROMFROM s_emps_emp e , s_dept d e , s_dept d 3 3 WHEREWHERE e.dept_id = d.ide.dept_id = d.id and e.last_na

37、me = Biri and e.last_name = BiriSQL SELECTSQL SELECT e.last_name e.last_name , , d.name d.name 2 2 FROMFROM s_emps_emp e e inner joininner join s_dept d s_dept d onon e.dept_id = d.id e.dept_id = d.id 3 WHERE e.last_name = Biri 3 WHERE e.last_name = Biri小结小结等值连接非等值连接外连接自连接第五章第五章组函数组函数本章目标本章目标定义及有效的

38、使用组函数使用Group By对查询数据分组使用HAVING子句对分组后的数据进行过滤使用使用Group By的查询语法的查询语法SELECTSELECT column, group_functioncolumn, group_functionFROMFROMtabletableWHEREWHEREconditionconditionGROUP BYGROUP BY group_by_expressiongroup_by_expressionHAVINGHAVINGgroup_conditiongroup_conditionORDER BYORDER BYcolumn;column;常用组函数

39、常用组函数vAVG (DISTINCT|ALL|n)vCOUNT (DISTINCT|ALL|expr|*)vMAX (DISTINCT|ALL|expr)vMIN (DISTINCT|ALL|expr)vSUM (DISTINCT|ALL|n)应用举例应用举例查询s_emp表中所有员工的平均工资：SQL SELECTSQL SELECT avgavg（salarysalary） 2 2 FROMFROM s_emps_emp查询s_emp表中各个部门员工的平均工资及部门名称：SQL SELECTSQL SELECT e.dept_id, e.dept_id, max(d.name) max(

40、d.name),avg(e.salary),avg(e.salary) 2 2 FROMFROM s_emps_emp e , s_dept d e , s_dept d 3 WHERE e.dept_id = d.id 3 WHERE e.dept_id = d.id 4 4 GROUP BY dept_idGROUP BY dept_id；应用举例应用举例查询s_emp表中31部门一共有多少员工：SQL SELECTSQL SELECTCOUNT(*)COUNT(*) 2 FROM 2 FROMs_emps_emp 3 WHERE 3 WHERE dept_id = 31;dept_id

41、= 31;查询s_emp表中销售人员的数量(提成率不为空的记录个数)：SQL SELECTSQL SELECTCOUNT(COUNT(commission_pctcommission_pct) ) 2 FROM 2 FROMs_emps_emp应用举例应用举例SQL SELECTSQL SELECT e.dept_id, e.dept_id, max(d.name),avg(e.salary),sum(salary)max(d.name),avg(e.salary),sum(salary) 2 2 FROMFROM s_emps_emp e , s_dept d e , s_dept d 3

42、WHERE e.dept_id = d.id 3 WHERE e.dept_id = d.id 4 GROUP BY dept_id 4 GROUP BY dept_id 5 ORDER BY sum(salary);5 ORDER BY sum(salary);查询s_emp表中各个部门员工的平均工资,工资总和及部门名称并按照工资总和排序：应用举例应用举例SQL SELECTSQL SELECT e.dept_id, e.dept_id, max(d.name),avg(e.salary),sum(salary)max(d.name),avg(e.salary),sum(salary) 2

43、2 FROMFROM s_emps_emp e , s_dept d e , s_dept d 3 WHERE e.dept_id = d.id 3 WHERE e.dept_id = d.id and e.dept_id !=41and e.dept_id !=41 4 GROUP BY dept_id 4 GROUP BY dept_id 5 ORDER BY sum(salary); 5 ORDER BY sum(salary);查询s_emp表中除41部门以外的部门员工的平均工资,工资总和及部门名称并按照工资总和排序：SQL SELECTSQL SELECT e.dept_id, e.

44、dept_id, max(d.name),avg(e.salary),sum(salary)max(d.name),avg(e.salary),sum(salary) 2 2 FROMFROM s_emps_emp e , s_dept d e , s_dept d 3 WHERE e.dept_id = d.id 3 WHERE e.dept_id = d.id 4 GROUP BY dept_id 4 GROUP BY dept_id 5 HAVING e.dept_id !=415 HAVING e.dept_id !=41 6 ORDER BY sum(salary); 6 ORDER

45、 BY sum(salary);应用举例应用举例求平均工资高于1500的部门的工资总和，最高工资，最低工资：SQL SELECTSQL SELECT e.dept_id, e.dept_id, max(d.name),avg(e.salary),sum(e.salary)max(d.name),avg(e.salary),sum(e.salary) 2 2 FROMFROM s_emps_emp e , s_dept d e , s_dept d 3 WHERE e.dept_id = d.id 3 WHERE e.dept_id = d.id 4 GROUP BY dept_id 4 GRO

46、UP BY dept_id 5 HAVING avg(e.salary)15005 HAVING avg(e.salary)1500 6 ORDER BY 6 ORDER BY avgavg(e.salary);(e.salary);课堂练习课堂练习求不以“VP”开头职位的，各个职位中工资总和大于5000的职位及工资总和，并按工资总和排序：SQL SELECTSQL SELECT title, SUM(salary) PAYROLLtitle, SUM(salary) PAYROLL 2 FROM 2 FROM s_emps_emp 3 WHERE 3 WHERE title NOT LIKE

47、 VP%title NOT LIKE VP% 4 GROUP BY 4 GROUP BY titletitle 5 HAVING 5 HAVING SUM(salary) 5000SUM(salary) 5000 6 ORDER BY 6 ORDER BY SUM(salary);SUM(salary);小结小结vSELECTSELECT column, group_functioncolumn, group_functionvFROMFROMtabletablevWHEREWHERE conditionconditionvGROUP BYGROUP BY group_by_expressio

48、ngroup_by_expressionvHAVINGHAVING group_conditiongroup_conditionvORDER BYORDER BY column;column;第六章第六章子查询子查询子查询子查询查询s_emp表中工资最低的员工的姓名：SQL SELECTSQL SELECT min min（salarysalary） 2 2 FROMFROM s_emps_empSQL SELECTSQL SELECT last_name last_name 2 2 FROMFROM s_emps_emp 3 WHERE salary = 3 WHERE salary =

49、最小工资（上一条的运行结果）最小工资（上一条的运行结果）最小工资（上一条的运行结果）最小工资（上一条的运行结果）SQL SELECTSQL SELECT last_name last_name 2 2 FROMFROM s_emps_emp 3 WHERE salary 3 WHERE salary = (= (SELECTSELECT min(salary) min(salary) FROMFROM s_emps_emp) )子查询子查询查询s_emp表中平均工资低于32部门的部门ID：SQL SELECTSQL SELECT avg avg（salarysalary） 2 2 FROMFR

50、OM s_emps_emp 3 WHERE dept_id = 32; 3 WHERE dept_id = 32;SQL SELECTSQL SELECT dept_id,avg(salary) dept_id,avg(salary) 2 2 FROMFROM s_emps_emp 3 GROUP BY dept_id 3 GROUP BY dept_id 4 HAVING 4 HAVING avg(salary)32avg(salary) SELECTSQL SELECT dept_id,avg(salary) dept_id,avg(salary) 2 2 FROMFROM s_emps_

51、emp 3 GROUP BY dept_id 3 GROUP BY dept_id 4 HAVING4 HAVING avg(salary)(avg(salary) SELECTSQL SELECT dept_id,avg(salary),name dept_id,avg(salary),name 2 2 FROMFROM s_emps_emp,s_dept,s_dept 3 WHERE 3 WHERE s_emps_emp.dept_id = s_dept.id.dept_id = s_dept.id 4 GROUP BY dept_id,name 4 GROUP BY dept_id,na

52、me 5 HAVING avg(salary)( 5 HAVING avg(salary) SELECTSQL SELECTlast_name, first_name, titlelast_name, first_name, title 2 FROM 2 FROMs_emps_emp 3 WHERE 3 WHEREdept_in =dept_in = 4 4 (SELECT ID (SELECT ID 5 5FROM s_deptFROM s_dept 6 6WHERE name = FinanceWHERE name = Finance 7 7 OR region_id = 2);OR re

53、gion_id = 2);ORA-01427:single-row subquery returns moreORA-01427:single-row subquery returns more than one rowthan one row小结小结SELECTSELECTselect_listselect_listFROMFROMtabletableWHEREWHEREexpr operatorexpr operator(SELECT(SELECTselect_listselect_list FROM FROMtabletable); );第七章第七章数据建模及数据库设计数据建模及数据库

54、设计本章目标本章目标了解系统开发的步骤数据关系的定义理解实体关系映射图（E-R图）系统开发步骤系统开发步骤StrategyandAnalysisDesignBuildandDocumentTransitionProduction数据模型数据模型Model of systemModel of systemin clients mindin clients mindEntity model ofEntity model ofclients modelclients modelTable model ofTable model ofentity modelentity modelTables on

55、diskTables on diskServerEntity Relationship Modeling ConceptsvEntityA thing of significance about which information needs to be knownExamples: customers, sales representatives, ordersvAttributeSomething that describes or qualifies an entityExamples: name, phone, identification numbervRelationshipAn

56、association between two entitiesExamples: orders and items, customers and sales representativesEntity Relationship ModelvCreate an entity relationship diagram from business specifications or narratives.vScenario. . . Assign one or more customers to a sales representative . . . . . Some sales represe

57、ntatives do not yet have assigned customers . . .CUSTOMER#* id*nameophoneEMPLOYEE#* id*last nameofirst nameassigned toassigned tothe sales the sales rep torep toRelationship TypesvOne-to-oneHave a degree of one and only one in both directions.Are rare.Example: Husband and wife.vMany-to-oneHave a deg

58、ree 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.vMany-to-manyHave a degree of one or more in both directions.Are resolved with an intersection entity.Example: Employees and skills.数据库数据库3范式范式数据库中的每一列都是不可再分的基本数据

59、项，同一列中不能有多个值数据库表中不存在非关键字段对任何候选关键字段的部分依赖数据库表中不存在非关键字段对任何候选关键字段的传递引用约束类型约束类型PK Primary Key 唯一且非空FK Foreign Key 外键约束，值引用另一张表已经存在的数据UKUnique Key 唯一且可为空NOT NULL 非空约束举例约束举例IDIDLAST_NAMELAST_NAMEFIRST_NAMEFIRST_NAME. .DEPT_IDDEPT_ID. .1 1VelasquezVelasquezCarmenCarmen50502 2NgaoNgaoLaDorisLaDoris41413 3Na

60、gayamaNagayamaMidoriMidori31314 4Quick-To-SeeQuick-To-SeeMarkMark10105 5RopeburnRopeburnAudryAudry5050IDIDNAMENAMEREGION_IDREGION_ID1010FinanceFinance1 13131SalesSales1 14141OperationsOperations1 15050AdministrationAdministration1 1Primary KeyPrimary KeyForeign KeyForeign KeyS_EMP TableS_EMP TableS_

61、DEPT TableS_DEPT TablePrimary KeyPrimary Key第八章第八章创建表创建表本章目标本章目标掌握创建表的语法Oracle的数据类型使用约束数据结构数据结构一个Oracle数据库包含下列几种数据结构： Table 存储数据 View 从一个表或多个表的数句中得到的子集 Sequence 生成主键值 Index 提高查询性能建表语法建表语法CREATE TABLE CREATE TABLE schemaschema. .tabletable( (column datatypecolumn datatype DEFAULT DEFAULT exprexpr co

62、lumn_constraintcolumn_constraint, , . . table_constrainttable_constraint););Oracle的数据类型的数据类型Char(size) 定长字符型，字符长度不够自动在右边右边加空格符号 Varchar2(size) 可变长字符型，大小必须指定 Number(m,n) 数字型，可存放实数和整数 Date 日期类型Blob 2进制大对象其最大大小为4 GB 此数据类型映射到 Byte 类型的 Array。 Clob 2进制大对象其最大大小为4 GB 此数据类型映射到 String 命名规范命名规范必须以字母开头1-30个字符长度

63、只允许包含AZ, az, 09, _, $, and #在一个数据库保证命名的唯一不能使用Oracle内部的关键字建表举例建表举例CREATE TABLECREATE TABLE Mytest( Mytest( id number id number， name varchar2(32)name varchar2(32); );CREATE TABLECREATE TABLE Mytest( Mytest( id number id number deault 11deault 11， name varchar2(32)name varchar2(32); );建表使用约束举例建表使用约束举例

64、CREATE TABLECREATE TABLE Mytest( Mytest( id number id number check(id10)check(id10), , name varchar2(32) name varchar2(32); );CREATE TABLECREATE TABLE Mytest( Mytest( id number id number check(id10)check(id10), , name varchar2(32) name varchar2(32) not nullnot null); );CREATE TABLECREATE TABLE Mytes

65、t( Mytest( id number id number UNIQUE UNIQUE, , name varchar2(32) not null name varchar2(32) not null); );建表主键约束举例建表主键约束举例CREATE TABLECREATE TABLE Mytest( Mytest( id number id number primary key primary key, , name varchar2(32) not null name varchar2(32) not null); );CREATE TABLECREATE TABLE Mytest(

66、 Mytest( id number, id number, name varchar2(32) not null, name varchar2(32) not null, primary key (id)primary key (id); );CREATE TABLECREATE TABLE Mytest( Mytest( m number, m number, n number, n number, primary key (m,n)primary key (m,n); );建表外键约束举例建表外键约束举例CREATE TABLECREATE TABLE parent( parent( i

67、d number primary key, id number primary key, name varchar2(32) name varchar2(32); );CREATE TABLECREATE TABLE child( child( id number primary key, id number primary key, p_id number p_id number references parent(id)references parent(id); );CREATE TABLECREATE TABLE child( child( id number primary key,

68、 id number primary key, p_id number , p_id number , foreign key(p_id) references parent(id)foreign key(p_id) references parent(id); );约束命名约束命名CREATE TABLECREATE TABLE child( child( id number id number constraint mytest_pkconstraint mytest_pk primary key, primary key, p_id number , p_id number , fore

69、ign key(p_id) references parent(id) foreign key(p_id) references parent(id); );查看表的约束查看表的约束SELECT * SELECT * FROM FROM user_constraintsuser_constraints WHERE WHERE table_name =CHILDtable_name =CHILD CONSTRAINT_TYPEC-checkP-primarykeyR-forgienkeyU-unique级联删除级联删除CREATE TABLECREATE TABLE child( child(

70、id number primary key, id number primary key, p_id number references parent(id) p_id number references parent(id) on delete cascade on delete cascade); );CREATE TABLECREATE TABLE child( child( id number primary key, id number primary key, p_id number references parent(id) p_id number references pare

71、nt(id) on delete set null on delete set null); );子查询创建表子查询创建表CREATE TABLECREATE TABLE emp_41 as ( emp_41 as ( select * from s_emp where dept_id =41 select * from s_emp where dept_id =41); );第九章第九章对数据的操作对数据的操作本章目标本章目标在已创建表中插入新的数据修改已经存在的数据删除表中的数据理解事物控制及其重要性DML命令命令DescriptionDescriptionAdds a new row

72、to the table.Adds a new row to the table.Modifies existing rows in the table.Modifies existing rows in the table.Removes existing rows from the table.Removes existing rows from the table.Makes all pending changes permanent.Makes all pending changes permanent.Allows a rollback to that savepoint marke

73、r.Allows a rollback to that savepoint marker.Discards all pending data changes.Discards all pending data changes.CommandINSERTUPDATEDELETECOMMITSAVEPOINTROLLBACKInsert插入语法插入语法INSERT INTOINSERT INTOtabletable ( (columncolumn , , columncolumn.).)VALUESVALUES( (valuevalue , , valuevalue.);.);插入举例插入举例CR

74、EATE TABLECREATE TABLE Mytest( Mytest( id number primary key, id number primary key, name varchar2(32), name varchar2(32), birth Date birth Date); );INSERT INTOINSERT INTOmytestmytest (id,name,brith)(id,name,brith)VALUESVALUES( (1,BluesWang,1,BluesWang,?) ); ;更新语法更新语法UPDATEUPDATEtabletableSETSETcolu

75、mn = column = valuevalue , , column column = = valuevalue WHEREWHEREconditioncondition; ;删除语法删除语法DELETE FROMDELETE FROMtabletableWHEREWHEREcondition;condition;Database TransactionsvContain one of the following statements:DML commands that make up one consistent change to the dataOne DDL commandOne D

76、CL commandvBegin when the first executable SQL command is executed.vEnd with one of the following events:COMMIT or ROLLBACKDDL or DCL command executes (automatic commit)Errors, exit, or system crash还原点还原点SQL UPDATE.SQL UPDATE.SQL SAVEPOINT update_done;SQL SAVEPOINT update_done;Savepoint created.Save

77、point created.SQL INSERT.SQL INSERT.SQL ROLLBACK TO update_done;SQL ROLLBACK TO update_done;Rollback complete.Rollback complete.Controlling TransactionsCOMMITROLLBACKINSERTUPDATEINSERTDELETESavepointMarker ASavepointMarker BROLLBACKROLLBACK to AROLLBACK to B第十章第十章修改表结构及添加约束修改表结构及添加约束本章目标本章目标增加或者修改列

78、添加删除约束删除表删除表所有的数据添加列添加列ALTER TABLE tableALTER TABLE tableADDADD(column datatype DEFAULT exprNOT NULL(column datatype DEFAULT exprNOT NULL, column datatype.);, column datatype.);添加列举例添加列举例向下表mytest添加名为age，类型为number的一列：ALTER TABLE mytest ADD (age number);ALTER TABLE mytest ADD (age number);再向下表mytest添

79、加名为salary,类型为number(9,1)缺省值为8888的一列：ALTER TABLE mytest ADD (salary number(9,1) default 8888);ALTER TABLE mytest ADD (salary number(9,1) default 8888);删除列删除列ALTER TABLE tableALTER TABLE tableDROPDROP columncolumn , column .;, column .;删除列举例删除列举例将表mytest的salary列删除：ALTER TABLE mytest drop column salary

80、 ;ALTER TABLE mytest drop column salary ;修改列修改列ALTER TABLEALTER TABLEtabletableMODIFYMODIFY(column datatype DEFAULT exprNOT NULL(column datatype DEFAULT exprNOT NULL, column datatype.);, column datatype.);修改列举例修改列举例将表mytest的age列由原来的number类型更改为number（4，2）类型：ALTER TABLE mytest modify (age number(4,2)

81、;ALTER TABLE mytest modify (age number(4,2) ;添加约束添加约束SQL ALTER TABLESQL ALTER TABLEtabletable 2 ADD CONSTRAINT constraint type (column); 2 ADD CONSTRAINT constraint type (column);添加约束举例添加约束举例将表mytest的id列添加主键约束：ALTER TABLE mytestALTER TABLE mytest ADD constraints mytest_pk primary key(id);ADD constra

82、ints mytest_pk primary key(id);将表child的p_id列添加外键约束：ALTER TABLE child ALTER TABLE child ADD constraints c_fkADD constraints c_fk foreign key(p_id) references parent(id); foreign key(p_id) references parent(id);删除约束举例删除约束举例SQL ALTER TABLESQL ALTER TABLEchildchild 2 DROP CONSTRAINT2 DROP CONSTRAINTc c_

83、fk;_fk;删除表及表的重命名删除表及表的重命名DROP TABLE table CASCADE CONSTRAINTS;DROP TABLE table CASCADE CONSTRAINTS;SQL RENAME SQL RENAME 表名表名表名表名 TO TO 新名新名新名新名; ;删除表数据删除表数据SQL TRUNCATE TABLESQL TRUNCATE TABLE 表名表名表名表名; ;小结小结CommandCREATE TABLEALTER TABLEDROP TABLERENAMETRUNCATEDescriptionDescriptionCreates a table

84、 and indicated constraints.Creates a table and indicated constraints.Modifies table structures and constraints.Modifies table structures and constraints.Removes the rows and table structure.Removes the rows and table structure.Changes the name of a table, view, sequence, or Changes the name of a tab

85、le, view, sequence, or synonym.synonym.Removes all rows from a table and releases the Removes all rows from a table and releases the storage space.storage space.第十一章第十一章创建序列创建序列本章目标本章目标掌握如何使用sequences创建sequences修改sequences删除sequences创建创建sequences语法语法CREATE SEQUENCE nameCREATE SEQUENCE nameINCREMENT

87、 1 3 3 START WITH 51 START WITH 51 4 4 MAXVALUE 9999999 MAXVALUE 9999999 5 5 NOCACHE NOCACHE 6 6 NOCYCLE; NOCYCLE;Sequence created.Sequence created.序列举例序列举例利用创建好的Sequence向mytest表中添加数据：insert into mytest values(insert into mytest values(s.nextvals.nextval,a),a)查看当前序列的值：SELECT SELECT s.currval s.currv

89、法DROP SEQUENCE nameDROP SEQUENCE name第十二章第十二章视图、索引视图、索引本章目标本章目标掌握如何使用视图、索引创建视图、索引修改视图、索引删除视图、索引ID LAST_NAME FIRST_NAME TITLE DEPT_ID- - - - - 1 Velasquez Carmen President 50 2 Ngao LaDoris VP, Operations 41 3 Nagayama Midori VP, Sales 31 4 Quick-To-See Mark VP, Finance 10 5 Ropeburn Audry VP, Admin

90、istration 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 Sales

91、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 Stock Clerk 4321 Markarian Alexander Stock Clerk 4322 Chang Eddie Stock C

92、lerk 4423 Patel Radha Stock Clerk 3424 Dancs Bela Stock Clerk 4525 Schwartz Sylvie Stock Clerk 45什么是视图什么是视图ID LAST_NAME TITLE - - -10 Havel Warehouse Manager24 Dancs Stock Clerk25 Schwartz Stock ClerkS_EMP TableEMPVU45 View创建视图的语法创建视图的语法CREATE OR REPLACE FORCE|NOFORCE VIEW viewCREATE OR REPLACE FORC

93、E|NOFORCE VIEW view (alias, alias.) (alias, alias.)AS subqueryAS subqueryWITH CHECK OPTION CONSTRAINT constraintWITH CHECK OPTION CONSTRAINT constraintWITH READ ONLYWITH READ ONLY视图举例视图举例CREATE VIEW CREATE VIEW emp_41 emp_41 ASASSelect e.first_name,d.name,e.salary from s_emp Select e.first_name,d.na

94、me,e.salary from s_emp e,s_dept de,s_dept dWhere e.dept_id = e.id and e.dept_id =41 ;Where e.dept_id = e.id and e.dept_id =41 ;索引索引一下情况下适合建立索引：1.查询多，增删少2.数据量大索引的结构索引的结构NguSedeghiVelasquezGiljumNgaoID LAST_NAME- - 1 Velasquez 2 Ngao 3 Nagayama 4 Quick-To-See 5 Ropeburn 6 Urguhart 7 Menchu 8 Biri 9 Ca

95、tchpole10 Havel11 Magee12 Giljum13 Sedeghi14 Nguyen15 Dumas16 Maduro17 Smith18 Nozaki19 Patel20 Newman21 Markarian22 Chang23 Patel24 Dancs25 SchwartzBiriGiljumMenchuNgaoNguyenSedeghiUrguhartVelasquezROWIDROWID索引的语法索引的语法CREATE INDEX CREATE INDEX indexindexON ON tabletable ( (columncolumn, , columncolumn.);.);索引举例索引举例SQL CREATE INDEX SQL CREATE INDEX s_emp_last_name_idxs_emp_last_name_idx 2 ON 2 ON s_emp(last_name);s_emp(last_name);Index created.Index created.

展开阅读全文

CAOracle数据库Sql语句详解大全.ppt

最新文档