mysql --等值连接 和 左右连接

创建表stu01 ，并插入数据

drop table if exists  stu01;
create table stu01(
studentno int(3) UNSIGNED zerofill primary key,
name VARCHAR(10),
sex VARCHAR(3),
age TINYINT ,
hometown VARCHAR(20),
class VARCHAR(10),
card VARCHAR(30)
);

insert into stu01 values 
('1','王昭君','女',20,'北京','1班','31162100000910018'),
('2','诸葛亮','男',18,'上海','2班','87235958214647020'),
('3','刘玄德', '男',24,'南京','1班','98230764940128343'),
('4','白起','男',22,'安徽','4班','72898231636783201'),
('5','大乔','女',19,'河南','3班','12872396253622358'),
('6','孙尚香','女',18,'河北','1班','32276853291927386'),
('7','百里玄策','男',20,'山西','2班','69283154237972492'),
('8','小乔','女',18,'河南','3班',NULL),
('9','百里守约','男',22,'河南','2班',''),
('10','妲己','女',19,'陕西','1班','77538912786375823'),
('11','李白','男',24,'山东','3班','13467923823989730'),
('12','刘禅','男',18,'陕西','4班','13729023873820233');

创建表stu02， 并插入数据

drop table if exists stu02;
create table stu02(
id  int primary key ,
courseno  int(2) UNSIGNED zerofill ,
studentno int(3) UNSIGNED zerofill ,
score int 
);

insert into stu02 values
(1,  1, 1, 91),
(2,  5, 2, 85),
(3,  3, 1, 74),
(4,  1, 2, 91),
(5,  4, 3, 82),
(6,  1, 4, 93),
(7,  2, 1, 91),
(8,  1, 6, 85),
(9,  5, 1, 78),
(10, 1, 5, 82),
(11, 3, 5, 83),
(12, 1, 3, 89),
(13, 4, 1, 71);

创建表stu03， 并插入数据

drop  table if exists stu03;
create table stu03(
courseno int(2) UNSIGNED zerofill primary key,
name varchar(30)
);

insert into stu03 values 
('1', '数据库'),
('2', 'linux'),
('3', '系统测试'),
('4', '单元测试'),
('5', '离散数学'),
('6', '高数'),
('7', '英语'),
('8', '物理');

等值连接

一、查询学生信息及学生成绩

-- 1.
select  * from stu01 , stu02 where stu01.studentno = stu02.studentno; 
-- 2.
select * from stu01 s1 inner join stu02 s2 on s1.studentno = s2.studentno;

 二、查询课程信息及课程成绩

-- 1.
select * from stu02,stu03 where stu02.courseno = stu03.courseno;
-- 2.
select * from stu02 inner join stu03 on stu02.courseno = stu03.courseno;

三、查询学生信息及学生的课程对应的成绩

-- 1.
select * from stu01,stu02,stu03 where stu01.studentno = stu02.studentno and stu02.courseno = stu03.courseno;
-- 2.
select * from stu01 INNER JOIN stu02 on stu01.studentno = stu02.studentno INNER JOIN stu03 on stu02.courseno = stu03.courseno;

四、查询王昭君的成绩，要求显示姓名、课程号、成绩 

-- 1.
select stu01.name,stu03.name, stu02.score from stu01,stu02,stu03 where stu01.studentno = stu02.studentno and stu02.courseno = stu03.courseno  and  stu01.name='王昭君';
-- 2.
select stu01.name,stu03.name, stu02.score  from stu01 INNER JOIN stu02 on stu01.studentno = stu02.studentno INNER JOIN stu03 on stu02.courseno = stu03.courseno where stu01.name='王昭君';

五、查询王昭君的数据库成绩，要求显示姓名、课程号、成绩

-- 1.
select stu01.name,stu03.name, stu02.score from stu01,stu02,stu03 where stu01.studentno = stu02.studentno and stu02.courseno = stu03.courseno  and  stu01.name='王昭君'and stu03.name = '数据库';
-- 2.
select stu01.name,stu03.name, stu02.score  from stu01 INNER JOIN stu02 on stu01.studentno = stu02.studentno INNER JOIN stu03 on stu02.courseno = stu03.courseno where stu01.name='王昭君'and stu03.name = '数据库' ;

六、查询所有学生的数据库成绩，要求显示姓名、课程号、成绩

-- 1.
select stu01.name,stu03.name, stu02.score from stu01,stu02,stu03 where stu01.studentno = stu02.studentno and stu02.courseno = stu03.courseno  and stu03.name = '数据库';
-- 2.
select stu01.name,stu03.name, stu02.score  from stu01 INNER JOIN stu02 on stu01.studentno = stu02.studentno INNER JOIN stu03 on stu02.courseno = stu03.courseno where stu03.name = '数据库' ;

七、查询男生最高成绩，要求显示姓名、课程号、成绩

-- 1.
select stu01.name,stu03.name, score, sex from stu01 INNER JOIN stu02 on stu01.studentno = stu02.studentno INNER JOIN stu03 on stu02.courseno = stu03.courseno where stu01.sex = '男'  ORDER BY score  desc LIMIT 0,1 ;
-- 2
select stu01.name,stu03.name, score,sex from stu01 INNER JOIN stu02 on stu01.studentno = stu02.studentno INNER JOIN stu03 on stu02.courseno = stu03.courseno where stu01.sex = '男' ORDER BY score  desc LIMIT 0,1  ;

左右连接

1.查询所有学生成绩，包括没有成绩的学生

select * from stu01 
LEFT JOIN stu02 on  stu01.studentno =  stu02.studentno;

 2.查询所有学生成绩，包括没有成绩的学生，需要显示课程名

select * from stu01  
LEFT JOIN stu02 on  stu01.studentno =  stu02.studentno
LEFT JOIN stu03 on  stu02.courseno =  stu03.courseno;

3.查询所有课程的成绩，包括没有成绩的课程 

select  * from stu02 
right JOIN stu03 on  stu02.courseno =  stu03.courseno;

4.查询所有课程的成绩，包括没有成绩的课程，包括学生信息

SELECT * FROM stu01 
	LEFT JOIN stu02 ON stu01.studentno = stu02.studentno
	RIGHT JOIN stu03 ON stu02.courseno = stu03.courseno;
	
SELECT * FROM stu02
	RIGHT JOIN stu03 ON stu02.courseno = stu03.courseno
	LEFT JOIN stu01 ON stu01.studentno = stu02.studentno;
 
select * from stu01
    RIGHT JOIN stu02 on  stu01.studentno =  stu02.studentno
    RIGHT JOIN stu03 on  stu02.courseno =  stu03.courseno;