MySQL表的增删改查（进阶-上）

👨‍💻作者简介：👨🏻‍🎓告别，今天

📔高质量专栏 ：☕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 主表 ( 列 )

案例：

• 创建班级表classes，id为主键：        

--创建学生表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 ;

🎊总结