oracle特有的操作命令,Oracle操作命令大全（备查）

备注：使用SQL*PLUS

1.登陆系统用户：

system/密码

2.sys用户登陆：

connect sys/root as sysdba;

3.查看当前用户：

show user；

4.选择查看数据字典：

select username form dba_users;

select tablespace_name from dba_tablespaes;

5.启用scott用户：

alter user username account unlcock;

6.scott用户登录：

scott/tiger

7.解锁system：

sqlplus / as sysdba

SQL>alter user system account unlock;

8.查看数据字典：

desc dba_users;

desc dba_tablespaces;

desc user_constraints;

9.创建永久表空间：

SQL> create tablespace test1_tablespace

2 datafile ’ test1file.dbf’ size 10m;

10.创建临时表空间：

SQL> create temporary tablespace temptest1_tablespace

2 tempfile ‘tempfile1.dbf’ size 10m;

11.查看永久表空间位置:

SQL> desc dba_data_files;

12.查看test1_tablespace位置：

SQL> select file_name from dba_data_files where tablespace_name=’TEST1_TABLESPACE’;

13.修改表空间状态：

alter tablespace test1_tablespace

2 offline；

14.增加表空间：

SQL> alter tablespace test1_tablespace2 add datafile ‘tast

2_file.dbf’size 10m;

15.查看表空间：

SQL> select file_name from dba_data_files where tablespace_name=’TEST1_TABLESPACE’;

16.删除数据文件：

SQL> alter tablespace test1_tablespace

2 drop datafile ‘test2_file.dbf’;

17.删除表空间：

drop tablespace test1_tablespace including contents;

18.创建表：

SQL> create table userifo

2 (id number(6.0),

3 username varchar2(20),

4 userpwd varchar2(20),

5 email varchar2(30),

6 regdate date);

19.查血表数据：

SQL> desc userifo

20.增加数据：

SQL> alter table userifo

2 add remarks varchar2(500);

21.修改数据：

SQL> alter table userifo

2 modify userpwd number(6,0);

22.删除表数据：

SQL> alter table userifo

2 drop column remarks;

23.重命名数据：

SQL> alter table userifo

2 rename column email to new_email;

24.清空表数据：

SQL> truncate table new_userifo;

25.删除表：

SQL> drop table new_userifo;

26.增加内容：

SQL> insert into userinfo

2 values(1,’1’,’xxx’,’xxx@163.com’,sysdate);

27.查询内容：

SQL> select userpwd,email from userinfo;

SQL> select from userinfo;

28.插入内容：

SQL> insert into userinfo(id,username,userpwd)

2 values(2,’yyy’,’123’);

29.复制表内容：

SQL> create table userinfo_new

2 as

3 select from userinfo；

SQL> create table userinfo_new1

2 as

3 select id,username from userinfo;

SQL> insert into userinfo_new

2 select * from userinfo;

SQL> insert into userinfo_new(id,username)

2 select id,username from userinfo;

30.更新表内容：

SQL> update userinfo

2 set userpwd=’111’,email=’111@126.com’;

SQL> update userinfo

2 set userpwd=’yyy’

3 where username=’1’;

31.删除内容：

SQL> delete from userinfo

2 where username=’yyy’;

32.创建非空约束：

SQL> create table userinfo_1

2 (id number(6.0),

3 username varchar2(20) not null,

4 userpwd varchar2(20) not null);

33.修改约束表空间：

SQL> alter table userinfo

2 modify username varchar2(20) not null;

SQL> alter table userinfo

2 modify username varchar2(20) null;

34.创建主约束空间：

SQL> create table userinfo_p

2 (id number(6.0) primary key,

3 username varchar2(20),

4 userpwd varchar2(20));

SQL> create table userinfo_p1

2 (id number(6.0),

3 username varchar2(20),

4 userpwd varchar2(20),

5 constraint pk_id_username primary key(id,username));

35.查找约束：

SQL> select constraint_name from user_constraints where table_name=’USERINFO_P1’;

36.添加主键：

SQL> alter table userinfo

2 add constraint pk_id primary key(id);

37.查找主键信息：

SQL> select constraint_name from user_constraints where table_name=’USERINFO’;

38.更改主键的名字：

SQL> alter table userinfo

2 rename constraint pk_id to new_pk_id;

39.禁用/启动主键：

SQL> alter table userinfo

2 disable constraint new_pk_id;

enable启动

40.查看约束状态：

SQL> select constraint_name,status from user_constraints where table_name=’USERINFO’;

41.删除约束：

SQL> alter table userinfo

2 drop constraint new_pk_id;

SQL> alter table userinfo

2 drop primary key;

42.创建表时设置键约束(从表当中相应的外键值必须是主表里面的值或者是空值)：

主表：

SQL> alter table typeinfo

2 rename column type to typeid;

从表：

SQL> create table userinfo_f

2 (id varchar2(10) primary key,

3 username varchar2(20),

4 typeid_new varchar2(10) references typeinfo(typeid));

主表里的值：

SQL> insert into userinfo_f(id,typeid_new)values(1,1);

空值：

SQL> insert into userinfo_f(id,typeid_new)values(2,null);

43.创建表时添加外键约束：

SQL> create table userinfo_f3

2 (id varchar2(10) primary key,

3 username varchar2(20),

4 typeid_new varchar2(10),

5 constraint fk_typeid_new1 foreign key(typeid_new)references typeinfo(typeid) on delete cascade)

44.修改表时添加外键约束：

SQL> alter table userinfo_f4

2 add constraint fk_typeid_alter foreign key(typeid_new) references typeinfo(typeid);

45.禁用/启动外键约束：

SQL> alter table userinfo_f4

2 disable constraint FK_TYPEID_ALTER;

46.删除外键约束：

SQL> alter table userinfo_f4

2 drop constraint FK_TYPEID_ALTER;

47.创建表时设置唯一约束：

SQL> create table userinfo_u

2 (id varchar2(20) primary key,

3 username varchar2(20) unique,

4 userpwd varchar2(20));

SQL> create table userinfo_u1

2 (id varchar2(10) primary key,

3 username varchar2(20),

4 constraint un_username unique(username));

48.修改表时添加唯一约束：

SQL> alter table userinfo_u2

2 add constraint un_username_new unique(username);

49.禁用唯一约束：

SQL> alter table userinfo_u2

2 disable constraint UN_USERNAME_NEW;

50.删除唯一约束：

SQL> alter table userinfo_u2

2 drop constraint UN_USERNAME_NEW;

51.创建表时设置检查约束：

SQL> create table userinfo_c

2 (id varchar2(10) primary key,

3 username varchar2(20),

4 salary number(5,0) check(salary>0));

SQL> create table userinfo_c1

2 (id varchar2(10) primary key,

3 username varchar2(20),

4 salary number(5,0),

5 constraint ck_salary check(salary>0));

52.在修改表时添加检查约束：

SQL> alter table userinfo_c3

2 add constraint ck_salary_new check(salary>0);

53.禁用检查约束：

SQL> alter table userinfo_c3

2 disable constraint CK_SALARY_NEW;

54.删除检查约束：

SQL> alter table userinfo_c3

2 drop constraint CK_SALARY_NEW;

55.设置别名：

SQL> col username heading 用户名;

SQL> select id as 编号,username as 用户名,salary 工资 from users;(as可用空格代替)

56.创建表时设置检查约束：

列级：

SQL> create table userinfo_c

2 (id varchar2(10) primary key,

3 username varchar2(20),

4 salary number(5,0) check(salary>0));

表级：

SQL> create table usrinfo_c1

2 (id varchar2(10) primary key,

3 username varchar2(20),

4 salary number(5,0),

5 constraint ck_salary check(salary>0));

57.不显示相同的用户名：

SQL> select distinct username as 用户名 from users;

58.带条件的查询：

SQL> select username from users where salary>800;

SQL> select username,salary from users where id=’3’;

SQL> select username from users where salary>800 and salary<>1800.5;(<>不等于)

SQL> select from users where username=’aaa’ or salary>2000;

SQL> select from users where username= ‘aaa’ or (salary>800 and salary<=2000);

SQL> select from users where not( username=’aaa’);

59.逻辑运算符的优先级：

1.not

2.and

3.or

60.模糊查询： _ 代替一个字符 % 代替多个字符

SQL> select from users where username like ‘a%’;

SQL> select from users where username like ‘_a%’;

SQL> select from users where username like ‘%a%’;

61.范围查询：

SQL> select from users where salary between 800 and 2000;

SQL> select from users where salary not between 800 and 2000;

SQL> select from users where username in (‘aaa’,’bbb’);

SQL> select from users where username not in (‘aaa’,’bbb’);

62.对查询结果进行排序：(desc 降序 asc 升序)

SQL> select from users order by id desc;

SQL> select from users order by id desc,salary asc;

63.使用运算符/函数对查询结果进行更改：

1.SQL> select id,username,salary+200 from users;

2.case..when语句使用：

SQL> select username,case username when ‘aaa’ then ‘计算机部门’

2 when ‘bbb’ then ‘市场部门’ else ‘其他部门’ end as 部门

3 from users;

SQL> select username,case when username=’aaa’ then ‘计算机部门 ’

2 when username=’bbb’ then ‘市场部门’ else ‘其他部门’ end as 部门

3 from users;

SQL> select username,case when salary<800 then ‘工资低’

2 when salary>5000 then ‘工资高’ end as 工资水平

3 from users;

3.decode函数的使用：

SQL> select username,decode(username,’aaa’,’计算机部门’,’bbb’,’市场部门’,’其他’)as 部门

2 from users;