实验五 连接查询和嵌套查询
连接查询 ①内连接：从两张表中提取满足连接条件的数据
语法：FROM 表1 [INNER] JOIN 表2 ON <条件表达式>
1．找出学生的姓名、出生年份、所在班级名称： SELECT Stu_name,year(birthday),Class_name
FROM Student INNER JOIN Class On Student.Class_id=Class.Class id 等价于：
SELECT Stu.sex,year(birthday),Class_name FROM Student.Class
WHERE Student.Class_id=Class.Class.id
2．找出各班级所属系的名称和电话 SELECT Class name,Depar_name,Phone
FROM Class INNER JOIN Deparment On Class.Depar_id=Deparment.Depar_id 等价于：
SELECT Class_name,Depar name,Phone FROM Class,Deparment
WHERE Class.Depar_id=Deparment.Depar_id
3．找出与“汤俊逸”老师在同一年出生的男学生的姓名、电话： SELECT Stu_name,Phone
FROM Student INNER JOIN Teacher On year(Student.Birthday)=year(Teacher.Birthday) WHERE Teacher.Teac_name='汤俊逸 AND Student.Stu_sex='男'
4．找出住在“广州市昌岗中路”的现管99的学生：
SELECT studen.*
FROM Student INNER JOIN Class On Student.Class_id=Class.Class_id WHERE Class.Class_name='现管99' AND address='广州市昌岗中路'
自连接：
5．找出同年同月同日出生的学生的姓名、出生年月 SELECT a.stu_name,b.stu_name,a.binhday
FROM student as a INNER JOIN student as b
ON a.birthday=b.birthday AND a.stu_id<>b.stu_id
6．找出“0203”班级中具有相同生日的学生的姓名、出生年月 SELECT a.stu_name,b.stu_name,a.birthday
FROM student as a INNER JOIN student as b ON month(a.birthday)=month(b.birthday) AND day(a.birthday)=day(b.birthday) AND a.stu_id<>b.stu_id
WHERE a.class_id='0203 AND b.Class_id='0203'