JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询

我会带着你远行 2023-09-29 17:21 18阅读 0赞

## day02\_Oracle ##

## 一、课程目标 ##

常用函数：了解会使用
    高级查询：掌握（mysql,复习）
    分页查询：伪列
    关联查询：掌握（mysql,复习）

## 二、常用函数 ##

### 2.1 Oracle字符函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ASCII</td> 
   <td>返回对应字符的十进制值</td> 
  </tr> 
  <tr> 
   <td>CHR</td> 
   <td>给出十进制返回字符</td> 
  </tr> 
  <tr> 
   <td><code>CONCAT</code></td> 
   <td>拼接两个字符串，与`</td> 
  </tr> 
  <tr> 
   <td>INITCAP</td> 
   <td>将字符串的第一个字母变为大写</td> 
  </tr> 
  <tr> 
   <td>INSTR</td> 
   <td>找出某个字符串的位置</td> 
  </tr> 
  <tr> 
   <td>INSTRB</td> 
   <td>找出某个字符串的位置和字节数</td> 
  </tr> 
  <tr> 
   <td><code>LENGTH</code></td> 
   <td>以字符给出字符串的长度</td> 
  </tr> 
  <tr> 
   <td>LENGTHB</td> 
   <td>以字节给出字符串的长度</td> 
  </tr> 
  <tr> 
   <td><code>LOWER</code></td> 
   <td>将字符串转换成小写</td> 
  </tr> 
  <tr> 
   <td>LPAD</td> 
   <td>使用指定的字符在字符的左边填充</td> 
  </tr> 
  <tr> 
   <td>LTRIM</td> 
   <td>在左边裁剪掉指定的字符</td> 
  </tr> 
  <tr> 
   <td>RPAD</td> 
   <td>使用指定的字符在字符的右边填充</td> 
  </tr> 
  <tr> 
   <td>RTRIM</td> 
   <td>在右边裁剪掉指定的字符</td> 
  </tr> 
  <tr> 
   <td><code>REPLACE</code></td> 
   <td>执行字符串搜索和替换</td> 
  </tr> 
  <tr> 
   <td><code>SUBSTR</code></td> 
   <td>取字符串的子串</td> 
  </tr> 
  <tr> 
   <td>SUBSTRB</td> 
   <td>取字符串的子串（以字节）</td> 
  </tr> 
  <tr> 
   <td>SOUNDEX</td> 
   <td>返回一个同音字符串</td> 
  </tr> 
  <tr> 
   <td>TRANSLATE</td> 
   <td>执行字符串搜索和替换</td> 
  </tr> 
  <tr> 
   <td>TRIM</td> 
   <td>裁剪掉前面或后面的字符串</td> 
  </tr> 
  <tr> 
   <td><code>UPPER</code></td> 
   <td>将字符串变为大写</td> 
  </tr> 
 </tbody> 
</table>

-- 字符函数
    -- 求字符的长度
    -- dual 是一个伪表，没有实际应用，就是配合着sql语法而来的
    select length('ALDKF') from dual;
    -- 截取字符
    -- 参数解释:HelloWord: 原字符，6：从第几位开始 , 5: 截取多少位
    -- 注意：索引是从1开始，不是0
    select substr('HelloWorld',6,5) from dual;
    -- 字符拼接
    -- concat('字符1','字符2')
    select concat('Hello','World') from dual;
    -- 注意：concat一次只能拼接两个字符，如果有多个字符，需嵌套使用concat
    select concat(concat('Hello','World'),'MM') from dual;
    -- concat嵌套方式比较麻烦，可以使用|| 进行嵌套
    select 'Hello' || 'World' || 'haha' from dual;
    -- 将字符转换为小写
    select lower('ALDKF') from dual;
    -- 字符串替换
    select replace('冬天就是好','冬天','切糕') from dual;

### 2.2 Oracle数学函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ROUND</td> 
   <td>四舍五入</td> 
  </tr> 
  <tr> 
   <td>TRUNC</td> 
   <td>截取数值</td> 
  </tr> 
  <tr> 
   <td>MOD(n1,n2)</td> 
   <td>返回一个n1除以n2的余数</td> 
  </tr> 
  <tr> 
   <td>CEIL</td> 
   <td>向上取整</td> 
  </tr> 
  <tr> 
   <td>FLOOR</td> 
   <td>向下取整</td> 
  </tr> 
  <tr> 
   <td>ABS</td> 
   <td>指定值的绝对值</td> 
  </tr> 
  <tr> 
   <td>POWER(n1,n2)</td> 
   <td>返回n1的n2次方</td> 
  </tr> 
 </tbody> 
</table>

-- 数学函数
    -- round四舍五入，默认小数点最近的一位，是否四佶五入
    select round(123.456) from dual;
    -- 保留两位小数，进行四舍五入
    select round(123.456,2) from dual;
    -- trunc截取数值，默认将小数点后全部舍弃掉
    select trunc(123.456) from dual;
    -- 保留两位小数，进行截取数值
    select trunc(123.456,2) from dual;
    -- mod 取模
    select mod(10,3) from dual;
    -- ceil 向上取整
    select ceil(123.456) from dual;
    -- floor 向下取整
    select floor(123.456) from dual;
    -- abs 取绝对值
    select abs(-123.456) from dual;
    -- power 求几次幂
    select power(2,3) from dual;

### 2.3 Oracle日期函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>systimestamp</td> 
   <td>获取当前日期和时间、小数点后面精确6位、时区、上下午</td> 
  </tr> 
  <tr> 
   <td>sysdate</td> 
   <td>获取当前日期和时间</td> 
  </tr> 
  <tr> 
   <td>ADD_MONTHS</td> 
   <td>在当前日期基础上加指定的月</td> 
  </tr> 
  <tr> 
   <td>LAST_DAY</td> 
   <td>获取当前日期所在月的最后一天</td> 
  </tr> 
  <tr> 
   <td>TRUNC</td> 
   <td>日期截取</td> 
  </tr> 
 </tbody> 
</table>

-- 日期函数
    -- 获取当前系统时间
    select sysdate from dual;
    -- 加月份
    select add_months(sysdate,4) from dual;
    -- 减月份，我们只需要数值为负值
    select add_months(sysdate,-4) from dual;
    -- 获取当前日期的所在的月份的最后一天
    select last_day(sysdate) from dual;
    -- sysdate-33：到了上一个月，也就是获取的是上一个月的最后一天
    select last_day(sysdate-33) from dual;
    -- 截取日期
    -- 默认按日截取，将时间截掉
    select trunc(sysdate) from dual;
    -- 按月截取(把日载掉)
    select trunc(sysdate,'mm') from dual; 
    -- 按年截取(把月载掉)
    select trunc(sysdate,'yyyy') from dual; 
    -- 按小时截取
    select trunc(sysdate,'hh') from dual; 
    -- 按分钟截取
    select trunc(sysdate,'mi') from dual;

### 2.4 Oracle转换函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CHARTOROWID</td> 
   <td>将 字符转换到 rowid 类型</td> 
  </tr> 
  <tr> 
   <td>CONVERT</td> 
   <td>转换一个字符节到另外一个字符节</td> 
  </tr> 
  <tr> 
   <td>HEXTORAW</td> 
   <td>转换十六进制到 raw 类型</td> 
  </tr> 
  <tr> 
   <td>RAWTOHEX</td> 
   <td>转换 raw 到十六进制</td> 
  </tr> 
  <tr> 
   <td>ROWIDTOCHAR</td> 
   <td>转换 ROWID 到字符</td> 
  </tr> 
  <tr> 
   <td><code>TO_CHAR </code></td> 
   <td>转换日期格式到字符串</td> 
  </tr> 
  <tr> 
   <td><code>TO_DATE</code></td> 
   <td>按照指定的格式将字符串转换到日期型</td> 
  </tr> 
  <tr> 
   <td><code>TO_MULTIBYTE</code></td> 
   <td>把单字节字符转换到多字节</td> 
  </tr> 
  <tr> 
   <td>TO_NUMBER</td> 
   <td>将数字字串转换到数字</td> 
  </tr> 
  <tr> 
   <td>TO_SINGLE_BYTE</td> 
   <td>转换多字节到单字节</td> 
  </tr> 
 </tbody> 
</table>

-- 转换函数
    
    -- 数字转字符串
    select to_char(100) from dual;
    -- 还可以与其他字符串进行拼接
    select to_char(100)||'分' from dual;
    -- 日期转字符串
    -- 将当前的系统时间，按照yyyy-MM-dd模式转换成字符串
    select to_char(sysdate,'yyyy-MM-dd') from dual;
    select to_char(sysdate,'yyyy-MM-dd HH:mi:ss') from dual;
    -- 注意：年月日中间的连接符，不能是中文，比如:yyyy年MM月dd日
    -- select to_char(sysdate,'yyyy年MM月dd日') from dual; -- 错误的写法
    -- 如果要yyyy年MM月dd日这种模式的日期时间，我们可以单独把年月日截取出来进行拼接
    select 
       to_char(sysdate,'yyyy')||'年' ||
       to_char(sysdate,'MM')||'月' ||
       to_char(sysdate,'dd')||'日'    
    from dual;
    -- 字符串转日期
    -- 将2020-03-11日期字符串按照yyyy-mm-dd模式转换成Date对象
    select to_date('2020-03-11','yyyy-mm-dd') from dual;
    -- 将字符串转数值
    select to_number('100') from dual;
    -- 验证100是否是数值
    select to_number('100')+100 from dual;
    -- 可以直接相加，会自动转换为数值进行参与运算
    select '100' + 100 from dual;

### 2.5 Oracle其他函数 ###

#### 2.5.1 nvl函数 ####

*  ###### 语法 ######
    
        NVL(检测的值，需要是字符型，如果为 null 的值);
 *  ###### 示例 ######
    
        select NVL(NULL,0) from dual;
        select NVL('','哈哈') from dual;   -- 返回哈哈
        select NVL(null,'哈哈') from dual;  -- 返回哈哈
        select NVL(' ','哈哈') from dual;  -- 返回空格

#### 2.5.2 nvl2函数 ####

*  ###### 需求 ######
    
    > 使用nvl函数，判断值是否为空，如果为空，将值替换为’补考’
    
    以学生表（student）英语成绩为例
    
    ![在这里插入图片描述][feaf699d26554702865776ab3b30fa51.png_pic_center]
 *  ###### sql演示 ######
    
        select NVL(english,'补考') from student
    
    ![在这里插入图片描述][b1a0df73576d4616b079fcdb69e7fb5b.png_pic_center]
    
    英语成绩是number类型的，我们替换的值是字符类型的，所以报错
 *  ###### nvl2语法 ######
    
        NVL2(检测的值，如果不为 null 的值，如果为 null 的值);
 *  ###### 示例 ######
    
        select NVL2(english,to_char(english),'补考') from student;
    
    ![在这里插入图片描述][554ad70350e244afb04812794df53327.png_pic_center]

### 2.6 Oracle聚合函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>count(*) | count(主键)</td> 
   <td>计算表中的总记录数</td> 
  </tr> 
  <tr> 
   <td>max</td> 
   <td>计算最大值</td> 
  </tr> 
  <tr> 
   <td>min</td> 
   <td>计算最小值</td> 
  </tr> 
  <tr> 
   <td>sum</td> 
   <td>计算和</td> 
  </tr> 
  <tr> 
   <td>avg</td> 
   <td>计算平均值</td> 
  </tr> 
 </tbody> 
</table>

**注意**：聚合函数的计算，排除null值。

**解决方案**：

1.  选择不包含非空的列进行计算
2.  nvl2或nvl函数

> 以student表为例，进行演示

*  查询学生总数（null值处理）
    
        SELECT COUNT(id) FROM student;
        select count(NVL(english,0)) from student;
        SELECT COUNT(*) FROM student;
 *  查询数学成绩总分
    
        SELECT SUM(math) FROM student;
 *  查询数学成绩平均分
    
        SELECT AVG(math) FROM student;
 *  查询数学成绩最高分
    
        SELECT MAX(math) FROM student;
 *  查询数学成绩最低分
    
        SELECT MIN(math) FROM student;

## 三、DQL高级查询 ##

### 3.1 数据准备 ###

-- 创建表
    CREATE TABLE person (
      id number,
      name varchar2(20),
      age number,
      sex varchar2(5),
      address varchar2(100),
      math number,
      english number
    );
    -- 插入记录
    INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
    (1,'马云',55,'男','杭州',66,78);
    INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
    (2,'马化腾',45,'女','深圳',98,87);
    INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
    (3,'马景涛',55,'男','香港',56,77);
    INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
    (4,'柳岩',20,'女','湖南',76,65);
    INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
    (5,'柳青',20,'男','湖南',86,NULL);
    INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
    (6,'刘德华',57,'男','香港',99,99);
    INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
    (7,'马德',22,'女','香港',99,99);
    INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
    (8,'德玛西亚',18,'男','南京',56,65);
    INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
    (9,'唐僧',25,'男','长安',87,78);
    INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
    (10,'孙悟空',18,'男','花果山',100,66);
    INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
    (11,'猪八戒',22,'男','高老庄',58,78);
    INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
    (12,'沙僧',50,'男','流沙河',77,88);
    INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
    (13,'白骨精',22,'女','白虎岭',66,66);
    INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
    (14,'蜘蛛精',23,'女','盘丝洞',88,88);
    commit;

### 3.2 排序查询 ###

*  ###### 语法 ######
    
        SELECT 字段名 FROM 表名 [WHERE条件] ORDER BY 字段名 [ASC|DESC];
 *  ###### 示例 ######
    
        -- 按照年龄的降序排序
        select * from person order by age desc;

### 3.3 分组查询 ###

> 对一列数据进行分组，相同的内容分为一组，通常与聚合函数一起使用，完成统计工作

#### 3.3.1 语法 ####

SELECT 字段 1,字段 2... FROM 表名 [where条件] GROUP BY 分组字段 [HAVING 条件] [order by];

*  注意事项
    
     *  分组之后查询的字段：分组字段、聚合函数
     *  `where` 和 `having`的区别？
        
         *  `where` 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来 where 对基本的条件筛选
         *  `where` 后不可以跟聚合函数，having可以进行聚合函数的判断。
     *  `where:` 操作的数据源: **原始表**
     *  `having:` 操作的数据源: **结果集**
        
        ![在这里插入图片描述][2a955bb67a044cd58f4cf9c48b53cfb0.png_pic_center]

#### 3.3.2 案例演示 ####

*  查询男女各多少人
    
        SELECT sex,COUNT(*) FROM person GROUP BY sex;
 *  查询年龄大于25岁的人,按性别分组,统计每组的人数
    
        SELECT sex,COUNT(*) FROM person WHERE age >25 GROUP BY sex;
 *  查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
    
        SELECT sex,COUNT(*) FROM person WHERE age >25 GROUP BY sex HAVING COUNT(*)>2;

### 3.4 分页查询 \* ###

伪列是Oracle提供的一个系统列值，在最左侧，可以直接使用。

> 我们在 ORACLE 进行分页查询，需要用到ROWNUM 和嵌套查询

#### 3.4.1 需求 ####

> 分页查询person表 每页 3条记录

#### 3.4.2 简单分页查询 ####

首先显示第一页的3条数据

> select rownum, p.\* from person p where rownum <= 3

![在这里插入图片描述][1f2293477e08470cb2b4b12af23620ac.png_pic_center]

显示第二页的3条数据

select rownum,p.* from person p where rownum>3 and rownum<=6

![在这里插入图片描述][8ae096ac147c466ba00355e51bf4d872.png_pic_center]

发现查询出来的没有结果伪列的特性决定；

> 这是因为 rownum 是在查询语句扫描每条记录时产生的，所以不能使用“大于” 符号，只能使用“小于”或“小于等于” ，只用“等于”也不行

那怎么办呢？我们可以使用子查询来实现

select * from (select rownum r,p.* from person p) where r > 3 and r<= 6

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PAKZljuY-1665832782011)(assets/image-![在这里插入图片描述][1ddb3f2a5af445788b13c2ba11b4fc1b.png_pic_center]  
.png)\]

#### 3.4.3 排序分页查询 ####

> 按照person表中的年龄降序进行查询第二页的数据

select * from (select rownum r,p.* from (select * from person order by age desc) p) where r > 3 and r<= 6 ;

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lPNVeCDx-1665832782012)(assets/![在这里插入图片描述][73f834fa4d5a44bea13976e2452c22aa.png_pic_center]  
)\]

## 四、多表查询 ##

### 4.1 数据准备 ###

-- 部门表
    CREATE TABLE dept (
      id NUMBER PRIMARY KEY, -- 部门id
      dname VARCHAR2(50),    -- 部门名称
      loc VARCHAR2(50)       -- 部门位置
    );
    
    -- 添加4个部门
    INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京');
    INSERT INTO dept(id,dname,loc) VALUES (20,'学工部','上海');
    INSERT INTO dept(id,dname,loc) VALUES (30,'销售部','广州');
    INSERT INTO dept(id,dname,loc) VALUES (40,'财务部','深圳');
    commit;
    
    -- 职务表
    CREATE TABLE job (
      id NUMBER PRIMARY KEY,
      jname VARCHAR2(20), -- 职务名称
      description VARCHAR2(50) -- 职务描述
    );
    
    -- 添加4个职务
    INSERT INTO job (id, jname, description) VALUES(1, '董事长', '管理整个公司，接单');
    INSERT INTO job (id, jname, description) VALUES(2, '经理', '管理部门员工');
    INSERT INTO job (id, jname, description) VALUES(3, '销售员', '向客人推销产品');
    INSERT INTO job (id, jname, description) VALUES(4, '文员', '使用办公软件');
    commit;
    
    -- 员工表
    CREATE TABLE emp (
      id NUMBER PRIMARY KEY, -- 员工id
      ename VARCHAR2(50), -- 员工姓名
      job_id NUMBER, -- 职务id  外键
      mgr NUMBER , -- 上级领导编号 *
      joindate DATE, -- 入职日期
      salary NUMBER(7,2), -- 工资 99999.99
      bonus NUMBER(7,2), -- 奖金 99999.99
      dept_id NUMBER, -- 所在部门编号  外键
      CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
      CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
    );
    
    -- 添加员工
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
    (1001,'孙悟空',4,1004,to_date('2000-12-17','yyyy-MM-dd'),'8000.00',NULL,20);
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
    (1002,'卢俊义',3,1006,to_date('2001-02-20','yyyy-MM-dd'),'16000.00','3000.00',30);
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
    (1003,'林冲',3,1006,to_date('2001-02-22','yyyy-MM-dd'),'12500.00','5000.00',30);
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
    (1004,'唐僧',2,1009,to_date('2001-04-02','yyyy-MM-dd'),'29750.00',NULL,20);
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
    (1005,'李逵',4,1006,to_date('2001-09-28','yyyy-MM-dd'),'12500.00','14000.00',30);
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
    (1006,'宋江',2,1009,to_date('2001-05-01','yyyy-MM-dd'),'28500.00',NULL,30);
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
    (1007,'刘备',2,1009,to_date('2001-09-01','yyyy-MM-dd'),'24500.00',NULL,10);
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
    (1008,'猪八戒',4,1004,to_date('2007-04-19','yyyy-MM-dd'),'30000.00',NULL,20);
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
    (1009,'罗贯中',1,NULL,to_date('2001-11-17','yyyy-MM-dd'),'50000.00',NULL,10);
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
    (1010,'吴用',3,1006,to_date('2001-09-08','yyyy-MM-dd'),'15000.00','0.00',30);
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
    (1011,'沙僧',4,1004,to_date('2007-05-23','yyyy-MM-dd'),'11000.00',NULL,20);
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
    (1012,'李逵',4,1006,to_date('2001-12-03','yyyy-MM-dd'),'9500.00',NULL,30);
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
    (1013,'小白龙',4,1004,to_date('2001-12-03','yyyy-MM-dd'),'30000.00',NULL,20);
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
    (1014,'关羽',4,1007,to_date('2002-01-23','yyyy-MM-dd'),'13000.00',NULL,NULL);
    commit;
    
    -- 工资等级表
    CREATE TABLE salarygrade(
      grade NUMBER PRIMARY KEY,  -- 等级
      losalary NUMBER, -- 最低工资
      hisalary NUMBER -- 最高工资
    );
    
    -- 添加5个工资等级
    INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
    (1,7000,12000);
    INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
    (2,12010,14000);
    INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
    (3,14010,20000);
    INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
    (4,20010,30000);
    INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
    (5,30010,99990);
    commit;

### 4.2 内连接 ###

> 拿左表的记录去匹配右表的记录，若符合条件显示（二张表的交集）

*  ##### 需求 #####
    
    > 1.查询所有员工的姓名，工资,入职日期和所在部门名称
 *  ##### sql语句 #####
    
        select 
           e.ename,
           e.salary,
           e.joindate,
           d.dname
        from emp e, dept d
        where e.dept_id = d.id;

### 4.3 外连接 ###

*  ###### 左外连接 ######
    
    > 展示左表全部，再去匹配右表记录，若条件符合显示，若条件不符合显示NULL
    
    语法
    
        select ... from 左表 left [outer] join 右表 on 连接条件;
 *  ###### 右外连接 ######
    
    > 展示右表全部，再去匹配左表记录，若条件符合显示，若条件不符合显示NULL
    
    语法
    
        select ...from 左表 right [outer] join 右表 on 连接条件;
 *  ###### sql演示 ######
    
        # 左外连接（推荐）
        -- 查询所有员工信息及对应的部门名称
        SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;
        -- 查询所有部门及对应的员工信息
        SELECT * FROM dept d LEFT JOIN emp e ON e.dept_id = d.id;
        
        # 右外连接（了解）
        -- 查询所有部门及对应的员工信息
        SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id = d.id;

### 4.4 子查询 （重点复习) ###

> 一条select语句执行结果，作为另一条select语法的一部分。其实就是select语句的嵌套！

*  ###### 语法 ######
    
        -- 【1】查询结果单值
        SELECT MAX(salary) FROM emp;
        -- 【2】查询结果单列多行
        SELECT salary FROM emp;
        -- 【3】查询结果多行多列
        SELECT * FROM emp;
    
    **规律**
    
        -- 【1】子查询结果为单列，肯定作为条件在where后面使用
        		select ... from 表名 where 字段 in (子查询);
        -- 【2】子查询结果为多列，一般作为虚拟表在from后面使用
        		select ... from (子查询) as 表别名;
 *  #### sql演示 ####
    
     *  ###### 子查询结果为单行单列 ######
        
            -- 【1】查询工资最高的员工是谁？ 
            SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
        
            -- 【2】查询工资小于平均工资的员工有哪些？
            -- 2.1 先求出平均工资
            SELECT AVG(salary) FROM emp;
            -- 2.2 查询低于平均工资的员工
            SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);
     *  ###### 子查询结果为单列多行 ######
        
            -- 【1】查询工资大于5000的员工，来自于哪些部门的名字  
            -- 1.1 查询工资大于5000的员工
            SELECT dept_id FROM emp WHERE salary >5000;
            -- 1.2 来自于哪些部门的名字  
            SELECT * FROM dept WHERE id IN(SELECT dept_id FROM emp WHERE salary >5000);
        
            -- 【2】查询开发部与财务部所有的员工信息
            -- 2.1 根据部门名称，查询部门主键
            SELECT id FROM dept WHERE `name` IN('开发部','财务部');
            -- 2.2 根据部门id查询员工信息
            SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE `name` IN('开发部','财务部'));
     *  ###### 子查询结果为多列多行 ######
        
            -- 【1】 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
            -- 1.1 根据 join_date 查询 2011-11-11 之后的员工信息
            SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11';
            
            -- 1.2 根据1步骤的结果，跟部门表关联
            SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;

t WHERE id IN(SELECT dept\_id FROM emp WHERE salary >5000);  
\`\`\`

```sql
    -- 【2】查询开发部与财务部所有的员工信息
    -- 2.1 根据部门名称，查询部门主键
    SELECT id FROM dept WHERE `name` IN('开发部','财务部');
    -- 2.2 根据部门id查询员工信息
    SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE `name` IN('开发部','财务部'));
    
    ```

*  ###### 子查询结果为多列多行 ######
    
        -- 【1】 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
        -- 1.1 根据 join_date 查询 2011-11-11 之后的员工信息
        SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11';
        
        -- 1.2 根据1步骤的结果，跟部门表关联
        SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
 *

[feaf699d26554702865776ab3b30fa51.png_pic_center]: https://img-blog.csdnimg.cn/feaf699d26554702865776ab3b30fa51.png#pic_center
[b1a0df73576d4616b079fcdb69e7fb5b.png_pic_center]: https://img-blog.csdnimg.cn/b1a0df73576d4616b079fcdb69e7fb5b.png#pic_center
[554ad70350e244afb04812794df53327.png_pic_center]: https://img-blog.csdnimg.cn/554ad70350e244afb04812794df53327.png#pic_center
[2a955bb67a044cd58f4cf9c48b53cfb0.png_pic_center]: https://img-blog.csdnimg.cn/2a955bb67a044cd58f4cf9c48b53cfb0.png#pic_center
[1f2293477e08470cb2b4b12af23620ac.png_pic_center]: https://img-blog.csdnimg.cn/1f2293477e08470cb2b4b12af23620ac.png#pic_center
[8ae096ac147c466ba00355e51bf4d872.png_pic_center]: https://img-blog.csdnimg.cn/8ae096ac147c466ba00355e51bf4d872.png#pic_center
[1ddb3f2a5af445788b13c2ba11b4fc1b.png_pic_center]: https://img-blog.csdnimg.cn/1ddb3f2a5af445788b13c2ba11b4fc1b.png#pic_center
[73f834fa4d5a44bea13976e2452c22aa.png_pic_center]: https://img-blog.csdnimg.cn/73f834fa4d5a44bea13976e2452c22aa.png#pic_center