MySQL数据库期末知识总结_mysql数据库期末考试重点

数据类型

• TINYINT：1 字节有符号整数，取值范围为 -128~127。
• SMALLINT：2 字节有符号整数，取值范围为 -32768~32767。
• MEDIUMINT：3 字节有符号整数，取值范围为 -8388608~8388607。
• INT 或 INTEGER：4 字节有符号整数，取值范围为 -2147483648~2147483647。
• BIGINT：8 字节有符号整数，取值范围为 -9223372036854775808~9223372036854775807。
• FLOAT(m, d)：单精度浮点数，m 表示数字总位数，d 表示小数部分位数。
• DOUBLE(m, d)：双精度浮点数，m 表示数字总位数，d 表示小数部分位数。
• DECIMAL(m, d)：精确数值，m 表示数字总位数，d 表示小数部分位数。

1. 日期/时间类型

• DATE：日期，格式为 'YYYY-MM-DD'。
• TIME：时间，格式为 'HH:MM:SS'。
• YEAR(2 or 4)：年份，格式为 'YY' 或 'YYYY'。
• DATETIME：日期和时间，格式为 'YYYY-MM-DD HH:MM:SS'。
• TIMESTAMP：日期和时间，格式为 'YYYY-MM-DD HH:MM:SS'，与时区相关。

1. 字符串类型

• CHAR(n)：定长字符串，长度为 n。
• VARCHAR(n)：变长字符串，长度为 n。
• TINYTEXT：小文本，最大长度为 255 字符。
• TEXT：大文本，最大长度为 65535 字符。
• MEDIUMTEXT：中等文本，最大长度为 16777215 字符。
• LONGTEXT：极大文本，最大长度为 4294967295 字符。
• BINARY(n)：定长二进制字符串，长度为 n。
• VARBINARY(n)：变长二进制字符串，长度为 n。
• TINYBLOB：小二进制对象，最大长度为 255 字节。
• BLOB：大二进制对象，最大长度为 65535 字节。
• MEDIUMBLOB：中等二进制对象，最大长度为 16777215 字节。
• LONGBLOB：极大二进制对象，最大长度为 4294967295 字节。
• ENUM('value1', 'value2', ...)：枚举类型，取值为给定的枚举值之一。
• SET('value1', 'value2', ...)：集合类型，取值为给定的集合值之一。

1. 其他类型

• BOOLEAN：布尔类型，取值为 0 或 1。
• JSON：JSON 数据类型，存储 JSON 格式数据。
• POINT：空间数据类型，用于表示一个二维平面上的点。
• GEOMETRY：空间数据类型，用于表示任意空间几何对象。

表设计

MySQL 中的表设计是数据库开发的基础。一个好的表设计能够提高数据的存储效率、查询效率和数据完整性，从而确保数据库的可靠性和性能。在进行表设计时，需要注意以下几个方面：

1. 定义主键：每张表都需要有一个主键，用于唯一标识表中的每个行数据。
2. 设计数据类型：根据实际数据需求选择合适的数据类型。
3. 设计表之间的关系：根据实际业务需求，设计表之间的关系，包括一对一关系、一对多关系和多对多关系。
4. 设计索引：为查询操作提高效率，需要在表中创建适当的索引。 

- 创建数据库：CREATE DATABASE database_name;
- 切换数据库：USE database_name;
- 查看所有数据库：SHOW DATABASES;
- 删除数据库：DROP DATABASE database_name;
- 创建表：CREATE TABLE table_name (column1 datatype, column2 datatype, column3 datatype, ...);
- 查看表结构：DESC table_name;
- 删除表：DROP TABLE table_name;
- 添加字段：ALTER TABLE table_name ADD column_name datatype;
- 修改字段数据类型：ALTER TABLE table_name MODIFY column_name new_datatype;
- 删除字段：ALTER TABLE table_name DROP column_name;

2. 数据增、删、改、查
- 插入数据：INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
- 更新数据：UPDATE table_name SET column_name = new_value WHERE condition;
- 删除数据：DELETE FROM table_name WHERE condition;
- 查询数据：SELECT column1, column2, ... FROM table_name WHERE condition;

3. 数据库索引和优化
- 创建索引：CREATE INDEX index_name ON table_name (column_name);
- 删除索引：DROP INDEX index_name ON table_name;
- EXPLAIN关键字：EXPLAIN SELECT ... 用于分析查询性能，输出查询执行计划。

4. 数据库安全和用户管理
- 创建用户：CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
- 授予权限：GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
- 修改密码：ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';
- 删除用户：DROP USER 'username'@'localhost';

5. 应用领域
- Web应用程序：MySQL常用于存储用户信息、网站内容、日志等。
- 企业信息管理：MySQL用于存储企业的客户信息、供应商信息、产品信息等。
- 游戏开发：MySQL用于存储游戏数据、用户信息等。

6.E-R视图

在MySQL中，可以使用CREATE VIEW语句来创建视图。基本语法格式如下：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW 视图名称 [(属性列)] AS SELECT 字段名称 FROM 表名 [WITH [CASCADED | LOCAL] CHECK OPTION];

可以使用ALTER VIEW语句来对已有的视图进行修改。语法格式如下：

ALTER VIEW 视图名 [(列名列表)] AS SELECT 语句 [WITH CHECK OPTION]

可以使用DROP VIEW语句来删除视图。语法格式如下：

DROP VIEW IF EXISTS 视图名 [视图名2…] [RESTRICT | CASCADE];

• view_name：视图名称，用于在数据库中唯一标识该视图。
• select_statement：SELECT语句，用于查询关联的表或其他视图。

1. 实体和属性：在SELECT语句中使用表字段定义实体和属性。

• E-R视图的三要素包括：

  1、实体（Entity）：实体是指具有独立存在意义的事物或对象，通常对应数据库中的表。在E-R视图中，每个实体都表示一个表，即视图所关联的基本表。
• 2、属性（Attribute）：属性指实体的某个方面特征或属性，通常对应数据库中的字段。在E-R视图中，属性对应于查询语句中列的名称和类型。
• 3、关系（Relationship）：关系指实体之间的相互关系，通常对应数据库中的表之间的关联关系。在E-R视图中，关系用于连接不同的实体，并定义它们之间的关联方式

CREATE VIEW student_view AS SELECT student_name, student_age FROM student_table;

7.主键外键约束

1. 语法：在MySQL中，可以使用ALTER TABLE语句来添加主键和外键约束。其中，主键用PRIMARY KEY关键字表示，而外键用FOREIGN KEY关键字表示。

2. 概念：主键是指表中某个字段或者多个字段的组合，在表中唯一标识每条记录，并确保每条记录都有主键值。而外键是指表中的一个字段或多个字段，这些字段与另外一个表中的主键产生的关联关系，确保在外键表中不会出现不存在的主键值。

3. 规范：在MySQL中，为了确保数据完整性和一致性，必须遵守以下规范：

• 一个表只能有一个主键，且主键不能为空。
• 外键必须引用主键或者具有唯一性约束的字段。
• 外键表中的数据必须存在于主键表中。
• 删除主键表中的记录时，与之关联的外键表中的记录必须做出响应的处理，如级联删除或设为NULL等。

8.一、二、三范式的概念

1. 一范式（1NF）：满足属性原子性，即每列都是不可再分的，不能存在多个值或者重复数据。适用于大部分数据表格，确保数据的单一性和可维护性。例如，一个订单表格中，如果将商品ID和商品名称合并在同一列中，就违反了1NF。

2. 二范式（2NF）：满足所有非主键属性完全依赖于主键，即表中所有非主键字段都必须完整依赖于主键，不能部分依赖于主键。适用于多对一关系的表格，确保数据的唯一性和简洁性。例如，一个学生选课表格中，将学生姓名和课程名称合并在同一列中，就违反了2NF。

3. 三范式（3NF）：在满足2NF的基础上，满足所有非主键属性都不依赖于其他非主键属性。适用于多对多关系的表格，确保数据的无冗余性和结构简明性。例如，一个图书馆借书记录表格中，如果包含了读者信息和图书信息，就违反了3NF。

9.冗余

1. 概念：冗余是指在数据库中存储重复或不必要的数据，造成空间浪费和数据不一致等问题。

2. 语法：在MySQL中，冗余可以通过将某个表的某些字段复制到另外一个表格中来实现。例如，将学生的姓名和班级信息存储在选课表格中，虽然方便了表格查询，但却造成了重复和不一致的数据。

3. 运用场景：冗余主要是在一些特定场景下使用，如高并发系统、查询优化等。例如，在高并发系统中，为了提高数据读取速度，可以将部分数据冗余到其他表格中，以减少跨表格的查询次数。但是，应该谨慎使用冗余功能，避免数据不一致性和冗余问题的发生。

10.存储过程语法

CREATE PROCEDURE procedure_name ([IN | OUT | INOUT] parameter_name data_type [(length)]) 
BEGIN
    -- 存储过程的执行体，包括 SQL 语句和流程控制语句等
END;

其中，procedure_name 为存储过程名称，可以自定义；parameter_name 为存储过程参数名称，可以是 IN（输入参数）、OUT（输出参数）或 INOUT（双向传递参数）中的任何一种；data_type 为数据类型，包括 INT、VARCHAR、DATE 等；length 为可选项，指定数据类型的长度。存储过程的执行体在 BEGIN 和 END 之间，可以包括 SQL 语句和流程控制语句等。

显示存储过程语法：

可以使用以下语句来查看存储过程的定义：

SHOW CREATE PROCEDURE procedure_name;

其中，procedure_name 为要显示的存储过程名称。

调用存储过程语法：

可以使用以下语句来调用存储过程：

CALL procedure_name (param1, param2, ...);

其中，procedure_name 为要调用的存储过程名称，param1、param2 等为存储过程的参数。

删除存储过程语法：

可以使用以下语句来删除存储过程：

DROP PROCEDURE IF EXISTS procedure_name;

其中，procedure_name 为要删除的存储过程名称。如果要删除的存储过程不存在，则添加 IF EXISTS 关键字可以避免发生错误。

11.函数

函数的概念：

在MySQL数据库中，函数是一种可重用的代码块，用于执行特定的操作或计算，并返回一个值。函数可以被看作是一种特殊的存储过程，它们接受输入参数并返回一个值，而不像存储过程那样在执行期间进行多个操作。

MySQL中的函数有以下几个特点：

1. 函数可以接受零个或多个参数，这些参数可以是常量、变量、表达式或任何结构。
2. 函数可以返回一个值或一个表格。
3. 函数允许递归调用，即函数可以在函数体内调用自身。
4. 函数可以包含流程控制语句，例如条件语句和循环语句。
5. 函数的名称必须是唯一的。

1、创建语法如下：

CREATE FUNCTION function_name ([parameter_list]) RETURNS return_type BEGIN -- 函数的执行体，包括 SQL 语句和流程控制语句等 END;

其中，function_name 为要创建的函数名称，parameter_list 为函数参数列表，格式为 parameter_name data_type，多个参数使用逗号分隔；return_type 为函数返回值类型，包括 INT、VARCHAR、DATE 等。函数的执行体在 BEGIN 和 END 之间，可以包括 SQL 语句和流程控制语句等。

2、调用函数语法如下：

SELECT function_name([arguments]);

其中，function_name 为要调用的函数名称，arguments 为传递给函数的参数。

3、删除函数语法如下：

DROP FUNCTION IF EXISTS function_name;

其中，function_name 为要删除的函数名称。如果要删除的函数不存在，则添加 IF EXISTS 关键字可以避免发生错误。

4、设置触发器的语法

触发器的概念：

MySQL中的触发器（Trigger）是一种与表相关联的特殊类型的存储过程，它在特定的数据库事件（如在表中插入、更新或删除数据时）发生时自动执行。触发器可以用于捕获数据库事件并在事件发生时执行一些自定义的操作。与存储过程不同的是，触发器是隐式触发的，由DBMS自动触发执行。

在MySQL中，一个表可以定义多个触发器，每个触发器处理特定的数据库事件。触发器定义了事件响应时要执行的SQL语句或程序代码。触发器可以在插入、更新、删除表中的数据时自动执行，也可以在指定时间段后执行。

MySQL提供了BEFORE和AFTER关键字，让用户能够根据自己的需要设置触发器的执行时机。如果使用BEFORE关键字，则在触发事件发生之前执行触发器操作；如果使用AFTER关键字，则在触发事件发生之后执行触发器操作。

1.创建语法如下：

CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name FOR EACH ROW BEGIN -- 触发器的执行体，包括 SQL 语句和流程控制语句等 END;

其中，trigger_name 为要创建的触发器名称；{BEFORE | AFTER} 指定触发时机为数据插入、更新或删除前还是后；{INSERT | UPDATE | DELETE} 指定触发器关联的操作类型；table_name 指定触发器所关联的表名；FOR EACH ROW 表示对表中每一行数据进行操作。触发器的执行体在 BEGIN 和 END 之间，可以包括 SQL 语句和流程控制语句等。

2.删除触发器语法如下：

DROP TRIGGER IF EXISTS trigger_name;

其中，trigger_name 为要删除的触发器名称。如果要删除的触发器不存在，则添加 IF EXISTS 关键字可以避免发生错误。