MySQL基础 & SQL入门

常用的数据库软件

• MySQL：免费开源，6.X以上版本开始收费，推荐使用。
• SQL：微软公司收费的中型数据库，常用于C#、.net等语言，但该数据库仅能在Windows上运行，扩展性、稳定性、安全性及性能都表现一般。
• Oracle：收费的大型数据库，安全性高。
• DB2：IBM公司出品，收费的超大型数据库，常用于银行系统。

MySQL的启动与关闭

• 方法一：直接进入Windows服务中进行启停
• 方法二：使用命令行（管理员）方式关闭：
  • 启动：net start <MySQL名>
  • 停止：net stop <MySQL名>

命令行登录MySQL

DML操作表中数据

插入数据

• 格式：

  insert into 表名（字段名1，字段名2，...）values（字段值1，字段值2，...）
  

• 注意事项：

  • 字段与值必须一一对应，个数、数据类型都要相同
  • 值的数据大小，必须在字段指定的长度范围内
  • 插入全部字段时，可以不写字段名
  • varchar char date 类型的值必须使用单引号或双引号

修改数据

• 格式：

  update 表名 set 列名 = 值 [where 字段名 = 值]
  

删除数据

• 格式：

  delete from 表名 [where 字段名 = 值]
  

• 删除表格所有数据：

  # 方法一：
  delete from 表名 		# 不推荐，原理是逐条删除表格数据，效率低
  
  # 方法二：
  truncate table 表名	# 推荐，原理是直接将整个表格删除，再创建一个一模一样的表格，效率高
  

DQL查询表中数据

简单查询

• 查询不会对数据库中的数据进行修改，只是一种显示方式

• 语法格式：

  select 列名 from 表名
  

  • 查询所有数据：

    select * from 表名
    

  • 可以使用 AS 关键字，为列起别名（AS也可以忽略不写）

    SELECT
    	eid AS '编号',
    	ename AS '姓名', 
    	sex AS '性别' 
    FROM person;
    

  • 去重关键字 distinct

    • 去掉重复的信息：

      select distinct salary from person;		# 结果仅显示不同的数据
      

条件查询

MySQL单表 & 约束 & 事务

DQL_排序查询

• 语法格式：

  select 字段名 from 表名 [where 字段名 = 值] order by 字段名 [asc / desc]
  # asc：表示升序（默认）
  # desc：表示降序
  

• 组合排序：

  select 字段名 from 表名 order by 字段名1, 字段名2
  # 表示若字段名1相同则比较字段名2的值，作升序排序
  

DQL_聚合函数

• 含义：聚合函数查询是指纵向查询，先对某一列的值进行计算，然后返回一个单一的值（聚合函数会忽略null空值）

• 语法结构：

  select 聚合函数(字段名) from 表名
  

  聚合函数	作用
  count(字段)	统计指定列的行数
  sum(字段)	计算指定列的数值和
  max(字段)	计算指定列的最大值
  min(字段)	计算指定列的最小值
  avg(字段)	计算指定列的平均值

DQL_分组查询

• 含义：使用 GROUP BY 语句，对查询的信息进行分组，相同数据作为一组

• 语法：

  select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件]
  

• 需要在分组后对数据进行过滤，使用关键字 having，作用类似于 where

• where 与 having 的区别：

  • where：进行分组前的过滤，后面不能接聚合函数
  • having：进行分组后的过滤，后面可以接聚合函数

limit关键字

• 作用：

  • 用于限制返回的查询结果的行数
  • 语法是MySQL的方言，用来完成分页

• 语法：

  select 字段名 from 表名 limit offset, length
  # offset：起始行数，可以省略，默认为 0
  # length：返回的行数
  

SQL约束

• 作用：对表中的数据进行进一步的限制，从而保证数据的完整性，有效性，正确性，违反约束的数据将无法插入到表中

主键约束

• 关键字：primary key

• 特点：不可重复，唯一，非空

• 作用：表示数据库中的每一条数据

• 添加方式一：在创建表的时候直接加在字段类型后面，如：

  create table person(id int primary key);
  

• 添加方式二：在创建表的时候指定某个字段为主键，如：

  create table person(id int, primary key(id));
  

• 添加方式三：给已经创建好的表设置主键，如：

  alter table person add primary key(id);
  

• 删除方式：

  alter table person drop primary key;
  

主键自增

• 意义：在每次插入新数据时，由数据库自动生成主键字段的值，避免人工输入导致的错误

• 关键字：auto_ increment （注意：字段类型必须为整数类型）

• 创建方法一：

  create table test(id int primary key auto_increment);	# 默认起始值为1
  

• 创建方法二：

  create table test(id int primary key auto_increment)auto_increment=100;		# 表示将起始值设置为100
  

delete 和 truncate 对自增的影响

• delete：仅删除表中所有数据，对自增没有影响
• truncate：由于是将整个表删掉，再创建一个相同的表，故自增的主键字段，重新从1开始自增

非空约束

• 定义：约束某一列不能为空
• 语法：字段名 字段类型 not null

唯一约束

• 特点：约束表格中某一列的值不能重复（对 null 值不作判断）

• 语法：字段名 字段类型 unique

• 添加方式：

  create table test(id int unique);
  

• 主键约束与唯一约束的区别：

  1. 主键约束：唯一且不能为空
  2. 唯一约束：唯一但可以为空
  3. 一个表中只能存在一个主键，但可以存在多个唯一约束

默认值

• 含义：用来指定某一列的默认值

• 语法：字段名 字段类型 default 默认值

• 添加方式：

  create table test(sex char(1) default '男');
  

数据库事务

• 含义：事务是一个整体，由一条或者多条 SQL 语句组成，这些 SQL 语句要么都执行成功，要么都执行失败, 只要有
  一条 SQL 出现异常，整个操作就会回滚，整个业务执行失败。
• 回滚：即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成
  的操作全部撤销，回滚到事务开始时的状态。（在提交之前执行）

MySQL事务操作

• MySQL中有两种方式进行事务的操作：
  1. 手动提交事务
  2. 自动提交事务

手动提交事务

• 语法：
  • 开启事务：start transaction; 或者 begin;（显式地标记一个事务的起始点）
  • 提交事务：commit;（将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束）
  • 回滚事务：rollback;（将对数据库中已完成的操作全部撤销，回到事务开始时的状态）
• 手动提交事务的流程：
  • 执行成功的情况：开启事务 → 执行多条SQL语句 → 提交事务
  • 执行失败的情况：开启事务 → 执行多条SQL语句 → 回滚事务

自动提交事务

• 含义：MySQL 默认每一条 DML(增删改) 语句都是一个单独的事务，每条语句都会自动开启一个事务，语句

  执行完毕自动提交事务，MySQL 默认开始自动提交事务。

事务的四大特性 ACID

• 原子性：每个事务都是一个整体，不可拆分，事务中所有的 SQL 语句要么都执行成功， 要么都执行失败。
• 一致性：事务在执行前数据库的状态与执行后数据库的状态保持一致。
• 隔离性：事务与事务之间不应该相互影响，执行时保持隔离的状态。
• 持久性：事务执行成功，对数据库的修改是持久的。即便关机，数据也会保存下来。

事务的隔离级别（了解）

• 数据并发访问：一个数据库可能拥有多个访问客户端，这些客户端都可以并发地访问数据库，即数据库的相同数据可能被多个事务同时访问，如果不采取隔离措施，可能会导致各种问题，破坏数据的完整性。

并发访问可能会产生的问题

• 脏读：一个事务读取到了另一个事务中尚未提交的数据
• 不可重复读：一个事务中两次读取的数据内容不一致，要求是在一个事务中多次读取时数据是一致的，这是在进行 update 操作时引发的问题
• 幻读：一个事务中，某一次的 select 操作得到的结果所表征的数据状态，无法支撑后续的业务操作。查询得到的数据状态不准确，导致幻读

四种隔离级别

• 通过设置隔离级别，可以防止以上的三种并发问题。
• √：会出现问题
• ×：不会出现问题

• 注意事项：串行化 serializable 可以彻底解决三种并发问题，但在这种隔离级别下，事务只能排队执行，严重影响效率，一般不使用

隔离级别的相关命令

• 查看隔离级别：select @@tx_isolation;

• 设置隔离级别：

  set global transaction isolation level 级别名称;
  

  • 级别名称：
    • 读未提交：read uncommitted
    • 读已提交：read committed
    • 可重复读：repeatable read
    • 串行化：serializable

MySQL多表 & 外键 & 数据库设计

外键 & 主表 & 从表

• 外键：员工表的字段 dept_id 与部门表中的主键相对应，dept_id 这个字段就是外键
• 主表：与外键相对应的主键所在的表（上图中的部门表）
• 从表：拥有外键的表（上图中的员工表）

外键约束

• 意义：使两张表之间产生一个对应关系，从而保证主从表的引用的完整性
• 注意事项：
  • 从表外键类型必须与主表主键类型一致，否则会报错，无法创建
  • 添加数据时，应该先添加主表中的数据
  • 删除数据时，应该先删除从表中的数据
• 多表关系中的主从表：
  • 主表：主键ID所在的表，即约束别人的表
  • 从表：外键所在的表，即被约束的表

创建外键约束

• 语法：

  • 新建表时添加：

    [constraint] [外键约束名称] foreign key(外键字段名) references 主表(主键字段名);
    

  • 已有表添加：

    alter table 从表 add [constraint] [外键约束名称] foreign key(外键字段名) references 主表(主键字段名);
    

  • 注意事项：

    [constraint] [外键约束名称] 可以省略，外键约束名称由系统自动给出
    

删除外键约束

• 语法：

  alter table 从表 drop foreign key 外键约束名称
  

级联删除操作（了解）

• 含义：在删除主表数据的同时，也删除掉从表数据

• 语法：on delete cascade

• 实例：

  create table test(dept_id int, foreign key(dept_id) references department(id) on delete cascade);
  

多表关系设计

• 在实际开发中，一个项目通常需要多张表才能完成，且这些表之间存在一定的联系。
• 表与表之间的三种关系：
  1. 一对多：最常见的关系，如班级对学生，部门对员工
  2. 多对多：学生与课程，用户与角色
  3. 一对一：较罕见，因为一对一的关系可以合成为一张表

一对多关系（常见）

• 建表原则：在从表（多方）创建一个字段作为外键，指向主表（一方）的主键

多对多关系（常见）

• 建表原则：需要创建第三张表作为中间表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

多表查询

• 含义：同时查询多张表，获取需要的数据；例如我们要查询家电分类下都有哪些商品，那么我们就需要查询分类与商品这两张表

多表查询的分类

内连接查询

• 含义：通过指定的条件去匹配两张表中的数据，匹配上就显示，匹配不上就不显示
• 例如：使用 从表的外键 = 主表的主键 的条件去匹配

隐式内连接

• 使用 where 条件过滤无用的数据

• 语法：

  select 字段名 from 表1, 表2 where 连接条件;
  

显示内连接

• 语法：

  select 字段名 from 表1 [inner] join 表2 on 条件;	# inner可以省略
  

外连接查询

左外连接

• 含义：以左表为基准，匹配右表中的数据

  • 若能够匹配，则展示匹配到的数据
  • 若无法匹配，则左表中的数据正常显示，右表的显示为null

• 语法：

  select 字段名 from 左表 left [outer] join 右表 on 条件;	# outer可以省略
  

右外连接

• 含义：以右表为基准，匹配由表中的数据

  • 若能够匹配，则展示匹配到的数据
  • 若无法匹配，则左表中的数据正常显示，右表的显示为null

• 语法：

  select 字段名 from 左表 right [outer] join 右表 on 条件;		# outer可以省略
  

各种连接方式的总结

• 内连接：取两张表中交集部分的数据
• 左外连接：以左表为基准，查询左表的所有数据，以及与右表有交集的部分
• 右外连接：以右表为基准，查询右表的所有数据，以及与左表有交集的部分

子查询（SubQuery）

• 概念：一条 select 查询语句的结果，作为另一条 select 语句的一部分

• 特点：

  • 子查询必须放在小括号中
  • 子查询一般作为父查询的查询条件使用

• 常见分类：

  • where型：将子查询的结果作为父查询的比较条件

    • 语法：

      select 查询字段 from 表 where 字段 = (子查询);
      

  • from型：将子查询的结果作为一张表提供给父查询使用

    • 语法：

      select 查询字段 from (子查询) 表别名 where 条件;
      

    • 注意：当子查询作为一张表的时候，需要起别名，否则无法访问表中的字段。

  • exists型：子查询的结果为单列多行，类似一个数组，父查询使用 in 函数，包含子查询的结果

    select 查询字段 from 表 where 字段 in (子查询);
    

• 总结：

  • 子查询如果查出的是一个字段(单列)，那么就在 where 后面作为条件使用
  • 子查询如果查询出的是多个字段(多列)，就当做一张表使用(需要起别名)

数据库设计

数据库三范式（最省空间）

• 概念：为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
• 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以此类推。一般说来，数据库只需满足第三范式（3NF）即可。

第一范式 1NF

• 概念：原子性，做到列不可拆分。第一范式是最基本的范式。数据库表里面字段都是单一属性的，不可再分, 如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。

第二范式 2NF

• 概念：
  • 在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关
  • 一张表只能描述一件事

第三范式 3NF

• 概念：消除传递依赖，表的消息，如果能够被推导出来，就不应该单独设计一个字段来存放

数据库反三范式

• 概念：反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能；浪费存储空间，节省查询时间 (以空间换时间)
• 冗余字段：设计数据库时，某一个字段属于一张表，但它同时出现在另一个或多个表，且完全等同于它在其本来所属表的意义表示，那么这个字段就是一个冗余字段。

总结

• 创建一个关系型数据库设计，我们一般有两种选择：
  1. 尽量遵循范式理论的规约，尽可能少的冗余字段，让数据库设计看起来精致、优雅、让人心醉。
  2. 合理的加入冗余字段这个润滑剂，减少join，让数据库执行性能更高更快。

MySQL索引 & 视图 & 存储过程

MySQL索引

• 概念：在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询和运行更加高效。如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。拿汉语字典的目录页索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
• MySql将一个表的索引都保存在同一个索引文件中，如果对表中数据进行增删改操作，MySql都会自动地更新索引。

常见索引分类

主键索引（primary key）

• 主键是一种唯一性索引，每个表只能有一个主键，用于标识数据表中的每一条记录
• 添加方式即为主键的添加方式

唯一索引（unique）

• 特点：索引列的所有值都仅能出现一次，且唯一。唯一索引可以保证数据记录的唯一性。事实上，在许多场合，创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

• 语法：

  • 创建表时添加：

    create table 表名(列名 类型(长度), unique [索引名称] (列名));
    

  • 在已有表添加：

    • 方法一：

      create unique index 索引名 on 表名(列名(长度));
      

    • 方法二：

      alter table 表名 add unique(列名);
      

普通索引（index）

• 概念：普通索引（由关键字 KEY 或 INDEX 定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column =）或排序条件（ORDERBY column）中的数据列创建索引。

• # 添加方式一：
  create index 索引名 on 表名(列名[长度]);
  
  # 添加方式二：
  alter table 表名 add index 索引名(列名);
  

删除索引

• 意义：由于索引会占用一定的磁盘空间，因此为了避免影响数据库的性能，应该及时删除不再使用的索引。

• 语法：

  alter table 表名 drop index 索引名;
  

索引的优缺点总结

• 添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。
• 优点：
  1. 极大地提高查询速度
  2. 显著地减少查询中分组和排序的时间
• 缺点：
  1. 创建索引和维护索引需要时间，而且数据量越大时间越长
  2. 当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度

MySQL视图

• 概念：
  • 视图是一种虚拟表
  • 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表
  • 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句
  • 视图向用户提供基表数据的另一种表现形式
• 作用：
  • 权限控制时使用：例如某几个列可以运行用户查询，其他列不允许，可以开通视图，查询特定的列，起到权限控制的作用
  • 简化复杂的多表查询：视图本身就是一条查询SQL，我们可以将一次复杂的查询构建成一张视图，用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)，这也是视图主要的意义所在。

视图的使用

• 添加格式：

  create view 视图名 [column_list] as select语句;
  # column_list：可选参数，表示属性清单，指定视图中各个属性的名称，默认情况下，与SELECT语句中查询的属性相同
  # as：表示视图要执行的操作
  # select语句：向视图提供数据内容
  

视图与表的区别

• 视图建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示
• 通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列 经过计算得到的结果,不允许更新）
• 删除视图，表不受影响，而删除表，视图不再起作用

MySQL存储过程（了解）

• 概念：MySQL 5.0 版本开始支持存储过程。存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
• 简单理解: 存储过程其实就是一堆 SQL 语句的合并。中间加入了一些逻辑控制。

存储过程的优缺点

• 优点：
  • 存储过程一旦调试完成后，就可以稳定运行（前提是，业务需求要相对稳定，没有变化）
  • 存储过程减少业务系统与数据库的交互，降低耦合，数据库交互更加快捷（应用服务器，与数据库服务器不在同一个地区）
• 缺点：
  • 在互联网行业中，大量使用MySQL，MySQL的存储过程与Oracle的相比较弱，所以较少使用，并且互联网行业需求变化较快也是原因之一
  • 尽量在简单的逻辑中使用，存储过程移植十分困难，数据库集群环境，保证各个库之间存储过程变更一致也十分困难。
  • 阿里的代码规范里也提出了禁止使用存储过程，存储过程维护起来非常麻烦。

存储过程的创建方式

方式一

• 语法格式：

  delimiter $				   	 # 自定义语句结束符
  create  procedure 过程名称()   # 声明存储过程
  begin						# 开始编写存储过程
  # 需要执行的操作...			
  end $						# 存储过程结束
  

• 调用方式：

  call 存储过程名
  

方式二

• in 输入参数：表示调用者向存储过程传入值

  CREATE PROCEDURE 存储过程名称(IN 参数名 参数类型)
  

• 创建接收参数的存储过程

• 示例：接收一个商品id，根据id删除数据

  DELIMITER $$ 
  CREATE PROCEDURE goods_proc02(IN goods_id INT) 
  BEGINDELETE FROM goods WHERE gid = goods_id ; 
  END $$
  

• 调用存储过程，传递参数：

  # 删除id为2的商品
  call goods_proc02(2);
  

方式三

# 变量赋值
set @变量名 = 值

# out输出参数：表示存储过程向调用者传出值
out 变量名 数据类型

# 创建存储过程
# 实例：向订单表插入一条数据，返回1，表示插入成功
# 接收参数插入数据, 并返回受影响的行数 
DELIMITER $$ 
CREATE PROCEDURE orders_proc(IN o_oid INT , IN o_gid INT ,IN o_price INT, OUT out_num INT) 
BEGIN
	# 执行插入操作...
    INSERT INTO orders VALUES(o_oid,o_gid,o_price); 
	# 设置 num的值为 1 
    SET @out_num = 1; 
    # 返回 out_num的值 
    SELECT @out_num; 
END $$

# 调用存储过程插入数据，获取返回值 
CALL orders_proc(1,2,30,@out_num);

MySQL触发器（了解）

• 概念：触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete， update）时就会激活它执行。
• 简单理解：当我们执行一条sql语句的时候，这条sql语句的执行会自动去触发执行其他的sql语句。

触发器的四个要素

1. 监视地点（table）
2. 监视事件（insert/update/delete）
3. 触发时间（before/after）
4. 触发事件（insert/update/delete）

创建方式

delimiter $
create trigger 触发器名			      # 触发器名在一个数据库中是唯一的
before/after（insert/update/delete）	# 触发时机 & 监视的事件
on table_Name					    # 触发器所在的表
for each row					    # 行触发器，固定写法，每一行都会受影响
begin
	# 触发事件
end $

DCL（数据控制语言）

• 概念：MySql默认使用的都是 root 用户，超级管理员，拥有全部的权限。除了root用户以外，我们还可以通过DCL语言来定义一些权限较小的用户, 分配不同的权限来管理和维护数据库。

创建用户

• 语法格式：

  CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
  # 用户名：创建的新用户的登录名
  # 主机名：指定该用户在哪个主机上可以登录，本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符 %
  # 密　码：登录密码
  

用户授权

• 创建用户之后，需要进行授权。

• 语法格式：

  GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
  # 权限：授予用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授予所有的权限则使用 ALL
  # ON：指定权限针对哪些库和表
  # TO：表示将权限授予哪个用户
  
  # 示例：给 admin1 用户分配对 db4 数据库中 products 表的 操作权限：查询
  GRANT SELECT ON db4.products TO 'admin1'@'localhost';
  

查看权限

• 语法格式：

  SHOW GRANTS FOR '用户名'@'主机名';
  

删除用户

• 语法格式：

  DROP USER '用户名'@'主机名';
  

查询用户

# 选择名为 mysql 的数据库, 直接查询 user表即可
SELECT * FROM USER;

数据库的备份 & 还原

• 意义：备份的应用场景在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。 这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

# 备份
mysqldump -u 用户名 -p 密码 数据库 > 文件路径

# 还原
source sql文件地址