当前位置:首页 > 信息技术 > 数据库系统 > SQL: Structured Query Language

SQL: Structured Query Language

SQL: Structured Query Language

全局数据库名称名是数据库在服务器网络中的唯一标识;

DBCA(Database Configuration Assistant)

数据库创建后会有一系列的为该数据库提供服务的内存空间和后台进程,称为该数据库的实例.每个数据库至少会有一个实例为其服务.实例中的结构称为系统全局区(SGA),系统会根据当前计算机的性能给SGA分配非常可观的内存空间.

CONN 用户名/密码 AS 连接身份@服务器连接字符串

SYSDBA/SYSOPER/Normal

network/ADMIN/tnsnames.ora

HIS[服务器连接字符串]=

(DESCRIPTION=

(ADDRESS=(PROTOCAL=TCP)(HOST=127.0.0.1)(PORT=1521))

(CONNECT_DATA=(SERVER=DEDICATED)(SERVER_NAME=HIS[数据库名服务名,在安装中设置]))

)

OracleService+服务名: 数据库启动的基础;

OracleOraDb11g_home1TNSListener: 服务器端为客户端提供的监听程序;

OracleOraDb11g_home1iSQL*Plus: 提供了通过浏览器对数据库中数据进行操作的方式;

OracleDbConsole+服务名: Oracle提供的一个基于B/S的企业管理器(emctl start dbconsole);

startup open;

shutdown immediate

CREATE USER user IDENTIFIED BY passwd [ACCOUNT LOCK|UNLOCK];

ALTER USER user IDENTIFIED BY newPasswd;

ALTER USER user ACCOUNT LOCK|UNLOCK;

如果用户需要正常登录,至少还要CREATE SESSION系统权限

Oracle用户对数据库管理或者对对象操作的权利分为系统权限和数据库对象权限

数据库角色(Role)就是若干个系统权限(或其它角色)的集合.以下是几个常用角色:

CONNECT/RESOURCE/DBA

一个普通的用户拥有CONNECT和RESOURCE角色即可进行常规的数据库开发工作;

可以把某些权限赋给某个角色,也可以把权限或角色赋给用户

GRANT 角色|权限 TO 用户(角色);

GRANT CONNECT TO jerry;

REVOKE 角色|权限 FROM 用户(角色);

Oracle客户端链接服务器前,服务器要启动监听服务,并且客户端工具要安装Oracle客户端,并且在客户端要建立本地网络服务名;

Oracle服务和监听启动后才能对数据库进行操作

数据库定义语言DDL: CREATE|ALTER|DROP|TRUNCATE;

数据库操作语言DML: INSERT|UPDATE|DELETE|SELECT …FOR UPDATE(查询);

数据库查询语言DQL: 基本查询语句|ORDER BY|GROUP BY;

数据控制语言DCL: GRANT|REVOKE;

事务控制语言TCL: COMMIT|SAVEPOINT|ROLLBACK;

CHAR(length): 定长,如果实际存储小于length,用空格填充,默认长度1,最长不过2000字节;

VARCHAR2(length): 变长,默认长度1,最长不过4000字节;

NUMBER(p,s): 存储浮点数或整数均可,有s部分时,p包含s,p默认为38位;

DATE: 存储纪元,4位年,月,日,时,分,秒(公元前4712年1月1日~公元后4712年12月31日);

TIMESTAMP: 不但存储日期的年月日时分秒,还存储秒后6位及时区;

CLOB: 存储大文本,比如结构化的XML文档;

BLOB: 存储二进制对象;

SELECT SYSDATE,SYSTIMESTAMP FROM dual;

dual表在系统中只有一行一列,该表在输出单行函数时为了SELECT…FROM的完整性使用;

创建约束: ALTER TABLE table ADD CONSTRAINT 约束名 约束内容;

注意: 在Oracle中DEFAULT是一个值,而SQL Server中DEFAULT是一个约束,在Oracle中不存在DEFAULT约束,因此Oracle的DEFAULT可以在建表的时候创建;

创建INFOS学生信息表及其约束:

CREATE TABLE INFOS(

STUID VARCHAR2(7) NOT NULL,–学号,学号=’S’+班号+2位序号

STUNAME VARCHAR2(10) NOT NULL,–姓名

GENDER VARCHAR2(2) NOT NULL,–性别

AGE NUMBER(2) NOT NULL,–年龄

SEAT NUMBER(2) NOT NULL,–座位号

ENROLLDATE DATE,–入学时间

STUADDRESS VARCHAR(50) DEFAULT ‘地址不详’,–住址

CLASSNO VARCHAR2(4) NOT NULL–班号,班号=学期序号+班级序号

)

/

ALTER TABLE INFOS ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID)

/

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_GENDER CHECK(GENDER=’男’ OR GENDER=’女’)

/

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_SEAT CHECK(SEAT>=0 AND SEAT<=50)

/

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_AGE CHECK(AGE>=0 AND AGE<=100)

/

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_CLASSNO

CHECK((CLASSNO>=’1001′ AND CLASSNO<=’1999′) OR (CLASSNO>=’2001′ AND CLASSNO<=’2999′))

/

ALTER TABLE INFOS ADD CONSTRAINT UN_INFOS_STUNAME UNIQUE(STUNAME)

/

创建成绩表及其约束:

CREATE TABLE SCORES(

ID NUMBER,–ID

TEAM VARCHAR2(2),–学期S1或S2

STUID VARCHAR2(7) NOT NULL,–学号

EXAMNO VARCHAR2(7) NOT NULL,–考号,E+班号+序号

WRITTENSCORE BUMBER(4,1) NOT NULL,–笔试成绩

LABSCORE NUMBER(4.1) NOT NULL–机试成绩

)

/

ALTER TABLE SCORES ADD CONSTRAINT CK_SCORES_TEAM CHECK(TEAM=’S1′ OR TEAM=’S2′)

/

ALTER TABLE SCORES ADD CONSTRAINT FK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCES INFOS(STUID)

/

SQL Server中可以使用IDENTIFY创建自动增长列,但是Oracle中的自动增长需要借助序列(Sequence)完成;

SELECT *|列名|表达式(列名|函数|常数) [别名] FROM 表名 WHERE 条件 ORDER BY 列名 [ASC|DESC] GROUP BY 列名;

根据查询到的结果集新建表:

CREATE TABLE newTable AS SELECT *|列名|表达式 FROM oldTable [WHERE 1=1];–复制表结构及所有数据

CREATE TABLE newTable AS SELECT *|列名|表达式 FROM oldTable [WHERE 1=2];–只复制表结构

插入数据:

INSERT INTO 表名(列表) VALUES(值表);–注意保持数量,类型,顺序的一致性;

INSERT INTO INFOS

VALUES(‘s100102′,’曹操’,’男’,22,2,TO_DATE(‘2009-8-9 06:30:10′,’YYYY-MM-DD HH24:MI:SS’),’西安’,’1001′)

/

INSERT INTO INFOS VALUES(‘s100104′,张飞’,’男’,23,3,SYSDATE,DEFAULT,’1001′)

/

COMMIT;

在遇到存在默认值的列时,可以使用DEFAULT代替;

使用一个INSERT语句一次性把一个结果集插入到一张表中:

INSERT INTO table SELECT子句;–要求结果集的数量,类型与目的表的要相同

Oracle和SQL Server一样,可以在查询列中使用常量:

SELECT ‘s100105′,’貂蝉’,’女’,18,5,TO_DATE(‘2009-8-9 08:00:10′,’YYYY-MM-DD HH24:MI:SS’),’银川’,’1001′) FROM dual

/

可以把该查询的结果集插入到INFOS表中;

更新修改数据:

UPDATE table SET 列1=值1,列2=值2 WHERE 条件;

UPDATE INFOS SET CLASSNO=’1002′,STUADDRESS=’宁夏银川’ WHERE STUNAME=’曹操’

/COMMIT;

删除数据:

DELETE FROM table WHERE 条件;

DELETE FROM INFOS WHERE STUID=’s100103′

/

COMMIT;

TRUNCATE TABLE table;–可以把table中的数据一次性全部删除,不可恢复

TRUNCATE与DELETE的区别:

TRUNCATE是DDL命令,删除的数据不能恢复;DELETE是DML命令,删除的数据可以通过日志文件恢复;

如果一个表中数据很多,TRUNCATE相对DELETE速度更快;

由于TRUNCATE命令比较危险,因此在实际开发中应慎用;


Oracle默认安装完,会自动创建scott用户,该用户下有4张表:雇员表,部门表,工资登记表,奖金表,接下来的很多操作都是基于该用户下的4张表完成的!

案例: 每名员工年终奖都是2000,请查询月薪在2000以上的员工的月薪和年薪;

SELECT ename,sal,(sal*12+2000) FROM emp WHERE sal>2000

/

关系运算符: =,<>|!=,<,<=,>,>=

逻辑运算符: AND,OR,NOT

字符串连接操作符: ||

SELECT (ename || ‘ is a ‘ || job) AS “Employee Details” FROM emp WHERE sal>2000

/

Oracle字符串可以用单引号,也可以用双引号,在别名中存在空格时必须用双引号.在表名,列名时用双引号.

消除重复行:

SELECT DISTINCT deptno FROM emp

/

NULL空值操作:

空值不等于0或者空格,空值是指未赋值,未知或者不可用的值,任何数据类型的列都可以包括NULL值,除非该列被定义为非空或者主键.

SELECT ename,job,sal,comm “奖金” FROM emp WHERE sal<2000–会发现结果集中有些位置的值为空

/

案例: 查询emp中月薪小于2000且没有发奖金的员工:

SELECT ename,job,salary,comm “奖金” FROM emp WHERE sal<2000 AND comm IS NULL;

[NOT] IN操作符

案例: 查询职位是SALESMAN,PRESIDENT或ANALYST的员工及其月薪,其查询条件可以有以下两种:

WHERE job=’SALESMAN’ OR job=’PRESIDENT’ OR job=’ANALYST’–传统方式

WHERE job IN(‘SALESMAN’,’PRESIDENT’,’ANALYST’)

SELECT ename,job,sal FROM emp WHERE job IN(‘SALESMAN’,’PRESIDENT’,’ANALYST’)

/

对应的NOT IN正好与此相反;

BETWEEN … AND …(闭区间)

案例: 查询月薪在1000到2000之间的员工:

SELECT ename,job,sal FROM emp WHERE sal>=1000 AND sal<=200;–传统方式

SELECT ename,job,sal FROM emp WHERE sal BETWEEN 1000 AND 2000

/

NOT TETWEEN … AND …

LIKE模糊查询:

%: 匹配零个或多个任意字符;

_: 匹配一个任意字符;

语法是: LIKE ‘字符串’ [ESCAPE ‘字符’].匹配的字符串中,ESCAPE后面的字符作为转义字符

例如: ‘%30\%%’ ESCAPE ‘\’–包含”30%”的字符串,”\”指转义字符,”\%”在字符串中表示一个字符”%”;

案例: 查询姓名以”J”开头,以”S”结尾的员工的姓名,工作和工资:

SELECT ename,job,sal FROM emp WHERE ename LIKE “J%S”

/

集合运算:

集合运算就是将两个或多个结果集组合成为一个结果集.集合运算包括:

INTERSECT,UNION,UNION ALL,MINUS(返回第一个查询检索出来的记录减去第二个查询检索出的记录后剩余的记录)

当使用集合操作时,要注意查询所返回的列数以及列的类型必须匹配,列名则可以不同;

案例: 查询dept表中哪个部门下没有员工.只需求出dept表中的部门号和emp表中的部门号的补集即可:

SELECT deptno FROM dept MINUS SELECT deptno FROM emp

/

前面学习过使用INSERT INTO table SELECT把一个结果集插入到一张表中,因此也可以使用UNION把若干条记录一次性插入到一张表中:

INSERT INTO dept

SELECT 50,’公关部’,’台湾’ FROM dual UNION

SELECT 60,’研发部’,’西安’ FROM dual UNION

SELECT 70,’培训部’,’西安’ FROM dual

/

联接查询

INNER JOIN,OUTER JOIN,LEFT OUTER JOIN,RIGHT OUTER JOIN;

Oracle中对两个表或者若干表之间的外连接用(+)表示;

案例: 查询月薪大于2000元的员工的姓名,工作,工资及其部门:

由于部门在dept表中,员工在emp表中,需要内联接才能完成:

SELECT e.ename,e.job,e.sal,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno AND e.sal>2000

/

SELECT e.ename,e.job,e.sal,d.danme FROM emp e INNER JOIN dept d ON e.deptno=d.deptno

WHERE e.sal>2000–SQL/92标准中的内连接,INNER JOIN中的关键字INNER可以省略

/

案例: 查询每个部门下的员工姓名,月薪:

分析:emp表用外键deptno引用dept表中的deptno,在dept表中如果某些部门没有员工,用内联接,没有员工的部门将无法显示,因此必须使用以dept表为基准的外联接

SELECT e.ename,e.job,e.sal,d.dname FROM emp e,dept d

WHERE e.deptno(+)=d.deptno–(+)为Oracle专用的联接符,在条件中出现在左边指右外连接,反之亦然

/

SELECT e.ename,e.job,e.sal,d.dname FROM emp e RIGHT OUTER JOIN dept d

ON e.deptno=d.deptno–SQL/92标准中的外连接,RIGHT OUTER JOIN中的关键字RIGHT可以省略

/

虽然Oracle自身的联接查询语法比较好写,同时容易理解,但是为了程序的可移植性,推荐使用SQL/92标准中的联接查询.

子查询: 内部查询的结果作为外部查询的条件

子查询分为两种类型: 单行子查询(不向外部返回结果,或者只返回一行结果),多行子查询(向外部返回零行,一行或者多行结果).

案例: 查询销售部(SALES)下面的员工姓名,工作,月薪:

SELECT ename,job,sal FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname=’sales’)

/

需要注意:

如果内部查询的结果为空,则外部字段deptno与NULL的比较永远为假,也就是说外部查询不返回任何结果;

在单行子查询中外部查询可以使用=,<>,!=,<,<=,>,>=等比较运算符;

内部查询返回的结果必须与外部查询条件中的字段(deptno)匹配;

如果内部查询返回多条结果将出现错误;

案例: 查询emp表中比任意一个销售员(‘SALESMAN’)月薪低的员工姓名,工作,月薪:

SELECT ename,job,sal FROM emp WHERE sal<ANY(SELECT sal FROM emp WHERE job=’SALESMAN’)

/

<ANY: 比子查询中的任意值都小,即: 比子查询中的最大值还小,同理可知>ANY的意义;

案例: 查询比所有销售员月薪都高的员工的姓名,工作,月薪:

SELECT ename,job,sal FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE job=’SALESMAN’)

/

<ALL: 比子查询中的所有值都小,即: 比子查询中的最小值还小,同理可知>ALL的意义;

对子查询还可以使用[NOT]IN操作符进行操作

Oracle中的伪列:

在Oracle的实际开发中,实绩表中还有一些附加的列,称为伪列.伪列就像表中的其他列一样,但是在表中并不存储.伪列只能查询不能进行增删改操作.

ROWID伪列: 表中的每一行在数据文件中都有一个物理地址,ROWID伪列返回的就是该行的物理地址.使用ROWID可以快速的定位表中的某一行.ROWID的值可以唯一的标识表中的一行.由于ROWID返回的是该行的物理地址,因此使用ROWID可以显示行是如何存储的.

SELECT ROWID,ename FROM emp WHERE sal>2000

/

ROWNUM伪列: 在查询的结果集中,ROWNUM为结果集中的每一行标识一个行号(从1开始计数),通过ROWNUM可以限制结果集中返回的行数.

案例: 查询出员工表中前5名员工的姓名,工作,月薪:

SELECT ROWNUM,ename,job,sal FROM emp WHERE ROWNUM<=5

/

注意ROWNUM与ROWID不同,ROWID是插入数据时生成,ROWNUM是查询数据时生成.ROWID标识的是行的物理地址,ROWNUM标识的是查询结果中行的次序.

案例: 查询月薪最高的前5名员工的姓名,工作,月薪:

案例分析: “月薪最高的前5名”需要先降序,再取前5名,但是生成ROWNUM的操作比排序要早,排序时已经连同ROWNUM一起排序了,因此不能直接在前面案例的基础上直接加ORDER BY,而是需要对排序的结果做二次查询,产生新的ROWNUM才能作为查询的依据.

SELECT ROWNUM,T.* FROM(SELECT ename,job,sal FROM emp ORDER BY sal DESC) T WHERE ROWNUM<=5

/

案例: 查询emp中第5~10条之间的员工的姓名,工作,月薪:

案例分析: 这是分页的应用,如果查询条件中的ROWNUM大于某一正整数,则不返回任何结果.

SELECT * FROM(SELECT ROWNUM R,ename,job,sal FROM emp WHERE ROWNUM<=10) WHERE R>5

/

内部查询中得到ROWNUM,用别名R记录供外层查询使用;内部查询的ROWNUM与外层的ROWNUM是平等的两列;使用的R是内层产生的ROWNUM,在外层看来,内层的ROWNUM是正常的一列

Oracle函数:

函数可以接受零个或者多个参数,并返回一个结果;Oracle中主要有两类函数:

单行函数: 只能输入一行内容,返回一个结果,如: 字符函数,数字函数,转换函数,日期函数;

聚合函数: 可以同时对多行数据进行操作,并返回一个结果;

字符函数:

SELECT ASCII(‘a’) FROM dual;–97

SELECT CONCAT(‘hello’,’ world’) FROM dual;–hello world

SELECT INSTR(‘hello world’,’or’) FROM dual;–8

SELECT LENGTH(‘hello’) FROM dual;–5

SELECT UPPER(‘hell0’) FROM dual;–HELLO

SELECT LOWER(‘Hello’) FROM dual;–hello

SELECT LTRIM(‘===hello===’,’=’) FROM dual;–hello===

SELECT ‘==’||LTRIM(‘ hello===’) FROM dual;–==hello—

SELECT RTRIM(‘===hello===’,’=’) FROM dual;–===hello

SELECT ‘=’||TRIM(‘ hello ‘)||’=’ FROM dual;–=hello=

SELECT TRIM(‘=’ FROM ‘===hello===’) FROM dual;–hello

SELECT REPLACE(‘ABCDE’,’CD’,’AAA’) FROM dual;–ABAAAE

SELECT SUBSTR(‘ABCDE’,2) FROM dual;–BCDE

SELECT SUBSTR(‘ABCDE’,2,3) FROM dual;–BCD

数字函数:

ABS(x),ACOS(x),COS(x),CEIL(x),FLOOR(x),MOD(x),POWER(x),ROUND(x,[n]),SQRT(x),TRUNC(x,[n]);

例如POWER(2,4)=16–x的n次幂

日期函数:

ADD_MOUNTHS(d,n),LAST_DAY(d),

ROUND(d,fmt): 返回一个以fmt为格式的四舍五入日期值.默认fmt为DDD,即月中的某一天;

如果fmt为YEAR,则舍入到最近的年的1月1日,即前半年舍去,后半年返回下一年,同理的月和日;

注意: 如果fmt为DAY,则舍入到最近的周的周日;

例如: SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,’DAY’),ROUND(SYSDATE,’MONTH’),ROUND(SYSDATE,’YEAR’) FROM dual;

与ROUND对应的函数是TRUNC函数对日期的操作,与ROUND类似,只是TRUNC是直接截取到对应格式的第一天;

EXTRACT(fmt FROM d): 提取日期中的特定部分:

fmt为YEAR,MONTH,DAY,HOUR,MINUTE,SECOND.其中的YEAR,MONTH,DAY即可以和DATE类型匹配,也可以与TIMESTAMP类型匹配,但是HOUR,MINUTE,SECOND必须与TIMESTAMP类型匹配;

HOUR匹配的结果没有加上时区,因此在中国的结果小8小时;

例如: SELECT SYSDATE,EXTRACT(YEAR FROM SYSDATE),EXTRACT(MONTH FROM SYSDATE),EXTRACT(DAY FROM SYADATE),EXTRACT(HOUR FROM TIMESTAMP),EXTRACT(MINUTE FROM TIMESTAMP),EXTRACT(SECOND FROM TIMESTAMP) FROM dual;

转换函数:

TO_CHAR(d|n[,fmt])

例如:

SELECT TO_CHAR(SYSDATE,’YYYY”年”MM”月”DD”日” HH24:MI:SS’) “Data” FROM dual

/

数字参数: 9,.,,,$,EEEE,L(数字前面加一个本地货币符号),PR(如果数字是负数,则用尖括号表示)

例如:

SELECT TO_CHAR(-123123.45,’L9EEEEPR’) FROM dual–<¥1.2E+05>

/

TO_DATE(x[,fmt]),TO_NUMBER(x[,fmt])

其它单行函数:

NVL(x,value),NVL2(x,not_null_value,null_value)

聚合函数:

AVG(x),SUM(x),MIN(x),MAX(x),COUNT(x)

表空间 & 数据库对象

前面接触的数据库对象有表,用户,现在学习一些新的数据库对象: 同义词,序列,视图,索引.

首先需要知道Schema的定义:

Oracle中一个用户可以创建表,视图等多种数据库对象,一个用户和该用户下的所有数据库对象的集合称为Schema(模式或解决方案),用户名就是Schema名.一个数据库对象的全称是: schema.objectName,如果一个用户有权限访问其他用户的对象时,就可以用全称来访问.管理员可以访问任何用户的数据库对象.

例如:

conn system/manager@orcl;

SELECT ename,job,sal FROM scott.emp WHERE SAL>2000;

同义词: 给数据库对象起的一个别名;Oracle可以为表,视图,序列,过程,函数,程序包等指定一个别名.同义词有两种:

私有同义词: 拥有CREATE SYNONYM权限的用户(包括非系统管理员)即可创建私有同义词,私有同义词只能由当前用户使用.

共有同义词: 系统管理员可以创建共有同义词,可以被所有用户访问

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]synonymName FOR [schema.]objectName;

DROP [PUBLIC] SYNONYM schema.snonymName;–只删同义词,不影响源数据和对象

案例: 创建一个用户xiaomei,该用户拥有CONNECT角色和RESOURCE角色,为scott用户的emp表创建同义词,通过该同义词访问emp表:

conn system/manager@orcl;

CREATE USER xiaomei IDENTIYIED BY xiaomei;

GRANT CONNECT TO xiaomei;

GRANT RESOURCE TO xiaomei;

GRANT CREATE SYNONYM TO xiaomei;

conn xiaomei/xiaomei@orcl;

CREATE SYNONYM myEmp FOR scott.emp;

SELECT * from myEmp;

ORA-00942: 表或视图不存在

conn scott/tiger@orcl;

GRANT ALL ON emp TO xiaomei;

conn xiaomei/xiaomei@orcl;

SELECT ename,job,sal FROM myEmp WHERE SAL>2000;

序列: Oracle中实现自动增长(缩减)的对象.生成连续的整数数据的对象.序列常作为主键中的自动增长列,序列可以升序生成,也可以降序生成.创建语法如下:

CREATE SEQUENCE squenceName

[START WITH num]–升序默认1,降序默认-1

[INCREMENT BY increment]–升序默认1,降序默认-1

[MAXVALUE num|NOMAXVALUE]–默认NOMAXVALUE,升序最大是10^27,降序默认-1

[MINVALUE num|NOMINVALUE]–默认NOMAXVALUE,升序默认1,降序默认是-10^26

[CYCLE|NOCYCLE]–默认NOCYCLE,达到极值后报错

[CACHE num|NOCACHE]–Oracle默认在内存中产生20个序列号以在下次使用时得到更快的响应

序列创建之后,可以通过序列对象的CURRVAL和NEXTVAL两个”伪列”分别访问序列的当前值和下一个值

例如:

CREATE SQUENCE mySEQ

START WITH 1

INCREMENT BY 1

NOMAXVALUE

NOCYCLE

CACHE 50

/

SELECT mySEQ.NEXTVAL FROM dual;–1

SELECT mySEQ.NEXTVAL FROM dual;–2

SELECT mySEQ.CURRVAL FROM dual;–2

使用ALTER SQUENCE可以修改序列,但是不能修改初始值,最小值不能大于当前值,最大值不能小于当前值.

例如:

ALTER mySEQ

MAXVALUE 1000

MINVALUE -100

/

删除序列使用DROP:

DROP SQUENCE mySEQ

/

视图: 预定义的查询,作为类似表一样的查询使用,是一张虚拟表;实际上是一张或者多张表上的预定义查询,这些表称为基表.

视图具有以下优点:

1.可以限制用户只能通过视图检索数据.这样就可以对最终用户屏蔽建表时底层的基表.

2.可以将复杂的查询保存为视图.可以为最终用户屏蔽一定的复杂性.

3.限制某个视图只能被访问基表中的部分列或者部分行的特定数据.这样可以实现一定的安全性.

4.从多张表中按一定的业务逻辑抽出用户关心的部分,形成一张虚拟表.

CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW viewName AS SELECT… [WITH READ ONLY CONSTRAINT]

说明: FORCE表示即使基表不存在,也创建该视图,但是该视图不能正常使用,当基表创建成功后,视图才能正常使用,默认是NOFORCE;WITH READ ONLY表示只读视图,不能通过该视图对基表进行修改,默认为非只读,现实开发中,基本上不通过视图对表中的数据进行增删改操作.

例如:

CREATE OR REPLACE VIEW empDetail AS SELECT empno,ename,job,hiredate,emp.deptno,dname FROM emp JOIN dept ON emp.deptno=dept.deptno WITH READ ONLY

/

SELECT * FROM empDetail

/

DROP VIEW empDetail

/

索引: 对数据库中的某些列进行排序,提高查询效率.创建索引就是对某些特定列中的数据排序,生成独立的索引表.在某列上创建索引后,如果该列出现在查询条件中,Oralce就会自动引用该索引,先从索引表中查询中符合条件记录的ROWID,由于ROWID记录的是物理地址,因此可以根据ROWID快速的定位到具体的记录,当表中的数据非常多时,引用索引带来的效果非常显著.

如果表中的某些字段经常作为查询条件出现,就应当考虑为该列创建索引

当从很多行中查询少数行时,也要考虑创建索引,基本准则是: 当任何单个查询要检索的行少于或者等于整个表的行数的10%时,索引就非常有用.

Oracle会为表的主键和包含唯一约束的列自动创建索引.

索引可以提高查询效率,但是在数据增删改操作时,需要更新索引,因此索引对增删改会有负面影响.

CREATE [UNIQUE] INDEX indexName ON tableName(columnName…);

UNIQUE指定引用的列上的值必须是唯一的,称为唯一索引

columnName为多列时,称为组合索引

案例: 为emp表的ename创建唯一索引,为emp的月薪列创建普通索引,把job列先变为小写再创建索引.

CREATE UNIQUE INDEX UQ_INDEX_IDX ON emp(ename);

CREATE INDEX IDX_SAL ON emp(sal);

CRATE INDEX IDX_JOB_LOWER ON emp(LOWER(job));–基于函数的索引

Oracle可以为一些列值重复非常多且值有限的列(比如性别列)创建位图索引.关于更多的索引类型(比如反向键索引),请参考Oracle官方文档.

表空间:

Oracle通过表空间提供统一存取格式的大容量

一个数据库空间由多个表空间组成,一个表空间可以由多个数据文件组成,一个数据文件只属于一个表空间

Oracle中所有的数据(包括系统数据)都保存在表空间中,常见的表空间有:

系统表空间: 存放系统数据,在数据库创建时创建.表空间名为SYSTEM

TEMP表空间: 临时表空间,安装数据库时创建,可以在运行时通过命令扩充空间.临时表空间的重要作用是数据排序.在内存装不下太大的数据时,可能会将一些中间的数据写在硬盘的临时表空间中.

用户自定义空间: 用户通过CREATE TABLESPACE命令创建表空间

创建表空间首先要考虑数据库对分区(数据库文件中一段连续的空间,分区是Oracle管理中的最小单位)的管理.Oracle8i以后,推荐使用本地管理表空间EXTENT MANAGEMENT LOCAL,这种表空间的分区是一个固定大小的值.

CREATE TABLESPACE spaceName

DATAFILE ‘file_1_Path’ SIZE size[,file_2_Path SIZE size,…]

EXTENT MANAGEMENT LOCAL

UNIFORM SIZE size

案例: 创建一个表空间,包含两个数据文件大小分别是10MB,5MB,要求EXTENT的大小统一为1MB.

CREATE TABLESPACE mySpace

DATAFILE ‘D:\first.ORA’ SIZE 10M,’D:\second.ORA’ SIZE 5M

EXTENT MANAGEMENT LOCAL

UNIFORM SIZE 1M

/

必须是管理员才能创建表空间,当表空间不足时可以使用ALTER TABLESPACE命令向表空间中追加数据文件扩充表空间

ALTER TABLESPACE mySPace

ADD DATAFILE ‘D:\third.ORA’ SIZE 10M

/

空间不用时可以使用DROP TABLESPACE spaceName删除表空间

创建用户时,可以为用户指定某一表空间,那么该用户下所有的数据库对象(比如表)默认都存储在该空间中

CREATE USER lily IDENTIFIED BY lily ACCOUNT UNLOCK

DEFAULT tableSpace mySpace

/

在创建表时,表中的数据默认用户的表空间中,也可以通过TABLESPACE子句指定表中的数据所放置的表空间.

CREATE TABLE myTable(

ID NUMBER(2),

TERM VARCHAR2(2),

STUID VARCHAR2(7) NOT NULL,

EXAMNO VARCHAR2(7) NOT NULL,

创建索引时,也可以为索引指定表空间.

CREATE INDEX myIndex ON emp(empno)

TABLESPACE mySpace

/

表和索引一旦创建,表空间无法修改


PL/SQL程序设计

Procedural Language/SQL

PL/SQL是一中块结构的语言,它将一组语句放在一个块中,一次性发给服务器,PL/SQL引擎分析收到PL/SQL语句块中的内容,把其中的过程控制语句交给PL/SQL引擎自身去执行,把其中的SQL语句交给服务器的SQL语句执行器去执行.

PL/SQL块发给服务器后,先被编译,然后执行,对于有名称的PL/SQL块(如子程序)可以单独编译,永久的存储在数据库中,随时准备执行.

PL/SQL的优点还有: 支持SQL,支持面向对象编程,可移植性,安全性.

PL/SQL是一种块结构的语言,一个PL/SQL程序包含了一个或者多个逻辑块,每个逻辑块分为三个部分,语法是:

[DECLARE

–declaration statement(包含变量和常量的定义)]

BEGIN

–executable statement(PL/SQL的执行部分)

[EXCEPTION

–exception statement]

END;–注意后面以分号结尾

PL/SQL中的符号说明:

:= 赋值运算符

|| 字符串连接符

— 单行注释

/*,*/多行注释

<<,>>标签分隔符.只为了标识程序的特殊位置

.. 范围操作符.

+,-,*,/

** 求幂运算,比如3**2=9

>,>=,<,<=,= 注意,=不是赋值运算符

<>,! 不等关系

AND,OR,NOT 逻辑运算符

变量声明:

变量名 变量类型[:=初始值]

例如:

DECALRE

Sname VARCHAR2(20):=’Tom’;

BEGIN

Sname:=Sname||’and Jerry”s Home’;

dbms_output.line(Sname);

END;

/

对变量赋值还可以使用SELECR…INTO语句从数据库中查询数据对变量进行赋值,但是查询的结果只能是一条,不能是空或者多行,例如:

DECLRE

Sname VARCHAR2(20) DEFAULT ‘Tom’;–可以使用DEFAULT对变量进行初始化

BEGIN

SELECT ename INTO Sname FROM emp WHERE empno=7934;

dbms_output.put_line(Sname);

END;

/

常量声明:

常量在声明时赋予初值,并且在运行时不允许重新赋值,使用关键字CONSTANT关键字声明常量.

例如:

DECLARE

pi CONSTANT NUMBER:=3.14;

r NUMBER DEFAULT 3;

area BUMBER;

BEGIN

area:=pr*r*r;

dbms_output.line(area);

END;

/

宿主变量: 在PL/SQL中可以声明Session全局变量,在PL/SQL中引用宿主变量要用”:变量名”引用.

例如:

var emp_name VARCHAR2(30);
BEGIN

SELECT ename INTO :emp_name FROM emp WHERE empno=7499;

END;

/

print emp_name;

PL/SQL数据类型:

PL/SQL的数据类型有标量数据类型,引用数据类型,存储文本,图像,影音视频等非结构化的大数据类型(LOB数据类型)等.

标量数据类型: 只有一个值,且内部没有分量.标量数据类型包括数字型,字符型,日期型和布尔型.字符型和数字型又有子类型,子类型只与限定的范围有关,比如NUMBER类型可以表示整数和小数,其子类型POSITIVE只表示整数.

属性数据类型: 当声明的一个变量的值是数据库中的一行或者某列时,可以直接使用属性类型来表示.Oracle中存在两种属性类型: %TYPE和%ROWTYPE

%TYPE: 引用某个变量或者数据库中某列的类型作为某变量的类型.

DECLARE

sal emp.sal%TYPE;

mysal NUMBER(4):=3000;

total mysal%TYPE;

BEGIN

SELECT sal INTO sal FROM emp WHERE empno=7934;

total:=sal+mysal;

dbms_ouput.put_line(total);

END;

/

%ROWTYPE: 引用数据库中的一行作为类型,即RECORD类型(记录类型),是PL/SQL附加的数据类型.表示一条记录,就相当于C#中的一个对象,用”.”来访问记录中的属性.

DECLARE

myemp emp%ROWTYPE;

BEGIN

SELECT * INTO myemp FROM emp WHERE empno=7934;

dbms_output.put_line(myemp.ename);

END;

/

PL/SQL条件控制

IF-THEN,IF-THEN-ELSE,IF-THEN-ELSIF,CASE

IF 条件 THEN

–条件结构体

END IF;

案例: 查询James的月薪,如果大于900元,则发奖金800元:

DECLARE

newsal emp.sal%TYPE:=0;

BEGIN

SELECT sal INTO newsal FROM emp WHERE ename=’James’;

IF(newsal>900) THEN

UPDATE emp SET comm=800 WHERE ename=’James’;

END IF;

COMMIT;–注意: COMMIT同时也能将PL/SQL块以外没有提交的数据一并提交

END;

/

IF 条件 THEN

–条件结构体

ELSE

–条件结构体

END IF;

案例: 查询James的月薪,如果大于900,则发奖金800元,否则发奖金400元:

DECLARE

newsal emp.sal%TYPE:=0;

BEGIN

SELECT sal INTO newsal FROM emp WHERE ename=’James’;

IF(newsal>900) THEN

UPDATE emp SET comm=800 WHERE ename=’James’;

ELSE

UPDATE emp SET comm=400 WHERE ename=’James’;

END IF;

END;

/

IF 条件 THEH

–条件结构体

ELSIF 条件 THEN

–条件结构体

ELSE 条件

–条件结构体(以上条件都不成立时)

END IF;

案例: 查询James的月薪,如果大于1500元,则发奖金1000元,如果大约900元,则发奖金800元,否则发奖金400元:

DECLARE

newsal emp.sal%TYPE:=0;

BEGIN

SELECT sal INTO newsal FROM emp WHERE ename=’James’;

IF(newsal>1500) THEN

UPDATE emp SET comm=1000 WHERE ename=’James’;

ELSIF(newsal>900) THEN

UPDATE emp SET comm=800 WHERE ename=’James’;

ELSE

UPDATE emp SET comm=400 WHERE ename=’James’;

END IF;

END;

CASE是一种选择结构的控制语句,可以根据条件从多个执行分支中选择相应的执行动作.也可以作为表达式使用,返回一个值.

CASE[selector]

WHEN 表达式1 THEN 语句序列1;

WHEN 表达式2 THEN 语句序列2;

……

[ELSE 语句序列N];

END CASE;

案例: 输入字母A,B,C,分别输出对应的级别信息:

代码演示一: CASE中存在selector,不返回值:

DECLARE

v_grade CHAR(1):=UPPER(‘&p_grade’);

BEGIN

CASE v_grade

WHEN ‘A’ THEN dbms_output.put_line(‘Excellent’);

WHEN ‘B’ THEN dbms_output.put_line(‘Very Good’);

WHEN ‘C’ THEN dbms_output.put_line(‘Good’);

ELSE dbms_output.put_line(‘No Such Grade!’);

END CASE;

END;

/

代码演示二: CASE中存在selector,作为表达式返回使用:

DECLARE

v_grade CHAR(1):=UPPER(‘&p_grade’);

grade VARCHAR2(20);

BEGIN

grade:=

CASE v_grade

WHEN ‘A’ THEN ‘Excellent’;

WHEN ‘B’ THEN ‘Very Good’;

WHEN ‘C’ THEN ‘Good’;

ELSE THEN ‘No Such Grade!’;

END CASE;

dbms_output.put_line(‘Grade: ‘||v_grade||’,the result is ‘||grade);

END;

/

代码演示三: 搜索CASE语句(不适用CASE中的selector选择器,直接在WHEN后面判断条件,第一个条件为真时,执行THEN后面的语句序列):

DECLARE

v_grade CHAR(1):=UPPER(‘&p_grade’);

grade VARCHAR(20);

BEGIN

grade:=

CASE

WHEN ‘A’ THEN ‘Excellent’;

WHEN ‘B’ THEN ‘Very Good’;

WHEN ‘C’ THEN ‘Good’;

ELSE THEN ‘No Such Grade!’;

END CASE;

dbms_output.put_line(‘Grade: ‘||v_grade||’,the result is ‘||grade);

END;

/

循环结构

PL/SQL提供了丰富的循环结构来重复执行一系列语句.Oracle提供的循环类型有:

1.无条件循环LOOP-END LOOP语句;

2.WHILE循环语句;

3.FOR循环语句;

在上面的3类循环中可以使用EXIT强制结束循环,相当于C#中的break.

LOOP循环: LOOP循环式最简单的循环,使用LOOP和END LOOP关键字;

LOOP

–循环体,可以使用EXIT[或者EXIT WHEN 条件]的形式终止循环,否则该循环就是死循环.

END LOOP;

案例: 执行1+2+3+…+100的值

DECLARE

counter NUMBER(3):=0;

sumResult NUMBER:=0;

BEGIN

LOOP

counter:=counter+1;

sumResult:=sumResult+counter;

IF counter>=100 THEN

EXIT

END IF;

–EXIT WHEN counter>=100;

END LOOP;

dbms_output.put_line(‘result is ‘||TO_CHAR(sumResult));

END;

/

WHILE循环:

DECLARE

counter NUMBER(3):=0;

sumResult NUMBER:=0;

BEGIN

WHILE counter<100 LOOP

counter:=conuter+1;

sumResult:=sumResult+counter;

END LOOP;

dbms_output.put_line(‘result is ‘||TO_CHAR(sumResult));

END;

FOR循环:

FOR循环需要预先确定循环次数,可以通过给循环变量指定循环下限和上限来确定循环运行的次数,然后循环变量在每次循环中递增(或者递减).FOR循环的语法是:

FOR 循环变量 IN[REVERSE] 循环下限…循环上限 LOOP

–循环体

END LOOP;

上述案例FOR循环实现:

DECLARE

counter NUMBER(3):=0;

sumResult NUMBER:=0;

BEGIN

FOR counter IN 1…100 LOOP

sumResult:=sumResult+counter;

END LOOP;

dbms_output.put_line(‘result is ‘||TO_CHAR(sumResult));

END;

顺序结构:

顺序结构中有两个特殊的语句: GOTO和NULL.

GOTO语句无条件跳转到标签指定的语句中去执行.标签是用双尖括号括起来的标识符,在PL/SQL中必须具有唯一的名称.GOTO语句不能跳转到IF,CASE,LOOP或者字块中.

NULL语句什么也不做,只是将控制权转到下一行语句.NULL是可执行语句.NULL语句在IF或者其他语句语法要求至少有一条可执行语句,但又不需要具体操作的地方.

代码演示案例: GOTO语句和NULL语句:

DECLARE

sumsal emp.sal%TYPE:=0;

BEGIN

SELECT SUM(sal) INTO sumsal FROM emp;

IF(sumsal>20000) THEN

GOTO first_label;

ELSE

GOTO second_label;

END IF;

<<first_label>>

dbms_output.put_line(‘ABOVE 20000: ‘||sumsal);

<<second_label>>

NULL;

END;

PL/SQL中动态执行SQL语句

PL/SQL块先编译后执行,动态SQL语句在编译时不能确定,只有在程序执行时把SQL语句以字符串参数的形式由动态SQL命令来执行.在编译阶段,SQL语句作为字符串存在,程序不会对字符串中的内容进行编译,在运行阶段再对字符串中的SQL语句进行编译和执行,动态SQL的语法是:

EXECUTE IMMEDIATE 动态SQL语句字符串

[INTO 变量列表]

[USING 参数列表]

如果动态语句是SELECT语句,可以把查询的结果保存到INTO后面的变量中.

如果动态语句中存在参数,USING为语句中的参数传值.

动态语句的参数格式是: [:参数名],参数在运行时需要使用USING传值,参数可以使数字,也可以是字符串

案例: 动态执行SQL:

DECLARE

sql_stmt VARCHAR(200);–动态SQL语句字符串

emp_id NUMBER(4):=7566;

salary NUMBER(7,2):=0;

dept_id NUMBER(2):=90;

dept_name VARCHAR(14):=’PERSONNEL’;

location VARCHAR(13):=’DALLAS’;

emp_rec emp%ROWTYPE;

BEGIN

EXECUTE IMMEDIATE ‘CREATE TABLE bonus1(id NUMBER,amt NUMBER)’;

sql_stmt:=’INSERT INTO dept VALUES(:1,:2,:3)’;

EXECUTE IMMEDIATE sql_stmt USING dept_id,dept_name,location;

sql_stmt:=’SELECT * FROM emp WHERE deptno=:id’;

EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

sql_stmt:=’UPDATE emp SET sal=2000 WHERE empno=:1 RETURNING sal INTO :2′;

EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;

EXECUTE IMMEDIATE ‘DELETE FROM dept WHERE deptno=:num’ USING dept_id;

PL/SQL的异常处理

在程序运行时出现错误称为异常.发生异常后,语句将停止运行,PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分.PL/SQL中用关键字EXCEPTION关键字开始异常处理.语法格式:

BEGIN

–可执行部分

EXCEPTION

WHEN 异常名1 THEN

–对应异常处理

WHEN 异常名2 THEN

–对应异常处理

……

WHEN OTHERS THEN

–其他异常处理

END;

预定义异常: 为了Oracle开发和维护的方便,在Oracle异常中,为常见的异常码定义了对应的异常名称,称为预定义异常.

案例:

DECLARE

newsal emp.sal%TYPE;

BEGIN

SELECT sal INTO newsal FROM emp;

EXCEPTION

WHEN TOO_MANY_ROWS THEN

dbms_output.put_line(‘返回的记录太多了’);

WHEN OTHERS THEN

dbms_output.put_line(‘未知异常!’);

END;

/

自定义异常: 预定义异常的两个关键点:

异常定义: 在PL/SQL的声明部分采用EXCEPTION声明异常;

异常引发: 在程序可执行区域,使用关键字RAISE引发异常;

案例: 自定义异常:

DECLARE

sal emp.sal%TYPE;

myexp EXCEPTION;

BEGIN

SELECT sal INTO sal FROM emp WHERE ename=’James’;

IF sal<5000 THEN

RAISE myexp;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line(‘NO FECORD FOUND!’);

WHEN myexp THEN

dbms_output.put_line(‘SAL IS TOO LESS!’);

END;

/

引发应用程序异常

在Oracle开发中,遇到系统异常都有对应的异常码,在应用系统开发中,用户自定义的异常也可以指定一个异常码和异常信息,Oracle系统为用户预留了自定义异常码,其范围是-20000到-20999之间的负整数.引发应用程序异常的语法是:

RAISE_APPLICATION_ERROR(异常码,异常信息)

案例: 引发应用系统异常:

DECLARE

myexp EXCEPTION;

newsal emp.sal%TYPE;

BEGIN

SELECT sal INTO newsal FROM emp WHERE ename=’James’;

IF(newsal<5000) THEN

RAISE myexp;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line(‘NO RECORD FOUND!’);

WHEN myexp THEN

RAISE_APPLICATION_ERROR(-20001,’SAL IS TOO LESS!’);

END;

/

ORA-20001:SAL IS TOO LESS!

如果要处理未命名的内部异常,必须使用OTHERS异常处理器,也可以利用PRAGMA EXCEPTION_INIT把一个异常码与异常名绑定.

EXCEPTION_INIT告诉编译器将异常名与Oracle错误码绑定起来,这样可以通过异常名引用任意的内部异常,并且可以用过异常名为异常编写适当的异常处理器,语法规则是:

PRAGMA EXCEPTION_INIT(异常名,异常码)–这里的异常码可以使用户自定义的,也可以是Oracle系统预定义的.

案例: PRAGMA EXCEPTION_INIT异常:

DECLARE

null_salary EXCEPTION;

PRAGMA EXCEPTION_INIT(null_salary,-20001);

BEGIN

<<innerStart>>

DECALRE

curr_comm NUMBER;

BEGIN

SELECT comm INTO curr_comm FROM emp WHERE empno=&empno;

IF curr_comm IS NULL THEN

RAISE_APPLICATION_ERROR(-20001,’Salary is missing’);

ELSE

dbms_output.put_line(‘有津贴!’);

END IF;

END;

EXCEPTION

WHEN NO_DATA_FOUND WHEN

dbms_output.put_line(‘没有发现行’);

WHEN null_salary THEN

dbms_output.put_line(‘津贴未知’);

WHEN OTHERS THEN

dbms_output.put_line(‘未知异常’);

END; 

 

SQL: Structured Query Language:等您坐沙发呢!

发表评论

表情
还能输入210个字