《【教学课件】第二讲SQL语言基础.ppt》由会员分享，可在线阅读，更多相关《【教学课件】第二讲SQL语言基础.ppt（73页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第二讲第二讲 SQL SQL语言基础语言基础本章学习目标本章学习目标 SQL语言是数据库的标准语言。只有理语言是数据库的标准语言。只有理解了解了SQL才能真正理解关系数据库。本才能真正理解关系数据库。本章将介绍怎样使用章将介绍怎样使用“结构化查询语言结构化查询语言”（SQL）来操作数据库。）来操作数据库。内容安排内容安排n2.1 SQL2.1 SQL简介简介n2.2 SQLPLUS2.2 SQLPLUS工具工具n2.3 2.3 数据查询语言数据查询语言(DQL)(DQL)n2.4 2.4 数据操纵语言数据操纵语言(DML)(DML)n2.5 2.5 数据定义语言数据定义语言(DDL)(DDL)
2、n2.6 2.6 数据控制语言数据控制语言(DCL)(DCL)n2.7 2.7 常用函数常用函数2.4 2.4 数据操纵语言数据操纵语言(DML)(DML)数据操纵语言允许用户对数据库中的数数据操纵语言允许用户对数据库中的数据进行据进行insert、update和和delete等操作。等操作。它只有三个命令：它只有三个命令：ninsert 将记录插入到数据库将记录插入到数据库 nupdate 修改数据库的记录修改数据库的记录 ndelete 删除数据库的记录删除数据库的记录INSERTninsert 将记录插入到数据库将记录插入到数据库INSERT INTOINSERT INTO tablet
3、able(columncolumn,columncolumn.).)VALUES(VALUES(valuevalue,valuevalue.);.);n用用SQL*Plus中的变量可以实现交互式的插入中的变量可以实现交互式的插入SQL INSERT INTOSQL INSERT INTOs_dept(id,name,s_dept(id,name,2 2region_id)region_id)3 VALUES 3 VALUES (&department_id,&department_name,(&department_id,&department_name,4 4®ion_id);®
4、ion_id);Enter value for department_id:Enter value for department_id:6161Enter value for department_name:Enter value for department_name:AccountingAccountingEnter value for region_id:Enter value for region_id:2 21 row created.1 row created.n拷贝记录拷贝记录SQL INSERT INTOSQL INSERT INTOhistory(id,last_name,s
5、alary,history(id,last_name,salary,2 title,start_date)2 title,start_date)3 SELECT 3 SELECTid,last_name,salary,id,last_name,salary,4 4 title,start_datetitle,start_date 5 FROM 5 FROMs_emps_emp 6 WHERE 6 WHEREstart_date 01-JAN-94;start_date CREATE TABLE s_dept 2 (idNUMBER(7)3 CONSTRAINT s_dept_id_pk PRI
6、MARY KEY,4 nameVARCHAR2(25)5 CONSTRAINT s_dept_name_nn NOT NULL,6 region_id NUMBER(7)7 CONSTRAINT s_dept_region_id_fk REFERENCES 8 s_region(id),9 CONSTRAINT s_dept_name_region_id_uk UNIQUE 10 (name,region_id);创建表：实例创建表：实例2SQL CREATE TABLE s_emp 2 (idNUMBER(7)3 CONSTRAINT s_emp_id_pk PRIMARY KEY,4 la
7、st_nameVARCHAR2(25)5 CONSTRAINT s_emp_last_name_nn NOT NULL,6 first_nameVARCHAR2(25),7 useridVARCHAR2(8)8 CONSTRAINT s_emp_userid_nn NOT NULL 9 CONSTRAINT s_emp_userid_uk UNIQUE,10 start_dateDATE DEFAULT SYSDATE,11 commentsVARCHAR2(25),12 manager_idNUMBER(7),13 titleVARCHAR2(25),14 dept_idNUMBER(7)1
8、5 CONSTRAINT s_emp_dept_id_fk REFERENCES 16 s_dept(id),17 salaryNUMBER(11,2),18 commission_pctNUMBER(4,2)19 CONSTRAINT s_emp_commission_pct_ck CHECK 20 (commission_pct IN(10,12.5,15,17.5,20);创建表：实例创建表：实例3 使用使用CREATE TABLE和和AS创建表并且创建表并且插入记录插入记录CREATE TABLE CREATE TABLE emp_41emp_41AS AS SELECTSELECTi
9、d,last_name,userid,start_dateid,last_name,userid,start_dateFROMFROMs_emps_empWHEREWHEREdept_id=41;dept_id=41;查看约束信息查看约束信息n查询查询 USER_CONSTRAINTS 可以获取所有定可以获取所有定义的约束的信息义的约束的信息SQL SELECT constraint_name,constraint_type,2search_condition 3 FROMuser_constraints 4 WHEREtable_name=EMP;CONSTRAINT_NAME C SEAR
10、CH_CONDITION-SYS_C00674 C EMPNO IS NOT NULL SYS_C00675 C DEPTNO IS NOT NULLEMP_EMPNO_PK P.获取同约束条件相关的列的信息获取同约束条件相关的列的信息n查询查询 USER_CONS_COLUMNS 获取同约束条获取同约束条件相关的列的信息件相关的列的信息SQL SELECTconstraint_name,column_name 2 FROMuser_cons_columns 3 WHEREtable_name=EMP;CONSTRAINT_NAME COLUMN_NAME-EMP_DEPTNO_FK DEPT
11、NOEMP_EMPNO_PK EMPNOEMP_MGR_FK MGRSYS_C00674 EMPNOSYS_C00675 DEPTNO2 2表表的修改的修改 使用使用 ALTER TABLE 命令命令n增加列增加列n修改列修改列n定义缺省值定义缺省值n删除列删除列nALTER TABLE table ADD (column datatype DEFAULT expr ,column datatype.);nALTER TABLE table MODIFY (column datatype DEFAULT expr ,column datatype.);nALTER TABLEtable_nam
12、e DROP COLUMN column_name.增加约束条件增加约束条件SQL ALTER TABLE emp 2 ADD CONSTRAINT emp_mgr_fk 3 FOREIGN KEY(mgr)REFERENCES emp(empno);Table altered.Table altered.使约束失效使约束失效n在在ALTER TABLE子句中使用子句中使用DISABLE CONSTRAINT子句可以使约束条件失效子句可以使约束条件失效SQL ALTER TABLEemp 2 DISABLE CONSTRAINT emp_empno_pk;Table altered.Table
13、 altered.激活约束条件激活约束条件n使用使用ENABLE子句激活表中定义的失效的约子句激活表中定义的失效的约束条件束条件nUNIQUE 或或 PRIMARY KEY 索引在激活索引在激活UNIQUE or PRIMARY KEY 约束时自动创建约束时自动创建SQL ALTER TABLEemp 2 ENABLE CONSTRAINTemp_empno_pk;Table altered.Table altered.3.3.表的删除表的删除 删除表的结构、表中的数据、表上所建立删除表的结构、表中的数据、表上所建立的索引、约束、触发器和权限的索引、约束、触发器和权限 DROP TABLE t
14、able;3.3.表的重命名表的重命名n使用使用RENAME命令，可以修改表、视图、命令，可以修改表、视图、序列号、同义词的名称序列号、同义词的名称 RENAME s-dept TO department;n若要使用若要使用RENAME命令，必须是数据对象命令，必须是数据对象的拥有者的拥有者索引索引n索引是一种数据库对象。在表的索引列索引是一种数据库对象。在表的索引列上的每一值将包含一项，为行提供直接上的每一值将包含一项，为行提供直接的快速存取。在下列情况下，的快速存取。在下列情况下，Oracle可可利用索引改进性能：利用索引改进性能：n按指定的索引列的值查找行。按指定的索引列的值查找行。n按
15、索引列的顺序存取表。按索引列的顺序存取表。1 1索引的建立索引的建立n自动自动 在定义在定义 PRIMARY KEY 或或 UNIQUE 约束约束时，系统自动创建唯一性索引时，系统自动创建唯一性索引n手动手动 用户创建非唯一性索引提高访问的速度用户创建非唯一性索引提高访问的速度 CREATE INDEX index ON table(column,column.);CREATE INDEX id_idx ON s_emp(id);2 索引的索引的删除删除 DROP INDEX index;DROP INDEX id_idx;3.下列情况不宜创建索引下列情况不宜创建索引:n基表数据较少基表数据较
16、少n查询中很少用作条件的列查询中很少用作条件的列n多数查询提取的记录超过多数查询提取的记录超过2-4%n基表经常被更新基表经常被更新视图视图 视图是一个逻辑表，它允许操作者从其它表视图是一个逻辑表，它允许操作者从其它表或视图存取数据，视图本身不包含数据。视图或视图存取数据，视图本身不包含数据。视图所基于的表称为基表。视图的作用有：所基于的表称为基表。视图的作用有：n限制数据访问限制数据访问n简化查询简化查询n增强数据独立性增强数据独立性n同类数据不同显示同类数据不同显示ID LAST_NAME FIRST_NAME TITLE DEPT_ID-1 Velasquez Carmen Presid
17、ent 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,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
18、 Marta Warehouse Manager 4511 Magee Colin Sales Representative 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 Stoc
19、k Clerk 4219 Patel Vikram Stock Clerk 4220 Newman Chad Stock 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 Schwar
20、tz Stock Clerk1.视图的建立视图的建立CREATE OR REPLACE VIEW CREATE OR REPLACE VIEW viewview (attrattr,attrattr.).)AS AS subquerysubqueryWITH CHECK OPTIONWITH CHECK OPTIONWITH READ ONLYWITH READ ONLYn子查询可以是复杂的查询语句子查询可以是复杂的查询语句.n子查询中不能含有子查询中不能含有ORDER BY子句子句视图创建实例视图创建实例SQL CREATE OR REPLACE VIEW SQL CREATE OR REP
21、LACE VIEW empvu45empvu45 2 AS SELECT 2 AS SELECTid,last_name,titleid,last_name,title 3 FROM 3 FROMs_emps_emp 4 WHERE 4 WHEREdept_id=45;dept_id=45;View created.View created.使用组函数创建视图使用组函数创建视图SQL CREATE VIEW dept_sum_vuSQL CREATE VIEW dept_sum_vu 2 (name,minsal,maxsal,avgsal)2 (name,minsal,maxsal,avgs
22、al)3 AS SELECT 3 AS SELECTd.name,MIN(e.salary),d.name,MIN(e.salary),4 MAX(e.salary),AVG(e.salary)4 MAX(e.salary),AVG(e.salary)5 FROM 5 FROMs_emp e,s_dept ds_emp e,s_dept d 6 WHERE 6 WHEREe.dept_id=d.ide.dept_id=d.id 7 GROUP BY 7 GROUP BY d.name;d.name;View created.View created.基表数包含函数包含组数视图上的DML命令比较
23、：简单视图和复杂视图比较：简单视图和复杂视图复杂视图复杂视图复杂视图复杂视图One or moreOne or moreYesYesYesYesNoNo简单视图简单视图简单视图简单视图OneOneNoNoNoNoYesYesDML操作的规则操作的规则n在简单视图上可以进行在简单视图上可以进行DML操作操作 n若视图中含有下列内容，则不能删除若视图中含有下列内容，则不能删除n组函数组函数nGROUP BY 子句子句nDISTINCT 命令命令n含有下列内容时，不能修改数据含有下列内容时，不能修改数据n上述情况上述情况n表达式定义的列表达式定义的列nROWIDn含有下列内容时，不能插入数据含有下列
24、内容时，不能插入数据n上述情况上述情况n视图中未选中视图中未选中NOT NULL列列WITH CHECK OPTION 子句子句n表示对视图进行表示对视图进行update、insert、delete操作时，操作时，要保证满足视图定义的条件要保证满足视图定义的条件SQL CREATE OR REPLACE VIEW empvu41SQL CREATE OR REPLACE VIEW empvu41 2 AS SELECT 2 AS SELECT*3 FROM 3 FROMempemp 4 WHERE 4 WHEREdept_id=41dept_id=41 5 WITH CHECK OPTION;
25、5 WITH CHECK OPTION;n改变上述视图中任何记录的部门编号的操作都改变上述视图中任何记录的部门编号的操作都将失败，原因是由于违反了将失败，原因是由于违反了WITH CHECK OPTION约束约束WITH READ ONLY 子句子句n在有在有 WITH READ ONLY 选项的视图上，选项的视图上，不可以执行不可以执行DML命令命令 2.视图的删除视图的删除 DROP VIEW empvu45;同义词同义词n同义词即数据库对象的别名同义词即数据库对象的别名n创建同义词可以缩短对象名称的长度，以便简创建同义词可以缩短对象名称的长度，以便简化访问化访问1 创建同义词创建同义词
26、CREATE SYNONYM d_sum FOR dept_sum_vu；2 删除删除同义词同义词 DROP SYNONYM d_sum;序列序列n序列为序列为ORACLEORACLE整数，最多可有整数，最多可有3838位数字位数字n序列号生成是独立于表，所以同一序列生成器序列号生成是独立于表，所以同一序列生成器可用于一个和多个表可用于一个和多个表n序列号可用于生成唯一的主码序列号可用于生成唯一的主码CREATE SEQUENCE sequenceINCREMENT BY nSTART WITH nMAXVALUE n|NOMAXVALUEMINVALUE n|NOMINVALUECYCLE|
27、NOCYCLECACHE n|NOCACHE;1 创建序列创建序列SQL CREATE SEQUENCE emp_sequence 2 INCREMENT BY 1 3 START WITH 1 4 NOMAXVALUE 5 NOCYCLE 6 CACHE 10;序列已创建。序列已创建。2 序列的使用序列的使用 NEXTVAL:返回序列生成的下一个值，返回序列生成的下一个值，CURRVAL:返回序列的当前值返回序列的当前值 必须先使用必须先使用NEXTVAL产生一个序列数值，才产生一个序列数值，才能使用能使用CURRVAL查看查看 INSERT INTO emp(empno,ename)VAL
28、UES(emp_sequence.nextval,scott);3 序列的删除序列的删除 DROP SEQUENCE emp_sequence 4 查询序列号查询序列号从从 USER_SEQUENCES 中查询序列号的信息中查询序列号的信息SQL SELECT sequence_name,min_value,max_value,2 increment_by,last_number 3 FROMuser_sequences;n注意：当用户执行注意：当用户执行DDL语句时，在每一条语语句时，在每一条语句执行前后，句执行前后，Oracle都将提交当前的事务。都将提交当前的事务。例如：如果用户使用例如
29、：如果用户使用insert命令插入记录到数命令插入记录到数据库后，执行了一条据库后，执行了一条DDL语句，如语句，如create table，此时来自，此时来自insert命令的数据将被提交到命令的数据将被提交到数据库。当数据库。当DDL语句执行完成时，语句执行完成时，DDL语句语句会自动提交，不能回滚。会自动提交，不能回滚。2.6 数据控制语言数据控制语言1 权限管理权限管理 Oracle可以允许某些用户使用某些特定可以允许某些用户使用某些特定的表而不允许另外一些用户使用这些表的表而不允许另外一些用户使用这些表。这是通过权限分配。这是通过权限分配 实现的。实现的。GRANT命令命令:为用户或
30、角色授予系统权为用户或角色授予系统权限或针对具体对象授权限或针对具体对象授权 REVOKE命令命令:从用户或角色处收回系从用户或角色处收回系统或对象权限。统或对象权限。2 事务处理命令事务处理命令 在完成一条或多条在完成一条或多条DML命令后，可以用命令后，可以用COMMIT:将更改保存到数据库将更改保存到数据库 ROLLBACK:撤销更改撤销更改提交提交n显式提交显式提交 用用COMMIT语句直接完成的提交为显语句直接完成的提交为显式提交。其格式为：式提交。其格式为：SQLCOMMIT;n隐式提交隐式提交 用用SQL语句间接完成的提交为隐式提交语句间接完成的提交为隐式提交 这些语句包括：这些
31、语句包括：ALTER，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，QUIT，REVOKEn自动提交自动提交 若若在在SQLPLUS上把上把AUTOCOMMIT设设置为置为ON，则在插入、修改、删除语句执，则在插入、修改、删除语句执行后，系统将自动进行提交。其格式为：行后，系统将自动进行提交。其格式为：SQLSET AUTOCOMMIT ON;2.7 常用函数常用函数n单行函数（对每条记录有效）单行函数（对每条记录有效）n字符类字符类n数字类数字类n日期类日期类n转换类转换类n组函数（对多条记录有效）组函数（对多条记录有效）字符函数字符函数nLOWERnU
32、PPERnINITCAPnCONCATnSUBSTRnLENGTH转换字符串格式转换字符串格式转换字符串格式转换字符串格式nLOWER(SQL Course)sql coursenUPPER(SQL Course)SQL COURSEnINITCAP(SQL Course)Sql Course字符串操作函数字符串操作函数nCONCAT(Good,String)GoodStringnSUBSTR(String,1,3)StrnSUBSTR(String,-1,1)g nLENGTH(String)6数字函数数字函数nROUND -对数值进行四舍五入操作对数值进行四舍五入操作nTRUNC -对数值
33、进行截断操作对数值进行截断操作nMOD -对数值进行取余操作对数值进行取余操作nROUND(45.923,2)45.92nROUND(45.923,0)46nROUND(45.923,-1)50nTRUNC(45.923,2)45.92nTRUNC(45.923)45nTRUNC(45.923,-1)40nMOD(1600,300)100日期函数日期函数nOracle将日期按照内部格式将日期存成以下七将日期按照内部格式将日期存成以下七个字节：世纪、年、月、日、时、分、秒个字节：世纪、年、月、日、时、分、秒n在在Oracle中日期缺省的显示格式为：中日期缺省的显示格式为：DD-MON-YYn可以
34、使用系统变量可以使用系统变量SYSDATE获取系统时间获取系统时间n查看查看SYSDATE时，可以使用虚拟表时，可以使用虚拟表DUALnMONTHS_BETWEEN(01-SEP-95,11-JAN-94)19.774194nADD_MONTHS(11-JAN-94,6)11-JUL-94转换函数转换函数nTO_CHAR 将数字或日期转换为字符串将数字或日期转换为字符串nTO_NUMBER 将字符串转换成数字将字符串转换成数字nTO_DATE 将字符串转换成日期将字符串转换成日期日期格式串分类日期格式串分类nYYYY 表示完整的四位数字年表示完整的四位数字年nYEAR 表示英文拼写的年表示英文
35、拼写的年nMM 表示两位数字月表示两位数字月nMONTH 表示英文全拼的月表示英文全拼的月nDY 表示三位缩写的星期表示三位缩写的星期nDAY 表示英文全拼的星期表示英文全拼的星期转换日期举例转换日期举例SQLSELECT 2 to_char(sysdate,mm-dd-yyyy hh12:mi:ss)3 现在时刻现在时刻 4 FROM dual 5 /现在时刻现在时刻-02-23-2009 11:23:05SQLSELECT 2 to_date(21-07-2007,DD-MM-YYYY)3 日期日期 4 FROM dual 5 /日期日期-21-07-07DECODE函数函数n格式格式 D
36、ECODE(col|expression,search1,result1 ,search2,result2,.,default)实例实例SELECT ename,job,sal,SELECT ename,job,sal,decode(job,SALESMAN,1.10*sal,decode(job,SALESMAN,1.10*sal,CLERK,1.15*sal,CLERK,1.15*sal,PRESIDENT,1.20*sal,PRESIDENT,1.20*sal,sal)REVISED_SALARY sal)REVISED_SALARYFROM empFROM emp组函数组函数nAVG
37、nCOUNT nMAXnMINnSUMnSTDDEV(标准偏差）标准偏差）nVARIANCE（方差）方差）GROUP BY和和HAVING子句子句n在查询语句中使用在查询语句中使用GROUP BY子句将记录子句将记录分成组分成组,GROUP BY子句中的列一定要出子句中的列一定要出现在现在SELECT中中n使用使用HAVING子句作为组函数的条件，子句作为组函数的条件，不不能用能用WHERE子句作为组函数的条件子句作为组函数的条件n缺省情况下，缺省情况下，ORACLE将查询结果按照分将查询结果按照分组列的升序排列。可以使用组列的升序排列。可以使用ORDER BY子子句改变结果的排列顺序句改变结
38、果的排列顺序SQL SELECTSQL SELECTjob,SUM(sal)PAYROLLjob,SUM(sal)PAYROLL 2 FROM 2 FROMempemp 3 WHERE 3 WHEREjob NOT LIKE VP%job NOT LIKE VP%4 GROUP BY 4 GROUP BYjobjob 5 HAVING 5 HAVINGSUM(sal)5000SUM(sal)5000 6 ORDER BY 6 ORDER BYSUM(sal);SUM(sal);非法使用组函数（非法使用组函数（1）n使用了组函数的查询语句中，出现未写使用了组函数的查询语句中，出现未写在在GROU
39、P BY子句中的单独列子句中的单独列SQL SELECTSQL SELECTregion_id,COUNT(name)region_id,COUNT(name)2 FROM 2 FROMdept;dept;SELECT region_id,COUNT(name)SELECT region_id,COUNT(name)*ERROR at line 1:ERROR at line 1:ORA-00937:not a single-group group functionORA-00937:not a single-group group function非法使用组函数（非法使用组函数（2）n不能使
40、用不能使用WHERE子句作为组函数的限子句作为组函数的限制条件制条件n只能使用只能使用HAVING子句作为限制条件子句作为限制条件SQL SELECTSQL SELECTdeptno,AVG(sal)deptno,AVG(sal)2 FROM 2 FROMempemp 3 WHERE 3 WHEREAVG(sal)2000AVG(sal)2000 4 GROUP BY 4 GROUP BYdeptno;deptno;WHERE AVG(salary)2000WHERE AVG(salary)2000 *ERROR at line 3:ERROR at line 3:ORA-00934:group function is not allowed hereORA-00934:group function is not allowed here