SpringBoot-14-MyBatis预热篇,MySQL小结
标签:
SpringBoot
.在总结MyBatis之前,还是先巩固一下MySQL的知识吧,毕竟MyBatis是SQL语句为王。
2.为了后面有素材可以实验,借此弄个库也不错。
3.如果你觉得自己MySQL很厉害,只是想看MyBatis,可以看一下表结构,不必细究。
创建一张剑的表,并显示表信息:
剑表: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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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)
0准备工作:
先为剑表添加两个属性:属性(attr)和类型(type)
mysql> ALTER TABLE sword ADD attr VARCHAR(4) NOT NULL; Query OK, 0 rows affected (0.11 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE sword ADD type VARCHAR(8) NOT NULL; Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SELECT*FROM sword; +----+--------+-------+------+-------+------+------+| id | name | atk | hit | crit | attr | type |+----+--------+-------+------+-------+------+------+| 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)
修改数据:(为属性添加值)
mysql> UPDATE sword SET attr='木',type='神界' WHERE name='黑风'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0mysql> UPDATE sword SET attr='木',type='人界' WHERE name='木藜'; Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0mysql> UPDATE sword SET attr='金',type='魔界' WHERE name='荆戈'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql> UPDATE sword SET attr='水',type='道界' WHERE name='痕兮'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql> UPDATE sword SET attr='火',type='鬼界' WHERE name='逐暮'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql> UPDATE sword SET attr='木',type='仙界' WHERE name='风跃'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql> UPDATE sword SET attr='金',type='神界' WHERE name='洛神'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql> UPDATE sword SET attr='土',type='人界' WHERE name='隐锋'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql> SELECT*FROM sword; +----+--------+-------+------+-------+------+--------+| id | name | atk | hit | crit | attr | type |+----+--------+-------+------+-------+------+--------+| 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)8 rows in set (0.00 sec)
子查询
出现在其他SQL语句内的SELECT语句 子查询必须在()内 增删改查都可以进行子查询 返回:标量,行,列或子查询
1.比较运算符的子查询(=、>、<、>=、<=、<>、!=、<=>)
计算平均攻击力(四舍五入2位)
mysql> SELECT ROUND(AVG(atk),2) AS '平均攻击力' FROM sword; +-----------------+| 平均攻击力 |+-----------------+| 8797.25 |+-----------------+1 row
普通查询:所有攻击力大于平均攻击力的剑,并降序排序
mysql> SELECT * FROM sword WHERE atk > 5636.36 ORDER BY atk DESC; +----+--------+-------+------+------+------+--------+| id | name | atk | hit | crit | attr | type |+----+--------+-------+------+------+------+--------+| 8 | 洛神 | 20020 | 1 | 10 | 金 | 神界 || 1 | 黑风 | 10800 | 400 | 400 | 木 | 神界 || 6 | 风跃 | 9020 | 10 | 10 | 木 | 仙界 || 4 | 痕兮 | 8998 | 800 | 999 | 水 | 道界 || 3 | 荆戈 | 8020 | 1000 | 10 | 金 | 魔界 || 9 | 隐锋 | 8020 | 2000 | 10 | 土 | 人界 |+----+--------+-------+------+------+------+--------+6 rows in set (0.00 sec)
使用子查询(也就是将上面两步简化为一步)
mysql> SELECT * FROM sword WHERE atk > -> (SELECT ROUND(AVG(atk),2) AS '平均攻击力' FROM sword) -> ORDER BY atk DESC; +----+--------+-------+-----+------+------+--------+| id | name | atk | hit | crit | attr | type |+----+--------+-------+-----+------+------+--------+| 8 | 洛神 | 20020 | 1 | 10 | 金 | 神界 || 1 | 黑风 | 10800 | 400 | 400 | 木 | 神界 || 6 | 风跃 | 9020 | 10 | 10 | 木 | 仙界 || 4 | 痕兮 | 8998 | 800 | 999 | 水 | 道界 |+----+--------+-------+-----+------+------+--------+4 rows in set (0.00 sec)
2.ANY、SOME、ALL、IN
ANY 满足一条即可查询到 SOME 同ANY ALL 满足所有可查询到 IN 等价于 = ANY 或 = SOME
查询类型为人界的剑攻击力
mysql> SELECT atk FROM sword WHERE type = '人界'; +------+ | atk | +------+ | 5400 | | 8020 | +------+ 2 rows in set (0.00 sec) 2 rows in set (0.00 sec)
查询攻击力大于(任意一个:ANY)"人族"的剑名称,并降序排列
虽然荆戈的攻击力不大于8020,但大于5400,满足一个就能查询到
mysql> SELECT id,name,atk FROM sword WHERE atk > -> ANY(SELECT atk FROM sword WHERE type = '人界') -> ORDER BY atk DESC; +----+--------+-------+| id | name | atk |+----+--------+-------+| 8 | 洛神 | 20020 || 1 | 黑风 | 10800 || 6 | 风跃 | 9020 || 4 | 痕兮 | 8998 || 3 | 荆戈 | 8020 || 9 | 隐锋 | 8020 |+----+--------+-------+6 rows in set (0.00 sec)
查询攻击力大于(所有:ALL)"人族"的剑名称,并降序排列
虽然荆戈的攻击力大于5400,但不大于8020,所以查不到
mysql> SELECT id,name,atk FROM sword WHERE atk > -> ALL(SELECT atk FROM sword WHERE type = '人界') -> ORDER BY atk DESC; +----+--------+-------+| id | name | atk |+----+--------+-------+| 8 | 洛神 | 20020 || 1 | 黑风 | 10800 || 6 | 风跃 | 9020 || 4 | 痕兮 | 8998 |+----+--------+-------+4 rows in set (0.00 sec)
查询任意攻击力等于"人族"的剑名称,并降序排列
mysql> SELECT id,name,atk FROM sword WHERE atk IN -> (SELECT atk FROM sword WHERE type = '人界') -> ORDER BY atk DESC; +----+--------+------+| id | name | atk |+----+--------+------+| 3 | 荆戈 | 8020 || 9 | 隐锋 | 8020 || 2 | 木藜 | 5400 |+----+--------+------+3 rows in set (0.00 sec)
创建类型分类表
建表
mysql> CREATE TABLE IF NOT EXISTS sword_type( -> type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> type_name VARCHAR(8) -> ); Query OK, 0 rows affected (0.04 sec)
用分组查询查看剑分类:
mysql> SELECT type FROM sword GROUP BY type; +--------+| type |+--------+| 人界 || 仙界 || 神界 || 道界 || 鬼界 || 魔界 |+--------+6 rows in set (0.00 sec)
将查询的结果写入另一个数据表:
根据type的分组,通过sword的type字段,插入到sword_type表中的type_name字段
mysql> INSERT sword_type (type_name) SELECT type FROM sword GROUP BY type; Query OK, 6 rows affected (0.00 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> SELECT*FROM sword_type; +---------+-----------+| type_id | type_name | +---------+-----------+ | 1 | 人界 || 2 | 仙界 | | 3 | 神界 || 4 | 道界 | | 5 | 鬼界 || 6 | 魔界 | +---------+-----------+ 6 rows in set (0.00 sec)
使用type_id更新sword中的数据
mysql> UPDATE sword INNER JOIN sword_type -> ON type = type_name -> SET type = type_id ; Query OK, 8 rows affected (0.00 sec) Rows matched: 8 Changed: 8 Warnings: 0mysql> SELECT*FROM sword; +----+--------+-------+------+-------+------+------+| id | name | atk | hit | crit | attr | type |+----+--------+-------+------+-------+------+------+| 1 | 黑风 | 10800 | 400 | 400 | 木 | 3 || 2 | 木藜 | 5400 | 200 | 200 | 木 | 1 || 3 | 荆戈 | 8020 | 1000 | 10 | 金 | 6 || 4 | 痕兮 | 8998 | 800 | 999 | 水 | 4 || 5 | 逐暮 | 100 | 1000 | 10000 | 火 | 5 || 6 | 风跃 | 9020 | 10 | 10 | 木 | 2 || 8 | 洛神 | 20020 | 1 | 10 | 金 | 3 || 9 | 隐锋 | 8020 | 2000 | 10 | 土 | 1 |+----+--------+-------+------+-------+------+------+8 rows in set (0.00 sec)
通过CREATE...SELECT来创建数据表并且同时写入记录
此时字段值必须为sword中的attr
mysql> CREATE TABLE sword_attr ( -> attr_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> attr VARCHAR(4) NOT NULL -> ) SELECT attr FROM sword GROUP BY attr; Query OK, 5 rows affected (0.47 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> SELECT*FROM sword_attr; +---------+------+| attr_id | attr | +---------+------+ | 1 | 土 || 2 | 木 | | 3 | 水 || 4 | 火 | | 5 | 金 |+---------+------+5 rows in set (0.00 sec)
如果两表字段相同,这样会报错,需具体指定字段属于哪张表
错误:
mysql> UPDATE sword INNER JOIN sword_attr -> ON attr = attr -> SET attr = attr_id; ERROR 1052 (23000): Column 'attr' in field list is ambiguous
正确
mysql> UPDATE sword AS s INNER JOIN sword_attr AS a -> ON s.attr = a.attr -> SET s.attr = a.attr_id; Query OK, 8 rows affected (0.00 sec) Rows matched: 8 Changed: 8 Warnings: 0mysql> SELECT*FROM sword; +----+--------+-------+------+-------+------+------+| id | name | atk | hit | crit | attr | type |+----+--------+-------+------+-------+------+------+| 1 | 黑风 | 10800 | 400 | 400 | 2 | 3 || 2 | 木藜 | 5400 | 200 | 200 | 2 | 1 || 3 | 荆戈 | 8020 | 1000 | 10 | 5 | 6 || 4 | 痕兮 | 8998 | 800 | 999 | 3 | 4 || 5 | 逐暮 | 100 | 1000 | 10000 | 4 | 5 || 6 | 风跃 | 9020 | 10 | 10 | 2 | 2 || 8 | 洛神 | 20020 | 1 | 10 | 5 | 3 || 9 | 隐锋 | 8020 | 2000 | 10 | 1 | 1 |+----+--------+-------+------+-------+------+------+8 rows in set (0.00 sec)
查看一下表信息:
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 | || attr | varchar(4) | NO | | NULL | | | type | varchar(8) | NO | | NULL | |+-------+----------------------+------+-----+---------+----------------+7 rows in set (0.00 sec)
此时可以改attr和type的类型为数字,
mysql> ALTER TABLE sword -> CHANGE type type_id SMALLINT UNSIGNED NOT NULL, -> CHANGE attr attr_id SMALLINT UNSIGNED NOT NULL; Query OK, 9 rows affected (0.05 sec)Records: 9 Duplicates: 0 Warnings: 0mysql> SELECT*FROM sword; +----+--------+-------+------+-------+---------+---------+| id | name | atk | hit | crit | attr_id | type_id |+----+--------+-------+------+-------+---------+---------+| 1 | 黑风 | 10800 | 400 | 400 | 2 | 3 || 2 | 木藜 | 5400 | 200 | 200 | 2 | 1 || 3 | 荆戈 | 8020 | 1000 | 10 | 5 | 6 || 4 | 痕兮 | 8998 | 800 | 999 | 3 | 4 || 5 | 逐暮 | 100 | 1000 | 10000 | 4 | 5 || 6 | 风跃 | 9020 | 10 | 10 | 2 | 2 || 8 | 洛神 | 20020 | 1 | 10 | 5 | 3 || 9 | 隐锋 | 8020 | 2000 | 10 | 1 | 1 |+----+--------+-------+------+-------+---------+---------+9 rows in set (0.01 sec) 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 | || attr_id | smallint(5) unsigned | NO | | NULL | | | type_id | smallint(5) unsigned | NO | | NULL | |+---------+----------------------+------+-----+---------+----------------+7 rows in set (0.00 sec)
这样表中两列重复汉字都用数字等价替换,减少表的体积。
表连接
添加两条类型
mysql> INSERT sword_type(type_name) VALUES('兽界'),('佛界'); Query OK, 2 rows affected (0.42 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> SELECT*FROM sword_type; +---------+-----------+| type_id | type_name | +---------+-----------+ | 1 | 人界 || 2 | 仙界 | | 3 | 神界 || 4 | 道界 | | 5 | 鬼界 || 6 | 魔界 | | 7 | 兽界 || 8 | 佛界 | +---------+-----------+ 8 rows in set (0.00 sec)
添加两条属性
mysql> INSERT sword_attr(attr) VALUES('光'),('暗'); Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> SELECT*FROM sword_attr; +---------+------+| attr_id | attr | +---------+------+ | 1 | 土 || 2 | 木 | | 3 | 水 || 4 | 火 | | 5 | 金 || 6 | 光 | | 7 | 暗 |+---------+------+7 rows in set (0.01 sec)
插入一条数据:弑神(attr_id越界)
mysql> INSERT sword VALUES(NULL,'弑神',12000,100,100,10,6); Query OK, 1 row affected (0.00 sec) mysql> SELECT*FROM sword; +----+--------+-------+------+-------+---------+---------+| id | name | atk | hit | crit | attr_id | type_id |+----+--------+-------+------+-------+---------+---------+| 1 | 黑风 | 10800 | 400 | 400 | 2 | 3 || 2 | 木藜 | 5400 | 200 | 200 | 2 | 1 || 3 | 荆戈 | 8020 | 1000 | 10 | 5 | 6 || 4 | 痕兮 | 8998 | 800 | 999 | 3 | 4 || 5 | 逐暮 | 100 | 1000 | 10000 | 4 | 5 || 6 | 风跃 | 9020 | 10 | 10 | 2 | 2 || 8 | 洛神 | 20020 | 1 | 10 | 5 | 3 || 9 | 隐锋 | 8020 | 2000 | 10 | 1 | 1 || 10 | 弑神 | 12000 | 100 | 100 | 10| 6 |+----+--------+-------+------+-------+---------+---------+10 rows in set (0.00 sec)
INNER JOIN
查询所有剑的详细信息(通过内连接实现:只呈现正确连接的记录)
由于弑神的attr_id越界,连接不正确,故无法查出
mysql> SELECT id,name,atk,hit,type_name,attr FROM sword AS s -> INNER JOIN sword_type AS t ON s.type_id = t.type_id -> INNER JOIN sword_attr AS a ON s.attr_id = a.attr_id; +----+--------+-------+------+-----------+------+| id | name | atk | hit | type_name | attr | +----+--------+-------+------+-----------+------+ | 1 | 黑风 | 10800 | 400 | 神界 | 木 || 2 | 木藜 | 5400 | 200 | 人界 | 木 | | 3 | 荆戈 | 8020 | 1000 | 魔界 | 金 || 4 | 痕兮 | 8998 | 800 | 道界 | 水 | | 5 | 逐暮 | 100 | 1000 | 鬼界 | 火 || 6 | 风跃 | 9020 | 10 | 仙界 | 木 | | 8 | 洛神 | 20020 | 1 | 神界 | 金 || 9 | 隐锋 | 8020 | 2000 | 人界 | 土 | +----+--------+-------+------+-----------+------+ 8 rows in set (0.00 sec)
作者:张风捷特烈
链接:https://www.jianshu.com/p/9cfa4eb85b5c
点击查看更多内容
为 TA 点赞
评论
共同学习,写下你的评论
评论加载中...
作者其他优质文章
正在加载中
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦