mysql知识点整理

数据库的概述：数据库指长期保存在计算机的存储设备上，按照一定的规则组织起来，可以被各种用户
共享的数据聚合；
1、sql语句
SELECT * FROM orderitem ORDER BY RAND() LIMIT 3 //随机抽取三条数据
select * from 表名1 left join 表名2 on id=id; //左连接查询
select * from 表名1 left outer 表名2 on id=id; //左外连接查询 作用一样
select * from 表名1 inner join 表名 on id=id；//内连接查询
2、数据库的备份与恢复
备份：c:\users\mysql\mysqldump -uroot -p123456 day_06>路径\day_06.sql
恢复：c:\users\mysql\mysql -uroot -p123456 day_06<路径\day_06.sql
3、sql的分类
DDL：数据库定义语言
操作对象：数据库 表
关键字：create alert drop
DML：数据库操作语言
操作对象：记录
关键字：insert update delete
DQL：数据库查询语言
操作对象：记录
关键字：select
DCL：数据库控制语言
操作对象：用户 权限 事务
4、创建
create database mydb1
create database mydb2 chracter set gbk;
create database mydb3 character set gbk collate gbk _chinese_ci;
5、查询
查看当前数据库服务其中的所有数据
show database ；
查看前面创建的mydb2的数据库的定义信息
show create database mydb2；
删除前面创建的mydb3数据库
drop database mydb3;
6、修改
查看服务器中的数据库，并把mydb2中的字符集修改成utf8；
alert database mydb2 character set utf8
7、删除
drop database mydb3
8、其他；
查看当前使用的数据库
select database（）；
切换数据库
use mydb2；
9、操作数据库表
*语法
create table 表名（
字段1 字段类型
字段2 字段类型
字段3 字段类型 ）
*常用数据类型：
int：整型
double：浮点型，例如double（5,2）表示最多5位，其中必须有两位小数，
即最大值为999.99
char:固定的长度字符串类型；char（10）’abc
varchar:可变长度字符串类型；varchar（10）‘abc’
text:字符串类型；
blob；字节类型；（图片视频音频）
date；日期类型，格式为 yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss
timestamp;时间戳类型 yyyy-MM-dd hh:mm:ss

当前数据库中的所有表
show tables
查看表的字段信息
desc employee
在上面员工表的基本上增加一个image列
alert table employee add image blob
修改name列，使其长度为100；
alert table employee modify name varchar（100）；
删除image列，一次只能一列
alert table employee drop image;
表名改为user
rename table employee to user;
查看表格的创建细节
show create table user；
修改表的字符集为gbk
alert table user character set gbk;
列名name修改username
alert table user change name username varchar(100);
删除表
drop table user;
10、DML操作（重要）
查询表中的所有数据
select * from 表名；
DML 是对表中的数据进行增删查改的操作。不要与 DDL混淆了。
insert、update、delete
小知识
在MySQL中，字符串类型和日期类型都要单引号括起来。‘tom’’2015-09-04’
空值：null
11、插入操作：insert
语法：insert into 表名（列名1，列名2….）values(列值1，列值2…..)
注意：列名与列值得类型、个数、顺序要一一对相应。可以把列名当做java中的形参，把列值当做实参。值不要超出列定义的长度。如果插入空值
MySQL

第一节 MySQL引擎

1.1 概念

数据库中的存储引擎其实是对使用了该引擎的表进行某种设置，数据库中的表设定了什么存储引擎，那么该表在数据存储方式、数据更新方式、数据查询性能以及是否支持索引等方面就会有不同的“效果”。在MySQL数据库中存在着多种引擎（不同版本的MySQL数据库支持的引擎不同），熟悉各种引擎才能在软件开发中应用引擎，从而开发出高性能的软件。

1.2 分类

MySQL有以下几种引擎：ISAM、MyISAM、HEAP（也称为MEMORY）、CSV、BLACKHOLE、ARCHIVE、PERFORMANCE_SCHEMA、InnoDB、 Berkeley、Merge、Federated和Cluster/NDB等，除此以外我们也可以参照MySQL++ API创建自己的数据库引擎

1.2.1 MyISAM

该引擎基于ISAM数据库引擎，除了提供ISAM里所没有的索引和字段管理等大量功能，MyISAM还使用一种表格锁定的机制来优化多个并发的读写操作，但是需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间，否则碎片也会随之增加，最终影响数据访问性能。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMChk工具和用来恢复浪费空间的 MyISAMPack工具。MyISAM强调了快速读取操作，主要用于高负载的select，这可能也是MySQL深受Web开发的主要原因：在Web开发中进行的大量数据操作都是读取操作，所以大多数虚拟主机提供商和Internet平台提供商（Internet Presence Provider，IPP）只允许使用MyISAM格式。

MyISAM类型的表支持三种不同的存储结构：静态型、动态型、压缩型。

静态型：指定义的表列的大小是固定（即不含有：xblob、xtext、varchar等长度可变的数据类型），这样MySQL就会自动使用静态MyISAM格式。使用静态格式的表的性能比较高，因为在维护和访问以预定格式存储数据时需要的开销很低；但这种高性能是以空间为代价换来的，因为在定义的时候是固定的，所以不管列中的值有多大，都会以最大值为准，占据了整个空间。

动态型：如果列（即使只有一列）定义为动态的（xblob, xtext, varchar等数据类型），这时MyISAM就自动使用动态型，虽然动态型的表占用了比静态型表较少的空间，但带来了性能的降低，因为如果某个字段的内容发生改变则其位置很可能需要移动，这样就会导致碎片的产生，随着数据变化的增多，碎片也随之增加，数据访问性能会随之降低。

对于因碎片增加而降低数据访问性这个问题，有两种解决办法：

1、尽可能使用静态数据类型；

2、经常使用optimize table table_name语句整理表的碎片，恢复由于表数据的更新和删除导致的空间丢失。如果存储引擎不支持 optimize table table_name则可以转储并 重新加载数据，这样也可以减少碎片；

压缩型：如果在数据库中创建在整个生命周期内只读的表，则应该使用MyISAM的压缩型表来减少空间的占用。

1.2.2 InnoDB

  该存储引擎为MySQL表提供了ACID事务支持、系统崩溃修复能力和多版本并发控制（即MVCC Multi-Version Concurrency Control）的行级锁;该引擎支持自增长列（auto_increment）,自增长列的值不能为空，如果在使用的时候为空则自动从现有值开始增值，如果有但是比现在的还大，则直接保存这个值; 该引擎存储引擎支持外键（foreign key） ,外键所在的表称为子表而所依赖的表称为父表。该引擎在5.5后的MySQL数据库中为默认存储引擎。

ACID:原子性 隔离性 一致性 持久性

1.2.3 ISAM

该引擎在读取数据方面速度很快，而且不占用大量的内存和存储资源；但是ISAM不支持事务处理、不支持外来键、不能够容错、也不支持索引。该引擎在包括MySQL 5.1及其以上版本的数据库中不再支持。

1.2.4 HEAP

  也称为MEMORY,该存储引擎通过在内存中创建临时表来存储数据。每个基于该存储引擎的表实际对应一个磁盘文件，该文件的文件名和表名是相同的，类型为.frm。该磁盘文件只存储表的结构，而其数据存储在内存中，所以使用该种引擎的表拥有极高的插入、更新和查询效率。

这种存储引擎默认使用哈希（HASH）索引，其速度比使用B-+Tree型要快，但也可以使用B树型索引。由于这种存储引擎所存储的数据保存在内存中，所以其保存的数据具有不稳定性，比如如果mysqld进程发生异常、重启或计算机关机等等都会造成这些数据的消失，所以这种存储引擎中的表的生命周期很短，一般只使用一次。

1.2.5 CSV

 （Comma-Separated Values逗号分隔值）,使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件（所以，它可以将CSV类型的文件当做表进行处理），这种文件是一种普通文本文件，每个数据行占用一个文本行。该种类型的存储引擎不支持索引，即使用该种类型的表没有主键列；另外也不允许表中的字段为null。

1.2.6 BLACKHOLE

   （黑洞引擎）,该存储引擎支持事务，而且支持mvcc的行级锁，写入这种引擎表中的任何数据都会消失，主要用于做日志记录或同步归档的中继存储，这个存储引擎除非有特别目的，否则不适合使用。

1.2.7 ARCHIVE

    该存储引擎非常适合存储大量独立的、作为历史记录的数据。区别于InnoDB和MyISAM这两种引擎，ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差一些。

1.2.8 PERFORMANCE_SCHEMA

  该引擎主要用于收集数据库服务器性能参数。这种引擎提供以下功能：提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。

1.2.9 Berkeley（BDB）

    该存储引擎支持COMMIT和ROLLBACK等其他事务特性。该引擎在包括MySQL 5.1及其以上版本的数据库中不再支持

1.2.10 Merge

 该引擎将一定数量的MyISAM表联合而成一个整体，该存储引擎允许将一组使用MyISAM存储引擎的并且表结构相同（即每张表的字段顺序、字段名称、字段类型、索引定义的顺序及其定义的方式必须相同）的数据表合并为一个表，方便了数据的查询

1.2.11 Federated

该存储引擎可以不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。这种存储引擎非常适合数据库分布式应用。

1.2.12 Cluster/NDB

该存储引擎用于多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大、安全和性能要求高的场景。

第二节 索引

1.1 简介

1.2 格式

格式：

CREATE TABLE table_name[col_name data type][unique|fulltext][index|key][index_name](col_name[length])[asc|desc]1.unique|fulltext为可选参数，分别表示唯一索引、全文索引2.index和key为同义词，两者作用相同，用来指定创建索引3.col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度6.asc或desc指定升序或降序的索引值存储

1.3 分类

1.3.1 普通索引

是最基本的索引，它没有任何限制

（1）直接创建索引

CREATE INDEX index_name ON table(column(length))

（2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

（3）创建表的时候同时创建索引

CREATE TABLE `table` (    `id` int(11) NOT NULL AUTO_INCREMENT ,    `title` char(255) CHARACTER NOT NULL     PRIMARY KEY (`id`),    INDEX index_name (title(length)))

（4）删除索引

DROP INDEX index_name ON table

1.3.2 唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

（1）创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

（2）修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

（3）创建表的时候直接指定

CREATE TABLE table (`id` int(11) NOT NULL AUTO_INCREMENT ,`title` char(255) CHARACTER NOT NULL ,`content` text CHARACTER NULL ,`time` int(10) NULL DEFAULT NULL ,UNIQUE indexName (title(length)));

1.3.3 主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

CREATE TABLE `table` (    `id` int(11) NOT NULL AUTO_INCREMENT ,    `title` char(255) NOT NULL ,    PRIMARY KEY (`id`));

1.3.4 组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

1.3.5 全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

（1）创建表的适合添加全文索引

CREATE TABLE table (`id` int(11) NOT NULL AUTO_INCREMENT ,`title` char(255) CHARACTER NOT NULL ,`content` text CHARACTER NULL ,`time` int(10) NULL DEFAULT NULL ,PRIMARY KEY (`id`),FULLTEXT (content));

（2）修改表结构添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

（3）直接创建索引

CREATE FULLTEXT INDEX index_content ON article(content)

1.4 索引缺陷

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。

2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。

索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

1.5 索引总结

1.5.1 索引不会包含有null值的列

只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。

1.5.2 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

1.5.3 索引列排序

查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

1.5.4 like语句操作

一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%bb%” 不会使用索引而like “bb%”可以使用索引。

1.5.5 不要在列上进行运算

这将导致索引失效而进行全表扫描，例如

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

1.5.6 不使用not in和<>操作

第三节 存储过程

3.1 概念

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

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

存储过程是数据库的一个重要的功能，MySQL 5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0开始支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性

3.2 优点

(a).增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

(b).标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

(c).较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

(d).减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。

(e).作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

3.3 格式

CREATE PROCEDURE  存储过程名([[IN|OUT|INOUT] 参数名 数据类型[[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体DELIMITER //  CREATE PROCEDURE myproc(OUT s int)    BEGIN      SELECT COUNT(*) INTO s FROM students;    END    //DELIMITER ;

分隔符

MySQL默认以”;”为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个”//”之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。

参数

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用”,”分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型IN,OUT,INOUT:

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

OUT:该值可在存储过程内部被改变，并可返回

INOUT:调用时指定，并且可被改变和返回

过程体

过程体的开始与结束使用BEGIN与END进行标识

3.4 创建和调用和删除

创建：

DELIMITER //CREATE PROCEDURE `proc_add`(IN a int, IN b int, OUT sum int)BEGIN    DECLARE c int;    if a is null then set a = 0;     end if;    if b is null then set b = 0;    end if;    set sum  = a + b;END//DELIMITER ;

调用：

set @b=5;call proc_add(2,@b,@s);select @s as sum;

运行结果：

mysql> DELIMITER //CREATE PROCEDURE `proc_add`(IN a int, IN b int, OUT sum int)BEGIN    DECLARE c int;    if a is null then set a = 0;     end if;    if b is null then set b = 0;    end if;    set sum  = a + b;END//DELIMITER ;Query OK, 0 rows affectedmysql> set @b=5;call proc_add(2,@b,@s);select @s as sum;Query OK, 0 rows affectedQuery OK, 0 rows affected+-----+| sum |+-----+|   7 |+-----+1 row in set

3.5 存储过程中的语句

局部变量：

格式：DECLARE 变量名1[,变量名2…] 数据类型 [默认值];

赋值：set 变量名

使用： select 变量名

用户变量

用户变量一般以@开头

注意：滥用用户变量会导致程序难以理解及管理

格式：set @变量名

3.5.1 if

创建：

DELIMITER // CREATE PROCEDURE `proc_if`(IN type int)BEGIN    #Routine body goes here...    DECLARE c varchar(500);    IF type = 0 THEN        set c = 'param is 0';    ELSEIF type = 1 THEN        set c = 'param is 1';    ELSE        set c = 'param is others, not 0 or 1';    END IF;    select c;END//DELIMITER ;

调用：

set @t=1;call proc_if(@t);

运行结果：

mysql> DELIMITER // CREATE PROCEDURE `proc_if`(IN type int)BEGIN    #Routine body goes here...    DECLARE c varchar(500);    IF type = 0 THEN        set c = 'param is 0';    ELSEIF type = 1 THEN        set c = 'param is 1';    ELSE        set c = 'param is others, not 0 or 1';    END IF;    select c;END//DELIMITER ;Query OK, 0 rows affectedmysql> set @t=1;call proc_if(@t);Query OK, 0 rows affected+------------+| c          |+------------+| param is 1 |+------------+1 row in setQuery OK, 0 rows affectedmysql> 

3.5.2 case

创建：

DELIMITER //CREATE PROCEDURE `proc_case`(IN type int)BEGIN    DECLARE c varchar(500);    CASE type    WHEN 0 THEN        set c = 'param is 0';    WHEN 1 THEN        set c = 'param is 1';    ELSE        set c = 'param is others, not 0 or 1';    END CASE;    select c;END//DELIMITER ;

调用：

set @p=2;call proc_case(@p);

结果：

mysql> DELIMITER //CREATE PROCEDURE `proc_case`(IN type int)BEGIN    DECLARE c varchar(500);    CASE type    WHEN 0 THEN        set c = 'param is 0';    WHEN 1 THEN        set c = 'param is 1';    ELSE        set c = 'param is others, not 0 or 1';    END CASE;    select c;END//DELIMITER ;Query OK, 0 rows affectedmysql> set @p=2;call proc_case(@p);Query OK, 0 rows affected+-----------------------------+| c                           |+-----------------------------+| param is others, not 0 or 1 |+-----------------------------+1 row in setQuery OK, 0 rows affectedmysql> 

3.5.3 while

创建：

DELIMITER //CREATE PROCEDURE `proc_while`(IN n int)BEGIN    DECLARE i int;    DECLARE s int;    SET i = 0;    SET s = 0;    WHILE i <= n DO        set s = s + i;        set i = i + 1;    END WHILE;    SELECT s;END//DELIMITER ;

调用：

set @n=10;call proc_while(@n);

结果：

mysql> DELIMITER //CREATE PROCEDURE `proc_while`(IN n int)BEGIN    DECLARE i int;    DECLARE s int;    SET i = 0;    SET s = 0;    WHILE i <= n DO        set s = s + i;        set i = i + 1;    END WHILE;    SELECT s;END//DELIMITER ;Query OK, 0 rows affectedmysql> set @n=10;call proc_while(@n);Query OK, 0 rows affected+----+| s  |+----+| 55 |+----+1 row in setQuery OK, 0 rows affectedmysql> 

3.6 对表建存储过程

创建:

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

调用：

set @c=0;call proc1(@c);select @c;

运行结果：

mysql> DELIMITER // CREATE PROCEDURE proc1(OUT s int)  BEGIN SELECT COUNT(*) INTO s FROM role; END // DELIMITER ;Query OK, 0 rows affectedmysql> set @c=0;call proc1(@c);select @c;Query OK, 0 rows affectedQuery OK, 1 row affected+----+| @c |+----+|  2 |+----+1 row in setmysql> 

聚合函数

mysql> DELIMITER // CREATE PROCEDURE proc2()  BEGIN SELECT avg(score) avgs,sum(score) sumscode,max(score) maxscore  FROM tb_score; END // DELIMITER ;Query OK, 0 rows affectedmysql> call proc2();+---------+----------+----------+| avgs    | sumscode | maxscore |+---------+----------+----------+| 33.0000 | 99       |       44 |+---------+----------+----------+1 row in setQuery OK, 0 rows affectedmysql> 

3.7 弊端

不同数据库，语法差别很大，移植困难，换了数据库，需要重新编写；

不好管理，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务，其它的必要性不是很大；

第四节 视图

4.1 概述

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。

视图是存储在数据库中的查询的SQL 语句，它主要出于两种原因：

安全原因， 视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，

可使复杂的查询易于理解和使用。这个视图就像一个“窗口”，从中只能看到你想看的数据列

4.2 好处

1、 视图能简化用户操作

2、 视图使用户能以多种角度看待同一数据

3、 视图对重构数据库提供了一定程度的逻辑独立性

4、视图能够对机密数据提供安全保护

5、适当的利用视图可以更清晰地表达查询

4.3 使用

格式：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [db_name.]view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

创建：

create view v_r(rid,rname,rmk) as select * from role;

使用：

select * from v_r;

运行结果：

mysql> create view v_r(rid,rname,rmk) as select * from role;Query OK, 0 rows affectedmysql> select * from v_r;+-----+-------+------------+| rid | rname | rmk        |+-----+-------+------------+|   1 | admin | 超级管理员 ||   2 | test  | remark     ||   3 | abc   | aaa        |+-----+-------+------------+3 rows in setmysql> 

第五节 触发器

第六节 MySQL的锁

6.1 概念

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不能例外。

MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定。

6.2 分类

6.2.1 表级锁定（table-level）

表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。

使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

6.2.2 行级锁定（row-level）

行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

使用行级锁定的主要是InnoDB存储引擎。

6.2.3 页级锁定（page-level）

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。

使用页级锁定的主要是BerkeleyDB存储引擎。

6.2.4 总结

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

6.3 表级锁定

MyISAM存储引擎使用的锁定机制完全是由MySQL提供的表级锁定实现，所以下面我们将以MyISAM存储引擎作为示例存储引擎。

6.3.1 MySQL表级锁的锁模式

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。锁模式的兼容性：

对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；

MyISAM表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

6.3.2 设置表锁

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

6.3.3 MyISAM锁优化

对于MyISAM存储引擎，虽然使用表级锁定在锁定实现的过程中比实现行级锁定或者页级锁所带来的附加成本都要小，锁定本身所消耗的资源也是最少。但是由于锁定的颗粒度比较到，所以造成锁定资源的争用情况也会比其他的锁定级别都要多，从而在较大程度上会降低并发处理能力。所以，在优化MyISAM存储引擎锁定问题的时候，最关键的就是如何让其提高并发度。由于锁定级别是不可能改变的了，所以我们首先需要尽可能让锁定的时间变短，然后就是让可能并发进行的操作尽可能的并发。

查询表级锁争用情况

mysql>  show status like 'table%';+----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| Table_locks_immediate      | 112   || Table_locks_waited         | 0     || Table_open_cache_hits      | 0     || Table_open_cache_misses    | 0     || Table_open_cache_overflows | 0     |+----------------------------+-------+5 rows in setmysql> 

Table_locks_immediate：产生表级锁定的次数；

Table_locks_waited：出现表级锁定争用而发生等待的次数；

两个状态值都是从系统启动后开始记录，出现一次对应的事件则数量加1。如果这里的Table_locks_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。

（2）缩短锁定时间

如何让锁定时间尽可能的短呢？唯一的办法就是让我们的Query执行时间尽可能的短。

a)尽两减少大的复杂Query，将复杂Query分拆成几个小的Query分布进行；

b)尽可能的建立足够高效的索引，让数据检索更迅速；

c)尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型；

d)利用合适的机会优化MyISAM表数据文件。

（3）分离能并行的操作

说到MyISAM的表锁，而且是读写互相阻塞的表锁，可能有些人会认为在MyISAM存储引擎的表上就只能是完全的串行化，没办法再并行了。大家不要忘记了，MyISAM的存储引擎还有一个非常有用的特性，那就是ConcurrentInsert（并发插入）的特性。

MyISAM存储引擎有一个控制是否打开Concurrent Insert功能的参数选项：concurrent_insert，可以设置为0，1或者2。三个值的具体说明如下：

concurrent_insert=2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录；

concurrent_insert=1，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置；

concurrent_insert=0，不允许并发插入。

可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。例如，将concurrent_insert系统变量设为2，总是允许并发插入；同时，通过定期在系统空闲时段执行OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。

（4）合理利用读写优先级

MyISAM存储引擎的是读写互相阻塞的，那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？

答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前。

这是因为MySQL的表级锁定对于读和写是有不同优先级设定的，默认情况下是写优先级要大于读优先级。

所以，如果我们可以根据各自系统环境的差异决定读与写的优先级：

通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接读比写的优先级高。如果我们的系统是一个以读为主，可以设置此参数，如果以写为主，则不用设置；

通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

虽然上面方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。

6.4 行级锁定

行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的，InnoDB是目前事务型存储引擎中使用最为广泛的存储引擎

1.InnoDB锁定模式及实现机制

考虑到行级锁定君由各个存储引擎自行实现，而且具体实现也各有差别，而InnoDB是目前事务型存储引擎中使用最为广泛的存储引擎，所以这里我们就主要分析一下InnoDB的锁定特性。

总的来说，InnoDB的锁定机制和Oracle数据库有不少相似之处。InnoDB的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。所以，可以说InnoDB的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX）

第七节 优化

7.1 慢查询

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能

slow_query_log 慢查询开启状态

slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）

long_query_time 查询超过多少秒才记录

show variables like 'slow_query%';   #慢查询状态和文件show variables like 'long_query_time'; #慢查询的时间

7.3.2 设置

方法一：全局变量设置

将 slow_query_log 全局变量设置为“ON”状态

设置慢查询日志存放的位置

查询超过1秒就记录

 set global slow_query_log='ON';  set global slow_query_log_file='/usr/local/mysql/data/slow.log'; set global long_query_time=1;

方法二：配置文件设置

修改配置文件my.cnf，在[mysqld]下的下方加入

[mysqld]slow_query_log = ONslow_query_log_file = /usr/local/mysql/data/slow.loglong_query_time = 1

重启MySQL服务

7.3.3 测试

 select sleep(3);

查看慢查询的文件，是否记录语句

7.2 explain

格式：

EXPLAIN tbl_name或：EXPLAIN [EXTENDED] SELECT select_options

mysql> explain role;+--------+--------------+------+-----+---------+----------------+| Field  | Type         | Null | Key | Default | Extra          |+--------+--------------+------+-----+---------+----------------+| ID     | int(11)      | NO   | PRI | NULL    | auto_increment || NAME   | varchar(100) | NO   |     | NULL    |                || REMARK | varchar(50)  | NO   | MUL | NULL    |                |+--------+--------------+------+-----+---------+----------------+3 rows in setmysql> explain select * from role;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+|  1 | SIMPLE      | role  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |      100 | NULL  |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+1 row in set各个属性的含义idselect查询的序列号select_typeselect查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询。table输出的行所引用的表。type联合查询所使用的类型。type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般来说，得保证查询至少达到range级别，最好能达到ref。possible_keys指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。key显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。key_len显示MySQL决定使用的键长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。ref显示哪个字段或常数与key一起被使用。rows这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的。Extra如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。如果是where used，就是使用上了where限制。如果是impossible where 表示用不着where

7.3 SQL优化

7.3.1 只要一行数据时使用 LIMIT 1

7.3.2 EXPLAIN SELECT 查询

7.3.3 为搜索字段建索引

7.3. 4 Join表的时候使用相同类型的列，并将其索引

7.3.5 千万不要 ORDER BY RAND()

7.3.5 永远为每张表设置一个ID

7.3.6 使用 ENUM 而不是 VARCHAR

7.3.7 PROCEDURE ANALYSE()