全栈冲刺 之 一天速成MySQL

一、为什么使用数据库

数据储存在哪里？

硬盘、网盘、U盘、光盘、内存（临时存储）

数据持久化

使用文件来进行存储，数据库也是一种文件，像excel ，xml 这些都可以进行数据的存储，但大量数据操作，还是用数据库文件效率最高

什么是数据库

数据库就是存储数据的“仓库”

数据库的作用

1.存储大量的数据，访问和检索数据（管理数据）

2.保证数据的完整性

3.安全与共享

4.数据可以进行组合，产生新的数据（数据分析）

数据库的发展历史

1.层次模型：单个记录以父子关系形成的树状结构树创建了一个层次状结构，在其中数据被分解为逻辑的分类和子类，使用记录代表逻辑数据单元。

2.网状模型：网状模型通过允许记录有多个父子关系，来增强层次模型

3.关系模型：独立于应用程序的。更改数据库设计，而不会影响应用程序成为可能表的行和列这种结构，来替换父子框架。使用关系模型，我们可以克服早期模型的缺陷，在表之间定义复杂的关系。

表

表（table）是关系型数据库的核心单元，它是数据存储的地方

术语：

        行： 记录，实体

        列：字段

关系：（表与表之间的关系）

1对1 ：一对一关系是比较少见的关系类型。很多数据库也很少包含一对一关系。

A 表中的一行最多只能匹配于 B 表中的一行，反之亦然。

人 身份证号

1 1

1 1

---

1 : 1

1对多: 最常用的关系类型是一对多关系，通常把一对多关系中，“多边”的表称为从表，把“一边”的表称为主表。

人 银行卡

1 N

1 1

---

1 ： N

多对多：

学生 老师

1 N

N 1

---

N : N

1. 非关系模型（noSQL）

关系型数据库

关系型数据库有哪些？

Access ，Mysql (小型企业， 免费)，Sql Server(微软 ,中企) ， Oracle (大型) , DB2 , SyBase 等..

数据库（Database）

DBMS(Database Manage System)

RDBMS( Relation Database Manage System)

 

数据库是存储和管理数据的仓库，但数据库并不能直接存储数据，数据是存储在表中的。

在存储数据的过程中一定会用到数据库服务器，所谓的数据库服务器就是指在计算机上安装一个数据库管理程序。

关系与非关系数据库的对比

关系型数据库的优势:

1.复杂查询可以用SQL语句方便的在- -个表以及多个表之间做非常复杂的数据查询。

2.事务支持使得对于安全性能很高的数据访问要求得以实现。对于这两类数据库，对方的优势就是自己的弱势，反之亦然。

非关系型数据库的优势:。

1.性能NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。

2.可扩展性同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

MySQL数据库

发展史

2003年12月，MySQL 5.0版本发布

2008年1月，MySQL AB公司被Sun公司以10亿美金收购

2008年11月，MySQL 5.1发布.

2009年4月，Oracle公 司以74亿美元收购Sun公司

2010年12月，MySQL 5.5发布

详细内容:

1、MySQL的历史可以追溯到1979年，一个名为Monty Widenius的程序员在为TcX的小公司打工，并且用BASIC设计了一个报表工具，使其可以在4MHz主频和16KB内存的计算机上运行。当时，这只是一个很底层的且仅面向报表的存储引擎，名叫Unireg。

　　2、1990年，TcX公司的客户中开始有人要求为他的API提供SQL支持。Monty直接借助于mSQL的代码，将它集成到自己的存储引擎中。令人失望的是，效果并不太令人满意，决心自己重写一个SQL支持。

　　3、　1996年，MySQL 1.0发布，它只面向一小拨人，相当于内部发布。到了1996年10月，MySQL 3.11.1发布(MySQL没有2.x版本)，最开始只提供Solaris下的二进制版本。一个月后，Linux版本出现了。在接下来的两年里，MySQL被依次移植到各个平台。

　　4、1999～2000年，MySQL AB公司在瑞典成立。Monty雇了几个人与Sleepycat合作，开发出了Berkeley DB引擎, 由于BDB支持事务处理，因此MySQL从此开始支持事务处理了。

　　5、2000，MySQL不仅公布自己的源代码，并采用GPL(GNU General Public License)许可协议，正式进入开源世界。同年4月，MySQL对旧的存储引擎ISAM进行了整理，将其命名为MyISAM。

　　6、2001年，集成Heikki Tuuri的存储引擎InnoDB，这个引擎不仅能持事务处理，并且支持行级锁。后来该引擎被证明是最为成功的MySQL事务存储引擎。MySQL与InnoDB的正式结合版本是4.0

　　7、2003年12月，MySQL 5.0版本发布，提供了视图、存储过程等功能。

　　8、2008年1月，MySQL AB公司被Sun公司以10亿美金收购，MySQL数据库进入Sun时代。在Sun时代，Sun公司对其进行了大量的推广、优化、Bug修复等工作。

　　9、2008年11月，MySQL 5.1发布，它提供了分区、事件管理，以及基于行的复制和基于磁盘的NDB集群系统，同时修复了大量的Bug。

　　10、2009年4月，Oracle公司以74亿美元收购Sun公司，自此MySQL数据库进入Oracle时代，而其第三方的存储引擎InnoDB早在2005年就被Oracle公司收购。

　　11、2010年12月，MySQL 5.5发布，其主要新特性包括半同步的复制及对SIGNAL/RESIGNAL的异常处理功能的支持，最重要的是InnoDB存储引擎终于变为当前MySQL的默认存储引擎。MySQL 5.5不是时隔两年后的一次简单的版本更新，而是加强了MySQL各个方面在企业级的特性。Oracle公司同时也承诺MySQL 5.5和未来版本仍是采用GPL授权的开源产品。

….

MySql 8.0 新版本。

一般使用的都是Mysql 5.x；

MySQL特点

安装简单，部署迅速,

适合大批量快速部署

易于扩展，扩展性能极佳

架构灵活，可以根据业务特点配置适合自己的MySQL集群

开源，可以根据自己的业务需求进行二次开发,

使用广泛，几乎所有的互联网公司都在使用MySQL数据库

对于OLTP业务，可以进行良好的支撑

SQL：

SQL(Structured Query Language)语言是1974年由Boyce和Chamberlin提出的一种介于关系代数与关系演算之间的结构化查询语言，是一个通用的、功能极强的关系型数据库语言。

SQL语句

–数据定义语言（DDL）:data definition language (定义数据库，定义表)

–数据查询语言（DQL）: data query language （查询 数据）

–数据操作语言（DML）: data manipulation language （操作数据）

–数据控制语言（DCL）: data control language （用户控制 ，权限控制 ）

DDL 定义数据库及表

1. 创建数据库
   create database 数据库名;
   
2. 使用数据库
   use database 数据库名;
   
3. 删除数据库 
   drop database 数据库名；
   
   
 数据库命名规范：
 1. 标识符不能是所用RDBMS的保留字
 2. 不允许嵌入空格或其它特殊字符
 3. 第一个字符必须是下列字符之一：a-z 和 A-Z，下划线 (_)、at 符号 (@) 或者数字符号 (#)
 4. 后续字符可以是at符号（@）、美元符号 ($)、数字符号或下划线,字母。
 
 
 4. 创建表 (数据是放在表中)
   语法：
       create table 表名 (列名 列的数据类型  [列的约束] ,  …)
       
       注: char , varchar 两个类型，需要设置长度，其它的类型都不需要设置
       
       
   方法2：复制表（只能复制表的数据与表字段，注：表的约束不能复制）
      CREATE TABLE student2 SELECT * FROM student
       
5. 删除表
     drop table 表名;
     
6. 修改表

    增加列
        alter table 表名 add 列名 数据类型
        
        ALTER TABLE student ADD address VARCHAR(100)
        
    删除列
        alter table 表名 drop 列名
        
    更改列
        alter table 表名 modify 列名 类型   —  只改列的类型 
        alter table 表名 change 旧列名 新列名 类型
        
    重命名表
        alter table 旧表名 rename 新表名

mysql中的数据类型

1. 数字

   A- 整型 int 4字节

   B- 浮点 float 4字节 7位

   double 8 字节 15位

   decimal(总位数，小数点后的位数) decimal(M,D) 17字节， 30位小数

   注意：

   M为总位数，D为小数位，M必须大于D

2. 字符串

   A- 固定长度字符串 char(长度）

   B- 可变长度字符串 varchar(最大的长度)

   C- 超长字符串 text

nchar , nvarchar , ntext 前面加n, 说明支持unicode编码，即可以显示中文

一般不会去使用n开头的类型，为什么呢？ 因为在创建数据库时，就已经指定了编码，支持中文显示

3. 日期：

A: date 年月日 yyyy – MM – dd

B： datetime 年月日 时分秒 yyyy-MM-dd HH:mm:ss

扩展数据类型：

text : 存放超长文本

blob: 二进制

enum: 枚举

数据完整性分类

什么是数据的完整性？

        完整性 = 准确性 + 可靠性

Q： 如何保证数据的完整性？

数据完整性(约束)的分类

–1.实体完整性 主键约束 、 唯一约束、 标识列

• 保证 一 行数据是有效的

主键约束 ： primary key

唯一约束 ： unique

标识列 : 系统给一个自增长的值，这个值不会重复，一般默认从1开始，每次加1(步长) auto_increment

–2. 域完整性 非空约束：Not Null 默认约束：Default（定义与实体时都用default）

• 保证 一 列数据是有效的

–3. 引用完整性 外键约束

• 保证引用的编号是有效的

–4.用户自定义完整性

• 保证自定义规则

DML数据操作语言

表的curd:

CURD 操作，即指对数据库中实体对象的增Create、改Update、查Read、删Delete操作。

增加数据 :

INSERT [INTO] 表名[(列名,…)] VALUES (值表达式,…)

— 注： 如果所有的列都要插入数据， 表名后面的列名可以省略

INSERT [INTO] 表名SET 列名=值表达式, …

INSERT [INTO] 表名1[(列名,…)] SELECT {*|列名,…} FROM表名2

— 上面这种方式添加数据，要求查询的列与插入的列，数量与数据类型要一致

注意：

在MySQL3.0.2 之后 into关键字在INSERT中是可以省略的，但是基于标准考虑建议大家在书写时保留该关键字

修改数据：

UPDATE 表名 SET 列名 = 更新后的值, [WHERE 条件子句]

删除数据：

方法1 :

DELETE FROM 表名 [WHERE 子句]

方法2：

TRUNCATE TABLE 表名

DELETE会记录日志，意味着删除后的数据还可以恢复，但是效率低。

TRUNCATE不会记录日志，删除后的数据不能恢复，但是效率高。TRUNCATE不能用于有外键约束引用的表 。

TRUNCATE 可以重置自增列， delete 不能重置自增列

TRUNCATE 只能删除整表，不能删除部分

DQL:

查询数据:

select * from 表名 — 所有列

select 列名, 列名2 … from 表名 — 所有列

数据完整性分类

–1.实体完整性 主键约束 、 唯一约束、 标识列

•保证一行数据是有效的

–2.域完整性 非空约束：Not Null 默认约束：Default（定义与实体时都用default）， check约束

•保证一列数据是有效的

–3.引用完整性 外键约束

-4. 自定义完整性

引用完整性( 外键约束 )

外键: 一个表某列与另一个表的某列存在着依附关系（这关系是在设计这两个表时根据业务去创建, 但是并没有强制依赖）

外键约束: 给外键加上一个强制的约束，如果违反这个约束，就报错，不允许去修改数据

（没有建立外键约束，不等于没有外键）

如何添加外键约束呢？

1. 了解两个概念， 主表，从表

   创建外键约束时，必须先创建主表，再创建从表

2. 创建约束

   CONSTRAINT FOREIGN KEY(s_c_id) REFERENCES t_class(c_id)

— 添加从表数据，依赖的主表数据一定是存在 — 删除主表数据，必须保证从表没有对它的引用

问题？ 项目中要不要加外键约束

不要，用代码去保证数据的完整性； 为什么不加，让数据操作更方便

DQL 数据查询语言

查询的机制

从表的第一开始，把整个数据都进行查询 ，返回符合条件的数据； 返回的数据也表格形式，但是临时的，不会进行保存

简单查询

投影操作

select * from 表名 — 全部列

select 列名1， 列名2 from 表名 — 部分列

select 列名1 as 别名 from 表名 — 部分列且改变列名

select 列名1+5 from 表名 ；– 计算列

select distinct 列名1 from 表名 — 去重复

select 列名 from 表名 limit 开始行数 ，返回的行数 — 序号从0开始

选择操作

单条件：

select * from 表名 where 列名 = 值; — 还有 >= , <= ,!=

多条件：

and , or 进行条件 的连接

select * from 表名 where 条件1 （and 或 or ） 条件2

条件范围：

between… and …

select * from 表名 where 列名 between 下限 and 上限

in, not in

select 列集合 from 表名 where in / not in (值列表)

like 模糊查询

select 列集合 from 表名 where 列名 like 模式

通配符：

—： 任何单个字符

%：0个或多个任意字符

处理空值：

select 列集合 from 表名 where 列名 is null / is not null

排序操作

单列排序：

select 列名 from 表名 order by 列名

多列排序：

select 列名 from 表名 order by 列名1， 列名2

SQL的执行顺序

from – where – select – order by – limit

聚合函数

count

avg

max

min

sum

注：只返回单个值，只对不为null的数据进行统计

分组函数

select

from

where

group by

having

order by

SQL执行顺序：

from -> where -> select -> group by -> having -> order by -> limit

分组统一

group by 列名 — 根据指定的列名分组

having 分组后进行条件筛选

需求：学生的平均成绩

每个组的平均成绩（group by 组名, 性别）

分组函数

select

from

where

group by

having

order by

limit

SQL执行顺序：

from -> where -> select -> group by -> having -> order by -> limit

子查询

一个查询语句，在嵌套一个查询语句，这就嵌套查询 语句就叫，子查询 .

如下：关键字后面，都可以嵌套一个子查询

select

from

where

group by

having

order by

limit

select 子查询 (单行单列) – 效率极低，一般不用

SELECT *,(SELECT className FROM clazz WHERE id=student2.classId) AS className FROM student2

from 子查询 (多行多列)

 SELECT *
  FROM ( SELECT * FROM student2 WHERE classId = 3) AS a WHERE StudentName LIKE ‘张%’

where 子查询

关系表达式后( = ,> ,<, !=)，返回的是单行单列

 SELECT * FROM student2 WHERE classId = ( SELECT id FROM clazz WHERE className=’352′)

 in/not in , 返回的多行，单列

表联接

笛卡尔乘积

SELECT * FROM student2,clazz WHERE student2.classId = clazz.Id  — 默认显示笛卡尔乘积
 — 写法， 表联接 
 
 SELECT * FROM student2 JOIN clazz ON student2.classId = clazz.Id

多表连接

SELECT * FROM student3 stu JOIN score s ON stu.id = s.studentId
                                 JOIN course c ON s.courceId = c.id
                                 JOIN teacher t ON c.teacherId = t.id

表联接分类：

内联接：

inner join -> join

外联接:

左外联接 outer left join -> left join

右外联接： outer right join -> right join

存储引擎

MySQL数据库及其分支版本主要的存储引擎有InnoDB、MyISAM、 Memory等。

简单地理解，存储引擎就是指表的类型以及表在计算机上的存储方式。

存储引擎的概念是MySQL的特色，使用的是一个可插拔存储引擎架构，能够在运行的时候动态加载或者卸载这些存储引擎。

不同的存储引擎决定了MySQL数据库中的表可以用不同的方式来存储。我们可以根据数据的特点来选择不同的存储引擎。

MySQL支持哪些存储引擎?

MySQL支持多种存储引擎,比如InnoDB,MyISAM,Memory,Archive等等.在大多数的情况下,直接选择使用InnoDB引擎都是最合适的,InnoDB也是MySQL的默认存储引擎.

1. InnoDB和MyISAM有什么区别?

• InnoDB支持事物，而MyISAM不支持事物

• InnoDB支持行级锁，而MyISAM支持表级锁

• InnoDB支持MVCC, 而MyISAM不支持

• InnoDB支持外键，而MyISAM不支持

• InnoDB不支持全文索引，而MyISAM支持。

事务

事务：就是把多个数据库操作打包成一个“不可分割”的整体来进行执行，同时在这些操作执行后，需要保证数据可靠且一致

创建事务：

start transcation ; — 开启事务 // begin

commit ; — 提交

callback — 回滚

ACID模型

ACID模型，就是我们常说的数据库事务四大特性：原子性，一致性，隔离性，持久性；由这四种特性保证了事务的可靠和一致

一致性是由原子性，隔离性，持久性共同保证的

原子性： 需要向原子一样不可分割，操作要么全部成功，要么全部失败；

如何实现： Innodb 提供了 undo log 机制

隔离性：

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，

并发执行的各个事务之间不能互相干扰。

分为：写与写隔离，写和读隔离

面试题

第一题：一张自增表里面总共有7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

8

第二题：如何获取当前数据库版本？

MySQL的两种方式：

在cmd中输入mysql -V查看；注意：需要在对应的目录下执行；

在MySQL的命令窗口[Command Line Client]中输入：select version(); [函数]；或者在Navicat或其他的工具中创建查询输入select version();

Oracle：

select * from v$version;

select * from product_component_version;

第三题：说一下ACID是什么？

① 原子性(Atomicity,或称不可分割性)、

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

② 一致性(Consistency)、

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

③ 隔离性(Isolation,又称独立性)、

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

④ 持久性(Durability)。

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

第四题：mysql 的内连接、左连接、右连接有什么区别？

内连接：只显示所有有关联的数据，左表或右表没有的则不显示；

左连接：显示左表的所有数据，右表没有的用null补全；

右连接：显示右表的所有数据，左表没有的用null补全；

第五题：mysql 索引是怎么实现的？

MySQL采用B+ Tree实现索引；

B+ Tree的特点：

单节点能存储更多的数据，减少磁盘IO次数；

叶子节点形成有序链表，便于执行范围操作(如：where id < 10)；

聚集索引，叶子节点存储数据，而非聚集索引，叶子节点存储数据的地址；

说一下数据库的事务隔离？

第六题：事务的隔离级别?

第一类丢失更新：

第二类丢失更新：当两个或多个事务更新同一行时，都是基于最初选定的值更新，由于事务是相互隔离的，所以最后一个事物的更新覆盖其他事务的更新；

脏读：事务A读取了事务B还没有提交的数据(读未提交)；

不可重复读：在一个事务内多次读取了某个数据，而读取出来的数据不一致(数据发生改变或被删除)；

幻读：在一个事务内的操作中发现了未被操作的数据；幻读出现的前提是并发的事务中有事务发生了插入、删除操作；

事务的隔离级别越高，在并发下出现的上述五种问题越少，但同时付出的性能开销也越大(并发和性能之间的权衡)；

DEFAULT默认隔离级别：

每种数据库支持的数据隔离级别不一样；

MySql可以使用select @@(global.(系统级别)|session级别)tx_isolation查看默认的事务隔离级别(REPEATABLE-READ)；

Spring将事务隔离级别设置为DEFAULT表示使用底层数据库的默认事务隔离级别；

READ-UNCOMMITTED读未提交：

能读取到未被提交的数据，无法解决脏读、不可重复读及幻读；

READ-COMMITTED读已提交：

能读取到已经提交的数据，能防止脏读，但是不能防止不可重复读和幻读；

REPEATABLE-READ重复读取：

在读取完数据之后加锁，类似于”select * from XXX for update”，明确表示读取数据为了更新；REPEATABLE-READ读取一条数据，事务不结束，别的事务就不可以修改这条数据，解决了脏读、不可重复读，但是不能解决幻读；

SERLALIZABLE串行读：

最高事务隔离级别，一个事务(包括子事务)接一个事务的执行，解决了幻读问题；

注意：Oracle只支持Read Committed和Serializable和自定义的Read Only隔离级别；

常用命令：

修改隔离级别：set tx_isolation = “四种事务隔离级别”;

事务传播特性(面试) 7种传播特性

概念：当事务方法被另外一个事务方法调用时，指定事务应该如何传播；

传播属性(默认REQUIRED)：

REQUIRED：事务内所有的子事务都成功执行结束后(都不抛出异常)才提交数据；

REQUIRED_NEW：事务内所有的子事务都是独立事务，执行一个挂起一个(事务完成一个提交一个，抛出异常的不提交并结束整个事务)；不管是否存在事务，都new一个新的事务，将原来的事务挂起，新的事务执行完毕后再执行老的事务；

SUPPORTS：如果有正在运行的事务，则在该事务内运行，否则可不运行在事务中；

NOT_SUPPORTS：当前方法不运行在事务中，如果有正在运行的事务，则将该事务挂起(不开启事务)；

MANDATORY(强制)：当前方法必须运行在事务中，如果没有正在运行的事务，则抛出异常；

NERVER：当前方法不运行在事务中，如果有运行的事务，则抛出异常；

NESTED(嵌套)：如果有事务在运行，当前事务方法嵌套到运行事务中运行，否则开启一个新事务运行；

注：MySql中索引是INNODB，才支持事务；MYISAM不支持事务；

第七题:说一下mysql 的行锁和表锁？

表锁：

特点：加锁快，开销小；不会出现死锁(一次性获取全部锁)；发生锁冲突的概率高，并发低；

使用表锁的场景：

场景一：事务需要更新大部分或全部表数据；

场景二：事务需要使用多张表，可能会引起死锁，造成大量事务回滚；

行锁：

共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；

排他锁：允许获取排他锁的更新数据，阻止其他事务获取相同数据集的共享读锁和排他写锁；

特点：开销大，加锁慢；可能出现死锁；发生锁冲突的概率低，并发最高；

注：InnoDB行锁是通过索引上的索引项实现的，而Oracle则是给数据行加锁；InnoDB只有通过索引条件检索数据，才会使用行锁，否则使用表锁；

注：表锁适用于并发度不高，以查询为主的小型web应用；而行锁适用于高并发，对事务完整性要求较高的系统；

第八题:如何做mysql的性能优化？(重要)

①用PreparedStatement一般来说比Statement性能高；

②有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，在设计数据库时就去掉外键；

看mysql帮助文档子查询章节的最后部分，下面的子查询语句要比第二条关联查询效率高：

select e.name,e.salary where e.managerid=(select id from employee where name=’zxx‘);

select e.name,e.salary,m.name,m.salary from employees e,employees m where

e.managerid = m.id and m.name=’zxx‘;

③表中允许适当冗余；

④sql语句全部大写，特别是列名和表名都大写。

⑤开启缓存查询(使用变量代替函数)；根据缓存的特点，不要拼凑条件，而是用?和PreparedStatment；

⑥对频繁查询的字段建立索引；查询时尽量使用主键作为条件；

⑦对于明确知道查询结果只有一条数据的使用limit 1；

⑧不要使用select *，需要什么字段就查询什么字段；

⑨表设计时尽量用固定长度的类型如char；

⑩垂直分割：将表中的常用列和非常用列垂直拆分成多张表存储；如：将姓名和密码单独从用户表中独立出来。

使用expain关键字可以查看性能；

批量插入(batch insert，group commit)