oracle用户
数据库角色
DBA 角色
DBA 角色是管理数据库管理员该有的角色,它拥护系统了所有权限,和给其他用户授权的权限。
SYSTEM 用户就具有 DBA 权限。CONNECT 角色
connect 角色是 Oracle 用户的基本角色,connect 权限代表着用户可以和 Oracle 服务器进行 连接,建立 session(会 话)RESOURCE 角色
resouce 角色是开发过程中常用的角色。 RESOURCE 给用户提供了可以创建自己的对象, 包括:表、视图、序列、过程、触发器、索引、包、类型等
用户权限
系统权限
create session 可以和数据库进行连接权限、create table、create view 等具有创建数据库 对象权限
注意:系统权限只能通过 DBA 用户授权,用户不能自己给自己授权对象权限
对表中数据进行增删改查操作,拥有数据库对象权限的用户可以对所拥有的对象进行相应 的操作
创建用户
- create user 用户名 identified by ‘密码
‘例:create user student—用户名
identified by “123456”—密码
修改用户信息
- 修改用户的密码或者状态
—修改用户信息
alter user STUDENT
identified by 111111 —修改密码
—修改用户处于锁定状态或者解锁状态 (LOCK|UNLOCK )
account unlock;
删除用户
- 例:drop user student cascade;
删除的 oracle 用户” student “ 下面还有数据库对象,如 table, view 等,这样你删除用户时必须加选 项 cascade:
一般具有dba权限的用户才可以删除用户
SQL语言
数据定义语言(DDL):创建,删除,更新表结构。
数据操作语言(DML):对数据库的数据进行操作,包括数据插入、更新,以及删除操作。
数据查询语言(DQL):对数据库的数据进行查询。
oracle的数据类型
数值
NUMBER(a,b)
可以存整数,也可以存浮点型。
a 代表数值的最大位数:包含小数位和小数点,b 代表小数的位数
例:number(6,2),输入 123.12345,实际存入:123.12INTEGER 类型
INTEGER 是 NUMBER 的子类型,用来存储整数。若插入、更新的数值有小数,则会 被四舍五入。
例:integer(35.6) 存入integer(36)
字符串类型
varchar2(length)
varchar2是oracle提供的独有的数据类型,最大长度4000,默认不填是1,存储长度可变的字符串,varchar是标准的sql字符型,存储长度固定,最大长度2000。char(length)
存储固定长度的字符串,length:字符串的固定长度大小,默认是 1,最大长度不超过 2000两者的差异
VARCHAR2 比 CHAR 节省空间,在效率上比 CHAR 会稍微差一些,即要想获得效率,就必须牺牲一定的空间,
日期/时间
DATE
存储的是日期和时间,包括年、月、日、时、分、秒。
例子:
内置函数 sysdate 获取的就是 DATA 类型TIMESTAMP
存储的不仅是日期和时间,还包含了时区。
例子:
内置函数 systimestamp 获取的就是 timestamp 类型
其他类型
CLOB(大字段类型)
存储的是大的文本,比如:非结构化的 txt 文本,字段大于 4000 长度的字符串BLOB(二进制类型)
存储的是二进制对象,比如图片、视频、声音等转换过来的二进制对象
表的基本操作(DDL)
创建表
— 备份查询数据命令结构:
— create table 表名 as select 语句
例:
create table student.stuinfo_2020 as select * from student.stuinfo;
注意:表创建到了用户下了,查询其他用户建的表需要在表前加上用户名。
- create table表名 (
字段名称 字段的数据类型 [字段的约束],
字段名称 字段的数据类型 [字段的约束],
…
);
create table STUDENT.stuinfo
(
stuid
varchar2(11) not null,—学号:’S’+班号(7 位数)+学生序号(3 位数)(1)
stuname
varchar2(50) not null,—学生姓名
sex
char(1) not null,—性别
age
number(2) not null,—年龄
classno
varchar2(7) not null,—班号:’C’+年级(4 位数)+班级序号(2 位数)
stuaddress varchar2(100) default ‘地址未录入’,—地址 (2)
grade
char(4) not null,—年级
enroldate date,—入学时间
idnumber
varchar2(18) default ‘身份证未采集’ not null—身份证
)
-
修改表
修改表名
例:ALTER TABLE t_student RENAME to t_student10;- alter table旧表名rename to 新表名;
ALTER TABLE t_student RENAME to t_student10;
- alter table旧表名rename to 新表名;
修改字段
修改字段名
- alter table 表名 rename column 旧字段 to 新字段
ALTER TABLE t_student10 renaALTER TABLE t_student10 rename column info to stu_infome column info to stu_info
- alter table 表名 rename column 旧字段 to 新字段
alter table 表名modify字段 类型;
例:ALTER TABLE t_student10 MODIFY info integer;
增加字段
- alter table 表名 add 字段 类型;
ALTER TABLE t_student10 ADD info varchar(100);
- alter table 表名 add 字段 类型;
删除表
- alter table 表名;
操作表的约束
NOT NULL
- 约束字段的值不能为空
非空约束对 null 值和‘ ’起作用,都不能插入数据
CREATE TABLE t_student(
name VARCHAR2(255) NOT NULL
);
INSERT INTO t_student(name) VALUES(NULL);
INSERT INTO t_student(name) VALUES(‘’);
null 和’’是不一样的,但现在 oracle 可以看做是一样的,以后不一定,null 就是空,没有类型,’’代表空是字 符型的,理论上不一样
- 约束字段的值不能为空
DEFAULT
- 设置字段的默认值
CREATE TABLE t_student(
name VARCHAR2(255),
sex VARCHAR2(20) DEFAULT ‘MAN’
);
INSERT INTO t_student(name,sex) VALUES(‘’,’男’);
—只给name赋值了,没有给sex赋值,默认为MAN
INSERT INTO t_student(name) VALUES(‘’);
- 设置字段的默认值
UNIQUE KEY(UK)
- 设置字段的唯一性
CREATE TABLE t_student(
name VARCHAR2(255),
sex VARCHAR2(20) DEFAULT ‘MAN’,
tel VARCHAR2(11) UNIQUE
);
— 对 null 和空字符不起作用
INSERT INTO t_student(name,sex,tel) VALUES(‘’,’’,NULL);
INSERT INTO t_student(name,sex,tel) VALUES(‘’,’’,’’);
- 设置字段的唯一性
PRIMARY KEY(PK)
- 表的主键
CREATE TABLE t_student(
id INTEGER PRIMARY KEY,
name VARCHAR2(255) NOT NULL,
sex VARCHAR2(20) DEFAULT ‘MAN’,
tel VARCHAR2(11),
CONSTRAINT unique_tel UNIQUE(tel)
);
- 表的主键
FOREIGN KEY(FK)
- 表的外键
外键用于维护表与表之间的关系
在建立外键关系的两个表中,如果其中的外键表中存在主表的某个数据,就不能删除主表中的相关数据,因为两
个表之间已经建立了外键关系。可以通过先解除外键关系(比如设置外键字段为 null 值)后,进行删除操作。
- 表的外键
数据操作语言(DML)
注:oracle 数据库,需要手动提交事务,可以在增删改语句后,添加 commit 语句
插入数据记录
在开发过程中,一般使用指定字段的插入方式,进行数据的插入操作
- insert into 表名 (field1,field2,…) values(val1,val2,…);
insert into STUDENT.STUINFO (id,name,sex,age,address,date)
values (‘SC201801005’, ‘李四’, ‘1’, 26, ‘福建省厦门市 XXX 号’, ‘2018’, to_date(‘01-09-2018’, ‘dd-mm-yyyy’),
‘3503021992XXXXXXXX’);
更新数据记录
在更新的时候,一定要注意是否需要条件
- update表名 set field1=val1,field2=val2,… where 条件
update student.stuinfo t set t.age = ‘30’, t.id = ‘SC201801005’
where t.stuname = ‘李四’;
commit;
删除数据记录
在删除数据时,也需要特别注意是否需要删除条件,如果没有删除条件,就会把这个表的数据删除,并非清空表格,id 不重置
truncate 命令是属于 DDL 命令,一次性删除表中所有数据,并且数据不能恢复,在实际开发过程当中 truncate
命令慎用。
- delete from table_name where 条件
delete from stuinfo t where t.stuname=’李四’;
commit;
数据记录查询
单表的数据记录查询
查询所有的数据
在实际开发过程中,把需要查询的字段查询处理,用以减轻数据库的压力。- select * from 表名;
避免重复数据的查询
- select distinct 字段 from 表名;
实现数学运算的数据查询
Oracle 的数学运算符:+ - * / %- select 字段 ,字段*整数 from 表名;
字符串连接符
CONCAT
concat连接两个字符串- select concat (参数1,参数2)
from 表名 where 条件;
select concat (‘B软件172-‘,name)
from employee where sex=’女’ and name=’王五’
- select concat (参数1,参数2)
||
- select 参数1||参数2||参数3 from 表名 where 条件;
SELECT ‘19 级 3 班-‘||stuname||’-同学’,salary,salary*12 FROM STUINFO t;
- select 参数1||参数2||参数3 from 表名 where 条件;
条件数据记录的查询
在条件内部定义:- 带关系运算符和逻辑运算符的表达式
- 带 between…and 关键字的条件数据查询
- 使用 is null 关键字的条件数据查询
- 使用 in 关键字的条件数据查询
- 带 like(模糊查询)关键字的条件数据查询
带关系运算符和逻辑运算符的表达式
Oracle 中的关系运算符:>、 >=、 <、 <=、 !=(<>) 、 =
Oracle 中的逻辑运算符:&&(AND)、||(OR)、!(NOT)
SELECT stuname,salary,salary12 FROM STUINFO WHERE salary=1000;
SELECT stuname,salary,salary12 FROM STUINFO WHERE salary<>1000;带 between…and 关键字的条件数据查询
SELECT stuname,salary FROM STUINFO WHERE salary BETWEEN 1500 AND 2000;
SELECT stuname,salary FROM STUINFO WHERE NOT(salary BETWEEN 1500 AND 2000);
其中查询出来的结果集,是包含边界;在实际开发过程中,一般用于对日期类型的数据,进行查询使用 is null 关键字的条件数据查询
空字符串和 null 是两个不同的值,在使用的时候,需要注意,查询空字符串,使用”xxx=’’ ”,而判断是否是 null 值,要使用 is null。
不是空值,使用 IS NOT NULL 表示
例:SELECT * FROM t_student WHERE code IS NULL;使用 in 关键字的条件数据查询
在已知的指定范围内,进行数据的查询。
SELECT * FROM t_student WHERE id in(2,4);带 like(模糊查询)关键字的条件数据查询
模糊查询
— 只要带有“悟”,会匹配出来
SELECT FROM t_student WHERE name LIKE ‘%悟%’;
— 以“唐”开头
SELECT FROM tstudent WHERE name LIKE ‘唐%’;
— 以“僧”结尾
SELECT FROM t_student WHERE name LIKE ‘%僧’;
— 第一个字符不管,只要第二个字符是“白”,会匹配出来
SELECT FROM t_student WHERE name LIKE ‘白%’;
排序数据记录查询
- select * from 表名 order by field1 排序条件(asc/desc),field2 排序条件(asc/desc)
限制数据记录的查询数量
- 分页公式:rownum<=页数每页显示条数,rownum>=(页数-1)每页显示条数+1
— 每页显示 2 条,第一页数据
select from
(select tt.,rownum r
from (select from t_student t order by t.id desc) tt
where rownum<=2) ttt
where ttt.r >=1;
— 每页显示 2 条,第二页数据
25 / 52课程内容页
26 / 52
select from
(select tt.,rownum r
from (select from t_student t order by t.id desc) tt
where rownum<=4) ttt
where ttt.r >=3;
- 分页公式:rownum<=页数每页显示条数,rownum>=(页数-1)每页显示条数+1
统计函数和分组数据的查询
统计数据查询(聚合函数)
SELECT AVG(salary) AS “ 平 均 薪 水 “,MAX(salary) AS “ 最 高 薪 水 “,MIN(salary) AS “ 最 低 薪 水 “,SUM(salary) AS “ 薪 水 总 和 “,COUNT(id) AS “总记录数” FROM t_student where 条件
除了 count 函数,其他函数如果没有找到记录,返回值是 null,count 函数返回 0分组数据查询
— 多字段分组
— 自行添加 sex
SELECT group_num,sex,AVG(salary) AS “平均薪水” FROM t_student
WHERE group_num IS NOT NULL
GROUP BY group_num,sex;
注: select 聚合函数或分组条件 from 表名 where 条件 group by 分组条件- 使用 having 对聚合完的数据,进行条件限制查询
— 多字段分组
SELECT group_num,sex,AVG(salary) AS “平均薪水” FROM t_student
WHERE salary > 300 and group_num IS NOT NULL
GROUP BY group_num,sex
27 / 52课程内容页
HAVING AVG(salary) > 500
Order by group_num asc;
- 使用 having 对聚合完的数据,进行条件限制查询
多表数据记录的查询
表与表之间存在一定的结果关系,关系包括一对一,一对多,多对一,多对多
- 内连接查询
内连接查询中,要保证 连接条件(e.dept_id = d.id )的完全匹配,结果才会被检索出来。
- select 字段 from 表名1 inner join 表名2 on 连接条件(表1.字段 = 表2.字段);
SELECT e.name AS "姓名",d.name AS "部门名称"
FROM t_employee e
INNER JOIN t_department d
ON e.dept_id = d.id;
- 外连接查询
外连接查询中,可以检索出左表/右表的字段给null的数据来。
- 左外连接
左表某些字段没有数据,但是作为左表,也会被查询出来。
- select 字段 from 表名1 left join 表名2 on 连接条件(表1.字段 = 表2.字段);
-- 左外连接查询
SELECT e.name AS "姓名",d.name AS "部门名称"
FROM t_employee e
LEFT JOIN t_department d
ON e.dept_id = d.id;
- 右外连接
右表某些字段没有数据,但是作为右表,也会被查询出来
- select 字段 from 表名2 right join 表名1 on 连接条件(表2.字段 = 表1.字段);
SELECT e.name AS "姓名",d.name AS "部门名称"
FROM t_employee e
RIGHT JOIN t_department d
ON e.dept_id = d.id;
- 多张表的关联查询
- select 字段 from 表名1 内/左/右 join 表名2 on 连接条件 内/左/右 join 表名3 on 连接条件;
SELECT e.name AS "姓名",d.name AS "部门名称",da.address,da.is_default
FROM t_employee e
LEFT JOIN t_department d
ON d.id = e.dept_id
LEFT JOIN t_dept_address da
ON da.dept_id=d.id and is_default=1;
- 子查询
在一个 select 中包含另外一个 select,把包含在内部的 select 的语句,称之为子查询。
- 标量子查询
查询结果只有一个值。
- select 字段 from 表名where 条件=(select 字段 from 表名 where 条件);
SELECT d.name AS "部门名称" FROM t_department d
WHERE d.id=(SELECT e.dept_id FROM t_employee e WHERE e.name='夏老师');
- 列子查询
子查询的结果有多个值。
- select 字段 from 表名1 内/左/右 join 表名2 on 连接条件 where 查询条件
SELECT d.name AS "部门名称",e.name AS "姓名"
FROM t_department d
LEFT JOIN t_employee e
ON e.dept_id = d.id
WHERE d.name='市场部' OR d.name='就业部';
- exists
exists 用于检查子查询是否至少会返回一行数据,若该子查询没有任何数据,返回 false
- select 字段 from 表名 where exists(子查询);
SELECT d.name FROM t_department d WHERE EXISTS(SELECT * FROM t_employee e WHERE e.dept_id=d.id);
- 合并查询
通过多个 select 语句查询,将查询的结果合并在一起,并返回一个新的结果集
- union 关键字
UNION 内部的每个 SELECT 语句必须拥有相同数量的列,列也必须拥有相似的数据类型
- select 字段 from 表名1 union select 字段 from 表2
SELECT e.name FROM t_employee e
UNION
SELECT ec.name FROM t_employee_copy ec;
- union all关键字
允许重复的数据,可以使用 union all
- select 字段 from 表名1 union all select 字段 from 表2
SELECT e.name FROM t_employee e
UNION ALL
SELECT ec.name FROM t_employee_copy ec;
SQL函数
操作符的优先级
- 算术>连接>比较>not 逻辑>and 逻辑>or 逻辑操作符
字符函数
字符函数接受字符的输入并返回字符或数值
- 例:select concat(‘员工号为’,stuid) from STUINFO t
其他单行函数
- 例:length() , char(),…
数字函数
- 例:abs(),cos(),…
日期函数
- 例:months_between(),..
转换函数
to_char:按照指定的格式转化字符串;
to_date:将字符串转化为日期;
to_number:将数字字符串转化为数字;
- 例:to_date(),to_char(),…
其他函数
Null 与任何数字相加的结果还是 null,这里需要 nvl 做处理。
常用来处理薪资问题
nvl(string, replace_with)
nvl(string, replace_with):不为空时为本身,为空时为指定值;nvl2(e1, e2, e3)
nvl2(e1, e2, e3):不为空时指定值 e2,为空时指定值 e3;NullIF(a,b)
NullIF(a,b):相等时为空,否则为前者;
分组函数
- avg、min、max、sum、count
分析函数
分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是: 对于每个组返回多行,而聚合函数对于每个组只返回一行。
数据库对象
同义词
别人将权限授予了我们,我们也是能对数据库进行操作的,但是我们必须要在已授权的表的名称前嵌入该表所有者的名称(该表所有者.表名)
同义词就是代替该表所有者.表名
共有同义词
系统管理员可以创建公有同义词,公有同义词可以被所有用户访问。私有同义词
拥有 create synonym 权限的用户(包括非管理员用户)即可创建私有同义词,创建的私有同义词只能由当前用户使用同义词的创建与删除示例
1、system 账号登录:创建 test_user 测试账号,赋予权限
- —创建测试账号
create user test_user identified by 123456; - —赋予测试账号连接和资源创建相关权限
grant connect,resource to test_user; - —赋予 test_user 账号创建同义词权限
grant create public synonym to test_user; - —赋予 test_user 账号删除同义词权限
grant drop public synonym to test_user;
- —创建测试账号
2、student 账号登录:让 test_user 获得 student 的 t_student 的查询权限
- grant select on t_student to test_user;
3、test_user 账号登录:创建同义词
tudent.t_student;- create or replace public synonym syn_t_student for student.t_student;s
4、System 账号登录:现在可查询该同义词是否建立成功:
- select * from dba_synonyms where table_owner = ‘STUDENT’;
表名在oracle中都是大写
- select * from dba_synonyms where table_owner = ‘STUDENT’;
5、test_user 账号登录
- (1)接下来可以直接在 test_user 中查询该同义词的数据 select * from syn_t_student;
- (2)使用 test_user 连接,删除同义词 drop public synonym syn_t_student;
6、student 账号登录:移除 t_student 上的 student 查询权限
- revoke select on t_student from test_user;
序列
序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列,序列中的可以升序生成,也可以降序生成
创建序列
create sequence seq_empno start with 1 —初始值
increment by 1; —增量常用取值操作
— 序列中的下一个值,从定义(start with)的值开始,调用了 nextval 之后, currval 会变成 nextval 的值
select seq_empno.nextval from dual;
— 获取序列的当前值
select seq_empno.currval from dual;
视图
插入/更新/删除视图的操作会受到一定的限制; 所有针对视图的操作都会影响到视图的基表; 为了防止用户通过视图间接修改基表的数据, 可以将视图创建为只读视图(带上 with read only 选项)
概念
视图(View)是一种虚表,在已有的数据表或其他视图的基础上创建的,可以理解为存储起来的查询语句,视图 本身不存储数据,因此对虚表的操作最终都会转换为对基表的操作,只需要简单的 SELECT…FROM 即可优缺点
优点:- 可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基表。
- 可以将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。
- 限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。
- 从多张基表中按一定的业务逻辑抽出用户关心的部分,形成一张虚拟表。
缺点:
1、视图的操作最终都会转换为对基表的操作,因此如果是对复杂的视图的修改操作可能会失败。
视图分类
简单视图
指基于单个表并且不包含函数或表达式的视图,在该视图上可以执行 DML 语句(即可执行增、删、改操作)。复杂视图
指基于单个或者多个表或者包含函数、表达式或者分组数据的视图,在该视图上执行 DML 语句时必须要符合特定条件。注意:在定义复杂视图时必须为函数或表达式定义别名连接视图
指基于多个表建立的视图,一般来说不会在该视图上执行INSERT、UPDATE、DELETE 操作。
视图操作
视图的创建
基本语法 :
CREATE [OR REPLACE] VIEW view_name [(column_name1[,column_name2…
AS
select_statement
[WITH CHECK OPTION]
[WITH READ ONLY]
参数说明:
CREATE OR REPLACE:用于创建和修改视图(OR REPLACE :若所创建的试图已经存在,则替换旧视图;)
WITH CHECK OPTION :用于创建限制数据访问的视图
WITH READ ONLY :用于创建只读视图查询视图
select 字段 from 视图名;删除视图
drop view 视图名;测试案例
1、已有表stuinfo,并插入了数据
2、复制表格,创建测试表
- create table stuinfo1 as select * from stuinfo;
3、创建视图
- create or replace view v_student asselect * from stuinfo1;
4、对视图数据进行增删改查操作
4.1查询
- select * from v_student;
4.2更新
update v_student set v_student.age=15 where v_student.stuname=’唐僧’;
查询视图(验证视图数据是否变化)
- select * from v_student;
查询基表(验证基本数据是否变化)
- select * from stuinfo1;
4.3增加
insert into v_student(STUID, STUNAME, SEX, AGE, CLASSNO, STUADDRESS, GRADE, ENROLDATE, IDNUMBER) 43 / 52
课程内容页 values (‘SC201801005’, ‘李四’, ‘1’, 26, ‘C201801’, ‘福建省厦门市 XXX 号’, ‘2018’, to_date(‘01-09-2018’, ‘dd-mm-yyyy’), ‘3503021992XXXXXXXX’);查询视图(验证视图数据是否变化)
- select * from v_student;
查询基表(验证基本数据是否变化)
- select * from stuinfo1;
4.4删除
delete from v_student where v_student.stuname=’李四’;
查询视图(验证视图数据是否变化)
- select * from v_student;
查询基表(验证基本数据是否变化)
- select * from stuinfo1;
5、删除视图
- drop view v_student;
索引
索引的内建工作对用户是透明的,由数据库自行维护,我们只需要指定是否添加索引。
索引是为表中字段添加的。当一个字段经常出现在 WHERE 中作为过滤条件,或 ORDER BY 或 DISTINCT 中时可以为其添加索引以提高查询效率。
概念
索引就类似的书的目录,能够在一定程度上快速的进行数据的定位。 能够加快数据检索的速度。
一旦建立了索引,在数据进行插入或更新时,就会去更新索引结构,带来一定效率损耗。而且因为加了索引会多 占用一定的磁盘空间。索引操作
创建索引
- create index 索引名 on 表名(列名);
create index ix_student on stuinfo(stuname);
- create index 索引名 on 表名(列名);
删除索引
- drop index 索引名;
drop index ix_student;
- drop index 索引名;
创建组合索引
- create index 索引名 on 表名(列名 1,,列名 2);
create index ix_student on stuinfo(stuname,sex);
- create index 索引名 on 表名(列名 1,,列名 2);
GRANT 角色 TO 用户
grant connect to STUDENT;—授权 connect 角色
grant resource to STUDENT;—授予 resource 角色
Revoke 角色(role) from 用户
revoke resource from STUDENT;
取消 Resource 权限后,创建表提示权限不足
切换到 student 账号,再创建表格,否则是在 system 账号下创建的
普通用户
XMind - Trial Version