mysql基础知识

发布于：2024-05-21 ⋅ 阅读:(152) ⋅ 点赞:(0)

1. SQL概述

1.1 SQL的特点

  • 具有综合统一性，不同的数据库支持的SQL稍有不同
  • 非过程化语言
  • 语言简捷，用户易接受
  • 以一种语言结构提供两种使用方式

1.2 SQL语言的组成

  • DDL（数据定义语言）
  • DML（数据操纵语言）
  • DCL（数据控制语言）
  • DQL（数据查询语言）

2. MySQL的基本操作

2.1 DDL

  • 常用操作

    功能 SQL
    查看所有的数据据库 SHOW DATABASES;
    创建数据库 CREATE DABASE [IF EXISTS] mydb1 [CHARSET=utf8];
    选择要操作的数据库 USE mydb1;
    删除数据库 DROP DATABASE [IF EXISTS] mydb1;
    修改数据库编码 ALTER DATABASE mydb1 CHARSET SET utf8;
    查当前数据库的所有表 SHOW TABLES;
    查看指定表的创建语句 SHOW CREATE TABLE 表名;
    查看表结构 DESC 表名;
    删除表 DROP TABLE 表名;
    修改表添加列 ALTER TABLE 表名 ADD 列名 类型(长度) [约束]；
    修改列明和类型 ALTER TABLE 表名 change 旧列名 新列名 类型(长度) [约束];
    修改表删除列 ALTER TABLE 表名 DROP 列名;
    修改表名 RENAME TABLE 表名 TO 新表名；

  • 数据类型

    • 数值类型

      类型 大小(字节)
      TINYINT 1
      SMALLINT 2
      MEDIUMINT 3
      INT 4
      BIGINT 8
      FLOAT 4
      DOUBLE 8
      DECIMAL

    • 日期和时间类型

      类型 格式
      DATE YYYY-MM-DD
      TIME HH:MM:SS
      YEAR YYYY
      DATETIME YYYY-MM-DD HH:MM:SS
      TIMESTAMP YYYYMMDD HHMMSS

    • 字符串类型

      类型 大小(字节) 用途
      CHAR 0-255 定长字符串
      VARCHAR 0-6535 变长
      TINYBLOB 0-255 不超过255个字符的二进制字符串
      TINYTEXT 0-255 短文本字符串
      BLOB 0-65535 二进制形式的长文本数据
      TEXT 0-65535 长文本数据
      MEDIUMBLOB 0-16 777 215 二进制形式的中长度文本数据
      MEDIUMTEXT 0-16 777 215 中等长度文本数据
      LONGBLOB 0-4 294 967 295 二进制形式的极大文本数据
      LONGTEXT 0-4 294 967 295 极大文本数据

2.2 DML

  • 常用操作

    功能 SQL
    数据插入 INSERT INTO 表 [(列名1，列名2…)] VALUES(值1，值2…)
    数据修改 UPDATE 表名 SET 字段名=值，字段名=值 … [WHERE 条件]；
    数据删除 DELECT FROM 表名 [WHERE 条件]

  • 约束constraint

    • 主键约束PRIMARY KEY
    • 自增长约束AUTO_INCREMENT
    • 非空约束NOT NULL
    • 唯一约束UNIQUE
    • 默认约束DEFAULT
    • 零填充约束ZEROFILL
    • 外键约束（谨慎使用）

2.3 DQL

		SELECT 
		    [ALL|DISTINCT] 
		    <目标列的表达式1> [别名],
		    <目标列的表达式2> [别名]...
		FROM <表名|视图名> [别名]，<表名/视图名> [别名]...
		[WHERE <条件表达式>]
		[GROUP BY <列名>]
		[HAVING <条件表达式>]
		[ORDER BY <列表> [ASC|DESC]]
		[LIMIT <数字或列表>];

3. 什么是实体关系模型（ER模型）

        实体-关系模型（Entity-Relationship Model，简称ER模型）是一种用于数据库设计的概念工具。它描述了现实世界中各种实体以及它们之间的关系。在ER模型中，实体表示现实世界中的一个独立对象，可以是具体的物理对象或抽象的概念。关系表示实体之间的联系或连接方式，可以是一对一、一对多或多对多的关系。

4. 如何使用ER图进行数据库设计

        使用ER图进行数据库设计通常包含以下步骤：

  • 确定实体：识别出系统中存在的主要实体，并为每个实体确定一个适当的名称。
  • 确定属性：为每个实体确定相应的属性，这些属性描述了实体的特征。
  • 确定关系：确定实体之间的关系，包含一对一、一对多和多对多的关系。
  • 绘制ER图：根据上述信息，使用矩形框表示实体，使用菱形框表示关系，并使用连线表示实体和关系之间的联系。
  • 优化设计：通过检查ER图，消除冗余，确定主键和外键，并进行必要的优化。

5. MySQL数据库的约束条件

            在MySQL数据库中，约束用于对表中的数据进行限制和约束，确保数据的完整性和一致性。MySQL支持以下几种约束：

5.1.主键约束（PIRMARY KEY）
  • 主键约束用于唯一标识表中的记录。一个表只能有一个主键，且主键不能包含NULL值。
  • 主键的值必须是唯一的，用于确保表中每条记录的唯一性。
  • 在创建表或修改表结构时，可以给某个字段添加主键约束。
5.2. 唯一约束（UNIQUE）
  • 唯一约束用于确保表中某个字段的取值唯一，同一个表可以有多个唯一约束。
  • 唯一约束允许NULL值，但是在表中同一字段中只能有一个NULL值。
5.3. 非空约束（NOT NULL）
  • 非空约束用于确保表中某个字段的值不能为NULL。
  • 如果一个字段被指定为非空约束，则在插入或更新数据时，必须提供字段的非空值。
5.4. 外键约束(FOREIGN KEY)
  • 外键约束用于确保表与表之间的关联完整性。
  • 外键约束建立了两个表之间的关系，并指定了外键与其关联的主键之间的关系。
  • 外键约束可以用于限制表之间的数据操作，如级联更新和级联删除。
5.5. 默认约束
  • 默认约束用于指定字段在未显示插入值时的默认值。
  • 默认约束在插入新记录时，如果没有为字段提供值，则使用默认值。
5.6. 检查约束
  • 检查约束用于检查指定值是否满足check定义的值范围。

6. SQL注入和防御

        SQL注入是一种常见的安全漏洞，它利用应用程序对用户输入数据的处理不当（未经过过滤和验证），使得恶意用户可以通过构造恶意的输入数据来破坏SQL语句的结构，执行恶意的SQL代码，从而绕过应用程序的安全机制，获取、修改或删除数据库中的数据。为防止SQL注入攻击，可以采取以下几种措施：

  • 使用参数化查询（Prepared Statements）或存储过程（Stored Procedures）来执行SQL语句，在执行之前将用户输入作为参数传递，而不是将其直接拼接到SQL语句中。
  • 对用户输入进行充分的过滤和验证，包括输入长度限制、类型检查、白名单过滤等，以过滤恶意代码和特殊字符。
  • 最小化数据库账号的权限，限制数据库账号的能力，确保其只能执行所需的操作，避免攻击者通过注入获得对整个数据库的完全访问权限。
  • 进行安全审计和漏洞扫描，及时发现和修复潜在的安全漏洞。

7. SELECT语句完整的执行顺序

	SELECT <字段名>
	FROM <表名>
	JOIN <表名>
	ON <连接条件>
	WHERE <筛选条件>
	GROUP BY <字段名>
	HAVING <筛选条件>
	UNION
	ORDER BY <字段名>
	LIMIT <限制行数>
  • FROM子句组装来自不同数据源的数据，产生虚拟表1
  • ON子句是JOIN的连接条件，符合连接条件的行数会被记录在虚拟表2
  • JOIN子句中如果制定了LEFT JOIN，那么保留表中未匹配的行数就会作为外部行添加到虚拟表2中，产生虚拟表3；如果有多个JOIN连接，会重复执行步骤1-3，直到处理完所有表。
  • WHERE子句基于指定的条件对虚拟表3进行筛选，符合条件的记录hi被插入到虚拟表4中
  • GROUP BY子句根据子句中的列，对虚拟表2中的记录进行分组，产生虚拟表5
  • 使用聚合函数进行计算
  • 使用HAVING子句对虚拟表5进行过滤，符合条件的记录会被插入到虚拟表6中
  • SELECT的字段选择指定的列，插入到虚拟表7中
  • UNION连接的两个SELECT查询语句会重复执行步骤1-7，产生两个虚拟表7，UNION会将这些记录合并到虚拟表8中
  • 使用ORDER BY对结果集虚拟表8进行排序，产生虚拟表9
  • LIMIT取出指定的记录，返回结果集

8. 数据库连接池

        数据库连接池是一种用于管理和维护数据库连接的技术，旨在提高数据库访问的性能和效率。它通过在应用程序和数据库之间建立一组预先分配的数据库连接，并且这些连接可以被重复使用，而不需要频繁地创建和销毁连接。
数据库连接池的主要优势包括：

  • 提高性能：由于链接池中的连接已经被创建并准备好使用，可以避免频繁地创建和销毁连接地开销，从而提供更快地数据库访问速度。
  • 优化资源利用：连接池可以管理连接的数量，根据应用程序的需求动态调整连接地数量，避免连接过多或不足地情况，从而优化数据库资源的利用。
  • 连接可重用：连接池中地连接可以被多个线程或请求重复使用，避免每次请求都需要创建新连接的开销，提高数据库访问地效率。
  • 连接管理和监控：连接池可以提供连接的管理和监控功能，包括连接的创建、验证、保持活动状态、回收和释放等，确保连接地可用性和稳定性。

9. 数据库三大范式

        数据库设计凡是是一组规则和原则，用于规范化数据库模式，确保数据库地结构更加合理、灵活和高效。常用的数据库设计范式包括第一范式、第二范式、第三范式、BC范式等。

  • 第一范式（1NF）：要求数据库表中的每个字段具有原子性，即每个字段不能再分解成更小的数据项。此外，每个字段必须有一个唯一的名称，确保表中没有重复的数据。
  • 第二范式（2NF）：在满足1NF的基础上，要求表中的非主键字段必须完全依赖于完整主键，而不能依赖于部分主键。换句话说，非主键字段必须与完整主键相关，而不是与部分主键相关。
  • 第三范式（3FN）:在满足2NF的基础上，要求表中的非主键字段之间不存在传递依赖关系。即非主键字段只能依赖于表中的主键字段，而不能依赖于其他非主键字段。
    范式设计的原则是为了减少数据冗余、提高数据一致性和完整性，以及简化数据更新操作。范式的级别越高，数据库模式的结构越规范，但有时可能需要根据实际情况进行灵活处理，根据具体需求权衡范式和性能的关系。

10. 反范式设计

        反范式化的主要思想是在设计数据库时引入冗余数据，以避免频繁的连接操作。这样可以提高查询性能，减少连接操作的开销。此外，反范式化还可以简化复杂的数据模型，使得查询和管理数据变得更加直观和高效。然而，反范式化也存在一些问题和风险，需要谨慎使用。增加冗余数据可能会导致数据不一致问题，因此必须确保在更新数据库时对冗余数据进行正确的处理。此外，反范式化还可以增加存储空间的需求，并且对数据模型的修改和维护会更加困难。

11. JDBC编程的基本步骤

        JDBC（Java DataBase Connectivity）是一种用在Java应用程序和数据库之间进行连接和交互的API。JDBC的编程步骤如下：

  • 加载数据库驱动程序
  • 建立数据库连接
  • 创建Statement
  • 发送并执行SQL
  • 处理SQL执行结果
  • 关闭数据库连接

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布