数据库概念基础 +[Oracle、MySQL区别]

目录

一、数据库概述

数据库的三大范式

Oracle数据库概述

MySQL数据库与Oracle 数据库有什么区别？

Oracle的导入导出有几种方式，有何区别？

数据库备份

二、SQL分类

三、SQL优化

四、简单查询

带条件的查询

五、数据类型

数据类型

日期类型

字符串类型

六、排序分组

排序

聚合函数

分组 和 having

七、集合操作符

并集

交集，差集

八. 联合查询

九、子查询

十、分页

伪列 rownum

开窗函数

数据库去重

---

一、数据库概述

数据库(database、db)指的是长期存在计算机内、有组织、可共享的、大量数据的集合。数据是按照特定的数据模型来组织、存储在数据库中的。

关系型数据库： 它是将数据以关联关系或者二维表的形式存储的数据库。
如 oracle、mysql、db2、sql server、等;
非关系型数据库： 一般是以键值对方式存储数据的数据库。 如 redis、hadoop(hbase) 等

数据库的三大范式

• 第一范式：原子性，要求每一列的值不能再拆分了。
• 第二范式：一张表只描述一个实体（若列中有冗余数据，则不满足）
• 第三范式：所有列与主键值直接相关。

Oracle数据库概述

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

plsql中怎么看运行的快慢：
查看执行计划。在plsql中输入sql语句后，按键F5可进入执 行计划查看界面.
一个过程在哪可以看到浪费时间：查看执行任务。把过程拆分，每条语句之间加上log，记录运行时间，分析慢的过程。

MySQL数据库与Oracle 数据库有什么区别？

应用方面

1. MySQL是一个开源的关系数据库管理系统
   Oracle数据库是一个对象关系数据库管理系统
2. MySQL使用三个参数来验证用户，即用户名，密码和位置
   Oracle使用了许多安全功能，如用户名，密码，配置文件，本地身份验证，外部身份验证，高级安全增强功能等

使用方面

• 自动增长的数据类型方面

        MySQL有自动增长的数据类型。
        Oracle 没有自动增长的数据类型。需要建立一个自增序列。

• group by 用法

        MySQL 中group by 在SELECT 语句中可以随意使用。
        Oracle中如果查询语句中有组函数，那么其他列必须是组函数处理过的或者是group by 子句中的列，否则会报错。

• 引号

        MySQL中可以用单引号、双引号包起字符串
        Oracle 中只可以用单引号包起字符串

        与Oracle相比，MySQL没有表空间，角色管理，快照，同义词和包以及自动存储管理。

• 大小写

        Oracle对所有对象名称都不区分大小写，而某些MySQL对象名称（如数据库和表）区分大小写。

• 数据类型

        MySQL具有CHAR和VARCHAR

        最大长度允许为65,535字节（CHAR最多可以为255字节，VARCHAR为65.535字节）。
        Oracle支持四种字符类型，即CHAR，NCHAR，VARCHAR2和NVARCHAR2;

        所有四种字符类型都需要至少1个字节长; CHAR和NCHAR最大可以是2000个字节。

• 临时表

        MySQL中，临时表是仅对当前用户会话可见的数据库对象，并且一旦会话结束，这些表将自动删除。
        Oracle中临时表的定义与MySQL略有不同，因为临时表一旦创建就会存在

Oracle跟SQL Server 2005的区别？

平台
最大的区别在于平台
oracle可以运行在不同的平台上
SQL server只能运行在windows平台上，由于windows平台的稳定性和安全性影响了SQL server的稳定性和安全性
脚本
oracle使用的脚本语言为PL-SQL
SQL server使用的脚本为T-SQL 微观上： 从数据类型,数据库的结构等等回答

Oracle的导入导出有几种方式，有何区别？

使用Oracle工具 exp/imp
使用PL SQL相关工具
1 导入/导出的是二进制的数据，
2 PL SQL导入/导出的是SQL语句的文本文件

数据库备份

冷备份和热备份都备份物理数据库文件，因而被称为物理备份。
可使用rman恢复数据

冷备份
冷备份是Oracle最简单的一种备份
执行冷备份前必须关闭数据库；然后使用操作系统实用工具或者第三方工具备份所有相关的数据库文件。
优点:能简单快速地备份。能简单快速地恢复。执行简单。
缺点:必须关闭数据库，不能进行点恢复。

热备份
热备份是当数据库正在运行时进行数据备份的过程。
数据库运行在可归档日志模式，适用于24X7不间断运行的关键应用系统。
优点:

        备份时数据库可以是打开的，热备份可以用来进行点恢复。
        初始化参数文件、归档日志在数据库正常运行时是关闭的，可用操作系统命令拷贝。
缺点:

        执行过程复杂。
        由于数据库不间断运行，测试比较困难。必须使用Oracle提供的ocopy工具来拷贝打开的文件。
        热备份可能造成CPU、I/O过载，应在数据库不太忙时进行。

export备份的是数据库对象，因此被称为逻辑备份。
（exp导出，数据泵（expdp命令），export导出）

优点:
能执行对象或者行恢复，备份和恢复速度更快。
能够跨操作系统平台迁移数据库，数据库可一直运行。
缺点:
export并不是冷备份和热备份的替代工具。
冷、热备份可保护介质失效，export备份可保护用户或应用错误。

二、SQL分类

SQL 是数据库语言，通过 SQL可以实现与 Oracle 服务器的通信。

DDL（Data Definition Language）
数据定义语言，用来定义表、列等
如：创建数据表
create drop alter

DML（Data Manipulation Language）
数据操作语言，用来定义数据库记录（数据）
如：添加数据，修改数据，删除数据
insert update delete

DQL（Data Query Language）
数据查询语言，用来查询记录（数据）
如：查询数据
select

DTL (Data Transaction Language)
数据事务语言,用来操作事务。
如：开启事务，提交事务
commit rollback

DCL（Data Control Language)
数据控制语言。用来操作用户和权限
如：创建用户，授权。
grant revoke

三、SQL优化

1. 避免复杂的多表联查

2. 避免使用 ‘*’

ORACLE在解析的过程中，需要将“*”依次转换成所有的列名，会消耗更多的时间和资源。

3. 避免使用耗费资源的操作

带有 DISTINCT、 UNION、 MINUS、 INTERSECT、 ORDER BY的SQL语句会启用SQL引擎执行耗费资源的排序功能，以上 DISTINCT 需要一次排序操作，而其他的至少需要执行两次排序。
通常，带有 UNION、 MINUS、 INTERSECT、 ORDER BY的SQL语句都可以用其他方法重写。

4. 用 EXISTS 替换 DISTINCT

DISTINCT
SELECT DISTINCT NUMBER,NAME FROM A, B WHERE A.NUMBER = B.NUMBER;
EXISTS
SELECT NUMBER,NAME FROM A WHERE EXISTS (SELECT 'X' FROM B WHERE B.NUMBER = A.NUMBER);

5. 用 UNION ALL 替换 UNION

union all    不删除重复
union        删除重复
这俩个结果集会以UNION ALL 的方式被合并，然后再输出最终结果前进行排序。

6. 创建索引，根据索引查询

A表中有10条数据，查询id为8的数据， 
SELECT * FROM A WHERE ID = 8;
没索引的情况下，会从头遍历一次查询8次。

创建索引后，那么会先判断 ID = 8 
1. 属于 1-5 还是 6-10 ，
2. 进入 6-10 后，再判断输入 6 还是 7-8 还是 9-10，
3. 进入 7-8，拿到 id=8的数据。
只需要查询3次

7. 避免再索引列上进行计算

where子句中，如果索引列是函数的一部分。优化器将不会使用索引，而使用全表扫描。

8. 避免再索引列上使用NOT

9. 避免使用前置通配符

SELECT * FROM WHERE A LIKE '%R';
这种情况下，ORACLE将使用全盘扫描。

10. 避免再索引列上使用 IS NULL 和 IS NOT NULL

因为空值不存在索引列中

11. 减少访问数据库的次数

12. 使用DECODE来减少处理时间

13. 用WHERE替换HAVING子句

SQL语句的执行顺序：

from --> 
where -->
group by -->
having -->
order by

14. 用NOT EXISTS替代 NOT IN

在子查询中，NOT IN 子句将执行一个内部的排序和合并（因为对子查询中的表执行了一个全表遍历）
使用NOT EXISTS子句可以有效地利用索引。
1：
    select name,number from A where number not in(select number from B);
2：
    select name,number from A where not exists(select number from B where B.number = A.number);

1中对B表进行了全表扫描
2中的语句对B表进行的是缩小范围的查询。

15. 用>=替代>

    select * from A where number >= 4;
    select * from A where number > 3;
直接锁定4的，然后再往后查询，而第二条需要先排查前3条，再往后查询。

16. 尽量多使用commit

事务是消耗资源的，大事务还容易引起死锁。

17. 用 TRUNCATE替代DELETE

当使用 DELETE 的时候，会产生事务。而 TRUNCATE 则不会。

18. 关于COUNT()

COUNT()函数的如下，依次降低：
COUNT(索引/主键)
COUNT(*)
COUNT(1)

四、简单查询

select

select 列名,列名,...,列名 from 表名;
select和from之间的列名可以用*表示

注释

 单行注释：--
 多行注释：以/* 开始，以*/结尾    

别名

对象名 as 别名
{给表、视图取别名时不能加as）
对象名 别名

带条件的查询

select 列名.. from 表名 where 条件表达式;
条件表达式：
    >    <
    >=  <=
    !=,<>:表示不等于  =等于

in 和 not in

in：后面跟一个集合或者子查询 
not in:和in是相反的
--select * from a where number in (1,2,3);

like 和 not like

%：%出现的地方可以有0个或者多个字符
select * from a where name like '%s%'; --注意大小写
_: _出现的地方有且只能一个任意字符
select * from a where name like '_A%';
/*
like中的转义字符：\
查询姓名中有_的员工信息
select * from a where name like '%\_%' escape '\';
*/

all 和 any

all、any：后面都跟一个集合或者子查询

全满足
>all:    大于集合最大元素 
<all:    小于所有  
--select * from a where number >all(1,2,3,4)
满足一个就行
>any:    大于集合中最小的元素  
<any:    小于最大  

exists 和 not exists
exists:它不和任何列一起使用，后面跟的是一个子查询（查询语句select语句）

如果子查询能查出结果，那么这个条件就是成立的，查不出结果条件不成立
select * from a where exists(select * from a where 1=0); --查不出来

条件连接符

and:    表示并且，只要有一个条件不成立，整体不成立
or：    表示或者，当or连接的两个条件有一个条件成立，整体条件成立，只有两个条件都不成立时，整体才不成立
between v1 and v2：v1的值比v2值小，
v1和v2一般是数字类型，还可以是日期，表示值在v1和v2之间,值是包含value1和value2的。

is null 和 is not null

is null:       表示这个值是空时条件成立
is not null：  表示值不为空时条件成立
select * from a where number is null;

dual
它是oracle内置的一个单行表(虚表)

五、数据类型

数据类型

数据类型
1. int：整数类型（4个字节）
2. double:小数类型（8个字节）
3. float:浮点数（4个字节）
4. number(l,s):表示数值类型，可以表示整数和小数 --Oracle
--最大长度是38，s表示精度默认是0，
    number(5,2):表示整体长度是5，有2位小数   

日期类型

日期类型
5. date:日期，只包含年月日，yyyy-MM-dd
6. time:时间，HH:mm:ss
7. datetime:日期，包含年月日时分秒     yyyy-MM-dd HH:mm:ss
8. timestamp:时间错类型    包含年月日时分秒     yyyy-MM-dd HH:mm:ss     如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
9. year:年份表示

yyyy:表示4位的年
MM：表示两位月
dd:表示两位的天
hh24:表示24小时制的小时
mi:表示分钟
ss：表示秒
day:表示星期

字符串类型

字符串
varchar：字符串
name varchar(20)--姓名最大20个字符
--> zhangsan 8个字符  张三 2个字符
char(l):定长字符串类型，l表示长度
--char(8) 存'abcd' 它长度是8
varchar2(l)：变长字符串类型，l表示长度--最长4000字节 (Oracle)

CLOB/BLOB：
CLOB:存储大文本文件的数据类型，
BLOB:二进制文件存储类型，

LONG:存储长字符串，最大可以存2G的字符串（oracle不建议使用）

六、排序分组

排序

按照第一排序列排序，如果第一个排序字段的值相同时，它会按照第二个排序字段进行排序

select 列名,. from 表名 where 条件表达式 order by asc|desc;
asc表示升序（可不写），desc表示降序
--select * from a order by number, id desc;

聚合函数

count(列名):  它求记录数（数据条数 '*'）
max(列名):     取最大值
min(列名):     取最小值
avg(列名):     取平均值
sum(列名):      求和
distinct:     去重,跟在列的最前面

分组 和 having

select 列名.. from 表名
where 条件
group by 分组列
having 条件
order by 排序列 asc|desc
having:它是对分组后的数据进行筛选，条件表达式中可以使用聚合函数

--平均工资大于2000的id编号和平均工资
select id,avg(sal) from a group by id having avg(sal)>2000;

where和having的异同
where:条件where后面跟的条件比having后的条件先执行，条件中不允许使用聚合函数。
having：条件中可以使用聚合函数，一般having和group by联用。

七、集合操作符

Union：返回两个查询的结果集的并集，不包含重复值
Union ALL：返回两个查询的结果集的并集，包括所有重复⾏。

Minus： 是从第⼀个查询结果减去第二个查询结果,如果有相交部分就减去相交部分, 不包含重复值。差集
INTERSECT：只返回两个查询的公共，是交集，不包含重复值。

并集

集合：每一个sql查询的结果就是一个集合
(1,2,3) (2,3,4)

union和union all 的作用是将多个结果合并在一起显现

并集：取两个集合中所有元素
union all:表示取两个集合的并集，不删除重复元素 --123234
union ：取两个集合的并集，删除重复的元素 --1234

union会自动压缩多个结果集合中的重复结果，同时进行默认规则的排序；
union all 则不作任何处理，也不进行排序。

union执行效率比union all低--（先进行union all把两个集合合并，之后对合并后的数据进行排序，去掉重复元素）
如果允许有重复值，则union不能替换union all。
union内部的select语句需要拥有相同的列且列排列顺序相同，列的数据类型要相似。

交集，差集

交集：取两个集合重叠部分的元素
intersect --23

差集：第一个集合的所有元素减去两个集合重叠部分的元素
minus --14

八. 联合查询

1.交叉连接（笛卡尔积连接）

        将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积.

select 列名,列名,...
from a表  cross join b表;

2.内连接

        是把两个表中符合条件的数据连接为一条数据，如果哪个表中存在不符合连接条件的数据，那么这些数据就会被过滤掉（不显示）

select 列名,...,列名
from a表  inner join b表  on 连接条件
--inner join:中的inner可以省略不写

select a.*,b.* from a join b on a,id=b.id;

3.自连接
        自连接是一个特殊的内连接，内连接一般是两个表的连接，自连接将一个表和它自已进行连接

select a1.*,a2.name from a1,a2 where a1.id=a2.number;

4,不等连接
        不等连接：连接条件是不等条件,（大于、小于、不等于）

select a.*,b.name,b.hisal,b.losal from a join b on a.sal between s.losal and s.hisal;

5.全连接
全连接：它会查出两个表中的所有数据，

1.  如果两个表中的数据都符合连接条件，那么它会和内连接一样，将符合条件的数据连接为一条记录
2.  如果第一表中的一条数据，在第二张表中没有找到和它对应的记录（第二张表没有和第一张表这条数据符合连接条件），那么它同样会显示第一张表的这条记录，同时它对应的第二张表数据的位置会显示为空
3.  同如果第二张的一条记录，在第一张表中没有找到符合连接条件的数据，那么同样会显示第一张表数据的位置显示为空

select 列名,列名,列名,....
from a full outer join b on 连接条件;
--full outer join ：中的outer可以省略
--select a.*,b.* from a full outer join b on a.id=b.id;

6.左外连接

查出左表（left outer join关键字前面的表）的所有数据，根据连接条件去右表中找对应的数据如果找到变显示出来，如果找不到就显示空
select 列名,...from a left outer join b on 连接条件
--left outer join ：其中outer可以省略
--select a.*,b.* from a,b where a.id=b,id(+);  
用（+）表示的左连接或者右连接，如果是左连接，那么符号加在右表的关系列上，如果是右连接，那么符号加在左表的关系列上
/*
--左连接
select d.*,e.* from dept d,emp e where e.deptno(+)=d.deptno; 
--右连接 
select d.*,e.* from dept d,emp e where d.deptno(+)=e.deptno;
*/

7.右外连接
        它会查出右表的所有数据，根据连接条件去左表查找对应的数据，如果找到就显示，如果找不到就显示为空语法：

select 列名,列名,列名,...
from 左表 right outer join 右表 on 连接条件

8.自然连接
        自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。     

select 列名.. from a natural join b using(关系列); 
--select * from a natural join b;        
--select * from a join b using(id); 

九、子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询

1.from后面的子查询
2.where后面的子查询
    (1)单行子查询
    单行子查询：子查询的sql语句只查出一条记录
--select avg(sal) from a where id = 1;
    (2)多行子查询
    多行子查询：子查询的sql语句查出若干条记录
--in、not in、exists、not exists、all、any
--select * from emp where sal>all(select sal from emp where deptno=20);

3.exists、not exists  
exists:后面跟子查询，如果子查询能查出数据，那么条件是真的，如果子查询查不出结果，条件不成立
--select * from dept d where exists(select * from emp where deptno=d.deptno);

4.having后面的子查询
--select deptno,count(*),avg(sal) from emp group by deptno having avg(sal)>1500;

5.select 和from 之间的子查询
--select a.*,(select count(*) from a where sal>a.sal) from a order by sal;

--注意：select 和from之间的子查询，子查询的结果只能是单行单列

十、分页

伪列 rownum

它是oracle数据库内置列，任何表都可以使用，它作用是显示表中数字的行号

--select a.*,rownum from a;
select * from (
select a.*,rownum r from a
)t
where t.r>10;
--小于一个值是可以，不能直接大于某个值,可以直接等于1，不能直接等于

-- 转变成个表查询工资前五
select *
  from (select t.*, rownum r from (select a.* from a order by sal) t) tt
 where tt.r <= 5;

开窗函数

row_number(): 1 2 3 4 5 6 
需要和over分析函数联用，排序的序号和rownum伪列相同。
连续序号，不考虑值相等的情况（值相同序号不相同）

!开窗函数!
over(partition by 分组列 order by 排序列)：
over子句中的分组partition by和group by的分组不同
它不会把数据聚合成一条，在over子名中可以省略

select a.*,row_number()over(partition by id order by sal desc) r from a;
--id分个组 给每个id里的工资排序
-----------------------------------------------------
select * from (
select a.*,row_number()over( order by sal) r from a) t--先走order by 排序然后走 分页
where t.r>4; --工资前四个
-----------------------------------------------------

dense_rank(): 1 2 2 2 3 4 5 6
可以用来做排序，它序号连续，考虑重复数据，如果值相等序号就相同
select a.*,dense_rank()over(order by sal desc) r from a;

rank(): 1 2 2 4 5 6
可以用来做排序，它序号不连续，考虑重复数据，如果值相等序号相同
select a.*,rank()over(order by sal desc) r from a;

MySQL
1. 语法：limit 开始的索引,每页查询的条数;
2. 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数
-- 每页显示3条记录 
SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页

case when

如果表达式成立，那么整个语句值为表达式then后面的值，
如果所有表达式都不成立，会取else后的默认值else语句可以省略

case 
   when 条件表达式1 then
     值
   when 条件表达式2 then
     值
   when 条件表达式3 then
     值
     ...
   else
     默认值
end

/*
select 
sum(case when ssex='男' then 1 else 0 end) 男生人数,
sum(case when ssex='男' then 0 else 1 end) 女生人数 
from a;
*/

数据库去重

rowid

删掉name重复的
-- 根据去重需求
delect from student s where s.rowid > (
select min(t.rowid) from student t where t.name = s. name);
-- 如果s.name = t.name 就把查到最小的rowid删掉
-- rowid 每个都不一样 根据ASCII排序

distinct

select distinct name from student;

group by
 

delete from student group by num having count(name)>1;