【MySql】基本查询
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');- 截断表
- 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
- 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚
- 会重置 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方式机型的：就是单纯地想等一切都就绪了，然后统一放入、更新、生效等
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2023-06-14，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。
腾讯云开发者
