Oracle和Mysql数据库

数据库

  • Oracle 体系结构与基本概念
    • 体系结构
    • 基本概念
      • 表空间(users)和数据文件
      • 段、区、块
      • Oracle数据库的基本元素
  • Oracle数据库启动和关闭
    • Oracle数据库启动
    • Oracle数据库关闭
  • Sqlplus
    • sqlplus 登录数据库管理系统
      • 使用sqlplus登录Oracle数据库
      • 远程登录
      • 解锁用户
      • 修改用户密码
      • 查看当前语言环境
    • sqlplus基本操作
      • 显示当前用户
      • 查看当前用户下的表
      • 查看员工表的结构
      • 设置行宽
      • 设置页大小
      • 永久设置行宽、页宽
      • 设置员工ename的列宽
      • 设置薪水的列宽
    • Oracle默认方案
      • scott方案
        • 业务场景
        • 根据公司业务抽离出的表
      • hr方案
  • SQL
    • SQL语言的类型
      • DML
      • DDL
      • DCL
    • 基本 SELECT 语句
      • 查询案例
        • 查询员工号、姓名、薪水
        • 别名:as
        • DISTINCT关键字
        • 算数运算 + - * / 和 NULL值
      • 过滤和排序数据
        • where条件过滤
          • 查询10号部门的员工信息
          • 查询"KING"的信息
          • 修改系统当前日期格式
          • 比较运算
          • like:模糊查询
          • 逻辑运算
        • order by 排序
          • 使用 ORDER BY 子句排序
      • 单行函数
        • 字符函数
        • 数值函数
        • 时间函数
        • 日期函数
        • 转换函数
        • 通用函数
        • 条件表达式
      • 分组函数
        • 分组数据
          • 查询“部门”的平均工资
          • group by后面有多列的情况
        • having
          • 查询平均薪水大于2000的部门
          • 求10号部门的平均工资
      • 多表查询
        • 笛卡尔积
        • 等值连接
        • 不等值连接
        • 外连接
        • 自连接
      • 子查询
        • 定义子查询需要注意的问题
        • 主、子查询在不同表间进行
        • 在主查询的where select having from 放置子查询
        • 在from后面放置的子查询
        • 单行子查询只能使用单行操作符,多行子查询只能使用多行操作符
          • 单行子查询
          • 多行子查询
        • 子查询中null
        • 一般不在子查询中使用order by
        • 一般先执行子查询,再执行主查询
      • 集合运算
        • 集合运算符
          • 集合运算需要注意的问题
    • 数据处理
      • 插入数据insert

Oracle 体系结构与基本概念

体系结构

Oracle服务器 :是一个数据管理系统(RDBMS),它提供开放的、 全面的、近乎完整的信息管理。由1个数据库和一个(或多个)实例组成。数据库位于硬盘上,实例位于内存中。
在这里插入图片描述

基本概念

表空间(users)和数据文件

1:n(表空间:数据文件)
逻辑概念:表空间由多个数据文件组成。位于实例上,在内存中。
物理概念:数据文件,位于硬盘之上。(C:\app\Administrator\oradata\orcl目录内后缀为.DBF的文件)
一个表空间可以包含一个或者是多个数据文件。
/home/oracle/app/oradata/orcl

段、区、块

在这里插入图片描述
段存在于表空间中;段是区的集合;区是数据块的集合;数据块会被映射到磁盘块。
在这里插入图片描述
在这里插入图片描述

Oracle数据库的基本元素

  • 方案schema
    方案就是一个集合,包含多个表(tables)、视图(views)、序列(sequences)、存储过程(store procudures)、索引(indexes)、同义词(synonyms)。
    oracle中每个用户都对应一个方案,方案名就是用户名。
  • 表table
    表示存储数据的逻辑容器,类似excel多行多列的表格。表创建的时候要定义它的每一列的列名以及该列的数据类型。(如:name char(10),表示定义列名为name类型为字符串型)
    创建表之后才可以往里边添加数据。
  • 视图view
    从多张表或者其他视图提取出用户所关心数据的一张虚表
  • 存储过程
    一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译。
  • 序列sequence
    是oracle提供的用于产生一系列唯一数字的数据库对象。
  • 索引index
    用于加快数据的检索,类似于书籍的索引。
  • 同义词synonyms
    别名的意思,和视图的功能类似。就是一种映射关系。

Oracle数据库启动和关闭

前提条件: 使用Oracle用户登录Linux操作系统
[root@localhost ~]# su - oracle
在这里插入图片描述
数据库管理员用户:
用户名:sys
密 码:sys

Oracle数据库启动

第一步: 通过数据库管理员登录Oracle数据库
[oracle@localhost ~]$ sqlplus / as sysdba;
在这里插入图片描述

第二步: 启动Oracle数据库
SQL> startup
在这里插入图片描述
第三步: 启动监听服务

SQL> quit
[oracle@localhost ~]$ lsnrctl start
在这里插入图片描述

Oracle数据库关闭

第一步: 通过数据库管理员登录Oracle数据库
[oracle@localhost ~]$ sqlplus / as sysdba;
在这里插入图片描述
第二步: 关闭Oracle数据库

SQL> shutdown immediate
在这里插入图片描述

第三步: 关闭监听服务
SQL> quit
[oracle@localhost ~]$ lsnrctl stop
在这里插入图片描述

Sqlplus

Oracle的sqlplus是与oracle数据库进行交互的客户端工具,借助sqlplus可以查看、修改数据库记录。在sqlplus中,可以运行sqlplus命令与sql语句
Sqlplus命令是用来修改以及调整sqlplus工具的命令。
Sql语句是用来让服务器执行相关动作的命令。

sqlplus 登录数据库管理系统

前提条件是Oracle数据库必须启动。

使用sqlplus登录Oracle数据库

sqlplus 用户名/密码
[oracle@localhost ~]$ sqlplus scott/11
在这里插入图片描述

远程登录

sqlplus 用户名/密码@//ip/实例名 ↙
远程登录oracle服务器方式,如(sqlplus scott/11@//192.168.161.130/orcl)
在这里插入图片描述
在这里插入图片描述

解锁用户

[oracle@localhost ~]$ sqlplus / as sysdba;
SQL> alter user scott account unlock;

在这里插入图片描述

修改用户密码

[oracle@localhost ~]$ sqlplus / as sysdba;
SQL> alter user scott identified by 11;
在这里插入图片描述

查看当前语言环境

SQL> select userenv('language') from dual;
在这里插入图片描述

sqlplus基本操作

前提条件是使用scott用户登录
[oracle@localhost ~]$ sqlplus scott/11

显示当前用户

SQL> show user
在这里插入图片描述

查看当前用户下的表

SQL> select *from tab;
在这里插入图片描述

查看员工表的结构

SQL> desc dept;
在这里插入图片描述

设置行宽

默认显示不好看,因此需要设置行宽。
在这里插入图片描述SQL> set linesize 140;

在这里插入图片描述

设置页大小

SQL> set pagesize 100;
在这里插入图片描述

永久设置行宽、页宽

在这里插入图片描述

在这里插入图片描述
永久设置edit打开为gedit。
在这里插入图片描述

设置员工ename的列宽

在这里插入图片描述

SQL> col ename for a6 (a表示字符串)
在这里插入图片描述

设置薪水的列宽

col sal for 9999(一个9表示一位数字)
在这里插入图片描述

Oracle默认方案

安装oracle数据库默认会自动创建scott和hr两个用户以及其方案,这些都是oracle提供给我们学习和练习的数据,我们直接在上边练习就好。

scott方案

业务场景

Scott(斯科特)是一家软件公司的数据分析师,公司为其提供专门的数据库管理账号以及方案。
公司有总裁一名(president)、经理(manager)、店员(clerk)、销售专员(salesman),分析师(analyst)若干。
公司设置有多个部门:会计部(accounting)、研发部(researching)、销售部(sales)。

根据公司业务抽离出的表
  • 员工信息表EMP(Employee)
  • 部门表DEPT(Department)
  • 工资等级表(SALGRADE)
  • 奖金表(BONUS)

在这里插入图片描述

grade: 薪水等级
losal: 最低薪水
hisal: 最高薪水
在这里插入图片描述

deptno: 部门编号
dname: 部门名称
loc: 部门位置

在这里插入图片描述
empno: 员工编号
ename: 员工姓名
job: 员工工种
mgr: 员工上司
hiredate: 雇佣日期
sal: 员工薪水
comm: 员工奖金
deptno: 员工部门编号
在这里插入图片描述

hr方案

0

在这里插入图片描述

SQL

结构化查询语言(Structured Query Language)简称SQL,是对数据库进行增、删、查、改等操作的语言。
SQL 是1986年10 月由美国国家标准局(ANSI)通过的数据库语言美国标准,接着,国际标准化组织(ISO)颁布了SQL正式国际标准。1989年4月,ISO提出了具有完整性特征的SQL89标准,1992年11月又公布了SQL92标准.
各种不同的数据库对SQL语言的支持与标准存在着细微的不同,这是因为,有的产品的开发先于标准的公布,另外,各产品开发商为了达到特殊的性能或新的特性,需要对标准进行扩展。

SQL语言的类型

DML

数据库中,称呼增删改查,为DML语句。(Data Manipulation Language 数据操纵语言),如:
增 insert
删 delete
改 update
查 select

DDL

数据定义语言(Data Definition Language)。如:
create table(创建表)
alter table(修改表)
truncate table(清空表)
drop table(删除表)
create view(视图)
create index(索引)
create sequence(序列)
create synonym(同义词)

DCL

数据控制语言(Data Control Language)。如:
commit(提交)
rollback(回滚)

基本 SELECT 语句

基本语法格式为:
在这里插入图片描述
语法描述说明:
花括号{}括起的部分为必填部分。
中括号[]括起来的部分为可选部分。
多种形式的语法用竖线|来表示并列单选,或者的意思。

SQL语句使用注意事项:

  • SQL 语言大小写不敏感。
  • SQL 可以写在一行或者多行,以分号;作为一条sql语句的结束标志。
  • 关键字不能被缩写也不能分行。
  • 各子句一般要分行写。
  • 使用缩进提高语句的可读性。

查询案例

查询员工号、姓名、薪水

在这里插入图片描述
增加查询年薪(使用表达式)

在这里插入图片描述

别名:as

在这里插入图片描述
解决乱码问题,修改系统字符集。
在这里插入图片描述
解决方法

DISTINCT关键字

去除结果集重复的行。
在这里插入图片描述

算数运算 + - * / 和 NULL值
  • 乘除的优先级高于加减
  • 优先级相同时,按照从左至右运算
  • 可以使用括号改变优先级

查询: 员工号、姓名、月薪、年薪、奖金、年收入。
在这里插入图片描述
结果不正确。没有奖金的员工,年收入不正确。
NULL值问题:
包含NULL值的表达式都为空。
NULL != NULL
任何算数运算碰到NULL值都变成NULL,任何逻辑运算碰到NULL值都变成假

解决:滤空函数:nvl(a, b) 如果a为NULL, 函数返回b。所以:sal * 12 + nvl(comm, 0) 年收入。
在这里插入图片描述
NULL != NULL举例:
在这里插入图片描述
在SQL中,判断一值是否等于另外一值不用“=” 和“!=”而使用is和is not。
在这里插入图片描述

过滤和排序数据

where条件过滤
查询10号部门的员工信息

在这里插入图片描述

查询"KING"的信息

在这里插入图片描述

修改系统当前日期格式

查询入职日期为1981年11月17日的员工:
在这里插入图片描述
查看系统当前的日期格式:
在这里插入图片描述
获取系统当前日期格式:

SQL> select * from v$nls_parameters在这里插入图片描述
修改日期格式:

SQL> alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd '

在这里插入图片描述
再次查询:
在这里插入图片描述
改回系统默认格式:SQL> alter session set NLS_DATE_FORMAT = ‘DD-MON-RR’;

比较运算

普通比较运算符:

= 等于(不是==)> 大于
>= 大于等于< 小于
<= 小于等于<> 不等于(也可以是!=)

查询薪水不等于1250的员工信息:
在这里插入图片描述
查询工资在1000-2000之间的员工:
在这里插入图片描述
在这里插入图片描述
查询81年2月至82年2月入职的员工信息:
在这里插入图片描述

IN:在集合中。(not in 不在集合中)

在这里插入图片描述
查询部门号为10或20的员工信息:
在这里插入图片描述

在这里插入图片描述

like:模糊查询

‘%’匹配任意多个字符。
‘_’匹配一个字符。
查询名字以S开头的员工:
在这里插入图片描述

查询名字是4个字的员工:
在这里插入图片描述
转义字符:

增加测试例子:向表中插入员工:
SQL> insert into emp(empno, ename, sal, deptno) values(1001, ’ TOM_ABC ', 3000, 10)

查询名字中包含_的员工:

SQL> select * from emp where ename like '%\_% ' escape '\';
在这里插入图片描述
转义单引号本身:

SQL> select 'hello '' world' from dual;

使用两个单引号来完成转义。

在这里插入图片描述

逻辑运算

AND 逻辑与
OR 逻辑或
NOT 逻辑非

如果
……where 表达式1 and 表达式2;
……where 表达式2 and 表达式1;
这两句SQL语句功能一样吗?效率一样吗?
SQL在解析where的时候,是从右至左解析的。
所以and时应该将易假的值放在右侧,or时应该将易真的值放在右侧。

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

order by 排序

order by + 列名,表达式,别名,序号。 注意:语法要求order by 子句应放在select的结尾。

使用 ORDER BY 子句排序

• ASC(ascend): 升序。默认采用升序方式。
• DESC(descend): 降序

查询员工信息,按月薪排序:
在这里插入图片描述

order by后有多列时,列名之间用逗号隔分,order by会同时作用于多列。
在这里插入图片描述
在这里插入图片描述

desc 只作用于最近的一列,两列都要降序排,则需要两个desc。即:
在这里插入图片描述
order by + 列名,表达式,别名,序号。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
查询员工信息, 按奖金由高到低排序:
在这里插入图片描述

结果前面的值为NULL, 数据在后面,如果是一个100页的报表,这样显示肯定不正确。较为人性化的显示应该将空值放在最后, 即:
在这里插入图片描述

排序的规则

  • 可以按照select语句中的列名排序
  • 可以按照别名列名排序
  • 可以按照select语句中的列名的顺序值排序
  • 如果要按照多列进行排序,则规则是先按照第一列排序,如果相同,则按照第二列排序;以此类推

单行函数

单行函数:只对一行数据进行计算,产生一个结果。函数可以没有参数,但必须要有返回值。如:concat、nvl

  • 操作数据对象 hello world
  • 接受参数返回一个结果
  • 只对一行进行变换
  • 每行返回一个结果
  • 可以转换数据类型
  • 可以嵌套
  • 参数可以是一列或一个值
字符函数

操作对象是字符串。
大致可分为两大类:一类是大小写控制函数,主要有
lower 转小写
在这里插入图片描述

upper 转大写
在这里插入图片描述

initcap 首字母大写
在这里插入图片描述

另一类是字符控制函数:有CONCAT、SUBSTR、LENGTH/LENGTHB、INSTR、LPAD | RPAD、TRIM、REPLACE

concat(a,b):连接字符串a和b,只能连接两个

在这里插入图片描述

注意:SQL双引号“”表示别名,使用‘’来表示字符串。

另一种连接字符串的方法,使用||,可以做到多个字符串连接
在这里插入图片描述
在这里插入图片描述

substr(a, b):从a中,第b位开始取(计数从1开始),取到结尾。
在这里插入图片描述

substr(a, b, c):从a中,第b位开始,向右取c位。
在这里插入图片描述

length:字符数, lengthb:字节数
在这里插入图片描述

instr:在母串中查找子串, 找到返回下标,计数从1开始。没有返回0
在这里插入图片描述

lpad:左填充,参1:待填充的字符串,参2:填充后字符串的总长度(字节), 参3:填充什么
rpad:右填充。
在这里插入图片描述

trim:去掉前后指定的字符
在这里插入图片描述
在这里插入图片描述

replace:替换
在这里插入图片描述
在这里插入图片描述

数值函数

ROUND: 四舍五入
在这里插入图片描述

TRUNC: 截断
在这里插入图片描述

MOD: 求余

在这里插入图片描述

时间函数

在Oracle中日期型的数据,既有日期部分,也有时间部分。下一节介绍日期部分。
在这里插入图片描述
显示 昨天、今天、明天:
在这里插入图片描述
计算员工的工龄:
在这里插入图片描述

日期函数

上面求取员工工龄的结果不精确,如果想将其算准确,可以使用日期函数来做。

months_between:两个日期值相差的月数(精确值) 跟between…and无关
在这里插入图片描述

add_months:在某个日期值上,加上多少的月,正数向后计算,负数向前计算。
在这里插入图片描述

last_day:日期所在月的最后一天。
在这里插入图片描述

next_day:指定日期的下一个日期

在这里插入图片描述
在这里插入图片描述

转换函数

在不同的数据类型之间完成转换。将“123” 转换为 123。有隐式转换和显式转换之分。

隐式转换:
在这里插入图片描述

显式转换:
在这里插入图片描述

隐式转换的前提条件是:被转换的对象是可以转换的。

在这里插入图片描述
显示转换:借助to_char(数据,格式)、to_number、to_date函数来完成转换。
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述
如果隐式转换和显示转换都可以使用,应该首选哪个呢?

SQL优化:如果隐式、显式都可以使用,应该首选显式,这样可以省去Oracle的解析过程。

在固定的格式里加入自定义的格式,是可以的,必须要加“”。
在这里插入图片描述
反向操作:已知字符串“2024-2-1 18:30:20 ”转化成日期。
在这里插入图片描述
在这里插入图片描述

查询员工的薪水:2位小数, 本地货币代码, 千位符
在这里插入图片描述
将$2,975.00转化成数字:
在这里插入图片描述

通用函数

这些函数适用于任何数据类型,同时也适用于空值:

  • NVL (expr1, expr2)
    在这里插入图片描述

  • NVL2 (expr1, expr2, expr3)

  • NULLIF (expr1, expr2)

  • COALESCE (expr1, expr2, …, exprn)

nvl2: 是nvl函数的增强版。 nvl2(a, b, c) 当a = null 返回 c, 否则返回b。
在这里插入图片描述

nullif: nullif(a, b) 当 a = b 时返回null, 不相等的时候返回a值。
在这里插入图片描述

coalesce: coalesce(a, b, c, …, n) 从左向右找参数中第一个不为空的值。
在这里插入图片描述

条件表达式

例子:老板打算给员工涨工资, 要求:
总裁(PRESIDENT)涨1000,经理(MANAGER)涨800,其他人涨400。请将涨前、涨后的薪水列出。

但是在SQL中无法实现if else 逻辑。当有这种需求的时候,可以使用case 或者 decode
case: 是一个表达式,其语法为:
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
decode:是一个函数,其语法为:
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

分组函数

分组函数作用于一组数据,并对一组数据返回一个值。
如:AVG、COUNT、MAX、MIN、SUM操作的是一组数据,返回一个结果。
求员工的工资总额:
在这里插入图片描述

员工人数:
在这里插入图片描述

平均工资:
在这里插入图片描述

求员工的平均奖金:
在这里插入图片描述

☆NULL空值:组函数都有自动滤空功能(忽略空值),所以:
在这里插入图片描述

如何屏蔽 组函数 的滤空功能:
在这里插入图片描述
但是实际应用中,结果为14和结果为4都有可能对,看问题本身是否要求统计空值。

count函数:求个数,如果要求不重复的个数,使用distinct。
求emp表中的工种:
在这里插入图片描述

分组数据

group by

按照group by 后给定的表达式,将from后面的table进行分组。针对每一组,使用组函数。

查询“部门”的平均工资

SQL> select deptno,avg(sal)from emp group by deptno;
在这里插入图片描述
上述SQL语句可以抽象成:select a, 组函数(x) from 表 group by a; 这样的格式。

如果select a, b 组函数(x) …… group by 应该怎么写?
注意:在SELECT 列表中所有没有包含在组函数中的列,都必须在group by的后面出现。所以上问应该写成group by a, b;没有b语法就会出错,不会执行SQL语句。但,反之可以。group by a,b,c; c可以不出现在select语句中。

group by后面有多列的情况

SQL> select deptno,job,avg(sal) from emp group by deptno,job order by 1;

因为deptno, job 两列没有在组函数里面,所以必须同时在group by后面。
该SQL的语义:按部门,不同的职位统计平均工资。先按第一列分组,如果第一列相同,再按第二列分组。
所以查询结果中,同一部门中没有重复的职位。

having

使用 HAVING 过滤分组:

  1. 行已经被分组。
  2. 使用了组函数。
  3. 满足HAVING 子句中条件的分组将被显示。

语法:
在这里插入图片描述

查询平均薪水大于2000的部门

分析:该问题实际上是在分组的基础上过滤分组。
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
在这里插入图片描述

注意:
不能在 WHERE 子句中使用组函数。
可以在 HAVING 子句中使用组函数。

从功能上讲,where和having都是将满足条件的结果进行过滤。但是差别是where子句中不能使用 组函数!所以上句中的having不可以使用where代替。

求10号部门的平均工资

在这里插入图片描述
在子句中没有使用组函数的情况下,where、having都可以,应该怎么选择?

SQL优化: 尽量采用where。
如果有分组的话,where是先过滤再分组,而having是先分组再过滤。当数据量庞大如1亿条,where优势明显。

多表查询

多表查询的过程中其实就会构造多个表的一个笛卡尔积。

笛卡尔积

笛卡尔积就是两个集合的乘积计算,其实跟我们普通的乘法分配律相像,不过是作用于集合:

如果有一个集合M(a,b) 和集合N(c,d) , 那么集合的乘积
M x N = (a,b) x (c,d)= ( a x (c,d), b x (c,d) ) = ( ac,ad,bc,bd)

具体到我们的数据库表如下:
在这里插入图片描述
笛卡尔积的行数 = table1的行数 x table2的行数
笛卡尔积的列数 = table1的列数 + table2的列数

比如:
select * from emp,dept; 会得到如下的一个表
在这里插入图片描述
多表查询就是按照给定条件(连接条件),从笛卡尔全集中选出正确的结果。
比如上图理应该两个deptno 相同的数据才是有意义的数据。

根据连接条件的不同可以划分为:等值链接、不等值链接、外链接、自连接 。

在这里插入图片描述

等值连接

从概念上,区分等值连接和不等值连接非常简单,只需要辨别where子句后面的条件,是“=”为等值连接。不是“=”为不等值连接。

查询员工信息:员工号 姓名 月薪和部门名称

通常在进行多表查询的时,会给表起一个别名,使用“别名.列名”的方式来获取数据,直接使用“表名.列名”语法上是允许的,但是实际很少这样用。
SQL> select e.empno,e.ename,e.sal,e.deptno,d.dname,d.deptno from emp e , dept d where e.deptno= d.deptno;
在这里插入图片描述
如果:select e.empno, e.ename, e.sal, e.deptno, d.dname, d.deptno from emp e, dept d;
直接得到的是笛卡尔全集。其中有错误结果。所以应该加 where 条件进行过滤。
如果有N个表,where后面的条件至少应该有N-1个。

不等值连接

将上面的问题稍微调整下,查询员工信息:员工号 姓名 月薪 和 薪水级别(salgrade表)

分析:
在这里插入图片描述

看到员工总的薪水级别,共有5级,员工的薪水级别应该满足 >=当前级别的下限,<=该级别的上限:

SQL> select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;

在这里插入图片描述

外连接

按部门统计员工人数,显示如下信息:
部门号 部门名称 人数

分析:
人数:一定是在emp表中,使用count()函数统计emp表中任一非空列均可。
部门名称:在dept表dname中,直接读取即可。
部门号:任意,两张表都有。

在这里插入图片描述
注意:由于使用了组函数count(),所以组函数外的d.deptno和d.dname必须放到group by后。

但是select * from dept发现40号部门没有显示出来,原因是40号部门没有员工,where没满足。结果不对,40号部门没有员工,应该在40号部门位置显示0。
在这里插入图片描述
我们希望: 在最后的结果中,包含某些对于where条件来说不成立的记录 (外连接的作用)

左外连接:当 where e.deptno=d.deptno 不成立的时候,=左边所表示的信息,仍然被包含。
写法:与叫法相反:where e.deptno=d.deptno(+)

右外连接:当 where e.deptno=d.deptno 不成立的时候,=右边所表示的信息,仍然被包含。
写法:依然与叫法相反:where e.deptno(+)=d.deptno

以上我们希望将没有员工的部门仍然包含到查询的结果当中。因此应该使用外链接的语法。

右外链接写法
在这里插入图片描述
左外链接写法
在这里插入图片描述

自连接

核心,通过表的别名,将同一张表视为多张表。

查询员工信息:xxx的主管是 yyy
在这里插入图片描述

在这里插入图片描述
可是观察结果我们发现,老板KING没有,老板KING的上司应该是老板KING。
因此使用左自连接,当条件不满足,=左边所表示的信息,仍然被包含。
在这里插入图片描述

子查询

子查询语法很简单,就是select 语句的嵌套使用。

查询工资比SCOTT高的员工信息:

分析:两步即可完成
1.查出SCOTT的工资 SQL> select ename, sal from emp where ename=‘SCOTT’ ,其工资为3000。
2.查询比3000高的员工 SQL> select * from emp where sal>3000

通过两步可以将问题结果得到。子查询,可以将两步合成一步。
——子查询解决的问题:问题本身不能一步求解的情况。
在这里插入图片描述
子查询语法格式:
在这里插入图片描述

定义子查询需要注意的问题
  1. 小括号( )
  2. 主查询和子查询可以是不同表,只要子查询返回的结果主查询可以使用即可
  3. 可以在主查询的where、select、having、from后都可以放置子查询
  4. 不可以在主查询的group by后面放置子查询 (SQL语句的语法规范)
  5. 一般先执行子查询(内查询),再执行主查询(外查询);但是相关子查询除外
  6. 一般不在子查询中使用order by, 但在Top-N分析问题中,必须使用order by
  7. 单行子查询只能使用单行操作符;多行子查询只能使用多行操作符
  8. 子查询中的null值
主、子查询在不同表间进行

查询部门名称是“SALES”的员工信息
在这里插入图片描述
主查询:查询员工信息。select * from emp;
子查询:负责得到部门名称(在dept表中)、部门号对应关系。select deptno from dept where dname=‘SALES’

主查询,查询的是员工表emp,子查询,查询的是部门表dept。是两张不同的表。

将该问题使用“多表查询”解决:

在这里插入图片描述
两种方式哪种好呢?

※SQL优化: 理论上,既可以使用子查询,也可以使用多表查询,尽量使用“多表查询”。子查询有2次from,但是还得看数据量,如果数据量比较大,那么做多表查询产生的笛卡尔积也会相当庞大。

不同数据库处理数据的方式不尽相同,如Oracle数据库中,子查询地位比较重要,做了深入的优化。有可能实际看到结果是子查询快于多表查询。

在主查询的where select having from 放置子查询

子查询可以放在select后,但,要求该子查询必须是 单行子查询:(该子查询本身只返回一条记录,2+叫多行子查询)。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

在from后面放置的子查询

表,代表一个数据集合、查询结果(SQL)语句本身也代表一个集合。

查询员工的姓名、薪水和年薪:
在这里插入图片描述
将select 语句放置到from后面,表示将select语句的结果,当成表来看待。这种查询方式在Oracle语句中使用比较频繁。

单行子查询只能使用单行操作符,多行子查询只能使用多行操作符
单行子查询

单行子查询就是该条子查询执行结束时,只返回一条记录(一行数据)。
使用单行操作符:
=、>、>=、<、<=、<>

在这里插入图片描述
在这里插入图片描述
也可以在having子句中使用:
在这里插入图片描述
上面的例子告诉我们:

  1. 单行子查询,只能使用单行操作符(=号、>号等)
  2. 在一个主查询中可以有多个子查询。
  3. 子查询里面可以嵌套多层子查询。
  4. 子查询也可以使用组函数。子查询也是查询语句,适用于前面所有知识。
多行子查询

子查询返回2条记录以上就叫多行。

多行操作符有:
IN:等于列表中的任意一个
ANY :和子查询返回的任意一个值比较
ALL:和子查询返回的所有值比较

IN(表示在集合中):
查询部门名称为SALES和ACCOUNTING的员工信息。
在这里插入图片描述
使用 多表查询 来解决该问题:
在这里插入图片描述
ANY(表示和集合中的任意一个值比较):

查询薪水比30号部门任意一个员工高的员工信息:

SQL> select * from emp where sal > (select sal from emp where deptno=30); 正确吗?

这样是错的,子句返回多行结果。而‘>’是单行操作符。 ——应该将‘>’替换成‘> any’
在这里插入图片描述

实际上>集合的任意一个值,就是大于集合的最小值。
在这里插入图片描述
ALL(表示和集合中的所有值比较):

查询薪水比30号部门所有员工高的员工信息。
在这里插入图片描述
在这里插入图片描述

子查询中null

判断一个值等于、不等于空,不能使用=和!=号,而应该使用is 和 not。

如果集合中有NULL值,不能使用not in。如: not in (10, 20, NULL),但是可以使用in。为什么呢?
先看一个例子:
查询不是老板的员工信息:
在emp表中有列mgr,该列表示该员工的上司的员工号是多少。那么,如果一个员工的这列是空值,那么说明这员工是老板。
在这里插入图片描述

一般不在子查询中使用order by

一般情况下,子查询使用order by或是不使用order by对主查询来说没有什么意义。子查询的结果给主查询当成集合来使用,所以没有必要将子查询order by。

但,在Top-N分析问题中,必须使用order by。

一般先执行子查询,再执行主查询

含有子查询的SQL语句执行的顺序是,先子后主。

但,相关子查询例外。

集合运算

查询部门号是10和20的员工信息

思考有几种方式解决该问题 ?
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述
还可以用集合运算
select * from emp where deptno=10 加上
select * from emp where deptno=20

集合运算所操作的对象是两个或者多个集合,而不再是表中的列(select一直在操作表中的列)

集合运算符

集合运算的操作符。A∪B、A∩B、A – B

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

集合运算需要注意的问题

1.参与运算的各个集合必须列数相同,且类型一致。
2.采用第一个集合的表头作为最终使用的表头。 (列名别名也只能在第一个集合上起)
3.可以使用括号()先执行后面的语句。

问题:按照部门统计各部门不同工种的工资情况,要求按如下格式输出:

在这里插入图片描述

在这里插入图片描述
第一部分数据是按照deptno和job进行分组;select 查询deptno、job、sum(sal)
第二部分数据是直接按照deptno分组即可,与job无关;select 只需要查询deptno,sum(sal)
第三部分数据不按照任何条件分组,即group by null;select 查询sum(sal)

数据处理

插入数据insert

使用 INSERT 语句向表中插入数据。其语法为:
在这里插入图片描述
如果:values后面的值,涵盖了表中的所有列,那么table的列名可以省略不写。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/254224.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

【网工】华为设备命令学习(服务器发布)

本次实验主要是内网静态nat配置没&#xff0c;对外地址可以理解为一台内网的服务器&#xff0c;外网设备可以ping通内网的服务器设备&#xff0c;但是ping不通内网的IP。 除了AR1设备配置有区别&#xff0c;其他设备都是基础IP的配置。 [Huawei]int g0/0/0 [Huawei-GigabitEt…

前端框架学习 Vue(3)vue生命周期,钩子函数,工程化开发脚手架CLI,组件化开发,组件分类

Vue 生命周期 和生命周期的四个阶段 Vue生命周期:一个Vue实例从创建 到 销毁 的整个过程 生命周期四个阶段 :(1)创建 (2)挂载 (3)更新 (4)销毁 Vue生命周期函数(钩子函数) Vue生命周期过程中,会自动运行一些函数,被称为[生命周期钩子] ->让开发者可以在[特定阶段] 运行自…

【初识爬虫+requests模块】

爬虫又称网络蜘蛛、网络机器人。本质就是程序模拟人使用浏览器访问网站&#xff0c;并将需要的数据抓取下来。爬虫不仅能够使用在搜索引擎领域&#xff0c;在数据分析、商业领域都得到了大规模的应用。 URL 每一个URL指向一个资源&#xff0c;可以是一个html页面&#xff0c;一…

鸿蒙(HarmonyOS)项目方舟框架(ArkUI)之Span组件

鸿蒙&#xff08;HarmonyOS&#xff09;项目方舟框架&#xff08;ArkUI&#xff09;之Span组件 一、操作环境 操作系统: Windows 10 专业版、IDE:DevEco Studio 3.1、SDK:HarmonyOS 3.1 二、Span组件 鸿蒙&#xff08;HarmonyOS&#xff09;作为Text组件的子组件&#xff0…

【MySQL】:深入理解并掌握DML和DCL

&#x1f3a5; 屿小夏 &#xff1a; 个人主页 &#x1f525;个人专栏 &#xff1a; MySQL从入门到进阶 &#x1f304; 莫道桑榆晚&#xff0c;为霞尚满天&#xff01; 文章目录 &#x1f4d1;前言一. DML1.1 添加数据1.2 修改数据1.3 删除数据 二. DCL2.1 管理用户2.2 权限控制…

RabbitMQ-1.介绍与安装

介绍与安装 1.RabbitMQ1.0.技术选型1.1.安装1.2.收发消息1.2.1.交换机1.2.2.队列1.2.3.绑定关系1.2.4.发送消息 1.2.数据隔离1.2.1.用户管理1.2.3.virtual host 1.RabbitMQ 1.0.技术选型 消息Broker&#xff0c;目前常见的实现方案就是消息队列&#xff08;MessageQueue&…

基于AST实现一键自动提取替换国际化文案

背景&#xff1a;在调研 formatjs/cli 使用&#xff08;使用 formatjs/cli 进行国际化文案自动提取 &#xff09;过程中&#xff0c;发现有以下需求formatjs/cli 无法满足&#xff1a; id 需要一定的语义化&#xff1b; defaultMessage和Id不能直接hash转换&#xff1b; 需要…

简化版SpringMVC

简化版SpringMVC web.xml xml version"1.0" encoding"UTF-8"?> <web-app version"2.5" xmlns"http://java.sun.com/xml/ns/javaee" xmlns:xsi"http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation&quo…

MySQL学习记录——칠 表操作

文章目录 1、了解2、创建和插入1、基本创建和插入2、插入并更新on duplicate3、插入并替换replace 3、Retrieve1、查询select2、条件查询where3、结果排序order by4、限制行数limit 4、更新Update5、删除delete6、去重7、聚合函数&#xff08;5个&#xff09;1、count2、sum3、…

第十六篇【传奇开心果系列】Python的OpenCV库技术点案例示例:图像质量评估

传奇开心果短博文系列 系列短博文目录Python的OpenCV库技术点案例示例短博文系列博文目录前言一、图像质量评估方法和相关函数的介绍二、均方误差示例代码三、峰值信噪比示例代码四、结构相似性指数示例代码五、视频质量评估示例代码六、OpenCV均方根误差计算示例代码七、OpenC…

Vivado开发FPGA使用流程、教程 verilog(建立工程、编译文件到最终烧录的全流程)

目录 一、概述 二、工程创建 三、添加设计文件并编译 四、线上仿真 五、布局布线 六、生成比特流文件 七、烧录 一、概述 vivado开发FPGA流程分为创建工程、添加设计文件、编译、线上仿真、布局布线&#xff08;添加约束文件&#xff09;、生成比特流文件、烧录等步骤&a…

05 06 Verilog基础语法与应用讲解

05. 1. 位操作 计数器实验升级&#xff0c;设计8个LED灯以每个0.5s的速率循环闪烁&#xff08;跑马灯&#xff09; 1.1 方法1&#xff1a;使用移位操作符<<来控制led灯的循环亮灭 设计代码 Verilog中&#xff0c;判断操作的时候不加位宽限定是可以的&#xff0c;比如i…

MySQL之体系结构

华子目录 MySQL简介MySQL的特性MySQL版本MySQL常见版本 数据库排名网站MySQL结构体系查看最大连接数查询缓存配置情况 一条SQL语句执行流程 MySQL简介 MySQL是一个小型关系数据库管理系统&#xff0c;开发者为瑞典MySQL AB公司。在2008年1月16号被sun公司10亿美金收购。2009年…

IntelliJ IDE 插件开发 | (六)内部模式的使用

系列文章 IntelliJ IDE 插件开发 |&#xff08;一&#xff09;快速入门IntelliJ IDE 插件开发 |&#xff08;二&#xff09;UI 界面与数据持久化IntelliJ IDE 插件开发 |&#xff08;三&#xff09;消息通知与事件监听IntelliJ IDE 插件开发 |&#xff08;四&#xff09;来查收…

Oracle systemstate、gdb、dbx介绍

当数据库出现严重的性能问题或者hang了的时候&#xff0c; 可能最常用的办法就是重启数据库&#xff0c;简单有效解决问题&#xff1b;但是重启后如何追踪问题的根本原因成了难题&#xff0c;很多信息随着重启也消失不见了&#xff0c;让追查问题变的十分棘手&#xff0c;这时就…

Spring第二天

一、第三方资源配置管理 说明&#xff1a;以管理DataSource连接池对象为例讲解第三方资源配置管理 1 管理DataSource连接池对象 问题导入 配置数据库连接参数时&#xff0c;注入驱动类名是用driverClassName还是driver&#xff1f; 1.1 管理Druid连接池【重点】 数据库准备…

Android 识别车牌信息

打开我们心爱的Android Studio 导入需要的资源 gradle //开源车牌识别安卓SDK库implementation("com.github.HyperInspire:hyperlpr3-android-sdk:1.0.3")button.setOnClickListener(v -> {Log.d("Test", "");try (InputStream file getAs…

#Z0458. 树的中心2

题目 代码 #include <bits/stdc.h> using namespace std; struct ff {int z,len; }; vector<ff> vec[300001]; int n,u,v,w,dp[300001][2],ans 1e9; void dfs(int x,int fa) {for(int i 0;i < vec[x].size();i){ff son vec[x][i];if(son.z ! fa){dfs(son.z,…

搭建macOS开发环境-1:准备工作

请记住&#xff1a; 最重要的准备工作永远是&#xff1a;备份数据 !!! 通过图形界面检查 Mac 的 CPU 类型&#xff1a; 在搭载 Apple 芯片的 Mac 电脑上&#xff0c;“关于本机”会显示一个标有“芯片”的项目并跟有相应芯片的名称&#xff1a; 通过命令行检查Mac的CPU类型 …

ASP.NET Core 预防开放式重定向攻击

写在前面 为预防钓鱼网站的常用套路&#xff0c;在进行 Web 应用程序的开发时&#xff0c;原则上应该将所有由用户提交的数据视为不可信。如果应用程序中包含了基于 URL 内容重定向的功能&#xff0c;需要确保这种类型的重定向操作只能在应用本地完成&#xff0c;或者明确判断…