oracle数据库学习的相关知识点总结

单表的查询

1.基本语句 select 字段名 from 表名

2.起别名 as

3.连接符 ||

4.去重 distinct

5.排序  order by asc

order by desc 

6.select 字段名 from 表名 where 条件

7.and 和 or

8.分组group by

having，使用group by分组后在进行数据筛选的时候，where中不能出现多行函数，所以使用新的关键字having进行条件筛选

 

 

多表连接：

1.笛卡尔积：sql92： select * from A，B      

2.等值连接：                 

sql92：select * from A,B where A.学号=B.学号 

3.非等值连接：

sql92：select * from B,B mm where B.成绩>mm.成绩

4.自连接：sql92：select * from A mm,A tt where mm.姓名=tt.姓名

5.外连接： 利用此语句 select * from A,B where A.成绩=B.成绩 将两个表等值连接了起来，次结果会将A表或B表中不符合的行删除。此时保留不合格的次行信息，便有了左外连接和右外连接和全连接

定义：

左(外)连接，左表(A)的记录将会全部表示出来，而右表(B)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

右外连接：与左外连接实现效果相反

全连接：实现左外和右外连接的效果。 

左外连接：sql92：select * from A,B where A.成绩=B.成绩(+) order by A.学号 （+在那边就显示那边的null）

右外连接：sql92 : select * from A,B where A.成绩(+)=B.成绩 order by A.学号  

 

 

下1面的用sql99表示：

1.笛卡尔积：

      select * from A cross join  B

2.对sql92等值连接的变形

2.1 自然连接：自动使用多表中所有相同字段（不但值相同，名字也要相同）进行连接（两表中有几个相同，就连接合并几个相同字段）

 select * from A natural join B

2.2 使用using关键字对指定字段进行链接查询，但是必须是同名字段

 select * from A inner join B using(学号)

2.3 使用on关键字可以直接在其后书写链接条件，没有限制 ，想和那个字段连接都可以。

select * from  A inner join B on A.成绩>B.学号

3.外连接

3.1 左外链接 left outer join

select * from A left outer join B on A.成绩=B.成绩 order by A.学号

 

3.2右外链接  right outer join

 

3.3全外链接  full outer join  

select * from A full outer join B on A.成绩=B.成绩 order by A.学号

 

子查询

1.单行子查询：

      问题：查询工资高于平均工资的员工的名字和工资

select * from emp where sal>(select avg(sal) from emp) order by sal

       问题：查询和soctt属于同一部门且工资比他低的员工资料

select * from emp where deptno=(select deptno from emp where ename='SCOTT') and     sal<(select sal from emp where ename='SCOTT')        

 

2.多行子查询

多行子查询学习：其实就使用使用关键字：any  all  in

eg：

问题一：查询工资高于所有SALESMAN的员工信息

select * from emp where sal>(select max(sal) from emp where job='SALESMAN')--单行子查询的写法

select * from emp where sal>all (select sal from emp where job='SALESMAN';

问题二：查询部门20中同部门10的雇员工作一样的雇员信息

select * from emp where deptno='20' and (job='MANAGER' or job='PRESIDENT' or job='CLERK')

 

表的约束

1.主键 ：primary key   外键：foreign key

2.非空约束: not null

3.检查约束条件  : default 值 check(条件)

4.唯一约束 :  unique

 

创建，删除，修改表

1.创建表和字段：创建表的基本语句：create table 表名（字段名 类型，字段名 类型....）

2.修改表

在命令窗口查看表结构： desc 表名

添加新字段 ：

   alter table 表名 add 字段名 类型

 alter table student add phone number(11)

 修改字段类型：    

     alter table 表名 modify 字段名 类型

alter table student modify sname varchar2(200)

 删除字段：    

      alter table 表名 drop column 字段名

alter table student drop column phone

 修改表名 ：

rename 表名 to 新的表名

rename student to stu

rename stu to student

删除表：

    drop table 表名

drop table student

 

插入，更新，删除表的字段数据：

1、插入数据(insert)

   insert into 表名(字段1，字段2，字段3，....)values('值1','值2','值3'.....)

 

insert all

into test_insert(user_name,address) values('aaa','henan')

into test_insert(user_name,address) values('bbb','shanghai')

select * from dual;

2、创建数据的备份

（1）create table 表名 as 查询语句，创建的是和查询结果一样的表，查询结果是什么就会备份一个相同的表

（2）insert into 表名 查询语句，注意：查询出来的结果在结构上必须和插入数据的表相同，字段个数必须相同

（3）注意：备份表只有字段和数据相同，并不会备份约束。

1、备份完整的数据和表

select * from dept;

create table tdept as select * from dept;--备份dept表和数据，只能备份数据和字段

select * from tdept

2、备份完整表

create table tdept1 as select * from dept where 1>2--备份表，不备份数据。

3、备份部分数据和表.create table 表名 as 查询语句，创建的是和查询结果一样的表，查询结果是什么就会备份一个相同的表

create table tdept2 as select dname,loc from dept

4、给备份表添加数据 insert into 表名 查询语句，注意：查询出来的结果在结构上必须和插入数据的表相同，字段个数必须相同

insert into tdept1 select dname,loc from dept where deptno>40

insert into tdept2 select dname,loc from dept where deptno>40

 

更新数据:update

语法结构：update 表名 set 字段名1='字段值1',字段名2='字段值2'，...where 条件

 

删除数据：delete

1)删除语句：delete 表名 where 条件

delete tdept---清空表数据

truncate table tdept---清空表数据建议使用truncate关键字，但是此关键字不能回滚数据

 

序列：

//无论什么序列刚开始都是指向没有值的地方，也可以理解为，指向了0

--创建自定义序列

create sequence aa--创建序列

start with 5 --设置开始位置

increment by 2 --设置步长

select aa.currval from dual --查看序列此时的值

select aa.nextval from dual --获得序列自增一次后的值

--创建测试表

create table teacher(

tid number(10) primary key,

tname varchar(100) not null

)

insert into teacher values(cc.nextval,'张三');

insert into teacher values(cc.nextval,'张三');

-删除序列

--drop sequence 序列名

 

索引：

--创建

create index 索引名 on 表名(字段名)

--删除索引

drop index 索引名

--特点：

--显示的创建，隐式的执行

--注意：

--oracle会自动给表的主键创建索引。

 

视图：

--创建视图

create view 视图名 as select 对外提供的内容 from 真实表名

--删除视图

drop view 视图名

 

分页： 

oracle使用rownum可以实现分页的功能。

oracle对外提供的自动给查询结果编号的rownum关键字，与每行的数据没有关系。

--注意：rownum关键字只能做< <=的判断，不能进行> >=的判断

现有一个数据表aa

--1到2 select e.* from (select rownum r, aa.* from aa where rownum<=9 order by aa.编号) e where e.r<=2 --3到4 select e.* from (select rownum r, aa.* from aa where rownum<=4 order by aa.编号) e where e.r>=3 --5到6 select e.* from (select rownum r, aa.* from aa where rownum<=6 order by aa.编号) e where e.r>=5

 

oracle中的用户：

当前用户介绍：权限级别的分配

-------system：系统账户

-------sys：超级管理员

-------scott：普通用户

1、 创建自定义用户: create user 用户名 identified by 密码；

普通用户不具备创建用户的权限，需要使用system账户进行创建

直接创建好的用户不能登录，还需要使用system进行权限的分配（角色：一系列权限的集合）

create user zyp identified by 123456;

给创建的用户赋予角色。

grant 角色名，角色名...to 用户名；

grant connect to zyp;--赋予链接库权限

grant resource to zyp;--赋予操作资源权限

grant dba to zyp;--赋予DBA角色

grant dba to zyp

删除用户权限

revoke dba from zyp

revoke connect from zyp

2、 删除用户

drop user zyp

 

oracle 怎样查询某用户下的所有表的表名

select * from all_tab_comments -- 查询所有用户的表,视图等。

select * from user_tab_comments -- 查询本用户的表,视图等。

select * from all_col_comments  --查询所有用户的表的列名和注释。

select * from user_col_comments -- 查询本用户的表的列名和注释。

select * from all_tab_columns --查询所有用户的表的列名等信息。

select * from user_tab_columns --查询本用户的表的列名等信息。