MySQL1---理论概述：DB,DBMS，SQL、数据库设计三范式、常见数据类型、事务、存储引擎、锁等

一、DB、DBMS和SQL

1、DB（DataBase）： 数据库

2、DBMS（DataBase Management System）：数据库管理系统

                   常见：MySQL Oracle DB2 Sybase SqlServer

3、SQL：结构化查询语言，是一门标准通用的高级语言（先编译，再执行sql语句）

                标准的SQL适合所有数据库产品

                sql语句的编译由DBMS完成

             

二、数据库设计三范式

1、什么是设计范式

    设计表的依据

    按照这个三范式设计的表可避免出现数据冗余

2、三范式

（1）第一范式：任何一张表都应该由主键，并且每个字段原子性不可再分【主键】

（2）第二范式：所有非主键字段完全依赖主键，不能产生部分依赖【依赖】

（3）第三范式：所有非主键字段直接依赖主键，不能产生传递依赖【依赖不传递】

[注]在实际开发中，以满足客户的需求为住，有的时候会拿冗余换执行速度

     如：班级表和学生表用一张表展示，虽然冗余，但是查询速度快（表连接越多，查询效率越低）

3、二三范式常见表设计

（1）二范式

      多对多 ，三张表 ，关系表两外键

     老师和学生：一个老师对应多个学生，一个学生对应多个老师

（2）三范式

    一对多，两张表，多的表加外键

   班级和学生表：一个班级对应多个学生（学生表加外键）

（3）一对一

      方案一：主键共享（id既是主键又是外键）

      方案二：外键唯一（外键不能重复，只能和主键一一对应）

三、常见数据类型

int                整数型（java中int）

bigint           长整型（Java中long）

float             浮点型（Java中float double）

char             定长字符串（String）

varchar        可变长字符串（StringBuffer/StringBuilder）

date             日期型（对应Java中的java.sql.Date类型）

BLOB           二进制大对象（存储图片、视频等流媒体信息）Binary Large OBject（对应java中obbject）可放路径

CLOB           字符大对象（存储较大文本，可以存储4G的字符串，小说）Character Large OBject（对应java中object）

【注】 char和varchar

 char（6） ---定长

                   无论输入几个字符，都分配6个空间----适合于数据长度不发生改变的字段，定长的：                      性别，生日（11，19）

   varchar（6）---非定长

                   根据输入的字符个数动态分配空间---适合于数据长度不确定的字段，非定长：

                   简介，姓名

四、事务

1、事务特性 ACID   

A:原子性---事务必须保证多条DML语句同时成功或者同时失败

C：一致性--类能量守恒，事务执行后数据库与其他业务规则保持一致，

                    如转账，无论事务执行成功与否，参与转账的两个账号的余额之和是不变的

I：隔离性--事务A与事务B之间具有隔离，（同步进行）

D：持久性--最终数据必须持久化到硬盘文件中，事务才算成功的结束

2、隔离级别

（1）读未提交（read uncommitted）

   <1>特点

        对方事务还没有提交，我们当前事务就可以读取到对方未提交的数据

 <2>缺点：存在脏读现象（dirty read）

                  标识读到了脏的数据

2、 读已提交（read committed）

  <1>特点

        对方事务提交之后的数据，我方才可以读取到

 <2>优点：解决了脏读问题

       缺点： 不可重读

3、可重复读（repeatable read）

  <1>特点

         提交之后的数据无法读取到，只能读到开启事务时候的初始数据

          如：一开始我开启事务时，员工表有14条数据，我读到了这14条，后面你将这14条删除了，提交了，我读到的仍然是这14条数据

 <2>优点：解决了不可重复读的问题

        缺点：幻读，读取到的数据是幻象，

                  事务前后两次查询同一范围内数据，后一次查询看到了前一次查询没有看到的新数据行

4、序列化读/串行化读

         一个事务提交后（commit），另一个事务才能读取到。

         解决了所有问题，但效率低，需要排队

【注】

1、幻读---可重复读产生

   解决：序列化读--加锁，效率较慢，适用于没有并发场景

              MVCC--多版本并发控制，InnoDB，避免加锁，通过事务版本号控制

2、默认隔离级别

     oracle----读已提交

     mysql----可重复读

五、存储引擎

1、存储引擎是表的存储方式

     不同的存储引擎对应不同的表的存储方式

      Mysql中独有的称呼，Oracle中就是“表的存储方式”

2、常见的搜索引擎

（1）MyISAM[可压缩，节省空间]

    <1>采用三个文件组织一张表

           xxx.frm---------存储格式的文件

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

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

   <2>优点：

           可被压缩，节省存储空间，并且可以转换为只读表，提高检索效率(实际物理文件，可压缩)

          缺点：

             不支持事务

（2）InnoDB[事务 安全 重量级]

   <1>存储结构

          xxx.frm---------存储表的结构

          tablespace----存储表的数据（逻辑概念，不可压缩）

  <2>优点：

             支持事务、行级锁、外键等，引擎数据的安全得到保障

             支持级联删除和级联更新（两个表有关联关系的，改动父表，子表跟着改动）

             在MySQL数据库崩溃之后提供自动恢复机制

        缺点：

              数据存储子啊tablespace这样的逻辑概念中，无法压缩

（3）MEMORY[查询速度快]

    <1>以前叫HEPA引擎

    <2>优点：

               查询速度最快

          缺点：

               不支持事务，数据容易丢失，所有数据和索引都在内存中，关机断电即丢失

六、锁

 1、读写锁

         读锁---共享锁：多个用户同时读取同一个资源互不干扰

                                   自己可读不可写；并发可读不可写

         写锁---排他锁：只有一个用户在做写操作

                                   自己可读可写；并发不可读不可写

  2、表锁

               锁定整张表---读写锁

  3、行锁

                锁定某一行---for update（select语句之后）

                 所有操作都阻塞，直到该行提交事务

4、同步

    悲观锁：事务必须排队执行，数据被锁后不允许并发----写锁，行锁

    乐观锁：事务并发，无需排队----读锁

5、数据库解决死锁问题

（1）死锁

     多个事务在同一资源上相互占用并请求锁定对方占用的资源而导致恶性循环的线程

     解决：只有部分或完全回滚一个事务才能打破死锁

（2）解决机制

• 死锁检测：检测到死锁的循环依赖，并立即返回一个错误
• 死锁超时机制：当查询时间达到锁等待超时的设定后放弃锁的请求

      InnoDB处理死锁的方法：将持有最少行级排他锁的事务进行回滚

          

七、数据库相关常见命令【dos窗口】

1、查看mysql版本号

   mysql>select version（）

2、数据库的导入

    mysql -u用户名 -p密码

3、查看有哪些数据库

   show databases；  [这是MySql命令，不是SQL语句]

4、创建数据库

    create database 自定义数据库名 ；   [这是MySql命令，不是SQL语句]

5、使用数据库

      use  数据库名

    [注]using相当于多表查询jion...on中的on，不是用来指定使用的数据库名的

6、初始化数据库

   mysql>source .sql文件的本地路径（如：D：\java\db\xx.sql）

7、查看当前使用哪个数据库

    mysql>select database

8、删除数据库

    drop database 数据库名;

9、查看当前数据库中有那些表

     show tables；

10、查看创建表的语句

     show create table 表名

11、数据的导入和导出    

（1）导出

          导出指定库：

           mysqldump  数据库名>要导出到的路径  -u用户名 -p密码

          导出指定表：

           mysqldump  数据库名 表名>要导出到的路径  用户名和密码

    （2）导入

           create database 数据库名

           use 数据库名

           source 要导入的数据库文件路径

12、查看当前mysql支持的存储引擎

       show engines \G

13、\c  命令

       结束一条语句