0
0

【MySQL 保姆级教学】表数据的操作--上（7）

表的增删改查

CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）

1. 创建一个表 CREATE

语法：

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

示例：

create table students(
id int unsigned primary key auto_increment,
sn int not null unique comment ‘学号’,
name varchar(8) not null,
qq varchar(12) );

2 插入数据 INSERT INTO

2.1 语法

语法：

INSERT [INTO] table_name
[(column [, column] …)]
VALUES (value_list)
[, (value_list)] … value_list: value, [, value] …

注：

INTO

可以省略不写，

insert into

是只对表进行执行，

insert into

后不跟关键字

table

，而是==直接跟表名 ==。

2.2 插入单行数据+全列插入

示例：

  1. 全列插入, 写出插入的列。 命令：insert into students (id, sn, name, qq) values(1, 300, '李明', 888);在这里插入图片描述
  2. 全列插入，不写出插入的列。 命令：insert into students values(2, 400, '张三', 666);在这里插入图片描述
  3. 插入单行数据，不插入自增型的列。 命令：insert into students (sn, name, qq) values(500, '李逵', 999);在这里插入图片描述
  4. 查看插入的数据。 命令：select * from students;在这里插入图片描述 注：AUTO_INCREMENT 列会自动为每一行生成一个唯一的标识符，通常从1开始，并且每次插入新行时递增1。

2.3 插入多行数据+指定列插入

指定列插入时，插入的数据必须和指定列对应。

示例：

  1. 插入单行，指定 sn和name 列 命令：insert into students (sn, name) values(600, '郭靖');在这里插入图片描述
  2. 插入多行，指定 sn、 name和 qq列。 命令：> insert into students> (sn, name, qq)> values(700, ‘李华’, 111),> (800, ‘龙葵’, 222);在这里插入图片描述
  3. 查看插入的数据 命令：select * from students;在这里插入图片描述 注：AUTO_INCREMENT 列会自动为每一行生成一个唯一的标识符，通常从1开始，并且每次插入新行时递增1。

2.4 同步更新 ON DUPLICATE KEY UPDATE

2.4.1 引入

由于 主键 或者 唯一键 对应的值已经存在而导致插入失败

  1. 主键存在导致失败： 命令：insert into students (id, sn, name) values(1, 900, '杨过' );在这里插入图片描述
  2. 唯一键存在导致错误： 命令：insert into students (id, sn, name) values(100, 300, '杨过' );在这里插入图片描述

2.4.2 同步更新

可以选择性进行同步更新操作：
语法：

INSERT INTO table_name ON DUPLICATE KEY UPDATE
column = value [, column = value] …

示例：

主键存在，进行更新：

  1. 使用 on duplicate key upfate 命令：insert into students values(1, 400, '李白', 111) on duplicate key update sn=400, name='李白', qq=1;在这里插入图片描述 使用了 on duplicate key update 怎么还是错误呢？ 仔细观察sn = 400，我们创建表时，sn int not null unique comment '学号', 所以，sn 的列是唯一的，跟新后的sn 与表中已存在的 sn 冲突了。
  2. 正确使用 on duplicate key update 命令：insert into students values(1, 100, '李白', 111) on duplicate key update sn=100, name= '李白', qq=111;在这里插入图片描述

唯一键存在，进行更新：

  1. 把 sn=800 的行进行更换。 命令：insert into students values(2, 800, '程咬金', 333) on duplicate key update id=2, name='程咬金, qq=333;在这里插入图片描述 同学们可对比没换之前的下图，是不是出现了问题？在这里插入图片描述 是不是没有把 sn=800 的列进行更换，而是把 id=2的进行了更换。 结论，on duplicate key update 是按照vulues的先后顺序进行识别，再进行更换。
  2. 更改 sn=800的行 命令：insert into students (sn, name, qq) values(800, '雪见', 888) on duplicate key update name='雪见', qq=888;在这里插入图片描述

2.4.3 查看被影响的行

当使用

on duplicate key update

时，执行后会显示 

** rows affected

:
– 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等。
– 1 row affected: 表中没有冲突数据，数据被插入
– 2 row affected: 表中有冲突数据，并且数据已经被更新

也可以使用命令查询被影响的行数
语法：

SELECT ROW_COUNT();

2.5. 替换 REPLACE INTO

语法：

REPLACE INTO table_name ...

(和insert into 用法一样)

替换规则：

  • 主键 或者 唯一键 没有冲突，则直接插入；
  • 主键 或者 唯一键 如果冲突，则删除后再插入

示例：

  1. 命令：replace into students values(6, 700, '小龙女', 444)在这里插入图片描述
  2. 命令：replace into students values(5, 700, '白居易', 555);在这里插入图片描述

同学们可以看看前后对比；这里是把values中重复的键全部删除后替换了。

当使用

replace into

时，执行后会显示 

** rows affected

:

  • 1 row affected: 表中没有冲突数据，数据被插入
  • 2 row affected: 表中有一个冲突数据，删除后重新插入
  • 3 row affected: 表中有二个冲突数据，全部删除后重新插入

3. Retrieve（查询SELECT）

3.1 语法

语法：

SELECT
[DISTINCT] {* | {column [, column] …}
[FROM table_name]
[WHERE …]
[ORDER BY column [ASC | DESC], …]
LIMIT …

样例：

创建表：

create table score(
id int not null unique comment ‘学号’，
name varchar(8) not null comment ‘姓名’，
chinese tinyint default 0.0，
math tinyint default 0.0，
english tinyint default 0.0
);

插入数据：

insert into score
values(1,‘雷军’，100,100,100)，
(2, ‘马化腾’，91,93,90)，
(3, ‘马云’, 90,45,91),
(4, ‘李白’,90, 60, 30),
(5, ‘李华’, 72, 80, 88),
(6, ‘白居易’, 90, 75, 70
);

3.2 全列查询 SELECT

通常情况下不建议使用 * 进行全列查询，查询的列越多，意味着需要传输的数据量越大，可能会影响到索引的使用。

示例：

  1. 指定全列进行查询。 命令：selecte id, name, chinese, math, english from score;在这里插入图片描述
  2. 使用 * 进行查询。 命令：select * from score;在这里插入图片描述

3.3 指定列查询

指定列查询不需要按定义表的顺序来，显示的数据序列是指定的顺序。

示例：
命令：

select name, id, chinese from score;

在这里插入图片描述

3.4 查询字段为表达式

在SQL中，查询字段可以是一个表达式，这允许你在查询结果中直接进行计算。这种功能非常有用，因为它可以在不改变表结构的情况下，动态地生成所需的计算结果。

示例：

  1. 表达式为 1+1 命令：select '1+1;'在这里插入图片描述
  2. 更改表达式的列名为 ‘ 计算 ’。 命令：selecte '1+1' as '计算' ;在这里插入图片描述
  3. 表达式为计算总成绩 ‘ chinese+math+english’ 命令：select id, name, 'chinese +math + english from sacore;'在这里插入图片描述
  4. 更改计算总成绩的列名 命令：select id, name, chinese+ math+ english as total from score;在这里插入图片描述
  5. 给 ‘chinese’ 列 增加100, 命名为 new_chinese 命令：selecte id, name, chinese+100 as new_chinese, math, english from score;在这里插入图片描述 这里的chinese+10只是在打印的时候显示，并没有改变表中实际的chinese值。

3.5 为查询结果指定别名 AS

在SQL查询中，使用别名（Alias）是一种常见的做法，它提供了多种好处，使查询结果更易读、更灵活。别名可以应用于列名、表名甚至子查询。
语法:

SELECT column [AS] alias_name [...] FROM table_name;

示例：

  1. 设置id 别名为 “学号”，设置 name 别名为 “姓名” 命令：select id, name from score;在这里插入图片描述
  2. 设置总分为 chinese*0.4 + math*0.4 + english*0.2,设置别名为 “成绩” 命令：select id, name, chinese*0.4 + math*0.4 + english*0.2 as '成绩' from score;在这里插入图片描述

3.6 查询结果去重 DISTINCT

示例：

  1. 打印未去重的chinese 列 命令：select name, chinese from score;在这里插入图片描述
  2. 用 distinct 关键字去重。 命令：select distinct chinese from score ;在这里插入图片描述 显然，重复的90只剩下一个了。

4. WHERE 条件

4.1 运算符

  1. 比较运算符
    运算符说明>,>=,<,<=大于，大于等于，小于，小于等于=等于，NULL 不安全，例如 NULL=NULL的结果是NULL<=>等于，NULL 安全，例如，NULL<=>NULL 的结果是TRUE（1）IS NULL是 NULLIS NOT NULL不是 NULL

  2. 逻辑运算符
    运算符说明AND多个条件必须都为TRUE（1），结果才为TRUE（1）OR任意一个条件为TRUE（1），结果才为TRUE（1）NOT条件为TRUE（1），结果为（0）；相当于 ‘非’

  3. 条件运算符
    运算符说明BETWEEN n1 AND n2范围匹配，[ n1, n2 ],如果 n1<= value <= ,返回TRUE（1）IN检查某个值是否存在于一个指定的值列表或子查询结果中LIKE模糊匹配。%表示任意多个（包括0）个任意字符；；_表示任意一个字符

    4.2 示例

4.2.1 所有查询示例

  1. 查询英语不及格的同学 命令：select id, name from score where english<60;在这里插入图片描述
  2. 语文成绩在[ 80, 90] 分的同学，使用 and 连接 命令：select id, name, chinese from score where chinese>=80 and chinese<=90;在这里插入图片描述
  3. 数学成绩在[ 90，100 ] 分的同学，使用 between...and.. 连接 命令：select id, name, select score where chinese between 90 and 100;在这里插入图片描述
  4. 数学成绩等于100，或则 数学成绩小于等于100的同学 命令：select id, name, math from score where math<=60 or math=100;在这里插入图片描述
  5. 使用 in 查询 英语成绩为 （70,80,90，100）的同学 命令：select id, name, english from score where english in (70,80, 90, 100);在这里插入图片描述
  6. 查询姓 “李” 的同学，多个字符匹配 命令：select * from score where name like "李% ;"在这里插入图片描述
  7. 查询名字为两个字，且名为 ‘军’ 的人 命令：select * from score where name like '_军';在这里插入图片描述
  8. 查询语文成绩好于英语成绩的同学 命令：select id, name, chinese, english from score wehere chinese>english;在这里插入图片描述
  9. chinese*0.4 + nmath*0.4 + english*0.2 按比例的总成绩大于80的同学 命令：select id, name, chinese*0.4+math*0.4+english).2 from score where chinese*0.4 + math *0.4+english*0.2 > 80;
  10. 数学成绩大于80，但是不姓雷的同学 命令：select id, name, mah from score where math>80 and name not like '雷%';在这里插入图片描述
  11. 查询数学成绩已知的同学 命令：select id, name from score where math id not null;在这里插入图片描述
  12. 查询数学成绩未知的同学 命令：select id, name from score where chinese is null;在这里插入图片描述 表示数学成绩为空的同学 不存在。
  13. NULL 和 NULL 的 比较，“=” 命令：select NULL=NULL;在这里插入图片描述 在SQL中，NULL 表示一个未知或不存在的值。与其他编程语言不同，SQL中的 NULL 不能直接使用等号 = 进行比较。具体来说，NULL = NULL 的结果不是 TRUE，而是 UNKNOWN。为什么 NULL = NULL 结果是 UNKNOWN？ 在SQL中，NULL 表示一个未知的值。当你尝试比较两个 NULL 值时，实际上是问“未知值是否等于另一个未知值”。由于这两个值都是未知的，因此无法确定它们是否相等，所以结果是 UNKNOWN。如何正确比较 NULL 值？ 为了检查一个值是否为 NULL，应该使用 IS NULL 或 IS NOT NULL 运算符。
  14. NULL 和 NULL 的 比较，“<=>” 命令：select NULL<=>NULL;在这里插入图片描述 在SQL中，<=> 是一个特殊的运算符，称为==“空安全等值运算符”（null-safe equality operator）。它用于比较两个值，包括 NULL 值，并且在两个值都为 NULL 时返回 TRUE==

4.2.2 易错点

  1. 命令：select id, name, chinese*0.4+math*0.4+english*0.2 from score where chinese*0.4 + math *0.4+english*0.2 > 80;上述命令能否改成：select id, name, chinese*0.4+math*0.4+english*.2 as total from score where total > 80; 这样写不是方便多了吗？ 我们执行一下试试在这里插入图片描述为什么会报错呢？服务端执行命令的时候是后先后顺序的，先执行from score 检索表，再执行 where total > 80执行条件,最后 执行 select id, name, chinese*0.4+math*0.4+english*.2 as total 需要打印出的数据。在这个命令中，当执行where total > 80 的时候没有total的关键字的定义，显然会报错。那同学们是不是想到这样写了呢？ 命令：select id, name, total from score where chinese*0.4+math*0.4+english*0.2 as total >80在这里插入图片描述 这又为什么会发生错误呢？ 因为 这里的total 只是作为 chinese*0.4+math*0.4+english*0.2结果的重命名，而不是重新生成了一个列。总之，还是要按正确的格式来写， 命令：select id, name, chinese*0.4+math*0.4+english*.2 as total from score where chinese*0.4+math*0.4+english*0.2 > 80;

查询命令的执行顺序：from table_name 从哪个表查询 --> where筛选数据内容 --> 显示表的内容

5. 排序 ORDER BY

5.1 语法：

– ASC 为升序（从小到大）
– DESC 为降序（从大到小）
– 默认为 ASC
SELECT … FROM table_name [WHERE …]
ORDER BY column [ASC|DESC], […];

注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

5.2 示例

  1. 按数学成绩排序（默认是升序）。 命令：select * from score order by math;在这里插入图片描述
  2. 按数学成绩降序排列 desc 命令：select * from score order by math desc;在这里插入图片描述如果数学中有 NULL 值，NULL值被视为最小的值，会放到最下面。
  3. 按语文降序排列，如果语文值相等则数学降序排列。 命令：select * from score order by math desc ,chinese desc;在这里插入图片描述
  4. 查询学生总分，按总分的降序排列 命令：select id, name, chinese+math+english from score order by chinese+math+english desc;在这里插入图片描述
  5. 按总分的降序排列，order by 别名 命令：select id, name, chinese+math+english as total from score order by total desc;在这里插入图片描述 这里为什么可以用total 别名了呢？查询命令的执行顺序：from table_name 从哪个表查询 --> where筛选数据内容 --> 显示表的内容 --> order by 排序这时候显示的内容是一个新表，可以用新表的列名进行操作。
  6. 查询姓马和姓雷的同学名字，把他们的总成绩按降序排列 命令：select id,name, chinese+math+english as total from score where name like '马%' or name like '雷%' order by total;在这里插入图片描述

6. 筛选分页 LIMIT OFFSET

6.1 语法

– 起始下标为 0
1.- 从 0 开始，筛选 n 条结果
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n;
2.- 从 s 开始，筛选 n 条结果
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT s, n
3.- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n OFFSET s;

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死

6.2 示例

  1. 查询三行。在这里插入图片描述
  2. 查询总成绩的前三名 命令：select * from score order by chinese+math+english desc limit 3;在这里插入图片描述
  3. 查询总成绩，从第二名开始的依次后三人。 命令：select * from score order by chinese+math+english limit 1,3; 说明：1是行的索引， 3是页数。在这里插入图片描述
  4. 查询总成绩，从第二名开始的一次后三人，使用offset 命令：select * from score order by chinese+math+english desc limit 3 offset 1; 说明：limit 3 是查询三页， offset 1 是从索引1开始查。在这里插入图片描述
标签： mysql android 数据库
本文转载自: https://blog.csdn.net/2302_79527141/article/details/142985157
版权归原作者 Code哈哈笑 所有， 如有侵权，请联系我们删除。
登录后发布评论

“【MySQL 保姆级教学】表数据的操作--上（7）”的评论:

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

信息安全工程师（83）Windows操作系统安全分析与防护

Linux下open函数详解

米思奇安装——Mac版本

MySQL 创建数据库

Ubuntu 解决Oh no Something has gone wrong 方法

深入了解 Jenkins、GitLab 和 SSH 密钥的配置与管理

鸿蒙HarmonyOS应用开发 | 仓颉开发语言Linux 版本工具链的支持与安装

文章导航