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: 0
mysql> ALTER TABLE sword ADD type VARCHAR(8) NOT NULL;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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: 0
mysql> UPDATE sword SET attr='木',type='人界' WHERE name='木藜';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> UPDATE sword SET attr='金',type='魔界' WHERE name='荆戈';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE sword SET attr='水',type='道界' WHERE name='痕兮';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE sword SET attr='火',type='鬼界' WHERE name='逐暮';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE sword SET attr='木',type='仙界' WHERE name='风跃';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE sword SET attr='金',type='神界' WHERE name='洛神';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE sword SET attr='土',type='人界' 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 | 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: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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: 0
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 |
+----+--------+-------+------+-------+---------+---------+
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: 0
mysql> 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: 0
mysql> 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)LEFT JOIN
查询所有剑的详细信息(左外连接：左表全部，右表无匹配则置空)
mysql> SELECT id,name,atk,hit,type_name,attr FROM sword AS s
-> LEFT JOIN sword_type AS t ON s.type_id = t.type_id
-> LEFT 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 | 人界 | 土 |
| 10 | 弑神 | 12000 | 100 | 魔界 | NULL |
+----+--------+-------+------+-----------+------+
9 rows in set (0.00 sec)RIGHT JOIN
查询所有剑的详细信息(左外连接：右表全部，左表无匹配则置空)
mysql> SELECT id,name,atk,hit,type_name,attr FROM sword AS s
-> RIGHT JOIN sword_type AS t ON s.type_id = t.type_id
-> RIGHT JOIN sword_attr AS a ON s.attr_id = a.attr_id;
+------+--------+-------+------+-----------+------+
| id | name | atk | hit | type_name | attr |
+------+--------+-------+------+-----------+------+
| 9 | 隐锋 | 8020 | 2000 | 人界 | 土 |
| 1 | 黑风 | 10800 | 400 | 神界 | 木 |
| 2 | 木藜 | 5400 | 200 | 人界 | 木 |
| 6 | 风跃 | 9020 | 10 | 仙界 | 木 |
| 4 | 痕兮 | 8998 | 800 | 道界 | 水 |
| 5 | 逐暮 | 100 | 1000 | 鬼界 | 火 |
| 3 | 荆戈 | 8020 | 1000 | 魔界 | 金 |
| 8 | 洛神 | 20020 | 1 | 神界 | 金 |
| NULL | NULL | NULL | NULL | NULL | 光 |
| NULL | NULL | NULL | NULL | NULL | 暗 |
+------+--------+-------+------+-----------+------+
10 rows in set (0.00 sec)