oracle表格及高级查询知识点

oracle数据库知识点总结

1、表空间
（1）创建表空间

create   tablespace studentspace  
datafile    'D:/oracle /stu.dbf'
size    50M

autoextend on; //开启数据文件自动扩展
（2）表空间的删除

drop    tablespace  studentspace;

2、表格
（1）创建表–例如创建一个学生表

create  table student(
stuno   number(5,0) primary     key,
stuname     varchar2(20)    not null,
sex default('男')     check(sex='男'  or  sex='女')；
)

（2）删除表格

drop    table   student;

（3）增加表格列

alter   table     student    add    
grade   varchar2(10)   not  null;

（4）删除表格列

alter     table   student   drop  column   grade;

（5）修改表格列

alter  table   student   modify  name   varchar2(30);

（6）向表中添加数据

insert  into  student  values  (1,'张山'，‘男’)；

（7）删除表中选定的数据

delete   from  student  where  id=1;

（8）删除表中的所有的数据
1、方法一 ：delete –支持回滚

    delete  from student 

2、方法二 ： truncate –不支持回滚

truncate    table   student;

3、delete 和truncate的区别
a1、 truncate 不能接where 条件，是删除表中的所有的记录，而delete 可以根据条件来删除记录
a2、 truncate 不能删除有外键约束的表中的记录，而delete没有此约束
a3、 truncate 删除的记录不能回滚，也不能存在日志中，而delete删除的能回滚
a4、 truncate 删除的效率比delete删除的高

（9）更改表中的数据
update student set name=’张三’ where id=1;

（10）表格内容查寻
表查询：
语法

select  列1，列2 ，列 3 from
where 条件
group by 分组条件
having  分组后过滤的条件
order   by  (asc升序，desc降序)

where 和having的区别

a、where 后不能跟聚合函数，因为where执行顺序大于聚合函数。
b、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
c、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
（11）子查询
a、使用in关键字的子查询

select  empno  ,ename ,deptno   from    emp
where  deptno   in
(select deptno  from  emp  where  ename ='SCOTT');
外层为父查询，内层查询为子查询

b、使用any 、all的子查询

select  empno  ,ename  ,sal  ,deptno  from  emp e
where sal<any (select  sal  from emp  where deptno=30);
=any等价于in  <any等价于<max   <>all等价于not    <all等价于<min;

c、使用exists或not exists的子查询

select  dname  from dept d  where exists
(select  * from emp e where  d.deptno=e.deptno and sal>3000);

(12)连接
a、内连接-自然连接

select  * from  表1 ，表2  where  表1.列=表2.列；

b、内连接-等值连接

select  * from  表1 inner join 表2  on  表1.列=表2.列；

c、内连接-不等值连接

select  * from  表1 inner join 表2  on  表1.列<>表2.列；

d、左外连接(以左表为准，另一个表的列进行合并)

select  * from  表1 left join 表2  on  表1.列=表2.列；

**
e、右外连接(以右表为准，另一个表的列进行合并)

select  * from  表1 right join 表2  on  表1.列=表2.列；

f、全外连接(两个表的列进行合并)

select  * from  表1 full join 表2  on  表1.列=表2.列；

g、交叉连接

select  * from  表1  cross  join 表2  ;
相当于 select  * from  表1，表2；

3、创建用户操作
（1）创建一个用户密码及指定空间表

create  user    scott   identifide  by  
scott   default  tablespace   studentspace;

（2）修改用户账户不锁定

alter   user  scott     account     unlock;

（3）修改用户登录密码

alter   user    scott   identifide      by  scott;

（4）给用户分配权限 –connet(登录) resoure(增删改操作) dba(管理)

grant   connet  ,   resoure to  scott ;

（5）撤销权限

revoke  connet ,    resoure to      scott;

4、约束
a1、主键约束

altet   table   student     add     
constraint  stu_pk      primary   key   (stuno);

a2、唯一值约束

alter   table   student     add   
constraint      stu_uq      unique (stuid);

a3、外键约束

alter   table   student (主表)        add   
constraint  stuno_fk        foreign     key (stuno) 
references  score(从表)   (stuno);

a4、检查约束

alter   table   student     add     
constraint  sex_ck      check(sex='男'   or  sex='女')；

a5、默认约束

alter   table   student     modify  sex default('男')；

a6、删除约束

alter       table   student drop        constraint  约束名;

5、序列
1、创建序列：oracle数据库提供的一个数据库对象，并且序列产生的数据只能是数字且为一

create  sequence    stu_seq
start   with    1               --启动值
increment   by  1           --增加或减少的量
maxvalue    999                 --最大上限
minvalue    100                 --最小下限
cycle|nocycle                   ---是否循环
cache   10                      --缓存数量

2、序列使用

selete  stu_seq.navel   from    dual;

3、修改序列

alter   sequence    序列名（启动值不能修改其他的都可以修改）；

4、删除序列

drop    sequence    序列名;

6、基础复制数据或表格
1、从已存在表中复制数据到表中（含数据）

create  table   stu_bak 
as
select  *  from    student; 

2、从已存在表中复制数据到表中（不含数据）

create  table   stu_bak2    
as
select  *  from    student   where 1=2; 

3、从已存在的表中的数据复制到另一张表

insert  into    stu_bak2        
select  *   from  student ;

7、函数
单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，比如：MOD(x,y)返回x除以y的余数（x和y可以是两个整数，也可以是表中的整数列）。
常用的单行函数有：
字符函数：对字符串操作。
数字函数：对数字进行计算，返回一个数字。
转换函数：可以将一种数据类型转换为另外一种数据类型。
日期函数：对日期和时间进行处理。
a、聚合函数：
（1）sum(): 对符合条件的记录的指定列求和
（2）avg() 对符合条件的记录的指定列求平均值
（3）max() 对符合条件的记录的指定列求最大值
（4）min() 对符合条件的记录的列求最小值
（5）count() 对符合条件的列进行计数