数据库复习笔记总结
本文地址:http://www.cnblogs.com/maplefighting/p/7825313.html
mysql：
1、登录：mysql -u root -p
2、创建数据库：mysqladmin -u root -p create xx;
3、删除数据库： drop xx;
4、选择数据库：use xx
5、创建数据库：create table if not exist '.....' (
'id' INT UNSIGNED AUTO_INCREMENT,
.......
)
PRIMARY KEY('.....','........') 主键
6、删除数据库：DROP TABLE xx;
7、插入数据：INSERT INTO TABLE ( , ) VALUE ( , )
8、查询：SELECT col_name,col_id
FORM table_name
[ where .......]
[ OFFSET M ] [ LIMIT N ]
偏移量 返回记录数
where BINARY ..... 区分大小写
9、UPDATE：UPDATE table_name SET .....=......
[ where .....]
10、DELETE：DELETE FROM table_name [ WHERE .....]
11、LIKE：SELECT ......FROM ...... WHERE ....LIKE '%COM';
12、排序：ORDER BY ....ASC/DESC
13、GROUP BY：SELECT coalesce(name,'总数'),sum( ) as ....
FROM .....
GROUP BY .......
coalesce(a,b,c) a==null，选b --->b==null时，选c
14、连接：SELECT a.xx,b.xx FROM tb1 a INNER JOIN tb2 b ON a.k = b.k 内连接，取交集
LEFT JOIN 左连接，读取左边全部数据
RIGHT JOIN 右连接
15、事务：BEGIN;开始
COMMIT;提交事务
ROLLBACK;回滚
16、ALTER：ALTER TABLE tb1 DROP i;
ADD i INT;
MODIFY c char(10);
CHANGE i j BIGINT;
ALTER TABLE tb1 ALTER i SET DEFAULT 100; 修改默认值
ALTER TABLE tb1 RENAME TO tb2; 修改表名
17、临时表 关闭客户端程序时销毁 temporary
18、索引：CREATE INDEX index ON mytable(username(length))
19、外键：FOREIGN KEY (parent_id) REFERENCES parents(id)
20、WHERE子句中不能以聚集函数(SUM,AVG....) 为条件
21、完整性约束：CONSTRAINT c CHECK(Sno Between......) NOT NULL
22、IF(ex1, ex2, ex3 ) ex1为TRUE时，返回ex2，否则返回ex3
IF condition THEN .......
ELSEIF con THEN ....
ELSE .......
END IF
23、CREATE FUNCTION "sp" (n INT,......)
RETURNS text
BEGIN
DECLARE i INT DEFAULT 0;
SET i = 1;
while i < 11 do
insert ........
set i = i + 1;
end while;
RETURN s;
END $$
repeat
insert......;
set i = i + 1;
util i >= 5
end repeat
-----------
24、数据库完整性:实体完整性，参照完整性，用户定义的完整性
25、完整性约束：非空约束，唯一约束，主键约束，外键约束
26、规范化理论：(1) 清除不合适的数据依赖
(2) 减少重复 (3) 消除冗余 (4) 解决操作异常
27、范式：(1) 2NF 消除部分函数依赖
(Sno, Cno ) ----> Sdept, Sno ---> Sdept 存在部分函数依赖
(2) 3NF 不存在部分函数依赖，没有传递依赖
(3) BCNF：只有一个候选码的3NF
有多个候选码的3NF，讨论剩下属性有没依赖关系
(S, J ) --> T , (S, T ) --> J, T--> J 不是BCNF
分解为 ST(S, T) TJ(T, J )
(4) 4NF 消除非平凡且非函数依赖的多值依赖
28、数据库设计：1、需求分析阶段 2、概念结构设计阶段 3、逻辑结构设计阶段 4、物理结构设计阶段 5、数据库实施阶段 6、数据库运行和维护阶段
29、事务是用户定义的一个数据库操作序列，要么全做，要么全不做
4个特性：原子性，一致性，隔离性，持久性 (ACID)
30、并发控制：封锁
排他锁 -->写锁 共享锁-->读锁
31、乐观锁：每次拿到数据都认为别人不会修改，不上锁，更新时会判断期间别人有没去更新数据 使用版本号等机制 cas,mvcc 指定版本号
悲观锁：每次都上锁 表锁，读锁，写锁
select ......... for update
32、查找表中多余记录记录
SELECT * FROM people where peopleid in (
SELECT peopleid FROM people GROUP BY peopleid
HAVING count(peopleid) > 1
)
33、事务的隔离性：(1) 未提交读：事务中的修改，即使没提交，其他事务也可见，会引起脏读
(2) 提交读：一个事务从开始到提交之前，所做的修改对其他事务不可见，会引起不可重复读
(3) 可重复读 (mysql默认) ：解决脏读的问题，也保证可多次读取相同的数据。但会出现幻读
(4) 可串行化：每一行数据都加锁
幻读：当某个事务读取范围内记录时。另一个事务插入，然后再读取 (2)(3)才会发生
提交读的读锁是读完释放，可重复读是事务完释放 (--这个不知道正确与否)
34、并发操作带来的数据不一致包括：(1) 幻读 (新增，删除) (2) 不可重复读 (修改) (3)读脏数据
35、MVCC 多版本并发控制
SELECT：a、InnoDB 只检查版本早于当前版本的数据行
b、行的删除版本要么未定义，要么大于当前事务版本号
INSERT：保留当前系统版本号为行版本号
DELETE：保存行删除标识
36、B-树 (读做b树)：为了减少磁盘I/O，把高瘦的二叉查找树变成矮胖的 每个节点都带有具体数据(关键字)
m阶段B-树特征：
(1) 根节点至少有两个子女
(2) 每个中间节点包含 k - 1 个元素和 k 个孩子 m/2 <= k <= m
(3) 每个叶子节点都包含 k - 1 个元素
(4) 所有叶子节点都位于同一层
InnoDB 使用 B+树
B+树
(1) 有k个子树的中间节点包含有 k 个元素，每个元素只使用索引，所有数据保存在叶子节点
(2) 所有叶子包含全部元素信息 及指针，本身以关键字从小到大排序
(3) 所有中间节点元素同时存在于子节点，在子节点中是最大(或最小的) 元素
区别：单个查询 B+ 树比B-树优点：
a、中间节点没有具体数据，可以容纳更多节点元素，即在数据量相同时，可以更加矮胖，使 I/O次数更少
b、稳定，每次都要查询到叶子节点
范围查询：B-树需要查询每个节点，即一个一个查询。B+树可以通过叶子链表查询 (具体两种树的形状可google或百度搜索)
37、聚簇索引就是数据的物理存储顺序，一个表只能有一个 ------叶子节点 数据
非聚簇索引的索引顺序与数据物理排列顺序无关 ----索引
38、数据库引擎 ：MyISAM，InnoDB (详细结构可上网查询)
MyISAM是指向物理块的指针
InnoDB是聚簇索引，二级索引包含主键
InnoDB主索引树存所有数据，辅助索引存主键
MyISAM存数据地址
39、MyISAM和InnoDB的区别
InnoDB支持事务，MyISAM不支持
MyISAM只支持表锁，InnoDB 行级锁
MyISAM 适合执行大量SELECT，InnoDB 支持大量SELECT或UPDATE
MyISAM以文件形式存储，备份和恢复可对某个表操作，可以备份覆盖恢复
40、MySQL性能优化
(1) 查询缓存 (2) 创建索引 (3) 避免SELECT * (4) 拆分大的DELETE或INSERT语句 (5) 要一行数据时加上limit 1
MySQL客户端和服务器之间的通信是半双工的
41、执行顺序：from --> where --> group by --> having --> select --> order by
42、statement 和 preparedStatement 的区别
(1) 代码的可读性和可维护性
(2) preparedStatement 是预编译的，多次执行相同 sql 语句，会直接使用数据库缓冲区
(3) 安全性， preparedStatement 使用存储过程执行所有操作，可防止 sql 注入
43、事务的实现：镜像拷贝和事务日志
InnoDB通过 undo log 和 redo log 实现
事务中，每执行SQL语句对数据产生影响，就会记录相反操作到 undo log 中。每执行一条 SQL，会将操作记录到 redo log 中，一旦提交，持久化到磁盘
44、Mysql存储过程
存储过程是预编译的，较快执行速度
能减少网络流量
数据库专业人员可以随时修改存储过程，不用修改源程序
create procedure proc1 (OUT s int)
BEGIN
.........
END
存储过程和函数区别
存储过程可以返回多个，函数只能返回一个值
存储过程只能call调用，函数可以sql调用
45、mysql表水平拆分和垂直拆分
水平拆分为行的拆分，垂直拆分为列的拆分
分区只是为了存放数据的区块，还是一张表
分表：单表的并发能力提高了，磁盘 I/O 也提高
分区：突破磁盘的读写能力
46、hash 索引
(1) 仅仅满足 “=” ，“IN” 和 “<=>” 查询。不能使用范围查询
(2) 没办法利用索引完成排序
(3) 不能利用部分索引查询
(4) 在大量重复键值情况下，哈希索引效率低。因为碰撞多。如果是等值查询，哈希索引相对 B-Tree 等有绝对优势
参考书籍：数据库系统概论(第5版) 王珊 萨师煊 编著
--------------------------------------------------------------------------------------------------------------
以上为maplefighting个人笔记整理，如有出错，欢迎指正
