SQL/PLUS
来源:互联网 发布:ubuntu一键安装lamp 编辑:程序博客网 时间:2024/05/05 15:09
SQL/PLUS
分四类:
DQL :select
DDL :create,alert,drop,rename,truncate
DML :insert,update,delete
DCL :grant(授权),revoke(收回)
逻辑事务管理:commit,rollback,save-point
使用SQL*PLUS
编辑命令:
A[PPEND] text;
C[HANGE] /old/new;
C[HANGE] /text/;
CL[EAR] BUFF[ER];
DEL;DEL n; DEL m n;
I[NPUT] 插入不定数量的行;
I[NPUT] text;
L[IST] 列出缓冲区中;
L[IST] m n 列出从m到n;
R[UN];
N 制定该行为当前行;
n text :用text代替n行;
O text :第一行前插入
文件命令:
SAV[E] filename [.ext][REPLACE] APP[END]
GET filename [.ext]
STA[RT] filename[.ext]
@filename
ED[IT] [filename [.ext]]
SPO[OL] [filename [.ext]] |OFF|OUT 将查询结果保存到文件中,OFF关闭输出文件,OUT关闭并打印
EXIT 退出SQL*PLUS
数据类型
字符:CHAR(n),VARCHAR(n),LONG 最大2G
数字:NUMBER(m,n)
日期:DD-MON-YY
大对象:
内部 LOB:BLOB,CLOB,NCLOB
BLOB:二进制数据,如图像,影像
CBLOB:单字节字符数据,如Microsoft Word文件
NCLOB:多字节国家字符数据,如Microsoft Word文件
外部LOB:
Bfile:存放在操作系统文件中,只读,Oracle数据库不能创建,二进制
RAW(n):可变二进制数据。n:1-2000
LONG RAW:可变二进制数据。最大长度2G。同一表中不能同时有LONG和 LONG RAW存在。
运算符
算术及单行运算符:+-,+,-,*,/,=,!=,<>,<,>,<=,>=
多行及特殊运算符:any,some,all,[NOT] BETWEEN m AND n,[NOT] EXISTS,m [NOT] LIKE n,IS [NOT] NULL,IN
逻辑运算符: NOT,AND,OR
DQL
语法:
SELECT [DITINCT] *|tablename-alias.column [alias],...]
FROM tablename [alias],...
WHERE [NOT] expression operater expression [AND|OR...]
ORDER BY column|expression [ASC|DESC],column|expression [ASC|DESC]
column:列名
alias:别名
expression:可为列名,别名,数值或表达式,字符和日期必须使用单引号引起且区分大小写
ASC:升值序
DESC:降值序
规则:
●无大小写区分
●可以多行形式输入
●关键字不能跨行
●关键字对齐
●可使用TAB和伸缩符号
●ORDER字句中可以使用数字序号代替列名称
执行:
●句后加(;)
●句后加(/)
●SQL提示符后加(/)
●SQL提示符后运行RUN
算术表达式:对所选的数据可以进行数学运算并给出别名,如遇空值,运算结果只能是空值。
改变列标题:使用关键字AS,也可省略。
连接字符串:(| |)
连接查询:从不同的表中获得查询信息。使用表名限定列名,如果FROM子句中定义了表的别名,则整个SELECT语句中使用该表名的时候都得使用该别名。连接查询可分为内外连接
查询。内连接有等价与非等价连接。当直性子连接时,必须为表指定两个别名。
合计计算:求最大值,平均值,合计等。
合计函数:AVG(DISTINCT|ALL|expression)
COUNT(*|DISTINCT|ALL|expression)
MAX(DISTINCT|ALL|expression)
MIN(DISTINCT|ALL|expression)
STDDEV((DISTINCT|ALL|x):标准偏差
SUM(DISTINCT|ALL|expression)
VARIANCE(DISTINCT|ALL|expression):方差
除COUNT(*)外都忽略空值
分组计算:按指定烈进行分组,然后使用合计函数
语法:
SELECT columnname [alias],group-function(column)
FROM tablename [alias],...
WHERE [NOT] expression operater expression [AND|OR…]
GROUP BY columnname
HAVING expression operater expression [AND|OR…]
ORDER BY columnname|expression [ASC|DESC],columnname|expression [ASC|DESC],…
规则:
●SELECT子句后只能有两种表达式,一种是合计函数,一种是出现在ORDER BY子句后的列名。
●如果使用WHERE子句,所有参加分组计算的数据必须首先满足WHERE子句的条件。
●系统按照ORDER BY子句中指定的列的升序排列。
●如按多列分组,先分大足,后分小组。
常见错误:
●如果在SELECT语句中没有出现ORDER BY子句,不能在SELECT子句中同时出现单个列名和合计函数的混合现象。
●如果希望限制分组中的数据,只能使用HAVING而不能使用WHERE子句。
子查询:SELECT语句中使用SELECT语句。子查询又被称为内查询。包含子查询的语句被称为外查询。
规则:
●子查询必须用括号括起来
●子查询只能出现在WHERE或HANVING子句中的比较运算的右边
●不能在子查询语句中包含ORDER BY子句,外查询语句中可以
●子查询允许嵌套多层
●子查询中可以使用单行或多行运算符
种类:
●单行子查询:返回一个常数,可运用单行运算符
●多行子查询:返回多行单列(一系列值),必须运用多行运算符
IN ANY ALL
<ANY:小于最大值
=ANY:与IN相等
>ANY:大于最小值
●多列子查询:返回多行多列
语法:SELECT columnname,columnnanme,…
FROM tablename
WHERE (columnname,columnname,…)
IN (
SELECT columnname,columnname,…
FROM tablename
WHERE condition)
SELECT语句的结合:
●UNION 返回两个查询结果,无重复
●UNION ALL 返回两个查询结果,可有重复
●INTERSECTION 返回两个查询的相同部分
●MINUS 返回前查询部分减去后查询部分
SELECT语句中的函数
语法:
function-name (columnname|expression,[arg1,arg2,…])
类型:
1.字符串函数
LOWER(columnname|expression)
UPPER(columnname|expression)
INITCAP(columnname|expression)
CONCAT(columnname1|expression1, columnname2|expression2)
SUBSTR(columnname|expression,m[,n])返回子字符串起始位置m,长度是n
LENGTH(columnname|expression)
INSTR(columnname|expression,c)返回指定字符c的位置
LPAD(columnname|expression,n,’填充’):返回n长度对齐字符串,如长度
不足n,则以‘填充’补齐。
2.数字函数
ROUND(columnname|expression[,n]):返回小数点后n位的四舍五入后的
值,如n为负值,则小数点前n位为0。
TRUNC(columnname|expression[,n]):与ROUND相似,只是不进行四舍五
入运算。
MOD(m,n):返回m除以n的余数。
3.日期函数
Oracle系统默认的日期格式DD-MON-YY
日期的算术运算:日期是作为数据储存的,可以执行算术运算。
●日期+数字在日期上添加指定的日数
●日期+数字在日期上减除指定的日数
●日期+日期两个日期间的日数
●日期+数字/24 在日期上添加指定数量的小时数
SYSDATE 系统当前的日期
MONTHS_BETWEEN(date1,date2) 两个日期间的月数
ADD_MONTHS(date,n) 在指定日期上添加n个月份
NEXT_DAY 当前系统日期的第二天
NEXT_DATE 指定日期下周周(n-1)的日期
LAST_DAY 本月的最后一天
ROUND(date,’YEAR|MONTH|…’) 按指定的格式返回四舍五入后的日期。如果个实施
4.数据类型转换函数
●隐式转换
赋值时可进行的隐式转换有
VARCHAR2或CHAR —〉NUMBER
VARCHAR2或CHAR —〉DATE
NUMBER —〉VARCHAR2
DATE —〉VARCHAR2
表达式中可进行的隐式转换有
VARCHAR2或CHAR —〉NUMBER
VARCHAR2或CHAR —〉DATE
●显式转换
TO_CHAR(number|date[,’fm___’])按格式把数字或日期转换成VARCHAR2
TO_NUMBER(char)将数字字符串转化成数字
TO_DATE(char[,’fm___’])按格式把字符串转换成日期
5.常规函数
●NVL(expression1,expression2):如果expression1中有空值存在将它转化成
expression2的值。
●DECODE(columnname|expression,search1,result1[,search2,result2,…][,default])
如果expression的值等于search1,则显示result1,否则继续向下查找。如果所有的
search都不满足,显示default值。
6.替换变量
●&:如果在变量前使用&符号,在执行SELECT语句时,系统会提示用户提供该变量的
值规则:
1)替换变量的类型是字符,必须用引号引起来
2)系统会显示替换变量被替换的过程
●&&:如果需要重复使用某替换变量的值而不要求用户多次键入,可以在它第一次出现
时使用&&字符。
●DEFINE:DEFINE variable*value:创建一个CHAR类型变量并给予初始值
DEFINE variable:显示变量的名称,值和类型
DEFINE:显示所有用户定义的变量
UNDEFINE variable:消除变量定义
●ACCEPT:ACCEPT variable [datatype][FORMAT format][PROMPT text][HIDE]
按指定格式创建制定类型的变量,并制定用户提示,提示用户输入制定的数
据,并可隐藏用户输入。
7.制定SQL*PLUS环境
SET命令,SHOW命令(SHOW ALL)
DML
包括INSERT,UPDATE,DELETE。一组DML语句形成的逻辑单元被称为事务。事物的特点是其中的操作要么全部成功,要么全部失败。
1. INSERT:两种用法:一是通过关键字VALUES直接输入值;另一种是将SELECT语句的结果插入表中。
●直接提供值:
INSERT INTO tablename (column[,column,…])
VALUES(value[,value,…])
规则:
1)一次只能插入一行数据
2)如table名后无列名,则按照表定义的列的顺序插入数据
3)如某列允许空值,可不提供其数值
4)可以使用系统提供的函数。如SYSDATE
●间接提供值:
INSERT INTO tablename (column[,column,…])
AS SELECT column[,column,…]
FROM tablename
WHERE codition
2. UPDATE:
UPDATE table
SET column=value[,column=value]
WHERE condition
3. DELECT:
DELECT FROM table
WHERE condition
如果没指定WHERE子句,表中所有数据被删除。
4. 事务管理:
连续的执行COMMIT或ROLLBACK之间的操作成为一个事务。对事务的控制包含事务提交,事务回退(或撤销)及设立检查点。Oracle规定,提交后的更新操作是不能撤销的。
●事务提交:3种方式
1〉显示提交:COMMIT命令
2〉隐式提交:命令如ALERT,AUDIT,COMMENT,CONNECT,CREATE,
DISCOUNT,DROP,EXIT,GRANT,NOAUDIT,REVOKE,RENAME,
以及退出SQL*PLUS都隐含COMMIT操作。
2〉自动提交:用SET命令设置自动提交环境
●事务回滚:尚未提交的事务,可以使用ROLLBACK命令撤销。
ROLLBACK TO SAVEPOINT pointname
EXP:Savepoint sp1;
Savepoint sp2;
ROLLBACK to Savepoint pointname
DDL
包括CREATE,ALERT,DROP,RENAME,TRUNCATE
●表
1.创建:CREATE TABLE table(
Column 数据类型,
Column 数据类型,
…);
或 CREATE TABLE table
AS SELCT column [,column,…]
FROM table[,table,…]
WHERE condition
2.修改:增加或删除列,更改列的默认值。但不能队列进行重命名。
语法:ALERT TABLE table
ADD|DROP column 类型[DEFAULT value]
规则:
1〉 当增加超过一列时,用括号把要增加的列括起来。
2〉 当数据类型别修改时,列值必须为空。
3〉 删除列时,如果该列有索引或其他约束,必须使用附加功能CASCADE CONSTRAINS。
4〉 可以先将某列设为UNUSED,然后再将其删除。
语法:ALERT TABLE table
SET UNUSED COLUMN column
3.重命名:对一个表进行重命名后,Oracle将自动更新相应的约束,索引和与此表相关的权限。但相应的视图,同义词,存储过程和函数为非法。
语法:RENAME table TO new_table
4.删除:表删除后,所有数据和词表的定义都被删除,标的索引,约束,触发器和权被删除。但Oracle并不删除试图,只是标示他们非法。
PL/SQL:
三部分组成:定义部分,可执行部分和例外处理部分。三部分共同形成模块。有人称PL/SQL是模块化的过程SQL。
1. 定义部分:定义在执行部分所使用的变量,常量,游标和用户自定义的例外处理。
语法:DECLARE
变量名[CONSTANT]数据类型[NOT NULL][:=值] DEFAULT值|sql expression
规则:
●以DECLEAR开头
●直接赋值可用:=expression
●间接赋值可用SELECT INTO或PETCH INTO进行
SELECT 值[,值,…] INTO 变量名[,变量名,…]
FROM table
标量类型:
●BINARY_INTEGER:-214783647~+214783647
子类:NATURAL,NATRUALN,POSITIVE,POSITIVEN,SIGNTYPE
●NUMBER:1.0E-130~9.99E125
子类:DEC,DECMAL,DOUBLE,PRECISION,FLOAT,INTEGER,INT,NUMBERIC,REAL,SMALLINT
●字符类型:CHAR:最大32767字节,定长
VARCHAR2:最大32767字节,变长
LONG:最大2G字节,变长
●日期类型:DD-MONTH-YY
●布尔类型:BOOLEAN
●大数据类型:BFILE,BLOB,CLOB,NCLOB
2.执行部分:包括对数据库进行操作的SQL语句以及对语句进行组织,控制PL/SQL语句。
语法:BEGIN
END;
●控制结构:
1)IF条件语句语法:
IF Boolean expression THEN
PL/SQL语句
ELSE
PL/SQL语句
END IF;
规则:可以嵌入多个IF…THEN…ELSEIF语句形成条件语句链
2)CASE语句语法:
[<lable>]
CASE selector
WHEN expression 1 THEN statement 1;
WHEN expression 2 THEN statement 2;
……….
[ELSE Statement];
END CASE[lable_name];
搜索CASE语句:没有选择器,根据布尔搜索条件执行。
[<lable>]
CASE
WHEN search_condition1 THEN statement1;
WHEN search_condition2 THEN statement2;
……….
[ELSE statement];
END CASE [lable];
3)LOOP循环
简单循环:无限循环
LOOP
Sequence_of_statements
END LOOP;
可用EXIT或EXIT [lable] WHEN condition来退出循环。
4)WHILE LOOP循环
语法:
WHILE condition LOOP
Sequence_of_statements
END LOOP;
5)FOR循环
语法:
FOR counter IN [REVERSE] lower_bound…higher_bound LOOP
Sequence_of_statements
END LOOP;
6)GOTO和NULL语句
语法:GOTO lable
NULL语句:不做任何操作,只提高可读性
●DMS_OUTPUT.PUT_LINE程序:用于SQL*PLUS下显示运算结束
SQL>SET SERVEROUTPUT ON
之后可用DBMS_OUTPUT.PUT_LINE程序将结束显示在屏幕上。
●过程(PROCEDURE)与函数(FUNCTION)
过程,函数,触发器和包都是带名的PL/SQL块,也可将过程,函数成为子程序。过程和函数类似,过程是为了执行一定任务而组合在一起的SQL或PL/SQL语句集,而函数的最终任务是返回一个值。函数的调用是表达式的一部分,而过程的条用本身就是一条PL/SQL语句。
创建过程:
CREATE [OR REPLACE] PROCEDURE procedurename
[param1{IN|OUT|IN OUT}] param_type
………
paramN [{IN|OUT|IN OUT}] paramN_type]
{IS|AS} procdure_body;
调用:
Procedure_name(param1=>值1,param2=>值2,…);
创建函数:
CREATE [OR REPLACE] FUNCTION functionname
[Param1 [{IN|OUT|INOUT}] param1_type,
……
ParamN [{IN|OUT|INOUT}] paramN_type]
RETURN returntype {IS|AS} function_body
函数的调用:与过程相似但需考虑函数的返回值。
●本地子程序的调用:在调用本地子程序前必须先声明该子程序,但在声明部分必须将子程序的声明放在最后。Oracle支持先声明后定义的模式。
●游标:
定义:CURSOR cursor_name IS select_statement
打开:即执行定义中的SELECT语句
OPEN cursor_name;
取值:对游标第一次执行FETCH语句时首先将指针指到第一行(成为当前行),然后将该行的数据输出
FETCH cursor_name INTO variable [,variable,……];
关闭:游标关闭后,系统自动释放资源。
属性:%FOUND,%NOTFOUND,%ROWCOUNT和%OPEN李
例:
条件:
IF up_sal >= 2000 THEN
RAISE out_range;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO TEMP VALUES(NULL,NULL,’employee not found’);
WHEN out_range THEN (自定义陷阱)
INSERT INTO TEMP VALUES(NULL,NULL,’out of range SAL’);
WHEN OTHERS THEN
INSERT INTO TEMP VALUES(sql_code,NULL,sql_errm);
(系统错误, 错误内容)
COMMIT;
游标:
DECLARE CURSOR CURselline IS (定义游标)
SELECT ENAME,SAL+NVL(COMM,0) SALS
FROM INTO TEMP VALUES(selrec.SALS,null,selrec.ENMAE);
游标取值方法1:
OPEN CURselline;
FOR selrec in CURselline LOOP
INSERT INTO TEMP VALUES(selrec.SALS,null,selrec.ENMAE);
COMMIT;
END LOOP;
CLOSE CURselline;
游标取值方法2:
LISTselrec CURselline%ROWTYPE; (定义与游标相同结构的结构行)
OPEN CURselline; (打开游标)
LOOP
FETCH CURselline INTO LISTselrec;(循环下一条,并把数据传给结构行)
EXIT WHEN CURselline%NOTFOUND;(判断游标是否到尾)
INSERT INTO TEMP VALUES(LISTselrec.SALS,NULL,LISTselrec.ENMAE);
COMMIT;
END LOOP;
CLOSE CURselline;(关闭游标,释放资源)
方法:
CREATE OR REOLACE FUNCTION 方法名 (建立方法)
(
参数1 类型,
参数2 类型…...
)
RETURN 返回值类型
IS
[变数定义]
BEGIN
……….
EXCEPTION
例外处理部
END;
ans NUMBER(1):=0;
FOR cnt IN 1..10 LOOP
ans:=ans+cnt; (给变量赋值)
END LOOP;
WHILE cnt<=10 LOOP
ans:=ans+cnt;
cnt:=cnt+1;
END LOOP;
CREATE OR REPLACE PRECEDURE 存储过程名 (建立可返回值的存储过程)
(
参数1 类型 OUT/INPUT, (OUT只有返回值,INPUT需要传值的返回值)
参数2 类型……
)
IS
[变数定义]
BEGIN
……
EXCEPTION
例外处理部
END;
CREATE OR REPLACE PACHAGE 存储过程 (单纯的数据处理过程,无参数和返回值)
IS
PROCEDURE 可返回值的存储过程
……
FUNCTION 方法
……
……………
EXCEPTION
………
END;
Execute 存储过程([参数1],[参数2]……) (调用存储过程)
- SQL*PLUS
- SQL*Plus
- SQL*PLUS
- SQL/PLUS
- SQL Plus
- SQL PLUS
- SQL/PLUS
- SQL * plus
- sql Plus
- SQl PLUS
- SQL*PLUS
- SQL*PLUS
- Sql*plus
- sql*plus
- SQL*Plus
- sql plus
- sql*plus
- Sql*plus
- C# 给站点指定位置的某种格式的图片添加水印
- html中分页
- DateTime数据类型的XML序列化及时区问题(存档)
- mysql两种查询方式对比--子查询与join查询
- 博士生传给硕士生的经验
- SQL/PLUS
- sharepoint的单点登录
- perl 多维数组创建问题
- locale 详解
- shell cut命令具体应用
- 对付银行里态度恶劣的工作人员的几招
- 马云演讲:未来的十年阿里巴巴的使命(全文)http://blog.sina.com.cn/s/blog_4a78b4ee0100djs8.html~type=v5_one&label=rela_articletagpub
- MNS与仲裁群集磁盘的区别
- Linux下C语言编程