多表查询_语法使用

1.多表设计之外键约束
         * 约束的作用: 保证数据的完整性.
         * 单表约束分为:
                * 主键约束
                * 唯一约束
                * 非空约束
         * 多表约束分为:
                   外键约束：用来保证数据完整性（多表之间）。
2.创建外键约束
        * alter table 表名 add foreign key (外键字段) references 被约束的表名(主键字段);
        * 设置外键为非空
                alter table 表名 modify 外键字段 数据类型 not null;
#注意:
        *有关联关系的多个表时,就要遵循一定的原则
        **设置外键的语法
               
                *** 一般不设置成真正的外键
                  需要手动的保证数据的完整性(只是有外键这个字段,但没有实际的约束)
                  删部门之前先手动的删除部门下边的员工
                 
                 *** 表中正常的字段在需求中可以找到
               
3.表与表之间的关系 及 建表原则
        * 一对多的关系
                * 在多的一方创建外键指向一的一方的主键
        * 多对多的关系
                * 需要创建中间表,中间表中至少两个字段,分别为外键指向多对多双方的主键;
        * 一对一的关系
                ** 唯一外键对应
                        *** 假设是一对多,在多的一方创建外键指向一的一方的主键,将外键设置为unique;
                ** 主键对应
                        *** 将两个表的主键建立对用关系即可;
4.多表查询
        * 连接查询
                ** 交叉连接:查询到的两个表的笛卡尔积;
                        *** 语法:
                                        select * from 表1 cross join 表2;
                                        select * from 表1 , 表2;
        * 内连接:inner join(inner 是可以省略的e)
                ** 显示内连接:在SQL语句中显示的调用inner join 关键字
                        *** select * from 表1 inner join 表2 on 关联条件;
                        例:select * from classes c inner join student s on c.cid = s.cno;
                ** 隐式内连接:在SQL中没有调用inner join关键字
                        *** select * from 表1 ,表2 where 关联条件;
                        例:SELECT * FROM classes c,student s WHERE c.cid = s.cno;
        * 外连接:outer join(outer 可以省略)
                ** 左外连接
                        *** select * from 表1 left outer join 表2 on 关联条件;
                        例:SELECT * FROM classes c LEFT OUTER JOIN student s ON c.cid = s.cno;
                ** 右外连接
                        *** select * from 表1 right outer join 表2 on 关联条件;
                        例:select * from classes c right outer join student s on c.cid = s.cno;
        ****关联条件:
                        可以通过 and or 添加多个关联条件
5.内连接与外连接的区别:
        * 内连接 : 两个表的公共部分
        * 外连接 : 返回的结果为公共部分和参照表(基准表)的所有行.
6.子查询(嵌套查询)
        * 一个查询语句条件需要依赖另一个查询语句的结果。


        * 带in 的子查询 : 返回包含在 in 的返回结果中的内容
                例:select * from classes where cid in (SELECT cno
                   FROM student WHERE birthday > '1991-01-01');
        * 带exists的子查询 : 外部查询的结果至少有一个结果存在于 exists 的返回结果中,
          就返回所有的外部查询结果
                例:select * from classes where exists (SELECT cno
                FROM student WHERE birthday > '1991-01-01');
        * 带 any 的子查询 : 将外部查询的结果中  > , < ,= any 返回结果中任意一个的结果返回;
                例:SELECT * FROM classes WHERE cid > ANY (SELECT cno
                FROM student )
                *** 带any的子查询: 大于任意一个值
               
        * 带 all 的子查询 : 将外部结果中 全 > ,全 < , 全 =  all返回结果的 结果返回;
                例:SELECT * FROM classes WHERE cid > ALL (SELECT cno
                FROM student)
7.多表查询到组合使用 举例:
        * 查询班级名称，和班级总人数
                SELECT c.cname,COUNT(*) FROM classes c,student s
                WHERE c.cid = s.cno GROUP BY c.cname;
        * 查询学生的姓名和学生所选的总课程平均成绩。
                select s.sname,avg(sc.score) from student s,stu_cour sc
                where s.sid = sc.sno group by s.sname;
        * 查询学生的姓名和学生的选课总数，显示选课超过2门学生姓名。
                select s.sname,count(*) from student s,stu_cour sc
                where s.sid = sc.sno group by s.sname having count(*) > 2;
        * 查询平均成绩大于80分的学生的总数。
                select count(*) from student s where s.sid in (SELECT sc.sno
                FROM stu_cour sc GROUP BY sc.sno HAVING AVG(sc.score) >  80);
        * 查询学生和平均成绩，但是平均成绩大于01班的任何一个学生的平均成绩。
                select s.sname,avg(sc.score) from student s,stu_cour sc
                where s.sid = sc.sno group by s.sname having avg(sc.score)
                > any(SELECT AVG(sc.score) FROM student s,stu_cour sc,classes c
                WHERE s.sid = sc.sno AND s.cno = c.cid AND c.cname= '01班' GROUP BY s.sname);
8.内/外连接的查询原理:
        * 内连接查询原理:
                内连接根据关联条件,去两个表中匹配,如果匹配上就返回匹配上的记录,入匹配不上就不返回;
                        先加关联条件
                        后加外部业务条件
        * 做外连接查询原理:
                以左表为基准,根据关联条件去右侧的表中去匹配,无论右侧是否可以匹配,左侧表都会返回,
                右侧匹配不上的返回null.        

多表查询_语法使用

1.多表设计之外键约束
         * 约束的作用: 保证数据的完整性.
         * 单表约束分为:
                * 主键约束
                * 唯一约束
                * 非空约束
         * 多表约束分为:
                   外键约束：用来保证数据完整性（多表之间）。
2.创建外键约束
        * alter table 表名 add foreign key (外键字段) references 被约束的表名(主键字段);
        * 设置外键为非空
                alter table 表名 modify 外键字段 数据类型 not null;
#注意:
        *有关联关系的多个表时,就要遵循一定的原则
        **设置外键的语法
               
                *** 一般不设置成真正的外键
                  需要手动的保证数据的完整性(只是有外键这个字段,但没有实际的约束)
                  删部门之前先手动的删除部门下边的员工
                 
                 *** 表中正常的字段在需求中可以找到
               
3.表与表之间的关系 及 建表原则
        * 一对多的关系
                * 在多的一方创建外键指向一的一方的主键
        * 多对多的关系
                * 需要创建中间表,中间表中至少两个字段,分别为外键指向多对多双方的主键;
        * 一对一的关系
                ** 唯一外键对应
                        *** 假设是一对多,在多的一方创建外键指向一的一方的主键,将外键设置为unique;
                ** 主键对应
                        *** 将两个表的主键建立对用关系即可;
4.多表查询
        * 连接查询
                ** 交叉连接:查询到的两个表的笛卡尔积;
                        *** 语法:
                                        select * from 表1 cross join 表2;
                                        select * from 表1 , 表2;
        * 内连接:inner join(inner 是可以省略的e)
                ** 显示内连接:在SQL语句中显示的调用inner join 关键字
                        *** select * from 表1 inner join 表2 on 关联条件;
                        例:select * from classes c inner join student s on c.cid = s.cno;
                ** 隐式内连接:在SQL中没有调用inner join关键字
                        *** select * from 表1 ,表2 where 关联条件;
                        例:SELECT * FROM classes c,student s WHERE c.cid = s.cno;
        * 外连接:outer join(outer 可以省略)
                ** 左外连接
                        *** select * from 表1 left outer join 表2 on 关联条件;
                        例:SELECT * FROM classes c LEFT OUTER JOIN student s ON c.cid = s.cno;
                ** 右外连接
                        *** select * from 表1 right outer join 表2 on 关联条件;
                        例:select * from classes c right outer join student s on c.cid = s.cno;
        ****关联条件:
                        可以通过 and or 添加多个关联条件
5.内连接与外连接的区别:
        * 内连接 : 两个表的公共部分
        * 外连接 : 返回的结果为公共部分和参照表(基准表)的所有行.
6.子查询(嵌套查询)
        * 一个查询语句条件需要依赖另一个查询语句的结果。


        * 带in 的子查询 : 返回包含在 in 的返回结果中的内容
                例:select * from classes where cid in (SELECT cno
                   FROM student WHERE birthday > '1991-01-01');
        * 带exists的子查询 : 外部查询的结果至少有一个结果存在于 exists 的返回结果中,
          就返回所有的外部查询结果
                例:select * from classes where exists (SELECT cno
                FROM student WHERE birthday > '1991-01-01');
        * 带 any 的子查询 : 将外部查询的结果中  > , < ,= any 返回结果中任意一个的结果返回;
                例:SELECT * FROM classes WHERE cid > ANY (SELECT cno
                FROM student )
                *** 带any的子查询: 大于任意一个值
               
        * 带 all 的子查询 : 将外部结果中 全 > ,全 < , 全 =  all返回结果的 结果返回;
                例:SELECT * FROM classes WHERE cid > ALL (SELECT cno
                FROM student)
7.多表查询到组合使用 举例:
        * 查询班级名称，和班级总人数
                SELECT c.cname,COUNT(*) FROM classes c,student s
                WHERE c.cid = s.cno GROUP BY c.cname;
        * 查询学生的姓名和学生所选的总课程平均成绩。
                select s.sname,avg(sc.score) from student s,stu_cour sc
                where s.sid = sc.sno group by s.sname;
        * 查询学生的姓名和学生的选课总数，显示选课超过2门学生姓名。
                select s.sname,count(*) from student s,stu_cour sc
                where s.sid = sc.sno group by s.sname having count(*) > 2;
        * 查询平均成绩大于80分的学生的总数。
                select count(*) from student s where s.sid in (SELECT sc.sno
                FROM stu_cour sc GROUP BY sc.sno HAVING AVG(sc.score) >  80);
        * 查询学生和平均成绩，但是平均成绩大于01班的任何一个学生的平均成绩。
                select s.sname,avg(sc.score) from student s,stu_cour sc
                where s.sid = sc.sno group by s.sname having avg(sc.score)
                > any(SELECT AVG(sc.score) FROM student s,stu_cour sc,classes c
                WHERE s.sid = sc.sno AND s.cno = c.cid AND c.cname= '01班' GROUP BY s.sname);
8.内/外连接的查询原理:
        * 内连接查询原理:
                内连接根据关联条件,去两个表中匹配,如果匹配上就返回匹配上的记录,入匹配不上就不返回;
                        先加关联条件
                        后加外部业务条件
        * 做外连接查询原理:
                以左表为基准,根据关联条件去右侧的表中去匹配,无论右侧是否可以匹配,左侧表都会返回,
                右侧匹配不上的返回null.