基本介绍
1.列左对齐,字符右对齐。
2.包含空值的数学表达式的值都为空值
3.DESC[RIBE] +表明用在MANAGE中用来描述表结构。
SELECT 语句详解
ORACLE 中一个语句里面包含多个操作,这些操作并不是顺序执行的,因此,我们可以将Oracle语句拆分,逐步分析它的执行构成。下面以SELECT语句为例。
表1
表2
SELECT 语句由于执行顺序不同,因此对数据的处理方式不同。我们可以把他们分为单行操作符,多行操作符,表操作符。(不知道怎么称呼,姑且这么叫。)
表一中除了SELECT外的所有函数都是单行操作符,因此后面必须和单行数据使用。
表二中的函数+select是多行操作符,因此可以和单行数据,多行数据和分组函数一块儿使用。
ORDER BY 是对整个表进行操作,因此可以称为表操作符。
因此WHERE 后面不能加分组函数,多行数值。
由于SELECT 语句在执行过中有先后顺序,而且每一个函数都有相应的格式,因此SELECT 语句在写法上有一些要求。
如上表,在SELECT 语句中,子查询的优先级别最高因此,先调用子查询,子查询的执行顺序与一般查询一样,但是子查询的返回值被不同函数调用时,应根据函数要求返回不同的值。
如在WHERE 语句中返回单行值,如果有多行,用ANY, ALL函数一一拆分。
1.SELECT与句中先执行子查询,
2.在SELECT 语句顺序执行2.1 GROUP BY ,2.2HAVING和2.3分组函数将原表内容进行分区,浓缩。
由于使用的是原表,因此可以调用新表中没有出现的列。
GROUP BY DEPTNO HAVING AVG(SAL)>1000,是先按DEPTNO分组,再选择条件,条件中执行分组函数,因此HAVING 中可以有分组函数
3.执行SELECT语句,先执行3.1 join on WHERE逐条连接,进行条件删选,因此ON与WHERE
的条件可以互换位置。这样将两个表连接成一个表。
同样是调用原表,因此可以调用新表中没有出现的列。
然后执行SELECT
然后执行表函数ORDER BY 将表排序。
(最后将单行数据赋值:into。)
ORACLE单词及出处
调试权限:
GRANT DEBUG CONNECT SESSION TO SCOTT;
DDL 数据定义语句
用来定义数据库对象(SESSION 会话, TABLE,VIEW,SEQUENCE,INDEX,PROCEDUE, SYNONYM\USER)的语句。
包含CREATE, ALTER, DROP, COMMENT,TRUNCATE.
直接提交,不可回滚,在pl/sql块中要用动态SQL :EXECUTE IMMEDIATE表示。
如EXECUTE IMMEDIATE 'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)';
,
CREATE创建
对数据库的对象创建
表和视图TABLE, VIEW
CREATE TABEL [CREATE OR REPLACE VIEW]表名(
列名数据类型DEFAULT 值constraint 名字约束类型,
CONSTRAINT 名字约束类型(类名));
用户USER
CREATE USER
IDENTIFIED BY PASSWORDS;
序列SEQUENCE
CREATE SEQUENCE 序名
INCREMENT BY 跳跃值
START WITH初始值
MAXVALUE 最大值
NOCACHE/CACHE 存入内存数
NOCYCLE/CYCLE 是否循环
索引INDEX
CREATE INDEX ON 表名(列名,可以多列)
过程PROCEDUE
CREATE PROCEDUE 过程名(参数)**参数在定义时不能指定长度。
IS \AS
定义内容;
BEGIN
执行块;
END;
同义词SYNONYM
CREATE SYNONYM FOR 对象名。
ALTER修改
对数据库的对象进行修改,包括增加列,删除列,改名列,修改类中的数据类型。ALTER TABLE 表名ADD\DROP\RENAME\MODIFY COLUMN;
ALTER 也可以对用户进行修改
ALTER USER
IDENTIFIED BY NEW PASSWORD;
FUNCTION 函数。
单行函数只对表的一行数据进行数据操纵,返回一个值的语句
字符函数UPPER/ LOWER/ INT(ION)CAP(字符串)
CONCAT(字符A,字符B)等价于字符A||字符B :连接
SUBSTR(CHAR A,NUM M,NUM N):截取B如果不写默认为
返回从A中截取索引从M到N的字符串
** 在ORACLE中索引从1开始,且前后都是闭区间。
LENGTH(字符串A)返回字符串A的长度
INSTR(CHAR A,CHAR B [,NUM M,NUM N])后面两个默认为1。
返回从A中从左边第M个字符开始索引B字符在A中出现的第N次的索引。
LPAD | RPAD左|右填充(CHAR A,NUM M,CHAR B)
返回:填充了M长度字符,长度不足则用B补充,长度长了截取。
TRIM(CHAR A FROM CHAR B)||(字符:表示修剪空格):修剪
TRIM从两端开始连续修剪B直到下个不是A。
RTRIM(CHAR A,CHAR B)从右边连续修剪A直到下个不是B,不写默认是空格。
单个字符有效
LTRIM(CHAR A,CHAR B)从左边连续修剪A直到下个不是B,不写默认是空格。
单个字符有效
REPLACE(CHAR A,CHAR B,CHRA C):在A中将B替换成C **全部替换TRANSLATE (CHAR A,CHAR B,CHRA C):变换
将A的B每一个字符换成相应的C的索引相同的字符,C如果没有相应的,用NULL代替。数字函数
ROUND (数字M,整数N) :将M在小数点后N位处四舍五入
TRUNC (数字M,整数N):将M在小数点后N位处截短
N=小数点后第几位,如果N是复数,代表小数点左边相应的位数。
Mod 取模(除数,被除数)
与java中的%一样,返回值的符号与除数保持一至。
CEIL (数字M) :取比M大的最小整数
FLOOR(数字M):取比M小的最大整数
ABS(数字M) :取M的绝对值
SIN(数字M):取M的余弦;
COS(数字M):取M的余弦;
POWER(数字M,数字N):返回M的N次方。
SQRT(数字M) 返回M的开方。
SIGN(数字M) 返回M的符号,正数返回1,复数返回-1,0返回0。
CASE WHEN A THEN B
ELSE C
END CASE;选择。
DECODE(A,B,C
D,E
F)选择。
**DUAL 伪表。在验证函数时可以使用伪表。
SELECT ROUND(4.3,1) FROM DUAL;
DATE日期
日期函数
DATE A-DATE B =A与B相差的天数。浮点型
MONTHS_BETWEEN(DATE A,DATE B) A与B 相差的月份。返回浮点型整数。
ADD_MONTHS(DATE A,NUM M)往日期A 中添加M个月份,返回新日期。
NEXT_DAY(DATE A,DAY/DY/1..7)返回下个周几,可用1234567代表星期的第几天。
中文ORACLE 中用‘星期X’来代替DAY/DD;因此不能用DAY/DD;
LAST_DAY(DATE A)返回A 所在月的最后一天的日期;
ROUND(DATE A,FIELD B)返回A在B出的四舍五入后的日期。FIELD 默认是DD
TRUNC(DATE A,FIELD B)返回A在B出的截断后的日期。FIELD 默认是DD
数据类型转化函数
NUMBER 可以自动转化成字符串。
TO_NUMBER(CHAR R)将R转化成数字,R必须是纯数字型,不能有空格
TO_CHAR(NUMBER,格式)格式中用0代表0..9(一定会显示),9代表数值(0在开头或者末尾不显示),可用美元符号$,用L代表当地货币符号
TO_CHAR(DATE,格式,‘nls_date_language=AMERICAN’)可以选择语言。
SELECT TO_CHAR( 123.5,'L0000.00')FROM DUAL; ¥0123.50
TO_DATE(CHAR A,格式)
日期格式默认格式是DD-MON-RR,一般自定义格式,不用默认的。
YYYY代表四位数年份;YY代表后两个年份数;YEAR代表年份的英文拼写。
1945=1945; 1945=45; 1945= NINETEEN FORTY-FIVE
MM 代表月份数;MON 代表英文月份前三个字母;MONTH代表英文中月份
09=09;09=9月(中文版)=SEP 09=9月(中文版)=SEPTEMBER
DD代表天数;DY 代表英文周的前三个字母;DAY代表英文
09=09;09=MON; 09=MONDAY
HH代表小时后面可以加24表示24小时制;MI代表分钟;SS代表秒.
**用FM可以去掉日期前面的‘0’;
** 时间格式对大小写不敏感(MM和MI区分),在使用时必须放到‘’中;
**“”可以让oracle强制略过不和给定的字符,因此可以用‘YYYY”ANY ”DD”ANY”MM’来表示时间
空值转化函数属于单行函数
NVL(A,B)如果A 为空输出B,否则输出A
NVL2(A,B,C)如果A 为空输出B,否则输出C
NVLIF(A,B…F)连续判断,直到输出非空。全为空还是输出空
分组函数
将数行数值浓缩成一行数值输出,忽略空值。
如果空值必须作为一列就必须用到去空函数。
AVG(列名) 返回该列的平均值。
MAX(列名) :返回该列最大值。
MIN(列名);返回该列最小值
COUNT(列名) :该列的数量
SUM(列名):返回该列的总和
GROUP BY(列,列。。)
HAVING 条件;
**HAVING 中能使用组函数。因为GROUP BY 是对整个表的操纵,因此可以用分组函数。**WHERE 中不能使用组函数,SELECT WHERE 操纵单行数据,因此不能使用分组函数。
**分组函数将数行数据浓缩成一行,因此如果存在分组函数,选出的类必须都浓缩,因此没有组函数的列必须出现在group by语句中。
表函数
对整个表的数据
DISTINCT.
连接
笛卡尔连接,等值连接,非等值连接,自连接,外连接(左连接,右连接,FULL连接)
等值连接也可以用表A JOIN 表B USING (列),列不能用表点出,如果出现在SELECT 语句中也不能点出。因为在SELECT 语句中首先计算表的联合,该等值只有一个列,因此不能被点出,而使用where 列=列有两个列,因此可以点出。
通用连接 A (LEFT/RIGHT)JOIN B ON +条件
**由于分组函数的限制,要完全显示列,必须使用连接。
子查询
子查询要包含在括号内,
将子查询放在比较条件的右侧
子查询可以返回一个值,一列值,多列值,多行值,甚至临时表。
具体使用根据调用子查询的函数有关。
例如在WHERE 下调用子查询,由于WHERE 是单行函数,因此返回值只能有一行。
因此返回值中有多行时必须用其他函数筛选出一行。如ANY,ALL
ANY是所有行的任意一行,ALL是所有行的每一行。
CONSTRAINT 约束
(NOT NULL, PRIMARY KEY, FOREIGN KEY,UNIQUE,CHECK)
列级约束在类的后面CONSTRAINT 名字约束类型
表级约束在表的后面CONSTRAINT 名字约束类型(类名)
NOT NULL 非空
只能作为列级约束;
PRIMARY KEY 主键约束
表名该类值唯一且不为空
UNIQUE 唯一的
用在唯一约束时表示这列的值唯一;
FOREIGN KEY 外键
FOREIGN KEY (列名) REFERENCES 表名(列名);
外键约束必须连到主键上。
REFERENCES关联
用在外键约束中,CONSTRAINT 名字FOREIGN KEY (列名) REFERENCES 表名(列名);
外键约束必须连到主键上。
CHECK 检查
用在where 语句中,WHERE CHECK (条件表达式)表示满足条件才输出。
用在CONSTRAINT 中列+CONSTRAINT 名字CHECK(表达式) 表示该列
值满足一定条件才能输入。
如果是列约束其中表达式中不能出现其他列的值(包括伪列和索引)。
如果是表约束则能现出先列值
CASCADE级联
在外键约束中,ON DELETE CASCADE表示级联删除,
ON DELETE SET NULL表示删除后子列设置为空
(级联删除,与触发器同时运行是可能会造成死循环。。)
权限
权限包括系统权限和对象权限。语法:GRANT/REVOKE;
系统权限
CREATE TABLE/VIEW/SESSION/PROCEDUR/SEQUENCE/INDEX/COMMENT
调试权限。
对象权限
SELECT /UPDATE/EXECUTE/DEBUG CONNECT SESSION /ON 表名
ROLE 角色。建立一个全体,可以为该全体整体赋予权限。
GRANT/REVOKE权限TO用户名WITH ADMIN OPTION级联给予系统权限GRANT/REVOKE权限ON 表名FROM 用户名WITH GRANT OPTION;级联收回对象权限。
TCP/IP结构体
组成体:
Declare申明
Begin:开始
结构体;
EXCEPTION:
End;
1.DECLARE声明
DECLARE 声明变量
变量名数据类型:NUM NUMBER(8,0);
如果没有制定类型的长度,使用默认长度。
PL/SQL 块中可以使用使用%TYPE,%ROWTYPE; 如:EMP. NUMBER%TYPE
PL?SQL还支持布尔类型的数据。
DECLARE 中赋值
变量名数据类型:= 值NUM NUMBER(8,0)=60;
变量名数据类型DEFAULT值NUM NUMBER(8,0)DEFAULT 60;
BEGIN
END;执行块
赋值:
在执行块中可以为变量赋值:变量名:=值;
SELECT 列,列INTO变量名, 变量名
WHERE 条件。。。**加粗部分不能改变顺序。返回值只能有一行。
使用USING为绑定变量赋值
:sal
USING 值
BEGIN +执行块
执行块中的执行与java的执行很相似,但也有很多不同。
1.相似点:顺序执行:PL/SQL 也是顺序执行每一条语句。
也从选择语句,循环语句,跳转语句进行讲解。
不同点:不能在执行语句中声明变量(for除外),要使用的变量必须在declare中声明。
pl/sql中没有{}来表示域,因此每个语句都是以关键字开头,以END;结束形成一个域,for循环域内声明的局部变量同样不能在域外面使用。
2.选择语句:case。。。else;IF ELSIF ELSE;
IF 条件case 判断体
THEN WHEN …THEN
ELSIF WHEN….THEN
THEN ELSE………
ELSE END CASE;
ENDIF;
循环语句,loop,while,for
Loop 无条件循环
LOOP
循环体;
END LOOP;
为了避免死循环,会与exit when或者goto等跳转语句联用。
FOR循环:有限次数的循环
FOR循环结构体:FOR 变量IN 赋值范围LOOP
循环体;
END LOOP;
FOR 后面的条件可以申明一个变量,变量的数据类型与赋值范围一致,不需要显性给予数据类型。给变量逐一赋值in里面的值,当in里面的值用完后结束循环。For循环在游标遍历中最常见,其他由于条件限制,很少用。注意域对变量的限制。
WHILE循环:条件判断循环
WHILE循环体WHILE 条件LOOP
循环体;
END LOOP;
While循环中,条件如果为真则执行循环体,与java最相似的语句,功能也最强大。
3.跳转语句:
EXIT WHEN 满足条件退出循环;
GOTO <<跳转点名字>>跳转到跳转点。
4.事务控制语句(TCL)SAVEPOINT ; ROLLBACK ; ROLLBACT TO SAVEPOINT 保存点名。
EXCEPTION 块:相当于java中的catch块
EXCEPTION
WHEN 要捕获的异常名称THEN 执行操作。
RAISE_APPLICATION_ERROR(-20000到-20999,‘字符串’);
同样,我们可以在DECLARE 中申明异常,在执行块中用RAISE 抛出异常。
动态SQL语句。
执行时才会生成,并生效的sql语句。
一般用在执行是才能确定的SQL语句,或者pl/sql块中不能使用的语句,如DDL语句。一:'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)';
Pl/SQL语句中不能使用DDL语句,所以用动态SQL;
二:sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
:变量名,表示绑定变量。需要使用Using赋值,只有执行的时候,才能知道赋的值是多少,因此使用动态sql。
游标CURSOR
隐式游标
系统在执行DML 语句是自动生成的游标,我们不能打开或者关闭,也不能查看。SQL%ROWCOUNT;
SQL%FOUND;
SQL%NOTFOUND;
%
显式游标
我们声明并使用的游标。游标可以申明return值,确定游标中的数据类型。
CURSOR 在DECLARE 中申明并赋值。CURSOR游标名IS 多列数值。
在执行块中,用OPEN 游标名打开游标,并将游标指向第一个值。
用FETCH 游标名INTO 变量,来将游标中的当前值取出,并使游标指向下一值。
用CLESE 游标名关闭游标。
游标名%FOUND,游标名%NOTFOUND显示反映游标上一个指向值是否存在。因此,必须放在FETCH INTO 语句后面。
FOR循环在游标中的使用
FOR 变量IN 游标名
FOR 简写了游标,不需要OPEN 和CLOSE 游标,也不需要FETCH其中变量与游标的数据类型一致,不能给变量确定数据类型必须用变量.列名返回值。如:
CURSOR CS IS SELECT DEPTNO FORM DEPT;
FOR NUM1 IN CS LOOP
DBMS_OUTPUT.PUT_LINE(NUM1.DEPTNO);
END LOOP;
END;
REF 游标
由于ORACLE支持动态sql,因此有时候,在程序执行前并不能给游标具体赋值,这个时候我们可以用动态游标REN CURSOR;
DECLARE中,TYPE 类型名is REF CURSOR;
对象名类型名;
并不需要赋值。
BEGIN
赋值并指向第一个值:OPEN 对象名FOR 多行值代码。其余用法与游标一直。存储过程,函数。PROCEDURE,FUNCTION.
存储在数据库中的代码块。记住格式就可以了
触发器TRIGGER;
TRIGGER 也是存储在数据库中的代码块,只能自动调用,不能显性调用。触发器触发器不能有参数,