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: 表或视图不存在