MySQL，Oracle

MySQL.mdSQL，一般发音为 sequel，SQL 的全称 Structured QueryLanguage)，SQL 用来和数据库打交道，完成和数据库的通信，SQL 是一套标准。但是每一个数据库都有自己的特性别的数据库没有,当使用这个数据库特性相关的功能,这时 SQL 语句可能就不是标准了.(90%以上的 SQL 都是通用的)

数据库，通常是一个或一组文件，保存了一些符合特定规格的数据,数据库对应的英语单词是 DataBase,简称:DB,数据库软件称为数据库管理系统（DBMS），全称为 DataBase Management System，如：Oracle、SQLServer、MySql、Sybase、informix、DB2、interbase、PostgreSql 。

MySQL 最初是由“MySQL AB”公司开发的一套关系型数据库管理系统（RDBMS-RelationalDatabase Mangerment System）。MySQL 不仅是最流行的开源数据库，而且是业界成长最快的数据库，每天有超过 7 万次的下载量，其应用范围从大型企业到专有的嵌入应用系统。MySQL AB 是由两个瑞典人和一个芬兰人：David Axmark、Allan Larsson 和 Michael “Monty” Widenius 在瑞典创办的。在 2008 年初，Sun Microsystems 收购了 MySQL AB 公司。在 2009 年，Oracle 收购了 Sun 公司，使 MySQL 并入 Oracle 的数据库产品线。

最新版 MYSQL 下载的是压缩包，详细安装步骤见博客：https://www.cnblogs.com/puhongjun/p/10076039.htmlhttps://www.cnblogs.com/2020javamianshibaodian/p/mysql8020anzhuangjiaocheng.html一下是老版本的打开下载的 mysql 安装文件 mysql-essential-5.0.22-win32.msi，双击运行，出现如下界面

按“Next”继续

选择安装类型，有“Typical（默认）”、“Complete（完全）”、“Custom（用户自定义）”三个选项，我们选择“Custom”，有更多的选项，也方便熟悉安装过程

上一步选择了 Custom 安装，这里将设定 MySQL 的组件包和安装路径，设定好之后，单击 Next 继续安装。

现在软件安装完成了，出现上面的界面，将 “Configure the Mysql Server now”前面的勾打上，点“Finish”结束软件的安装并启动 mysql 配置向导。mysql 配置向导启动界面，按“Next”继续。

选择配置方式，“Detailed Configuration（手动精确配置）”、“StandardConfiguration（标准配置）”，我们选择“Detailed Configuration”，方便熟悉配置过程。

选择服务器类型，“Developer Machine（开发测试类，mysql 占用很少资源）”、“Server Machine（服务器类型，mysql 占用较多资源）”、“Dedicated MySQL ServerMachine（专门的数据库服务器，mysql 占用所有可用资源）”，大家根据自己的类型选择了，一般选“ServerMachine”，不会太少，也不会占满。

选择 mysql 数据库的大致用途，“MultifunctionalDatabase（通用多功能型，能很好的支持 InnoDB 与 MyISAM 存储引擎）”、“TransactionalDatabase Only（服务器类型，专注于事务处理，一般）”、“Non-Transactional DatabaseOnly（非事务处理型，较简单，主要做一些监控、记数用，对 MyISAM 数据类型的支持仅限于 non-transactional），随自己的用途而选择了，我这里选择“MultifunctionalDatabase”， 按“Next”继续。

对 InnoDB Tablespace 进行配置，就是为 InnoDB 数据库文件选择一个存储空间，如果修改了，要记住位置，重装的时候要选择一样的地方，否则可能会造成数据库损坏，当然，对数据库做个备份就没问题了，这里不详述。我这里没有修改，使用用默认位置，直接按“Next”继续。

选择您的网站的一般 mysql 访问量，同时连接的数目，“DecisionSupport(DSS)/OLAP（20 个左右）”、“Online TransactionProcessing(OLTP)（500 个左右）”、“ManualSetting（手动设置，自己输一个数）”，我这里选“DecisionSupport(DSS)/OLAP)”，按“Next”继续

是否启用 TCP/IP 连接，设定端口，如果不启用，就只能在自己的机器上访问 mysql 数据库了，我这里启用，把前面的勾打上，PortNumber：3306，在这个页面上，您还可以选择“启用标准模式”（Enable StrictMode），按“Next”继续。

这个比较重要，就是对 mysql 默认数据库语言编码进行设置，第一个是西文编码，我们要设置的是 utf8 编码，按“Next”继续。

选择是否将 mysql 安装为 windows 服务，还可以指定 ServiceName（服务标识名称），是否将 mysql 的 bin 目录加入到 WindowsPATH（加入后，就可以直接使用 bin 下的文件，而不用指出目录名，比如连接，“mysql.exe-uusername-ppassword;”就可以了，不用指出 mysql.exe 的完整地址，很方便），我这里全部打上了勾，ServiceName 不变。按“Next”继续。

设置完毕，按“Next”继续。

确认设置无误，如果有误，按“Back”返回检查。按“Execute”使设置生效。

设置完毕，按“Finish”结束 mysql 的安装与配置 可以通过服务管理器管理 MYSQL 的服务。通过命令调用服务管理器:services.msc停止 MYSQL 的服务。

启动 MYSQL 的服务。

也可以在 DOS 中直接通过命令行的形式进行控制。停止 MYSQL 的服务。

启动 MYSQL 的服务。

数据查询语言(DQL-Data Query Language)代表关键字:select数据操纵语言(DML-Data Manipulation Language)代表关键字:insert,delete,update数据定义语言(DDL-Data Definition Language)代表关键字:create ,drop,alter,事务控制语言(TCL-Transactional Control Language)代表关键字:commit ,rollback;数据控制语言(DCL-Data Control Language)代表关键字:grant,revoke.

mysql 的启动和关闭在 cmd 命令行# 1.Windows下# 启动服务mysqld —console　　# 或　　net start mysql　　

关闭服务mysqladmin -uroot shudown　　或　　net stop mysql　　

2.Linux下启动服务service mysql start　　　

关闭服务service mysql stop　　

重启服务service restart stop— 查看mysql版本mysql —versionmysql -V—创建数据库create database 数据库名称;— 选择数据库use 数据库名称;— 查询当前使用的数据库select database();— 如果想要终止一条正在编写的语句，可键入\c。— 退出mysql\q (ctrl +c )

— 查看和指定现有数据库show databses;— 查看当前库中的表show table;— 查看其他库的表show tables from exam;— 查看表结构desc

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。数值类型：

注意：int(5) 和 int(10)的区别并不是最大长度的意思，而是在存储时以 0 填充的位数，当然存的超过长度也是可以的，比如 int(5)也可以存入 12345678 的日期和时间类型

字符串类型

在存储上， varchar 类型需要 2 个字节的额外空间来跟踪存储字符串的长度，这样 varchar(1) 实际占用的是 3 个字节。char(10)存数据存的是 1000000000，而 varchar(10)存数据存的是 1编码格式：默认是 latin1，打开 mysql 安装目录下的 myini.tet；找到两个 default-character-set,将其改为 utf-8 的字符集

13.1.1 创建表

show variables like ‘%char%’;

建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识

create table t_student( student_id int(10), student_name varchar(20), sex char(2), birthday date, email varchar(30), class_id int(3))

insert into t_student(student_id,student_name,sex,birthday,email,class_id) values(1001,’zhangsan’,’m’,’1998-01-01’,’[email protected]’,10)13.1.2：截断表删除表数据，保留表结构，数据无法恢复truncate table 表名

采用 alter table 来增加/删除/修改表结构，不影响表中的数据13.2.1、添加字段如：需求发生改变，需要向 t_student 中加入联系电话字段，字段名称为：contatct_tel 类型为 varchar(40)alter table t_student add contact_tel varchar(40);13.2.2、修改字段如：student_name 无法满足需求，长度需要更改为 100 alter table t_student modify student_name varchar(100) ;如 sex 字段名称感觉不好，想用 gender 那么就需要更改列的名称13.2.3、删除字段如：删除联系电话字段alter table t_student drop contact_tel;

13.3.1、insert添加、修改和删出都属于 DML，主要包含的语句：insert、update、delete

Insert into 表名(字段，。。。。) values(值,………..) |

insert into emp values(9999,’zhangsan’,’MANAGER’, null, null,3000, 500, 10);不建议使用此种方式，因为当数据库表中的字段位置发生改变的时候会影响到 insert 语句

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values(9999,’zhangsan’,’MANAGER’, null, null,3000, 500, 10);出现了主键重复的错误，主键表示了记录的唯一性，不能重复如何插入日期：第一种方法，插入的日期格式和显示的日期格式一致insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9997,’zhangsan’,’MANAGER’, null, ‘1981-06-12’,3000, 500, 10);第二种方法，采用 str_to_dateinsert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) valu es(9996,’zhangsan’,’MANAGER’,null,str_to_date(‘1981-06-12’,’%Y-%m-%d’),3000, 500, 10);）insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9995,’zhangsan’,’MANAGER’,null,now() ,3000, 500, 10);

| create table emp_bak as select empno,ename,sal from emp;以上方式，会自动创建表，将符合查询条件的数据自动复制到创建的表中

insert into emp_bak select * from emp where sal=3000;13.3.2、update可以修改数据，可以根据条件修改数据

| update 表名 set 字段名称 1=需要修改的值 1, 字段名称 2=需要修改的值 2 where …….

| update emp set sal=sal+sal*0.1 where job=’MANAGER’;13.3.3、delete可以删除数据，可以根据条件删除数据

| Delete from 表名 where 。。。。。

| delete from emp where comm=500;

| delete from emp where comm is null;

非空约束，针对某个字段设置其值不为空，如：学生的姓名不能为空create table t_student( student_id int(10), student_name varchar(20) not null,}以上错误为加入的学生姓名为空。13.4.2、唯一约束，unique唯一性约束，它可以使某个字段的值不能重复，如：email 不能重复：create table t_student( student_id int(10), student_name varchar(20) not null, email varchar(30) unique,}以上插入了重复的 email，所以出现了“违反唯一约束错误”，所以 unique 起作用了同样可以为唯一约束起个约束名

select from table_constraints where table_name=’t_student’;关于约束名称可以到 table_constraints 中查询以上约束的名称我们也可以自定义。drop table if exists t_student;create table t_student( student_id int(10), student_name varchar(20) not null, sex char(2) default ‘m’, birthday date, email varchar(30) , classes_id int(3) , constraint email_unique unique(email))13.4.3、主键约束，primary key每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的drop table if exists t_student;create table t_student() student_id int(10) primary key, \列级约束*/ student_name varchar(20) not null, sex char(2) default ‘m’, birthday date, email varchar(30) , classes_id int(3))

insert into t_student( student_id, student_name , sex, birthday, email, classes_id) values ( 1001, ‘zhangsan’, ‘m’, ‘1988-01-01’, ‘[email protected]’, 10)向以上表中加入学号为 1001 的两条记录，出现如下错误，因为加入了主键约束

我们也可以通过表级约束为约束起个名称：drop table if exists t_student; create table t_student( student_id int(10), student_name varchar(20) not null, sex char(2) default ‘m’, birthday date, email varchar(30) , classes_id int(3), CONSTRAINT p_id PRIMARY key (student_id) )insert into t_student(student_id, student_name , sex, birthday, email, classes_id) values (1001,’zhangsan’,’m’, ‘1988-01-01’, ‘qqq\@163.com’, 10)外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键，如：emp 中的 deptno 值必须来源于 dept 表中的 deptno 字段值。建立学生和班级表之间的连接首先建立班级表 t_classes| drop table if exists t_classes;create table t_classes( classes_id int(3), classes_name varchar(40), constraint pk_classes_id primary key(classes_id) ) |在 t_student 中加入外键约束| drop table if exists t_student; create table t_student( student_id int(10), student_name varchar(20), sex char(2), birthday date, email varchar(30), classes_id int(3), constraint student_id_pk primary key(student_id), constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id) ) |向 t_student 中加入数据| insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, ‘zhangsan’, ‘m’, ‘1988-01-01’, ‘qqq\@163.com’, 10) |

出现错误，因为在班级表中不存在班级编号为 10 班级，外键约束起到了作用存在外键的表就是子表，参照的表就是父表，所以存在一个父子关系，也就是主从关系，主表就是班级表，从表就是学生表以上成功的插入了学生信息，当时 classes_id 没有值，这样会影响参照完整性，所以我们建议将外键字段设置为非空drop table if exists t_student; create table t_student( student_id int(10), student_name varchar(20), sex char(2), birthday date, email varchar(30), classes_id int (3) not null, constraint student_id_pk primary key(student_id), constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id) ) insert into t_student(student_id, student_name, sex, birthday, email, cla sses_id) values(1001, ‘zhangsan’, ‘m’, ‘1988-01-01’, ‘qqq\@163.com’, null);再次插入班级编号为 null 的数据添加数据到班级表，添加数据到学生表，删除班级数据，将会出现如下错误：insert into t_classes (classes_id,classes_name) values (10,’366’); insert into t_student( student_id, student_name, sex, birthday, email, classes_id ) values( 1001, ‘zhangsan’, ‘m’, ‘1988-01-01’, ‘qqq\@163.com’, 10 ) mysql> update t_classes set classes_id = 20 where classes_name = ‘366’;因为子表（t_student）存在一个外键 classes_id，它参照了父表（t_classes）中的主键，所以先删除子表中的引用记录，再修改父表中的数据。 我们也可以采取以下措施 级联更新。delete from t_classes where classes_id = 10;因为子表（t_student）存在一个外键 classes_id，它参照了父表（t_classes）中的主键，所以先删除父表，那么将会影响子表的参照完整性，所以正确的做法是，先删除子表中的数据，再删除父表中的数据，采用 drop table 也不行，必须先 drop 子表，再 drop 父表 我们也可以采取以下措施 级联删除。13.4.5、级联更新与级联删除13.4.5.1、on update cascade;mysql 对有些约束的修改比较麻烦，所以我们可以先删除，再添加 alter table t_student drop foreign key fk_classes_id; alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id) on update cascade; 我们只修改了父表中的数据，但是子表中的数据也会跟着变动。13.4.5.2、on delete cascade;| mysql 对有些约束的修改时不支持的，所以我们可以先删除，再添加 alter table t_student drop foreign key fk_classes_id; alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id) on delete cascade; delete from t_classes where classes_id = 20; 我们只删除了父表中的数据，但是子表也会中的数据也会删除。13.5、t_student 和 t_classes 完整示例| drop table if exists t_classes; create table t_classes( classes_id int (3), classes_name varchar(30) not null, constraint pk_classes_id primary key(classes_id) ) drop table if exists t_student; create table t_student( student_id int(10), student_name varchar(50) not null, sex char(2) not null, birthday date not null, email varchar(30) unique, classes_id int (3) not null, constraint pk_student_id primary key(student_id), constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id) )

Select 语句后面跟的是字段名称，select 是关键字，select 和字段名称之间采用空格隔开，from 表示将要查询的表，它和字段之间采用空格隔开— 查询一个字段select ename from emp;— 查询多个字段select empno, ename from emp;— 查询全部字段select from emp;在 select 语句中可以使用运算符— 计算员工年薪select empno, ename, sal12 from emp;— 将查询出来的字段显示为中文select empno as ‘员工编号’, ename as ‘员工姓名’, sal12 as ‘年薪’ from emp;— 注意:字符串必须添加单引号 | 双引号— 可以采用as关键字重命名表字段，其实as也可以省略，如：select empno “员工编号”, ename “员工姓名”, sal12 “年薪” from emp;

条件查询需要用到 where 语句，where 必须放到 from 语句表的后面支持如下运算符

— or 只要满足条件即可,相当于包含— 查询出job为manager或者job为salesman的员工select * from emp where job=’MANAGER’ or job=’SALESMAN’;

— 表达式的优先级— 查询薪水大于1800，并且部门代码为20或30的（正确的写法）select * from emp where sal > 1800 and (deptno = 20 or deptno = 30);

— in 表示包含的意思，完全可以采用or来表示，采用in会更简洁一些— - 查询出job为manager或者job为salesman的员工select from emp where job in (‘manager’,’salesman’);—- 查询出薪水包含1600和薪水包含3000的员工select from emp where sal in(1600, 3000);

— not— 查询出薪水不包含1600和薪水不包含3000的员工（第一种写法）select from emp where sal 1600 and sal 3000;— 查询出薪水不包含1600和薪水不包含3000的员工（第二种写法select from emp where not (sal = 1600 or sal = 3000);select from emp where sal not in (1600, 3000);— 查询出津贴不为null的所有员工select from emp where comm is not null;

— like— like可以实现模糊查询，like支持%和下划线匹配— 查询姓名以M开头所有的员工select from emp where ename like ‘M%’;— 查询姓名以N结尾的所有员工select from emp where ename like ‘%N’;— 查询姓名中包含O的所有的员工select from emp where ename like ‘%O%’; select from emp where ename like ‘_A%’;Like 中%和下划线的差别？%匹配任意字符出现的个数下划线只匹配一个字符Like 中的表达式必须放到单引号中|双引号中，以下写法是错误的：select * from emp where ename like _A%

单一字段排序排序采用 order by 子句，orderby 后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by 默认采用升序，如果存在 where 子句那么 order by 必须放到 where 语句的后面— 按照薪水由小到大排序(系统默认由小到大)select from emp order by sal;— 取得job为MANAGER的员工，按照薪水由小到大排序(系统默认由小到大)select from emp where job=’MANAGER’ order by sal;如果包含 where 语句 order by 必须放到 where 后面，如果没有 where 语句 order by 放到表的后面— 按照多个字段排序，如：首先按照job排序，再按照sal排序select * from emp order by job,sal;手动指定排序顺序

select * from emp order by sal asc;

select * from emp order by sal desc;多个字段排序

select * from emp order by job desc, sal desc;如果采用多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序使用字段的位置来排序

select * from emp order by 6;不建议使用此种方式，采用数字含义不明确，程序不健壮

注意：分组函数自动忽略空值，不需要手动的加 where 条件排除空值。select count(*) from emp where xxx; 符合条件的所有记录总数。select count(comm) from emp; comm 这个字段中不为空的元素总数。注意：分组函数不能直接使用在 where 关键字后面。mysql> select ename,sal from emp where sal > avg(sal);ERROR 1111 (HY000): Invalid use of group functioncount— 取得所有的员工数select count(*) from emp;

Count(*)表示取得所有记录，忽略null，为null的值也会取得

— 取得津贴不为null员工数select count(comm) from emp;

采用count(字段名称)，不会取得为null的记录

— 取得工作岗位的个数select count(distinct job ) from emp;sum

select sum(sal) from emp;

select sum(comm) from emp;null 会被忽略

select sum(sal+comm) from emp;从以上结果来看，不正确，原因在于 comm 字段有 null 值，所以无法计算，sum 会忽略掉，正确的做法是将 comm 字段转换成 0select sum(sal+IFNULL(comm, 0)) from emp;7.3、avg取得某一列的平均值

| select avg(sal) from emp; 7.4、max取得某个一列的最大值

| select max(sal) from emp;![](media1/0a4f8

| select max(str_to_date (hiredate, ‘%Y-%m-%d’)) from emp;7.5、min取得某个一列的最小值

| select min(sal) from emp;

select min(str_to_date(hiredate, ‘%Y-%m-%d’)) from emp;7.6、组合聚合函数可以将这些聚合函数都放到 select 中一起使用select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

https://www.runoob.com/mysql/mysql-functions.html1：CAST 函数CAST 函数语法规则是：Cast(字段名 as 转换的类型 )其中类型可以为：CHAR[(N)] 字符型，DATE 日期型，DATETIME 日期和时间型，DECIMAL float 型，SIGNED int，TIME 时间型—查询字段精度和小数位数SELECT CAST(‘12.5’ AS decimal(9,2))—精度与小数位数分别为9 与2。精度是总的数字位数，包括小数点左边和右边位数的总和。而小数位数是小数点右边的位数。这表示本例能够支持的最大的整数值是9999999，而最小的小数是0.01。2：concat 函数concat(str1, str2,…) 返回结果为连接参数产生的字符串，如果有任何一个参数为 null，则返回值为 null。select concat(id,’,’,name) as info from table13：concat_ws()函数concat_ws(separator, str1, str2, …)说明：第一个参数指定分隔符。需要注意的是分隔符不能为 null，如果为 null，则返回结果为 null。select concat_ws(‘,’,id ,name) as info from t1;查询结果为info10002,zhang4：round()函数返回离 x 最近的整数select round(1.23456) —1

分组查询主要涉及到两个子句，分别是：group by 和 having8.1、group by

select job, sum(sal) from emp group by job;如果使用了 order by，order by 必须放到 group by 后面

select job,deptno,sum(sal) from emp group by job,deptno;mysql> select empno,deptno,avg(sal) from emp group by deptno;| empno | deptno | avg(sal)| 7782 | 10 | 2916.666667 || 7369 | 20 | 2175.000000 || 7499 | 30 | 1566.666667 |以上 SQL 语句在 Oracle 数据库中无法执行，执行报错。以上 SQL 语句在 Mysql 数据库中可以执行，但是执行结果矛盾。在 SQL 语句中若有 group by 语句，那么在 select 语句后面只能跟分组函数+参与分组的字段。8.2、having如果想对分组数据再进行过滤需要使用 having 子句取得每个岗位的平均工资大于 2000| select job, avg(sal) from emp group by job having avg(sal) >2000; |分组函数的执行顺序：根据条件查询数据分组采用 having 过滤，取得正确的数据8.3、select 语句总结一个完整的 select 语句格式如下select 字段 from 表名 where ……. group by …….. having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现) order by ……..以上语句的执行顺序

原则：能在 where 中过滤的数据，尽量在 where 中过滤，效率较高。having 的过滤是专门对分组之后的数据进行过滤的。

left join ，right join ，all join做连接查询的时候一定要写上关联条件

连接查询：也可以叫跨表查询，需要关联多个表进行查询

select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;以上称为“自连接”，只有一张表连接，具体的查询方法，把一张表看作两张表即可，如以上示例：第一个表 empe 代码了员工表，emp m 代表了领导表，相当于员工表和部门表一样

—SQL92select e.name,e.sal,d.name from emp e,dept d where e.deptno = d.deptno and e.sal >2000;—SQL99select e.name,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno where e.sal>2000;在实际中一般不加 inner 关键字Sql92 语法和 sql99 语法的区别：99 语法可以做到表的连接和查询条件分离，特别是多个表进行连接的时候，会比 sql92 更清晰

—右连接select e.name,e.sal,d.dname from emp e right join dept d on e.deptno = d.deptno—左连接select e.name,e.sal,d.dname from dept d left join emp e on e.deptno = d.deptno;左外连接（左连接）和右外连接（右连接）的区别：左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件的显示,不符合条件的不显示，右连接恰恰相反

子查询就是嵌套的 select 语句，可以理解为子查询是一张表1：where （select ……）例：查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名思路：首先取得管理者的编号，去除重复的，查询员工编号包含管理者编号的select empno, ename from emp where empno in(select mgr from emp where mgr is not null);例：查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水思路：取得平均薪水，取得大于平均薪水的员工select empno, ename, sal from emp where sal > (select avg(sal) from emp);2、from (select……)例：查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名思路：首先取得管理者的编号，去除重复的select e.empno, e.ename from emp e join (select distinct mgr from emp where mgr is not null) m on e.empno=m.mgr;3、select(select……)

union 合并集合（相加）：合并结果集的时候，需要查询字段对应个数相同。在 Oracle 中更严格，不但要求个数相同，而且还要求类型对应相同。union 与 union all 的区别：union all 只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用 union all 的执行效率要比 union 高一、区别 1：取结果的交集1、union： 对两个结果集进行并集操作 , 不包括重复行,相当于 distinct, 同时进行默认规则的排序;2、union all： 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;二、区别 2：获取结果后的操作union: 会对获取的结果进行排序操作union all: 不会对获取的结果进行排序操作

mysql 提供了 limit ，主要用于提取前几条或者中间某几行数据select from table limit m,n其中 m 是指记录开始的 index，从 0 开始，表示第一条记录n 是指从第 m+1 条开始，取 n 条。—取得前 5 条数据select from emp limit 5;执行顺序SELECT的语法顺序就是起执行顺序

FROMWHERE （先过滤单表／视图／结果集，再JOIN）GROUP BYHAVING （WHERE过滤的是行，HAVING过滤的是组，所以在GROUP之后）ORDER BY

mysql> SHOW TABLE STATUS LIKE ‘emp’ \G

MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但 MyISAM不支持事务和行级锁(myisam 改表时会将整个表全锁住)，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

MyISAM 存储引擎是 MySQL 最常用的引擎。

它管理的表具有以下特征：

InnoDB 存储引擎是 MySQL 的缺省引擎。

支持外键及引用的完整性，包括级联删除和更新

使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得 MEMORY 存储引擎非常快。

MEMORY 存储引擎以前被称为 HEAP 引擎。

MyISAM 表最适合于大量的数据读而少量数据更新的混合操作。MyISAM 表的另一种适用情形是使用压缩的只读表。

查询较多的选择 MYISAM：InnoDB 的表是根据主键进行展开的 B+tree 的聚集索引。MyIsam 则非聚集型索引，myisam 存储会有两个文件，一个是索引文件，另外一个是数据文件，其中索引文件中的索引指向数据文件中的表数据。聚集型索引并不是一种单独的索引类型，而是一种存储方式，InnoDB 聚集型索引实际上是在同一结构中保存了 B+tree 索引和数据行。当有聚簇索引时，它的索引实际放在叶子页中。

innodb 保存时一个.frm 文件，一个.idb 文件.索引文件和数据文件是放在同一个文件中的myisam 有三个文件，索引文件和数据文件分开存放 .MYD 文件表示数据。.MYI 文件表示索引。.frm 文件InnoDB 默认可以创建 16 个索引

InnoDB 不支持全文检索，而 MyIsam 支持全文检索，查询效率上 MyIsam 要高

事务具有四个特征 ACID1：原子性（Atomicity） 整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的 DML 要么全成功，要么全失败。实现：利用 undo log 回滚日志，

(1)当你 delete 一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert 这条旧数据

undo log记录了这些回滚需要的信息，当事务执行失败或调用了 rollback，导致事务需要回滚，便可以利用 undo log 中的信息将数据回滚到修改之前的样子。2：一致性（Consistency） 在事务开始之前与结束之后，数据库都保持一致状态。实现：通过实现其他三个保证一致性3：隔离性(Isolation) 一个事务不会影响其他事务的运行。实现：加锁和 MVCC 机制，一个行记录数据有多个版本对快照数据，这些快照数据在undo log中。如果一个事务读取的行正在做 DELELE 或者 UPDATE 操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。4：持久性(Durability) 在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。实现：redo log 重做日志采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。事务中存在一些概念：

当执行 DML 语句是其实就是开启一个事务关于事务的回滚需要注意：只能回滚 insert、delete 和 update 语句，不能回滚 select（回滚 select 没有任何意义），对于 create、drop、alter 这些无法回滚.事务只对 DML 有效果。注意：rollback，或者 commit 后事务就结束了。

mysql> SET AUTOCOMMIT = OFF；mysql> SET AUTOCOMMIT = ON；或mysql> SET SESSION AUTOCOMMIT = OFF；mysql> SET SESSION AUTOCOMMIT = ON；show variables like ‘%auto%’; — 查看变量状态

事务的隔离级别决定了事务之间可见的级别。当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：（1）脏读取（Dirty Read）：事务 A 读到了事务 B 未提交的数据。（2）不可重复读（Non-repeatable Read）事务 A 第一次查询得到一行记录 row1，事务 B 提交修改后，事务 A 第二次查询得到 row1，但列内容发生了变化。（3）幻像读（Phantom Read）事务 A 第一次查询得到一行记录 row1，事务 B 提交修改后，事务 A 第二次查询得到两行记录 row1 和 row2。InnoDB 引擎，可重复读隔离级别，，使用当前读时。表现：一个事务(同一个 read view)在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行。幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。注意：1、在可重复读隔离级别下，普通查询是快照读，是不会看到别的事务插入的数据的，幻读只在当前读下才会出现。 幻读专指新插入的行，读到原本存在行的更新结果不算。因为当前读的作用就是能读到所有已经提交记录的最新值。影响：会造成一个事务中先产生的锁，无法锁住后加入的满足条件的行。产生数据一致性问题，在一个事务中，先对符合条件的目标行做变更，而在事务提交前有新的符合目标条件的行加入。这样通过 binlog 恢复的数据是会将所有符合条件的目标行都进行变更的。原因：行锁只能锁住行，即使把所有的行记录都上锁，也阻止不了新插入的记录。解决：

当前读情况下加间隙锁，快照读情况下 mysql 会自动使用 MVCC 机制解决幻读。

InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务：

允许一个事务可以看到其他事务未提交的修改。原理：写数据时加上排他锁，直到事务结束， 读的时候不加锁。

允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。（Oracle 默认）原理：写数据的时候加上排他锁， 直到事务结束， 读的时候加上共享锁， 读完数据立刻释放。(共享锁规则 1)

确保如果在一个事务中执行两次相同的 SELECT 语句，都能得到相同的结果，不管其他事务是否提交这些修改。 （银行总账）该隔离级别为 InnoDB 的缺省设置。原理：写数据的时候加上排他锁， 直到事务结束， 读数据的时候加共享锁， 也是直到事务结束。(共享锁规则 2)

将一个事务与其他事务完全地隔离。原理：严格有序执行，事务不能并发执行。例:A 可以开启事物,B 也可以开启事物A 在事物中执行 DML 语句时,未提交B 不以执行 DML,DQL 语句

通过修改配置文件设置

[mysqld]transaction-isolation = READ-COMMITTED通过命令动态设置隔离级别

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL 其中的可以是：

例如： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

事务隔离级别的作用范围分为两种：

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED；或：mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；

对比 Oracle 查询隔离级别：1）：

declaretrans_id Varchar2(100);begintrans_id := dbms_transaction.local_transaction_id( TRUE );end;

2）：

SELECT s.sid, s.serial#,CASE BITAND(t.flag, POWER(2, 28))WHEN 0 THEN ‘READ COMMITTED’ELSE ‘SERIALIZABLE’ END AS isolation_levelFROM v$transaction tJOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context(‘USERENV’, ‘SID’);

redolog：重做日志，保证了事务的持久性。事务开启后，只要开始改变数据信息就会持续写入 redo buffer 中，具体落盘可以指定不同的策略。在数据库发生意外故障时，尚有修改的数据未写入磁盘，在重启 mysql 服务的时候，根据 redo log 恢复事务修改后的新数据。

脏页：当内存数据页跟磁盘数据页内容不一样的时候，称内存也为脏页。几种情况下会刷脏页：

Redo buffer 持久化到 Redo log 的策略有三种： 取值 0 每秒一次进行提交持久化[可能丢失一秒内 的事务数据] 取值 1 默认值，每次事务提交执行 Redo buffer —> Redo log OS cache —>flush cache to disk [最安全，性能最差的方式] 取值 2 每次事务提交到系统缓存 OS cache，再每一秒从系统缓存中执行持久化 操作undolog：回滚日志，用于记录数据被修改前的信息，实现事务的原子性。update 操作会将当前数据加入到 undolog 中，然后使用行中的隐藏字段 DB_ROLL——PTR 回滚字段执行的前一个版本的数据。binglog 是一个二进制的日志文件，会记录 mysql 的数据更新或潜在个跟新 （delete from table where id =xxx）主从复制就是依靠 binglog

Multi-Version Concurrency Control，多版本并发控制，MVCC 在 MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。当前读：读取记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的几率进行加锁。快照读：不加锁的非阻塞读，快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；

它的实现原理主要是依赖记录中的 3 个隐式字段，undo 日志 ，Read View 来实现的。隐式字段：DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID 等字段

undo 日志ReadView 读视图就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID(当每个事务开启时，都会被分配一个 ID, 这个 ID 是递增的，所以最新的事务，ID 值越大)如何实现读已提交和可重复读呢？就是生成 ReadView 的时机不同。对读已提交来说，事务中的每次读操作都会生成一个新的 ReadView，也就是说，如果这期间某个事务提交了，那么它就会从 ReadView 中移除。这样确保事务每次读操作都能读到相对比较新的数据而对可重复读来说，事务只有在第一次进行读操作时才会生成一个 ReadView，后续的读操作都会重复使用这个 ReadView。也就是说，如果在此期间有其他事务提交了，那么对于可重复读来说也是不可见的，因为对它来说，事务活跃状态在第一次进行读操作时就已经确定下来，后面不会修改了。通过在每行记录的后边保存两个隐藏的列来实现。这两个列， 一个保存了行的创建时间，一个保存了行的过期时间， 当然存储的并不是实际的时间值，而是系统版本号。

MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。不同存储引擎的 MVCC 实现是不同的，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。新增：会给行数据添加两个隐藏列，数据版本号和删除版本号。数据版本号值为插入时的事务 id，删除版本号默认为 null。删除：会给行数据的删除版本号设一个当前事务 id 值。修改：会先拷贝一份行数据，再把原先行数据的删除版本号设值，再修改拷贝的数据，并改变数据版本号值。查询：必须保证当前事务 ID 大于等于该行数据的数据版本号，并且删除版本号必须为 null 或者大于当前事务 ID 值。大多数情况下可以代替行级锁，降低系统开销InnoDB 使用的是行锁。而 InnoDB 的事务分为四个隔离级别，其中默认的隔离级别 REPEATABLE READ 需要两个不同的事务相互之间不能影响，而且还能支持并发，这点悲观锁是达不到的，所以 REPEATABLE READ 采用的就是乐观锁，而乐观锁的实现采用的就是 MVCC。正是因为有了 MVCC，才造就了 InnoDB 强大的事务处理能力。因为有行 id，事务 idredo 日志和 undo 日志用于回滚，先 redo 在 undoundo 日志记录操作之前的数据redo 是mysql 的删除并不是物理上的删除，而是标记……

当执行查询 SQL 时会生成一致性视图 read-view，他由执行查询时所有未提交事务 id 数组（数组里最小的 id 为 min_id)和已创建的最大事务 id（max_id)组成，查询的数据结果需要跟 read-view 做对比从而得到快照结果：对比规则：如果落在绿色部分（trx_is 如果落在红色部分(trx_id > max_id)，表示这个版本是由将来启动的事务生成的，是肯定不可见的，如果落在黄色部分，那就包括两种情况：)，表示这个版本是已提交的事务生成的，这个数据是可见的，

当我们创建了两个索引时，一个是主键，一个是 name，它还会在生成一棵 B+Tree，这棵树的叶子节点存放的是主键，当我们通过 name 进行查找的时候，会得到一个主键，然后在通过主键再去上面的这个主键 B+Tree 中进行查找，我们称这个操作为 ==回表==

当我们的 SQL 语句使用的是下面这种的时候，它会查找第一颗树，直接返回我们的数据select from tb where id = 1当我们使用下面这种查询的时候，它会先查找第二棵树得到我们的主键，然后拿着主键再去查询第一棵树select from tb where name = ‘gang’回表就是通过普通列的索引进行检索，然后再去主键列进行检索，这个操作就是回表==但是我们在使用检索的时候，尽量避免回表，因为这会造成两次 B+Tree 的查询，假设一次 B+Tree 查询需要三次 IO 操作，那么查询两次 B+Tree 就需要六次 IO 操作。==

我们看下面的两个 SQL 语句，看看它们的查询过程是一样的么？select * from tb where id = 1select name from tb where name = zhou答案是不一样的，首先我们看第二个语句，就是要输出的列中，就是我们的主键，当我们通过 name 建立的 B+Tree 进行查询的时候

我们可以直接找到我们的数据，并得到主键，但是因为我们要返回的就是 name，此时说明数据存在了，那么就直接把当前的 name 进行返回，而不需要通过主键再去主键 B+Tree 中进行查询。这样一个不需要进行回表操作的过程，我们称为索引覆盖

PRIMARY KEY(id)ALTER TABLE customeradd PRIMARY KEY customer(customer_no);

Q：主键与索引的区别？

A：

1：主键是索引的一种，定义主键将自动创建主键索引，主键索引时唯一索引的特定类型。

2：一个表只能有一个主键，但是索引可以有多个。

唯一索引 类似于普通索引，索引列的值必须唯一唯一索引和主键索引的区别就是，唯一索引允许出现空值，而主键索引不能为空create unique index index_name on table(column)

当我们需要建立索引的字段，既不是主键索引，也不是唯一索引那么就可以创建一个普通索引create index index_name on table(column)或者创建表时指定create table(…, index index_name column)

CREATE INDEX idx_customer_name ON customer(customer_name);

就可以通过组合 name 和 age 来建立一个组合索引，加快查询效率，建立成组合索引后，我的索引将包含两个 key 值在多个字段上创建索引，遵循最左匹配原则alter table t add index index_name(a,b,c);

创建联合索引时，索引存储在叶子节点，叶子会根据最左边的进行排序，如果没有匹配上，后边也不再匹配，导致索引失效。索引失效的情况：

高效：select loc_id , loc_desc , region from location where loc_id = 10unionselect loc_id , loc_desc , region from location where region = “melbourne”低效：select loc_id , loc desc , region from location where loc_id = 10 or region = “melbourne”

使用了函数的

概念基于哈希的实现，只有精确匹配索引所有的列的查询才有效，在 mysql 中，只有 memory 的存储引擎显式支持哈希索引，哈希索引自身只需存储对应的 hash 值，索引索引的结构十分紧凑，这让哈希索引查找的速度非常快。哈希索引的限制

哈希索引值包含哈希值和行指针，而不存储字段值。索引不能使用索引中的值来避免读取行

哈希冲突比较多的话，维护的代价也会很高

MySQL 每次只使用一个索引，与其说 数据库查询只能用一个索引，倒不如说，和全表扫描比起来，去分析两个索引 B+树更耗费时间，所以 where A=a and B=b 这种查询使用（A，B）的组合索引最佳，B+树根据（A，B）来排序。Q：哪些情况需要创建索引？

主键自动建立唯一索引

Q：哪些情况不要创建索引？

Why:提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件

注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈一般用来查看索引是否起作用了，查询了多少行记录，执行计划：执行流程

IDid 列的编号是 select 的序列号，有几个 select 就有几个 id，并且 id 的顺序是按 select 出现的顺序增长的。id 值相同，从上往下顺序执行表的执行顺序，因数量的个数改变而改变，数据量小的优先查询。嵌套子查询时，先查内层，再查内层。id 不同，id 越大越优先查询，2>1select_type查询的类型，主要是用于区分普通查询（simple）、联合查询、子查询等复杂的查询simple：简单的 select 查询 ;primary：查询中包含任何复杂的子部分，最外层查询则被标记为 primary=》复杂查询中最外层的 select例explain select from (select from t3 where id=3952602) a ;subquery：在 select 或 where 列表中包含了子查询=》含在 select 中的子查询（不在 from 子句中） ;simple：表示不需要 union 操作或者不包含子查询的简单 select 查询。有连接查询时，外层的查询为 simple，且只有一个。primary：一个需要 union 操作或者含有子查询的 select，位于最外层的单位查询的 select_type 即为 primary。且只有一个。subquery：除了 from 字句中包含的子查询外，其他地方出现的子查询都可能是 subquerydependentsubquery：与 dependent union 类似，表示这个 subquery 的查询要受到外部表查询的影响。derive：衍生查询，使用到了临时表，from 字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套 select。explain select cr.name from (select from course where tid in (1,2)) cr;union：union 连接的两个 select 查询，第一个查询是 dervied 派生表，除了第一个表外，第二个以后的表 select_type 都是 uniondependentunion：与 union**一样，出现在 union 或 union all 语句中，但是这个查询要受到外部查询的影unionresult：包含 union的结果集，在 union 和 union all 语句中,因为它不需要参与查询，所以 id 字段为 null。table表示 explain 的一行正在访问哪个表。type表示关联类型或访问类型，即 MySQL 决定如何查找表中的行。访问类型，sql 查询优化中一个很重要的指标，结果值从好到坏依次是：依次从最优到最差分别为：system >const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL从最好到最差的连接类型为 const、eq_reg、ref、range、indexhe 和 ALL一般来说，好的 sql 查询至少达到 range 级别，最好能达到 ref,system 和 const 基本达不到。system：只有一条数据的系统表，或：衍生表只有一条数据的主查询。新版的也查不到了const：仅仅能查到一条数据，用于主键或唯一索引。eq_ref：唯一性索引，对于每个索引建的查询，返回匹配唯一行数据（有且只有一个，不能多，不能 0）select …… from …… where name = ……常见于唯一索引和主键索引以上可预不可求。ref：非唯一性索引，对于每个索引建的查询，返回匹配的所有行（0，多）range：检索指定范围的行，where 后面是一个范围查询（between，in，> ,=，但是 in 有时候会失效变成 all）index：查询全部索引树中数据explain select id from teacherall：查询所有表的数据possible_keys显示查询可能使用哪些索引来查找。Key显示 mysql 实际采用哪个索引来优化对该表的访问。果没有使用索引，则该列是 NULL。如果想强制 mysql 使用或忽视 possible_keys 列中的索引，在查询中使用 force index、ignore index。 查询中如果使用了覆盖索引，则该索引仅出现在 key 列表中Key_Len索引的长度，用于判断符合索引是否被完全使用，比如 a,b,c 都是 char(20),查询时 key_len 为 60，如果索引字段可以为 null，则会使用一个字节用于标识。如果是 varchar 可变长度，则会增加两个字节用于标识。ref作用：指明当前表所参考的字段，select …… from where a.c = b.x其中 b.x 可以是常量，此时就是 constnull 代表没有给列添加索引这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）rows被优化查询的数据个数(实际通过索引查到的数据个数)根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数Extra(1）：using filesort：性能消耗比较大，需要额外一次排序（查询）排序：先查询，根据年龄排序，先查出年龄，再根据排序explain select from test02 where a1 = ‘’ order by a2;复合索引：不能跨列，（最左匹配原则）对(a1,a2,a3)建立复合索引select from test02 where a1 = ‘’ order by a2;—-此时不会出现using filesortselect from test02 where a1 = ‘’ order by a3; — 会出现，性能不如上一个好（2）：using temporary ：性能耗损大，用到了临时表，一般出现在 group by 语句中explain select a1 from test02 where a1 in (‘1’,’2’,’3’) group by a2;这条语句会先根据 a1 查询出 a1 的记录，再查一遍 a2，放到临时表进行分组。解析过程：from on join where groupby having select distinct order by limit（3）：using index ：性能提升，索引覆盖。不读取源文件，只从索引文件中查询。不需要回表查询。只要索引都在索引表中select a1 from test where a1 = ‘1’;复合索引（a1，a2）select a1,a2 from test where a1 = ‘’ or a2 = ‘’;索引覆盖对 possible key 和 key 有影响，如果没有 where ，则索引只出现在 key 中，如果有 where ，则索引出现在 key 和 possible key 中（4）using where；需要回表查询，（5）impossible where ：where 子句永远为 falseselect from where a1 = ‘’ and a1 = ‘test’;Case**id，表（真实表），可能索引index

索引的使用顺序和建立索引的顺序一致，但是可能自己没遵循，但是优化器会帮助你进行优化。

存储过程，没有返回值，存储过程，有返回模拟创建海量数据（1）分析show profiles;—默认是关闭的,会记录所有profiling打开之后的全部SQL查询语句所花费的时间show variables like ‘%profiling%’;set profiling = on;—开启show profiles;（2）SQL 诊断：上述缺点：不够精确，不能分别显示 IO 等，可以使用进行 SQL 诊断show profile all for query 2;—2代表profiles中的SQL序号show profile cpu,blockio for query;—显示cpu占用时间和IO响应时间（3）全局查询日志：记录开启后，全部 SQL 语句。show variables like ‘%general_log%’;set global general_log = 1;—开启全局日志

在说索引下推的时候，我们首先在举两个例子select from tb1 where name = ? and age = ?在 mysq 5.6 之前，会先根据 name 去存储引擎中拿到所有的数据，然后在 server 层对 age 进行数据过滤在 mysql5.6 之后，根据 name 和 age 两个列的值去获取数据，直到把数据返回。通过对比能够发现，第一个的效率低，第二个的效率高，因为整体的 IO 量少了，原来是把数据查询出来，在 server 层进行筛选，而现在在存储引擎层面进行筛选，然后返回结果。我们把这个过程就称为 *索引下推

情况：

项目中有一个表A的goodsid是varchar类型，另一个表B的goodsid是int类型，同时这两个表的goodsid都是主键。在获取表B的goodsid后直接在表A中查询，大数据量下超时严重

表B的goodsid是int类型，使用int类型在表A中查询时会发生隐式转换，此时表A的goodsid字段因为隐式转换会扫描全表，造成字段的索引阻塞。

原理：

1）如果表定义的是varchar字段，传入的是int型数字，则会发生隐式转换

2）表定义的是int字段，传入的是varchar数字字符串，不会发生隐式转换，如果在与数字字符串比较大小并且数字字符串还超过int定义的长度（会以字符串类型比较’$’）会隐式转换

3）隐式转换会扫描全表，造成字段的索引的阻塞。

解决：

将ID类型全部

如果未使用索引，我们查询 工资大于 1500 的会执行全表扫描

什么时候需要给字段添加索引：-表中该字段中的数据量庞大-经常被检索，经常出现在 where 子句中的字段-经常被 DML 操作的字段不建议添加索引索引等同于一本书的目录主键会自动添加索引，所以尽量根据主键查询效率较高。如经常根据 sal 进行查询，并且遇到了性能瓶颈，首先查看程序是否存算法问题，再考虑对 sal 建立索引，建立索引如下：1、create unique index 索引名 on 表名(列名);create unique index u_ename on emp(ename);2、alter table 表名 add unique index 索引名 (列名);create index test_index on emp (sal);

show index from emp;

注意一定不可以用 select * … 可以看到 type!=all 了，说明使用了索引explain select sal from emp where sal > 1500;条件中的 sal 使用了索引

如下图：假如我们要查找 sal 大于 1500 的所有行，那么可以扫描索引，索引时排序的，结果得出 7 行，我们知道不会再有匹配的记录，可以退出了。 如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（如二分查找法）。 这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，通常这些技术都属于 DBA 的工作。

DROP INDEX index_name ON talbe_nameALTER TABLE table_name DROP INDEX index_nameALTER TABLE table_name DROP PRIMARY KEY其中，前两条语句是等价的，删除掉 table_name 中的索引 index_name。 第 3 条语句只在删除 PRIMARY KEY 索引时使用，因为一个表只可能有一个 PRIMARY KEY 索引，mysql> ALTER TABLE EMP DROP INDEX test_index; 删除后就不再使用索引了，查询会执行全表扫描。

聚簇索引就是按每张表的主键构造一棵 B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚簇索引的叶子节点称为数据也，这个特性就决定了索引组织表中的数据也是索引的一部分。==一句话来说：将索引和数据放在一起的，就称为聚簇索引==

在 InnoDB 中，在聚簇索引之上创建的索引被称为辅助索引，非聚簇索引都是辅助索引，像复合索引，前缀索引，唯一索引。辅助索引叶子节点存储不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找，这个就被称为 回表操作

InnoDB 使用的是聚簇索引，将主键组织到一棵 B+树中，而行数据就储存在叶子节点上，若使用”where id = 14”这样的条件查找主键，则按照 B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对 Name 列进行条件搜索，则需要两个步骤：第一步在辅助索引 B+树中检索 Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引 B+树种再执行一次 B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）聚簇索引具有唯一性，由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这是因为聚簇索引改变了表中行的物理顺序，数据行 按照一定的顺序排列，并且自动维护这个顺序；聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键（类似 oracle 中的 RowId）来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

优点：

缺点：

每次使用辅助索引检索都要经过两次 B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了 Buffer 中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键 Id 来组织数据，获得数据更快。2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次 IO 读写的缓存中没有，需要发生一次新的 IO 操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。注：我们知道一次 io 读写，可以获取到 16K 大小的资源，我们称之为读取到的数据区域为 Page。而我们的 B 树，B+树的索引结构，叶子节点上存放好多个关键字（索引值）和对应的数据，都会在一次 IO 操作中被读取到缓存中，所以在访问同一个页中的不同记录时，会在内存里操作，而不用再次进行 IO 操作了。除非发生了页的分裂，即要查询的行数据不在上次 IO 操作的换村里，才会触发新的 IO 操作。3.因为 MyISAM 的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行 I/O 读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次 I/O。（强烈的对比）4.不过，如果涉及到大数据量的排序、全表扫描、count 之类的操作的话，还是 MyISAM 占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

当使用主键为聚簇索引时，主键最好不要使用 uuid，因为 uuid 的值太过离散，不适合排序且可能出线新增加记录的 uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。建议使用 int 类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到 IO 操作读取到的数据量。为什么主键通常建议使用自增 id聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增 id，那么可以想 象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

非聚簇索引也被称为辅助索引，辅助索引在我们访问数据的时候总是需要两次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到主键值，然后在通过主键值找到数据行的数据页，在通过数据页中的 Page Directory 找到数据行。InnoDB 辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了行数据的聚簇索引建。辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在 InnoDB 中有时也称为辅助索引为二级索引

MyISAM 使用的是非聚簇索引，非聚簇索引的两棵 B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引 B+树的节点存储了主键，辅助键索引 B+树存储了辅助键。表数据存储在独立的地方，这两颗 B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

1：尽量不要使用 NULL，应该在 Service 层优化处理。2：查询条件中不要使用函数，计算3：尽量不要使用 NOT 等负向查询，因为这样会发生全表的遍历。4：OR 改成 IN 或者使用 Union3：分解关联查询缓存效率高，分解后，单个表改变可能少，命中率就高了。减少数据库锁竞争微服务化友好，易于做数据库拆分减少冗余查询，减少传输4：大于小于，IN，OR，betweenSELECT FROM tin where c1 >= 100 and c1 ,则自身和右侧索引失效优化时 SQL 优化器会影响我们的优化，导致一些概率性的出现。补救：使用索引覆盖(5)：like 以% 开头，失效2：exist 和 in如果主查询的数据集大，则使用 In。如果子查询的数据量大，则使用 existselect …… from table where exist (子查询);select …… from table where 字段 in (子查询);

2：优化数据库结构将字段多的表分解成多个表，增加中间表

默认关闭，开发调优时打开日志，部署时关闭。检查是否开启了慢查询日志show variables like ‘%slow_query_log%’;开启慢查询日志— 临时开启，退出关闭服务即关闭set global slow_query_log = 1;

—永久开启，一般不用/etc/my.cnf中追加配置slow_query_log = 1slow_query_log_file= /var/lib/mysql/localhost-slow.log慢查询默认阈值为 10 秒，修改慢查询阀值— 设置临时阈值set global long_query_time = 5;

—永久开启，一般不用/etc/my.cnf中追加配置long_query_time = 3;查询超过慢查询阈值的命令show global status like ‘%slow_querise%’;—只显示有几条，具体是哪条查看日志文件通过工具查看mysqldumpslow – help

用来协助适配表的结构以适应前端现有的应用程序视图作用：

如下示例：查询员工的姓名，部门，工资入职信息等信息。| select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno = e.deptno and e.deptno = 10;为什么使用视图？因为需求决定以上语句需要在多个地方使用，如果频繁的拷贝以上代码，会给维护带来成本，视图可以解决这个问题

| alter view v_dept_emp as select ename,dname,sal,hiredate,e.deptno from e mp e,dept d where e.deptno = 20;

| drop view if exists v_dept_emp; |

优点：

创建的存储过程保存在数据库的数据字典中。

(1)CREATE USER username IDENTIFIED BY ‘password’;//修改用户密码alter USER username identified by ‘newpassword’说明:username——你将创建的用户名,password——该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.例如： create user p361 identified by ‘123’;(2)可以登录但是只可以看见一个库 information_schema命令详解grant all privileges on dbname.tbname to ‘username’@’login ip’ identified by ‘password’ with grant option;dbname=表示所有数据库tbname=表示所有表login ip=%表示任何 ippassword 为空，表示不需要密码即可登录with grant option; 表示该用户还可以授权给其他用户细粒度授权 首先以 root 用户进入 mysql，然后键入命令：grant select,insert,update,delete on . to p361 \@localhost Identified by “123”;如果希望该用户能够在任何机器上登陆 mysql，则将 localhost 改为 “%” 。粗粒度授权 我们测试用户一般使用该命令授权，GRANT ALL PRIVILEGES ON . TO ‘p361’\@’%’ Identified by “123”;注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:GRANT ALL PRIVILEGES ON . TO ‘p361’\@’%’ Identified by “123” WITH GRANT OPTION;privileges 包括： alter：修改数据库的表 create：创建新的数据库或表 delete：删除表数据 drop：删除数据库/表 index：创建/删除索引 insert：添加表数据 select：查询表数据 update：更新表数据 all：允许任何操作 usage：只允许登录

//授予某一项权利grant create view to 用户名

//撤销角色/权限revoke 角色|权限 from 用户名

//查看自身有哪些角色select * from user_role_privs;

//查看自身的角色和权限select * from role_sys_privs;

//修改用户处于锁定（非锁定）状态，锁定状态是不能登录的alter user 用户名 account lock|unlock;

命令详解revoke privileges on dbname[.tbname] from username; revoke all privileges on . from p361; use mysql select * from user进入 mysql 库中 修改密码; update user set password = password(‘qwe’) where user = ‘p646’;刷新权限; flush privileges

在 windows 的 dos 命令窗口中执行：mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123

在 windows 的 dos 命令窗口中执行：mysqldump bjpowernode emp> D:\ bjpowernode.sql -uroot –p123

登录 MYSQL 数据库管理系统之后执行：source D:\ bjpowernode.sql

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。

加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。

MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：

默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。

但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

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

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。 （This ensures that updates to a table are not “starved” even when there is heavy SELECT activity for the table. However, if there are many updates for a table, SELECT statements wait until there are no more updates.）。

这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。同时，一些需要长时间运行的查询操作，也会使写线程“饿死” ，应用中应尽量避免出现长时间运行的查询操作（在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解” ，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行）。

可以设置改变读锁和写锁的优先级：

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

在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

MyISAM存储引擎支持并发插入，以减少给定表的读和写操作之间的争用：

如果MyISAM表在数据文件中间没有空闲块，则行始终插入数据文件的末尾。 在这种情况下，你可以自由混合并发使用MyISAM表的INSERT和SELECT语句而不需要加锁——你可以在其他线程进行读操作的时候，同时将行插入到MyISAM表中。 文件中间的空闲块可能是从表格中间删除或更新的行而产生的。 如果文件中间有空闲快，则并发插入会被禁用，但是当所有空闲块都填充有新数据时，它又会自动重新启用。 要控制此行为，可以使用MySQL的concurrent_insert系统变量。

如果你使用LOCK TABLES显式获取表锁，则可以请求READ LOCAL锁而不是READ锁，以便在锁定表时，其他会话可以使用并发插入。

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁的争夺，如果 Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况：

InnoDB 实现了以下两种类型的行锁：

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

锁模式的兼容情况：

（如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务； 反之， 如果两者不兼容，该事务就要等待锁释放。）

InnoDB在事务执行过程中，使用两阶段锁协议：

随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；

锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

select for update：

在执行这个 select 查询语句的时候，会将对应的索引访问条目进行上排他锁（X 锁），也就是说这个语句对应的锁就相当于update带来的效果。

select * for update 的使用场景：为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。

select lock in share mode ：in share mode 子句的作用就是将查找到的数据加上一个 share 锁，这个就是表示其他的事务只能对这些数据进行简单的select 操作，并不能够进行 DML 操作。select * lock in share mode 使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。

性能影响：select for update 语句，相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。select lock in share mode 语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

for update 和 lock in share mode 的区别：

前一个上的是排他锁（X 锁），一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 for update ；后一个是共享锁，多个事务可以同时的对相同数据执行 lock in share mode。

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

InnoDB使用间隙锁的目的：

MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制（复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复）有以下特点：

一是 MySQL 的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。

二是 MySQL 的 Binlog 是按照事务提交的先后顺序记录的， 恢复也是按这个顺序进行的。

由此可见，MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。

锁和多版本数据（MVCC）是 InnoDB 实现一致性读和 ISO/ANSI SQL92 隔离级别的手段。

因此，在不同的隔离级别下，InnoDB 处理 SQL 时采用的一致性读策略和需要的锁是不同的：

对于许多 SQL，隔离级别越高，InnoDB 给记录集加的锁就越严格（尤其是使用范围条件的时候），产生锁冲突的可能性也就越高，从而对并发性事务处理性能的 影响也就越大。

因此， 我们在应用中， 应该尽量使用较低的隔离级别， 以减少锁争用的机率。实际上，通过优化事务逻辑，大部分应用使用 Read Commited 隔离级别就足够了。对于一些确实需要更高隔离级别的事务， 可以通过在程序中执行 SET SESSION TRANSACTION ISOLATION

LEVEL REPEATABLE READ 或 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 动态改变隔离级别的方式满足需求。

可以通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况：

Mysql也支持lock tables和unlock tables，这都是在服务器层（MySQL Server层）实现的，和存储引擎无关，它们有自己的用途，并不能替代事务处理。 （除了禁用了autocommint后可以使用，其他情况不建议使用）：

正确的方式见如下语句：例如，如果需要写表 t1 并从表 t 读，可以按如下做：

给表显示加表级锁（InnoDB表和MyISAM都可以），一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。（与MyISAM默认的表锁行为类似）

在用 LOCK TABLES 给表显式加表锁时，必须同时取得所有涉及到表的锁，并且 MySQL 不支持锁升级。也就是说，在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。

其实，在MyISAM自动加锁（表锁）的情况下也大致如此，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

例如，有一个订单表 orders，其中记录有各订单的总金额 total，同时还有一个 订单明细表 order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检 查这两个表的金额合计是否相符，可能就需要执行如下两条 SQL：

这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，order_detail 表可能已经发生了改变。因此，正确的方法应该是：

（在 LOCK TABLES 时加了“local”选项，其作用就是允许当你持有表的读锁时，其他用户可以在满足 MyISAM 表并发插入条件的情况下，在表尾并发插入记录（MyISAM 存储引擎支持“并发插入”））

如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

悲观锁，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

只有通过索引进行检索的时候才会使用行级锁，如果不是通过索引进行检索就会升级成表锁。行锁是为了最大并发化所提供的一种锁，封锁某一行数据。我知道的 mysql 行锁有三种，就间隙锁使用场景，我分成了唯一索引和非唯一索引两种情况。记住所有的for update都是当前读并且加上行锁，跟快照读不一样，（1）按对数据操作：读锁（共享锁）：多个读操作可以同时进行，互不干扰写锁（互斥锁）：如果当前写操作没有完毕，则无法进行其他读操作和写操作。（2）按粒度分：表锁和行锁表锁：偏小 myisam 存储引擎，开销小，无死锁，容易发生锁冲突，并发度低。lock table 表名字 1 read(write)，表名字 2 read(write)，其它行锁偏向 innodb,开销大，支持事务总的来说，Innodb 共有其中类型的锁

InnoDB 为了支持多粒度锁机制(multiple granularity locking)，即允许行级锁与表级锁共存，而引入了意向锁(intention locks)。意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

加锁的语法为：select … lock in share mode;　　要设置IS锁；select … for update;　　　　　　 要设置IX锁；事务要获得某些行的 S/X 锁，必须先获得表对应的 IS/IX 锁，意向锁仅仅表明意向，意向锁之间相互兼容，兼容互斥表如下：

虽然意向锁之间互相兼容，但是它与共享锁/排它锁互斥，其兼容互斥表如下:

记录锁，它封锁索引记录

间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。间隙锁一般都是针对非唯一索引而言的，它会对相邻的区间进行封锁，被锁上的区间内的值是无法再被使用的。当那个条件不存在的时候会找表中存在的相邻数据，然后再进行加锁。当主键做为条件的时候，不存在间隙锁，除非手动指定范围。间隙锁：是 Innodb 在可重复读提交下为了解决幻读问题时引入的锁机制，幻读是因为新增或则更新操作，这时如果进行范围查询的时候（加锁查询）会出现不一致的问题。加锁特性：1.加锁的基本单位是（next-key lock）,他是前开后闭原则2.插叙过程中访问的对象会增加锁3.索引上的等值查询—给唯一索引加锁的时候，next-key lock 升级为行锁4.索引上的等值查询—部分命中或者全不命中，next-key lock 退化为间隙锁5.唯一索引上的范围查询会访问到不满足条件的第一个值为止间隙锁死锁问题：不同于写锁相互之间是互斥的原则，间隙锁之间不是互斥的，如果一个事务 A 获取到了（5,10]之间的间隙锁，另一个事务 B 也可以获取到（5,10]之间的间隙锁。这时就可能会发生死锁问题，

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。默认情况下，innodb 使用 next-key locks 来锁定记录。但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为 Record Lock，即仅锁住索引本身，不是范围。但是 id 降级为普通索引(key)，也就是说即使这里声明了要加锁(for update)，而且命中的是索引，但是因为索引在这里没有 UK 约束，所以 innodb 会使用 next-key locks，数据库隔离级别 RR：事务A执行如下语句，未提交：select * from lock_example where id = 20 for update;

事务B开始，执行如下语句，会阻塞：insert into lock_example values(‘zhang’,15);如上的例子，事务 A 执行查询语句之后，默认给 id=20 这条记录加上了 next-key lock，所以事务 B 插入 10(包括)到 30(不包括)之间的记录都会阻塞。临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为 RC，临键锁则也会失效。

对已有数据行的修改与删除，必须加强互斥锁(X 锁)，那么对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对 insert 操作的。多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。事务 A 先执行，在 10 与 20 两条记录中插入了一行，还未提交：insert into t values(11, xxx);事务 B 后执行，也在 10 与 20 两条记录中插入了一行：insert into t values(12, ooo);因为是插入操作，虽然是插入同一个区间，但是插入的记录并不冲突，所以使用的是插入意向锁，此处 A 事务并不会阻塞 B 事务。

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入 AUTO_INCREMENT 类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。比如：事务 A 先执行，还未提交： insert into t(name) values(xxx);事务 B 后执行： insert into t(name) values(ooo);此时事务 B 插入操作会阻塞，直到事务 A 提交。总结：1. 按锁的互斥程度来划分，可以分为共享、排他锁；

2. 按锁的粒度来划分，可以分为：

其中

实这种版本号的方法并不是适用于所有的乐观锁场景。举个例子，当电商抢购活动时，大量并发进入，如果仅仅使用版本号或者时间戳，就会出现大量的用户查询出库存存在，但是却在扣减库存时失败了，而这个时候库存是确实存在的。想象一下，版本号每次只会有一个用户扣减成功，不可避免的人为造成失败。这种时候就需要我们的第二种场景的乐观锁方法。UPDATE t_goodsSET num = num - #{buyNum}WHERE id = #{id}AND num - #{buyNum} >= 0AND STATUS = 1说明：num-#{buyNum}>=0 ，这个情景适合不用版本号，只更新是做数据安全校验，适合库存模型，扣份额和回滚份额，性能更高。这种模式也是目前我用来锁产品库存的方法，十分方便实用。更新操作，最好用主键或者唯一索引来更新,这样是行锁，否则更新时会锁表

在可重复读级别下，因为加锁导致两个事务出现死锁。排查：— Oracle—查看死锁用户，状态，机器，程序select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object)— 查看被死锁的语句select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object))—-mysql1：查看当前事务select from information_schema.innodb_trx;2:查看当前锁定的事务select from information_schema.innodb_lock;3:查看当前等锁的事务select * from information_schema.innofb_lock_waits;解决方案：1：已经出现，查找，杀掉—-Oracle—查找死锁进程select s.username,I.object_id,I.session_id,s.serial#,I.oracle_username,I.os_user_name,I.process from v$locked_object I,v$session s where I.session_id = s.sid;—kill 掉这个死锁的进程alter system kill session ‘sid,serial#’;(其中sid = I.session_id)1：数据库级别设置超时时间[mysqld]log-error =/var/log/mysqld3306.loginnodb_lock_wait_timeout=60 #锁请求超时时间(秒)innodb_rollback_on_timeout = 1 #事务中某个语句锁请求超时将回滚整个事务innodb_print_all_deadlocks = 1 #死锁都保存到错误日志

死锁的可能情况：

1：程序问题

用户X访问表a锁住然后又申请访问B，用户Y访问B并锁住申请访问a。

解决：调整逻辑，仔细分析程序逻辑，对于数据的多表操作，尽量按照相同的顺序进行处理。

2：锁升级

用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。——真的会出现吗，独占和共享可以同时存在？

解决：

3：全表扫描

如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。

解决：

SQL语句不要使用太复杂的关联多表查询；explain对于有全表扫描的SQL语句建立索引；

—查看表空间select concat(round(sum(index_length)/(1024*1024),2),’MB’) AS ‘MB’ ,’Index Data Size’ as TABLESPACE from information_schema.TABLE where table_schema=’alp’

select a.tablespace_name “表空间名”， total “表空间大小”, free “表空间剩余大小”, (total - free) “表空间使用大小”, total/(102410241024) “表空间大小(G)”, free/(102410241024) “表空间剩余大小(G)”, (total - free)/(102410241024) “表空间使用大小(G)”, round((total - free)/total,4) * 100 “使用率%”, from (select tablespace_name,sum(bytes) free from dba_free_space group by tablespace_name) a, (select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) b

数据库表中不能出现重复记录，每个字段是原子性的不能再分不符合第一范式的示例

存在问题：

关于第一范式，每一行必须唯一，也就是每个表必须有主键，这是我们数据库设计的最基本要求，主要通常采用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利行可能就采用一个字段了。

第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖示例：

确定主键：

以上虽然确定了主键，但此表会出现大量的冗余，主要涉及到的冗余字段为“学生姓名”和“教师姓名”，出现冗余的原因在于，学生姓名部分依赖了主键的一个字段学生编号，而没有依赖教师编号，而教师姓名部门依赖了主键的一个字段教师编号，这就是第二范式部分依赖。解决方案如下：学生信息表

教师信息表

教师和学生的关系表

如果一个表是单一主键，那么它就复合第二范式，部分依赖和主键有关系以上是一种典型的“多对多”的设计

建立在第二范式基础上的，非主键字段不能传递依赖于主键字段。（不要产生传递依赖）

从上表可以看出，班级名称字段存在冗余，因为班级名称字段没有直接依赖于主键，班级名称字段依赖于班级编号，班级编号依赖于学生编号，那么这就是传递依赖，解决的办法是将冗余字段单独拿出来建立表，如：学生信息表

班级信息表

以上设计是一种典型的一对多的设计，一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向一的一方的主键

第一范式：有主键，具有原子性，字段不可分割第二范式：完全依赖，没有部分依赖第三范式：没有传递依赖数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终用目的要满足客户需求。一对一设计，有两种设计方案：第一种设计方案：主键共享第二种设计方案：外键唯一

基本配置：datadir：指定 mysql 的数据目录位置，用于存放 mysql 数据库文件、日志文件等。配置示例：datadir=D:/wamp/mysqldata/Datadefault-character-set：mysql 服务器默认字符集设置。配置示例：default-character-set=utf8skip-grant-tables：当忘记 mysql 用户密码的时候，可以在 mysql 配置文件中配置该参数，跳过权限表验证，不需要密码即可登录 mysql。日志相关：log-error：指定错误日志文件名称，用于记录当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。配置示例：log-error=”WJT-PC.err”（默认在 mysql 数据目录下）log-bin：指定二进制日志文件名称，用于记录对数据造成更改的所有查询语句。配置示例：log-bin=”WJT-PC-bin.log”（默认在 mysql 数据目录下）binlog-do-db：指定将更新记录到二进制日志的数据库，其他所有没有显式指定的数据库更新将被忽略，不记录在日志中。配置示例：binlog-do-db=db_namebinlog-ignore-db：指定不将更新记录到二进制日志的数据库，其他没有显式忽略的数据库都将进行记录。配置示例：binlog-ignore-db=db_name 如果想记录或忽略多个数据库，可以对上面两个选项分别使用多次。sync-binlog：指定多少次写日志后同步磁盘。配置示例：sync-binlog=Ngeneral-log：是否开启查询日志记录。配置示例：general-log=1general_log_file：指定查询日志文件名，用于记录所有的查询语句。配置示例：general_log_file=”WJT-PC.log”（默认在 mysql 数据目录下）slow-query-log：是否开启慢查询日志记录。配置示例：slow-query-log=1slow_query_log_file：指定慢查询日志文件名称，用于记录消耗时间较长的查询语句。配置示例：slow_query_log_file=”WJT-PC-slow.log”（默认在 mysql 数据目录下）long_query_time：设置慢查询的时间，超过这个时间的查询语句才记录日志。配置示例：long_query_time=10（单位：秒）log-slow-admin-statements：是否将慢管理语句（例如 OPTIMIZE TABLE、ANALYZE TABLE 和 ALTER TABLE）写入慢查询日志。存储引擎相关：default-table-type：设置 mysql 的默认存储引擎。innodb_data_home_dir：InnoDB 引擎的共享表空间数据文件根目录。若没有设置，则使用 mysql 的 datadir 目录作为缺省目录。innodb_data_file_path：单独指定共享表空间数据文件的路径与大小。数据文件的完整路径由 innodb_data_home_dir 与这里配置的值组合起来，文件大小以 MB 单位指定。配置示例：innodb_data_home_dir=innodb_data_file_path=ibdata1:12M;/data/mysql/mysql3306/data1/ibdata2:12M:autoextend如果想为 innodb 表空间指定不同目录下的文件，必须指定 innodb_data_home_dir =。这个例子中会在 datadir 下建立 ibdata1，在/data/MySQL/mysql3306/data1/目录下创建 ibdata2。innodb_file_per_table：是否开启独立表空间，若开启，InnoDB 将使用独立的.idb 文件创建新表而不是在共享表空间中创建。配置示例：innodb_file_per_table=1innodb_autoinc_lock_mode：配置在向有着 auto_increment 列的表插入数据时，相关锁的行为。该参数有 3 个取值：0：tradition 传统，所有的 insert 语 句开始的时候得到一个表级的 auto_inc 锁，在语句结束的时候才能释放 这个锁，影响了并发的插入。1：consecutive 连续，mysql 可以一次生成 几个连续的 auto_inc 的值，auto_inc 不需要一直保持到语句结束，只要 语句得到了相应的值后就可以提前释放锁（这也是 mysql 的默认模式）。2：interleaved 交错，这个模式下已经没有了 auto_inc 锁，所以性能是最好的，但是对于同一个语句来说它得到的 auto_inc 的值可能不是连续的。配置示例：innodb_autoinc_lock_mode=1low_priority_updates：在 myisam 引擎锁使用中，默认情况下写请求优先于读请求，可以通过将该参数设置为 1 来使 myisam 引擎给予读请求优先权限， 所有的 insert、update、delete 和 lock table write 语句将等待直到受影响的表没有挂起的 select 或 lock table read。配置示例：low_priority_updates=0（默认配置）max_write_lock_count：当一个 myisam 表的写锁定达到这个值后，mysql 就暂时 将写请求优先级降低，给部分读请求获得锁的机会。innodb_lock_wait_timeout：InnoDB 锁等待超时参数，若事务在该时间内没有获 得需要的锁，则发生回滚。配置示例：innodb_lock_wait_timeout=50（默认 50 秒）max_heap_table_size：设置 memory 表的最大空间大小，该变量可以用来计算 memory 表的 max_rows 值。在已有 memory 表上设置该参数是没有效果 的，除非重建表。查询相关：max_sort_length：配置对 blob 或 text 类型的列进行排序时使用的字节数（只对配置的前 max_sort_length 个字节进行排序，其他的被忽略）max_length_for_sort：mysql 有两种排序算法，两次传输排序和单次传输排序。当查询需要所有列的总长度不超过 max_length_for_sort 时，mysql 使用 单次传输排序，否则使用两次传输排序。optimizer_search_depth：在关联查询中，当需要关联的表数量超过 optimizer_search_depth 的时候，优化器会使用“贪婪”搜索的方式查找“最优”的关联顺序。

方法 1： 用 SET PASSWORD 命令首先登录 MySQL。格式：mysql> set password for 用户名\@localhost = password(‘新密码’);例子：mysql> set password for root\@localhost = password(‘123’);方法 2：用 mysqladmin格式：mysqladmin -u 用户名 -p 旧密码 password 新密码例子：mysqladmin -uroot -p123456 password 123方法 3：用 UPDATE 直接编辑 user 表首先登录 MySQL。mysql> use mysql;mysql> update user set password=password(‘123’) where user=’root’ and host=’localhost’;mysql> flush privileges;方法 4：在忘记 root 密码的时候，可以这样以 windows 为例：