一切有为法 ✨ 应作如是观 ☀️|
收藏闪存小组博问

sy0313

园龄：4年9个月粉丝：10关注：1

在docker中对mySQL的基本操作

合集 - 数据库(2)
1.mongoDB数据库安装与配置
2.在docker中对mySQL的基本操作
收起

使用docker跑一个mySQL镜像,设置数据挂载目录和root账户密码：


如果navicat报错Authentication plugin ‘caching_sha2_password‘ cannot be loaded则在mySql容器中依次输入：

  1. (登录mysql)
    mysql -h localhost -u root -p
    2.修改身份验证类型(修改密码)
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

#刷新权限
FLUSH PRIVILEGES;

然后可以正常使用navicat连接。

新建数据库和表

  1. 在navicat中新建hello-mysql数据库，新建一个查询
  2. 先建立一个表
CREATE TABLE student(
   id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Id',
   name VARCHAR(50) NOT NULL COMMENT '学生名',
   gender VARCHAR(10) NOT NULL COMMENT '性别',
   age INT NOT NULL COMMENT '年龄',
   class VARCHAR(50) NOT NULL COMMENT '班级名',
   score INT NOT NULL COMMENT '分数'
) CHARSET=utf8mb4

id为主键，且设置自动增长
name为名字，非空
gender为性别，非空
age为年龄，非空
class为班级名，非空
score为成绩，非空
3. 插入一些数据:

INSERT INTO student ( NAME, gender, age, class, score )
VALUES
	( '张三', '男', 18, '一班', 90 ),
	( '李四', '女', 19, '二班', 85 ),
	( '王五', '男', 20, '三班', 70 ),
	( '赵六', '女', 18, '一班', 95 ),
	( '钱七', '男', 19, '二班', 80 ),
	( '孙八', '女', 20, '三班', 75 ),
	( '周九', '男', 18, '一班', 85 ),
	( '吴十', '女', 19, '二班', 90 ),
	( '郑十一', '男', 20, '三班', 60 ),
	( '王十二', '女', 18, '一班', 95 ),
	( '赵十三', '男', 19, '二班', 75 ),
	( '钱十四', '女', 20, '三班', 80 ),
	( '孙十五', '男', 18, '一班', 90 ),
	( '周十六', '女', 19, '二班', 85 ),
	( '吴十七', '男', 20, '三班', 70 ),
	( '郑十八', '女', 18, '一班', 95 ),
	( '王十九', '男', 19, '二班', 80 ),
	( '赵二十', '女', 20, '三班', 75 );

基础练习

  1. 指定查询的列：
SELECT name,score FROM student;
  1. 通过as为返回列指定别名
SELECT name as '名字',score as '分数' FROM student;
  1. 带条件查询
SELECT name AS
	'名字',
	class AS '班级' 
FROM
	student 
WHERE
	age >= 19;
  1. 多个条件使用and连接：
SELECT name as '名字',class as '班级' FROM student WHERE gender='男' and score>=90;
  1. 使用like做模糊查询
//查询名字以“王”开头的学生
SELECT * FROM student WHERE name LIKE '王%';
  1. 通过in指定一个集合
//查询一班和二班的所有学生
SELECT * FROM student WHERE class in('一班','二班')
  1. 通过not in来查询不在指定集合中的数据：
SELECT * FROM student WHERE class NOT IN ('一班','二班')
  1. 通过between and来指定一个区间
SELECT * FROM student WHERE age BETWEEN 18 AND 20;
  1. 分页返回
SELECT * FROM student LIMIT 0,5;
SELECT * FROM student LIMIT 5,5;
  1. 通过order by来指定排序的列
//order by 指定根据 score 升序排列，如果 score 相同再根据 age 降序排列。
SELECT name,score,age FROM student ORDER BY score asc,age desc;
  1. 使用group by分组查询
SELECT
	class AS '班级',
	AVG( score ) AS '平均成绩' 
FROM
	student 
GROUP BY
	class 
ORDER BY
	'平均成绩' DESC;

这里用到不少新语法：

根据班级来分组是 GROUP BY class。

求平均成绩使用 sql 内置的函数 AVG()。

之后根据平均成绩来降序排列。

  1. count内置函数
SELECT
	class,
	count( * ) AS count 
FROM
	student 
GROUP BY
	class;
  1. having过滤查询结果
SELECT
	class,
	AVG( score ) AS avg_score 
FROM
	student 
GROUP BY
	class 
HAVING
	avg_score > 90;

14.distinct去重

SELECT DISTINCT class FROM student;
  1. 过一遍所有内置函数
    函数分为如下几类：

    • 聚合函数(用于对数据的统计),比如AVG、COUNT、SUM、MIN、MAX

            SELECT
	class AS '班级',
	avg( score ) AS '平均成绩',
	count( * ) AS '人数',
	sum( score ) AS "总成绩",
	min( score ) AS '最低分',
	max( score ) AS '最高分' 
FROM
	student 
GROUP BY
	class

    • 字符串函数(用于对字符串的处理) 比如CONCAT、SUBSTR、LENGTH、UPPER、LOWER

               SELECT
	CONCAT( 'xx', NAME, 'yy' ),
	SUBSTR( NAME, 2, 3 ) as '名字',
	LENGTH( NAME ),
	UPPER( 'aa' ),
	LOWER( 'TT' ) 
FROM
	student;

      其中，substr 第二个参数表示开始的下标（mysql 下标从 1 开始），所以 substr('一二三',2,3) 的结果是 '二三'。
      当然，也可以不写结束下标 substr('一二三',2)

    • 数值函数(用于对数值的处理) 比如ROUND、CEIL、FLOOR、ABS、MOD

       SELECT
 	ROUND( 1.234567, 2 ),
 	CEIL( 1.234567 ),
 	FLOOR( 1.234567 ),
 	ABS( - 1.234567 ),
 	MOD ( 5, 2 );

      分别是 ROUND 四舍五入、CEIL 向上取整、FLOOR 向下取整、ABS 绝对值、MOD 取模。

    • 日期函数:(对日期、时间进行处理),比如DATE、TIME、YEAR、MONTH、DAY

      SELECT YEAR
	( '2023-06-01 22:06:03' ),
	MONTH ( '2023-06-01 22:06:03' ),
	DAY ( '2023-06-01 22:06:03' ),
	DATE( '2023-06-01 22:06:03' ),
	TIME( '2023-06-01 22:06:03' );

    • 条件函数：根据条件是否成立返回不同的值,比如IF、CASE

      SELECT NAME
	,
IF
	( score >= 60, '及格', '不及格' ) 
FROM
	student;

  //case案例
  SELECT NAME
  	,
  	score,
  CASE
  	
  	WHEN score >= 90 THEN
  	'优秀' 
  	WHEN score >= 60 THEN
  	'良好' ELSE '差' 
  	END AS '档次' 
  FROM
  student;

    • 系统函数（用于获取系统信息）比如VERSION、DATABASE、USER

      select VERSION(), DATABASE(), USER()

    • 其他函数NULLIF、COALESCE、GREATEST、LEAST
      NULLIF：如果相等返回 null，不相等返回第一个值。
      COALESCE：返回第一个非 null 的值：
      GREATEST、LEAST：返回几个值中最大最小的。

      select NULLIF(1,1), NULLIF(1,2);
select COALESCE(null, 1), COALESCE(null, null, 2);
select GREATEST(1,2,3),LEAST(1,2,3,4);
  • 类型转换函数(转换类型为另一种),比如CAST、CONVERT、DATE_FORMAT、STR_TO_DATE

一对一、join查询、级联方式

数据库中会有很多表，分别存储不同的信息，比如学生表存学生的信息，教师表存教师的信息，班级表存班级的信息，这些表之间不是孤立的，有着一定的联系。
比如班级和学生之间是一对多的关系，也就是一个班级可以有多个学生;
班级和老师是多对多的关系，也就是一个班级可以有多个老师，一个老师也可以教多个班级
如何存储一对一、一对多、多对多这些关系呢？
答案是使用外键

比如一对一的关系，一个用户只能有一个身份证。
这样两个表，分别存储用户信息，还有身份证信息:

他们之间是一对一的关系，这是可以用外键来表示：

user表的主键是id,可以通过id来唯一标识一个user
那id_card想查找user,自然也是通过id来查找，多一个列来存储user的id就可以实现这种一对一的关联。
这个user_id的列就是外键
user表叫主表，使用外键引用他的id_card表是从表

我们建个表来实践一下:
建立user表：

CREATE TABLE `hello-mysql`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` VARCHAR(45) NOT NULL COMMENT '名字',
  PRIMARY KEY (`id`)
);

然后再建立一个id_card表:

CREATE TABLE `id_card` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `card_name` varchar(45) NOT NULL COMMENT '身份证号',
  `user_id` int DEFAULT NULL COMMENT '用户 id',
  PRIMARY KEY (`id`),
  INDEX `card_id_idx` (`user_id`),
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
)  CHARSET=utf8mb4

插入几条数据：

INSERT INTO `user` (`name`)
	VALUES
		('张三'),
		('李四'),
		('王五'),
		('赵六'),
		('孙七'),
		('周八'),
		('吴九'),
		('郑十'),
		('钱十一'),
		('陈十二');

查询一下：
select * from user;

user表数据成功插入

再插入id_card表的数据:

INSERT INTO id_card (card_name, user_id) 
    VALUES
        ('110101199001011234',1),
	('310101199002022345',2),
	('440101199003033456',3),
	('440301199004044567',4),
	('510101199005055678',5),
	('330101199006066789',6),
	('320101199007077890',7),
	('500101199008088901',8),
	('420101199009099012',9),
	('610101199010101023',10);

查询一下:
SELECT * FROM id_card;

这样一对一关系的数据就插入成功了！
那应该如何关联查询出来呢？
像这样:

SELECT
	*
FROM
	USER JOIN id_card ON USER.id = id_card.user_id

这里就用到了JOIN ON,也就是连接user和id_card表，关联方式是user.id=id_card.user_id，也就是id_card表中的外键关联user表中的主键.

查询结果是这样的：

这里的两个 id 分别是 user 和 card 的 id，而且后面的 user_id 也没必要展示。
更改一下查询sql,指定显示的列，并给 id_card 表的 id 起个 card_id 的别名:

SELECT
	user.id,
	name,
	id_card.id AS card_id,
	card_name
	
FROM
	USER JOIN id_card ON user.id = id_card.user_id

这就是多表关联查询，语法是JOIN ON
有些情况，id_card表里的某条记录没有关联user表,像这样:

这时候再执行上面的查询语句，就会发现少了这条记录:

因为JOIN ON默认是INNER JOIN ON,相当于这么写：

SELECT user.id, name, id_card.id as card_id, card_name 
    FROM user
    INNER JOIN id_card ON user.id = id_card.user_id;
  • INNER JOIN是只能返回两个表中能够关联上的数据
  • LEFT JOIN是额外返回左表中没有关联上的数据
  • RIGHT JOIN是额外返回右表中没有关联上的数据
    在FROM后的是左表，JOIN后的是右表

我们来试一下：

SELECT 
	user.id,
	user.NAME,
	id_card.id,
	id_card.card_name
	
FROM
	user RIGHT JOIN id_card ON user.id = id_card.user_id


可以看到额外返回了id_card表(右表)中的所有数据，没有关联user的记录user信息部分为null。

当使用LEFT JOIN时，正好相反:

SELECT 
	user.id,
	user.NAME,
	id_card.id,
	id_card.card_name
	
FROM
	user LEFT JOIN id_card ON user.id = id_card.user_id

返回了左表中的全部信息，没有关联id_card的记录id_card部分为null。

一般情况，还是用默认的 JOIN ON 比较多，也就是 INNER JOIN。

前面还讲到了删除和更新时的级联操作。
也就是当 user 删除的时候，关联的 id_card 要不要删除？
当 user 的 id 修改的时候，关联的 id_card 要不要改 user_id？
我们之前设置的是默认的RESTICT:

这四种可选值分别代表:

  • CASCADE： 主表主键更新，从表关联记录的外键跟着更新，主表记录删除，从表关联记录删除
  • SET NULL: 主表主键更新或者主表记录删除，从表关联记录的外键设置为null
  • RESTRICT：只有主表没有从表的关联记录时，才允许删除主表记录或者更新主表记录的主键id
  • NO ACTION: 同RESTRICT。只是sql标准中分了4种，但是mysql里NO ACTION等于RESTRICT

我们来试一下,现在user表是这样的:

右键选择删除第一条：

这是会提示删除失败，因为有外键的约束。

然后更改id为11:

提示更新失败，也是因为有外键的约束：

这就是 RESTIRCT 和 NO ACTION 的处理逻辑：只要从表有关联记录，就不能更新 id 或者删除记录。

我们手动把从表记录的关联去掉，也就是删除第一条记录的外键：

然后再试一下主表的更新：

更新成功了！
再来试一下删除：

删除成功了！这就是 RESTRICT 或者 NO ACTION，只有当从表没有关联的记录的时候，才能更新主表记录的 id 或者删除它。

我们再来试试 CASCADE：

修改外键级联方式为CASCADE
先看一下id_card表的数据:

把id为2的user更改为22:

再看下 id_card 表的数据，你会发现 user_id 跟着改了。

然后把id为22的user删除掉，id_card表中user_id为22的记录也删除了:

这就是级联方式为 CASCADE 的处理逻辑：主表删除，从表关联记录也级联删除，主表 id 更新，从表关联记录也跟着更新。

然后再试一下 SET NULL:

查询下现在id_card的数据:

把user表中id为5的记录更改为55

这个时候id_card中的那条记录的外键就被设置为null了:

这就是 set null 的处理逻辑：主表记录删除或者修改 id，从表关联记录外键置为 null。

一对多、多对多关系的表设计

一对多关系在生活中随处可见:
一个作者可以写多篇文章，而每篇文章只能属于一个作者

一个订单有多个商品，而商品只能属于一个订单

一个部门有多个员工，员工只属于一个部门：

多对多关系也是随处可见:
一篇文章可以有多个标签，一个标签可以多篇文章都有。

一个学生可以选修多门课程，一门课程可以被多个学生选修。

一个用户可以有多个角色，一个角色可能多个用户都有。

那么在数据库里如何建模这种关系呢？

我们分别来看一下：

一对多的关系，比如一个部门有多个员工。

我们会有一个部门表和一个员工表：

在员工表添加外键 department_id 来表明这种多对一关系：


其实和一对一关系的数据表设计是一样的。
我们添加这两个表。

同样的方式创建 employee 表：

设置外键约束为set NULL;因为部门没了员工不一定也没了，可能会分配到别的部门

向部门表里插入几条数据:

INSERT INTO `department` (`id`, `name`) 
    VALUES 
        (1, '人事部'),
        (2, '财务部'),
        (3, '市场部'),
        (4, '技术部'),
        (5, '销售部'),
        (6, '客服部'),
        (7, '采购部'),
        (8, '行政部'),
        (9, '品控部'),
        (10, '研发部');

然后查询一下：

再向员工表插入几条数据:

INSERT INTO `employee` (`id`, `name`, `department_id`)
    VALUES 
        (1, '张三', 1),
        (2, '李四', 2), 
        (3, '王五', 3),
        (4, '赵六', 4),
        (5, '钱七', 5),
        (6, '孙八', 5),
        (7, '周九', 5),
        (8, '吴十', 8),
        (9, '郑十一', 9),
        (10, '王十二', 10);

然后查询一下：

我们通过JOIN ON关联查询下id为5的部门下所有的员工

select * from department JOIN employee on department.id = employee.department_id where department.id = 5

可以看到，正确查找出了销售部的 3 个员工：

JOIN ON 默认是 INNER JOIN。

我们再来试试 LEFT JOIN 和 RIGHT JOIN：
from 后的是左表，可以看到两个还没有员工的部门也显示在了结果里：

然后是 RIGHT JOIN：

因为所有的员工都是有部门的，所以和 inner join 结果一样：
把 id 为 5 的部门删掉，然后可以看到销售部下的三个员工的部门被设置为NULL了：

这就是 SET NULL的级联处理。
一对多是不是还挺简单
确实，他和一对一没啥本质的区别;

接下来我们来看多对多。

比如文章和标签：

之前一对多关系是通过在多的一方添加外键来引用的一方的id:

但是现在是多对多了，每一方都是多的一方，这时候是不是双方都要添加外键呢？
一般我们是这么设计的:

文章一个表、标签一个表，这两个表都不保存外键，然后添加一个中间表来保存双方的外键。
这样文章和标签的关联关系就都被保存到了这个中间表里。

我们试一下：

这次我们就直接通过 sql 建表了：

CREATE TABLE `article` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `title` VARCHAR(50) NOT NULL,
 `content` TEXT NOT NULL,
 PRIMARY KEY (`id`)
) CHARSET=utf8mb4;

这里的 TEXT 是长文本类型，可以存储 65535 长度的字符串。
然后插入几条数据:

INSERT INTO `article` (`title`, `content`)
    VALUES
            ('文章1', '这是文章1的内容。'),
            ('文章2', '这是文章2的内容。'),
            ('文章3', '这是文章3的内容。'),
            ('文章4', '这是文章4的内容。'),
            ('文章5', '这是文章5的内容。');

再查询一下：

然后创建标签表：

CREATE TABLE `tag` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`id`)
);

插入几条数据:

INSERT INTO `tag` (`name`)
    VALUES
            ('标签1'),
            ('标签2'),
            ('标签3'),
            ('标签4'),
            ('标签5');

查询一下：

可以看到，建表和插入数据都成功了。

然后创建中间表:
中间表还是通过可视化的方式创建吧：


primary key (article_id, tag_id) 是指定复合主键。

后面分别是添加两个外键约束。

建表 sql 能看懂即可，不需要手写。
然后插入几条数据：

INSERT INTO `article_tag` (`article_id`, `tag_id`)
    VALUES
    (1,1), (1,2), (1,3),
    (2,2), (2,3), (2,4),
    (3,3), (3,4), (3,5),
    (4,4), (4,5), (4,1),
    (5,5), (5,1), (5,2);

查询一下

那现在有了 article、tag、article_tag 3 个表了，怎么关联查询呢？
我们可以JOIN3个表

SELECT
	* 
FROM
	article a
	JOIN article_tag AT ON a.id = AT.article_id
	JOIN tag t ON t.id = AT.tag_id 
WHERE
	a.id = 1;

这样查询出的就是 id 为 1 的 article 的所有标签：

当然，一般我们会指定返回的列：

SELECT
	t.NAME AS 标签名,
	a.title AS 文章标题 
FROM
	article a
	JOIN article_tag AT ON a.id = AT.article_id
	JOIN tag t ON t.id = AT.tag_id 
WHERE
	a.id =1

此外，我们把文章1 删除试试：

可以看到关系也被级联删除了，这就是 CASCADE 的作用。

当然，删除的只是关系，并不影响 id=1 的标签：

这就是多对多数据的表设计、关联查询和级联方式。

本文作者：sy0313

本文链接：https://www.cnblogs.com/sunyan97/p/17848702.html

版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。

posted @   sy0313  阅读(64)  评论(0)  编辑  收藏  举报
相关博文：
· RBAC权限控制 （Role Based Access Control）
· Dockerfile基础
· 外键和多表建立 连表查询
· 数据库表设计中的关系实现：多对多、一对多、一对一
· 数据库一对一、一对多、多对多关系
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· 单线程的Redis速度为什么快？
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 展开说说关于C#中ORM框架的用法！
点击右上角即可分享
微信分享提示
评论
收藏
关注
推荐
深色
回顶
收起