我的java学习之路-oracle

最新推荐文章于 2022-12-09 10:48:10 发布

denglin2014

最新推荐文章于 2022-12-09 10:48:10 发布

阅读量896

点赞数

本文链接：https://blog.csdn.net/denglin2014/article/details/70118030

版权

D:\MainSoftWare\Oracle\product\11.2.0\dbhome_1\deinstall\deinstall.bat 可用进行卸载oracle

D:\MainSoftWare\Oracle\product\11.2.0\dbhome_1\jdbc\lib\ojdbc6.jar 一般jdbc连接时推荐使用

全局数据库名：orcl

管理口令：orcl

数据库用户：scott 和tiger

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = CLRExtProc)

(ORACLE_HOME = D:\MainSoftWare\Oracle\product\11.2.0\dbhome_1)

(PROGRAM = extproc)

(ENVS = "EXTPROC_DLLS=ONLY:D:\MainSoftWare\Oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")

)

(SID_DESC =

(GLOBAL_DBNAME= ORCL)

(ORACLE_HOME = D:\MainSoftWare\Oracle\product\11.2.0\dbhome_1) //文件存放路径

(SID_NAME=ORCL)

)

D:\MainSoftWare\Oracle\product\11.2.0\dbhome_1\bin;

SQL Developer导入的java.exe应为Oracle自带的，D:\MainSoftWare\Oracle\product\11.2.0\dbhome_1\jdk\jre\bin

如果设置错误了，D:\MainSoftWare\Oracle\product\11.2.0\dbhome_1\sqldeveloper\sqldeveloper\bin

修改sqldeveloper.conf的SetJavaHome为正确路径。

配置教程： http://jingyan.baidu.com/article/e4511cf33f289e2b845eafb6.html

按照规范的设计方法，一个完整的数据库设计一般分为以下六个阶段：
⑴需求分析：分析用户的需求，包括数据、功能和性能需求；
⑵概念结构设计：主要采用E-R模型进行设计，包括画E-R图；
⑶逻辑结构设计：通过将E-R图转换成表，实现从E-R模型到关系模型的转换；
⑷数据库物理设计：主要是为所设计的数据库选择合适的存储结构和存取路径；
⑸数据库的实施：包括编程、测试和试运行；
⑹数据库运行与维护：系统的运行与数据库的日常维护

概念模型：用于信息世界的建模，与具体的DBMS无关

模式(概念模式或逻辑模式)概念级是所有用户的公共数据视图(全局视图，数据库的表，字段的类型等

外模式(子模式)于用户级反映了数据库的用户观（视图、查出数据的表）

内模式(存储模式)，物理级顺序存储、按照B树结构存储还是按hash方法存储（索引）

两层架构：外模式/模式模式/内模式

分布式数据库是数据库技术和计算机网络结合的产物

数据库

脏读：一个事务读到另一个事务还未提交的更新数据。

幻读 : 第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时第二个事务向表中插入一行新数据。

那么第一个事务发现表中还有没有修改的数据行，就好象发生了幻觉一样。

不可重复读：指一个事务两次对同一行数据查询，由于第二个事务在此期间对此行数据进行了修改导致第一个事务两次读取到的同一行数据不同

数据库锁：数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况

行级锁是一种排他锁，防止其他事务修改此行，SELECT … FOR UPDATE语句允许用户一次锁定多条记录进行更新

使用COMMIT或ROLLBACK语句释放锁

http://blog.sina.com.cn/s/blog_548bd2090100ir7k.html

表级锁又分为5类：
行共享 (ROW SHARE) – 禁止排他锁定表
行排他(ROW EXCLUSIVE) – 禁止使用排他锁和共享锁
共享锁(SHARE) - 锁定表，如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。

获准共享锁的事务只读不写。

排他锁：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务能读能写。

数据库处理层存取的对象是单个元祖。

1.SYS和SYSTEM(创建表默认账户)用户都是Oracle 的系统用户，它们都使用SYSTEM表空间，SYS拥有更大的权限。
进程分为用户进程、后台进程和服务进程。

2.listener.ora和tnsnames.ora分别用来存储监听器和本地网络服务名的信息

3.Oracle中的 Net Manager工具和Net Configuration Assistant都能用来配置监听器和网络服务名

4. DQL(数据查询语言) select

DML（数据操作语言） insert、update、delete

DDL（数据定义语言） create、alter、drop

DCL（数据控制语言） grant、revoke

5.在数据操纵语音（DML）的基本功能中，包含插入、删除数据，对数据库中数据排序，但不包含描述数据库结构。

DBS(数据库系统):数据库+数据库管理系统+数据库应用程序+数据库管理员

数据库系统的日志文件用于记录数据更新操作

Oralce数据库在进行物理备份有联机备份和脱机备份两种方式可供选择

使用Describe命令可以显示表的结构信息。 SQL Plus下

使用SQL*Plus的Get命令可以将文件检索到缓冲区，并且不执行。

使用Save命令可以将缓冲区中的SQL命令保存到一个文件中，并且可以使用Run命令运行该文件。

Oracle提供了两种类型的权限：系统权限和对象权限

系统权限：提供了在Oracle数据库系统分为内执行某种任务的操作能力。

对象权限：赋予用户操作指定的数据库对象(如表、视图、过程等)。

SQL 语言分类： DQL( 数据查询语言 ) 、 DML( 数据操纵语言 ) 、 DDL( 数据定义语言 ) 、 DCL （数据控制语言）、 TCL( 事务控制语言 ) 。

is null 运算符进行空值判断

查询：

1.select empno,ename,sal*12+1000 from emp //对数值型的数据可以使用算术运算符创建表达式。

2.select empno as 员工编号,ename 员工姓名,sal*13 “Anual Salary ” from emp; //可以使用别名

select sal*12+5000 as "年度奖" from emp

a.重命名查询结果中的字段，增强可读性。

b.别名如果含有空格或者其他特殊字符或大小写敏感，要用双引号引起来,但不能用单引号。

c. as可以省略。

3.select ename || ' is a ' || job from emp;

SELECT '名字为' || ename ||'的员工,年薪为'||sal*12||'年提成为'|| comm*12 FROM emp;

SELECT '员工为'||ename||'年薪为:'||sal*12 anual FROM emp ORDER BY anual //由于||连接成了一个字段，用anual排序只会按首字母排

SELECT '员工为'||ename||'年薪为:',sal*12 anual FROM emp ORDER BY anual // 分成两个字段，可以直接按anual排序

连接运算符（||）可以把列与字符、或其他表达式连接成一个新的字符串,然后一起显示。

4.distinct 可从关键字查询结果中清除重复行

select distinct deptno from emp; //查询deptno 并且去除重复的字段值（两个重复时会先取哪个字段？）

select distinct deptno,job from emp ORDER BY deptno; //distinct作考虑的是deptno和job字段的组合，同时重复去掉，而不是单一某个字段重复。

5.order by 对查询进行排序（默认升序，可以不写）

select deptno,empno,ename,sal from emp order by sal, deptno; //多字段排序，以先指定排序规则的字段优先。

select sal*12 annsal,ename from emp order by annsal //按别名进行排序，还可以按表达式排序。

SELECT ename FROM emp ORDER BY length(ename) //根据ename的长度来排序

SELECT length('SMITHY ') FROM DUAL //SMITHY之后跟7个空格，得到长度为13

6.select 指定条件where查询

select ename,deptno from emp where hirdate='2011-04-11；

注意点：

a.对于表、关键字，大小写不敏感；但是对于字段的值; 必须注意大小写

b.日期格式敏感，缺省格式 'DD-MON-RR'

c.可以使用比较运算符，如不等于<>；between..and..(界于两者之间，包括边界)；in(出现在集合中)；

LIKE 模糊查询；is NULL 为空值

数据库存储默认格式, 日期格式敏感，缺省日期格式是‘DD-MON-RR’

SELECT * FROM emp WHERE HIREDATE>'3/12月/1981'

7.group by:

select column,group_function(column) from table

where condition group by expresion

order by column

出现在select列表中的字段，如果没有在组函数中，那么必须出现在group by 子句中

如果出现大小写时，应转换成一致的类型，group by也是该转换的类型

出现在 SELECT 列表中的字段，如果不是包含在多行函数中，那么该字段必须同时在 GROUP BY 子句中出现(如：count())：

SELECT deptno,ename,job,COUNT(*),AVG(sal) FROM emp GROUP BY deptno,ename,job HAVING COUNT(*)<10

如： SELECT COUNT(*),upper(job) FROM emp GROUP BY upper(job)

SELECT deptno,job,COUNT(*),AVG(sal) FROM emp GROUP BY deptno,job ORDER BY deptno

如果没有Group by子句，则Select 列表中不允许出现字段(包括单行函数)与多行函数混用的情况。

错误：SELECT empno,AVG(sal) FROM emp; //不能混用

不允许在where子句中使用多行函数。

错误： SELECT * FROM emp WHERE AVG(sal) >2000 //where子句中不能有多行函数。

思考？？：

select max(avg(sal)),deptno from emp group by deptno //执行不了，看做先求avg，deptno 并按deptno分组，再求max(两个字段),报错

select max(avg(sal)) from emp group by deptno //max(avg(sal))为聚合函数,必须先分组才能使用

8.having 子句过滤分组后的结果，只能出现在group by 子句之后

where 过滤行(不能跟分组函数，先排除后选，执行效率高)，having 过滤分组(先选后排除，效率比较慢)。having支持所有where操作符。

语法：

select column,group_function(column)

from table

where condition

group by column(或者表达式)

having condition

order by column;

执行过程：from-where-group by-having-select-order by

如果要对部门进行处理，应在group by之后再进行having过滤，因为where在group by之前已执行过了。

group by a,b,c 是按a先分组，a中相同按b,最后c分组

SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno,job HAVING AVG(sal) >1200 ORDER BY deptno, AVG(sal) DESC //用平均数进行排序

dual是一个伪表，用于方便调试使用。

9.插入语句：

a.记录值插入：一次操作只能插入一行。

1.添加所有列,插入元素的个数、顺序与emp结构完全一致(表名之后的列名可省略不写 )

2.添加部分列时可省略的条件，该列允许null值或者表定义中有默认值

3.全部录入，一一对应列，可省略列名，直接录入值。

4.数据类型与值应一一对应，

5.增删改涉及事物的联系，与事务产生联系，而查询数据不涉及数据的更改，不需要使用事务

事务：多条语句作为一个整体来执行，要么全部执行，要么全部不执行。

可以使用commit(对象之类的直接提交)来完成事务的手动提交或者通过工具-》首选项设置自动提交sql，rollback完成回滚

b.查询结果插入

1. CREATE TABLE temp AS SELECT * FROM EMP WHERE 1=2 //加where条件时创建一个表，但没有值;不加where时有值。

2. INSERT INTO temp SELECT * FROM emp //查询emp的数据作为结果集，然后依次插入到temp数据表中 ???

10.删除语句：

1. DELETE FROM temp //清空temp表中所有数据，delete会写入日志，推荐使用 TRUNCATE TABLE temp 完成相同工作，速度更快

delete temp where ... //也能清空数据

11.删除整个表

1. DROP TABLE emp //删除整个表 DROP TABLE emp CASCADE CONSTRAINT //删除表同时删除约束条件

12.查看表结构和重命名表名：

1.desc emp //需在pl/sql中cmd命令窗口执行 2.RENAME old_name to new_name //但是操作者必须是表（视图）的所有者

7.模糊查询：

SELECT empno,ename,SAL*12 ANNUALSAL FROM emp WHERE ENAME LIKE 'J%' ORDER BY ANNUALSAL

Like通配符查询：(跟的字符串注意区分大小写) 'Q%' 与 'q%' 是不一样的

% 表示零个或者多个字符 _ 表示一个字符

模糊查询，不能用=号

SELECT * FROM emp WHERE ename='S%' //错误

ESCAPE 用于特殊符号查找

select * from emp where ename like '_A'

select * from emp where ename like '%\_%' escape '\'; //escape 把\变成了转义，可查询SMITH_MATH

select * from emp where ename like '%？_%' escape '？'; //escape 把？变成转义字符

select * from emp where ename is not null;

8.逻辑运算符：and ; or ; in ; not in

select * from emp where sal not in(100,300,500); //不在这几个值，不是区间

select * from emp where sal >100 or ename='kkkk';

优化：and 把检索结果较少的条件放后面

or 把检索结果较多的条件放到后面

运算符优先级：算数 > 连接 > 比较 > 逻辑但是可以通过小括号来改变优先级

select * from emp where job='SAL' or job='clerk' and sal >1000 //先查sal然后

select * from emp where (job='SAL' or job='clerk') and sal > 1000 //优先查括号内部的

9.修改列：

添加列： ALTER TABLE stu ADD birthdat VARCHAR(10) DEFAULT '2001-9-01' //会在原有的基础上增加一列，并且都有默认值

修改列名： ALTER TABLE stu RENAME COLUMN birthdat TO birthday

修改列的数据类型： ALTER TABLE stu MODIFY birthdat VARCHAR2 (20)

删除列： ALTER TABLE stu DROP COLUMN birthdat

10.函数：只是将取出的数据进行处理，不改变数据库中的值

单行函数： 1.字符函数 2.数值函数日期函数转换函数通用函数 //一般是返回对应的行数值

多行函数： 1.sum() avg() 仅适用于数值型 //一般是求多行的一个结果

2.count() max() min() 适用任何数据类型

SELECT COUNT ( DISTINCT (deptno)) FROM emp

SELECT COUNT(nvl(comm,0)) FROM emp

多行函数特点：

a.除了count(*)之外，都会跳过空值，而处理非空值

b.可使用NVL强制多行函数处理空值

SELECT AVG ( nvl(comm,0) ), MAX (nvl(sal,0)), MIN (nvl(sal,0)), SUM (nvl(sal,0)) FROM emp

字符函数：

SELECT ltrim(ename,'SM'),ENAME FROM EMP

数值函数：

ORACLE中计算月份的差值，都是月底或是日期相同，才会按整月计算。

如果计算结果不是整数，ORACLE 会把31作为分母来计算。

日期函数：

ADD_MONTHS：在输入日期上加上指定的几个月返回一个新的日期。如果给出一负数，返回值日期之前几个月日期。

当给出的数是小数时，正数被截为小于该数的最大整数，负数被截为大于该数的最小整数。

Oracle:类型分为自动转换和强制转换。

转换函数：(虽然数据类型之间可以自动转换，但仍推荐使用转换函数，保持良好的设计风格)

to_char() 函数将日期型数值转换为字符串形式,可以得到数字 tochar( num .format) tochar( date .format)

to_char(date) 转换成 dd-mon-yy格式 to_char(date,'yyyy-mm-dd')

SELECT to_char(SYSDATE,'yyyy')-to_char(to_date('2008-02-28 ', 'yyyy-mm-dd '),'yyyy') FROM dual; //to_char就可以获取年份的数字

SELECT to_char( SYSDATE ,'YYYY-MM-DD HH24:MI:SS AM DY') FROM DUAL; // SYSDATE 获取当前系统日期和时间

SELECT to_char(sal,'$99,999.00') FROM emp //以$99,999.00格式来显示数据

SELECT to_char(sal,'L00,000.00') FROM EMP //以01,888.00 位数不够0就作为占位符

日期格式元素： DY星期的缩写 HH24或HH12 24和12小时进制下的时间 MI 分钟

数字格式元素： 9代表一位数字，如果没有则不显示，但之后的小数部分仍会显示

0代表数字，该位要是没有则强制显示为0

$显示美元符号 L显示本地货币符号

. 小数点 , 显示千分位符号

to_date(char) //将字符串转换为日期类型 to_date(char,''yyyy-mm-dd') //按指定的模式串进行解析

SELECT to_char(SYSDATE,'yyyy')-to_char(to_date('2008-02-28 ', 'yyyy-mm-dd '),'yyyy') FROM dual; //2016-2008能得出8

to_number()

SELECT '12.5'+30 FROM dual //查询的结果 42.5

SELECT '12.5'||30 FROM dual //查询的结果 12.530

转换过程：

特殊函数：

select * from emp for update

NVL(EXP1，EXP2) 如果exp1为null,则返回exp2的值；否则返回exp1的值

NVL(EXP1，EXP2，EXP3) 如果exp1的值为null，则返回exp2的值，否则返回exp3的值类似： ?... :....

DECODE(VALUE,IF1,THEN1,IF2,THEN2,....,ELSE) 如果value的值为IF1，则返回then1的值，

如果value的值为IF2，则返回then2的值，否则返回ELSE的值；类似switch...case

SELECT sal,comm,sal+comm FROM emp //sal+comm 直接相加，当comm为空时，会导致没有数据显示

SELECT sal,comm,sal+nvl(comm,0) FROM emp //使用nvl(comm,0),当comm为空时，返回0，仍有数据显示

SELECT empno,ename,sal,nvl2(comm,sal+comm,sal) total FROM emp

count (*)将返回表格中所有存在的行的总数包括值为 null 的行，然而 count (列名)将返回表格中除去 null 以外的所有行的总数(有默认值的列也会被计入）.

如何删除sales表中产品类型为toys的profits列的列值（ A ）。（选择一项）

A.UPDATE sales SET profits = NULL WHERE product_type = 'TOYS'

B.DELETE profits FROM sales WHERE product_type = 'TOYS'

C.DELETE FROM sales WHERE product_type = 'TOYS'  //删除一行

D.DELETE FROM sales

DELETE FROM S WHERE 年龄 >60 语句的功能是对年龄大于60的进行删除标记   //需要commit才能完成事务提交

rownum和rowid都是伪列，rownum是返回查询的记录行数，主要用于分页；规定每页显示的行数，一共有多少页。

根据sql查询出的结果给每行分配一个逻辑编号，rownum会根据查询条件重新编号。

  SELECT ROWNUM ,e.* FROM emp  e WHERE ROWNUM>5; //查不到数据，rownum变化了

--利用rownum每次查询条件改变重写编号的特点，把第一次的查询排序结果当成一个表作为新的数据源

SELECT ROWNUM,t.* FROM (

SELECT e.*

FROM emp e

ORDER BY e.sal DESC) t

WHERE ROWNUM<=5; --查前5行

--查询第二页记录

SELECT * FROM (

  SELECT ROWNUM r,t.* FROM (

    SELECT e.*

    FROM emp e

    ORDER BY e.sal DESC) t

   WHERE ROWNUM<=10 --查第二页，5到10行

)d

WHERE r>5;

--查询第三页

SELECT * FROM (

  SELECT ROWNUM r,t.* FROM (

    SELECT e.*

    FROM emp e

    ORDER BY e.sal DESC) t

   WHERE ROWNUM<=15 --查第三页，10到15行

)d

WHERE r>10;

sql分页时或是查找某一范围内的记录时，我们会使用rownum

例子：查找2到10范围内的记录（这里包括2和10的记录）

select * from (select rownum rn , a.* from emp a) t

where t.rn between 2 and 10;

rowid是物理结构上的,人工无法改变，访问效率高，在每条记录insert到数据库中时，都会有一个唯一的物理记录

处理一张表中重复记录时经常用到

--b) 删除重复部门，只留下一项: ???

delete from dept d where rowid <>

(select min(rowid) from dept where dname=d.dname and d.loc=loc);

--a)    将各部门员工的工资修改为该员工所在部门平均工资加1000:

update emp b

set sal=(select sal from (select deptno,avg(sal)+1000 sal from emp group by deptno) a
where a.deptno=b.deptno)    //通过查询到的部门编号跟当前的部门编号比较得出具体的员

--c)    更新员工工资为他的主管的工资,奖金:

UPDATE emp b SET sal=

(SELECT sal FROM emp WHERE EMPNO=b.MGR),COMM=(

SELECT comm FROM emp WHERE EMPNO=b.MGR);

按年月显示入职期限

select empno,ename,

to_char(floor(to_number((sysdate-hiredate)/365)))||' 年 '||

to_char(ceil(months_between(sysdate,hiredate)-(floor(to_number((sysdate-hiredate)/365)))*12))||' 月 '
from emp

求人数最多的部门，查询人数最多的部门信息

SELECT d.DEPTNO,d.DNAME,d.LOC,COUNT(*)

FROM emp e,dept d

WHERE e.deptno=d.DEPTNO

GROUP BY d.deptno,d.DNAME,d.LOC

HAVING COUNT(*)=(SELECT MAX(e.c) FROM (SELECT COUNT(*) c

FROM emp

GROUP BY deptno
) e);

11.多表查询

笛卡尔积：

a.select * from dept; select * from emp; 等价于select * from dept,emp

总结：1.检索出的行的数目将是第一个表的行数乘以第二个表中的行数

2.检索出的列的数目将是第一个表的列数乘以第二个表中的列数

3.应保证所有的联结都有where子句，不然会返回比原本想要多的数据。

等值（列）查询：内连接

语法： select table1.col,table2.col from table1,table2 where table1.col=table2.col

s elect d.deptno,dname,loc,empno,ename,job from dept d,emp e where d.deptno=e.deptno and d.deptno=10

特点：a.当被连接的多个表中有同名字段时，须在该字段前加上"表明."前缀

b.可是用AND操作符增加查询条件

c.使用别名简化查询效率, 有别名时就要用别名来操作，不能再使用源表名。

不等值连接： > < <> between

SELECT ename,sal,grade,losal,hisal

FROM emp e,SALGRADE s

WHERE e.sal BETWEEN s.LOSAL AND s.HISAL

外连接：

使用外连接可以看到参与连接的某一方不满足查询条件的记录。

92语法不支持全外连接：

左连接：显示左边表的全部行（无 + 号一侧，所有的记录都要显示）

SELECT d.deptno dno,e.deptno eno,dname,ename FROM

dept d,emp e WHERE d.deptno =e.deptno (+)

右连接：显示右边表的全部行

select table1.col,table2.col from table1,table2

where table1.col (+) =table2.col

自连接：

将一个表，当两个表使用。

显示员工的名字、职位和上级的名字，前提是表里已经有存在上级与下级的关系字段

SELECT e.ename 员工,e.job 员工职位,m.ENAME 上级名字

FROM emp e,emp m

WHERE e.EMPNO=m.MGR
ORDER BY e.EMPNO

99语法：

左外联接：两个表在连接过程中除返回满足连接条件的行以外，还返回左表中不满足条件的行

select deptno,dname,empno,ename,job

from dept left join emp

using(deptno);

右外联接：两个表在连接过程中除返回满足连接条件的行以外，还返回右表中不满足条件的行

select deptno, dname,empno,ename,job

from dept RIGHT join emp

using(deptno);

满外联接：两个表在连接过程中除返回满足连接条件的行以外，还返回两个表中不满足条件的所有行

select deptno, dname,empno,ename,job

from dept FULL join emp

using(deptno);

多表连接查询：

连接n个表，至少需要0到n-1个连接条件。

交叉连接：看做笛卡尔积

select dept.deptno,dname,ename,job from dept cross join emp;

等价于 select dept.deptno,dname,ename,job from dept,emp;

自然连接：自动把表之间名称相同类型相同的列进行自然的匹配

优点：不需要自己添加条件

特点：相同的列只显示一次

select 中展示的相同列不能有限定词（指定某个具体的表）

select d.deptno,e.ename,d.dname from emp e natural join dept d

Using子句：如果不希望参照被连接表的所有同名列进行等值连接，自然连接将无法满足要求，可以在连接时使用 USING

子句来设置用于等值连接的列（参照列）名。

using 子句引用的列在sql任何地方多表相同的字段就不能使用表名或者别名做前缀。

select e.ename,e.sal, deptno ,d.dname --不能指定相同的列，如d.deptno 是错的

from emp e join dept d

using(deptno)

where deptno= 20

On：基于表中所有同名列的等值连接，为了设置任意的连接条件或者指定连接的列，需要使用ON子句。

SELECT d.DEPTNO,ename,dname

FROM dept d  LEFT JOIN  emp e

ON e.DEPTNO=d.DEPTNO    --表连接条件

WHERE e.empno>7800 AND d.deptno>10 --表内数据过滤

---------------------------------------------------------------------------

SELECT e.ename,e.job,e.sal,deptno,c.cname

FROM emp e JOIN deptcopy d

USING (deptno)

JOIN city c

/*ON c.loc =d.loc AND e.sal>2500;*/

--USING(loc)    对多表中存在相同字段优先 使用using ,不能使用别名，如d.deptno就是错的

ON d.loc=c.loc   --也可以使用on
WHERE e.sal>2500;

12.子查询：

a.子查询在主查询前执行一次，主查询使用子查询的结果。

b.除了Top-N分析，否则不要在子查询中使用Order By子句。

c如果子查询返回的是单行结果，则可使用的单行比较运算符： < 、 > 、 = 、 >= 、 <= 、 <>

d..如果子查询返回多行结果，则不允许对其使用单行记录比较运算符。

多行运算符： • ALL 和子查询返回的所有值比较

•ANY 和子查询返回的任意一个值比较

•IN 等于列表中的任何一个

13.数据库对象：

a.创建用户： CREATE USER bjsxt IDENTIFIED BY bjsxt;

b.分配权限或角色： GRANT CONNECT,RESOURCE,DBA TO bjsxt //CONNECT:临时用户 RESOURCE：更为可靠和正式的用户

GRANT DBA TO scott; //通过系统用户来授予scott用户权限

c.取消权限： REVOKE RESOURCE FROM bjsxt //DBA:数据库管理角色，拥有管理数据库的最高权限

d.删除用户：drop user bjsxt;

e.修改密码：alter user bjsxt815 identified by sxt;

f.解锁： alter user scott account unlock;

select * from scott.emp; //授权给用户dba之后，才能查询对应的表。

语法：select username,default_tablespace from user_users;

忘记sys密码：

数据表字段基本类型：

1.字符型数据类型

char：固定长度的字符串 varchar2:存储可变长度的字符串，不要用varchar，在后续版本中可能不支持

2.数值

NUMBER:存储整数和浮点数，格式：NUMBER(3) //整数,默认38位

NUMBER(3,2) //浮点数,两位小数，实际能输入只能1位数字，因为要预留两个小数位

3.日期时间

DATE:存储日期和时间数据 TIMESTAMP:比DATE更精确

日期增加一周：update emp set hiredate=hiredate+7 where empno=7369;

4.LOB(large object block)数据

BLOB:存储二进制对象，如图像、音频和视频文件(BINARY)

CLOB:存储字符格式的大型对象(CHARACTER)

14.创建表：

CREATE TABLE STUDENT(

   SNO NUMBER(6),

   SNAME VARCHAR2(10)    NOT NULL , --使用varchar2，不要varchar,因为后续版本不支持varchar；not null 列级约束

   SEX CHAR(2),   --固定字符长度，注意中文2个字符

   AGE NUMBER(3),

   ENTERDATE DATE,

   clazz VARCHAR2(10),

   email VARCHAR2(20)  --最后一个字段不要加标点符号

);

--to_date

INSERT INTO STUDENT VALUES(1,'AAA','男',25,to_date('2016-10-12 17:20:30','yyyy-mm-dd HH24:MI:SS'),'0815班','kkk@0815.com');

--默认日期格式,26-09月-2016是错误的，不能使用09月，只能是9月

INSERT INTO STudent VALUES(1,'CCC','女',19,'26/9月/2016','看看','ccc@2019.com');

15.完整性约束：

约束分类：

表级约束：可以约束表中的任意一列或多列。可以定义出了 Not Null 以外的任何约束。

列级约束：只能约束其所在的某一列。可以定义任何约束。

a.域完整性约束(非空 not null,检查 check) //字段约束

b.实体完整性约束(唯一unique ,主键 primary key) //行与行之间的约束

c.参照完整性约束(外键 foreign key) //表与表的约束

外键：用于两表间建立关系，外键是表中的一个列，其值必须在另一表的主键或者唯一键中列出。

– 当要删除主表的某个记录（即删除一个主键值，那么对依赖的影响可采取下列 3 种做法：

•RESTRICT（no action）方式：只有当依赖表中没有一个外键值与要删除的主表中主键值相对应时，才可执行删除操作。

先删除子表记录，再删除主表记录。

•CASCADE方式：将依赖表中所有外键值与主表中要删除的主键值相对应的记录一起删除

删除主表的同时把子表依赖于主表的记录也一起删除。

--添加外键约束，同时增加级联删除

ALTER TABLE student ADD CONSTRAINT fk_student_clazzid FOREIGN KEY(clazzid)

REFERENCES clazz(CLAZZID) ON DELETE CASCADE;

•SET NULL方式：将依赖表中所有与主表中被删除的主键值相对应的外键值设为空值

--删除的时候，把外键的值设为null，解除关联关系

ALTER TABLE student ADD CONSTRAINT fk_student_clazzid FOREIGN KEY(clazzid)

REFERENCES clazz(CLAZZID) ON DELETE SET NULL;

约束命名规则推荐：

•非空约束　　　　NN__表名列名

CREATE TABLE STUDENT(

SNO NUMBER(6) PRIMARY KEY,

SNAME VARCHAR2(10) NOT NULL,

）

•唯一约束　　　　UK_表名_列名

唯一：确保所在的字段或者字段组合不出现重复值；但是允许出现空值，可以有多个空值。

例子：

--唯一约束不允许列之间的值重复，但是可以为空，而主键约束既不能重复，也不能为空。

CREATE TABLE STUDENT(

SNO NUMBER(6) PRIMARY KEY,--主键约束，创建时添加,约束名由系统分配,如sys_c0012101

email VARCHAR2(20) UNIQUE

  );
--增加唯一约束

ALTER TABLE student ADD CONSTRAINT uq_student_ename unique(email)

•主键约束　　　　PK_表名

主键：1.不允许为空，也不允许出现重复。

2.一个表只允许一个主键，并且主键能唯一确定一个行数据的字段。

3.主键字段可以是单字段或多字段（联合主键）的组合。

例子：

列级约束：

CREATE TABLE STUDENT(

SNO NUMBER(6) PRIMARY KEY,--主键约束，创建时添加,约束名由系统分配,如sys_c001210

grade NUMBER(3) --number(3,2)会预留两个小数位，只能输入9,90会报错

);

表级约束：

CREATE TABLE STUDENT(

SNO NUMBER(6),

grade NUMBER(3), --number(3,2)会预留两个小数位，只能输入9; 90会报错

CONSTRAINT pk_student PRIMARY KEY(sno)--表级约束,往往使用多个字段来联合命名，又称联合主键

--CONSTRAINT pk_student PRIMARY KEY(sno,grade) --使用多个字段来联合命名，又称联合主键 ,sno和grade的值同时一样才会报错误。

);

补充约束：

ALTER TABLE student ADD CONSTRAINT pk_student PRIMARY KEY(sno);

--原本非空，修改成可为空

ALTER TABLE student MODIFY sname NULL;

删除约束：

ALTER TABLE student DROP CONSTRAINT pk_student;

•外键约束　　　　FK_表名_列名表与表之间的关系

如dept的deptno是主键列,emp的deptno是外键列，外键依赖或者从属于主键列。

--修改列名

ALTER TABLE student RENAME COLUMN clazz TO classiz;

---创建列级外键约束

CREATE TABLE STUDENT(

SNO NUMBER(6) PRIMARY KEY,

stu_clazzid NUMBER(6) REFERENCES clazz(CLAZZID), --列级外键约束

);

--创建表级外键约束

CREATE TABLE STUDENT(

SNO NUMBER(6) PRIMARY KEY,

CONSTRAINT fk_student_clazzid FOREIGN KEY(stu_clazzid) REFERENCES clazz(CLAZZID) --表级外键约束

);

--修改student外键关联clazzid

ALTER TABLE student ADD CONSTRAINT fk_student_clazzid FOREIGN KEY(clazzid) REFERENCES clazz(CLAZZID);

•检查约束　　　　CK_表名_列名

检查：对一个属性的值加以限制

CREATE TABLE STUDENT(

   SNO NUMBER(6) PRIMARY KEY,--主键约束，创建时添加,约束名由系统分配,如sys_c0012101

   SNAME VARCHAR2(10) NOT NULL,

   SEX CHAR(2) DEFAULT '男' CHECK(sex='男' OR sex='女'),--检查约束,写了“，值则为”，而不是默认男

   email VARCHAR2(20) UNIQUE,

   grade NUMBER(3)         --number(3,2)会预留两个小数位，只能输入9,90会报错

);

--增加约束

ALTER TABLE student ADD CONSTRAINT ck_student_sex CHECK(sex='男' OR sex='女');

序列：oracle专有的对象，用于生成一个自动递增的数列。

例子： CREATE SEQUENCE seq_empcopy_id START WITH 1

INCREMENT BY 1 cache 10; //以1开始，自增1 cache 设置缓存提高读取效率

create sequence seq_02; //第一次, 在执行sq_test02.currval之前要先执行sq_test02.nextval，否则序列不知指向具体的是什么值

--循环序列化100-90-100

CREATE SEQUENCE sq_test02

START WITH 100

INCREMENT BY -1

MAXVALUE 100

MINVALUE 90

NOCACHE

CYCLE;

查下一个值： SELECT seq_empcopy_id. nextval FROM dual

查当前值： SELECT seq_empcopy_id. currval FROM dual

删除序列： DROP SEQUENCE seq_empcopy_id

应用：insert into student(seq_empcopy_id.nextval,“aaaa”); //可防止重复录入id

索引：实现快速查询数据，采用B树结构，减少对磁盘I/O操作。创建两种情况：自动和手动

定义主键或者UNIQUE约束条件后系统都会自动创建索引,索引单独占用空间，在数据量大的时候发挥作用。

创建索引之后，不用手动调用，会自动发挥作用，但必须使用该索引进行筛选或者排序的时候。

如建立索引： CREATE INDEX index_emp_sal ON emp(sal); //位图索引

在select * from emp e where e. sal>1500； //会用到索引

索引能提高速度，为什么不在所有字段都添加索引？

1.有些列很少出现在筛选条件中，建立索引没有实际的意义。

2.索引本身就占据一定的存储空间，如果给大量的字段添加不必要的索引，会造成空间的浪费。

3.索引本身会降低增删改的效率，比如删除某条记录，其他的记录可能发生位置的变动，需重新维护改索引。

对于像job之类的域值范围较小的时候，效率不高。

CREATE INDEX index_emp_deptno_sal ON emp(deptno ASC,sal DESC); //联合索引

SELECT * FROM emp e

WHERE e.DEPTNO>10 AND e.SAL>1100; //deptno和sal都会排序

--创建多列索引，如果筛选条件没有同时用到索引的列，则只对用到的列有效。

SELECT * FROM emp e WHERE e.sal>1100; //只有sal会排序

视图特点：

1.视图带来了查询便捷，可以预先制作局域不同的权限显示内容的视图，提高了效率。

2.安全，不同层级领导和员工可以在授权范围内查看相关的数据内容。

3.视图的操作本质上是对其真正的表进行增删改查的操作。视图本身不存储数据，只是一条或者多条的sql命令，虚表。

4.视图一般用于查询操作，不建议进行DML操作，即增删改的操作。

5.视图可以基于表的创建，也可以基于其他的视图来创建。

6. 如果视图对应多个表，一般不允许添加操作，可以通过触发器解决

INSERT INTO myview(EMPNO,ename,job) VALUES(8888,'kkkkk','ccccc'); //可以对视图myview插入数据，同时插入数据到emp,不建议

CREATE OR REPLACE VIEW myview //创建或者代替旧的视图

AS SELECT empno,ename,sal,deptno

FROM emp WHERE deptno>20 //可以插入10部门的记录，用myview查不出来，但是用emp能查得到10号部门记录

WITH CHECK OPTION //限定输入条件。加上之后会检查deptno的取值，不加的话可以插入10部门的记录。

或者 WITH READ ONLY //限制只读，不能录入数据

DROP VIEW myview; //删除视图。

CREATE OR REPLACE VIEW myview2

AS SELECT e.ename,e.JOB,e.sal,deptno,d.dname --deptno 共同列，不能用别名

FROM emp e JOIN dept d

USING(deptno) WHERE e.SAL>1100;

CREATE OR REPLACE VIEW myview03

AS SELECT e.deptno 部门编号, AVG (e.sal) 部门平均工资, MAX (e.sal) 最大工资, COUNT (*)总人数

FROM emp e,dept d

WHERE e.deptno IS NOT NULL

GROUP BY e.deptno

ORDER BY 部门平均工资 DESC;

CREATE OR REPLACE VIEW myview04

AS SELECT * FROM myview03

WHERE 部门编号>10; --此处必须使用别名，否则会报错

事务特点：

是一个操作的序列，要么全做，要么全不做。主要是为了保证数据库的完整性。

没有必要手动开启事务，当执行DML语句时，事务就已经自动开启，需要提交事务，才能永久保存至数据库。

commit; //提交事务之前信息会被锁住，只有提交事务之后才会释放锁。关闭pl/sql也会自动提交

Commit表示事务成功地结束，此时告诉系统，数据库要进入一个新的正确状态，该事务对数据库的所有更新都以交付实施。

rollback; //回滚，发生错误，数据库可能不正确的状态，则事务需要撤销，但是在commit之后就不能再rollback

导入/导出数据库:

exp 命令行推荐

imp

三大范式:

第一范式：遵循系统的实际需求来定。

原子性：

用户表(用户名，家庭地址) //家庭地址可以再分，不符合第一范式

用户表(用户名，省，城市，详细地址) //已经细分，符合第一范式

第二范式：

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关( 主要针对联合主键而言)

即在一个数据表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

第三范式：

第三范式要确保数据表中的每一列数据都和主键直接相关，而不能间接相关；属性不依赖与其他非主属性。

范式优点： 1）范式化的数据库更新起来更加快；

2）范式化之后，只有很少的重复数据，只需要修改更少的数据；

3）范式化的表更小，可以在内存中执行，效率比较低；

4）很少的冗余数据，在查询的时候需要更少的distinct或者group by语句。

虽然满足三大范式是设计数据表的要求，但是有时实际需求更重要。可以在3中增加价格列，适当增加冗余

数据表之间的关系：

一对一：学生和学生证

一对多：学生和班级

多对多：学生和课程

数据库外键关系：其实是一种一对多的关系

一对一：外键+唯一

多对多：引入中间表，把一个多对多表示为两个一对多

第一范式：字段不能再分（原子性）

第二范式：不存在局部依赖(不能只依赖联合主键的某一部分)

第三范式：不含传递依赖(间接依赖)

范式的优点：使用范式可以减少冗余，但会降低性能。

特定表的设计可以违反第三范式，增加冗余提高性能。

Cursor游标：

1. / 代表输入结束，可以执行

commnd window运行前必须先执行：set serveroutput on; 否则不输出结果

DECLARE

CURSOR c IS //声明游标c

SELECT * FROM emp; //查询全部emp记录

v_emp c%ROWTYPE; //c%rowtype ：表示该类型为行数据类型，存储的时候为一行数据，一行有很多列

BEGIN

OPEN c; //打开游标

LOOP //循环开始 loop

FETCH c INTO v_emp; //不断fetch下一个记录

EXIT WHEN (c%NOTFOUND); //当没有记录时就退出

DBMS_OUTPUT.put_line(v_emp.ename); //在界面打印姓名

END LOOP; //结束循环

CLOSE c; //关闭游标

END;

/ //commd window下必须写，代表输入结束

while+loop:循环输出

DECLARE

CURSOR c IS

SELECT * FROM emp;

show_emp c%ROWTYPE;

BEGIN

OPEN c;

FETCH c INTO show_emp;

WHILE(c%FOUND)

LOOP

DBMS_OUTPUT.put_line(show_emp.ename);

FETCH c INTO show_emp; //show_emp看做一个Cursor对象

END LOOP;

CLOSE c;

END;

for循环：推荐使用

DECLARE

CURSOR c IS

SELECT * FROM emp;

BEGIN

FOR show_emp IN c

LOOP

DBMS_OUTPUT.put_line(show_emp.ename);

END LOOP;

END;

带参数游标：

DECLARE

CURSOR c(sdeptno emp.DEPTNO%TYPE,sjob emp.JOB%TYPE) //规定sdeptno是emp.deptno的类型

SELECT ename,sal FROM emp WHERE deptno=sdeptno AND job=sjob;

BEGIN

FOR temp IN c(30,'CLERK') LOOP

DBMS_OUTPUT.PUT_LINE(temp.ENAME);

END LOOP;

END;

：= 赋值

= 相等

DECLARE

CURSOR c IS

SELECT * FROM emp FOR UPDATE; 用于更新

BEGIN

FOR temp IN c LOOP

IF(temp.sal<2000) THEN

UPDATE emp SET sal=sal*2 WHERE CURRENT OF c; //当前游标的记录

ELSIF(temp.sal=500)THEN //elsif 此次比较用=跟java 不一样

DELETE FROM emp WHERE CURRENT OF c;

END IF;

END LOOP;

COMMIT;

END;

存储过程：procedure 带有名字的plsql程序块

只有 CREATE OR REPLACE PROCEDURE pp 跟普通的plsql块不一样，其他语法一样

存储过程创建不代表执行：

执行的两种方式：

1.exec pp;

2.begin pp; end; /

--------------------------------------

CREATE OR REPLACE PROCEDURE pp

CURSOR c IS

SELECT * FROM emp FOR UPDATE;

BEGIN

FOR temp IN c LOOP

IF(temp.deptno=10)THEN

UPDATE emp SET sal=sal+100 WHERE CURRENT OF c;

ELSIF(temp.deptno=20)THEN

UPDATE emp SET sal=sal-100 WHERE CURRENT OF c;

ELSE

UPDATE emp SET sal=sal+55 WHERE CURRENT OF c;

END IF;

END LOOP;

COMMIT;

END;

带参数的存储过程：

CREATE OR REPLACE PROCEDURE myopt

(v_a IN NUMBER,v_b NUMBER,v_ret OUT NUMBER,v_temp IN OUT NUMBER)

BEGIN

IF(v_a>v_b)THEN

v_ret:=v_a;

ELSE

v_ret:=V_b;

END IF;

v_temp:=v_temp+1;

END;

调用存储过程： show error 可以显示错误的地方

DECLARE

v_a NUMBER:=3; //：=赋值符号

v_b NUMBER:=4;

v_ret NUMBER;

v_temp NUMBER:=5;

BEGIN

myopt(v_a,v_b,v_ret,v_temp);

DBMS_OUTPUT.put_line(v_ret);

DBMS_OUTPUT.put_line(v_temp);

END;

函数：

CREATE OR REPLACE FUNCTION myfunction(v_sal NUMBER)

RETURN NUMBER

BEGIN

IF(v_sal<2000)THEN

RETURN 0.10;

ELSIF(v_sal<3200)THEN

RETURN 0.3;

ELSE

RETURN 0.5;

END IF;

END;

执行函数：SQL> select lower(ename),myfunction(sal) from emp; //输出对应的税率

触发器：删除使用drop trigger mytrigger

CREATE OR REPLACE TRIGGER mytrigger

AFTER INSERT OR UPDATE OR DELETE ON emp //可以加for each row，则会在每更新一行的时候就触发触发器

BEGIN

IF INSERTing THEN

INSERT INTO emp2_log VALUES(USER,'insert',SYSDATE);

ELSIF updating THEN

INSERT INTO emp2_log VALUES(USER,'update',SYSDATE);

ELSIF deleting THEN

INSERT INTO emp2_log VALUES(USER,'delete',SYSDATE);

END IF;

END;

在emp表上操作记录就会触发mytrigger

UPDATE emp SET sal=sal/2 WHERE deptno=10;

----------------------------------------------------------

更新时触发：

UPDATE dept SET deptno=99 WHERE deptno=10;

//没有触发器之前不能执行，在触发之后，会将新的deptno赋给deptno;关联的地方也改变(触发器的副作用)

//先触发触发器，然后检查约束条件

CREATE OR REPLACE TRIGGER trig

AFTER UPDATE ON dept FOR EACH ROW

BEGIN

UPDATE emp SET deptno=:NEW.deptno WHERE deptno=:OLD.deptno;

END;

denglin2014

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫