Oracle基础内容

数据库连接对比

Oracle:
         数据库驱动：oracle.jdbc.driver.OracleDriver
         连接Url:jdbc:oracle:thin:@127.0.0.1:1521:orcl
Mysql:
         数据库驱动：com.mysql.jdbc.Driver
         连接Url:jdbc://127.0.0.1:3306/dbname?...

-- plsql中文乱码问题
输入 sql 语句 select userenv('language') from dual 查看数据库字符集

新建变量，设置变量名：NLS_LANG，
变量值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK
（这里的变量值需要与数据库环境相同所以不固定：比如还会有 utf-8 等），确定即可

-- 创建表空间：
create tablespace LIHUA 
datafile 'C:\app\LIHUA\oradata\LIHUA.dbf' 
size 2000m
autoextend on 
next 100m  maxsize 2048m
extent management local;

-- 创建临时表空间：
create temporary tablespace temp_myspace
tempfile 'C:\app\LIHUA\oradata\temp_myspace.dbf' 
size 2000m
autoextend on 
next 100m  maxsize 2048m
extent management local;

-- 创建用户
create user lihua 
identified by lihua123
default tablespace LIHUA
temporary tablespace temp_myspace;

-- 授权
grant dba to lihua;
grant connect,resource to username;

#数据库表空间授权  
alter user RM_HT_DEV default tablespace rm_ht temporary rm_ht_temp;  
alter user RM_HT_DEV quota unlimited on rm_ht; 

-- 修改密码
alter user 用户名 identified by "密码";


-- 创建业主表  
create table t_owners
( 
id number primary key,
name varchar2(30),
addressid number,
housenumber varchar2(30),
watermeter varchar2(30),
adddate date,
ownertypeid number
);

--业主类型
create table t_ownertype
(
id number primary key,
name varchar2(30)
);

--建立价格区间表
create  table t_pricetable
(
id number primary key,
price number(10,2),
ownertypeid number,
minnum number,
maxnum number
);

--区域表
create table t_area
(
id number,
name varchar2(30)
);

--收费员表
create table t_operator
(
id number,
name varchar2(30)
);

--地址表
create table t_address
(
id number primary key,
name varchar2(100),
areaid number,
operatorid number
);

--账务表--
create table t_account 
(
id number primary key,
owneruuid number,
ownertype number,
areaid number,
year char(4),
month char(2),
num0 number,
num1 number,
usenum number,
meteruser number,
meterdate date,
money number(10,2),
isfee char(1),
feedate date,
feeuser number
);

函数

• 分析函数
  
  

集合运算

UNION ALL     -- 并集
INTERSECT     -- 交集
MINUS         -- 差集

授权相关

alter user scott account unlock;      -- 解锁
alter user scott identified by tiger; -- 改密码