MySQL的增删改查（进阶）

最新推荐文章于 2024-08-19 22:23:29 发布

棕豆兔＆

最新推荐文章于 2024-08-19 22:23:29 发布

阅读量1.1k

点赞数 23

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_60462557/article/details/136507029

版权

MySQL 专栏收录该内容

3 篇文章 1 订阅

订阅专栏

本文详细介绍了MySQL数据库中的各种约束类型，包括NULL、唯一、默认值、主键、外键和检查约束，以及表设计的通用步骤、E-R图，以及增删改查中的高级查询技巧，如聚合函数、分组查询、内连接、外连接、自连接和子查询等。

摘要由CSDN通过智能技术生成

目录

前言

本篇主要分享关于 MySQL 的增删改查进阶版，MySQL的删除与修改的用法较为简单，前篇内容已经介绍完毕，主页有分享关于 MySQL的基础版了解知识，推荐阅读了前篇内容之后再进行对本篇内容的了解。

一、数据库约束

1.约束类型

not null：指定某列不能存储空值；
unique：保证某列的每行必须有唯一的值；
default：规定没有给某列赋值时的默认值；
primary key：not null 和 unique 的结合，确保某几列组成的表有唯一标识；
foreign key: 保证一个表中的数据匹配另一个数据的表中的值的参照完整性；
check：指定一个更加具体的条件。

2.NULL约束

创建表时，可以指定某列不为空。

#创建学生表，id不可为空
create table Students (
id int not null,
name varchar(20) 
);

3.unique：唯一约束

创建表时，可以指定某列为唯一的、不重复的。

#创建学生表，id为唯一的，name不可为空
create table Students (
id int unique,
name varchar(20) not null
);

加上unique约束后，每次插入/修改需要先进行查询，如果发现已经存在或者重复就会插入/修改失败。

4.default：默认值约束

指定插入数据时，插入某列的值为空，则会出现设置的默认值。

#创建学生表，name列为空，设置默认值为unkown
create table Students (
id int not null,
name varchar(20) default 'unkown'
);

5.primary key：主键约束

创建表时，指定某列或某几列的组合为该表的主键，每个表只能有一个主键。

#创建学生表，指定id为主键
create table Students (
id int primary key auto_increment,
name varchar(20),
class varchar(20)
);

insert into Students values (1,'张三'，'计算机一班')；


#此处null并不表示插入空值，而是由于前面插入了id=1的同学，本次执行结果会是：2 李四 计算机一班
，实现自增，并不与primary key 的不为空要求冲突！
insert into Students values (null,'李四','计算机一班')；

该代码示例中，anto_increment 表示自增长，对于整数型的主键，可以搭配 anto_increment 来使用，插入数据对应字段不给值时，使用最大值+1。

由于primary key 是 not null 和 unique 的结合，不为空且不重复，所以可以不用另外指定这两个约束。

6.foreign key：外键约束

外键是用于关联其他表的主键或唯一键，语法：

foreign key (字段名) references 主表 (列)

#创建班级表
create table Classes (
id int primary key anto_increment,
name varchar(20),
'desc' varchar(100)
);

#创建学生表
create table Students(
id int primary key anto_increment,
name varchar(20) default 'unkown',
sex varchar(1),
classes_id int,
foreign key (classes_id) references Classes(id)
);

外键约束，和 unique 具有类似的效果，都需要在插入/修改先进行查询。

如果要删除表，则需先删除子表，再删除父表。（子表与父表的关系为：子表的外键存在于父表，如上述，学生表为子表，班级表为父表。外键引用的父表某一列，要么是主键，要么是unique）

7.check约束

MySQL 8.0 系列之前的版本使用 check 时不报错，但忽略该约束。8.0系列之后支持此功能。

create table Students(
id int primary key,
name varchar(20),
sex varchar(1),
check (sex = '男' or sex = '女')
);

二、表的设计

1.设计表的通用步骤

明确需求中的“实体”（entry），实体是数据库中描述的现实世界中的对象的概念。譬如学生是一个实体，包含姓名、学号、专业、成绩等属性。
清楚实体之间的关系，它们之间存在一对一、一对多、多对多的三大范式关系。

例如在教务系统中，存在学生（姓名，年龄，班级），账号（用户名，密码），班级（班级编号，班级姓名）课程（课程号，课程名）那么：

一对一：一个学生只能有一个账号，一个账号只能属于一个学生，学生和账号之间是一对一的关系。那么设计时可以只设计一个表，包含学生的信息和账号的信息，或者也可以设计两张表，分别为Student (id,name,gender,classId) 和 Account (accoundId,username,password)

一对多：一个班级有多个学生，一个学生只属于一个班级，班级和学生之间属于一对多的关系。那么在设计表时，可以设计成两个表，有两种办法，一种是Student(id,name...)和Class（classId,className,StudentList），StudentList类似于数组，存储了学生的信息清单，但是MySQL没有数组类型，因此此种方式不行，另一种是Student(id,name,classId)和Class（classId,className）。

多对多：一个学生可以选择多门课程，一门课程可以被多个学生选择，学生和课程之间属于多对多的关系。那么在设计表时，有一种固定的设计方式，即引入“关联表”。除 Student (id,name)和Course (courseId,courseName)，再引入一个课程表，即Student_course(StudentId,courseId)，该表关系学生表和课程表。

2.E-R图

E-R图描述的是实体之间的联系。

三、新增

新增的语法为：

insert into 表名 select 列名 from 表名;

#创建用户表
create table 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 user(name , email) select name ,qq_mail form student;

四、查询

1.聚合函数

COUNT ( [distinct] expr)：返回查询到的数据的数量；
SUM ( [distinct] expr)：返回查询到的数据的总和；
AVG ( [distinct] expr)：返回查询到的数据的平均值；
MAX ( [distinct] expr)：返回查询到的数据的最大值；
MIN ( [distinct] expr)：返回查询到的数据的最小值。

注：能使用上述函数的是数字，不是数字没有意义。

聚合函数的函数名和括号之间不能有空格，否则运行会报错。

聚合函数的查询是会跳过空值的，如：查询某一列的数量总和时，count查询的是非空的集合，存在null则不参加计数。“ * ”一般只用count(*)。

#统计学生表的总数
select count(*) from Student;

#计算学生表里语文成绩的总和
select sum(chinese) from Student;
#计算学生表里三科总成绩并重命名为total
select sum(chinese + math + english) as total from Student;

#计算语文成绩平均值
select avg(chinese) from Student;

#计算英语成绩最大、最小值
select max(english),min(english) from Student;

2.分组查询：GROUP BY子句

针对某个列，把值相同的行，分到同一组中，再针对这个组，进行聚合查询。

#创建员工表
create table emp (
id int primary key auto_increment,
name varchar(20),
role varchar(),
salary int,
);

#添加信息
insert into emp values(01,'张三','开发',10000)，
                      (02,'李四','开发',14000)，
                      (03,'王五','测试',12000),
                      (04,'丽丽','测试',10000);

#按role进行分组
select role, count(id) from emp group by role;

#计算各职位的工资平均值
select role,avg(salary) from emp group by role;

#计算各职位的工资平均值，并按升序排序
select role,avg(salary) from emp group by role order by avg(salary);

#计算除去张三此人之外的平均工资
select role,avg(salary) from emp where name != '张三' group by role;

#计算平均工资，后除去工资超过2w的职位
select role,avg(salary) from emp group by role having avg(salary) < 20000;

#计算除去张三的平均工资，后除去工资超过2w的职位
select role,avg(salary) from emp where name != '张三' group by role having avg(salary) < 20000;

上述按role 这列group by ，如果前面select不使用聚合函数，那么也不能再select后添加别的列，这样出来的结果是不科学的。

3.内连接

语法如下：

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;

select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

笛卡尔积：它是多表查询的基础，如下演示：

#创建班级表
create table class(classId int,className varchar(20));

#创建学生表
create table student(id int,name varchar(20),classId int);

#插入数据
insert into class values(101,'java101'),(102,'java102');
insert into student values (1,'张三',101),(2,'王五',101),(3,'李四',102),(4,'赵六',102);

#笛卡尔积
select * from  student,class;

但是明显，我们看到查询到的笛卡尔积的结果，部分数据是错误的，即classId不一样的部分，故而需要去掉无效数据。

#去除无效数据
select * from student,class where student.classId = class.classId;

上述student.classId = class.classId 被称为多表查询的连接条件。

create table classes (id int primary key auto_increment,name varchar(20),`desc` varchar(100));
create table student (id int primary key auto_increment,sn varchar(20),name varchar(20),qq_mail varchar(20),classes_id int);
create table course (id int primary key anto_increment,name varchar(20));
create table score (score decimal(3,1),student_id int,course_id int);

insert into classes(name, `desc`) values
    -> ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
    -> ('中文系2019级3班','学习了中国传统文学'),
    -> ('自动化2019级5班','学习了机械自动化');

insert into student(sn, name, qq_mail, classes_id) values
    -> ('09982','黑旋风李逵','xuanfeng@qq.com',1),
    -> ('00835','菩提老祖',null,1),
    -> ('00391','白素贞',null,1),
    -> ('00031','许仙','xuxian@qq.com',1),
    -> ('00054','不想毕业',null,1),
    -> ('51234','好好说话','say@qq.com',2),
    -> ('83223','tellme',null,2),
    -> ('09527','老外学中文','foreigner@qq.com',2);

insert into score(score, student_id, course_id) values
    -> -- 黑旋风李逵
    -> (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
    -> -- 菩提老祖
    -> (60, 2, 1),(59.5, 2, 5),
    -> -- 白素贞
    -> (33, 3, 1),(68, 3, 3),(99, 3, 5),
    -> -- 许仙
    -> (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
    -> -- 不想毕业
    -> (81, 5, 1),(37, 5, 5),
    -> -- 好好说话
    -> (56, 6, 2),(43, 6, 4),(79, 6, 6),
    -> -- tellme
    -> (80, 7, 2),(92, 7, 6);

insert into course(name) values
    -> ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

下面我们进行一些多表查询的实例：首先确定信息来自哪几个表，然后进行笛卡尔积，接着指定连接条件，再补充其他条件，最后对列进行精简。

#查询许仙同学的成绩(以下两种方法可以实现)
select student.name,score.score from student,score where student.id = score.student_id and student.name = '许仙';

select student.name,score.score from student join score on student.id = score.student_id and student.name = '许仙';

#查询所有同学的总成绩及个人信息
select name,sum(score) from student,score where student.id = score.student_id group by student.name;

#查询所有同学的成绩及个人信息
select student.name,course.name,score.score from student,course,score where student.id = score.student_id and course.id = score.course_id;

select student.name,course.name,score.score from student join score on student.id = score.student_id join course on course.id = score.course_id;

4.外连接

外连接只能使用join...on....。

外连接分为左外连接和右外连接，例如：表1 join 表2 on....，左外连接是以表1 为基准，完全显示表1，表2 部分没有的表1部分的值结果会以Null来填充，右外连接则是以表2 为基准，完全显示表2，表1 部分没有表2部分的值结果会以Null来填充.语法如下：

select 字段名 from 表1 left join 表2 on 连接条件;

select 字段名 from 表1 right join 表2 on 连接条件;

#查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示
SELECT
 stu.id,
 stu.sn,
 stu.NAME,
 stu.qq_mail,
 sco.score,
 sco.course_id,
 cou.NAME
FROM
 student stu
 LEFT JOIN score sco ON stu.id = sco.student_id
 LEFT JOIN course cou ON sco.course_id = cou.id
ORDER BY
 stu.id;

5.自连接

自连接是指在同一张表连接自身进行查询。自连接能够把行之间的关系转化为列之间的关系。

#显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

select * from score as s1,score as s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;

6.子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。

单行子查询;

#查询与“不想毕业”同学同班的同学
 select * from student where classes_id=(select classes_id from student where name='不想毕业');

多行子查询：

#查询“语文”或“英文”课程的成绩信息
select * from score where course_id in (select id from course where name='语文' or name='英文');

7.合并查询

合并查询就是将多个查询的结果集合到一起。

#查询id小于3，或者名字为“英文”的课程：
select * from course where id<3 union select * from course where name='英文';

select * from course where id<3 or name='英文';

union允许多个表的查询合并，它默认是去重的，or只允许一个表。还可以使用union all，用法同union，但是union all 是不去重的。

总结

以上就是本次分享的内容，保持良好的代码练习习惯有助于更好的了解这些知识，感谢大家阅读，错误之处请批评指正！

棕豆兔＆

关注

23
点赞
踩
45

收藏

觉得还不错? 一键收藏
0
评论
MySQL的增删改查（进阶）

本篇主要分享关于 MySQL 的增删改查进阶版，主页有分享关于 MySQL的基础版了解知识，推荐阅读了前篇内容之后再进行对本篇内容的了解。
复制链接

扫一扫

专栏目录