【MySql】基本查询

news/2024/10/18 1:34:47/

文章目录

  • 插入操作insert
  • 查询操作select
    • select查询
    • where条件判断
    • order by排序
    • limit筛选分页结果
  • 更新操作update
  • 删除操作delete
  • 插入查询结果

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

先创建提供一张表：

mysql> create table students (-> id int unsigned primary key auto_increment,-> sn int unsigned unique key,-> name varchar(20) not null,-> qq varchar(32) unique key-> );
Query OK, 0 rows affected (0.26 sec)

插入操作insert

-- 指定列插入
mysql> insert into students (sn,name,qq) values (123,'张三','456789');
Query OK, 1 row affected (0.03 sec)-- 全列插入
mysql> insert into students values (10,124,'关羽','123456');
Query OK, 1 row affected (0.04 sec)-- 多行插入
mysql> insert into students values (13,127,'王五','14678909'),(14,128,'赵七','567890'),(15,129,'陈九','7890123');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

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

INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...

image-20230612190626621

mysql> insert into students values (13,132,'许攸','111111') on duplicate key update sn=132,name='xuyyou',qq='1111';
Query OK, 2 rows affected (0.04 sec)-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，并且数据已经被更新

发生主键冲突把插入操作改为更新操作

替换

-- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入
mysql> replace into students (sn,name,qq) values (140,'许攸','34567812');
Query OK, 1 row affected (0.03 sec)
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入

查询操作select

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

创建一张表：

-- 创建表结构
CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩'
);-- 插入测试数据
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);

select查询

全列查询

image-20230612194518939

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

指定列查询

-- 指定列的顺序不需要按定义表的顺序来
SELECT id, name, english FROM exam_result;

image-20230612194726928

查询字段为表达式

-- 表达式不包含字段
SELECT id, name, 10 FROM exam_result;

image-20230612195259892

-- 表达式包含多个字段
SELECT name,math, chinese + math + english FROM exam_result;

image-20230612195502482

为查询结果指定别名

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

比如上面我们的查询名称太长，加上as即可：

image-20230612195601375

当然，下面这样也是可以的：

image-20230612195741439

结果去重distinct

select distinct math from exam_result;

image-20230612200152730

where条件判断

比较运算符：

运算符 									说明
>, >=, <, <= 						大于，大于等于，小于，小于等于
= 									等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>									等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <> 								不等于
BETWEEN a0 AND a1 					范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, ...) 					如果是 option 中的任意一个，返回 TRUE(1)
IS NULL 							是 NULL
IS NOT NULL 						不是 NULL
LIKE 								模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

逻辑运算符：

运算符 						说明
AND 				多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR 					任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT					条件为 TRUE(1)，结果为 FALSE(0)

下面，举一些案列来进行练习一下把：

  • 英语不及格的同学及英语成绩 ( < 60 )
select name,english from exam_result where english<60;

image-20230612201151019

  • 语文成绩在 [80, 90] 分的同学及语文成绩
select name,chinese from exam_result where chinese>=80 and chinese<=90;

image-20230612201333958

 select name,chinese from exam_result where chinese between 80 and 90;

image-20230612201447761

  • 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
select name,math from exam_result where math=58 or math=59 or math=98 or math=99;

image-20230612201639111

select name,math from exam_result where math in (58,59,98,99);

image-20230612201751705

  • 姓孙的同学 及 孙某同学
select name from exam_result where name like '孙%';select name from exam_result where name like '孙_';

image-20230612202256140

  • 语文成绩好于英语成绩的同学
select name,chinese,english from exam_result where chinese > english;

image-20230612202402744

  • 总分在 200 分以下的同学
select name,chinese+english+math from exam_result where chinese+english+math<200;

image-20230612202657562

注意：下面这样写是错误的：

mysql> select name,chinese+english+math total from exam_result where total<200;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'
mysql>

这是由于sql的执行顺序影响：这里的执行顺序非常重要，这里无法使用别名，很简单：这是因为先执行from,先从哪个表里筛选数据，筛选的时候，得先设定筛选条件

image-20230612203353738

当然，下面这样也是错误的：不能在筛选条件做重命名

image-20230612203623043

  • 语文成绩 > 80 并且不姓孙的同学
select name,chinese from exam_result where chinese >80 and name not like '孙%';

image-20230612203919113

  • 孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
select name,chinese,math,english,chinese+math+english 总分 from exam_result where name like'孙_' or  (chinese+math+english>200 and chinese < math and english>80);

image-20230612204322235

  • NULL 的查询

为了演示，先创建一张表test：

image-20230612204712628

select * from test where name is null;

image-20230612204745578

order by排序

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

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

下面，通过一些案例来理解order by排序：

  • 同学及数学成绩，按数学成绩升序显示
 select name,math from exam_result order by math asc;

image-20230612205303662

  • 同学排序显示
-- NULL 视为比任何值都小，升序出现在最上面

image-20230612205532311

  • 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
select name,math,english,chinese from exam_result order by math desc,english asc,chinese asc

image-20230612205902228

注意：order by默认是升序asc 排序

  • 查询同学及总分，由低到高
select name,math+chinese+english as total from exam_result order by total;

image-20230612210207058

为什么里又能使用别名了呢？where后面不能使用别名，为什么order by排序这里可以了呢？

要对表结构的数据做排序，一定是得先有数据的，然后再进行排序。

在这里插入图片描述

  • 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示
select name,math from exam_result where name like '孙%' or name like '曹%' order by math desc;

image-20230614105603234

limit筛选分页结果

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

image-20230614110244193

image-20230614110109012

需要由数据才能排序，只有数据准备好了，你才要显示，limit的本质功能是“显示”

更新操作update

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

对查询到的结果进行列值更新

  • 将孙悟空同学的数学成绩变更为 80 分
update exam_result set math=80 where name='孙悟空';

image-20230614111325331

  • 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
update exam_result set math=60,chinese=70 where name='曹孟德';

image-20230614111704366

  • 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
 select name,math+chinese+english total from exam_result order by total asc limit 3;

image-20230614122933070

  • 将所有同学的语文成绩更新为原来的 2 倍
update exam_result set chinese=chinese*2;

image-20230614123447406

删除操作delete

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
  • 删除孙悟空同学的考试成绩
delete from exam_result wherename='孙悟空';

image-20230614123917746

  • 删除整张表数据
-- 准备测试表
CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)
-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');-- 删除整表数据
DELETE FROM for_delete;-- 查看删除结果
SELECT * FROM for_delete;
Empty set (0.00 sec)-- 再插入一条数据，自增 id 在原值上增长
INSERT INTO for_delete (name) VALUES ('D');
  • 截断表
  1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
  2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚
  3. 会重置 AUTO_INCREMENT 项
TRUNCATE [TABLE] table_name

举例子：

-- 准备测试表
CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);-- 插入测试数据
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
TRUNCATE for_truncate;

image-20230614125301516

-- 再插入一条数据，自增 id 在重新增长
INSERT INTO for_truncate (name) VALUES ('E');

image-20230614125342791

-- 查看表结构，会有 AUTO_INCREMENT=2 项
SHOW CREATE TABLE for_truncate\G

image-20230614125423155

插入查询结果

INSERT INTO table_name [(column [, column ...])] SELECT ...
  • 删除表中的的重复记录，重复的数据只能有一份
-- 创建原数据表
CREATE TABLE duplicate_table (id int, name varchar(20));-- 插入测试数据
INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');

image-20230614172612701

-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样
CREATE TABLE no_duplicate_table LIKE duplicate_table;
-- 将 duplicate_table 的去重数据插入到 no_duplicate_table
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
-- 通过重命名表，实现原子的去重操作
RENAME TABLE duplicate_table TO old_duplicate_table,
no_duplicate_table TO duplicate_table;
-- 查看最终结果
SELECT * FROM duplicate_table;

image-20230614184618308

为什么以后是通过rename方式机型的：就是单纯地想等一切都就绪了，然后统一放入、更新、生效等


http://www.ppmy.cn/news/386824.html

相关文章

次时代的终端产品

次时代的终端产品

看到一位领导报告&#xff0c;其中一些文字觉得总结十分到位&#xff0c;在此做一下记录。 工业革命下的产品&#xff0c;每一个时代都有这个时代进入千家万户的终端产品。 第一次工业革命是机械化&#xff0c;当时进入千家万户的&#xff0c;是200多年前开始出现的自行车、缝纫…
阅读更多...
黑客松必备｜Bear Necessities Hackathon Office Hours汇总

黑客松必备｜Bear Necessities Hackathon Office Hours汇总

由Moonbeam和AWS Startups联合主办的Bear Necessities Hackathon黑客松启动仪式已于5月30日举行。本次黑客松将历时约1个月的时间&#xff0c;包含6个挑战&#xff0c;分别由Moonbeam基金会、Chainlink、StellaSwap、SubQuery、Biconomy提供赞助&#xff0c;总奖池超过5万美金。…
阅读更多...
全球限量1000台！京东联手格力首推全AI定制冰箱 618开售

全球限量1000台！京东联手格力首推全AI定制冰箱 618开售

又是一年京东店庆月。每年618京东都会玩一些新花样&#xff0c;为各位剁手党提供价格优惠、品质优良的好产品。今年京东与“家电一姐”格力强强联合&#xff0c;推出了定制款“格力晶弘303升变频风冷多门冰箱”。它不仅是业界首款全部由人工智能&#xff08;AI&#xff09;进行…
阅读更多...
【后端面经-数据库】MySQL的事务隔离级别简介

【后端面经-数据库】MySQL的事务隔离级别简介

【后端面经-数据库】MySQL的事务隔离级别简介 0. 事务的概念1. 三类问题2. 事务隔离级别3. 操作指令4. 总结5. 参考博文 0. 事务的概念 事务指的是一连串的集中操作指令&#xff0c;一个事务的执行必须执行完所有的动作才能算作执行结束。事务具有四个特点&#xff0c;简记作A…
阅读更多...
【力扣 】查找所有至少连续出现三次的数字/求连续登录3天的用户数（SQL完成）

【力扣 】查找所有至少连续出现三次的数字/求连续登录3天的用户数（SQL完成）

0. 题目 编写一个 SQL 查询&#xff0c;查找所有至少连续出现三次的数字。 返回的结果表中的数据可以按 任意顺序 排列。 输入&#xff1a; Logs 表&#xff1a; —---- | Id | Num | —---- | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | —---- …
阅读更多...
空压机设备远程监控解决方案

空压机设备远程监控解决方案

空压机设备远程监控解决方案 空压机物联网系统&#xff0c;通过物联网技术实现远程监控管理功能&#xff0c;空压机物联网系统将终端空压机的各种数据信息传输到监控中心&#xff0c;便于时时在线监控、记录、查询、统计、分析。空压机物联网&#xff0c;同时客户可使用PC端、…
阅读更多...
SRE 的工作介绍

SRE 的工作介绍

哈喽大家好&#xff0c;我是咸鱼 今天看到了一篇很不错的文章&#xff0c;作者是一名 SRE 工程师&#xff0c;在 Shopee 工作&#xff0c;base 新加坡 分享出来给大家看看 作者&#xff1a;卡瓦邦噶 原文链接&#xff1a;https://www.kawabangga.com/posts/4481 原文如下&…
阅读更多...
MAC电脑设置权限

MAC电脑设置权限

​​​​​​​ click on your background to go to finderclick on go and go to folder /usrright click on local and do get infounlock the lock at the bottomclick sign and add your user to the list and give read/write privilegesclick on the gear sign at the …
阅读更多...
最新文章

Copyright @ 2022~2023