数据库学习过程随手记

MySQL and Oracle瞎记

关系型数据库：

Oracle sql-server，mysql ，db2 (围绕事务和交易展开)

Nosql：菲关系型数据库 （不考虑事务影响）

Mongodb,redis,hbase 主要考虑：高并发的写入数据，在数据量庞大的情况下，快速提取数据

在线数据： OLTP（在线交易 transaction)

历史数据：数据仓库 OLAP（在线分析 analyse） hadoop

Hibernate

Gave king

外网系统：

前台系统 ：部署在外网上

后台系统 ：部署在内网上

前后台系统共用一套数据库

外网用户与管理员用两个不同的表，用户和管理员从不同的网址登陆

企业应用系统

办公环境为企业内网

用户是业务系统的登陆人员的概念，每个业务子系统都有自己的用户

表之间的关系

一对一，一对多，多对多，多对一

drop table tuser; //DDL删除 表结构和数据全部删除

delete from tuser; //事务删除，可以回滚

truncate table tuser; //数据删除，结构保留

表之间的关系：

多对多： 通常用两个多对一表示多对多，需要一个中间表

Class的提取过程：

1. 找名词概念 &＃8212;&＃8212;&＃8211; CDM(概念模型)

2. 把名词抽象成Class或Class的属性

3. 类的目的是组成程序、描述业务

4. 类的设计原则：SRP OCP

Table的提取过程：

1. 找名词概念 &＃8212;&＃8212;&＃8211; PDM(物理模型)

2. 把名词抽象成Table或Table的属性

3. 表的目的是数据存储

4. 表的设计原则：三范式与反范式

三范式：

1. 每一列的概念独立、完整

2. 每个表有主键

3. 不要出现数据冗余 （数据量大幅增加，很难保证数据完整性）

反范式：

1. 通过数据冗余的方式，来提高查询性能

2. 横向切割表&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;如人员表，按省市存储不同的人员数据

3. 纵向切割表&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;如供应商，基础信息和业务信息分割成一对一的多张表

4. 冗余列

&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;学生表中，既有班级编号，又存有班级名称（冗余的）

&＃8212;&＃8212;父子表的层级一般不超过4层，超过4层的时候，需要考虑采用冗余列的方式来提高联合查询的性能

5. 附加列

&＃8212;&＃8212;-订单中的有很多明细项，都有自己的价格和数量。在订单表中增加总价的附加列，提取计算多个明细的总和

//用户是表的管理者

select * from scott.dept

//系统中的图片存储方式

1. blob字段

2. 文件存磁盘，数据库存图片位置

3. 图片服务器

4. CDN

varchar2(4000)

clob &＃8212;&＃8212;&＃8212;- 博客、新闻

oracle和mysql 都有 blob &＃8212;&＃8212;&＃8212;&＃8212;存二进制字节流

oracle 用 clob &＃8212;&＃8212;- 存文本

mysql 用 text

数据库的自增长字段：

oracle &＃8212;&＃8212;&＃8211; sequence

sql server &＃8212;&＃8212;&＃8212;- indentity

mysql &＃8212;&＃8212;&＃8212;&＃8212;&＃8212;- auto increment

//自增长

create sequence aseq; //在高并发环境下，不会出现数据冲突

insert into taa values(aseq.nextval,&＃8217;aa&＃8217;)

//最大值加1

insert into taa values( (select nvl(max(aid),0)+1 from taa),&＃8217;aa&＃8217;) //数据迁移性好

//oracle中的虚表

select * from dual;

SELECT master_seq.currval FROM dual；

数据库的翻页算法：

oracle : select * from (select rownum rn, tb.* from (select * from emp)tb) where rn>5 and rn<=11

mysql : select * from emp limit 2,5

Oracle

rownum 伪列 三层嵌套翻页它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。

dual 虚表

rowid select rowid,sno,name from tstaff

select rowid,sno,name from tstaff where rowid = &＃8216;#RlCAAK#ADv#&＃8217;

每行记录的物理标识，查找记录的速度最快

//必须是两列内容完全一致，才算重复行

select distinct ename,job from emp

注意：不推荐使用distinct进行数据过滤，主要是执行效率比较低

排序

select * from emp order by job desc ,empno asc

//条件查询

select * from emp where deptno != 10

//尽量不要使用!= ，用in语句替代它，效率更高

select * from emp where deptno in (20,30)

select * from emp where deptno = 10 OR deptno=20 //这个语句，也应该用in语句代替

SELECT ename, job,sal FROM emp WHERE sal>1000 AND sal<2000;

还可以使用 between and

SELECT ename, job,sal FROM emp WHERE sal between 1000 and 2000

//模糊查询

SELECT ename, job,sal FROM emp WHERE ename like &＃8216;%MA%&＃8217;

%：代表0个或多个任意字符。

_ ：代表一个任意字符

select * from emp e where job is not null

select * from emp e where job is null

//系统时间转换成指定格式的日期，输出

select sysdate from dual;

SELECT TO_CHAR(sysdate,&＃8217;YYYY-MM-DD HH24:MI:SS AM DY&＃8217;) FROM dual;

update emp set hiredate = to_date(&＃8216;2004-11-27&＃8217;, &＃8216;yyyy-mm-dd&＃8217;) where empno=7788

//多表查询，没有任何约束条件，会出现笛卡尔积的结果 （结果：表一记录数 * 表二记录数）

select * from emp ,dept

//避免笛卡尔积的办法就是，要关联的两个表必须有主外键关系

select * from emp e,dept d where e.deptno=d.deptno

select * from emp e inner join dept d on e.deptno=d.deptno

//左外链接

select * from dept d left join emp e on d.deptno=e.deptno

//返回符合查询条件的记录条数

select count(*) from emp

分组查询

select max(deptno),count(*) from emp //多个分组函数可以同时使用

select max(deptno),count(*),deptno from emp //分组函数与字段不能同时使用

//只有分组字段和分组函数，才能同时使用

select job,avg(sal) from emp group by job

select job,max(sal) from emp group by job

select deptno,max(sal) from emp group by deptno

select deptno,max(sal),job from emp group by deptno,job； //可以同时使用多个分组字段，先按部门分组，再按job分组

//使用having语句，对分组结果进行再次过滤

SELECT deptno, max(sal) FROM emp

GROUP BY deptno having max(sal) >3000

// 1.先按照where条件筛选数据结果集

2.然后使用GROUP BY 分组

3.对分组结果使用having进行过滤

SELECT deptno, max(sal) FROM emp where SAL>2500

GROUP BY deptno having max(sal) >3000

insert into tstaff values ( seq_id,nextval,?,?,?)

insert into tstaff values ( (select nvl(max(id),0) from tstafff),?,?,?)

select nvl(max(id),0) from tstafff

//子查询 &＃8212; 返回一列数据

select * from emp where sal > (select sal from emp where ename=&＃8217;SCOTT&＃8217;)

//子查询 &＃8212; 返回多列，可以作为新表使用

select rownum rn,tb.* from (select * from emp)tb

select * from (select rownum rn,tb.* from (select * from emp)tb) where rn>3

union 与 union all 的区别：

union是和集，去掉了两个集合的重复部分

union all 保留了重复的记录

uinion 和集

minus 差集

intersect 交集

insert into dept values(50，&＃8217;a3&＃8242;,&＃8217;beijing&＃8217;) &＃8212;&＃8212;&＃8212;&＃8212;&＃8212;只能插入所有字段，而且是按照顺序插入（与列的物理顺序一致）

insert into dept(dname,deptno) values(&＃8216;a3&＃8217;,52) &＃8212;&＃8212;&＃8212;- 可以少字段，可以调整顺序，更加灵活

//update语句

update emp set sal=sal+100 where job=&＃8217;CLERK&＃8217;