#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 7902
MILLER 7934
SQL> select ename name,empno as "id"
2 from emp
3 where empno>7900;
NAME id
---------- ----------
FORD 7902
MILLER 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.