MySQL与Oracle数据库必备知识

你的名字 2021-03-12 14:39 532阅读 0赞

**MySQL**:  
执行顺序:  
1. from: 组装来自不同数据源的数据;  
2. on:  
3. where: 子句基于指定的条件对记录行进行筛选；  
4. grop by:  
5.使用聚集函数进行计算；  
6.计算所有的表达式；  
7. select:  
8. grop by:  
聚合函数:  
1. avg(): 平均数;  
2. sum(): 求和;  
3. max(): 最大值;  
4. min(): 最小值;  
5. count(): 累加;  
6. group by: 分组;  
类型转换:  
1. cast(value as type);  
2. convert(value,type);

连接方式:  
外连接：  
左连接（左外连接）：以左表作为基准进行查询，左表数据会全部显示出来，右表如果和左表匹配的  
数据则显示相应字段的数据，如果不匹配则显示为 null。  
右连接（右外连接）：以右表作为基准进行查询，右表数据会全部显示出来，左表如果和右表匹配的  
数据则显示相应字段的数据，如果不匹配则显示为 null。  
全连接：先以左表进行左外连接，再以右表进行右外连接。  
内连接：  
显示表之间有连接匹配的所有行。  
SQL注入问题:  
1. 预编译防止sql注入,使用? 占位符;  
2. MyBatis中的\#也可以防止sql注入,$不能防止sql注入;  
MySQL性能优化:  
1. 当只有一行数据时使用 limit 1:  
查询时如果已知只得到一条数据,加上limit 1 会提高性能,因为当mysql数据库查询到一条数据后会停止搜索,  
而不是查询下一条是否符合知道查询完所有记录。  
2. 选择正确的数据库引擎：  
Mysql 中有两个引擎 MyISAM 和 InnoDB。  
MyISAM：适合查询大量数据，对于update操作会使整个表锁起来，在update执行完之前无法继续读，  
另外， MyISAM 对于 select count(\*)这类操作是超级快的。  
InnoDB：InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用会比 MyISAM 还慢，  
但是支持“行锁”，所以在写操作比较多的时候会比较优秀。并且，它支持很多的高级应用，例如：事物。  
3. 使用not exists 替代 not in：  
Not exists 用到了连接能够发挥已经建立好的索引的作用，not in 不能使用索引。 Not in 是最  
慢的方式要同每条记录比较，在数据量比较大的操作红不建议使用这种方式。  
4. 对操作符的优化，尽量不采用不利于索引的操作符：  
如： in not in is null is not null <> 等  
某个字段总要拿来搜索，为其建立索引：  
Mysql 中可以利用 alter table 语句来为表中的字段添加索引，语法为： alter table 表明  
add index (字段名)；

MySQL存储引擎：  
1. MyISAM 是 MySQL 官方提供默认的存储引擎，其特点是不支持事务、表锁和全文索引，对于一些 OLAP（联机  
分析处理）系统，操作速度快；  
每个 MyISAM 在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、 .MYD (MYData，  
存储数据)、 .MYI (MYIndex，存储索引)。这里特别要注意的是 MyISAM 不缓存数据文件，只缓存索引文件。  
2. InnoDB: InnoDB 存储引擎支持事务，主要面向 OLTP（联机事务处理过程）方面的应用，其特点是行锁设置、支持外键，  
并支持类似于 Oracle 的非锁定读，即默认情况下读不产生锁。 InnoDB 将数据放在一个逻辑表空间中（类似 Oracle）。  
InnoDB 通过多版本并发控制来获得高并发性，实现了 ANSI 标准的 4 种隔离级别，默认为 Repeatable，使用一种被  
称为 next-key locking 的策略避免幻读。  
对于表中数据的存储， InnoDB 采用类似 Oracle 索引组织表 Clustered 的方式进行存储。  
InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比 Myisam 的存储引擎，  
InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

事务的四大特征(acid):  
原子性： 每个事务是不可再分的整体，要么同时成功，要么同时失败；  
持久性： 事务执行成功后，数据永久存储，不可回滚；  
一致性： 事务执行前后，数据量保持一致；  
隔离性： 隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。

事务的隔离级别：  
1. Read uncommitted(读未提交)：未提交读隔离级别也叫读脏，就是事务可以读取其它事务未提交的数据。  
2. Read committed（读已提交）（MySQL默认）：在其它数据库系统比如 SQL Server 默认的隔离级别就是提交读，已提交读  
隔离级别就是在事务未提交之前所做的修改其它事务是不可见的。  
3. Repeatable read（可重复读） ：保证同一个事务中的多次相同的查询的结果是一致的，比如一个事务一开始  
查询了一条记录然后过了几秒钟又执行了相同的查询，保证两次查询的结果是相同的，可重复读也是 mysql 的默认隔  
离级别  
4. Serializable（串行读）：可串行化就是保证读取的范围内没有新的数据插入，比如事务第一次查询得到某个  
范围的数据，第二次查询也同样得到了相同范围的数据，中间没有新的数据插入到该范围中。  
存储过程的创建(MySQL5.0)：  
计算两个数之和（备注：实现计算两个整数之和的功能）：  
drop procedure if exists pr\_add; （备注：如果存在 pr\_add 的存储过程，则先删掉）  
create procedure pr\_add ( a int, b int )  
begin declare c int;  
if a is null then set a = 0;  
end if;  
if b is null then set b = 0;  
end if;  
set c = a + b;  
select c as sum;

触发器： 百度一下，你就知道；

SQL语句优化：  
1. limit基数比较大时使用between；  
2. 尽量避免在列上做运算，这样导致索引失效

**Oracle：**  
存储过程: 存储过程（Stored Procedure ）是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中。用户  
通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对  
象，任何一个设计良好的数据库应用程序都应该用到存储过程。  
创建方法:  
create or replace procedure 存储过程名（param1 in type， param2 out type）  
as  
变量 1 类型（值范围） ;  
变量 2 类型（值范围） ;  
Begin  
Select count(\*) into 变量 1 from 表 A where 列名=param1；  
If (判断条件) then  
Select 列名 into 变量 2 from 表 A where 列名=param1；  
Dbms\_output。 Put\_line(‘打印信息’ );  
Elsif (判断条件) then  
Dbms\_output。 Put\_line(‘打印信息’ );  
Else  
Raise 异常名（NO\_DATA\_FOUND） ;  
End if;  
Exception  
When others then  
Rollback;  
End;  
分页查询:  
select \* from  
( select rownum r,a from tabName where rownum <= 20 )  
where r > 10

存储过程和存储函数的区别和特点:  
特点:  
（1） 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。  
（2） 对于存储过程来说可以返回参数，而函数只能返回值或者表对象。  
（3） 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以  
返回一个表对象，因此它可以在查询语句中位于 FROM 关键字的后面。  
区别：  
（1）函数必须有返回值,而过程没有.  
（2）函数可以单独执行.而过程必须通过 execute 执行.  
（3）函数可以嵌入到 SQL 语句中执行.而过程不行.  
其实我们可以将比较复杂的查询写成函数.然后到存储过程中去调用这些函数;

触发器的作用:  
1）触发器可通过数据库中的相关表实现级联更改；通过级联引用完整性约束可以更有效地执行这些更改。  
2）触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它  
表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或  
显示用户定义错误信息。  
3）触发器还可以强制执行业务规则  
4）触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。