MySQL数据库之多表查询

外键约束

内连接(inner join)：查询在两个表中都存在的数据，相当于查询两个表的交集

外连接：

        左外连接(left join)：以左表为主，左表的数据全部列出，右表中不存在的数据显示为null；

        右外连接(right join)：以右表为主，右表的数据全部列出，左表中不存在的数据显示为null；

        全外连接(full join)：查询在左边或右表中存在的数据，相当于查找两个表的并集，mysql中不支持全连接

        总结：左右连接可以互相转换，习惯上使用左连接

sql--示例

1 SELECT * from score;
2 SELECT * from students;
3
4 #笛卡尔积，两个表的乘积，不具有实际意义
5 SELECT stu_id,name,SUBJECT,sc from score,students;
6
7 SELECT stu_id,name,SUBJECT,sc from score,students
8 where score.stu_id=students.xuhao;
9
10 SELECT * from score,students WHERE score.stu_id=students.xuhao;
11
12 /*内连接：查询两个表中都存在的数据，
13 也就是关联字段的值同时存在于两个表
14 语法：
15 select 列名 from 表1
16 inner join 表2
17 on 表1.列名=表2.列名
18 两个表之间关联的列通常时主外键的关系
19 */
20 SELECT * from score
21 INNER JOIN students
22 on score.stu_id=students.xuhao;
23
24 #查询名字为张三的学生的学号、姓名、性别、科目、成绩
25 SELECT students.xuhao,students.name,students.sex,score.SUBJECT,score.sc 
26 from score
27 INNER JOIN students
28 on score.stu_id=students.xuhao
29 WHERE name='张三';
30
31 #另外一种写法：给表起别名，as可以省略不写，其他筛选条件换成用and
32 SELECT stu.xuhao,stu.name,stu.sex,s.SUBJECT,s.sc 
33 from score s
34 INNER JOIN students as stu
35 on s.stu_id=stu.xuhao
36 and name='张三';
37
38 /*左连接：查询以左表为准的数据，
39 左表中存在的数据都显示，右表中不存在的数据显示为null
40 语法：
41 select 列名 from 表1
42 left join 表2
43 on 表1.列名=表2.列名
44 */
45 SELECT stu.xuhao,stu.name,stu.sex,s.SUBJECT,s.sc 
46 from students stu
47 left join score s
48 on stu.xuhao=s.stu_id
49
50 SELECT stu.xuhao,stu.name,stu.sex,s.SUBJECT,s.sc 
51 from score s
52 left join students stu
53 on stu.xuhao=s.stu_id
54
55 /*右连接：以右表数据为主
56 右表中的数据都显示，左边中不存在的数据显示为null
57 语法：select 列名 from 表1
58 right join 表2
59 on 表1.列名=表2.列名
60 */
61 SELECT stu.xuhao,stu.name,stu.sex,s.SUBJECT,s.sc 
62 from students stu
63 right join score s
64 on stu.xuhao=s.stu_id
65
66 SELECT stu.xuhao,stu.name,stu.sex,s.SUBJECT,s.sc 
67 from score s
68 right join students stu
69 on stu.xuhao=s.stu_id
70
71 #三表联查
72 #查询学号、姓名、性别、科目、成绩、教师编号、教师名称
73 select stu.xuhao,stu.name,stu.sex,s.subject,s.sc,t.tid,t.tname 
74 from students stu
75 inner join score s
76 on stu.xuhao=s.stu_id
77 inner join teacher t
78 on s.tid=t.tid;
79
80 #内连接可以换种写法：
81 select stu.xuhao,stu.name,stu.sex,s.subject,s.sc,t.tid,t.tname 
82 from students stu
83 inner join score s
84 inner join teacher t
85 on stu.xuhao=s.stu_id
86 and s.tid=t.tid;
87
88 #三表左连接
89 SELECT stu.xuhao,stu.name,stu.sex,s.subject,s.sc,t.tid,t.tname
90 from students stu
91 left join score s
92 on stu.xuhao=s.stu_id
93 left join teacher t
94 on s.tid=t.tid;
95
96 #三表右连接
97 SELECT stu.xuhao,stu.name,stu.sex,s.subject,s.sc,t.tid,t.tname
98 from students stu
99 right join score s
100 on stu.xuhao=s.stu_id 
101 right join teacher t
102 on s.tid=t.tid;
103
104 #查询各个科目成绩最高的学生的学号、姓名、科目、成绩、教师编号，教师名称
105 SELECT stu_id,`subject`,sc from score ORDER BY subject,sc desc;
106
107 SELECT s.subject,max(s.sc) from score s GROUP BY s.subject;
108
109 select stu_id,tid ,score.sc,score.subject
110 from score,
111 (SELECT s.subject,max(s.sc) ms from score s GROUP BY s.subject) a
112 WHERE score.sc=a.ms and score.subject=a.subject;
113
114 select stu_id,stu.name,t.tid,t.tname,score.sc,score.subject
115 from score 
116 INNER JOIN
117 (SELECT s.subject,max(s.sc) ms from score s GROUP BY s.subject) a
118 on score.sc=a.ms and score.subject=a.subject
119 INNER JOIN students stu
120 INNER JOIN teacher t
121 on score.stu_id=stu.xuhao and score.tid=t.tid
122
123 SELECT b.stu_id,stu.name,b.sc,b.subject,b.tid,t.tname
124 from
125 (select stu_id,tid ,score.sc,score.subject
126 from score,
127 (SELECT s.subject,max(s.sc) ms from score s GROUP BY s.subject) a
128 WHERE score.sc=a.ms and score.subject=a.subject) b
129 INNER JOIN students stu
130 INNER JOIN teacher t
131 on b.stu_id=stu.xuhao and b.tid=t.tid;
132
133 #查询学生的学号、姓名、总分，并取前3名
134 SELECT stu.xuhao,stu.name,a.zf 
135 from(SELECT stu_id,sum(sc) zf from score GROUP BY stu_id) a
136 INNER JOIN students stu
137 on a.stu_id=stu.xuhao 
138 ORDER BY a.zf desc limit 3;
139
140 #以学生表为主，查询所有学生的学号、姓名、总分，并按总分倒序排列
141 SELECT stu.xuhao,stu.name,a.zf 
142 from students stu
143 LEFT JOIN (SELECT stu_id,sum(sc) zf from score GROUP BY stu_id) a
144 on a.stu_id=stu.xuhao ORDER BY a.zf desc;
145
146 #查询总分最高的学生的学号、姓名、总分
147 SELECT stu.xuhao,stu.name,a.zf 
148 FROM
149 (SELECT stu_id,sum(sc) zf 
150 from score GROUP BY stu_id 
151 ORDER by SUM(sc) desc LIMIT 1
152 ) a
153 INNER JOIN students stu
154 on a.stu_id=stu.xuhao;
155
156 #查询总分比张三总分高的学生的学号、姓名、总分
157 SELECT a.id,a.zf,stu.name FROM
158 students stu
159 INNER JOIN
160 (SELECT score.stu_id id,sum(sc) zf from score 
161 GROUP BY stu_id 
162 HAVING zf>
163 (SELECT sum(sc) zf 
164 from score
165 INNER JOIN students stu
166 on score.stu_id=stu.xuhao
167 WHERE stu.name='张三'
168 GROUP BY stu_id
169 )
170 ) a
171 on stu.xuhao=a.id
172 ;
173
174 SELECT stu.xuhao,stu.name,a.zf 
175 from(SELECT stu_id,sum(sc) zf from score GROUP BY stu_id) a
176 INNER JOIN students stu
177 on a.stu_id=stu.xuhao 
178 where a.zf>
179 (SELECT sum(sc) zf 
180 from score
181 INNER JOIN students stu
182 on score.stu_id=stu.xuhao
183 WHERE stu.name='张三'
184 GROUP BY stu_id
185 );