数据库篇(一)

一、DB
长期存储在计算机内、有组织、可共享的大量的数据的集合。数据库中的数据按照一定的数据模型组织、描述和存储，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。特点：永久存储、有组织、可共享。
二、DBMS
数据库管理系统，是位于用户与操作系统之间的一层数据管理软件，用于科学地组织、存储和管理数据、高效地获取和维护数据。
三、DBS
数据库系统，指在计算机系统中引入数据库后的系统，一般由数据库、数据库管理系统、应用系统、数据库管理员（DBA）构成。
四、DBA
数据库管理员。

五、SQL结构化查询语言

1.DDL (Data Definition Language)数据定义语言
用来定义数据库对象：数据库、表、列等。关键字：create，drop，alter等

1.1操作数据库

C(Create):创建

创建数据库：

create database 数据库名称

创建数据库，判断不存在，再创建：

create database if not exists 数据库名称；

创建数据库，并指定字符集

create database 数据库名称 character set 字符集名；

练习：创建db3数据库，判断是否存在，并指定字符集为gbk

create database if not exists db3 character set gbk;

R(Retrieve):查询

询所有数据库的名称：

show database；

查询某个数据库的字符集：查询某个数据库的创建语句

show create database 数据库名称；

U(Update):修改

修改数据库的字符集

alter database 数据库名称 character set 字符集名称：

D(Delete):删除

删除数据库

drop database 数据库名称；

判断数据库存在，存在再删除

drop database if exists 数据库名称；

select、use 使用数据库

查询当前正在使用的数据库名称

select database();

使用数据库

use 数据库名称；

1.2 操作表

C(Create):创建

语法;

create table 表名(
列名1 数据类型1，
列名2 数据类型2，
...
列名n 数据类型n
);

注意：最后一列，不需要加逗号（，）

数据类型：
1.int：整数类型
age int，
2.double：小数类型
score duoble （3，7）
3.data：日期，只包含年月日，yyyy-MM-dd
4.datatime：日期，包含年月日时分秒 ，yyyy-MM-dd HH：mm；ss
5.timestamp：时间类型 包含年月日时分秒 yyyy-MM-dd HH：mm；ss
如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
6.varchar：字符串
name varchar(20):姓名最大20的字符
cainiao 7个字符 张三 2个字符

create table student(
id int,
name varchar(37),
age int,
score double(4.1),
birthday data,
insert_time timestamp
);

复制表：

create table 表名 like 被复制的表名

R(Retrieve):查询

查询某个数据库中所有的表名称

show tables；

查询表结构

desc 表名；

U(Update):修改

修改表名

alter table 表名 rename to 新的表名；

修改表的字符集

alter table 表名 character set 字符集名称；

添加一列

alter table 表名 add 列名 数据类型；

修改列名称 类型

alter table 表名 change 列名 新列名 新列别 新数据类型；
alter table 表名 modify 列名 新数据类型；

删除列

alter table 表名 drop 列名；

D(Delete):删除

drop table 表名；
drop table if exists 表名；

2.DML(Data Manipulatio Language)数据操作语言

用来对数据库中表的数据进行增删改。关键字：insert，delete，update 等。

1.添加数据：
语法：

insert into 表名(列名1，列名2，...列名n) values(值1，值2，...值n)；

注意：
列名和值要一一对应。
如果表名后，不定义列名，则默认给所有列添加值。

insert into 表名 values(值1，值2，...值n)；

除了数字类型，其他类型需要使用引号（单双都可以）引起来

2.删除数据：
语法：

delete from 表名 [where 条件]

注意：
如果不加条件，则删除表中所有记录。
如果要删除所有记录

delete from 表名；-- 不推荐使用。有多少条记录就会执行多少次删除操作。
TRUNCATE TABLE 表名； -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表。

3.修改数据
*语法：

update 表名 set 列名1 = 值1，列名2 = 值2，...[where 条件];

注意：
如果不加任何条件，则会将表中所有记录全部修改。

3.DQL(Data Query Language)数据库查询语言
用来查询数据库中表的记录（数据）。关键字：select，where 等。

1.语法：

select
　　字段列表
from
　　表名列表
where
　　条件列表
group by
　　分组字段
having
　　分组之后的条件
order by
　　排序
limit
　　分页限定

2.基础查询：
多个字段的查询

select 字段名1，字段名2... from 表名；

注意：
如果查询所有字段，则可以使用*来替代字段列表。

3.去除重复：

distinct

4.计算列：
一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）

ifnull(表达式1，表达式2);

表达式1：那个字段需要判断是否为null
如果改字段为null后的替换值。

5.起别名：

as ：as也可以省略

6.条件查询：
where子句后跟条件
运算符

>,<,>=,<=,=,<>
BETWEEN...AND
IN(集合)
LIKE
IS NULL
and or &&
or or ||
not or !

7.排序查询
语法：order by 子句
order by 排序字段1 排序方式1，排序字段2 排序方式2…
排序方式：
ASC：升序，默认的
DESC：降序
注意：
如果有多个排序条件，则当前的条件值一样时，才会判断第二条件。

8.聚合函数：将一列数据作为一个整体，进行纵向的计算。
count：计算个数
一般选择非空的列：主键，count(*)
max：计算最大值
min：计算最小值
sum：计算和
avg：计算平均值
注意：聚合函数的计算，排除null值。
解决方案：
选择不包含非空的列进行计算
IFNULL函数

9.分组查询
语法：group by 分组字段
注意：
分组之后查询的字段：分组字段、聚合函数
where 和 having的区别？
答：where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
where 后不可以跟聚合函数，having可以进行聚合函数的判断。

10.分页查询
语法：limit开始的索引，每页查询的条数；
公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数
每页显示3条记录：

SELECT * FROM student LIMIT 0.3； --第1页
SELECT * FROM student LIMIT 3.3； --第2页
SELECT * FROM student LIMIT 6.3； --第3页

limit 是一个MySQL"方言"

4.DCL(Data Control Language)数据控制语言（了解）
用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANY，REVOKE 等。

六、关系代数
1.交
集合交 intersection ∩ SQL中没有对应的操作符
2.并
并 union ∪ 类似于 SQL 中的 union
3.差
集合差 set-difference - SQL中没有对应的操作符
4.笛卡尔积
笛卡儿积 Cartesian-product × 类似于 SQL 中不带 on 条件的 inner join
5.选择
选择 select σ 类似于 SQL 中的 where
6.投影
投影 project Π 类似于 SQL 中的 select
7.连接
自然连接 natural join ⋈ 类似于 SQL 中的 inner join
8.除
9.赋值
赋值 assignment ←
10.重命名
重命名 rename ρ 类似于 SQL 中的 as

七、实体型之间的联系
1.一对多
如：部门和员工
分析：一个部门有多个员工，一个员工只能对应一个部门
实现关系：在多的一方建立外键，指向另一方的主键。
2.一对一
如：人和身份证
分析：一个人只有一个身份证，一个身份证只能对应一个人
实现关系：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。
3.多对多
如：学生和课程
分析：一个学生可以选择多个课程，一个课程也可以被多个学生选择
实现关系：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。
4.E-R图
实体关系图

八、主键、外键、超键、候选键
超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键：是最小超键，即没有冗余元素的超键。

主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

外键：在一个表中存在的另一个表的主键称此表的外键。

九、完整性约束

1. 实体完整性：规定表的每一行在表中是惟一的实体。主键约束。
2. 域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。取值范围、精度约束。
3. 参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。外键约束。
4. 用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。非空约束、级联约束等。

十、关系数据库三范式
设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求。
设计关系数据库时，遵从不同的规范要求，设计合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF），第二范式（2NF），第三范式（3NF），巴斯-科德范式（BCNF），第四范式（4NF），

分类：
1.第一范式（1NF）：每一列都是不可分割的原子数据项；
2.第二范式（2NF）：在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)；
3.第三范式(3NF):在2NF基础上,任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)。

十一、级联
添加级联操作
语法：

ALTER TABLE 表名 ADD CONSTRAINT（外键名称）
FOREIGN KEY (外键字段名称) REFERENCES 主表名称（主表列名称）

分类：
1.级联更新：

ON UPDATE CASCADE

2.级联删除：

ON DELETE CASCADE

十二、多表查询

CREATE TABLE `course` (
  `cno` int(11) NOT NULL,
  `cname` char(30) CHARACTER SET utf8 NOT NULL,
  `ctime` int(11) NOT NULL,
  `scount` int(11) NOT NULL,
  `ctest` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `student` (
  `sno` char(4) CHARACTER SET utf8 DEFAULT NULL,
  `sname` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `dname` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `ssex` char(2) CHARACTER SET utf8 NOT NULL,
  `cno` int(11) NOT NULL,
  `mark` decimal(3,1) NOT NULL,
  `type` char(4) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `teacher` (
  `tno` int(11) NOT NULL,
  `tname` varchar(10) CHARACTER SET utf8 NOT NULL,
  `cno` int(11) NOT NULL,
  `sal` int(11) DEFAULT NULL,
  `dname` char(10) CHARACTER SET utf8 NOT NULL,
  `tsex` char(2) CHARACTER SET utf8 NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1.自连接
自连接是指表与其自身进行连接，这需要使用表别名。

SELECT s.sname,s.dname,s.cno,s.mark
from student s,student s2
where s.sno=s2.sno
and s2.mark<60

2.自然连接
它将表中具有相同名称的列自动进行记录匹配，自然连接不必指定任何同等连接条件。

SELECT sname,dname,cno,tname
from student NATURAL join teacher

等价于

SELECT sname,s.dname,s.cno,tname
from student s, teacher t
where s.dname=t.dname
and s.cno=t.cno

3.内连接
隐式内连接
不使用关键字inner join

SELECT tname,dname,cname,ctest from teacher,course WHERE teacher.cno=course.cno

显示内连接
使用inner join

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

4.外连接
左外连
左外连接，left outer join ,告诉DBMS生成的结果表中，除了包括匹配行外，还包括join关键字（from子句中）左边表的不匹配行。

SELECT s.sno,sname,s.cno,cname,ctest,mark
from student s LEFT JOIN course c
on s.cno=c.cno
ORDER BY sname

右外连
右外连接，right outer join ,告诉DBMS生成的结果表中，除了包括匹配行外，还包括join关键字（from子句中）右边表的不匹配行。

SELECT s.sno,sname,s.cno,cname,ctest,mark
from student s RIGHT JOIN course c
on s.cno=c.cno
ORDER BY sname

全外连
全外连接，full outer join，告诉DBMS生成的结果表中，除了包括匹配行外，还包括join关键字（from子句中）左边表和右边表的不匹配行。

SELECT s.sno,sname,s.cno,cname,ctest,mark
from student s full OUTER JOIN course c
on s.cno=c.cno
ORDER BY sname

5.交叉连接
求笛卡尔积

SELECT 字段名 FROM 左表, 右表

6.联合
UNION 和 UNION ALL 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。去重
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

UNION ALL
与UNION实现相同的功能，不去重。

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

十三、视图
视图（子查询）：是从一个或多个表导出的虚拟的表，其内容由查询定义。具有普通表的结构，但是不实现数据存储。
对视图的修改：单表视图一般用于查询和修改，会改变基本表的数据，
多表视图一般用于查询，不会改变基本表的数据。

--创建视图--  
create or replace view v_student as select * from student;  
--从视图中检索数据--  
select * from v_student;  
--删除视图--  
drop view v_student; 

作用：

①简化了操作，把经常使用的数据定义为视图。
我们在使用查询时，在很多时候我们要使用聚合函数，同时还要 显示其它字段的信息，可能还会需要关联到其它表，这时写的语句可能会很长，如果这个动作频繁发生的话，我们可以创建视图，这以后，我们只需要select * from view就可以啦，这样很方便。

②安全性，用户只能查询和修改能看到的数据。
因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以将基表中重要的字段信息，可以不通过视图给用户，视图是动态的数据的集合，数据是随着基表的更新而更新。同时，用户对视图不可以随意的更改和删除，可以保证数据的安全性。

③逻辑上的独立性，屏蔽了真实表的结构带来的影响。
视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。

缺点:

①性能差
数据库必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间。

②修改限制
当用户试图修改视图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的视图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的。

十四、索引
数据库索引是数据库管理系统中一个排序的数据结构。以协助快速查询、更新数据库表中数据。

为表设置索引要付出代价的，
一是：增加了数据库的存储空间。
二是：在插入和修改数据时要花费较多的时间（因为索引也要随之变动）。

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL 
 
);  

索引分类：
1.主键索引 PRIMARY KEY
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
PRIMARY KEY(ID)  
 
);

当然也可以用 ALTER 命令。记住：一个表只能有一个主键。
2.普通索引 INDEX
这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON mytable(username(length)); 

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

ALTER mytable ADD INDEX [indexName] ON (username(length)) ;

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);

3.唯一索引 UNIQUE
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ;

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
); 

4.组合索引 INDEX
添加字段：

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
city VARCHAR(50) NOT NULL,  
 
age INT NOT NULL 
 
);  

创建方式：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 

5.全文索引 FULLTEXT
对大数据文本进行索引，在建立的索引中对要查找的单词进行搜索，定位哪些文本数据包括要搜索的单词。只可以用在MyISAM引擎。

 CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,

    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

    PRIMARY KEY (`id`),

    FULLTEXT (content) );

ALTER TABLE table ADD FULLTEXT(`content`)；

查找时：

SELECT * FROM article WHERE MATCH( content) AGAINST('想查询的字符串')

聚簇索引与非聚簇索引：

聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置。

检索过程如下：

索引的实现原理：B+树。

十五、触发器
触发器（TRIGGER）是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时，会激活促发其执行相应的操作。

创建触发器：

CREATE TRIGGER [标识触发器名称，用户自行指定]
[标识触发时机，取值为 BEFORE 或 AFTER]
[标识触发事件，取值为 INSERT、UPDATE 或 DELETE] ON [表名]
FOR EACH ROW
[触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句]

FOR EACH ROW：指定对于受触发事件影响的每一行都要激活触发器的动作。例如 用 insert 向一个表中插入多行数据时，触发器会对每一行数据的插入都执行相应触发器动作。

由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

注意：有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。

例子：

-- 班级表
CREATE table class(
classId int(5) PRIMARY KEY not NULL auto_increment,
stuCount int(8)
)
-- 插入3条数据，表示3个班级都为0人
insert into class(stuCount) values(0),(0),(0)
-- 创建学员表
CREATE TABLE student(
stuId int(5) PRIMARY KEY not NULL auto_increment,
classId int(5)
)
-- 创建触发器
DELIMITER $
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end $
DELIMITER 
-- 向学员表中插入4条数据，表示1班2人，2班1人，3班一人
insert into student(classId) values(1),(2),(1),(3)
-- 查询班级表，可以看到班级表中人数随之变化
select * from class

删除触发器：

drop trigger [ if exists][数据库名]触发器名称

注意：当删除一个表的同时，也会自动地删除该表上的触发器，且触发器不能更新或覆盖，为了修改一个触发器必须先删除她，然后在重新创建。

查看触发器：
SHOW TRIGGERS 语句查看触发器信息

SHOW TRIGGERS;

在 triggers 表中查看触发器信息

SHOW TRIGGER trig_book2 ;

十六、存储过程
存储过程（Stored Procedure）是数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

mysql存储过程：
语法：
CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 …] 过程体

DELIMITER //
  CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
    //
DELIMITER ;

IN 输入参数
表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值。

OUT 输出参数
该值可在存储过程内部被改变，并可返回。

INOUT 输入输出参数
调用时指定，并且可被改变和返回。

语句：
if-then-else 语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then 
     -> insert into t values(17);  
     -> end if;  
     -> if parameter=0 then 
     -> update t set s1=s1+1;  
     -> else 
     -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
mysql > DELIMITER ;

case语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3 (in parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> case var  
     -> when 0 then   
     -> insert into t values(17);  
     -> when 1 then   
     -> insert into t values(18);  
     -> else   
     -> insert into t values(19);  
     -> end case;  
     -> end;  
     -> //  
mysql > DELIMITER ; 
case
    when var=0 then
        insert into t values(30);
    when var>0 then
    when var<0 then
    else
end case

while ···· end while语句：

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     -> declare var int;  
     -> set var=0;  
     -> while var<6 do  
     -> insert into t values(var);  
     -> set var=var+1;  
     -> end while;  
     -> end;  
     -> //  
mysql > DELIMITER ;

while 条件 do
    --循环体
end while

repeat···· end repea语句：
它在执行操作后检查结果，而 while 则是执行前进行检查。

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc5 ()  
     -> begin   
     -> declare v int;  
     -> set v=0;  
     -> repeat  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> until v>=5  
     -> end repeat;  
     -> end;  
     -> //  
mysql > DELIMITER ;

repeat
    --循环体
until 循环条件  
end repeat;

loop ·····end loop语句：
loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc6 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v >=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

LABLES 标号：
标号可以用在 begin repeat while 或者 loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

ITERATE迭代：
ITERATE 通过引用复合语句的标号,来从新开始复合语句:

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc10 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> if v=3 then   
     -> set v=v+1;  
     -> ITERATE LOOP_LABLE;  
     -> end if;  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v>=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

十七、游标
游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

使用：
一、声明一个游标: declare 游标名称 CURSOR for table;(这里的table可以是你查询出来的任意集合)
二、打开定义的游标:open 游标名称;
三、获得下一行数据:FETCH 游标名称 into testrangeid,versionid;
四、需要执行的语句(增删改查):这里视具体情况而定
五、释放游标:CLOSE 游标名称;
注:mysql存储过程每一句后面必须用;结尾，使用的临时字段需要在定义游标之前进行声明。
例子：

-  
BEGIN  
  
--定义变量  
declare testrangeid BIGINT;  
declare versionid BIGINT;   
declare done int;  
--创建游标，并存储数据  
declare cur_test CURSOR for   
   select id as testrangeid,version_id as versionid from tp_testrange;  
--游标中的内容执行完后将done设置为1  
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;   
--打开游标  
open cur_test;  
--执行循环  
  posLoop:LOOP  
--判断是否结束循环  
        IF done=1 THEN    
      LEAVE posLoop;  
    END IF;   
--取游标中的值  
    FETCH  cur_test into testrangeid,versionid;  
--执行更新操作  
    update tp_data_execute set version_id=versionid where testrange_id = testrangeid;  
  END LOOP posLoop;  
--释放游标  
CLOSE cur_test;  
  
END  
-

十八、存储引擎

数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

mysql支持的存储引擎：
1、InnoDB 存储引擎
支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其特点是行锁设计、支持外键,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。 从 MySQL 5.5.8 版本开始是默认的存储引擎。

InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用 于建立其表空间。

InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。

对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。

InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解 InnoDB 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。

2、MyISAM 存储引擎
不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统 与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。究其根 本,这也并不难理解。用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗?此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。

3、NDB 存储引擎
年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。

4、Memory 存储引擎
正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。

5、Infobright 存储引擎
第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。

6、NTSE 存储引擎
网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。

7、BLACKHOLE
黑洞存储引擎，可以应用于主备复制中的分发主库。
MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。如果 你喜欢,完全可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅 力所在。

十九、临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
创建：

CREATE TEMPORARY TABLE 临时表名 AS
(
    SELECT *  FROM 旧的表名
    LIMIT 0,10000
);

删除MySQL 临时表：

默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。

二十、事务

1.ACID
A（原子性Atomicity）：原子性指的是事务是一个不可分割的，要么都执行要么都不执行。
C（一致性Consistency）：事务必须使得数据库从一个一致性状态，到另外一个一致性状态。
I（隔离性Isolation）：指的是一个事务的执行，不能被其他的事务所干扰。
D（持久性Durability）：持久性指的是一个事务一旦提交了之后，对数据库的改变就是永久的。

2.隔离级别

3.并发问题
（1）脏读
有俩事务T1,T2。如果T1读了一条数据，这条数据是T2更新的但是还没提交，突然T2觉得不合适进行事务回滚了，也就是不提交了。此时T1读的数据就是无效的数据。
（2）不可重复读
有俩事务T1,T2。如果T1读了一条数据，之后T2更新了这条数据，T1再次读取就发现值变了。
（3）幻读
有俩事务T1,T2。如果T1读了一条数据，之后T2插入了一些新的数据，T1再次读取就会多出现一些数据。
（4）两类丢失更新
第一类丢失更新(回滚丢失，Lost update)
撤销一个事务的时候,把其它事务已提交的更新数据覆盖了。这是完全没有事务隔离级别造成的。
第二类丢失更新(覆盖丢失/两次更新问题，Second lost update)
A事务覆盖B事务已经提交的数据，造成B事务所做操作丢失。

二十一、函数
函数是完成特定功能的SQL语句，函数分类内置函数和自定义函数 (user-defined function UDF)。
创建：

CREATE FUNCTION 函数名(参数 类型,[参数 类型,...])
RETURNS 返回类型 RETURN 表达式值
-- 注意
-- 这种方式不能使用任何SQL语句

CREATE FUNCTION 函数(参数 类型,[参数 类型,...])
RETURNS 返回类型
BEGIN
END;
-- 如果要在函数体中可以使用更为复杂的语法，比如复合结构/流程控制/任何SQL语句/定义变量等。带复合结构的函数体的自定义函数的

函数与存储过程的区别：
存储过程可以有多个in,out,inout参数，而函数只有输入参数类型，而且不能带in.
存储过程实现的功能要复杂一些；而函数的单一功能性（针对性）更强。
存储过程可以返回多个值；存储函数只能有一个返回值。
存储过程一般独立的来执行；而存储函数可以作为其它sql语句的组成部分来出现。
存储过程可以调用存储函数。函数不能调用存储过程。

二十二、备份
1.mysqldump命令

mysqldump -u username -p dbname table1 table2...->BackupName.sql;

mysqldump -u username -p --databases dbname2 dbname2 > Backup.sql

mysqldump -u username -p --all-databases > BackupName.sql

2.直接复制整个数据库项目
注意：这种方法不适用于InnoDB存储引擎的表，而对于MyISAM存储引擎的表很方便。同时，还原时MySQL的版本最好相同。

3.使用mysqlhotcopy工具快速备份
先将需要备份的数据库加上一个读锁，然后用FLUSH TABLES将内存中的数据写回到硬盘上的数据库，最后，把需要备份的数据库文件复制到目标目录。

[root@localhost ~]# mysqlhotcopy [option] dbname1 dbname2 backupDir/

dbname：数据库名称；
backupDir：备份到哪个文件夹下；

二十三、锁
悲观锁（Pessimistic Lock）
顾名思义，很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人拿这个数据就会block（阻塞），直到它拿锁。
悲观锁按使用性质划分：

共享锁（Share Lock）
S锁，也叫读锁，用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源。后面加LOCK IN SHARE MODE。

排他锁（Exclusive Lock）
X锁，也叫写锁，表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。后面加FOR UPDATE。

更新锁
U锁，在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。

悲观锁按作用范围划分：
行锁
锁的作用范围是行级别。

表锁
锁的作用范围是整张表。

乐观锁（Optimistic Lock）
顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以，不会上锁。但是在更新的时候会判断一下在此期间别人有没有更新这个数据，可以使用版本号等机制。

乐观锁的实现方式：
1.版本号（version）
版本号（记为version）：就是给数据增加一个版本标识，在数据库上就是表中增加一个version字段，每次更新把这个字段加1，读取数据的时候把version读出来，更新的时候比较version，如果还是开始读取的version就可以更新了，如果现在的version比老的version大，说明有其他事务更新了该数据，并增加了版本号，这时候得到一个无法更新的通知，用户自行根据这个通知来决定怎么处理，比如重新开始一遍。这里的关键是判断version和更新两个动作需要作为一个原子单元执行，否则在你判断可以更新以后正式更新之前有别的事务修改了version，这个时候你再去更新就可能会覆盖前一个事务做的更新，造成第二类丢失更新，所以你可以使用update … where … and version=”old version”这样的语句，根据返回结果是0还是非0来得到通知，如果是0说明更新没有成功，因为version被改了，如果返回非0说明更新成功。

2.时间戳（使用数据库服务器的时间戳）
时间戳（timestamp）：和版本号基本一样，只是通过时间戳来判断而已，注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间。

3.待更新字段
待更新字段：和版本号方式相似，只是不增加额外字段，直接使用有效数据字段做版本控制信息，因为有时候我们可能无法改变旧系统的数据库表结构。假设有个待更新字段叫count,先去读取这个count,更新的时候去比较数据库中count的值是不是我期望的值（即开始读的值），如果是就把我修改的count的值更新到该字段，否则更新失败。java的基本类型的原子类型对象如AtomicInteger就是这种思想。

4.所有字段
所有字段：和待更新字段类似，只是使用所有字段做版本控制信息，只有所有字段都没变化才会执行更新。

二十四、SQL优化
1、在表中建立索引，优先考虑where、group by使用到的字段。

2、尽量避免使用select *，返回无用的字段会降低查询效率。如下：

SELECT * FROM t 

优化方式：使用具体的字段代替*，只返回使用到的字段。

3、尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id IN (2,3)

SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)

优化方式：如果是连续数值，可以用between代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，可以用exists代替。如下：

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)

4、尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3

（PS：如果or两边的字段是同一个，如例子中这样。貌似两种方式效率差不多，即使union扫描的是索引，or扫描的是全表）

5、尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE '%li%'

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE 'li%'

6、尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0

7、尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t2 WHERE score/10 = 9

SELECT * FROM t2 WHERE SUBSTR(username,1,2) = 'li'

优化方式：可以将表达式、函数操作移动到等号右侧。如下：

SELECT * FROM t2 WHERE score = 10*9

SELECT * FROM t2 WHERE username LIKE 'li%'

8、当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE 1=1

优化方式：用代码拼装sql时进行判断，没where加where，有where加and。