LEVEL I - PART 3 数据库应用

PART 3 数据库应用 - 总占比(17%)


总体要求

理解数据库的基本概念、理解 DDL 及 DML 语言、能够根据业务需求及数据特征使用查询语言从数据库中获取准确、完整的数据信息、能够应用数据库函数进行数据处理及计算


1. 数据库相关概念（占比 1%）

数据库分类 - 【领会】

  • 数据库的功能定位：OLTP

  • 数据仓库的功能定位：OLAP

  • 联机事务处理 ( OLTP ) 系统旨在针对小工作单元进行快速写入，例如，用于快速创建单个事件。 联机分析处理 ( OLAP ) 数据仓库旨在促进跨大型数据集进行快速分析，例如，在去年创建的所有事件中快速确定服务级别协议 SLA 的遵从。


SQL 语言的功能 - 【领会】

  • sql语言具数据定义、数据操作、数据控制等三个常见功能。
    • 1、SQL数据定义功能
      • 能够定义数据库的三级模式结构，即外模式、全局模式和内模式结构。在SQL中，外模式有叫做视图(View)，全局模式简称模式(Schema)，内模式由系统根据数据库模式自动实现，一般无需用户过问。
    • 2、SQL数据操纵功能
      • 包括对基本表和视图的数据插入、删除和修改，特别是具有很强的数据查询功能。
    • 3、SQL的数据控制功能
      • 主要是对用户的访问权限加以控制，以保证系统的安全性。

加载数据库、数据库管理系统与 SQL 之间的关系 - 【熟识】


2. DDL 数据定义语言（占比 2%）

DDL适⽤于定义和管理数据库元素的语⾔，主要⽤于数据库中的数据表、索引、视图的创建、修改和删
除等。常⽤的关键字主要包括create、drop、alter等。

数据库基本结构 - 【领会】

  • 最小的储存单位：字段

数据类型的约束条件 - 【熟识】

  • 为了保证数据的完整性，可以通过四种方式来实现，约束、规则、默认值、触发器。

    完整性约束包括：主键约束（PRIMARY KEY）、外键约束（FOREIGN KEY）、 唯一约束（UNIQUE）、非空约束（NOT NULL）、检查约束（CHECK）。

    CREATE TABLE emp(

    depid CHAR(3) PRIMARY KEY,

    depname VARCHAR(20) NOT NULL,

    peoplecount INT UNIQUE DEFAULT 0);


创建、选用、删除数据库 / 创建、修改、删除表 - 【应用】

  • CREATE DATABASE 库名

    USE 库名

    DROP 库名

  • CREATE TABLE 表名（数据名 类型 约束条件,……）;

    ALTER TABLE 表名 RENAME 新表名; #修改表名

    ALTER TABLE 表名 MODIFY 字段名 数据类型; #修改数据类型

    ALTER TABLE 表名 CHANGE 字段名 新字段名 数据类型; #修改字段名和数据类型

    ALTER TABLE 表名 ADD 字段名 数据类型 约束条件; #添加新字段

    ALTER TABLE 表名 MODIFY 字段名 数据类型 FIRST; #将字段的排列顺序改为第一位

    ALTER TABLE 表名 MODIFY 字段名1 数据类型 AFTER 字段名2; #将字段1的排列顺序改到字段2之后

    ALTER TABLE 表名 DROP 字段名 #删除字段

    DROP TABLE 表名（只有DROP不用跟数据类型）


3. DDL 数据操作语言（占比 2%）


DML是对数据库中表记录的操作，⽤于添加、删除、更新和查询记录，并检查数据完整性。

常⽤的关键字主要包括：insert、delete、update等，是开发⼈员⽇常使⽤最频繁的操作。


添加数据的步骤 - 【领会】

  • insert into 表名(字段名,……)

​ values(值1,……),(值2,……)


添加、修改、删除数据的语法规则 - 【领会】

  • 添加数据：

    • INSERT INTO table_name
      VALUES (value1,value2,value3,...);

  • 修改数据：

    • update 表名

      set 列=新值，列=新值,……

      where 筛选条件;

  • 删除数据：

    delete from 表名 where 筛选条件

    （删除数据但保留表结构）


4. 单表查询（占比 3%）


运算符 - 【熟知】

img

img

SQL 语句的书写顺序和执行逻辑:

  • FROM-->WHERE-->GROUP BY-->HAVING-->SELECT-->ORDER BY
    • 正因为上边的SQL语句的执行顺序问题，因此不能在WHERE中引入别名

基本查询：去重查询、设置别名 - 【应用】

  • 别名是在WHERE⼦句后计算的，所以WHERE⼦句中不能⽤别名，FROM⼦句中可以⽤表别名。

  • Distinct 必须出现在字段开头

  • select id, distinct name from A; --会提示错误

  • select count(distinct name) from A; --正确
    条件查询：多条件查询、空值查询、模糊查询 分组查询：分组聚合、分组后筛选

  • HAVING 子句中的筛选字段必须是可以出现在分组结果中的字段。

  • SELECT deptid,AVG(sal) FROM employee GROUP BY deptid HAVING job=’clerk’; (x)

  • 查询结果排序、限制查询结果数量

  • WHERE 语句中不能出现聚合函数


5. 多表查询 （占比 3%）


对应关系：一对一、一对多、多对多连接方式：内连接、左连接、右连接连接条件：等值连接、不等值连接 - 【领会】

左连接 - left join：

  • left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。

    左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

    img

右连接 - right join：

  • 与左连接相反，右连接中，右表(b_table)的记录将会全部表示出来，左表(a_table)只会显示符合搜索条件的记录，记录不符合搜索条件的地方均为NULL。
  • img

内连接 - inner join：

  • 组合两个表中的记录，返回关联字段相符的记录，即：两个表的交集（阴影）部分。
  • img

连接查询的逻辑和联合查询规则 - 【熟识】

  • 连接的关键字段可以有一个或多个

  • 连接查询：内连接、左连接、右连接联合查询：去重、不去重

    Union: 合并，去重，相当distinct

    Union all ：合并，不去重


6. 子查询 （占比 3%）

子查询分类 - 【领会】

  • 所有的连接查询都能表示为子查询 - 正确

  • WHERE子句的查询条件是聚合函数时不能表示为连接查询


子查询位置、子查询操作符

  • 子查询的位置

    • 子查询必须括在圆括号中。
    • 子查询的 SELECT 子句中只能有一个列，除非主查询中有多个列，用于与子查询选中的列相比较。
    • 子查询不能使用 ORDER BY，不过主查询可以。在子查询中，GROUP BY 可以起到同 ORDER BY 相同的作用。
    • 返回多行数据的子查询只能同多值操作符一起使用，比如 IN 操作符。
    • SELECT 列表中不能包含任何对 BLOB、ARRAY、CLOB 或者 NCLOB 类型值的引用。
    • 子查询不能直接用在聚合函数中。
    • BETWEEN 操作符不能同子查询一起使用，但是 BETWEEN 操作符可以用在子查询中。
    • 子查询支持的SQL语句：
      • SELECT、INSERT、UPDATE 、HAVING和 DELETE 语句中，同 =、<、>、>=、<=、IN、BETWEEN 等运算符一起使用。

  • 子查询操作符：

    • Any：满足其中任意一个条件

    • All：满足所有条件

    • Exists：表示存在，返回布尔值

    • Limit：查询后只显示limit指定数字的行数结果


子查询语法规则 - 【应用】

  • 一定在小括号内
  • 子查询可以出现在SELECT、WHERE、HAVING、FROM、JOIN子句中。

7. 数据库函数（占比 3%）


计算字段 - 【领会】


函数功能及参数 - 【熟知】

  • 聚合函数不能出现在WHERE子句中

数学函数、字符串函数、日期时间函数、分组合并函数、逻辑函数 -【应用】

数学函数：round 四舍五入

      Ceil 向上取整

      Floor 向下取整数

      Mod 取余

      Truncate 截断

      DECIMAL 保留精度

字符串函数：concat 字符串连接

        Substr 截取

        Instr 返回第一次出现的索引

        Upper/lower 返回大小写

日期时间函数：now curdate curtime

Group_concat()返回由属于一组的列值连接组合而成的结果，常与group by一起使用，将分组后的字段值都显示出来。

如：使用group_concat查询不同s_id下所有的f_name信息

Select s_id,group_concat(f_name) from fruits group by s_id;

Cast()将一个值转换为指定的数据类型


posted @ 2022-07-12 17:35  DoHerasYang  阅读(278)  评论(0)  编辑  收藏  举报