1 / 27

SQL 语言与 SQL*PLUS

SQL 语言与 SQL*PLUS. O racle9i 数据库 语言. SQL 命令. SQL 关键字 （ 以下命令为 SQL 命令，以“；”结束） Alter 、 Audit 、 Commit 、 Comment 、 Create 、 Delete Drop 、 Grant 、 Insert 、 Lock 、 NoAudit 、 Rename Revoke 、 Rollback 、 Select 、 Update 、 Validate 。. SQL*PLUS 命令.

maili
Download Presentation

SQL 语言与 SQL*PLUS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL语言与SQL*PLUS Oracle9i 数据库语言

  2. SQL命令 • SQL关键字（以下命令为SQL命令，以“；”结束）Alter、 Audit、Commit、Comment、Create、Delete Drop、Grant、Insert、Lock、NoAudit 、RenameRevoke、Rollback、Select 、Update 、Validate。 tengyc@263.net

  3. SQL*PLUS命令 • SQL*PLUS关键字（以下命令以“CR”结束）@、 #、/、 Accept、 Append、 Break、 Btitle、 Change 、 Clear、 Column、 Compute、 Connect、 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、 tengyc@263.net

  4. 编辑和运行SQL缓冲区的SQL*PLUS命令 • 命令 缩写 解释APPEND text A text 行尾增加 textCHANGE C/old/new 在当前行中将old换为/new CHANGE C/text/ 在当前行中删除 textCLEAR Buffer CL Buff 清除缓冲区 • DEL DEL 删除当前行INPUT I 增加一行INPUT text I text 增加有text组成的行LINE L 显示缓冲区内容LINE n L n 显示第n 行RUN R 运行SQL缓冲区命令 tengyc@263.net

  5. Oracle Quiz • 《QL 测 试》Oracle • Assessment Tests tengyc@263.net

  6. 《Oracle Assessment Tests》 • You query the database with this command: SELECT name FROM employee WHERE name LIKE ‘_a%’;Which names are displayed?A. names starting with ‘a’B. names starting with ‘a’ or ‘A’C. names containing ‘a’ as the second letterD. names containing ‘a’ as any letter except the first tengyc@263.net

  7. 《Oracle Assessment Tests》 • For which task would it be mostappropriate to use the keyword DISTINCT?A. Identify duplicate rows in a table.B. Identify which column has unique data.C. Eliminate duplicate columns in a table.D. Eliminate duplicate rows in the result set. tengyc@263.net

  8. 《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 displayed for the SALARY columnwhen a NULL value is returned?A. 0 B. NULL C.spaces D.nothing tengyc@263.net

  9. 《Oracle Assessment Tests》 • 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 in a specified list. • (Answers: C D A B) tengyc@263.net

  10. Oracle Quiz • 《DML,DCL 测 试》Oracle • Assessment Tests tengyc@263.net

  11. 《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 table will be updated.D. The statement will execute,but no change will be made. tengyc@263.net

  12. 《Oracle Assessment Tests》 • Which commands cause a transaction to end?A. ALTERB. GRANT.C. DELETE.D. INSERTE. UPDATE.F. ROLLBACK tengyc@263.net

  13. 《Oracle Assessment Tests》 • The Correct Answers • 1.C2.A B F tengyc@263.net

  14. Oracle Quiz • 《DDL 测 试》Oracle • Assessment Tests tengyc@263.net

  15. 《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 you have privileges to access. tengyc@263.net

  16. 《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 access.D. only the names of all the tables you can access. tengyc@263.net

  17. 《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. CONSTRAINT alpha_3000_id_nn NOT NULL); What line in the statement will cause an error?A. 1 B. 2 C.3 D.7 tengyc@263.net

  18. 《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 constraint 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. tengyc@263.net

  19. 《Oracle Assessment Tests》 • 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 last_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” tengyc@263.net

  20. 《Oracle Assessment Tests》 • You alter the database with this command: RENAME streets to CITY; Which task 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. tengyc@263.net

  21. 《Oracle Assessment Tests》 • The Correct Answers • B,D,B,D,B,B tengyc@263.net

  22. 使用 SQL*PLUS命令 • SQL*PLUS是Oracle对SQL语言的扩充,是一中交互式的表报生成工具.它使用SQL命令从Oracle中查询信息,用SQL*PLUS命令设置控制表报的输出格式,对SQL命令加以扩充,可以对题目,列标题,以及汇总信息进行控制. • 定义表头与表尾定义表头:SQL>Ttitle <option> 描述定义表尾:SQL>Btitle <option> 描述 option: Left 左对齐Center 居中Right 右对齐使用TITLE 则报表输出在每页加上日期及页号 tengyc@263.net

  23. SQL*PLUS命令总结 tengyc@263.net

  24. 聚组函数的使用 • 聚组函数是从一组中返回汇总信息，聚组函数有SUM,COUNTCOUNT DISTINCT,MAX,MIN,AVG,STDDEV • 计算max,min,avg,suma.SQL>SELECT MIN(sal),MAX(sal),AVG(sal), SUM(sal) FROM emp;b.找出具有最高工资的员工？SQL>SELECT ename,job,sal FROM emp WHERE sal=(SELECT MAX(sal) FROM emp); • COUNT与COUNT DISTINCTa.求emp表中总行数？SQL>SELECT COUNT(*) FROM emp;b.求job的行数？ SQL>SELECT COUNT(job) FROM emp;c.求有多少工中？SQL>SELECT COUNT(DISTINCT job) FROM emp; tengyc@263.net

  25. SELECT 字句中目标的一致性不能把单行函数与聚组函数混在一起使用，如SQL>SELECT ename,SUM(sal) FROM emp;单行目标 聚组函数 • 使用GROUP BY 分组a.查询从事每一个工种的员工的工资总和？SQL>SELECT job,SUM(sal) FROM empGROUP BY job;b.求出从事每一个工种的员工总数？SQL>SELECT job,COUNT(*) FROM empGROUP BY job;c.查询每个部门的最高工资及工资总和？ 句子语法错误! tengyc@263.net

  26. SQL>SELECT deptno,SUM(sal),MAX(sal) FROM emp GROUP by deptno; • 按多个条件分组SQL>SELECT deptno,job,COUNT(*) FROM empGROUP BY deptno,job; • 使用Having,选则满足条件的组SQL>SELECT deptno,SUM(sal) FROM emp GROUP BY deptnoHAVING SUM(SAL)>8000; tengyc@263.net

  27. 查询语句语法总结 • SQL>select 列1,列2,…from 基表where 条件表达式group by 分组列,分组列２,…having 分组条件表达式order by 排序列1,排序列2,… ;求,除去秘书CLERK职业,那些部门工资总和超过$3000?SQL>select deptno,sum(Sal) from emp where job !=‘CLERK’ group by deptno having sum(sal)>3000 order by sum(sal); tengyc@263.net

More Related