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

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

2、算的优先顺序:,SQL SELECT last_name, salary, 12 * salary + 100 2 FROM s_emp; .Velasquez 2500 30100,SQL SELECT last_name, salary, 12 * (salary + 100) 2 FROM s_emp; .Velasquez 2500 31200,列别名,请查询出s_emp表中所有的员工的姓名:,SQL SELECT firname_name | last_name FROM s_emp,SQL SELECT firname_name | last_name “姓名” FROM s_em

3、p,请查询出s_emp表中所有的员工的姓名:,空值的处理,请查询出s_emp表中所有的员工的工资:,SQL SELECT last_name, salary*commission_pct/100 “工资” 2 FROM s_emp;,SQL SELECT last_name , salary+salary*NVL(commission_pct,0)/100 2 FROM s_emp;,去掉重复行,请查询出s_dept表的部门名称:,SQL SELECTname 2 FROM s_dept;,SQL SELECTDISTINCT name 2 FROM s_dept;,去掉多列重复行,SQL S

4、ELECT DISTINCT dept_id, title 2 FROM s_emp;,请查询出s_emp表中所有的员工的部门ID及职称:,小结,查询表的全部记录 查询指定的列 给列起别名 NVL函数及字符串连接符 重复行的处理Distinct,SELECT DISTINCT *,columnalias,. FROM table;,引言,第二章 条件查询,本章目标,WHERE条件查询 在查询中使用表达式、运算符 使用LIKE、BETWEEN、IN进行模糊查询,第一章内容回顾,对员工表中信息进行查询，具体要求如下： 1. 查询s_emp表要求输出员工姓名(firs_name、last_name)

5、和实际工资(基本工资+提成)：,查询基本语法,SELECT FROM WHERE ,请查询出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=50,WHERE条件查询-BETWEEN,小结,Where条件查询 Betweenand ,第三章 单行函数,本章

6、目标,熟悉各种类型单行函数的使用 掌握转换函数的使用,两种SQL函数,单行函数 Character Number Date Conversion,SINGLE-ROW FUNCTION,多行函数 Group,MULTI-ROW FUNCTION,字符函数,LOWER将字符串转换成小写 UPPER将字符串变为大写 INITCAP将字符串的第一个字母变为大写 CONCAT拼接两个字符串，与 | 相同 SUBSTR取字符串的子串 LENGTH以字符给出字符串的长度 NVL以一个值来替换空值,字符函数举例,LOWER(SQL Course) sql course UPPER(SQL Course) S

7、QL COURSE INITCAP(SQL Course)Sql Course,SELECT * FROM s_emp WHERE last_name=PATEL,SELECT * 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 截取value,SQL

8、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 -55,SQL SELECT TRUNC (124.16666, -2) trunc1, trunc(124.16666,2) FROM dual; TRUNC1 TRUNC(124.16666,2) - - 100 124.16,Round,SQL SELECT Order | TO_CHAR(id) | 2 was fil

9、led for a total of | TO_CHAR(total,fm$9,999,999) 3 FROM s_ord 4 WHERE ship_date = 21-SEP-92;,To-char举例,查询员工表中入职日期在7月份的员工信息：,SELECT * FROM s_emp WHERE to_char(start_date,mm)=07,RR 日期格式,Current Year 1995 1995 2001 2001,Specified Date 27-OCT-95 27-OCT-17 27-OCT-17 27-OCT-95,RR Format 1995 2017 2017 199

10、5,YY Format 1995 1917 2017 2095,If the specified two-digit year is,If two digits of the current year are,0-49,0-49,50-99,50-99,The return date is in the current century.,The return date is in the century after the current one.,The return date is in the century before the current one.,The return date

11、 is in the current century.,转换函数,TO_NUMBER(String) 转换字符串到数字,TO_DATE(String) 转换字符串到日期格式,SELECT to_date(2009-09-22,yyyy-mm-dd) FROM dual,转换函数的嵌套,F3(F2(F1(col,arg1),arg2),arg3),Step 1 = Result 1,Step 2 = Result 2,Step 3 = Result 3,转换函数嵌套举例,SQL SELECTlast_name, 2 NVL(TO_CHAR(manager_id),No Manager) 3 FR

12、OMs_emp 4 WHEREmanager_id IS NULL;,查询员工表中manager_id为空的员工查询出来，并将空列的值置为“No Manager”：,小结,字符函数 日期函数 数值函数 转换函数,第四章 关联查询,本章目标,在一张或多张表中使用等值或非等值连接 使用外连接查询 自连接查询,等值连接的种类,等值连接 非等值连接 外连接 自连接,S_EMP Table ID LAST_NAME DEPT_ID - - - 1 Velasquez50 2 Ngao41 3 Nagayama31 4 Quick-To-See10 5 Ropeburn50 6 Urguhart41 7

13、Menchu42 8 Biri43 9 Catchpole44 10 Havel45 11 Magee31 12 Giljum32 13 Sedeghi33 14 Nguyen34 15 Dumas35 16 Maduro41,表间的关系,S_DEPT Table ID NAME REGION_ID - - - 30 Finance 1 31 Sales 1 32 Sales 2 43 Operations 3 50 Administration 1,S_REGION Table ID NAME - - 1 North America 2 South America 3 Africa / Mi

14、ddle East 4 Asia 5 Europe,简单关联查询的语法,查询员工表中last_name为Biri的员工的last_name与部门名称查询出来：,SELECT table.column, table.column FROM table1, table2 WHERE table1.column1 = table2.column2,SQL SELECT e.last_name , 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id and e.last_name = Biri,非等值连接,SQL SELECT e.ena

15、me, e.job, e.sal, s.grade 2FROM emp e, salgrade s 3WHERE e.sal BETWEEN s.losal AND s.hisal;,自连接,S_EMP (WORKER),S_EMP (MANAGER),LAST_NAME MANAGER_IDIDLAST_NAME-Ngao11VelasquezNagayama11Velasquez Ropeburn11Velasquez Urguhart22NgaoMenchu2 2Ngao Biri2 2Ngao Magee33NagaymaGiljum3 3Nagayma .,自连接,查询员工表中las

16、t_name为Biri的员工的last_name及其部门经理名称查询出来：,SQL SELECT worker.last_name| works for |manager.last_name 2 FROM s_emp worker, s_emp manager 3 WHERE worker.manager_id = manager.id;,外连接,SQL SELECT worker.last_name| works for |manager.last_name 2 FROM s_emp worker, s_emp manager 3 WHERE worker.manager_id = mana

17、ger.id （+）;,SQL SELECT worker.last_name| works for |manager.last_name 2 FROM s_emp worker, s_emp manager 3 WHERE worker.manager_id（+） = manager.id;,SQL SELECT worker.last_name| works for |manager.last_name 2 FROM s_emp worker left outer join s_emp manager 3 on worker.manager_id= manager.id;,内连接,SQL

18、SELECT e.last_name , 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id and e.last_name = Biri,SQL SELECT e.last_name , 2 FROM s_emp e inner join s_dept d on e.dept_id = d.id 3 WHERE e.last_name = Biri,小结,等值连接 非等值连接 外连接 自连接,第五章 组函数,本章目标,定义及有效的使用组函数 使用Group By对查询数据分组 使用HAVING子句对分组后的数据进行

19、过滤,使用Group By的查询语法,SELECT column, group_function FROMtable WHEREcondition GROUP BY group_by_expression HAVINGgroup_condition ORDER BYcolumn;,常用组函数,AVG (DISTINCT|ALL|n) COUNT (DISTINCT|ALL|expr|*) MAX (DISTINCT|ALL|expr) MIN (DISTINCT|ALL|expr) SUM (DISTINCT|ALL|n),应用举例,查询s_emp表中所有员工的平均工资：,SQL SELECT

20、 avg（salary） 2 FROM s_emp,查询s_emp表中各个部门员工的平均工资及部门名称：,SQL SELECT e.dept_id, max(),avg(e.salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id 4 GROUP BY dept_id；,应用举例,查询s_emp表中31部门一共有多少员工：,SQL SELECTCOUNT(*) 2 FROMs_emp 3 WHERE dept_id = 31;,查询s_emp表中销售人员的数量(提成率不为空的记录个数)：,SQL SELECTCOUNT(c

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

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

23、 !=41 6 ORDER BY sum(salary);,应用举例,求平均工资高于1500的部门的工资总和，最高工资，最低工资：,SQL SELECT e.dept_id, max(),avg(e.salary),sum(e.salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id 4 GROUP BY dept_id 5 HAVING avg(e.salary)1500 6 ORDER BY avg (e.salary);,课堂练习,求不以“VP”开头职位的，各个职位中工资总和大于5000的职位及工资总和，并按工资总和

24、排序：,SQL SELECT title, SUM(salary) PAYROLL 2 FROM s_emp 3 WHERE title NOT LIKE VP% 4 GROUP BY title 5 HAVING SUM(salary) 5000 6 ORDER BY SUM(salary);,小结,SELECT column, group_function FROMtable WHERE condition GROUP BY group_by_expression HAVING group_condition ORDER BYcolumn;,第六章 子查询,子查询,查询s_emp表中工资最

25、低的员工的姓名：,SQL SELECT min（salary） 2 FROM s_emp,SQL SELECT last_name 2 FROM s_emp 3 WHERE salary = 最小工资（上一条的运行结果）,SQL SELECT last_name 2 FROM s_emp 3 WHERE salary = (SELECT min(salary) FROM s_emp),子查询,查询s_emp表中平均工资低于32部门的部门ID：,SQL SELECT avg（salary） 2 FROM s_emp 3 WHERE dept_id = 32;,SQL SELECT dept_id

26、,avg(salary) 2 FROM s_emp 3 GROUP BY dept_id 4 HAVING avg(salary)32部门的平均工资,SQL SELECT dept_id,avg(salary) 2 FROM s_emp 3 GROUP BY dept_id 4 HAVING avg(salary)(SELECT avg(salary) 5 FROM s_emp 6 WHERE dept_id = 32) 7 ORDER BY DEPT_ID;,子查询,查询s_emp表中平均工资低于32部门的部门ID及名称：,SQL SELECT dept_id,avg(salary),nam

27、e 2 FROM s_emp,s_dept 3 WHERE s_emp.dept_id = s_dept.id 4 GROUP BY dept_id,name 5 HAVING avg(salary)(SELECT avg(salary) 6 FROM s_emp 7 WHERE dept_id = 32) 8 ORDER BY DEPT_ID;,子查询举例,SQL SELECTlast_name, first_name, title 2 FROMs_emp 3 WHEREdept_in = 4 (SELECT ID 5FROM s_dept 6WHERE name = Finance 7 O

28、R region_id = 2); ORA-01427:single-row subquery returns more than one row,小结,SELECTselect_list FROMtable WHEREexpr operator (SELECTselect_list FROMtable);,第七章 数据建模及数据库设计,本章目标,了解系统开发的步骤 数据关系的定义 理解实体关系映射图（E-R图）,系统开发步骤,数据模型,Model of system in clients mind,Entity model of clients model,Table model of en

29、tity model,Tables on disk,Entity Relationship Modeling Concepts,Entity A thing of significance about which information needs to be known Examples: customers, sales representatives, orders Attribute Something that describes or qualifies an entity Examples: name, phone, identification number Relations

30、hip An association between two entities Examples: orders and items, customers and sales representatives,Entity Relationship Model,Create an entity relationship diagram from business specifications or narratives. Scenario . . . Assign one or more customers to a sales representative . . . . . . Some s

31、ales representatives do not yet have assigned customers . . .,Relationship Types,One-to-one Have a degree of one and only one in both directions. Are rare. Example: Husband and wife. 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. Ar

32、e very common. Example: passengers and plane. Many-to-many Have a degree of one or more in both directions. Are resolved with an intersection entity. Example: Employees and skills.,数据库3范式,数据库中的每一列都是不可再分的基本数据项，同一列中不能有多个值 数据库表中不存在非关键字段对任何候选关键字段的部分依赖 数据库表中不存在非关键字段对任何候选关键字段的传递引用,约束类型,PK Primary Key 唯一且非

33、空 FK Foreign Key 外键约束，值引用另一张表 已经存在的数据 UKUnique Key 唯一且可为空 NOT NULL 非空,约束举例,IDLAST_NAMEFIRST_NAME.DEPT_ID. 1VelasquezCarmen50 2NgaoLaDoris41 3NagayamaMidori31 4Quick-To-SeeMark10 5RopeburnAudry50,IDNAMEREGION_ID 10Finance1 31Sales1 41Operations1 50Administration1,Primary Key,Foreign Key,S_EMP Table,S

34、_DEPT Table,Primary Key,第八章 创建表,本章目标,掌握创建表的语法 Oracle的数据类型 使用约束,数据结构,一个Oracle数据库包含下列几种数据结构： Table 存储数据 View 从一个表或多个表的数句中得到的子集 Sequence 生成主键值 Index 提高查询性能,建表语法,CREATE TABLE schema.table (column datatype DEFAULT expr column_constraint, . table_constraint);,Oracle的数据类型,Char(size) 定长字符型，字符长度不够自动在右边加空格符号

35、Varchar2(size) 可变长字符型，大小必须指定 Number(m,n) 数字型，可存放实数和整数 Date 日期类型 Blob 2进制大对象其最大大小为4 GB 此数据类型映射到 Byte 类型的 Array。 Clob 2进制大对象其最大大小为4 GB 此数据类型映射到 String,命名规范,必须以字母开头 1-30个字符长度 只允许包含AZ, az, 09, _, $, and # 在一个数据库保证命名的唯一 不能使用Oracle内部的关键字,建表举例,CREATE TABLE Mytest( id number， name varchar2(32) );,CREATE TAB

36、LE Mytest( id number deault 11， name varchar2(32) );,建表使用约束举例,CREATE TABLE Mytest( id number check(id10), name varchar2(32) );,CREATE TABLE Mytest( id number check(id10), name varchar2(32) not null );,CREATE TABLE Mytest( id number UNIQUE, name varchar2(32) not null );,建表主键约束举例,CREATE TABLE Mytest(

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

38、rimary key, p_id number references parent(id) );,CREATE TABLE child( id number primary key, p_id number , foreign key(p_id) references parent(id) );,约束命名,CREATE TABLE child( id number constraint mytest_pk primary key, p_id number , foreign key(p_id) references parent(id) );,查看表的约束,SELECT * FROM user

39、_constraints WHERE table_name =CHILD,CONSTRAINT_TYPE C -check P -primary key R -forgien key U -unique,级联删除,CREATE TABLE child( id number primary key, p_id number references parent(id) on delete cascade );,CREATE TABLE child( id number primary key, p_id number references parent(id) on delete set null

40、 );,子查询创建表,CREATE TABLE emp_41 as ( select * from s_emp where dept_id =41 );,第九章 对数据的操作,本章目标,在已创建表中插入新的数据 修改已经存在的数据 删除表中的数据 理解事物控制及其重要性,DML命令,Description Adds a new row to the table. Modifies existing rows in the table. Removes existing rows from the table. Makes all pending changes permanent. Allow

41、s a rollback to that savepoint marker. Discards all pending data changes.,Command INSERT UPDATE DELETE COMMIT SAVEPOINT ROLLBACK,Insert插入语法,INSERT INTOtable (column , column.) VALUES(value , value.);,插入举例,CREATE TABLE Mytest( id number primary key, name varchar2(32), birth Date );,INSERT INTOmytest

42、(id,name,brith) VALUES(1,BluesWang,?);,更新语法,UPDATEtable SETcolumn = value , column = value WHEREcondition;,删除语法,DELETE FROMtable WHEREcondition;,Database Transactions,Contain one of the following statements: DML commands that make up one consistent change to the data One DDL command One DCL command

43、Begin when the first executable SQL command is executed. End with one of the following events: COMMIT or ROLLBACK DDL or DCL command executes (automatic commit) Errors, exit, or system crash,还原点,SQL UPDATE. SQL SAVEPOINT update_done; Savepoint created. SQL INSERT. SQL ROLLBACK TO update_done; Rollba

44、ck complete.,Controlling Transactions,COMMIT,ROLLBACK,INSERT,UPDATE,INSERT,DELETE,Savepoint Marker A,Savepoint Marker B,ROLLBACK,ROLLBACK to A,ROLLBACK to B,第十章 修改表结构及添加约束,本章目标,增加或者修改列 添加删除约束 删除表 删除表所有的数据,添加列,ALTER TABLE table ADD(column datatype DEFAULT exprNOT NULL , column datatype.);,添加列举例,向下表my

45、test添加名为age，类型为number的一列：,ALTER TABLE mytest ADD (age number);,再向下表mytest添加名为salary,类型为number(9,1)缺省值为8888的一列：,ALTER TABLE mytest ADD (salary number(9,1) default 8888);,删除列,ALTER TABLE table DROPcolumn , column .;,删除列举例,将表mytest的salary列删除：,ALTER TABLE mytest drop column salary ;,修改列,ALTER TABLEtable

46、 MODIFY(column datatype DEFAULT exprNOT NULL , column datatype.);,修改列举例,将表mytest的age列由原来的number类型更改为number（4，2）类型：,ALTER TABLE mytest modify (age number(4,2) ;,添加约束,SQL ALTER TABLEtable 2 ADD CONSTRAINT constraint type (column);,添加约束举例,将表mytest的id列添加主键约束：,ALTER TABLE mytest ADD constraints mytest_pk

47、 primary key(id);,将表child的p_id列添加外键约束：,ALTER TABLE child ADD constraints c_fk foreign key(p_id) references parent(id);,删除约束举例,SQL ALTER TABLEchild 2 DROP CONSTRAINTc_fk;,删除表及表的重命名,DROP TABLE table CASCADE CONSTRAINTS;,SQL RENAME 表名 TO 新名;,删除表数据,SQL TRUNCATE TABLE 表名;,小结,Command CREATE TABLE ALTER TA

48、BLE DROP TABLE RENAME TRUNCATE,Description Creates a table and indicated constraints. Modifies table structures and constraints. Removes the rows and table structure. Changes the name of a table, view, sequence, or synonym. Removes all rows from a table and releases the storage space.,第十一章 创建序列,本章目标

49、,掌握如何使用sequences 创建sequences 修改sequences 删除sequences,创建sequences语法,CREATE SEQUENCE name INCREMENT BY n START WITH n MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUE CYCLE | NOCYCLE CACHE n | NOCACHE,创建序列举例,SQL CREATE SEQUENCE s_dept_id 2 INCREMENT BY 1 3 START WITH 51 4 MAXVALUE 9999999 5 NOCACHE 6 N

50、OCYCLE; Sequence created.,序列举例,利用创建好的Sequence向mytest表中添加数据：,insert into mytest values(s.nextval,a),查看当前序列的值：,SELECT s.currval FROM dual,修改sequences语法,ALTER SEQUENCE name INCREMENT BY n START WITH n MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUE CYCLE | NOCYCLE CACHE n | NOCACHE,删除sequences语法,DROP 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-Se