MySQL基本查询

2年前 (2023-03-10)未命名137

MySQL基本查询还小给个面子于2023-03-06 20:19:12发布 1557 收藏 23 分类专栏： MySQL 文章标签： mysql 数据库 sql MySQL 专栏收录该内容 6 篇文章 0 订阅订阅专栏

文章目录表的增删查改Create（创建）单行数据 + 全列插入多行数据 + 指定列插入插入否则更新替换 Retrieve（读取）SELECT列全列查询指定列查询查询字段为表达式查询结果指定别名结果去重 WHERE 条件基本比较BETWEEN AND 条件连接OR 条件连接IN 条件连接LIKE 条件匹配WHERE 条件中使用表达式AND 与 NOT 的使用综合性查询NULL的查询结果排序升序显示降序排序多字段排序ORDER BY 使用表达式结合 WHERE 子句和 ORDER BY 子句筛选分页结果 Update（更新）更新单列更新多列更新值为原值基础上变更更新全表 Delete（删除）删除单条记录删除整表截断表插入查询结果聚合函数group by子句的使用

表的增删查改

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

Create（创建）

基本语法：

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

案例：

单行数据 + 全列插入

插入两条记录，当value_list 数量和定义表的列的数量及顺序一致时，就可以省略value_list。注意，这里在插入的时候，也可以不用指定id，mysql会使用默认的值进行自增。

mysql> insert into students values (100, 1000, 'Curry', NULL); Query OK, 1 row affected (0.01 sec) mysql> insert into students values (101, 1001, 'Durant', '3306@163.com'); Query OK, 1 row affected (0.00 sec) mysql> select * from students; +-----+------+--------+--------------+ | id | sn | name | email | +-----+------+--------+--------------+ | 100 | 1000 | Curry | NULL | | 101 | 1001 | Durant | 3306@163.com | +-----+------+--------+--------------+ 2 rows in set (0.00 sec)

多行数据 + 指定列插入

插入两条记录，value_list 数量必须和指定列数量及顺序一致

mysql> insert into students (id, sn, name) values (102, 1002, 'Kobe'), (103, 1003, 'Klay'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from students; +-----+------+--------+--------------+ | id | sn | name | email | +-----+------+--------+--------------+ | 100 | 1000 | Curry | NULL | | 101 | 1001 | Durant | 3306@163.com | | 102 | 1002 | Kobe | NULL | | 103 | 1003 | Klay | NULL | +-----+------+--------+--------------+ 4 rows in set (0.00 sec)

插入否则更新

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

主键冲突：

mysql> insert into students (id, sn, name) values (100, 1004, 'Brown'); ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'

唯一键冲突：

mysql> insert into students (id, sn, name) values (104, 1003, 'Bryant'); ERROR 1062 (23000): Duplicate entry '1003' for key 'sn'

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

INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ... mysql> insert into students (id, sn, name) values (104, 1003, 'Bryant') -> on duplicate key update id=104, name='Bryant'; Query OK, 2 rows affected (0.01 sec) mysql> select * from students; +-----+------+--------+--------------+ | id | sn | name | email | +-----+------+--------+--------------+ | 100 | 1000 | Curry | NULL | | 101 | 1001 | Durant | 3306@163.com | | 102 | 1002 | Kobe | NULL | | 104 | 1003 | Bryant | NULL | +-----+------+--------+--------------+ 4 rows in set (0.00 sec) 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等1 row affected: 表中没有冲突数据，数据被插入2 row affected: 表中有冲突数据，并且数据已经被更新

替换

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

mysql> replace into students (sn, name) values (1002, 'Mitchell'); Query OK, 2 rows affected (0.00 sec) mysql> select * from students; +-----+------+----------+--------------+ | id | sn | name | email | +-----+------+----------+--------------+ | 100 | 1000 | Curry | NULL | | 101 | 1001 | Durant | 3306@163.com | | 104 | 1003 | Bryant | NULL | | 105 | 1002 | Mitchell | NULL | +-----+------+----------+--------------+ 4 rows in set (0.00 sec) 1 row affected: 表中没有冲突数据，数据被插入2 row affected: 表中有冲突数据，删除后重新插入

Retrieve（读取）

基础语法：

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

案例：

创建表结构：

mysql> 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 '英语成绩' -> )engine=innodb default charset=utf8; Query OK, 0 rows affected (0.02 sec)

插入测试数据：

mysql> 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); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0

SELECT列全列查询

通常情况下不建议使用 * 进行全列查询

查询的列越多，意味着需要传输的数据量越大；可能会影响到索引的使用； mysql> select * from exam_result; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 1 | 唐三藏 | 67 | 98 | 56 | | 2 | 孙悟空 | 87 | 78 | 77 | | 3 | 猪悟能 | 88 | 98 | 90 | | 4 | 曹孟德 | 82 | 84 | 67 | | 5 | 刘玄德 | 55 | 85 | 45 | | 6 | 孙权 | 70 | 73 | 78 | | 7 | 宋公明 | 75 | 65 | 30 | +----+-----------+---------+------+---------+ 7 rows in set (0.00 sec)

指定列查询

指定列的顺序不需要按定义表的顺序来

mysql> select id, name, math from exam_result; +----+-----------+------+ | id | name | math | +----+-----------+------+ | 1 | 唐三藏 | 98 | | 2 | 孙悟空 | 78 | | 3 | 猪悟能 | 98 | | 4 | 曹孟德 | 84 | | 5 | 刘玄德 | 85 | | 6 | 孙权 | 73 | | 7 | 宋公明 | 65 | +----+-----------+------+ 7 rows in set (0.00 sec)

查询字段为表达式

表达式不包含字段：

mysql> select id, name, 10 from exam_result; +----+-----------+----+ | id | name | 10 | +----+-----------+----+ | 1 | 唐三藏 | 10 | | 2 | 孙悟空 | 10 | | 3 | 猪悟能 | 10 | | 4 | 曹孟德 | 10 | | 5 | 刘玄德 | 10 | | 6 | 孙权 | 10 | | 7 | 宋公明 | 10 | +----+-----------+----+ 7 rows in set (0.00 sec)

表达式包含一个字段：

mysql> select id, name, math+10 from exam_result; +----+-----------+---------+ | id | name | math+10 | +----+-----------+---------+ | 1 | 唐三藏 | 108 | | 2 | 孙悟空 | 88 | | 3 | 猪悟能 | 108 | | 4 | 曹孟德 | 94 | | 5 | 刘玄德 | 95 | | 6 | 孙权 | 83 | | 7 | 宋公明 | 75 | +----+-----------+---------+ 7 rows in set (0.00 sec)

表达式包含多个字段：

mysql> select id, name, math+chinese+english from exam_result; +----+-----------+----------------------+ | id | name | math+chinese+english | +----+-----------+----------------------+ | 1 | 唐三藏 | 221 | | 2 | 孙悟空 | 242 | | 3 | 猪悟能 | 276 | | 4 | 曹孟德 | 233 | | 5 | 刘玄德 | 185 | | 6 | 孙权 | 221 | | 7 | 宋公明 | 170 | +----+-----------+----------------------+ 7 rows in set (0.00 sec)

查询结果指定别名

基础语法：

ELECT column [AS] alias_name [...] FROM table_name; mysql> select id, name, math+chinese+english total from exam_result; +----+-----------+-------+ | id | name | total | +----+-----------+-------+ | 1 | 唐三藏 | 221 | | 2 | 孙悟空 | 242 | | 3 | 猪悟能 | 276 | | 4 | 曹孟德 | 233 | | 5 | 刘玄德 | 185 | | 6 | 孙权 | 221 | | 7 | 宋公明 | 170 | +----+-----------+-------+ 7 rows in set (0.00 sec)

结果去重

查询结果重复：

mysql> select math from exam_result; +------+ | math | +------+ | 98 | | 78 | | 98 | | 84 | | 85 | | 73 | | 65 | +------+ 7 rows in set (0.00 sec)

查询结果去重：

mysql> select distinct math from exam_result; +------+ | math | +------+ | 98 | | 78 | | 84 | | 85 | | 73 | | 65 | +------+ 6 rows in set (0.00 sec)

WHERE 条件基本比较

英语不及格的同学及英语成绩 ( < 60 )：

mysql> select name, english from exam_result where english<60; +-----------+---------+ | name | english | +-----------+---------+ | 唐三藏 | 56 | | 刘玄德 | 45 | | 宋公明 | 30 | +-----------+---------+ 3 rows in set (0.00 sec)

BETWEEN AND 条件连接

语文成绩在 [80, 90] 分的同学及语文成绩：

使用 AND 进行条件连接

mysql> select name, chinese from exam_result where chinese>=80 and chinese<=90; +-----------+---------+ | name | chinese | +-----------+---------+ | 孙悟空 | 87 | | 猪悟能 | 88 | | 曹孟德 | 82 | +-----------+---------+ 3 rows in set (0.00 sec)

使用 BETWEEN AND 条件连接

mysql> select name, chinese from exam_result where chinese between 80 and 90; +-----------+---------+ | name | chinese | +-----------+---------+ | 孙悟空 | 87 | | 猪悟能 | 88 | | 曹孟德 | 82 | +-----------+---------+ 3 rows in set (0.00 sec)

OR 条件连接

数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩：

mysql> select name, math from exam_result where math=58 or math=59 or math=98 or math=99; +-----------+------+ | name | math | +-----------+------+ | 唐三藏 | 98 | | 猪悟能 | 98 | +-----------+------+ 2 rows in set (0.00 sec)

IN 条件连接

数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩：

mysql> select name, math from exam_result where math in (58,59,98,99); +-----------+------+ | name | math | +-----------+------+ | 唐三藏 | 98 | | 猪悟能 | 98 | +-----------+------+ 2 rows in set (0.00 sec)

LIKE 条件匹配

查找姓孙的同学：% 匹配任意多个（包括 0 个）任意字符

mysql> select name from exam_result where name like '孙%'; +-----------+ | name | +-----------+ | 孙悟空 | | 孙权 | +-----------+ 2 rows in set (0.00 sec)

查找孙某同学： _ 匹配严格的一个任意字符

mysql> select name from exam_result where name like '孙_'; +--------+ | name | +--------+ | 孙权 | +--------+ 1 row in set (0.00 sec)

WHERE 条件中使用表达式

总分在 200 分以下的同学：

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

这里我们发现一个问题，where条件查询中不能使用指定别名，这是因为chinese+math+english这个字句比where total<200字句先执行，所以MySQL并不认识total这个别名，就会报错。

正确写法：

mysql> select name, chinese+math+english total from exam_result where chinese+math+english<200; +-----------+-------+ | name | total | +-----------+-------+ | 刘玄德 | 185 | | 宋公明 | 170 | +-----------+-------+ 2 rows in set (0.00 sec)

AND 与 NOT 的使用

语文成绩 > 80 并且不姓孙的同学：

mysql> select name,chinese from exam_result where chinese>80 and name not like '孙%'; +-----------+---------+ | name | chinese | +-----------+---------+ | 猪悟能 | 88 | | 曹孟德 | 82 | +-----------+---------+ 2 rows in set (0.00 sec)

综合性查询

查询孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80：

mysql> select name,chinese,math,english,chinese+math+english total from exam_result -> where (name like '孙_') or (chinese+math+english>200 and chinese<math and english>80); +-----------+---------+------+---------+-------+ | name | chinese | math | english | total | +-----------+---------+------+---------+-------+ | 猪悟能 | 88 | 98 | 90 | 276 | | 孙权 | 70 | 73 | 78 | 221 | +-----------+---------+------+---------+-------+ 2 rows in set (0.00 sec)

NULL的查询

查询 email 号已知的同学姓名：

mysql> select name from students where email is not null; +--------+ | name | +--------+ | Durant | +--------+ 1 row in set (0.00 sec)

NULL 和 NULL 的比较，= 和 <=> 的区别：

结果排序

基本语法：

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

查询姓名及数学成绩，按数学成绩升序显示：

mysql> select name,math from exam_result order by math; +-----------+------+ | name | math | +-----------+------+ | 宋公明 | 65 | | 孙权 | 73 | | 孙悟空 | 78 | | 曹孟德 | 84 | | 刘玄德 | 85 | | 唐三藏 | 98 | | 猪悟能 | 98 | +-----------+------+ 7 rows in set (0.00 sec)

降序排序

查询姓名及 eamil，按 eamil排序显示：

NULL 视为比任何值都小，升序出现在最上面

多字段排序

查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示：

mysql> select name,chinese,math,english from exam_result order by math desc, english asc, chinese asc; +-----------+---------+------+---------+ | name | chinese | math | english | +-----------+---------+------+---------+ | 唐三藏 | 67 | 98 | 56 | | 猪悟能 | 88 | 98 | 90 | | 刘玄德 | 55 | 85 | 45 | | 曹孟德 | 82 | 84 | 67 | | 孙悟空 | 87 | 78 | 77 | | 孙权 | 70 | 73 | 78 | | 宋公明 | 75 | 65 | 30 | +-----------+---------+------+---------+ 7 rows in set (0.00 sec)

多字段排序，排序优先级随书写顺序

ORDER BY 使用表达式

查询同学及总分，由高到低：

mysql> select name, chinese+math+english from exam_result order by chinese+math+english desc; +-----------+----------------------+ | name | chinese+math+english | +-----------+----------------------+ | 猪悟能 | 276 | | 孙悟空 | 242 | | 曹孟德 | 233 | | 唐三藏 | 221 | | 孙权 | 221 | | 刘玄德 | 185 | | 宋公明 | 170 | +-----------+----------------------+ 7 rows in set (0.00 sec)

ORDER BY 子句中可以使用列别名：

mysql> select name, chinese+math+english total from exam_result order by total desc; +-----------+-------+ | name | total | +-----------+-------+ | 猪悟能 | 276 | | 孙悟空 | 242 | | 曹孟德 | 233 | | 唐三藏 | 221 | | 孙权 | 221 | | 刘玄德 | 185 | | 宋公明 | 170 | +-----------+-------+ 7 rows in set (0.00 sec)

结合 WHERE 子句和 ORDER BY 子句

查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示：

mysql> select name,math from exam_result where name like '孙%' or name like '曹%' order by math desc; +-----------+------+ | name | math | +-----------+------+ | 曹孟德 | 84 | | 孙悟空 | 78 | | 孙权 | 73 | +-----------+------+ 3 rows in set (0.00 sec)

筛选分页结果

基础语法：

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

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死按 id 进行分页，每页 3 条记录，分别显示第 1、2、3 页。

案例：第 1 页：

mysql> select id, name, chinese, math, english from exam_result order by id limit 3 offset 0; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 1 | 唐三藏 | 67 | 98 | 56 | | 2 | 孙悟空 | 87 | 78 | 77 | | 3 | 猪悟能 | 88 | 98 | 90 | +----+-----------+---------+------+---------+ 3 rows in set (0.00 sec)

第 2 页：

mysql> select id, name, chinese, math, english from exam_result order by id limit 3 offset 3; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 4 | 曹孟德 | 82 | 84 | 67 | | 5 | 刘玄德 | 55 | 85 | 45 | | 6 | 孙权 | 70 | 73 | 78 | +----+-----------+---------+------+---------+ 3 rows in set (0.00 sec)

第 3 页，如果结果不足 3 个，不会有影响：

mysql> select id, name, chinese, math, english from exam_result order by id limit 3 offset 6; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 7 | 宋公明 | 75 | 65 | 30 | +----+-----------+---------+------+---------+ 1 row in set (0.00 sec)

Update（更新）

基本语法：

UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...] 更新单列

将孙悟空同学的数学成绩变更为 80 分：

mysql> select name, math from exam_result where name='孙悟空'; +-----------+------+ | name | math | +-----------+------+ | 孙悟空 | 78 | +-----------+------+ 1 row in set (0.00 sec) mysql> update exam_result set math=80 where name='孙悟空'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select name, math from exam_result where name='孙悟空'; +-----------+------+ | name | math | +-----------+------+ | 孙悟空 | 80 | +-----------+------+ 1 row in set (0.00 sec)

更新多列

将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分：

mysql> select name, math, chinese from exam_result where name='曹孟德'; +-----------+------+---------+ | name | math | chinese | +-----------+------+---------+ | 曹孟德 | 84 | 82 | +-----------+------+---------+ 1 row in set (0.00 sec) mysql> update exam_result set math=60, chinese=70 where name='曹孟德'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select name, math, chinese from exam_result where name='曹孟德'; +-----------+------+---------+ | name | math | chinese | +-----------+------+---------+ | 曹孟德 | 60 | 70 | +-----------+------+---------+ 1 row in set (0.00 sec)

更新值为原值基础上变更

将总成绩倒数前三的 3 位同学的数学成绩加上 30 分：

mysql> update exam_result set math=math+30 order by chinese+math+english limit 3; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select name, math from exam_result; +-----------+------+ | name | math | +-----------+------+ | 唐三藏 | 98 | | 孙悟空 | 80 | | 猪悟能 | 98 | | 曹孟德 | 90 | | 刘玄德 | 115 | | 孙权 | 73 | | 宋公明 | 95 | +-----------+------+ 7 rows in set (0.00 sec)

更新全表

将所有同学的语文成绩更新为原来的 2 倍：

mysql> select name, chinese from exam_result; +-----------+---------+ | name | chinese | +-----------+---------+ | 唐三藏 | 67 | | 孙悟空 | 87 | | 猪悟能 | 88 | | 曹孟德 | 70 | | 刘玄德 | 55 | | 孙权 | 70 | | 宋公明 | 75 | +-----------+---------+ 7 rows in set (0.00 sec) mysql> update exam_result set chinese=chinese*2; Query OK, 7 rows affected (0.00 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> select name, chinese from exam_result; +-----------+---------+ | name | chinese | +-----------+---------+ | 唐三藏 | 134 | | 孙悟空 | 174 | | 猪悟能 | 176 | | 曹孟德 | 140 | | 刘玄德 | 110 | | 孙权 | 140 | | 宋公明 | 150 | +-----------+---------+ 7 rows in set (0.00 sec)

Delete（删除）

基础语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...] 删除单条记录

删除孙悟空同学的考试成绩：

mysql> select * from exam_result where name='孙悟空'; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 2 | 孙悟空 | 174 | 80 | 77 | +----+-----------+---------+------+---------+ 1 row in set (0.00 sec) mysql> delete from exam_result where name='孙悟空'; Query OK, 1 row affected (0.00 sec) mysql> select * from exam_result where name='孙悟空'; Empty set (0.00 sec)

删除整表

注意：删除整表操作要慎用！

准备测试表：

mysql> create table for_delete ( -> id int unsigned primary key auto_increment, -> name varchar(20) -> )engine=innodb default charset=utf8; Query OK, 0 rows affected (0.02 sec)

插入测试数据：

mysql> insert into for_delete (name) values ('a'), ('b'), ('c'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from for_delete; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | +----+------+ 3 rows in set (0.00 sec)

删除整表数据：

mysql> delete from for_delete; Query OK, 3 rows affected (0.00 sec) mysql> select * from for_delete; Empty set (0.00 sec)

再插入一条数据，自增 id 在原值上增长：

mysql> insert into for_delete (name) values ('d'); Query OK, 1 row affected (0.00 sec) mysql> select * from for_delete; +----+------+ | id | name | +----+------+ | 4 | d | +----+------+ 1 row in set (0.00 sec)

查看表结构，会有 AUTO_INCREMENT=n 项：

mysql> show create table for_delete \G *************************** 1. row *************************** Table: for_delete Create Table: CREATE TABLE `for_delete` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

说明：虽然delete语句删除了整表，但是再向删除后的表插入时，表中的自增值会在之前的原数据的基础之上增加。

截断表

基础语法：

TRUNCATE [TABLE] table_name TRUNCATE 只能对整表操作，不能像 DELETE 一样针对部分数据操作；实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚会重置 AUTO_INCREMENT 项

准备测试表：

mysql> create table for_truncate ( -> id int unsigned primary key auto_increment, -> name varchar(20) -> )engine=innodb default charset=utf8; Query OK, 0 rows affected (0.02 sec)

插入测试数据：

mysql> insert into for_truncate (name) values ('a'), ('b'), ('c'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from for_truncate; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | +----+------+ 3 rows in set (0.00 sec)

截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作：

mysql> truncate for_truncate; Query OK, 0 rows affected (0.02 sec) mysql> select * from for_truncate; Empty set (0.00 sec)

再插入一条数据，自增 id 在重新增长：

mysql> insert into for_truncate (name) values ('d'); Query OK, 1 row affected (0.00 sec) mysql> select * from for_truncate; +----+------+ | id | name | +----+------+ | 1 | d | +----+------+ 1 row in set (0.00 sec)

查看表结构，会有 AUTO_INCREMENT=2 项：

mysql> show create table for_truncate \G *************************** 1. row *************************** Table: for_truncate Create Table: CREATE TABLE `for_truncate` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

插入查询结果

基础语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

案例：删除表中的的重复记录，重复的数据只能有一份

创建原数据表，插入测试数据：

mysql> create table duplicate_table ( -> id int, -> name varchar(20) -> ); Query OK, 0 rows affected (0.04 sec) mysql> insert into duplicate_table values -> (100, 'aaa'), -> (100, 'aaa'), -> (200, 'bbb'), -> (200, 'bbb'), -> (200, 'bbb'), -> (300, 'ccc'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0

创建一张空表 no_duplicate_table结构和 duplicate_table结构一样：

将 duplicate_table 的去重数据插入到 no_duplicate_table：

mysql> insert into no_duplicate_table select distinct * from duplicate_table; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from no_duplicate_table; +------+------+ | id | name | +------+------+ | 100 | aaa | | 200 | bbb | | 300 | ccc | +------+------+ 3 rows in set (0.00 sec)

通过重命名表，实现原子的去重操作：

mysql> alter table duplicate_table rename to duplicate_table_bak; Query OK, 0 rows affected (0.00 sec) mysql> alter table no_duplicate_table rename to duplicate_table; Query OK, 0 rows affected (0.01 sec)

聚合函数

案例：

统计班级共有多少同学：

mysql> select count(*) from students; +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec)

统计班级收集的 email 有多少：

mysql> select count(email) from students; +--------------+ | count(email) | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)

统计本次考试的数学成绩分数个数：

统计全部成绩：

mysql> select count(math) from exam_result; +-------------+ | count(math) | +-------------+ | 6 | +-------------+ 1 row in set (0.00 sec)

统计去重成绩数量：

mysql> select count(distinct math) from exam_result; +----------------------+ | count(distinct math) | +----------------------+ | 5 | +----------------------+ 1 row in set (0.00 sec)

统计数学成绩总分：

mysql> select sum(math) from exam_result; +-----------+ | sum(math) | +-----------+ | 569 | +-----------+ 1 row in set (0.00 sec)

统计平均总分：

mysql> select avg(chinese+math+english) from exam_result; +---------------------------+ | avg(chinese+math+english) | +---------------------------+ | 297.5 | +---------------------------+ 1 row in set (0.00 sec)

返回英语最高分：

mysql> select max(english) from exam_result; +--------------+ | max(english) | +--------------+ | 90 | +--------------+ 1 row in set (0.00 sec)

返回 > 70 分以上的数学最低分：

mysql> select min(math) from exam_result where math>70; +-----------+ | min(math) | +-----------+ | 73 | +-----------+ 1 row in set (0.00 sec)

group by子句的使用

在select中使用group by 子句可以对指定列进行分组查询

基本语法：

select column1, column2, .. from table group by column;

案例：

准备工作，创建一个雇员信息表

显示每个部门的平均工资和最高工资：

mysql> select deptno, avg(sal) avg, max(sal) max from emp group by deptno; +--------+-------------+---------+ | deptno | avg | max | +--------+-------------+---------+ | 10 | 2916.666667 | 5000.00 | | 20 | 2175.000000 | 3000.00 | | 30 | 1566.666667 | 2850.00 | +--------+-------------+---------+ 3 rows in set (0.00 sec)

每个部门的每种岗位的平均工资和最低工资：

mysql> select deptno, job, avg(sal) avg, min(sal) min from emp group by deptno, job; +--------+-----------+-------------+---------+ | deptno | job | avg | min | +--------+-----------+-------------+---------+ | 10 | CLERK | 1300.000000 | 1300.00 | | 10 | MANAGER | 2450.000000 | 2450.00 | | 10 | PRESIDENT | 5000.000000 | 5000.00 | | 20 | ANALYST | 3000.000000 | 3000.00 | | 20 | CLERK | 950.000000 | 800.00 | | 20 | MANAGER | 2975.000000 | 2975.00 | | 30 | CLERK | 950.000000 | 950.00 | | 30 | MANAGER | 2850.000000 | 2850.00 | | 30 | SALESMAN | 1400.000000 | 1250.00 | +--------+-----------+-------------+---------+ 9 rows in set (0.00 sec)

平均工资低于2000的部门和它的平均工资：

mysql> select deptno, avg(sal) avg from emp group by deptno having avg < 2000; +--------+-------------+ | deptno | avg | +--------+-------------+ | 30 | 1566.666667 | +--------+-------------+ 1 row in set (0.00 sec)

having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where，但是having通常在数据where选择完，group by进行分组，再执行having筛选。

标签: [db:标签TAG]

返回列表

上一篇：学习 Python 之 Pygame 开发魂斗罗（十）

下一篇：第十四届蓝桥杯第三期模拟赛 C/C++ B组原题与详解

MySQL基本查询

相关文章

行人检测(人体检测)3：Android实现人体检测(含源码，可实时人体检测)

MySQL基本运维命令

「ChatGPT」一夜之间“火爆出圈“【杞人忧天 or 未雨绸缪】

FPGA图像处理HLS实现三种图像缩放算法，线性插值、双线性插值、双三次插值，提供HLS工程和vivado工程源码

vue+echarts.js 实现中国地图——根据数值表示省份的深浅——技能提升

入职字节外包一个月，我离职了

Copyright Your WebSite.Some Rights Reserved.

Powered By Z-BlogPHP. Theme by TOYEAN.

MySQL基本查询

相关文章

行人检测(人体检测)3：Android实现人体检测(含源码，可实时人体检测)

MySQL基本运维命令

「ChatGPT」一夜之间“火爆出圈“【杞人忧天 or 未雨绸缪】

FPGA图像处理HLS实现三种图像缩放算法，线性插值、双线性插值、双三次插值，提供HLS工程和vivado工程源码

vue+echarts.js 实现中国地图——根据数值表示省份的深浅——技能提升

入职字节外包一个月，我离职了

Copyright Your WebSite.Some Rights Reserved.var _hmt = _hmt || [];(function() { var hm = document.createElement("script"); hm.src = "https://hm.baidu.com/hm.js?30b336128641baa43b1404dd15891277"; var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(hm, s);})();

Powered By Z-BlogPHP. Theme by TOYEAN.

Copyright Your WebSite.Some Rights Reserved.