数据库mysql

数据库基础

  • ------------------------------------------------------------------------
  • 第1章 绪论
    • 1.1 数据库系统概述
    • 1.2 数据模型
    • 1.3 数据库系统的结构
      • 1.3.1 数据库系统模式的概念
      • 1.3.2 数据库系统的三级模式结构与二级映像
    • 1.4 MySQL环境搭建
  • ------------------------------------------------------------------------
  • 第 2 章 关系数据库
    • 2.1 关系数据结构
    • 2.2 关系操作
    • 2.3 关系的完整性
      • 2.3.1 实体完整性和参照完整性
      • 2.3.2 用户定义的完整性
    • 2.4 关系代数
      • 2.4.1 集合运算
      • 2.4.2 关系运算
    • 2.5 关系型数据库设计规则
      • 2.5.1 表、记录、字段
  • ------------------------------------------------------------------------
  • 第三章 关系数据库标准语言SQL与Mysql
    • 3.1 数据查询select:连接过滤,分组过滤,选择去重与排序
      • 3.1.1 基础SSELECT语句
      • 3.1.2 单表查询
        • 1.select选择列
        • 2.where选择行
        • 3.排序与分页
      • 3.1.3 多表查询 ★ \bigstar ★ \bigstar ★ \bigstar
        • 1.外连接
      • 3.1.4 函数
        • 1.单行函数
        • 2.多行函数
          • 聚集函数:
          • GROUP BY
          • HAVING
      • 3.1.4 子查询
        • 1.单行子查询 与 多行子查询
        • 2.相关子查询 与 不相关子查询
        • ★ \bigstar ★ \bigstar 总结:
    • 3.2 DDL、DCL
      • 3.1.1 DDL:创建、更新、删除(数据库,表)
      • 3.1.2 DCL-COMMIT 和 ROLLBACK:清空和回滚
    • 3.3 DML(表的增删改)
    • 3.5 视图
  • 第6章 数据库设计规范
    • 6.1 问题的提出
    • 6.2 范式
  • ------------------------------------------------------------------------
  • 第10章 事务
    • 10.1 事务基础知识
    • 10.2 并发控制概述
    • 10.2 可串行化调度与冲突可串行化调度
      • 问题
      • 1.在cmd 中输入了错误mysql命令后,如何退出?




------------------------------------------------------------------------

第1章 绪论

在这里插入图片描述

1.1 数据库系统概述

▶ \blacktriangleright 课前问题

  1. 数据与数据库的概念?
  2. 如何科学的组织和储存数据?
    ∙ \bullet 使用数据库管理系统

▶ \blacktriangleright 基本概念

  1. 数据:
    ∙ \bullet 数据是数据库中储存的基本对象,是描述事物的符号记录。
    ∙ \bullet 数据的含义称为数据的语义。
  2. 数据库DB
    ∙ \bullet 数据库是长期储存在计算机内、有组织的、可共享的大量数据的集合
    ∙ \bullet 数据库中的数据按一定的数据模型组织、描述和储存。
  3. 数据库管理系统DBMS
    ∙ \bullet 定义:
    位于用户应用与操作系统之间的一层数据管理软件;是计算机基础软件,是一个大型复杂的软件系统。
    ∙ \bullet 主要功能:
    ∙ \bullet ∙ \bullet 数据定义功能:定义数据库中的数据对象。
    ∙ \bullet ∙ \bullet 数据的组织储存和管理:分类组织储存和管理各种数据、实现数据之间的联系、提供多种存取方法,提高储存空间利用率和方便存取。
    ∙ \bullet ∙ \bullet 数据的操纵功能:实现对数据库的基本操作(增删改查)
    ∙ \bullet ∙ \bullet 数据库的事务管理和运行管理:保证正确运行。
    ∙ \bullet ∙ \bullet 数据库的建立和维护功能:批量装载、数据库转储等
  4. 数据库系统
    ∙ \bullet 定义:数据库系统是由数据库、数据库管理系统、应用程序和数据库管理员 组成的 存储、管理、处理和维护数据的系统
    在这里插入图片描述
  5. 数据库系统的特点
    ∙ \bullet 数据结构化
    ∙ \bullet 数据的共享性高、冗余程度低且易扩充
    减少数据冗余,节约空间,避免数据的不一致性
    ∙ \bullet 数据独立性高
    数据库中的数据与应用程序相互独立(物理储存和逻辑结构)。
    ∙ \bullet 数据由数据库管理系统统一管理和控制
    保证数据完整性、安全性、并发控制、数据恢复


1.2 数据模型

▶ \blacktriangleright 数据模型

  1. 定义
    ∙ \bullet 对现实世界数据特征的抽象,是现实世界的模拟。
    ∙ \bullet 数据模型是严格定义的一组概念的集合,精确地描述了系统的静态特性、动态特性和完整性约束条件。
    在这里插入图片描述

▶ \blacktriangleright 数据模型的组成要素

  1. 数据结构:
    ∙ \bullet 描述数据库的组成对象。(对象的类型、内容、性质)
    ∙ \bullet 描述对象之间的联系
    ∙ \bullet 描述系统的静态特性。
  2. 数据操作:
    ∙ \bullet 对数据库中各种对象的实例允许执行的操作的集合,包括操作以及有关操作规则。
    ∙ \bullet 描述数据的动态特性
  3. 数据的完整性约束条件:
    ∙ \bullet 数据的完整性约束条件是一组完整性规则的集合
    ∙ \bullet 完整性规则:给定数据模型中数据和联系所具有的制约与依存规则。

▶ \blacktriangleright 两类数据模型

  1. 概念模型
    按用户的观点来对数据和信息建模,用于数据库的设计。
  2. 逻辑模型和物理模型
    ∙ \bullet 逻辑模型:按计算机系统的观点对数据建模,主要用于数据库管理系统的实现。
    ∙ \bullet 物理模型:是系统内部对数据的存储、存取方式。
    在这里插入图片描述

▶ \blacktriangleright 概念模型

  1. 基本概念:
    ∙ \bullet 实体、属性、码、实体型、实体集、联系
    ∙ \bullet 联系:
    实体内部的联系:指个属性之间的联系;
    实体之间的联系:指不同实体集之间的联系,一对一,一对多,多对多。
    在这里插入图片描述

▶ \blacktriangleright 主要的逻辑数据模型

  1. 层次模型:
    用树形结构来表示各类实体以及实体间的联系。
  2. 网状模型
    允许结点没有双亲结点,允许结点有多个双亲结点;
    层次模型是网状模型的一个特例
  3. 关系模型
    ∙ \bullet 基本概念:关系(一张表)、元组(行)、属性(列)、码、域(属性的取值范围)、分量
    ∙ \bullet 关系模式:对关系的描述
    表示为:关系名(属性1,属性2… 属性n)


1.3 数据库系统的结构

1.3.1 数据库系统模式的概念

▶ \blacktriangleright 模式

  1. 模式的定义:
    ∙ \bullet 是对数据库逻辑结构和特征的描述;
    ∙ \bullet 仅仅涉及数据模型中型的描述,不涉及具体的值。(型是指对某一类数据的 结构和属性的说明
    例:学生的记录/实体型:(学号、姓名、性别、年龄)
    记录/实体值:(202011、李明、男、20)
  2. 模式的值(实例)
    ∙ \bullet 模式的一个具体值称为模式的一个实例,同一个模式可以有多个实例;
    例:同一个“学生选课数据库”模式:20年的实例和21年的实例不同
    ∙ \bullet 模式是稳定的,实例是变动的。
  3. 模式的组成
    ∙ \bullet 由关系模式组成(表)
    例:“学生选课数据库”模式:由学生、课程和学生选课3个关系模式组成

1.3.2 数据库系统的三级模式结构与二级映像

▶ \blacktriangleright 三级模式结构

  1. 模式:
    ∙ \bullet 是对数据库中全体数据的逻辑结构和特征的描述;
    ∙ \bullet 仅仅涉及数据模型中型的描述,不涉及具体的值。(型是指对某一类数据的 结构和属性的说明
  2. 外模式
    ∙ \bullet 定义:子模式,是局部数据的逻辑结构和特征的描述,外模式是模式的子集。
  3. 内模式
    ∙ \bullet 定义:存储模式,一个数据库只有一个内模式。是数据库物理结构和储存方法的描述

▶ \blacktriangleright 模式映像

  1. 外模式/模式映像
    ∙ \bullet 一个模式有任意多个外模式,每一个外模式对应一个映像;
    ∙ \bullet 定义了该外模式与模式之间的对应关系;
    ∙ \bullet 数据与程序的逻辑独立性
    模式改变,外模式可保持不变,程序不变。
  2. 内模式/模式映像
    ∙ \bullet 数据库中只有一个模式,也只有一个内模式;
    ∙ \bullet 定义了数据全局逻辑结构与储存结构之间的对应关系;
    ∙ \bullet 数据与程序的物理独立性
    内模式改变,模式可保持不变,程序不变。

▶ \blacktriangleright 各种人员的数据视图
在这里插入图片描述



1.4 MySQL环境搭建




------------------------------------------------------------------------

第 2 章 关系数据库

关系模型、关系代数、关系演算

2.1 关系数据结构

从集合论角度给出关系数据结构的形式化定义

▶ \blacktriangleright 关系

  1. 关系定义
    ∙ \bullet D 1 ∗ D 2 ∗ … D n D_1*D_2*…D_n D1D2Dn的子集叫作在域 D 1 , D 2 , … D n D_1,D_2,…D_n D1,D2,Dn上的关系,表示为 R ( D 1 , D 2 , … D n ) R(D_1,D_2,…D_n) R(D1,D2,Dn)
    ∙ \bullet R:关系名
    ∙ \bullet n:关系的目或度(Degree)
  2. 元组
    ∙ \bullet 关系中的每个元素(d1,d2,…,dn)叫作一个n元组(n-tuple)或简称元组,通常用t表示。
  3. 属性
    ∙ \bullet 对每列的 起一个名字,称为属性(Attribute)
    n目关系必有n个属性

  4. ∙ \bullet 候选码(Candidate key)
    若关系中的某一属性组的值能唯一地标识一个元组,则称该属性组为候选码。候选码至少有一个。
    简单的情况:候选码只包含一个属性(如:学号,ID)
    ∙ \bullet 全码(All-key)
    最极端的情况:关系模式的所有属性组是这个关系模式的候选码
    称为全码(All-key)

▶ \blacktriangleright 关系模式

  1. 关系模式定义
    关系模式(Relation Schema)是型
    关系是值
    关系模式是对关系的描述
  2. 关系模式是对关系的描述
    ∙ \bullet 元组集合的结构
    属性构成
    属性来自的域
    属性与域之间的映象关系
    ∙ \bullet 完整性约束条件
  3. 关系模式形式化地表示为:R (U,D,DOM,F)
    R:关系名
    U:组成该关系的属性名集合
    D:U中属性所来自的域
    DOM:属性向域的映象集合
    F:属性间数据的依赖关系的集合
    ∙ \bullet 关系模式通常可以简记为:R(U)或R(A1,A2…,An)
  4. 关系与关系模式
    ∙ \bullet 关系模式
    对关系的描述
    静态的、稳定的
    ∙ \bullet 关系
    关系是关系模式在某一时刻的状态或内容
    动态的、随时间不断变化的

2.2 关系操作


2.3 关系的完整性

2.3.1 实体完整性和参照完整性

关系模型必须满足的完整性约束条件称为关系的两个不变性,应该由关系系统自动支持。

▶ \blacktriangleright 实体完整性

  1. 实体完整性规则
    关系的主属性不能取空值,空值就是“不知道”或“不存在”或“无意义”的值。

▶ \blacktriangleright 参照完整性

  1. 外码定义
    ∙ \bullet 设F是基本关系R的一个或一组属性,但不是关系R的主码。
    如果F与基本关系S的主码K相对应,则称F是R的外码。
    ∙ \bullet 基本关系R称为参照关系(Referencing Relation)
    ∙ \bullet 基本关系S称为被参照关系(Referenced Relation)或目标关系(Target Relation)
  2. 参照完整性规则
    若属性(或属性组)F是基本关系R的外码它与基本关系S的主码K相对应(基本关系R和S不一定是不同的关系),则对于R中每个元组在F上的值必须为:
    ∙ \bullet 或者取空值(F的每个属性值均为空值)
    ∙ \bullet 或者等于S中某个元组的主码值

2.3.2 用户定义的完整性

应用领域需要遵循的约束条件,体现了具体领域中的语义约束

▶ \blacktriangleright 用户定义的完整性

  1. 针对某一具体关系数据库的约束条件,反映某一具体应用所涉及的数据必须满足的语义要求
  2. 关系模型应提供定义和检验这类完整性的机制,以便用统一的系统的方法处理它们,而不需由应用程序承担这一功能


2.4 关系代数

2.4.1 集合运算

▶ \blacktriangleright 集合运算

  1. 定义:
    ∙ \bullet 二元运算,包括并、差、交、笛卡儿积。
    ∙ \bullet 集合运算将关系看成元组的集合,其运算是从关系的“水平”方向,即行的角度来进行;
  2. 并(union)
    由属于R或属于S的元组组成。
  3. 差(except)
    由属于R而不属于S的所有元组组成。
  4. 交(intersection)
    由既属于R又属于S的元组组成。
  5. 笛卡积(cartesian product)
    ∙ \bullet 是广义的笛卡儿积(extended cartesian product),因为这里笛卡儿积的元素是元组。
    ∙ \bullet 两个分别为n目和m目的关系R和S的笛卡儿积是一个(n+m)列的元组的集合。
    ∙ \bullet 若R有k1个元组,S有k2个元组,则关系R和关系S的笛卡儿积有k1×k2个元组。记作

2.4.2 关系运算

▶ \blacktriangleright 关系运算概述

  1. 定义:
    关系运算包括选择、投影、连接、除运算等。
  2. 设关系模式为 R ( A 1 , A 2 … , A n ) R(A_1,A_2…,A_n) R(A1,A2,An)
    ∙ \bullet R R R:关系模式的一个关系
    ∙ \bullet t ∈ R t\in R tR:t是R中的一个元组。
    ∙ \bullet t [ A i ] t[A_i] t[Ai]:表示元组中相应于属性 A i A_i Ai的一个分量
    ∙ \bullet A A A:A称为属性列或属性组。
  3. 连接 t r t s ^ \widehat{t_rt_s} trts
    ∙ \bullet t r t s ^ \widehat{t_rt_s} trts 称为元组的连接(R为n目关系,S为m目关系,t∈R,t∈S)
    ∙ \bullet t r t s ^ \widehat{t_rt_s} trts 是一个n+m列的元组。
  4. 象集 Z x Z_x Zx
    给定一个关系R(X,Z),X和Z为属性组。
    Z x = { t [ Z ] ∣ t ∈ R , t [ X ] = x } Z_x= \{t[Z] |t∈R,t[X]=x \} Zx={t[Z]tR,t[X]=x}
    ∙ \bullet 它表示R中属性组X上值为x的元组,在属性组Z上对应的分量z的集合
    例: Z x 1 = { Z 1 , Z 2 , Z 3 } Z_{x1}= \{Z_{1},Z_{2},Z_{3}\} Zx1={Z1,Z2,Z3}
    在这里插入图片描述

▶ \blacktriangleright 四个关系运算

  1. 选择 σ \sigma σ
    ∙ \bullet 定义:
    选择又称为限制(Restriction),在关系R中选择满足给定条件的诸元组。
    ∙ \bullet 选择运算符的含义
    σ F ( R ) = { t ∣ t ϵ R ∧ F ( t ) = ′ 真 ′ } \sigma_F(R)=\{ t| t \epsilon R∧F(t)='真' \} σF(R)={ttϵRF(t)=}
    ∙ \bullet 选择条件F:是一个逻辑表达式,取值为“真”或“假”
    基本形式: X 1 θ Y 1 X_1 \theta Y_1 X1θY1
    θ : \theta : θ:表示比较运算符,它可以是>,≥,<,≤,=或<>
  2. 投影 Π \Pi Π
    ∙ \bullet 定义:
    从R中选择出若干属性列组成新的关系
    记作: Π A ( R ) = { t [ A ] ∣ t ∈ R } \Pi_A (R)= \{t[A] | t∈R \} ΠA(R)={t[A]tR} ,A:R中的属性列。
    ∙ \bullet 投影之后不仅取消了原关系中的某些列,而且还可能取消某些元组(避免重复行)
    ∙ \bullet 例:查询学生关系中有那些系: Π S d e p t ( S t u d e n t ) \Pi_{Sdept}(Student) ΠSdept(Student)
  3. 连接 ⊳ ⊲ \rhd\lhd
    ∙ \bullet 定义:
    从两个关系的笛卡尔积中选取属性间满足一定条件的元组。(A和B:分别为R和S上度数相等且可比的属性组)
    R ⊳ ⊲ S = { t r t s ^ ∣ t r ∈ R ∧ t s ∈ S ∧ t r [ A ] θ t s [ B ] } R \rhd\lhd S=\{ \widehat{t_r t_s} | t_r ∈R∧t_s \in S\land t_r[A]\theta t_s[B]\} RS={trts trRtsStr[A]θts[B]}
    ∙ \bullet 详细定义:
    连接运算从关系R和S的广义笛卡尔积RXS中,选取 R关系在A属性组上的值与 S关系在B属性组上的值满足比较关系 θ \theta θ的元组。
    ∙ \bullet 悬浮元组(dangling tuple)
    关系R中某些元组有可能在S中不存在公共属性上值相等的元组,这些被舍弃的元组称为悬浮元组(dangling tuple)。
    ∙ \bullet 外连接(outerjoin)
    如果把悬浮元组也保存在结果关系中,而在其他属性上填空值(NULL)。

  4. 设关系R除以关系S的结果为关系T,则T包含所有在R不在S中的属性及其值,且T的元组与S的元组的所有组合都在R中。在这里插入图片描述

▶ \blacktriangleright 连接运算分类

  1. 连接运算分类
    非等值连接、等值连接、自然连接
  2. 等值连接:
    从关系R和S的广义笛卡尔积RXS中选取A、B属性值相等的元组
  3. 自然连接:
    一种特殊的等值连接,要求比较的两个分量必须是同名的属性组,且在结果中把重复的属性列去掉。
    在这里插入图片描述
    在这里插入图片描述

▶ \blacktriangleright 输入输出分析法

  1. 例题1
    在这里插入图片描述
  2. 例题2
    在这里插入图片描述
    在这里插入图片描述


2.5 关系型数据库设计规则

2.5.1 表、记录、字段

▶ \blacktriangleright 表、记录、字段

  1. E-R(entity-relationship,实体-联系)模型中有三个主要概念是:
    实体集 、 属性 、 联系集 。
  2. 实体集(class)
    对应于数据库中的一个表(table)
  3. 实体(instance)
    对应于数据库表中的一行(row),也称为一条记录(record)。
  4. 属性(attribute)
    对应于数据库表中的一列(column),也称为一个字段(field)。
    在这里插入图片描述
  5. 对应关系
    数据库中的一个表 ⇒ \rArr Java或Python中的一个类
    表中的一条数据 ⇒ \rArr 类中的一个对象(或实体)
    表中的一个列 ⇒ \rArr 类中的一个字段、属性(field)



------------------------------------------------------------------------

第三章 关系数据库标准语言SQL与Mysql

3.1 数据查询select:连接过滤,分组过滤,选择去重与排序

3.1.1 基础SSELECT语句

▶ \blacktriangleright 基础SSELECT语句

  1. 列的别名:as “xxx "
    ∙ \bullet as(可省略)
    ∙ \bullet 列的别名可以使用一对” "引起来,使用场景:别名中存在空格
  2. 去除重复行
    DISTINCT
  3. 空值参与运算
    ∙ \bullet 所有运算符或列值遇到null值,运算的结果都为null
    ∙ \bullet 在 MySQL 里面, 空值不等于空字符串。一个空字符串的长度是 0,而一个空值的长度是空。而且,在 MySQL 里面,空值是占用空间的
  4. 着重号 ``
    表名与SQL关键字重名
  5. 查询常数
    在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的,而不是从数据表中动态取出的。
  6. 显示表结构
    DESCRIBE:DESC显示了表中字段的详细信息
空值参与运算解决方案:
SELECT employee_id,salary "月工资",salary *(1+IFNULL(commission_pct,0))*12 "年工资",commission_pct
FROM employees;



3.1.2 单表查询

先查询所有行,返回完整查询结果,再看查某几列,然后对查询结果排序或去重

1.select选择列

▶ \blacktriangleright 选择表中若干列:指定列、全部列、经过计算的值

  1. 查询格式
    在这里插入图片描述
    如:
    在这里插入图片描述
  2. 关键字解释
    ∙ \bullet SELECT子句:指定要查询的
    ∙ \bullet FROM子句:指定查询对象(基本表或视图)
    ∙ \bullet WHERE子句:指定查询条件
    ∙ \bullet GROUP BY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
    ∙ \bullet HAVING短语:只有满足指定条件的组才予以输出
    ∙ \bullet ORDER BY子句:对查询结果表按指定列值的升序或降序排序
    在这里插入图片描述
例子:
#1. 查询员工12个月的工资总和,并起别名为ANNUAL SALARY
SELECT employee_id,last_name,salary*12 "ANNUAL SALARY"
FROM employees;

#2.查询employees表中去除重复的job id以后的数据
SELECT DISTINCT job id
FROM employees;

#3.查询工资大于12000的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary >12000;

#4.查询员工号为176的员工的姓名和部门号
SELECT employee_id,last_name,department_id
FROM employees
WHERE employee_id = 176;

#5.显示表departments的结构,并查询其中的全部数据
DESCRIBE departments;
SELECT *FROM departments;

2.where选择行

▶ \blacktriangleright 选择表中若干元组:查询满足条件的元组

  1. 四种判断运算符
    算术运算符、比较运算符、逻辑运算符、位运算符

▶ \blacktriangleright 算术运算符

  1. 加法与减法
    ∙ \bullet 在MySQL中+只表示数值相加
    ∙ \bullet 如果遇到非数值类型,先尝试转成数值,如果转失败,就按0计算。(补充:MySQL中字符串拼接要使用字符串函数CONCAT()实现)
  2. 乘法与除法
    ∙ \bullet 一个数乘以整数1和除以整数1后仍得原数;
    ∙ \bullet 一个数乘以浮点数1和除以浮点数1后变成浮点数,数值与原数相等;
    ∙ \bullet 一个数除以整数后,不管是否能除尽,结果都为一个浮点数;并保留到小数点后4位;
    ∙ \bullet 在数学运算中,0不能用作除数,在MySQL中,一个数除以0为NULL。
    在这里插入图片描述
  3. 求模(求余)运算符
    %或MOD:

▶ \blacktriangleright 比较运算符

  1. 比较运算符
    ∙ \bullet null参与判断
    只要有nul1参与判断,结果就为null
    ∙ \bullet 等号运算符
    如果等号两边的值、字符串或表达式中有一个为NULL,则比较结果为NULL
    ∙ \bullet 安全等于运算符
    两边的操作数的值都为NULL时,返回的结果为1,其他返回结果与等于运算符相同。
    在这里插入图片描述
  2. 空值判断:
    IS NULL / IS NOT NULL
  3. 最小值运算符
    语法格式为:LEAST(值1,值2,…,值n)。
  4. 最大值运算符
    语法格式为:GREATEST(值1,值2,…,值n)。其中,n表示参数列表中有n个值。
  5. BETWEEN AND运算符 (包含边界)
    ∙ \bullet 使用的格式:
    SELECT D FROM TABLE WHERE C BETWEEN A AND B,此时,当C大于或等于A,并且C小于或等于B时,结果为1,否则结果为0。
    ∙ \bullet 否定 NOT BETWEEN AND
  6. IN运算符
    ∙ \bullet 用于判断给定的值是否是IN列表中的一个值,如果是则返回1,否则返回0。
    ∙ \bullet 如果给定的值为NULL,或者IN列表中只存在NULL,则结果为NULL
    ∙ \bullet 否定 NOT IN
  7. LIKE运算符
    ∙ \bullet LIKE运算符主要用来匹配字符串,通常用于模糊匹配,如果满足条件则返回1,否则返回0。
    ∙ \bullet 如果给定的值或者匹配条件为NULL,则返回结果为NULL。
    ∙ \bullet 通配符
    “%”:匹配0个或多个字符。
    “_”:只能匹配一个字符。
    ∙ \bullet 转义字符 “\”
  8. REGEXP运算符
    用来匹配字符串,语法格式为: expr REGEXP 匹配条件 。
#1.null参与判断:
# = :只要有nul1参与判断,结果就为null
# <=> :安全等于
SELECT 1 = NULL,NULL = NULL
FROM DUAL

SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct = NULL;#全为NULL

练习1:查询表中commission pct为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct <=> NULL;

练习2:查询表中commission pct不为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;SELECT last_name,salary,commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL;

练习3:查询last_name中以字符'a'开头的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE last_name LIKE 'a%'

练习:查询第2个字符是_且第3个字符是'a'的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE last_name LIKE '_\_a%'


▶ \blacktriangleright 逻辑运算符

  1. 逻辑运算符
    AND的优先级高于OR
    在这里插入图片描述
  2. 运算符左右两边地位平等
  3. 异或的使用场景
    两个条件只满足其中一个

3.排序与分页

▶ \blacktriangleright 排序数据:ORDER BY子句

  1. 操作
    对查询结果按照属性列升序(ASC) 或者 降序(DESC) 排列.
  2. 排序规则
    ∙ \bullet 使用 ORDER BY 子句排序
    ASC(ascend): 升序(默认)
    DESC(descend):降序
    ∙ \bullet 升序降序
    以左上为基点往下看,由小到大为升序。
  3. 二级排序
    ∙ \bullet 对两列按照不同排序规则排序
    ∙ \bullet 首先排序的第一列必须有相同的列值,才会对第二列进行排序。 如果第一列数据中所有值都是唯一的,将不再对第二列进行排序。
  4. 多列排序
    ∙ \bullet 可以使用不在SELECT列表中的列排序
    ∙ \bullet 对多列进行排序
    即多级排序
    ∙ \bullet 列的别名进行排序
    列的别名只能在ORDER BY中使用,不能在WHERE中使用。
  5. ORDER BY 子句在SELECT语句的结尾
二级排序
练习:显示员工信息,按照department_id的降序排列,salaryl的升序排列
SELECT employee id,salary,department id
FROM employees
ORDER BY department id DESC,salary ASC;

▶ \blacktriangleright 分页

  1. 定义
    所谓分页显示,就是将数据库中的结果集,一段一段显示出来需要的条件。
  2. MySQL中使用 LIMIT 实现分页
    ∙ \bullet L I M I T [ 位 置 偏 移 量 , ] 行 数 LIMIT [位置偏移量,] 行数 LIMIT[,]
    ∙ \bullet 第二个参数“行数”指示返回的记录条数。
  • 分页显式公式
    (当前页数-1)*每页条数,每页条数

3.1.3 多表查询 ★ \bigstar ★ \bigstar ★ \bigstar

▶ \blacktriangleright 多表查询概述

  1. 多表连接
    ∙ \bullet 区分重复的列名:
    多个表中有相同列时,必须在列名之前加上表名前缀。
    ∙ \bullet 连接条件:
    连接 n个表,至少需要n-1个连接条件。
    ∙ \bullet 表的别名
    使用别名可以简化查询。
    列名前使用表名前缀可以提高查询效率。
  2. 多表查询分类
    ∙ \bullet 内连接:
    合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行在这里插入图片描述
    ∙ \bullet 外连接:
    两个表在连接过程中除了返回满足连接条件的行以外,还返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
    在这里插入图片描述
-------------------------------------------------------------------------
多表查询分类
1.等值连接与非等值连接
#等值连接
#非等值连接:查询员工工资等级
SELECT last_name,salary,grade_level
FROM employees e,job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

SELECT * FROM employees;

2.自连接与非自连接:即将一张表看作两张表,然后用两个不同的别名进行查询
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.manager_id =mgr.employee_id;

3.内连接与外连接:
#A:员工
#B:部门
#AB员工和部门都存在

3.1 内连接:关键字 JOININNER JOINCROSS JOIN 的含义是一样的,都表示内连接
#合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
# 即:交集∪,共106SELECT employee_id,department_name
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id


3.2 外连接:除了查询满足条件的记录以外,外连接还可以查询某一方不满足条件的记录。
#两个表在连接过程中除了返回满足连接条件的行以外,还返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外连接
#sql99实现外连接:join ...on...
SELECT employee_id,department_name,city
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON l.location_id;

1.外连接
  1. SQL92实现外连接
    ∙ \bullet Oracle 对 SQL92 支持较好,而 MySQL 则不支持 SQL92 的外连接。
    ∙ \bullet MySQL 则不支持 SQL 的满连接。
    ∙ \bullet 在 SQL92 中采用**(+)代表从表所在的位置。即左或右外连接中, (+) 表示哪个是从表
    在这里插入图片描述
  2. SQL99实现外连接
    join xx on sb:加入xx,由谁的关系加入的的
  3. 实现连接三种方式:
    ∙ \bullet WHERE:
    适用于所有关联查询
    ∙ \bullet JOIN…ON :
    只能和JOIN一起使用,只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写,但分开写可读性更好。
    ∙ \bullet USING:
    只能和JOIN一起使用,而且要求两个关联字段在关联表中名称一致,而且只能表示关联字
    段值相等
  • UNION关键字:
    合并查询结果
    ∙ \bullet UNION 操作符:返回两个查询的结果集的并集,去重。
    ∙ \bullet UNION ALL操作符:返回两个查询的结果集的并集。不去重。
    注意:执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据,或者不需要去除重复的数据,则尽量使用UNION ALL语句,以提高数据查询的效率。
-------------------------------------------------------------------------
SQL99语法实现多表查询
#外连接语法:
SELECT 字段列表
FROM AJOIN BON 关联条件
WHERE 等其他子句;

-------------------------------------------------------------------------
实现连接三种方式:
#1.把关联条件写在where后面
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;

#2.把关联条件写在on后面,只能和JOIN一起使用
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;

SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;

SELECT last_name,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;

#3.把关联字段写在using()中,只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同,而且只能表示=
#查询员工姓名与基本工资
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);


在这里插入图片描述

-------------------------------------------------------------------------
#sql99实现外连接:满FULL\左LEFT\右RIGHT OUT JOIN ...ON...

MySQL实现外连接:
3.内连接与外连接:
#A:员工107
#B:部门122
#AB员工和部门都存在106

3.1 内连接:关键字 JOININNER JOINCROSS JOIN 的含义是一样的,都表示内连接
#合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
# 即:AB交集,共106SELECT employee_id,department_name
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id


3.2 外连接:除了查询满足条件的记录以外,外连接还可以查询某一方不满足条件的记录。
#两个表在连接过程中除了返回满足连接条件的行以外,还返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外连接
#sql99实现外连接:join ...on...
SELECT employee_id,department_name,city
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON l.location_id;

#左满:所有员工,即A107SELECT employee_id,last_name,department_name
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;  #无部门的员工 A-AB=1条


#右满:所有部门,即:B122SELECT employee_id,last_name,department_name
FROM employees e 
RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;   #无员工的部门 即:B-AB=16-------------------------------------------------------------------------
#满连接:
#MySQL不支持FULL JOIN,但是可以用 LEFT JOIN UNION RIGHT JOIN 代替
#UNIONUNION ALL操作符

方法一:AB去重 
#左满:所有员工,即A
SELECT employee_id,department_name
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.department_id
UNION
#右满:所有部门,即:B
SELECT employee_id,department_name
FROM employees e 
RIGHT JOIN departments d
ON e.department_id = d.department_id

方法二: A∪(B-AB)
#左满:所有员工,即A
SELECT employee_id,department_name
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
#右满:所有部门,即:B
SELECT employee_id,department_name
FROM employees e 
RIGHT JOIN departments d
ON e.department_id = d.department_id
#无员工的部门 即:B-AB=16WHERE e.department_id IS NULL;
-------------------------------------------------------------------------
练习:
#1.显示所有员工的姓名,部门号,部门名称
#外连接:**所有** 有员工没有部门
SELECT last_name,e.department_id,d.department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`= d.`department_id`

#2.查询90号部门员工的e.job_id和90号部门的d.laction_id
#无外连接:即忽略没有部门城市的人
SELECT e.job_id,d.location_id
FROM employees e 
JOIN departments d
ON e.`department_id`= d.`department_id`
WHERE d.`department_id`=90;

#3.选择所有有奖金的员工的 e.last_name, d.department_name, d.location_id, l.city
#外连接:所有:有的员工没有部门,没有部门所在城市,即代应该使用外连接
SELECT e.last_name,e.`commission_pct`,d.`department_name`,d.location_id,l.city
FROM employees e 
LEFT JOIN departments d
ON e.`department_id`= d.`department_id`
LEFT JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE e.`commission_pct` IS NOT NULL;

#4.选择 l.city在Toronto工作的员工的e.last_name , e.job_id, d.department_id , d.department_name
#无外连接:即忽略没有部门城市的人
SELECT e.last_name , e.job_id, d.department_id , d.department_name
FROM employees e 
JOIN departments d
ON e.`department_id`= d.`department_id`
JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE l.city = 'Toronto';
 

#5.查询员工所在的部门名称、部门地址、姓名、工作、工资,其中员工所在部门的部门名称为'Executive
#无外连接:即忽略没有部门城市的人
SELECT  d.department_name , l.street_address , e.last_name , e.job_id , e.salary
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
JOIN locations l
ON d.`location_id` = l.location_id
WHERE d.`department_name`='Executive';


#7.查询哪些部门没有员工
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.`department_id`
WHERE e.`department_id` IS NULL;


#8.查询哪城市没有部门
SELECT d.`department_id`, l.city
FROM departments d
RIGHT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id` IS NULL;


#9.查询部门名为SalesIT的员工信息
SELECT  e.employee_id, e.last_name , e.`department_id`
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.`department_name` IN ('Sales','IT');

3.1.4 函数

1.单行函数

▶ \blacktriangleright 数值函数

  1. 分类:
    绝对值、天花板、地板、随机数rand四舍五入round、截断truncate、三角函数、指数POW()、对数、进制转换
  2. 单行函数可以嵌套

▶ \blacktriangleright 字符串函数
在这里插入图片描述


  • 字符长度:CAHR_LENGTH()
  • 连接:concat()
  • 替换:INSERT()、REPLACE()
  • 大小写:lower()、upper()
  • 对齐:LPAD()、RPAD()
    在这里插入图片描述

▶ \blacktriangleright 流程控制函数

  1. 流程处理函数可以根据不同的条件,执行不同的处理流程,可以在SQL语句中实现不同的条件选择。
    MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()

2.多行函数
聚集函数:

▶ \blacktriangleright 聚集函数:作用于一组数据,并对一组数据返回一个值,查询指定字段时自动过滤空值

  1. AVG()、SUM()
    只适用于数值类型的字段(或变量)
  2. MAX()、MIN()
    适用于数值类型、字符串类型、日期时间类型的字段(或变量)
  3. COUNT():不计算空值,需要特殊处理
    ∙ \bullet 作用:计算指定字段在查询结果中出现的个数
    注意:计算指定字段出现的个数时,是不计算NULL值的。
    ∙ \bullet 公 式 : A V G ( x ) = S U M ( x ) / C O U N T ( x ) 公式:AVG(x)=SUM(x) / COUNT(x) AVG(x)=SUM(x)/COUNT(x)
    在这里插入图片描述
#如果计算表中有多少条记录,如何实现?
#方式1:COUNT(*)
#方式2:COUNT(1)
#方式3:COUNT(具体字段):不一定对!

#需求:查询公司中平均奖金率
#错误的!
SELECT AVG(commission_pct)
FROM employees; 
#正确的:
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)), #count需要把所有的人都算上
# AVG(IFNULL(commission_pct,0))
FROM employees;

#某员工所在部门平均工资
SELECT AVG(salary)							  #相关子查询:每次查询都子查询都要重新计算一次
FROM employees e2                             #子查询中使用主查询中的列
WHERE e2.`department_id` = e1.`department_id` #过滤得到相同部门行,聚合函数作用于过滤得到整体的结果,即求得本部门平均工资



GROUP BY

▶ \blacktriangleright 分组:GROUP BY子句

  1. 作用
    ∙ \bullet 将表中的数据分成若干组
    即:细化 聚集函数的作用对象
    如果未对查询结果分组,聚集函数将作用于整个查询结果
    对查询结果分组后,聚集函数将分别作用于每个组
  2. 结论:
    ∙ \bullet SELECT中出现的非聚合函数的字段必须声明在GROUP BY中。 反之,GROUP BY中声明的字段可以不出现在SELECT中。
    即:在SELECT列表中所有未包含在组函数中的列 都应该包含在 GROUP BY子句中在这里插入图片描述
  • GROUP BY 声明位置:
    在FROM后面、WHERE后面ORDER BY前面、LIMIT前面
    在这里插入图片描述
  • count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行
#错误的!
#job_id:SELECT中出现的非聚合函数的字段必须声明在GROUP BY中
SELECT department_id, job_id,AVG(salary)
FROM employees
GROUP BY department_id;

HAVING

▶ \blacktriangleright 对分组过滤数据:HAVING

  1. 作用
    在分组查询的结果中再次过滤
  • 不能在 WHERE 子句中使用聚合函数
    在这里插入图片描述
    在这里插入图片描述
  1. 要求:
    ∙ \bullet 当过滤条件中有聚合函数时,则此过滤条件必须声明在HAVING中。
    ∙ \bullet 当过滤条件中没有聚合函数时,则此过滤条件声明在WHERE中或HAVING中都可以。但是,建议大家声明在WHERE中。
    ∙ \bullet HAVING必须声在 GROUP BY后面。
#sq192语法:
#方式1:
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#方式2:
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件 AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,


▶ \blacktriangleright 聚合函数练习

#7.查询所有部门的(名字,location_id,员工数量和平均工资),并按平均工资降序
SELECT d.department_name , d.location_id , COUNT(salary) , AVG(salary)
#外连接:存在有员工没有部门,存在有部门没有员工
FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id`
GROUP BY d.department_name , d.location_id
ORDER BY AVG(salary) DESC

#8.查询每个工种、每个部门的(部门名、工种名和最低工资)
SELECT d.department_name , e.job_id , MIN(salary)
FROM employees e RIGHT JOIN departments d ON e.`department_id`=d.`department_id`
GROUP BY d.department_name , e.job_id



3.1.4 子查询

▶ \blacktriangleright 子查询

  1. 子查询的分类
    ∙ \bullet 角度1:从内查询返回的结果的条目数
    单行子查询vs多行子查询
    ∙ \bullet 角度2:内查询是否被执行多次
    相关子查询 vs不相关子查询
  • 自连接的处理速度要比子查询快得多

1.单行子查询 与 多行子查询

▶ \blacktriangleright 子查询书写方式

  1. 由内而外
  2. 由外而内
    ∙ \bullet 例子:定语划分
    题目:查询((与141号或174号)员工的manager_id和department_id相同的)其他员工的employee_id,manager_id,department_id
一、由内而外:
一、由内而外:
题目:显示员工的employee_id,last_name和location。
其中,若员工department_id与location_id为1800 的department_id相同,则location为’Canada’,其余则为’USA’。
#1.查询location_id为1800 的department_id为20
SELECT department_id FROM departments WHERE location_id = 1800

#2.若员工department_id与location_id为1800 的department_id相同,则location为’Canada’,其余则为’USA’。
SELECT CASE department_id WHEN 20 THEN 'Canada'
				   ELSE 'USA' END
FROM departments;

#3.显示员工的employee_id,last_name和location
SELECT employee_id,LAST NAME,() "location"
FROM employees

#4.组合
题目:显示员工的employee_id,last_name和location。
SELECT employee_id,last_name,(CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada'
				   								                       ELSE 'USA' END) "location"
FROM employees


▶ \blacktriangleright 多行子查询

  1. 多行子查询
    ∙ \bullet 也称为集合比较子查询
    ∙ \bullet 内查询返回多行,使用多行比较操作符
  2. 多行比较操作符
    在这里插入图片描述
#多行查询
#题目:查询(平均工资最低)的部门id
解析:
1.查询最低平均工资
2.分组过滤:求各部门平均工资, 查询等于最低平均工资的 部门id
即:先求最低平均工资,在找哪一个部门才是最低平均工资

#1.查询最低的平均工资
#1.1 方法一:错误,MYSQL中聚合函数不可以嵌套,Oracle可以
SELECT MIN(AVG(salary))
FROM employees
GROUP BY department_id
#1.2 方法二:将AVG聚合函数结果视为一张表dep_avg_sal(必需取别名),再使用MIN聚合函数
由内而外
#最低工资
SELECT MIN(avg_sal)
FROM (SELECT AVG(salary) avg_sal
	FROM employees
	GROUP BY department_id) dep_avg
	
-----------------------------------------------------------------
#2.1分组过滤-单行查询:求各部门平均工资, 单行查询等于最低平均工资的 部门id
SELECT *
FROM departments
WHERE department_id =(
	SELECT department_id
	FROM employees
	GROUP BY department_id    #分组过滤:查找部门平均工资等于最低平均工资的部门
	HAVING AVG(salary) = ( 
		SELECT MIN(avg_sal)   #部门最低平均工资
		FROM (
			SELECT AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id) dep_avg_sal #部门平均工资
	     )
)

-----------------------------------------------------------------
#2.2分组过滤-多行查询
#思路:查询各部门平均工资结果,多行查询使用<ALL从结果中查找最小值
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
			SELECT AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id
			);

2.相关子查询 与 不相关子查询

▶ \blacktriangleright 相关子查询:通过主查询A表,查找与A表相关的B表的信息,返回查找的信息

  1. 定义
    ∙ \bullet 如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重新计算一次。
    ∙ \bullet 相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询。
    ∙ \bullet 子查询中使用主查询中的列

#相关子查询
#题目:查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
-----------------------------------------------------------------
#1.相关子查询方式
#思路:求员工部门的平均工资后与直接与员工工资比较

#1.1 某员工的 部门的平均工资
SELECT AVG(salary)
FROM employees e2
WHERE e2.`department_id` = e1.`department_id`

#1.2 员工工资与本部门平均工资比较
select last_name,salary,department_id
from employees e1
where e1.`salary` > (
			SELECT AVG(salary)							  #相关子查询:每次查询都子查询都要重新计算一次
			FROM employees e2                             #子查询中使用主查询中的列
			WHERE e2.`department_id` = e1.`department_id` #过滤得到相同部门行,聚合函数作用于过滤得到整体的结果,即求得本部门平均工资
			);

-----------------------------------------------------------------
#2.内连接:在FROM中查询
#思路:连接,将各部门工资查询结果作为一个表,连接employees表后进行工资比较

#2.1各部门工资
SELECT e.`department_id`,AVG(salary)
FROM employees e
group by e.`department_id`

#2.2连接employees表后进行工资比较
SELECT e1.last_name,e1.salary,e1.department_id
FROM employees e1 join (SELECT e.department_id,AVG(salary) dep_sal FROM employees e GROUP BY e.`department_id`) e2
on e1.`department_id`= e2.department_id
where e1.`salary`>e2.dep_sal;


▶ \blacktriangleright 结论: ★ \bigstar ★ \bigstar
在SELECT中,除了GROUP BY和LIMIT之外,其他位置都可以声明子查询!

总结:
# 1.SELECT中子查询
#题目:显示员工的employee_id,last_name和location。其中,若员工department_id与location_id为1800 的department_id相同,则location为’Canada’,其余则为’USA’。
SELECT employee_id,last name,(CASE department id WHEN (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada'
				   								 ELSE 'USA' END) "location"
FROM employees

#CASE中的子查询
题目:显示员工的employee_id,last_name和location。
SELECT employee_id,last_name,(CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada'
				   								                       ELSE 'USA' END) "location"
FROM employees

#2.FROM中子查询
#题目:查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#2.FROM中查询
#思路:连接,将各部门工资查询结果作为一个表,连接employees表后进行工资比较
#2.1各部门工资
SELECT e.`department_id`,AVG(salary)
FROM employees e
group by e.`department_id`
#2.2连接employees表后进行工资比较
SELECT e1.last_name,e1.salary,e1.department_id
FROM employees e1 join (SELECT e.department_id,AVG(salary) dep_sal FROM employees e GROUP BY e.`department_id`) e2
on e1.`department_id`= e2.department_id
where e1.`salary`>e2.dep_sal;

#3.HAVING中子查询
题目:查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
		(SELECT MIN(salary)
		FROM employees
		WHERE department_id = 50);
		
#4.ORDER BY中子查询
#题目:查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
	SELECT department_name
	FROM departments d
	WHERE e.department_id = d.department_id);

★ \bigstar ★ \bigstar 总结:

子查询:由外而内、由内而外
什么时候由外而内?:查询较简单时,
什么时候由内而外?:查询较复杂时,更清晰

每层:连接过滤,分组过滤,选择去重与排序

#查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id

SELECT last_name,salary,department_id
FROM employees e1
WHERE e1.`salary` > (
			SELECT AVG(salary)			      #相关子查询:每次查询都子查询都要重新计算一次
			FROM employees e2                             #子查询中使用主查询中的列
			WHERE e2.`department_id` = e1.`department_id` #过滤得到相同部门行,聚合函数作用于过滤得到整体的结果,即求得本部门平均工资
			);
			
SELECT last_name,salary,department_id
FROM employees e1
WHERE e1.`salary` > (
	SELECT avg_sal
	FROM (
		SELECT department_id,AVG(salary) avg_sal 
		FROM employees 
		GROUP BY department_id) dep_avg_sal
	WHERE e1.department_id = dep_avg_sal.department_id
);

#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id=(
			SELECT department_id
			FROM employees
			WHERE last_name='Zlotkey'
) ;


#2.查询工资 比公司平均工资高的员工的员工号,姓名和工资。
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
		SELECT AVG(salary)
		FROM employees
		);

#3.选择工资 大于 (所有JOB_ID = 'SA_MAN'的员工的工资的)员工的last_name, job_id, salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary>(
		SELECT MAX(salary)
		FROM employees
		WHERE job_id='SA_MAN'
		);

#4.(内:查询姓名中包含字母u的员工) 外:查询在相同部门的员工的 员工号和姓名
#分析:相关子查询,每次查询时子查询的操作不同
SELECT job_id,last_name
FROM employees
WHERE department_id = (
			SELECT department_id 
			FROM employees
			WHERE last_name LIKE '%u%'
			);



#5.查询 部门的location_id为1700的部门(部门id) (这些部门)工作的员工的员工号
SELECT employee_id,location_id
FROM employees e JOIN departments d
ON e.`department_id`= d.`department_id`
WHERE location_id = 1700;

SELECT employee_id
FROM employees
WHERE department_id IN (
			SELECT department_id
			FROM departments
			WHERE location_id = 1700
			)

#6.查询 管理者是King的 员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id =ANY (
			SELECT employee_id 
			FROM employees
			WHERE last_name='King'
			);
			
SELECT last_name, salary
FROM employees
WHERE manager_id IN (
	SELECT employee_id
	FROM employees
	WHERE last_name = 'King'
)
			

#7.查询工资最低的员工信息: last_name, salary
SELECT last_name, salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);


#8.查询平均工资最低的部门信息
#分组过滤:平均工资最低的部门id
SELECT department_id
FROM employees
GROUP BY department_id #3.分组过滤:查找部门平均工资等于最低平均工资的部门id
HAVING AVG(salary) = ( 
	SELECT MIN(avg_sal) #2.各部门中最低平均工资
	FROM (
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id) dep_avg_sal #1.各部门平均工资
)

#查询平均工资最低的部门信息
SELECT *
FROM departments
#1.过滤条件:部门id = 平均工资最低的部门id
WHERE department_id =( 
	SELECT department_id
	FROM employees
	GROUP BY department_id #2.1分组过滤:查找部门平均工资等于最低平均工资的部门
	HAVING AVG(salary) = ( 
		SELECT MIN(avg_sal) #3.1选择:各部门中最低平均工资
		FROM (
			SELECT department_id,AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id) dep_avg_sal #4.1分组过滤:部门平均工资
	     )
)

连接过滤,分组过滤,选择去重与排序
#9.查询平均工资最低的部门信息 和 该部门的平均工资(相关子查询)
SELECT *        #1.3选择连接表的所有列
FROM departments d 
RIGHT JOIN (    #1.1右外连接:得到所有部门的信息和平均工资
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id) dep_avg_sal 
ON d.`department_id`= dep_avg_sal.department_id
WHERE d.department_id =(  #1.2过滤条件:部门id = 平均工资最低的部门id
	SELECT department_id
	FROM employees
	GROUP BY department_id #2.1分组过滤:查找部门平均工资等于最低平均工资的部门
	HAVING AVG(salary) = ( 
		SELECT MIN(avg_sal) #各部门中最低平均工资
		FROM (
			SELECT department_id,AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id) dep_avg_sal #部门平均工资
	     )
)


#10.查询平均工资最高的 job 信息
SELECT *    #1.2 选择:在jobs表中通过job_id选择所有列    
FROM jobs j
WHERE j.job_id = ( #1.1 过滤:job_id = 平均工资最高的job_id
	SELECT job_id
	FROM employees
	GROUP BY job_id #2.1分组过滤:按job_id分组,查找job_id=平均工资最高的job_id
	HAVING AVG(salary) = ( 
		SELECT MAX(avg_sal) #3.1选择:各工作中最高平均工资
		FROM (
			SELECT job_id,AVG(salary) avg_sal
			FROM employees
			GROUP BY job_id) job_avg_sal #4.1分组过滤:各部门平均工资
	)
)


#11.查询平均工资 高于 公司平均工资的 部门有哪些?
SELECT *
FROM departments
WHERE department_id IN( #1.1过滤:部门id = (平均工资高于公司平均工资的)部门id
	SELECT department_id
	FROM employees
	GROUP BY department_id #2.1分组过滤:部门平均工资>公司平均工资的部门id
	HAVING AVG(salary) > (
		SELECT AVG(salary)
		FROM employees
	     )
)

#查询公司员工工资的最大值,最小值,平均值,总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;


#12.查询出公司中所有 manager 的详细信息
SELECT employee_id
FROM employees 
WHERE employee_id IN (
	SELECT manager_id							  
	FROM employees                           
)


#13.各个部门中最高工资中 最低的那个部门的 最低工资是多少?

#1.2 选择:在departments表中通过department_id选择所有列, 最高工资中的最低工资
SELECT d.*,(SELECT MIN(max_sal)FROM (SELECT department_id,MAX(salary) max_sal FROM employees GROUP BY department_id) dep_max_sal) min_sal 
FROM departments d
#1.1 过滤:department_id = 最高工资中的最低工资的department_id
WHERE d.department_id = ( 
	SELECT department_id
	FROM employees
	GROUP BY department_id #2.1分组过滤:按department_id分组,查找department_id=工资最低的department_id
	HAVING AVG(salary) = ( 
		SELECT MIN(max_sal) #3.1选择:各部门最高工资中的最低工资
		FROM (
			SELECT department_id,MAX(salary) max_sal
			FROM employees
			GROUP BY department_id) dep_max_sal #4.1分组过滤:各部门最高工资
	)
)

SELECT MIN(max_sal)FROM (SELECT department_id,MAX(salary) max_sal FROM employees GROUP BY department_id) dep_max_sal #4.1各部门最高工资

#14.查询 平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
#查询平均工资最低的部门信息
SELECT *
FROM departments
WHERE department_id =( #1.1过滤条件:部门id = 平均工资最高的部门id
	SELECT department_id
	FROM employees
	GROUP BY department_id #2.1分组过滤:查找部门平均工资等于最高平均工资的部门
	HAVING AVG(salary) = ( 
		SELECT MAX(avg_sal) #3.1选择:各部门中最高平均工资
		FROM (
			SELECT department_id,AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id) dep_avg_sal #4.1分组过滤:部门平均工资
	     )
)

#公司中所有 manager 的详细信息
SELECT employee_id
FROM employees 
WHERE employee_id IN (
	SELECT manager_id							  
	FROM employees                           
)

SELECT *
FROM employees 
#1.1过滤:是平均工资最高的部门 且 是管理者
WHERE employee_id IN (SELECT manager_id FROM employees)
AND department_id =( 
	SELECT department_id
	FROM employees
	GROUP BY department_id #2.1分组过滤:查找部门平均工资等于最高平均工资的部门
	HAVING AVG(salary) = ( 
		SELECT MAX(avg_sal) #3.1选择:各部门中最高平均工资
		FROM (
			SELECT department_id,AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id) dep_avg_sal #4.1分组过滤:部门平均工资
	     )
)

#15. 查询 部门的部门号,其中不包括job_id是"ST_CLERK"的部门号
SELECT DISTINCT department_id 
FROM employees 
WHERE department_id NOT IN (
	SELECT DISTINCT department_id 
	FROM employees 
	WHERE job_id='ST_CLERK')

#16. 选择 所有没有管理者的 员工的last_name
SELECT last_name
FROM employees
WHERE manager_id IS NULL

#17.查询员工号、姓名、雇用时间、工资,其中员工的管理者为 'De Haan'
SELECT employee_id,last_name,salary
FROM employees
WHERE manager_id = (
	SELECT employee_id
	FROM employees 
	WHERE last_name = 'De Haan');


#18.查询 各部门中工资 比 本部门平均工资高的 员工的员工号, 姓名和工资(相关子查询)
SELECT employee_id,last_name,salary
FROM employees e1
WHERE e1.salary > ( 
	SELECT AVG(salary)
	FROM employees e2
	WHERE e1.department_id = e2.`department_id` #部门平均工资
	)
)


#19.查询每个部门下的部门人数大于 5 的部门名称(相关子查询)
SELECT department_name
FROM departments d
WHERE d.department_id IN (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING COUNT(employee_id)>5
)

#20.查询 每个国家下的 部门个数 大于2的  国家编号(相关子查询)
#表A: locations(国家编号country_id)
#表B: departments
#表AB的关联条件A.a=B.a:l.location_id = d.location_id,每一次主查询都执行一次子查询

SELECT l.country_id,COUNT(department_id)
FROM departments d 
JOIN locations l
ON d.location_id = l.location_id
GROUP BY country_id
HAVING COUNT(department_id)>5

SELECT country_id 
FROM locations l 
WHERE 2 < ( #1.1过滤:部门个数>2
	SELECT COUNT(*) #2.2选择列或聚合函数:聚合函数计算,每个位置有几个部门
	FROM departments d
	WHERE l.`location_id` = d.`location_id` 
	#2.1相关子查询过滤:每次查询使用一个关联条件过滤B表,A.a=B.a,即:位置id=部门位置id,
	#过滤行:使用关联过滤条件 过滤 B表的各行,得到对应于该条件的结果
);


	


3.2 DDL、DCL

在这里插入图片描述

3.1.1 DDL:创建、更新、删除(数据库,表)

▶ \blacktriangleright SQL(Structured Query Language)概述

  1. 定义:
    结构化查询语言,是关系数据库的标准语言
  2. SQL语言 分类
    在这里插入图片描述

▶ \blacktriangleright 数据库:创建、更新、删除、使用

#1.创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER CHARACTER SET 'utf-8';

#2.修改数据库
ALTER DATABASE mytest2 CHARACTER SET 'utf8'; #更改数据库字符集

#3.删除
DROP DATABASE IF EXISTS 数据库名;

#4.使用查看
#4.1.使用数据库
USE atguigudb;
SHOW TABLES; #查看当前数据库中保存的数据表
SHOW TABLES FROM 数据库名; #查看指定库下所有的表

#4.2查看当前所有的数据库
SHOW DATABASES;

#4.3查看当前正在使用的数据库
SELECT DATABASE();

#4.4查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;

▶ \blacktriangleright 表:1创建、2更新、3删除、4清空

▶ \blacktriangleright 基本表的定义

  1. 定义语句
    在这里插入图片描述
1.创建表:直接创建和查询创建
USE atguigudb;
SHOW CREATE DATABASE atguigudb; #默认使用的是utf8
SHOW TABLES;

#方式1:直接创建,"白手起家"的方式
CREATE TABLE IF NOT EXISTS myemp1(   #需要用户具备创建表的权限。
id INT,
emp_name VARCHAR(15), #使用VARCHAR来定义字符串,必须在使用VARCHAR时指明其长度。
hire_date DATE
);
#查看表结构
DESC myemp1;
#查看创建表的语句结构
SHOW CREATE TABLE myemp1; #如果创建表时没有指明使用的字符集,则默认使用表所在的数据库的字符集。
#查看表数据
SELECT * FROM myemp1;

#方式2:查询创建,基于现有的表,同时导入数据
CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;

DESC myemp2;
DESC employees;

SELECT *
FROM myemp2;

#说明1:查询语句中字段的别名,可以作为新创建的表的字段的名称。
#说明2:此时的查询语句可以结构比较丰富,使用前面章节讲过的各种SELECT
CREATE TABLE myemp3
AS
SELECT e.employee_id emp_id,e.last_name lname,d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

SELECT *
FROM myemp3;

DESC myemp3;


--------------------------------------------------------------------
2.修改表内容
#1. 修改表字段:ALTER TABLEADD\MODIFY\CHANGE\DROP 字段
DESC myemp1;

# 1.1 添加一个字段
ALTER TABLE myemp1
ADD salary DOUBLE(10,2); #默认添加到表中的最后一个字段的位置

ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;

ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name;

# 1.2 修改一个字段:数据类型、长度、默认值(略)
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) ;

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';

# 1.3 重命名一个字段
ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);

ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);

# 1.4 删除一个字段
ALTER TABLE myemp1
DROP COLUMN my_email;

#2. 修改名表
ALTER TABLE myemp2 RENAME TO myemp12;
DESC myemp12;



--------------------------------------------------------------------
3.删除表
#不光将表结构删除掉,同时表中的数据也删除掉,释放表空间
DROP TABLE IF EXISTS

3.1.2 DCL-COMMIT 和 ROLLBACK:清空和回滚

4. 清空表
#清空表,表示清空表中的所有数据,但是表结构保留。
TRUNCATE TABLE;
DELETE FROM;

# 4.1 DCLCOMMITROLLBACK
# COMMIT:提交数据。一旦执行COMMIT,则数据就被永久的保存在了数据库中,意味着数据不可以回滚。
# ROLLBACK:回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次COMMIT之后。

# 4.2 对比 TRUNCATE TABLEDELETE FROM 
# 相同点:都可以实现对表中所有数据的删除,同时保留表结构。
# 不同点:
#	TRUNCATE TABLE:一旦执行此操作,表数据全部清除。同时,数据是不可以回滚的。
#	DELETE FROM:一旦执行此操作,表数据可以全部清除(不带WHERE)。同时,数据是可以实现回滚的。


4.3 DDLDML 的说明
  ① DDL的操作一旦执行,就不可回滚。指令SET autocommit = FALSEDDL操作失效。(因为在执行完DDL
    操作之后,一定会执行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE影响的。)DML的操作默认情况,一旦执行,也是不可回滚的。但是,如果在执行DML之前,执行了 
    SET autocommit = FALSE,则执行的DML操作就可以实现回滚。


# 演示:DELETE FROM 
#1)
COMMIT;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
DELETE FROM myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;

# 演示:TRUNCATE TABLE
#1)
COMMIT;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
TRUNCATE TABLE myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;

▶ \blacktriangleright 使用规范

  1. SQL大小写规范(建议遵守):
    ∙ \bullet 数据库名、表名、表别名、字段名、字段别名等都小写
    ∙ \bullet SQL关键字、函数名、绑定变等都大写
  2. 注释:
    ∙ \bullet 单行注释:# 注释文字(MySQL特有的方式)
    ∙ \bullet 单行注释:-- 注释文字(–后面必须包含一个空格。)
    ∙ \bullet 多行注释:/* 注释文字 */

▶ \blacktriangleright 数据导入

  1. 命令行客户端导入
    在命令行客户端登录mysql,使用source指令导入
    mysql> source d:\mysqldb.sql
  2. 使用软件导入



3.3 DML(表的增删改)

DML之增删改

#0. 储备工作
USE atguigudb;

CREATE TABLE IF NOT EXISTS emp1(
id INT,
`name` VARCHAR(15),
hire_date DATE,
salary DOUBLE(10,2)
);

DESC emp1;
SELECT * FROM emp1;

--------------------------------------------------------
#一、添加数据
#1.手动添加:是否指明添加字段、是否多条插入
#1.1没有指明添加的字段
#正确的
INSERT INTO emp1
VALUES (1,'Tom','2000-12-21',3400); #注意:一定要按照声明的字段的先后顺序添加

#错误的
INSERT INTO emp1
VALUES (2,3400,'2000-12-21','Jerry');

#1.2指明要添加的字段 (推荐)
INSERT INTO emp1(id,hire_date,salary,`name`)
VALUES(2,'1999-09-09',4000,'Jerry');
# 说明:没有进行赋值的hire_date 的值为 null
INSERT INTO emp1(id,salary,`name`)
VALUES(3,4500,'shk');

#同时插入多条记录 (推荐)
INSERT INTO emp1(id,NAME,salary)
VALUES
(4,'Jim',5000),
(5,'张俊杰',5500);

#2.查询添加,指明添加字段
INSERT INTO emp1(id,NAME,salary,hire_date)
#查询语句
SELECT employee_id,last_name,salary,hire_date  # 查询的字段一定要与添加到的表的字段一一对应
FROM employees
WHERE department_id IN (70,60);

SELECT * FROM emp1;
DESC emp1;
DESC employees;

#注意:emp1表中要添加数据的字段的长度不能低于employees表中查询的字段的长度。
#如果emp1表中要添加数据的字段的长度低于employees表中查询的字段的长度的话,就有添加不成功的风险。

--------------------------------------------------------
#2. 更新数据 (有约束):把set的位置类比select的位置,先过滤再更新
# UPDATE .... SET .... WHERE ...
# 可以实现批量修改数据的。

UPDATE emp1
SET hire_date = CURDATE()
WHERE id = 5;

SELECT * FROM emp1;

#同时修改一条数据的多个字段
UPDATE emp1
SET hire_date = CURDATE(),salary = 6000
WHERE id = 4;

#题目:将表中姓名中包含字符a的提薪20%
UPDATE emp1
SET salary = salary * 1.2
WHERE NAME LIKE '%a%';

#修改数据时,是可能存在不成功的情况的。(可能是由于约束的影响造成的)
UPDATE employees
SET department_id = 10000
WHERE employee_id = 102;

--------------------------------------------------------
#3. 删除数据(有约束): DELETE FROM .... WHERE....
DELETE FROM emp1
WHERE id = 1;

#在删除数据时,也有可能因为约束的影响,导致删除失败
DELETE FROM departments
WHERE department_id = 50;

#小结:DML操作默认情况下,执行完以后都会自动提交数据。
# 如果希望执行完以后不自动提交数据,则需要使用 SET autocommit = FALSE,配合rollback使用

--------------------------------------------------------
#4. MySQL8的新特性:计算列
#计算列:通过别的列计算得来的,自动更新
USE atguigudb;

CREATE TABLE test1(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL  #字段c即为计算列
);

INSERT INTO test1(a,b)
VALUES(10,20);

DESC test1
SELECT * FROM test1;

UPDATE test1
SET a = 100; 

▶ \blacktriangleright 数据定义

  1. SQL的数据定义功能:定义各种数据库的“对象”
    ∙ \bullet 模式
    ∙ \bullet 基本表
    ∙ \bullet 视图
    ∙ \bullet 索引
    在这里插入图片描述


3.2.2 基本表的定义、修改、删除

▶ \blacktriangleright 基本表的定义

  1. 定义语句
    在这里插入图片描述
  2. 完整性约束条件
    ∙ \bullet <列级完整性约束条件>:涉及相应属性列的完整性约束条件
    ∙ \bullet <表级完整性约束条件>:涉及一个或多个属性列的完整性约束条件
    ∙ \bullet 如果完整性约束条件涉及到该表的多个属性列,则必须定义在表级上。
    在这里插入图片描述

▶ \blacktriangleright 基本表的修改:增删改

# 增:
# 1.VALUES的方式
INSERT INTO 表名 VALUES( ) 

# 2.查询方式
INSERT INTO 表名(tar_column1 [, tar_column2,, tar_columnn])
SELECT(src_column1 [, src_column2,, src_columnn])
FROM 源表名
WHERE ...

# 改:
UPDATE 表名 SET ...WHERE ...

# 删:
DELETE FROM 表名 WHERE ...


3.5 视图

  1. 视图
    ∙ \bullet 视图是一种 虚拟表 ,本身是不具有数据的,占用很少的内存空间,视图是从一个或几个基本表(或视图)导出的表。
    ∙ \bullet 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。数据库中只存放视图的定义,而不存放视图对应的数据。
    ∙ \bullet 视图的优点:
    简化查询;控制数据的访问
  2. 定义视图
    ∙ \bullet 组成视图的属性列名或者全部省略或者全部指定
  3. 查询视图
    视图定义后,用户就可以像对基本表一样对视图进行查询了。
  4. 更新视图数据
    ∙ \bullet 用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作
    视图的作用
    ∙ \bullet 要使视图可更新,视图中的行和底层基本表中的行之间必须存在 一对一 的关系。
    ∙ \bullet 虽然可以更新视图数据,但总的来说,视图作为 虚拟表 ,主要用于 方便查询 ,不建议更新视图的数据。对视图数据的更改,都是通过对实际数据表里数据的操作来完成的。
  5. 修改视图
  6. 删除视图
# 1.创建视图
CREATE VIEW 视图名称
AS 查询语句

#2.修改视图
#方式1:使用CREATE OR REPLACE VIEW
CREATE OR REPLACE VIEW 视图名称
AS
查询语句

#方式2:ALTER VIEW
ALTER VIEW 视图名称
AS
查询语句

#3.删除视图
#删除视图只是删除视图的定义,并不会删除基表的数据。
DROP VIEW IF EXISTS 视图名称

约束

▶ \blacktriangleright 约束概述

  1. 数据完整性(Data Integrity)
    是指数据的精确性(Accuracy)和可靠性(Reliability)。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
  2. 为了保证数据的完整性,SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑:
    ∙ \bullet 实体完整性(Entity Integrity)
    ∙ \bullet 域完整性(Domain Integrity)
    ∙ \bullet 引用完整性(Referential Integrity)
    ∙ \bullet 用户自定义完整性(User-defined Integrity)

第6章 数据库设计规范

6.1 问题的提出

总之,开始设置数据库的时候,我们就需要重视数据表的设计。为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则。



6.2 范式

  • 在关系型数据库中,关于数据表设计的基本原则、规则就称为范式。 可以理解为,一张数据表的设计结构需要满足的某种设计标准的级别。要想设计一个结构合理的关系型数据库,必须满足一定的范式。
  • 目前关系型数据库有六种常见范式,按照范式级别,从低到高分别是:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯·科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。

函数依赖


▶ \blacktriangleright 码(键)

  1. 超键:
    能唯一标识元组的属性集叫做超键。
  2. 候选键:
    如果超键不包括多余的属性,那么这个超键就是候选键。
  3. 主键:
    用户可以从候选键中选择一个作为主键。
  4. 外键:
    如果数据表R1中的某属性集不是R1的主键,而是另一个数据表R2的主键,那么这个属性集就是数据表R1的外键。
  5. 主属性:
    包含在任一候选键中的属性称为主属性。
  6. 非主属性:
    与主属性相对,指的是不包含在任何一个候选键中的属性。

通常,我们也将候选键称之为“码”,把主键也称为“主码”。因为键可能是由多个属性组成的,针对单个属性,我们还可以用主属性和非主属性来进行区分。


▶ \blacktriangleright 范式

  1. 第一范式
    主要是确保数据表中每个字段的值必须具有原子性,也就是说数据表中每个字段的值为不可再次拆分的最小数据单元。
  2. 第二范式
    ∙ \bullet 在满足第一范式的基础上
    ∙ \bullet 唯一标识:
    满足数据表里的每一条数据记录,都是可唯一标识的。
    ∙ \bullet 完全依赖:
    而且所有非主键字段,都必须完全依赖主键,不能只依赖主键的一部分。 如果知道主键的所有属性的值,就可以检索到任
    何元组(行)的任何属性的任何值。(要求中的主键,其实可以拓展替换为候选键)。
  1. 3NF
  2. BCNF

6.2.7多值依185
6.2.8 4NF.188
6.2.9规范化小189
6.3数据依赖的公理系统190
*6.4模式的分解194
6.4.1模式分解的三个定义194
6.4.2分解的无损连接性和保持
函数依赖性·196
6.4.3模式分解的算法198
6.5 小.201

视图
3.7.1
定义视图
3.7.2
查询视图
3.7.3
更新视图
3.7.4
视图的作用

视图

约束pdf
视图pdf
存储过程与函数.pdf
变量、流程控制与游标.pdf
触发器.pdf
MySQL8其它新特性pdf




------------------------------------------------------------------------

第10章 事务

10.1 事务基础知识

▶ \blacktriangleright 事务的ACID原则

  1. 原子性
  2. 一致性 consistency
    ∙ \bullet 根据定义,一致性是指事务执行前后,数据从一个合法性状态变换到另外一个合法性状态。
    ∙ \bullet 这种状态是语义上的而不是语法上的,跟具体的业务有关。
    ∙ \bullet 合法的数据状态:
    满足预定的约束的状态就叫做合法的状态,通俗一点,这状态是由你自己来定义的(比如满足现实世界中的约束)。如果事务中的某个操作失败了,系统就会自动撤销当前正在执行的事务,返回到事务操作之前的状态。
  3. 隔离性 isolation
    事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
  4. 持久性
    ∙ \bullet 是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响。
    ∙ \bullet 持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志。
    ∙ \bullet 当我们通过事务对数据进行修改的时候,首先会将数据库的变化信息记录到重做日志中,然后再对数据库中对应的行进行修改。这样做的好处是,即使数据库系统崩溃,数据库重启后也能找到没有更新到数据库系统中的重做日志,重新执行,从而使事务具有持久性。

10.2 并发控制概述

数据不一致性:由于并发操作破坏了事务的隔离性

▶ \blacktriangleright 并发操作带来的数据不一致性

  1. 丢失修改(Lost Update)
    两个事务T和T2读入同一数据并修改,T2的提交结果破坏了T1提交的结果,导致T的修改被丢失。
  2. 不可重复读(Non-repeatable Read)
    ∙ \bullet 事务1读取数据,事务2修改该数据
    ∙ \bullet 事务1读取数据,事务2删除该数据
    ∙ \bullet 事务1读取数据,事务2插入数据
  3. 读“脏”数据(Dirty Read)
    ∙ \bullet 事务T1修改某一数据,并将其写回磁盘,事务T2读取同一数据后,T1由于某种原因被撤销,这时T1已修改过的数据恢复原值,T2读到的数据就与数据库中的数据不一致
    ∙ \bullet T2读到的数据就为“脏”数据,即不正确的数据

▶ \blacktriangleright 并发控制的主要技术

  1. 封锁(Locking)
  2. 时间戳(Timestamp)
  3. 乐观控制法
  4. 多版本并发控制(MVCC)

▶ \blacktriangleright 封锁(Locking)

  1. 封锁(Locking)
    ∙ \bullet 封锁就是事务T在对某个数据对象(例如表、记录等)操作之前,先向系统发出请求,对其加锁,加锁后事务T就对该数据对象有了一定的控制,在事务T释放它的锁之前,其它的事务不能更新此数据对象。
    ∙ \bullet 一个事务对某个数据对象加锁后究竟拥有什么样的控制由封锁的类型决定。
  2. 基本封锁类型
    排它锁(Exclusive Locks,简记为X锁)
    共享锁(Share Locks,,记为S锁)
  3. 排它锁(Exclusive Locks,简记为X锁)
    ∙ \bullet 若事务T对数据对象A加上X锁,则只允许T读取和修改A,其它** ∙ \bullet 任何事务都不能再对A加任何类型的锁**,直到T释放A上的锁
    保证其他事务在T释放A上的锁之前不能再读取和修改A
    ∙ \bullet 排它锁又称为写锁
  4. 共享锁(Share Locks,,记为S锁)
    ∙ \bullet 若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其它事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁
    ∙ \bullet 保证其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改
    ∙ \bullet 共享锁又称为读锁

▶ \blacktriangleright 封锁协议

  1. 在运用X锁和S锁对数据对象加锁时,需要约定一些规则,这些规则为封锁协议(Locking Protocol)。
    ∙ \bullet 何时申请X锁或S锁
    ∙ \bullet 持锁时间
    ∙ \bullet 何时释放

▶ \blacktriangleright 三级封锁协议

  1. 一级封锁协议
    ∙ \bullet 事务T在修改数据R之前必须先对其加X锁,直到事务结束才释放。
    正常结束(COMMIT)
    非正常结束(ROLLBACK)
    ∙ \bullet 一级封锁协议可防止丢失修改,并保证事务T是可恢复的。
    在这里插入图片描述
  2. 二级封锁协议
    ∙ \bullet 一级封锁协议加上事务T在读取数据R之前必须先对其加S锁,读完后即可释放S锁。
    ∙ \bullet 二级封锁协议可以防止丢失修改和读“脏”数据。
    在这里插入图片描述
  3. 三级封锁协议
    ∙ \bullet 一级封锁协议加上事务T在读取数据R之前必须先对其加S锁,直到事务结束才释放
    ∙ \bullet 三级封锁协议可防止丢失修改、读脏数据和不可重复读。
    在这里插入图片描述
  4. 总结
    在这里插入图片描述

10.2 可串行化调度与冲突可串行化调度

▶ \blacktriangleright 可串行化调度

  1. 可串行化(Serializable)调度
    ∙ \bullet 多个事务的并发执行是正确的,当且仅当其结果与按某一次序串行地执行这些事务时的结果相同
  2. 可串行性(Serializability)
    ∙ \bullet 是并发事务正确调度的准则
    ∙ \bullet 一个给定的并发调度,当且仅当它是可串行化的,才认为是正确调度

▶ \blacktriangleright 冲突可串行化调度

  1. 冲突可串行化调度
    ∙ \bullet 一个调度S在保证冲突操作的次序不变的情况下,通过交换两个事务不冲突操作的次序得到另一个调度S’,如果S’是串行的,称调度S’是冲突可串行化的调度
    ∙ \bullet 若一个调度是冲突可串行化,则一定是可串行化的调度
  2. 不同事务冲突操作:
    ∙ \bullet 不同的事务对同一数据的读写操作和写写操作
    涉及同一个数据库元素,并且至少有一个是写操作
  3. 不能交换(Swap)的动作:
    ∙ \bullet 同一事务的两个操作,是系统安排的顺序
    ∙ \bullet 不同事务的冲突操作


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



问题

1.在cmd 中输入了错误mysql命令后,如何退出?

▶ \blacktriangleright ****

  1. 第一种方法:
    mysql以“;” 结尾,打一个“;” 试下。
  2. 第二种办法是:
    ‘>SA’;按ENTER回车,就OK了.
    mysql> ,不过错了显示,但不用去理它.
    注意:SA是随便输入的,后要 '+;就可以退去了.
  3. 第三种方法:
    ctrl+C;
例如:
mysql> select * from tb_name

         '>

         '>

         '>

         '>

在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

folielxx

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值