1. 键
1.1 主键
主键 (PRIMARY KEY) 的完整称呼是主键约束. MySQL 主键约束是一个列或者列的组合, 其值能唯一地标识表中的每一行. 这样的一列或多列称为表的主键, 通过它可以强制表的实体完整性.
PRIMARY KEY 约束唯一标识数据库表中的每条记录.
主键列不能包含 NULL 值.
主键必须包含唯一的值.
每个表都应该有一个主键, 并且每个表只能有一个主键.
使用范例
ALTER TABLE table_name ADD PRIMARY KEY (Id); -- 添加主键
ALTER TABLE table_name DROP PRIMARY KEY; -- 删除主键
- CREATE TABLE table_name (
- Id int NOT NULL AUTO_INCREMENT,
- ...,
PRIMARY KEY (Id) -- 创建表时设置主键
)
主键冲突
在数据插入的时候, 假设主键对应的值已经存在, 那么插入就会造成失败. 当主键存在冲突的时候(Duplicate key), 可以选择性的进行处理, 更新和替换.
insert into 表名[字段列表: 包含主键] value(值列表) on duplicate key update 字段 = 新值
- insert into table_name values(...) on duplicate key update;
- replace into table_name values(...);
1.2 外键
外键约束 (FOREIGN KEY) 用来在两个表的数据之间建立链接, 它可以是一列或者多列. 一个表可以有一个或多个外键.
外键对应的是参照完整性, 一个表的外键可以为空值, 若不为空值, 则每一个外键的值必须等于另一个表中主键的某个值. 外键是表的一个字段, 不是本表的主键, 但对应另一个表的主键. 定义外键后, 不允许删除另一个表中具有关联关系的行. 外键的主要作用是保持数据的一致性, 完整性.
使用范例
ALTER TABLE curr_table_name ADD CONSTRAINT fk_name FOREIGN KEY column_name REFERENCES fk_table_name (column_name); -- 添加外键
ALTER TABLE curr_table_name DROP FOREIGN KEY fk_name; -- 删除外键
- CREATE TABLE curr_table_name (
- curr_Id int NOT NULL,
- ...,
- fk_Id int,
- PRIMARY KEY (curr_Id),
FOREIGN KEY (fk_Id) REFERENCES fk_table_name(fk_col) -- 创建表的时候添加外键
)
1.3 索引
在 MySQL 中, 索引 (index) 也叫做键(key), 它是存储引擎用于快速找到记录的一种数据结构. 系统根据某种算法, 将索引数据单独建立一个文件, 文件能够实现快速匹配数据, 并且能够快速的找到对应表中的记录.
1.3.1 索引的特点
提高查询效率, 同时也会降低更新表的速度, 如 insert/update/delete 时, 不仅要保存数据, 还要保存一下索引文件.
约束数据的有效性(如唯一性等).
索引本身会产生索引文件, 会占用磁盘空间.
1.3.2 索引的分类
1. 主键索引: Primary Key
2. 外键索引: Foreign Key
2. 唯一索引: Unique Key|Index
3. 全文索引: fulltext
4. 普通索引: index
1.3.3 索引的类型: BTREE 和 HASH
BTREE 索引, 对索引列是顺序存储的, 并且每一个叶子页到根的距离相同. 很适合查找范围数据.
HASH 索引, 基于哈希表实现的, 只有精确匹配索引所有列的查询才有效.
HASH 索引有一些重要的特征需要在使用的时候特别注意
1. 只用于使用 = 或 <=> (NULL 安全的等于)操作符的等式比较.
2. 优化器不能使用 HASH 索引来加速 ORDER BY 操作.
3. 只能使用整个关键字来搜索一行.
而对于 BTREE 索引, 当使用范围查询如>,<,>=,<=,BETWEEN,!= 或者 <>, 或者 LIKE 'pattern'(其中'pattern'不以通配符开始) 操作符时, 都可以使用相关列上的索引.
1.3.4 索引的设计原则
不是越多越好
常更新的表越少越好
数据量小的表最好不要建立索引
不同的值比较多的列才需要建立索引
某种数据本身具备唯一性的时候, 建立唯一性索引, 可以保证定义的列的数据完整性, 以提高查询熟度
频繁进行排序或分组的列 (group by 或者是 order by) 可以建立索引, 提高搜索速度
经常用于查询条件的字段应该建立索引
创建索引
索引在创建表的时候可以同时创建, 也可以随时增加新的索引. 创建新索引的语法为:
- CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
- ON table_name (column_name, ...)
- column_name: column_name [(length)] [ASC | DESC]
-- length 表示字节长度
使用如下
CREATE UNIQUE INDEX index_name ON table_name column_name(length); -- 创建唯一索引
CREATE INDEX index_name ON table_name column_name(length);
ALTER table table_name ADD INDEX index_name(column_name); -- 添加索引(修改表的结构)
- ALTER TABLE table_name ADD UNIQUE index_name(column_name);
- ALTER TABLE table_name ADD PRIMARY KEY column_name(length);
- ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);
- ALTER TABLE table_name ADD FULLTEXT column_name(length);
DROP INDEX [index_name] ON table_name; -- 删除索引
2. 存储过程
2.1 函数
将一段代码封装到一个结构中, 在需要执行的时候调用结构执行即可.
2.1.1 变量
系统变量: 系统自定义的变量, 如 autocommit 等. 但是一般情况下不会使用系统变量.
show variables; -- 查看所有系统变量
set @@autocommit = 0; -- 修改系统变量, 禁止自动提交, 修改之后客户端需重启才能生效
set global autocommit=0; -- 全局修改, 对所有的客户端都生效
自定义变量: 用户自己定义变量
-- 通过 DECLARE 可以定义一个局部变量
DECLARE varname varchar(32) default '';
-- 定义变量并赋值, 注意这里使用一个 @ 符号, 而不是两个; 如果不用 @ 则是局部变量
set @varname = "trent";
select @varname := varname, varname from table_name; -- 在 select 中 := 表示赋值, 而 = 表示比较.
2.1.2 循环语句
- while expr do
- statement...
-- iterate -- iterate 相当于 continue
-- leave -- leave 相当于 break
end while;
-- 类似的循环还有 loop, 用法类似
2.1.3 使用函数
系统自定义的函数可以直接拿来使用.
create function func_name() return int -- 定义函数
return 100; -- 当函数自由一条语句的时候, 可以不使用 begin/end 语句
select func_name(); -- 调用函数
select function status\G -- 查看所有函数
show create function func_name; -- 查看函数的创建语句
drop function func_name; -- 删除函数
使用参数
定义时的参数叫形参, 调用时的参数叫实参. 形参必须指定数据类型.
- delimiter $$
- create function func_name(var1 int) return int
- begin
- set @varname = 1;
- set @i = 0;
- while @i < var1 do
- if mod(@i, 2) = 2 then
- set @varname = @varname + @i
- else iterate
- end if
- set @i = @i + 1
- return @varname
- end $$
- delimiter ;
2.2 存储过程
存储过程 (Stored Procedure) 是一种在数据库中存储复杂程序, 以便外部程序调用的一种数据库对象.
存储过程是为了完成特定功能的 SQL 语句集, 经编译创建并保存在数据库中, 用户可通过指定存储过程的名字并给定参数 (需要时) 来调用执行. 为了代码封装与重用.
2.2.1 优点
存储过程可封装, 并隐藏复杂的商业逻辑.
存储过程可以回传值, 并可以接受参数.
存储过程无法使用 SELECT 指令来运行, 因为它是子程序, 与查看表, 数据表或用户定义函数不同.
存储过程可以用在数据检验, 强制实行商业逻辑等.
2.2.2 缺点
存储过程, 往往定制化于特定的数据库上, 因为支持的编程语言不同. 当切换到其他厂商的数据库系统时, 需要重写原有的存储过程.
存储过程的性能调校与撰写, 受限于各种数据库系统.
没有参数的存储过程
- delimiter $$
- create procedure proc_name()
- begin
- select * from table_name;
- -- ... do somethings
- end $$
- delimiter ;
CALL proc_name (); -- 调用存储过程
DROP PROCEDURE [IF EXISTS] proc_name; -- 删除存储过程
-- 修改存储过程, 需要先删除后修改
2.2.3 参数
函数的参数需要数据类型指定, 过程比函数更严格, 过程还有自己的类型限定:
IN: IN 类型参数一般只用于传入, 在调用存储过程中一般不作修改和返回.
OUT: OUT 是传出参数, 不能用于传入参数值, 在调用存储过程中, 可以改变其值, 并可返回.
INOUT: INOUT 参数集合了 IN 和 OUT 类型的参数功能, 可传入值, 也可修改其值, 同时也可返回值.
使用如下
-- IN 参数, OUT 参数
- delimiter $$
- create procedure test(IN id varchar(32),OUT name varchar(32))
- begin
- select users.name into name from users where users.id = id;
- select name;
- end $$
- dilimiter ;
-- INOUT 参数
- delimiter $$
- create procedure teste(INOUT id varchar(32), INOUT name varchar(32))
- begin
- set id= '666';
- set name = 'trent';
- select users.id,users.name into id, name from users where users.id = id;
- end $$
- delimiter ;
3. 事务
MySQL 事务主要用于处理操作量大, 复杂度高的数据. 比如说, 在人员管理系统中, 你删除一个人员, 你即需要删除人员的基本资料, 也要删除和该人员相关的信息, 如信箱, 文章等等, 这样, 这些数据库操作语句就构成一个事务!
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务.
事务用来管理 insert, update, delete 语句
3.1 语法
MySQL 通过 SET AUTOCOMMIT,START TRANSACTION,COMMIT 和 ROLLBACK 等语句支持本地事务, 具体语法如下.
- START TRANSACTION | BEGIN [WORK]
- COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] | ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
- SET AUTOCOMMIT = {
- 0 | 1
- }
默认情况下, MySQL 是自动提交 (Autocommit) 的, 如果需要通过明确的 Commit 和 Rollback 来提交和回滚事务, 那么需要通过明确的事务控制命令来开始事务, 这是和 Oracle 的事务管理明显不同的地方.
START TRANSACTION 或 BEGIN 语句可以开始一项新的事务.
COMMIT 用来提交事务.
ROLLBACK 用来回滚事务.
CHAIN 子句用来定义在事务提交之后的操作, CHAIN 会立即启动一个新事物, 并且和刚才的事务具有相同的隔离级别.
RELEASE 子句用来定义在事务回滚之后的操作, RELEASE 会断开和客户端的连接.
SET AUTOCOMMIT 可以修改当前连接的提交方式, 如果设置了 SET AUTOCOMMIT=0, 则设置之后的所有事务都需要通过明确的命令进行提交或者回滚, 即禁止自动提交.
3.2 保留点 SAVEPOINT
在事务中可以通过定义 SAVEPOINT, 指定回滚事务的一个部分, 但是不能指定提交事务的一个部分.
需要注意的是, 如果定义了相同名字的 SAVEPOINT, 则后面定义的 SAVEPOINT 会覆盖之前的定义.
对于不再需要使用的 SAVEPOINT, 可以通过 RELEASE SAVEPOINT 命令删除 SAVEPOINT, 删除后的 SAVEPOINT, 不能再执行 ROLLBACK TO SAVEPOINT 命令.
3.3 事务是必须满足的 4 个条件 (ACID):
A:Atomic, 原子性, 一个事务 (transaction) 中的所有操作, 要么全部完成, 要么全部不完成, 不会结束在中间某个环节. 事务在执行过程中发生错误, 会被回滚 (Rollback) 到事务开始前的状态, 就像这个事务从来没有执行过一样.
C:Consistent, 一致性, 事务完成后, 所有数据的状态都是一致的, 即 A 账户只要减去了 100,B 账户则必定加上了 100;
I:Isolation, 隔离性, 如果有多个事务并发执行, 每个事务作出的修改必须与其他事务隔离; 事务隔离分为不同级别, 包括读未提交 (Read uncommitted), 读提交(read committed), 可重复读(repeatable read) 和串行化(Serializable).
D:Duration, 持久性, 即事务完成后, 对数据库数据的修改被持久化存储.
3.4 隔离级别
1. Read Uncommitted 读未提交: 在该隔离级别, 所有事务都可以看到其他未提交事务的执行结果. 本隔离级别很少用于实际应用, 因为它的性能也不比其他级别好多少. 读取未提交的数据, 也被称之为脏读(Dirty Read).
2. Read Committed 读已提交: 这是大多数数据库系统的默认隔离级别(但不是 MySQL 默认的). 它满足了隔离的简单定义: 一个事务只能看见已经提交事务所做的改变. 这种隔离级别 也支持所谓的不可重复读(Nonrepeatable Read), 因为同一事务的其他实例在该实例处理其间可能会有新的 commit, 所以同一 select 可能返回不同结果.
3. Repeatable Read 可重读: 这是 MySQL 的默认事务隔离级别, 它确保同一事务的多个实例在并发读取数据时, 会看到同样的数据行. 不过理论上, 这会导致另一个棘手的问题: 幻读 (Phantom Read). 简单的说, 幻读指当用户读取某一范围的数据行时, 另一个事务又在该范围内插入了新行, 当用户再读取该范围的数据行时, 会发现有新的 "幻影" 行. InnoDB 和 Falcon 存储引擎通过多版本并发控制 (MVCC,Multiversion Concurrency Control) 机制解决了该问题.
4. Serializable 可串行化: 这是最高的隔离级别, 它通过强制事务排序, 使之不可能相互冲突, 从而解决幻读问题. 简言之, 它是在每个读的数据行上加上共享锁. 在这个级别, 可能导致大量的超时现象和锁竞争.
如果没有指定隔离级别, 数据库就会使用默认的隔离级别. 在 MySQL 中, 如果使用 InnoDB, 默认的隔离级别是 Repeatable Read.
这四种隔离级别采取不同的锁类型来实现, 若读取的是同一个数据的话, 就容易发生问题. 例如:
脏读(Drity Read): 某个事务已更新一份数据, 另一个事务在此时读取了同一份数据, 由于某些原因, 前一个 RollBack 了操作, 则后一个事务所读取的数据就会是不正确的.
不可重复读(Non-repeatable read): 在一个事务的两次查询之中数据不一致, 这可能是两次查询过程中间插入了一个事务更新的原有的数据.
幻读 (Phantom Read): 在一个事务的两次查询中数据笔数不一致, 例如有一个事务查询了几列(Row) 数据, 而另一个事务却在此时插入了新的几列数据, 先前的事务在接下来的查询中, 就会发现有几列数据是它先前所没有的.
在 MySQL 中, 实现了这四种隔离级别, 分别有可能产生问题如下所示:
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read Uncommitted 读未提交 | ? | ? | ? |
Read Committed 读已提交 | ? | ? | ? |
Repeatable Read 可重读 | ? | ? | ? |
Serializable 可串行化 | ? | ? | ? |
使用如下
begin; -- 开始事务
set autocommit = 0; -- 禁止自动提交
insert into tablename values(10, "trent1"); -- 预操作数据
savepoint savepointname; -- 定义一个标记 / 保留点, 如果回滚就先回滚到这
insert into tablename values(11, "trent2"); -- 预操作数据 insert/update/delete
rollback to savepoint savepointname; -- 回滚到定义 savepointname 处
release savepoint savepointname; -- 删除标记
rollback; -- 回滚数据, 即取消
rollback and release; -- 回滚数据, 并断开连接
commit; -- 提交事务
commit and chain; -- 提交后又自动开始一个新的事务, 之前操作的数据依然无效, 等待 commit. 一般少使用
来源: http://www.bubuko.com/infodetail-3101134.html