超详细的MySQL CRUD 并配备了大量的测试用例， 包教包会

于 2024-07-31 21:51:37 发布
阅读量231 收藏 14
点赞数 15
分类专栏： MySQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xiyun_king/article/details/140833345
版权

MySQL数据库表的增删查改

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

Create

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

  • insert语句主要有两种情况，一种是全行插入，就是在table_name后面不跟插入列的名称，直接在全行进行插入，还有就是指定列进行插入操作
-- 创建一张学生表
CREATE TABLE students (
   id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   sn INT NOT NULL UNIQUE COMMENT '学号',
   name VARCHAR(20) NOT NULL,
   qq VARCHAR(20)
);

单行数据+全列插入

-- 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致
-- 注意，这里在插入的时候，也可以不用指定id(当然，那时候就需要明确插入数据到那些列了)，那么mysql会使用默认的值进行自增。
INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);
Query OK, 1 row affected (0.02 sec)
)
-- 查看插入结果

SELECT * FROM students;
+-----+-------+-----------+-------+
| id | sn   | name     | qq   |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏     | NULL |
| 101 | 10001 | 孙悟空     | 11111 |
+-----+-------+-----------+-------+
2 rows in set (0.00 sec)
  • 这里student后面不跟列的名称就是一种全列插入，在values后要根据顺序把每一列的值填写进去

多行插入 + 指定列插入

-- 插入两条记录，value_list 数量必须和指定列数量及顺序一致
INSERT INTO students (id, sn, name) VALUES 
 (102, 20001, '曹孟德'), 
 (103, 20002, '孙仲谋');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0  Warnings: 0

-- 查看插入结果
SELECT * FROM students;
+-----+-------+-----------+-------+
| id | sn   | name     | qq   |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏     | NULL |
| 101 | 10001 | 孙悟空     | 11111 |
| 102 | 20001 | 曹孟德     | NULL |
| 103 | 20002 | 孙仲谋     | NULL |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)
  • 这里table_name后面指定了哪些列的内容就插入到哪些列当中，而且可以在插入内容后加上 , 继续进行插入操作 -> 多行插入

插入否则更新

  • 这主要使用于当插入内容与主键或者unique冲突时更新内容使用
INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师')
 ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';
 Query OK, 2 rows affected (0.47 sec)

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

替换

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

REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
Query OK, 2 rows affected (0.00 sec)

-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入

​ 在MySQL中，REPLACE 语句是一种用于插入或更新数据的语句。它类似于 INSERT 语句，但有一个关键的区别：如果目标表中已经存在一条具有相同唯一键或主键值的记录，**REPLACE 会首先删除旧的记录，然后插入新的记录。**这种操作方式有时也被称为“插入或替换”。

REPLACE 语句的基本语法如下：

REPLACE INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

或者使用另一种形式：

REPLACE INTO table_name
SET column1 = value1, column2 = value2, ...;

工作原理

  1. 查找冲突：MySQL会检查目标表中的数据，查看是否存在与新数据具有相同唯一键或主键的记录。
  2. 删除旧记录：如果找到匹配的记录，MySQL会删除这条记录。此删除操作会触发任何相关的删除触发器。
  3. 插入新记录：然后，MySQL插入新的记录。这个插入操作会触发任何相关的插入触发器。

注意事项

  1. 删除和插入的开销：由于 REPLACE 先删除旧记录再插入新记录，这个过程可能比简单的更新操作要慢，尤其是在有外键约束或触发器时。此外，这个过程还可能导致自动增长列的值跳跃。
  2. 唯一性约束：REPLACE 语句只能应用于有唯一键（如主键或唯一索引）的表中。如果没有唯一键，REPLACE 将始终插入新记录。
  3. 触发器：如上所述，REPLACE 操作会触发相关的触发器，所以在设计表和数据库逻辑时需要小心处理这些触发器的影响。

使用场景

REPLACE 语句常用于那些需要确保数据唯一性的场景，比如：

  • 同步数据：例如从其他数据源同步数据时，如果记录存在则更新，不存在则插入。
  • 数据去重：在某些情况下，可能需要根据唯一键去重，这时可以使用 REPLACE 代替复杂的检查和更新逻辑。

Retrive

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

全列查询

#通常情况下不建议使用 * 进行全列查询
#1. 查询的列越多，意味这要传输的数据量越大
#2. 可能影响到索引的使用 后面会出博客讲解
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, chinese from exam_result;
+----+-----------+---------+
| id | name      | chinese |
+----+-----------+---------+
|  1 | 唐三藏    |      67 |
|  2 | 孙悟空    |      87 |
|  3 | 猪悟能    |      88 |
|  4 | 曹孟德    |      82 |
|  5 | 刘玄德    |      55 |
|  6 | 孙权      |      70 |
|  7 | 宋公明    |      75 |
+----+-----------+---------+
7 rows in set (0.00 sec)

查询字段为表达式

  • 可以直接将Chinese, English，Math进行相加并且重命名
mysql> SELECT id, name, chinese + math + 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.01 sec)

​ 这里可以使用 as total 也可以直接使用total进行重命名

where 条件

  • MySQL的where语句就像是c/c++的if一样，有些东西相对简单就不做过多的说明，我只挑几个我认为新手比较陌生的讲解

比较运算符

运算符说明
>, >=, <, <=大于，大于等于，小于，小于等于
=等于，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)
between
语文成绩在 [80, 90] 分的同学及语文成绩\

-- 使用 AND 进行条件连接 
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 ... 条件  
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90; 
+-----------+-------+ 
| name | chinese | 
+-----------+-------+ 
| 孙悟空 | 87 | 
| 猪悟能 | 88 | 
| 曹孟德 | 82 | 
+-----------+-------+ 
3 rows in set (0.00 sec)
In
数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

-- 使用 OR 进行条件连接 
 
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.01 sec) 
-- 使用 IN 条件 
 
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 个）任意字符 
 
SELECT name FROM exam_result WHERE name LIKE '孙%'; 
+-----------+ 
| name | 
+-----------+ 
| 孙悟空 | 
| 孙权 | 
+-----------+ 
2 rows in set (0.00 sec) 
-- _ 匹配严格的一个任意字符 
 
SELECT name FROM exam_result WHERE name LIKE '孙_'; 
+--------+ 
| name | 
+--------+ 
| 孙权 | 
+--------+ 
1 row in set (0.00 sec)
where不能使用别名
6.2.2.6 总分在 200 分以下的同学 
-- WHERE 条件中使用表达式 
-- 别名不能用在 WHERE 条件中 
 
SELECT name, chinese + math + english 总分 FROM exam_result 
 WHERE chinese + math + english < 200; 
+-----------+--------+ 
| name | 总分 | 
+-----------+--------+ 
| 刘玄德 | 185 | 
| 宋公明 | 170 | 
+-----------+--------+ 
2 rows in set (0.00 sec)

​ 这里有一个注意到点就是哪一个先运行，这里是先选出总分<20的，然后在进行显示，所以显示的是后运行的，所以不能使用别名

结果排序

语法：

-- ASC 为升序（从小到大） 
-- DESC 为降序（从大到小） 
-- 默认为 ASC 
 
SELECT ... FROM table_name [WHERE ...] 
 ORDER BY column [ASC|DESC], [...];
多级排序
查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示 

-- 多字段排序，排序优先级随书写顺序 
 
SELECT name, math, english, chinese FROM exam_result 
 ORDER BY math DESC, english, chinese; 
+-----------+--------+--------+-------+ 
| name | math | english | chinese | 
+-----------+--------+--------+-------+ 
| 唐三藏 | 98 | 56 | 67 | 
| 猪悟能 | 98 | 90 | 88 | 
| 刘玄德 | 85 | 45 | 55 | 
| 曹孟德 | 84 | 67 | 82 | 
| 孙悟空 | 78 | 77 | 87 | 
| 孙权 | 73 | 78 | 70 | 
| 宋公明 | 65 | 30 | 75 | 
+-----------+--------+--------+-------+ 
7 rows in set (0.00 sec)
  • 这里是使用不同的优先级，就是先按照math降序排列，如果math相同就按照English升序排列（如果不写desc还是asc默认是升序），如果这两个都相同就按照Chinese排序
使用别名
SELECT name, chinese + english + math total FROM exam_result 
 ORDER BY 总分 DESC; 
+-----------+--------+ 
| name | total | 
+-----------+--------+ 
| 猪悟能 | 276 | 
| 孙悟空 | 242 | 
| 曹孟德 | 233 | 
| 唐三藏 | 221 | 
| 孙权 | 221 | 
| 刘玄德 | 185 | 
| 宋公明 | 170 | 
+-----------+--------+ 
7 rows in set (0.00 sec)

​ 这里排序是可以使用别名的，因为排序是先选择在进行排序

Update

语法：

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

​ 这里的update一般与where和limit等一起使用，因为如果不使用这些作为限制的话，就可能会导致整行元素直接消失,所以update要小心使用，以防造成数据的大量丢失

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

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

-- 一次更新多个列 
 
-- 查看原数据

SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德';
+-----------+--------+-------+
| name | math | chinese | 
+-----------+--------+-------+ 
| 曹孟德 | 84 | 82 | 
+-----------+--------+-------+ 
1 row in set (0.00 sec) 
 
-- 数据更新 
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德'; 
Query OK, 1 row affected (0.14 sec) 
Rows matched: 1 Changed: 1 Warnings: 0 
 
-- 查看更新后数据 
SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德'; 
+-----------+--------+-------+ 
| name | math | chinese | 
+-----------+--------+-------+ 
| 曹孟德 | 60 | 70 | 
+-----------+--------+-------+ 
1 row in set (0.00 sec)
使用order by
-- 更新值为原值基础上变更 
 
-- 查看原数据 
-- 别名可以在ORDER BY中使用 
SELECT name, math, chinese + math + english 总分 FROM exam_result 
 ORDER BY 总分 LIMIT 3; 
+-----------+--------+--------+ 
| name | math | 总分 | 
+-----------+--------+--------+ 
| 宋公明 | 65 | 170 | 
| 刘玄德 | 85 | 185 | 
| 曹孟德 | 60 | 197 | +-----------+--------+--------+ 
3 rows in set (0.00 sec) 
 
-- 数据更新，不支持 math += 30 这种语法 
UPDATE exam_result SET math = math + 30 
 ORDER BY chinese + math + english LIMIT 3; 
 
-- 查看更新后数据 
-- 思考：这里还可以按总分升序排序取前 3 个么？ 
SELECT name, math, chinese + math + english 总分 FROM exam_result 
 WHERE name IN ('宋公明', '刘玄德', '曹孟德'); 
+-----------+--------+--------+ 
| name | math | 总分 | 
+-----------+--------+--------+ 
| 曹孟德 | 90 | 227 | 
| 刘玄德 | 115 | 215 | 
| 宋公明 | 95 | 200 | 
+-----------+--------+--------+ 
3 rows in set (0.00 sec) 
 
-- 按总成绩排序后查询结果 
SELECT name, math, chinese + math + english 总分 FROM exam_result 
 ORDER BY 总分 LIMIT 3; 
+-----------+--------+--------+ 
| name | math | 总分 | 
+-----------+--------+--------+ 
| 宋公明 | 95 | 200 | 
| 刘玄德 | 115 | 215 | 
| 唐三藏 | 98 | 221 | 
+-----------+--------+--------+ 
3 rows in set (0.00 sec)

​ 从这里我们可以看出，update不仅可以使用where进行挑选，而且可以使用order by + limit,就是先选出我们要进行更改的行，然后再进行update

Delete

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...] 

#删除孙悟空的成绩
mysql> select * from exam_result where name = '孙悟空' ;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  2 | 孙悟空    |     174 |  100 |      77 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)

mysql> delete from exam_result where name = '孙悟空';\
Query OK, 1 row affected (0.01 sec)

mysql> select * from exam_result where name = '孙悟空' ;
Empty set (0.00 sec)

删除整张表

mysql> CREATE TABLE for_delete ( 
    ->  id INT PRIMARY KEY AUTO_INCREMENT, 
    ->  name VARCHAR(20) 
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C'); 
Query OK, 3 rows affected (0.01 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.01 sec)

mysql> delete from for_delete;
Query OK, 3 rows affected (0.01 sec)

mysql> select * from for_delete;
Empty set (0.01 sec)

#在这里可以看到auto_increment=4,证明这样删除并不会将auto_increment置空
mysql> show create table for_delete \G;
*************************** 1. row ***************************
       Table: for_delete
Create Table: CREATE TABLE `for_delete` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

#所以下次再进行插入时id会从4开始
mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | A    |
|  5 | B    |
|  6 | C    |
+----+------+
3 rows in set (0.00 sec)

​ delete 并不会清空auto_increment 下面我们说的截断表 truncate则会将auto_increment置空

截断表

语法：

TRUNCATE [TABLE] table_name 

注意：这个操作慎用

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

-- 准备测试表 
CREATE TABLE for_truncate ( 
 id INT PRIMARY KEY AUTO_INCREMENT, 
 name VARCHAR(20) 
); 
Query OK, 0 rows affected (0.16 sec) 
 
-- 插入测试数据 
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C'); 
Query OK, 3 rows affected (1.05 sec) 
Records: 3 Duplicates: 0 Warnings: 0 
 
-- 查看测试数据 
SELECT * FROM for_truncate; 
+----+------+ 
| id | name | 
+----+------+ 
| 1 | A | 
| 2 | B | 
| 3 | C | 
+----+------+ 
3 rows in set (0.00 sec) 
-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作 
TRUNCATE for_truncate; 
Query OK, 0 rows affected (0.10 sec) 
 
-- 查看删除结果 
SELECT * FROM for_truncate; 
Empty set (0.00 sec) 
-- 再插入一条数据，自增 id 在重新增长 
INSERT INTO for_truncate (name) VALUES ('D'); 
Query OK, 1 row affected (0.00 sec)  
-- 查看数据 
SELECT * FROM for_truncate; 
+----+------+ 
| id | name | 
+----+------+ 
| 1 | D | 
+----+------+ 
1 row in set (0.00 sec) 
 
-- 查看表结构，会有 AUTO_INCREMENT=2 项 
SHOW CREATE TABLE for_truncate\G 
*************************** 1. row *************************** 
 Table: for_truncate 
Create Table: CREATE TABLE `for_truncate` ( 
 `id` int(11) 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) 

# 再次进行插入id会从0开始

插入查询结果

  • 有时候我们会有这样的需求，就是将一张表的查询结果作为数据插入到另一张表中，这时就要用到插入查询结果了

语法：

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

mysql> CREATE TABLE duplicate_table (id int, name varchar(20)); 
Query OK, 0 rows affected (0.05 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.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

#创建表时使用like可以创建与原来表结构相同的表
mysql> create table no_duplicate_table like duplicate_table;
Query OK, 0 rows affected (0.08 sec)

#进行插入操作
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> rename table duplicate_table to old_duplicate_table, no_duplicate_table to duplicate_table;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

聚合函数

函数说明
COUNT([DISTINCT] expr)返回查询到的数据的 数量
SUM([DISTINCT] expr)返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)返回查询到的数据的 最小值，不是数字没有意义

gruop by语句

语法：

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

给大家一份脚本代码作为练习

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);


DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);


insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

大家可以把他source一下，然后得到一份表，用于后面的练习

  • 表中的数据
mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
  • 选出部门的最高工资和平均工资
mysql> select deptno, avg(sal), max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal)    | max(sal) |
+--------+-------------+----------+
|     20 | 2175.000000 |  3000.00 |
|     30 | 1566.666667 |  2850.00 |
|     10 | 2916.666667 |  5000.00 |
+--------+-------------+----------+
3 rows in set (0.02 sec)

​ 这里是可以显示部门的，因为它是分过组的。

  • 显示每个部门的每个岗位的平均工资和最低工资
mysql> select deptno, job, avg(sal), min(sal) from emp group by deptno, job;
+--------+-----------+-------------+----------+
| deptno | job       | avg(sal)    | min(sal) |
+--------+-----------+-------------+----------+
|     20 | CLERK     |  950.000000 |   800.00 |
|     30 | SALESMAN  | 1400.000000 |  1250.00 |
|     20 | MANAGER   | 2975.000000 |  2975.00 |
|     30 | MANAGER   | 2850.000000 |  2850.00 |
|     10 | MANAGER   | 2450.000000 |  2450.00 |
|     20 | ANALYST   | 3000.000000 |  3000.00 |
|     10 | PRESIDENT | 5000.000000 |  5000.00 |
|     30 | CLERK     |  950.000000 |   950.00 |
|     10 | CLERK     | 1300.000000 |  1300.00 |
+--------+-----------+-------------+----------+
9 rows in set (0.00 sec)

  • 显示平均工资低于两千的部门，和他的平均工资

    • 统计各个部门的平均工资
    select avg(sal) from EMP group by deptno
    • hanving 和 gruop by混合使用，对gruop by进行过滤
    select avg(sal) as myavg from EMP group by deptno having myavg<2000; 
 
--having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。

having 与 where的区别

​ 条件筛选的阶段是不同的，where是对具体的任意列进行条件筛选，having是对分组聚合之后的结果进行条件筛选。

spigcoder
关注
  • 15
    点赞
  • 踩
  • 14
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
laravel生成php代码,使用 Laravel Generator 快速为 Laravel 应用生成CRUD、API、测试用例代码 —— 使用篇...
weixin_28686771的博客
03-21 764
使用 Laravel Generator 快速为 Laravel 应用生成CRUD、API、测试用例代码 —— 使用篇由 学院君 创建于4年前, 最后更新于 11个月前版本号 #223006 views8 likes0 collectsAPI生成器要生成API，我们可以使用如下命令：php artisan infyom:api $MODEL_NAME$其中$MODEL_NAME$需要替换成你的模型...
Web_Service教程和所需要的全部包
04-26
10. **实例演示**：教程可能会包含创建一个简单的Web服务，以及如何使用客户端工具如cURL或Postman进行测试的步骤。 通过这个教程，初学者将能够掌握Web服务的基本概念，并具备实现和使用Web服务的能力。记住，实践...
mysql编写单元测试用例_SpringSide 3 中的数据库访问层和单元测试用例
weixin_35513543的博客
02-08 160
在SpringSide 3 中，数据库访问层使用的是Hibernate，Hibernate是一个很优秀的ORM框架，是大家耳熟能详的东西了。关于Hibernate的内容，很多人是写了又写，我想我是很难写出新意了。不过我的思路是这样的，我从实际开发的过程出发，写出在SpringSide 3中使用Hibernate的步骤，在这些步骤中，探讨SpringSide 3对Hibernate的封装，探讨数据持...
MyBatis详细教程，一文带你学习MyBatis，MyBatis超详细教程
weixin_46009389的博客
03-10 705
MyBatis详细教程，一文带你学习MyBatis，MyBatis超详细教程
GO从入门到进阶教程系列 - 研发高性能ORM框架操作mysql篇
shadowsick的专栏
03-17 510
上一篇教程我们了解到了基础的GO语法,今天我们来学习如何使用GO操作mysql,下面就直接进入步骤操作环节 技术版权归属 广州市金狮网络科技有限公司 (https://kingc.cn/) ,如需商用请联系公司 1. 先获取mysql驱动,类似Java加载驱动jar, 通过window cmd命令或者Linux控制台执行 go get github.com/go-sql-driv......
spring-test-examples:这是包含我在Spring教程中测试的示例的存储库
03-14
例如，对于数据访问层，可能会有对Repository的查询和CRUD操作的测试；对于Web层，可能会涉及Controller的HTTP请求处理测试。 总的来说，`spring-test-examples`项目涵盖了Spring框架的多个测试方面，通过学习这些...
Windows下Java+MyBatis框架+MySQL的开发环境搭建教程
09-02
14. **测试**：编写JUnit测试用例，验证你的数据访问层是否能正确执行SQL语句并返回预期结果。 通过以上步骤，你将在Windows环境下成功搭建起一个基于Java、MyBatis和MySQL的开发环境。这只是一个基础配置，实际...
s2sh整合最新jar包以及流程帮助图
05-09
本教程主要针对"S2SH整合最新jar包以及流程帮助图"进行详细介绍。 首先，让我们关注标题中的“jar包整合”。在Java开发中，jar（Java Archive）文件是Java类库的打包形式，包含了编译后的Java类文件和其他资源。S2...
SSM框架-详细整合教程.docx
11-13
2. **添加依赖**：在`pom.xml`文件中引入SSM框架及相关的JAR包，如Spring、SpringMVC、MyBatis以及它们的依赖库，如MySQL驱动、Log4j日志框架等。 3. **配置文件**：SSM整合通常需要多个配置文件，如`spring-...
CRUD-SpringCompleto
02-23
9. **单元测试和集成测试**: 项目可能包含`src/test`目录下的JUnit测试用例，用于验证各个组件的功能。 10. **模板引擎**: 可能会使用Thymeleaf、FreeMarker或Velocity等模板引擎来生成动态HTML页面。 11. **...
springboot_crud_311
02-14
8. **测试(Test)**：编写JUnit测试用例，验证CRUD操作是否正常工作。 这个教程可能还涵盖了错误处理、RESTful API设计、JSON序列化、事务管理、日志记录等方面的知识。学习者通过这个项目，能深入了解Spring Boot...
crud_rest_api
02-11
通过"crud_rest_api-master"这个项目，我们可以学习如何从零开始构建一个完整的CRUD REST API，包括设置Express服务器、定义路由、连接数据库、创建模型、实现CRUD操作、处理请求和响应，以及编写测试用例。...
AspNetMVC：小asp.net MVC CRUD任务
02-17
这个“小asp.net MVC CRUD任务”是一个基础教程，旨在教你如何使用C#语言和ASP.NET MVC框架实现对数据库中的员工实体进行创建(Create)、读取(Retrieve)、更新(Update)和删除(Delete)操作，即CRUD操作。 在ASP.NET ...
[简单]Spring_Mybatis_CRUD简单示例(带数据库)
08-06
6. **测试代码**：可能包含JUnit测试用例，用于验证CRUD操作是否正常工作。 在压缩包的`lib2`目录下，很可能是存放了项目所需的库文件，比如Spring和MyBatis的jar包，以及其他依赖的第三方库。这些库是运行和构建...
sparkJavaTest:该项目用于创建CRUD SparkJava项目进行实践
05-09
使用JUnit或TestNG等单元测试框架，我们可以编写测试用例来验证CRUD操作的正确性。这有助于保证代码质量，减少bug的出现。 总结起来，SparkJavaTest项目为我们提供了一个学习和实践SparkJava框架以及CRUD操作的平台...
Spring-Boot-MySQL-Prueba：Ejemplo de Prueba Spring Boot MySQL
02-21
标题 "Spring-Boot-MySQL-Prueba：Ejemplo de Prueba Spring Boot MySQL" 指向的是一个基于Spring Boot框架与MySQL数据库结合的应用测试示例。Spring Boot是Java领域中广泛使用的微服务开发框架，它简化了初始化、...
nhibernate2.0 + mysql5.0粒子
02-23
7. **测试用例**：为了验证配置和映射的正确性，通常会有单元测试或集成测试用例，通过执行特定的数据库操作并验证结果。 学习这些示例可以帮助开发者理解如何在实际项目中使用NHibernate和MySQL，例如： - 如何...
关于SH JPA 简单的CRUD操作
01-19
SH JPA，全称Spring Hibernate Java Persistence API，是Spring框架中的一个模块，它提供了一种在Java...通过理解并实践这些基础的CRUD操作，开发者可以更深入地掌握JPA和Spring框架的使用，从而在实际开发中提升效率。
mysql CRUD
最新发布
10-06
MySQL CRUD是指在MySQL数据库中进行增加(Create)、读取(Retrieve)、更新(Update)和删除(Delete)数据的操作。下面是一些常用的MySQL CRUD操作的示例： 创建(Create)： - 创建表：CREATE TABLE table_name (column1 datatype, column2 datatype, ...); - 插入数据：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); 读取(Retrieve)： - 查询所有数据：SELECT * FROM table_name; - 查询符合条件的数据：SELECT * FROM table_name WHERE condition; 更新(Update)： - 更新某个字段：UPDATE table_name SET column_name = new_value WHERE condition; 删除(Delete)： - 删除表：DROP TABLE table_name; - 删除符合条件的数据：DELETE FROM table_name WHERE condition; 请注意，上述示例中的"table_name"是指表名，"column_name"是指字段名，"value"是指具体的值，"condition"是指查询或更新的条件。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值