hibernatetemplate多表查询

表之间的关系

一对一、一对多

不需要创建中间表

CREATE TABLE person(id INT PRIMARY KEY auto_increment,name VARCHAR(50));

CREATE TABLE car(name VARCHAR(20),color VARCHAR(20),pid INT，# pid 外键 关联person的id
		CONSTRAINT c_p_fk FOREIGN KEY(pid) REFERENCES person(id) #添加约束
);

多对多关系创建

需要创建中间表——分别关联两个表
比如学生选课

CREATE TABLE teacher(tid INT PRIMARY KEY,name VARCHAR(50));
CREATE TABLE student(sid INT PRIMARY KEY,name VARCHAR(50));
CREATE TABLE tea_stu_relation(tid INT,sid INT);
-- 一个表可添加多个外键
-- ADD 添加约束
ALTER TABLE tea_stu_relation ADD CONSTRAINT FOREIGN KEY(tid) REFERENCES teacher(tid);
ALTER TABLE tea_stu_relation ADD CONSTRAINT FOREIGN KEY(sid) REFERENCES student(sid);

拆分表

在上例中使用了三个表而非一整个表。

• 拆分表的目的：避免大量冗余数据的出现。

多表查询

合并结果集

• 把两个select语句的查询结果合并到一起
• 两种方式：UNION-合并时去除重复记录；UNION ALL-合并时不去除重复记录。
• 格式：

SELECT * FROM 表1 UNION SELECT * FROM 表2;
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;

• 示例：

CREATE TABLE A(name VARCHAR('a',10),score INT);
CREATE TABLE B(name VARCHAR('b',10),score INT);
-- 插入数据
INSERT INTO A VALUES('a',10),('b',20),('c',30);
INSERT INTO B VALUES('a',10),('b',20),('c',40);

SELECT * FROM A UNION SELECT * FROM B;

SELECT * FROM A UNION ALL SELECT * FROM B;

• 注意：被合并的两个结果的列数、列数据类型必须相同。

连接查询

• 连接查询也叫跨表查询，需要关联多个表进行查询。
• 笛卡尔集：假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
• 同时查询两个，结果为笛卡尔集。

SELECT * FROM student,teacher;

去除笛卡尔集

查询时给表起别名。

SELECT * FROM student stu,teacher tea;

多表查询，如何保证数据正确

1. 查询时主键外键要保持一致；

SELECT * FROM student stu,score sco WHERE stu.id=sco.sid;
-- 这里不起别名也行

2. 主表当中的数据参照子表当中的数据；
3. 原理：相等的留下，不相等的全不要。

根据连接方式分类

内连接

等值连接

两个表同时出现的id值才显示。

SELECT * FROM student stu INNER JOIN score sco ON stu.id=sco.sid;

与多表联查约束主外键一样，只是写法不同。
ON后只写主外键。
如果还有其他条件，直接用where写。
多表联查后面还有条件就直接写and。

多表连接

-- 创建科目表
CREATE TABLE course(cid INT PRIMARY KEY,name VARCHAR(50));
-- 多表连接-学生、成绩、科目
--99查询->每多一个条件就多写一个AND
SELECT st.`name`,sc.score,c.name FROM student st,score sc,score c WHERE st.id=sc.sid AND sc.sid=c.;

非等值连接

查询后的限制条件不再是‘=’，仅此而已，原理都相同。

自连接（不赘述，不太重要）

外连接

左外连接

查询结果：两表中条件相同的数据+左边表中的独有数据=左边全部数据+右边满足条件的数据

SELECT * FROM student st LEFT JOIN score sc ON st.id=sc.sid;

右外连接

跟左连接相反

自然连接

• 连接查询会产生无用的笛卡尔集，我们通常使用上述的主外键关系等式去除笛卡尔集。
• 自然连接无需给出主外键等式，会自动生成。
• 要求：两张表中的列名称和列数据类型必须一致，会去出相同的列。

-- 先建表
CREATE TABLE student(id INT,name VARCHAR(20));
CREATE TABLE score(sid INT,score INT);

不去除笛卡尔集，直接连

CREATE * FROM sutdent,score;

去除笛卡尔集

-- 普通连接 两种方法 二选一
SELECT * FROM student,score WHERE student.id=score.sid;
SELECT * FROM student JOIN score ON student.id=score.sid;

-- 自然连接
SELECT * FROM student NATURAL JOIN score;

没达到自然连接的效果，需要修改表结构——把student中的id改成sid重试即可。

ok了

子查询

• 一个select语句中包含另一个或两个以上完整的select语句。
• 使用情况：一、where后把select查询出的结果当作另一个select的值；二、from后，把查询出的结果当作一个新表。

where 条件查询

-- 查询与项羽同一部门的员工 编号20
SELECT ename,deptno FROM emp 
WHERE deptno=(SELECT deptno FROM emp WHERE ename='项羽');

from表形式

SELECT ename FROM 
(SELECT ename,salary,deptno FROM emp WHERE deptno=30) s #别名在这种场合比较实用
WHERE s.salary>2000;

自连接

表连接表本身的不同字段

SELECT * FROM emp e1,emp e2
WHERE e1.mgr=e2.empno;