MySQL基础

MySQL基础

目录

一、概述

启动与停止

image-20250111093040557

image-20250111093054080

image-20250111093104650

  • 其中80是服务器的名称
  • 注意：默认mysql是开机自动启动的

image-20250111093115696

客户端连接

方式一：MySQL提供的客户端命令行工具

image-20250111093132616

  • 方式二：系统自带的命令行工具执行命令

image-20250111093143560

[!CAUTION]

这种方式需要配置环境变量

数据模型

✨关系型数据库（RDBMS）：

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

  • 特点：

    1. 使用表存储数据，格式统一，便于维护。

    2. 使用SQL语言操作，标准统一，使用方便

      img

✨MySQL数据库是如何存储数据的？

  • 我们安装了MySQL，我们的计算机就成为了MySQL数据库服务器
  • 然后，我们就可以通过客户端来连接MySQL数据库管理系统dbms
  • 然后，可以使用sql语句，通过数据库管理系统来创建数据库，也可以使用sql语句通过数据库管理系统在指定的数据库当中来创建表
  1. 在一个服务器当中是可以创建多个数据库的
  2. 而在一个数据库当中又可以创建多张表
  3. 而这个表就是我们刚才所说的二维表
  4. 然后，在这个表中我们又可以存储一条一条的记录

img

二、基础内容

01 SQL

SQL通用语法

  • SQL语句可以单行或多行书写，以分号结尾。

  • SQL语句可以使用空格/缩进来增强语句的可读性。

  • Mysql数据库的SQL语句不区分大小写，关键字建议使用大写。

  • 注释：

    img

SQL分类
DDL 数据定义语言，用来定义数据库对象（数据库，表，字段）
DML 数据操作语言，用来对数据库表中的数据进行增删改
DQL 数据查询语言，用来查询数据库中表的记录
DCL 数据控制语言，用来创建数据库用户、控制数据库的访问权限
DDL

✨数据库操作

  • 设置字符集时，不建议用UTF-8,因为它存储的长度是三个字节
  • 我们使用UTF-8mb4,它支持四个字节
功能 代码
查询所有数据库 SHOW DATABASES;
查询当前数据库 SELECT DATABASE();
创建 CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]；
删除 DROP DATABASE [IF EXISTS] 数据库名;
使用 USE 数据库名;

✨表操作

功能 代码
查询当前数据库所有表 SHOW TABLES;
查询表结构 DESC 表名；
查询指定表的建表语句 SHOW CREATE TABLE 表名；

✨表操作----创建

CREATE TABLE 表名(
    字段1 字段1类型 [COMMENT 字段1注释],
    字段2 字段2类型 [COMMENT 字段2注释],
    字段3 字段3类型 [COMMENT 字段3注释],
    ......
    字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释];

[!CAUTION]

[…]为可选参数，最后一个字段后面没有逗号

📎案例：

id name age gender
1 令狐冲 28 男
2 风清扬 68 男
3 东方不败 32 男
  • 代码：
CREATE TABLE tb_user (
       id INT COMMENT'编号',
       NAME VARCHAR(50) COMMENT'名字',
       age INT COMMENT'年龄',
       gender VARCHAR(1) COMMENT'性别'
 )COMMENT'成员表';
 
 DESC tb_user;
  • 结果展示：

image-20250105215254728

✨表操作---数据类型

  • 主要分为三类：数据类型、字符串类型、日期时间类型

🖊数据类型

类型 大小 有符号（SIGNED）范围 无符号（UNSIGNED）范围 描述
TINYINT 1byte (-128,127) （0，255） 小整数值
SMALLINT 2bytes (-32768,32767) （0，65535） 大整数值
MEDIUMINT 3bytes (-8388608,8388607) （0，16777215） 大整数值
INT或INTEGER 4bytes (-2147483647,2147483647) （0，4294967295） 大整数值
BIGINT 8bytes （-2^63 , 2^63-1） （0，2^64-1） 极大整数值
FLOAT 4bytes （-3.402823466 E+38，3.402823466 E+38） 0 和（1.175494351 E-38，3.402823466 E+38） 单精度浮点数值
DOUBLE 8bytes （-1.7976931348623157 E+308，1.7976931348623157 E+308） 0 和 （2.2250738585072014 E-308，1.7976931348623157 E+308） 双精度浮点数值
DECIMAL 依赖于M（精度）和 D（标度）的值 依赖于M（精度）和 D（标度）的值 小数值（精确定点数）]

  • 精度和标度的说明：

    如：123.45

    精度：整串数字的长度是5

    标度：小数位数是2

  • 案例：

    1. 描述一个用户的年龄 age

    数据类型选则：

    ①INT ：占用4个字节，相对来说是比较占用存储空间的

    ②TINYINT UNSIGNED ：年龄不会出现负数，所以选用无符号类型

    1. 描述一个分数：只用出现一位小数

    数据类型选择：DOUBLE(4,1)

🖊字符串类型

类型 大小 描述
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过255个字符的二进制数据
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

  • 10，表示能存储的最大长度，为10

  • char(10)：定长字符串，即使存储1个字符，那么它也占用10个字符的存储空间，其他空间用空格来补位，性能好

  • varchar（10）：变长字符串，存储几个字符就占几个字符的空间，性能较差，因为varchar会根据内容去计算存储的空间

🖊日期时间类型

类型 大小 范围 格式 描述
DATE 3 1000-01-01至9999-12-31 YYYY-MM-DD 日期值
TIME 3 -838：59：59至839：59：59 HH:MM:SS 时间值或持续时间
YEAR 1 1901至2155 YYYY 年份值
DATETIME 8 1000-01-01 00：00：00至9999-12-31 23：59：59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00：00：01至1038-01-19 03：14：07 YYYY-MM-DD HH:MM:SS 混合日期和时间值，时间戳

📎案例：根据需求创建表（设计合理的数据类型、长度）

设计一张员工信息表，要求如下：

  1. 编号（纯数字）
  2. 员工工号（字符串类型，长度不超过10位）
  3. 员工姓名（字符串类型，长度不超过10位）
  4. 性别（男/女，存储一个汉字）
  5. 年龄（正常人年龄，不可能存储负数）
  6. 身份证号（二代身份证号均为18位，身份证中有X这样的字符）
  7. 入职时间（取值年月日即可）
CREATE TABLE tb_user(
	id INT COMMENT'编号',
	userid VARCHAR(10) COMMENT'员工工号',
	username VARCHAR(10) COMMENT'员工姓名',
	gender CHAR(1) COMMENT'性别',
	age TINYINT UNSIGNED COMMENT'年龄',
	idnumber CHAR(18) COMMENT'身份证号',
	entrytime DATE COMMENT'入职时间'
)COMMENT'员工信息表';

DESC tb_user;
  • 显示结果：

image-20250106100303827

✨表操作---添加、修改、删除

操作 代码
添加字段 ALTER TABLE 表名 ADD 字段名 类型（长度）[COMMENT 注释] [约束]；
修改数据类型 ALTEB TABLE 表名 MODIFY 字段名 新数据类型（长度）;
修改字段名和字段类型 ALTEB TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度）[COMMENT 注释] [约束];
删除字段 ALTEB TABLE 表名 DROP 字段名;
修改表名 ALTER TABLE 表名 RENAME TO 新表名;
删除表 DROP TABLE [IF EXISTS] 表名；
删除指定表，并重新创建该表 TRUNCATE TABLE 表名;

📎案例：

  1. 为tb_user表增加一个新的字段“昵称”为nickname，类型为varchar（20）
ALTER TABLE tb_user ADD nickname VARCHAR(20);

DESC tb_user;
  • 显示结果：

image-20250106101131983

  1. 将tb_user表的nickname字段修改为nick，类型为varchar（30）
ALTER TABLE tb_user CHANGE nickname nick VARCHAR(30);

DESC tb_user;
  • 显示结果：

image-20250106102012328

  1. 将tb_user表中的nick删除掉
ALTER TABLE tb_user DROP nick;
  1. 将tb_user表的表名修改为employee
ALTER TABLE tb_user RENAME TO employee;

SHOW TABLES;
  • 显示结果：

image-20250106102833463

[!CAUTION]

在删除表时，表中的全部数据也会被删除。

DML
  • 用来对数据库中表的数据记录进行增删改操作

✨添加数据

操作 代码
添加数据 INSERT INTO 表名 (字段1,字段2,…) VALUES(值1,值2,…);
给全部字段添加数据 INSERT INTO 表名 VALUES(值1,值2,…);
批量添加数据 ①INSERT INTO 表名(字段名1,字段名2,…) VALUES(值1,值2,…),(值1,值2,…);②INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…);

[!CAUTION]

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

📎案例：

INSERT INTO 			employee(id,userid,username,gender,age,idnumber,entrytime) VALUES(1,'1234','小明','男','24','123456789876543210','2000-01-02');

SELECT * FROM employee;
  • 显示结果

image-20250106111928881

✨修改、删除数据

操作 代码
修改数据 UPDATE 表名 SET 字段名1=值1,字段名2=值2,……[WHERE 条件];
删除数据 DELETE FROM 表名 [WHERE 条件];

[!CAUTION]

  • 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
  • DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
  • DELETE语句不能删除某一个字段的值（可以使用UPDATE）。

📎案例：修改id为1的数据，将username修改为“李华”

UPDATE employee SET username = '李华' WHERE id = 1;
 
SELECT * FROM employee;
  • 显示结果

image-20250106113039410

DQL
  • 数据查询语言，用来查询数据库中表的记录。
  • 查询的频次要高于增删改的频次。
  • 关键字：SELECT。

🖊语法：下图为编写顺序

image-20250106114750240

✨基本查询

操作 代码
查询多个字段 ①SELECT 字段1,字段2,字段3…FROM 表名；②SELECT * FROM 表名；（*代表查询返回所有字段）
设置别名 SELECT 字段1 [AS 别名1],字段2[AS 别名2]…FROM 表名；
去除重复记录 SELECT DISTINCT 字段列表 FROM 表名;

[!CAUTION]

  • 设置别名时，as是可以省略的
  • 在开发过程中尽量不要写*

✨条件查询

  1. 语法:

    SELECT 字段列表 FROM 表名 WHERE 条件列表;

  2. 条件：

    比较运算符 功能
    ＞ 大于
    >= 大于等于
    < 小于
    <= 小于等于
    = 等于
    <> 或 != 不等于
    BETWEEN…AND… 在某个范围之内（含量小、最大值）
    IN(…) 在in之后的列表中的值，多选一
    LIKE 占位符 模糊匹配（匹配单个字符__，%匹配任意个字符）
    is NULL 是NULL
    逻辑运算符 功能
    AND 或 && 并且（多个条件同时成立）
    OR 或 || 或者（多个条件任意一个成立）
    NOT 或 ！ 非，不是

📎案例：

  1. 查询年龄等于18或20或40的员工信息

    SELECT * FROM employee WHERE age in(18,20,40);

  2. 查询姓名为两个字的员工信息,用“-”,几个下划线代表几个字

    SELECT * FROM employee WHERE name like '--';

  3. 查询身份证号最后一位是X的员工信息，“%”代表前面是多少个字符无所谓，保证最后一个字符是X

    SELECT * FROM employee WHERE idcard like '%X';

✨聚合函数

  1. 介绍

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

  2. 常见聚合函数

    函数 功能
    count 统计计数
    max 最大值
    min 最小值
    avg 平均值
    sum 求和

  3. 语法

    SELECT 聚合函数(字段列表) FROM 表名;

[!CAUTION]

  • null值不参与所有聚合函数运算。
  • 执行顺序：where > 聚合函数 >having。
  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

✨分组查询

  1. 语法：

    SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

  2. where和having区别：

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

✨排序查询

  1. 语法

    SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

  2. 排序方式：

    • ASC：升序（默认值）
    • DESC：降序

[!CAUTION]

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

✨分页查询

  1. 语法

    SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

[!CAUTION]

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

✨执行顺序

image-20250106162142239

DCL
  • 数据控制语言，用来管理数据库 用户、控制数据库的访问 权限

image-20250106162641933

✨管理用户

操作 代码
查询用户 ①USE mysql;②SELECT * FROM user;
创建用户 CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’
修改用户密码 ALTER USER ‘用户名’@‘主机名’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;
删除用户 DROP USER ‘用户名’@‘主机名’;

[!CAUTION]

  • 主机名可以使用%通配
  • 这类SQL开发人员操作的比较少，主要是DBA(Database Administrator数据管理员)使用。

✨权限控制

  1. 常用权限
权限 说明
ALL,ALL PRIVILEGES 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据
ALTER 修改表
DPOP 删除数据库/表/视图
CREATE 创建数据库/表
  1. 权限控制

  • 查询权限

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

  • 授予权限

    GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

  • 撤销权限

    REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

02函数

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

例如：

image-20250106172942292

字符串函数

✨常用的字符串函数

函数 功能
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个长度的字符串

📎效果演示

  • 示例：concat：
SELECT concat('a','b','c','d');
  • 结果

image-20250106193416987

📎练习：根据需求完成以下SQL编写

​ 由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如：1号员工的工号应该为00001。

UPDATE employee set userid = LPAD(userid,5,'0');
数值函数

✨常见的数值函数

函数 功能
CEIL(X) 向上取整
FLOOR(X) 向下取整
MOD(X,Y) 返回x/y的模
RAND() 返回0~1内的随机数
ROUND(X,Y) 求参数x的四舍五入的值，保留y位小数

📎练习：根据需求完成以下SQL编写

通过数据库的函数，生成一个六位数的随机验证码

SELECT ROUND(RAND()*1000000,0);

  • 显示结果：

    image-20250106195603778

  • 进阶：如果显示结果为5位，需要在前/后补零。

    SELECT LPAD(ROUND(RAND()*1000000,0),6,'0');
日期函数

✨常见的日期函数

函数 功能
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date) 获取指定date的年份
MONTH(date) 获取指定date的月份
DAY(date) 获取指定date的日期
DATE_ADD(date,INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1，date2) 返回起始时间date1和结束时间date2之间的天数

📎示例：

SELECT CURDATE();

  • 结果显示

    image-20250106200929187

📎练习：根据需求完成以下SQL编写

查询所有员工的入职天数，并根据入职天数倒序排序

select name,datediff(curdate(),entrydate) as 'entrydates' from employee order by entrydates desc;
流程函数
  • 流程控制函数在SQL语句中实现条件筛选，从而提高语句的效率。
函数 功能
if(value,t,f) 如果value为ture，则返回t，否则返回f
ifnull(value1,value2) 如果value1不为空，返回value1，否则返回value2
case when [val1] then [res1]…else[default] end 如果val1为ture，返回res1,……否则返回default默认值
case [expr] when [val1] then [res1] …else [default] end 如果expr的值等于val1，返回res1,……否则返回default默认值

📎练习：根据需求完成以下SQL编写

统计班级各个成员的成绩，展示的规则如下：

  • 大于等于85，展示优秀
  • 大于等于60，展示及格
  • 否则，展示不及格

已知：

image-20250107085457444

create table score(
	id int comment 'ID',
	name varchar(20) comment '名字',
	math int comment '数学',
	english int comment '英语',
	chinese int comment '语文'
)comment '学员成绩表';

insert into score(id,name,math,english,chinese) values (1,'Tom',67,88,95),
(2,'ROSE',23,66,90),
(3,'Jack',56,98,76);

select 
	id,
	name,
	(case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end)'数学',
	(case when english>=85 then '优秀' when english>=60 then '及格' else '不及格' end)'英语',
	(case when chinese>=85 then '优秀' when chinese>=60 then '及格' else '不及格' end)'语文'
from score;
  • 显示结果

image-20250107093621254

03约束

  • 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
  • 目的：保证数据库中数据的正确、有效性和完整性。
  • 分类：
约束 描述 关键字
非空约束 限制该字段的数据不能为null not null
唯一约束 保证该字段的所有数据都是唯一、不重复的 unique
主键约束 主键是一行数据的唯一标识，要求非空且唯一 primary key
默认约束 保存数据时，如果未指定该字段的值，则采用默认值 default
检查约束（8.0.16版本之后） 保证字段值满足某一个条件 check
外键约束 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 foreign key

[!CAUTION]

约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

约束演示

📎案例：根据需求，完成表结构的创建

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

[!CAUTION]

多个约束之间用空格分开

create table user(
	id int primary key auto_increment comment'主键',
	name varchar(10) not null unique comment '姓名',
	age int check(age>0 && age<=120) comment '年龄',
	status char(1) default '1' comment '状态',
	gender char(1) comment '年龄'
)comment '用户表';
外键约束
  • 概念：外键用来让两个表的数据之间建立连接，从而保证数据的一致性和完整性。
  • 示例：

image-20250111093247776

[!CAUTION]

​ 目前上述的两个表，只是逻辑上有这个关系，但在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性。

  • 语法：

📎添加外键

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

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

例如：

alter table user add constraint fk_user_dept_id foreign key (dept_id) refenences dept(id);

[!CAUTION]

黄色的小钥匙，代表主键；蓝色的小钥匙，代表外键

📎删除外键

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

📎删除/更新行为

行为 说明
no action 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与RESTRICT一致）
restrict 当在父表中删除/更新记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与NO ACTION一致）
cascade 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
set null 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）
set default 父表有变更时，子表将外键列设置成一个默认的值（lnnodb不支持）

📎语法演示

  • cascade
alter table 表名 add constraint 外键名称 foreign key 主表名(主表字段名) on update on delete cascade;

[!CAUTION]

cascade：

  • 在更新时它也会更新外键在子表中的记录。
  • 当删除附表中的数据时，如果附表的这条数据在子表当中存在外界关联，子表当中的数据也会被删除

04多表查询

  • 概述：指从多张表中查询数据
  • 语法：
select * from [表1名],[表2名]...,[表n名];

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

image-20250111093259974

  • 消除掉无效的笛卡尔积后

image-20250111093306525

✨案例：

image-20250111093314377

  • 代码：
select * from emp , dept where emp.dept_id = dept.id;
  • 结果：

image-20250111093324798

✨多表查询的分类

  1. 连接查询

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

    • 外连接：

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

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

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

  2. 子查询

多表关系

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

（1）一对多（多对一）

（2）多对多

（3）一对一

✨一对多（多对一）

  • 案例：部门与员工的关系
  • 关系：一个部门对应多个员工，一个员工对应一个部门
  • 实现：在多的一方建立外键，指向一的一方的主键

image-20250111093334226

✨多对多

  • 案例：学生与课程的关系
  • 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
  • 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

image-20250111093341994

📎例题

image-20250111093354431

create table student(
	id int primary key auto_increment comment 'ID',
	name varchar(20) comment'名字',
	no int comment'编号'
)comment'学生表';

insert into student (name,no) value ('黛绮丝',2000100101),('谢逊',2000100102),('殷天正',2000100103),('韦一笑',2000100104);

create table course(
	id int primary key auto_increment comment'主键',
	name varchar(20) comment'名字'
)comment '课程表';

insert into course (name) value ('Java'),('PHP'),('MySQL'),('Hadoop');
  • 建立一张中间表
create table student_course(
	id int primary key auto_increment comment'主键',
	studentid int not null comment'学生ID',
	courseid int not null comment'课程ID',
	constraint fk_courseid foreign key (courseid) references course (id),
	constraint fk_studentid foreign key(studentid) references student (id)
)comment'中间表';

insert into student_course (studentid,courseid) value (1,1),(1,2),(1,3),(2,2),(2,3),(3,4);

image-20250111093407502

✨一对一

  • 案例：用户与用户详情的关系
  • 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
  • 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

image-20250111093415413

image-20250111093422903

内连接

✨内连接查询语法：

  • 隐式内连接：
select 字段列表 from 表1,表2 where 条件...;
  • 显示内连接
select 字段列表 from 表1 [inner] join 表2 0n 连接条件...

📎内连接演示

  1. 查询每一个员工的姓名，及关联的部门的名称（隐式内连接）
--可以给表起别名，as可以省略
select e.name,d.name from emp e,dept d where e.dept_id = d.id;
  1. 显示内连接
select e.name,d.name from emp e inner join dept d on e.dept_id = d.id;

[!CAUTION]

inner关键字可以省略

外连接

image-20250111093430498

✨外连接查询语法

  1. 左外连接：相当于查询表1（左表）的所有数据 包含 表1和表2交集部分的数据
select 字段列表 from 表1 left [outer] join 表2 on 条件...;
  1. 右外连接：相当于查询表2（右表）的所有数据 包括 表1和表2交集部分的数据
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
自连接
  • 自连接查询，可以是内连接查询，也可以是外连接查询。如图所示：

image-20250111093438761

  • 语法：
select 字段列表 from 表A 别名A join 表A 别名B on 条件...;

[!CAUTION]

必须对表起别名，把一张表看成两个表

📎示例：查询员工 及其 所属领导的名字

select a.name , b.name from emp a,emp b where a.managerid=b.id;
联合查询（union，union all）

  • 对应union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

  • 语法

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

[!CAUTION]

  • union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。
  • 不是所有的查询都可以用union或union all，对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
子查询
  • 概念：SQL语句中嵌套select语句，称为嵌套语句，又称子查询。
select * from t1 where column1 = (select column1 from t2);

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

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

    1. 标量子查询（子查询结果为单个值）
    2. 列子查询（子查询结果为一列）
    3. 行子查询（子查询结果为一行）
    4. 表子查询（子查询结果为多行多列）

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

✨标量子查询

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

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

✨列子查询

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

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

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

📎案例：

查询“销售部” 和 “市场部” 的所有员工信息

分为三步：

  1. 查询“销售部” 和 “市场部” 的部门id
select id from dept where name = '销售部' or name = '市场部';
  1. 根据部门id，查询员工信息
select * from emp where dept_id in(2,4);
  1. 将上面两条SQL合并为一条
select * from emp where dept_id in(select id from dept where name = '销售部' or name = '市场部';);

✨行子查询

  • 子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
  • 常用的操作符：= 、 <>、in、not in

📎案例：

查询与“张无忌” 的薪资及直属领导相同的员工信息；

分为三部：

  1. 查询“张无忌”的薪资及直属领导
select salary,managerid from emp where name = '张无忌';
  1. 查询与“张无忌”的薪资及直属领导相同的员工信息
select * from emp where salary = 12500 and managerid = 1;

也可以写为

select * from emp where (salary , managerid) = (12500,1);
  1. 将上面两个代码合并
select * from emp where (salary , managerid) = (select salary,managerid from emp where name = '张无忌';);
表子查询

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

  • 常用的操作符：in

📎案例：

查询与 “鹿杖客”，“宋远桥”的职位和薪资相同的员工信息

分为两步：

  1. 查询“鹿杖客”，“宋远桥”的职位和薪资
select job,salary from emp where name ='鹿杖客' or name ='宋远桥';
  1. 查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息,并合并
select * from emp where (job,salary) in (select job,salary from emp where name ='鹿杖客' or name ='宋远桥');

05事务

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

  • 示例：银行转账

image-20250110200534905

[!CAUTION]

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

事务操作
image-20250110201651918 
create table account(
	id int auto_increment primary key comment'主键ID',
	name varchar(10) comment'姓名',
	money int comment'余额'
)comment'账户表';

insert into account(name,money) values ('张三',2000),('李四',2000);
  • 转账操作（张三给李四转账1000）
  1. 查询张三账户余额
select * from account where name ='张三';
  1. 将张三账户余额-1000
update account set money = money-1000 where name = '张三';
  1. 将李四账户余额+1000
update account set money = money+1000 where name = '李四';

显示结果：转账成功

image-20250110202348029
  • 模拟异常情况
  1. 恢复张三和李四的数据
update account set money = 2000 where name ='张三' or name ='李四';
  1. 在转账操作2、3过程中间直接输入“程序抛出异常…”
  2. 显示结果：张三-1000，而李四的钱没变化
image-20250110202910284

​ 这时出现问题，我们需要把转账操作控制在一个事务范围之内，而在上述代码中每个SQL语句都是一个事务，在MySQL中的事务默认是自动提交的。

✨事务提交方式为0的情况

  1. 查看/设置事务提交方式(如果为1是自动提交，如果为0是手动提交)
select @@autocommit;
set @@autocommit = 0;
  1. 提交事务
commit;
  1. 回滚事务（代表事务就此结束了）
rollback;

✨事务提交方式为1的情况

  1. 开启事务
start transaction 或 begin;
  1. 提交和回滚事务和上述一样

[!CAUTION]

只要有start transaction 就代表着要手动提交事务

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

[!CAUTION]

数据库当中的数据是存储在磁盘中的，存在磁盘中的数据就会永久保留下来

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

一、脏读

​ 事务A要执行三个操作首先执行了查询和更新id=1的操作，这时事务A还没有执行完成，这时事务B第一条SQL正好执行查询id=1的操作，将事务A更新的数据拿到了，但此时A事务没有提交。

image-20250111093507203

二、不可重复读

​ 事务A，第一步，先执行一个select语句，再去执行第二步操作。在执行第二步操作的过程中，B事务执行了一个update操作，会将id=1这条数据更新，更新完之后将这条数据提交到了数据库。A事务再去往下执行，执行到了第三步操作，又执行了一个查询操作而且和第一条查询语句是一模一样的，都是执行id=1的数据，但此时发现第三步查询的数据和第一步不一样。

image-20250110210851910

三、幻读

​ 事务A，第一步查询id=1的数据，此时没有。这时，有一个并发的事务B，操作数据库，执行了一条添加操作，在id=1里面添加数据并提交到数据库，此时，数据库里面存在id=1的数据。事务A，第二步插入id=1的数据，此时插入不成功，因为id是主键，已经插入了id=1的数据，再插入会出现主键重复。再执行第三步，查询操作，id=1的数据没有。

image-20250110211435921

事务隔离级别
隔离级别 脏读 不可重复读 幻读
read uncommitted √ √ √
read committed × √ √
repeatable read(默认) × × √
serializable × × ×
  1. 查看事务隔离级别
select @@transaction_insolation;
  1. 设置事务隔离级别
set [session global] transaction level {read uncommtted | read commtted | repetable read | serializable}

[!CAUTION]

  • 四种隔离级别，从上到下级别越来越高。
  • 事务隔离级别越高，数据越安全，但是性能越低。
posted @ 2025-01-11 09:39  大婉神嬚  阅读(62)  评论(0)    收藏  举报