Oracle(一)

你是九月的梦中浓冬，而我躲在梦里不敢见你。
程佳乐-<未梦及半>

SQL的分类:

DDL:数据定义语言: Create,Drop,Alter
DCL:数据控制语言: Grant,if...
DML: 数据操纵语言: insert,update,delete...
DQL: 数据查询语言: select

DDL及DCL语句:

-- 创建表空间
create tablespace waterboss --表空间名
datafile 'c:\waterboss.dbf' --数据文件存放位置
size 100M --初始大小
AUTOEXTEND ON NEXT 10M; --自动增长大小

-- 验证表空间
select * from  DBA_DATA_FILES; 
select * from  DBA_TABLESPACES;

-- 创建用户
create user wateruser --用户名
identified by itcast --密码
default tablespace waterboss; --默认表空间

-- 授权
grant dba to wateruser; --授予dba级别的权限
-- 验证用户
select * from dba_users;

DDL及DCL语句:

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

--查看当前用户下的所有表：
select * from user_tables;

数据增删改语句:

--插入语句
insert into T_OWNERTYPE (ID,NAME) VALUES (1,'居民');
select * from T_OWNERTYPE;
insert into T_OWNERS VALUES (1,'张三丰',1,'1-1','123456',sysdate,1 );
select * from T_OWNERS;

--追加两个字段
ALTER TABLE T_OWNERS ADD
(
REMARK VARCHAR2(20),
OUTDATE DATE
)

--重命名表
ALTER TABLE t_owners RENAME TO t_owner2
--修改语句
update T_OWNERS set adddate=adddate-3 where id=1;
--修改字段名语法：
ALTER TABLE 表名称 RENAME COLUMN 原列名 TO 新列名
--修改两个字段
ALTER TABLE T_OWNERS MODIFY
(
REMARK CHAR(20),
OUTDATE TIMESTAMP
)

--删除数据
delete from T_OWNERS where id=1;
--删除表
truncate table T_OWNERTYPE

删除字段名
--删除一个字段
ALTER TABLE 表名称 DROP COLUMN 列名
--删除多个字段
ALTER TABLE 表名称 DROP (列名 1,列名 2...)

数据类型：

数据类型：

字符型
（ 1） CHAR : 固定长度的字符类型，最多存储 2000 个字节
（ 2） VARCHAR2 :可变长度的字符类型，最多存储 4000 个字节
（ 3） LONG : 大文本类型。最大可以存储 2 个 G

数值型
NUMBER : 数值类型
例如： NUMBER(5) 最大可以存的数为 99999
NUMBER(5,2) 最大可以存的数为 999.99

日期型
（ 1） DATE：日期时间型，精确到秒
（ 2） TIMESTAMP：精确到秒的小数点后 9 位

二进制型（大数据类型）
（ 1） CLOB : 存储字符,最大可以存 4 个 G
（ 2） BLOB：存储图像、声音、视频等二进制数据,最多可以存 4 个 G

sqlplus命令：

1 2	sqlplus 用户名/密码@服务器地址及端口号/全局数据库名例：sqlplus system/12345@192.168.80.10:1521/orcl

删除的区别:

delete 删除的数据可以rollback.
delete 删除可能产生碎片，并且不释放空间.
truncate 是先摧毁表结构，再重构表结构.
delete和truncate都不改表结构，要把表也删除，使用drop table.

--练习数据库建表语句
--建立价格区间表
create  table t_pricetable
(
id number primary key,
price number(10,2),
ownertypeid number,
minnum number,
maxnum number
);

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

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

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

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

--地址表
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
);

create sequence seq_account;

--业主类型
insert into t_ownertype values(1,'居民');
insert into t_ownertype values(2,'行政事业单位');
insert into t_ownertype values(3,'商业');

--地址信息--
insert into t_address values( 1,'明兴花园',1,1);
insert into t_address values( 2,'鑫源秋墅',1,1);
insert into t_address values( 3,'华龙苑南里小区',2,2);
insert into t_address values( 4,'河畔花园',2,2);
insert into t_address values( 5,'霍营',2,2);
insert into t_address values( 6,'回龙观东大街',3,2);
insert into t_address values( 7,'西二旗',3,2);

--业主信息
insert into t_owners values(1,'范冰',1,'1-1','30406',to_date('2015-04-12','yyyy-MM-dd'),1 );
insert into t_owners values(2,'王强',1,'1-2','30407',to_date('2015-02-14','yyyy-MM-dd'),1 );
insert into t_owners values(3,'马腾',1,'1-3','30408',to_date('2015-03-18','yyyy-MM-dd'),1 );
insert into t_owners values(4,'林小玲',2,'2-4','30409',to_date('2015-06-15','yyyy-MM-dd'),1 );
insert into t_owners values(5,'刘华',2,'2-5','30410',to_date('2013-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(6,'刘东',2,'2-2','30411',to_date('2014-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(7,'周健',3,'2-5','30433',to_date('2016-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(8,'张哲',4,'2-2','30455',to_date('2016-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(9,'昌平区中西医结合医院',5,'2-2','30422',to_date('2016-10-11','yyyy-MM-dd'),2 );
insert into t_owners values(10,'美廉美超市',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),3 );

--操作员
insert into t_operator values(1,'马小云');
insert into t_operator values(2,'李翠花');

--地区--
insert into t_area values(1,'海淀');
insert into t_area values(2,'昌平');
insert into t_area values(3,'西城');
insert into t_area values(4,'东城');
insert into t_area values(5,'朝阳');
insert into t_area values(6,'玄武');

--价格表--
insert into t_pricetable values(1,2.45,1,0,5);
insert into t_pricetable values(2,3.45,1,5,10);
insert into t_pricetable values(3,4.45,1,10,null);
insert into t_pricetable values(4,3.87,2,0,5);
insert into t_pricetable values(5,4.87,2,5,10);
insert into t_pricetable values(6,5.87,2,10,null);
insert into t_pricetable values(7,4.36,3,0,5);
insert into t_pricetable values(8,5.36,3,5,10);
insert into t_pricetable values(9,6.36,3,10,null);

--账务表--
insert into t_account values( seq_account.nextval,1,1,1,'2012','01',30203,50123,0,1,sysdate,34.51,'1',to_date('2012-02-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','02',50123,60303,0,1,sysdate,23.43,'1',to_date('2012-03-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','03',60303,74111,0,1,sysdate,45.34,'1',to_date('2012-04-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','04',74111,77012,0,1,sysdate,52.54,'1',to_date('2012-05-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','05',77012,79031,0,1,sysdate,54.66,'1',to_date('2012-06-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','06',79031,80201,0,1,sysdate,76.45,'1',to_date('2012-07-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','07',80201,88331,0,1,sysdate,65.65,'1',to_date('2012-08-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','08',88331,89123,0,1,sysdate,55.67,'1',to_date('2012-09-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','09',89123,90122,0,1,sysdate,112.54,'1',to_date('2012-10-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','10',90122,93911,0,1,sysdate,76.21,'1',to_date('2012-11-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','11',93911,95012,0,1,sysdate,76.25,'1',to_date('2012-12-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','12',95012,99081,0,1,sysdate,44.51,'1',to_date('2013-01-14','yyyy-MM-dd'),2 );

insert into t_account values( seq_account.nextval,2,1,3,'2012','01',30334,50433,0,1,sysdate,34.51,'1',to_date('2013-02-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','02',50433,60765,0,1,sysdate,23.43,'1',to_date('2013-03-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','03',60765,74155,0,1,sysdate,45.34,'1',to_date('2013-04-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','04',74155,77099,0,1,sysdate,52.54,'1',to_date('2013-05-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','05',77099,79076,0,1,sysdate,54.66,'1',to_date('2013-06-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','06',79076,80287,0,1,sysdate,76.45,'1',to_date('2013-07-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','07',80287,88432,0,1,sysdate,65.65,'1',to_date('2013-08-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','08',88432,89765,0,1,sysdate,55.67,'1',to_date('2013-09-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','09',89765,90567,0,1,sysdate,112.54,'1',to_date('2013-10-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','10',90567,93932,0,1,sysdate,76.21,'1',to_date('2013-11-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','11',93932,95076,0,1,sysdate,76.25,'1',to_date('2013-12-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2014-01-14','yyyy-MM-dd'),2 );

insert into t_account values( seq_account.nextval,100,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2014-01-01','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,101,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2015-01-01','yyyy-MM-dd'),2 );

update t_account set usenum=num1-num0;
update t_account set money=usenum*2.45;
commit;