Oracle数据库

数据库

DBS(数据库系统) = DB(数据库)+DBMS(数据库管理系统)

注意：字符要使用’'单引号括起来

Oracle

数据库>数据文件>表空间>表>数据

类型

char(5)

定长字符串，如果输入位数不足，自动补空格

varchar2(10)

可变字符串，存储长度最大为10为得字符串

nchar和varchar使用Unicode字符集方式

number

number默认使用整数位最长为38为，没有小数位；

number(3)最大位数为3位得整数；

number(5,2)最大存储5位数，最多2位小数，最多3位整数

date

存放时间，年月份，时分秒

datestamp

时间戳

lob

大型数据

blob

存放大型二进制文件

clob

存放大型字符数据文件

完整性约束

完整性约束保证了数据的准确性

域完整性约束

检查约束

范围：列名>(>=)值

包含：like 列名 ‘%值%’

长度：length(列名)>值

默认值约束

设置默认值

非空约束

不能为空

实体完整性约束

主键约束

主键不能为空，主键要唯一

外键约束

标识列约束

唯一约束

引用完整性约束

主键所在的表为主表，外键所在的表为子表（从表）

自定义完整性约束

sql(Structured query language)组成

DML(Data Manipulation Language)：数据操作语言

增加：insert into 表名 [(列名)] values (值)

多行增加：

第一种：insert into 表名1(列名) select 列名 from 表名2 (将表2中的部分列数据添加到表1中)

第二种：create table 表名1 as select 列名 from 表名2 (创建表1将表2中的部分数据添加到表1中)

第三种：insert all

into 表名 (列名) values (值)

into 表名 (列名) values (值)

…

select * from dual

第四种：insert into 表名 (列名)

select 值 from dual

union

select 值 from dual

union…

删除：

修改：update 表名 set 列名=值 while 布尔表达式(列名=值)

有些数据库未写字段数据为空字符串有些数据库未写字段数据为null（判断是否为null需要使用is null）

DQL(Data Query Language)：数据查询语言

查询：select 列名 from 表名 [while等布尔表达式式]

查询顺序：from>where>group by>having>select>order by

DDL(Data Definition Language ):数据定义语言

创建：

删除：

DCL(Data Control Language):数据控制语言

获取权限：

释放权限：

逻辑运算符

and or not

算数运算符

+，-，*，/，mod

比较运算符

< ， >， = ， >= ，<= ， <> ，

!=(不推荐使用)

关键字

where as union null is sysdate(获取当前时间),length(列名)，mod(值1，值2)，default，rownum，in,not in,between，on

as别名

列别名需要使用as，表起别名不用as

rownum

在查询结果完之后系统自动添加的行号

分组

对数据进行分组

group by

在分表之后需要查询使用having，在where后进行操作

排序

order by

在最后进行操作

模糊

like 可以查询数字或字符串

多表查询

inner join：内连接，两个都有的才有

left join：左连接，左表为主表

right join：右连接，右表为主表

full join：全连接，两个表的都要，不管是否两个表都有

高级查询

子查询

嵌套在select,insert,update,delete或其他子查询中的查询语句

子查询中可以使用父查询中的列名

查询效率低

任何可以使用表达式的部分都可以使用子查询

返回值为单列

常用函数

单行函数

字符函数

initcap(char)

首字母大写

initcap('hello')

lower(char)

转小写

lower('HELLO')

upper(char)

转大写

upper('')

ltrim(char,str)

从左边开始截取指定字符串中的字符

ltrim('heello','he')

hee会一起被截取

rtrim(char,str)

从左边开始截取指定字符串中的字符

rtrim('heello','lo')

llo会一起被截取

translate(char,from,to)

将指定的字符翻译成指定的字符

translate('jack','abcd','1234')

如果第一个字符串中存在第二个字符串中的字符，则将第一个字符串中的对应字符转换成第三个字符串中的对应字符

replace(char,old,new)

将第一个字符串中的所有old字符串转换成new字符串

replace('hello','he','**')

instr(char,s1,pos)

查找子字符串的位置

instr('hello',‘lo’,2)

从第二个位置开始查找指定字符串

substr(char,pos,len)

从指定位置开始截取指定长度的字符串

substr('hello',,5)

concat(str1,str2)

将两个字符串进行拼接，可以使用||代替

concat('hello',' world')

数字函数

abs(n)

ceil(n)

floor(n)

round(n)

sqrt(m,n)

mod(m,n)

power(m,n)

日期函数

天数的加减可以直接将日期和数字进行运算，可以使用to_char()函数获取当天时间的信息（例如星期几，年份等）

sysdate

extract(year from sysdate)

获取指定的部分时间

months_between(日期1，日期2)

add_months(日期,数字)

next_day(日期,星期)

获取下一个星期的日期，星期需要使用英文

last_day(日期)

获取这个歌月的最后一天日期

转换函数

to_char(日期，格式)

将日期转换成指定格式的字符串,可以获取当前星期几

to_date(字符串，格式)

将字符串转换成日期

to_number(字符串)

将字符串转换成数字，数据库会自动转换，一般用不上

其他函数

nvl(值，s1)

判断值是不是为null,如果为空，则输出s1

nvl2(值，s1,s2)

判断值是不是为空，如果为空，则输出s1，否则输出s2

case when then end

判断范围或是指定值，相当于Java中的if

case when 判断 then 输出值1
when 判断 then 输出值2
else 输出值3
end as 列别名

decode(值,判断值1，输出值，判断值2，输出值，其他输出值)

将值和具体的值进行对比，相当于Java中的switch

聚合函数

聚合函数只能和分组的列一起查询

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

使用语句块

declare

cat number;

begin:

select count(1) from user_tables where table_name=‘STUDENT’

if cnt > 1 then

execute immediate ‘drop table STUDENT’

dbms_output.put_line(‘表存在，删除成功!’);

end if;

end;

创建表

create table 表名 （

列名1 类型，

列名2 类型

）

添加约束

添加实体约束

alter table 表名

add constraint 约束名 约束类型 列名

添加域约束

添加检查约束

alter table 表名

add constraint 约束名 check(约束语句)

非空约束

alter table 表名

modify 列名 not null

默认约束

alter table 表名

modify 列名 default 默认值

添加外键约束

默认级联删除为no action

aiter table 表名

add constraint 约束名称 约束类型 (列名) referents 表名 (列名) [on delete (其他级联删除[cascade,set null])]

删除约束

alter table student

drop constraint 约束名称

只能通过约束名称来删除键或是检查约束，其他约束通过修改约束**(列约束)**来达到约束的删除

索引

创建索引

create index 索引名称 on 表名 列名

删除索引

drop index 索引名称

PL/SQL

一种过程化语言，通过增加编程语言的特点，形成对sql的扩展

流程控制

所有流程控制语句结束都是end加上流程语句名再加";"号

流程控制结构

declare
--变量声明
 变量名 [constant] 类型 [not null] [:= 值]
begin
  语句块
end;

case

如果是在sql中写end后面跟的是别名

两种写法，

一、再case后面跟变量名比较具体值

case 变量
 when 具体值 then 语句;
 when 具体值 then 语句;
else 语句;
end case;

二、在case后面跟判断条件，可以比较范围值

case when 判断表达式 then 语句;
when 判断表达式 then 语句;
else 语句;
end case;

if

不是elseif是elsif

if 判断条件 then
 语句块;
elsif 判断条件 then
 语句块;
else 语句块;
end if;

loop

有三种循环方式（loop、while loop、for loop）,所有流程控制语句中，唯一不需要使用then的流程语句

loop

loop
 语句块
end loop;

while loop

while 判断条件 loop
 语句块
end loop;

for loop

for 变量 in 集合 loop
 语句块
end loop;

游标（cursor）

DML和DQL语句中得到的结果集，DML只返回受影响的行数

显式游标

使用前需要先打开游标，后关闭游标（如果使用for访问游标，会自动开启和关闭游标）

需要声明游标

cursor 游标名称 is sql语句

游标的四个属性

%found

查询本索引处是否有数据

%isopen

查看此游标是否被打开

%notfound

和found相反

%rowcount

返回第几条记录（如果是隐式游标，显示值（受影响的行数））

打开游标

open 游标名称

for访问游标

会自动开启和关闭游标,以及访问吓一条记录

for 变量名 in 游标 loop
 语句块
end loop

关闭游标

close 游标

隐式游标

使用DML时系统自动生成一个游标，名称为sql，只有一条记录，受影响的行数

数据库对象

视图

基于一个或多个表或视图创建的逻辑表，不包含数据，可以对数据进行查询和修改，基于基本表创建的视图，基本表为基表（就是sql语句的返回结果表）

语法：

create [or replace] view view_name as subquery

创建一个以只包含学生姓名的试图

create or replace view stu_name as select studentname from student

优点

1、可以展示表的部分数据

2、可以简单查询查询到复杂查询

3、增强了表的独立性，多表进行检索

4、相同数据可以创建不同视图

简单试图和复杂视图

简单视图为基于单个表创建的视图

复杂视图时基于多个表创建的视图

过程

将sql或PL/SQL代码快进行完成特定功能的集合

语法：

create [or replace] procedure pro_name [(参数 in/out/in out) type]

[is/as]

变量声明

begin

代码快；

end;

根据学生的学号返回学生的姓名

create or replace procedure stu
(parameter1 in varchar2,parameter2 out varchar2)
is
aa number;
begin
 select studentname into parameter2 from student where studentn0=parameter1;
end;

调用过程

在pl/sql中调用过程

begin

end;

declare
studentname varchar2(20);
begin
stu('1001',studentname);//调用函数，返回学生姓名
dbms_output.put_line(studentname);//输出姓名
end;

注意：

如果在调用过程时没有参数可以直接使用过程名调用，入参和返回值都在()中传入或传出

定义参数可以不需要指定长度，但是定义变量需要指定长度

函数

函数和过程类似，都是一组sql或PL/SQL语句块的集合，但是函数一定有返回值（可以在sql中使用）

创建函数

可以不写参数的in\out\in out

create [or replace] function fun_name

[(parameter type)]

return trturn_type

is/as

begin

语句块;

return 变量;

end;

根据课程id返回课程名称

create or replace function getname
(id number)
return varchar2
is
name varchar2(10);
begin
 select cname into name from course where cid=id;
 return name;
end;

触发器

触发器是一种特殊的过程，但是用户不能直接调用触发器（通过:new和:old查询记录的MDL操作前后数据）

语法

create [or replace] trigger tri_name

触发时间 触发事件

on 表名

begin

语句块;

end;

插入数据时打印记录

create or replace trigger tri_aa_id
before insert
on aa
begin
  dbms_output.put_line('增加了,'||:new.id||:new.name);
end;

注意

触发时间只与处理时间有关

序列

一组用来自增且保持唯一的数字(序列不可以使用or replace)

语法：

create sequence seq_name

minvalue min_value

maxvalue max_value

start with start_value

increment by add_value

cache value;

创建一个aa的id自增序列

create sequence sen_aa_id
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
cache 50;

用户

创建用户

grant connect,resource,dba to 用户名

收回权限

revoke resource,dba from 用户名

java连接数据库

1、加载配置文件

使用反射和类加载器加载配置文件的Io流

InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream();
Properties pro = new Properties();
pro.load(is);
pro.getString("键值")

2、连接数据库

使用DriverManager获取数据库连接
Connection conn = DriverManager.getConnection();

3、获取PreparedStatement对象

获取预处理对象，预处理先将sql语句编译好，之后再填充值，提高了安全性，防止sql注入

String sql = "sqlect * from student";
PreparedStatement pstmt = conn.prepareStatement(sql);

4、获取结果集

使用executeQuery或executeUpdate运行sql语句，获取结果

ResultSet rs = pstmt.executeQuery();

5、释放资源

使用close方法关闭连接，释放资源