Sql Plus

  • cmd 启动

    Win + R -> sqlplus username/password@数据库名

  • 不退出 Sql Plus 断开与数据路服务器的链接

    DISCONNECT

  • 重新链接或切换用户

    CONNECT username/password as sysdba

命令

*当前登录数据库用户*
show user 

*sql plus 所有设置信息*
show all

*显示数据库服务器参数设置信息*
show parameter 参数名

*临时修改环境变量值*
set sys_option value

执行缓存区内容 run | /
编辑 edit | ed
显示 list
追加 append | input
清除 del
读写 @ | get | save

变量 
& | define key=val
&&

表空间、数据文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
/*创建永久表空间*/
create tablespace my_tb datafile 'd\my_tb\1.dbf' size 20M extent management local;
create tablespace my_tb datafile 'd\my_tb\1.dbf' size 20M extent management local uniform size 1M;
*自动分配*
create tablespace my_tb datafile 'd\my_tb\1.dbf' size 20M 
autoextend on next 5M
maxsize 100M
extent management local
autoallocate;

/*创建临时表空间*/
create temporary tablespace my_temp_tb tempfile 'd\my_temp_tb\1.dbf' size 20M extent management local uniform size 1M;

/*重命名表空间*/
alter tablespace my_tb rename to myTBspace

/*重命名数据文件*/
alter tablespace my_tb offline normal;
1、host rename d\my_tb\1.dbf 1_1.dbf;
2、alter tablespace my_tb rename datafile 'd\my_tb\1.dbf' to 'd\my_tb\1_1.dbf'
alert tablespace my_tb online;

/*数据文件脱机状态*/
shutdwon immediate;
startup mount;
alter database archivelog;
alter database datafile 'd\my_tb\1.dbf' offline;

/*设置默认表空间，临时表空间*/
alter database default tablespace my_tb;
alter database default temporary tablespace my_temp_tb;

/*扩展表空间*/
alter tablespace my_tb add datafile 'd\my_tb\1.dbf' size 100M;
alter database datafile 'd\my_tb\1.dbf' resize 100M;

/*删除表空间*/
drop tablespace my_tb including contents and datafiles;

/*删除数据文件*/
alter tablespace my_tb drop datafile 'd\my_tb\1.dbf';

表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
/*创建表*/
create table student(
  sno char(10),
  sname varchar(30),
  ssex char(2),
  sbirthday date,
  sdept varchar2(30)
);

/*子查询建表*/
create table emp_tem(
  emp_no,emp_name,dept_no
)
as select empno,ename,deptno, from emp where sal > 2000;

/*增加字段*/
alter table student add(
  sphone char(11),
  seamil varchar2(60),
  saddress varchar2(50)
);

/*修改字段*/
alter table student rename column sdept to sdepartment

/*修改类型*/
alter table student modify sno number(10);

/*删除字段*/
alter table student drop column saddress;
alter table student drop column (sphone,seamil);
alter table student set unused (sphone,seamil); *逻辑删除*

alter table student drop unused column;

/*重命名表*/
rename student to student_Table;

/*移动表*/
*确认当前表所在表空间*
select table_name,tablespace_name from user_tables where table_name = 'student';
alter table student move tablespace users;

/*清空数据*/
truncate table student;

/*删除表*/
drop table student;

表约束

作用域
表级约束
字段约束

用途
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/*设置约束条件*/
*NOT NULL*
alter table student modify sname constraint sname_notnull not null;
*UNIQUE*
alter table student add sname constraint sname_unique unique (sname);
alter table student drop (sname);
alter table student drop constraint sname_unique;
*PRIMARY KEY*
alter table student add sname constraint sname_primarykey primary key (sname);
*FOREIGN KEY*
create table class(
  classid number(10) PRIMARY KEY,
  classname varchar(30),
  classcount char(2)
);
create table student(
  sno char(10),
  sname varchar(30),
  ssex char(2),
  sbirthday date,
  sdept varchar2(30),
  classid number(10),
  constraint studentForeign FOREIGN KEY (classid)，
  reference class (classid)，
  on delete cascade，
  constraint sexCheck CHECK (ssex in ('男'，'女'));
);

视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*创建视图*/
create view emp_view 
as 
select empno,ename,sal 
from emp where deptno = 30;
/*复杂视图*/
create view emp_view 
as 
select empno,ename,sal*1.25 new_sal 
from emp where sal*1.25 > 2000;
/*多表视图*/
create view emp_view 
as 
select empno,ename,sal,dname 
from emp,dept 
where emp.deptno =dept.deptno;
/*修改视图*/
create view || replace view 重新同名创建
/*删除视图*/
drop view emp_view

索引

类型
B树索引
位图索引
函数索引
簇索引
散裂簇索引
反序索引
位图连接索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/*B树索引*/
create index sname_index
on student (sname)
tablespace myspace;
/*函数索引*/
create index sname_index
on student (LOWER(sname))
tablespace myspace;
/*位图索引*/
create bitmap index sname_index
on student (ssex)
tablespace myspace;
/*重命名*/
alter index index_name rename to indexName;
/*合并索引*/
alter index index_name coalesce;
/*重建索引*/
alter index sname_index rebuild tablespace myspace;
/*监视索引*/
alter index index_name monitoring usage;
alter index index_name nomonitoring usage;
/*删除索引*/
drop index index_name;

序列

1
2
3
4
5
6
cerate sequence student_sequence
start with 20190809
increment by 1
nocycle
order
nocache;

同义词

1
2
cerate public synonym scores for scott.scores_2014_2015_1;
drop synonym scores;

SQL 分类

  • 数据操纵语言、数据定义语言、数据控制语言、事务控制语言
  • DML：增、删、改、查
  • DDL：创建、修改、删除、更新数据结构
  • DCL：设置或更改数据库用户角色权限
  • TCL：协调相同数据的多个访问的同步
1
2
3
4
5
6
7
8
9
10
11
/*去重*/
select dinstinct deptno from scott
/*汇总*/
select job,count(*) as 人数 from scott group by job;
/*where -> group by -> having*/
select deptno as 部门编号, count(*) as 人数 from scott group by deptno
having count(*) > 3;
/*设置别名*/
select empno,ename,sal,e.deptno,d.deptno,dname from scott.emp e,scott.dept d
where e.deptno = d.deptno;