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.