关系数据库理论的基础知识

一、数据库、数据库技术、数据库系统、数据库管理系统的概念；数据库系统的体系结构
二、数据模型与数据视图
三、关系代数、关系演算及关系模型
四、结构化查询语言 SQL
五、数据库的完整性与安全性，事务管理、并发控制、故障恢复，数据库的备份与恢复
六、数据库的设计，数据依赖的概念及关系模式的规范化理论
七、数据库应用开发工具，常见数据库系统产品的名称、特点，Microsoft SQL Server 数据库的使用

一、数据库基础概念

  1. 数据库（Database）
    数据库是一个有组织的、可以长期存储和管理的相关数据集合。数据以一定的结构存储，支持数据的插入、查询、更新和删除等操作。
  2. 数据库技术（Database Technology）
    数据库技术是指用于组织、管理和操作数据库的方法和工具，包括数据库设计、存储管理、数据处理、查询优化等。
  3. 数据库系统（Database System, DBS）
    数据库系统由数据库、数据库管理系统（DBMS）、硬件、软件及用户组成。它是支持数据存储、管理和访问的完整系统。
  4. 数据库管理系统（Database Management System, DBMS）
    数据库管理系统是用于创建、管理和操作数据库的软件。例如：MySQL、Oracle、Microsoft SQL Server 等。

数据库系统的体系结构

数据库系统通常采用 三层体系结构：

  1. 外部模式（用户视图）
    定义用户如何查看和使用数据库。
  2. 概念模式（逻辑视图）
    定义数据库的逻辑结构，独立于具体的存储方式。
  3. 内部模式（物理视图）
    定义数据在存储设备上的物理组织。

二、数据模型与数据视图

  1. 数据模型
    数据模型是数据组织和描述的方式，主要包括：

    • 层次模型：用树形结构组织数据。
    • 网状模型：用图结构组织数据。
    • 关系模型：用二维表表示数据。
    • 面向对象模型：结合对象和数据库技术。
  2. 数据视图
    数据视图是用户或应用程序与数据库交互的接口，提供数据的部分或特定视角。

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

  1. 关系模型
    数据以表格形式表示，每张表称为一个关系。表由行（元组）和列（属性）组成。

  2. 关系代数
    提供操作关系的基本方法，包括：

    • 集合操作：并、交、差。
    • 专用操作：选择、投影、连接、分组等。

  3. 关系演算
    描述查询条件的逻辑公式，有两种形式：

    • 元组关系演算。
    • 域关系演算。

四、结构化查询语言 SQL

  1. SQL 的组成

    • 数据定义语言（DDL）：创建和修改数据库对象，如 CREATE、ALTER、DROP。
    • 数据操作语言（DML）：插入、更新、删除数据，如 INSERT、UPDATE、DELETE。
    • 数据查询语言（DQL）：查询数据，如 SELECT。
    • 数据控制语言（DCL）：控制权限，如 GRANT、REVOKE。

  2. SQL 的特点

    • 易于学习和使用。
    • 独立于具体的 DBMS。
    • 支持复杂查询和事务处理。

五、数据库的完整性与安全性

  1. 完整性
    确保数据库数据的准确性和一致性：

    • 实体完整性：主键不能为 NULL 或重复。
    • 参照完整性：外键引用必须合法。
    • 用户定义完整性：自定义约束，如 CHECK。

  2. 安全性
    保护数据不被未授权访问或篡改：

    • 认证：通过用户名和密码验证用户身份。
    • 授权：定义用户对数据库的操作权限。
    • 加密：保护敏感数据。

  3. 事务管理、并发控制与故障恢复

    • 事务管理：事务是操作的逻辑单元，具有 ACID 特性（原子性、一致性、隔离性、持久性）。
    • 并发控制：使用锁机制、防止数据不一致。
    • 故障恢复：通过日志和备份恢复数据。

  4. 数据库的备份与恢复

    • 备份：定期保存数据库的副本。
    • 恢复：使用备份和日志重建数据库。

六、数据库设计

  1. 数据库设计步骤

    • 需求分析。
    • 概念设计（如 E-R 图）。
    • 逻辑设计（如关系模式）。
    • 物理设计（如索引、存储分区）。

  2. 数据依赖与规范化理论

    • 数据依赖：描述属性之间的逻辑关系。

      • 函数依赖、部分依赖、传递依赖等。
    • 规范化：消除冗余，分为 1NF、2NF、3NF、BCNF 等。

七、数据库应用开发工具与常见数据库系统

  1. 常见数据库系统

    • MySQL：开源、轻量、高效。
    • Oracle：高性能，支持复杂业务。
    • Microsoft SQL Server：强大的集成和数据分析能力。
    • PostgreSQL：支持高级功能和扩展性。
    • MongoDB：NoSQL，适合非关系型数据。

  2. 数据库应用开发工具

    • 图形化管理工具：

      • MySQL Workbench。
      • phpMyAdmin。

    • 编程语言接口：

      • Python：pymysql、sqlalchemy。
      • Java：JDBC。

  3. Microsoft SQL Server 使用

    • 提供强大的 T-SQL 语言支持。
    • 集成开发工具：SQL Server Management Studio（SSMS）。

normal

https://www.runoob.com/sql/sql-having.html

  • select - 从数据库中提取数据
  • update - 更新数据库中的数据
  • delete - 从数据库中删除数据
  • insert into - 向数据库中插入新数据
  • create database - 创建新数据库
  • alter database - 修改数据库
  • create table - 创建新表
  • alter table - 变更（改变）数据库表
  • drop table - 删除表
  • create index - 创建索引（搜索键）
  • drop index - 删除索引
  • having, group by, select distinct, join ... on ..., order by ... asc|desc
    select column_name(s) from table_name
    where condition
    order by column_name [ASC|DESC];

    insert into table_name
    values (values1, values2, null, null, ...);

    update table_name
    set column1 = values1, column2 = values2, ...
    where condition;

    delete from table_name
    where condition;    


    create table table_name (
        column1 date_type constraint, # for example: book_name varchar primary key,
        column2 date_type constraint,
        column3 date_type constraint,
        column4 date_type constraint,
    );

    alter table table_name
    add column_name data_type;

    alter table table_name
    drop column column_name;

    drop table table_name;

    create index index_name
    on table_name (column_name);

    drop index index_name
    on table_name;

    select column_name(s)
    from table_name
    where condition;

    select column_name(s)
    from table_name
    order by column_name [ASC|DESC];

    select column_name(s), aggregate_function(column_name) # count(), sum(), avg()
    from table_name
    where condition
    group by column_name(s);

    select column_name(s), aggregate_function(column_name)
    from table_name
    group by column_name(s)
    having condition;        # having 用于分组后的结果查询

    select column_name(s)
    from table_name1
    join table_name2        # join 用于将两个或多个表的记录结合起来
    on table_name1.column_name = table_name2.column_name;
    
    select distinct column_name(s)
    from table_name;

super

  • limit
  • like, regexp, in, between
  • as
  • join ... on ...
  • union, union all
  • group by ... having ...
# select ... limit number;
    select * from websites
    where country='CN'
    limit 2;

# select ... where column_name like pattern; # % _
    select * from websites
    where url like 'https%';

    select * from websites
    where name like 'g__gle';


# select ... where column_name regexp pattern; 
    # [char] [a-g] [^char] ^start .* end$
    #^：表示匹配字符串的开始。
    #[gs]：表示第一个字符可以是 g 或 s。
    #[^c]: not c
    #.*：表示中间可以有任意数量的字符（包括零个）。
    select * from websites
    where name regexp '^[gs].*e$';

# select ... where column in (value1, value2, ...);
    select * from websites
    where name in ('google', 'facebook');

# select ... where  column between value1 and value2
    select * from access_log
    where date between '2016-01-01 00:00:00' and '2016-05-15 23:59:59';

# ... column_name(s) as alias_name ...
    select name as n, country as c from websites;

    select w.name, w.url, a.count, a.date from websites as w, access_log as a
    where w.id=a.site_id;

# SQL join 用于把来自两个或多个表的行结合起来。
    select w.id, w.name, a.count, a.date from websites as w
    join access_log as a
    on w.id = a.site_d;

# SQL UNION 操作符合并两个或多个 SELECT 语句的结果。
    select country from apps
    union
    select country from websites;

    select country, name from websites
    where country='CN'
    union all
    select country, app_name from apps
    where country='CN'
    order by country; 

# create table_name (name type(size) )
    create table Persons (
        PersonID int,
        LastName varchar(255),
        FirstName varchar(255),
        Address varchar(255),
        City varchar(255)
    ); 

    create table Orders (
        OrderID INT NOT NULL primary key,
        OrderNumber INT NOT NULL,
        CustomerID INT,
        foreign key (CustomerID) references Customers(CustomerID)
    );

# 通过使用 DROP 语句，可以轻松地删除索引、表和数据库。
     drop index     name;
     drop table     name;
     drop database  name;

# ALTER TABLE 语句用于在已有的表中添加、删除或修改列。
    alter table     table_name
    modify column   column_name datatype;

    alter table Persons
    add DateOfBirth date;

    alter table Persons
    modify column DateOfBirth year;

    alter table Persons
    drop column DateOfBirth

# auto_increment

    CREATE TABLE Persons (
        ID int NOT NULL AUTO_INCREMENT,
        LastName varchar(255) NOT NULL,
        FirstName varchar(255),
        Address varchar(255),
        City varchar(255),
        PRIMARY KEY (ID)
    )

# SQL views: 在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。
    # create view ... as ...
    CREATE VIEW view_name AS
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;

    -- 创建包含高工资员工信息的视图
     CREATE VIEW high_salary_employees AS
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE salary > 50000;


    # 更新视图的实质是通过更新视图所基于的表中的数据，然后视图会反映这些变化。
    -- 步骤 1: 更新 employees 表中的数据
    UPDATE employees
    SET salary = 60000
    WHERE employee_id = 1001;
    
    -- 步骤 2: 查询更新后的高工资员工视图
    SELECT *
    FROM high_salary_employees;

    -- 删除视图
    drop view high_salary_employees;

# SQL 通用数据类型
      # character(n) # 固定字长
      # varchar(n)   # 可变字长
      # boolean
      # int
      # float
      # date         # YYYY-MM-DD
      # time         # hh:mm:ss
      # timestamp    # YYYY-MM-DD hh:mm:ss

# SQL Aggregate 函数

    # AVG() - 返回平均值
    # COUNT() - 返回行数
    # MAX() - 返回最大值
    # MIN() - 返回最小值
    # SUM() - 返回总和

    select avg(count) as count_average from access_log;

    select site_id, count from access_log
    where count > (select avg(count) from access_log);

    select count(count) as nums from access_log
    where site_id=3;

    select count(distinct site_id) as nums from access_log; # distinct 不同的

    select max(count) as max_count from access_log; # max, min, sum

# GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

    # 统计 access_log 各个 site_id 的访问量：
    select site_id, sum(access_log.count) as nums from access_log
    group by site_id;


    # SQL GROUP BY 多表连接
    # 统计有记录的网站的记录数量
    select websites.name, count(access_log.site_id) as nums
    from access_log
    join websites on access_log.site_id = websites.id
    group by websites.name;

    # 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
    # HAVING 子句可以让我们筛选分组后的各组数据。
    select websites.name, count(access_log.site_id) as nums from access_log
    join websites on access_log.site_id = websites.id
    where date between '2016-05-14 00:00:00' and '2016-05-15 23:59:59'
    group by websites.name
    having websites.name regexp '^[FG].*e$';


    --  where 和having之后都是筛选条件，但是有区别的：
    -- 1.where在group by前， having在group by 之后
    -- 2.聚合函数（avg、sum、max、min、count），不能作为条件放在where之后，但可以放在having之后
    select ... from ...
    join ... on ...
    where ...   # 不能使用avg, sum, max, min, count 聚合函数
    group by ...
    having ...  # 可以使用avg, sum, max, min, count 聚合函数

Sample

图书(书号，书名,作者,出版社,出版日期)
读者(读者号,姓名,性别,年龄,电话)
借阅(读者号,书号,借出日期,还书日期)

(1)向读者关系中新增一个读者记录(‘1009','Tom',男',29),电话字段为空；

insert into 读者(读者号,姓名,性别,年龄) 
values(‘1009','Tom',男',29);

(2)将读者号为'1009’的读者电话修改为‘0123456789’

update 读者
set 电话='0123456789'
where 读者号='1009';

(3)查询 2019 年出版的图书书名和作者,并按出版日期降序排列；

select 书名,作者 from 图书
where 出版日期 between '2019-01-01' and '2019-12-31'
order by 出版日期 desc;

(4)查询借阅过红楼梦的读者姓名；

select 读者.姓名 from 读者
join 借阅 on 读者.读者号 = 借阅.读者号
join 图书 on 借阅.书号 = 图书.书号
where 图书.书名 = '红楼梦';

(5)统计被借阅次数超过 100 次的图书书号和借阅次数。

select 书号,count(*) as 借阅次数 from 借阅
group by 书号
having count(*)>100;

32.从数据库中删除已经存在的表 t,可用的命令是:
drop table t

37.数据库系统提供授权功能是为了保障数据库的
安全性

38.E-R 模型的基本成分是:
实体-联系

E-R 模型（实体-联系模型）的基本成分是：

1. 实体（Entity）：表示现实世界的对象（如学生、图书）。
2. 联系（Relationship）：表示实体之间的关系（如借阅关系）。
3. 属性（Attribute）：描述实体或联系的特征（如姓名、书号）。

40.在数据库设计中,将 E-R 图转换成关系数据模型的过程属于:
逻辑设计阶段

44.DB、DBMS 和 DBS 三者之间的关系是:
DBS 包括 DB 和 DBMS

45.数据库管理系统与操作系统、应用软件的层次关系从核心到外围依次是
OS、DBMS、应用软件

2.数据库关系模型的完整性的束包括：
A.数据安全性约束
B.实体完整性约束
C.参照完整性约束
D.联系完整性约束
E.用户自定义完整性约束

BCE

数据库完整性约束包括：

1. 实体完整性（Entity Integrity）：确保每个实体有唯一标识（如主键）。
2. 参照完整性（Referential Integrity）：确保外键引用的实体存在。
3. 用户自定义完整性（User-Defined Integrity）：用户定义的特定规则。
  • A. 数据安全性约束：是数据库安全性的一部分，而非完整性约束。
  • D. 联系完整性约束：并不存在这种约束类型。

标签: mysql

添加新评论