mysql数据库教程内连接
mysql数据库教程内连接规则：返回两个表的公共记录语法：-- 语法一
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
-- 语法二
select * from 表1，表2 where 表1.公共字段=表2.公共字段
例题
-- inner join
mysql> select * from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno;
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
| stuNo| stuName| stuSex | stuAge | stuSeat | stuAddress | examNo| stuNo| writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | s25303 | 80 | 58 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | s25302 | 50 | 90 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | s25304 | 65 | 50 |
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | s25301 | 77 | 82 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | s25318 | 56 | 48 |
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
5 rows in set (0.00 sec)
-- 相同的字段只显示一次
mysql> select s.stuno,stuname,stusex,writtenexam,labexam from stuinfo s inner join stumarks m on s.stuno=m.stuno;
+--------+----------+--------+-------------+---------+
| stuno| stuname| stusex | writtenexam | labexam |
+--------+----------+--------+-------------+---------+
| s25303 | 李斯文 | 女 | 80 | 58 |
| s25302 | 李文才 | 男 | 50 | 90 |
| s25304 | 欧阳俊雄 | 男 | 65 | 50 |
| s25301 | 张秋丽 | 男 | 77 | 82 |
| s25318 | 争青小子 | 男 | 56 | 48 |
+--------+----------+--------+-------------+---------+
5 rows in set (0.00 sec)
-- 使用where
mysql> select * from stuinfo,stumarks where stuinfo.stuno=stumarks.stuno;
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
| stuNo| stuName| stuSex | stuAge | stuSeat | stuAddress | examNo| stuNo| writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | s25303 | 80 | 58 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | s25302 | 50 | 90 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | s25304 | 65 | 50 |
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | s25301 | 77 | 82 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | s25318 | 56 | 48 |
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
5 rows in set (0.00 sec)多学一招：
-- 1、内连接中inner可以省略
select * from 表1join 表2 on 表1.公共字段=表2.公共字段
mysql> select * from stuinfo join stumarks on stuinfo.stuno=stumarks.stuno;
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
| stuNo| stuName| stuSex | stuAge | stuSeat | stuAddress | examNo| stuNo| writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | s25303 | 80 | 58 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | s25302 | 50 | 90 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | s25304 | 65 | 50 |
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | s25301 | 77 | 82 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | s25318 | 56 | 48 |
+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+
5 rows in set (0.00 sec)
-- 如何实现三表查询
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 inner join 表3 on 表2.公共字段=表3.公共字段
-- 表连接越多，效率越低思考：
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
和
select * from 表2 inner join 表1 on 表1.公共字段=表2.公共字段 一样吗？
答：一样的
页:
[1]