SQL语句高级（十一）——等级查询、连接和full join_sql查询数据属于哪个级别

一、按等级查询
mysql> create table grade(
    -> low int(3),
    -> upp int(3),
    -> grade char(1)
    -> );
Query OK, 0 rows affected (0.87 sec)

mysql> insert into grade values(90,100,'A');
Query OK, 1 row affected (0.21 sec)

mysql> insert into grade values(80,89,'B');
Query OK, 1 row affected (0.11 sec)

mysql> insert into grade values(70,79,'C');
Query OK, 1 row affected (0.09 sec)

mysql> insert into grade values(60,69,'D');
Query OK, 1 row affected (0.11 sec)

mysql> insert into grade values(0,59,'E');
Query OK, 1 row affected (0.10 sec)

mysql> select * from grade;
+------+------+-------+
| low  | upp  | grade |
+------+------+-------+
|   90 |  100 | A     |
|   80 |   89 | B     |
|   70 |   79 | C     |
|   60 |   69 | D     |
|    0 |   59 | E     |
+------+------+-------+
5 rows in set (0.00 sec)

mysql> select sno,cno,grade from score,grade
    -> where degree between low and upp; --太智能了吧！！
+-----+--------+-------+
| sno | cno    | grade |
+-----+--------+-------+
| 100 | 3-105  | C     |
| 100 | 3-245  | C     |
| 101 | 6-1166 | E     |
| 102 | 3-245  | A     |
| 103 | 3-105  | B     |
| 103 | 3-245  | B     |
| 103 | 6-1166 | E     |
| 104 | 3-105  | E     |
| 104 | 3-245  | A     |
| 104 | 6-1166 | B     |
| 105 | 3-245  | B     |
| 105 | 6-1166 | B     |
+-----+--------+-------+

二、连接查询（内连接、左连接、右连接）
内连接
inner join 或者 join

外连接
1、左连接 left join 或者 left outer join

2、右连接 right join 或者 right outer join

3、完全外连接 full join 或者 full outer join

创建两个表：
create database testJoin;

person表
id，name，cardid

create table person(
	id int,
	name varchar(20),
	cardId int
);

card表
id，name

create table card(
	id int,
	name varchar(20)
);

insert into card values(1,'fanka');
insert into card values(2,'jianhangka');
insert into card values(3,'nonghangka');
insert into card values(4,'gongshangka');
insert into card values(5,'youzhengka');

insert into person values(1,'zhangsan',1);
insert into person values(2,'lisi',3);
insert into person values(3,'wangwu',6);

mysql> select * from card;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | fanka       |
|    2 | jianhangka  |
|    3 | nonghangka  |
|    4 | gongshangka |
|    5 | youzhengka  |
+------+-------------+

mysql> select * from person;
+------+----------+--------+
| id   | name     | cardId |
+------+----------+--------+
|    1 | zhangsan |      1 |
|    2 | lisi     |      3 |
|    3 | wangwu   |      6 |
+------+----------+--------+

目前并没有创建外键！

——inner join 查询（查找有关系的数据！）
select * from person inner join card on person.cardId=card.id;--注意语法！
+------+----------+--------+------+------------+
| id   | name     | cardId | id   | name       |
+------+----------+--------+------+------------+
|    1 | zhangsan |      1 |    1 | fanka      |
|    2 | lisi     |      3 |    3 | nonghangka |
+------+----------+--------+------+------------+
内联查询，其实就是两张表中的数据，通过某个字段相等，查询出相关记录数据。
或者省略inner：
select * from person join card on person.cardId=card.id;--注意语法！
+------+----------+--------+------+------------+
| id   | name     | cardId | id   | name       |
+------+----------+--------+------+------------+
|    1 | zhangsan |      1 |    1 | fanka      |
|    2 | lisi     |      3 |    3 | nonghangka |
+------+----------+--------+------+------------+

——left join 查询(左外连接)
select * from person left join card on person.cardId=card.id;
+------+----------+--------+------+------------+
| id   | name     | cardId | id   | name       |
+------+----------+--------+------+------------+
|    1 | zhangsan |      1 |    1 | fanka      |
|    2 | lisi     |      3 |    3 | nonghangka |
|    3 | wangwu   |      6 | NULL | NULL       |
+------+----------+--------+------+------------+
左外连接，会把左边表里面的所有数据取出来，而右边表中的数据，如果有相等的，就显示出来
如果没有，就会补 NULL。

select * from person left outer join card on person.cardId=card.id;
+------+----------+--------+------+------------+
| id   | name     | cardId | id   | name       |
+------+----------+--------+------+------------+
|    1 | zhangsan |      1 |    1 | fanka      |
|    2 | lisi     |      3 |    3 | nonghangka |
|    3 | wangwu   |      6 | NULL | NULL       |
+------+----------+--------+------+------------+

——right join 查询（右外连接）
select * from person right join card on person.cardId=card.id;
+------+----------+--------+------+-------------+
| id   | name     | cardId | id   | name        |
+------+----------+--------+------+-------------+
|    1 | zhangsan |      1 |    1 | fanka       |
|    2 | lisi     |      3 |    3 | nonghangka  |
| NULL | NULL     |   NULL |    2 | jianhangka  |
| NULL | NULL     |   NULL |    4 | gongshangka |
| NULL | NULL     |   NULL |    5 | youzhengka  |
+------+----------+--------+------+-------------+
右外连接，会把右边表里面的所有数据取出来，而左边表中的数据，如果有相等的，就显示出来
如果没有，就会补 NULL。
或者多个outer：
select * from person right outer join card on person.cardId=card.id;
+------+----------+--------+------+-------------+
| id   | name     | cardId | id   | name        |
+------+----------+--------+------+-------------+
|    1 | zhangsan |      1 |    1 | fanka       |
|    2 | lisi     |      3 |    3 | nonghangka  |
| NULL | NULL     |   NULL |    2 | jianhangka  |
| NULL | NULL     |   NULL |    4 | gongshangka |
| NULL | NULL     |   NULL |    5 | youzhengka  |
+------+----------+--------+------+-------------+

—— full join 查询（全外连接）——但是mysql不支持full join！！！！
select * from person full join card on person.cardId=card.id;

三、mysql为什么不支持 full join 全外连接？

inner join 其实是两表的交集
left join 是左表并交集
right join 是右表并交集
full join  全集：用union将 left join 和 right join 联合起来！
即是：
mysql> select * from person right outer join card on person.cardId=card.id
    -> union
    -> select * from person left outer join card on person.cardId=card.id;
+------+----------+--------+------+-------------+
| id   | name     | cardId | id   | name        |
+------+----------+--------+------+-------------+
|    1 | zhangsan |      1 |    1 | fanka       |
|    2 | lisi     |      3 |    3 | nonghangka  |
| NULL | NULL     |   NULL |    2 | jianhangka  |
| NULL | NULL     |   NULL |    4 | gongshangka |
| NULL | NULL     |   NULL |    5 | youzhengka  |
|    3 | wangwu   |      6 | NULL | NULL        |
+------+----------+--------+------+-------------+
6 rows in set (0.47 sec)
或者：
mysql> select * from person left outer join card on person.cardId=card.id
    -> union
    -> select * from person right outer join card on person.cardId=card.id;
+------+----------+--------+------+-------------+
| id   | name     | cardId | id   | name        |
+------+----------+--------+------+-------------+
|    1 | zhangsan |      1 |    1 | fanka       |
|    2 | lisi     |      3 |    3 | nonghangka  |
|    3 | wangwu   |      6 | NULL | NULL        |
| NULL | NULL     |   NULL |    2 | jianhangka  |
| NULL | NULL     |   NULL |    4 | gongshangka |
| NULL | NULL     |   NULL |    5 | youzhengka  |
+------+----------+--------+------+-------------+




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223