08.记录操作CURD（增删改查）

创建一张剑的表，并显示表信息：

剑表：id，名称，攻击，命中，暴击

CREATE TABLE sword (
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(32) NOT NULL,
atk SMALLINT UNSIGNED NOT NULL,
hit SMALLINT UNSIGNED NOT NULL DEFAULT 20,
crit SMALLINT UNSIGNED NOT NULL DEFAULT 10
); 
Query OK, 0 rows affected (0.39 sec)

mysql> DESC sword;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(32)          | NO   |     | NULL    |       |
| atk   | smallint(5) unsigned | NO   |     | NULL    |       |
| hit   | smallint(5) unsigned | NO   |     | NULL    |       |
| crit  | smallint(5) unsigned | NO   |     | NULL    |       |
+-------+----------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

插入：INSERT

插入两条数据，id会自递加，id位可用DEFAULT或NULL

mysql> INSERT sword VALUES(NULL,'黑风',10000,400,400);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT*FROM sword;
+----+--------+-------+-----+------+
| id | name   | atk   | hit | crit |
+----+--------+-------+-----+------+
|  1 | 黑风   | 10000 | 400 |  400 |
+----+--------+-------+-----+------+
1 row in set (0.01 sec)

mysql> INSERT sword VALUES(DEFAULT,'木藜',5000,200,200);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT*FROM sword;
+----+--------+-------+-----+------+
| id | name   | atk   | hit | crit |
+----+--------+-------+-----+------+
|  1 | 黑风   | 10000 | 400 |  400 |
|  2 | 木藜   |  5000 | 200 |  200 |
+----+--------+-------+-----+------+
2 rows in set (0.00 sec)

有DEFAULT的字段，赋值时用DEFAULT则会插入默认值

复习一下修改字段信息

mysql> ALTER TABLE sword MODIFY crit SMALLINT UNSIGNED DEFAULT 10;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC sword;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32)          | NO   |     | NULL    |                |
| atk   | smallint(5) unsigned | NO   |     | NULL    |                |
| hit   | smallint(5) unsigned | NO   |     | NULL    |                |
| crit  | smallint(5) unsigned | YES  |     | 10      |                |
+-------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> INSERT sword VALUES(DEFAULT,'荆戈',8000,1000,DEFAULT);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT*FROM sword;
+----+--------+-------+------+------+
| id | name   | atk   | hit  | crit |
+----+--------+-------+------+------+
|  1 | 黑风   | 10000 |  400 |  400 |
|  2 | 木藜   |  5000 |  200 |  200 |
|  3 | 荆戈   |  8000 | 1000 |   10 |
+----+--------+-------+------+------+
3 rows in set (0.00 sec)

一次插入多行，逗号隔开

mysql> INSERT sword VALUES(
    -> DEFAULT,'痕兮',7000,800,999),
    -> (DEFAULT,'逐暮',100,1000,10000),
    -> (DEFAULT,'风跃',9000,10,DEFAULT
    -> );
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT*FROM sword;
+----+--------+-------+------+-------+
| id | name   | atk   | hit  | crit  |
+----+--------+-------+------+-------+
|  1 | 黑风   | 10000 |  400 |   400 |
|  2 | 木藜   |  5000 |  200 |   200 |
|  3 | 荆戈   |  8000 | 1000 |    10 |
|  4 | 痕兮   |  7000 |  800 |   999 |
|  5 | 逐暮   |   100 | 1000 | 10000 |
|  6 | 风跃   |  9000 |   10 |    10 |
+----+--------+-------+------+-------+
6 rows in set (0.00 sec)

有把剑不想让人知道名字，这里用MD5加密

mysql> INSERT sword VALUES(DEFAULT,MD5('隐锋'),8000,2000,10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT*FROM sword;
+----+----------------------------------+-------+------+-------+
| id | name                             | atk   | hit  | crit  |
+----+----------------------------------+-------+------+-------+
|  1 | 黑风                             | 10000 |  400 |   400 |
|  2 | 木藜                             |  5000 |  200 |   200 |
|  3 | 荆戈                             |  8000 | 1000 |    10 |
|  4 | 痕兮                             |  7000 |  800 |   999 |
|  5 | 逐暮                             |   100 | 1000 | 10000 |
|  6 | 风跃                             |  9000 |   10 |    10 |
|  7 | 99f3a83768bb97bc7644f0ee278897dc |  8000 | 2000 |    10 |
+----+----------------------------------+-------+------+-------+
7 rows in set (0.00 sec)

用另一种方式插入值

mysql> INSERT sword SET name='洛神',atk='20000',hit=1;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT*FROM sword;
+----+----------------------------------+-------+------+-------+
| id | name                             | atk   | hit  | crit  |
+----+----------------------------------+-------+------+-------+
|  1 | 黑风                             | 10000 |  400 |   400 |
|  2 | 木藜                             |  5000 |  200 |   200 |
|  3 | 荆戈                             |  8000 | 1000 |    10 |
|  4 | 痕兮                             |  7000 |  800 |   999 |
|  5 | 逐暮                             |   100 | 1000 | 10000 |
|  6 | 风跃                             |  9000 |   10 |    10 |
|  7 | 99f3a83768bb97bc7644f0ee278897dc |  8000 | 2000 |    10 |
|  8 | 洛神                             | 20000 |    1 |    10 |
+----+----------------------------------+-------+------+-------+
8 rows in set (0.00 sec)

第三种方法插入数据

新建一个测试表：将sword_insert_test中test_name赋值为sword表中id大于5的name

mysql> CREATE TABLE sword_insert_test (
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> test_name VARCHAR(32) NOT NULL
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT*FROM sword_insert_test ;
Empty set (0.01 sec)
mysql> INSERT sword_insert_test(test_name) SELECT name FROM sword WHERE id>5;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT*FROM sword_insert_test ;
+----+-----------+
| id | test_name |
+----+-----------+
|  1 | 风跃      |
|  2 | 洛神      |
|  3 | 隐锋      |
+----+-----------+
3 rows in set (0.00 sec)

更新数据UPDATE

更新某项值：将命中值(hit)全加1

mysql> UPDATE sword SET hit=hit+1;
Query OK, 8 rows affected (0.04 sec)
Rows matched: 8  Changed: 8  Warnings: 0

mysql> SELECT*FROM sword;
+----+----------------------------------+-------+------+-------+
| id | name                             | atk   | hit  | crit  |
+----+----------------------------------+-------+------+-------+
|  1 | 黑风                             | 10000 |  401 |   400 |
|  2 | 木藜                             |  5000 |  201 |   200 |
|  3 | 荆戈                             |  8000 | 1001 |    10 |
|  4 | 痕兮                             |  7000 |  801 |   999 |
|  5 | 逐暮                             |   100 | 1001 | 10000 |
|  6 | 风跃                             |  9000 |   11 |    10 |
|  7 | 99f3a83768bb97bc7644f0ee278897dc |  8000 | 2001 |    10 |
|  8 | 洛神                             | 20000 |    2 |    10 |
+----+----------------------------------+-------+------+-------+
8 rows in set (0.00 sec)

更新多项值：将攻击(atk)值修改为：攻击+暴击(crit)*2；命中全部减1；

mysql> UPDATE sword SET atk=atk+2*crit,hit=hit-1;
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8  Changed: 8  Warnings: 0

mysql> SELECT*FROM sword;
+----+----------------------------------+-------+------+-------+
| id | name                             | atk   | hit  | crit  |
+----+----------------------------------+-------+------+-------+
|  1 | 黑风                             | 10800 |  400 |   400 |
|  2 | 木藜                             |  5400 |  200 |   200 |
|  3 | 荆戈                             |  8020 | 1000 |    10 |
|  4 | 痕兮                             |  8998 |  800 |   999 |
|  5 | 逐暮                             | 20100 | 1000 | 10000 |
|  6 | 风跃                             |  9020 |   10 |    10 |
|  7 | 99f3a83768bb97bc7644f0ee278897dc |  8020 | 2000 |    10 |
|  8 | 洛神                             | 20020 |    1 |    10 |
+----+----------------------------------+-------+------+-------+
8 rows in set (0.00 sec)

逐暮的属性太高了，下面来用限定条件对单条数据进行更新：

mysql> UPDATE sword SET atk=atk-20000 WHERE name='逐暮';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT*FROM sword;
+----+----------------------------------+-------+------+-------+
| id | name                             | atk   | hit  | crit  |
+----+----------------------------------+-------+------+-------+
|  1 | 黑风                             | 10800 |  400 |   400 |
|  2 | 木藜                             |  5400 |  200 |   200 |
|  3 | 荆戈                             |  8020 | 1000 |    10 |
|  4 | 痕兮                             |  8998 |  800 |   999 |
|  5 | 逐暮                             |   100 | 1000 | 10000 |
|  6 | 风跃                             |  9020 |   10 |    10 |
|  7 | 99f3a83768bb97bc7644f0ee278897dc |  8020 | 2000 |    10 |
|  8 | 洛神                             | 20020 |    1 |    10 |
+----+----------------------------------+-------+------+-------+
8 rows in set (0.00 sec)

删除DELETE

第七个看着不舒服，删掉吧：

再插入没加密的：(注意，删掉id为7的，不会影响其他数据的id，再插入id为9)

mysql> DELETE FROM sword WHERE id=7;
Query OK, 1 row affected (0.07 sec)

mysql> SELECT*FROM sword;
+----+--------+-------+------+-------+
| id | name   | atk   | hit  | crit  |
+----+--------+-------+------+-------+
|  1 | 黑风   | 10800 |  400 |   400 |
|  2 | 木藜   |  5400 |  200 |   200 |
|  3 | 荆戈   |  8020 | 1000 |    10 |
|  4 | 痕兮   |  8998 |  800 |   999 |
|  5 | 逐暮   |   100 | 1000 | 10000 |
|  6 | 风跃   |  9020 |   10 |    10 |
|  8 | 洛神   | 20020 |    1 |    10 |
+----+--------+-------+------+-------+
7 rows in set (0.00 sec)
mysql> INSERT sword SET name='隐锋',atk=8020,hit=2000;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT*FROM sword;
+----+--------+-------+------+-------+
| id | name   | atk   | hit  | crit  |
+----+--------+-------+------+-------+
|  1 | 黑风   | 10800 |  400 |   400 |
|  2 | 木藜   |  5400 |  200 |   200 |
|  3 | 荆戈   |  8020 | 1000 |    10 |
|  4 | 痕兮   |  8998 |  800 |   999 |
|  5 | 逐暮   |   100 | 1000 | 10000 |
|  6 | 风跃   |  9020 |   10 |    10 |
|  8 | 洛神   | 20020 |    1 |    10 |
|  9 | 隐锋   |  8020 | 2000 |    10 |
+----+--------+-------+------+-------+
8 rows in set (0.00 sec)

查询任何查询都不会影响表中的数据,)

查询指定字段：以id和name为例

mysql> SELECT id,name FROM sword;
+----+--------+
| id | name   |
+----+--------+
|  1 | 黑风   |
|  2 | 木藜   |
|  3 | 荆戈   |
|  4 | 痕兮   |
|  5 | 逐暮   |
|  6 | 风跃   |
|  8 | 洛神   |
|  9 | 隐锋   |
+----+--------+
8 rows in set (0.05 sec)

查询另一种方式

mysql> SELECT sword.id,sword.name FROM sword;
+----+--------+
| id | name   |
+----+--------+
|  1 | 黑风   |
|  2 | 木藜   |
|  3 | 荆戈   |
|  4 | 痕兮   |
|  5 | 逐暮   |
|  6 | 风跃   |
|  8 | 洛神   |
|  9 | 隐锋   |
+----+--------+
8 rows in set (0.00 sec)

为查询的字段取别名

mysql> SELECT crit AS '暴击',name AS'名称' FROM sword;
+--------+--------+
| 暴击   | 名称   |
+--------+--------+
|    400 | 黑风   |
|    200 | 木藜   |
|     10 | 荆戈   |
|    999 | 痕兮   |
|  10000 | 逐暮   |
|     10 | 风跃   |
|     10 | 洛神   |
|     10 | 隐锋   |
+--------+--------+
8 rows in set (0.00 sec)

查询指定位置的记录

查询指定位置的记录

分组

mysql> SELECT crit FROM sword GROUP BY crit;
+-------+
| crit  |
+-------+
|    10 |
|   200 |
|   400 |
|   999 |
| 10000 |
+-------+
5 rows in set (0.00 sec)

添加条件下的分组查询：

mysql> SELECT crit FROM sword GROUP BY crit HAVING crit<500;
+------+
| crit |
+------+
|   10 |
|  200 |
|  400 |
+------+
3 rows in set (0.01 sec)

降序排列

mysql> SELECT * FROM sword ORDER BY id DESC;
+----+--------+-------+------+-------+
| id | name   | atk   | hit  | crit  |
+----+--------+-------+------+-------+
|  9 | 隐锋   |  8020 | 2000 |    10 |
|  8 | 洛神   | 20020 |    1 |    10 |
|  6 | 风跃   |  9020 |   10 |    10 |
|  5 | 逐暮   |   100 | 1000 | 10000 |
|  4 | 痕兮   |  8998 |  800 |   999 |
|  3 | 荆戈   |  8020 | 1000 |    10 |
|  2 | 木藜   |  5400 |  200 |   200 |
|  1 | 黑风   | 10800 |  400 |   400 |
+----+--------+-------+------+-------+
8 rows in set (0.00 sec)

多条记录排序(当第一个字段相同，相同的数据按第二个字段再排)

mysql> SELECT * FROM sword ORDER BY crit,id DESC;
+----+--------+-------+------+-------+
| id | name   | atk   | hit  | crit  |
+----+--------+-------+------+-------+
|  9 | 隐锋   |  8020 | 2000 |    10 |
|  8 | 洛神   | 20020 |    1 |    10 |
|  6 | 风跃   |  9020 |   10 |    10 |
|  3 | 荆戈   |  8020 | 1000 |    10 |
|  2 | 木藜   |  5400 |  200 |   200 |
|  1 | 黑风   | 10800 |  400 |   400 |
|  4 | 痕兮   |  8998 |  800 |   999 |
|  5 | 逐暮   |   100 | 1000 | 10000 |
+----+--------+-------+------+-------+
8 rows in set (0.00 sec)

限制：LIMIT

查询前四条数据：

mysql> SELECT * FROM sword LIMIT 4;
+----+--------+-------+------+------+
| id | name   | atk   | hit  | crit |
+----+--------+-------+------+------+
|  1 | 黑风   | 10800 |  400 |  400 |
|  2 | 木藜   |  5400 |  200 |  200 |
|  3 | 荆戈   |  8020 | 1000 |   10 |
|  4 | 痕兮   |  8998 |  800 |  999 |
+----+--------+-------+------+------+
4 rows in set (0.00 sec)

查询前2~4条数据：

mysql> SELECT * FROM sword LIMIT 1,4;
+----+--------+------+------+-------+
| id | name   | atk  | hit  | crit  |
+----+--------+------+------+-------+
|  2 | 木藜   | 5400 |  200 |   200 |
|  3 | 荆戈   | 8020 | 1000 |    10 |
|  4 | 痕兮   | 8998 |  800 |   999 |
|  5 | 逐暮   |  100 | 1000 | 10000 |
+----+--------+------+------+-------+
4 rows in set (0.00 sec)