【学习分享】Oracle笔记

Oracle笔记 作者：Nomo

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;