sql92语法和99语法介绍

#多表查询
#查询出每个学生的所属班级名
SELECT s.name,c.class_name FROM student s,class c;
#笛卡尔积现象：多张表进行查询不做任何限制，最终的查询结果是这几张表的乘积
#查询每个学生的所属班级名
#sql 92语法
SELECT S.name,c.class_name FROM student s,class c where s.class_num = c.class.num and sex='男';
#优点：解决了笛卡尔积现象，实现了连接查询
#缺点：表的连接条件和查询条件放在了一起
#SELECT s.name,c.class_name from student s,class c where s.class_num =c.class_num and sex='男';
#sql 99语法：SELECT xxx from 表A join 表B on 连接条件 where 查询条件
SELECT s.name,c.class_name from student s join class c on s.class_num = c.class_num where sex='男';

#sql 99连接查询方式：
#1.内联式 inner join ---->两张表中重和部分
SELECT s.name,c.class_name from student s inner join class c on s.class_num = c.class_num;
#2.左外连接 left join ---->AB两张表中 左边的表为主表，查询结果偏向左表
SELECT s.name,c.class_name from student s left join class c on s.class_num = c.class_num;
#3.右外连接 right join ---->AB两张表中 右边的表为主表，查询结果偏向右表
SELECT s.name,c.class_name from student s right join class c on s.class_num = c.class_num;

#多表进行连接查询
#SELECT xxx from 表A join 表B  on 连接条件 join 表C on 连接条件

此外简介一下嵌套查询作为了解：

#嵌套查询 ----》复杂sql 
#1. 找到所有选择课程号为1001的学生姓名
SELECT t1.name ,t1.cno from
(SELECT student.name,relationship.cno from student inner join relationship on relationship.sno = student.sno)  t1
where t1.cno='1001';

#2.找到所有选择数学课的同学(先执行（）最里面的，然后逐渐向外执行）
SELECT t2.name,t2.gradeName from
(SELECT student .name,course.gradeName from
(SELECT student.name,relationship.cno from student inner join relationship on relationship.sno = student.sno)  t1
inner join course on t1.cno =course.cno) t2  where gradeName ='数学’