当前位置: 首页 > news >正文

02-mysql高级-

文章目录

    • mysql高级
    • 1,约束
      • 1.1 概念
      • 1.2 分类
      • 1.3 非空约束
      • 1.4 唯一约束
      • 1.5 主键约束
      • 1.6 默认约束
      • 1.7 约束练习
      • 1.8 外键约束
        • 1.8.1 概述
        • 1.8.2 语法
        • 1.8.3 练习
    • 2,数据库设计
      • 2.1 数据库设计简介
      • 2.2 表关系(一对多)

mysql高级

今日目标

  • 掌握约束的使用

  • 掌握表关系及建表原则

  • 重点掌握多表查询操作

  • 掌握事务操作

1,约束

在这里插入图片描述

上面表中可以看到表中数据存在一些问题:

  • id 列一般是用标示数据的唯一性的,而上述表中的id为1的有三条数据,并且 马花疼 没有id进行标示

  • 柳白 这条数据的age列的数据是3000,而人也不可能活到3000岁

  • 马运 这条数据的math数学成绩是-5,而数学学得再不好也不可能出现负分

  • 柳青 这条数据的english列(英文成绩)值为null,而成绩即使没考也得是0分

针对上述数据问题,我们就可以从数据库层面在添加数据的时候进行限制,这个就是约束。

1.1 概念

  • 约束是作用于表中列上的规则,用于限制加入表的数据

    例如:我们可以给id列加约束,让其值不能重复,不能为null值。

  • 约束的存在保证了数据库中数据的正确性、有效性和完整性

    添加约束可以在添加数据的时候就限制不正确的数据,年龄是3000,数学成绩是-5分这样无效的数据,继而保障数据的完整性。

1.2 分类

  • 非空约束: 关键字是 NOT NULL

保证列中所有的数据不能有null值。

例如:id列在添加 马花疼 这条数据时就不能添加成功。

  • 唯一约束:关键字是 UNIQUE

    保证列中所有数据各不相同。

    例如:id列中三条数据的值都是1,这样的数据在添加时是绝对不允许的。

  • 主键约束: 关键字是 PRIMARY KEY

主键是一行数据的唯一标识,要求非空且唯一。一般我们都会给没张表添加一个主键列用来唯一标识数据。

例如:上图表中id就可以作为主键,来标识每条数据。那么这样就要求数据中id的值不能重复,不能为null值。

  • 检查约束: 关键字是 CHECK

    保证列中的值满足某一条件。

    例如:我们可以给age列添加一个范围,最低年龄可以设置为1,最大年龄就可以设置为300,这样的数据才更合理些。

    注意:MySQL不支持(check)检查约束。

    这样是不是就没办法保证年龄在指定的范围内了?从数据库层面不能保证,以后可以在java代码中进行限制,一样也可以实现要求。

  • 默认约束: 关键字是 DEFAULT

    保存数据时,未指定值则采用默认值。

    例如:我们在给english列添加该约束,指定默认值是0,这样在添加数据时没有指定具体值时就会采用默认给定的0。

  • 外键约束: 关键字是 FOREIGN KEY

    外键用来让两个表的数据之间建立链接,保证数据的一致性和完整性。

    外键约束现在可能还不太好理解,后面我们会重点进行讲解。

1.3 非空约束

  • 概念

    非空约束用于保证列中所有数据不能有NULL值

  • 语法

    • 添加约束

      -- 创建表时添加非空约束
      CREATE TABLE 表名(列名 数据类型 NOT NULL,); 
      -- 建完表后添加非空约束
      ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
      
    • 删除约束

      ALTER TABLE 表名 MODIFY 字段名 数据类型;
      

1.4 唯一约束

  • 概念

    唯一约束用于保证列中所有数据各不相同

  • 语法

    • 添加约束

      -- 创建表时添加唯一约束
      CREATE TABLE 表名(列名 数据类型 UNIQUE [AUTO_INCREMENT],-- AUTO_INCREMENT: 当不指定值时自动增长); 
      CREATE TABLE 表名(列名 数据类型,[CONSTRAINT] [约束名称] UNIQUE(列名)
      ); 
      
      -- 建完表后添加唯一约束
      ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;
      
    • 删除约束

      ALTER TABLE 表名 DROP INDEX 字段名;
      

1.5 主键约束

  • 概念

    主键是一行数据的唯一标识,要求非空且唯一

    一张表只能有一个主键

  • 语法

    • 添加约束

      -- 创建表时添加主键约束
      CREATE TABLE 表名(列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],); 
      CREATE TABLE 表名(列名 数据类型,[CONSTRAINT] [约束名称] PRIMARY KEY(列名)
      ); 
      -- 建完表后添加主键约束
      ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
      
    • 删除约束

      ALTER TABLE 表名 DROP PRIMARY KEY;
      

1.6 默认约束

  • 概念

    保存数据时,未指定值则采用默认值

  • 语法

    • 添加约束

      -- 创建表时添加默认约束
      CREATE TABLE 表名(列名 数据类型 DEFAULT 默认值,); 
      
      -- 建完表后添加默认约束
      ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;
      
    • 删除约束

      ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
      

1.7 约束练习

根据需求,为表添加合适的约束

-- 员工表
CREATE TABLE emp (id INT,  -- 员工id,主键且自增长ename VARCHAR(50), -- 员工姓名,非空且唯一joindate DATE,  -- 入职日期,非空salary DOUBLE(7,2),  -- 工资,非空bonus DOUBLE(7,2)  -- 奖金,如果没有将近默认为0
);

上面一定给出了具体的要求,我们可以根据要求创建这张表,并为每一列添加对应的约束。建表语句如下:

drop table if exists emp;create table emp(id INT primary key ,  -- 员工id,主键且自增长 (自增先不管了)ename VARCHAR(50) not null unique, -- 员工姓名,非空且唯一  (中间不用and连接 空格直接写就行了)joindate DATE not null,  -- 入职日期,非空salary DOUBLE(7,2) not null,  -- 工资,非空bonus DOUBLE(7,2) default 0  -- 奖金,如果没有将近默认为0
);

通过上面语句可以创建带有约束的 emp 表,约束能不能发挥作用呢。接下来我们一一进行验证,先添加一条没有问题的数据

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);
  • 验证主键约束,非空且唯一
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'张三','1999-11-11',8800,5000);

执行结果如下:

从上面的结果可以看到,字段 id 不能为null。那我们重新添加一条数据,如下:

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);

执行结果如下:

从上面结果可以看到,1这个值重复了。所以主键约束是用来限制数据非空且唯一的。那我们再添加一条符合要求的数据

INSERT INTO emp(id,ename,joindate,salary,bonus) values(2,'李四','1999-11-11',8800,5000);

执行结果如下:

  • 验证非空约束
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);

执行结果如下:

从上面结果可以看到,ename 字段的非空约束生效了。

  • 验证唯一约束
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'李四','1999-11-11',8800,5000);

执行结果如下:

从上面结果可以看到,ename 字段的唯一约束生效了。

  • 验证默认约束
INSERT INTO emp(id,ename,joindate,salary) values(3,'王五','1999-11-11',8800);

没有插入bonus列的值

执行完上面语句后查询表中数据,如下图可以看到王五这条数据的bonus列就有了默认值0。


在这里插入图片描述

注意:默认约束只有在不给值时才会采用默认值。如果给了null,那值就是null值。

如下:

INSERT INTO emp(id,ename,joindate,salary,bonus) values(4,'赵六','1999-11-11',8800,null);

执行完上面语句后查询表中数据,如下图可以看到赵六这条数据的bonus列的值是null。

  • 验证自动增长: auto_increment 当列是数字类型 并且唯一约束 才可以用auto_increment来修饰

重新创建 emp 表,并给id列添加自动增长

drop table if exists emp;
-- 员工表
CREATE TABLE emp (id INT PRIMARY KEY auto_increment, -- 员工id,主键且自增长ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名,非空并且唯一joindate DATE NOT NULL , -- 入职日期,非空salary DOUBLE(7,2) NOT NULL , -- 工资,非空bonus DOUBLE(7,2) DEFAULT 0 -- 奖金,如果没有奖金默认为0
);

接下来给emp添加数据,分别验证不给id列添加值以及给id列添加null值,id列的值会不会自动增长:

INSERT INTO emp(ename,joindate,salary,bonus) values('赵六','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六2','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六3','1999-11-11',8800,null);

在这里插入图片描述 [插入EMP(ID,ENAME,加入,工资,奖金)值(null,'赵六2','1999-11-11',8800,null);]

1.8 外键约束

1.8.1 概述

外键用来让两个表的数据之间建立链接,保证数据的一致性和完整性。

如何理解上面的概念呢?如下图有两张表,员工表和部门表:

员工表中的dep_id字段是部门表的id字段关联,也就是说1号学生张三属于1号部门研发部的员工。现在我要删除1号部门,就会出现错误的数据(员工表中属于1号部门的数据)。而我们上面说的两张表的关系只是我们认为它们有关系,此时需要通过外键让这两张表产生数据库层面的关系,这样你要删除部门表中的1号部门的数据将无法删除。

1.8.2 语法

  • 添加外键约束
-- 创建表时添加外键约束
CREATE TABLE 表名(列名 数据类型,[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
); 
-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
  • 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

1.8.3 练习

根据上述语法创建员工表和部门表,并添加上外键约束:

-- 删除表
drop table if exists emp;
drop table if exists dept;-- 部门表 (先建立)
create table dept(id int PRIMARY key auto_increment,dep_name varchar(20),addr varchar(20)
);-- 员工表
create table emp(id int PRIMARY key auto_increment,name varchar(20),age int,dep_id int,-- 添加外键 dep_id, 关联 dept 表的id主键constraint fk_emp_deptID foreign key(dep_id) references dept(id));

添加数据

-- 给部门表添加数据  添加2个部门
insert into dept(dep_name,addr) values('研发部','广州'),('销售部','深圳');-- 给员工表添加数据 dpt_id就是所在部门编号
insert into emp(name,age,dep_id) values
('张三',20,1),
('李四',20,1),
('王五',20,1),
('赵六',20,2),
('孙七',22,2),
('周八',18,2);

此时删除 研发部 这条数据,会发现无法删除。
在这里插入图片描述

删除外键

alter table emp drop FOREIGN key fk_emp_dept;

重新添加外键

alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

在这里插入图片描述
dept部门表是主表
emp员工表是从表

Tips:

在这里插入图片描述

2,数据库设计

2.1 数据库设计简介

  • 软件的研发步骤

  • 数据库设计概念

    • 数据库设计就是根据业务系统的具体需求,结合我们所选用的DBMS,为这个业务系统构造出最优的数据存储模型。
    • 建立数据库中的表结构以及表与表之间的关联关系的过程。
    • 有哪些表?表里有哪些字段?表和表之间有什么关系?
  • 数据库设计的步骤

    • 需求分析(数据是什么? 数据具有哪些属性? 数据与属性的特点是什么)

    • 逻辑分析(通过ER图对数据库进行逻辑建模,不需要考虑我们所选用的数据库管理系统)

      如下图就是ER(Entity/Relation)图:

      在这里插入图片描述

    • 物理设计(根据数据库自身的特点把逻辑设计转换为物理设计)

    • 维护设计(1.对新的需求进行建表;2.表优化)

  • 表关系

    • 一对一

      • 如:用户 和 用户详情
      • 一对一关系多用于表拆分,将一个实体中经常使用的字段放一张表,不经常使用的字段放另一张表,用于提升查询性能

      上图左边是用户的详细信息,而我们真正在展示用户信息时最长用的则是上图右边红框所示,所以我们会将详细信息查分成两周那个表。

    • 一对多

      • 如:部门 和 员工

      • 一个部门对应多个员工,一个员工对应一个部门。如下图:

    • 多对多

      • 如:商品 和 订单

      • 一个商品对应多个订单,一个订单包含多个商品。

2.2 表关系(一对多)

  • 一对多

    • 如:部门 和 员工
    • 一个部门对应多个员工,一个员工对应一个部门。
  • 实现方式

    在多的一方建立外键,指向一的一方的主键

  • 案例

    我们还是以 员工表部门表 举例:

    经过分析发现,员工表属于多的一方,而部门表属于一的一方,此时我们会在员工表中添加一列(dep_id),指向于部门表的主键(id):

    建表语句如下:

相关文章:

02-mysql高级-

文章目录mysql高级1,约束1.1 概念1.2 分类1.3 非空约束1.4 唯一约束1.5 主键约束1.6 默认约束1.7 约束练习1.8 外键约束1.8.1 概述1.8.2 语法1.8.3 练习2,数据库设计2.1 数据库设计简介2.2 表关系(一对多)mysql高级 今日目标 掌握约束的使用 掌握表关系…...

windows 使用everything 查看文件(夹)存储空间占用

起因 总是那个原因,C: D: E:全都红了,下的游戏太多了,然后就这样了,之前也有过不少这种情况.几年前,就在智能手机上见过类似的功能. 大概就是遍历文件系统,统计每个文件的大小,然后父节点记录所有子节点的和,然后可以显示占用百分比之类的. 经过 在windows 上我最开始使用ex…...

2023该好好赚钱了,推荐三个下班就能做的副业

在过去的两年里,越来越多的同事选择辞职创业。许多人通过互联网红利赚到了他们的第一桶金。随着短视频的兴起,越来越多的人吹嘘自己年收入百万,导致很多刚进入职场的年轻人逐渐迷失自我,认为钱特别容易赚。但事实上,80…...

vue3如何进行数据监听watch/watchEffect

我们都知道监听器的作用是在每次响应式状态发生变化时触发,在组合式 API 中,我们可以使用 watch()函数和watchEffect()函数, 当你更改了响应式状态,它可能会同时触发 Vue 组件更新和侦听器回调。 默认情况下,用户创建的侦听器回…...

Wgcloud安装和使用(性能监控)

一、Wgcloud说明 官网:https://www.wgstart.com/ WGCLOUD支持主机各种指标监测(cpu使用率,cpu温度,内存使用率,磁盘容量,磁盘IO,硬盘SMART健康状态,系统负载,连接数量&…...

前端如何实现本地图片上传?

前端如何实现本地图片上传? 摘要 对于学习前端的小伙伴都有一个困惑,就是平常想上手小项目,但碍于不想购买服务器,实践受到了限制。 一般我选择node.js搭建服务器,毕竟基于JavaScript语言,简直不是一家人…...

【基础算法】差分的应用(一维差分和二维差分)

🌹作者:云小逸 📝个人主页:云小逸的主页 📝Github:云小逸的Github 🤟motto:要敢于一个人默默的面对自己,强大自己才是核心。不要等到什么都没有了,才下定决心去做。种一颗树,最好的时间是十年前…...

第49章 API统一集中管理

1 关于统一集中管理API的一些思考 1、统一集中管理是保证工程性项目得保质、保量、成功实施,并对后期维护提供数据支撑的最有效,最节省资源和时间的技能和做法,软件做为一种特殊的工程性项目,也符合上述特性。 2、由于在前台实现中…...

carla0.9.13-UE4添加4轮车模型(Linux系统)

前期准备建模工具:blender:v3.4.1;可以在Ubuntu Software商店直接下载虚拟引擎:carla-UE4 (carla v0.9.13),无需额外安装UE4,carla中自带插件编译carla参照官方文档:https://carla.readthedocs.io/en/0.9.1…...

对比yolov4和yolov3

目录 1. 网络结构的不同 1.1 Backbone 1.1.1 Darknet53 1.1.2 CSPDarknet53 1.2 Neck 1.2.1 FPN 1.2.2 PAN 1.2.3 SPP 1.3 Head 2. ​​​​​数据增强​​​​​ 2.1 CutMix 2.2 Mosaic 3. 激活函数 4. 损失函数 5. 正则化方法 知识点 记录备忘。 总体而言&…...

Android ServiceManager

1.ServiceManager ServiceManager在init进程启动后启动,用来管理系统中的Service。 一般开机过程分为三个阶段: ①OS级别,由bootloader载入linux内核后,内核开始初始化,并载入built-in的驱动程序,内核完成开机后,载入init process,切换至user-space后,结束内核的循…...

数据挖掘,计算机网络、操作系统刷题笔记53

数据挖掘,计算机网络、操作系统刷题笔记53 2022找工作是学历、能力和运气的超强结合体,遇到寒冬,大厂不招人,可能很多算法学生都得去找开发,测开 测开的话,你就得学数据库,sql,orac…...

地球板块运动vr交互模拟体验教学提高学生的学习兴趣

海陆变迁是地球演化史上非常重要的一个过程,它不仅影响着地球的气候、地貌、生物多样性等方面,还对人类文明的演化产生了深远的影响。为了帮助学生更加深入地了解海陆变迁的过程和机制,很多高校教育机构开始采用虚拟现实技术进行教学探究。 V…...

【Android玩机】跟大家聊聊面具Magisk的使用(安装、隐藏)

目录:1、Magisk中文网2、隐藏面具和Root(一共3种方法)1、Magisk中文网 (1)首先Magisk有一个中文网,对新手非常友好 (2)这网站里面主要包含:6 部分 (3)按照他给…...

DACS: Domain Adaptation via Cross-domain Mixed Sampling 学习笔记

DACS介绍方法Naive MixingDACSClassMix![在这里插入图片描述](https://img-blog.csdnimg.cn/ca4f83a2711e49f3b754ca90d774cd50.png)算法流程实验结果反思介绍 近年来,基于卷积神经网络的语义分割模型在众多应用中表现出了显著的性能。然而当应用于新的领域时&…...

python并发编程(并发与并行,同步和异步,阻塞与非阻塞)

最近在学python的网络编程,学了socket通信,并利用socket实现了一个具有用户验证功能,可以上传下载文件、可以实现命令行功能,创建和删除文件夹,可以实现的断点续传等功能的FTP服务器。但在这当中,发现一些概…...

【项目】DTO、VO以及PO之间的关系和区别

【项目】DTO、VO以及PO之间的关系和区别 文章目录【项目】DTO、VO以及PO之间的关系和区别1.概念2. 作用1.概念 DTO:DTO是 Data Transfer Object 的缩写,也叫数据传输对象。 PO:PO是 Persistent Object 的缩写,也叫持久化对象。 …...

Nginx介绍

什么是Nginx? Nginx 是一款高性能的 http 服务器/反向代理服务器及电子邮件(IMAP/POP3)代理服务器。由俄罗斯的程序设计师伊戈尔西索夫(Igor Sysoev)所开发,官方测试 nginx 能够支支撑 5 万并发链接&#x…...

你什么档次?敢和我用一样的即时通讯平台WorkPlus?

现今,很多企业越来越青睐私有化部署,尤其是在选择组织内部即时通讯平台的时候,更是会提出私有化部署的需求。究其原因,企业选择私有化部署即时通讯软件完全是出于安全方面考虑。因此,越来越多的企业将眼光望向了本地化…...

学习资源 - 深度学习

文章目录PyTorchNLP语音CV深度学习其它在我过往博客笔记中,每个专项技术,前面我会贴上官网、官方文档、书籍教程等。 但有些topic,资源比较分散;一个博主/up主,也有可能有多个topic的分享,这里分享我遇到的…...

OkHttp 中实现断点续传 demo

在 OkHttp 中实现断点续传主要通过以下步骤完成,核心是利用 HTTP 协议的 Range 请求头指定下载范围: 实现原理 Range 请求头:向服务器请求文件的特定字节范围(如 Range: bytes1024-) 本地文件记录:保存已…...

LLM基础1_语言模型如何处理文本

基于GitHub项目:https://github.com/datawhalechina/llms-from-scratch-cn 工具介绍 tiktoken:OpenAI开发的专业"分词器" torch:Facebook开发的强力计算引擎,相当于超级计算器 理解词嵌入:给词语画"…...

AI编程--插件对比分析:CodeRider、GitHub Copilot及其他

AI编程插件对比分析:CodeRider、GitHub Copilot及其他 随着人工智能技术的快速发展,AI编程插件已成为提升开发者生产力的重要工具。CodeRider和GitHub Copilot作为市场上的领先者,分别以其独特的特性和生态系统吸引了大量开发者。本文将从功…...

什么是Ansible Jinja2

理解 Ansible Jinja2 模板 Ansible 是一款功能强大的开源自动化工具,可让您无缝地管理和配置系统。Ansible 的一大亮点是它使用 Jinja2 模板,允许您根据变量数据动态生成文件、配置设置和脚本。本文将向您介绍 Ansible 中的 Jinja2 模板,并通…...

搭建DNS域名解析服务器(正向解析资源文件)

正向解析资源文件 1)准备工作 服务端及客户端都关闭安全软件 [rootlocalhost ~]# systemctl stop firewalld [rootlocalhost ~]# setenforce 0 2)服务端安装软件:bind 1.配置yum源 [rootlocalhost ~]# cat /etc/yum.repos.d/base.repo [Base…...

LRU 缓存机制详解与实现(Java版) + 力扣解决

📌 LRU 缓存机制详解与实现(Java版) 一、📖 问题背景 在日常开发中,我们经常会使用 缓存(Cache) 来提升性能。但由于内存有限,缓存不可能无限增长,于是需要策略决定&am…...

Vite中定义@软链接

在webpack中可以直接通过符号表示src路径,但是vite中默认不可以。 如何实现: vite中提供了resolve.alias:通过别名在指向一个具体的路径 在vite.config.js中 import { join } from pathexport default defineConfig({plugins: [vue()],//…...

C++实现分布式网络通信框架RPC(2)——rpc发布端

有了上篇文章的项目的基本知识的了解,现在我们就开始构建项目。 目录 一、构建工程目录 二、本地服务发布成RPC服务 2.1理解RPC发布 2.2实现 三、Mprpc框架的基础类设计 3.1框架的初始化类 MprpcApplication 代码实现 3.2读取配置文件类 MprpcConfig 代码实现…...

从物理机到云原生:全面解析计算虚拟化技术的演进与应用

前言:我的虚拟化技术探索之旅 我最早接触"虚拟机"的概念是从Java开始的——JVM(Java Virtual Machine)让"一次编写,到处运行"成为可能。这个软件层面的虚拟化让我着迷,但直到后来接触VMware和Doc…...

【免费数据】2005-2019年我国272个地级市的旅游竞争力多指标数据(33个指标)

旅游业是一个城市的重要产业构成。旅游竞争力是一个城市竞争力的重要构成部分。一个城市的旅游竞争力反映了其在旅游市场竞争中的比较优势。 今日我们分享的是2005-2019年我国272个地级市的旅游竞争力多指标数据!该数据集源自2025年4月发表于《地理学报》的论文成果…...