MySQL 基础笔记

MySQL概述

关系型数据库 (RDBMS)

概念 ：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

特点 ：

• 使用表存储数据，格式统一，便于维护。
• 使用 SQL 语言操作，标准统一，使用方便。

SQL

通用语法

分类

• DDL(Data Definition Language) ：数据定义语言，用来定义数据库对象（数据库、表、字段）
• DML(Data Manipulation Language) ：数据操作语言，用来对数据库表中的数据进行增删改
• DQL(Data Query Language) ：数据查询语言，用来查询数据库中表的记录
• DCL(Data Control Language) ：数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL (数据定义语言)

DDL — 数据库操作

库查询

• 查询所有数据库：

  SHOW DATABASES;
  

• 查询当前数据库：

  SELECT DATABASE();
  

库创建

 CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

库删除

DROP DATABASE [IF EXITS] 数据库名

库使用

USE 数据库名

DDL — 表操作

查询当前所数据库所有表：

show tables;

查询表结构：

DESC 表名;

查询指定表的建表语句：

show create table 表名;

create (表创建) ：

create table 表名（
		字段1 字段1类型[comment 字段1注释],
		字段2 字段2类型[comment 字段2注释],
		...,
)[comment 表注释];

alter (表修改) ：

• 添加字段 ：

  alter table 表名 add 字段名 类型（长度）[comment 注释][约束]; []内容为可选的;
  

• 修改数据类型 ：

  alter table 表名 modify 字段名 新数据类型（长度);
  

• 修改字段名和字段类型 ：

  alter table 表名 change 旧字段名 新字段名 类型（长度）;
  

• 删除字段 ：

  alter table 表名 drop 字段名;
  

• 修改表名 ：

  alter table 表名 rename to 新表名;
  

drop (表删除) ：

• 删除表 ：

  drop table  表名;
  

• 删除指定表 ：

  truncate table 表名;
  

DML (数据操作语言)

insert (添加数据)

• 给指定字段添加数据：

  insert into 表名(字段名1，字段名2，…) values (值1，值2，…);
  

• 给全部字段添加数据：

  insert into 表名 values (值1，值2，…);
  

• 批量添加数据：

    insert into 表名(字段名1，字段名2，…) values (值1，值2，…), (值1，值2，…), (值1，值2，…);
    insert into 表名 values (值1，值2，…),(值1，值2，…),(值1，值2，…);
  

update (修改数据）

update 表名 set 字段名1=值1，字段名2=值2，… [where 条件];

delete (删除数据)

delete from 表名 [where 条件];

DQL (数据查询语言：select）

基础查询

查询多个字段 ：

select 字段1，字段2，… from 表名;

返回所有字段 ：

select * from 表名;		# * 为通配符

• 设置别名 ：

  select 字段1 as 别名1，… from 表名;	# as 可省略
  

• 去除重复记录 ：

  select distinct 字段列表 from 表名;
  

条件查询 (where)

select 字段列表 from 表名 where 条件列表;

• 除了常见的运算符，还有：

  • between ... and ... ：在某个范围之内，闭区间

  • in(...) ：在in之后的列表中的值，多选一

  • like 占位符 ：模糊匹配（ _ 匹配单个字符， %匹配多个字符）

  • is null ：是NULL

聚合函数

• 将一列数据作为一个整体，进行纵向计算，NULL值不参与运算

• 常见聚合函数：

  • count--统计数量
  • max--最大值
  • min--最小值
  • avg--平均值
  • sum--求和

• 语法 ：select 聚合函数（字段列表） from 表名;

分组查询 (group by)

• 语法 ：

  select 字段列表 from 表名 [where 条件]  group by 分组字段名 [having 分组后过滤条件]
  

• where 与 having 区别：

  • 执行时机不同：where 是分组之前进行过滤，不满足 where 条件，不参与分组；而 having 是分组之后对结果进行过滤。
  • 判断条件不同：where 不能对聚合函数进行判断，having 可以。

排序查询 (order by)

• 语法 ：

  select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2;
  

• 排序方式 ：ASC--升序；DESC--降序。

分页查询 (limit)

• 语法 ：

  select 字段列表 from 表名 limit 起始索引 查询记录数;
  

• 起始索引 ：（页码数-1）* 每一页展示记录数。

DCL (数据控制语言)

管理用户

• 查询用户 ：

  use mysql;
  select * from user;
  

• 创建用户 ：

  create user '用户名'@'主机名' identified by '密码';
  

• 修改用户密码 ：

  alert user '用户名'@'主机名' identified with mysql_native_password by '新密码';
  

• 删除用户 ：

  drop user '用户名'@'主机名';
  

权限控制

• 常用权限：

  • 所有权限：all / all privileges

  • 查询数据：select

  • 插入数据：insert

  • 修改数据：update

  • 删除数据：delete

  • 修改表：alert

  • 删除数据库/表/视图：drop

  • 创建数据库/表：create

• 查询权限 ：

  show grants for '用户名'@'主机名';
  

• 授予权限 ：

  grant 权限列表 on 数据库名/表名 to  '用户名'@'主机名';
  

• 撤销权限 ：

  revoke 权限列表 on 数据库名/表名 to  '用户名'@'主机名';
  

函数

语法 ：

select 函数（参数）

select
	name,
	case workaddress when '' then ''end
from emp;

约束

概念 ：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的 ：保证数据库中数据的正确、有效性、完整性。

使用时机 ：在创建表的时候，对表的字段进行约束。

分类：

• 非空约束：限制该字段的数据不能为 null ，not null。

• 唯一约束：保证该字段的所有数据都是唯一、不重复的 unique。

• 主键约束：主键是一行数据的唯一标识，要求非空且唯一 primary key。

• 默认约束：保存数据时，如果为指定该字段的值，则采用默认值 default。

• 检查约束：保证字段值满足某一个条件 check。

• 外键约束：用来让两张表的数据之间建立连接，保证数据的一致性和完整性 foreign key。

  • create table 表名(
    	字段名 数据类型,
       constraint 外键名称 foreign key (外键字段名） references 主表（主表列名)
    );
    

  • alert table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);

  • 删除外键 ：alert table 表名 drop foreign key 外键名

  • 删除/更新 ：alert table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名) on update cascade on delete cascade;

多表查询

多表关系

• 一对多

  • 案例 ：部门与员工的关系。

  • 关系 ：一个部门对应多个员工，一个员工对应一个部门。

  • 实现 ：在多的一方建立外键，指向一的一方的主键。

• 多对多

  • 案例 ：学生与课程的关系。

  • 关系 ：一个学生可以选修多门课程，一门课程也可以供多个学生选择。

  • 实现 ：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

• 一对一

  • 案例 ：用户与用户详情的关系。

  • 关系 ：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其它详情字段放在另一张表中，以提升操作效率。

  • 实现 ：在任意一方假如外键，关联另外一方的主键，并且设置外键为唯一的 (unique)。

多表查询

• 概述

  • 从多张表中查询数据。
  • 笛卡尔积：笛卡尔乘积是指在数学中，两个集合 A 和 B 的所有组合情况。在多表查询时，需要消除无效的笛卡尔积。
  • 通过加入条件语句消除无效的笛卡尔积：select * from 表名1，表名2 where 条件。

• 分类

  • 连接查询

    • 内连接 ：相当于查询 A、B 交集部分数据。

    • 外连接 ：

      左外连接 ：查询 左表 所有数据，以及两张表交集部分数据。

      右外连接 ：查询 右表 所有数据，以及两张表交集部分数据。

    • 自连接：当前表与自身的连接查询，自连接必须使用表别名。

  • 子查询

内连接

语法 ：

• 隐式内连接 ：

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

• 显式内连接 ：

  select 字段列表 from 表1 [inner] join 表2 on 连接条件;
  

外连接

语法 ：

• 左外连接 ：

  select 字段列表 from 表 left [outer] join 表2 on 条件;
  

• 右外连接 ：

  select 字段列表 from 表 right [outer] join 表2 on 条件;
  

自连接

语法 ：

 select 字段列表 from 表A 别名A join 表A 别名B on 条件;

联合查询

• 关键字 ：union / union all ，区别在于，union可以去重。

• 效果 ：把多次查询的结果合并起来，形成一个新的查询结果集。

• 语法 ：

  select 字段列表 from 表A...
  union[all]
  select 字段列表 from 表B...;
  

• 联合查询的多张表的列数必须一致，字段类型也要一致。

子查询

• 概念 ：SQL语句中嵌套select语句，称为嵌套查询，也叫子查询。

• 语法 ：

  select * from t1 where column1 = (select column1 from t2);
  

• 子查询外部的语句可以是 insert/update/delete/select。

• 根据查询结果不同，分为：

  • 标量子查询（子查询结果为单个值）：= <> > >= < <=

  • 列子查询（子查询结果为一列）：in / not in / any / some / all

  • 行子查询（子查询结果为一行）：= <> in not in

  • 表子查询（子查询结果为多行多列）：in

• 根据子查询位置，分为：where 之后；from 之后；select 之后。

事务

事务简介

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作 要么同时成功，要么同时失败，例如银行转账。

事务操作

• 提交方式 ：1 为自动提交，0 为主动提交，区别在于，默认情况下，事务提交是自动提交的，即 sql 语句执行完毕之后，数据库会同步进行更新。设置为主动提交之后，执行 sql 语句，会临时改变表的内容，但数据库不会进行更新，此时需要单独执行一个提交事务的 sql 语句：commit，执行完毕之后，数据库才会进行更新。为了使程序异常的时候，不会将数据丢失，就需要用到回滚事务语句：rollback。

• 查看/设置事务提交方式

    select @@autocommit
  
    set @@autocommit = 0
  

• 开启事务

  start transaction 或 begin
  

• 提交事务

  commit
  

• 回滚事务

  rollback
  

事务四大特性

• 原子性 ：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
• 一致性 ：事务完成时，必须使所有的数据都保持一致状态
• 隔离性 ：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性 ：事务一旦提交或回滚，它对数据库中的数据的改变是永久的

并发事务问题

• 脏读 ：一个事务读到另外一个事务还没有提交的数据，这是由于事务如果为主动提交，两个并行事务，事务 A 会读取到事务 B 未提交的事务中修改的数据。
• 不可重复读 ：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读，这是由于事务 A 内有两个相同的 sql 语句读取同一数据库，但是由于事务 B 此时也在对同一数据库进行操作，而事务 B 在事务 A 再一次读取同一数据库之前，对该数据库的数据已经进行了修改，因此事务 A 就会读取到被修改过后的数据库。
• 幻读 ：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，这是由于两个并行事务，与重复读取原理一样，事务 A 在进行数据的插入时，由于事务 B 此时已经对该数据库进行了插入操作，且插入数据一样，会导致事务 A 无法进行数据的插入，但是由于在事务 A 内还未进行数据的插入，因此导致事务 A 无法插入，但也无法读取。

事务隔离级别

使用时机 ：事务开始之前设置事务隔离级别

作用 ：解决并发事务问题

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable Read	×	×	√
Serializable	×	×	×

• 查看事务隔离级别：

  select @@transaction_isolation;
  

• 设置事务隔离级别：

  set [session|global] transaction isolation level 事务隔离级别;