一、Oracle数据库简介

Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的、适应高吞吐量的数据库方案。

二、了解数据库

数据库（DataBase）是按照数据结构来组织、储存和管理数据的仓库。随着市场发展出现了各种的数据库，从最简单的储存有各种数据的表格到进行海量数据存储的大型数据库系统在各个方面得到了广泛的应用。数据库已经成为了数据管理的重要技术

常用的数据库有：MySQL、Oracle、DB2等。数据库又分为小型、中型、大型数数据库

分类 产品 特点
小型 access、foxbase 负载小、用户大概100人以内；成本千元内、对安全性要求不高
中型 sqlServler、MySQL 负载量、日访问量在5000-10000;成本在万元内，满足日常安全要求
大型 syBase、DB2、Oracle 海量负载、可以处理海量数据，安全性高，相对较贵

1、DBMS

DBMS（Database Management System）数据管理系统，是一种操纵和管理数据库的大型软件。大部分DBMS提供了数据定义语言DDL和数据操纵语言DML，供用户定义数据库和操作数据库。

2、关系型数据库和非关系型数据库

  • 关系型数据库

    关系型数据库指的是二维表格，关系型数据库就是多个二维表格之间联系所组成的一个数据组织。适合储存复杂的数据。

  • 非关系型数据库

    非关系型数据库指的是键值对形式存在的数据，适用于简单的存储。

3、SQL语言

SQL语言为数据库查询语言，一种介于关系代数与关系演算之间的结构化查询语言。是一个通用的、功能极强的关系型数据库语言，包含三部分：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eRwzpgBg-1584187117156)(G:\java\笔记\Oracle数据库\Oracle数据库\images\SQL 数据化查询语言.png)]

分类 命令
DDL create:创建、drop:删除、alter:修改、rename:重命名、truncate:截断
DML insert:插入、delete:删除、update:更新、select:查询
DCL grant:授权、revoke:回收权力、commit:提交事务、rollback:回滚事务

三、表

表是一个概念，并不是在物理上存储的是一个表。

1、表结构

表由表名、字段(名称+类型+约束)、记录组成。

2、字段类型

类型 说明 例子
number(长度[,精度]) 数值类型,指定数值长度，有几位小数 number(5)、number(5,2)
char(长度) 字符类型，指定长度，不足补空，不能超过长度 char(5)
varchar2(长度) 字符类型，指定长度，写几个算几个，不能超过长度 varchar2(15)
date 日期类型

Tips：长度单位是字节。

3、字段约束

约束 描述
primary key 主键约束
foreign key 外键约束
not null 非空约束
unique 唯一约束
default 默认约束
check 检查约束

4、表和表之间的关系

表和表之间有三种关系：

  • 一对一
    • 一条记录对应另一个表中的一条记录。
    • 例如：用户→身份证
    • 一个人只能有一个身份证号
  • 一对多
    • 一条记录对应另一个表中的多条记录。
    • 例如：用户→多个订单
    • 一个用户可以创建多个订单
  • 多对多
    • 多条记录对应另一个表中的多条记录。
    • 例如：店铺j→商品。
    • 多个店铺可以卖同一个商品，一个商品也可以被多个店铺出售

5、主从表

从表数据依赖于主表，称之为主从表。

主表：被关联依赖的表

从表：关联主表的表

在这里插入图片描述

例如上图：student表的pro字段，设置外键，指向了province表的pro字段，这两个表就形成了一个主从表的关系。student表的pro字段依赖于province表的pro字段，就称student表为从表，province表为主表。

四、创建表

1、三范式

创建表之前需要提到一个行业标准，叫做三范式。不一定非要按照这种标准来创建表，但是建议按照这种标准来。三范式

1.1、一范式(1NF)，保证每列的原子性

保证每列的原子性，就是说每列的数据应该是一个完整的数据，不可分割的数据。不能再一个字段上方式两个数据。

在这里插入图片描述在这里插入图片描述

1.2、二范式(2NF)，非主键部分依赖主键

二范式是在一范式的基础上增加的。为了区分相同的数据，可以给表设置一个主键，用来区分不同数据。主键必须是唯一的。

在这里插入图片描述在这里插入图片描述

1.3、三范式(3NF)，属性不依赖与其它非属性

三范式又是在二范式的基础上增加的。每个表中应该存放和本表有关的数据，和表无关的数据可以在创建一个表用来单独存放。两表通过外键关联。

在这里插入图片描述在这里插入图片描述

2、创建表

创建表的语法为：

1
2
3
4
5
create table 表名(
	字段1 类型 [约束],
	字段2 类型 [约束],
	字段3 类型 [约束].....
)

例如，创建一个employee表

1
2
3
4
5
create table employee(
       e_id number(5) primary key,
       e_name varchar(15) not null,
       e_age number(3) not null
)
  • e_id number(5) primary key：字段名叫e_id，类型为number，长度为5，主键约束
  • e_name varchar(15) not null：字段名叫e_name，类型为字符类型，长度15，非空约束
  • e_age number(3) not null：字段名叫e_age，类型为number，长度3，非空约束

这就是一个最简单的创建表的过程。

创建表也可以不带约束。建议看实际情况决定带不带约束，但是建议有一个主键约束。

1
2
3
4
5
create table employee(
       e_id number(5),
       e_name varchar(15),
       e_age number(3)
)

3、三种添加约束方式

创建表时的约束也有三种添加方式。分别为创建时添加约束、创建快结束时添加约束、创价结束后添加约束。

3.1、创建表时添加约束

创建表时添加约束语法：字段名 类型 约束

1
2
3
4
5
6
7
create table employee(
       e_id number(5) primary key, -- 主键约束
       e_name varchar(15) not null unique, -- 非空约束 唯一约束
       e_age number(3) check(e_age >=0 and e_age <= 150) not null,  -- 检查约束 非空约束
       e_gender number(1) check(e_gender in (1,0)), -- 检查约束
       e_date date default(sysdate) -- 默认约束
)
  • unique：唯一约束，在这个表中e_name字段的值必须时唯一的，不能重复
  • check(e_age >=0 and e_age <= 150)：检查约束，e_age 的值只能时在0-150之间
  • check(e_gender in (1,0)：检查约束：e_gender 的值只能为1或0，1表示男，0表示女
  • default(sysdate)：默认约束，如果没给这个字段值，默认给一个当前时间

上面写在字段后的阿约束就是在创建时添加约束。

3.2、创建表结束时添加约束

创建表结束时添加约束语法：

1
2
3
4
create table 表名(
 字段信息....,
 约束信息...
)
1
2
3
4
5
6
7
8
9
10
11
12
13
create table employee(
       e_id number(5),
       e_name varchar(15),
       e_age number(3),
       e_gender number(1),
       e_date date,
       primary key (e_id), -- 主键约束
       unique(e_name), -- 唯一约束
       check(e_name is not null), -- 检查约束 非空约束
       check(e_age is not null), -- 检查约束 非空约束
       check(e_age >=0 and e_age <= 150), -- 检查约束
       check(e_gender in (1,0)) -- 检查约束
)

3.3、创建表结束后追加约束

创建表结束后追加约束语法：alter table 表名 add 约束 (字段)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 一个没有约束的表
create table employee(
       e_id number(5),
       e_name varchar(15),
       e_age number(3),
       e_gender number(1),
       e_date date
)

alter table employee add primary key (e_id); -- 主键约束
alter table employee add unique (e_name);      -- 唯一约束
alter table employee add check(e_name is not null); -- 非空约束
alter table employee add check(e_age is not null);  -- 非空约束
alter table employee add check(e_age >=0 and e_age <= 150);  -- 检查约束
alter table employee add check(e_gender in (1,0)); -- 检查约束

4、约束添加约束名

约束的好处就是保证数据的正确，不能随意添加。添加约束后如果要添加错误的数据就会出错，例如下图：

在这里插入图片描述

上述就是唯一约束错误，SCOTT.SYS_C007698就是唯一约束的约束名，是系统给的一个默认名字。出现约束错误就需要根据这个约束名来查找错误，系统给的约束名不太便于查找，所以可以自己添加约束名。

添加约束名的方式和添加约束的方式差不多，只不过是在添加约束的时候多加几个字，所以也分为三种添加方式。

4.1、创建表时添加约束名

创建表时添加约束名语法：字段名 类型 constraints 约束名 约束

1
2
3
4
5
6
7
create table employee(
       e_id number(5) constraints pk_employee_e_id primary key, -- 主键约束,约束名为pk_employee_e_id
       e_name varchar(15) constraints employee_e_name_not_null not null constraints employee_e_name_unique unique, -- 非空约束 唯一约束
       e_age number(3) constraints employee_e_age check(e_age >=0 and e_age <= 150) constraints employee_e_age_not_null not null,  -- 检查约束 非空约束
       e_gender number(1) constraints employee_e_gender check(e_gender in (1,0)), -- 检查约束
       e_date date default(sysdate) -- 默认约束
)

Tips：default不能添加约束名

4.2、创建表结束时添加约束名

创建表结束时添加约束名语法：

1
2
3
4
create table 表名(
 字段信息....,
 constraints 约束名 约束信息...
)
1
2
3
4
5
6
7
8
9
10
11
12
13
create table employee(
       e_id number(5),
       e_name varchar(15),
       e_age number(3),
       e_gender number(1),
       e_date date,
       constraints pk_employee_e_id primary key (e_id), -- 主键约束
       constraints employee_e_name_unique unique(e_name), -- 唯一约束
       constraints employee_e_name_not_null check(e_name is not null), -- 检查约束 非空约束
       constraints employee_e_age_not_null check(e_age is not null), -- 检查约束 非空约束
       constraints employee_e_age check(e_age >=0 and e_age <= 150), -- 检查约束
       constraints employee_e_gender check(e_gender in (1,0)) -- 检查约束
)

4.3、创建表结束后追加约束名

创建表结束后追加约束名语法：alter table 表名 add constraints 约束名 约束 (字段)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table employee(
       e_id number(5),
       e_name varchar(15),
       e_age number(3),
       e_gender number(1),
       e_date date
)

alter table employee add constraints pk_employee_e_id primary key (e_id); -- 主键约束
alter table employee add constraints employee_e_name_unique unique (e_name);      -- 唯一约束
alter table employee add constraints employee_e_name_not_null check(e_name is not null); -- 非空约束
alter table employee add constraints employee_e_age_not_null check(e_age is not null);  -- 非空约束
alter table employee add constraints employee_e_age check(e_age >=0 and e_age <= 150);  -- 检查约束
alter table employee add constraints employee_e_gender check(e_gender in (1,0)); -- 检查约束

5、删除约束

既然有添加约束那就肯定有删除约束。

删除约束语法：alter table 表名 drop constraints 约束名

1
alter table employee drop constraints employee_e_gender;

6、外键约束

前面提到过主从表关系，主从表关系就是通过主外键来关联两个表的。都是约束所以外键约束也有三种添加方式。

1
2
3
4
5
-- 主表
create table loc(
       l_id number(5) primary key,
       l_name varchar(18) not null
)

6.1、创建表时添加外键约束

创建表时添加外键约束语法：字段 类型 references = 主表(主表被引用的字段)

1
2
3
4
5
6
7
8
9
-- 从表
create table employee(
       e_id number(5),
       e_name varchar(15),
       e_age number(3),
       e_gender number(1),
       e_date date,
       e_loc number(5) references loc(l_id) --外键约束
)

6.2、创建表结束时添加外键约束

创建表结束时添加外键约束语法：

1
2
3
4
create table 表名(
 字段信息....,
 foreign key(从表外键字段) references 主表(主表被引用的字段)
)
1
2
3
4
5
6
7
8
9
create table employee(
       e_id number(5),
       e_name varchar(15),
       e_age number(3),
       e_gender number(1),
       e_date date,
       e_loc number(5),
       foreign key(e_loc) references loc(l_id) -- 外键
)

6.3、创建表结束后追加外键约束

创建表结束后追加外键约束语法：

alter table 从表 add foreign key (从表外键字段) references 主表(主表被引用的字段)

7、表注释注释

有时候创建了很多表，记不住了每个字段是干嘛的，就可以给表|字段添加注释，方便以后查看。

7.1、给表添加注释

给表添加注释语法：comment on table 表名 is '注释信息'

1
comment on table employee is '员工表'

7.2、给表中字段添加注释

给表中字段添加注释语法：comment on column 表名.字段名 is '注释信息'

1
2
3
4
5
6
comment on column employee.e_id is '员工ID';
comment on column employee.e_name is '员工姓名';
comment on column employee.e_age is '员工年龄';
comment on column employee.e_gender is '员工性别';
comment on column employee.e_date is '员工入职日期';
comment on column employee.e_loc is '员工地址';

五、插入数据

想要操作表中的数据就需要先插入数据。

1、常规插入数据

插入数据语法：insert into 表名 values(字段值....)。

因为没有指定要插入数据字段，所以默认是给表的所有字段添加数据。

1
insert into employee values(1,'张三',20,1,sysdate,1)

如果要插入的数据字段是外键，引用了主表的数据，那么这个字段就只能是主表字段的值。所以要保证主表存在数据，依赖主表的数据插入从表的数据。否则会报错

在这里插入图片描述

2、指定字段插入数据

指定字段插入数据语法：insert into employee (指定字段....) values(字段值....)

1
insert into employee (e_id,e_name,e_age,e_gender,e_loc) values(2,'李四',20,1,2)

3、 从结果集中插入数据

从结果集中插入数据：insert into 表名 select * from[筛选条件]

1
insert into employee1 select * from employee where e_loc = 1

意思就是，从employee表中查找e_loc字段为1的记录，把查出来的结果插入到employee1表中

4、将结果集中的指定字段插入到表中的指定字段

从结果集中插入数据：insert into 表名(字段) select 字段... from[筛选条件]

1
2
insert into employee1(e_id,e_name,e_age,e_loc) select e_id,e_name,e_age,e_loc from employee where e_loc = 2

意思就是，从employee表中查找e_loc字段为1的记录的指定字段的值，把查出来的结果插入到employee1表的指定字段中

六、删除数据

有插入数据那么就有删除数据

1、删除表中全部数据

删除表中的全部数据语法：delete from 表名

1
delete from employee1

2、删除表中的指定数据

删除表中的指定数据语法：delete from 表名 [筛选条件]

1
delete from employee1 where e_loc = 1

意思就是删除employee1表中的e_loc为1的数据

七、修改数据

修改数据同样也不能少

1、修改全部数据

语法：update 表名 要修改的字段 = 要修改的结果[,.....]

1
update employee1 set e_date = sysdate,e_age = 20

意思就是修改employee1表中的e_date字段为当前日期,e_age字段为20

2、修改指定数据

语法：update 表名 要修改的字段 = 要修改的结果[,.....] where 条件

1
update employee1 set e_date = sysdate,e_age = 20 where e_loc = 1

意思就是修改employee1表中的e_loc为1的记录的e_date字段为当前日期,e_age字段为20

八、查找数据

增删改查就查查了。

1、查找全部数据

语法：select * from 表名

1
select * from employee

在这里插入图片描述

2、查找指定数据

语法：select * from 表名 where 条件

1
select * from employee where e_loc = 1

在这里插入图片描述

九、去重、伪列、虚表、别名、字符串、连接符、null

1、distinct(去重)

去除结果集中的重复数据。只能对结果集中完全重复的数据去重

1
2
select e_loc from employee	-- 未去重
select distinct e_loc from employee -- 去重

在这里插入图片描述在这里插入图片描述
左边是没有去重的，右边是去重后的。

2、伪列

伪列就是一个不存在的列

1
select distinct 1+1 from employee

在这里插入图片描述

伪列要是一个表达式

3、虚表

虚表和伪列一样，就是一个不存在的表，但是我们可以使用

1
select 9*9 from dual

在这里插入图片描述

4、别名

别名就是给结果集起一个名字，不然如果字段很长书写有点麻烦。

1
select e_id id from employee

在这里插入图片描述

别名可以是英文也可以是中文

5、字符串

在SQL语言中字符串使用''单引号表示，“”在这里表示是原样输出。字符串可以当作伪劣使用。

1
select '哈哈哈' from dual

在这里插入图片描述

6、连接符

和Java不同的是，这里的连接符是使用||表示。

1
2
-- 给结果集添加前缀
select 'ahh-'||'啊哈哈' from dual

在这里插入图片描述

7、null处理

可以使用一个nvl函数处理null的问题。

nvl(参数1,参数2)：参数1为指定字段，如果参数1为null，则返回参数2的内容

1
select ename,job,sal,nvl(comm,0) from emp

在这里插入图片描述

十、查询过滤

有时候并不会查找所有的数据，这是可以使用where语句进行条件过滤

语法：where 条件

1、比较条件

比较条件 >、<、>=、<=、=、!=、<>、between and

!=和<>都是不等于、between and就是大于等于且小于等于

between and

1
select * from employee where e_age between 18 and 22	-- 筛选出年龄>=18 并且 年龄 <= 22

在这里插入图片描述

2、逻辑判断

and、or、not

and：且

or：或

not：取反

and

1
2
select * from employee where e_age >=18 and e_age <= 22	-- 且

在这里插入图片描述

or

1
select * from employee where e_loc = 1 or e_loc = 2	-- 或

在这里插入图片描述

not

1
select * from employee where not e_loc = 3	-- 取反

在这里插入图片描述

3、结果集操作

union、union all、intersect、minus

union：并集，去除重复，两个结果集的并集，默认规则排序

union all：和上面一样，不过不会去除重复的记录值，不进行排序

intersect：交集，找出重复的记录，两个结果集的交集，默认排序

minus：差集，去掉重复记录，两个结果集的差集，默认排序

union

1
2
3
select * from employee where e_loc = 3
union
select * from employee where e_loc = 1

在这里插入图片描述

union all

1
2
3
select * from employee where e_loc = 1
union all
select * from employee where e_loc = 1

在这里插入图片描述

intersect

1
2
3
select * from emp where mgr = 7839
intersect
select * from emp where deptno = 20

在这里插入图片描述

minus

1
2
3
select * from emp where sal >=1500
minus
select * from emp where sal <=3000

在这里插入图片描述

4、null

is null、is not null、not is null

is null：是否为null

is not null|not is null：不为null

is null

1
select * from emp where comm is null

在这里插入图片描述

not is null|is not null

1
2
select * from emp where comm is not null;
select * from emp where not comm is null;

在这里插入图片描述

5、模糊查询

like：%、_

%：任意个字符

_：一个字符

%

1
select * from employee where e_name like '%二%'

在这里插入图片描述

_

1
select * from employee where e_name like '_美%'

在这里插入图片描述

6、in、exists

in、exists

in：相当于多个or

exists：如果exists有结果则外层保存数据，内查询借助外部数据

in

1
select * from employee where e_age in (18,19,20)

在这里插入图片描述

exists

1
select * from employee e1 where exists(select * from employee e2 where e1.e_id = e2.e_id)

在这里插入图片描述

7、子查询

把一个查询的结果当作数据源或者比较数据使用，查询语句内套查询语句

1
2
-- 查询和ahh在一个地方的员工
select * from employee where e_loc = (select e_loc from employee where e_name = '啊哈哈')

在这里插入图片描述

十一、排序

在SQL语言中，如果想要对结果集进行按照指定字段排序，需要使用Order By，排序不是真正改变真正数据储存的排序，只是对展示的结果集进行排序。

1、升序

升序就是由小到大的排序，例如：1、2、3、4、5、6、7、8、9。

在SQL中使用asc关键字默认是升序

1
2
select * from employee order by e_age asc;
select * from employee order by e_age;	-- asc可以省略

在这里插入图片描述

2、降序

降序就是由小大小的排序，例如：9、8、7、6、5、4、3、2、1

在SQL中使用desc关键字

1
select * from employee order by e_age desc

在这里插入图片描述在这里插入图片描述

3、多个字段排序

可以对多个字段排序，如果第一个字段相等，则按照第二个字段排序….

1
select * from employee order by e_age desc,e_id desc

3、null的排序

null做为一个特殊的存在，也有自己的排序关键字

  • nulls first：所有的null在前面

  • nulls last：所有的null在后面

1
select * from emp order by comm nulls first

在这里插入图片描述

函数分为系统内置函数、自定义函数，根据函数的返回结果分为：单行函数和组函数

  • 单行函数：一条记录返回一个结果
  • 组函数(多行函数)：多条记录返回一个结果

十二、单行函数

1、日期函数

1.1、sysdate|current_date函数

sysdate|current_date返回当前日期

1
2
3
-- 获取当前日期时间
select current_date from dual
select sysdate from dual
运行结果
在这里插入图片描述

1.2、add_months函数

add_months(日期,x)：返回加上x月后的日期

1
2
-- 当前时间加上2月后的时间
select add_months(sysdate,2) from dual
运行结果
在这里插入图片描述

1.3、last_day函数

last_day(日期)：返回当前月份的最后一天

1
2
-- 本月的最后一天
select last_day(sysdate) from dual
运行结果
在这里插入图片描述

1.4、months_between函数

months_between(日期1,日期2)：返回日期1和日期2之间相差的月份数量

1
2
-- 时间差
select months_between(sysdate,sysdate-125) from dual
运行结果

Tips：日期可以参与加减运算

1.5、next_day函数

next_day(日期,’x’)：返回下一个x的日期

1
2
-- 下一个周五
select next_day(sysdate,'星期五') from dual
运行结果
在这里插入图片描述

星期几可以使用数字代替：

1->星期日，2->星期一，3->星期二，4->星期三，5->星期四，6->星期五，7->星期六

2、日期转换函数

2.1、to_char函数

to_char(日期,m)：把日期转换为m样式的字符串

1
2
-- 日期转换为字符串
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual
运行结果
在这里插入图片描述

Tips: 如果字符串样式里要使用中文，需要给中文加上"",hh24代表是24小时制，mi是分，ss是秒

2.2、to_date函数

刚好和上面的相反，把字符串转换为日期

1
2
-- 字符串转换为日期
select to_date('2020-3-11','yyyy-mm-dd') from dual
运行结果
在这里插入图片描述

3、其它函数

3.1、nvl

nvl(参数1,参数2)：如果参数1为null，则返回参数2

1
select ename,nvl(comm,0) from emp
运行结果
在这里插入图片描述

3.2、decode

decode(参数,参数1,参数2,参数3,参数4,…….)：对参数进行判断，如果参数是参数1，就返回参数2，如果是参数3就返回参数4…

1
select decode(deptno,10,'十',20,'二十',30,'三十','默认时-四十') from dept
运行结果
在这里插入图片描述

3.2、case when then else end

类似于java的switch选择语句

1
2
3
4
5
6
7
8
9
10
11
12
select case deptno
         when 10 then
          '十'
         when 20 then
          '二十'
         when 30 then
          '三十'
         else
          ' 默认四十'
       end
  from dept

运行结果
在这里插入图片描述

十三、组函数

常用的组函数：count、max、min、sum、avg

  • count：记录数量统计
  • max：最大值
  • min：最小值
  • sum：求和
  • avg：平均值

Tips：组函数仅在选择列表和having子句中有效，出现组函数，select中只能有组函数或者分组字段

1
select count(ename) 员工数量,max(sal) 最高工资,min(sal) 最低工资,sum(sal) 工资总和,avg(sal) 平均工资 from emp
运行结果
在这里插入图片描述

十四、分组

之前都是单条记录数据筛选分析，分组就可以对一组记录数据进行筛选分析。

group by 字段用来对数据进行分组

1
2
-- 每个部门有几个员工
select deptno,count(1) from emp group by deptno
运行结果
在这里插入图片描述

Tips:

  • 如果出现了分组，select后就不能在出现非分组信息
  • group by 字段关键字不能出现在select中

组过滤

having关键字是组过滤

where：过滤行信息，不能使用组顾虑

having：过滤组，可以使用组函数

1
2
-- 每个部门平均工资高于2000的
select deptno,count(1) from emp group by deptno having avg(sal) > 2000
运行结果
在这里插入图片描述

十五、表连接

当获取的数据不是来源于同一张表，而是来源于多张表的时候就需要用到表连接。

1、92连接

1.1、笛卡尔积

在这里插入图片描述
两个集合A和B，A*B就是笛卡尔积

course表

在这里插入图片描述

teacher表

在这里插入图片描述

1
select * from course,teacher
运行结果
在这里插入图片描述

1.2、等值连接

等值连接，就是在笛卡尔积的基础上去条件相同的值

1
select * from course c,teacher t where c.tno = t.tno
运行结果
在这里插入图片描述

1.3、非等值连接

非等值连接：>、<、!=、<>、between and

1
2
-- 查询员工姓名，工资及等级
select ename,sal,grade from salgrade s,emp e where e.sal between s.losal and s.hisal
运行结果
在这里插入图片描述

1.4、自连接

特殊的等值连接，来自于一张表

1
2
-- 员工的名字和员工的上级名字
select e1.ename 员工,e2.ename 上级 from emp e1,emp e2 where e1.mgr = e2.empno
运行结果
在这里插入图片描述

1.5、外连接

在上述的自连接有一个问题，缺一条记录。那是因为有一条的记录的上级为null，为了解决这一问题，可以使用外连接解决。

1
2
-- 员工的名字和员工的上级名字
select e1.ename 员工,e2.ename 上级 from emp e1,emp e2 where e1.mgr = e2.empno(+)
运行结果
在这里插入图片描述

就多加了一个+号就解决了这个问题，加号对面的表，也就是上面的e1表是主表，如果主表在左边就是左连接，在右边就是右连接

2、99连接

2.1、内连接

2.1.1、交叉连接(笛卡尔积)

和92链接一样，99连接也有笛卡尔积,不过99连接使用指定关键字[inner] cross join实现。

1
select * from emp inner cross join dept;
运行结果
在这里插入图片描述

Tips：在内连接中可以省略inner关键字。

2.1.2、自然连接

在92连接中，为了避免出现笛卡尔积现象，可以使用等值连接来避免，当然在99语法中也可以同样避免。在99语法中叫做自然连接，也算是等值连接。使用natural join关键字实现。

1
select * from emp natural join dept
运行结果
在这里插入图片描述

使用自然连接不需要指定字段，系统会自动按照两个表中的同名字段或主键进行关联。

2.1.3、join using

上面的自然连接不用指定字段，但是如果有多个同名字段，那么就可以使用指定的字段进行关联，在99语法中使用join using关键字实现。

1
select * from emp join dept using(deptno)
运行结果
在这里插入图片描述

2.1.4、非等值连接

和92语法一样99语法也有非等值连接，使用关键字join on实现。join on可以实现等值连接，也可以实现非等值连接。

2.1.4.1、等值连接
1
select * from emp e join dept d on e.deptno = d.deptno
运行结果
在这里插入图片描述
2.1.4.2、非等值连接
1
select * from emp e join salgrade s on e.sal between s.losal and s.hisal
运行结果
在这里插入图片描述

2.2、外连接

和内连接不同的是，内连接只要满足条件就可以显示，外连接不仅包含满足条件的还包含不满足条件的，外连接分为左连接和右连接，左连接就是，在不满足条件的情况下包含左边的表的所有记录数据，右连接就是包含右边表的所有记录数据。

99语法的外连接和92语法的外连接稍微不同，99语法中不再是使用+，而且多了一个全连接。

2.2.1、左连接

在99语法中左连接使用left join

1
select * from emp e1 left join emp e2 on e1.mgr = e2.empno
运行结果
在这里插入图片描述

2.2.2、右连接

99语法中右连接使用right join

1
select * from emp e1 right join emp e2 on e1.mgr = e2.empno
运行结果
在这里插入图片描述

2.2.3、全连接

在99语法中还有一个全连接，不管满足不满足条件，左边和右边的表都全部显示，相互补充null。使用full join on|using

1
select * from emp e1 full join emp e2 on e1.mgr = e2.empno
运行结果
在这里插入图片描述

十六、视图

视图是一个虚拟的表，可以当作一个真实的表使用。视图也有行和列数据，但是视图并不在数据库中以储存数据的形式存在。视图里的数据来自于定义视图查询所引用的表，并且在引用视图时动态生成（视图不是真是存在磁盘上的）。

  • 简化：select查询语句
  • 重用：封装select 语句
  • 隐藏：隐藏内部细节
  • 区分：相同数据不同查询

1、视图的创建

不是所有的用户都有创建视图的权限，如果没有权限需要提权。

  • 授权：gran t dba to 用户;
  • 回收：revoke dba from 用户;

视图创建的语法：

create or replace view 视图名称 as 查询语句;

1
create create or replace view vw_sstudent as select * from sstudent;

Tips：

  • 如果要修改视图只需要更改查询语句再次执行创建视图的SQL语句就行。
  • 如果修改源数据则视图中的数据也会发生改变。

2、删除视图

对视图的删除操作不会影响到源表

删除视图的语法：

drop view 视图名称

1
drop view vw_sstudent;

十七、索引

索引时数据库的对象之一，用于加快数据库的检索。

索引时提查询速度的一种手段，但不是适用于任何场景。如果数据很少不建议使用，数据量特别多使用才有明显效果，否则很有可能会降低查询速度。

1、索引的创建

给表设置属性的时候会默认自动生成一个索引。

创建索引的语法：

create index 索引名称 on 表名(字段名...);

1
create index index_sal on emp(sal);

2、删除索引

删除索引的语法：

drop index 索引名称;

1
drop index index_sal;

十八、rowid和rownum

rowid是Oracle的一个重要概念，用于定位数据库中一条记录的一个相对位移的地址值。用rowid来定位记录时最快的。

rownum是一种伪列，它会根据返回的记录审生成一个有序的数字。

1、rowid

1
select s.*,rowid from sstudent s
运行结果
在这里插入图片描述

每条记录的rowid是唯一的，可以用来快速定位到一条记录。

2、rownum

rownum是一个伪列

1
select s.*,rownum from sstudent s
运行结果
在这里插入图片描述

mysql数据库里可以使用limit截取数据，模仿分页，oracle里没有这个，但是可以使用rownum实现。

rownum模仿实现分页

数据少，所以模拟设置为每页显示2条数据

1
select * from (select s.*,rownum n from sstudent s) where n between 3*2-1 and 3*2
运行结果
在这里插入图片描述

通过这条命令可以实现分页查询数据，3表示的是3页，2是每页显示2条记录