SQL进阶部分二

2019-01-12 本文已影响1人我叫吴智斌

表之间关系

一对一

一夫一妻

一对多关系

一个人可以拥有多辆汽车，要求查询某个人拥有的所有车辆。

创建Person表

创建Car表

多对多关系

学生选课，一个学生可以选修多门课程，每门课程可供多个学生选择。
一个学生可以有多个老师，而一个老师也可以有多个学生

1.创建老师表

2.创建学生表

3.创建学生与老师关系表

4.添加外键

为什么要拆分表，避免大量冗余数据的出现

多表查询

合并结果集

什么是合并结果集

合并结果集就是把两个select语句的查询结果合并到一起

合并结果集的两种方式

UNION
合并时去除重复记录
UNION ALL
合并时不去除重复记录

格式：
UNION
SELECT * FROM 表1 UNION SELECT * FROM 表2；
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;
示例
创建表

UNION

UNION ALL

注意事项

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

连接查询

什么是连接查询

也可以叫跨表查询，需要关联多个表进行查询

什么是笛卡尔集

假设集合A={a,b}，集合B={0,1,2}，
则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。
可以扩展到多个集合的情况

同时查询两个表，出现的就是笛卡尔集结果

查询时给表起别名

多表联查，如何保证数据正确
在查询时要把主键和外键保持一致

主表当中的数据参照子表当中的数据
原理
逐行判断，相等的留下，不相等的全不要

根据连接方式分类

内连接

等值连接
两个表同时出现的id号（值）才显示

与多表联查约束主外键是一样，只是写法改变了。
ON后面只写主外键
如果还有条件直接在后面写where

多表联查后面还有条件就直接写and

多表连接

建立学生，分数，科目表

使用99连接法

使用内联查询

非等值连接
示例表

建表语句

CREATE TABLE `emp` (

  `empno` int(11) NOT NULL,
  `ename` varchar(255) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `mgr` varchar(255) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `salary` decimal(10,0) DEFAULT NULL,
  `comm` double DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `emp` VALUES (7369, '孙悟空', '职员', '7902', '2010-12-17', 800, NULL, 20);
INSERT INTO `emp` VALUES (7499, '孙尚香', '销售人员', '7698', '2011-2-20', 1600, 300, 30);
INSERT INTO `emp` VALUES (7521, '李白', '销售人员', '7698', '2011-2-22', 1250, 500, 30);
INSERT INTO `emp` VALUES (7566, '程咬金', '经理', '7839', '2011-4-2', 2975, NULL, 20);
INSERT INTO `emp` VALUES (7654, '妲己', '销售人员', '7698', '2011-9-28', 1250, 1400, 30);
INSERT INTO `emp` VALUES (7698, '兰陵王', '经理', '7839', '2011-5-1', 2854, NULL, 30);
INSERT INTO `emp` VALUES (7782, '虞姬', '经理', '7839', '2011-6-9', 2450, NULL, 10);
INSERT INTO `emp` VALUES (7788, '项羽', '检查员', '7566', '2017-4-19', 3000, NULL, 20);
INSERT INTO `emp` VALUES (7839, '张飞', '总裁', NULL, '2010-6-12', 5000, NULL, 10);
INSERT INTO `emp` VALUES (7844, '蔡文姬', '销售人员', '7698', '2011-9-8', 1500, 0, 30);
INSERT INTO `emp` VALUES (7876, '阿珂', '职员', '7788', '2017-5-23', 1100, NULL, 20);
INSERT INTO `emp` VALUES (7900, '刘备', '职员', '7698', '2011-12-3', 950, NULL, 30);
INSERT INTO `emp` VALUES (7902, '诸葛亮', '检查员', '7566', '2011-12-3', 3000, NULL, 20);
INSERT INTO `emp` VALUES (7934, '鲁班', '职员', '7782', '2012-1-23', 1300, NULL, 10);

CREATE TABLE `dept` (
  `deptno` bigint(2) NOT NULL AUTO_INCREMENT COMMENT '表示部门编号，由两位数字所组成',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称，最多由14个字符所组成',
  `local` varchar(13) DEFAULT NULL COMMENT '部门所在的位置',
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8;

INSERT INTO `dept` VALUES (10, '财务部', '北京');
INSERT INTO `dept` VALUES (20, '调研部', '上海');
INSERT INTO `dept` VALUES (30, '销售部', '王者峡谷');
INSERT INTO `dept` VALUES (40, '运营部', '腾讯大楼');

CREATE TABLE `salgrade` (
  `grade` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '工资等级',
  `lowSalary` int(11) DEFAULT NULL COMMENT '此等级的最低工资',
  `highSalary` int(11) DEFAULT NULL COMMENT '此等级的最高工资',
  PRIMARY KEY (`grade`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `salgrade` VALUES (1, 700, 1200);
INSERT INTO `salgrade` VALUES (2, 1201, 1400);
INSERT INTO `salgrade` VALUES (3, 1401, 2000);
INSERT INTO `salgrade` VALUES (4, 2001, 3000);
INSERT INTO `salgrade` VALUES (5, 3001, 9999);

查询所有员工的姓名，工资，所在部门的名称以及工资的等级
1.查询所有员工的姓名，工资

2.查询所有员工的姓名，工资和所有部门

3.查询所有员工的姓名，工资和所在部门及工资等级

外连接

左外连接（左连接）
两表满足条件相同的数据查出来，如果左边表当中有不相同的数据，也把左边表当中的数据查出来。
左边表当中的数据全部查出，右边表当中，只查出满足条件的内容

使用内连接时，周七不会查出来，没有成绩，缺考了。把所有考过试的学生分数查出来。

使用左连接查询所有学生及学生的考试分数

左连接会把左表当中的数据全部查出，右表当中只查出满足条件的数据
可以省略outer不写
查询时，两个表可以不需要建立主外键约束

右外连接（右连接）

右连接会把右当中的数据全部查出，左表当中只查出满足条件的数据

右边表当中的所有数据全部查出，左边表只查出满足条件的记录
站在表的角度去看，使用左连接就把左边表当中的内容全部查出，右边查出满足条件的。
使用右连接，就把右边表当中的数据全部查出。左边查出满足条件的。

自然连接

连接查询会产生无用笛卡尔集，我们通常使用主外键关系等式来去除它。
而自然连接无需你去给出主外键等式，它会自动找到这一等式
也就是说不用去写条件

        select * from stu natural join score;

要求
两张连接的表中列名称和类型,需要完全一致的列作为条件
会去除相同的列

子查询

什么是子查询

一个select语句中包含另一个完整的select语句。
或两个以上SELECT，那么就是子查询语句了。

子查询出现的位置

where后，把select查询出的结果当作另一个select的条件值
from后，把查询出的结果当作一个新表；

示例表

使用

查询与项羽同一个部门人员工

1.先查出项羽所在的部门编号

2.再根据编号查同一部门的员工

把第1条查出来的结果当第2条语句的条件

查询工资高于程咬金的员工
1.查出程咬金的工资

2.再去根据查出的结果查询出大于该值的记录员工名称

工资高于30号部门所有人的员工信息
1.先查出30号部门工资最高的那个人

2.再到整个表中查询大于30号部门工资最高的那个人

查询工作和工资与妲己完全相同的员工信息
1.先查出妲已的工作和工资

2.根据查询结果当作条件再去查询工作和工资相同的员工

由于是两个条件，使用 IN进行判断

有2个以上直接下属的员工信息
1.对所有的上级编号进行分组

2.找出大于2个的，大于2个说明有两个下属

3.把上条的结果当作员工编号时行查询

查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

自连接

求7369员工编号、姓名、经理编号和经理姓名

以上这种方法只能查询出一个经理的名称

自连接：自己连接自己，起别名