MYSQL：MYSQL基础超全总结，包含面试常考

软件概述

数据库、sql语言、数据库管理系统之间的关系

• 数据库(DB)

  • 数据库是按照一定格式储存的文件的组合

• 数据库管理系统(DBMS)

  • 数据库管理系统是用于管理数据库中的数据的，
    常见的数据库管理系统有：MySQL、Oracle、MS SqlServer、DB2、sybase

• sql(结构化查询语言)

  • 程序员编写sql语句，交给数据库管理系统，来完成数据库的CRUD

• 三者之间的关系

  • DBMS---->通过sql语句---->对DB进行CRUD

安装与卸载

• 安装

  • 端口号

    • mysql的端口号为：3306
    • 端口号是计算机中每一个软件的唯一标识，具有唯一性不可重复

  • 编码方式

    • 将mysql的编码方式设置为UTF-8

• 完美卸载

  • 第一步：双击安装包进行卸载删除。
  • 第二步：删除目录：
    把C:\ProgramData下面的MySQL目录干掉。
    把C:\Program Files (x86)下面的MySQL目录干掉。

mysql的常用命令

• 退出mysql ：exit
• 查看mysql中的数据库：show databases;
• 使用数据库：use 表名
• 创建数据库：create databases 数据库名;
• 查看数据库当中的表：show tables;
• 查看mysql数据库的版本号：select version();
• 查看当前使用的数据库：select databases();

数据库当中的基本单位：表

• 行

  • 被称为数据/记录

• 列

  • 被称为字段

sql语句的分类

• DQL：数据查询语言：凡是带有select关键字的都是查询语句

• DML：数据操作语言：凡是对表中的数据进行增删改的都是DML语言，例: insert\delete\update.

• DDL：数据定义语言：凡是对表的结构进行增删改的都是DDL语言，例：create\drop\alter.

• TCL：事务控制语言：事务提交(commit)，事物回滚(rollback)等等

• DCL：数据控制语言：授权(grant)，撤销权限(revoke)…

sql中的数据类型

C：INSERT、CREATE

insert(DML)

语法格式：
--------------------------------------------
方式一：
Insert into 表名(字段，。。。。) values(值,………..)
//插入多条数据的时候使用较为方便
方式二：
Insert into 表名(字段，。。。。) values
  				(值1,………..),	
				(值2,………..),
				(值3,………..);
方式三：将查询到的数据直接插入表中
Insert into 表名 select * from emp where sal=3000;

create(DDL)

语法格式：
--------------------------------
方式一：
create table 表名(
字段名1 数据类型 字段长度限制 字段约束,
字段名2 数据类型 字段长度限制 字段约束,
字段名3 数据类型 字段长度限制 字段约束,
约束名(字段名,字段名)//表级约束
);//注意：其中，约束可有可无。
--------------------------------------------
方式二：将查询到的结果创建为一张新的表
create table 表名 as select empno,ename,sal from emp;

R：SELECT（DQL）

完整格式与执行顺序

• 完整格式：

  select....from....join...on...where....group by....having....order by....limit....
  

• 执行顺序

  1. from 2.join 3.on 4.where 5.group by 6.having 7.select 8.order by 9.limit
  

join（添加表进行连接查询（联表查询））

• 连接查询（多张表联合在一起查询称作连接查询）

• 笛卡尔积现象

  • 当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象

• 按表连接的方式分类

  • 内连接（表与表之间无主次关系，匹配则查出来，默认表连接就是内连接，其中关键字inner(inner join)被省略）

    • 等值连接（当两者相等时，连接）

    • 非等值连接（当两者不相等时，连接）

    • 自连接（表自己与自己进行连接查询）

  • 外连接（表与表之间有主次关系，与主表数据匹配的副表数据全部查出之外，主表的数据也全部查出）

    • 左连接（格式：from A left join B，左边的为主表）
    • 右连接（格式：from A right join B，右边的为主表）

• 思考：外连接的查询结果条数一定是 >= 内连接的查询结果条数？正确

on\where\having（三者都是对表的筛选）

• 三者间的区别

  • on是针对表的连接的条件（join 必须与 on 同用）
  • where是针对查询出来的数据的条件
  • having是针对分组后数据的条件

• 条件运算符

group by（将表中的数据按照字段名分组，分组是针对分组函数的 ）

• 重点注意：在 SQL 语句中若有 group by 语句，那么在 select 语句后面只能跟分组函数+参与分组的字段

select（将查询到的数据显示）

• 数据处理函数/单行处理函数（单行处理函数可以用于一切与数据相关的sql语句）

• 分组函数（分组函数只能分组后使用，若没有分组，则默认是将整体分为一组），常用的分组函数如下：

distinc关键字（将查询结果，根据其后关键字，去重）

--------表数据----------
1 nihao 19
2 woshi 19
--------查询------------
SELECT DISTINCT age FROM `test1`
--------结果------------
19

order by（后面跟字段名，可以完成对查询结果的排序）

• desc 降序 asc 升序

limit（对查询结果的数量与范围进行限定）

• 完整用法

  • limit startIndex（开始索引）, length（截取长度）

• 通用的网页分页格式

子查询（select语句中嵌套select语句，被嵌套的select语句称为子查询）

• 子查询出现的位置

select：出现在select后的子查询查询出来的也只能是一条记录
from：出现在from中的子查询，查询出来的记录但做一张临时的表
where：在WHERE子句之中处理单行单列子查询、多行单列子查询(主要使用三种操作符：IN、ANY、ALL)、单行多列子查询

• union合并查询结果集（union是将两条或多条sql语句查询出来的结果集行与行合并，使用union的效率会比表连接高）
  • 注意：union在合并结果集的时候，要求两个结果集的列数相同，Oracle则更严格，要求结果集中列与列的数据类型也相同

要点

• 在select、from、join中的字段以及表名，可以通过起别名的方式来提高编写sql语句的效率

U：UPDATE(DML)、ALTER（DDL）

--------UPDATE(DML)------
语法格式：update 表名 set 字段名称 1=需要修改的值 1, 字段名称 2=需要修改的值 2 where …….
--------alter(DDL)-------了解即可，采用 alter table 来增加/删除/修改表结构，不影响表中的数据
alter table 表名 add/modify 需要操作字段的组成内容;
alter table 表名 drop 删除的字段名;

D：DELETE(DML)、TRUNCATE（DDL）、DROP（DDL）

-------delete(DML)-------
语法格式：Delete from 表名 where ......
-------truncate(DDL)-------快速删除表的数据，但不会删除表
truncate table 表名
 -与 delete 的比较
	- delete删除的数据支持事务回滚，但truncate不支持
	- delete删除只是将数据删除但表的真实存储空间并不会释放
	- delete删除数据效率低
-------drop(DDL)-------
语法格式：drop table if exists 表名;如果这张表存在则删除

约束(constraint，约束是为了使数据有效)

约束的类型

• 非空约束：not null
• 唯一性约束: unique
• 主键约束: primary key （简称PK）
• 外键约束：foreign key（简称FK）
• 检查约束：check（mysql不支持，oracle支持

表级约束与联合约束

• 表级约束：多个字段联合联合起来添加一个约束
  联合约束：多个约束联合约束一个字段

非空约束：not null

• 概述

  • 非空约束的字段不能为空

唯一性约束: unique

• 概述

  • 唯一性约束的字段不能为重复，但可以为null

主键约束: primary key （简称PK）

• 概述

  • 主键是每一行记录的唯一标识

• 创建表级约束的语法格式

  • 格式一：primary key (字段名,…),
  • 格式二(给约束命名): constraint 约束名 primary key (约束的字段名)

• 相关术语

  • 主键约束：就是一种约束
  • 主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段
  • 主键值：主键字段中的每一个值都叫做：主键值

• 主键的特征

  • not null+unique(主键值不能是null，且同时不能重复)

• 按主键的形式分类

  • 单一主键

    • 一个字段做主键

  • 复合主键

    • 用主键的表级约束联合起来的多个字段，这些字段被称为复合主键

• 按主键与业务的关系分类

  • 自然主键：主键值是一个自然数，和业务没关系
  • 业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！

• 主键的维护

  • 概述

    • 在mysql当中，有一种机制可以自动帮我们维护主键值（也就是自动生成主键值）

  • 格式

    • 

• 注意事项

  • 一张表的主键只能有一个
  • 在实际开发中，推荐使用自然主键与单一主键
  • 主键值的类型建议为：int,bigint,char

外键约束：foreign key（简称FK）

• 概述

  • 使用外键约束的字段，改字段的值是被外键所限定的

• 创建表级约束语法格式

  • 格式一：foreign key (约束的字段名) references 引用的表名(引用的字段)
  • 格式二(给约束命名): constraint 约束名 foreign key (约束的字段名) references 引用的表名(引用的字段)

• 相关术语

  • 外键约束：一种约束（foreign key）
  • 外键字段：该字段上添加了外键约束
  • 外键值：外键字段当中的每一个值

• 父表与子表关系

  • 概述

    • 在使用了外键约束的表中，外键所在的表叫做主表，而被外键约束的表叫做副表

  • 创建表：要先创建父表后再创建子表

  • 删除表：要先删除子表后再删除父表

  • 插入数据：要先插入父表数据，后插入子表数据

  • 删除数据：要先删除子表数据，后删除父表数据

• 注意事项

  • 子表引用父表中的某个字段，改字段可以不是主键，但至少具有unique约束
  • 外键的值可以为null

事务(transaction)

概述

• 一个事务就是一个完整的业务逻辑，是一个最小的工作单元，不可再分
• 只有DML语句才有事务一说，因为涉及到数据的增删改
• 事务的产生是由于数据的安全
• 事务：就是批量的DML语句，同时成功，或者同时失败，只有这两者情况

MySQL中自动提交事务的默认行为

• 在MySQL中，每一条DML语句默认是一个事务，并且每执行一次就自动提交一次事务

自动提交机制的关闭命令

• start transaction；

提交事务的命令

• commit;

回滚事务的命令

• rollback; 语句（回滚永远都是只能回滚到上一次的提交点！）

事务的四个特性（ACID）

• 原子性

  • 说明事务是最小的工作单元。不可再分。

• 一致性

  • 同一个事务中，所有操作只能同时成功或者同时失败

• 隔离性

• 持久性

  • 是事务最终结束的一个保障，事务提交就将其数据保存在硬盘里

隔离性的４个级别

• 读未提交：read uncommitted（最低的隔离级别）《没有提交就读到了》

  • 概述

    • 事务A可以读到事务B未提交的数据

  • 缺点

    • 脏读现象，读到了脏数据

  • 这种隔离级别一般是理论上的，大多数数据库的隔离级别都是二档起步

• 读已提交：read committed《提交之后才能读到》（Oral默认的隔离级别）

  • 概述

    • 事务A可以读到事务B已经提交的

  • 优点

    • 解决了脏读问题
    • 读到的数据是绝对真实的

  • 缺点

    • 不可以重复读取数据

  • Oracle中的默认隔离级别

• 可重复读：repeatable read《提交之后也读不到，永远读取的都是刚开启事务时的数据》（Mysql中默认的数据库隔离等级）

  • 概述

    • 事务A读到的数据，是事务A开启时那一刻的数据内容

  • 优点

    • 解决了不可重复读

  • 缺点

    • 每次读到数据是幻象，不够真实

  • MySQL中的隔离级别

• 序列化/串行化：serializable（最高的隔离级别）

  • 概述

    • 最高的隔离级别，事务A开启后，所有其他事务都不可以访问数据，直到事务A结束后，其他事务方可执行，表示事务同步

  • 优点

    • 解决了脏读，读的数据是幻象，不可重复读的问题

  • 缺点

    • 效率低

索引(index)

概述

• 索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制
• MySQL中索引的数据结构是Ｂ－Tree
• 在MySQL中，主键已经unique约束的字段都会自动添加索引

MySQL中主要的两种查询方式

• 全表扫描
• 根据索引检索

添加索引的条件

• 数据量比较大
• 改字段经常被扫描，也就是经常出现在where条件中
• 该字段很少DML操作
• 该字段的唯一性较强

索引的创建语法

• create　index　索引名　on 表名(字段名)

索引的删除

• drop index 索引名 on 表名

查看一个sql语句是否用了检索来查询的方法

• explain select语句;
  若type等于ref则使用了，all则没有使用

索引失效的情况(面试官可能会问)

• 模糊匹配当中以“%”开头
• 使用or条件判断时，只有两边的条件字段都有索引，才会走索引。所以这也是不建议用or的原因
• 使用复合索引时，没有使用左侧的列查找，索引失效。
  复合索引：两个或以上字段联合起来添加一个索引
• where当中索引字段参加了加减乘除的运算，索引失效
• where当中的索引字段使用了函数

视图(view)

概述

• 站在不同角度去看待同一份数据
• 视图对应的语句只能是DQL语句

创建视图对象的语法格式

• create view 视图名 as select语句

删除视图对象的语法格式

• drop view 视图名;

视图的功能

• 我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作！（视图的特点：通过对视图的操作，会影响到原表数据。）
• 视图对象创建完成之后，可以对视图进行增删改查等操作

视图在实际开发中的作用

• 用于简化复杂的sql语句，面向视图开发

DBA

数据的导入步骤

• 登入数据库
• 创建数据库：create databases 导入数据库名;
• 使用数据库:use 导入数据库名
• 初始化数据库：source sql脚本文件的绝对路径

数据的导出语法

• 导出指定的数据库：mysqldump 数据库名>D:\数据库名.sql -u… -p…
• 导出数据库中指定的表：mysqldump 数据库名 表名>D:\数据库名.sql -u… -p…

注意：在去到公司的时候，一开始会给你一个sql脚本文件让你把数据导入自己的电脑里面。

数据库设计的三范式(面试官会问)

第一范式

• 最核心，最重要的范式，所有表的设计都需要满足，必须有主键，并且每一个字段都是原子性不可再分。

    学生编号 学生姓名 联系方式
    ------------------------------------------
    1001        张三      zs@gmail.com,1359999999
    1002        李四      ls@gmail.com,13699999999
    1001        王五      ww@163.net,13488888888

    以上是学生表，满足第一范式吗？ 
    不满足，第一：没有主键。第二：联系方式可以分为邮箱地址和电话

    学生编号(pk) 学生姓名   邮箱地址            联系电话
    ----------------------------------------------------
    1001                张三      zs@gmail.com    1359999999
    1002                李四      ls@gmail.com    13699999999
    1003                王五      ww@163.net      13488888888

第二范式

• 建立在第一范式的基础之上，要求所有非主键字段必须完全依赖主键，不要产生部分依赖。

    学生编号 学生姓名 教师编号 教师姓名
    ----------------------------------------------------
    1001            张三      001     王老师
    1002            李四      002     赵老师
    1003            王五      001     王老师
    1001            张三      002     赵老师

• 这张表描述了学生和老师的关系：（1个学生可能有多个老师，1个老师有多个学生）,这是非常典型的：多对多关系！
• 分析以上的表是否满足第一范式？
  • 不满足，如何修改？

    学生编号+教师编号(pk)       学生姓名  教师姓名
    ----------------------------------------------------
    1001            001             张三          王老师
    1002            002             李四          赵老师
    1003            001             王五          王老师
    1001            002             张三          赵老师

• 修改后，学生编号 教师编号，两个字段联合做主键，复合主键（PK: 学生编号+教师编号）
• 经过修改之后，以上的表满足了第一范式。但是满足第二范式吗？
  • 不满足，“张三”依赖1001，“王老师”依赖001，显然产生了部分依赖。
  • 产生部分依赖有什么缺点？
    • 数据冗余了。空间浪费了。“张三”重复了，“王老师”重复了。
• 如何设计使其满足第二范式？

    使用三张表来表示多对多的关系！！！！

    学生表
    学生编号(pk)        学生名字
    ------------------------------------
    1001                    张三
    1002                    李四
    1003                    王五
     
    教师表
    教师编号(pk)        教师姓名
    --------------------------------------
    001                 王老师
    002                 赵老师

    学生教师关系表
    id(pk)          学生编号(fk)            教师编号(fk)
    ------------------------------------------------------
    1                       1001                        001
    2                       1002                        002
    3                       1003                        001
    4                       1001                        002

• 背口诀：多对多怎么设计？多对多，三张表，关系表两个外键！

第三范式

• 第三范式建立在第二范式的基础之上,要求所有非主键字典必须直接依赖主键，不要产生传递依赖。

    学生编号（PK） 学生姓名 班级编号  班级名称
    ---------------------------------------------------------
        1001                张三      01          一年一班
        1002                李四      02          一年二班
        1003                王五      03          一年三班
        1004                赵六      03          一年三班

• 以上表的设计是描述：班级和学生的关系。很显然是1对多关系！一个教室中有多个学生。
• 以上表足一二范式:
  • 满足第一范式，有主键。
  • 满足第二范式，因为主键不是复合主键，没有产生部分依赖。主键是单一主键。
• 分析以上表是否满足第三范式？
  • 第三范式要求：不要产生传递依赖！一年一班依赖01，01依赖1001，产生了传递依赖。不符合第三范式的要求。产生了数据的冗余。
• 那么应该怎么设计一对多呢？

    班级表：一
    班级编号(pk)                班级名称
    ----------------------------------------
    01                              一年一班
    02                              一年二班
    03                              一年三班

    学生表：多

    学生编号（PK） 学生姓名 班级编号(fk)
    -------------------------------------------
    1001                张三          01          
    1002                李四          02          
    1003                王五          03          
    1004                赵六          03      

• 背口诀：一对多，两张表，多的表加外键！！！！！！！！！！！！

总结表的设计原则

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

 多对多：
     多对多，三张表，关系表两个外键！！！！！！！！！！！！！！！

 一对一：
     一对一放到一张表中不就行了吗？为啥还要拆分表？
     在实际的开发中，可能存在一张表字段太多，太庞大。这个时候要拆分表。
     一对一怎么设计？
         没有拆分表之前：一张表
             t_user
             id      login_name      login_pwd       real_name       email               address........
             ---------------------------------------------------------------------------
             1           zhangsan        123             张三              zhangsan@xxx
             2           lisi            123             李四              lisi@xxx
             ...
          
         这种庞大的表建议拆分为两张：
             t_login 登录信息表
             id(pk)      login_name      login_pwd   
             ---------------------------------
             1               zhangsan        123         
             2               lisi            123         

             t_user 用户详细信息表
             id(pk)      real_name       email               address........ login_id(fk+unique)
             -----------------------------------------------------------------------------------------
             100         张三              zhangsan@xxx                                1
             200         李四              lisi@xxx                                        2


             口诀：一对一，外键唯一！！！！！！！！！！

嘱咐一句话：

• 数据库设计三范式是理论上的,实践和理论有的时候有偏差,最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度,因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）,有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低,面试的时候把这句话说上：他就不会认为你是初级程序员了！
• 数据库三范式的存在就是为了使得数据库字段不冗余，节约存储空间，让存储的逻辑更为清晰。