MySql表的基本增删改查详解

之前我们使用了对数据库以及表的基本创建，添加约束，和销毁;

其实面临庞大的数据量，我们对表中数据的增删改查使用频率非常高，MySql也为我们提供了一系列方便操作的功能接口;

创建表create

-- 创建一张学生表（唯一键or主键的各种设置在这里）
CREATE TABLE students (
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	sn INT NOT NULL UNIQUE COMMENT '学号',
	name VARCHAR(20) NOT NULL,
	qq VARCHAR(20)
);

表中–插入数据–“增”

• 基本语法:INSERT INTO（各种属性）VALUES (各种值);

单行，全列插入

可不指定插入的列属性(但是插入的values个数得匹配全)

INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);
Query OK, 1 row affected (0.02 sec)

INSERT INTO students VALUES (101, 10001, '孙悟空', '11111');
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)

多行插入

-- 一次性插入两行数据
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)

插入重复则-更新

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

-- 主键冲突
INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师');

ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
-- 唯一键冲突
INSERT INTO students (sn, name) VALUES (20001, '曹阿瞒');

ERROR 1062 (23000): Duplicate entry '20001' for key 'sn'

可以选择性的进行更新操作：

-- ON DUPLICATE KEY 当发生重复key的时候
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: 表中有冲突数据，并且数据已经被更新(先删除，再更新，2 row影响)


-- 通过 MySQL 函数获取受到影响的数据行数
SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)

插入重复则-替换

-- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入(ON DUPLICAT冲突但是其他属性一样的时候，就不会执行，这个replace比较粗暴，重复了直接delete 再insert)

REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
Query OK, 2 rows affected (0.00 sec)
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据（即使冲突与想插入的一样），删除后重新插入

插入查询结果

语法:

INSERT INTO 表名 (字段) SELECT ...

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

-- 创建原数据表
CREATE TABLE duplicate_table (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)

-- 插入测试数据
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

-- 思路: 创建一张相似的表，我们用SELECT DISTINCT FROM......可以去重，之后再把查询结果插入新表，把这个新表名字改回去就行;

-- 创建空表: (可以用LIKE方便表的创建)
CREATE TABLE no_duplicate_table LIKE duplicate_table;
Query OK, 0 rows affected (0.00 sec)

-- 插入 去重查询的结果！ -- 先执行select 再 insert (字段需一一匹配！)
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;

Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

-- 通过重命名表，实现原子的去重操作
RENAME TABLE duplicate_table TO old_duplicate_table,
no_duplicate_table TO duplicate_table;
Query OK, 0 rows affected (0.00 sec)

-- 查看最终结果 去重成功;
SELECT * FROM duplicate_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)

表中–检索数据–“查”

• 基本语法:SELECT (各种属性) FROM (表名);

创建案例:

-- 创建表结构
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. 可能会影响到索引的使用。（索引待后续讲解）

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)

指定列查询

-- 指定列（指定属性）的顺序不需要按定义表的顺序来

SELECT id, name, english FROM exam_result;

+----+-----------+--------+
| id | name | english |
+----+-----------+--------+
| 1 | 唐三藏 | 56 |
| 2 | 孙悟空 | 77 |
| 3 | 猪悟能 | 90 |
| 4 | 曹孟德 | 67 |
| 5 | 刘玄德 | 45 |
| 6 | 孙权 | 78 |
| 7 | 宋公明 | 30 |
+----+-----------+--------+
7 rows in set (0.00 sec)

查询字段为表达式

-- 表达式不包含字段  --下面查的这个表达式:10并不是表中的字段(属性), 则默认全用10填充了;
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)

-- 表达式包含一个字段 存在字段english，那么表达式english+10就是给他的value+10进行输出;
SELECT id, name, english + 10 FROM exam_result;

+----+-----------+-------------+
| id | name | english + 10 |
+----+-----------+-------------+
| 1 | 唐三藏 | 66 |
| 2 | 孙悟空 | 87 |
| 3 | 猪悟能 | 100 |
| 4 | 曹孟德 | 77 |
| 5 | 刘玄德 | 55 |
| 6 | 孙权 | 88 |
| 7 | 宋公明 | 40 |
+----+-----------+-------------+
7 rows in set (0.00 sec)

-- 表达式包含多个字段（同理一个字段）
SELECT id, name, chinese + math + english FROM exam_result;

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

给查询结果指定别名

SELECT id, name, chinese + math + english as 总分 FROM exam_result; -- as可以省略成 空格;

+----+-----------+--------+
| id | name | 总分 |
+----+-----------+--------+
| 1 | 唐三藏 | 221 |
| 2 | 孙悟空 | 242 |
| 3 | 猪悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 刘玄德 | 185 |
| 6 | 孙权 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+--------+
7 rows in set (0.00 sec) 

查询结果去重

-- 98 分重复了

SELECT math FROM exam_result;

+--------+
| math |
+--------+
| 98 |
| 78 |
| 98 |
| 84 |
| 85 |
| 73 |
| 65 |
+--------+
7 rows in set (0.00 sec)

-- 查询去重 --SELECT后加DISTINCT
SELECT DISTINCT math FROM exam_result;
+--------+
| math |
+--------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+--------+
6 rows in set (0.00 sec)

WHERE 限定条件

比较运算符

1. List item
2. 注意：常规的‘=’不能判断NULL值，是NULL不安全的，我们判断NULL值有专用的IS NULL或者<=>等，常用前者;

逻辑运算符

where使用实例

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

-- 基本条件比较
SELECT name, english FROM exam_result WHERE english < 60;

+-----------+--------+
| name | english |
+-----------+--------+
| 唐三藏 | 56 |
| 刘玄德 | 45 |
| 宋公明 | 30 |
+-----------+--------+
3 rows in set (0.01 sec)

语文成绩在 [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)

数学成绩是 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)

姓孙的同学 及 孙某同学

模糊匹配

• 格式: WHERE 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 条件chinese > english 中比较运算符 两侧都是字段
SELECT name, chinese, english FROM exam_result WHERE chinese > english;

+-----------+-------+--------+
| name | chinese | english |
+-----------+-------+--------+
| 唐三藏 | 67 | 56 |
| 孙悟空 | 87 | 77 |
| 曹孟德 | 82 | 67 |
| 刘玄德 | 55 | 45 |
| 宋公明 | 75 | 30 |
+-----------+-------+--------+
5 rows in set (0.00 sec)

总分在200分以下的同学

-- WHERE 条件中使用表达式,别名 不能用在 WHERE 条件中!

SELECT name, chinese + math + english 总分 FROM exam_result
WHERE chinese + math + english < 200;  -- WHERE 总分<200; 会报错；

+-----------+--------+
| name | 总分 |
+-----------+--------+
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+--------+
2 rows in set (0.00 sec)

• 原因是，我们先用where筛选数据以后，才根据select来查询相应的字段;（否则海量数据，先查再筛就太低效了;）
• 因此where执行的优先级高于select！！！ 所以别名 where 自然也就 识别不到了;

注意！理解SQL语句的执行顺序有很重要的作用！可以清楚底层运行机制;

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

-- WHERE 配合 AND 与 NOT 的使用
SELECT name, chinese FROM exam_result
WHERE chinese > 80 AND name NOT LIKE '孙%';

+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
+----+-----------+-------+--------+--------+
2 rows in set (0.00 sec)

孙某同学，否则 要求总成绩 > 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
); -- 注意 这里不能用别名，因为where先执行;

+-----------+-------+--------+--------+--------+
| name | chinese | math | english | 总分 |
+-----------+-------+--------+--------+--------+
| 猪悟能 | 88 | 98 | 90 | 276 |
| 孙权 | 70 | 73 | 78 | 221 |
+-----------+-------+--------+--------+--------+
2 rows in set (0.00 sec)

NULL 的查询

-- 准备查询的students 表
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10010 | 唐大师 | NULL |
| 101 | 10001 | 孙悟空 | 11111 |
| 103 | 20002 | 孙仲谋 | NULL |
| 104 | 20001 | 曹阿瞒 | NULL |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

-- 查询 qq号已知(NOT NULL) 的同学 的姓名
SELECT name, qq FROM students WHERE qq IS NOT NULL;
+-----------+-------+
| name | qq |
+-----------+-------+
| 孙悟空 | 11111 |
+-----------+-------+
1 row in set (0.00 sec)

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

-- = 无法辨别NULL
SELECT NULL = NULL, NULL = 1, NULL = 0;
+-------------+----------+----------+
| NULL = NULL | NULL = 1 | NULL = 0 |
+-------------+----------+----------+
| NULL | NULL | NULL |
+-------------+----------+----------+
1 row in set (0.00 sec)

--  <=> 可以辨别NULL
SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0;

+---------------+------------+------------+
| NULL <=> NULL | NULL <=> 1 | NULL <=> 0 |
+---------------+------------+------------+
| 1 | 0 | 0 |
+---------------+------------+------------+
1 row in set (0.00 sec)

ORDER BY排序

语法：

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC 升序

SELECT 属性 FROM 表名 ORDER BY 某序列属性 (ASC);

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

同学及数学成绩，按数学成绩升序显示

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)

同学及 qq 号，按 qq 号排序显示

-- NULL 视为比任何值都小，升序出现在最上面
SELECT name, qq FROM students ORDER BY qq;
+-----------+-------+
| name | qq |
+-----------+-------+
| 唐大师 | NULL |
| 孙仲谋 | NULL |
| 曹阿瞒 | NULL |
| 孙悟空 | 11111 |
+-----------+-------+
4 rows in set (0.00 sec)

-- NULL 视为比任何值都小，降序出现在最下面
SELECT name, qq FROM students ORDER BY qq DESC;
+-----------+-------+
| name | qq |
+-----------+-------+
| 孙悟空 | 11111 |
| 唐大师 | NULL |
| 孙仲谋 | NULL |
| 曹阿瞒 | NULL |
+-----------+-------+
4 rows in set (0.00 sec)

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

-- 多字段排序，排序优先级随书写顺序, 逗号隔开，只需要最开始一个ORDER BY 即可;
SELECT name, math, english, chinese FROM exam_result
ORDER BY math DESC, english ASC, chinese ASC;

+-----------+--------+--------+-------+
| 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)

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

-- ORDER BY 中可以使用表达式(一个or多个字段的表达式)
SELECT name, chinese + english + math FROM exam_result
ORDER BY chinese + english + math DESC;

+-----------+-------------------------+
| name | chinese + english + math |
+-----------+-------------------------+
| 猪悟能 | 276 |
| 孙悟空 | 242 |
| 曹孟德 | 233 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+-------------------------+
7 rows in set (0.00 sec)


-- ORDER BY 子句中可以使用列别名;
SELECT name, chinese + english + math 总分 FROM exam_result
ORDER BY 总分 DESC;

+-----------+--------+
| name | 总分 |
+-----------+--------+
| 猪悟能 | 276 |
| 孙悟空 | 242 |
| 曹孟德 | 233 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+--------+
7 rows in set (0.00 sec)

• 因为是先根据where筛选以后，再select查询出结果以后，然后对这些等会需要显示的结果进行order by排序;
• 所以order by的优先级比较低，执行的时候已经有了别名，可以在里面用了;

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

-- 结合 WHERE + 条件子句 和 ORDER BY 子句
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)

筛选分页结果

语法:

SELECT 属性 FROM 表名 ORDER BY 某序列属性 ASC LIMIT X,NUM; -- 从x偏移量开始，挑出NUM个结果; LIMIT NUM OFFSET X; 等价于 LIMIT NUM，X;

• 我们查询数据的时候，万一数据很多，未加限制的查询可能会滚屏几十分钟，而且这种情况查的那么多数据也没意义了;

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

按** id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页：

-- 第 1 页
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id LIMIT 3 OFFSET 0;  -- LIMIT 3 OFFSET 0; 不如 LIMIT 0，3; 看个人习惯;

+----+-----------+--------+--------+-------+
| id | name | math | english | chinese |
+----+-----------+--------+--------+-------+
| 1 | 唐三藏 | 98 | 56 | 67 |
| 2 | 孙悟空 | 78 | 77 | 87 |
| 3 | 猪悟能 | 98 | 90 | 88 |
+----+-----------+--------+--------+-------+
3 rows in set (0.02 sec)

-- 第 2 页
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id LIMIT 3 OFFSET 3;
+----+-----------+--------+--------+-------+
| id | name | math | english | chinese |
+----+-----------+--------+--------+-------+
| 4 | 曹孟德 | 84 | 67 | 82 |
| 5 | 刘玄德 | 85 | 45 | 55 |
| 6 | 孙权 | 73 | 78 | 70 |
+----+-----------+--------+--------+-------+
3 rows in set (0.00 sec)

-- 第 3 页，如果结果不足 3 个，不会有影响
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id LIMIT 3 OFFSET 6;
+----+-----------+--------+--------+-------+
| id | name | math | english | chinese |
+----+-----------+--------+--------+-------+
| 7 | 宋公明 | 65 | 30 | 75 |
+----+-----------+--------+--------+-------+
1 row in set (0.00 sec)

根据之前分析优先级的方式分析一下limit的优先级，不难得出，他的优先级很低，他可以用在order by之后，用来选择显示条数的; 那他自然没有order by优先级高;

聚合函数

实际的业务中，当我们需要某些数据的个数，最大值，最小值，平均值的时候，MySql为我们提供了一些聚合数据的函数，方便我们使用;

上述聚合函数可以进一步搭配where等限定条件进行使用！

**统计班级共有多少同学：**count（）函数

-- 使用 * 做统计; 不受 NULL 影响，直接统计记录的个数;
SELECT COUNT(*) FROM students;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)


-- 使用表达式做统计;
SELECT COUNT(1) FROM students;
+----------+
| COUNT(1) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)

-- 统计不数学成绩不重复的总数 --count + distinct
SELECT COUNT(DISTINCT math) FROM exam_result;

统计数学成绩总分 sum（）函数

SELECT SUM(math) FROM exam_result;
+-------------+
| SUM(math) |
+-------------+
| 569 |
+-------------+
1 row in set (0.00 sec)

统计平均总分AVG（）函数

SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;
+--------------+
| 平均总分 |
+--------------+
| 297.5 |
+--------------+

min和max的使用同上，我们省略;

***group by分组查询

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

语法:

select 字段  from table group by  字段;

案例:

• 准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）
• EMP员工表 DEPT部门表 SALGRADE工资等级表
• 如何显示每个部门的平均工资和最高工资?

select deptno,avg(sal),max(sal) from EMP group by deptno; -- 先按部门分组，再进行相关数据的计算展示;

• 显示 每个部门的 每种岗位 的 平均工资 和 最低工资

select avg(sal),min(sal),job, deptno from EMP group by deptno, job; -- 前面查询的字段job, deptno 和 group by分组依据的字段deptno, job对应了;

• 显示 平均工资 低于2000 的 部门和它的平均工资

-- 统计各个部门的平均工资;
select deptno,avg(sal) from EMP group by deptno; 

-- 引入having 限制group by之后 显示的条件；
select deptno,avg(sal) myavg from EMP group by deptno having myavg<2000; 

having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。

复合查询

很重要！放在我的另一篇博客详解

表中–更新数据–“改”

语法:

UPDATE 表明 SET 字段 = X;

• 可以看到Update还是挺危险得，一般需要配合WHERE…使用; 不然一下全改了，相当于一下全删了某些数据;

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

-- 更新值为具体值80

-- 查看原数据
SELECT name, math FROM exam_result WHERE name = '孙悟空';
+-----------+--------+
| name | math |
+-----------+--------+
| 孙悟空 | 78 |
+-----------+--------+
1 row in set (0.00 sec)

-- 数据更新
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

-- 查看更新后数据
SELECT name, math FROM exam_result WHERE name = '孙悟空';
+-----------+--------+
| name | math |
+-----------+--------+
| 孙悟空 | 80 |
+-----------+--------+
1 row in set (0.00 sec)

将曹孟德同学的数学成绩变更为 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)

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

-- 更新值为原值基础上+30
-- 查看原数据
-- 别名可以在ORDER BY中使用
SELECT name, math, chinese + math + english 总分 FROM exam_result
ORDER BY 总分 LIMIT 3;  -- 限制出最后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; -- 注意这里不能ORDER BY 总分 了; ORDER BY 找不到总分 这个别名;

-- 查看更新后数据

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)

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

注意：更新全表的语句慎用!

-- 没有 WHERE 子句，则更新全表
-- 查看原数据
SELECT * FROM exam_result;

+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 80 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 70 | 90 | 67 |
| 5 | 刘玄德 | 55 | 115 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 95 | 30 |
+----+-----------+-------+--------+--------+
7 rows in set (0.00 sec)

-- 数据更新
UPDATE exam_result SET chinese = chinese * 2;


Query OK, 7 rows affected (0.00 sec)
Rows matched: 7 Changed: 7 Warnings: 0

-- 查看更新后数据
SELECT * FROM exam_result;


+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 2 | 孙悟空 | 174 | 80 | 77 |
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 5 | 刘玄德 | 110 | 115 | 45 |
| 6 | 孙权 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+-------+--------+--------+
7 rows in set (0.00 sec)

表中–删除数据–“删”

语法:

DELETE FROM 表名;

• 注意：删除整表操作要慎用！一般要根据where order by等限定删除记录个数

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

-- 查看原数据
SELECT * FROM exam_result WHERE name = '孙悟空';

+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 2 | 孙悟空 | 174 | 80 | 77 |
+----+-----------+-------+--------+--------+
1 row in set (0.00 sec)

-- 删除数据
DELETE FROM exam_result WHERE name = '孙悟空';
Query OK, 1 row affected (0.17 sec)

-- 查看删除结果
SELECT * FROM exam_result WHERE name = '孙悟空';
Empty set (0.00 sec)

删除整张表数据

删除数据

-- 准备测试表
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');

Query OK, 3 rows affected (1.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

-- 查看测试数据
SELECT * FROM for_delete;

+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
3 rows in set (0.00 sec)

-- 删除整表数据
DELETE FROM for_delete;
Query OK, 3 rows affected (0.00 sec)

-- 查看删除结果
SELECT * FROM for_delete;
Empty set (0.00 sec)

-- 再插入一条数据，自增 id 在原值上增长 所以delete 不会充值自增的值！！！
INSERT INTO for_delete (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)
-- 查看数据
SELECT * FROM for_delete;
+----+------+
| id | name |
+----+------+
| 4 | D |
+----+------+
1 row in set (0.00 sec)
-- 查看表结构，会有 AUTO_INCREMENT=n 自增数据项

SHOW CREATE TABLE for_delete\G
*************************** 1. row ***************************
Table: for_delete
Create Table: CREATE TABLE `for_delete` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

截断表

语法:

TRUNCATE 表名;

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，所以实际上没有对数据真正操作，比delete快！
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)

-- 查看数据  id = 1；所以这个截断会重置自增 AUTO_INCREMENT=0 的值;
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)

注意删除表用drop… delete是删除表中的记录;