数据库简单须知

SQL语句分类：

 

DQL（数据查询语句）

select（查询）

select * from 表名

 

DML（数据操作语言）

insert（添加）

insert into table_name ( 字段名1, 字段名2,...字段名N) values ( 字段值1, 字段值2,...字段值N);

delete（删除）

delete from 表名 where 条件;

uqdate（修改）

update 表名 set  字段名=“字段内容” where 条件

 

DDL（数据定义语言）

create（创建表）

create table 数据表名 (字段名 字段类型 约束条件);

drop（删除表）

drop table 表名

alter（添加列）

alter table 表名 add 字段名  字段类型          添加字段 drop column  字段名           删除字段 alter column 字段名  字段类型   修改字段

 

DCL（数据控制语言）

grant（把权限赋予用户）

revoke（把权限从用户收回）

 

TPL(TCL，事务控制语言)

commit（提交事务）

rollback（回滚事务）

 

数据库的三大范式

第一范式：原子件，要求每一列的值不能再拆分了

第二范式：一张表只描述一个实体（若列中有冗余数据，则不满足）

第三范式：所有列与主键直接相关

 

事务的特性（ACID）

原子性（Atomic）：事务中的各项操作，要么全做要么全不做，任何一项操作的失败都会导致整个事务的失败

一致性（Consistent）：事务结束后系统状态是一样的

隔离性（Isolated）：并发执行的事务彼此无法看到对方的中间状态

持久性（Durable）：事务完成后，即使发生灾难性的故障，通过日志和同步备份可以在故障发生后重建数据

 

聚合函数

函数名称	函数功能
SUM()	返回选取结果集中所有值的总和
MAX()	返回选取结果集中所有值的最大值
MIN()	返回选取结果集中所有值的最小值
AVG()	返回选取结果及中的所有值的平均值
COUNT()	返回选取结果集中行的数目

 

常用函数

函数名	说明
length	长度
lower	小写
upper	大写
to_date	转化日期
to_char	转化字符
to_number	转化数字
Ltrim	去左边空格
rtrim	去右边空格
substr	截取字符串
add_month	增加或减掉月份

表连接

表连接分类

1、内连接：

select * from 表1 [inner] join 表2 on 表1.字段1=表2.字段1;

说明：

内连接中的inner join 和 join 是等价的！但是建议为了程序的可读性,尽量不要省略inner!

2、外连接分类

1）、左外连接：left outer join

连接作用：左侧的表中的全部数据都会被显示出来，但是右侧表的数据，只有和左侧匹配上的字段才会被查询出来！否则都会显示null！

select * from 表1 left outer join 表2 on 表1.字段1=表2.字段1;

2）、右外连接： right outer join

连接作用：右侧的表中的全部数据都会被显示出来，但是左侧表的数据，只有和右侧匹配上的字段才会被查询出来！否则都会显示null！

select * from 表1 right outer join 表2 on 表1.字段1=表2.字段1;

 

3）、全外连接：full/all outer join

select * from 表1 full outer join 表2 on 表1.字段1=表2.字段1;

3、自连接(self join)

自连接(self join)是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

示例：

     在oracle的scott的schema中有一个表是emp。在emp中的每一个员工都有自己的mgr(经理)，并且每一个经理自身也是公司的员工，自身也有自己的经理。但现在我们只有一张emp表。所以我们可以采用自连接。自连接的本意就是将一张表看成多张表来做连接。

我们可以这样来写SQL语句:

select work.ename worker , mgr.ename manager from scott.emp work , scott.emp mgr where work.mgr = mgr.empno

4、交叉连接： 表与表之间做笛卡尔积查询！

SQL语法格式：(无条件查询)

select * from 表1 cross join 表2;

 

Oracle

Oracle分页

Oracle用rownum进行分页

分页语句的步骤：

1、最内层sql，查询要分页的所有数据

2、第二层sql，通过rownum伪列确定显示数据的上限，并且给查询的数据添加rownum伪列的值

3、最外层sql，设置显示数据的下限

select * from (select a.* , rownum r from (select * from表名 where条件 orderby列) a where rownum<=页数*条数) b where r>（页数-1）*条数

MySQL

MySQL分页

用limit进行分页

分页语句：

select * from foo limit 第几页,显示条数

 

数据库中存在的区别

drop,delete与truncate的区别

drop直接删掉表、truncate删除表中数据，在插入时自增长id又从1开始 delete删除表中数据，可以加where字句。

1、delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。truncate 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

2、表和索引所占空间。当表被truncate后，这个表和索引所占用的空间会恢复到初始大小，而delete操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

3、应用范围。truncate只能对TABLE；delete可以是table和view

4、delete语句为DML,这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。truncate、drop是DLL,操作立即生效，原数据不放到 rollback segment中，不能回滚

MySQL数据库与Oracle数据库的区别

1）应用方面：MySQL是中小型应用的数据库，一般用于个人项目或中小型网站及论坛。Oracle属于大型数据库，一般在具有相当规模的企业应用。

2）自动增长的数据类型方面：MySQL有自动增长的数据类型。Oracle没有自动增长的数据类型，需要建立一个自增序列

3）group by用法：MySQL中group by在select语句中可以随意使用，但是在Oracle中如果查询语句中有组函数，那其他列名必须是组函数处理过的或者是groupby子句中的列，否则报错

4）引号方面：MySQL中用双引号包起字符串，Oracle中只可以用单引号包起字符串

内连接,左连接,右连接的区别

 内连接：指主表，从表中符合连接条件的记录全部显示

 左连接：外连接方式，主要是显示主表，从表中符合连接条件的记录，并且主表中所有不符合连接条件的记录也要显示。

 右连接：外连接方式，主要是显示主表，从表中所有符合连接条件的记录，并且从表中不符合的记录也要显示。

数据库中<> 和 !=的区别

<>是标准的

!=是兼容的,一般没啥问题,但有时会故障

在数据库中建议用<>表示不等于

数据库中where与having区别

where在sql中是一个约束声明，使用where来约束数据库的数据

having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作。having是要配合group by 使用

数据库语句的执行顺序：

(1)from

对from子句进行执行，两个表的话执行成一个笛卡尔乘积，形成虚拟表v1，如果多表的话会先执行前两个表，执行顺序1、2、3，再与第三张表执行成笛卡尔乘积，执行1、2、3，一直重复，直到表都连接完。

(2) on

应用on筛选器，从v1中筛选出满足on逻辑表达式的行，形成虚拟表v2

(3) join

如果事outer join的话将添加外部行，left outer jion 就把左表在第二步中过滤的添加进来，如果是right outer join 那么就将右表在第二步中过滤掉的行添加进来，形成虚拟表v3

(4) where

应用where筛选器，从v3中筛选出满足where逻辑表达式的行，形成虚拟表v4，where筛选出的才是最终的结果，高于on筛选器

(5)group by(开始使用select中的别名，后面的语句中都可以使用)

group by 子句将中的唯一的值组合成为一组，得到虚拟表v5

(6) avg,sum....

如果应用了group by，那么后面的所有步骤都只能得到的vt5的列或者是聚合函数（count、sum、avg等）。原因在于最终的结果集中只为每个组包含一行。

(7)having

应用having筛选器，生成v7。having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器。

(8) select

处理select子句。将v7中的在select中出现的列筛选出来。生成v8.

(9) distinct

应用distinct子句，v8中移除相同的行，生成v9。

(10) order by

应用order by子句。按照order_by_condition排序v9，此时返回的一个游标，而不是虚拟表。

(11)应用top选项。此时才返回结果给请求者即用户。

 

数据库的简单优化

1、索引优化

创建索引，索引使用的原则，有什么优缺点

create index 索引名 on 表名（列名）

原则：

建议索引列建立not null约束

经常与其他表进行连接的表，在连接列上建立索引

优缺点：

创建索引能大大加快检索速度，加强表与表的连接，但是创建索引很占用空间

2、sql语句优化

1）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

3）很多时候用 exists 代替 in 是一个好的选择

4）用where子句替换having子句 因为having只会在检索出所有记录之后才对结果集进行过滤

3、数据库结构优化

1）范式优化： 比如消除冗余（节省空间）

2）反范式优化：比如适当加冗余等（减少join）

3）拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间