#Oracle9i SQL语句的类型 —数据查询语句(SELECT 语句) —数据定义语句（DDL 语句）包括数据对象的建立、修改、删除等。 —数据操作语句（DML）包括数据的insert、update、delete等。 —数据控制语句（DCL语句）用户的权限授予、实现数据安全性。 —事务控制语句（PCL语句）commit、rollback等。 #基本select语句的书写 Objectives #After completing this lesson,you should be able to do the following: -List the capabilities of SQL select statements -Execute a basic select statement -Differentiate between SQL statments and iSQL*Plus commands #Capabilities of SQL select statements
-select 语句可以基于某一个数据对象的如只列出某个表的某些列或某些行我们也能通 过join连接访问多个表中的数据。 例:比如用户scott（密码tiger）登陆有emp表(可以用select * from user_tables得到 当前用户所包含的表) ·查询emp表某些列(column) select empno,ename from emp; 得到了emp表中empno,ename两列的信息 ·查询emp表中的某些行（row） select * from emp where empno>7850;得到了emp表中所有empno>7850行的信息 ·查询多表中的信息 select t1.empno,t1.ename,t2.fname,t2.lname from emp t1,store t2 where t1.ename=t2.lname;得到两表中 t1.ename=t2.lname的信息
#Basic select statement -select *|{[distinct] column|expression [alias],...} from table; ·select identifies what columns; ·from identifies which table; -Selecting All Columns ·select * from emp; '*'号表示当前数据对象的所有列（Column）,意思就是说'*'是在这是简写那么它对 应到的就是当前数据对象的所有Column; -Selecting Specific Columns ·select empno,ename from emp; 在这条语句中我们能看到指定栏位名（列名，Columns）的方式限定我们需要查 找的这些栏位信息; 注意：栏位名之间有(,)号分隔(;)号作为语句的结束符; Writing SQl Statements ·SQL statements are not case sensitive.不敏感大小写 ·SQL statements can be on one of more lines.可在一行或多行写 ·Keywords cannots be abbreviated or split across lines.关键字不能跨行或缩写 ·Clause are usually placed on separate lines.子句(where等)通常另开一行写 ·Indents are used to enhance readability.缩行可提高语句的可读性 #Column Heading Dafaults ·iSQL*Plus: -Default heading justification:Center 缺省栏位居中 -Default heading display:Uppercase 缺省显示栏位大写 ·SQL*Plus: -Character and Date column headings are left-justified 字符和日期类型的栏位头居左 -Number column heading are right-justified 数字类型栏位头居右 -Default heading display:Uppercase 缺省显示栏位大写 #Arithmetic Expressions 算术运算符 ·Create expressions with number and date data by using arithmetic operators --------|------------ Operator| Description --------|------------ + | Add --------|------------ - | Subtract --------|------------ * | Multiply --------|------------ / | Divide --------|------------ -Using Arithmetic Expressions
例:SQL> Select empno,ename,sal+300 2 from emp; EMPNO ENAME SAL+300 ---------- ---------- ---------- 7369 SMITH 1100 7499 ALLEN 1900 7521 WARD 1550 7566 JONES 3275 7654 MARTIN 1550 7698 BLAKE 3150 7782 CLARK 2750 7788 SCOTT 3300 7839 KING 5300 7844 TURNER 1800 7876 ADAMS 1400 EMPNO ENAME SAL+300 ---------- ---------- ---------- 7900 JAMES 1250 7902 FORD 3300 7934 MILLER 1600 已选择14行。 SQL> ·Operator Precedence 运算符的优先级 ---------------------- * / + - ---------------------- -Multiplication and division take priority over addition and subtraction . -Opertors of the same priority are evaluated from left to right. -Parentheses are used to force prioritized evaluated and to clarify statements. 与我们日常的优先级一样,也可以用括弧'()'改变优先级 -Opertor Precedence例:SQL> run 1 Select empno,ename,sal+300*3 2 from emp
EMPNO ENAME SAL+300*3---------- ---------- ---------- 7369 SMITH 1700 7499 ALLEN 2500 7521 WARD 2150 7566 JONES 3875 7654 MARTIN 2150 7698 BLAKE 3750 7782 CLARK 3350 7788 SCOTT 3900 7839 KING 5900 7844 TURNER 2400 7876 ADAMS 2000
EMPNO ENAME SAL+300*3---------- ---------- ---------- 7900 JAMES 1850 7902 FORD 3900 7934 MILLER 2200
已选择14行。例:用括弧'()'改变优先级SQL> Select empno,ename,(sal+300)*3 2 from emp;
EMPNO ENAME (SAL+300)*3---------- ---------- ----------- 7369 SMITH 3300 7499 ALLEN 5700 7521 WARD 4650 7566 JONES 9825 7654 MARTIN 4650 7698 BLAKE 9450 7782 CLARK 8250 7788 SCOTT 9900 7839 KING 15900 7844 TURNER 5400 7876 ADAMS 4200
EMPNO ENAME (SAL+300)*3---------- ---------- ----------- 7900 JAMES 3750 7902 FORD 9900 7934 MILLER 4800
已选择14行。 #Defining a Null Value Null值的定义 -A null is a value that is unavailable,unassigned, Null是一个无效的、不被指定的、未 unknown,or inapplicable.知的或是不确定的状态 -A null is not the same as zero or a blank space.及不等于零也不是一个空格 在数据库环境里面null只是说一种状态、没有给它指定一个确切的值，是一种不确定的状态。 -Null Vlaues in Arithmetic Expresstions Arithmetic expresstions containing a null value evaluate to null 算术表达式中如果包含任何一个null值,它的结果也为null 例:SQL> select empno,ename,null*sal+99 from emp 2 where empno=7369;
EMPNO ENAME NULL*SAL+99---------- ---------- ----------- 7369 SMITH
#Defining a Column Alias 栏位别名的定义 -A column Alias ·Renames a column heading ·Is useful with calculations ·Immediately follows the column name-there can also be the optional as keyword between the column name and alias 一种是在栏位名后紧跟着定义别名一种是栏位名后加as再加别名 ·Requires double quotation marks if it contains spaces or special character or is case sensitive.保持别名的大小写时用双引号鉴定例:SQL> select ename name,empno as id from emp 2 where empno>7900;
NAME ID---------- ----------FORD 7902MILLER 7934SQL> select ename name,empno as "id" 2 from emp 3 where empno>7900;
NAME id---------- ----------FORD 7902MILLER 7934 #Concatenation Operator连接处理 -A Concatenation Operator: ·Concatenates columns or character strings to other columns ·Is repressented by two vertical bars(||) ·Creates a resultant column that is a character experssion oracle在实现字符连接时使用的(||)不同与SQL Server用(+).例:SQL> select empno||' is '||ename from emp 2 where empno<7400;
EMPNO||'IS'||ENAME--------------------------------------------7369 is SMITH #Literal Character Strings - A Literal is a charcter,a number,or a date included in the selece list - Date and character literal values must be enclosed within single quotation marks - Each character string is output once for each row returned Literal用的单引号进行鉴定,如果Literal中包含一个单引号,要在加一个单引号进行转定义.例:SQL> select empno,'''is name ',ename from emp 2 where empno>7900;
EMPNO '''ISNAME ENAME---------- --------- ---------- 7902 'is name FORD 7934 'is name MILLER
#Duplicate Rows 重复行 -The default display of queries is all rowsm,including duplicate rows. Oracle在默认显示所有的行,包括重复行. #Eliminate Duplicate Rows除去重复行 -Eliminate duplicate rows by using the DISTINCT keyword in the select clause例:SQL> select distinct deptno from emp;
DEPTNO---------- 10 20 30
# SQL and iSQL*Plus Interaction - SQL statements Versus iSQL*Plus Commands SQL iSQL*Plus ·A language | ·An enviroment ·ANSI standard | ·Oracle proprietary ·Keyword cannot be | ·Keywords can be abbreviated | addreviated ·Statements manipu-| ·Commands do not allow late data and tab- | manipution of values in le definitions in | the database the database | ·Runs on a browser | ·Centrally loaded,does not | have to be implemented on each machine
-Logging in to iSQL*Plus ·open a browser ·URL 'http://localhost/isqlplus' 注意:如果键入URL后 发现地址未找到 应查看OracleOraHome92HTTPServer服务是否启用. #Display Table Structure 显示表的结构 -Use the iSQL*Plus DESCRIDE command to display the structure of a table 例:SQL> desc emp; 名称 是否为空? 类型 ----------------------------------------------------- -------- -------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) iSQL*Plus 可以保存脚本和加载运行脚本
Summary in this lession ,you should have learned how to : -Write a select statement that: ·Returns all rows and columns from a table ·Returns specified columns from a table ·Uses column aliases to give desciptive column heading -Use the iSQL*Plus enviroment to write,save,and execute SQL statements and iSQL*Plus commands.
