0


MySQL 表的增删改查

前言:

在SQL语句中,对于表的增删改查围绕着这几种基本格式:增(create),删(delete),改(update),查(retrieve)。

对于表的操作,这里推荐一个MySQL的可视化工具:Navicat(Navicat 中国 | 支持 MySQL、Redis、MariaDB、MongoDB、SQL Server、SQLite、Oracle 和 PostgreSQL 的数据库管理)

新增:

单行数据 + 指定列插入:

insert into 表名(属性1,属性2...) values(值1,值2...);

多行数据 + 全列插入:

insert into 表名 values (值1,值2...),(值1,值2...),(值1,值2...)...(值1,值2...);

注:属性和值是按顺序一一对应,若类型对应不上则会报错!!!

演示:

我们先看学生的表结构(姓名,班级,语文成绩,数学成绩,英语成绩):

单行全列插入:

已显示插入成功,我们可以查看一下:

单行指定列插入:

可以看到,指定列插入时,没有指定的列默认值为null,也可以指定值为null

多行全列插入:

查询:

全列查询:

select * from 表名;

整张表都显示:

指定列查询:

select 属性1,属性2... from 表名;

表部分属性显示:

表达式:

可以计算出分数总数(属性/列根据行与行之间计算):

注意的是,null不参与表达式计算

别名:

select 属性/表达式 [as] '别名' from 表名;

如上图给chinese + math + English 取个别名:

对于别名,as可以省略,单引号也可以省略(别名中有空格的情况下还是得用单引号括起来)

去重查询:

select DISTINCT 属性 from 表名;

查询出来的一列或多列相同才能被判定为相同数据,重复数据只保留一条数据。

去重前:

去重后:

排序:

select ... from 表名 ... order by 属性 [ ASC / DESC ];

排序分为升序(ASC)和降序(DESC),升序是指从小到大排,降序是指从大到小排,默认是升序。

数据排序中,null会被认为是最小,默认排序就排在最小位。

也可以按名字等其他类型排序:

也可以排序多次,如在班级排序的情况下按总分从大到小排序:

条件查询:

select ... from 表名 ... where 列/表达式 条件符号 [order by...];

注:and的优先级比or高,所以同时使用俩个时最好使用括号来括起来。

增加条件限制来进行查询:

1.查询语文不及格的同学:

2.查询总分高于200的3班同学:

注:在使用别名时,对于条件查询中表达式必须完整,不能使用别名充当条件过滤!!!

3.查询数学成绩比语文好的同学:

4.查询语文比英语好或者语文比数学好的总分高于100的同学:

5.查询数学成绩在80~120分同学并且按高到低的顺序排列:

6.查询姓张的同学:

对于_符号而言,若是匹配一个是同样的结果,若是匹配俩个__就代表找到叫张xx的数据,则为空。

7.查询未知班级的同学:

分页查询(起始下标为0):

select ... from 表名 [where...] [order by...] LIMIT n / LIMIT s , n;

表示从0开始,往后n条 / 从s开始,往后n条

select ... from 表名 [where...] [order by...] LIMIT n OFFSET s;

表示从每页n条记录,偏移量为s(从s开始,往后n条)

计算第x页的偏移量s公式:s =(x - 1) * n

所有表格:

1.查询0开始往后3条的结果:

2.查询从2开始,往后n条的结果:

3.查询从偏移量2开始,共4条记录的结果:

4.查询总分第二的同学:

修改:

单行或多行数据修改:

update 表名 set 列1 = 值1,列2 = 值2... [where...] [order by...];

注:修改时必须加上where条件,否则修改的是整张数据表的所有记录!!!

修改盒子的英语成绩为100:

删除:

delete from 表名 [where] [order by] [limit...]

注:删除不加上where条件,删除的也是整个表的数据!!!

若没有where条件,则删除全部数据!

删除刘奔同学的记录:

数据库约束:

约束类型

NOT NULL:非空约束,被指定not null的列不允许为空

UNIQUE:唯一约束,这个列里的值在表中是唯一的,不能重复

DEFAULT:默认约束,对于没填充的列指定该默认值

PRIMARY KEY:主键约束,NOT NULL 和 UNIQUE 的结合,且一张表只能有一个主键。若是用int类型相关的数据类型置为主键,则可以使用auto_increment关键字来实现自增(注:该自增是使用当前使用过的最大值+1的自增方式,不一定有序)

FOREIGN KEY:外键约束,一张表要与另外一张表的主键或唯一键来进行关联,则可以说这些表存在关键关系。

例:创建一个学生表和班级表,学生表和班级表之间有关联关系,且学生表中学生id为主键,名字默认值为‘无名氏’,性别不为空,兴趣唯一;班级表中设置班级id为主键。

注:

一个表只能有一个主键,但是一个主键可以包含多个列,所以也称为复合主键: primary key(列1,列2...)

外键约束时,添加数据只能添加主表里对应列有的数据,不能添加没有的数据,另外,主表中存在的数据若是和此表相关联,也不可以删除!!!

如班级表里有:

所以班级只能添加1,2,3班:

添加其他的会报错,删除班级也会报错:

表的设计理念:

设计表的时候,一定要满足三大范式:

第一范式:该表中的属性/字段不可再分,如:

不符合第一范式

第二范式:在第一范式的基础上,消除了部分函数依赖,比如一个表中存在复合主键,但是某些字段只对其中一个主键依赖,则可能会造成数据冗余,更新异常等情况:

比如上图:姓名,年龄,学分都之和其中之一个主键挂钩,只有成绩是和学号课程一起挂钩的,所以该图不符合第二范式

把他们拆开后就能符合:

第三范式:在第二范式的基础上,消除了非关键字段的传递依赖。

要想满足第三范式,可以把该表的俩个主体各分为俩张表:

除了三大范式,还要明确实体间的关系:

一对一关系:

一对多关系:

多对多关系:

进阶新增:

insert into 目标表名 [(指定列)] select...

该语法是创立一个新表,把查询结果全部放入到这个新表中

例:将班级表的数据放入class1中

创建一个新表:

查询结果插入到新表中:

进阶查询:

聚合查询:

聚合查询就是进行行与行之间的计算(对于非数值类型不适用)

聚合函数:

注意的是,只有数值类型才可以进行sum,avg,max,min的计算,函数括号内可以包含表达式。

演示:

1.显示班级有多少名写名字的同学:

可以看到,null值不算入里面。

2.显示数学最高分:

3.显示一班四班语文总分:

我们可以看到,null是不参与计算的

4.显示英语最低分:

null值也不参与高低分的比较

5.显示年级平均总分:

group by 子句:

select [需分组列名] ,[聚合函数] from 表名 [where] group by 分组列名 [order by] [limit...];

用于分组查询,对指定列进行分组查询。只有分组的列和在聚合函数里的列才能一起查询。

例:1.查看各个班的平均总分

可以加上round(数据,小数位数)语法来省略小数点,也可以跟上order by和取别名。

2.查看各个班的数学最高分

having:

select [需分组列名] ,[聚合函数] from 表名 [where] group by 分组列名 [having 条件][order by] [limit...];

group by语句进行分组完后,需要条件过滤时,不能使用where语句(分组完后行数缩减,where不适用),而是使用HAVING

例:1.查询平均总分高于300的班级

注意:having后面的条件中和where一样不能使用别名!!!

2.查询每个班姓王同学中数学高于80的最高分数

可以看到,where和having可以同时使用

联合查询:

概念:

联合查询,顾名思义就是联合多张表进行查询操作,然后取这些表的笛卡尔积,利用主键来筛选出联合后的表数据。

笛卡尔积:

笛卡尔积是集合论里的一个概念,描述了俩个或多个集合之间所有可能的序列对,也就是全排列:

俩张实验表(班级和学生表):

内连接:

联合俩张不一样的表的方式称为内连接

select 列名 from 表1,表2... where 连接条件 ...;

select 列名 from 表1 [inner]join 表2** on **连接条件...;

俩种方式都可以取笛卡尔积:

表连接查询的执行过程:
1.先计算连接的笛卡尔积

2.然后使用条件过滤掉无效数据

3.加入查询条件得到需要的结果行

4.精简列名(取别名)得到最终结果列

外连接:

以左(或右)表为基准,左(或右)表的数据全部显示,右(或左)表若是没数据则用null去填充

以左为基准:

select 列 from 表1 left join表2** on **连接条件...;

可以看到右表class表没有的数据用null填充

以右为基准:

select 列 from 表1 right join 表2 **on **连接条件...;

同理,以class为基准,student表没有的数据用null填充

自连接:

联合俩张一样的表的方式称为自连接

select 列名 from 表1 别名1 join 表2 别名2 on 连接条件...;

如有以下三个表,分别为学生表,科目表,成绩表:

例:查询java语言大于计算机组成原理的成绩的记录

第一步,我们先连接三个表:

一般情况下,比较都是列与列进行比较的,但是行与行直接该如何比较呢?

先查询各科不一样的课程成绩取笛卡尔积(连接俩张成绩表,同一名学生不同成绩):

然后筛选出来java和高数的序列号,先后顺序不影响,取其一即可(取序列1和序列2):

后面结合学生表,就可以进行列与列之间的比较了(课程表也需结合俩个,方便与成绩比较):

分类比较(简洁易懂):

例:

注意:若是俩表自连接的列名属性不一样,mysql是不会进行校验,所以结果可能会有问题。

** 自连接的时候要另外取别名,否则判别不出俩张相同表各自属性**

子查询:

也称为嵌套查询,内层查询的查询结果作为外层查询的条件,把多条语句合并在一起。子查询可以****无限嵌套,层数过多会影响效率。

例:查询刘峰的同班同学:

合并查询:

合并多个select的执行结果

例:查询一班和二班的同学

union(会自动去掉结果集中的重复行):

union all(不会去掉结果集中的重复行):

本次关于MySQL的基础使用就介绍到这里,后续还会有MySQL的进阶分享出来,制作不易,感谢大家的支持,谢谢!

标签: mysql 数据库

本文转载自: https://blog.csdn.net/2302_80521088/article/details/141889695
版权归原作者 web旅行者阿玄 所有, 如有侵权,请联系我们删除。

“MySQL 表的增删改查”的评论:

还没有评论