oracle_常用初级命令

小结

1.DDL:数据库定义语言。

create、alter（add、modify、drop）、drop；

2.DML：数据库操作语言。

insert、delete、update；

select（where、group by。。。having、order by）、

（and、or、not、in、like、between。。。and、is null、is not null）；

3.DCL：数据库控制语言。

grant。。。to、revoke。。。from；

4.TCL：事务控制语言。

commit、rollback；

--DDL(create、drop、alter（add、drop、modify）)

----1.1创建表
create table person(
 name varchar(20) not null,
 age number(3)
 );


----1.2删除表
drop table person;


----1.3增加字段
------1.3.1增加单个字段
alter table person add(sex char(2));
------1.3.2增加多个字段
alter table person add(birthday varchar(10),hobby varchar(20));


----1.4删除字段
------1.4.1删除单个字段
alter table person drop(sex);
------1.4.2删除多个字段
alter table person drop(birthday,hobby);


----1.5修改字段
------1.5.1修改单个字段
alter table person modify(sex char(5));
------1.5.2修改多个字段
alter table person modify(age number(10),sex varchar2(10));


----1.6查询表
select * from person;






--DML(insert、delete、update、select)
----2.1增加
insert into person values('goxp',20,'男');
insert into person values('gxy',21,'女');
insert into person values('gt',22,'男');
insert into person values('gxtp',23,'男');
insert into person values('gtxp',24,'男');


----2.2删除
delete from person where age=20;


----2.3修改
update person set age=18 where age=20;


----2.4查询(where【group by】【having】【order by】)
select * from person;
-------2.4.1where
select * from person where age>22;
-------2.4.2group by。。。having
select age from person group by age having age>22;
-------2.4.3order by(desc:降序；asc:升序默认的)
select * from person order by age desc;


----2.5其它查询(and、or、not、in、like、between。。。and、is null、is not null)
-------2.5.1and
select * from person where age=20 and name='gxp';


-------2.5.2or
select * from person where age=20 or age=22;


-------2.5.3not
select * from person where not age=20;


-------2.5.4in
select * from person where age in(20,23);


-------2.5.5like
select * from person where NAME like '_x%';


-------2.5.6between。。。and
select * from person where age between 20 and 22;


-------2.5.7is null
select * from person where sex is null;


-------2.5.8is not null
select * from person where sex is not null;




--DCL(grant、revoke)
----3.1grant。。。to
------授权
grant dba to scott;


----3.2revoke。。。from
------取消权限
revoke dba from scott;






--TCL(commit、rollback)
----4.1commit
------提交
insert into person values('gxl',23,'女');
commit;


----4.2rollback
------与提交对立
insert into person values('gxl',23,'fm');

rollback;