Oracle 数据库基本语句

---管理员登录
conn sys/oracle@orcl as sysdba;
--解锁scott方案
alter user scott account unlock;
--scott登录
conn scott/tiger@orcl as normal;

 

例题：

创建表 temp id nubmer(8) names varchar2(20) age number(8)
create table temp(
id number(8),
names varchar2(20),
age number(8)
);

1 增加列 address(30)
alter table temp add address varchar2(30);

2 将地址列 长度修改为50
alter table temp modify address varchar2(50);

3 删除地址列
alter table temp drop column address;

4 增加一条数据 1,'张三',30
insert into temp values(1,'张三',30);

5 再增加一个数据 '王五'
insert into temp(names) values('王五');

6 将王五的年龄设置为25
update temp set age=25 where names='王五';

7 将标号是1的人的 姓名修改为李强，年龄修改为33
update temp set names='天佑',age=60 where id=1;

8 将王五删除
delete from temp where names='王五';

9 查询所有的数据
select * from temp;

10 删除temp表
drop table temp;

 

在Oracle 中效果如下：

 

SQL> create table temp(
2 id number(8),
3 names varchar2(20),
4 age number(8)
5 );
Table created

 

SQL> select * from temp;
ID NAMES AGE
--------- -------------------- ---------

 

SQL> alter table temp add address varchar2(30);
Table altered

 

SQL> select * from temp;
ID NAMES AGE ADDRESS
--------- -------------------- --------- ------------------------------

 

SQL> alter table temp modify address varchar2(50);
Table altered

 

SQL> select * from temp;
ID NAMES AGE ADDRESS
--------- -------------------- --------- --------------------------------------------------

 

SQL> alter table temp drop column address;
Table altered

 

SQL> select * from temp;
ID NAMES AGE
--------- -------------------- ---------

SQL> insert into temp values(1,'张三',30);
1 row inserted

SQL> insert into temp(names) values('王五');
1 row inserted

SQL> select * from temp;
ID NAMES AGE
--------- -------------------- ---------
1                   张三                        30
                     王五

SQL> update temp set age=25 where names='王五';
1 row updated

SQL> select * from temp;
ID NAMES AGE
--------- -------------------- ---------
1                       张三                30
                         王五                25

SQL> update temp set names='天佑', age=60 where id=1;
1 row updated

SQL> select * from temp;
ID NAMES AGE
--------- -------------------- ---------
1                       天佑                 60
                         王五                 25

SQL> delete from temp where names='王五';
1 row deleted

SQL> select * from temp;
ID NAMES AGE
--------- -------------------- ---------
1                      天佑                   60

SQL> drop table temp;
Table dropped

SQL> select * from temp;

select * from temp
ORA-00942: 表或视图不存在

 

转载于:https://www.cnblogs.com/mumuda/p/6442455.html