oracle笔记

李楠
WHAT:
Oracle:甲骨文公司(全球最大的数据库软件公司
位于美国加州 红木城 全球一百强)
CEO:Lawrance(Larry) Ellison SDL IBM
1989 正式进入中国大陆
Buiness：服务器以及相关联的软件
(大型服务器 数据库服务器 企业应用服务器)
（ERP CRM）
Scott McNealy JBuilder
IDE(intergraph development environment) IBM eclipse 开源
BEA WeblogicServer 全球第一大java应用服务器
Oracle 9i 10g 11g

ERP:企业资源设计软件 仁科公司（PersonSoft）
CRM:用户管理资源系统 希柏公司（Siebel）
WHY:
oracle市场需求最大的 薪资最高的
ocp ocm oca
HOW:
多做练习
复习

数据库服务器：
微软： sql servler access
瑞典： MySql
美国Sybase： Sybase公司
IBM： DB2
美国Oracle： oracle


第一章
一个认知：
oracle 是一个数据库 还是一个公司
两个基本概念:
数据库：数据的仓库 存放数据的地方
关系型数据库：相对于网状数据库和层次 数据库，数据库中的表存在在相关联的关 系，不能单独存在。
三个关键词：
SQL:第四代语言 只管做什么不管怎么做
select * from s_emp;
PL/SQL:第三代语言 包含这有些逻辑性的语句（if for 。。。。）
SQL*Plus：oracle自带的一个可以输入SQL语言，别且将结果显示在终端的这样一个工具。
四种存储：
一个数据库服务器
一个/多个数据库
一个/多个数据库表
一个/多个数据库数据
五种SQL分类：
1、数据的查询语句
select
2、数据的操作语句（DML）
insert delete update
3、数据定义语句（DDL）
creat drop alter truncate（清空）rename
4、事务控制
commit rollback savepoint
5、授权/取消
grant revoke
delete：删除一条或者多条记录 不能返回表的初始化状态
truncated：删除一个或者多个表 返回表的初始化状态
主键：不能为空 必须唯一
外键：可以为空 如果有值（值必须与其他表里面的键相关联）



第二章
oracle 安装 启动（XEserver listener）
username:system
password:system

sys:用有最高的权限 没有密码
sqlplus "/as sysdba"
system:oracle的管理员 相当于sys 数据库管理员

显示当前用户
show user；

查询存在的所有表单
select table_name from user_tables;

创建user并且赋予密码
create user alexlee identified by alexlee;

赋予权限user
grant resource,connect to username;

切换用户user
conn username/password

查询语句select
select [distinct] */column(aliace) from tablename

查询表的结构desc
desc tablename

查询s_emp中的内容
select * from s_emp;
select (column....) from s_emp;

查询id，name

，salary的内容
selelct id , name , salary
from s_emp;

查询部门的id
select dept_id from s_emp;
select id from s_dept;

查询部门的name
select name from s_dept;

查询部门的详细信息
select * from s_dept;

查询部门id，员工名称，管理者id
select dept_id , last_name , manager_id
from s_emp;

查询员工的姓名，年薪，提成
select last_name , salary*12 , commission_pct
from s_emp;

查询员工姓名，工资，年薪
(算术运算符的使用 先乘除 后加减)
select last_name , salary , salary*12+100
from s_emp;
select last_name , salary , (salary+100)*12
from s_emp;

查询员工的完整姓名同时修改属性名
select last_name || first_name "Employee"
from s_emp;

查询员工的完整姓名，职位 要求first_name和
last_name之间有空格 同时和职位之间有逗号
修改属性名为“Employee”
select first_name || ' ' || last_name || ',' ||
title "Employee"
from s_emp;

查询员工的姓名，职位，提成(没有提成的显示为0)
select last_name , title , salary*nvl(commission_pct,0) from s_emp;

使用distinct关键词，可以将显示中重复的记录只显示一条
语法：select distinct col_name,col_name
from tablename
注意:1 distinct关键词只能放在select关键词后面
错误表达式
select id1,distinct id2
from tablename;

2 如果distinct关键词后面出现多列，表示多列联合去重，即
多列的值都相同的时候才会认为是重复的记录

例如 id1 id2
1 2
2 3
3 4
4 1
4 2
4 4
4 4
select distinct id1,id2
from tablename;
查询员工的职位和部门（相同的职位、部门的只显示一次）
查询员工所在的区域id和名称（相同的区域id只显示一次）
select distinct id,name from s_region;

select distinct salary,title from s_emp;

sqlplus命令
清屏：clear screen/host cls
l,';':显示Buffer中存在的命令
row_num:显示需要操作哪一行内容
a:在显示的行后继续增加内容
c:在显示的行中进行替换操作
c/old/new
del:删除所显示的行内容
i:在显示的行内容下一行增加内容
/:执行缓冲区里面的内容
clear buffer:清空缓冲区
文件相关命令 file_name
save pathname:缓存池中的命令保存在某个文件中
get pathname:将保存命令的那个文件中的命令显示在终端
start/@ pathname:执行保存命令的那个文件中的命令并且将命令显示在终端
spool pathname:将命令和结果都保存在某个文件中
spool off：关闭缓存池
exit

column命令
column(colu)
格式：
colu col_num... FORMAT a/n($ ￥)
注意：colu不能

改变表内部的数据，只能改变显示的效果.
练习 查询员工的id，名字，工资，奖金
要求 1 属性在一行显示
2 有奖金的显示奖金数目 没有的为0
3 工资以￥开头

第三章 排序和限制查询

1.排序：所谓排序，就是根据某个字段的值按照升序或者降序的情况将记录查询出来
注意：1.排序使用order by字句，该字句只对查询记录显示调整，并不改变查询结果
所以执行权最低，即最后执行
格式: select col_num...
from tabname
order by 条件;
2.排序关键词：
升序(ASC)默认执行升序排列
降序(DESC)必须写。
3.如果有多个列排序，后面的列排序的前提是前面的列排好序以后有重复(相同)的值。
格式:select col_num
from tabname
order by 条件1 DESC，条件2

例子：id1 id2
1 2
2 3
3 4
4 2
4 3
4 4
select id1,id2
from tabname
order by id1,id2 DESC;

需求：查看员工的id，名字和薪资，按照薪资的降序排序显示（薪资相同的前提下以按照id的升序排列）
select id,last_name||first_name,salary
from s_emp
order by salary DESC,id;
colu id FORMAT A5;
colu last_name||first_name FORMAT A30
colu salary FORMAT $99,990.00;
/
练习：查看员工的id，名字和薪资，按照id的升序排列（id相同的前提下按照工资的降序排列 同时属性在一行显示）

2.限制查询，即指定查询条件进行查询
语法：
select col_name,....
from tb_name
where col_name 比较操作表达式...
逻辑操作符 col_name 比较操作表达式...

注意：1.限制查询条件，使用where子句
2.条件可以多个，使用逻辑操作符和（）进行条件的逻辑整合
3.where子句的优先级别最高
4.比较操作表达式由操作符和值组成
常见的操作符有三类：
1：逻辑比较操作符
= > < >= <= !=
需求：查看员工工资小于1000的员工id和名字
select id,last_name,salary
from s_emp
where salary < 1000;
查看员工工资大于1000小于2000的员工id和名字
select id,last_name,salary
from s_emp
where salary > 1000 and salary < 2000;

select id,last_name,salary
from s_emp
where salary between 1000 and 2000;

查看员工号1,3,5,7,9员工的工资
select id,last_name,salary
from s_emp
where id in(1,3,5,7,9);
like：模糊查询，即值不是准确的值的时候使用
通配符，即可以代替任何内容的符号
%：通配0到多个字符
_: 当且仅当通配一个字符
转义字符：
默认为\,可以指定 指定的时候用escape 符号指明即可，转义字符
只能转义后面的一个字符
需求：查看员工名字以C

字母开头的员工的id、工资。 select id,last_name,salary
from s_emp
where last_name like 'C%';
练习：查看员工名字长度不小于5，且第四个字母为n字母的员工id和工资
select id,last_name,salary
from s_emp
where last_name like '___n_%';
需求：查看员工名字中包含一个_的员工id和工资
select id,last_name,salary
from s_emp
where last_name like '%_%';
需求：查看员工提成为空的员工的id和名字（is null）
select id,last_name,commission_pct
from s_emp
where commission_pct is null;

select id,last_name,nvl(commission_pct,0) com
from s_emp
where commission_pct is null;
需求：查看员工所在部门中以'_'开头的部门名称，id
select id,name
from s_emp
where name like '\_%' escape '\';
逻辑操作符
and:与运算
or:或运算
null:非运算
注意：and的运算级别比or高
需求：查看员工部门id为41且职位名称为Stock Clerk（库存管理员）的员工id和名字
select id,last_name
from s_emp
where dept_id=41 and title='Stock Clerk';
需求：查看员工部门id为41或者44号且工资大于1000的员工id和名称
select id,last_name,salary,dept_id
from s_emp
where (dept_id=41 or dept_id=44) and salary > 1000;
需求：查看员工部门id为41且工资大于1000或者部门id为44的员工id和名称
select id,last_name,salary,dept_id
from s_emp
where dept_id=41 and salary > 1000 or dept_id=44;
第四章：单行记录（每一行）操作函数
dual：哑表 测试用
语法：
select 函数运算表达式
from tabname
where 函数运算表达式 逻辑运算表达式
1.字符操作函数
lower:将字符转换成小写
select lower('Sql Course') from dual;
upper:将字符转换成大写
select upper('Sql Course') from dual;
initcap:首字母大写其他小写
select initcap('sql course') from dual;
length:统计字符串的长度
select length('sql course') from dual;
substr:从字符串找出某几个元素
select substr('SqlCourse',1,3) from dual;
需求：查看员工名字为chang的员工的id和薪资，chang不区分大小写，即（CHANG Chang chang）都符合。
select id,salary,last_name
from s_emp
where lower(last_name)='chang';
需求：查询员工的名字并且以大写的形式显示，并且名字的长度大于6字符
select upper(last_name)
from s_emp
where length(last_name) > 6;
2.数字函数 44.66
0 40 45 44.7 44.66
round:四舍五入 trunc:直接舍去 mod:取余
select round(44.66,0) from dual;
3.日期函数
需求：查看系统时间
4.转换函数
to_char:将日期等一些格式转换成字符串类型

千年虫 00-50 1900-1950
51-99 1951-1999
00-50 1900-1950

第五章：多表查询
所谓多表查询，又称表联合查询，即一条语句涉及到的表有多张，数据通过特定的连接进行联

合显示。
多表查询连接方式：
等值连接
笛卡尔积:A={a,b} B={1,2,3}
希望n张表进行连接需要几条连接条件（n-1）
语法：
select tab1.col_name,tab2.col_name....
from tab1,tab2
where tab1.col_name=tab2.col_name and
tab1.col_name=tab2.col_name....
需求：查看每个员工的id，last_name以及部门名称(25条)
select e.id,https://www.360docs.net/doc/8117586005.html,st_name,https://www.360docs.net/doc/8117586005.html,
from s_emp e,s_dept d
where e.dept_id=d.id;
练习：查看每个员工的id，last_name以及部门名称和所属区域名称.(25条)
select e.id,https://www.360docs.net/doc/8117586005.html,st_name,https://www.360docs.net/doc/8117586005.html,,https://www.360docs.net/doc/8117586005.html,
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id=r.id;
需求：查询部门名称包含sa的员工姓名薪水
练习：查看员工工资小于1000的员工的id，名字和区域名称(7条)
select e.id,https://www.360docs.net/doc/8117586005.html,st_name,https://www.360docs.net/doc/8117586005.html,,e.salary
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id=r.id and
e.salary < 1000;
不等连接
使用的是除=以外的操作符号的多表查询
例如: between.. and..
select tab1.col_name
from tab1
where tab1.col_name between min_value and max_value
需求：查询欧洲销售部门的薪水在1000到2000的员工信息(1条)
select e.id,https://www.360docs.net/doc/8117586005.html,st_name,e.salary,https://www.360docs.net/doc/8117586005.html,,https://www.360docs.net/doc/8117586005.html,
from s_emp e,s_dept d,s_region r
where https://www.360docs.net/doc/8117586005.html,='Europe' and https://www.360docs.net/doc/8117586005.html,='Sales' and (e.salary between 1000 and 2000) and e.dept_id=d.id and d.region_id=r.id;
需求：查看test1表中的数据，限制条件test1表中的id列在一个区间范围之间，但是这个区间在不断的变化
练习：创建一张员工工资等级表
create table s_grade(
id number(7) primary key,
name varchar2(10),
minsal number(7),
maxsal number(7)
);
insert into s_grade values(1,'蓝领',1000,2000);
insert into s_grade values(2,'白领',2000,3000);
insert into s_grade values(3,'金领',3000,4000);
insert into s_grade values(4,'红领',4000,5000);
练习:查询员工的工资的等级的名称
select e.salary,https://www.360docs.net/doc/8117586005.html,st_name,https://www.360docs.net/doc/8117586005.html,
from s_emp e,s_grade g
where e.salary between g.minsal and g.maxsal;
练习：查询部门名称是5位，该部门员工的薪水不等于1500，并按员工的薪水降序排序
select e.id,https://www.360docs.net/doc/8117586005.html,st_name,e.salary,https://www.360docs.net/doc/8117586005.html,
from s_emp e,s_dept d
where https://www.360docs.net/doc/8117586005.html, like '_____' and e.salary !=1500 and e.dept_id=d.id
order by e.salary desc;
外连接:
当一个表的记录在另外一张表中不存在的时候，我们依旧需要显示，使用外连接即可。
外连接分为右外连接和左外连接
右外连接的语法：
select tab1.col_name,tab2.col_name...
from tab1,tab2
where tab1.col_name(+)=tab2.col_name;

select tab1.col_name,tab2.col_name...
from tab1 right join tab2 on tab1.col_name=tab2.col_name;
意义：右外连接表示右边表（=号右边的表）中的记录在左边表中不存在的时候，右边表的记录依旧显示。
需求：查询所有员工所在部门的名称，但是需要把所有部门给查询出来
insert into s_dept values(100,'br

iup',null);
select https://www.360docs.net/doc/8117586005.html,,https://www.360docs.net/doc/8117586005.html,st_name
from s_emp e,s_dept d
where e.dept_id(+)=d.id;
左外连接的语法：
select tab1.col_name,tab2.col_name...
from tab1,tab2
where tab1.col_name=tab2.col_name(+);
意义：左外连接表示左边表（=号左边的表）中的记录在右边表中不存在的时候，左边表的记录依旧显示。
insert into s_region values(100,'briup');
需求：查询所有员工所在部门的名称，但是需要把所有员工给查询出来
select https://www.360docs.net/doc/8117586005.html,,https://www.360docs.net/doc/8117586005.html,
from s_dept d,s_region r
where d.region_id=r.id(+);
标准的sql语句
full join on....
left join on....
right join on....
需求：查询部门所在的区域信息，没有部门号的区域及所有的部门都查询出来
select https://www.360docs.net/doc/8117586005.html,,r.id
from s_dept d full join s_region r on d.region_id=r.id;
自连接
实质就是一张表当多张表用，即一张表中的某列的值取决于自己的某一列
语法：
select tab1.col_name,tab1.col_name
from tab1
where tab1.col_name=tab1.col_name
需求：查看每一个员工的经理名字
select https://www.360docs.net/doc/8117586005.html,st_name,e.dept_id
from s_emp e,s_emp m
where e.manager_id=m.id
集合连接
union:将上下结果取并集，去除掉重复的记录（重复的只显示一次）
union all：将上下结果全部显示
minus：取差集 A-B
intersect：取交集
rownum：记录行号只等于1可以小于任何正整数不能大于任何正整数
rowid：存放每条记录在磁盘上的位置
需求：查询员工表中第三条到第六条记录
select last_name,salary,id
from s_emp
where rownum <=6
minus
select last_name,salary,id
from s_emp
where rownum <=2;

第六章：组查询
所谓组查询即将数据按照某列或者某些列相同的值进行分组，然后对改组的数据进行组函数运用
语法：
select ....
from....
where...
group by col_name...
having ...
order by....
group by col_name:即将数据按照col_name相同值进行分组
组函数常见有5个：
avg：求平均值
count：求总数
max：最大值
min：最小值
sum：求和
需求：查看所有部门的部门工资，按照部门工资的降序排序
select dept_id,sum(salary)
from s_emp
group by dept_id
order by sum(salary) desc;
练习：查询每个部门的平均工资
select dept_id,avg(salary)
from s_emp
group by dept_id
order by avg(salary) desc;
查看各个部门的员工数
select dept_id,count(*)
from s_emp
group by dept_id
order by count(*);
需求：查看部门平均工资大于1000的部门id
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary) > 1000;
练习：查询各个部门各个职称的平均薪水和最大薪水,并且平均薪水大于500的部门id
select dept_id,avg(salary),max(salary),title
from s_emp
group by dept_id,title
having avg(salary) > 500;
注意1：没有出现在group by子句中和组函数中的列，不能出现在select子句中
注

意2：当group by子句中出现多列的时候，表示按照从左至右的顺序进行分组，即先按照第一列分组，然后再第一列分好的组里面 按照第二列进行分组，以此类推。
注意3：如果限制条件中出现了组函数，该条件必须放到having子句中，不能放在where子句中
需求：查询title中不包含vp字符串的每个职位的平均薪水，并对平均薪水进行降序排列并且每个职位的总薪水大于5000
select title,avg(salary),sum(salary)
from s_emp
where lower(title) not like '%vp%'
group by title
having sum(salary) > 5000
order by avg(salary) desc;
第七章：子查询（嵌套查询）
所谓子查询，即一个select语句中嵌套了另外的一个或者多个select语句
子查询出现情况一：
比较值不确定，需要另外一个select语句执行后才能得到，使用子查询
语法：
select ....
from ...
where .... 逻辑运算符(
select ...
from ...
where...)
having...
order by...
需求：查看工资大于Chang员工工资的所有员工的id和名字。
第一步：确定需求
第二步：写父句，写子句
父句：select id ,last_name
from s_emp
where salary > Chang员工的工资
子句：求Chang员工的工资
select salary
from s_emp
where lower(last_name)="chang";
第三步：嵌套

练习1：查看职位名称和名字为Chang的员工一样的所有员工id和名字
父句：select id,last_name,title
from s_emp
where title=名字为chang员工的职位
子句：求chang员工的职位
select title
from s_emp
where lower(last_name)="chang";
嵌套：select id,last_name,title
from s_emp
where title = (
select title
from s_emp
where lower(last_name)="chang"
);
练习2：查看员工工资小于平均工资的所有员工的id和名字
父句：select id,last_name,salary
from s_emp
where salary < 平均工资
子句：求平均工资
select avg(salary)
from s_emp;
嵌套: select id,last_name,salary
from s_emp
where salary < (
select avg(salary)
from s_emp
);
练习3：查看部门和名字为Chang的部门相同或者区域ID为2的部门相同的部门所有员工id和名字(6行)
父句：
select id,last_name,dept_id
from s_emp
where dept_id = ?
or
dept_id in ?;
子句1：
select dept_id
from s_emp
where last_name = 'Chang'
子句2：
select id
from s_dept
where region_id = 2;
嵌套：select id,last_name,dept_id
from s_emp
where dept_id=(select dept_id
from s_emp
where last_name ='Chang')
or dept_id in(select id
from s_dept
where region_id=2);
练习4：查看部门平均工资大于32号部门平均工资的部门id
父句：(2行)
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary) > ?
子句：
select avg(salary)
from s_emp
where dept_id = 32
嵌套：select dept_id,avg(sala

ry)
from s_emp
group by dept_id
having avg(salary) > (select avg(salary)
from s_emp
where dept_id =32);
练习5：查询工资大于smith所在部门平均工资的员工的(14行)
父句：
select id,last_name,salary
from s_emp
where salary > ?
子句：
select avg(salary)
from s_emp
where dept_id = ?

子子句：
select dept_id
from s_emp
where last_name = 'Smith'
嵌套
select id,last_name,salary
from s_emp
where salary > (
select avg(salary)
from s_emp
where dept_id = (
select dept_id
from s_emp
where last_name = 'Smith')
);
课后练习：
练习：
1.查看薪资大于Chang员工薪资的员工信息（21行）
2.查看薪资大于Chang员工薪资或者所在部门在3号区域下的员工的信息（22行）
3.查看薪资大于Chang所在区域平均工资的员工信息（16行）
4.查看薪资高于Chang员工经理薪资的员工信息（12行）
5.查看薪资大于Chang员工经理的经理所在区域的最低工资的员工的信息(18行）
6.查看客户负责员工中工资大于Chang员工的工资的员工信息（5行）
7.查看Chang员工所在部门其他员工薪资总和(1300)
8.统计不由11号和12号员工负责的客户的人数（8）9
9.查看部门平均工资大于Chang所在部门平均工资的部门信息(10行）
10.查看员工的id，last_name，salary，部门名字，区域名字，这些员工有如下条件：薪资大于Chang所在区域的平均工资或者跟Chang员工不在同个部门(24)
需求：查看部门平均工资最低的部门id



练习：
1.查看薪资大于Chang员工薪资的员工信息（21行）
select id,last_name,salary from s_emp
where salary>(select salary
from s_emp
where last_name='Chang') ;

2.查看薪资大于Chang员工薪资或者所在部门在3号区域下的员工的信息（22行）
select id,last_name,salary,dept_id from s_emp
where salary>(select salary
from s_emp
where last_name='Chang')
or dept_id in(select id
from s_dept
where region_id=3);

3.查看薪资大于Chang所在区域平均工资的员工信息（16行）
select id,last_name,salary from s_emp
where salary>(select avg(salary)
from s_emp
where dept_id in(select id
from s_dept
where region_id=(select d.region_id
from s_emp e,s_dept d
where d.id=e.dept_id and https://www.360docs.net/doc/8117586005.html,st_name='Chang')

)
);
4.查看薪资高于Chang员工经理薪资的员工信息（12行）
select id,last_name,salary from s_emp
where salary>(select salary
from s_emp
where id= (select manager_id
from s_emp
where last_name='Chang'));
5.查看薪资大于Chang员工经理的经理所在区域的最低工资的员工的信息(18行）
select id,last_name,salary from s_emp
where salary>(select min(salary) from s_emp
where dept_id in(select id from s_dept
where region_id = (select region_id from s_dept
where id=(select dept_id from s_emp
where id=(select manager_id from s_emp
where id=(select manager_id from s_emp
where last_name='Chang')
)
)
)
)
);
6.查看客户负责员工中工资大于Chang员工的工资的员工信息（5行）
select id,last_name,salary from s_emp
where id in( select sales_rep_id
from s_customer
group by sales_rep_id)
7.查看Chang员工所在部门其他员工薪资总和(1300)
方法一：
select sum(salary)
from s_emp
where id=(select id from s_emp
where dept_id=(select dept_id from s_emp where last_name='Chang')
minus
select id from s_emp
where last_name='Chang');
方法二：
select sum(salary)
from s_emp
where dept_id=(select dept_id from s_emp
where last_name='Chang')
and last_name !='Chang';
8.统计不由11号和12号员工负责的客户的人数（8）9 ??????????????????????????????????
select count(*)
from s_customer
where sales_rep_id not in(select id from s_emp
where id in(11,12));
9.查看部门平均工资大

于Chang所在部门平均工资的部门信息(10行）
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>(select avg(salary) from s_emp
where dept_id=(select dept_id from s_emp
where last_name='Chang'));
10.查看员工的id，last_name，salary，部门名字，区域名字，这些员工有如下条件：薪资大于Chang所在区域的平均工资
或者
跟Chang员工不在同个部门(24)
select e.id,https://www.360docs.net/doc/8117586005.html,st_name,e.salary,https://www.360docs.net/doc/8117586005.html,,https://www.360docs.net/doc/8117586005.html,
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id=r.id
and (salary>(select avg(salary) from s_emp
where dept_id in(select id from s_dept
where region_id=(select region_id from s_dept
where id=(select dept_id from s_emp where last_name='Chang'))))
or dept_id not in(select dept_id from s_emp
where last_name='Chang')
);






第八章：数据建模和数据库的设计
数据库设计的步骤
第一步需求：需求分析
抽象实体：域对象、行为对象
提炼属性：
分析关系：一对一，一对多， 多对多、maybe/must be
主外键的关系
订单，订单的明细表
主键：一张表中必须有主键
并且只能有一个主键
外键：一张表中可以有多个外键，也可以没有外键，外键依赖于另外一张的主键或者是唯一性的属性
第二步：ER模型图的绘制

ER模型：用圆角的矩形来表示实体
第一行是实体名，并且是大写的形式
符号：# 代表唯一性
* 代表非空
o 代表任意
| 代表联合主键
ER模型图中的外键不会显示的标识出来，但是能够通过关系来确定外键

对于一对一的关系：外键可以建在任何一方
对于一对多的关系：外键必须建在多的这一方
对于多对多的关系：看成两个一对多的关系或者建一张桥表
桥表
学生表：id，name 课程表：id，name
s_c：student_id,course_id
第三步：创建文档
表实例图
第四步：建表
第五步：测试
第九章 creating table
CREATE TABLE [schema.模式]table
(column datatype
[DEFAULT expression/expr]
[column_constraint],
...
[table_constraint]);
[schema.]:该值就是用户名
如果没有这个，就把该表创建在
你登录时候的用户下
DEFAULT expr：如果你插入数据的数据，
该列没有传入任何的值，存入数据库的
值就是expr
取表名的规定：
1：必须以字母开头
2：长度 1到30字符之间
3

：有字母，数字，_,$,#组成
4：在同一用户下，不能出现同名的表
5：不能oracle中的保留字(select alter delete)

约束：表级约束:所有列的最后面
列级约束:紧跟在column的后面
联合主键，联合外键，联合唯一必须
使用我们的表级约束
非空not null必须使用列级约束
其他的情况，可以使用这两种约束的
其中一种
主键约束：primary key
外键约束：foregin key
非空约束：not null
选择性约束：check
唯一性约束：unique
创建student表
create table students(
id number(7) constraint stud_id_pk primary key,
name varchar2(10) constraint stud_name_nn not null,
age number(7) default 0,
ic char(18) constraint stud_ic_uq unique,
gender varchar2(10) check (gender in('female','male'))
);
查询student表的约束条件
约束条件默认名字为(SYS_COn....)
select constraint_name,constraint_type
from user_constraints
where table_name='STUDENT';
给约束条件起个别名

数据类型：
数字类型：number
number(7):有7为有效数0000000-9999999
number(5,2):有5为有效数，
包含2位小数
最大值999.99
字符类型：varchar,varchar2:可变长度的字符串
char：不可变长度的字符串
varchar2(10):hello
char(10):hello_____
定义定长的字符串，使用char
char（18）：身份证号
clob：最大的空间为2G
日期类型：date
二进制类型：blob
主键约束：

age number(7) default 0;
id number(7) constraint
test_id_pk primary key,
主键的列级约束的格式：
column typedate constraint
constraintName constraintType
主键的表级约束的格式:
constraint constraintName
constraintType(column)
constraint test_id_pk
primary key(id),

非空约束：not null
非空的列级约束的格式：
column datetype constraint
constraintName not null
如：
name varchar2(20) constraint
test_name_nn not null,
选择性约束：check
选择性约束的列级约束
column datetype constraint
constraintName check (
column in(list)
)
如：
gender varchar2(10) constraint
test_gender_ck check(
gender in('male',
'female')
)
选择性约束的表级约束的格式
constraint constraintName
column check(column in(list))

唯一性约束：unique
唯一性约束的列级约束的格式:
column dateType constraint
constraintName unique
如：phone char(11) constarint
test_phone_uk unique
唯一性约束的表级约束的格式:
constraint constraintName
unique(column)
如：constraint test_phone_uk
unique(phone)

外键约束：foreign key
外键约束的列级约束的格式:
column dateType constraint
constraintName references
pktableName(pkcolumn)
外键约束的表级约束的格式:
constraint constraintName foreign
key(column) references
pktableName(pkcolumn)
联合外键的格式：
constraint constraintName

foreign
key(column1,column2...)
references
pktableName
(pkcolumn1,pkcolumn2...)
注意：联合外键必须是另外一张的联合
主键。
联合主键的格式：
constraint constraintName primary
key(column1,column2...)
联合唯一的格式：
constraint constraintName unique
(column1,column2...)

学生表（id,name,age,gender,address
phone,classId,course_id）
老师表（id,name,salary,age）
课程表（id,name,time,teacher_id）

create table student(
id number(7) primary key,
name varchar2(10) not null,
age number(5) default 0,
gender varchar2(10) check (gender in('male','female')),
address varchar2(20),
phone char(11) unique,
classId number(5)
);
create table course(
id number(7) primary key,
name varchar2(20) not null
);
create table s_c(
id number(7),
student_id number(7)
constraint s_c_id1_fk
references student(id),
couser_id number(7)
constraint s_c_id2_fk
references course(id),
constraint s_c_pk3
primary key(id,student_id,
couser_id)
);


select table_name from user_tables

select count(*) from dictionary;
626张

select object_name
from user_objects
where object_type='TABLE'

desc user_objects;
查询数据对象的类型：
select distinct object_type
from user_objects;
查看约束：
select constraint_name
from user_constraints;
查看约束建在哪一列：user_cons_columns
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'S_EMP';


第十一章：DML语言
insert:
insert into
tableName[(column1,column2..)]
values(value1,value2..)

create table insert_test(
id number(7) primary key,
name varchar2(20) not null,
age number(5) default 0
)
//插入一条记录
insert into insert_test(id,name) values(
2,'test2');
注意：主键约束，非空唯一
外键约束


insert into insert_test2
values(2,3);

create table table1
as
select id,last_name,salary,dept_id
from s_emp
where dept_id=42;


insert into table1
select id,last_name,salary,dept_id
from s_emp
where dept_id=41;

更新update:
update table1 set salary=5000
where dept_id=41;

update table1 set id=2
where last_name='Ngao'

(1,null)
(2,1)
update insert_test2 set id=1
where test1_id=1;
注意：修改的时候注意主键约束和外键约束
update insert_test2 set test1_id=5
where id=1;


update insert_test set
name='briup',age=20
where id=1;

删除delete
delete [from] tableName
[where column=value]

delete table1
where id=1;

delete,truncate:
共同点：都是删除表中的数据，不删除
表的结构
不同点：delete可以回滚,DML
truncate不可以回滚,DDL
truncate效率高
drop table tableName:删除表中的数据和
表结构
不可以回滚的
commit;
insert into insert_test2
values(5,2);
DML语言：不能自动提交

事务：
为什么要使用事务？
会员注册｛
try{
//提交方式手动的
更新用户表
更新

等级表
更新积分表
}catch(Exception e){
//回滚事物
}
｝
会员注册这件事成功了

事务的启动：
1：sql*plus开启
2：一个事物结束就代表另外
一个事物的开始
事物的结束：
1：sql*plus关闭
2：DDL，DCL语言就提交事物
3：exit,Errors,system crash
事务四大特性：
原子性：要么一次性成功，
要么失败
一致性：事物前后数据一致
隔离性：并发访问的事务，是不能
看到别的事物的一些信息
持久性：数据保存到数据库中
commit;
insert into insert_test2
values(9,2);
savepoint a;
insert into insert_test2
values(10,2);
savepoint b;
insert into insert_test2
values(11,2);
rollback to b
select * from insert_test2
commit;
注意：事物提交之后是不能回滚
rollback to a;//出错

truncate和delete的区别？
undo
隐式的提交
DML语言
为什要使用事物？
会员注册{

}

第十二章：alter tables and contraints


ALTER TABLE table
ADD (column datatype
[DEFAULT expr][NOT NULL]
[, column datatype]...);
1:增加约束的时候，只能增加非空约束
并且在增加这种非空约束的时候，表中
必须是没有数据的
alter table insert_test2
add (name varchar2(20));
///增加一个age属性
alter table insert_test2
add (age number(5));
删除某一个字段：
alter table tableName
drop column columnName
//删除age属性
alter table insert_test2
drop column age
//删除name属性
alter table insert_test2
drop column name
修改的属性
alter table tableName
modify (columnName datatype
[default expr][not null])
//修改name not null
alter table insert_test2
modify (name varchar2(20) not null)

增加约束
ALTER TABLE tableName
ADD [CONSTRAINT constraint]
type (column);
//name变为唯一性约束
alter table insert_test2
add constraint test_name_uk
unique(name)
//删除约束
alter table insert_test2
drop constraint test_name_uk
//使约束失效disable
alter table insert_test2
disable constraint test_name_uk
//使约束生效enable
alter table insert_test2
enable constraint test_name_uk
删除表：
DROP TABLE table
[CASCADE CONSTRAINTS];

重命名：rename
表，列重命名
对对象名
rename oldName to newName
//insert_test2 test3
rename insert_test2 to test3
对列名：
alter table tableName
rename column columnName
to newcolumnName
//将insert_test2中name改为last_name
alter table insert_test2
rename column name
to last_name
删除表
TRUNCATE TABLE s_item;
增加注释：
COMMENT ON TABLE insert_test2
IS 'test Information';
查询用户增加的注释
select comments
from USER_TAB_COMMENTS
where table_name='INSERT_TEST2';

第十三章 Creating Sequences
序列：用来生成主键的值
oracle所特有的。

CREATE SEQUENCE name
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CAC

HE n | NOCACHE}]
INCREMENT BY n：步长,序列以n增长
start with n:序列的开始值n
maxvalue n:最大值
nomaxvalue:没有最大值
minvalue n:最小值
nominvalue :没有最小值
cycle/nocycle:是否循环
cache/nocache:是否使用缓冲区

create sequence s1
start with 10
increment by 2

nextval:取序列的下一个值
currval:取序列的当前值
select s1.nextval from dual;
再执行下面这句
select s1.currval from dual;

10,12,14.......

修改序列：
ALTER SEQUENCE sequence
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
alter sequence s1
increment by 10
修改序列注意不能修改序列的开始值

使用序列：
insert into insert_test2
values(s1.nextval,1,'test');
Hilo来生成id
序列来生成id

dba用户给该用户一个
create sequence的权限

删除序列：
drop sequence sequenceName;








create table title(
id number(7) primary key,
title varchar2(20) not null,
description varchar2(20)
not null,
)


create table title_copy(
id number(7),
status varchar2(20) not null,
title_id number(7) not null,
foreign key(title_id)
references title(id),
primary key(id,title_id)
)

create table rental(
book_date date,
member_id number(7) not null
foregin key(member_id)
references member(id),
title_copy_id1 number(7)
not null,
title_copy_id2 number(7)
not null,
foreign key(title_copy_id1,
title_copy_id2) references
title_copy(id,title_id),
primary key(book_date,
title_copy_id1,title_copy_id2)
)


40%经常用的
60%不用
视图
增加检索的效率
控制访问的权限，工资
工资单--信封


创建一个视图：从员工表查询出部门号41
的员工的id,last_name,salary
create view view1
as
select id,last_name,salary
from s_emp
where dept_id=41;

conn system/密码
show user
grant create view to 用户名
conn 用户名/密码

create or replace view view1
as
select id,last_name,salary,dept_id
from s_emp
where dept_id=41
with read only;

select * from view1;
delete from view1
where last_name='Ngao';

create force view view2
as
select *
from s_emp1;

desc view2;

select view_name from user_views;

select * from view1;
insert into view1 values(9998,'briup1'
,9800,42);
select last_name
from s_emp
where id=9999;


create view view3
as
select id,last_name name
from s_emp
where dept_id=41;

desc view3;

创建视图：包含
每个部门的最低工资，最高工资
，平均工资
create or replace view view4
as
select dept_id,min(salary) m,
max(salary) a,avg(salary) v
from s_emp
group by dept_id
order by v

drop table tableName
drop view viewName
drop view view1;

s_dept name
创建视图：
包含最高工资，最低工资，平均工资，该
该相同部门名称的总人数
按照部门名称分组查询
并按平均工资降序排序

create or replace view view1
as
select min(e.salary) m,max(e.salary) b,
avg(e.salary) a,count(*) c
from s_emp e,s_dept d
where e.dept_id=d.id
group by https://www.360docs.net/doc/8117586005.html,
order by avg(e.salary) desc










rowid
B*tree
id,last_name
create index last_name_index
on s_emp(id,last_name)

select index_name from user_indexes

SELECT ic.index_name,ic.column_name,
ic.column_position col_pos,
ix.uniqueness
FROM user_indexes ix,
user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = 'S_EMP';
and ic.index_name='LAST_NAME_INDEX'


drop index last_name_index


create database name
use name
create table ...

XE
create table
create view
create index

系统权限：连接到数据库，创建对象的
权限等
对象权限：操作对象的权限

create session
create table
create view
.....

角色：分配好一些权限给角色
平民：
皇帝：

connect
resource
dba

修改密码：
当前用户修改当前用户的密码
password
具有dba权限的用户可以修改每个用户
的密码
conn system/system
alter user briup1 identified by briup
identified by后面加上你要修改的新密码
conn briup1/briup1
conn briup1/briup

conn

grant update(name),insert
on s_dept
to briup1
回收权限：
revoke insert
on s_dept
from briup1;

当前用户briup中有s_dept
用户briup1,briup把select
s_dept这表的权限给briup1
briup1又把select，s_dept
给了用户briup2
grant select
on s_dept
to briup1
with grant option;
当前用户把select回收
briup2还有select的权限？



什么要使用主键与外键：
用户注册的表
id, name, phone......
1 ,zhangsan,189********
2 ,lisi ,23987900083
3, terry, 88993430998
外键：连接数据库中的对象
积分表
id,sorce,user_id