Oracle

clown
277 阅读8分钟

“这是我参与8月更文挑战的第2天，活动详情查看：8月更文挑战”

Orcle

环境配置:windows7_sp1, Orcle 11g企业版

表空间

  • 概念

    数据库中最大的逻辑单位

  • 作用

    方便存储管理

  • 分类

    永久性, 临时性, 撤销表空间

  • 默认表空间

    system, sysaun, users, undo(撤销), temp(临时)

--创建表空间loser
	create tablespace loser
	--表空间文件存放位置
	datafile 'c:\loser.dbf'
	--初始大小
	size 100M
	--如果表空间放满了则自动扩展,最大不限制
	autoextend on next 50M maxsize unlimited
--删除表空间
	drop tablespace loser --只删除表空间,不删除表空间文件
	drop tablespace loser including contents and datafiles--删除表空间以及文件

用户

  • 创建用户
create user loser identified by toor default tablespace loser
--如果没有指定默认表空间,Oracle就会将users设为默认表空间,将temp设为临时表空间
  • 修改用户密码
alert user loser identified by 123456

权限

系统权限

  • create session

    连接到数据库

  • create table

    创建表

  • create view

    创建视图

  • create sequence

    创建序列

--赋予用户权限
grant create session to loser
--收回用户权限
revoke create session from loser

对象权限

是指用户对具体的数据库中的对象所拥有的权限,对象权限是针对某个特定的模式对象执行操作的权力

Oracle获取权限的途径有两种:

  • 管理员直接向用户授予权限
  • 管理员将权限授予角色,然后再将角色授予一个或多个用户

数据库中常用的内置角色有:connect, resource, DBA等,一般程序员使用的用户只需要connect和resource就可以了.

--赋予用户权限
grant 权限|角色 to 用户名;
grant connect, resource to loser
--撤销权限
revoke 权限|角色 from 用户名
revoke connect, resource from loser

数据类型

字符数据类型

  • char数据类型

    默认占用1字节,长度为1-2000字节,是固定长度,如果用户输入的不足指定长度时,oracle会自动填充空格

  • varchar2数据类型

    可变长度的字符串,大小为1-4000字节,定义该数据类型时应该指定长度

  • long数据类型

    用来存储可变长度字符数据,最多2GB,当varchar2不能存储时就可以使用long进行存储,设置long类型需要遵守以下几点规则:

    ​ 一个表中只有一列可以为long数据类型

    ​ long列不能定义为唯一约束或主键约束

    ​ long列上不能建立索引

    ​ 过程或存储过程不能接收long数据类型的参数

  • 数值数据类型

    number数据类型可以存储正数,负数,零,定点数和精度为38位的浮点数.

    存储格式为:(p,s)

    ​ number {p = 38, s = 0}

    ​ number(p) {定点数}

    ​ number(p,s){浮点数}

    p表示数字的总位数,在1-38之间.s表示小数点右边的位数,在-84~127之间

  • 日期时间数据类型

    • date数据类型

      oracle有自己的时间格式,它是7个字节固定长度,每个字节分别存储世纪,年,月,日,小时,分,秒

表

create table table_name(
	column datatype,
	column1 datatype
)

alter

可以更改表的结构,可以是修改现有的列,也可以是新添加一列

--更改现有列的定义
alter table table_name modify(column definition)
--向表中添加新列的语法
alter table table_name add(column definition);
--删除表中现有列的语法
alter table table_name drop column column_name;

truncate table

删除表中所有数据,不删除表的结构: truncate table tableName

desc

可以查看表的结构:desc tableName

drop table

将表从数据库中全部删除包括表结构: drop table table_name

数据完整性

实体完整性约束

要求表中的每一行数据都反映不同的实体,不能存在相同的数据行.通过索引 唯一约束 主键约束,可以用来实现表的实体完整性.

表中一列或者几列组合的值能用来唯一的标示表中的每一行,这样的一列或者多列的组合叫做主键, 一个表中只有一个主键

主键的选择应该是尽量指定列数最少的键,并且主键不要经常更新,理想下应该是永远不变的.

域完整性约束

指给定列的输入有效性,通过限制数据类型,检查约束,输入格式 ,外键约束, 默认值 ,非空约束等多种方法,可以用来实现表的域完整性

引用完整性约束

在输入和删除数据行时,引用完整性约束用来保持表之间已定义的关系

自定义完整性约束

用户定义完整性用来定义特定的规则,比如性别不能乱输入只有男女两个选择

使用SQL语句操作约束

创建约束

推荐将建表语句和约束语句分开

alter table table_name add constraint 约束名[推荐:约束类型_表名_列名] 约束类型

alter table student add constraint pk_student_sno primary key(sno)

--添加主键
Student(
	sno varchar2(8) primary key,
    --constraint pk_student_sno primary key(sno)
)

alter table Student add constraint pk_student_sno primary key(sno);

--添加外键
Student(
	cno varchar2(8),
    constraint fk_student_cno foreign key(cno) references Course(cno)
)
alter table Student add constraint fk_student_cno foreign key(cno) references Course(cno);

--检查约束
Student(
	sage int,
    constraint ck_student_sage(sage < 100 and sage > 0)
)
alter table Stduent add constraint ck_student_sage check (sage < 100 and sage > 0);

删除约束

alter table table_name drop constraint 约束名

alter table student drop constraint pk_student_sno

查看约束

select * from user_constraints where table_name = 'STUDENT'

表名必须大写

数据操纵语言(DML/Data Manager Language)

创建表空间, 创建用户, 创建表, 约束的操作这些用到的语言属于DDL(数据定义语言)

为用户授权, 收回权限这些语言属于数据控制语言(DCL)

数据操纵语言用于检索, 插入和修改数据库信息.

insert(插入)

语法:

insert into table_name [(column,column)]
values(value,value);

--demo
insert into Student(sno, sname, sage)
values('20176326','loser',24)
  • column是要插入数据的那一列,必须使用括号将其包含起来.也可以不在括号中写列名,那就代表着需要将所有的属性都赋值.
  • value代表着这个列具体的值
  • 如果表有主键,那么插入的时候必须要插入值

update(更新)

语法:

update table_name
set column_name = value,[column_name = value]
[where condition]

--demo
update Student
set sname = 'clown'
where sname = 'loser'

delete(删除)

语法:

delete [from] table
where [condition]

--demo
delete from Student
where sno = '20176326'
  • delete直接将数据进行物理删除,企业大部分都是使用update进行逻辑删除

select(查询)

语法:

select * 
from table_name
[where condition]
[group by column [having condition]]
[order by column(asc|desc)]

--demo 
select *
from Student
where sno like '2017%'
order by sage asc

select

模糊查询

/**
 * %:匹配任意多个字符
 * _:匹配一个字符
 */
--查找名字中有A的人
select * from emp where ename like '%A%'
--查找名字中第二个字母是A的人
select * from emp where ename like '_A%'
--查找名字中倒数第二个字母是s的人
select * from emp where ename like '%S_'

子查询

在一个查询中,可以嵌套另外一个查询,嵌套的查询语句就是子查询语句,子查询语句获取的值可以被当前查询使用

--通过相关子查询查询员工的部门名称
select e.ename,(select d.dname 
                from dept d 
                where d.deptno = e.deptno) dname
from emp e;

--查询工资比JONES高的所有员工
select * from emp where sal > (select sal from emp where ename = 'JONES')

--和工资大于2000的人在同一个部门的人
select * 
from emp 
where deptno in (select distinct deptno
                 from emp 
                 where sal > 2000)
in

子查询返回的结果有多个时就可以使用in关键字, 它表示只要查询的结果在子查询的集合中就为true.

not in就与之相反

比较运算符

只要当确切的知道子查询返回的是单值时才可以使用

exists

如果子句有返回结果,那么返回true,否则返回false.

not exists则是相反的意思

select d.* 
from dept d 
where exists(
	select e.empno 
	from emp e
	where e.deptno = d.deptno
	)

image-20210816184352578

order by

排序,默认从小到大排. desc: 从大到小. asc: 从小到大

select * from emp order by sal desc, comm asc

image-20210816184918860

拼接字符串
select ename||','||job from emp
分组查询

分组只能查询按照分组的字段信息,以及相关的聚合数据. 一般情况下分组查询都会用到聚合函数:

avg(), min(), max(), sum()

count():如果字段为null则不计入行数

where后面不能跟聚合函数,如果想对组进行筛选需要使用having

select deptno, avg(sal), max(sal), min(sal), sum(sal)
from emp
group by deptno

image-20210816194403224

select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000

image-20210816194518235

多表连接查询

全连接y

两张表的每一行数据都要一一对应,但是这种连接是没有必要的所有很少用

select * from emp, dept
等值连接

一般情况下通过两张表的主外键进行连接两张表

select * from emp, dept where emp.deptno = dept.deptno
左(右)外连接

以左(右)边的表为主,左(右)边表的所有数据都查询出来,与右(左)边表无法建立关联的数据空着

select e.*, d.*
from emp e right join dept d on e.deptno = d.deptno

image-20210816195630406

分析函数

可以在组内对数据进行排序,该函数最终返回的是一个数字

partition by在分析函数中用来代替group by

/**
 * row_number():返回连续的排位,不论值是否相等
 * rank():具有相同值的行排位相同,序数随后跳跃
 * dense_rank():具有相同值的行排位相同,序号是连续的
 */
select e.*, 
    row_number() 
    	over(partition by deptno order by sal) rn, 
    rank() 
    	over(partition by deptno order by sal) r, 
    dense_rank() 
    	over(partition by deptno order by sal) dr
from emp e

image-20210816200722589

伪列

rowid:在插入数据的时候数据库自动给该行数据生成唯一的rowid,通过rowid查询数据最快

select emp.*,ROWID rid from emp where rownum <= 3

image-20210816201842894

rownum:是查询返回的结果集之中行的序号,可以使用他来限制查询返回的行数,rownum在排序之前就已经存在了,排序会打乱rownum的排序

select emp.*, rownum from emp where rownum <= 3

image-20210816201559696

select * 
from(select e.* ,rownum r 
	 from(select * 
		  from emp 
		  order by sal desc) e
	) 
where r >= 6 and r <= 10

image-20210816201423548

avatar
文章
阅读
粉丝