ORACLE之SQL基础SQLPLUS基础.docx
《ORACLE之SQL基础SQLPLUS基础.docx》由会员分享，可在线阅读，更多相关《ORACLE之SQL基础SQLPLUS基础.docx（73页珍藏版）》请在冰点文库上搜索。
ORACLE之SQL基础SQLPLUS基础
ORACLE之 SQL基础
SQL*PLUS基础
QQ：
1084931388
SQL与SQL*PLUS命令
●SQL：
在Oracle数据库中创建（表、视图、索引等）、存储、改变（结构、数据）和维护信息；
●SQL命令存放在SQL缓冲区内，保持到下一条SQL命令进入缓冲区，并以“；”（分号）结尾。
●SQL命令有如下特点：
1.简单、易读、功能强大，是Oracle各种开发工具的基础
2.与终端用户进行交互能力强
SQL*Plus概述
●SQL*PLUS工具的作用
▪ORACLE的SQL和PL/SQL的运行环境
▪数据库进行操作和管理
▪输入、编辑、存储、提取和运行SQL命令和PL/SQL程序
▪处理数据、生成报表、存储、打印和格式化查询结果
▪在SQL数据库之间存取和拷贝数据
▪从一个用户端发送消息和接受消息
●与SQL的区别：
①不进入SQL缓冲区
②不用“；”（分号）结尾
●启动SQL*Plus
▪命令行方式启动SQL*PLUS
▪图形界面方式启动SQL*Plus
启动SQL*Plus
●命令行方式启动SQL*PLUS
▪sqlplus[username]/[password][@connect_string]|[NOLOG]
●图形界面方式启动SQL*PLUS
●退出SQL*PLUS：
SQL>exit
父表dept
●SQL>select*fromdept;
●DEPTNODNAMELOC
●------------------------------------
●10ACCOUNTINGNEWYORK
●20RESEARCHDALLAS
●30SALESCHICAGO
●40OPERATIONSBOSTON
Emp子表
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
------------------------------------------------------
7369SMITHCLERK790217-DEC-8080020
7499ALLENSALESMAN769820-FEB-81160030030
7521WARDSALESMAN769822-FEB-81125050030
7566JONESMANAGER783902-APR-81297520
7654MARTINSALESMAN769828-SEP-811250140030
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010
7788SCOTTANALYST756619-APR-87300020
7839KINGPRESIDENT17-NOV-81500010
7844TURNERSALESMAN769808-SEP-811500030
7876ADAMSCLERK778823-MAY-87110020
7900JAMESCLERK769803-DEC-8195030
7902FORDANALYST756603-DEC-81300020
7934MILLERCLERK778223-JAN-82130010
14rowsselected.
SQL*PLUS基础
●SQL命令的组成
数据定义语言（DDL）：
creat（建立）drop（删除）
alter（修改表结构）rename（改名）
数据操纵语言（DML）：
select（查询）insert（插入）
delete（删除）update（更新）
数据控制语言（DCL）：
grant（授权）revoke（回收）
数据检索语言（SELECT）
事务处理控制命令:
包括：
commit（提交）、rollback（回退）savepoint（设置保存点），
练习
●什么是SQL？
SQL有什么功能？
●下列哪个语句可以从数据库中取得数据？
●A.selectB.updateC.insertD.delete
●3.指出SQL数据控制语言（DCL）中的命令。
●4.指出SQL数据操纵语言（DML）中的命令。
●5.指出SQL数据定义语言（DDL）中的命令。
ORACLE的数据类型
●下列代码演示了两者最重要的差别：
●SQL>createtabletester
●2（col1char（10）,col2varchar2（10））;
●SQL>insertintotestervalues（'BRADY','BRADY'）;
●SQL>selectvsize（col1）,vsize（col2）fromtester;VSIZE（COL1）VSIZE（COL2）
●----------------------
●105
ORACLE的数据类型
3.number（w，d）型—数值型
存放数值型数据，w代表总位数（1—38位），d代表小数点后的位数
number1234567.89
number（9）1234568
1234567.89number（9,2）1234567.89
number（9,1）1234567.9
number（7）1234568
number（7,-1）1234570
ORACLE的数据类型
●插入的值超过允许位数时，只要超出的位数在小数点后面，Oracle总是对其四舍五入。
●如果超出的位数在小数点前面，则Oracle返回错误。
●SQL>createtabletester22（col1number（15,2））;
●SQL>insertintotestervalues（1234567891011）;
●1rowcreated.
●SQL>insertintotestervalues（12345678910111）;*ERRORatline1:
●ORA-01438:
valuelargerthanspecifiedprecisionallowsforthiscolumn
●SQL>insertintotester2values（1234567891011.121）
●1rowcreated.
ORACLE的数据类型
4.date型—日期型
●用于存放日期，范围从公元前4712年的1月1日到公元后4712年12月31日。
采用一种用七位数字表示日期时间的Juliandate（儒略日）存储方式。
●儒略日有如下优点：
①可进行日期计算②可进行有效性检查③有利于正确排序④能存储时间
●date型的缺省格式为：
DD-MON-YY（如19-Apr-02）
ORACLE的数据类型
6.多媒体数据类型
⑴lob：
用于存储无组织的大的文本文档、静态图像、音频、视频等多媒体信息，最大长度为4GB。
分为以下几种：
⑵clob：
存储文本、字符等大对象
⑶blob：
存储二进制的大对象，最大长度为4GB
⑷bfile：
存储指向数据库之外，由操作系统管理的巨型对象的指针。
BFILE类型数据的文件，可以存储在硬盘上，也可以在CD或DVD上。
其中：
BLOB、CLOB属于内部LOB类型，存储在数据库的表空间里，一个内部LOB数据的长度如果小于4000字节，存储在表的记录中；超过4000字节，自动移出表外单独存储。
但无论存在何处，都有一个LOB定位器（指向存储LOB数据实际位置的指针）存于表中的LOB列里。
BFILE属于外部LOB类型，BFILE数据是只读的。
●注：
一个表中可以有多个多媒体数据类型，解除了long型的限制；一个表可以在列中存储定位指针，而不存储真的句型对象本身。
练习
1.SALES表中的PROFITS列声明为NUMBER（10，2）。
下列哪个值无法在这个列中存储？
A.5392845.324B.871039453.1
C.75439289.34D.60079829.25
2.员工KING于1981年11月17日招聘，你对Oracle数据库发出下列查询：
selectvsize（hiredate）fromempwhereename=‘KING’。
下列哪个选项是返回的值？
A.4B.7C.9D.17
❿SALES表中将PRODUCT_NAME列定义为CHAR（40）。
后面要在表中增加一行，PRODUCT_NAME值为“CAT_TOYS”。
然后发出下列命令：
selectvsize（product_name）from
sales。
哪个选项是返回的值？
A.8B.12C.40D.4000
ORACLE中的比较运算符
比较运算符含义
=相等
!
=或<>不等于
>大于
>=大于等于
<小于
<=小于等于
in（列表）等于列表中的任意值
between值1and值2大于等于值1并且小于等于值2
like%匹配0个或任意个字符
_匹配一个字符
isnull空值
isnotnull非空值
●NAMElike’J%’ORLIKE’__J%’ORLIKE’%O%O%’
●Pagein（1,2,3）agebetween20and25
ORACLE中的比较运算符
●以下为多值比较运算符：
[not]in：
是否在所选数据集合当中
[not]any：
是否将一个值与子查询返回结果中的一
个值相比较
[not]all：
是否将所选的值与集合中所有值进行比
较
[not]exists：
exists表示一个子查询至少返回一行
时条件成立，notexists表示一个
子查询不返回任何行时条件成立
数据查询
●数据查询基础
●基本查询
●分组查询
●连接查询
●子查询
●合并查询
查询语言——SELECT
★基本形式：
select<查询的列>
from<表>
[where<条件>
[orderby<列名>[asc|desc]];
注：
①from后面的表可以是一个或多个
②WHERE条件限制查询信息，选择指定行
③orderby后面的列名可以是一列，也可以是多列
④asc表示升序排列，为系统缺省值；desc表
示降序排列
select语句：
●SQL>SELECTEMPNO,ENAME,SALFROMEMP
2WHEREENAME='SMITH';
●SQL语句可以在多行中输入。
●Oracle中的关键字不能跨行。
●SQL语句是大小写无关的，下列语句等效于上述语句：
●SQL>selectempno,ename,salfromemp
2whereename='SMITH';
●尽管列名、表名和关键字（如select、from与where）是大小写无关的，但单引号中的文本字符串（如上面的SMITH）是大小写相关的，因为Oracle精确存储输入的文本。
如果在EMP表中存储SMITH时输入大写字母，则Oracle精确存储大写字母。
查询语言——SELECT
●直接将SQL语句输入SQL*Plus时，要用分号（；）结束。
Selectdeptno,dnamefromdept;
●查找多列内容时，用‘，’将各字段分开，显示结果由select子句后各字段排列顺序决定。
基本查询
●无条件查询
●有条件查询
●查询排序
●查询统计
无条件查询
●查询所有列
▪select*fromdept;
●查询指定列
▪selectdeptno,dnamefromdept;
▪selectempno,sal*0.18fromemp;
●使用函数
▪Selectempno,UPPER（ename）fromemp;
无条件查询
●使用算术表达式及字符常量或数字常量
●selectename,sal*12fromemp；
select'3*4='||3*4fromdual;
select'Iam'||'ateacher'fromdual；
运算符优先顺序：
括号、指数、乘与除、加与减
◆伪表DUALselect语句中的列子句使用固定数字时，不需要实际表中的数据，这时可以使用伪表DUAL。
DUAL表只有一个DUMM列，包含一个X值。
执行select*fromDUAL语句，可以看到其中没有存储任何有意义的数据。
这只是个SQL结构，支持from子句中的表指定要求。
SQL>selectuserfromdual;
无条件查询
◆接合列:
将列接合在一起，生成更有趣或更可读的输出，称为接合（concatenation）。
接合运算符就是把两个管道符放在一起：
||，也可以用concat（）运算，传入两个列名。
下例中，ENAME列用两种方法与文本表达式和JOB列接合，得到有意义的结果：
●SQL>selectename||',whoisthe'||
●2concat（job,'forthecompany'）
●3as"NameandRole“
●4fromemp;
●NameandRole
●--------------------------------------------SMITH,whoistheCLERKforthecompany
●ALLEN,whoistheSALESMANforthecompany
●WARD,whoistheSALESMANforthecompany
●JONES,whoistheMANAGERforthecompany
无条件查询
●改变列标题
◆列名或表达式的别名可以在SQL查询输出中增加更有意义的标题名称。
默认情况下，oracle采用select语句中定义的列名，包括函数（如果有）。
但是，这个方法通常无法很好地说明列数据的意义。
oracle截断表达式以适应所返回列数据类型对应的长度，使问题更加严重。
例如：
格式：
SELECT列名别名，…FROM表名；
SQL>selectempno,enamenamefromemp;
EMPNONAME
-------
7369SMITH
7499ALLEN
7698WARD
无条件查询
v当别名中有空格或/字符时，使用双引号括起来
v例：
那些雇员的奖金高于工资的5%
vSELECTENAME,SAL,COMM/SAL“C/SRATIO”
FROMEMP
WHERECOMM>0.05*SAL
ORDERBYCOMM/SALDESC；
v别名仅仅在使用了它的SELECT命令中起作用，而不影响查询结果
无条件查询
●消除重复行
●对具有重复值的列显示惟一值，只要在列子句的列名前面加上distinct关键字，
●例如：
SQL>selectdistinctjobfromemp;
●JOB
●---------
●ANALYST
●CLERK
●MANAGER
●PRESIDENT
●SALESMAN
●提示：
要使用distinct关键字，就要将其放在SQL查询中select关键字的紧后面。
无条件查询
●如果select语句的distinct关键字后面出现多个列名，则Oracle尝试找出这些列中的所有不同组合值。
例如：
●SQL>selectdistinctjob,empnofromemp;
●JOBEMPNO
●------------------
●ANALYST7788
●ANALYST7902
●CLERK7369
●CLERK7876
●CLERK7900
●CLERK7934
●MANAGER7566
●MANAGER7698
●MANAGER7782
●PRESIDENT7839
无条件查询
查询其它用户的表
SQL>selectempno,ename,sal
2fromscott.emp;
查询中引用的表没有冠以结构前缀时，Oracle假设这个表是属于你的结构，。
如果你的结构中没有这个表，则要在表名前面冠以结构信息，将表拥有者和表名本身用点号分开。
对列加表名前缀，用点号（.）分开表名和列名。
SELECTtable_name.column_name,table_name.column_name
FROMschema.table_name;
有条件查询
●查询满足条件的元组可以通过WHERE子句实现。
●WHERE条件中常用的运算符号
有条件查询
●关系运算
▪selectempno,ename,salfromempwheredeptno=10;
▪Selectempno,ename,salfromempwheresal>1500
●确定范围
▪谓词BETWEENAND与NOTBETWEENAND。
▪selectempno,ename,sal,deptnofromempwheredeptnobetween10and20
▪selectempno,ename,sal,deptnofromempwheresalnotbetween1000and2000
有条件查询
●确定集合
▪谓词IN可以用来查找属性值属于指定集合的元组。
▪selectempno,ename,sal,deptnofromempwheredeptnoin（10,30）;
●字符匹配
▪%（百分号）代表任意长（长度为0）字符串。
▪_（下划线）代表任意单个字符。
▪selectempno,ename,sal,deptnofromempwhereenamelike'S%';
•selectename，salfromemp
whereenamelike‘J_’;
有条件查询
●空值操作
▪涉及空值查询时使用ISNULL或ISNOTNULL，这里的IS不能用=替代。
▪selectempno,ename,sal,deptnofromempwherecommisnotnull
▪selectempno,ename,sal,deptnofromempwherecommisnull
有条件查询
●逻辑操作
▪用逻辑运算符NOT、AND和OR来联结多个查询条件。
▪优先级：
NOT、AND、OR（用户可以用括号改变优先级）。
▪IN谓词实际上是多个OR运算的缩写。
▪selectempno,ename,sal,deptnofromempwherecommisnotnullandsal>1500;
▪selectename,job,salfromemp
where（job='SALESMAN'orjob=‘CLERK'）andsal>1500;
查询排序
●升序、降序排序
●多列排序
●表达式排序
●使用别名排序
●使用列位置编号排序
查询排序
●升序、降序排序
▪ASC:
升序（缺省）；DESC:
降序
▪SELECTempno,ename,salFROMempORDERBYsal;
▪SELECTempno,ename,salFROMempORDERBYsalDESC;
●多列排序
▪首先按照第一个列或表达式进行排序；当第一个列或表达式的数据相同时，以第二个列或表达式进行排序，以此类推。
▪SELECT*FROMempORDERBYdeptno,salDESC;
查询排序
●按表达式排序
▪可以按特定的表达式进行排序。
▪SELECTempno,ename,salFROMempORDERBYsal*12;
●使用别名排序
▪可以使用目标列或表达式的别名进行排序。
：
▪SELECTempno,sal*12salaryFROMempORDERBYsalary;
●使用列位置编号排序
▪如果列名或表达式名称很长，那么使用位置排序可以缩短排序语句的长度。
▪SELECTempno,sal*12salaryFROMempORDERBY2;
练习
●列出所有在部门20工作的雇员姓名，且列名为name
●列出名字不以‘N’开头的工作地点
●找出工资比奖金高的雇员，按工资降序排列，姓名升序排列
●列出有关雇员姓名、奖金收入百分比的信息，按
雇员姓名排序，不包括奖金未知的雇员
选择：
1.你用SQL*Plus执行一些数学函数。
对select语句中定义的值（而不是从表列中取得的值）进行数学运算时，可以用哪个表？
A.EMPB.包含列值的表C.DUALD.Oracle定义的表
练习
2.PLAYS表有两列PLAY＿NAME与AUTHOR，下面是该表的输出，它们是用哪个SQL语句产生的？
PLAY_TABLE
-------------------------------------"MidsummerNightsDream",SHAKESPEARE
"WaitingForGodot",BECKETT
"TheGlassMenagerie",WILLIAMS
A.selectPLAY_NAME||AUTHOR
升级会员 