oracle学习笔记之表管理

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as SYSTEM


SQL> conn lihui/lihui;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as lihui




SQL> show user;
User is "lihui"
 
SQL> create table student(Id number(4) primary key,name varchar2(20),sex char(2),birthday date,sal number(7,2));//创建表
 
Table created
 
SQL> create table classes(calssId number(2) primary key,cName varchar2(30));
 
Table created
 
SQL> rename student to stu;//修改表名
 
Table renamed
 
SQL> alter table stu add(classId number(2));//给表增加一列
 
Table altered
 
SQL> desc stu;//查看表结构
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
ID       NUMBER(4)                              
NAME     VARCHAR2(20) Y                         
SEX      CHAR(2)      Y                         
BIRTHDAY DATE         Y                         
SAL      NUMBER(7,2)  Y                         
CLASSID  NUMBER(2)    Y                         
 
SQL> alter table stu modify(name varchar2(30));//修改字段长度
 
Table altered


SQL> alter table stu drop column sal;//删除表里面的一列
 
Table altered
 
SQL> show user;
User is "lihui"
 
SQL> insert into stu values(1,'李慧','女','1992-09-29',1);//插入数据
 
insert into stu values(1,'李慧','女','1992-09-29',1)//oracle里面默认的date数据类型格式为"DD-MON-YY"
 
ORA-01861: 文字与格式字符串不匹配


SQL>  insert into stu values(1,'李慧','女','29-9月-1992',1);
 
1 row inserted
 


SQL>  alter session set nls_date_format = 'yyyy-MM-dd';//修改数据库中date的数据格式
 
Session altered
 
SQL> insert into stu values(2,'周日红','男','1991-06-12',1);
 
1 row inserted
 
SQL>