Mysql全程讲解。

1、Mysql常识

数据库在服务器上安装完成后，一般通过客户端管理工具访问服务器，常见的有Navicat、SQLyog等，输入服务器IP、数据库端口号、数据库用户名和密码即可，本地登陆命令是是mysql -u数据库用户-p 登陆密码。登陆后，使用show databases;查看系统中Mysql已经创建出来的数据库，安装完后数默预置就有information_schema，mysql和test三个库。

数据库中的数据都是以二维表的形式被保存在不同的数据表中。其中每行表示一条数据记录，每条记录包含多个字段。数据库中的每张表都会通过几个文件占用一定的存储空间，分别存储表结构、表里数据和表索引信息等。如果确定不再使用某张表，就删除以释放存储空间。

2、数据库设计

2.1、安装数据库

以Mysql为例，多个项目可用同一个数据库，也可以单独使用一台服务器安装全新Mysql，安装完成后需要提供四个信息：

2.2、建库脚本

如何是大型复杂的项目，需要编写规范的建库脚本create_database.sql。但如果是规模较小的项目，本地或远程登录Mysql后在库模式下执行create database 数据库名称;就可以创建项目需要的数据库了。

2.3、建表脚本

实际开发中，当从产品经理处拿到新需求后，首先就是考虑如何建表，根据E-R模型需求有哪些关键信息就建立哪些字段，并且是通过建表脚本或者是客户端软件的建表模板而不是直接登录数据库服务器通过CLI的方式建表。

3、增删改查基础

增

createdatabase 数据库名称; 创建新的数据库。

use 数据库名称; 选择一个数据库对其进行操作。

createtable 表名( 字段1 字段类型[约束或索引][额外设置],

字段2 字段类型[约束或索引] [额外设置],

…………

[索引或约束]，

)[ENGINE=MYISAMAUTO_INCREMENT=1 DEFAULT CHARSET=utf-8]; 创建新表

insertinto 表名(字段列表) values (对应插入的值列表); 插入新记录

altertable 表add 新字段字段类型[宽度] [约束或索引] [额外设置][位置];

删

dropdatabase 数据库名称; 删除数据库

droptable 数据表名称; 删除一张表

deletefrom 表查询条件; 删除表中符合条件的记录

altertable 表drop 字段; 删除一个字段

改

altertable 表名renameto 新表名;改表名

update表名set 字段=新值查询条件;更新表的某个字段

altertable 表change旧字段名新字段名; 改字段名

altertable 表modify字段名新字段类型; 改字段的类型

在型

查

showtables;显示当前库已经存在的表

describle表名; 查看表结构

selecta,b from 表名 查询条件;查看符合条件的表的a和b字段

select distinct namefrom 表名;排重显示表中的name字段

select(temp_hi+temp_lo)/2 astemp_avgfrom weather;

标示符as可作为单个字段或表达式的别名，输出时显示该别名。

4、查询字句、子查询和多表查询

4.1、五种查询子句

a．where子句

where后接布尔表达式针对表中的列发挥作用，查询数据，支持算术运算符、关系运算符和逻辑运算符

1、selectgoods_id,goods_name from goods where market_price - shop_price> 200;

输出market_price比shop_price多200的记录的商品ID和商品名

2、whereprice>=3000 and price <= 5000 or price betweenand price 1000;

取3000-5000或者500-1000的值

3、delete from 表whereproduct_id = 30; 删除表中product_id为30的记录

4、where goods_namelike '诺基亚%'; %匹配0到多个字符

5、where goods_namelike '诺基亚N_'; _匹配单个字符

b．having子句

having与where类似，可以筛选数据，where后的表达式怎么写，having后就怎么写，

不同的是where只能针对表中的列发挥作用，查询数据， Having是对查询结果中的列发挥作用，筛选数据。

1、selectgoods_id,good_name,market_price - shop_price as s from goods having s>200 ;比a.where字句的第一个例子，多输出了差价s

2、select cat_id,market_price from goods wherecat_id> 3 having market_price> 200;输出cat_id、market_price，要求是cat_id>3且market_price大于200的记录

c．group by（分组）

一般情况下group需与统计函数（聚合函数，共五种）一起使用才有意义，从字面意义上理解就是根据“By”指定的规则先对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。

d．order by（排序）

从字面意义上理解就是根据“By”指定的规则对输出结果进行排序

sql语句没有order by//按表中记录的顺序默认输出

order byprice //默认升序排列

order by price asc//升序排列

order by price desc//降序排列

e．limit（限制结果区间）

用于获取一段区间的记录，第一个参数是初始值，第二个参数是偏移量，常常和order by字句合用。

selectgood_id,goods_name,goods_price from goodsorder by good_price limit 3,3;

取价格第4-6高的商品，输出其三字段，并按字段good_price升序

4.2、子查询

a、where型子查询

把内层查询结果当作外层的查询条件

select name from (select name,count(*) as guakefrom stu where score < 60having guake>=2);

查出挂科2门以上的同学有多少个，输出其姓名

b、from型子查询

把内层的查询结果(可以看做是内存里的一张临时表)供外层再次查询

selectname,avg(score) from stu where namein (select name from (select name,count(*) as guake from stuwhere score < 60havingguake>=2)) group by name;

然后获取这些同学的名字，最后再计算每个同学的平均分

4.3、多表查询

当查询结果来自多个表时，就需要进行多表查询，常用的有内连接、外连接和自连接3种，实际开发中一个表的某个字段是主键，另一个表的字段是外键约束。比如查询学生ID、学生姓名和学生老师姓名，但学生表中没有“学生老师姓名”字段只有“老师ID”字段，此时就需要进行多表查询。

4.3.1、内连接

特点是两个表中只有符合连接条件的记录才能在结果集中出现，不符合就过滤掉数据，所以缺陷就是可能丢失信息，比如字段为NULL的时候就查询不出来，又称自然连接。连接条件使用=的是等值连接，不使用=的是不等值连接。

这是使用where的写法，还有一种写法是使用 join或inner join，形式不同实际结果是一样的。
[page]

4.3.2、外连接

方法是根据一张表的记录，在被其连接的表中找出符合连接条件的记录与之匹配，特点是符合的能筛选出来，不符合的也能筛选出来，就弥补了内连接的缺陷。

a、左连接

根据左表的记录，在被连接的右表中找出符合条件的记录与之匹配

写法是left（outer）join 表 on 连接条件

b、右连接

根据右表的记录，在被连接的左表中找出符合条件的记录与之匹配。

写法是使用right（outer） join 表 on 连接条件

c、全外连接

返回两个表中符合条件的所有的记录，没有与之匹配的，用null表示，结果是左连接和右连接的并集。

写法是full （outer） join 表 on 连接条件

4.3.3、自连接

把一个表当两个表使用，通过给表起两个别名，在多级分类场景的表中会使用到。

要求是查询每个员工的编号、姓名和上级领导的名字

缺陷是jpjid为NULL的员工的记录没有筛选出来，所以使用左自连接

5、mysql数据类型

有下面四大类，但具体不止于此

数值类型【整数(int)、浮点数(float)、常表钱(DECIMAL) 】、

字符串(含汉字) 【定长(char)、变长(varcar)、长文本数据(text)】

日期时间【year、month、day、time、datetime】

枚举【单选enum、多选set】

6、Mysql运算符与表达式

和编程语言一样，Mysql也支持三类运算符，运算符和运算元素组成表达式。

6.1、算术运算符

在多个表达式或单元素之间上执行数学运算

主要有+、-、*、/、%等

6.2、关系运算符

用来比较两个表达式或单元素的大小，比较结果是个Boolean值

主要有=和<=>、>、<、>=、<=、<>和!=、

between and、not between and、in、not in、

is null、is not null、like、REGEXP等

例如delete from 表 where user_idis null;就是删除表中的user_id为空的记录

6.3、逻辑运算符

与运算符 and ( && )

或运算符or ( || )

非运算符 not ( ! )

7、Mysql存储引擎

MySQL的存储引擎处于MySQL体系架构中Server端底层，是底层物理结构的实现，用于将数据以各种不同的技术方式存储到文件或者从文件中读出，不同的存储引擎具备不同的IO机制、索引机制和锁定机制。

登陆Mysql后可用showengines当前Mysql支持的所有存储引擎；MySQL5.5以后默认使用InnoDB存储引擎；若要修改默认引擎，可以修改配置文件中的default-storage-engine；可以通过：showvariables like 'default_storage_engine';查看当前数据库的默认引擎。

Mysql常用的存储引擎是InnoDB和MyISAM

8、键Key

describle表名; 查看表结构，可以获得以下信息

数据库中的键（key）又称为关键字，是关系模型中的一个重要概念，它是逻辑结构，不是数据库的物理部分。Key本身只具有约束（constraint）属性，建立唯一键和主键会附带创建索引，所以也具有索引（index）属性。

什么都没有 该字段没有索引

MUL 该字段被设为普通索引字段

UNI 该字段被设为唯一键字段

PRI 该字段被设为主键字段

8.1、唯一键

唯一键，即一个或者一组列，其中没有重复的记录；

一张表中可以设置多个唯一键。

用于创建唯一约束，附带会创建唯一索引，所以unique、unique key、unique index、constraint约束名 unique（字段）其实都是一样的，既是唯一约束也是唯一索引。

8.2、主键

属于唯一键，是一个比较特殊的唯一键，区别在于主键不可为空；

一张表中只能设置一个主键，auto_increment必须搭配主键才能使用。

用于创建主键约束，附带会创建主键索引，所以primarykey、constraint约束名 primary key（字段）其实都是一样的，既是主键约束也是主键索引。

8.3、外键

如果在一个表中设置了外键，这张表就是从表，被参考的表就是主表，是为了保持数据的完整性和一致性。

1、 默认情况下，可以单独删除从表某条记录，但删除主表的时会被从表限制。

2、 On delete cascade时，两表该记录会同步删除

3、 On delete update时，两表该字段会同步更新

用于创建外键约束，不会附带创建索引，建立时带不带constraint都是外键约束。

9、Mysql索引

索引用以提高数据库IO性能，特别是查询的性能，就好比按照目录查找文章，索引采用二叉树(BTREE)和哈希算法的形式组织数据，数据库可以根据索引快速的定位用户需要查询数据的位置。在建立索引时可以给索引起个名字也可以不起。至于是怎么提高数据库性能的，就和数据库底层的存储结构相关了，这个我不是DBA也不懂。

9.1、普通索引

是纯粹的索引，不是通过Key创建的，所以只有优化查询没有什么实际限制作用。

1、一个表中可以把多个字段设为index字段

2、index字段的值可以重复

3、把经常作为查询条件的字段作为index字段

4、一个字段设为index字段后，在表结构中的KEY标志是MUL

9.2、由Key创建的索引

9.2.1、唯一(键)索引

唯一索引是在建立唯一键的时候顺带建立的，所以unique、unique key、uniqueindex、constraint约束名 unique（字段）其实都是一样的，既是唯一约束也是唯一索引。以下这些要求都是Key做的事，index只是优化查询....

1、一个表中可以把多个字段设为unique字段

2、unique字段的值不可以重复

3、unique字段的值允许为NULL

4、一个字段设为unique字段后，在表结构中的KEY标志是UNI
[page]

9.2.2、主键索引

主键索引是在建立主键的时候顺带建立的，所以primary key、constraint约束名 primary key（字段）其实都是一样的，既是主键约束也是主键索引。以下这些要求都是Key做的事，index只是优化查询....

1、一个表中只能建立一个PrimaryKey，但可以多个字段做复合主键

2、主键字段的值不可以重复

3、主键字段的值不允许为NULL

4、auto_increment必须搭配主键才能使用，也经常与主键连用

5、一个字段设为主键字段后，在表结构中的KEY标志是PRI

9.3、全文索引

是纯粹的索引，不是通过Key创建的，所以只有优化查询没有什么实际限制作用。用于在一篇文章中，检索文本信息。

10、Mysql约束

10．1、非空约束

建表时，字段后使用not null可建立非空约束，在表结构的Null字段里是yes标，默认是No，就是可以为NULL。

10．2、默认值约束

建表时，字段后使用DEFAULT可建立默认值约束，在表结构的Default字段里是就是默认值，默认是没有默认值。

10．3、由Key创建的约束

10.3.1、唯一(键)约束

和唯一键索引是一样的

10.3.2、主键约束

和主键索引是一样的

10.3.3、外键约束

如果在一个表中设置了外键，这张表就是从表，被参考的表就是主表，是为了保持数据库信息的完整性和一致性，可以使用constraint给外键约束起个名字也可以不起。

在建立外键约束时需要满足三个条件：

1、从表和主表都使用innodb存储引擎

2、从表的外键字段和主表中的参考字段必须是统一数据类型

3、主表中被参考的字段要有明确的索引，最好是主键索引而且带auto_increment。

1、默认情况下，和no action一样什么都不做。可以单独删除从表某条记录，但删除主表的时会被从表限制；可以单独编辑从表某条记录的字段，但编辑主表的时会被从表限制。

2、On delete cascade时，如果主表中的记录被删除，子表中改行也相应删除

3、On update cascade时，如果主表中被参考字段更新，子表中也更新

10．4、CHECK约束

Mysql不支持此约束。

11、Mysql锁

锁是数据库用以解决多进程或多用户访问同一资源的并发问题......

11.1、锁粒度

就是通常所说的锁级别，为了尽量减少锁定的开销和提高并发度，数据库引擎会自动将资源锁定在适合任务的级别。不同的存储引擎支持不同的锁粒度，共有三种锁粒度。

a、表级锁

某张表是MyISAM存储引擎时，加锁和解锁的操作是针对整张表

1、开销低，只需判断表是否被加锁，然后获取锁、操作、释放锁

2、并发访问量低，因为是忽略表中记录，锁住了整张表

b、行级锁

某张表是innoDB存储引擎时，加锁和解锁是针对某个记录单独操作，

1、开销高，操作表里的每一条记录时都要对加锁进行判断、获取锁、操作、释放锁

2、并发访问量高，因为是需要锁住哪条记录就只操作那条，其他记录不限制用户访问

c、页面锁

一般不用

11.2、锁类型

a、读锁

学名shared locks，就是共享锁，因为读操作不会改变数据，所以当某个用户获取读锁后，其他用户仍然可以进行读操作（select、show等），但是不能写（update、insert、delete等）。加解读锁是用lock/unlock ..... read....。

b、写锁

学名Exclusive Locks，就是独占锁，因为写操作会改变数据，所以当某个用户获取写锁后，其他用户无论是读还是写都要等待释放。加解写锁是用lock/unlock ..... write....。

12、Mysql事务

12.1、事务及其特性

事务由一条或者多条sql语句组成，在事务中的操作，这些sql语句要么都执行，要么都不执行，这就是事务的目的。事务需要满足ACID特性

A；原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，整个事务的执行才算成功。事务中任何一个sql语句执行失败，那么已经执行成功的sql语句也必须撤销，数据库状态应该退回到执行事务前的状态；

C；一致性指事务将数据库从一种状态转变为另一种一致的状态，在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏；

I；隔离性也叫做并发控制或者锁，要求每个读写事务的对象与其它事务的操作对象能相互分离，即该事务提交前对其它事务都不可见，这通常配合锁机制来实现；

D；持久性是表示事务一旦提交了，其结果就是永久性的，也就是数据就已经写入到数据库了，如果发生了宕机等事故，数据库也能将数据恢复。

12.2、事务实例

12.3、与锁配合实现隔离性

Mysql使用事务时会发生脏读和幻读问题

脏读：是指当一个事务正在访问数据，并且对数据进行了修改，在提交之前，另外一个进程恰好读取这个数据，这个进程读取的和commit之后的数据就不一致了。

幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么第一个事务的用户commit之后就会发现表中还有没有修改的数据行，就好象发生了幻觉一样。

所以需要对第一个事务操作的对象加写锁，阻塞其他事务的读写请求。

13、Mysql触发器

13.1、触发器及基础操作

可以看做自动执行的存储过程，当触发器被指定的事件激活后，就会执行一系列SQL指令，这些指令在BEGIN和END之间，并支持逻辑代码如流程控制。

CREATE TRIGGER <触发器名称> --触发器必须有名字，名字不可重复，同一个表也不行
{ BEFORE | AFTER } --触发器执行的时间设置：一般会事件发生后。
{ INSERT | UPDATE | DELETE } --设定激活事件：insert、update或delete三种事件。
ON <表名称> --触发器属于某一个表，不能给同一张表的同一个事件安排两个触发器。
FOR EACH ROW --触发器的执行间隔：FOR EACH ROW子句通知触发器针对每一行
<触发器SQL语句>;--被触发的操作：new指激活表。

DROP TRIGGER <触发器名称> ； --删除当前库的某个触发器。

13.2、触发器实例

14、Mysql函数

14.1、内置函数

Mysql数据库内置的函数，不用人为定义就可以直接使用，比如五种统计函数，max求最大值、min求最小值、min求最小值、avg求平均值、count求总行数。

select count(*) asguake from stu where score < 60;输出60分以下同学总数。

14.2、自定义函数

15、Mysql存储过程

15.1、存储过程

大多数SQL语句都是针对一个或多个表的单条语句。并非所有的操作都怎么简单，经常会有一个完整的操作需要多条才能完成，存储过程就是为以后的使用而保存的一条或多条SQL语句的集合。存储过程经过编译后存储在数据库中，使用者指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用它，实现特定功能。

15.2、存储过程实例

15.3、存储过程与函数的异同

本质上没有区别，都是使用前创建的SQL的语句，为了可重复地 操作数据库，区别主要在使用方法和效率上。

1、都可以使用参数，也可以不用参数，都是输入、输出和输入输出三种类型。

2、流程上都是先创建再调用，也可以删除。

3、函数必须且只能返回一个参数，存储过程可以不返回也可以返回多个。

4、存储过程经过编译后就会存在服务器中，以后调用就不用再编译了，但函数每次调用都会编译一次，所以存储过程的效率高。

5、函数可以直接嵌入在sql语句中，一般是在select语句中，存储过程是作为一个独立部分来调用或执行（Mysql是call,Oracle是exec）。