__________________________________________________________
Table
__________________________________________________________
Creat Table//建表
1、数据类型
1>char
create table lhj_char(
a char(10),
b varchar(10),
c varchar2(10)
);
char(10)//固定长度,不足10个用空格填补,如果字段经常
更新建议使用char类型
varchar(10),//可变长的sql标准的类型
varchar2(10)//可变长的oracle自己的类型
insert into lhj_char values('aaa','aaa','aaa');
select length(a),length(b),length(c) from lhj_char;
===========================================================
2>num
create table lhj_num(
a number,
b number(3),
c number(5,2)
);
b number(3),//相当于数据只会存放整型
c number(5,2)
5代表插入数的总的位数
2代表小数点后的小数位数1234.1不可以插入
insert into lhj_num(a) values(1234);
insert into lhj_num(a) values(123456789123);
insert into lhj_num(a) values(1.2);
以上三条记录都可以成功插入
insert into lhj_num(b) values(1234);
ERROR at line 1:
ORA-01438: value larger than specified precision
allows for this column
insert into lhj_num(b) values(123);
insert into lhj_num(b) values(1.2);
只保留整数部分
insert into lhj_num(c) values(123);
create table lhj_num1(
a number,
b number(3),
c number(5,2),
d int
);
===========================================================
3>date
create table lhj_date(
a date
);
insert into lhj_date values(sysdate);
insert into lhj_date values('25-DEC-87');
日-月-年的格式可以自动加入表中
insert into lhj_date values('DEC-25-87');
ERROR at line 1:
ORA-01858: a non-numeric character was found
where a numeric was expected
SQL> select * from lhj_date;
A
---------
24-SEP-08
25-DEC-87
23-SEP-80
24-SEP-08
23-SEP-08
select * from lhj_date;
查询显示系统默认的日期格式
to_date
insert into lhj_date
values(to_date('SEP-23-80','MM-DD-YY'));
insert into lhj_date
values(to_date('SEP-23-2008','MM-DD-YY'));
insert into lhj_date
values(to_date('2008-9-24','YYYY-MM-dd'));
to_char
用指定的格式显示日期
select to_char(a,'yyyy-mm-dd') from lhj_date;
insert into lhj_date values(sysdate);
select to_char(sysdate,'DD-
Table
__________________________________________________________
Creat Table//建表
1、数据类型
1>char
create table lhj_char(
a char(10),
b varchar(10),
c varchar2(10)
);
char(10)//固定长度,不足10个用空格填补,如果字段经常
更新建议使用char类型
varchar(10),//可变长的sql标准的类型
varchar2(10)//可变长的oracle自己的类型
insert into lhj_char values('aaa','aaa','aaa');
select length(a),length(b),length(c) from lhj_char;
===========================================================
2>num
create table lhj_num(
a number,
b number(3),
c number(5,2)
);
b number(3),//相当于数据只会存放整型
c number(5,2)
5代表插入数的总的位数
2代表小数点后的小数位数1234.1不可以插入
insert into lhj_num(a) values(1234);
insert into lhj_num(a) values(123456789123);
insert into lhj_num(a) values(1.2);
以上三条记录都可以成功插入
insert into lhj_num(b) values(1234);
ERROR at line 1:
ORA-01438: value larger than specified precision
allows for this column
insert into lhj_num(b) values(123);
insert into lhj_num(b) values(1.2);
只保留整数部分
insert into lhj_num(c) values(123);
create table lhj_num1(
a number,
b number(3),
c number(5,2),
d int
);
===========================================================
3>date
create table lhj_date(
a date
);
insert into lhj_date values(sysdate);
insert into lhj_date values('25-DEC-87');
日-月-年的格式可以自动加入表中
insert into lhj_date values('DEC-25-87');
ERROR at line 1:
ORA-01858: a non-numeric character was found
where a numeric was expected
SQL> select * from lhj_date;
A
---------
24-SEP-08
25-DEC-87
23-SEP-80
24-SEP-08
23-SEP-08
select * from lhj_date;
查询显示系统默认的日期格式
to_date
insert into lhj_date
values(to_date('SEP-23-80','MM-DD-YY'));
insert into lhj_date
values(to_date('SEP-23-2008','MM-DD-YY'));
insert into lhj_date
values(to_date('2008-9-24','YYYY-MM-dd'));
to_char
用指定的格式显示日期
select to_char(a,'yyyy-mm-dd') from lhj_date;
insert into lhj_date values(sysdate);
select to_char(sysdate,'DD-