数据库基笔记

一．数据库初始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系统（3）.命名规范可读性原则（大小写，如CustAddress）表意性原则（描述数据内容、储存过称的功能）长名原则（不用意义不明的缩写）（4）.字段类型优先选择数字类型，其次是日期或二进制类型，最后是字符Char与varchar区别：数据长度差不多、最大小于50b用char（浮点）Decimal与float区别：decimal用于精确数据（保留X位小数），float用于非精确Int与datatime区别：前者空间少，但后者常用于频繁调用时间时（5）.主键选择业务主键用于标识业务数据，进行表间关联；数据库主键用于优化储存若数据库按主键顺序逻辑储存，则主键增长主键字段要小6.避免外键、触发器与预留字段（7）.反范式化（适当对3NF违反，进而提高性能与读取效率）减少表的关联数量增加数据读取效率反范式化要适度4．维护与优化（1）.内容维护数据字典、维护索引、维护表结构、适当对表水平或垂直拆分（2）.维护数据字典使用第三方工具数据库本身的备注字段（COMMENT）——导出数据字典（3）.维护索引①选择出现在WHERE从句、GROUP BY从句、ORDER BY从句中列选择性高的列放于最前面数据类型要短②注意事项索引要少、定期维护索引碎片、不使用强制索引关键字（4）．维护表结构注意在线变更表结构、同时对数据字典维护、控制表宽度与大小（5）.表垂直（宽度）与水平拆分（数据量）对多列表拆为几个少列表、对多个行拆为多个（Hash key）（6）．数据库中合适的操作批量操作优于逐条操作禁止使用Select*查询控制自定义函数的应用（使用函数时列索引无效）不使用数据库全文索引二．MySQL数据库1.安装、配置与简单操作（1）.目录结构bin：储存可执行文件data：储存数据文件docs：文档include：储存包含的头文件lib：储存库文件share：错误消息与字符集文件（2）.开启与关闭服务窗口化操作或dos操作，如net start mysql和net stop mysql（3）.DOS参数如图1—1，—u名—p密码—P3306 —hXXX 1-1（4）修改提示符prompt名名可为自定义字符，也可为系统内置如\D（完整日期）、\d（当前数据库）、\h（服务器名）、\u（用户名）（5）常用命令SELECT VERSION()；服务器版本SELECT NOW();当前日期SELECT USER(); 当前用户SELECT DATABASE(); 当前打开的数据库（6）语句规范关键字与函数名称全大写数据库名称、表名称、字段名称都小写Sql语句以分号结尾二、SQL基础1．DDL（数据定义语言）语句（1）创建数据库CREATE DATABASE t1;SHOW DATABASES;（显示数据库）USE t1;（选择数据库）SHOW TABLES;（显示表格）（2）删除数据库DROP DATABASE t1;（3）创建表CREATE TABLE t11(enamevarchar(10),hiredata data,salary decimal(10,2));DESC t2;（显示表结构）（4）删除表DROP TABLE t2;（5）修改表①修改表类型ALTER TABLE t2 MODIFY enameVARCHAR(10);②增加表字段ALTER TABLE t2 ADD COLUMN age INT(3);③删除表字段ALTER TABLE t2 DROP COLUMN age;④表字段改名ALTER TABLE t2 CHANGE age workageINT(4);⑤修改字段排序ALTER TABLE t2 ADD birth DATE AFTER ename;（添于ename之后）ALTER TABLE t2 MODIFY age INT(3) FIRST;（添于最前端）⑥更改表名ALTER TABLE t2 RENAME t22;2．DML（数据操纵语言）语句（1）插入记录INSERT INTO t2(ename,hiredate) VALUES(‘lisa’,’2003-02-01’);=INSERT INTO t2 VALUES(‘lisa’,’2003-02-01’);（不指名时按默认顺序填充）INSERT INTO t2(ename,sal) VALUES(‘lisa’,1000);（选定名时未选部分按默认值填充）SELECT * FROM t2;（查看表内容）（2）更新记录UPDATE t2 SET sal=4000 WHERE ename=’lisa’;（按enamel更新sal）UPDATE t2 a,t3 b SET a.sal=a.sal*b.deptno,b.deptname=a.ename WHERE a.deptno=b.deptno;（以deptno为连接点同时更新t2与t3表）（3）删除记录DELETE FROM t2 WHERE ename=’lisa’;（删除lisa数据行）DELETE a,b FROM t2 a,t3 b WHERE a.deptno=b.deptno AND a.deptno=3;（删除多条记录）（4）查询记录SELECT * FROM t2;（查询表的所有条目）SELECT ename FROM t2;（查询ename条目与表中）①查询不重复记录SELECT DISTINCT deptno FROM t2;②条件查询SELECT * FROM t2 WHERE deptno=1 AND sal<3000;（where后接条件，可以是or、and等）③排序与限制查询字段SELECT * FROM t2 ORDER BY sal;（DESC为降序，ASC为升序，默认为升序）SELECT * FROM t2 ORDER BY deptno, sal DESC;（当有重复记录时，用第二个关键词对之排序）SELECT * FROM t2 ORDER BY sal LIMIT3;（显示前三条记录）SELECT * FROM t2 ORDER BY sal LIMIT1,3;（从第二条开始的三条记录，默认起始为0）④聚合SELECT COUNT(1) FROM t1;（显示总人数）SELECT deptno,COUNT(1) FROM t1 GROUP BY deptno;（显示各部门人数）SELECT deptno,COUNT(1) FROM t1 GROUP BY deptno WITH ROLLUP;（显示各部门人数和总人数）SELECT deptno,COUNT(1) FROM t1 GROUP BY deptno HAVING COUNT(1)>1;（人数大于一的部门人数）SELECT SUM(sal),MAX(sal),MIN(sal) FROM t1;（除计数函数外还有诸如求和、最值等函数）⑤表连接a.内连接SELECT ename,deptname FROM t1,t2 WHERE t1.deptno=t2.deptno;b.外连接左连接：SELECT ename,deptname FROM t1 LEFT JOIN t2 ON t1.deptno=t2.deptno; 右连接：SELECT ename,deptname FROM t2 RIGHT JOIN t1 ON t2.deptno=t1.deptno;⑥子查询SELECT * FROM t1 WHERE deptnoIN(SELECT deptno FROM t2);当记录唯一时可用“=”代替in：SELECT * FROM t1 WHERE deptno(SELECT deptno FROM t2 LIMIT1);其关键字还有诸如not in、!=、exisits、not exisits等⑦记录联合a.直接联合：SELECT deptno FROMt1UNION ALLSELECT deptno FROM t2;b.去重联合：SELECT deptno FROM t1UNIONSELECT deptno FROM t2;三．数据类型型：（2）浮点与定点型（3）日期时间类型（4）字符型（5）附件条件与表格①空值与非空：可空NULL、非空NOT NULLCREATE TABLE t12(username CHAR(8) NOT NULL,age TINYINT UNSIGNED);②自动编号（主键有唯一性、单一性、非空性）：AUTO_INCREMENT加主键如，CREATE TABLE t13(id SMALLINT UNSIGNED AUTO_INCREMENT KEY（主键）,username CHAR(8) NOT NULL);注：UNIQUE KEY 唯一约束，与主键PRIMARY KEY 主键约束区别是每表可以存在多个且可有空值。