oracle
1.oracle或者关系型数据库中的对象
表 由行和列组成的存储单位
视图 在逻辑上是由一个或多个表的数据子集组成
序列 用来自动生成主键值
索引 用来提高一些查询的性能
同义词 对象的替换名称
程序单元 在PL/SQL中声明的 存储过程,函数,包
表 由行和列组成的存储单位
视图 在逻辑上是由一个或多个表的数据子集组成
序列 用来自动生成主键值
索引 用来提高一些查询的性能
同义词 对象的替换名称
程序单元 在PL/SQL中声明的 存储过程,函数,包
2.sql sqlplus pl/sql的区别
sql 一种和数据库服务器进行通信的语言
sqlplus 一种和oracle数据库进行通信的工具,它可以用来执行 sql和pl/sql 命令行模式
DML,DDL,DCL语句都是sqlplus语句
pl/sql procedure language oracle特有的过程化语言 因为sql不具备过程控制功能
sqlplus 一种和oracle数据库进行通信的工具,它可以用来执行 sql和pl/sql 命令行模式
DML,DDL,DCL语句都是sqlplus语句
pl/sql procedure language oracle特有的过程化语言 因为sql不具备过程控制功能
3.sql命令的分类:
查询:select
数据操作语言:DML:insert.update.delete (表中数据层面操作)
DDL:create.alter.drop.truncate (表层面操作)
事务控制:commit.rollback.savepoint
数据控制语言:DCL:grant.rovoke
查询:select
数据操作语言:DML:insert.update.delete (表中数据层面操作)
DDL:create.alter.drop.truncate (表层面操作)
事务控制:commit.rollback.savepoint
数据控制语言:DCL:grant.rovoke
4.关系型数据库的数据的完整性约束:(4个方面)
(目的:关系型数据库中的数据有意义,使用更加方便)
1)实体性约束
主键:表中一行数据唯一标识符
(非空且唯一)
2)外键约束
一个表中的外键指向另一个表中的唯一列
外键:多张表之间的关系
(可以为空)
3)列的约束
必须定义数据类型(数值类型,字符类型,(oracle)日期类型)
4)用户自定义约束
gender,phone(长度11位)
(目的:关系型数据库中的数据有意义,使用更加方便)
1)实体性约束
主键:表中一行数据唯一标识符
(非空且唯一)
2)外键约束
一个表中的外键指向另一个表中的唯一列
外键:多张表之间的关系
(可以为空)
3)列的约束
必须定义数据类型(数值类型,字符类型,(oracle)日期类型)
4)用户自定义约束
gender,phone(长度11位)
5.表
列:column 属性
行:tuple 元祖
列:column 属性
行:tuple 元祖
6.where是筛选
***************************************************************8
查询:
1.desc +表名====查看表结构
2.
char() 定长字符串 char(10)
varchar() 变长字符串 varchar(10)
varchar2() oracle特有的数据类型
char() 定长字符串 char(10)
varchar() 变长字符串 varchar(10)
varchar2() oracle特有的数据类型
3.COL 列名 FOR 格式
4.select 语句排序 (order by)
order by:desc 降序
asc 升序
order by可以添加的子项:
1)列名(不论在select子句中是否出现过都可以排)
2)别名
3)表达式
4)select子项编号
order by:desc 降序
asc 升序
order by可以添加的子项:
1)列名(不论在select子句中是否出现过都可以排)
2)别名
3)表达式
4)select子项编号
5.select 条件限定 (where)
where: and or 否定
where: and or 否定
6.去重 distinct
放在select后面 select distinct
放在select后面 select distinct
7.顺序:
select 2 列筛选
from 1 先把整张表格拿出来
where 3 行筛选
orser by 4
select 2 列筛选
from 1 先把整张表格拿出来
where 3 行筛选
orser by 4
8.转义字符
where last_name like '\_%' escape '\';
where last_name like '\_%' escape '\';
9.Function函数
单行函数:调用函数时,输入数据进入一行,返回一行
多行函数: 若干行
单行函数:字符操作:lower,upper,initcap,concat,substr(name,start,lengrh),length,nvl
数值操作: round(,-1),trunc,mod
日期操作: sysdate,systimestamp 无参数的单行函数:伪列
rownum,rowid
months_between,add_months,next_day,last_day
round,trunc
数据类型转化操作:to_char,to_number,to_date('YYYY,MM,D,DD,DDD,YEAR,MONTH,ddsp,DY,HH24:MI:SS AM')
多行函数:group 组函数
数值操作: round(,-1),trunc,mod
日期操作: sysdate,systimestamp 无参数的单行函数:伪列
rownum,rowid
months_between,add_months,next_day,last_day
round,trunc
数据类型转化操作:to_char,to_number,to_date('YYYY,MM,D,DD,DDD,YEAR,MONTH,ddsp,DY,HH24:MI:SS AM')
多行函数:group 组函数
10.存储过程和函数的区别
11.连接查询
目的:多表查询(多个二维表)
1)内连接,自然连接
等值连接和不等值连接
(=) (不用=)
2) 外连接 (左,右,全(并集))
为避免笛卡尔乘积,建立联系条件个数-1 (最少)
为避免笛卡尔乘积,建立联系条件个数-1 (最少)
3) 自连接:自己的思路方法的多表连接
12.集合操作符
union union all
全外连接 全集(取两次)
minus intersect
大-小 补集 重复值 交集
union union all
全外连接 全集(取两次)
minus intersect
大-小 补集 重复值 交集
13.组函数
where中不可以使用组函数!!!
AVG,COUNT,MAX ,MIN ,STDDEV,SUM,VARIANCE
where中不可以使用组函数!!!
AVG,COUNT,MAX ,MIN ,STDDEV,SUM,VARIANCE
14.子查询
子查询是一个sql语句中嵌套了另外一个select语句
子查询与链接查询的优劣
1) 子查询更好理解,更灵活
2) 链接查询效率更高
子查询是一个sql语句中嵌套了另外一个select语句
子查询与链接查询的优劣
1) 子查询更好理解,更灵活
2) 链接查询效率更高
15.rownum原理 :
因为ROWNUM是对结果集加的一个伪列,即先筛选结果集之后再加上去的一个列
(强调:先要有结果集),并且rownum总是从1开始,故每次筛选剔除掉一行不符合要求的
后整个结果集当做一个新的结果继续筛选,所以每次where结果需要先筛选出来结果,
才能有rownum。
因为ROWNUM是对结果集加的一个伪列,即先筛选结果集之后再加上去的一个列
(强调:先要有结果集),并且rownum总是从1开始,故每次筛选剔除掉一行不符合要求的
后整个结果集当做一个新的结果继续筛选,所以每次where结果需要先筛选出来结果,
才能有rownum。
16.nvl函数
查询员工信息 员工每个月工资(考虑提成比例)
select last_name,salary*(1+nvl(commission_pct,0)/100)
from s_emp;
查询员工信息 员工每个月工资(考虑提成比例)
select last_name,salary*(1+nvl(commission_pct,0)/100)
from s_emp;
17.where 和 having 的区别
where筛选行记录 having分组记录
子句包含单行记录 只包含group by 后面的
执行在前 执行在后
相同:都不允许用行别名
where筛选行记录 having分组记录
子句包含单行记录 只包含group by 后面的
执行在前 执行在后
相同:都不允许用行别名
*******************************************************************************************
DDL:
1.DDL:create.alter.drop.truncate (表层面操作)
2. 表与表的关联关系
(转化成表之后 用外键来表示关系)
外键的位置
1:1
任意一方即可
所以注意 E-R图中的属性,少了外键 创建表记得加上
1:n
外键建立在多的一方
n:n
使用桥表进行连接
联合外键,第三张表(桥表)
构建一张桥表,桥表中两列外键分别指向两张表
(转化成表之后 用外键来表示关系)
外键的位置
1:1
任意一方即可
所以注意 E-R图中的属性,少了外键 创建表记得加上
1:n
外键建立在多的一方
n:n
使用桥表进行连接
联合外键,第三张表(桥表)
构建一张桥表,桥表中两列外键分别指向两张表
3.范式
降低耦合性
降低耦合性
第一范式 行和列交叉的地方只有一个值
第二范式 表中的每一列只能通过主键列去唯一标识
第三范式 表中的非主键列不能依赖于其他非主键列
第三范式 表中的非主键列不能依赖于其他非主键列
create:
4.约束条件:
声明的时候约束,列级约束
创建完表中约束,表级约束
声明的时候约束,列级约束
创建完表中约束,表级约束
主键约束:primary key
既可以写成列级约束,也可以写成表级约束
列级:
creat table customer(
id number(7) constraint cus_id_pk primary key,
name varchar2(25) constraint cus_name_nn not null,
phone varchar2(20) constraint cus_phone_uq unique,
emp_id number(7) constraint cus_emp_id_fk refeerces s_emp(id)
);
表级:
creat table customer(
id number(7),
name varchar2(25),
phone varchar2(20),
emp_id number(7),
primary key(id)
);
既可以写成列级约束,也可以写成表级约束
列级:
creat table customer(
id number(7) constraint cus_id_pk primary key,
name varchar2(25) constraint cus_name_nn not null,
phone varchar2(20) constraint cus_phone_uq unique,
emp_id number(7) constraint cus_emp_id_fk refeerces s_emp(id)
);
表级:
creat table customer(
id number(7),
name varchar2(25),
phone varchar2(20),
emp_id number(7),
primary key(id)
);
外键约束:foreign key ... references
既可以写成列级约束,也可以写成表级约束
creat table customer(
id number(7),
name varchar2(25),
phone varchar2(20),
emp_id number(7),
primary key(id),
foreign key(emp_id)
references s_emp(id)
);
既可以写成列级约束,也可以写成表级约束
creat table customer(
id number(7),
name varchar2(25),
phone varchar2(20),
emp_id number(7),
primary key(id),
foreign key(emp_id)
references s_emp(id)
);
非空约束:not null
只能写成列级约束
只能写成列级约束
唯一约束:
既可以写成列级约束,也可以写成表级约束
creat table customer(
id number(7),
name varchar2(25),
phone varchar2(20),
emp_id number(7),
primary key(id),
foreign key(emp_id)
references s_emp(id),
unique(phone)
);
既可以写成列级约束,也可以写成表级约束
creat table customer(
id number(7),
name varchar2(25),
phone varchar2(20),
emp_id number(7),
primary key(id),
foreign key(emp_id)
references s_emp(id),
unique(phone)
);
检查型约束:check
既可以写成列级约束,也可以写成表级约束
create table student(
gender varchar2(10) check(gender in('男','女'))
)
既可以写成列级约束,也可以写成表级约束
create table student(
gender varchar2(10) check(gender in('男','女'))
)
insert into student
values('你好');//违反约束
values('你好');//违反约束
insert into student
values('男');
values('男');
5.联合主键
多个列组合起来成为主键
多个列的组合唯一,每个列都是非空
create table t_s_tab(
t_id number(7),
s_id number(7),
primary key(t_id,s_id)
)
6.添加默认值default
create table stu(
id number(7),
name varchar2(25) default '李狗蛋'
);
create table stu(
id number(7),
name varchar2(25) default '李狗蛋'
);
7.通过使用子查询创建表
(原表中只有not null约束会复制到新表中)
create table jd1710_emp
as
select id,last_name,salary
from s_emp;
(原表中只有not null约束会复制到新表中)
create table jd1710_emp
as
select id,last_name,salary
from s_emp;
8.数据字典表
1.通过模糊查询
从需求中提取关键字
从dictionary 找到 相关的若干张系统表
1.通过模糊查询
从需求中提取关键字
从dictionary 找到 相关的若干张系统表
select table_name
from dictionary
where upper(table_name) like 'USER%TABLE%';
from dictionary
where upper(table_name) like 'USER%TABLE%';
2.使用desc命令 查看这些表有哪些列
desc user_tables
3.选择合适的咧进行查询
select table_name,num_rows
from user_tables;
from user_tables;
查询briup用户创建的所有视图
select table_name
from dictionary
where upper(table_name) like 'USER%VIEW%';
from dictionary
where upper(table_name) like 'USER%VIEW%';
*******************************************************************************************
DML:insert.update.delete (表中数据层面操作)
1.插入数据
insert into jd1710_emp
values(30,'yuyu',9999);
1.插入数据
insert into jd1710_emp
values(30,'yuyu',9999);
2.更新数据
updete jd1710_emp
set last_name='jack'
where ...
updete jd1710_emp
set last_name='jack'
where ...
3.删除数据
delete from
delete from
4.delete 和 truncate 区别:
truncate和delete作用一样,也是删除表中数据
delete是DML语句,删除数据后需要自己手动的提交事务或者回滚事务
truncate是DDL语句,删除数据后会自动的帮我们提交事务
*******************************************************************************************
事务控制:commit.rollback.savepoint
1.事务:是指作为单个逻辑工作单元执行的一系列操作,要么完整地执行,要么完全地不执行。
一组sql命令的集合(DML,select,DDL,DCL)
代表业务逻辑中的一个行为,这个行为涉及到的sql命令会有多条
一组sql命令的集合(DML,select,DDL,DCL)
代表业务逻辑中的一个行为,这个行为涉及到的sql命令会有多条
2.
一个事务中可以包含多条:DML,select
一个事务中只能包含一条:DDL,DCL
一个事务中可以包含多条:DML,select
一个事务中只能包含一条:DDL,DCL
3.事务的特性ACID:
Atomicity:原子性
不能分割成更小的单位,控制事务的最小单位是事务
Consistency:一致性
数据一致,事务开始之前,执行之后,数据没有改变
Isolation:隔离性
两个事务之间不应该发生信息的交换
事务操作时,操作的数据不应被别人操作
Durability:持久性
事务完成之后事务中所操作的数据会被持久保存
操作的数据上一次事务遗留下的数据
不能分割成更小的单位,控制事务的最小单位是事务
Consistency:一致性
数据一致,事务开始之前,执行之后,数据没有改变
Isolation:隔离性
两个事务之间不应该发生信息的交换
事务操作时,操作的数据不应被别人操作
Durability:持久性
事务完成之后事务中所操作的数据会被持久保存
操作的数据上一次事务遗留下的数据
4.cummit
只有事务提交之后,事务中操作的数据才能看到
1.oracle 需要手动提交实务
2.mysql 自动提交实务
3.jdbc 自动提交事务
只有事务提交之后,事务中操作的数据才能看到
1.oracle 需要手动提交实务
2.mysql 自动提交实务
3.jdbc 自动提交事务
5.哪些行为能触发提交:
DDL,DCL,exit
DDL:create,alter,drop,
DDL,DCL,exit
DDL:create,alter,drop,
6.rollback 事务的回滚
实务没提交之前,都可以用roolback回到事务开启之前的状态
rollback; 会让实务结束(行为执行失败)
rollback to p1; 不会让实务结束,可以继续回滚
实务没提交之前,都可以用roolback回到事务开启之前的状态
rollback; 会让实务结束(行为执行失败)
rollback to p1; 不会让实务结束,可以继续回滚
7.savepoint 回滚点
记录某些时候
当事务结束的时候,该事物
savapoint p1;
rollback to p1;
记录某些时候
当事务结束的时候,该事物
savapoint p1;
rollback to p1;
(*******)
8.alter
添加新的列
alter table jd1710_emp
add(first_name varchar2(25) unique);
8.alter
添加新的列
alter table jd1710_emp
add(first_name varchar2(25) unique);
删除列 考虑外键
alter table jd1710_emp
drop colnum first_name;
alter table jd1710_emp
drop colnum first_name;
修改列,
只能修改数据列的类型 和添加删除not null约束
如果原列中,有null值,不能添加not null 约束。
只能修改数据列的类型 和添加删除not null约束
如果原列中,有null值,不能添加not null 约束。
添加约束
alter table jd1710_emp
add constraint emp_salary_uq
unique(salary);
alter table jd1710_emp
add constraint emp_salary_uq
unique(salary);
删除约束
select constraint_name,
constraint_type,table_name
from user_constraints
order by table_name
;
select constraint_name,
constraint_type,table_name
from user_constraints
order by table_name
;
alter table jd1710_emp
drop constraint SYS_C004196;
drop constraint SYS_C004196;
删除表:
drop table stu;
drop table stu;
修改表名:
rename jd1710_emp to j_emp;
rename jd1710_emp to j_emp;
修改列名:
alter table j_emp
rename column salary ‘’;
alter table j_emp
rename column salary ‘’;
9.清空表空间:
truncate table j_emp;
1.DML
2.delete 不会立刻把数据删除
而是把要删除的数据添加一个标记,
select不会显示出带有标记的数据,从而得到删除数据的效果
truncate table j_emp;
1.DML
2.delete 不会立刻把数据删除
而是把要删除的数据添加一个标记,
select不会显示出带有标记的数据,从而得到删除数据的效果
*******************************************************************************************
序列(oracle特有)
使用方式类似于 Random
new Rondom().
nextInt(10);
使用方式类似于 Random
new Rondom().
nextInt(10);
1.创建序列
2.通过nextval关键字,取得序列中的下一个值
create sequence id_seq
increment by 2
start with 10;
2.通过nextval关键字,取得序列中的下一个值
create sequence id_seq
increment by 2
start with 10;
currval
nextval
nextval
select id_seq.nextval
from dual; 每次取出的值都不一样
from dual; 每次取出的值都不一样
insert into j_emp
values(id_seq.nextval,'hello',1000);
values(id_seq.nextval,'hello',1000);
*******************************************************************************************
视图
(目的:权限的划分,保证视图的安全性)
(目的:权限的划分,保证视图的安全性)
1.视图:是表中的一种映像
(里面的数据从别的表中取出来的虚拟表)
(里面的数据从别的表中取出来的虚拟表)
2.创建视图:
create view 视图名
as
子查询
create view 视图名
as
子查询
3.分类:简单视图,复杂视图
简单视图:创建视图使用的子查询,数据只来自一张表,仅用了where和order by
create view v_emp
as
select id,last_name,salary
from s_emp
where salary>1500;
create view v_emp
as
select id,last_name,salary
from s_emp
where salary>1500;
1)查 可以理解为 v_emp = select语句 视图解释成为一组sql语句
(数据库账号,查看表结构)
(屏蔽数据,权限的划分,保证视图的安全性)
2)增删改
如果对视图进行操作,会影响原表 update v_emp
如果对原表进行操作,会影响视图 set last_name='hello world'
where id=5;
(数据库账号,查看表结构)
(屏蔽数据,权限的划分,保证视图的安全性)
2)增删改
如果对视图进行操作,会影响原表 update v_emp
如果对原表进行操作,会影响视图 set last_name='hello world'
where id=5;
复杂视图:创建视图使用的子查询,子查询使用了group by 或者有连接的查询
create view v_emp_dept
as
select e.id id,e.last_name name,e.salary salary,d.name dept_name
from s_emp e,s_dept d
where e.dept_id=d.id;
create view v_emp_dept
as
select e.id id,e.last_name name,e.salary salary,d.name dept_name
from s_emp e,s_dept d
where e.dept_id=d.id;
1)查
2)不允许删除,修改,增加
2)不允许删除,修改,增加
4.修改视图(直接替换视图就行)
create or replace view v_emp
as
select id,last_name,title
from s_emp;
create or replace view v_emp
as
select id,last_name,title
from s_emp;
5.设置视图为只读
with read only
在简单视图后面加
with read only
在简单视图后面加
create or replace view v_emp
as
select id,last_name,title
from s_emp
with read only;
as
select id,last_name,title
from s_emp
with read only;
6.检查不允许更改建立视图所用的条件列
with check option
with check option
create or replace view v_emp
as
select id,last_name,title
from s_emp
where last_name='jack'
with check option;
as
select id,last_name,title
from s_emp
where last_name='jack'
with check option;
update v_emp
set last_name='tom'
where id=20; 错误
set last_name='tom'
where id=20; 错误
7.删除视图
drop view 视图名
drop view 视图名
*******************************************************************************************
索引
1.索引是提高在列上的一种数据库对象
目的是提高针对此列的查询效率
(平衡二叉树)
目的是提高针对此列的查询效率
(平衡二叉树)
2.判断是否使用索引(oracle会自行判断)
1)基于规则,超过标准值
2)基于代价,考虑性价比,耗费资源多少
1)基于规则,超过标准值
2)基于代价,考虑性价比,耗费资源多少
3.
自动添加索引:主键约束,唯一性约束
手动添加索引:数据大,非空大数值,大范围查询少量数据
create index 索引名
on 表名(列名)
删除索引:drop index
自动添加索引:主键约束,唯一性约束
手动添加索引:数据大,非空大数值,大范围查询少量数据
create index 索引名
on 表名(列名)
删除索引:drop index
*************************************************************************
其他:
1.round 和 trunc
select round(45.67) from dual; 46
select round(45.67,1) from dual; 45.7
select round(45.67,2) from dual; 45.67
select round(45.67,-1) from dual; 50
select round(45.67,-2) from dual; 0
select round(55.67,-2) from dual; 100
9)执行下面的sql语句,看看执行后结果,分析
trunc函数的含义?
select trunc(45.67) from dual; 45
select trunc(45.67,1) from dual; 45.6
select trunc(45.67,2) from dual; 45.67
select trunc(45.67,-1) from dual; 40
select trunc(45.67,-2) from dual; 0
select trunc(55.67,-2) from dual; 0
select round(45.67) from dual; 46
select round(45.67,1) from dual; 45.7
select round(45.67,2) from dual; 45.67
select round(45.67,-1) from dual; 50
select round(45.67,-2) from dual; 0
select round(55.67,-2) from dual; 100
9)执行下面的sql语句,看看执行后结果,分析
trunc函数的含义?
select trunc(45.67) from dual; 45
select trunc(45.67,1) from dual; 45.6
select trunc(45.67,2) from dual; 45.67
select trunc(45.67,-1) from dual; 40
select trunc(45.67,-2) from dual; 0
select trunc(55.67,-2) from dual; 0