1 :any的用法
表结构如下:(如果表没有关系,这个也就没用处)
班级表:
学生表
=any相当于in语句 相等的意思
select * from classstu where class_Id=any(select class_id from student);
+----------+------------+
| class_Id | class_name |
+----------+------------+
| 1 | 1232 |
| 2 | 一年级 |
+----------+------------+
2 rows in set
>any 大于any的意思就是大于等于最大的
select * from classstu where class_Id>any(select class_id from student);
+----------+------------+
| class_Id | class_name |
+----------+------------+
| 2 | 一年级 |
| 3 | 二年级 |
+----------+------------+
2 rows in set
mysql>
<any的意思小于等于最小的包含的意思
select * from classstu where class_Id<any(select class_id from student);
+----------+------------+
| class_Id | class_name |
+----------+------------+
| 1 | 1232 |
+----------+------------+
1 row in set
第二点:join的用法
inner join 内连接 跟join的用法类似.将多张表符合条件的都列出来
select * from classstu join student using(class_Id);
+----------+------------+-----------+---------+-----+
| class_Id | class_name | studentID | name | age |
+----------+------------+-----------+---------+-----+
| 1 | 1232 | 1 | 一班059 | 25 |
| 1 | 1232 | 2 | 一班059 | 25 |
| 1 | 1232 | 3 | 一班059 | 25 |
| 1 | 1232 | 4 | 一班059 | 25 |
| 1 | 1232 | 5 | 一班059 | 25 |
| 2 | 一年级 | 6 | 二班059 | 25 |
| 2 | 一年级 | 7 | 二班059 | 25 |
| 2 | 一年级 | 8 | 二班059 | 25 |
+----------+------------+-----------+---------+-----+
2:left join on的用法
select * from classstu left
join student using(class_Id);
+----------+------------+-----------+---------+------+
| class_Id | class_name | studentID | name | age |
+----------+------------+-----------+---------+------+
| 1 | 1232 | 1 | 一班059 | 25 |
| 1 | 1232 | 2 | 一班059 | 25 |
| 1 | 1232 | 3 | 一班059 | 25 |
| 1 | 1232 | 4 | 一班059 | 25 |
| 1 | 1232 | 5 | 一班059 | 25 |
| 2 | 一年级 | 6 | 二班059 | 25 |
| 2 | 一年级 | 7 | 二班059 | 25 |
| 2 | 一年级 | 8 | 二班059 | 25 |
| 3 | 二年级 | NULL | NULL | NULL |
他的意思是将 前者的表中的数据全部列出 后者必须满足条件
3 right join on的用法 是依赖于后者. 必须满足后者的条件
select * from classstu right
join student using(class_Id);
+----------+-----------+---------+-----+------------+
| class_id | studentID | name | age | class_name |
+----------+-----------+---------+-----+------------+
| 1 | 1 | 一班059 | 25 | 1232 |
| 1 | 2 | 一班059 | 25 | 1232 |
| 1 | 3 | 一班059 | 25 | 1232 |
| 1 | 4 | 一班059 | 25 | 1232 |
| 1 | 5 | 一班059 | 25 | 1232 |
| 2 | 6 | 二班059 | 25 | 一年级 |
| 2 | 7 | 二班059 | 25 | 一年级 |
| 2 | 8 | 二班059 | 25 | 一年级 |
+----------+-----------+---------+-----+------------+
8 rows in set