MySQL表的增删改查(进阶-上)

本文详细介绍了MySQL数据库中的各种约束,包括NOTNULL、UNIQUE、DEFAULT、PRIMARYKEY和FOREIGNKEY,以及它们的作用、使用场景和注意事项,帮助读者理解和应用这些约束来优化数据完整性。
摘要由CSDN通过智能技术生成

👨‍💻作者简介:👨🏻‍🎓告别,今天

📔高质量专栏 :☕java趣味之旅

📔(零基础)专栏:MSQL数据库​​​​​​​

欢迎🙏点赞🗣️评论📥收藏💓关注

💖衷心的希望我的📙作品能给大家带来收获。

🎊前言:

让我们如大自然般🌱悠然自在😊地生活一天吧,
别因为有坚果外壳或者蚊子翅膀落在铁轨上🛤️而翻了车🚆。让我们该起床时🛏️就赶紧起床🛏️,
该休息🛏️时就安心休息🛏️,
保持安宁而没有烦扰的心态;身边的人要来就让他来,要去就让他去,
让钟声🕰️回荡,让孩子👼哭喊—
下定决心好好地过一天。


🙏希望大家都可以过好每一天。也🙏希望我的博客,能给大家带来💯收获。

1. 数据库约束

1.1 约束类型

  • NOT NULL - 指示某列不能存储 NULL 值。
  • UNIQUE - 保证某列的每行必须有唯一的值。
  • DEFAULT - 规定没有给列赋值时的默认值。
  • PRIMARY KEY - NOT NULL UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
  • FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
  • CHECK - 保证列中的值符合指定的条件。对于MySQL数据库,对CHECK子句进行分析,但是忽略CHECK子句。

1.2 NULL约束

创建表时,可以指定某列不为空:
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id INT NOT NULL,
  sn INT,
  name VARCHAR(20),
  qq_mail VARCHAR(20)
);

 当引用NULL约束后,插入操作不能插入空值。

 

  当引用NULL约束后,修改操作不能修改空值。

1.3 UNIQUE:唯一约束

指定 sn 列为唯一的、不重复的:
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id INT NOT NULL,
  sn INT UNIQUE,
  name VARCHAR(20),
  qq_mail VARCHAR(20)
); 

 

当我们多次插入同一个数据时,数据库报错。

加上UNIQUE之后

每次插入/修改 都会需要先查询

如果发现重复(已经存在)就会插入/修改失败

注意:加上unique之后,执行效率会降低。对数据的检验更严格了,有助于写代码减少出错的概率。(提高了开发的效率)

1.4 DEFAULT:默认值约束

指定插入数据时, name 列为空,默认值 unkown
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id INT NOT NULL,
  sn INT UNIQUE,
  name VARCHAR(20) DEFAULT 'unkown',
  qq_mail VARCHAR(20)
); 

 1.5 PRIMARY KEY:主键约束

指定 id 列为主键:
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id INT NOT NULL PRIMARY KEY,
  sn INT UNIQUE,
  name VARCHAR(20) DEFAULT 'unkown',
  qq_mail VARCHAR(20)
); 

注意:

在mysql中,主键,还有两个具体要求:

1.不能为null

2.不能重复

一个表只能有一个主键

 对于整数类型的主键,常配搭自增长auto_increment来使用。插入数据对应字段不给值时,使用最大+1

-- 主键是 NOT NULL UNIQUE 的结合,可以不用 NOT NULL
id INT PRIMARY KEY auto_increment,

当然这种情况下也可以手动指定id,不一定非要依赖自增主键;

接下来见证奇迹的时候

当我们插入id为100的数后,默认插入自增为100+1 

当我们删除id为100,101的数后,再次默认插入会呈现什么结果?

注意: 自增主键,本质上是 mysql 服务器存储了当前表中的最大id,再进一步进行累加的。

上述插入最大值为101

 1.6 FOREIGN KEY:外键约束

外键用于关联其他表的 主键 唯一键 ,语法:
foreign key ( 字段名 ) references 主表 ( )
案例:
  • 创建班级表classesid为主键:        
--创建学生表student,一个学生对应一个班级,一个班级对应多个学生。使用id为主键,
classes_id为外键,关联班级表id
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
 create table student (id int primary key auto_increment,name varchar(20),classId int,foreign key (classId) references class(classId));
  • 创建学生表student,一个学生对应一个班级,一个班级对应多个学生。使用id为主键,classes_id为外键,关联班级表id
-- 主键是 NOT NULL 和 UNIQUE 的结合,可以不用 NOT NULL
id INT PRIMARY KEY auto_increment,
foreign key (字段名) references 主表(列)
-- 创建班级表,有使用MySQL关键字作为字段时,需要使用``来标识
DROP TABLE IF EXISTS classes;
 create table class (classId int primary key auto_increment,className varchar(20));

 

原则上来说,插入的学生表的数据中的classId必须要在class表中存在,这样插入的学生才有意义(从属于某个班级的)。但在实际情况中,并不会报错,所以我们要引用外键约束

此时,student 的 classid 就和 class 表的 classid 建立了联系。student classid 中的值,必须要在class 表的 classid 中存在。

class 表就对 student 表产生了制约。

此时,就把 class 表,制约别人的表,称为"父表"(parent table)

把student 表,被制约的表,称为"子表"(child table)

受外键约束影响,插入失败

同理,修改操作也一样。

外键约束和unique类似的效果,都是在插入/修改之前,进行查询

当我们删除没有被引用的父表时,不会造成影响,当我们删除被引用了时,MySQL表发生报错

虽然是父亲约束儿子,但实际上儿子也要反向约束父亲,双向奔赴。

外键约束,准确来说是两个表的列产生关联关系。当两个标产生关联关系后,直接删除父表会报错。这是正确的删除方式,是先删子表,再删父表

1.7 CHECK约束(了解)

MySQL 使用时不报错,但忽略该约束:
drop table if exists test_user;
create table test_user (
  id int ,
  name varchar ( 20 ),
  sex varchar ( 1 ),
  check (sex = ' ' or sex= ' ' )
);

2. 新增

插入查询结果
语法:
INSERT INTO table_name [( column [, column ...])] SELECT ...
案例:创建一张用户表,设计有 name 姓名、 email 邮箱、 sex 性别、 mobile 手机号字段。需要把已有的 学生数据复制进来,可以复制的字段为 name qq_mail
-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
  id INT primary key auto_increment,
  name VARCHAR ( 20 ) comment ' 姓名 ' ,
  age INT comment ' 年龄 ' ,
  email VARCHAR ( 20 ) comment ' 邮箱 ' ,
sex varchar ( 1 ) comment ' 性别 ' ,
mobile varchar ( 20 ) comment ' 手机号 '
);
-- 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name, qq_mail from student;

3. 查询

3.1 聚合查询

3.1.1 聚合函数

常见的统计总数、计算平局值等操作,可以使用聚合函数来实现,常见的聚合函数有:
案例:
  • COUNT
-- 统计班级共有多少同学
SELECT COUNT (*) FROM student;
SELECT COUNT ( 0 ) FROM student;
-- 统计班级收集的 qq_mail 有多少个, qq_mail NULL 的数据不会计入结果
SELECT COUNT (qq_mail) FROM student;
  • SUM
-- 统计数学成绩总分
SELECT SUM (math) FROM exam_result;
-- 不及格 < 60 的总分,没有结果,返回 NULL
SELECT SUM (math) FROM exam_result WHERE math < 60 ;
  • AVG
-- 统计平均总分
SELECT AVG (chinese + math + english) 平均总分 FROM exam_result;
  • MAX 
-- 返回英语最高分
SELECT MAX (english) FROM exam_result;
  •  MIN
-- 返回 > 70 分以上的数学最低分
SELECT MIN (math) FROM exam_result WHERE math > 70 ;

3.1.2 GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足:使用 GROUP BY 进行分组查询时, SELECT 指定的字段必须是 分组依据字段 ,其他字段若想出现在 SELECT 中则必须包含在聚合函 数中。
select column1, sum(column2), .. from table group by column1,column3
案例:
  • 准备测试表及数据:职员表,有id(主键)、name(姓名)、role(角色)、salary(薪水)
create table emp(
id int primary key auto_increment,
name varchar ( 20 ) not null ,
role varchar ( 20 ) not null ,
salary numeric ( 11 , 2 )
);
insert into emp(name, role, salary) values
( ' 马化云 ' , ' 服务员 ' , 1000.20 ),
( ' 马腾 ' , ' 游戏陪玩 ' , 2000.99 ),
( ' 孙悟空 ' , ' 游戏角色 ' , 999.11 ),
( ' 猪无能 ' , ' 游戏角色 ' , 333.5 ),
( ' 沙和尚 ' , ' 游戏角色 ' , 700.33 ),
( ' 隔壁老王 ' , ' 董事长 ' , 12000.66 );
查询每个角色的最高工资、最低工资和平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role;

3.1.3 HAVING

GROUP BY 子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用 WHERE 语句,而需要用 HAVING
  • 显示平均工资低于1500的角色和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role
having avg(salary)< 1500 ;

🎊总结

  • 24
    点赞
  • 18
    收藏
    觉得还不错? 一键收藏
  • 11
    评论
评论 11
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值