MySQL进阶-增删查改(全网最详细sql教学)-1

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL进阶-增删查改(全网最详细sql教学)

数据库约束

数据库约束其实是一种强制让数据库内容变得更加完整的方式, 就比如说有的字段里面可以为null, 但是为了其完整性, 可以使用not null 约束他来让他变得更加完整.


约束类型概览

  1. not null
  2. unique
  3. default
  4. primary key
  5. foreign key


NOT NULL

唯一约束, 对应字段的记录不能为null

创建表的时候,可以指定某个字段不为空, 例如:


drop table if exists student;

create table student (

id int not null,

sn int,

name varchar(20)

);


对于表student的id字段,我们将其设置为not null, 如果此时插入的记录里面,如果id为null,则会插入失败:


815adb1e9c4664fe99df26a5a7637423_14d085941861420aa6b5d2e08c297ce5.png

有时候, 一些实体所抽象出来的数据, 必须有一个识别码,也可以成为sn码, 就如同人的身份证一样,用来标识这些实物或者信息, 这些sn码是必不可少的, 所以这些用来标识身份的sn码就可以设置为not null约束(当然也需要避免重复), 来避免sn码为空, 数据也就更有意义和价值了.


UNIQUE

唯一约束, 对应字段的记录不能与其他记录重复.

创建表的时候, 指定sn码为unique约束


DROP TABLE IF EXISTS student;

CREATE TABLE student (

id INT NOT NULL,

sn INT UNIQUE,

name VARCHAR(20),

);被unique修饰的字段, 那么这个字段里面的每一条数据是唯一的,例如:

例如往里面插入:

然后插入具有相同sn码的"lisi":


发生异常:

Duplicate entry '1' for key 'sn': 键“sn”的重复条目“1”

注意: 所有的null 都遵循unique约束原则:


例如,对sn字段被unique修饰后, 连续插入两个null将不会抛出异常.

DEFAULT

默认值约束, 被default修饰的字段, 如果输入为null, 就会使用默认值来代替, 但是代替的类型必须一致


例如, 将name 设置为default约束, 如果输入name 的值没有被指定的时候, 那么就用 "unknown"代替(若name被指定为null, null不会被默认值替代)


PRIMARY KEY

主键约束, 指定字段约束为主键,能唯一的 标识表中的每一行

一个表中只能有一个primary key 主键列,被primary key 修饰的列不能为空,并且主键列的所有值都是唯一的.相当于同时被 not null 和 unique修饰


对于整形类型的主键, 一般搭配auto_increment 来使用, 插入字段的记录不给值的时候,使用表中主键的最大值 +1 .

例如:


create table student(

id int not null primary key,

sn int unique,

name varchar(20) default 'unknown'

);


表中已经有一个记录,如果继续插入:

搭配auto_increment :

插入两个记录,其中被primary key auto_increment 标记的字段传入值为null ,自动从0开始记录:

a020200201caea343386a46338186eb6_3c9638f8efc9427d8434d5576642e301.png


如果这个时候, 被 primary key auto_increment标记的id 传入一个指定 值的时候,然后再传空值:

66c9b84dab8b407cc256d558ef630adc_ee1262e13d3a4129b8a1e4e2463306e7.png

结果是主键最大值2,直接跳过被设置为指定的数值,然后后面的空值会继续跟着主键最大值自增.

FOREIGN KEY

外键约束

使用语法:

foreign key 字段 references 主表


添加class表,classId为主键:


create table class(classId int primary key auto_increment, className varchar(20));

添加student表,studentId为主键:


create table student(studentId int primary key auto_increment, name varchar(20), classId int, foreign key( classId) references class( classId));

设置student中的classId字段被foreign key约束, 其约束项为class 表中的classId字段, 也就是说:


student 表中的classId 中的每一个数据, 都必须在class表中的classId 字段的记录中存在.


此时,student表被class表约束, 我们称student为class的子表, 称class 为student 的父表


如果向student里面插入或者修改的记录对应的foreign key 外键约束的字段数据在对应父表中不存在的话, 那么就会插入失败.


相反,如果要删除class表中的数据, 如果对应约束项的记录已经在子表中存在, 那么删除就会失败


更直观的来看个例子:


创建两个表:


商品表(Id, name, price....)


订单表(orderId, userId, goodsId, time.....)


其中商品id被外键约束, 其约束的内容为订单表中的goodsId, 用MySQL的语言就是:


create table orderList (orderId int primary key auto_increment , userId int, goodsId int, foreign key(id) references goodsList(Id));

505ce0e806d6d93244ff657b128d7d5c_eeaaf9700cc44f1e86aea3c63318a364.png

假如这个商品表需要有一个商品下架功能, 但是, 这里面的id又有外键约束, 如果要在商品表中删除一个商品, 但是他的id 在订单表中还在被使用, 那么此时就无法直接删除, 那么这个功能该如何实现??


解决办法:给商品表加一个列字段, 用来表示该商品是否被下架,通过update 来修改这个字段, 此时外界约束就不会收到影响, 订单可以继续实时查看.


表的设计

如何设计表?

  1. 了解实际信息体的"实体"
  2. 明确实体之间的关系
  3. 按照实体的关系带入到表的体系当中.


无关联

各表之间没有关系, 只需要根据实体的个性 自行设计即可


一对一

例如, 一个学生表中 有许多关于学生的字段, 这些字段可以是姓名, 年龄等, 同时还存在一个账号表, 这两张表有很直接的关联, 那就是一个账号只能供一个学生使用, 同时 一个学生也只能拥有一个账号.


这种一对一的, 就可以直接设计成一张表:


student -- account ( studentId, studentName, studentAccount);


此外,更推荐将其设计成两个表, 然后让他们相互关联, 可以使用studentId进行关联, 也可以使用account进行关联.


一对多

一个班级可以包含多个学生, 但是一个学生只能存在于一个班级, 这就形成了一对多或者多对一的情况


student (studentID, name, age ....)

class (classID, className, studentList(每个记录为一个学生数据 集合 ) )

98b37ea7f229392d5c4289abadcc1b3b_27e9d3b5952d4f9f802ca4c553ac34aa.png

student表包含学生的基本数据, class 包含班级的数据, 同时也包含着具有的学生列表, 但是学生列表有很多行学生的数据, 不可能在一行里面显示, 但是使用数组的形式存放这些学生的记录的方法在MySQL是不支持的. 但是有些数据库支持以数组的形式存放, 比如redis ,


此外还可以使用外键约束来设计这个一对多的表:

417cb16ea0f7afa976cdeeb2dcf754a2_56dff7f5267f4f7da81e901742302604.png


多对多

例如, 一个学生可以上多个课程, 一个课程也可以有多个学生:


studentList (studentID, name );

course ( courseID, courseName );

将他们两个关联起来:


student_courseList (studentID, courseID );

ce8d0f732299f2c2482d687c812cdbb6_6e40135787744a789c03d0272e359b2c.png

进阶增删改查

与前面的直接使用insert into 表名 values (value1, value2, value3, ....)了不同的是, 这里的新增是直接插入查询结果. ;例如:


这里创建两张学生表:


student (id int, name varchar(20))

student2 (id int, name varchar(20))

student表中插入三个数据:


insert into student values (1, "张三"),(2, "李四"),(3, "王八");

如果这个时候, 我想把student表中的数据拷贝到student2 当中去, 使用将values改成select查询语句:


insert into student2 select * from student;

注意 :这并不代表任何两个表之间都可以这样操作, 查询的结果得到的列数, 对应字段的数据类型必须一样( 列名可以不一样, 但是类型必须一致 )


MySQL进阶-增删查改(全网最详细sql教学)-2

https://developer.aliyun.com/article/1517134

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
|
5天前
|
SQL 关系型数据库 MySQL
MySQL数据库进阶第五篇(锁)
MySQL数据库进阶第五篇(锁)
|
5天前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第三篇(MySQL性能优化)
MySQL数据库进阶第三篇(MySQL性能优化)
|
5天前
|
SQL 关系型数据库 MySQL
MySQL数据库基础第一篇(SQL通用语法与分类)
MySQL数据库基础第一篇(SQL通用语法与分类)
|
5天前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)
|
5天前
|
SQL 存储 关系型数据库
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
|
5天前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
|
2天前
|
SQL 关系型数据库 MySQL
MySQL数据库数据模型概念入门及基础的SQL语句2024
MySQL数据库数据模型概念入门及基础的SQL语句2024
7 0
|
5天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
13天前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
241 1