清华大学计算中心ORACLE培训资料SQL讲稿.ppt

1、SQL语言与SQL*PLUS,Oracle9i 数据库语言,SQL命令,SQL关键字（以下命令为SQL命令，以“；”结束） Alter、 Audit、Commit、Comment、 Create、Delete Drop、Grant、Insert、Lock、NoAudit 、Rename Revoke、Rollback、Select 、Update 、Validate。,SQL*PLUS命令,SQL*PLUS关键字（以下命令以“CR”结束） 、 #、/、 Accept、 Append、 Break、 Btitle、 Change 、 Clear、 Column、 Compute、 Connect

2、、 Copy、 Define、 Del、 Describe、 Disconnect、 Document、 Edit、 Exit、 Get 、 Help、 Host 、 Input、 List、 Newpage 、 Pause、 Quit、 Remark、 Run、 Save、 Set、 Show、 Spool、 Start、 Timing、 Ttitle、 Undefine、,编辑和运行SQL缓冲区的SQL*PLUS命令,命令 缩写 解释APPEND text A text 行尾增加 text CHANGE C/old/new 在当前行中将old换为/new CHANGE C/text/ 在当

3、前行中删除 textCLEAR Buffer CL Buff 清除缓冲区 DEL DEL 删除当前行 INPUT I 增加一行 INPUT text I text 增加有text组成的行 LINE L 显示缓冲区内容 LINE n L n 显示第 n 行 RUN R 运行SQL缓冲区命令,Oracle Quiz,QL 测 试 Oracle Assessment Tests,Oracle Assessment Tests,You query the database with this command: SELECT name FROM employee WHERE name LIKE _a%;

4、Which names are displayed?A. names starting with aB. names starting with a or AC. names containing a as the second letterD. names containing a as any letter except the first,Oracle Assessment Tests,For which task would it be most appropriate to use the keyword DISTINCT?A. Identify duplicate rows in

5、a table.B. Identify which column has unique data.C. Eliminate duplicate columns in a table.D. Eliminate duplicate rows in the result set.,Oracle Assessment Tests,You query the database with this SQLstatement: SELECT name,NVL(salary,0) FROM employee WHRER salary IS NULL ORDER BY name;What is displaye

6、d for the SALARY columnwhen a NULL value is returned? A. 0 B. NULL C.spaces D.nothing,For which task would you use the BETWEEN operator?A. Query the database for unknown values.B. Query the database for a range of values.C. Query the database for a character pattern.D. Query the database for values

7、in a specified list. (Answers: C D A B),Oracle Assessment Tests,Oracle Quiz,DML,DCL 测 试 Oracle Assessment Tests,Oracle Assessment Tests,What happens when you update a tables without a WHERE clause?A. The statement will not execute.B. Only the rows specified will be updated.C. All of the rows in the

8、table will be updated.D. The statement will execute,but no change will be made.,Oracle Assessment Tests,Which commands cause a transaction to end?A. ALTERB. GRANT.C. DELETE.D. INSERTE. UPDATE.F. ROLLBACK,Oracle Assessment Tests,The Correct Answers 1.C2.A B F,Oracle Quiz,DDL 测 试 Oracle Assessment Tes

9、ts,Oracle Assessment Tests,You query the database with this command: SELECT object_name FROM user_objects WHERE object_type =TABLE;Which values are displayed?A. names of all objects you ownB. only the names of tables you ownC. names of all objects you have privileges toD. only the names of tables yo

10、u have privileges to access.,Oracle Assessment Tests,You query the database with this command: SELECT object_name FROM all_objects WHERE object_type =TABLE;Which values are displayed?A. only names of table you ownB. only names of objects you ownC. Only names of all objects you have privileges to acc

11、ess.D. only the names of all the tables you can access.,Oracle Assessment Tests,You attempt to create the ALPHA_3000 tablewith this statement:1. CREATE TABLE alpha_30002. (3000_id number(9)3. CONSTRAINT alpha_3000_id_pk PRIMARY KEY,4. name VARCHAR2(25),5. title VARCHAR2(25),6. Idname VARCHAR2(25)7.

12、CONSTRAINT alpha_3000_id_nn NOT NULL); What line in the statement will cause an error? A. 1 B. 2 C.3 D.7,Oracle Assessment Tests,You alter the database with this command: ALTER TABLE employee ADD (end_date DATE);Which task is accomplished ?A. A constraint is added to an existing column.B. A constrai

13、nt is modified on an existing column.C. A new column with a constraint is added to the table.D. A new column with no constraint is added to the table.,You attempt to create a view with this command: CREATE VIEW last_first_vu ASSELECT first_name| | last_name“Employee Names” FROM employee ORDER BY las

14、t_name,first_name;Which clause causes an error? A. FROM employee.B. ORDER BY last_name,first_name.C. CREATE VIEW last_first_vuD. SELECT first_name| |last_name”Employee Names”,Oracle Assessment Tests,Oracle Assessment Tests,You alter the database with this command: RENAME streets to CITY; Which task

15、is accomplished ?A. The streets user is renamed city.B. The STREETS table is renamed CITY.C. The STREETS column is renamed CITY.D. The streets constraints is renamed city.,Oracle Assessment Tests,The Correct Answers B,D,B,D,B,B,使用 SQL*PLUS命令,SQL*PLUS是Oracle对SQL语言的扩充,是一中交互式的表报生成工具.它使用SQL命令从Oracle中查询信

16、息,用SQL*PLUS命令设置控制表报的输出格式,对SQL命令加以扩充,可以对题目,列标题,以及汇总信息进行控制. 定义表头与表尾 定义表头: SQLTtitle 描述 定义表尾: SQLBtitle 描述 option: Left 左对齐 Center 居中 Right 右对齐 使用TITLE 则报表输出在每页加上日期及页号,SQL*PLUS命令总结,聚组函数的使用,聚组函数是从一组中返回汇总信息，聚组函数有SUM,COUNTCOUNT DISTINCT,MAX,MIN,AVG,STDDEV 计算max,min,avg,suma.SQLSELECT MIN(sal),MAX(sal),AVG

17、(sal), SUM(sal) FROM emp;b.找出具有最高工资的员工？ SQLSELECT ename,job,sal FROM emp WHERE sal=(SELECT MAX(sal) FROM emp); COUNT与COUNT DISTINCTa.求emp表中总行数？ SQLSELECT COUNT(*) FROM emp;b.求job的行数？ SQLSELECT COUNT(job) FROM emp;c.求有多少工中？SQLSELECT COUNT(DISTINCT job) FROM emp;,SELECT 字句中目标的一致性不能把单行函数与聚组函数混在一起使用，如 S

18、QLSELECT ename,SUM(sal) FROM emp; 单行目标 聚组函数 使用GROUP BY 分组a.查询从事每一个工种的员工的工资总和？ SQLSELECT job,SUM(sal) FROM emp GROUP BY job;b.求出从事每一个工种的员工总数？ SQLSELECT job,COUNT(*) FROM emp GROUP BY job;c.查询每个部门的最高工资及工资总和？,句子语法错误!,SQLSELECT deptno,SUM(sal),MAX(sal) FROM emp GROUP by deptno; 按多个条件分组SQLSELECT deptno,job,COUNT(*) FROM emp GROUP BY deptno,job; 使用Having,选则满足条件的组SQLSELECT deptno,SUM(s