cmcy的文档

外观

MySQL

约 8402 字大约 28 分钟

2025-04-08

基础篇

MySQL概述

1. 数据库相关概念

名称全称简称
数据库存储数据的仓库，数据是有组织的进行存储Data Base (DB)
数据库管理系统操纵和管理数据库的大型软件Data Base Management System (DBMS)
SQL操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准Structured Query Language (SQL)
  • 主流的关系型数据库管理系统
    • Oracle
    • MySQL
    • Microsoft SQL Server
    • PostgreSQL
    • IBM Db2
    • Microsoft Access
    • SQLite
    • MariaDB
    • Microsoft Azure SQL Database
    • Hive

2. MySQL数据库准备

  • 安装一路到底即可

  • 启动与停止

    • net start mysql80
    • net stop mysql80

  • 客户端连接

    • MySQL提供的客户端命令行工具
    • 系统自带的命令行工具执行指令 mysql [-h 127.0.0.1] [-P 3306] -u root -p

3. 数据模型

  • 关系型数据库(RDBMS)

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

    idnamejobdept_id
    1金庸总裁2
    2张无忌项目经理1
    3杨逍开发3

SQL

1. SQL通用语法

  • SQL语句可以单行或多行书写，以分号结尾
  • SQL语句可以使用空格/缩进来增强语句的可读性
  • MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
  • 注释
    • 单行注释：-- 注释内容 或 # 注释内容(MySQL独有)
    • 多行注释：/* 注释内容 */

2. SQL分类

分类全称说明
DDLData Definition Language数据定义语句，用来定义数据库对象(数据库，表，字段)
DMLData Manipulation Language数据操作语言，用来对数据库表中的数据进行增删改
DQLData Query Language数据查询语句，用来查询数据库中表的记录
DCLData Control Language数据控制语言，用来创建数据库用户以及控制数据库的访问权限

3. DDL

  • 查询

    • 查询所有数据库

      SHOW DATABASES;

    • 查询当前数据库

      SELECT DATABASE();

  • 创建

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

  • 删除

    DROP DATABASE [IF EXISTS] 数据库名;

  • 使用

    USE 数据库名;

4. DDL-表操作-创建&查询

  • 查询当前数据库所有表show tables;

  • 查询表结构desc 表名;

  • 查询指定表的建表语句show create table 表名;

  • DDL-表操作-创建

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

5. DDL-数据类型及案例

分类类型大小有符号范围无符号范围描述
数值类型tinyint1 byte(-128,127)...小整数值
数值类型smallint2 bytes(-32768,32767)...大整数值
数值类型mediumint3 bytes(-8388608,8388607)...大整数值
数值类型int 或者 integer4 bytes......大整数值
数值类型bigint8 bytes(-263,263-1)...极大整数值
数值类型float4 bytes......单精度浮点数值
数值类型double8 bytes......双精度浮点数值
数值类型decimal依赖m(精度)和d(标度)同左小数值(精确定点数)

  • age tinyint unsigned

  • score double(4,1)

分类类型大小描述
字符串类型char0-255 bytes定长字符串
字符串类型varchar0-65535 bytes变长字符串
字符串类型tinyblob0-255 bytes不超过255个字符的二进制数据
字符串类型tinytext0-255 bytes短文本字符串
字符串类型blob0-65 535 bytes二进制形式的长文本数据
字符串类型text0-65 535 bytes长文本数据
字符串类型mediumblob0-16 777 215 bytes二进制形式的中等长度文本数据
字符串类型mediumtext0-16 777 215 bytes中等长度文本数据
字符串类型longblob0-4 294 967 295 bytes二进制形式的极大文本数据
字符串类型longtext0-4 294 967 295 bytes极大文本数据
  • char(10)性能好
  • varchar(10)性能差
分类类型大小范围格式描述
日期类型date31000-01-01, 9999-12-31YYYY-MM-DD日期值
日期类型time3-838:59:59, 838:59:59HH:MM:SS时间值或持续时间
日期类型year11901, 2155YYYY年份值
日期类型datetime8复合YYYY-MM-DD HH:MM:SS混合日期和时间值
日期类型timestamp41970-01-01 00:00:01 ,2038-01-19 03:14:07YYYY-MM-DD HH:MM:SS混合日期和时间值，时间戳

6. DDL-表操作-修改&删除

  • DDL-表操作-修改

    • 添加字段

      alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];

    ​ 案例:

    ​ 为emp表增加一个新的字段“昵称”为nickname

    • 修改数据类型

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

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

      alter table 表名 change 旧字段名 新字段名 类型(长度) [comment注释] [约束];

      案例:

      将emp表的nickname字段修改为username,类型为varchar(30)

    • 修改表名

      alter table 表名 rename to 新表名;

  • DDL-表操作-删除

    • 删除字段

      alter table 表名 drop 字段名;

      案例:

      将emp表的字段username删除

    • 删除表

      drop table [if exist] 表名;

    • 删除指定表，并重新创建该表

      truncate table 表名;

7. DDL小结

  • 数据库操作

    show databases;
create datebase 数据库名;
use 数据库名;
select database();#查看当前数据库
drop database 数据库名;

  • DDL-表操作

    show tables;
create table 表名(字段 字段类型，字段 字段类型);
desc 表名;#查看表的信息
show create table 表名;#查看表的详细信息
alter table 表名 add/modify/change/drop/rename to...;
drop table 表名;

8. 图形化界面工具DataGrip

  • mysql图形化界面

    • sqlyog
    • Navicat
    • DataGrip

  • 安装工具在黑马程序员的资料里面就有DataGrip

    操作也是简简单单学了一下下还是蛮好用的

9. DML-插入

  • DML介绍

    DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增删改操作

    • 添加数据(insert)
    • 修改数据(update)
    • 删除数据(delete)

  • DML-添加数据

    • 给指定字段添加数据

      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,...);

  • 注意

    • 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
    • 字符串和日期型数据应该包含在引号中。
    • 插入的数据大小，应该在字段的规定范围内。

10. DML-更新和删除

  • DML-修改数据

    update 表名 set 字段1=值1,字段名2=值2,...[where 条件];
    • 注意
      • 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据

  • DML-删除数据

    delete from 表名 [where 条件]
    • 注意
      • delete语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据
      • delete语句不能删除某一个字段的值(可以使用update)

11. DML小结

  • 添加数据

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

  • 修改数据

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

  • 删除数据

    delete from 表名[where 条件];

12. DQL-基础查询

  • DQL-介绍

    DQL英文全称Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中的表记录

    查询关键字:select

  • DQL-语法

    select
	字段列表#基本查询
from
	表名列表#基本查询
where
	条件列表#条件查询
group by
	分组字段列表#分组查询
having
	分组后条件列表#分组查询
order by
	排序字段列表#排序查询
limit
	分页参数#分页查询
    • 基本查询
    • 条件查询(where)
    • 聚合函数(count,max,min,avg,sum)
    • 分组查询(group by)
    • 排序查询(order by)
    • 分页查询(limit)

  • DQL-基本查询

    • 查询多个字段

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

    • 设置别名

      select 字段1 [as 别名1],字段2 [as 别名2] ... from 表名;

    • 去除重复记录

      select distinct 字段列表 from 表名;

13. DQL-条件查询

  • DQL-条件查询

    • 语法

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

    • 条件

      比较运算符功能
      >大于
      >=大于等于
      <小于
      <=小于等于
      =等于
      <> 或 !=不等于
      between...and...在某个范围之内(含最小,最大值)
      in(...)在in之后的列表中的值,多选一
      like 占位符模糊匹配(_匹配单个字符,%匹配任意字符)
      is NULL是NULL
      逻辑运算符功能
      and 或 &&并且(多个条件同时成立)
      or 或 ||或者(多个条件任意一个成立)
      not 或 !非,不是

14. DQL-聚合函数

  • DQL-聚合函数

    • 介绍

      将一列数据作为一个整体,进行纵向计算

    • 常见聚合函数

      函数功能
      count统计数量(行数)
      max最大值
      min最小值
      avg平均值
      sum求和(求每一行加起来的值)

    • 语法

      select 聚合函数(字段列表) from 表名;#聚合函数不包含null值

15. DQL-分组查询

  • DQL-分组查询

    • 语法

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

    • where 和 having 区别

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

    • 注意

      • 执行顺序：where > 聚合函数 > having
      • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

16. DQL-排序查询

  • DQL-排序查询

    • 语法

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

    • 排序方式

      • ASC：升序 (默认值)
      • DESC：降序

    • 注意

      如果是多字段排序，当地一个字段值相同时，才会根据第二个字段进行排序。

17. DQL-分页查询

  • DQL-分页查询

    • 语法

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

    • 注意

      • 起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数。
      • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是limit。
      • 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10.

18. DQL-执行顺序

  • DQL-执行顺序

    • 编写顺序

      select
	字段列表#基本查询
from
	表名列表#基本查询
where
	条件列表#条件查询
group by
	分组字段列表#分组查询
having
	分组后条件列表#分组查询
order by
	排序字段列表#排序查询
limit
	分页参数#分页查询

    • 执行顺序

      from
	表名列表#基本查询
where
	条件列表#条件查询
group by
	分组字段列表#分组查询
having
	分组后条件列表#分组查询
select
	字段列表#基本查询
having
	分组后条件列表#分组查询
order by
	排序字段列表#排序查询
limit
	分页参数#分页查询

19. DQL小结

  • DQL语句

    select
	字段列表#--------------->字段名[AS]别名
from
	表名
where
	条件列表#条件查询 分组之前过滤
group by
	分组字段列表#分组查询
having
	分组后条件列表#分组查询 分组之后过滤
order by
	排序字段列表#排序查询 升序ASC 降序DESC
limit
	分页参数#分页查询----->起始索引(从0开始)，每页展示记录数

20. DCL-用户管理

  • DCL-介绍

    DCL全程是Data Control Language(数据控制语言)，用来管理数据库用户，控制数据库的访问权限

  • DCL-管理用户

    • 查询用户

      use mysql;
select * from user;

    • 创建用户

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

    • 修改用户密码

      alter user '用户名'@'主机名' identified with musql_native_password by '新密码';

    • 删除用户

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

21. DCL-权限控制

  • DCL-权限控制

    MySQL中定义了很多种权限，但是常用的就以下几种：

    权限说明
    all,all privileges所有权限
    select查询权限
    insert插入数据
    update修改数据
    delete删除数据
    alter修改表
    drop删除数据库/表/视图
    create创建数据库/表

    • 查询权限

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

    • 授予权限

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

    • 撤销权限

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

    • 注意

      • 多个权限之间，使用,分隔
      • 授权时，数据库名和表名可以使用*进行通配，代表所有

22. DCL-小结

  • 用户管理

    create user '用户名'@'主机名' identified by '密码';
alter user '用户名'@'主机名' identified with musql_native_password by '新密码';
drop user '用户名'@'主机名';

  • 权限控制

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

函数

1. 函数-字符串函数

  • 函数是指可以直接被另一段程序调用的程序或代码

  • 字符串函数

    MySQL中内置了很多字符串函数，常用的几个如下：

    函数功能
    concat(s1,s2,...,sn)字符串拼接，将s1,s2,...,sn拼接成一个字符串
    lower(str)将字符串str全部转为小写
    upper(str)将字符串str全部转为大写
    lpad(str,n,pad)左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
    rpad(str,n,pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
    trim(str)去掉字符串头部和尾部的空格
    substring(str,start,len)返回从字符串str从start位置起的len个长度的字符串
    select 函数(参数)

2. 函数-数值函数

  • 数值函数

    常见的数值函数如下

    函数功能
    ceil(x)向上取整
    floor(x)向下取整
    mod(x,y)返回x/y的模
    rand()返回0~1内的随机数
    round(x,y)求参数x的四舍五入的值，保留y位小数

3. 函数-日期函数

  • 日期函数

    常见的日期函数如下：

    函数功能
    curdate()返回当前日期
    curtime()返回当前时间
    now()返回当前日期和时间
    year(date)获取指定的date的年份
    month(date)获取指定的date的月份
    day(date)获取指定的date的日期
    date_add(date, interval expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值
    datediff(date1,date2)返回起始时间date1和结束时date2之间的天数

4. 函数-流程函数

  • 流程函数也是很常用的一类函数，可以SQLl语句中实现条件筛选，从而提高语句效率

    函数功能
    if(value,t,f)如果value为true，则返回t，否则返回f
    ifnull(value1,value2)如value不为空，返回value1，否则返回value2
    case when [val1] then [res1] ... else [default] end如果val1为true，返回res1， ...否则返回default默认值
    case [expr] when [val1] then [res1] ... else [default] end如果expr的值等于val1，返回res1， ...否则返回default默认值

5. 函数-小结

  • 字符串函数

    concat,lower,upper,lpad,rpad,trim,substring

  • 数值函数

    ceil,floor,mod,rand,round

  • 日期函数

    curdate,curtime,now,year,month,day,date_add,datediff

  • 流程函数

    if,finull,case when then end

约束

1. 约束-概述

  • 概念

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

  • 目的

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

  • 分类

    约束描述关键字
    非空约束限制该字段的数据不能为nullnot null
    唯一约束保证该字段的所有数据都是唯一、不重复的unique
    主键约束主键是一行数据的唯一标识，要求非空且唯一primary key
    默认约束保存数据时，如果未指定该字段的值，则采用默认值default
    检查约束(8.0.16版本之后)保证字段值满足某一个条件check
    外键约束用来让两张表的数据之间建立连接，保证数据的一致性和完整性foreign key

2. 约束-演示

  • 根据需求，完成表结构的创建

    字段名字段含义字段类型约束条件约束关键字
    idID唯一标识int主键，并且自动增长primary key, auto_increment
    name姓名varchar(10)不为空，并且唯一not null, unique
    age年龄int大于0，并且小于等于120check
    status状态char(1)如果没有指定该值，默认为1default
    gender性别char(1)无

3. 约束-外键约束

  • 概念

    外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

    主-----------副 父------------子

  • 语法

    • 添加外键

      create table 表名(
	字段名 数据类型
    ...
    [constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
)：
      alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);

    • 删除外键

      alter table 表名 drop foreign key 外键名称;

4. 约束-外键删除更新行为

  • 删除/更新行为

    行为说明
    no action当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与restrict一致)
    restrict当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与no action一致)
    cascade当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
    set null当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null。(这就要求该外键允许取null)
    set default父表有变更时，子表将外键列设置成一个默认的值(lnnodb不支持)
    alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名) on update cascate on delete cascade;

5. 约束-小结

  • 约束种类
    • 非空约束：not null
    • 唯一约束：unique
    • 主键约束：primary key (自增：auto_increment)
    • 默认约束：default
    • 检查约束：check
    • 外键约束：foreign key

多表查询

1. 多表查询-多表关系介绍

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

  • 一对多(多对一)
    • 案例：部门与员工的关系
    • 关系：一个部门对应多个员工，一个员工对应一个部门
    • 实现：在多的一方建立外键，指向一的一方
  • 多对多
    • 案例：学生与课程的关系
    • 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
    • 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
  • 一对一
    • 案例：用户与用户详情的关系
    • 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提高操作效率
    • 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(unique)

2. 多表查询-多表查询概述

  • 概述：指从多张表中查询数据

  • 笛卡尔积：笛卡尔乘积是指在数学中，两个集合a集合和b集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

  • 多表查询分类

    • 连接查询

      • 内连接：相当于查询a、b交集部分数据

      • 外连接：

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

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

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

    • 子查询

3. 多表查询-内连接

  • 内连接查询语法：

    • 隐式内连接

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

    • 显式内连接

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

    内连接查询的是两张表交集部分

4. 多表查询-外连接

  • 外连接查询语法

    • 左外连接

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

    • 右外连接

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

5. 多表查询-自连接

  • 自连接查询语法

    select 字段列表 from 表a 别名a join 表a 别名b on 条件 ...;

    自连接查询，可以是内连接查询，也可以是外连接查询。

6. 多表查询-联合查询union，union all

  • 对于union查询，就是把多次查询的结果合并起来，幸好曾一个新的查询结果集

    select 字段列表 from 表a ...
union [all]
select 字段列表 from 表b ...;

    对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

    union all 会将全部的数据直接合并在一起，union会对合并之后的数据去重

7. 多表查询-子查询介绍

  • 概念：SQL语句中嵌套select语句，称为嵌套查询，又称子查询.

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

    子查询外部的语句可以是insert/update/delete/select的任何一个

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

    • 标量子查询（子查询结果为单个值）
    • 列子查询（子查询结果为一列）
    • 行子查询（子查询结果为一行）
    • 表子查询（子查询结果为多行多列）

8. 多表查询-标量子查询

  • 标量子查询

    子查询返回的结果是个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

    常用的操作符：= <> > >= < >=

9. 多表查询-列子查询

  • 列子查询

    子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

    常用的操作符：in、not in、any、some、all

    操作符描述
    in在指定的集合范围之内，多选一
    not in不在指定的集合范围之内
    any子查询返回列表中，有任意一个满足即可
    some与any等同，使用some的地方都可以使用any
    all子查询返回列表的所有条件都必须满足

10. 多表查询-行子查询

  • 行子查询

    子查询返回的结果是一行（可以是多列），这种子查询称为行子查询

    常用操作符：= <> in not all

  • 示例

    select * from emp where (salary,managerid)=(select salary,managerid from emp where name = '张无忌');

11. 多表查询-表子查询

  • 表子查询

    子查询返回的结果是多行多列，这种子查询称为表表查询

    常用的操作符：in

  • 示例

    select job,salary from emp where name = '鹿杖客' or name = '宋远桥';
select * from emp where (job,salary) in ( select job,salary from emp where name = '鹿杖客' or name = '宋远桥' );
    select * from emp where entrydate > '2006-01-01';
select e.*,d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id

12. 多表查询-多表查询案例

  • 根据需求，完成SQL语句的编写

    • 查询员工的姓名、年龄、职位、部门信息

      select e.name, e.age, e.job, d.name
from emp e,
     dept d
where e.dept_id = d.id;
#隐式内连接

    • 查询年龄小于30岁的员工姓名、年龄、职位、部门信息

      select e.name, e.age, e.job, d.name
from emp e
         inner join dept d on e.dept_id = d.id
where e.age < 30;
#显示内连接

    • 查询拥有员工的部门id、部门名称

      select distinct d.id, d.name
from emp e,
     dept d
where e.dept_id = d.id;
#隐式内连接去重

    • 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来

      where e.dept_id = d.id;
select e.*, d.name
from emp e
         left join dept d on e.dept_id = d.id
where e.age > 40;
#左外连接

    • 查询所有员工的工资等级

      select e.*, s.grade
from emp e,
     salgrade s
where e.salary >= s.losal
  and e.salary <= s.hisal;
#隐式内连接

    • 查询“研发部”所有员工的信息及工资等级

      select e.*, s.grade
from emp e,
     dept d,
     salgrade s
where e.dept_id = d.id
  and (e.salary >= s.losal and e.salary <= s.hisal)
  and d.name = '研发部';
#隐式内连接

    • 查询“研发部”员工的平均工资

      select avg(e.salary)
from emp e,
     dept d
where e.dept_id = d.id
  and d.name = '研发部';
#隐式内连接

    • 查询工资比“灭绝”高的员工信息

      select *
from emp
where salary > (select salary from emp where name = '灭绝');
#表子查询

    • 查询比平均薪资高的员工信息

      select *
from emp e
where salary > (select avg(salary) from emp);
#表子查询

    • 查询低于本部门平均工资的员工信息

      select *
from emp e2
where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);
#表子查询

    • 查询所有的部门信息，并统计部门的员工人数

      select d.id, d.name, (select count(*) from emp e where e.dept_id = d.id)
from dept d;
#表子查询

    • 查询所有学生的选课情况，展示出学生名称、学号、课程名称

13. 多表查询-小结

  • 多表关系
    • 一对多：在多的一方设置外键，关联一的一方的主键
    • 多对多：建立中间表，中间表包含两个外键，关联两张表的主键
    • 一对一：用于表结构拆分，在其中任何一方设置外键（unique），关联另一方的主键
  • 多表查询
    • 内连接
      • 隐式
      • 显式
    • 外连接
      • 左外
      • 右外
    • 自连接
    • 子查询

事务

1. 事务-简介

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

    默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务

2. 事务-操作演示

  • 事务操作

    • 开启事务

      start transaction; #或者 begin;

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

      select @@autocommit;
set @@autocommit=0;

    • 提交事务

      commit;

    • 回滚事务

      rollback;

3. 事务-事务的四大特性

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

4. 事务-并发事务问题

  • 并发事务问题

    问题描述
    脏读一个事务读到另一个事务还没有提交的数据。
    不可重复读一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
    幻读一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影“。

5. 事务-事务隔离级别

  • 事务隔离级别

    隔离级别脏读不可重复读幻读
    read uncommitted√√√
    read committed×√√
    repeatable read××√
    serializable×××

  • 查看事务隔离级别

    select @@transaction_isolation;

  • 设置事务隔离级别

    set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable};

  • 注意

    事务隔离级别越高，数据越安全，但是性能越低。

6. 事务-小结

  • 事务简介：事务时一组操作的集合，这组操作，要么全部执行成功，要么全部执行失败

  • 事务操作

    start transaction;#开启事务
commit / rollback;#提交/回滚事务

  • 事务四大特性

    • 原子性
    • 一致性
    • 隔离性
    • 持久性

  • 并发事务问题

    • 脏读
    • 不可重复读
    • 幻读

  • 事务隔离级别

    • read uncommitted
    • read committed
    • repeatable read
    • serializable

基础篇总结

  • MySQL概述
  • SQL
  • 函数
  • 约束
  • 多表查询
  • 事务

进阶篇

存储引擎

1. 存储引擎-MySQL体系结构

  • 连接层
  • 服务层
  • 引擎层
  • 存储层

2. 存储引擎-存储引擎简介

  • 存储引擎简介

    存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储类型也可被称为表类型

  • 查询建表语句(默认存储引擎innodb)

    show create table account;

  • 在创建表时，指定存储引擎

    create table 表名(
	字段1 字段1类型 [comment 字段1注释],
    ...
    字段n 字段n类型 [comment 字段n注释],
)engine=innodb [表注释];

  • 查询当前数据库支持的存储引擎

    show engines;

3. 存储引擎-存储引擎特点

  • InnoDB

    • 介绍

      InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎。

    • 特点

      DML操作遵循ACID模型，支持事务；

      行级锁，提高并发访问性能；

      支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

    • 文件

      xxx.ibd: xxx代表的是表名，innodb引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

      参数：innodb_file_per_table

    • 逻辑存储结构

      • tablespace: 表空间
      • segment: 段
      • extent: 区 1m 64page
      • page: 页 16k
      • row: 行

4. 存储引擎- MyISAM和Memory

  • MyISAM

    • 介绍

      MyISAM是MySQL早期的默认存储引擎

    • 特点

      不支持事务，不支持外键

      支持表锁，不支持行锁

      访问速度快

    • 文件

      xxx.sdi: 存储表结构信息 xxx.MYD: 存储数据

      xxx.MYI: 存储索引

  • Memory

    • 介绍

      Memory引擎的表数据是存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

    • 特点

      内存存放

      hash索引（默认）

    • 文件

      xxx.sdi: 存储表结构信息

  • 存储引擎特点

    特点InnoDBMyISAMMemory
    存储限制64TB有有
    事务安全支持--
    锁机制行锁表锁表锁
    B+tree索引支持支持支持
    Hash索引--支持
    全文索引支持(5.6版本之后)支持-
    空间使用高低N/A
    内存使用高低中等
    批量插入速度低高高
    支持外键支持--

5. 存储引擎-选择

  • 存储引擎选择

    在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合

    • InnoDB：

      是MySQL的默认存储引擎，支持事务、外键。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

    • MyISAM：

      如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适。

    • Memory：

      将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。Memory的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

6. 存储引擎-小结

  • 体系结构

    • 连接层
    • 服务层
    • 引擎层
    • 存储层

  • 存储引擎简介

    show engines;
create table xxxx(....) engines=innodb;

  • 存储引擎特点

    InnoDB与MyISAM：事务、外键、行级锁

  • 存储引擎应用

    InnoDB：存储业务系统中对于事务、数据完整性要求较高的核心数据。

    MyISAM：存储业务系统的非核心事务。

索引

1. 索引-索引概述

  • 介绍

    索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

公告板

持续建设中