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)

转载于:https://www.cnblogs.com/toly-top/p/9782021.html

相关资源：JAVA上百实例源码以及开源项目