mysql数据库多表

insert into aa(id,name)values(1001,'zhangsan'),(1002,'lisi');
insert into cc(s_id,score)values(1001,'99');
insert into bb(b_id,sex)values(1001,'男'),(1002,'女');
select * from aa;
select * from cc;
select * from bb;

# 多表连接
# 1、基本连接
# 对于两个表当中有字段值相同可以通过基本连接进行拼接。
# 这里的点‘.’表示里面

select * from aa,cc where aa.id = cc.s_id;

# 2、内连接
# 对于两个表当中有字段值相同可以通过内连接进行拼接。

SELECT * from aa inner join cc on aa.id = cc.s_id;

# 3、左连接
# 以左边的表为主，对于不符合的数据展示为null，或者不展示

select * from aa left join cc on aa.id = cc.s_id;

# 三表连接

select * from cc left join aa on aa.id = cc.s_id left join bb on aa.id=bb.b_id;

# 4、右连接
# 以右边的表为主，对于不符合的数据展示为null，或者不展示

SELECT * from aa right join cc on aa.id = cc.s_id;

# 三表连接

SELECT * from aa right join bb on aa.id = bb.b_id right join cc on bb.b_id = cc.s_id;

# 5、硬连接
# 机械链接 需要左边表和右边的表数据字段一致

SELECT * from aa union select * from cc;

# 6、临时表
-- 求张三的成绩

select * from aa,cc where aa.id=cc.s_id; 
 
select score from (select * from aa,cc where aa.id=cc.s_id)a where a.name ='zhangsan';

# 7、嵌套 = 

select id from aa where name = 'zhangsan';

select score from cc where s_id = (select id from aa where name = 'zhangsan');

# 8、 嵌套in

select score from cc where s_id in (select id from aa where name = 'zhangsan');