2024------MySQL数据库基础知识点总结

-- 最好的选择不是最明智的，而是最勇敢的，最能体现我们真实意愿的选择。

MySQL数据库基础知识点总结

一、概念

• 数据库：DataBase，简称DB。按照一定格式存储数据的一些文件的组合顾名思义: 存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。

• 数据库管理系统：DataBaseManagement，简称DBMS。数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据进行增删改查。

  • 常见的数据库管理系统:
    MySQL、 OracleMS SqlServer、 DB2、 sybase等

• SQL: 结构化查询语言程序员需要学习SQL语句，程序员通过编写SQL语句，然后DBMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。

• 以上三者关系：

  DBMS --(执行)--> SQL --(操作)--> DB

 

二、基础

• 在Windows操作系统中，使用命令来启动和停止MySQL服务：

  net stop MySQL;
  net start MySQL;
  #其他服务的启停也可以使用以上命令，修改服务名称即可
  

• 使用客户端登录MySQL数据库：（前提：MySQL安装了，服务启动了）

  使用bin目录下的mysql.exe命令来连接mysql数据库服务器

  1. 本地登录（显示编写密码）：

     mysql -uroot -p123  #root是用户名，123是密码
     

  2. 本地登录（隐藏密码）：

     mysql -uroot -p  #p后面不加密码直接回车
     

• 表（table）

• SQL语句的分类

  1. DQL：数据查询语言（凡是带有select关键字的都是查询语句）

     ​ select…

  2. DML：数据操作语言（凡是对表当中的数据进行增删改的都是DML）

     ​ insert：增

     ​ delete：删

     ​ update：改

     ​ ！注意：DML主要是操作表中的数据data。

  3. DDL：数据定义语言（凡是带有create、drop、alter的都是DDL）

     ​ create：新建，等同于增

     ​ drop：删除

     ​ alter：修改

     ​ ！注意：DDL主要是操作表结构。

  4. TCL：事务控制语言

     ​ commit：事务提交

     ​ rollback：事务回滚

  5. DCL：数据控制语言

     ​ grant：授权

     ​ revoke：撤销权限

     ​ ……

• 导入.sql数据：

  source D:\document\mysql\node.sql  #路径中不要有中文
  

• MySQL常用命令

• 增删改查又叫做CRUD

 

三、查询

（一）单表查询

1、简单查询

• 查询一个字段：

select 字段名 from 表名;

• 查询多个字段：

select 字段1,字段2,... from 表名;

• 查询所有字段：

select * from 表名;  #这种方式效率低、可读性差，在实际开发中不建议使用
select 所有字段名 from 表名;

• 给查询的列起别名：

select 字段名 (as) 别名 from 表名;
#as可以省略，原表列名不变，只是将查询的字段显示为别名
#如果起的别名有空格，可以用单引号or双引号将别名括起来

• 对查询的字段进行运算操作：

select 字段表达式 from 表名;
#比如 “select sal*12 as '年薪' from emp;”  

2、条件查询

条件查询需要用到where语句，where必须放到from语句表的后面

select 字段1，字段2，... from 表名 where 条件;

3、排序

• 单字段排序：

  select 字段 from 表名 order by 字段;  #默认升序
  select 字段 from 表名 order by 字段 desc;  #指定降序
  select 字段 from 表名 order by 字段 asc;  #指定升序
  

• 多字段排序：

  select 字段 from 表名 order by 字段a (desc/asc), 字段b (desc/asc);  #先按照字段a进行排序，序号相同的再按照字段b进行排序
  

• 根据字段位置进行排序：

  select 字段 from 表名 order by 2;  #2表示第2列，按照第2列进行排序
  #不建议使用这种方式，列的顺序会改变，不健壮
  

• 条件查询+排序：

  select ... from ... where ... order by ...;  #关键字顺序不能变
  #执行顺序：from、where、select、order by
  

4、数据处理函数/单行处理函数

• 单行处理函数：一个输入对应一个输出

• MySQL的日期格式

5、分组函数/多行处理函数

• 多行处理函数：输入多行，输出一行。

• 分组函数在使用的时候必须先进行分组，然后才能使用。如果没有对数据进行分组，默认整张表为一组。

• 注意事项：

  1. 分组函数自动忽略NULL，你不需要提前对null进行处理。

  2. count(*)和count(具体字段)的区别：

     ​ count(*)：统计表中的总行数。（只要一行数据中有一列不为NULL，则这行数据有效）

     ​ count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。

  3. 分组函数不能直接使用在where子句中。

6、分组查询（重点！！！）

• 分组查询语法

  select ... from ... group by ...
  

• 重要结论：

  在一条select语句中，如果有group by语句的话，select后面只能跟：参加分组的字段、分组函数。其他的不能跟。

• having

  • having可以对分完组后的数据再次进行过滤。

  • having不能单独使用，不能代替where，只能和group by搭配使用。

    #找出每个部门最高薪资，并显示最高薪资大于3000的
    select deptno,max(sal) from emp group by deptno having max(sal)>3000;
    #或者先使用where过滤
    select deptno,max(sal) from emp where sal>3000 group by deptno;
    

  • 优化策略：where和having，优先选择where。

• 执行顺序

select ... from ... where ... group by ... having ... order by ...
#以上关键字只能按照这个顺序来，不能颠倒

以上语句的执行顺序：from、where、group by、having、select、order by

7、查询结果去重：distinct

select distinct 字段 from 表名;

• distinct只能出现在所有字段的前面

• distinct出现在两个字段之前，则表示两个字段联合起来去除

（二）连接查询

*** 重点！！！**

1、连接查询

• 连接查询分类：

• 笛卡尔积现象：当两张表进行连接查询，没有任何条件限制时，最终查询结果的条数是两张表条数的乘积。（数学现象）

2、内连接

等值连接

• SQL99语法（内连接）：

  select ...
  from a (inner) join b
  on a和b的连接条件
  where 筛选条件
  #inner可以省略，加上可读性更强，表示内连接
  

• SQL92和SQL99对比

• 在on后面是一个等值条件，所以称为等值连接

非等值连接

• 非等值连接：on后面的条件不是等值关系。

自连接

• 自连接技巧：一张表看作两张表。

3、外连接

• 外连接与内连接的区别：内连接中，连接的两张表没有主次关系，平等的；在外连接中，两张表连接，产生主次关系。（主要看join前面有无right/left来区分）

• 带有right的是右外连接，又叫做右连接。

  带有left的是左外连接，又叫做左连接。

  任何一个右连接都有左连接的写法。

  任何一个左连接都有右连接的写法。

• 外连接的查询结果条数一定是>=内连接的查询结果条数

4、多表连接

• 语法：

  select ...
  from
  	a
  join
  	b
  on
  	a和b的连接条件
  join
  	c
  on
  	a和c的连接条件
  join
  	d
  on
  	a和d的连接条件
  ...
  

  • 一条SQL中，内连接和外连接可以混合，都可以出现。

（三）子查询

• 子查询：select语句中嵌套select语句，配嵌套的select语句称为子查询。

在where语句中使用子查询

在from语句中使用子查询

• from后面的子查询，可以将子查询的查询结果当作一张临时表。

在select语句中使用子查询

// 这个内容不需要掌握，掌握即可

• 对于select后面的子查询来说，这个子查询只能一次返回一条结果，多于一条就报错了，比如：

  select e.ename, e.deptno, (select dname from dept) as dname from emp; #报错
  

（四）union

• union用于合并查询结果集

  • 以上例子，union的效率要高一些。对于表连接来说，每连接一次新表，其匹配次数都满足笛卡尔积。union可以减少匹配的次数，并且完成两个结果集的拼接。

• 使用union的注意事项：

  • 两个要合并的结果集的列数要相同；
  • 结果集合并时列的数据类型要相同。（MySQL可以不同，Oracle不行）

（五）limit

• limit是将查询结果集的一部分取出来，通常使用在分页查询中。

  • 分页的作用：提高用户体验，因为一次性全都查出来，用户体验差。可以一页一页翻页看。

• 用法：

  • 完整用法：limit startIndex,length;

  • 缺省用法：limit 5;

• 注意：MySQL中limit在order by之后执行！！！

• 分页

  每页显示pageSize条记录，第pageNum页：

  limit (pageNum-1)*pageSize,pageSize
  

• DQL语句大总结：

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

  • 执行顺序：from->where->group by->having->select->order by->limit

 

四、表

1、表的创建（建表）create

• 建表属于DDL语句（DDL包括：create、drop、alter）

• 语法格式：

  create table 表名(
      字段名1 数据类型,
      字段名2 数据类型,
      ...
  );
  

  • 表名：建议以 t_ 或者 tbl_ 开始，可读性强。（表名和字段名都属于标识符）

• MySQL中常见的数据类型：

  • 在数据类型后面加括号'()'，是表示该字段的建议长度。

• 创建表时指定默认值：在字段的数据类型后加 **'default 默认要取的值' **。

• 数据库的命名规范：所有标识符都小写，单词和单词之间使用下划线进行衔接。

• 快速创建表：（表的复制）

  create table 要创建的表名 as select 字段名 from  要复制的表名 ...;
  

  • 原理：将一个查询结果当作一张表新建，可以完成表的快速复制。表创建出来，同时表中的数据也存在了。

2、删除表drop

• 语法：

  drop table 表名;  #当这张表不存在的时候会报错
  drop table if exists 表名;  #当表不存在时不会报错
  

3、插入数据insert

• 语法：

  insert into 表名(字段名1, 字段名2, 字段名3, ...) values(值1, 值2, 值3,...);
  

  • 注意：字段名和值的数量和数据类型要一一对应。
  • 每执行成功一次insert语句，记录就会增加一条。
  • 如果没有给其他字段指定值，其值默认为NULL（创建表时已经指定默认值的除外）。
  • insert语句中的字段名可以省略，相当于所有的字段名都被写上了。

• mysql中，获取系统当前时间：now()函数（获取的时间有时分秒信息，是datetime类型）

• 插入多条记录

  insert into 表名1(字段名1, 字段名2, 字段名3, ...) values
  (值1, 值2, 值3,...),
  (值1, 值2, 值3,...),
  ...;
  

• 将查询结果插入到一张表中：

  insert into 要插入的表名 select 字段名 from 要查询的表名;  #很少用
  

  • 查询结果要符合要插入的表的数据类型

4、修改 update（DML）

• 语法格式：

  update 表名 set 字段名1=值1, 字段名2=值2, ... where 条件;
  

  • 注意：如果没有条件限制，会导致全部数据更新。

5、删除数据 delete（DML）

• 语法格式：

  delete from 表名 where 条件;
  

  • 注意：如果没有条件，整张表的数据会被全部删除。

  • delete删除数据的原理：表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放。

• 快速删除表中的数据（truncate）：

  truncate table 表名;
  

  • truncate是删除表中的数据，表还在！

• delete属于DML语句，truncate属于DDL语句。

6、对表结构的增删改

* 这个内容不重要

• 对表结构的修改：添加一个字段，删除一个字段，删改一个字段

• 修改表结构是不需要写到Java代码中的。

• 使用alter语句。（属于DDL语句）

7、约束（重点！！！）

• 约束（constraint）：在创建表的时候，就可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性。

• 约束包括哪些？

（1）非空约束not null

• 非空约束not null约束的字段不能为NULL。

• not null只有列级约束，没有表级约束。

（2）唯一性约束unique

• 单独字段唯一（列级约束）

• 两个字段联合唯一（表级约束）

  • 在字段后面加上unique(字段名1, 字段名2)

  • 需要给多个字段联合起来添加某个约束的时候，需要使用表级约束。

• unique和not null联合使用：

（3）主键约束primary key（重点！！！）

• 相关术语：

• 主键

• 主键分类

• 一个表中主键约束只能添加一个，不能加两个。（主键只能有一个）

• 建议使用int，bigint，char等类型做主键值，不建议用varchar来做主键。

• 在mysql中，有一种可以自动维护一个主键的机制：

  create table t_vip(
      id int primary key auto_increment,
      name varchar(255)
  );
  

  使用auto_increment后，不需要在insert的时候给主键赋值，主键会自动从1开始自增赋值。

（4）外键约束foreign key（重点！！！）

• 相关术语：

• 语法：

  在字段定义后面加上：

  foreign key(要约束的字段名) references 被引用的表名(被引用的字段名)
  

• 顺序：

• 子表中的外键引用的父表中的某个字段，被引用的这个字段可以不是主键，但一定要有unique。（被引用的字段具有唯一性）
• 外表值可以为null。

 

五、存储引擎

1、存储引擎的使用

• 存储引擎：一个表存储/组织数据的方式。不同的存储引擎，表存储的方式不同。（mysql叫存储引擎，Oracle不这么叫）

• 数据库中的各表均被指定的存储引擎来处理。（在创建表时）

• 服务器可用的引擎依赖于以下因素：MySQL的版本、服务器在开发时如何被配置、启动选项

• 给表指定存储引擎：

  在建表的时候可以在最后小括号 ')' 的右边使用：ENGINE来指定存储引擎（默认为InnoDB），CHARSET来指定这张表的字符编码方式（默认为utf8）。

  create table 表名(
      ...
  )ENGINE=存储引擎 default CHARSET=字符编码方式;
  

• 查看当前服务器中可使用的存储引擎：

  show engine \G
  

  版本不同，支持情况不同。

2、常用的存储引擎

InnoDB

MySQL默认的存储引擎。

- 每个InnoDB表在数据库目录以.frm格式文件表示

- InnoDB表空间tablespace被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引）

- 提供一组用来记录事务性活动的日志文件

- 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理

- 提供全ACID兼容

- 在MySQL服务器奔溃后提供自动恢复

- 多版本(MVCC)和行级锁定

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

- 缺点：效率不是很高，不能压缩，不能转换为只读。不能很好的节省存储空间

- 特点：最大特点：支持事务，支持数据库奔溃后自动恢复机制。（安全）

MEMORY

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

- 在数据库目录内，每个表均以.frm格式的文件表示

- 表数据及索引被存储在内存中

- 表级锁机制

- 不能包含TEXT或BLOB字段

- 优点：查询效率是最高的。不需要和硬盘交互。

- 缺点：不安全，关机之后数据消失。因为数据和索引都是在内存中。

 

六、事务

* 重要！！！

1、概述

• 事务（transaction）：一个事务就是一个完整的业务逻辑。是一个最小的工作单元，不可再分。

• 只有DML语句（insert、delete、update）才会有事务这一说，其他语句和事务无关！！

• 说到底，一个事务本质上就是多条DML语句同时成功，或者同时失败！

• 在事务的执行过程中，每一条DML操作都会记录到“事务性活动的日志文件”中。

2、事务的提交与回滚

• 在事务的执行过程中，可以提交事务，也可以回滚事务。

• 提交事务（commit）

• 回滚事务（rollback）：回滚永远只能回滚到上一次

  rollback; #🔺回滚事务
  

  回滚到start transaction之前，或commit之后，或是上条语句之后。

3、事务的隔离级别

• 事务的特性

  A:原子性：说明事务是最小的工作单元，不可再分。

  C:一致性：所有事务要求，在同一个事务中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。

  I:隔离性：A事务和B事务之间具有一定的隔离。

  D:持久性：事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。

• 事务的隔离性

  • 隔离级别：

    事务与事务之间的隔离级别：

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

      ​ 事务A可以读取到事务B未提交的数据。

      ​ 脏读现象：事务A可以读取到事务B的脏数据。

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

    • 读已提交：read committed【提交之后才读到】

      ​ 事务A只能读到事务B提交之后的数据。

      ​ 缺点：不可重复读取数据。在事务A开启之后，事务B可能还有还未提交的事务正在进行中，此时第一次读到的数据就与后面读到的数据不同了，即事务A每次读取的数据不一定相等。

      ​ 优点：解决了脏读现象。且这种隔离级别每次读到的数据都是真实的。

      ​ Oracle数据库默认的隔离级别：read committed

    • 可重复读：repeatable read【提交之后也读不到，永远读取的都是刚开启事务时读到的数据】

      ​ 事务A开启之后，不管是多久，每次在事务A中读到的数据都是一致的。即使事务B已经将数据改变，并且提交了，事务A读取到的数据还是没有发生改变。

      ​ 优点：解决了不可重复读取数据。

      ​ 缺点：会出现幻影读。每一次读取到的数据都是幻象，不真实。

      ​ mysql中默认的事务隔离级别：repeatable read

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

      ​ 这种隔离级别最高，效率最低。

      ​ 表示事务排队，不能并发！

      ​ 每一次读到的数据都是最真实的，并且效率是最低的。

• 设置全局事务隔离级别：

  set global transaction isolation level read uncommitted; #设置全局事务隔离级别，可以将read committed换成其他隔离级别
  

• 查看隔离级别：

  select @@tx_isolation;
  

 

七、索引

1. 概述

   • 索引：索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。

   • MySQL在查询方面主要有两种方式：全表扫描、根据索引检索。

   • 注意：在mysql数据库中的索引也是需要排序的，并且这个索引的排序和TreeSet数据结构相同。TreeSet(TreeMap)底层是一个自平衡的二叉树（遵循左小右大原则存放，采用中序遍历方式遍历取数据）。在索引当中是一个B-Tree数据结构。（表中的字段不会动，是索引进行排序）

   • 索引是各种数据库进行优化的重要手段。

2. 索引的实现原理

   • 在任何数据库上，主键上都会自动添加索引对象。

   • 在mysql中，一个字段上如果有unique约束的话，也会自动创建索引对象。

   • 在任何数据库中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。

   • 在mysql中，索引是一个单独的对象。不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB中，索引存储在一个逻辑名称叫tablespace的表中。在MEMORY存储引擎中，索引存储在内存中。不管索引存储在哪，索引在MySQL中都是以一个树的形式存在（自平衡二叉树：B-Tree）。

   • 索引的实现原理：就是缩小扫描的范围，避免全表扫描。

   • 什么条件下，会考虑给字段添加索引？

     条件1：数据量庞大

     条件2：该字段经常出现在where后面，即该字段总是被扫描

     条件3：该字段很少使用DML（insert、delete、update）操作（因为使用DML后索引需要重新排序）

3. 索引相关语法

   • 创建索引：

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

   • 删除索引：

     drop index 索引名 on 表名;
     

   • 在MySQL中，查看一个SQL语句是否使用了索引进行检索：

     explain 语句;
     

4. 索引失效

   • 失效的第一种情况：模糊匹配中以"%"开头
   • 失效的第二种情况：使用or的时候其中一个字段没有索引
   • 失效的第三种情况：使用复合索引时没有使用左侧的列查找
   • 失效的第四种情况：在where中索引参加了运算
   • 失效的第五种情况：在where中的索引列使用了函数

   ... ...

5. 索引的分类

 

八、视图

• 视图（view）：站在不同的角度去看待同一份数据。

• 创建视图对象：create view

  create view 视图名 as DQL语句;  #🔺as后面只能是DQL语句
  

• 删除视图：drop view

  drop view 视图名;
  

• 注意：只有DQL语句才能以view 的形式创建。

• 视图的特点：通过对视图的增删改，会影响原表的数据。（相当于引用）

• 使用视图的时候，可以像使用table一样

• 视图的作用：

 

九、DBA命令

• 数据的导入和导出（数据的备份）

 

十、数据库设计的三范式

* 面试常问

• 数据库设计范式：数据库表的设计依据。

1、第一范式：要求任何一张表必须有主键，每一个字段原子性不可分。（最核心、最重要的）

2、第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖于主键，不要产生部分依赖。（数据冗余，空间浪费）

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

• 数据库设计三范式是理论上的。

  实践和理论有的时候是有偏差的。

  最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。

  因为在sql中，表和表之间连接次数越多，效率越低。（笛卡尔积）

  而且对于开发人员来说，sql语句的编写难度也会降低。