匿名通过本文主要向大家介绍了mysql,实例,实现等相关知识,希望本文的分享对您有所帮助
开始之前先说点有趣的事：经常听说没有主键就无法进行多表查询，其实这太过于片面了。实际上有些情况没有主键也是可以进行多表查询的（例一，所用到的表格也都是下面的表格，先来个4表查询热热身）。
mysql> select * from student,teacher,sc,course where (student.s=sc.s and teacher.t=course.t) and course.class=sc.class; +------+--------+------+------+------+--------+------+-------+-------+-------+--------+------+ | s | sname | sage | ssex | t | tname | s | class | score | class | cname | t | +------+--------+------+------+------+--------+------+-------+-------+-------+--------+------+ | 1 | 刘一 | 18 | 男 | 1 | 叶平 | 1 | 1 | 56 | 1 | 语文 | 1 | | 1 | 刘一 | 18 | 男 | 2 | 贺高 | 1 | 2 | 78 | 2 | 数学 | 2 | | 1 | 刘一 | 18 | 男 | 3 | 杨艳 | 1 | 3 | 67 | 3 | 英语 | 3 | | 1 | 刘一 | 18 | 男 | 4 | 周磊 | 1 | 4 | 58 | 4 | 物理 | 4 | | 2 | 钱二 | 19 | 女 | 1 | 叶平 | 2 | 1 | 79 | 1 | 语文 | 1 | | 2 | 钱二 | 19 | 女 | 2 | 贺高 | 2 | 2 | 81 | 2 | 数学 | 2 | | 2 | 钱二 | 19 | 女 | 3 | 杨艳 | 2 | 3 | 92 | 3 | 英语 | 3 | | 2 | 钱二 | 19 | 女 | 4 | 周磊 | 2 | 4 | 68 | 4 | 物理 | 4 | | 3 | 张三 | 17 | 男 | 1 | 叶平 | 3 | 1 | 91 | 1 | 语文 | 1 | | 3 | 张三 | 17 | 男 | 2 | 贺高 | 3 | 2 | 47 | 2 | 数学 | 2 | | 3 | 张三 | 17 | 男 | 3 | 杨艳 | 3 | 3 | 88 | 3 | 英语 | 3 | | 3 | 张三 | 17 | 男 | 4 | 周磊 | 3 | 4 | 56 | 4 | 物理 | 4 | | 4 | 李四 | 18 | 女 | 2 | 贺高 | 4 | 2 | 88 | 2 | 数学 | 2 | | 4 | 李四 | 18 | 女 | 3 | 杨艳 | 4 | 3 | 90 | 3 | 英语 | 3 | | 4 | 李四 | 18 | 女 | 4 | 周磊 | 4 | 4 | 93 | 4 | 物理 | 4 | | 5 | 王五 | 17 | 男 | 1 | 叶平 | 5 | 1 | 46 | 1 | 语文 | 1 | | 5 | 王五 | 17 | 男 | 3 | 杨艳 | 5 | 3 | 78 | 3 | 英语 | 3 | | 5 | 王五 | 17 | 男 | 4 | 周磊 | 5 | 4 | 53 | 4 | 物理 | 4 | | 6 | 赵六 | 19 | 女 | 1 | 叶平 | 6 | 1 | 35 | 1 | 语文 | 1 | | 6 | 赵六 | 19 | 女 | 2 | 贺高 | 6 | 2 | 68 | 2 | 数学 | 2 | | 6 | 赵六 | 19 | 女 | 4 | 周磊 | 6 | 4 | 71 | 4 | 物理 | 4 | +------+--------+------+------+------+--------+------+-------+-------+-------+--------+------+ 21 rows in set (0.05 second)
话不多说，先让我们来看看，接下来要用到的七个表吧：
mysql> select * from sc; +------+-------+-------+ | s | class | score | +------+-------+-------+ | 1 | 1 | 56 | | 1 | 2 | 78 | | 1 | 3 | 67 | | 1 | 4 | 58 | | 2 | 1 | 79 | | 2 | 2 | 81 | | 2 | 3 | 92 | | 2 | 4 | 68 | | 3 | 1 | 91 | | 3 | 2 | 47 | | 3 | 3 | 88 | | 3 | 4 | 56 | | 4 | 2 | 88 | | 4 | 3 | 90 | | 4 | 4 | 93 | | 5 | 1 | 46 | | 5 | 3 | 78 | | 5 | 4 | 53 | | 6 | 1 | 35 | | 6 | 2 | 68 | | 6 | 4 | 71 | +------+-------+-------+ 21 rows in set (0.00 sec)
mysql> desc sc; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | s | int(11) | YES | | NULL | | | class | int(12) | YES | | NULL | | | score | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql> select * from student; +------+--------+------+------+ | s | sname | sage | ssex | +------+--------+------+------+ | 1 | 刘一 | 18 | 男 | | 2 | 钱二 | 19 | 女 | | 3 | 张三 | 17 | 男 | | 4 | 李四 | 18 | 女 | | 5 | 王五 | 17 | 男 | | 6 | 赵六 | 19 | 女 | +------+--------+------+------+ 6 rows in set (0.00 sec)
ysql> desc student; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | s | int(11) | YES | | NULL | | | sname | char(32) | YES | | NULL | | | sage | int(11) | YES | | NULL | | | ssex | char(8) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 4 rows in set (0.02 sec)
mysql> select * from course; +-------+--------+------+ | class | cname | t | +-------+--------+------+ | 1 | 语文 | 1 | | 2 | 数学 | 2 | | 3 | 英语 | 3 | | 4 | 物理 | 4 | +-------+--------+------+ 4 rows in set (0.00 sec)
mysql> desc course; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | class | int(12) | YES | | NULL | | | cname | char(32) | YES | | NULL | | | t | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql> select * from teacher; +------+--------+ | t | tname | +------+--------+ | 1 | 叶平 | | 2 | 贺高 | | 3 | 杨艳 | | 4 | 周磊 | +------+--------+ 4 rows in set (0.00 sec)
mysql> desc teacher; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | t | int(11) | YES | | NULL | | | tname | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
mysql> select * from cadd; +--------+------+ | cadd | s | +--------+------+ | 上海 | 3 | | 广西 | 6 | | 江西 | 5 | | 深圳 | 2 | | 湖南 | 4 | | 福建 | 1 | +--------+------+ 6 rows in set (0.02 sec)
mysql> desc cadd; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | cadd | char(22) | NO | PRI | NULL | | | s | int(2) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
mysql> select * from tadd; +--------+------+ | tadd | s | +--------+------+ | 福建 | 1 | | 深圳 | 2 | | 上海 | 3 | | 湖南 | 4 | | 江西 | 5 | | 广西 | 6 | +--------+------+ 6 rows in set (0.00 sec)
mysql> desc tadd; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | tadd | char(21) | YES | | NULL | | | s | int(12) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
mysql> select * from cphone; +--------+------+ | cphone | s | +--------+------+ | 12345 | 1 | | 12346 | 2 | | 12347 |