取消
MySQL 表的CRUD与复合查询

目录

表的增删改查

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

Create

C++reate == insert

标准语法:

INSERT [INTO] table_name 
    [(column [， column] ...)] 
    VALUES (value_list) [， (value_list)] ... 
    
其中value_list: value， [， value] ...

案例:

-- 创建一张学生表
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 students(id，sn，name，qq) VALUES (100， 10000， '唐三藏'， NULL);
Query OK， 1 row affected (0.02 sec)

INSERT INTO students(id，sn，name，qq) 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)

单行数据+全列插入

全列插入时可以省略列名，表示每个列都插入

value_list 数量必须和定义表的列的数量及顺序一致

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'

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

标准语法：

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

案例

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:      ### 表中有冲突数据，并且数据已经被更新
-- 通过 MySQL 函数获取受到影响的数据行数
SELECT ROW_COUNT(); 
+-------------+
| ROW_COUNT() | 
+-------------+
|           2 | 
+-------------+
1 row in set (0.00 sec)

-- ON DUPLICATE KEY 当发生重复key的时候

替换 (replace)

-- 主键 或者 唯一键 没有冲突，则直接插入； 
-- 主键 或者 唯一键 如果冲突，则删除后再插入
REPLACE INTO students (sn， name) VALUES (20001， '曹阿瞒');
Query OK， 2 rows affected (0.00 sec)

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

Retrieve

retrieve == query

标准语法

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

案例:

-- 创建一张学生表
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT，
    sn INT NOT NULL UNIQUE COMMENT '学号'，
    name VARCHAR(20) NOT NULL， 
    qq VARCHAR(20)
);
0

SELECT列

查询结果返回表格，表现形式为笛卡尔积:详见数据库原理

全列查询
-- 创建一张学生表
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT，
    sn INT NOT NULL UNIQUE COMMENT '学号'，
    name VARCHAR(20) NOT NULL， 
    qq VARCHAR(20)
);
1
限制显示条目 limit (分页查询)

网页中每页 3 条记录: 按 id 进行分页，分别显示 第 1、2、3 页

基本语法:
-- 创建一张学生表
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT，
    sn INT NOT NULL UNIQUE COMMENT '学号'，
    name VARCHAR(20) NOT NULL， 
    qq VARCHAR(20)
);
2

基本案例:

  • 显示前四条数据(默认从0开始)
-- 创建一张学生表
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT，
    sn INT NOT NULL UNIQUE COMMENT '学号'，
    name VARCHAR(20) NOT NULL， 
    qq VARCHAR(20)
);
3
  • 从下标为2开始，显示4条数据
-- 创建一张学生表
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT，
    sn INT NOT NULL UNIQUE COMMENT '学号'，
    name VARCHAR(20) NOT NULL， 
    qq VARCHAR(20)
);
4
  • 以区间方式 从下标为2开始，显示4条数据
-- 创建一张学生表
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT，
    sn INT NOT NULL UNIQUE COMMENT '学号'，
    name VARCHAR(20) NOT NULL， 
    qq VARCHAR(20)
);
5

limit 是左闭右开区间

select * 时，如果未知总数量，最好限制一下回显条目数量，大约在1000条即可

指定列查询
-- 创建一张学生表
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT，
    sn INT NOT NULL UNIQUE COMMENT '学号'，
    name VARCHAR(20) NOT NULL， 
    qq VARCHAR(20)
);
6
select 查询字段为表达式

以下案例能够说明 select能够计算各种表达式

  • 常量表达式
-- 创建一张学生表
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT，
    sn INT NOT NULL UNIQUE COMMENT '学号'，
    name VARCHAR(20) NOT NULL， 
    qq VARCHAR(20)
);
7
  • 常量表达式笛卡尔积
-- 创建一张学生表
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT，
    sn INT NOT NULL UNIQUE COMMENT '学号'，
    name VARCHAR(20) NOT NULL， 
    qq VARCHAR(20)
);
8
  • 基本数值运算
-- 创建一张学生表
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT，
    sn INT NOT NULL UNIQUE COMMENT '学号'，
    name VARCHAR(20) NOT NULL， 
    qq VARCHAR(20)
);
9
  • 能够计算1+1，则也能够计算字段值的运算
INSERT INTO students(id，sn，name，qq) VALUES (100， 10000， '唐三藏'， NULL);
Query OK， 1 row affected (0.02 sec)

INSERT INTO students(id，sn，name，qq) 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)
0
  • 计算总成绩
INSERT INTO students(id，sn，name，qq) VALUES (100， 10000， '唐三藏'， NULL);
Query OK， 1 row affected (0.02 sec)

INSERT INTO students(id，sn，name，qq) 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)
1
表达式重命名

如果计算成绩的综合，会发现表达式math+chinese+english的返回的结果表格中列名很长;可以使用as对表达式进行重命名.

INSERT INTO students(id，sn，name，qq) VALUES (100， 10000， '唐三藏'， NULL);
Query OK， 1 row affected (0.02 sec)

INSERT INTO students(id，sn，name，qq) 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)
2

还可以省略as

INSERT INTO students(id，sn，name，qq) VALUES (100， 10000， '唐三藏'， NULL);
Query OK， 1 row affected (0.02 sec)

INSERT INTO students(id，sn，name，qq) 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)
3
去重
  • 原数据:
INSERT INTO students(id，sn，name，qq) VALUES (100， 10000， '唐三藏'， NULL);
Query OK， 1 row affected (0.02 sec)

INSERT INTO students(id，sn，name，qq) 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)
4
  • 去重数据
INSERT INTO students(id，sn，name，qq) VALUES (100， 10000， '唐三藏'， NULL);
Query OK， 1 row affected (0.02 sec)

INSERT INTO students(id，sn，name，qq) 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)
5

WHERE 条件

相当于 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); 逻辑非，相当于 !
案例:

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

  • 语文成绩在 [80， 90] 分的同学及语文成绩

    INSERT INTO students(id，sn，name，qq) VALUES (100， 10000， '唐三藏'， NULL);
Query OK， 1 row affected (0.02 sec)

INSERT INTO students(id，sn，name，qq) 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)
6
    • between
    INSERT INTO students(id，sn，name，qq) VALUES (100， 10000， '唐三藏'， NULL);
Query OK， 1 row affected (0.02 sec)

INSERT INTO students(id，sn，name，qq) 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)
7

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

    INSERT INTO students(id，sn，name，qq) VALUES (100， 10000， '唐三藏'， NULL);
Query OK， 1 row affected (0.02 sec)

INSERT INTO students(id，sn，name，qq) 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)
8
    • in
    INSERT INTO students(id，sn，name，qq) VALUES (100， 10000， '唐三藏'， NULL);
Query OK， 1 row affected (0.02 sec)

INSERT INTO students(id，sn，name，qq) 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)
9

  • 姓孙的同学 ; 孙X同学 (X为一个汉字)

    value_list 数量必须和定义表的列的数量及顺序一致

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)
0
    value_list 数量必须和定义表的列的数量及顺序一致

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

  • 语文成绩好于英语成绩的同学

    value_list 数量必须和定义表的列的数量及顺序一致

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

  • 总分在 200 分以下的同学

    value_list 数量必须和定义表的列的数量及顺序一致

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)
3
    • 不能使用别名计算，别名只在返回结果的列名生效;

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

    value_list 数量必须和定义表的列的数量及顺序一致

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

  • 孙X同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80

实际就是 孙X同学 或 总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80

value_list 数量必须和定义表的列的数量及顺序一致

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

结果排序 Order by

基本语法
value_list 数量必须和定义表的列的数量及顺序一致

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

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

descend缩写与descript缩写相同

基本案例:
  • 同学及数学成绩，按数学成绩升序显示
value_list 数量必须和定义表的列的数量及顺序一致

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)
7
  • NULL 视为比任何值都小，升序出现在最上面，降序出现在最下面
value_list 数量必须和定义表的列的数量及顺序一致

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)
8
  • 多字段排序，排序优先级随书写顺序 (相同时怎么排)

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

value_list 数量必须和定义表的列的数量及顺序一致

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)
9
  • 可以使用列别名

order by属于对结果进行处理了，即在select之后，因此可以使用别名

一定是先有数据，才能进行排序

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

Update

update是比较危险的行为，使用时要谨慎;

体现在:如果忘记添加条件，可能会导致所有数据被覆盖;

语法

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

update 的基本原理是: 筛选出数据，再对筛选出的数据做修改;
相当于update隐藏执行了一系列select操作，语法后的where，order by，limit都是提供给select使用;最后再执行update操作;

基本案例

  • 将孙悟空同学的数学成绩变更为 80 分(一次更新一列)
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)
2
  • 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分(一次更新多列)
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)
3
  • 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
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)
4
  • 将所有同学的语文成绩更新为原来的 2 倍

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

没有 条件 子句，则更新全表

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

Delete

语法

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

语法类似Update， Update需要修改字段，因此多了set; Delete只会删除整行，只需确定哪些行即可.

基本案例

  • 删除孙悟空同学的考试成绩
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)
7

delete删除整张表数据

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

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

truncate删除整张表数据(截断表)

语法：
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)
9
注意：

这个操作慎用

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

删除前的表信息

-- 主键冲突
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'
0

删除后

-- 主键冲突
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'
1

查询结果插入 Insert ... Select ...

语法

-- 主键冲突
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'
2

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

-- 主键冲突
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'
3

步骤:

-- 主键冲突
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'
4

传输文件相关操作时(拷贝，移动)，推荐做法是先复制一份临时文件，最后以重命名方式就能实现原子操作.

分组聚合查询

分组

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

语法:

-- 主键冲突
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'
5

聚合函数

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

不加group by 其实也是分组，只不过是单独的一单大组，可以理解为以建表约束的规则进行分组，即以原始表格直接聚合

  • distinct

去重之后再进行聚合函数计算

描述

分组:对某一组字段，不同的值为不同的组，相同的值为一组.

聚合:即合并，对某一组字段，相同的值可以合并(聚合)在一起.

分组聚合:在某种表达式条件下，对指定一组字段分组，然后分别对各组的指定的其他列进行统计，然后聚合一起形成一条新的记录;因为统计后得到的值在该组内都是相同的，因此该组能够合并到一起(未指定的列需要舍弃掉:因为不相同不能聚合);

分组聚合:可以理解成按分组的条件进行分表，每个组对应一个表(逻辑)，分成一个个子表，然后再对各个子表进行聚合查询

查询结果，以及中间筛选的结果， 都可以认为是一张表(逻辑)，这样的语义能够更好理解mysql

having 与 where

主要区别在于条件筛选的阶段不同

where用于对原始表格进行筛选，

having 用于对分组聚合后的表(逻辑)进行筛选，(优先级在select之后).

优先级:

from -> where -> group by -> select-> having ;

复合查询

准备工作，创建一个雇员信息表（来自Oracle 9i的经典测试表）

  • EMP员工表
  • DEPT部门表
  • SALGRADE工资等级表

scott_data.sql

-- 主键冲突
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'
6

案例

  • 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

法一:

-- 主键冲突
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'
7

法二(函数):

-- 主键冲突
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'
8
  • 按照部门号升序而雇员的工资降序排序
-- 主键冲突
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'
9
  • 使用年薪进行降序排序(月薪*12+奖金)
INSERT ... ON DUPLICATE KEY UPDATE 
    column = value [， column = value] ... 
0
  • 显示每个部门的平均工资和最高工资
INSERT ... ON DUPLICATE KEY UPDATE 
    column = value [， column = value] ... 
1
  • 显示平均工资低于2000的部门号和它的平均工资
INSERT ... ON DUPLICATE KEY UPDATE 
    column = value [， column = value] ... 
2
  • 显示每种岗位的雇员总数，平均工资
INSERT ... ON DUPLICATE KEY UPDATE 
    column = value [， column = value] ... 
3

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

  • 显示工资最高的员工的名字和工作岗位
INSERT ... ON DUPLICATE KEY UPDATE 
    column = value [， column = value] ... 
4
  • 显示工资高于平均工资的员工信息
INSERT ... ON DUPLICATE KEY UPDATE 
    column = value [， column = value] ... 
5

多表连接

笛卡尔积

不同的表做笛卡尔积

案例

  • 显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表，因此要联合查询
INSERT ... ON DUPLICATE KEY UPDATE 
    column = value [， column = value] ... 
6
  • 显示部门号为10的部门名，员工名和工资
INSERT ... ON DUPLICATE KEY UPDATE 
    column = value [， column = value] ... 
7
  • 显示各个员工的姓名，工资，及工资级别

法一:

INSERT ... ON DUPLICATE KEY UPDATE 
    column = value [， column = value] ... 
8

法二

INSERT ... ON DUPLICATE KEY UPDATE 
    column = value [， column = value] ... 
9

自连接

相同的表自己连接自己

案例

  • 显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）

法一:

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:      ### 表中有冲突数据，并且数据已经被更新
-- 通过 MySQL 函数获取受到影响的数据行数
SELECT ROW_COUNT(); 
+-------------+
| ROW_COUNT() | 
+-------------+
|           2 | 
+-------------+
1 row in set (0.00 sec)

-- ON DUPLICATE KEY 当发生重复key的时候
0

法二:

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:      ### 表中有冲突数据，并且数据已经被更新
-- 通过 MySQL 函数获取受到影响的数据行数
SELECT ROW_COUNT(); 
+-------------+
| ROW_COUNT() | 
+-------------+
|           2 | 
+-------------+
1 row in set (0.00 sec)

-- ON DUPLICATE KEY 当发生重复key的时候
1

内连接

上文用的笛卡尔积实际就是内连接;内连接也有特定的语法:

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，也是在开发过程中使用的最多的连接查询。

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:      ### 表中有冲突数据，并且数据已经被更新
-- 通过 MySQL 函数获取受到影响的数据行数
SELECT ROW_COUNT(); 
+-------------+
| ROW_COUNT() | 
+-------------+
|           2 | 
+-------------+
1 row in set (0.00 sec)

-- ON DUPLICATE KEY 当发生重复key的时候
2

使用语法能够能够提高模块化程度与可读性，同样的学习成本也提高了

外连接

外连接分为左外连接和右外连接

左外连接

如果联合查询，左侧的表完全显示我们就说是左外连接。

保留左表(左表始终所有数据始终可见)，右表如果不满足则填充NULL;

显然，核心就是以左表为主的思想

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:      ### 表中有冲突数据，并且数据已经被更新
-- 通过 MySQL 函数获取受到影响的数据行数
SELECT ROW_COUNT(); 
+-------------+
| ROW_COUNT() | 
+-------------+
|           2 | 
+-------------+
1 row in set (0.00 sec)

-- ON DUPLICATE KEY 当发生重复key的时候
3

右外连接

语法:

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:      ### 表中有冲突数据，并且数据已经被更新
-- 通过 MySQL 函数获取受到影响的数据行数
SELECT ROW_COUNT(); 
+-------------+
| ROW_COUNT() | 
+-------------+
|           2 | 
+-------------+
1 row in set (0.00 sec)

-- ON DUPLICATE KEY 当发生重复key的时候
4