Oracle11g创建表和修改表数据

SQL*Plus: Release 11.1.0.6.0 - Production on 星期三 11月 17 17:38:21 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

请输入用户名: scott
输入口令:

连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table Student(
2 ClassID char(4),
3 StuNum char(4),
4 Name char(6),
5 Age number(2,0),
6 Sex char(1),
7 Birthday date,
8 HomePlace char(6)
9 );

表已创建。

SQL> alter table Student modify(Name char(8));

表已更改。

SQL> alter table Student modify(Sex char(2));

表已更改。

SQL> alter table Student modify(Sex char(1),Name char(6));

表已更改。

SQL> alter table Student modify(ClassID char(6));

表已更改。


SQL> alter session set nls_date_format='mm-dd-yyyy';

会话已更改。

SQL> insert into Student values('zx0821','2142','jack',22,'M','11-23-1989','henan');

已创建 1 行。

SQL> insert into Student values('zx0821','2155','zhang',22,'F','3-7-1991','SH');

已创建 1 行。

SQL> insert into Student values('zx0821','2166','lijun',19,'M','8-7-1991','BJ');

已创建 1 行。

SQL> insert into Student values('zx0821','2177','wang',18,'F','9-15-1992','NJ');

已创建 1 行。

SQL> insert into Student values('zx0821','2166','linhua',19,'F','9-15-1991','ZJ');

已创建 1 行。

SQL> select * from Student ;

CLASSI STUN NAME AGE S BIRTHDAY HOMEPL
------ ---- ------ ---------- - ---------- ------
zx0821 2142 jack 22 M 11-23-1989 henan
zx0821 2155 zhang 22 F 03-07-1991 SH
zx0821 2166 lijun 19 M 08-07-1991 BJ
zx0821 2177 wang 18 F 09-15-1992 NJ
zx0821 2166 linhua 19 F 09-15-1991 ZJ

SQL> update student set birthday='02-19-1989' where stunum='2155';

已更新 1 行。

SQL> select * from Student ;

CLASSI STUN NAME AGE S BIRTHDAY HOMEPL
------ ---- ------ ---------- - ---------- ------
zx0821 2142 jack 22 M 11-23-1989 henan
zx0821 2155 zhang 22 F 02-19-1989 SH
zx0821 2166 lijun 19 M 08-07-1991 BJ
zx0821 2177 wang 18 F 09-15-1992 NJ
zx0821 2166 linhua 19 F 09-15-1991 ZJ

SQL> delete from student where stunum='2166';

已删除2行。

SQL> select * from Student ;

CLASSI STUN NAME AGE S BIRTHDAY HOMEPL
------ ---- ------ ---------- - ---------- ------
zx0821 2142 jack 22 M 11-23-1989 henan
zx0821 2155 zhang 22 F 02-19-1989 SH
zx0821 2177 wang 18 F 09-15-1992 NJ

SQL> insert into student values('zx0821','2166','huang',25,'M',null,'AH');

已创建 1 行。

SQL> select * from Student ;

CLASSI STUN NAME AGE S BIRTHDAY HOMEPL
------ ---- ------ ---------- - ---------- ------
zx0821 2142 jack 22 M 11-23-1989 henan
zx0821 2155 zhang 22 F 02-19-1989 SH
zx0821 2177

wang 18 F 09-15-1992 NJ
zx0821 2166 huang 25 M AH

SQL> desc student
名称 是否为空? 类型
----------------------------------------- -------- -----------

CLASSID CHAR(6)
STUNUM CHAR(4)
NAME CHAR(6)
AGE NUMBER(2)
SEX CHAR(1)
BIRTHDAY DATE
HOMEPLACE CHAR(6)

SQL> commit ;

提交完成。

SQL> alter table student add(mingzhu char(5));

表已更改。

SQL> desc student;
名称 是否为空? 类型
----------------------------------------- -------- ----------

CLASSID CHAR(6)
STUNUM CHAR(4)
NAME CHAR(6)
AGE NUMBER(2)
SEX CHAR(1)
BIRTHDAY DATE
HOMEPLACE CHAR(6)
MINGZHU CHAR(5)

SQL> update student set mingzhu='hanzu' where classid='zx0821';

已更新4行。

SQL> select * from Student ;

CLASSI STUN NAME AGE S BIRTHDAY HOMEPL MINGZ
------ ---- ------ ---------- - ---------- ------ -----
zx0821 2142 jack 22 M 11-23-1989 henan hanzu
zx0821 2155 zhang 22 F 02-19-1989 SH hanzu
zx0821 2177 wang 18 F 09-15-1992 NJ hanzu
zx0821 2166 huang 25 M AH hanzu

SQL> insert into student(classid,stunum,name) values('zx0822','2201','lihong');

已创建 1 行。

SQL> insert into student(classid,stunum,name) values(null,'2201','lihong');

已创建 1 行。

SQL> select * from Student ;

CLASSI STUN NAME AGE S BIRTHDAY HOMEPL MINGZ
------ ---- ------ ---------- - ---------- ------ -----
zx0821 2142 jack 22 M 11-23-1989 henan hanzu
zx0821 2155 zhang 22 F 02-19-1989 SH hanzu
zx0822 2201 lihong
zx0821 2177 wang 18 F 09-15-1992 NJ hanzu
2201 lihong
zx0821 2166 huang 25 M AH hanzu

已选择6行。

SQL> delete from student where birthday=null;

已删除0行。

SQL> delete from student where birthday is null;

已删除 1 行。
SQL> select * from student;

CLASSI STUN NAME AGE S BIRTHDAY HOMEPL MINGZ
------ ---- ------ ---------- - ---------- ------ -----
zx0821 2142 jack 22 M 11-23-1989 henan hanzu
zx0821 2155 zhang 22 F 02-19-1989 SH hanzu
zx0821 2177 wang 18 F 09-15-1992 NJ hanzu

drop table student; (删

除表的结构和数据)
delete from student; 删除所有记录,表结构还在,写日志,可以恢复的,速度慢
truncate table student;(删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快)

SQL> rename student to stu;

表已重命名。

SQL> select * from student;
select * from student
*
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> select * from stu;

CLASSI STUN NAME AGE S BIRTHDAY HOMEPL MINGZ
------ ---- ------ ---------- - ---------- ------ -----
zx0821 2142 jack 22 M 11-23-1989 henan hanzu
zx0821 2155 zhang 22 F 02-19-1989 SH hanzu
zx0821 2177 wang 18 F 09-15-1992 NJ hanzu

SQL> rename stu to student;

表已重命名。

SQL> select * from stu;
select * from stu
*
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> select * from student;

CLASSI STUN NAME AGE S BIRTHDAY HOMEPL MINGZ
------ ---- ------ ---------- - ---------- ------ -----
zx0821 2142 jack 22 M 11-23-1989 henan hanzu
zx0821 2155 zhang 22 F 02-19-1989 SH hanzu
zx0821 2177 wang 18 F 09-15-1992 NJ hanzu

SQL> select name,stunum from student order by stunum desc;

NAME STUN
------ ----
wang 2177
zhang 2155
jack 2142

SQL> select name,age from student order by age asc;

NAME AGE
------ ----------
wang 18
jack 22
zhang 22

SQL> select * from student where name<>'jack';

CLASSI STUN NAME AGE S BIRTHDAY HOMEPL MINGZ
------ ---- ------ ---------- - ---------- ------ -----
zx0821 2155 zhang 22 F 02-19-1989 SH hanzu
zx0821 2177 wang 18 F 09-15-1992 NJ hanzu

SQL> select * from student where name like 'j%';

CLASSI STUN NAME AGE S BIRTHDAY HOMEPL MINGZ
------ ---- ------ ---------- - ---------- ------ -----
zx0821 2142 jack 22 M 11-23-1989 henan hanzu

SQL> select * from student where name like 'zhan_';

未选定行

SQL> select * from student where name like '%j';

未选定行

SQL> select * from student where name like '%j%';

CLASSI STUN NAME AGE S BIRTHDAY HOMEPL MINGZ
------ ---- ------ ---------- - ---------- ------ -----
zx0821 2142 jack 22 M 11-23-1989 henan hanzu


SQL> select * from nls_session_parameters where parameter='NLS_DATE_FORMAT';

PARAMETER
------------------------ VALUE------------------

NLS_DATE_FORMAT mm-dd-yyyy