【教学课件】第二讲SQL语言基础.ppt

第二讲 SQL语言基础,本章学习目标 SQL语言是数据库的标准语言。只有理解了SQL才能真正理解关系数据库。本章将介绍怎样使用“结构化查询语言”（SQL）来操作数据库。,内容安排,2.1 SQL简介2.2 SQLPLUS工具2.3 数据查询语言(DQL)2.4 数据操纵语言(DML)2.5 数据定义语言(DDL)2.6 数据控制语言(DCL)2.7 常用函数,2.4 数据操纵语言(DML),数据操纵语言允许用户对数据库中的数据进行insert、update和delete等操作。它只有三个命令：insert 将记录插入到数据库 update 修改数据库的记录 delete 删除数据库的记录,INSERT,insert 将记录插入到数据库INSERT INTOtable(column,column.)VALUES(value,value.);用SQL*Plus中的变量可以实现交互式的插入,SQL INSERT INTOs_dept(id,name,2region_id)3 VALUES(Enter value for department_id:61Enter value for department_name:AccountingEnter value for region_id:21 row created.,拷贝记录,SQL INSERT INTOhistory(id,last_name,salary,2 title,start_date)3 SELECTid,last_name,salary,4 title,start_date 5 FROMs_emp 6 WHEREstart_date 01-JAN-94;10 rows created.,UPDATE,UPDATEtableSETcolumn=value,column=valueWHEREcondition;,DELETE,DELETE FROMtableWHEREcondition;,注意：在一个会话中执行的DML命令，如果没有提交，在其他会话中将看不到那个未提交的会话中执行的DML命令。除非：（1）在DML命令之后执行了DDL命令或DCL命令（2）用户退出会话（3）终止实例，此时系统会自动发出commit命令使未提交的DML命令提交,2.5 数据定义语言(DDL),DDL数据定义语言用于创建和定义数据库对象，并且将对这些对象的定义保存在数据字典中。部分DDL命令列表如下：SQL命令 功能 create table 创建表 alter table 修改表 drop table 删除表 truncate table 删除表中所有行 create index 创建索引 drop index 删除索引,表,1表的建立 表是存储用户数据的基本结构。建立表主要指定义下列信息：列定义 完整性约束 表所在表空间 存储特性 可选择的聚集,CREATE TABLE schema.table(column datatype DEFAULT exprcolumn_constraint,.table_constraint);,Oracle的基本数据类型,数据类型 描述VARCHAR2(n)变长字符CHAR(n)定长字符NUMBER(p,s)变长数字DATE 日期及时间LONG 变长字符（长度可达2G）RAW(n)and LONG RAW 二进制数据CLOB 单字节字符（长度可达4G）BLOB 二进制数据（长度可达4G）BFILE 存储在外部文件中的二进制 数据（长度可达4G）,Oracle的完整性约束,Oracle利用完整性约束机制防止无效数据进入基表任何DML执行结果破坏完整性约束，该语句被回滚并返回错误,有效的约束条件NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK,约束的语法,列级约束 column CONSTRAINT constraint_name constraint_type,表级约束级column,.CONSTRAINT constraint_name constraint_type(column,.),约束表级约束,创建表：实例1,SQL CREATE TABLE s_dept 2(idNUMBER(7)3 CONSTRAINT s_dept_id_pk PRIMARY KEY,4 nameVARCHAR2(25)5 CONSTRAINT s_dept_name_nn NOT NULL,6 region_idNUMBER(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);,创建表：实例2,SQL CREATE TABLE s_emp 2(idNUMBER(7)3 CONSTRAINT s_emp_id_pk PRIMARY KEY,4 last_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)15 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 emp_41AS SELECTid,last_name,userid,start_dateFROMs_empWHEREdept_id=41;,查看约束信息,查询 USER_CONSTRAINTS 可以获取所有定义的约束的信息,SQL SELECTconstraint_name,constraint_type,2search_condition 3 FROMuser_constraints 4 WHEREtable_name=EMP;,CONSTRAINT_NAME C SEARCH_CONDITION-SYS_C00674 C EMPNO IS NOT NULL SYS_C00675 C DEPTNO IS NOT NULLEMP_EMPNO_PK P.,获取同约束条件相关的列的信息,查询 USER_CONS_COLUMNS 获取同约束条件相关的列的信息,SQL SELECTconstraint_name,column_name 2 FROMuser_cons_columns 3 WHEREtable_name=EMP;,CONSTRAINT_NAME COLUMN_NAME-EMP_DEPTNO_FK DEPTNOEMP_EMPNO_PK EMPNOEMP_MGR_FK MGRSYS_C00674 EMPNOSYS_C00675 DEPTNO,2表的修改 使用 ALTER TABLE 命令增加列修改列定义缺省值删除列,ALTER TABLE table ADD(column datatype DEFAULT expr,column datatype.);ALTER TABLE table MODIFY(column datatype DEFAULT expr,column datatype.);ALTER TABLEtable_name DROP COLUMN column_name.,增加约束条件,SQL ALTER TABLE emp 2 ADD CONSTRAINT emp_mgr_fk 3 FOREIGN KEY(mgr)REFERENCES emp(empno);Table altered.,使约束失效,在ALTER TABLE子句中使用DISABLE CONSTRAINT子句可以使约束条件失效,SQL ALTER TABLEemp 2 DISABLE CONSTRAINTemp_empno_pk;Table altered.,激活约束条件,使用ENABLE子句激活表中定义的失效的约束条件UNIQUE 或 PRIMARY KEY 索引在激活UNIQUE or PRIMARY KEY 约束时自动创建,SQL ALTER TABLEemp 2 ENABLE CONSTRAINTemp_empno_pk;Table altered.,3.表的删除,删除表的结构、表中的数据、表上所建立的索引、约束、触发器和权限 DROP TABLE table;,3.表的重命名,使用RENAME命令，可以修改表、视图、序列号、同义词的名称 RENAME s-dept TO department;若要使用RENAME命令，必须是数据对象的拥有者,索引,索引是一种数据库对象。在表的索引列上的每一值将包含一项，为行提供直接的快速存取。在下列情况下，Oracle可利用索引改进性能：按指定的索引列的值查找行。按索引列的顺序存取表。,1索引的建立自动 在定义 PRIMARY KEY 或 UNIQUE 约束时，系统自动创建唯一性索引手动 用户创建非唯一性索引提高访问的速度 CREATE INDEX index ON table(column,column.);CREATE INDEX id_idx ON s_emp(id);,2 索引的删除 DROP INDEX index;DROP INDEX id_idx;,3.下列情况不宜创建索引:基表数据较少查询中很少用作条件的列多数查询提取的记录超过2-4%基表经常被更新,视图,视图是一个逻辑表，它允许操作者从其它表或视图存取数据，视图本身不包含数据。视图所基于的表称为基表。视图的作用有：限制数据访问简化查询增强数据独立性同类数据不同显示,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,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 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 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 45,ID LAST_NAME TITLE-10 Havel Warehouse Manager24 Dancs Stock Clerk25 Schwartz Stock Clerk,1.视图的建立CREATE OR REPLACE VIEW view(attr,attr.)AS subqueryWITH CHECK OPTIONWITH READ ONLY子查询可以是复杂的查询语句.子查询中不能含有ORDER BY子句,视图创建实例,SQL CREATE OR REPLACE VIEW empvu45 2 AS SELECTid,last_name,title 3 FROMs_emp 4 WHEREdept_id=45;View created.,使用组函数创建视图,SQL CREATE VIEW dept_sum_vu 2(name,minsal,maxsal,avgsal)3 AS SELECTd.name,MIN(e.salary),4 MAX(e.salary),AVG(e.salary)5 FROMs_emp e,s_dept d 6 WHEREe.dept_id=d.id 7 GROUP BY d.name;View created.,基表数包含函数包含组数视图上的DML命令,比较：简单视图和复杂视图,复杂视图One or moreYesYesNo,简单视图OneNoNoYes,DML操作的规则,在简单视图上可以进行DML操作 若视图中含有下列内容，则不能删除组函数GROUP BY 子句DISTINCT 命令,含有下列内容时，不能修改数据上述情况表达式定义的列ROWID含有下列内容时，不能插入数据上述情况视图中未选中NOT NULL列,WITH CHECK OPTION 子句,表示对视图进行update、insert、delete操作时，要保证满足视图定义的条件SQL CREATE OR REPLACE VIEW empvu41 2 AS SELECT*3 FROMemp 4 WHEREdept_id=41 5 WITH CHECK OPTION;改变上述视图中任何记录的部门编号的操作都将失败，原因是由于违反了WITH CHECK OPTION约束,WITH READ ONLY 子句,在有 WITH READ ONLY 选项的视图上，不可以执行DML命令,2.视图的删除 DROP VIEW empvu45;,同义词,同义词即数据库对象的别名创建同义词可以缩短对象名称的长度，以便简化访问,1 创建同义词 CREATE SYNONYM d_sum FOR dept_sum_vu；2 删除同义词 DROP SYNONYM d_sum;,序列,序列为ORACLE整数，最多可有38位数字序列号生成是独立于表，所以同一序列生成器可用于一个和多个表序列号可用于生成唯一的主码,CREATE SEQUENCE sequenceINCREMENT BY nSTART WITH nMAXVALUE n|NOMAXVALUEMINVALUE n|NOMINVALUECYCLE|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)VALUES(emp_sequence.nextval,scott);3 序列的删除 DROP SEQUENCE emp_sequence,4 查询序列号从 USER_SEQUENCES 中查询序列号的信息,SQL SELECTsequence_name,min_value,max_value,2 increment_by,last_number 3 FROMuser_sequences;,注意：当用户执行DDL语句时，在每一条语句执行前后，Oracle都将提交当前的事务。例如：如果用户使用insert命令插入记录到数据库后，执行了一条DDL语句，如create table，此时来自insert命令的数据将被提交到数据库。当DDL语句执行完成时，DDL语句会自动提交，不能回滚。,2.6 数据控制语言,1 权限管理 Oracle可以允许某些用户使用某些特定的表而不允许另外一些用户使用这些表。这是通过权限分配 实现的。GRANT命令:为用户或角色授予系统权限或针对具体对象授权 REVOKE命令:从用户或角色处收回系统或对象权限。,2 事务处理命令 在完成一条或多条DML命令后，可以用COMMIT:将更改保存到数据库 ROLLBACK:撤销更改,提交,显式提交 用COMMIT语句直接完成的提交为显式提交。其格式为：SQLCOMMIT;,隐式提交 用SQL语句间接完成的提交为隐式提交 这些语句包括：ALTER，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，QUIT，REVOKE,自动提交 若在SQLPLUS上把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交。其格式为：SQLSET AUTOCOMMIT ON;,2.7 常用函数,单行函数（对每条记录有效）字符类数字类日期类转换类组函数（对多条记录有效）,字符函数,LOWERUPPERINITCAPCONCATSUBSTRLENGTH,转换字符串格式,转换字符串格式LOWER(SQL Course)sql courseUPPER(SQL Course)SQL COURSEINITCAP(SQL Course)Sql Course,字符串操作函数,CONCAT(Good,String)GoodStringSUBSTR(String,1,3)StrSUBSTR(String,-1,1)g LENGTH(String)6,数字函数,ROUND-对数值进行四舍五入操作TRUNC-对数值进行截断操作MOD-对数值进行取余操作,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)40MOD(1600,300)100,日期函数,Oracle将日期按照内部格式将日期存成以下七个字节：世纪、年、月、日、时、分、秒在Oracle中日期缺省的显示格式为：DD-MON-YY可以使用系统变量SYSDATE获取系统时间查看SYSDATE时，可以使用虚拟表DUAL,MONTHS_BETWEEN(01-SEP-95,11-JAN-94)19.774194ADD_MONTHS(11-JAN-94,6)11-JUL-94,转换函数,TO_CHAR 将数字或日期转换为字符串TO_NUMBER 将字符串转换成数字TO_DATE 将字符串转换成日期,日期格式串分类,YYYY 表示完整的四位数字年YEAR 表示英文拼写的年MM 表示两位数字月MONTH 表示英文全拼的月DY 表示三位缩写的星期DAY 表示英文全拼的星期,转换日期举例,SQLSELECT 2 to_char(sysdate,mm-dd-yyyy hh12:mi:ss)3 现在时刻 4 FROM dual 5/现在时刻-02-23-2009 11:23:05,SQLSELECT 2 to_date(21-07-2007,DD-MM-YYYY)3 日期 4 FROM dual 5/日期-21-07-07,DECODE函数,格式 DECODE(col|expression,search1,result1,search2,result2,.,default),实例,SELECT ename,job,sal,decode(job,SALESMAN,1.10*sal,CLERK,1.15*sal,PRESIDENT,1.20*sal,sal)REVISED_SALARYFROM emp,组函数,AVG COUNT MAXMINSUMSTDDEV(标准偏差）VARIANCE（方差）,GROUP BY和HAVING子句,在查询语句中使用GROUP BY子句将记录分成组,GROUP BY子句中的列一定要出现在SELECT中使用HAVING子句作为组函数的条件，不能用WHERE子句作为组函数的条件缺省情况下，ORACLE将查询结果按照分组列的升序排列。可以使用ORDER BY子句改变结果的排列顺序,SQL SELECTjob,SUM(sal)PAYROLL 2 FROMemp 3 WHEREjob NOT LIKE VP%4 GROUP BYjob 5 HAVINGSUM(sal)5000 6 ORDER BYSUM(sal);,非法使用组函数（1）,使用了组函数的查询语句中，出现未写在GROUP BY子句中的单独列,SQL SELECTregion_id,COUNT(name)2 FROMdept;SELECT region_id,COUNT(name)*ERROR at line 1:ORA-00937:not a single-group group function,非法使用组函数（2）,不能使用WHERE子句作为组函数的限制条件只能使用HAVING子句作为限制条件,SQL SELECTdeptno,AVG(sal)2 FROMemp 3 WHEREAVG(sal)2000 4 GROUP BYdeptno;WHERE AVG(salary)2000*ERROR at line 3:ORA-00934:group function is not allowed here,