数据库SQL语言入门（三）

系列文章：

数据库SQL语言入门（一）
数据库SQL语言入门（二）

DML语句

DML操作指对数据中的表记录的操作，主要包括常用的增(insert)、删(delete)、改(update)、查(select)。

1 增: insert 插入记录

INSERT INTO tablename 
(field1,field2,......fieldn) 
VALUES(value1,value2,......valuesn);

方法1:

mysql> insert into table1 (level_update, level_guid, level_name) values('2018-10-19', '1000', '青铜')
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> select * from table1
    -> ;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
+--------------+------------+------------+
1 row in set (0.00 sec)

方法2:可以不指定字段名直接插入，但values中的顺序需要和数据库中字段名顺序相同。

mysql> insert into table1 values('2018-10-19', '1001', '白银');
Query OK, 1 row affected (0.00 sec)

mysql> select * from table1
    -> ;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-19   |       1001 | 白银       |
+--------------+------------+------------+
2 rows in set (0.00 sec)

• 含可空字段 若不填，默认NULL。
• 非空但含有默认值 若不填，默认值。
• 自增字段 若不填，自增的下一个数字

这三种字段，可以不用在insert后的字段列表出现，values后面只需添加前面出现的字段对应的value。

mysql> desc table1;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| level_update | date        | YES  |     | NULL    |       |
| level_guid   | int(5)      | YES  |     | NULL    |       |
| level_name   | varchar(10) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into table1 (level_guid, level_name) values('1002', '黄金');
Query OK, 1 row affected (0.00 sec)

mysql> select * from table1;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-19   |       1001 | 白银       |
| NULL         |       1002 | 黄金       |
+--------------+------------+------------+
3 rows in set (0.00 sec)

MySQL 还支持一次性插入多条记录。这个特性使得MySQL在插入大量数据时，可以节省很多的网络开销，大大提高插入效率。

INSERT INTO tablename (field1, field2,......fieldn) VALUES
(record1_value1, record1_value2,......record1_valuesn), 
(record2_value1, record2_value2,......record2_valuesn), 
......
(recordn_value1, recordn_value2,......recordn_valuesn) ;

如: 一次插入 3 条数据

mysql> insert into table1 (level_update, level_guid, level_name) value('2018-10-19', '1003', '铂金'),
    -> ('2018-10-19', '1004', '钻石'),
    -> ('2018-10-19', '1005', '星耀');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from table1;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-19   |       1001 | 白银       |
| NULL         |       1002 | 黄金       |
| 2018-10-19   |       1003 | 铂金       |
| 2018-10-19   |       1004 | 钻石       |
| 2018-10-19   |       1005 | 星耀       |
+--------------+------------+------------+
6 rows in set (0.00 sec)

2.1 更新记录(一条) update

UPDATE tablename SET field1=value1，field2.=value2，......fieldn=valuen [WHERE CONDITION]

将表 table1 中的 level_guid 为 “1002” 这行的 level_update 的值从 “NULL” 改为 “2018-10-19”。

mysql> update table1 set level_update='2018-10-19' where level_guid='1002';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from table1;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-19   |       1001 | 白银       |
| 2018-10-19   |       1002 | 黄金       |
| 2018-10-19   |       1003 | 铂金       |
| 2018-10-19   |       1004 | 钻石       |
| 2018-10-19   |       1005 | 星耀       |
+--------------+------------+------------+
6 rows in set (0.00 sec)

2.2 更新记录(多条) update

MySQL支持使用 update 命令同时更新多个表的多个字段的数据。语法:

UPDATE t1,t2...tn set t1.field1=expr1,tn.fieldn=exprn [WHERE CONDITION]

先创建表 table2

mysql> create table table2;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table table2 (level_guid int(5), level_name varchar(6), level_update date);
Query OK, 0 rows affected (0.02 sec)

mysql> desc table2;
+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| level_guid   | int(5)     | YES  |     | NULL    |       |
| level_name   | varchar(6) | YES  |     | NULL    |       |
| level_update | date       | YES  |     | NULL    |       |
+--------------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from table2
Empty set (0.00 sec)

mysql> insert into table2 values('1004', '钻石', '2018-10-19');
Query OK, 1 row affected (0.00 sec)

mysql> select * from table2;
+------------+------------+--------------+
| level_guid | level_name | level_update |
+------------+------------+--------------+
|       1004 | 钻石       | 2018-10-19   |
+------------+------------+--------------+
1 row in set (0.00 sec)

执行:如果表 table1 和表 table2 的 level_guid 有相同的，那么，表 table1 的 level_update 的value更新为“2018-10-20”，表 table2 的 level_update 的value更新为“2018-10-21”。

//执行多条数据记录更新   
mysql> update table1 a,table2 b set a.level_update='2018-10-20',b.level_update='2018-10-21' where a.level_guid=b.level_guid;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

ysql> select * from table1;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-19   |       1001 | 白银       |
| 2018-10-19   |       1002 | 黄金       |
| 2018-10-19   |       1003 | 铂金       |
| 2018-10-20   |       1004 | 钻石       |
| 2018-10-19   |       1005 | 星耀       |
+--------------+------------+------------+
6 rows in set (0.00 sec)

mysql> select * from table2;
+------------+------------+--------------+
| level_guid | level_name | level_update |
+------------+------------+--------------+
|       1004 | 钻石       | 2018-10-21   |
+------------+------------+--------------+
1 row in set (0.00 sec)

从结果上看，两个表的数据都更新了。

注意: 多表更新的语法更多的用在了根据一个表的字段，来动态的更新另一个表的字段。

3.1 删除记录(单表中的) delete

DELETE FROM tablename [WHERE CONDITION]

如: 将表 table1 中的level_guid为1005的那一行删去。

mysql> delete from table1 where level_guid='1005';
Query OK, 1 row affected (0.00 sec)

mysql> select * from table1;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-19   |       1001 | 白银       |
| 2018-10-19   |       1002 | 黄金       |
| 2018-10-19   |       1003 | 铂金       |
| 2018-10-20   |       1004 | 钻石       |
+--------------+------------+------------+
5 rows in set (0.00 sec)

3.2 删除记录(多表中的) delete

MySQL中除了支持一次性更新多表中的数据，还可以一次性删除多个表中的数据记录。

DELETE t1,t2...tn FROM t1,t2...tn [WHERE CONDITION]

如：删除表 table1 和表 table2 中level_guid相等的行。

mysql> delete a,b from table1 a,table2 b where a.level_guid=b.level_guid;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from table1;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-19   |       1001 | 白银       |
| 2018-10-19   |       1002 | 黄金       |
| 2018-10-19   |       1003 | 铂金       |
+--------------+------------+------------+
4 rows in set (0.00 sec)

mysql> select * from table2;
Empty set (0.00 sec)

注意:不管是单表还是多表，不加 where 条件将会把表的所有记录删除，所以操作时一定要小心。

4 查询记录 select

SELECT * FROM tablename [WHERE CONDITION]

“ * ”表示选出表中所有的数据记录，等同于用“ , ”号分割的所有字段的写法:

mysql> select * from table1;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-19   |       1001 | 白银       |
| 2018-10-19   |       1002 | 黄金       |
| 2018-10-19   |       1003 | 铂金       |
+--------------+------------+------------+
4 rows in set (0.00 sec)

mysql> select level_update, level_guid, level_name from table1;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-19   |       1001 | 白银       |
| 2018-10-19   |       1002 | 黄金       |
| 2018-10-19   |       1003 | 铂金       |
+--------------+------------+------------+
4 rows in set (0.00 sec)

在实际应用中常常遇到的查询情况:

• 查询不重复的记录 distinct+字段

mysql> select distinct level_update from table1;
+--------------+
| level_update |
+--------------+
| 2018-10-19   |
+--------------+
1 row in set (0.00 sec)

• 条件查询

mysql> select * from table1 where level_name='黄金';
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1002 | 黄金       |
+--------------+------------+------------+
1 row in set (0.00 sec)

where + 条件: 其中，

条件中除了“ = ”，还可以使用 >、 <、>=、<=、!=等比较运算符；

多个条件时，之间也可以使用or、and等逻辑运算符，进行多条件联合查询。

mysql> select * from table1 where level_guid>'1001' or level_name='青铜';
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-19   |       1002 | 黄金       |
| 2018-10-19   |       1003 | 铂金       |
+--------------+------------+------------+
3 rows in set (0.00 sec)

• 排除和限制

语法：

SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC] ， field2 [DESC|ASC]，......fieldn [DESC|ASC]]

实例:

mysql> select * from table1;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-19   |       1001 | 白银       |
| 2018-10-19   |       1002 | 黄金       |
| 2018-10-19   |       1003 | 铂金       |
| 2018-10-22   |       1001 | 钻石       |
| 2018-10-22   |       1002 | 星耀       |
+--------------+------------+------------+
6 rows in set (0.00 sec)

//level_guid的正序排列输出：从低到高
mysql> select * from table1 order by level_guid;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-19   |       1001 | 白银       |
| 2018-10-22   |       1001 | 钻石       |
| 2018-10-19   |       1002 | 黄金       |
| 2018-10-22   |       1002 | 星耀       |
| 2018-10-19   |       1003 | 铂金       |
+--------------+------------+------------+
6 rows in set (0.00 sec)

//level_guid的倒叙排列输出：从高到低
mysql> select * from table1 order by level_guid desc;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1003 | 铂金       |
| 2018-10-19   |       1002 | 黄金       |
| 2018-10-22   |       1002 | 星耀       |
| 2018-10-19   |       1001 | 白银       |
| 2018-10-22   |       1001 | 钻石       |
| 2018-10-19   |       1000 | 青铜       |
+--------------+------------+------------+
6 rows in set (0.00 sec)

//多字段(A,B,C...)正序排列，如果A相同，按B正序排列；如果B相同，按C正序排列；...
mysql> select * from table1 order by level_guid,level_update;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-19   |       1001 | 白银       |
| 2018-10-22   |       1001 | 钻石       |
| 2018-10-19   |       1002 | 黄金       |
| 2018-10-22   |       1002 | 星耀       |
| 2018-10-19   |       1003 | 铂金       |
+--------------+------------+------------+
6 rows in set (0.00 sec)

//多字段(A,B,C...)倒序排列，如果A相同，按B倒序排列；如果B相同，按C倒序排列；...
mysql> select * from table1 order by level_guid,level_update desc;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-22   |       1001 | 钻石       |
| 2018-10-19   |       1001 | 白银       |
| 2018-10-22   |       1002 | 星耀       |
| 2018-10-19   |       1002 | 黄金       |
| 2018-10-19   |       1003 | 铂金       |
+--------------+------------+------------+
6 rows in set (0.00 sec)

limit: 可以让排序后的记录，只显示其中的一部分。

SELECT ......[LIMIT offset_start,row_count]

如:

mysql> select * from table1 order by level_guid limit 4;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-19   |       1000 | 青铜       |
| 2018-10-19   |       1001 | 白银       |
| 2018-10-22   |       1001 | 钻石       |
| 2018-10-19   |       1002 | 黄金       |
+--------------+------------+------------+
4 rows in set (0.00 sec)

mysql> select * from table1 order by level_guid limit 2,4;
+--------------+------------+------------+
| level_update | level_guid | level_name |
+--------------+------------+------------+
| 2018-10-22   |       1001 | 钻石       |
| 2018-10-19   |       1002 | 黄金       |
| 2018-10-22   |       1002 | 星耀       |
| 2018-10-19   |       1003 | 铂金       |
+--------------+------------+------------+
4 rows in set (0.00 sec)

limit 经常和 order by 一起配合使用来进行记录的分页显示。

注意: limit 属于 MySQL 扩展 SQL92 后的语法，在其他数据库上并不能通用。

• 聚合
  语法

SELECT [field1,field2,......fieldn] fun_name FROM tablename
[WHERE where_contition]
[GROUP BY field1,field2,......fieldn
[WITH ROLLUP]]
[HAVING where_contition]

参考:

《深入浅出MySQL》