数据库期末速成

发布于 2023-10-13  395 次阅读

1、修改数据表结构的语句

ALTER TABLE <表名> MODIFY COLUMN <列名> <类型>
ALTER TABLE <表名> ADD COLUMN <列名> <类型>
ALTER TABLE <表名> RENAME TO <新表名>

2、修改数据表中数据的语句

UPDATE <表名> SET 字段1=值1, 字段2=值2, … WHERE …;

3、视图的定义

CREATE VIEW 视图名 AS SELECT 语句;
  • 视图是从基本表或其他视图中导出来的表。
1.视图能够简化用户的操作
2.视图使用户能以多种角度看待同一数据
3.视图对重构数据库提供了一定程度的逻辑独立性
4.视图能够对机密数据提供安全保护

4、建立索引的目的

1、通过唯一性索引（unique）可确保数据的唯一性；
2、加快数据的检索速度；
3、加快表之间的连接；
4、减少分组和排序时间；
5、使用优化隐藏器提高系统性能。
  • 下面的SQL语句将在orders表的customer_id列上创建一个名为idx_customer_id的普通索引：
CREATE INDEX idx_customer_id ON orders (customer_id);

5、用户授权语句

GRANT privileges ON database_name.table_name TO 'username'@'host';
  • 其中，privileges表示具体的权限，比如SELECT、INSERT、UPDATE、DELETE等，table_name表示表名，username表示用户名。

6、收回授权语句

REVOKE SELECT ON table_name FROM user_name;
  • 这个语句将从用户中收回对指定表的SELECT权限。 在这个语句中，table_name是要收回权限的表的名称，user_name是要收回权限的用户的名称。

7、MSQL日志文件的类型

错误日志 (error log)：记录MySQL服务器遇到的错误和异常事件。
查询日志 (query log)：记录每条执行的SQL语句。
慢查询日志 (slow query log)：记录执行时间超过一定阈值的查询语句。
Binlog (二进制日志)：记录所有修改数据库的事件，包括增、删、改等操作。
中继日志 (relay log)：主从复制时，从服务器上用于记录接收到的binlog事件。

8、MySQL角色的定义

  • 角色（Role）是一种用于管理用户权限的抽象概念，可以将一组权限赋予给角色，然后再将角色授予给用户。这样可以简化权限管理，提高安全性和可管理性。
  • 创建角色
CREATE ROLE role_name;
使用该语句可以创建一个新的角色，角色名为role_name。
  • 授予权限给角色
GRANT privileges ON database_name.table_name TO role_name;
这条语句将指定的权限授予给特定的角色，使得角色拥有这些权限。
  • 将角色授予用户
GRANT role_name TO username;
通过这条语句，可以将一个角色授予给特定的用户，从而让用户获得角色所包含的权限。
  • 2.3 查看角色权限
show grants for 'role_name'[@'host_name'];
  • 2.4 回收角色的权限
revoke privileges on table_name from 'role_name';

revoke insert,update,delete on school.* from 'school_write';
  • 删除角色
drop role 'role_name';

9、规范化主要的理论依据

  • 第一范式（1NF）：确保每个列都是不可分割的原子值，即确保每个字段都是原子性的，不可再分。
  • 第二范式（2NF）：要求表中的非主属性必须完全依赖于候选键，即消除部分函数依赖。
  • 第三范式（3NF）：要求在2NF的基础上消除传递依赖，即确保表中的每一列数据都只与候选键直接相关，而不是间接相关。

10、规范化过程主要为克服数据库逻辑结构中哪些问题

  • 冗余数据
  • 删除异常
  • 插入异常
  • 更新异常

11、字符串模式匹配中的通配符

  • _表示一位,%表示多位

12、创建存储过程的语句

  • 存储过程是一组预编译的SQL语句集合，它们被命名并存储在数据库中，以便重复使用。
CREATE PROCEDURE p_result_test(out num int, in id int)
BEGIN
SELECT COUNT(*) INTO num FROM result WHERE studentNo = id;
END
  • 这里的in和out咋判断的

13、如何与NULL值进行比较

SELECT * FROM mytable WHERE mycolumn IS NULL;
Copy CodeSELECT * FROM mytable WHERE mycolumn IS NOT NULL;

14、同一个关系模型的任两个元组值能否相同

  • 在关系模型中，每个元组都是唯一的，因此同一个关系模型中的任意两个元组值不能完全相同。关系模型要求每个元组都具有唯一的标识符（通常是主键），这样可以确保每个元组在关系表中是唯一的。

15、常用数据类型：VARCHAR、DATE等

  1. 整数类型（Integer Types）：
    • TINYINT：范围为-128到127的有符号整数，或者0到255的无符号整数。
    • SMALLINT：范围为-32768到32767的有符号整数，或者0到65535的无符号整数。
    • MEDIUMINT：范围为-8388608到8388607的有符号整数，或者0到16777215的无符号整数。
    • INT：范围为-2147483648到2147483647的有符号整数，或者0到4294967295的无符号整数。
    • BIGINT：范围为-9223372036854775808到9223372036854775807的有符号整数，或者0到18446744073709551615的无符号整数。
  2. 小数类型（Decimal Types）：
    • DECIMAL(M, D)：精确的十进制数，M表示总共的位数，D表示小数位数。
  3. 字符串类型（String Types）：
    • CHAR(N)：固定长度的字符串，最多可以存储N个字符。
    • VARCHAR(N)：可变长度的字符串，最多可以存储N个字符。
    • TEXT：用于存储较长的文本数据，最多可以存储65,535个字符。
  4. 日期和时间类型（Date and Time Types）：
    • DATE：用于存储日期，格式为'YYYY-MM-DD'。
    • TIME：用于存储时间，格式为'HH:MM:SS'。
    • DATETIME：用于存储日期和时间，格式为'YYYY-MM-DD HH:MM:SS'。
    • TIMESTAMP：用于存储日期和时间戳，可以自动更新为当前时间。
  5. 布尔类型（Boolean Types）：
    • BOOLEAN 或 BOOL：用于存储逻辑真或假的值。

16、数据库备份的类型

  • 数据库备份的类型主要包括以下几种：
  1. 完整备份（Full Backup）：完整备份是指对整个数据库的备份，包括所有的数据和对象。这种备份类型比较全面，恢复时可以还原数据库到备份时的完整状态。
  2. 增量备份（Incremental Backup）：增量备份是指只备份自上次备份以来发生变化的数据部分。它只备份自上次备份以来新增或修改的数据，相对于完整备份来说，增量备份节省了存储空间，但在恢复时需要依赖完整备份和所有增量备份一起使用。
  3. 差异备份（Differential Backup）：差异备份也是只备份自上次完整备份以来发生变化的数据部分，但不同于增量备份的是，差异备份只备份自上次完整备份以来发生变化的部分数据，而不是自上次备份以来的数据变化。在恢复时，只需要用最近的完整备份和差异备份即可还原数据库到最新状态。
  4. 事务日志备份（Transaction Log Backup）：事务日志备份是备份数据库中的事务日志，通过记录数据库操作的日志来实现对数据库的恢复和灾难恢复。事务日志备份可以帮助恢复到任何时间点的数据库状态，并且对于大型数据库来说是非常重要的备份类型。
  • 其他考点还有热备份,温备份,冷备份之类的

17、三大范式的基本概念

  • 三大范式是关系数据库设计中的基本原则，用于规范化数据库结构，提高数据存储和查询的效率。以下是三大范式的基本概念：
  1. 第一范式（1NF）：第一范式要求数据库表中的每个属性都是原子性的，不可再分。换句话说，每个属性的值都应该是不可再细分的最小单元，不允许多个值组合在一个属性中。例如，如果有一个学生表，每个学生只能有一个学号，不允许一个学号字段中包含多个学生的学号。
  2. 第二范式（2NF）：第二范式要求数据库表中的非主键属性必须完全依赖于主键，而不是部分依赖。简单来说，就是要保证每个非主键属性与主键之间的关系是直接的，没有冗余或部分冗余的情况。例如，如果有一个订单表，主键是订单号和商品编号的组合，订单金额和商品单价应该与订单号和商品编号直接相关，而不是只与订单号相关。
  3. 第三范式（3NF）：第三范式要求数据库表中的非主键属性之间不存在传递依赖关系。也就是说，任何非主键属性都不能依赖于其他非主键属性。这样可以避免数据冗余和更新异常。例如，如果有一个员工表，其中包含员工编号、部门编号和部门名称，部门名称是由部门编号决定的，不应该再将部门名称存储在员工表中，而是应该通过关联部门表来获取部门名称。
  • 总的来说，三大范式的目标是消除数据库中的冗余数据、确保数据的一致性和完整性，并提高数据的存储效率和查询效率。在数据库设计时，应该按照这些范式进行规范化，以提高数据库的质量和性能。

18、模糊查找语句

  • 模糊查找语句用于在数据库中进行模糊匹配查询，通常使用通配符来匹配符合条件的数据。在关系型数据库中，最常用的通配符是 % 和 _。

  1. % 通配符：表示零个或多个字符的任意组合。可以用在模糊查找语句的搜索条件中，表示可以匹配任意字符。例如，如果要查找姓氏以 "张" 开头的所有人，可以使用如下语句：

    Copy CodeSELECT * FROM 表名 WHERE 姓名 LIKE '张%'

  2. _ 通配符：表示单个字符的任意值。可以用在模糊查找语句的搜索条件中，表示匹配一个任意字符。例如，如果要查找姓名为两个字并以 "王" 字开头的所有人，可以使用如下语句：

    Copy CodeSELECT * FROM 表名 WHERE 姓名 LIKE '王_'

  3. 结合通配符使用：可以结合 % 和 _ 通配符进行更复杂的模糊查找。例如，如果要查找姓名以 "李" 开头，并且总共有三个字的所有人，可以使用如下语句：

    Copy CodeSELECT * FROM 表名 WHERE 姓名 LIKE '李__'
  • 需要注意的是，在使用模糊查找时，通配符可能会导致查询的效率降低，因为它需要对更多的数据进行匹配。因此，在设计数据库时，应合理使用模糊查找，并考虑添加适当的索引来提高查询效率。

19、事务的特征有哪些

  • 事务（Transaction）是数据库管理系统中的一个重要概念，具有以下特征：
  1. 原子性（Atomicity）：事务是一个不可分割的工作单位，要么全部执行，要么全部不执行。这意味着事务中的所有操作要么全部成功提交，要么全部失败回滚，不存在部分执行的情况。
  2. 一致性（Consistency）：事务执行前后，数据库从一个一致性状态转换到另一个一致性状态。这意味着事务执行后，数据库必须保持完整性约束、触发器、存储过程等方面的约束条件，确保数据的完整性和一致性。
  3. 隔离性（Isolation）：多个并发事务的执行互不干扰，各个事务的执行独立于其他事务。即使多个事务同时并发执行，每个事务也必须像其它事务在执行时不受其他事务影响一样执行。
  4. 持久性（Durability）：一旦事务提交，其修改将永久保存在数据库中，并不会因系统故障而丢失。即使系统崩溃，数据库也能够通过日志等手段来恢复已提交的事务的结果。
  • 这些特征保证了事务在数据库中的正确执行和可靠性，是数据库管理系统保证数据完整性和一致性的重要手段。

20、E-R模型向关系模型转换的时候，一个M:N联系转为关系模式时，该关系模式的关键字是M端实体关键字和N端实体关键字的组合

  • 在 E-R 模型向关系模型转换的过程中，一个 M:N 联系一般需要转化为一个关系模式，该关系模式的关键字是 M 端实体关键字和 N 端实体关键字的组合。具体转换步骤如下：
  1. 将 M:N 关系转化为 R 关系模式，其中 R 包含 M 实体和 N 实体的全部属性。
  2. 在 R 关系模式中添加一个新的关键字，该关键字由 M 端实体关键字和 N 端实体关键字的组合构成。
  3. 在 R 关系模式中添加两个外键，分别与 M 实体和 N 实体的关键字相对应。
  4. 如果 M 实体或 N 实体有其他属性，则可以将这些属性添加到 R 关系模式中。

  • 例如，有两个实体 Student 和 Course，它们之间存在一个 M:N 的联系，表示学生可以选修多门课程，一门课程也可以被多个学生选修。在转化为关系模式时，可以创建一个新的关系模式 SC（Student-Course），包含以下属性：

  • 学生编号（StudentID）：作为 SC 表的关键字之一，参照 Student 表中的主键。

  • 课程编号（CourseID）：作为 SC 表的关键字之一，参照 Course 表中的主键。

  • 其他属性（如选修时间、成绩等）

  • 同时，在 SC 表中添加两个外键，分别与 Student 和 Course 表的主键相对应。这样，就成功将 M:N 关系转化为了关系模式，并建立了正确的关联关系，使得数据能够以标准化的形式存储和管理。

1、数据操纵语言有哪些

  • 数据操纵语言（Data Manipulation Language，简称DML）是用于操作和查询数据库中数据的语言。常见的数据操纵语言包括以下几种：
  1. SELECT：用于从数据库中检索数据，可以指定要查询的表、列以及查询条件。
  2. INSERT：用于向数据库中插入新的数据行，可以指定要插入的表、列和对应的值。
  3. UPDATE：用于更新数据库中已有的数据行，可以指定要更新的表、列以及更新后的值和更新条件。
  4. DELETE：用于从数据库中删除数据行，可以指定要删除的表和删除条件。

2、聚合函数、视图的作用

  • 聚合函数：聚合函数是一种用于计算数据统计值的函数，例如 SUM、AVG、MAX、MIN 和 COUNT 等。这些函数可以对表中的数据进行聚合操作，从而计算出数据的总和、平均值、最大值、最小值和计数等统计信息。聚合函数通常用于 SELECT 语句中，可以对查询结果进行汇总和统计。
  • 视图：视图是一种虚拟表，它是基于 SELECT 查询语句定义的。通过视图，可以将多个表或查询结果组合在一起，并以一种更加简洁和易于理解的方式呈现给用户。视图可以像表一样使用，可以对其进行查询、过滤、排序和更新等操作。视图还可以提高数据的安全性，通过限制用户对视图的访问权限，可以保护敏感数据不被非授权用户查看。

3、索引的作用

  • 提高检索效率：在表中创建索引后，可以大大提高检索数据的速度。因为索引会按照一定的规则对数据进行排序和分组，从而加快检索的速度。对于大型数据库来说，索引是提高检索效率的关键。
  • 优化排序：如果需要对结果进行排序，使用索引可以避免进行全表扫描，从而加速排序过程。
  • 加速数据的唯一性约束：在表中创建唯一性索引后，可以保证该列的数据唯一，从而避免数据冲突和重复。
  • 优化连接操作：当多个表进行连接查询时，索引可以帮助减少连接所需的时间和资源。
  • 减少存储空间和提高性能：通过使用索引，可以减少表的物理存储空间，并且在检索时可以减少磁盘 I/O 操作，从而提高数据库的性能。

4、事务原子性的定义

  • 事务的原子性是指事务中的所有操作要么全部成功执行，要么全部失败回滚

5、读脏数据的定义

  • 脏数据是指一个事务读取了另一个未提交的事务修改的数据。

6、死锁发生的原因

  • 死锁是指在多个事务之间相互等待对方释放资源而无法继续向前执行的情况。

7、MySQL中获取当前日期时间的方法

  • 使用 NOW() 函数：该函数返回当前系统日期时间，包括年、月、日、时、分、秒等信息。例如，可以使用以下语句获取当前日期时间：

    SELECT NOW();

  • 使用 CURDATE() 函数：该函数返回当前系统日期，不包括时间信息。例如，可以使用以下语句获取当前日期：

    SELECT CURDATE();

  • 使用 CURTIME() 函数：该函数返回当前系统时间，不包括日期信息。例如，可以使用以下语句获取当前时间：

    SELECT CURTIME();

  • 使用 SYSDATE() 函数：该函数返回当前系统日期时间，与 NOW() 函数类似。例如，可以使用以下语句获取当前日期时间：

    SELECT SYSDATE();

8、数据表添加数据的方法

  • INSERT INTO Students (name, age, gender) VALUES ('Alice', 20, 'Female');

9、排它锁和共享锁

  • 排它锁（Exclusive Lock）也称写锁，是一种独占锁，一个事务获取了排它锁后，其他事务不能再对该资源进行任何读写操作，直到拥有排它锁的事务释放锁。如果一个事务获取了排它锁，则其他事务都无法获取该资源的排它锁和共享锁。通常用于更新或删除操作。
  • 共享锁（Shared Lock）也称读锁，是一种共享锁，多个事务可以同时获取同一资源的共享锁，但是不能获取排它锁。获取共享锁的事务只能进行读操作，而不能进行写操作。多个事务获取共享锁后，其他事务仍然可以获取资源的共享锁，但是不能获取排它锁。通常用于查询或读取操作。

10、主键约束的作用

  • 和索引有点像
  1. 唯一标识记录：主键约束确保了表中每一行记录的唯一性，即表中的每条记录都可以通过主键来唯一标识。这样可以避免数据重复和冗余，确保数据的准确性和完整性。
  2. 快速查询：数据库系统会自动为主键创建索引，这样在执行查询操作时可以更快速地定位到需要的记录，提高查询效率。
  3. 作为外键的引用：主键通常被其他表的外键引用，通过外键-主键的关联关系，实现不同表之间的数据关联和一致性维护。
  4. 数据完整性：主键约束可以保证数据的完整性，确保数据库中的数据不会存在重复和不一致的情况。当试图插入重复的主键值或者在外键引用中删除或更新没有对应主键值的记录时，数据库会自动触发错误，阻止此类操作的执行，从而保证数据的完整性。

11、视图中数据和基本表数据的关系

  • 视图数据来源于基本表

  • 视图数据随基本表数据变化而变化

  • 视图数据可以受到基本表约束的限制

12、去掉查询重复数据的方法

  • SELECT DISTINCT column1, column2 FROM table_name;

  • 使用 GROUP BY 子句：如果需要对查询结果进行分组聚合操作，可以使用 GROUP BY 子句来去除重复数据。

13、丢失修改的定义

  • 脏读：事务A查询数据后进行了一次修改且未提交，而事务B这个时候去查询，然后使用了这个数据，因为这个数据还没有被事务A 提交到数据库中，所以事务B的得到数据就是脏数据，对脏数据进行操作可能是不正确的。
  • 不可重复读: 事务A访问了两次数据，但是这访问第二次之间事务B进行一次并进行了修改，导致事务A访问第二次的时候得到的数据与第一次不同，导致一个事务访问两次数据得到的数据不相同。因此叫做不可重复读。
  • 幻读： 与不可重复读都点相似，只是这次是事务B在事务A访问第二次的之前做了一个新增，导致事务A第二次读取的时候发现了多的记录，这就是幻读。
  • 丢失修改：事务A访问该数据，事务B也访问该数据，事务A修改了该数据，事务B也修改了该数据，这样导致事务A的修改被丢失，因此称为丢失修改；

14、存储过程的定义

  • 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集

1、两个实体型之间的联系的三种类型

  • 一对一关系
  • 一对多关系
  • 多对多关系

2、关系型数据库中二维表的名称

  • 在关系型数据库中，二维表也被称为关系

3、关系模型中3类完整性约束

  • 实体完整性约束（Entity Integrity Constraint）： 实体完整性约束确保每个表中的主键列具有唯一且非空的值。主键是用来唯一标识表中每个记录的列或列组合。实体完整性约束要求主键值不能为NULL，并且在表中必须唯一存在，以避免数据冗余和不一致性。
  • 参照完整性约束（Referential Integrity Constraint）： 参照完整性约束用于维护表之间的引用关系，确保引用关系的一致性和有效性。它通常基于外键（Foreign Key）来定义。参照完整性约束要求外键值必须与引用表的主键值相匹配，或者可以为NULL（如果允许）。这样可以防止引用无效的数据，保持数据的一致性。
  • 用户自定义的完整性约束

4、事务的定义

  • 事务是指一组数据库操作，这些操作作为一个逻辑单元被执行，要么全部执行，要么全部不执行，其目的是确保数据的一致性和可靠性。

5、死锁的定义

  • 死锁是指两个或多个事务无法进行的情况，因为每个事务都在等待另一个事务释放资源

6、MYSQL中封锁的两种类型、两种粒度

  • 共享锁（Shared Lock）：共享锁是一种共享的封锁机制，也称为读锁，用于在读取共享资源时防止其他用户进行修改操作。多个事务可以同时持有共享锁，但不能同时持有排他锁。当一个事务持有共享锁时，其他事务只能再次获取共享锁，而不能获取排他锁。
  • 排他锁（Exclusive Lock）：排他锁是一种独占的封锁机制，也称为写锁，用于在修改共享资源时防止其他用户进行读取或修改操作。每个事务只能同时持有一个排他锁，不能同时持有共享锁和排他锁。
  • 表级封锁（Table-level Locking）：表级封锁是一种粗粒度的封锁机制，用于对整个表进行封锁。当一个事务持有表级封锁时，其他事务不能对该表进行任何修改操作，但可以对该表进行读取操作。
  • 行级封锁（Row-level Locking）：行级封锁是一种细粒度的封锁机制，用于对表中的某些行进行封锁。当一个事务持有行级封锁时，其他事务可以访问该表的其它行，但不能访问被封锁的行。

7、E-R图的基本成分

  1. 实体（Entity）： 实体代表现实世界中的一个独立对象，可以是具体的物理实体（如人、车、商品等）或抽象的概念（如订单、课程、公司等）。在 E-R 图中，实体通常用矩形框表示，框内写上实体的名称。
  2. 属性（Attribute）： 属性是实体所具有的特征或性质，用于描述实体的各个方面。属性可以是简单的（如姓名、年龄）或复杂的（如地址、电子邮件），并与实体相关联。在 E-R 图中，属性通常用椭圆形表示，并与相应的实体连接。
  3. 关系（Relationship）： 关系表示实体之间的联系或连接，描述了实体之间的相互作用或依赖关系。关系可以是一对一（One-to-One）、一对多（One-to-Many）或多对多（Many-to-Many）等不同类型。在 E-R 图中，关系通常用菱形表示，并用连线将相关实体连接起来。
  4. 主键（Primary Key）： 主键是唯一标识实体的属性或属性组合，用于确保实体的唯一性和标识性。在 E-R 图中，主键通常用下划线标记，并放置在相应的实体框内。
  5. 外键（Foreign Key）： 外键是一个实体中引用另一个实体的主键，用于建立实体之间的关系。外键通常表示为一个属性，并用箭头指向被引用的实体。
  6. 弱实体（Weak Entity）： 弱实体是一个依赖于其他实体存在的实体，它本身没有足够的属性来唯一标识自己。在 E-R 图中，弱实体通常用双框表示，并用双线连接到它所依赖的实体。

8、视图和虚拟表的概念

  • 视图是一种基于表的虚拟表，它通过一个预定义的 SQL 查询语句来生成一个虚拟表格。视图可以看作是一张虚拟的表格，它的内容并不实际存在于数据库中，而是由查询语句动态生成。视图可以对底层表进行过滤、排序、聚合等操作，从而提供了更加灵活的数据访问方式。视图还可以用于隐藏实际表格中的某些数据或限制用户对某些数据的访问权限。
  • 虚拟表是一种基于数据源的虚拟表，它可以将不同的数据源（如文件、表格、文本等）抽象成一个统一的表格形式。虚拟表的数据是通过查询操作从不同的数据源中动态获取，并以表格的形式呈现出来。虚拟表可以看作是一张包含了多个数据源的虚拟表格，它提供了一种方便的方式来对多个数据源进行联合查询和分析操作。

12、限制查询行数的SQL语句

  • limit关键字

14、PRIMARY KEY（主键）、FOREIGN KEY（外键）的定义

  • PRIMARY KEY（主键）是关系数据库表中的一列或一组列，其作用是唯一标识表中的每一行数据。主键必须具有唯一性，即表中任意两行数据在主键列上的取值不能相同。另外，主键列还不能包含 NULL 值，确保每一行都有一个有效的主键值。主键的作用是确保表中数据的唯一性以及提供快速的数据访问，通常用于查询、索引和连接表之间的关系。
  • FOREIGN KEY（外键）是关系数据库表中的一列或一组列，它建立了与另一个表的关联，用来确保数据完整性和一致性。外键的值通常是另一个表的主键值，通过外键可以实现表与表之间的关联和连接。外键的存在可以保证在进行表的连接操作时，相关的数据能够正确地匹配和检索。

SQL代码编写：创建表；数据表的增、删、改、查

1、E-R图：请用传统方式进行绘制（矩形、菱形、椭圆形、数量关系）；

2、关系模型文字描述，指出关系的主键和外键（如果有）。

届ける言葉を今は育ててる
最后更新于 2024-02-08
啥也没有呀