1、笛卡尔集
1、什么是笛卡尔集
假设集合A={a,b},集合B={0,1,2},
则两个集合的笛卡尔集为 {(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}
可以拓展到多个集合的情况
- 2、同时查询两个表(使用一个SQL语句),出现的就是笛卡尔集结果
SELECT *
FROM students,score;
- 3、查询时给表起别名
SELECT * FROM stu st,score sc;
- 4、（去除笛卡尔集）在查询时要把主键和外键保持一致
SELECT *
FROM students,score
WHERE students.id = score.sid;
- 5、连接方式分类
2、内连接划分
- 1、99写法
SELECT * FROM
students st,score sc
WHERE st.id = sc.sid;
- 2、内连接写法
SELECT * FROM students st INNER JOIN score sc ON st.id = sc.sid
- 3、方法:
2.多表联查约束主外键一样,只是写法改变了
3. ON后面只写主外键
4. (对于结果)如果还有条件直接在后面写WHERE5\. 多表联查后还有条件就直接写AND
3、左外连
- 1、定义
就是左边表数据全部查询出来,右边表只查询满足条件的
- 2、例子
SELECT *
FROM students st
LEFT OUTER JOIN score sc
ON st.id = sc.sid;
4、右外联
- 定义
就是右边表数据全部查询出来,左边表只查询满足条件的
- 例子
SELECT * FROM students st RIGHT OUTER JOIN score sc ON st.id = sc.sid;
5、多表连接
- 1、建立学生,分数,科目表(多对多)
- 多个学生可以去考同一个学科,同一个学生可以考多个科目
- 2、使用99连接法
SELECT st.name,sc.score,co.name
FROM students st,score sc,course co
WHERE st.id = sc.sid AND sc.cid = co.id
- 3、使用内联查询
SELECT *
FROM students st
JOIN score sc
ON st.id = sc.sid
JOIN course co
ON sc.cid = co.id;
6、非等值连接
1、非等值查询实现
- 查询所有员工的姓名,工资,所在部门的名称以及工资的等级.
SELECT e.ename,e.salary,d.dname
FROM emp e,dept d,salgrade g
WHERE e.deptno = d.deptno
AND e.salary >= g.lowSalary
AND e.salary <= g.highSalary;
SELECT e.ename,e.salary,d.dname
FROM emp e,dept d,salgrade g
WHERE e.deptno = d.deptno
BETWEEN g.lowSalary AND g.highSalary;
SELECT * FROM emp e
JOIN dept d ON e.deptno = d.deptno
JOIN salgrade g ON e.salary BETWEEN g.lowSalary AND g.highSalary;
7、自连接
- 注意
自然连接 还是笛卡尔集现象:两张连接的表中列名称和类型完全一致的列作为条件
- 1、两张连接的表中列名称和类型完全一致的列作为条件
- 2、 会去除相同的列
CREATE TABLE students( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20)
);CREATE TABLE score( sid INT PRIMARY KEY AUTO_INCREMENT,
score INT
);
- 随意添加数据后
- 99查询(隐式内连接)
SELECT * FROM students,score;
SELECT * FROM students,score WHERE students.id = score.sid;
- 内连接(显示内连接)
SELECT * FROM students JOIN score ON students.id = score.sid;
- 自然连接 还是笛卡尔集现象:两张连接的表中列名称和类型完全一致的列作为条件
SELECT * FROM students NATURAL JOIN score;
8、子查询
1、什么是子查询
- 一个select语句中包含另外一个完整的select语句
- 或者说两个以上select,那么就是子查询语句了
2、子查询出现的位置
- where后,把select查询出的结果当做另外一个select的条件值
- from后,把查询出的结果当作一个新表
3、例子：
- 1、查询与项羽同一个部门人员工
（1）先查出项羽所在的部门编号
SELECT deptno FROM emp WHERE ename = '项羽';
（2）再根据编号查询同一部门的员工
SELECT ename FROM emp WHERE deptno = 20;
（3）把第１条查出来的结果当第2条语句的条件
SELECT ename,deptno FROM empWHERE deptno = (SELECT deptno FROM emp WHERE ename = '项羽')
- 2、查询30号以内大于2000的薪水的人(放在from,是把这个语句当成一个表)
SELECT ename,salary,deptno FROM empWHERE deptno = 30;
SELECT ename FROM (SELECT ename,salary,deptno FROM emp WHERE deptno=30) AS s WHERE s.salary > 2000;
;
- 3、查询工资高于程咬金的员工
SELECT salary FROM emp WHERE ename = '程咬金';
SELECT ename,salary FROM emp WHERE salary > (SELECT salary FROM emp WHERE ename = '程咬金')
- 4、工资高于30号部门所有人的员工信息
（1）先查出30号部门工资最高的那个人
SELECT MAX(salary) FROM emp WHERE deptno=30;
（2）再到整个表中查询大于30号部门工资最高的那个人
SELECT ename,salary FROM emp WHERE salary > (SELECT MAX(salary) FROM emp WHERE deptno=30);
- 5、查询工作和工资与妲己已完全相同的员工信息
SELECT job,salary FROM emp WHERE ename='妲己';
SELECT * FROM emp WHERE (job,salary) in (SELECT job,salary FROM emp WHERE ename='妲己');
SELECT * FROM emp e,(SELECT job,salary FROM emp WHERE ename='妲己') AS rWHERE e.job = r.job AND e.salary = r.salary;
- 6、有2个以上直接下属的员工信息
SELECT mgr,GROUP_CONCAT(mgr),COUNT(mgr) FROM empGROUP BY mgr HAVING COUNT(mgr) >= 2;
SELECT * FROM emp WHERE
empno in (SELECT mgr,GROUP_CONCAT(mgr),COUNT(mgr) FROM empGROUP BY mgr HAVING COUNT(mgr) >= 2);
- 7、查询员工编号为7788的员工名称、员工工资、部门名称、部门地址
SELECT e.ename,e.salary,d.dname,d.local FROM emp e,dept dWHERE e.deptno = d.deptno AND e.empno = 7788;
自连接
例: 求7369员工编号、姓名、经理编号和经理姓名
SELECT mgr FROM emp WHERE empno = 7369;
SELECT * FROM emp WHERE empno = (SELECT mgr FROM emp WHERE empno = 7369)
- 所以要用到自连接
SELECT * FROM emp e1,emp e2WHERE e1.empno = e2.empno;
SELECT e1.empno,e1.ename,e2.ename FROM emp e1, emp e2WHERE e1.mgr = e2.empno;
AND e1.empno = 7369;
