MySQL 记录操作（四）
语法结构：
# 使用 INSERT 插入记录的语法结构
INSERT [INTO] tbl_name [(col_namem,...)] {VALUES | VALUE} ({expr | DEFAULT},..),(...),...
# 可以同时插入多条记录，省略列名时需要给所有列赋值, 也可以插入表达式
# 主键 id 可以写 DEFAULT 或 NULL（使用自动编号递增）
示例：
# 创建一个数据表 tb7
mysql> CREATE TABLE tb7(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL,
-> password VARCHAR(32) NOT NULL,
-> age TINYINT UNSIGNED NOT NULL DEFAULT 10,
-> sex BOOLEAN
-> );
Query OK, 0 rows affected (0.09 sec)
# 同时插入2条记录，中间以逗号分隔
mysql> INSERT tb7 VALUES(NULL,'Alice','123',25,1)，(NULL,'Tom','456',26,1);
Query OK, 1 row affected (0.02 sec)
# 查看插入记录
mysql> SELECT * FROM tb7;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Alice | 123 | 25 | 1 |
| 2 | Tom | 456 | 26 | 1 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)
# 主键也可以写成 DEFAULT ,也可以插入表达式，函数（md5（'123'））
mysql> INSERT tb7 VALUES(DEFAULT,'Bob','567',3*7-5,1),(NULL,'Rose',md5('123'),DEFAULT,1);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
# 查看插入记录
mysql> SELECT * FROM tb7;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Alice | 123 | 25 | 1 |
| 2 | Tom | 456 | 26 | 1 |
| 3 | Bob | 567 | 16 | 1 |
| 4 | Rose | 202cb962ac59075b964b07152d234b70 | 10 | 1 |
+----+----------+----------------------------------+-----+------+
4 rows in set (0.00 sec)
4、操作数据表中的记录
4.1、插入记录的三种方法
4.1.1、 INSERT 语句
INSERT 方法是最常用的方法，可以一次插入多条记录
4.1.2、INSERT SET/SELECT 语句
与第一种插入方式区别在于，此方法可以使用子查询（SubQuery），而且只能一次插入一条记录。
语法结构：
INSERT [INTO] tbl_name SET col_name={expr| DEFAULT},...
示例：
# 使用 INSERT SET 语句插入记录，age 有默认值，sex 可为空都可以省略
mysql> INSERT tb7 SET username='Ben',password='456';
Query OK, 1 row affected (0.02 sec)
# 查看数据表
mysql> SELECT * FROM tb7;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Alice | 123 | 25 | 1 |
| 2 | Tom | 456 | 26 | 1 |
| 3 | Bob | 567 | 16 | 1 |
| 4 | Rose | 202cb962ac59075b964b07152d234b70 | 10 | 1 |
| 5 | Ben | 456 | 10 | NULL |
+----+----------+----------------------------------+-----+------+
5 rows in set (0.00 sec)
4.1.3、INSERT SELECT 语句
此方法可以将查询结果插入到指定数据表中
语法结构：
INSERT [INTO] tbl_name [(col_name,...)] SELECT...
4.2、操作单个数据表中的记录
4.2.1、更新单表记录（UPDATE）
语法结构：
# 如果省略 where 条件，那么所有的记录都将会被更新
UPDATE [LOW_PRIORITY] [INGORE] table_reference SET col_name1={expr1 | DEFAULT} [,col_name2={expr2 | DEFAULT}]...[WHERE where_condition]
# 更新某一行中的某一列
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
# 更新某一行中的若干列
UPDATE 表名称 SET 列名称 = 新值, 列名称 = 新值 WHERE 列名称 = 某值
# 更新多条记录的不同值（批量更新）
mysql> UPDATE tdb_goods # 更新 tdb_goods 数据表
-> SET goods_name = CASE goods_id # 更新列 goods_name的多个字段
-> WHEN 1 THEN 'R510VC 15.6英寸笔记本' # 当 goods_id 为 1，则 goods_name 为 'R510VC 15.6英寸笔记本'
-> WHEN 2 THEN '商务双肩背包' # 当 goods_id 为 2，则 goods_name 为'商务双肩背包'
-> END
-> WHERE goods_id IN (1,2)
-> ;
Query OK, 2 row affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
示例1：将 age 全部加 5
# 省略 where 条件，将年龄全部加 5 岁
mysql> UPDATE tb7 SET age=age+5;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0
# 查看数据表 发现所有记录的年龄都在原有基础上加了 5 岁
mysql> SELECT * FROM tb7;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Alice | 123 | 30 | 1 |
| 2 | Tom | 456 | 31 | 1 |
| 3 | Bob | 567 | 21 | 1 |
| 4 | Rose | 202cb962ac59075b964b07152d234b70 | 15 | 1 |
| 5 | Ben | 456 | 15 | NULL |
+----+----------+----------------------------------+-----+------+
5 rows in set (0.00 sec)
示例2：将每条记录的 age 列，在原有的基础上减去其对应的 id，sex 设置为 0
# 将每条记录的 age 列，在原有的基础上减去其对应的 id，sex 设置为 0
mysql> UPDATE tb7 SET age = age - id,sex = 0;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0
# 查看数据表
mysql> SELECT * FROM tb7;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Alice | 123 | 29 | 0 |
| 2 | Tom | 456 | 29 | 0 |
| 3 | Bob | 567 | 18 | 0 |
| 4 | Rose | 202cb962ac59075b964b07152d234b70 | 11 | 0 |
| 5 | Ben | 456 | 10 | 0 |
+----+----------+----------------------------------+-----+------+
5 rows in set (0.00 sec)
示例3：设置 WHERE 条件，将所有 id 为偶数的记录 年龄加 10
# 设置 WHERE 条件，id 为偶数的记录加 10
mysql> UPDATE tb7 SET age = age + 10 WHERE id % 2 = 0;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
# 查看数据表
mysql> SELECT * FROM tb7;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Alice | 123 | 29 | 0 |
| 2 | Tom | 456 | 39 | 0 |
| 3 | Bob | 567 | 18 | 0 |
| 4 | Rose | 202cb962ac59075b964b07152d234b70 | 21 | 0 |
| 5 | Ben | 456 | 10 | 0 |
+----+----------+----------------------------------+-----+------+
5 rows in set (0.00 sec)
4.2.2、删除单表记录（DELETE）
语法结构：
DELETE FROM tbl_name [WHERE where_condition]
示例：
# 将第 5 条记录删除掉
mysql> DELETE FROM tb7 WHERE id = 5;
Query OK, 1 row affected (0.00 sec)
# 查看数据表，发现 id 为 5 的记录被删除了
mysql> SELECT * FROM tb7;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Alice | 123 | 29 | 0 |
| 2 | Tom | 456 | 39 | 0 |
| 3 | Bob | 567 | 18 | 0 |
| 4 | Rose | 202cb962ac59075b964b07152d234b70 | 21 | 0 |
+----+----------+----------------------------------+-----+------+
4 rows in set (0.00 sec)
# 重新插入一条记录，其id 会一直往上增长，而不是补充被删除的记录 id
mysql> INSERT tb7 VALUES(NULL,'Lila','abcd',DEFAULT,1);
Query OK, 1 row affected (0.01 sec)
# 查看数据表
mysql> SELECT * FROM tb7;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Alice | 123 | 29 | 0 |
| 2 | Tom | 456 | 39 | 0 |
| 3 | Bob | 567 | 18 | 0 |
| 4 | Rose | 202cb962ac59075b964b07152d234b70 | 21 | 0 |
| 6 | Lila | abcd | 10 | 1 |
+----+----------+----------------------------------+-----+------+
5 rows in set (0.00 sec)
4.2.3、查找表达式解析（SELECT）
语法结构：
SELECT select_expr [,select_expr...]
[
FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | position} [ASC | DESC],...]
[HAVING where_condition]
[ORDER BY {col_name | expr | position} [ASC | DESC],...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
]
查询表达式：
- 每一个表达式表示想要的一列，必须有至少一个
mysql> SELECT 3 + 5;
+-------+
| 3 + 5 |
+-------+
| 8 |
+-------+
1 row in set (0.00 sec)
- 多个列之间以英文逗号分隔，列名的顺序影响显示结果的顺序
# 可以只查询单列（需要指定列名），也可以查询多列，以逗号分隔
SHOW COLUMNS FROM tbl_name; # 不知道列名，可以先查看列名
SELECT id,username FROM tb7; # 查询 id、username 列
SELECT * FROM tb7; # * 表示查询所有列
- 星号
（*）表示所有列，（tbl_name.*）可以表示命名表的所有列
# 以数据表名.列名 的形式查询适用于多个数据表连接的时候，因为多个数据表间有可能有相同字段
SELECT tb7.id,tb7.username FROM tb7
- 查询表达式可以使用
[ AS ] alias_name为其赋予别名
# 在查询表达式时可以给列名指定别名（当列名比较长的时候，尤其适用）
mysql> SELECT id AS userid,username AS un FROM tb7;
+--------+-------+
| userid | un |
+--------+-------+
| 1 | Alice |
| 2 | Tom |
| 3 | Bob |
| 4 | Rose |
| 6 | Lila |
+--------+-------+
5 rows in set (0.00 sec)
- 别名可用于
GROUP BY ，ORDRE BY或HAVING字句
4.2.4、WHERE 语句进行条件查询
条件表达式：
对记录进行过滤，如果没有指定 WHERE 字句，则显示所有记录。
在 WHERE 表达式中，可以使用 MySQL 支持的函数或运算符
4.3、mysql group by 语句对查询结果分组
语法结构：
# 对查询结果分组 ASC 为升序（默认），DESC 为降序
[GROUP BY {col_name | position} [ASC | DESC],...]
示例：
# 查看数据表
mysql> SELECT * FROM tb7;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Alice | 123 | 29 | 0 |
| 2 | Tom | 456 | 39 | 0 |
| 3 | Bob | 567 | 18 | 0 |
| 4 | Rose | 202cb962ac59075b964b07152d234b70 | 21 | 0 |
| 6 | Lila | abcd | 10 | 1 |
+----+----------+----------------------------------+-----+------+
5 rows in set (0.00 sec)
# 选择将数据表 tb7 中的 sex 列分组（分为 0 和 1 两组），也可以指定位置（即 id ）
mysql> SELECT sex FROM tb7 GROUP BY sex;
+------+
| sex |
+------+
| 0 |
| 1 |
+------+
2 rows in set (0.01 sec)
4.4、having 语句设置分组条件
在HAVING字句中，查询对象必须出现在查询表达式中或使用聚合函数
分组条件：
SELECT column_name, aggregate_function(column_name) # 聚合函数
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
示例1：
# 查看数据表
SELECT * FROM tb7 ;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Alice | 123 | 29 | 0 |
| 2 | Tom | 456 | 39 | 0 |
| 3 | Bob | 567 | 18 | 0 |
| 4 | Rose | 202cb962ac59075b964b07152d234b70 | 21 | 0 |
| 6 | Lila | abcd | 10 | 1 |
+----+----------+----------------------------------+-----+------+
5 rows in set (0.01 sec)
# 使用 HAVING 语句设置分组条件（需要注意的是在HAVING字句中，查询对象必须出现在查询表达式中或使用聚合函数）
mysql> SELECT sex,age FROM tb7 GROUP BY 1 HAVING age >= 35;
Empty set (0.00 sec)
# 聚合函数(count(id)), 条件为sex 列中，id次数大于等于2的有哪些
mysql> SELECT sex FROM tb7 GROUP BY 1 HAVING count(id) >= 2;
+------+
| sex |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
# 聚合函数（count(id)），条件为sex 列中，id次数大于等于1的有哪些
mysql> SELECT sex FROM tb7 GROUP BY 1 HAVING count(id) >= 1;
+------+
| sex |
+------+
| 0 |
| 1 |
+------+
2 rows in set (0.00 sec)
示例2：
# 查看数据表
mysql> select * from tb7;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Alice | 123 | 29 | 0 |
| 2 | Tom | 456 | 39 | 0 |
| 3 | Bob | 567 | 18 | 0 |
| 4 | Rose | 202cb962ac59075b964b07152d234b70 | 21 | 0 |
| 6 | Lila | abcd | 10 | 1 |
+----+----------+----------------------------------+-----+------+
5 rows in set (0.00 sec)
# 设置条件（age 小于 28）的用户
mysql> select username,age from tb7 group by username having age < 28;
+----------+-----+
| username | age |
+----------+-----+
| Bob | 18 |
| Lila | 10 |
| Rose | 21 |
+----------+-----+
3 rows in set (0.01 sec)
# 设置条件（Bob 和 Tom 中哪个年龄大于 20）
mysql> select username,age from tb7 where username='Bob' or username='Tom' group by username having age > 20;
+----------+-----+
| username | age |
+----------+-----+
| Tom | 39 |
+----------+-----+
1 row in set (0.00 sec)
4.5、order by 对查询结果进行排序
对于需要排序的列中有相同的记录，则按照其 id 对其进行升序降序，也可以同时升序降序
SELECT * FROM tb7 ORDER BY age,id DESC;
# 默认对查询结果进行升序排列（ASC，可省略不写），若要降序则加上 DESC 关键字即可
[ORDER BY {col_name | expr | position} [ASC | DESC],...]
示例1：
# 查看数据表结构
mysql> SELECT * FROM tb7;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Alice | 123 | 29 | 0 |
| 2 | Tom | 456 | 39 | 0 |
| 3 | Bob | 567 | 18 | 0 |
| 4 | Rose | 202cb962ac59075b964b07152d234b70 | 21 | 0 |
| 6 | Lila | abcd | 10 | 1 |
+----+----------+----------------------------------+-----+------+
5 rows in set (0.04 sec)
# 对 id 列进行降序排列
mysql> SELECT * FROM tb7 ORDER BY id DESC;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 6 | Lila | abcd | 10 | 1 |
| 4 | Rose | 202cb962ac59075b964b07152d234b70 | 21 | 0 |
| 3 | Bob | 567 | 18 | 0 |
| 2 | Tom | 456 | 39 | 0 |
| 1 | Alice | 123 | 29 | 0 |
+----+----------+----------------------------------+-----+------+
5 rows in set (0.00 sec)
# 对 age 列进行升序排列
mysql> SELECT * FROM tb7 ORDER BY age;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 6 | Lila | abcd | 10 | 1 |
| 3 | Bob | 567 | 18 | 0 |
| 4 | Rose | 202cb962ac59075b964b07152d234b70 | 21 | 0 |
| 1 | Alice | 123 | 29 | 0 |
| 2 | Tom | 456 | 39 | 0 |
+----+----------+----------------------------------+-----+------+
5 rows in set (0.01 sec)
示例2：
# 数据表 tb8
mysql> SELECT * FROM tb8;
+----+----------+-------------+
| id | company | ordernumber |
+----+----------+-------------+
| 1 | IBM | 3532 |
| 2 | W3School | 2356 |
| 3 | Aplle | 4698 |
| 4 | W3School | 6953 |
+----+----------+-------------+
4 rows in set (0.00 sec)
# 对company 进行升序
mysql> SELECT company, ordernumber FROM tb8 ORDER BY company;
+----------+-------------+
| company | ordernumber |
+----------+-------------+
| Aplle | 4698 |
| IBM | 3532 |
| W3School | 2356 |
| W3School | 6953 |
+----------+-------------+
4 rows in set (0.00 sec)
# 对company升序，ordernumber降序
mysql> SELECT company, ordernumber from tb8 order by company,ordernumber desc;
+----------+-------------+
| company | ordernumber |
+----------+-------------+
| Aplle | 4698 |
| IBM | 3532 |
| W3School | 6953 |
| W3School | 2356 |
+----------+-------------+
4 rows in set (0.00 sec)
4.6、limit 语句限制查询数量
# limit 语句有2个参数（偏移量，结果出现的条数），需要注意的是记录是从 0 开始的，意味着第三条记录就是第二条，默认出现条数为1
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
示例：
# 数据表 tb8 的结构
mysql> SELECT * FROM tb8;
+----+----------+-------------+
| id | company | ordernumber |
+----+----------+-------------+
| 1 | IBM | 3532 |
| 2 | W3School | 2356 |
| 3 | Aplle | 4698 |
| 4 | W3School | 6953 |
+----+----------+-------------+
4 rows in set (0.00 sec)
# 只有一个参数时，指的是出现的条数，默认从第一个记录开始
mysql> SELECT * FROM tb8 LIMIT 1;
+----+---------+-------------+
| id | company | ordernumber |
+----+---------+-------------+
| 1 | IBM | 3532 |
+----+---------+-------------+
1 row in set (0.00 sec)
# 2 条记录
mysql> SELECT * FROM tb8 LIMIT 2;
+----+----------+-------------+
| id | company | ordernumber |
+----+----------+-------------+
| 1 | IBM | 3532 |
| 2 | W3School | 2356 |
+----+----------+-------------+
2 rows in set (0.00 sec)
# 2 个参数时，第一个参数为偏移量（即第几条记录，这里为2，即第三条记录），第二个参数为条数
mysql> SELECT * FROM tb8 LIMIT 2,2;
+----+----------+-------------+
| id | company | ordernumber |
+----+----------+-------------+
| 3 | Aplle | 4698 |
| 4 | W3School | 6953 |
+----+----------+-------------+
2 rows in set (0.00 sec)
示例2：
# 限制查询出现的记录与 id 号无关，只与第几条有关
# 对 id 进行降序排列
mysql> SELECT * FROM tb8 ORDER BY id DESC;
+----+----------+-------------+
| id | company | ordernumber |
+----+----------+-------------+
| 4 | W3School | 6953 |
| 3 | Aplle | 4698 |
| 2 | W3School | 2356 |
| 1 | IBM | 3532 |
+----+----------+-------------+
4 rows in set (0.00 sec)
# 现在出现的 2 条 id 号为 2、1，与 id 无关
mysql> SELECT * FROM tb8 ORDER BY id DESC LIMIT 2,2;
+----+----------+-------------+
| id | company | ordernumber |
+----+----------+-------------+
| 2 | W3School | 2356 |
| 1 | IBM | 3532 |
+----+----------+-------------+
2 rows in set (0.00 sec)
4.7、使用 insert 语句将一个数据表的记录插入到另一个数据表中
偏移量 = （当前页码 - 1）* 每页所显示的记录数
# 数据表 tb7
mysql> select * from tb7;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Alice | 123 | 29 | 0 |
| 2 | Tom | 456 | 39 | 0 |
| 3 | Bob | 567 | 18 | 0 |
| 4 | Rose | 202cb962ac59075b964b07152d234b70 | 21 | 0 |
| 6 | Lila | abcd | 10 | 1 |
+----+----------+----------------------------------+-----+------+
5 rows in set (0.00 sec)
# tb9 中只有 id 和 username列，没有插入任何记录
mysql> SHOW COLUMNS FROM tb9;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
+----------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
# 插入到数据表 tb9的 username列中，从 tb7 的 username中 挑选 age 大于等于 29的记录
mysql> INSERT tb9(username) SELECT username FROM tb7 WHERE age >= 29;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
# 查看数据表 tb9
mysql> SELECT * FROM tb9;
+----+----------+
| id | username |
+----+----------+
| 1 | Alice |
| 2 | Tom |
+----+----------+
2 rows in set (0.00 sec)
