数据库基笔记.docx

数据库基笔记.docx

《数据库基笔记.docx》由会员分享，可在线阅读，更多相关《数据库基笔记.docx（14页珍藏版）》请在冰点文库上搜索。

数据库基笔记

一．数据库初始

1.需求分析

（1）.优良的数据库设计

减少数据冗杂、避免数据维护异常、节约储存空间、高效访问

（2）.设计流程

需求分析（数据--属性--两者各自特点）

逻辑设计（ER图逻辑建模）

物理设计（依据各数据库特点）

维护优化（新建、索引优化、大表拆分）

（3）.为何需求分析与内容

都是什么数据、特点、数据周期

实体及其间的关系（1v1、1Vn、nVn）、实体包含的属性、怎样标识唯一个体

注：

永久与不永久储存、分表与分库储存、归档与清理

2.逻辑设计

（1）.ER图

矩形：

实体集

菱形：

关系集

椭圆：

属性

线段：

图像间连接

（2）.设计范式（第一、第二、第三、BC）

第一范式（1NF）：

表中所有字段都是单一属性，不可再分，也就是说都是二维表，如用户信息不可再分为姓名与电话

第二范式（2NF）：

数据库表中不存在非关键字短发对任一候选关键字段的部分函数依赖。

（部分函数依赖存在于组合关键字中：

多个属性组合起来确定唯一物品）如

商品名称

供应商

价格

重量

描述

供应商电话

商品名称与供应商为组合关键字，但供应商决定了其电话，商品决定了价格等，故而但关键字的一定符合2NF，有组合关键字则不一定

第三范式（3NF）：

在2NF基础上，数据表中不存在非关键字段对任意候选关键字段的传递函数依赖。

如

商品名称

有效期

价格

重量

分类

分类描述

商品名称——分类——分类描述，即分类描述对商品名称有传递函数依赖

BC范式：

在3NF基础上，数据库表中不存在任何字段对任一候选关键字段的传递函数依赖，即复合关键字间也不可有函数依赖。

（3）.操作异常：

由于实体间有依存关系

（插入异常--更新异常、删除异常）

（4）.数据冗杂：

相同数据多次出现或可由其它列计算得到

3．物理设计

（1）.做什么

选择合适数据库管理系统----定义数据库与表及字段的命名规范----根据所选的DBMS系统选择合适的字段类型----反范式化设计

（2）.DBNS系统

Oracle

商业数据库

高性能、大规模

Java、php

企业级项目（通讯、金融、小企业）

SQLServer

性能一般、小事务

.net

MySQL

开源数据库

Java、php

互联网项目

PgSQL

Java、php

（3）.命名规范

可读性原则（大小写，如CustAddress）

表意性原则（描述数据内容、储存过称的功能）

长名原则（不用意义不明的缩写）

（4）.字段类型

优先选择数字类型，其次是日期或二进制类型，最后是字符

Char与varchar区别：

数据长度差不多、最大小于50b用char

（浮点）Decimal与float区别：

decimal用于精确数据（保留X位小数），float用于非精确

Int与datatime区别：

前者空间少，但后者常用于频繁调用时间时

（5）.主键选择

业务主键用于标识业务数据，进行表间关联；数据库主键用于优化储存

若数据库按主键顺序逻辑储存，则主键增长

主键字段要小

6.避免外键、触发器与预留字段

（7）.反范式化

（适当对3NF违反，进而提高性能与读取效率）

减少表的关联数量

增加数据读取效率

反范式化要适度

4．维护与优化

（1）.内容

维护数据字典、维护索引、维护表结构、适当对表水平或垂直拆分

（2）.维护数据字典

使用第三方工具

数据库本身的备注字段（COMMENT）——导出数据字典

（3）.维护索引

选择

出现在WHERE从句、GROUPBY从句、ORDERBY从句中列

选择性高的列放于最前面

数据类型要短

注意事项

索引要少、定期维护索引碎片、不使用强制索引关键字

（4）．维护表结构注意

在线变更表结构、同时对数据字典维护、控制表宽度与大小

（5）.表垂直（宽度）与水平拆分（数据量）

对多列表拆为几个少列表、对多个行拆为多个（Hashkey）

（6）．数据库中合适的操作

批量操作优于逐条操作

禁止使用Select*查询

控制自定义函数的应用（使用函数时列索引无效）

不使用数据库全文索引

二．MySQL数据库

1.安装、配置与简单操作

（1）.目录结构

bin：

储存可执行文件

data：

储存数据文件

docs：

文档

include：

储存包含的头文件

lib：

储存库文件

share：

错误消息与字符集文件

（2）.开启与关闭服务

窗口化操作或dos操作，如

netstartmysql和netstopmysql

（3）.DOS参数

如图1—1，—u名—p密码—P3306—hXXX1-1

（4）修改提示符

prompt名

名可为自定义字符，也可为系统内置如\D（完整日期）、\d（当前数据库）、\h（服务器名）、\u（用户名）

（5）常用命令

SELECTVERSION（）；服务器版本

SELECTNOW（）;当前日期

SELECTUSER（）;当前用户

SELECTDATABASE（）;当前打开的数据库

（6）语句规范

关键字与函数名称全大写

数据库名称、表名称、字段名称都小写

Sql语句以分号结尾

二、SQL基础

1．DDL（数据定义语言）语句

（1）创建数据库

CREATEDATABASEt1;

SHOWDATABASES;（显示数据库）

USEt1;（选择数据库）

SHOWTABLES;（显示表格）

（2）删除数据库

DROPDATABASEt1;

（3）创建表

CREATETABLEt11（

enamevarchar（10）,

hiredatadata,

salarydecimal（10,2）

）;

DESCt2;（显示表结构）

（4）删除表

DROPTABLEt2;

（5）修改表

修改表类型

ALTERTABLEt2MODIFYenameVARCHAR（10）;

增加表字段

ALTERTABLEt2ADDCOLUMNageINT（3）;

删除表字段

ALTERTABLEt2DROPCOLUMNage;

表字段改名

ALTERTABLEt2CHANGEageworkageINT（4）;

修改字段排序

ALTERTABLEt2ADDbirthDATEAFTERename;（添于ename之后）

ALTERTABLEt2MODIFYageINT（3）FIRST;（添于最前端）

更改表名

ALTERTABLEt2RENAMEt22;

2．DML（数据操纵语言）语句

（1）插入记录

INSERTINTOt2（ename,hiredate）VALUES（‘lisa’,’2003-02-01’）;

=INSERTINTOt2VALUES（‘lisa’,’2003-02-01’）;（不指名时按默认顺序填充）

INSERTINTOt2（ename,sal）VALUES（‘lisa’,1000）;（选定名时未选部分按默认值填充）

SELECT*FROMt2;（查看表内容）

（2）更新记录

UPDATEt2SETsal=4000WHEREename=’lisa’;（按enamel更新sal）

UPDATEt2a,t3bSETa.sal=a.sal*b.deptno,b.deptname=a.enameWHEREa.deptno=b.deptno;（以deptno为连接点同时更新t2与t3表）

（3）删除记录

DELETEFROMt2WHEREename=’lisa’;（删除lisa数据行）

DELETEa,bFROMt2a,t3bWHEREa.deptno=b.deptnoANDa.deptno=3;（删除多条记录）

（4）查询记录

SELECT*FROMt2;（查询表的所有条目）

SELECTenameFROMt2;（查询ename条目与表中）

查询不重复记录

SELECTDISTINCTdeptnoFROMt2;

条件查询

SELECT*FROMt2WHEREdeptno=1ANDsal<3000;（where后接条件，可以是or、and等）

排序与限制查询字段

SELECT*FROMt2ORDERBYsal;（DESC为降序，ASC为升序，默认为升序）

SELECT*FROMt2ORDERBYdeptno,salDESC;（当有重复记录时，用第二个关键词对之排序）

SELECT*FROMt2ORDERBYsalLIMIT3;（显示前三条记录）

SELECT*FROMt2ORDERBYsalLIMIT1,3;（从第二条开始的三条记录，默认起始为0）

聚合

SELECTCOUNT

（1）FROMt1;（显示总人数）

SELECTdeptno,COUNT

（1）FROMt1GROUPBYdeptno;（显示各部门人数）

SELECTdeptno,COUNT

（1）FROMt1GROUPBYdeptnoWITHROLLUP;（显示各部门人数和总人数）

SELECTdeptno,COUNT

（1）FROMt1GROUPBYdeptnoHAVINGCOUNT

（1）>1;（人数大于一的部门人数）

SELECTSUM（sal）,MAX（sal）,MIN（sal）FROMt1;（除计数函数外还有诸如求和、最值等函数）

表连接

a.内连接

SELECTename,deptnameFROMt1,t2WHEREt1.deptno=t2.deptno;

b.外连接

左连接：

SELECTename,deptnameFROMt1LEFTJOINt2ONt1.deptno=t2.deptno;

右连接：

SELECTename,deptnameFROMt2RIGHTJOINt1ONt2.deptno=t1.deptno;

子查询

SELECT*FROMt1WHEREdeptnoIN（SELECTdeptnoFROMt2）;

当记录唯一时可用“=”代替in：

SELECT*FROMt1WHEREdeptno（SELECTdeptnoFROMt2LIMIT1）;

其关键字还有诸如notin、!

=、exisits、notexisits等

记录联合

a.直接联合：

SELECTdeptnoFROMt1

UNIONALL

SELECTdeptnoFROMt2;

b.去重联合：

SELECTdeptnoFROMt1

UNION

SELECTdeptnoFROMt2;

三．数据类型

数据类型

储存范围（要按所存数据来选类型）

字节

TINYINT

-128到127（-27到27-1）或0到255（0到28-1）

1（3）

SMALLINT

-32768到32767（-215到215-1）或0到65535（0到216-1）

2（5）

MEDIUMINT

-223到223-1或0到224-1

3（8）

INT

-231到231-1或0到232-1

4（10）

BIGINT

-263到263-1或0到264-1

8（>10）

（1）整型：

数据类型

存储范围（M为总位数,D为小数点后位数）

FLOAT[（M,D）]

小（单精度浮点数精确到7位小数）

DOUBLE[（M,D）]

大

DECIMA（M,D）

默认10位整数，0位小数

（2）浮点与定点型

列类型

储存需求

YEAR

1（年）

TIME

3（时）

DATE

3（年月日）

DATEIME

8（年月日时）

TIMESTAMP

4（字符串形式）

（3）日期时间类型

（4）字符型

（5）附件条件与表格

空值与非空：

可空NULL、非空NOTNULL

CREATETABLEt12（

usernameCHAR（8）NOTNULL,

ageTINYINTUNSIGNED

）;

自动编号（主键有唯一性、单一性、非空性）：

AUTO_INCREMENT加主键如，

CREATETABLEt13（

idSMALLINTUNSIGNEDAUTO_INCREMENTKEY（主键）,

usernameCHAR（8）NOTNULL

）;

注：

UNIQUEKEY唯一约束，与主键PRIMARYKEY主键约束区别是每表可以存在多个且可有空值。

如，idSMALLINTUNSIGNEDAUTO_INCREMENTUNIQUEKEY

默认值：

DEFAULT

CREATETABLEt15（

sexENUM（选择）（‘1’,’2’,’3’）DEFAULT‘3’

）;

四.外键约束

（1）约束

其目的在于保证数据的完整性与一致性，分为表级与列级约束。

其包括：

非空约束---NOTNULL

主键约束---PRIMARYKEY

唯一约束---UNIQUEKEY

默认约束---DEFAULT

外键约束---FOREIGNKEY

（2）外键约束

要求：

父表与子表用相同InnoDB存引擎且禁止使用临时表、外键列与参照列数据类型相似（数字长度和符合同、字符长度可不同）、外键列与参照列必须创建索引。