oracl笔记

权限授予

grant 权限1，权限2 to 指定的用户
connect --带有登录功能
resource–带有表操作的权限
sysdba-- 管理员
create view --创建视图
grant connect,resource to 用户名

基本语句

–创建数据表
create tablespace sc1911
datafile ‘D:\oracle\oradata\orcl\sc1911.dbf’
size 50M
autoextend on;

–创建用户
create user jcc
identified by 123
–提供默认表空间
default tablespace sc1911;

–赋予权限
grant connect, resource to jcc;

–修改密码
alter user jcc
identified by 111;

–创建表
create table student(
id number(6),
name varchar2(10),
age number(3),
sex char(10),
birthday date
);

–插入数据
insert into student
(name,age,sex) values(‘ff’,10,‘男’)
–提交
commit;

insert into student
values(2,‘ff’,12,‘女’,sysdate);

–更新数据
update student
set id=555 where sex=‘男’;

–删除数据
delete from student
where id=555;

–增加列
alter table student
add content varchar2(40);

–修改列
alter table student
modify content number(35);

命名规则

oracle数据库不区分大小写,都会默认转换大写
oracle中对于取名(表名、列名、索引、视图、序列)

1. 字母开头
2. 长度不能超过30字符
3. 不能使用oracle关键字
4. 可以使用 字母 0-9 * $ #

单行函数

concat（x,y）拼接字符串 或者用 ||
length（x） 返回x的长度
lower（x）转小写
upper（x）转大写
substr（x,start,length）截取字符
replace（x,old,new）替换字符
trim（[str from] x）取出x字符串，去掉两端str内容
如果不写[] 代表去掉两端的空格。

• select trim(‘a’ from ‘aa你好啊’) from dual;

to_date(‘1998-11-06’, ‘yyyy-mm-dd’) 日期转换

数学函数

mod（x,y） 返回x/y的余数
round(x[,y]) 在x的第Y位进行四舍五入
如:round(3.1415926,4)=3.1416
trunc(x[,y]) 在x的第Y位进行截断
如:trunc(3.1415926,4)=3.1416

转换函数

• to_date(字符串,日期格式) 日期格式 yyyy-mm-dd hh:mi:ss
  hh24:mi:ss
• to_char(日期\数字,字符串) 将日期、数字转换字符串
• to_number(x,格式) 转换数字
  select to_char(sysdate,‘yyyy-MM-dd HH24:mi:ss’) from dual;
• to_number(x,格式) 转换数字
  //string+数字 自动转换成数字
• select to_char(sysdate,‘yyyy-MM-dd HH24:mi:ss’) from dual;
• floor(日期1 ,日期2)： 计算两个日期天数差
• add_months(日期,数量):给日期加几个月
• months_between(日期1,日期2):计算两个日期月份差
• extract(fmt from 日期):提取日期中特定部分 (常用)
  fmt 常用值:year,month,day,hour
  minute,second

其他函数

nvl(x,value)：如果x是null 会显示value的值
nvl2(x,value1,value2): 如果x是null会显示value2 否则显示value1
decode函数(条件列名,值1,返回值1,值2,返回值2,…,默认返回值)
类似于java 中switch

虚表

dual 用于做不对特定表进行操作的语句

select sysdate from dual；
select ‘你好啊 同学’ from dual；

连接

• 自连接

• 内连接

• 外连接
  左外连接、右外连接、全外连接

约束

检查约束

• create table 表（
  sex varchar2（10） check（sex in(‘男’,‘女’）,
  age number(3) check(age > 0 and age < 200)
  ）；
• 外键
  创建表之后添加外键
  alter table 表 add constraint
  约束名 关键词(子表的列)
  references 父表(父表的列)
  注:创建主外键约束时，创建父表后创建子表, 删除表 先删除子表后删除父表
  删除表中的数据 先删除子表数据再删除父的数据

语句分类

1. DDL(data defintion language):数据定义语言,用来定义和修改数据库对象(表结构) 包括:create drop alter truncate(截断表
   DDL操作是隐式提交事务,不需要手动提交，也不能使用rollback
2. DML(data manipulation language):数据操作语言 包括:insert delete update
   每一次操作都要做事务commit/rollback
3. DCL(data control language):数据控制语言:用于控制数据库权限
   包括：grant 授权 revoke 撤销
   grant connect,resouce to admin
   revoke connect to admin
4. TCL(transaction control language):事务控制语言:commit/ rollback
5. DQL(data query language):数据查询语言: select

删除表

delete、drop 和truncate 区别?
delete:只删除表数据，不能删除表定义,不释放空间,可以提交或回滚事务,还会触发触发器.
truncate:使用语法类似于drop,也是删除表中的数据,不需要提交事务,不能删除表的定义，可以释放空间(相当于把表删除了,又创建了一张新的表)
drop：删除表，会删除表中的定义，并且会释放空间，也不需要提交事务
速度: drop>truncate >delete

集合运算:交集、并集、差集

1.并集union 和 union all
(1) union:将两个结果集合并，去重,会按照默认排序规则排序
(2) union all:将两个结果集合并，不去重，不排序
注:合并的两个结果集,列的类型和个数相同,union 也可以作为列转行来使用
2.交集 intersect:获取两个结果集相同的部分 语法类似于并集
3.差集 minus：两个结果集不同的部分
如: listA minus listB 返回A-B 的结果中的相同的部分

数据库索引

索引:是一种数据库加快查询数据库对象.类似于新华字典中的目录,减少查询数据库的次数，提高了效率
语法:create index 索引名 on
表名(列[,列2,列3])
[tablespace 表空间名]
注:主键和唯一键会默认创建索引
使用:1.如果查询时使用了添加索引的列作为查询条件.则数据库会走索引
2.如果对多列建立索引，代表联合索引，查询时条件顺序是创建索引的顺序来走索引
如何查看sql语句运行是否走了索引?
答:借助于plsql 查看 sql执行计划
explain plan F5 如果走了索引,就看到了索引的执行计划.
常用不走索引的情况:
(1) like 使用%进行模糊查询不走索引
如: 列like ‘%xx’ 不走索引
列 like ‘xx%’ 走索引
(2) 使用is null 或is not null 不走索引
(3) where子句是使用函数不走索引
(4) 使用<>, not 不走索引
什么样子的列适合加索引? 非空、唯一、且不经常修改的数据还有经常作为查询条件的数据
总结:索引并不是越多越好,索引可以增加查询的效率，但是会降低增删改的效率

序列

sequence
序列会产生一系列唯一的数字的数据库对象,一般用于设计数据库实现oracle主键自增、自减的数据
语法:create sequence 序列名
[increment by n]序列如何改变 每次自增n 如果不写默认自增1
[start with n] 代表从n开始
[maxvalue n] 最大值
[cycle | no cycle] 是否循环
使用:通过新增语法 insert into 表
values(序列名.nextval,列1,列2,列2)
mysql数据库主键自动增长的功能

视图

视图:用于存储一条sql语句查询结果的虚表。视图只是逻辑定义，不是真正存储数据，每次使用相当于重新执行一遍视图中的sql语句
优点:1.将复杂的查询保存到视图,可以对最终的用户屏蔽一些sql语句复杂度
2.通过视图查询数据，可以对用户屏蔽底层表结构和数据,安全性较高
视图语法:create view 视图名 as
select * from 表
相当于把sql语句结果集保存到了视图中
视图会占用表空间吗? 不占空间,视图不是直接存储数据,数据还是存在原来的位置.
视图可以加快查询吗? 不能 因为视图只相当于重新执行一遍sql语句
注:创建视图用户必须存在权限
create any view

分页

rownum:是数据库中伪列(本身不存在),根据查询结果动态生成当前数据的行号.
select * from(
select 表.*,rownum r from 表
) where r between 1 and 3;

数据库范式

设计关系型数据库,需要遵从一些规范要求,才能设计出结构合理的数据库。而这些所谓的规范称之为范式.
常见的范式有六种:
第一范式(1NF) 第二范式2NF
第三范式3NF
范式的等级越高，其数据的冗余越小

1. 第一范式:表中的数据具有原子性(代表不可分割),设计数据库的时候每列数据不能是集合、数组等非原子性的数据
   学生编号 姓名 成绩
2. 第二范式:建立在第一范式基础上,还要求数据每条记录是唯一的,简单来说每张表必须有主键
3. 第三范式:建立在二范式基础上,还要求表中不包含其他表中的非主键信息列
   不能有重复的列,主要用于减少数据冗余
   和降低维护成本.

事务

事务是被绑定在一起作为一个逻辑工作单元的sql语句组.
具有四大特性(ACID)：

1. 原子性:不可分割,这一个任务单元要保证，同时成功、同时失败.
2. 一致性:数据库必须从一个一致状态到另一个一致状态(逻辑运行不能出错，结结果才正常)
3. 持久性:事务一旦提交，对其数据改变是永久的
4. 隔离性:一个事务与其他事务不能相互干扰

plsql编程