1500字范文 > 零基础一天理解和掌握Mysql数据库

零基础一天理解和掌握Mysql数据库

时间：2023-02-25 17:27:07

相关推荐

零基础一天理解和掌握Mysql数据库

1.什么是数据库?1.1MySQL数据库 2.数据库语法2.1.Mysql中的排序规则utf8_unicode_ci、utf8_general_ci的区别2.2.mysql中引号的用法（反引号``，单引号''，双引号""） 3. 表语法3.1.创建表3.1.1.SQL DEFAULT 约束 3.2. 查看表结构3.3. 设置主键3.3.1.在修改表的时候添加主键约束3.3.2.删除主键约束 3.4. 完整的表约束3.5. 修改表MySQL----alter table modify | change的不同 3.6. 删除表 4.数据–增删改查(curd)4.1.MySQL 正则表达式4.2. curd的解释: 5.视图5.1.概述5.2 视图和表区别和联系5.3. MySQL创建视图（CREATE VIEW）5.3.1创建基于单表的视图5.3.2创建基于多表的视图 5.4 查询视图5.5. 更新(修改)视图数据5.6. 删除视图 6. 触发器语法7. 查询数据7.1. 基本查询语句7.2. 连接查询(从多个数据表中读取数据)7.3. 子查询7.4. 分组查询group by 8. 索引8.1介绍8.2. MySQL的索引分类8.3.索引的两大类型hash与btree8.4.创建/删除索引的语法8.5.测试索引8.6.正确使用索引8.7.慢查询优化的基本步骤 9. 账号管理9.1. 新建用户9.2. 删除用户9.3. 查询数据库的用户9.4. 修改密码9.5. 修改用户host：是否可以远程访问9.6. 忘记root密码，找回root密码(mysql忘记密码) 10. 权限管理10.1. 授权10.2. 收回权限 11.MySQL 事务11.1事务控制语句11.2使用保留点 SAVEPOINT

1.什么是数据库?

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。
我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。
所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS 即关系数据库管理系统(Relational Database Management System)的特点:
数据以表格的形式出现每行为各种记录名称每列为记录名称所对应的数据域许多的行和列组成一张表单若干的表单组成database

1.1MySQL数据库

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL 是开源的，目前隶属于 Oracle 旗下产品。MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL 使用标准的 SQL 数据语言形式。MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。MySQL 对PHP有很好的支持，PHP 是目前最流行的 Web 开发语言。MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

2.数据库语法

mysql -u root -p password 登入数据库，没有切换到任何数据库，才可以执行操作数据库的操作.

2.1.Mysql中的排序规则utf8_unicode_ci、utf8_general_ci的区别

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。utf8_general_ci 校对速度快，但准确度稍差。utf8_unicode_ci 准确度高，但校对速度稍慢。如果你的应用有德语、法语或者俄语，请一定使用utf8_unicode_ci。一般用utf8_general_ci就够了。

ci是 case insensitive, 即 “大小写不敏感”, a 和 A 会在字符判断中会被当做一样的；
bin 是二进制, a 和 A 会别区别对待。
例如你运行：
SELECT * FROM table WHERE txt = ‘a’那么在utf8_bin中你就找不到 txt = ‘A’ 的那一行，而 utf8_general_ci 则可以。utf8_general_ci不区分大小写，这个你在注册用户名和邮箱的时候就要使用。utf8_general_cs区分大小写，如果用户名和邮箱用这个就会照成不良后果utf8_bin:字符串每个字符串用二进制数据编译存储。区分大小写，而且可以存二进制的内容

2.2.mysql中引号的用法（反引号``，单引号’’，双引号""）

#这里的'图书ID' 就使用到了单引号#而CREATE TABLE `book`里的book就使用到了反引号#那么他们具体的有什么区别呢？CREATE TABLE `book` (`book_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '图书ID',`name` VARCHAR ( 100 ) NOT NULL COMMENT '图书名称',`number` INT NOT NULL COMMENT '图书数量',PRIMARY KEY ( `book_id` ) ) ENGINE = INNODB AUTO_INCREMENT = 1000 DEFAULT CHARSET = utf8 COMMENT = '图书表';

单引号：我们在例子中的条件值周围使用的是单引号。SQL 使用单引号来环绕文本值。如果是数值，请不要使用引号。
按照别的说法来说就是Varchar类型（也可以说是String、字符串类型）这一些在数据库语句中使用的时候应该使用单引号，而不是直接使用。
而对于数值类型的，反而是不能使用单引号。
如下图所示正确和错误的使用方法
反引号：它是为了区分MYSQL的保留字与普通字符而引入的符号。
注意划重点：有MYSQL保留字作为字段的，必须加上反引号来区分！！！所谓的保留字就是select database insert 这一类数据库的sql指令，当我们不得已要拿他们来做表名和字段名的时候我们必须要加反引号来避免编译器把这部分认为是保留字而产生错误。当然，在上面的例子中，book并不是保留字，这么加反引号只是作一个保险，这也是一个良好的sql建表习惯。务必要记住：保留字既不能作为表名，也不能作为字段名，如果非要这么操作，请记住要增加反引号！
3.双引号的用法和单引号有所类似，大多数数据库都支持单引号和双引号的互换，即varchar类型的变量既可以用单引号来囊括，也可以用双引号。
当然了，一边单引号，一边双引号是不被允许的。

// 显示所有数据库show databases;// 创建数据库create database 数据库名 [charset=utf8]; //语法create database my_first_databsee [charset=utf8];CREATE DATABASE `my_first_databsee ` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';// 查看创建数据库的语句show database 数据库名;//语法show database my_first_databsee ;// 删除数据库drop database 数据库名; drop database if exists `数据库名`;DROP DATABASE IF EXISTS `ihbs`;

if exists 判断是否存在，不存在也不报错

//使用数据库use 数据库的名字use my_first_database;// 查看当前使用的数据库select database(); # function

3. 表语法

3.1.创建表

create table 表名(列名1 列2类型 [完整性约束条件],列名2 列2类型 [完整性约束条件],……)ENGINE = myisam|innodb default charset = utf8|gbk|latin1;-- auto_increment表示自动增长-- not null 表示不能为空-- primary key 表示主键-- default 默认值-- enum 枚举create table 数据表名字 (字段类型约束[, 字段类型约束]);eg:create table zzzz(id int primary key not null auto_increment,name varchar(30) # 最后一个末尾不能加 ',' 逗号, 否则会报错);create table students(id int unsigned not null auto_increment primary key,name varchar(30),age tinyint unsigned default 0,high decimal(5,2),gender enum("男", "女", "中性", "保密") default "保密",cls_id int unsigned);ENGINE=InnoDB default charset=utf8

3.1.1.SQL DEFAULT 约束

DEFAULT 约束用于向列中插入默认值。

如果没有规定其他的值，那么会将默认值添加到所有的新记录。

ALTER TABLE 时的 SQL DEFAULT 约束当表已被创建时，如需在 “City” 列创建 DEFAULT 约束，请使用下面的 SQL：

ALTER TABLE PersonsALTER City SET DEFAULT 'SANDNES'

撤销 DEFAULT 约束如需撤销 DEFAULT 约束，请使用下面的 SQL：

ALTER TABLE PersonsALTER City DROP DEFAULT

3.2. 查看表结构

//查看该表的结构desc 表名 --> describe 表名show create table 表名;//查看表的创建信息show create table students;

3.3. 设置主键

主键(PRIMARY KEY)的完整称呼是“主键约束”，是 MySQL 中使用最为频繁的约束。一般情况下，为了便于 DBMS~~数据库管理系统(Database Management System)~~更快的查找到表中的记录，都会在表中设置一个主键。
使用主键应注意以下几点：
每个表只能定义一个主键。主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。这是唯一性原则。一个字段名只能在联合主键字段表中出现一次。联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。

在创建表时设置主键约束
在创建数据表时设置主键约束，既可以为表中的一个字段设置主键，也可以为表中多个字段设置联合主键。但是不论使用哪种方法，在一个表中主键只能有一个。下面分别讲解设置单字段主键和多字段联合主键的方法。设置单字段主键
在 CREATE TABLE 语句中，通过 PRIMARY KEY 关键字来指定主键。在创建表时设置联合主键所谓的联合主键，就是这个主键是由一张表中多个字段组成的。比如，设置学生选课数据表时，使用学生编号做主键还是用课程编号做主键呢？如果用学生编号做主键，那么一个学生就只能选择一门课程。如果用课程编号做主键，那么一门课程只能有一个学生来选。显然，这两种情况都是不符合实际情况的。实际上设计学生选课表，要限定的是一个学生只能选择同一课程一次。因此，学生编号和课程编号可以放在一起共同作为主键，这也就是联合主键了。

<字段名> <数据类型> PRIMARY KEY [默认值]单字段主键create table table2(stu int PRIMARY key,name VARCHAR(30));PRIMARY KEY [字段1，字段2，…,字段n]多字段主键create table table2(stu int ,name VARCHAR(30),primary key (stu,name));#假设表中没有主键stu,为了唯一确定，可以把stu、name联合起来作为主键

3.3.1.在修改表的时候添加主键约束

（前提是表单中没得主键约束）

#在修改数据表时添加主键约束的语法格式如下：ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);eg:#修改数据表 tb_emp2，将字段 id 设置为主键ALTER TABLE tb_emp2 ADD PRIMARY KEY(id)

3.3.2.删除主键约束

当一个表中不需要主键约束时，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。

删除主键约束的语法格式如下所示：ALTER TABLE <数据表名> DROP PRIMARY KEY;#删除 tb_emp2 表中的主键约束ALTER TABLE tb_emp2 DROP PRIMARY KEY;

3.4. 完整的表约束

主键：primary key

外键：foreign key

MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。

外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。

如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。

外键的使用条件

① 两个表必须是InnoDB表，MyISAM表暂时不支持外键

② 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；

③ 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。使两张表形成关联，外键只能引用外表中的列的值！可以使得两张表关联，保证数据的一致性和实现一些级联操作；

- 语法：constraint 外键别名 foreign key(属性1,属性2,…) references 表名(属性1，属性2) 依赖数据库中已存在表的主键，可以为空- // 删除表的外键约束- 语法：alter table 表名 foreign key 外键别名- - 非空：not null- 语法：属性名数据类型 not null- - 唯一性：unique- 语法：属性名数据类型 unique- - 自增：Auto_increment- 语法：属性名数据类型 auto_increment 一个表只能有一个字段使用auto_increment，且为主键一部分任何整数(默认从1开始自增)- - 默认值：default- 语法：属性名 default 默认值- - 提示: comment- 语法: comment ‘提示语’

完整的demo

create table if not exists test(id int PRIMARY key auto_increment COMMENT '主键，自增',name VARCHAR(30) UNIQUE COMMENT '唯一性约束',sex int DEFAULT 1 COMMENT '设置默认值',dsc VARCHAR(256) not null COMMENT '非空',forid int COMMENT '和table2表的stu进行外键约束',constraint forkey FOREIGN KEY(forid) references table2(stu) );

3.5. 修改表

// 修改表名：alter table 旧表名 RENAME [to] 新表名// 添加列alter table 表名 add 属性名属性类型 [完整性约束条件] [First | After 属性名]alter table 表名 add 属性名属性类型 after 某列名(指定添加到某列后)alter table 表名 add 属性名属性类型 first;(把新列加到最前面)// 删除列alter table 表名 drop 属性名// 修改列alter table 表名 modify 属性名属性类型;// 修改列名及列类型：alter table 表名 change 旧属性名新属性名新类型;// 修改字段的排列顺序alter table 表名 modify 属性1 数据类型 first | after 属性2// 修改表的存储引擎alter table 表名 engine = 存储引擎名注意字段改名后，完整性约束条件丢失// 以下修改后，约束条件还在alter table teacher change num t_id not null unique // 以下修改后，t_id约束条件丢失alter table teacher change num t_id结论：属性改名时，必须在语句中加上原来的完整性约束条件

MySQL----alter table modify | change的不同

modify、change都可以修改列的属性；一同的是modify只能修改表的数据类型、change比它要牛逼一点它可以在修改数据类型的同时也修改列名。

modify 的语法：

alter table table_name modify new_colum_defnition。

如：alter table user modify id bigint;

change的语法：

alter table table_name change old_colum_name new_column_definition。

如：alter table user change id userID int;

3.6. 删除表

// 删除没有被关联的表drop table 表名// 删除被其他表关联的表先删除外键约束： alter table 表名2 drop foreign key 外键别名再删除表：drop table 表名

4.数据–增删改查(curd)

4.1.MySQL 正则表达式

MySQL中使用 REGEXP 操作符来进行正则表达式匹配

实例
了解以上的正则需求后，我们就可以根据自己的需求来编写带有正则表达式的SQL语句。以下我们将列出几个小实例(表名：person_tbl )来加深我们的理解：

查找name字段中以’st’为开头的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以’ok’为结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含’mar’字符串的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

4.2. curd的解释:

创建（Create）更新（Update）读取（Retrieve）删除（Delete）

增加全列插入: 值的顺序与表中字段的顺序对应//insert [into] 表名 values(...)//主键字段主键列是自动增长，但是在全列插入时需要占位，通常使用0或者 default 或者 null 来占位，插入成功后以实际数据为准insert into students values(0,’郭靖‘,1,'蒙古','-1-2');-- 部分列插入：值的顺序与给出的列顺序对应// 如果有默认值,系统自动填默认值; 没有填 nullinsert into students(name,hometown,birthday) values('黄蓉','桃花岛','-3-2');insert into student(name,age) values("you",18);-- 多行插入: 一次性插入多行数据// 各个记录之间用逗号隔开 (), ()//全列多行插入：值的顺序与给出的列顺序对应insert into classes values(0,'python1'),(0,'python2');insert into students(name) values('杨康'),('杨过'),('小龙女');

删除 delete from ... where ... -- 物理删除-- delete from 表名 where 条件delete from students; -- 整个数据表中的所有数据全部删除delete from students where name="小李飞刀";update CLASS set FIELD CONSTRAIT where 哪些行-- 逻辑删除-- 用一个字段来表示这条信息是否已经不能再使用了-- 给students表添加一个is_delete字段 bit 类型alter table students add is_delete bit default 0;update students set is_delete=1 where id=6;

修改--update 表名 set 列1=值1,列2=值2... where 条件;update students set gender=1; -- 全部都改update students set gender=1 where name="小李飞刀"; --只要name是小李飞刀的全部的修改update students set gender=1 where id=3; -- 只要id为3的进行修改update students set age=22, gender=1 where id=3; -- 只要id为3的进行修改

查询基本使用 select 字段 from 表名 --> 查看哪些字段从哪个表-- 查询所有列-- select * from 表名;select * from students;---定条件查询select * from students where name="小李飞刀"; -- 查询 name为小李飞刀的所有信息select * from students where id>3; -- 查询 name为小李飞刀的所有信息-- 查询指定列-- select 列1,列2,... from 表名;select name,gender from students;-- 可以使用as为列或表指定别名-- select 字段[as 别名] , 字段[as 别名] from 数据表 where ....;elect name as 姓名,gender as 性别 from students;-- 字段的顺序select id as 序号, gender as 性别, name as 姓名 from students;

// 插入数据insert into 表名(属性名1,属性名2,...) values(值1，值2,...),[(值1,值2,...)...]// 将查询结果插入到表中insert into 表名1(属性列表1) select 属性列表2 from 表名 where 条件表达式// 更新数据update 表名 set 属性1=值1, 属性2=值2,.... where 表达式// 删除数据delete from 表名[ where 表达式]

5.视图

5.1.概述

视图简介:

视图是一个虚拟表，其内容由查询定义。同真实表一样，视图包含一系列带有名称的列和行数据，但视图并不是数据库真实存储的数据表。

视图是从一个、多个表或者视图中导出的表，包含一系列带有名称的数据列和若干条数据行。

视图并不同于数据表，它们的区别在于以下几点：

视图不是数据库中真实的表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的。存储在数据库中的查询操作 SQL 语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。视图的建立和删除只影响视图本身，不影响对应的基本表。

视图与表在本质上虽然不相同，但视图经过定义以后，结构形式和表一样，可以进行查询、修改、更新和删除等操作。同时，视图具有如下优点：

定制用户数据，聚焦特定的数据

在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息的视图，教师查看学生信息和课程信息表的视图。

简化数据操作

在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。

提高基表数据的安全性

视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。

共享所需数据

通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。

更改数据格式

通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。

重用 SQL 语句

视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。

注意：要区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

视图作用

操作简便增加数据安全性提高表的逻辑独立性

5.2 视图和表区别和联系

区别

视图时按照sql语句生成的一个虚拟的表视图不占用实际的物理空间，而表中的记录需要占用物理空间建立和删除视图只影响视图本身，不影响实际记录，而建立和删除表会影响实际记录

联系

视图是在基本表上建立的表，其字段和记录都来自基本表，其依赖基本表而存在一个视图可以对应一个基本表，也可以对应多个基本表视图是基本表的抽象，也可以对应多个基本表

5.3. MySQL创建视图（CREATE VIEW）

创建视图是指在已经存在的 MySQL数据库表上建立视图。视图可以建立在一张表中，也可以建立在多张表中

// 语法create [Algorithm={Undefined|Merge|Temptable}] View 视图名[(属性列表)]As select 语句[With [Cascaded | Local]] check option];Algorithm:Undefined：mysql默认算法Merge：将使用视图的语句与视图定义合并起来，使得视图定义某一部分取代语句对应部分TempTable：将视图结果存入临时表，然后临时表执行语句With：cascaded：更新视图时要满足所有相关视图和表条件(默认)Local：更新视图时需要满足视图本身的定义条件即可

<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

<SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

对于创建视图中的 SELECT 语句的指定存在以下限制：

用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。

SELECT 语句不能引用系统或用户变量。

SELECT 语句不能包含 FROM 子句中的子查询。

SELECT 语句不能引用预处理语句参数。

图定义中引用的表或视图必须存在。但是，创建完视图后，可以删除定义引用的表或视图。可使用 CHECK TABLE 语句检查视图定义是否存在这类问题。

视图定义中允许使用 ORDER BY 语句，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。

视图定义中不能引用 TEMPORARY 表（临时表），不能创建 TEMPORARY 视图。

WITH CHECK OPTION 的意思是，修改视图时，检查插入的数据是否符合 WHERE 设置的条件。

// 在单表上创建视图create view v_students_infoAs SELECT * from tb_students_info;// 在多表上创建视图create ALGORITHM=MERGE view v_appuser2As SELECT a.* from qappuser as a, qappfunction as bWITH LOCAL check option;// 查看视图DESC v_students_info;describe v_students_info;

5.3.1创建基于单表的视图

eg:

查看 test_db 数据库中的 tb_students_info 表的数据，如下所示。

在 tb_students_info 表上创建一个名为 view_students_info 的视图，输入的 SQL 语句和执行结果如下所示。

默认情况下，创建的视图和基本表的字段是一样的，也可以通过指定视图字段的名称来创建视图。

可以看到，view_students_info 和 v_students_info 两个视图中的字段名称不同，但是数据却相同。因此，在使用视图时，可能用户不需要了解基本表的结构，更接触不到实际表中的数据，从而保证了数据库的安全。

5.3.2创建基于多表的视图

MySQL 中也可以在两个以上的表中创建视图，使用 CREATE VIEW 语句创建。

在表 tb_student_info 和表 tb_departments 上创建视图 v_students_info，输入的 SQL 语句和执行结果如下所示。

通过这个视图可以很好地保护基本表中的数据。视图中(s_id,s_name,s_age,s_sex,s_height,s_date)对应表s_students_info中（id,name,age,height,login_date）字段，s_dept_name对应表tb_departments中dept_name字段。

5.4 查询视图

视图一经定义之后，就可以如同查询数据表一样，使用 SELECT 语句查询视图中的数据，语法和查询基础表的数据一样。

视图用于查询主要应用在以下几个方面：

使用视图重新格式化检索出的数据。使用视图简化复杂的表连接。使用视图过滤数据。

DESCRIBE 可以用来查看视图，语法如下：

DESCRIBE 视图名

通过 DESCRIBE 语句查看视图 v_students_info 的定义，输入的 SQL 语句和执行结果如下所示。

注意：DESCRIBE 一般情况下可以简写成 DESC，输入这个命令的执行结果和输入 DESCRIBE 是一样的。

5.5. 更新(修改)视图数据

// 创建测试视图CREATE View v_appuserAs SELECT * from tb_appuser where iAppuserId = 30// 更新视图记录// 通过视图更新时，只能更新权限范围内数据update v_appuser set iAppuserId =11说明：以上语句只更新iAppUserId=20的记录，而其他记录不更新// 不能更新的视图○ 视图中包含sum、count、max、min○ 视图中包含union、union all、distinct、group by、having○ 常量视图：create view t_view as select 'name' as name○ 视图中select中包含子查询：create view t_view7(name) as select (select name from qwork)○ 由不可更新视图导出的视图○ 创建视图时，Algorithm为Temptable类型视图对应表上存在没有设置默认值的列，而且改列没有包含在视图中

5.6. 删除视图

drop view if exists 视图名列表 [Restrict | cascade]

6. 触发器语法

什么是触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。

创建触发器

创建触发器的语法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmttrigger_name：触发器的名称tirgger_time：触发时机，为BEFORE或者AFTERtrigger_event：触发事件，为INSERT、DELETE或者UPDATEtb_name：表示建立触发器的表明，就是在哪张表上建立触发器trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句所以可以说MySQL创建以下六种触发器：BEFORE INSERT,BEFORE DELETE,BEFORE UPDATEAFTER INSERT,AFTER DELETE,AFTER UPDATE

其中，触发器名参数指要创建的触发器的名字

BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后

FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件ON 表名 FOR EACH ROWBEGIN执行语句列表END

其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开

tips：一般情况下，mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突为解决此问题可用DELIMITER，如：DELIMITER ||，可以将结束符号变成||当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;

mysql> DELIMITER ||mysql> CREATE TRIGGER demo BEFORE DELETE-> ON users FOR EACH ROW-> BEGIN-> INSERT INTO logs VALUES(NOW());-> INSERT INTO logs VALUES(NOW());-> END-> ||Query OK, 0 rows affected (0.06 sec)mysql> DELIMITER ;

上面的语句中，开头将结束符号定义为||，中间定义一个触发器，一旦有满足条件的删除操作

就会执行BEGIN和END中的语句，接着使用||结束

最后使用DELIMITER ; 将结束符号还原

tigger_event:

load data语句是将文件的内容插入到表中，相当于是insert语句，而replace语句在一般的情况下和insert差不多，但是如果表中存在primary 或者unique索引的时候，如果插入的数据和原来的primary key或者unique相同的时候，会删除原来的数据，然后增加一条新的数据，所以有的时候执行一条replace语句相当于执行了一条delete和insert语句。

触发器可以是一条SQL语句，也可以是多条SQL代码块，那如何创建呢？

DELIMITER $ #将语句的分隔符改为$BEGINsql1;sql2;...sqlnEND $DELIMITER ; #将语句的分隔符改回原来的分号";"

在BEGIN…END语句中也可以定义变量，但是只能在BEGIN…END内部使用：

DECLARE var_name var_type [DEFAULT value] #定义变量，可指定默认值

SET var_name = value #给变量赋值

NEW和OLD的使用:

eg:

用户users表

CREATE TABLE `users` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,`add_time` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `name` (`name`(250)) USING BTREE) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1;

日志logs表：

CREATE TABLE `logs` (`Id` int(11) NOT NULL AUTO_INCREMENT,`log` varchar(255) DEFAULT NULL COMMENT '日志说明',PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日志表';

需求是：当在users中插入一条数据，就会在logs中生成一条日志信息。

创建触发器：

DELIMITER $CREATE TRIGGER user_log AFTER INSERT ON users FOR EACH ROWBEGINDECLARE s1 VARCHAR(40)character set utf8;DECLARE s2 VARCHAR(20) character set utf8;#后面发现中文字符编码出现乱码，这里设置字符集SET s2 = " is created";SET s1 = CONCAT(NEW.name,s2);#函数CONCAT可以将字符串连接INSERT INTO logs(log) values(s1);END $DELIMITER ;

这里我用的navicat:

查看触发器

SHOW TRIGGERS语句查看触发器信息

navicat编译器：

#DELIMITER $CREATE TRIGGER user_log AFTER INSERT ON users FOR EACH ROWBEGINDECLARE s1 VARCHAR(40)character set utf8;DECLARE s2 VARCHAR(20) character set utf8;SET s2 = " is created";SET s1 = CONCAT(NEW.name,s2);#函数CONCAT可以将字符串连接INSERT INTO logs(log) values(s1);END #$#DELIMITER ;

在triggers表中查看触发器信息

SELECT * FROM information_schema.triggers;

结果显示了所有触发器的详细信息，同时，该方法可以查询制定触发器的详细信息

SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='user_log';

所有触发器信息都存储在information_schema数据库下的triggers表中

可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER_NAME字段指定查询

回到上面,我们创建好了触发器,继续在users中插入数据并查看数据：

insert into users(name,add_time) values('周伯通',now());

我们再来查看一下logs表吧！

通过上面的例子，可以看到只需要在users中插入用户的信息，日志会自动记录到logs表中，这也许就是触发器给我带来的便捷吧

7. 查询数据

7.1. 基本查询语句

select 属性列表from 表名和视图列表[where 条件表达式][Group by 属性1 [having 条件表达式2] ][Order by 属性2 [ASC|DESC]]带like查询[Not] like带"%"通配符，多个字符带"_"通配符，一个字符带in关键字查询[Not] in (元素1，元素2，...)带between and 查询[Not] between 值1 and 值2空值查询is [not] null带and、or查询条件带distinct查询带比较条件：=、<、<=、>、>=、!=、<>、!>、!<使用limit限制查询结果数目(从0开始)不指定初始位置，只指定显示N个select * from employ limit 2;指定初始位置select * from employ limit 0,2;

7.2. 连接查询(从多个数据表中读取数据)

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

eg:#创建tcount_tb1CREATE TABLE tcount_tb1 (CodingDict_author char(40),CodingDict_count int ) ENGINE=InnoDB DEFAULT CHARSET=utf8;#插入数据INSERT INTO tcount_tb1 values('mahran',20),('mahnaz',NULL),('Jen',NULL),('Gill',20),('John Poul',1),('Sanjay',1);#创建CodingDict_tb1create table CodingDict_tb1(CodingDict_id int,CodingDict_title char(30),CodigDict_author char(40),submission_date char(60));#插入数据insert into CodingDict_tb1 values(1,'Learn PHP','John Poul','-05-24'),(2,'Learn Mysql','Abdul 5','-05-24 '),(1,'Learn Tutorial','Sanjay','-05-06');

使用MySQL的 INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样) 来连接以上两张表来读取CodingDict_tbl表中所有CodingDict_author字段在tcount_tbl表对应的CodingDict_count字段值：

SELECT a.CodingDict_id, a.CodingDict_author, b.CodingDict_count FROM CodingDict_tb2 a INNER JOIN tcount_tb2 b ON a.CodingDict_author = b.CodingDict_author;等同：SELECT a.CodingDict_id, a.CodigDict_author, b.CodingDict_count FROM codingDict_tb2 a, tcount_tb2 b WHERE a.CodigDict_author = b.CodingDict_author;

MySQL LEFT JOIN

MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

以 CodingDict_tbl 为左表， tcount_tbl 为右表，理解MySQL LEFT JOIN

SELECT a.CodingDict_id, a.CodigDict_author, b.CodingDict_count FROM CodingDict_tb2 a LEFT JOIN tcount_tb2 b ON a.CodigDict_author = b.CodingDict_author;

使用了LEFT JOIN，该语句会读取左边的数据表CodingDict_tbl的所有选取的字段数据，即便在右侧表tcount_tbl中没有对应的CodingDict_author字段值。

MySQL RIGHT JOIN

MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。

以 tcount_tbl 为左表，CodingDict _tbl 为右表，理解MySQL RIGHT JOIN

SELECT b.CodingDict_id, b.CodingDict_author, a.CodingDict_count FROM tcount_tbl a RIGHT JOIN CodingDict_tbl b ON a.CodingDict_author = b.CodingDict_author;

使用了 RIGHT JOIN，该语句会读取右边的数据表 CodingDict_tbl 的所有选取的字段数据，即便在左侧表tcount_tbl中没有对应的CodingDict_author字段值。

7.3. 子查询

将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果可以为外层查询语句提供查询条件

带[Not] in关键字的子查询

select * from employ where id in (select d_id from department)

带比较运算符子查询

=、<、<=、>、>=、!=、<>、!>、!<select * from employ where id >= (select d_id from department)

带[Not] exists关键字查询

使用exists关键字，内层查询语句不返回查询的记录，而是返回一个真假值。如果内层查询语查询到满足条件的记录，就返回一个真值，否则返回一个假值。只有当返回的值为真时，外层查询语句才进行查询并获取记录select * from employ where exists (select d_name from department where d_id=1001)

SQL中EXISTS的用法

EXISTS与IN的使用效率的问题，通常情况下采用 exists要比in效率高，因为IN不走索引，但要看实际情况具体使用：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

带any关键字子查询

满足其中任一条件。只要满足内层查询语句返回结果中的任何一个，就可以通过该条件执行外层查询语句select * from employ where id >= any(select d_id from department)

带all关键字子查询

满足所有条件。只有满足内层查询语句返回所有结果，才可以执行外层查询语句

select * from employ where id >= all(select d_id from department)

合并查询结果

将多个select语句查询结果合并到一起union：将所有查询结果合并到一起，然后去除掉相同记录union all：简单合并到一起

表和字段取别名

表名或者字段名 [AS] 别名

7.4. 分组查询group by

分组查询：带有GROUP BY的查询，也叫组合查询

特征

GROUP BY是SELECT语句的从句，用来指定查询分组条件，主要用来对查询的结果进行分组，相同组合的分组条件在结果集中只显示一行记录。和其配合的聚合函数有：COUNT()、SUM、MAX()、MIN()等

count()：统计记录条数

sum()：计算字段值总和

avg()：计算字段值平均值

max(): 最大值

min(): 最小值GROUP BY在做组合查询的时候，会对NULL的分组单独形成一行可以在SELECT … GROUP BY 分组后筛选数据。筛选的关键字是HAVING。HAVING的作用和WHERE类似。都是用来过滤查询的中间记录。但是，HAVING从句指定的每个列规范必须出现在一个聚合函数内，或者出现在GROUP BY从句命名的列中。与WHERE不同的是：WHERE是在分组前（查询后）筛选数据；HAVING是在分组后筛选数据。

例如：

SELECTSUBSTR(A.HYLB_DM,1,2),COUNT(*),SUM(A.ZCZB)FROM DJ_ZT AGROUP BY SUBSTR(A.HYLB_DM,1,2)HAVING MAX(YEAR(A.CJRQ))<>;1234567带有WHERE和HAVING的SELECT语句执行过程：§ 执行WHERE筛选数据§ 执行GROUP BY分组形成中间分组表§ 执行WITH ROLLUP/CUBE生成统计分析数据记录并加入中间分组表§ 执行HAVING筛选中间分组表§ 执行ORDER BY排序

eg:

#创建employee_tbl表CREATE TABLE `employee_tbl` (`id` int(11) NOT NULL,`name` char(10) NOT NULL DEFAULT '',`date` datetime NOT NULL,`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;#插入数据INSERT INTO `employee_tbl` (id,name,date,singin) VALUES ('1', '小明', '-04-22 15:25:33', '1'), ('2', '小王', '-04-20 15:25:47', '3'), ('3', '小丽', '-04-19 15:26:02', '2'), ('4', '小王', '-04-07 15:26:14', '4'), ('5', '小明', '-04-11 15:26:40', '4'), ('6', '小明', '-04-04 15:26:54', '2');

使用 GROUP BY 语句将数据表按名字进行分组，并统计每个人有多少条记录

SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;

with rollup 可以实现在分组统计数据基础上再进行相同的统计

将以上的数据表按名字进行分组，再统计每个人登录的次数

SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;

其中记录 NULL 表示所有人的登录次数。
我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）#如果名字为空我们使用总数代替SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;

8. 索引

8.1介绍

什么是索引？

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

为什么要有索引呢？

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能
非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

索引原理

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段…这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

8.2. MySQL的索引分类

索引分类1.普通索引index :加速查找2.唯一索引主键索引：primary key ：加速查找+约束（不为空且唯一）唯一索引：unique：加速查找+约束（唯一）3.联合索引-primary key(id,name):联合主键索引-unique(id,name):联合唯一索引-index(id,name):联合普通索引4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。5.空间索引spatial :了解就好，几乎不用

举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表有下列字段：会员编号 INT会员姓名 VARCHAR(10)会员身份证号码 VARCHAR(18)会员电话 VARCHAR(10)会员住址 VARCHAR(50)会员备注信息 TEXT那么这个会员编号，作为主键，使用 PRIMARY会员姓名如果要建索引的话，那么就是普通的 INDEX会员身份证号码如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）#除此之外还有全文索引，即FULLTEXT会员备注信息，如果需要建索引的话，可以选择全文搜索。用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。#其他的如空间索引SPATIAL，了解即可，几乎不用各个索引的应用场景8.2. 普通索引可以创建在任何数据类型上，不附件任何限制条件

8.3.索引的两大类型hash与btree

#我们可以在创建上述索引的时候，为其指定索引类型，分两类hash类型的索引：查询单条快，范围查询慢btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）#不同的存储引擎支持的索引类型也不一样InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

8.4.创建/删除索引的语法

#方法一：创建表时CREATE TABLE 表名 (字段名1 数据类型 [完整性约束条件…],字段名2 数据类型 [完整性约束条件…],[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY[索引名] (字段名[(长度)] [ASC |DESC]) );#方法二：CREATE在已存在的表上创建索引CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ;#方法三：ALTER TABLE在已存在的表上创建索引ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX索引名 (字段名[(长度)] [ASC |DESC]) ;#删除索引：DROP INDEX 索引名 ON 表名字;善用帮助文档help createhelp create index==================1.创建索引-在创建表时就创建（需要注意的几点）create table s1(id int ,#可以在这加primary key#id int index #不可以这样加索引，因为index只是索引，没有约束一说，#不能像主键，还有唯一约束一样，在定义字段的时候加索引name char(20),age int,email varchar(30)#primary key(id) #也可以在这加index(id) #可以这样加);-在创建表后在创建create index name on s1(name); #添加普通索引create unique age on s1(age);添加唯一索引alter table s1 add primary key(id); #添加住建索引，也就是给id字段增加一个主键约束create index name on s1(id,name); #添加普通联合索引2.删除索引drop index id on s1;drop index name on s1; #删除普通索引drop index age on s1; #删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)

帮助查看

8.5.测试索引

准备

#1. 准备表create table s1(id int,name varchar(20),gender char(6),email varchar(50));#2. 创建存储过程，实现批量插入记录delimiter $$ #声明存储过程的结束符号为$$create procedure auto_insert1()BEGINdeclare i int default 1;while(i<3000000)doinsert into s1 values(i,concat('egon',i),'male',concat('egon',i,'@oldboy'));set i=i+1;end while;END$$ #$$结束delimiter ; #重新声明分号为结束符号#3. 查看存储过程show create procedure auto_insert1\G #4. 调用存储过程call auto_insert1();

在没有索引的前提下测试查询速度

#无索引：从头到尾扫描一遍，所以查询速度很慢mysql> select * from s1 where id=333;+------+---------+--------+----------------+| id | name | gender | email|+------+---------+--------+----------------+| 333 | egon333 | male | 333@ || 333 | egon333 | f| alex333@oldboy || 333 | egon333 | f| alex333@oldboy |+------+---------+--------+----------------+rows in set (0.32 sec)mysql> select * from s1 where email='egon333@oldboy';....... rows in set (0.36 sec)

加上索引

#1. 一定是为搜索条件的字段创建索引，比如select * from t1 where age > 5;就需要为age加上索引#2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，插入删除更新都很慢，只有查询快比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。建完以后，再查询就会很快了#3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

8.6.正确使用索引

覆盖索引

#分析mysql> select * from s1 where id=123;该sql命中了索引，但未覆盖索引。利用id=123到索引的数据结构中定位到该id在硬盘中的位置，或者说再数据表中的位置。但是我们select的字段为*，除了id以外还需要其他字段，这就意味着，我们通过索引结构取到id还不够，还需要利用该id再去找到该id所在行的其他字段值，这是需要时间的，很明显，如果我们只select id，就减去了这份苦恼，如下mysql> select id from s1 where id=123;这条就是覆盖索引了，命中索引，且从索引的数据结构直接就取到了id在硬盘的地址，速度很快

联合索引索引合并

#索引合并：把多个单列索引合并使用#分析：组合索引能做到的事情，我们都可以用索引合并去解决，比如mysql> create index ne on s1(name,email);#组合索引我们完全可以单独为name和email创建索引组合索引可以命中：mysql> select * from s1 where name='egon' ;mysql> select * from s1 where name='egon' and email='adf';索引合并可以命中：mysql> select * from s1 where name='egon' ;mysql> select * from s1 where email='adf';mysql> select * from s1 where name='egon' and email='adf';乍一看好像索引合并更好了：可以命中更多的情况，但其实要分情况去看，如果是name='egon' and email='adf',那么组合索引的效率要高于索引合并，如果是单条件查，那么还是用索引合并比较合理

若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下原则

#1.最左前缀匹配原则，非常重要的原则，mysql> create index ix_name_email on s1(name,email,)#最左前缀匹配：必须按照从左到右的顺序匹配mysql> select * from s1 where name='egon'; #可以mysql> select * from s1 where name='egon' and email='asdf'; #可以mysql> select * from s1 where email='alex@'; #不可以mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。#2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式#3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录#4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’-05-29’);

最左前缀示范

mysql> select * from s1 where id>3 and name='egon' and email='alex333@' and gender='male';Empty set (0.39 sec)mysql> create index idx on s1(id,name,email,gender); #未遵循最左前缀Query OK, 0 rows affected (15.27 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from s1 where id>3 and name='egon' and email='alex333@' and gender='male';Empty set (0.43 sec)mysql> drop index idx on s1;Query OK, 0 rows affected (0.16 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> create index idx on s1(name,email,gender,id); #遵循最左前缀Query OK, 0 rows affected (15.97 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from s1 where id>3 and name='egon' and email='alex333@' and gender='male';Empty set (0.03 sec)

索引无法命中的情况需要注意：

- like '%xx'select * from tb1 where email like '%cn';- 使用函数select * from tb1 where reverse(email) = 'wupeiqi';- orselect * from tb1 where nid = 1 or name = 'seven@';特别的：当or条件中有未建立索引的列才失效，以下会走索引select * from tb1 where nid = 1 or name = 'seven';select * from tb1 where nid = 1 or name = 'seven@' and email = 'alex'- 类型不一致如果列是字符串类型，传入条件是必须用引号引起来，不然...select * from tb1 where email = 999;普通索引的不等于不会走索引- !=select * from tb1 where email != 'alex'特别的：如果是主键，则还是会走索引select * from tb1 where nid != 123- >select * from tb1 where email > 'alex'特别的：如果是主键或索引是整数类型，则还是会走索引select * from tb1 where nid > 123select * from tb1 where num > 123#排序条件为索引，则select字段必须也是索引字段，否则无法命中- order byselect name from s1 order by email desc;当根据索引排序时候，select查询的字段如果不是索引，则不走索引select email from s1 order by email desc;特别的：如果对主键排序，则还是走索引：select * from tb1 order by nid desc;- 组合索引最左前缀如果组合索引为：(name,email)name and email -- 使用索引name -- 使用索引email-- 不使用索引- count(1)或count(列)代替count(*)在mysql中没有差别了- create index xxxx on tb(title(19)) #text类型，必须制定长度- 避免使用select *- count(1)或count(列) 代替 count(*)- 创建表时尽量时 char 代替 varchar- 表的字段顺序固定长度的字段优先- 组合索引代替多个单列索引（经常使用多个条件查询时）- 尽量使用短索引- 使用连接（JOIN）来代替子查询(Sub-Queries)- 连表时注意条件类型需一致- 索引散列值（重复少）不适合建索引，例：性别不适合

8.7.慢查询优化的基本步骤

先运行看看是否真的很慢，注意设置SQL_NO_CACHEwhere条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）order by limit 形式的sql语句让排序的表优先查了解业务方使用场景加索引时参照建索引的几大原则观察结果，不符合预期继续从0分析

9. 账号管理

9.1. 新建用户

// 为Entry数据库创建一个remote_entry账号，密码为password，拥有select、insert、delete、create、drop权限mysql> grant select,insert,update,delete,create,drop on entry.* to 'remote_entry'@'%' identified by 'password';// 不用刷新权限，就可以登录；如果通过insert、update对mysql.user进行操作，则需要进行刷新操作// flush privileges;

9.2. 删除用户

mysql> DROP USER 'remote_entry'@'%';1

9.3. 查询数据库的用户

mysql> SELECT * from mysql.user1

9.4. 修改密码

mysql>use mysql; #选择数据库 Database changed mysql> UPDATE user SET password=PASSWORD("新密码") WHERE user='你的用户名'; mysql> FLUSH PRIVILEGES; #更新// 拥有修改mysql表权限的用户(如root用户)修改自己或者别人密码，并刷新权限mysql> update mysql.user set password=Password("password") where user='remote_entry' and host='localhost';mysql> flush privileges;// 修改别人密码mysql> set Password for 'remote_entry'@'%' = Password("password") ;

9.5. 修改用户host：是否可以远程访问

// 修改用户只能本地访问，如果要任何IP都可以访问，则设置host为%；需要刷新权限mysql> update mysql.user set host = 'localhost' where user='mysql';#将用户名为mysql的改为localhostmysql> flush privileges;

9.6. 忘记root密码，找回root密码(mysql忘记密码)

// 1 打开命令行界面，使用 --skip-grant-tables方式启动服务，在命令行中执行，程序会停留在界面上mysqld --skip-grant-tables// 2 打开新命令行界面，使用root无密码登陆，并执行密码修改语句，并刷新权限mysql> mysql -urootmysql> update mysql.user set password=Password("password2") where user = "root";mysql> flush privileges;

10. 权限管理

10.1. 授权

// with grant option(未测试，是否可以给其他用户授权)表示对A用户进行的授权，A可以授予给其他用户，当收回对A的授权时，A授予给其他用户的权限不会被级联收回mysql> grant select,update on *.* to 'entry'@'localhost' identified by 'entry' with grant option;// 为一个用户赋予全部的权限mysql> grant all privileges on ab.* to hwalk1@'%' identified by 'hwalk1';

10.2. 收回权限

// 收回所有用户或者某个特定数据库的权限 mysql> Revoke all privileges on *.* from 'entry'@'localhost';

#10.3. 查看权限

// 获取用户在所有数据库上的权限mysql> select * from mysql.user \G;// 查看用户所有权限，在所有数据库和特定数据库上的权限mysql> show grants for 'root'@'localhost';

11.MySQL 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

11.1事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务ROLLBACK 事务回滚COMMIT 事务确认

直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交SET AUTOCOMMIT=1 开启自动提交

mysql> use RUNOOB;Database changedmysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb; # 创建数据表Query OK, 0 rows affected (0.04 sec)mysql> select * from runoob_transaction_test;Empty set (0.01 sec)mysql> begin; # 开始事务Query OK, 0 rows affected (0.00 sec)mysql> insert into runoob_transaction_test value(5);Query OK, 1 rows affected (0.01 sec)mysql> insert into runoob_transaction_test value(6);Query OK, 1 rows affected (0.00 sec)mysql> commit; # 提交事务Query OK, 0 rows affected (0.01 sec)mysql> select * from runoob_transaction_test;+------+| id |+------+| 5 || 6 |+------+2 rows in set (0.01 sec)mysql> begin; # 开始事务Query OK, 0 rows affected (0.00 sec)mysql> insert into runoob_transaction_test values(7);Query OK, 1 rows affected (0.00 sec)mysql> rollback; # 回滚Query OK, 0 rows affected (0.00 sec)mysql> select * from runoob_transaction_test; # 因为回滚所以数据没有插入+------+| id |+------+| 5 || 6 |+------+2 rows in set (0.01 sec)mysql>