MySQL_04多表

create table aa(id int(1) PRIMARY key, name char(20));
create table cc(s_id int(1) PRIMARY key, score char(20));
insert into aa(id,name)values(1001,'zhangsan'),(1002,'lisi');
insert into cc(s_id,score)values(1001,'99');

select * from aa;
select * from cc;

1、基本连接：2个表有相同字段的值则可以连接（相同字段的值连接在一起，没有相同的部分被舍弃）

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

2、内连接：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;

4、右连接：以右表为准，左表为辅（如果右表有的数据左边没有自动补充null）

select * from aa right JOIN cc on aa.id=cc.s_id;
select * from cc right JOIN aa on aa.id=cc.s_id;

5、硬链接：机械追加----强行连接（2个表字段数量必须是相同的）

select * from aa union select * from cc;

 

一：求张三的成绩

1、临时表方法：

select * from aa,cc where aa.id=cc.s_id;   ===》临时表
select t.score from (select * from aa,cc where aa.id=cc.s_id) t where t.name='zhangsan';

2、子查询（嵌套）

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

3、连接查询

select score from aa,cc where aa.id=cc.s_id and name='zhangsan';

 

二：求没有参加考试的学生的姓名

1、左连接方法

select * from aa LEFT JOIN cc on aa.id=cc.s_id;   ===》临时表
select a.name from (select * from aa LEFT JOIN cc on aa.id=cc.s_id) a where a.score is null;

2、右连接方法

select * from cc right JOIN aa on aa.id=cc.s_id;
select a.name from (select * from cc right JOIN aa on aa.id=cc.s_id) a where a.score is null;

3、子查询方法

select id from aa,cc where aa.id=cc.s_id;
select name from aa where id not in (select id from aa,cc where aa.id=cc.s_id);
select name from aa where id not in (select s_id from cc);

4、连接查询

select name from  aa LEFT JOIN cc on aa.id=cc.s_id where score is null;