当前位置: 首页 > 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的分享,这里分享我遇到的…...

国防科技大学计算机基础课程笔记02信息编码

1.机内码和国标码 国标码就是我们非常熟悉的这个GB2312,但是因为都是16进制,因此这个了16进制的数据既可以翻译成为这个机器码,也可以翻译成为这个国标码,所以这个时候很容易会出现这个歧义的情况; 因此,我们的这个国…...

通过Wrangler CLI在worker中创建数据库和表

官方使用文档:Getting started Cloudflare D1 docs 创建数据库 在命令行中执行完成之后,会在本地和远程创建数据库: npx wranglerlatest d1 create prod-d1-tutorial 在cf中就可以看到数据库: 现在,您的Cloudfla…...

1.3 VSCode安装与环境配置

进入网址Visual Studio Code - Code Editing. Redefined下载.deb文件,然后打开终端,进入下载文件夹,键入命令 sudo dpkg -i code_1.100.3-1748872405_amd64.deb 在终端键入命令code即启动vscode 需要安装插件列表 1.Chinese简化 2.ros …...

【android bluetooth 框架分析 04】【bt-framework 层详解 1】【BluetoothProperties介绍】

1. BluetoothProperties介绍 libsysprop/srcs/android/sysprop/BluetoothProperties.sysprop BluetoothProperties.sysprop 是 Android AOSP 中的一种 系统属性定义文件(System Property Definition File),用于声明和管理 Bluetooth 模块相…...

Cloudflare 从 Nginx 到 Pingora:性能、效率与安全的全面升级

在互联网的快速发展中,高性能、高效率和高安全性的网络服务成为了各大互联网基础设施提供商的核心追求。Cloudflare 作为全球领先的互联网安全和基础设施公司,近期做出了一个重大技术决策:弃用长期使用的 Nginx,转而采用其内部开发…...

微服务商城-商品微服务

数据表 CREATE TABLE product (id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 商品id,cateid smallint(6) UNSIGNED NOT NULL DEFAULT 0 COMMENT 类别Id,name varchar(100) NOT NULL DEFAULT COMMENT 商品名称,subtitle varchar(200) NOT NULL DEFAULT COMMENT 商…...

根据万维钢·精英日课6的内容,使用AI(2025)可以参考以下方法:

根据万维钢精英日课6的内容,使用AI(2025)可以参考以下方法: 四个洞见 模型已经比人聪明:以ChatGPT o3为代表的AI非常强大,能运用高级理论解释道理、引用最新学术论文,生成对顶尖科学家都有用的…...

全志A40i android7.1 调试信息打印串口由uart0改为uart3

一,概述 1. 目的 将调试信息打印串口由uart0改为uart3。 2. 版本信息 Uboot版本:2014.07; Kernel版本:Linux-3.10; 二,Uboot 1. sys_config.fex改动 使能uart3(TX:PH00 RX:PH01),并让boo…...

使用 Streamlit 构建支持主流大模型与 Ollama 的轻量级统一平台

🎯 使用 Streamlit 构建支持主流大模型与 Ollama 的轻量级统一平台 📌 项目背景 随着大语言模型(LLM)的广泛应用,开发者常面临多个挑战: 各大模型(OpenAI、Claude、Gemini、Ollama)接口风格不统一;缺乏一个统一平台进行模型调用与测试;本地模型 Ollama 的集成与前…...

LLMs 系列实操科普(1)

写在前面: 本期内容我们继续 Andrej Karpathy 的《How I use LLMs》讲座内容,原视频时长 ~130 分钟,以实操演示主流的一些 LLMs 的使用,由于涉及到实操,实际上并不适合以文字整理,但还是决定尽量整理一份笔…...