目录
Sys 数据库最大的BOSS,权限最高,Sys登录的时候,权限必须选sysdba
System数据库管理员拥有DBA权限(管理员权限)
Scott测试用户,在scott用户中会自带测试表
一.创建用户
--创建用户关键字 create user用户名 identified by 密码
(创建完后必须给予connect的权限,不然是连不上数据库的)
Default tablespace users --默认表空间为users,如果用户没有设置表空间,默认也为users
Temporary tablespace temp --临时表空间temporary temp(临时表)
Profile default Profile;表示概要文件
在没有给予权限时,账户无法登录
--授予权限关键字grant
格式 :Grant 权限 to 某一个用户
1、创建表空间:
格式: create tablespace 表间名 datafile '数据文件名' size 表空间大小
create tablespace data_test datafile 'e:\oracle\oradata\test\data_1.dbf' size 2000M;
create tablespace idx_test datafile 'e:\oracle\oradata\test\idx_1.dbf' size 2000M;
(*数据文件名 包含全路径, 表空间大小 2000M 表是 2000兆)
2、建好tablespace, 就可以建用户了
格式: create user 用户名 identified by 密码 default tablespace 表空间表;
create user study identified by study default tablespace data_test;
(*我们创建一个用户名为 study,密码为 study, 缺少表空间为 data_test -这是在第二步建好的.)
(*缺省表空间表示 用户study今后的数据如果没有专门指出,其数据就保存在 data_test中, 也就是保存在对应的物理文件 e:\oracle\oradata\test\data_1.dbf中)
创建用户并指定表空间
CREATE USER cici IDENTIFIED BY cici PROFILE DEFAULT DEFAULT TABLESPACE CICI ACCOUNT UNLOCK;
create user jykl identified by jykl default tablespace jykl_data temporary tablespace jykl_temp;
授权给新用户
GRANT connect, resource TO cici;
grant create session to cici;
1、创建表空间:
格式: create tablespace 表间名 datafile '数据文件名' size 表空间大小
create tablespace data_test datafile 'e:\oracle\oradata\test\data_1.dbf' size 2000M;
create tablespace idx_test datafile 'e:\oracle\oradata\test\idx_1.dbf' size 2000M;
(*数据文件名 包含全路径, 表空间大小 2000M 表是 2000兆)
2、建好tablespace, 就可以建用户了
格式: create user 用户名 identified by 密码 default tablespace 表空间表;
create user study identified by study default tablespace data_test;
(*我们创建一个用户名为 study,密码为 study, 缺少表空间为 data_test -这是在第二步建好的.)
(*缺省表空间表示 用户study今后的数据如果没有专门指出,其数据就保存在 data_test中, 也就是保存在对应的物理文件 e:\oracle\oradata\test\data_1.dbf中)
创建用户并指定表空间
CREATE USER cici IDENTIFIED BY cici PROFILE DEFAULT DEFAULT TABLESPACE CICI ACCOUNT UNLOCK;
create user jykl identified by jykl default tablespace jykl_data temporary tablespace jykl_temp;
授权给新用户
GRANT connect, resource TO cici;
grant create session to cici;
二.查询用户
--查询关键字 select
Select * from all_users all_users 所有用户的表
--修改密码 --修改关键字 alter
格式:Alter user 用户名 identified by 新的密码
三.解锁用户
--解锁关键字account unlock
Alter user 用户名 account unlock
四.删除用户
--删除用户关键字 drop
Drop user 用户名
五.数据库的三大语法(DDL DML DCL)
DDL 对于数据库表结构的操作语句
(其实就是我们在创建表的时候用到的一些sql,比如说:CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构,数据类型,表之间的链接和约束等初始化工作上)
DML 对于表数据的操作
(就是我们最经常用到的 SELECT、UPDATE、INSERT、DELETE。 主要用来对数据库的数据进行一些操作。)
DCL 数据库事务的操作
(是用来设置或更改数据库用户或角色权限的语句,包括(grant , deny , revoke等)语句。)
Oracle中的DDL语句
DDL:定义数据库及表结构的语句,自动提交(保存)的语句
创建与管理表
表名和列名的规则:
1.必须以字母开头,
2.长度在1-30个字符之间,
3.只能包含数字、字母、_、$和#
4.不能和用户定义的其他对象重名
5.不能是oracle关键字和保留关键字
1.数据库常用类型([ ]表示可有可无的意思)
【1】Number(length,[length1])
范围: -10的38次方到 10的38次方
可以表示整数,也可以表示小数
number(5,2) 表示一位小数有5位有效数,2位小数
范围:-999.99到999.99
number(5) 则表示一个5位整数
【2】字符串
char(l)固定长度的字符 ,最大2000个字符
Varchar2(l)可变长度的字符, 最大4000个字符(一般情况下用这个)
【3】日期
date(精确到时分秒) timestamp(精确到毫秒)
大数据:blob 存储2进制数据,最大达到4G
2.表的操作
【1】创建表
Create table 表名(
列名1 数据类型(长度) [primary key],
列名2 数据类型(长度),
……….
);
【2】修改表结构
Alter table 表名
新追加一列
例:alter table 表名
Add(列名数据类型 [列名数据类型(大小)…..])
修改列(修改列的用modify)
Alter table 表名 Modify(列名数据类型[列名数据类型。。。]);
对于修改,可以修改默认值、数据大小、数据类型
数据大小修改后只能对修改列后的数据产生影响
修改类型必须在插入数据之前进行修改
删除列:
Alter table 表名 Drop column 列名
修改列名:
Alter table 表名 Rename column 名字 to 新的名字
修改表名:
Rename 表名 to 新的表名
【3】删除表
Drop table 表名
【4】表的约束
主键 primary key 主键(包含非空、唯一)
外键 foreign key (主键id) references 表名(外键列)
非空 not null
唯一 unique
检查 check
建立约束
- 在创建表结构的时候直接写到列后面的列级约束
- 在表结构的后面写的约束叫表级
列级约束
create table emp2(
id number(8) primary key, --主键约束
ename varchar(20) unique, --唯一键约束
job varchar(20) not null, --非空约束
hiredate date,
sal number(10),
deptno number(8) references dept --外键约束
)
表级约束
create table student(
id number(8),
name varchar(20),
sex char(2),
age number(2),
gid number(5),
constraint stu_id_pk primary key(id),
constraint stu_sex_ck check(sex = '男' or sex = '女'),
constraint stu_age_ck check(age>0),
constraint stu_gid_fk foreign key(gid) references grade(gid)
);
约束关键字 constraint
格式:
Constraint 约束名 primary key(主键列名)
Constraint 约束名foreign key (主键id) references 表名(外键列)
Constraint 约束名 unique(列名)
Constraint 约束名Check(约束条件)
约束名:列级中没有取约束名,系统自动的取一个约束名,表级约束中需要自己取一个标准的约束名,也可以不取(primary key(主键名))
创建一个表,并将查询出来的数据插入到新的表中
Crete table 表名
As
Select * from 表名
去掉数据复制表结构
Create table 表名
As
Select * from 表名
Where 添加一个没有结果的条件 empno=null
3.DML对于表数据的操作
【1】Select 基本查询
格式: select 列名 as 列名的别名 from 表名
别名:如果别名在列中代表列名的别名,如果别名在表中,则认为是表名的别名并可以通过表名的别名点出表中的列
格式:select a.列名1,a.列名2。。。。。 from 表名 a(表名的别名不用加as)
注意:在取别名的时候,别名不能有空格,如果必须要空格,将别名用双引号引起来,as可以省略
【2】insert基本插入
格式:insert into 表名[列名1,列名2,列名3…..] values(列名1,列名2,列名3,列名4);
如果:当表名后面有列名的时候,表示为插入指定的列的数据,但是其他没有指出的列,必须可以为空;如果表名后没有指定列,values中则插入表中所有的列值
【3】update基本修改
格式:update 表名 set 列名=值[,列名1=值,。。。。。][where 列名=值]
【4】delete 基本删除
格式:delete from 表名 [where 列名=值]
4.基础查询
||连接查询结果 “||” 表示将两个数据或者值连接起来
关键字:
【1】nvl(comm,0)空值处理 第一个参数为列,第二参数表示为如果列中的值为空,用0来代替
注意:在sql中,所有的空值运算之后都为空
查询所有工资并不能有重复工资出现
【2】去掉重复 关键字:distinct
格式: select distinct sal from emp
5.过滤查询
- 加where条件
在sql语句中,字符、日期都必须用单引号引起来
where条件后面可以跟的格式:
1.数值的比较
2.字符串的比较
3.日期的比较
4.between…and….(在两者之间,包含两个边界)
5.like(_,%)(模糊查询,关键字查询,_表示的是匹配单个字符,而%表示的是匹配一个到多个字符)
6.in(精确匹配查询)
7.is null表示空 is not null 表示非空
格式: select * from 表明 where 列明 between XX and XX
比较符:> < >= <= !=或<> =
sql中的赋值符号: := 赋值符号
逻辑运算符: and 与 or或 not非
6.排序
关键字:order by
asc 升序 (默认排序可以省略asc) desc 降序
7.函数
单行函数(字符、数值、日期、转换、通用)
单行:只对一行数据影响的函数叫单行函数
字符函数:lower 将字符转换为小写
upper 将字符转换为大写
instr(列,str1) 查询字符 str1在列中的字符,返回位置
(这里查询的话是有分大小写的,查询的是位置,不是下标,字符串中有俩个或以上的要查询的字符,只会显示第一个字符的位置)
【1】concat 连接 将两个字符连接 类似于||
select concat(ename,job) from emp
【2】length 返回字符串的长度
select length(ename) from emp
【3】initcap将首字符大写,其他字符全部小写
select initcap(e.ename),e.ename from emp e
【4】substr截取字符串,第一个单位为起始位置,不是下标位置,第二个单位为截取的长度,长度默认到末尾
select substr(e.ename,2,2),e.ename from emp e
【1】数值函数
--dual 这个表没有具体的数据,只是在查询的时候,我们查询的格式为 select 列 from 表名,是虚拟表,当不需要用到表而用到查询结构的时候用dual占位
turnc() 数字截断,与四舍五入唯一不同就是他是直接去掉,而不是逢5进1
select e.empno, e.mgr,e.hiredate,trunc(e.sal*1.03,-2) from emp e
round()四舍五入后面的参数表示要保留的小数点前后的位置,整数表示小数点后几位,负数表示小数点前面几位
select e.empno,e.ename,e.job,e.mgr,e.hiredate,round(e.sal*1.03,2) from emp e
mod() 求余
select mod(36.13444,2) from dual
floor ()向下取整,不管小数位是多少,都比这个数小的最大整数
ceil ()向上取整
【2】日期函数
sysdate(当前时间) 分为日期和时间
1.Months_between 获得两者之间的月份数
--获得1987/4/19到1981/2/20这个日期间的月份差
select months_between('19-4月-1987','19-4月-1981') from dual
2.add_months 向指定的日期中添加若干月份
select add_months(e.hiredate,2),e.hiredate from emp e
3.last_day 获得本月的最后一天
select last_day('01-2月-2001')--这里写入其他月份也是可以的 from dual
4.round (日期,年/月/日) 日期同样有四舍五入
select round(sysdate,'mi')--后面的参数表示四舍五入到多少参数,也就是参数的下一位在四舍五入 from dual
5.trunc 日期的截断
【3】转换函数
分为两种形式:隐式转换 显示转换
隐式转换:自动类型转换
显示转换:对数据用方法进行转换
to_char 转换为字符
to_number 转换为数字
to_date 转换为日期
to_char(date\number,’字符格式’)
将日期转换为字符串 to_char(sysdate,’yyyy-mm-dd’);
将数字转换为字符串 to_char(sal,’00000.00或者9999.99’);
格式中的9的位数,一定要大于等于要转换的数字的位数
如果是货币可以在前面加上$或者L(本地货币)为字符串
to_date(‘字符’,‘日期格式’) yyyy 代表年 mm代表月 dd代表日 hh时(hh24表示24小时制) mi分 ss秒
--插入一条数据到数据库表emp 将日期转换为指定格式
insert into emp values(7856,'lisi','SALESMAN',7698,to_date('1982-12-12','yyyy-mm-dd'),6000,200,10)
to_number(‘字符’,’数字格式’)
--将¥1234.55转换为数字
select to_number('¥1234.55','L999999999.000') from dual
如果字符含有货币符号,那么格式中也必须有货币符号
【4】通用函数
nvl (参数1,参数2)处理空值
nvl2(参数1,参数2,参数3) 参数1列中的数据,如果不为空执行参数2,如果为空执行参数3
Case 参数 when 列值 then 返回数据(类似于switch…case)
[when 列值2 then 返回数据2
。。。。。]
Else 返回数据
End [别名]
通过每一列查出的值作为参数匹配when后面的值,入果匹配上了则返回then后面的值,如果都没匹配上 则返回else中的值,整个函数只是一列
Decode(参数,列值1,返回值1,列值2,返回值2,列值3,返回值3,返回值)
【5】多行函数(分组函数)
多行:对多行进行影响并且最终得出一个结果的函数多行函数
函数:max min count avg sum
1.max 求出列中最大值
2.min 求出列中最小值
3.sum 求和
4.avg 求平均忽略空值
5.count 总记录不会记录空值
6.group by 分组
规则:group by使用的时候,select列表中有单个的列出现,group by中就必须有
Group by中有的列,在select中不一定有
Group by中可以包含多个列
Where 条件依然在 from语句之后,在group by之前
7.Having 在分组之后,对数据进行再次过滤
Having于where之间的区别:having是分组后过滤条件,并且不能使用别名作为过滤条件 where 在分组之前进行过滤可以使用别名
总体格式:
Select 列名,组函数…
From 表名
Group by 列名
Having 条件
六.多表查询
笛卡尔集
省略连接、连接无效、所有表中的所有行进行了互相连接
如何避免:在查询的时候用where 加入有效的连接条件
等值查询
相关联的列,在select列中出现的时候必须指明为哪个表的列
用两个表所对应的数据进行一对一的连接匹配
如果有多个表进行查询:条件:n-1个条件,n代表表的个数
Iinner join 内连接查询:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表的不匹配的行
格式:select 列 from 表名1 [inner] join表名2 on 等值条件
外连接:分为左外连接右外连接查询
左外连接(left outer join):两个表在连接的过程中除了返回满足连接条件的行以外,还返回左表中不满足条件的行,这种连接查询叫做左外连接查询
右外连接(right outer join):两个表在连接的过程中除了返回满足连接条件的行以外,还返回右表中不满足条件的行,这种连接查询叫做右外连接查询
七.子查询
子查询又称为内查询,在主查询之前执行,并将结果返回给主查询使用
子查询注意事项:
1.子查询要写到括号之内
2.将子查询放在比较条件的右侧,相对主查询往右缩进
主查询和子查询可以不是一张表,只要子查询返回结果主查询使用就行
语法:
Select 列名[,子查询]
From 表名[,子查询]
Where 条件[,子查询]
Group by 列名
Having 条件[,子查询]
如:
select*from emp where deptno =(
select deptno
from dept
where lower(dname)='research')
子查询可以出现的地方:1.select 2.from 3.where 4.having 【 Group by 不能使用子查询 Where,having子查询不能使用order by(排序)】
单行操作符对应单行子查询,多行操作对应多行子查询
1.单行子查询
查询出来之后只与一个值进行匹配
子查询返回空值:如果子查询返回一个空值,那么整个查询都为空
2.多行子查询
在子查询中返回多行结果
多行子查询,用的操作符:
【1】 In :匹配返回结果中的任意一个结果,只要能匹配上都进行显示
【2】Any(大于最小值,小于最大值): 比结果中的任一一个小或任一一个大
如果是大于,大于最小的一个值 。 如果是小于,小于最大的一个值
【3】All: 比结果任意(所有)一个小或者所有的大
如果是小于,小于最小值。如果是大于,大于最大值
查询插入insert
格式: insert into 表名 select 列 from 表名
将查询出来的数据插入到一张新建好的表中
Update
Update 表名 set 列=值[,列2=值 where 条件]
修改数据库中相应的值,where、set中可以使用子查询
删除
Delete 删除表中的数据
Trunct 删除表结构 但是表空间还在
Drop 删除 完全删除 表空间不存在
八.事务管理
事务的作用:确保数据的完整性,数据改变被提交之前预览
1.提交
隐式提交:DDL语句 自动保存
显示提交:commit手动方式进行提交
2.回滚
隐式回滚:异常关闭数据库,自动回滚
显示回滚:rollback 回滚到前一个提交点
Savepoint 名字 : 创建一个保存点
Rollback to 名字: rollback通过名字回滚到相应的保存点
九.视图
概念:从表中抽出的逻辑上相关的数据集合,是一种虚表,可以从不同的角度查看不同的数据,主要是授权使用的
- 视图的数据来自于源表
- 视图一般用于查询数据
1.视图分类
简单视图(单表):一般可以做insert、delete、update、select操作,但是不推荐通过视图来做增删改数据
复杂视图:只能做查询操作,满足以下条件之一,就是复杂的视图:
- 涉及到多张表的视图
- 视图有组函数
- 视图的数据分组
2.创建视图
Create [or replace] view 视图名
As
Select 列 from 表
3.删除视图
Drop view 视图名
4.修改视图
修改关键字 replace
Create or replace view 同名视图
As
重新设置数据
十.序列
可以供多个用户来产生唯一数值的数据库对象,提供有规律的数值
1.自动提供唯一数据
2.共享对象
3.主要用于提供主键值
4.将序列放入内存可以提高访问效率
1.如何创建序列
Create sequence 序列名
[Increment by n] --序列的步长,默认值为1
[start with n] --序列初始位置,不能小于最小值
[minvalue n] --最小值
[maxvalue n] --最大值
[cycle/nocycle] --序列是否可以循环再取
[cache n] --序列的缓存
【1】获取序列的下一个值: select 序列名.nextval from dual
【2】获取到当前序列值:select 序列名.currval from dual
修改:
Alter sequence 序列名
Increment by 1
Maxvalue 99999
Minvalue 1
Nocycle
删除
Drop sequence 序列名
十一.触发
--创建触发
create or replace trigger trg_emp
--emp表插入的时候触发
before insert on emp
for each row
declare
begin
--将查询出来的下一个序列值,复制给新的一个主键列
select emp_seq.nextval into:new.empno from dual;
end trg_emp;
十二.索引
索引的作用:提高查询的性能
索引是什么:
1.一种独立于表的模式对象,可以存储在与表不同的磁盘或表空间中
2.索引被删除或损坏,不会对表产生影响,影响的只是查询的速度
3.索引一旦建立,oracle管理系统会对其进行自动维护,而且由oracle管理系统决定如何使用索引
4.在删除一个表的时候,所有基于该表的所以自动被删除
5.通过指针加速oracle服务器的查询速度
什么时候不需要索引:
1.表很小、数据很少
2.列不经常作为连接条件或出现在where字句中
3.查询的数据在2%到4%之间
4.经常要更新的表
创建索引:
自动创建:在定义主键或唯一键的时候系统会自动的为相应的列加上索引
手动创建:用户可以在其他列上创建非唯一的索引,以加速查询
Create index 索引名 on 表(列)
查看索引:select * from user_indexes