Mysql-基础知识（上）

关系型数据库实战-以Mysql为例

1.初识mysql

1.1数据库简介

数据库（database）：是用于存储，组织和检索数据的结构化集合或系统，用于管理大量数据 ，允许用户访问/操作这些数据。

数据库通常包含一下关键元素：

1. 数据: 数据库用于存储各种类型的数据，例如文本、数字、图像、音频和视频等。这些数据通常按照一定的结构进行组织，以便于存储和检索。

2. 数据库管理系统（DBMS）: DBMS是一种软件或系统，用于管理数据库的创建、维护、访问和操作。它提供了一组功能和工具，使用户可以定义数据结构、插入、更新、删除和查询数据，以及确保数据的完整性和安全性。

3. 数据模型: 数据模型定义了数据在数据库中的组织方式，包括数据之间的关系。常见的数据模型包括关系型数据库模型（如SQL数据库）、文档型数据库模型、键值对数据库模型、图数据库模型等。

4. 查询语言: 数据库系统通常提供一种查询语言，允许用户以结构化方式查询数据。例如，SQL（Structured Query Language）是一种常见的查询语言，用于关系型数据库。

5. 索引: 索引是一种数据结构，用于加快数据检索速度。它们允许数据库系统快速定位和访问存储在数据库中的特定数据。

6. 安全性: 数据库系统通常提供访问控制和权限管理功能，以确保只有经过授权的用户可以访问和修改数据。

7. 备份和恢复: 数据库系统通常具有备份和恢复功能，以防止数据丢失或损坏，并在需要时还原数据。

1.2数据库分类

数据库可以分为关系型数据库（RDBMS）和非关系型数据库（Nosql）是两种不同类型的数据库管理系统，它们在数据存储、处理和模型方面有一些重要的区别。

关系型数据库 ：

1. 数据结构：数据以表格（表）的形式进行组织，表包含行和列，行表示记录，列表示属性。表之间可以建立关系。（类似excel）

2. 数据一致性：关系型数据库强调数据的一致性，通常使用事务来确保数据的完整性和一致性。

3. 数据模型：使用结构化查询语言（SQL）进行数据操作和查询。SQL是一种强大的查询语言，适用于复杂的数据查询。

4. 扩展性：在大规模数据存储和高并发访问情况下，关系型数据库可能面临性能问题，需要水平和垂直扩展。

5. 常见数据库：MySQL、Oracle、SQL Server、PostgreSQL等。

非关系型数据库：

1. 数据结构：NoSQL数据库支持各种数据模型，包括文档型、键值对、列族和图形等。这使得非关系型数据库适用于不同类型的数据。

2. 数据一致性：NoSQL数据库通常放宽了对数据一致性的要求，允许灵活性和更高的性能。

3. 数据模型：查询语言因数据库类型而异，有些NoSQL数据库使用SQL，但其他数据库使用不同的查询语言或API。

4. 扩展性:NoSQL数据库通常具有良好的横向扩展性，可以更容易地处理大规模数据和高并发请求。

5. 常见数据库：MongoDB（文档型数据库）、Redis（键值对数据库）、Cassandra（列族数据库）、Neo4j（图形数据库）等。

1.3mysql数据类型

整数类型（Integer Types）:

1. TINYINT: 用于存储小整数值，通常占1字节空间。

2. SMALLINT: 用于存储中等大小的整数，通常占2字节空间。

3. MEDIUMINT: 用于存储较大的整数，通常占3字节空间。

4. INT (INTEGER): 用于存储普通整数值，通常占4字节空间。

5. BIGINT: 用于存储大整数值，通常占8字节空间。

类型名称	说明	存储需求
TINYINT	很小的整数	1个字节
SMALLINT	小的整数	2个字节
MEDIUMINT	中等大小的整数	3个字节
INT (INTEGER)	普通大小的整数	4个字节
BIGINT	大整数	8个字节

浮点数类型（Floating-Point Types）:

1. FLOAT: 用于存储单精度浮点数，通常占4字节空间。

2. DOUBLE: 用于存储双精度浮点数，通常占8字节空间。

3. DECIMAL: 用于存储固定精度的小数，需要指定精度和标度。

类型名称	说明	存储需求
FLOAT	单精度浮点数	4个字节
DOUBLE	双精度浮点数	8个字节
DECIMAL	固定精度小数	可变长度

字符类型（Character Types）:

1. CHAR: 固定长度的字符，需要指定字符数，例如CHAR(10)。

2. VARCHAR: 可变长度的字符，需要指定字符数上限，例如VARCHAR(255)。

3. TINYTEXT: 可变长度的短文本，最大长度为255个字符。

4. TEXT: 可变长度的文本，最大长度为65535个字符。

5. MEDIUMTEXT: 可变长度的中等长度文本，最大长度为16777215个字符。

6. LONGTEXT: 可变长度的长文本，最大长度为4294967295个字符。

7. ENUM: 用于存储从预定义列表中选择的一个值。

8. SET: 用于存储从预定义列表中选择的多个值。

类型名称	说明	存储需求
CHAR	固定长度的字符	可变长度
VARCHAR	可变长度的字符	可变长度
TINYTEXT	可变长度的短文本	最多255 字符
TEXT	可变长度的文本	最多65535 字符
MEDIUMTEXT	可变长度的中等长度文本	最多16777215 字符
LONGTEXT	可变长度的长文本	最多4294967295 字符
ENUM	枚举类型	1、2、3、4、7字节（取决于枚举值数量）
SET	集合类型	1、2、3、4、7字节（取决于集合值数量）

日期和时间类型（Date and Time Types）:

1. DATE: 用于存储日期，格式为'YYYY-MM-DD'。

2. TIME: 用于存储时间，格式为'HH:MM:SS'。

3. DATETIME: 用于存储日期和时间，格式为'YYYY-MM-DD HH:MM:SS'。

4. TIMESTAMP: 用于存储时间戳，通常与UTC时间一起使用。

5. YEAR: 用于存储年份，可以是2位或4位表示。

类型名称	说明	存储需求
DATE	日期	3个字节
TIME	时间	3个字节
DATETIME	日期和时间	8个字节
TIMESTAMP	时间戳	4个字节
YEAR	年份	1字节

二进制数据类型（Binary Types）:

1. BINARY: 固定长度的二进制数据。

2. VARBINARY: 可变长度的二进制数据。

3. TINYBLOB: 可变长度的短二进制大对象。

4. BLOB: 可变长度的二进制大对象。

5. MEDIUMBLOB: 可变长度的中等长度二进制大对象。

6. LONGBLOB: 可变长度的长二进制大对象。

类型名称	说明	存储需求
BINARY	固定长度的二进制数据	可变长度
VARBINARY	可变长度的二进制数据	可变长度
TINYBLOB	可变长度的短二进制大对象	最多255 字节
BLOB	可变长度的二进制大对象	最多65535 字节
MEDIUMBLOB	可变长度的中等长度二进制大对象	最多16777215 字节
LONGBLOB	可变长度的长二进制大对象	最多4294967295 字节

布尔类型（Boolean Type）:

1. BOOLEAN (BOOL): 用于存储布尔值，通常用1表示真，0表示假。

类型名称	说明	存储需求
BOOLEAN (BOOL)	布尔类型	1字节

JSON 数据类型（JSON Data Type）:

1. JSON: 用于存储JSON格式的数据。

类型名称	说明	存储需求
JSON	JSON格式的数据	可变长度

1.3三大范式

数据库范式是一种规范化数据库设计的方法，旨在消除数据冗余并确保数据的一致性和完整性。有三个主要的范式，通常被称为三大范式（1NF、2NF和3NF）。

1. 第一范式（1NF - First Normal Form）：

   • 范式定义：数据表中的每个列都包含原子值，即不可再分解。

   • 解释：每个列都应包含单一的、不可再分解的数据，而不是包含多个值的列表或数组。这确保了数据的原子性。

   • 目的：消除数据表中的冗余，并确保数据的原子性。

2. 第二范式（2NF - Second Normal Form）：

   • 范式定义：要符合第二范式，首先必须满足第一范式，然后确保每个非主键列都完全依赖于主键。

   • 解释：每个非主键列的值都应该完全依赖于主键，而不依赖于主键的任何一部分。这有助于消除数据表中的部分函数依赖。

   • 目的：确保数据库表的数据结构能够消除部分函数依赖，以保持数据的一致性和减少冗余。

3. 第三范式（3NF - Third Normal Form）：

   • 范式定义：要符合第三范式，首先必须满足第二范式，然后确保每个非主键列之间没有传递依赖关系。

   • 解释：在第二范式的基础上，确保非主键列之间没有传递依赖关系，即非主键列不直接依赖于其他非主键列。这有助于进一步消除数据表中的冗余。

   • 目的：进一步规范化数据库设计，以确保数据表中没有传递依赖关系，从而减少数据冗余、提高数据的一致性和维护性。

2.数据库基本操作

2.1 SQL的基本书写规则：

• SQL语句不区分大小写，但通常以大写书写关键字，以提高可读性。

• SQL语句以分号（;）作为语句的结束标志。

• 注释可以使用双减号（--）进行单行注释，或使用/* ... */进行多行注释。

2.2数据库基本操作

1. MySQL查看数据库：

• 查看所有数据库：SHOW DATABASES;

• 查看特定数据库的表格：SHOW TABLES FROM database_name;

2.MySQL创建数据库：

• 创建数据库：CREATE DATABASE database_name;

3. MySQL修改数据库：

• 修改数据库名称：ALTER DATABASE old_name RENAME new_name;

4. MySQL删除数据库：

• 删除数据库：DROP DATABASE database_name;

5. MySQL选择数据库：

• 选择数据库：USE database_name;

6.MySQL注释：

• 单行注释：

​​​​​​​-- This is a single-line comment

• 多行注释：

  /*
  This is a
  multi-line comment
  */

3.数据库表的操作

3.1MySQL创建数据表（CREATE TABLE语句）：

使用CREATE TABLE语句来创建新的数据表。以下是基本的语法示例：

sqlCopy code
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(20),
    gender ENUM('0', '1'),
    birth DATE,
    email VARCHAR(30),
    department VARCHAR(50),
    major VARCHAR(20)
);

3.2MySQL ALTER TABLE：修改数据表：

使用ALTER TABLE语句可以对数据表进行各种改，包括添加、修改或删除列，添加或删除约束等。

3.3 MySQL修改/删除字段：

• 修改字段：使用ALTER TABLE语句，例如：

  -- 修改字段名
  ALTER TABLE students
  CHANGE COLUMN gender sex ENUM('0', '1');
  /*
  alter table 表名
  change column 原字段名  新字段名 数据类型；
  */
  ​
  -- 修改字段的数据类型
  ALTER TABLE students
  MODIFY major int;
  /*
  alter table 表名
  modify column 字段名 新数据类型；
  */
  ​
   -- 修改表字段的排列位置
   alter table students modidfy major varchar(20) first/after gender enum('0','1');
   -- ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST|AFTER 字段2 数据类型;
   
   -- 删除表中外键约束
   -- ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

• 删除字段：使用ALTER TABLE语句，例如：

  sqlCopy code
  ALTER TABLE table_name DROP column_name;
  -- ALTER TABLE <表名> DROP <字段名>；

3.4.MySQL删除数据表（DROP TABLE语句）：

使用DROP TABLE语句来删除一个数据表，例如：

sqlCopy code
DROP TABLE table_name;

3.5MySQL删除被其他表关联的主表：

如果一个表被其他表关联，您需要确保首先删除与其相关的外键约束。然后，可以使用DROP TABLE语句删除主表。

删除父表有以下两种方法：

• 先删除与它关联的子表，再删除父表；但是这样会同时删除两个表中的数据。

• 将关联表的外键约束取消，再删除父表；适用于需要保留子表的数据，只删除父表的情况。

3.6MySQL数据表添加字段（三种方式）：

• 使用ALTER TABLE语句的ADD COLUMN子句来添加新的字段。

• 使用ALTER TABLE语句的CHANGE COLUMN子句来修改已有字段的名称和数据类型。

• 使用ALTER TABLE语句的MODIFY COLUMN子句来修改已有字段的数据类型。

-- 在表的最后一列添加字段
alter table students add Country varchar(20);
​
-- 在表的第一列添加字段
alter table students add Country varchar(20) first;
​
-- 在表的指定列后添加字段
alter table students add  Country varchar(20) after gender;

​

3.7MySQL查看表结构命令：

• 使用DESCRIBE语句或DESC缩写来查看表格的结构。

• 使用SHOW CREATE TABLE来查看包括表的创建语句在内的详细信息。

3.8SQL语句对应的文件操作：

SQL语句通常与文件操作没有直接关联。SQL主要用于对数据库进行操作，包括查询、插入、更新和删除数据。如果您需要将数据库中的数据导出到文件或将文件导入到数据库，通常需要使用数据库管理系统提供的导出和导入工具，或编写自定义脚本来执行这些操作。

4.数据的增删改

4.1.插入数据

向表中插入数据最简单的方法就是使用INSERT语句。INSERT语句需要你声明要插入内容的表(table)名和内容(values)。

1.表所有字段插入数据

/*
向表中插入数据最简单的方法就是使用INSERT语句。
INSERT语句需要你声明要插入内容的表(table)名和内容(values)。
*/
 -- 语法规则为： `INSERT INTO 表名 (字段名) VALUES (内容);`
 -- sql
INSERT INTO students (
    student_id, name, 
    gender, 
    birth, 
    email, 
    department, 
    major
)
VALUES (
    1, 
    'John Doe', 
    '1', 
    '2000-01-15', 
    'johndoe@example.com', 
    'Computer Science', 
    'Computer Engineering'
);

2.表指定字段插入数据

 -- 语法规则为： `INSERT INTO 表名 (字段名) VALUES (内容);`
​
INSERT INTO students (
    name,
    email
)
VALUES (
    'Alice Smith', 
    'alice@example.com'
);

3.表同时插入多条数据

 --  INSERT INTO 表名 (字段名) VALUES (内容1), (内容2), (内容3); 。
​
INSERT INTO students (
    name, 
    email
)
VALUES (
    'Alice Smith', 
    'alice@example.com'
        ),
       (
           'Bob Johnson', 
           'bob@example.com'
       ),
       (
           'Charlie Brown', 
           'charlie@example.com'
       );

4.2更新数据

要更新数据库表中的数据，可以使用UPDATE语句。UPDATE语句用于修改表中已存在的记录，通常需要指定要更新的表、要设置新值的列和一个条件来确定哪些行将被更新。

 -- update语法格式 
 UPDATE 表名
SET 列1 = 新值1, 列2 = 新值2, ...
[WHERE 条件];
表名 是您要更新数据的表格名称。

SET 子句用于指定要更新的列以及它们的新值。

WHERE 子句是可选的，用于指定应该更新哪些行。如果省略WHERE子句，将更新表中的所有记录。

 -- 实例
 /*
 示例 1：更新单个行的数据
​
假设我们有一个名为students的表格，其中包含student_id、name和score列。要将学生John的分数更新为90分，可以执行以下SQL语句：
 */
 UPDATE students
SET score = 90
WHERE name = 'John';
​
-- --------------------------------------------------------------------------------------
/* 
示例 2：更新多个行的数据
​
如果要更新多个行的数据，可以使用适当的条件。例如，将所有数学系学生的分数增加10分：
*/
UPDATE students
SET score = score + 10
WHERE department = 'Math';
​
 -- -------------------------------------------------------------------------------------
 
 /*
 示例 3：更新所有行的数据
​
如果省略WHERE子句，将更新表格中的所有记录。例如，将所有学生的分数重置为0：
 */
 UPDATE students
SET score = 0;

4.3删除数据

要删除数据库表中的数据，可以使用DELETE FROM语句。DELETE FROM语句用于删除表中的记录，通常需要指定要删除的表以及一个条件来确定哪些行将被删除。

  -- delete from 语法格式
  DELETE FROM 表名
[WHERE 条件];
表名 是您要从中删除数据的表格名称。

WHERE 子句是可选的，用于指定应该删除哪些行。如果省略WHERE子句，将删除表中的所有记录。

 -- 实例
/*
示例 1：删除单个行的数据
​
假设我们有一个名为students的表格，其中包含student_id、name和score列。要删除名为John的学生的记录，可以执行以下SQL语句：
*/
DELETE FROM students
WHERE name = 'John';
​
 -- -------------------------------------------------------------------------------------
 
 /*
 示例 2：删除多个行的数据
​
如果要删除多个行的数据，可以使用适当的条件。例如，删除所有数学系学生的记录
 */
 DELETE FROM students
WHERE department = 'Math';
 -- -------------------------------------------------------------------------------------
/*
示例 3：删除所有行的数据
​
如果省略WHERE子句，将删除表格中的所有记录。例如，删除表格中的所有数据：
*/
DELETE FROM students;