创建一张剑的表，并显示表信息：
剑表：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)