0
0

MySQL数据库 ---MySQL表的增删改查(进阶)

文章目录

MySQL表的增删改查(进阶)

1. 数据库约束

约束类型说明示例NULL约束使用NOT NULL指定列不为空name varchar(20) not null,UNIQUE唯一约束指定列为唯一的、不重复的name varchar(20) unique,DEFAULT默认值约束指定列为空时的默认值age int default 20,主键约束NOT NULL 和 UNIQUE 的结合id int primary key,外键约束关联其他表的主键或唯一键foreign key (字段名) references 主表(列)CHECK约束（了解）保证列中的值符合指定的条件check (sex =‘男’ or sex=‘女’)

1.1 约束类型

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

1.2 NULL约束

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

createtable student (
    id intnotnull,
    name varchar(20),
    score decimal(3,1));

在这里插入图片描述
在这里插入图片描述

1.3 UNIQUE：唯一约束

createtable student (
    id intunique,
    name varchar(20),
    score decimal(3,1));

在这里插入图片描述
在这里插入图片描述

1.4 DEFAULT：默认值约束

createtable student (
    id intuniquenotnull,
    name varchar(20)default'unknown',
    score decimal(3,1));

在这里插入图片描述
在这里插入图片描述

1.5 PRIMARY KEY：主键约束

等价于 not null + unique

createtable student (
    id intprimarykey,
    name varchar(20),
    score decimal(3,1));

在这里插入图片描述

在这里插入图片描述
如何保证主键不重复?人工保证不太靠谱.
可以借助数据库自动来生成.---- 

auto_increment

createtable student (
    id intprimarykeyauto_increment,
    name varchar(20),
    score decimal(3,1));

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

自增的特点是:

  • 如果表中没有任何的记录,自增从1开始.
  • 如果表中已经有记录了,自增从上一条记录往下自增.
  • 如果中间某个数据删了,再次插入数据,刚才删掉的自增主键的值不好被重复利用

1.6 FOREIGN KEY：外键约束

描述两张表的之间的关联关系
外键用于关联其他表的主键或唯一键，语法：

foreignkey(字段名)references 主表(列)

例:

createtable class (
    id intprimarykeyauto_increment,
    name varchar(20));createtable student (
    id intprimarykeyauto_increment,
    name varchar(20),
    classId int,foreignkey(classId)references class(id));

在这里插入图片描述
在这里插入图片描述

1.7 CHECK约束（了解）

MySQL使用时不报错，但忽略该约束：

createtable user_test (
id int,
name varchar(20),
sex varchar(1),check(sex ='男'or sex='女'));

2. 表的设计

2.1 一对一

在这里插入图片描述

2.2 一对多

在这里插入图片描述

2.3 多对多

在这里插入图片描述
多对多的关系两者之间的对应关系是非常复杂的.
多对多这个关系复杂需要引入中间表来解决这个问题.
例如,描述每个同学的每个科目的考试成绩.
先创建表来描述同学的信息,然后创建表描述科目信息.

createtable student(
    id intprimarykeyauto_increment,
    name varchar(20));createtable course(
    id intprimarykeyauto_increment,
    name varchar(20));insertinto student values(null,'甲'),(null,'乙'),(null,'丙'),(null,'丁');insertinto course values(null,'语文'),(null,'数学'),(null,'英语'),(null,'物理'),(null,'化学');

为了描述每个同学每一科考了多少分,就需要搞一个中间表来描述.

createtable score(
    courseId int,
    studentId int,
    score decimal(3,1));insertinto score values(1,1,90);

在这里插入图片描述
如果想查找"甲"这个同学的"语文"成绩如何?
此时的查找过程就会更复杂.

  1. 先找到甲的studentld
  2. 在找到语文的courseld
  3. 结合这两个id再在score表中查找

3. 新增

插入查询结果
语法:

insertinto[表名]select[列名],[列名]...from[表名];

案例:

createtableuser(
    id intprimarykeyauto_increment,
    name varchar(20),
    description varchar(1000));insertintouservalues(null,'曹操','乱世枭雄'),(null,'刘备','仁德之主'),(null,'孙权','年轻有为');createtable user2(
    name varchar(20),
    description varchar(1000));insertinto user2 select name,description fromuser;

在这里插入图片描述

4. 查询

4.1 聚合查询

4.1.1 聚合函数

函数说明COUNT([DISTINCT] expr)返回查询到的数据的 数量SUM([DISTINCT] expr)返回查询到的数据的 总和，不是数字没有意义AVG([DISTINCT] expr)返回查询到的数据的 平均值，不是数字没有意义MAX([DISTINCT] expr)返回查询到的数据的 最大值，不是数字没有意义MIN([DISTINCT] expr)返回查询到的数据的 最小值，不是数字没有意义

4.1.1.1 COUNT

在这里插入图片描述
在这里插入图片描述

4.1.1.2 SUM

在这里插入图片描述

4.1.1.3 AVG

在这里插入图片描述

4.1.1.4 MAX

在这里插入图片描述

4.1.1.5 MIN

在这里插入图片描述

案例求所有分数小于90的同学的平均分

在这里插入图片描述

4.1.2 GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时， SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。

select column1,sum(column2),..fromtablegroupby column1,column3;

案例:

createtable emp(
id intprimarykeyauto_increment,
name varchar(20)notnull,
role varchar(20)notnull,
salary numeric(11,2));insertinto 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 groupby role;

在这里插入图片描述

4.1.3 HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING

显示平均工资低于1500的角色和它的平均工资

select role,avg(salary)from emp groupby role havingavg(salary)<1500;

在这里插入图片描述

4.2 联合查询

实现联合查询的基本机制 : 笛卡尔积
在这里插入图片描述
测试数据:

createtable classes (id intprimarykeyauto_increment,name varchar(20),`desc`varchar(100));createtable student (id intprimarykeyauto_increment,sn varchar(20),name varchar(20),qq_mail varchar(20),classes_id int);createtable course (id intprimarykeyauto_increment,name varchar(20));createtable score (score decimal(3,1),student_id int,course_id int);insertinto classes(name,`desc`)values('计算机系2019级1班','学习了计算机原理、C和Java语言、数据结构和算法'),('中文系2019级3班','学习了中国传统文学'),('自动化2019级5班','学习了机械自动化');insertinto student(sn, name, qq_mail, classes_id)values('09982','黑旋风李逵','[email protected]',1),('00835','菩提老祖',null,1),('00391','白素贞',null,1),('00031','许仙','[email protected]',1),('00054','不想毕业',null,1),('51234','好好说话','[email protected]',2),('83223','tellme',null,2),('09527','老外学中文','[email protected]',2);insertinto course(name)values('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');insertinto 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);

4.2.1 内连接

语法:

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

注:多表查询时,写列的时候要写成

[表名].[列名]

示例1: 查找名字为"许仙"的同学的所有成绩

思路 : 许仙在 student 表中 ,成绩是score表, 对两个表进行笛卡尔积,如何按照条件筛选,名字为许仙,id相同.

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

在这里插入图片描述
在这里插入图片描述

示例2: 查找所有同学的总成绩,以及该同学的基本信息

思路 : 同学信息在student表中,成绩是score表,对两个表进行笛卡尔积.然后按照筛选条件筛选.

select student.id,student.name,sum(score.score)from student,score where student.id = score.student_id groupby student.id;

注: 如果某一列若干行的值已经相同的了,group by 没影响
如果某一个列若干行不相同,group by 最终就只剩下一条记录.

在这里插入图片描述

示例3: 查找所有同学的每一科的成绩,和同学的相关信息

思路 : 需要3张表 studednt表 score表 course表
先对3张表进行笛卡尔积. 然后根据id进行筛选

select student.id,student.name,course.name,score.score from student,score,course where student.id = score.student_id and course.id = score.course_id;

在这里插入图片描述

4.2.2 外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

语法:

-- 左外连接，表1完全显示select 字段名 from 表名1leftjoin 表名2on 连接条件;-- 右外连接，表2完全显示select 字段 from 表名1rightjoin 表名2on 连接条件;

示例1: 查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示

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

在这里插入图片描述

4.2.3 自连接

自连接是指在同一张表连接自身进行查询

示例1: 所有计算机原理的成绩 比 Java 成绩高的同学

  1. 先找到Java和计算机原理课程id — 计算机组成原理id=3 javaid = 1
  2. 对score表 进行 笛卡尔积 (score表 和 score表 分为命名为 s1 和 s2)
  3. 筛选条件1 s1.student_id = s2.student.id
  4. 筛选条件2 s1.course_id = 3 s2.course_id =1
  5. 筛选条件3 s1.score > s2.score
select s1.student_id from score s1,score s2 where s1.student_id = s2.student_id and s1.course_id =3and s2.course_id =1and s1.score > s2.score;

在这里插入图片描述

4.2.4 子查询

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

单行子查询：子查询只有一行

示例1: 查询与“不想毕业” 同学的同班同学：
select name from student where classes_id =(select classes_id from student where name ='不想毕业');

在这里插入图片描述

在这里插入图片描述

多行子查询：返回多行记录的子查询

查询"语文"或者"英文"课程的成绩信息
  1. [NOT] IN关键字：
-- 使用inselect*from score where course_id in(select id from course where name ='语文'or name ='英文');-- 使用 not inselect*from score where course_id notin(select id from course where name !='语文'and name !='英文');

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
2. [NOT] EXISTS关键字：

-- 使用existsselect*from score whereexists(select score.course_id from course where(name ='语文'or name ='英文')and course.id = score.course_id);-- 使用not existsselect*from score wherenotexists(select score.course_id from course where(name !='语文'and name !='英文')and course.id = score.course_id);

在这里插入图片描述
在这里插入图片描述
如果子表查询的结果集合比较小,就使用in
如果子表查询的结果集合比较大,而主表的集合小,就使用exists

4.2.5 合并查询

相当于把多个查询的结果集合合并成一个集合
可以使用集合操作符

union

，

union all

示例1: 查询 id < 3 或者 名字为 "英语"的课程

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

在这里插入图片描述
在这里插入图片描述
如果两个查询结果中存在相同的记录,就会只保留一个
如果不想去重,可以使用 

union all

即可.

示例2: 查询id小于3，或者名字为“Java”的课程

select*from course where id<3unionallselect*from course where name='Java';

在这里插入图片描述

4.2.6 内连 外连 集合图

在这里插入图片描述
在这里插入图片描述

标签： java 开发语言 后端
本文转载自: https://blog.csdn.net/wwzzzzzzzzzzzzz/article/details/122981656
版权归原作者 wwzzzzzzzzzzzzz 所有， 如有侵权，请联系我们删除。
登录后发布评论

“MySQL数据库 ---MySQL表的增删改查(进阶)”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

机器人技术在安全领域的重要作用

安全学习记录——网络篇（一）

图腾柱PFC：HP1010为您的电动两轮车之旅提供绿色，高效，安全的动力

单元测试数据库回滚问题

这才是单元测试，之前我们都错了

【C语言基础】：字符函数和字符串函数

ChatGPT-Next-Web SSRF漏洞+XSS漏洞复现(CVE-2023-49785)

文章导航