MySQl多表查询

加粗文本

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

create table Stu(
id int primary key auto_increment,
name varchar(20),
sex enum('m','f') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into Class values(302,'linux'),(303,'python'),(304,'java');


insert into Stu(name,sex,age,dep_id) values('zhang','m',18,303),('li','f',48,304),('tom','m',38,203),('yuanhao','f',28,302),('may','m',18,300),('wang','f',18,304);


#查看表
mysql> select * from Stu;
+----+-----------+--------+------+--------+
| id | name      | sex    | age  | dep_id |
+----+-----------+--------+------+--------+
|  1 | zhang     | m      |   18 |    303 |
|  2 | li        | f      |   22 |    304 |
|  3 | tom       | m      |   21 |    303 |
|  4 | yuanhao   | f      |   22 |    302 |
|  5 | may       | m      |   18 |    302 |
|  6 | wnag      | f      |   18 |    304 |
+----+-----------+--------+------+--------+
6 rows in set (0.00 sec)

mysql> select * from Class;
+------+--------------+
| id   | name         |
+------+--------------+
|  302 | linux        |
|  303 | python       |
|  304 | java         |
+------+--------------+
3 rows in set (0.00 sec)

加粗文本
交叉连接

mysql> select * from Class ,Stu;

内连接

mysql> select * from Stu  inner join Class on
Stu.dep_id=Class.id and Stu.age > 20 ;

左连接left

mysql> select Stu.name,Stu.age,S.sex,Class.id,Class.name
from Stu left join Class on Stu.dep_id=Class.id;

右连接right

mysql> select * from Stu right  join Class onStu.dep_id=Class.id ;

子查询

   子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
   还可以包含比较运算符：= 、 !=、> 、<等

mysql> select name from Class where id in ( select dep_id from Stu group by dep_id having avg(age) > 25 );