MySql简单入门

      工欲善其事，必先利其器。第一步，当然是在Linux下安装mysql了。

1.在这个mysql链接下载mysql客户端和mysql服务端，我下载的是5.5.60，然后用xftp上传到服务器，我是放到新建的app文件夹下。

2.解压安装
       在xshell中连接好服务器之后，用以下两个命令，先安装服务端，再安装客户端。

rpm -ivh MySQL-server-5.5.60-1.el6.x86_64.rpm

rpm -ivh MySQL-client-5.5.60-1.el6.x86_64.rpm

3.测试是否安装成功

命令行输入：

mysqladmin --version

出现：

表明安装成功！

4.启动、停止、重启mysql

service mysql start//启动
service mysql stop//停止
service mysql restart//重启

5.登录

reboot
重启后登录Mysql：mysql

       有可能登录不上，这里有两种情况，安装过程中给了随机密码(安装过程有提示)，还有就是没有密码，需要重新设置。这里以5.7以下为例：

第一步：跳过密码验证

#vim /etc/my.cnf

在[mysqld]后面任意一行添加skip-grant-tables
用来跳过密码验证。

第二步：重启Mysql

从以下两个选一个

/etc/init.d/mysql restart
/etc/init.d/mysqld restart

第三步：输入mysql
进入Mysql重设密码

mysql> use mysql;
mysql> update mysql.user set authentication_string=password('root') where user='root';
mysql> flush privileges;
mysql> quit

第四步：删掉第一步添加的skip-grant-tables

这时，就可以通过密码登录了：

下面开始mysql的入门学习。

1.操作数据库语句

创建数据库 :  create database [if not exists] 数据库名;


删除数据库 : drop database [if exists] 数据库名;


查看数据库 : show databases;


查看正在使用的数据库：select database();


使用数据库 : use 数据库名;

2.操作数据库中表的语句

创建表

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库


CREATE TABLE IF NOT EXISTS `student` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`address` varchar(100) DEFAULT NULL COMMENT '地址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

建表的时候要注意约束：

primary key:主键
default:默认
not null:非空
unique:唯一
foreign key:外键，实际开发中不会使用，解耦

删除表

语法：DROP TABLE [IF EXISTS] 表名


IF EXISTS为可选 , 判断是否存在该数据表


如删除不存在的数据表会抛出错误

表的增删改查

insert(增)

INSERT INTO 表名 [字段名] VALUES (字段值)
如果不写字段名，字段值要包含所有字段，且一一对应




一次插入多个字段值
INSERT INTO 表名 [字段名] 
VALUES (字段值1),
(字段值2),
...

delete(删)

DELETE FROM 表名 [WHERE 条件表达式]


TRUNCATE TABLE 表名
相当于删除表的结构，再创建一张表

测试：

- 创建一个测试表
CREATE TABLE `test` (
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8


-- 插入几个测试数据
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');

DELETE FROM test;

      再增加数据，主键接着增加。

      如不指定Where则删除该表的所有列数据,自增当前值依然从原来基础上进行,会记录日志。

TRUNCATE TABLE test;

truncate删除数据,自增当前值会恢复到初始值重新开始;不会记录日志。

update(改)

UPDATE 表名 SET 要修改的列名=value 
[WHERE 条件表达式];


一次修改多个列
UPDATE 表名 SET 要修改的列名1,列名2...=value1,value2... 
[WHERE 条件表达式];

select(查)

语句执行顺序：

select    5
  ..
from      1  
  ..
where      2
  ..
group by    3
  ..
having    4
  ..
order by    6

查询所有
SELECT * FROM 表名


查询多个字段
SELECT 字段名1,字段名2,...FROM 表名


别名
SELECT 字段名1 AS 别名,字段名2 AS 别名,...FROM TABLE AS 别名;
AS可以用空格代替，但是不清晰


合并
SELECT CONCAT(a,b)AS 新名字 
FROM TABLE AS 别名;
SELECT CONCAT('姓名：',StudentName) AS 新名字 
FROM TABLE AS 别名;


去重
SELECT DISTINCT 字段名 FROM 表名
distinct只能出现在所有字段的最前面。


WHERE条件语句
比较运算符：>、<、<=、>=、=、<>(不等于)、!=(不等于)
逻辑运算符：and、or、not


聚合函数
MAX
MIN
AVG
COUNT
SUM
IFNULL(列名，默认值)：如果列名为NULL，给个默认值，这样统计的个数就不会遗漏


排序和分页
排序：ORDER BY 
ASC:升序
DESC:降序
分页
语法：limit 开始的索引，每页查询的条数
公式：开始的索引=（当前的页码-1）*每页显示的条数


where 、order by、having
where:分组前将不符合where条件的过滤掉，where后面不能用聚合函数；
group by :当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。
order by:分组；
having：分组后过滤数据，可以使用聚合函数。


模糊查询
关键字：
IN
LIKE
BETWEEN...AND
IS NULL、IS NOT NULL


通配符
%:匹配任意多个字符
_:匹配一个字符


子查询
在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
嵌套查询可由多个子查询组成,求解的方式是由里及外;
子查询返回的结果一般都是集合,故而建议使用IN关键字
子查询结果只要是单列，则在WHERE后面作为条件
子查询结果只要是多列，则在FROM后面作为表进行二次查询

连接查询

内连接：

假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。AB两张表没有主副之分，两张表是平等的。

外连接：

假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

左外连接（左连接）：表示左边的这张表是主表。

右外连接（右连接）：表示右边的这张表是主表。

下面这种是SQL92式的，不建议使用了。

select 
    e.ename,d.dname
  from
    emp e, dept d
  where
    e.deptno = d.deptno;

现在推荐使用SQL99式：

select 
    e.ename,d.dname
  from
    emp e
  join
    dept d
  on
    e.deptno = d.deptno;   

理解这张表，连接查询不会错！

3.数据库设计的三大范式

1NF：原子性，表中每列不可再分。比如，联系方式：邮箱加电话显然不合适，要分成两列。

2NF：在满足第一范式的情况下，表中的每一个字段都完全依赖于主键。不产生局部依赖，每个表只做一件事。比如，一张表里主键是学生证号码，而其中又有借书证名称和借书证号字段，显然借书证名称依赖于借书证，不依赖于主键。

3NF：在满足第二范式的情况下，不产生传递依赖，表中每一列都直接依赖主键，而不是通过其他列间接依赖于主键。
如学号 姓名 年龄 所在学院 学院地点
显然学号(主键)确定了，所在学院就确定了，所在学院确定了，学院地点就确定了。

4.视图

       视图就是站在不同的角度去看到数据。同一张表的数据，通过不同的角度去看待。 

创建视图

 create view myview as select empno,ename from emp;

删除视图

drop view myview;

 注意：只有DQL语句才能以视图对象的方式创建出来。 

       对视图进行增删改查，会影响到原表数据。通过视图影响原表数据的，不是直接操作的原表。可以对视图进行CRUD操作。视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，程序员 只对视图对象进行CRUD。

5.事务

5.1 什么是事务？

      实际开发过程中，一个业务操作需要多次访问数据库，执行多个SQL，把他们看作一个整体，即事务，整个事务的所有SQL执行成功，事务才算成功。事务的存在是为了保证数据的完整性，安全性。

5.2 事务的原理

       事务开启之后，所有的操作都会临时保存到事务日志之中，事务日志只有在得到commit命令才会同步到数据表中，其他任何情况都会清空日志（rollback,断开连接）。所有的查询操作从表中查询，但会经过日志文件加工后才返回。

5.3 事务的提交

       Mysql默认开始自动提交事务，默认每一条DML（增删改）语句都是一个单独的事务，每条语句自动开启一个事务，语句执行结束自动提交事务。

DDL和DML

DML（Data Manipulation Language）数据操作语言-数据库的基本操作，SQL中处理数据等操作统称为数据操纵语言,简而言之就是实现了基本的“增删改查”操作。包括的关键字有：select、update、delete、insert、merge。DML操作是可以手动控制事务的开启、提交和回滚的。开启事务，就是需要手动提交事务。

DDL（Data Definition Language）数据定义语言-用于定义和管理 SQL 数据库中的所有对象的语言，对数据库中的某些对象(例如，database,table)进行管理。包括的关键字有：create、alter、drop、truncate、comment、grant、revoke。DDL操作是隐性提交的，自动提交事务，不能rollback。

控制台查看是否开启自动提交事务：select @@autocommit;

@@表示全局变量，1表示开启，0表示关闭。
取消自动提交事务：`select @@autocommit=0；

手动提交事务：

start transaction;//开启事务


commit;//提交事务


rollback;//回滚事务

5.4 ACID原则

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

一致性(Consistency)：事务必须是使数据库从一个一致性状态变到另一个一致性状态。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

隔离性(Isolation)：主要是针对并发操作，一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性(Durability)：在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

5.5 并发操作引发的问题

    并发操作下，多个用户同时访问同一个数据库，可能引起并发访问的问题：

脏读：一个事务读取了另一个没有提交的事务的数据。

不可重复读(虚读)：在同一个事务内，读取读取表中的数据，表数据已发生改变，分不清到底用哪个数据。update引起。
我开启了一个事务，9点开启了一个查询，屏幕在那放着呢，10点别人把表改了(update)，我的查询结果也跟着变，分不清到底哪个是哪个。就是我不能重复查询9点钟那个表的状态了。

幻读：在同一个事务内，读取到了别人插入的数据，导致前后读取结果不一致。delete或者insert引起。我查询表中没有id为10的数据，想要插入，结果一插入，告诉我存在，别人插的，让我感觉到出现了幻觉。

5.6 事务的隔离级别

      Mysql有四种隔离级别：读未提交、读已提交、可重复读、串行化。四种隔离级别依次提高，性能随之越差，安全性越高。
read uncommitted->read committed->repeatable read(MYSQL)->serializable(串行化)。

第一级别：读未提交（read uncommitted） 对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。 读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。 

第二级别：读已提交（read committed） 对方事务提交之后的数据我方可以读取到。 这种隔离级别解决了: 脏读现象没有了。 读已提交存在的问题是：不可重复读(虚读)。 

第三级别：可重复读（repeatable read） 这种隔离级别解决了：不可重复读问题。 这种隔离级别存在的问题是：读取到的数据是幻象。 

第四级别：序列化读/串行化读（serializable） 解决了所有问题。 效率低。需要事务排队。

       Mysql默认第三级别。

6.Mysql存储引擎

查询数据库支持哪些引擎：

show engines \G;

了解以下两种常见的存储引擎:

InnoDB(mysql默认)：支持事务，（适合高并发操作，行锁)，外键等，这种存储引擎数据的安全得到保障。表的结构存储在xxx.frm文件中。数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。

       此外，InnoDB支持级联删除和级联更新。

MyISAM:性能优先，表锁，不支持事务。是mysql最常用的存储引擎，但是这种引擎不是默认的。采用三个文件组织一张表：

             xxx.frm（存储格式的文件）

             xxx.MYD（存储表中数据的文件）

             xxx.MYI（存储表中索引的文件）

优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。

缺点：不支持事务。

7.索引优化

7.1 索引基本知识

索引：帮助Mysql高效获取数据的数据结构。

优点：1.提高查询效率，降低IO使用率‘2.降低 CPU使用率

缺点：1.索引本身很大2.索引不是所有情况都适用：a.少量数据 b.频繁更新的字段 c.很少使用的字段。3.降低增删改的效率。

索引分类：

主键索引 (Primary Key)：某一个属性组能唯一标识一条记录，是特殊的单值索引

唯一索引 (Unique)：避免同一个表中某数据列中的值重复

常规索引 (Index)：快速定位特定数据，单值索引，可以有多个单值索引

复合索引(Index)：多个列构成的索引，遵循靠左原则

全文索引 (FullText)：快速定位特定数据

注意：如果一个字段是primary key,则该字段默认就是主键索引。

添加索引：
方式一：

create 索引类型 索引名 on 表(字段)

例：

单值索引：create index dept_index on tb(dept); 
唯一索引：create unique index name_index on tb(name); 
复合索引：create index dept_name_index on tb(dept,name);

方式二：

alter table 表名 add 索引类型 索引名(字段)
alter table tb add index dept_index(dept)

删除索引：

drop index 索引名 on 表名
drop index dept_index on db;

查询索引：

show index from 表名;
或者
show index from 表名 \G;

7.2 性能问题

a.分析SQL的执行计算 ：explain可以模拟SQL优化器执行SQL语句
b.Mysql查询优化会干扰优化

explain select * from tb；

显示10个字段：id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。

(1)id

id相同时，值越大越优先查询；
id不同时，从上到下按顺序查询。

(2)select_type(查询类型)

PRIMARY:包含子查询的主查询。

SUBQUERY：包含子查询SQL中的子查询。

simple:简单查询，不包含子查询，union。

derived:衍生查询，使用了临时表

union:由union操作联合而成的单位select查询中，除第一个外，第二个以后的所有单位select查询的select_type都为union。union的第一个单位select的select_type不是union，而是DERIVED。它是一个临时表，用于存储联合（Union）后的查询结果。

union result:告知开发人员，存在union查询的表。

(3)type:索引类型、类型

system>const>eq_ref>ref>range>index>all

system、const只是理想情况，实际能达到ref、range

system:只有一条数据的系统表；或衍生表只有一条数据的主查询。

const:仅仅能查到一条数据的SQL，用于Primary key 或unique索引（类型与索引类型有关）

eq_ref:唯一性索引，对于每个索引键的查询，返回匹配唯一行数据，有且只能有一个。

ref:非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）

range:检索指定范围的行，where后面是一个范围查询(between,>,<,>=,特殊：in有时会失效)

index:查询全部索引中数据

all:查询全部表中的数据

总结：
system/const:结果只有一条数据
eq_ref:结果多条，但每条数据是唯一的
ref:结果多条，但每条数据是0或者多条

(4)possible_keys

可能用到的索引，是一种预测，不准确。

(5)key

实际使用到的索引。

如果possible_keys、key是NULL，说明没用到索引。

(6)key_len

索引的长度，用于判断复合索引是否被完全使用。

utf8中，一个字符占3字节；如果索引字段可以为NULL，则会使用一个字节用于标识；用两个字符标识可变长度(varchar)。

(7)ref

注意与type中的ref值区分，指明当前表所参照的字段。

(8)rows

被索引优化查询的数据个数(实际通过索引而查询到的数据个数)

(9)Extra

a.using filesort:性能消耗巨大，需要额外的一次排序(先查询，再排序)

对于单索引，如果排序和查找是同一个字段，则不会出现using filesort;如果排序和查找不是同一个字段，则会出现using filesort。

避免：where哪些字段，就order by 哪些字段。

对于复合索引，不能跨列(最佳左前缀）

避免：where和order by 按照复合索引的顺序使用，不要跨列或无序使用。

b.usingtemporary:性能损耗大，使用了临时表，一般出现在group by语句中。

避免：查询哪些列，就根据那些列group by

c.using index:性能提升，索引覆盖，不回表查询。不读取原文件，只从索引文件中获取数据。如果用到了索引覆盖，会对possible_key和key造成影响：(1)如果没有where，则索引只出现在key中;(2)如果有where，则索引出现在key和possible_key中。

d.using where:需要回表查询

e.impossible where:where子句永远为false

7.3索引优化

索引加在哪张表？建在哪个位置？

小表驱动大表，数据量小的表，使用频繁(经常查询，不经常增删改)的字段。

避免索引失效的原则

(1)复合索引

a.复合索引，不要跨列或无序使用(最佳左前缀原则)
b.复合索引，尽量使用全索引匹配。

(2)不要在索引上进行任何操作(计算、函数、类型转换)，否则索引失效

如果是a,b,c是复合索引，b进行操作从而失效进一步会影响c。

(3)复合索引不能使用不等于(!= <>)或is null(is not null),否则自身及右侧索引全部失效

SQL优化是一种概率层面的优化，实际是否使用优化，需要explain推测。

(4)补救，尽量使用索引覆盖。

(5)like 尽量以常量开头，不要以‘%’开头，否则索引失效。

如果必须使用’%x%'进行模糊查询，可以使用索引覆盖，挽救一部分。

(6)尽量不要使用类型转换(显示、隐式)，否则索引失效。

(7）尽量不要使用or，否则引起失效

使用or甚至会导致or左侧的索引失效。

索引优化的方法

1.exist和in

如果主查询的数据集大，则用in;如果子查询的数据集大，则用exist。

exist语法：将主查询的结果，放到子查询结果中进行条件校验(看子查询是否有数据)，如果符合校验，则保留数据。

select tname from teacher where exists(select  * from teacher);

2.order by优化

using filesort 有两种算法(根据IO的次数)：

双路排序：扫描两次磁盘，第一次，从磁盘读取排序字段，对排序字段在buffer(mysql自带)中排序；第二次，读取其他字段。

单路排序：只读取一次(全部字段），在buffer中排序。但有一定的隐患，不一定是真的单路排序，有可能多次IO。原因：如果数据量特别大，无法将所有字段的数据一次性读取完毕，因此会进行“分片读取，多次读取”。

单路排序时，可以考虑调整buffer的大小：

set max_length_for_sort_date =1024;//单位byte

如果max_length_for_sort_dat定义的字节数太小，mysql会自动从单路调整到双路。

提高order by 查询的策略：

a.选择使用单路、双路排序；调整buffer的大小
b.避免使用select *,不利于索引覆盖
c.复合索引不要跨列使用
d.尽量保证全部的排序字段排序的一致性

7.4 SQL排序-慢查询日志

MYSQL提供的一种日志记录，用于记录MYSQL响应时间超过阈值的SQL语句。

检查是否开启了慢查询日志：

show variables like "%slow_query_log%";

可以临时开启、永久开启。

也可以更改阈值。

8.锁机制

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

8.1 共享锁与排他锁

共享锁（读锁）：其他事务可以读，但不能写。
排他锁（写锁） ：其他事务不能读取，也不能写。

8.2 不同粒度锁的比较

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。最大程度的支持并发，同时也带来了最大的锁开销。
       在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。行级锁只在存储引擎层实现，而Mysql服务器层没有实现。行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

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

举例说明：

会话0给A表加了read锁，其他会话的操作：

a.可以对其他表(A表以外的表)进行操作；
b.对A表，读可以，写需要等待释放。

会话0给A表加了write锁，当前会话可以对加了写锁的表进行任何操作(增删改查);其他会话只有等当前会话释放写锁才可以进行增删改查操作。

8.3 死锁

       死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。

       锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

8.4 MyISM锁模式

MyISM执行查询时会自动给相关表加读锁；执行增删改时会自动加写锁。

8.5 行锁

a.如果没有索引，则行锁会转为表锁
b.行锁的一种特殊情况：间隙锁。值在范围内，但却不存在。

8.6 乐观锁和悲观锁

乐观锁(Optimistic Lock)：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁不能解决脏读的问题。
乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

悲观锁(Pessimistic Lock)：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
悲观锁，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

        到此为止，关于mysql的入门就结束了，更多更深入的知识，需要实际开发中慢慢深入理解！