表_创建 && 操作-Aet

Bingliaolong 2019-10-26 19:09 Aet | 隐藏边栏 | 抢沙发 | 3 0

文章评分 2 次，平均分 5.0 ：

数据类型

概述

在MySQL中，数据类型决定了表列能够存储的数据类型和大小
选择合适的数据类型对于存储和处理数据非常重要

类型

数值类型（Numeric Types）
日期和时间类型（Date and Time Types）
字符串类型（String Types）
空间类型（Spatial Types）
JSON类型

数值类型

整数类型（Integer Types）


类型	大小	范围-有符合	范围-无符号	用途
TINYINT	1字节	-128 127	0 255	小范围整数
SMALLINT	2字节	-32768 32767	65536	中等范围整数
MEDIUMINT	3字节	-8388608 8388607	16777215	大范围整数
INT	4字节	...	...	通常使用的整数
BIGINT	8字节	...	...	超大范围整数

浮点数和定点数类型（Floating-Point and Fixed-Point Types）


类型	用途
FLOAT	近似小数，精度较低
DOUBLE	近似小数，精度较高
DECIMAL	精确小数，适合存储货币等精确数值

日期和时间类型


类型	存储大小	范围	用途
DATE	3字节	'1000-01-01' 到 '9999-12-31'	存储日期
TIME	3字节	'-838:59:59' 到 '838:59:59'	存储时间
DATETIME	8字节	'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'	存储日期和时间
TIMESTAMP	4字节	'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC	存储Unix时间戳
YEAR	1字节	'1901' 到 '2155'	存储年份

字符串类型


类型	存储大小	用途
CHAR	0-255字节，定长	短文本字符串，定长适合固定长度数据
VARCHAR	0-65535字节，可变长	短文本字符串，变长适合可变长度数据
TINYTEXT	0-255字节	小文本字符串
TEXT	0-65535字节	中等文本字符串
MEDIUMTEXT	0-16,777,215字节	大文本字符串
LONGTEXT	0-4,294,967,295字节	超大文本字符串
BINARY	0-255字节，定长	短二进制数据，定长适合固定长度二进制数据
VARBINARY	0-65535字节，可变长	短二进制数据，变长适合可变长度二进制数据
TINYBLOB	0-255字节	小二进制数据
BLOB	0-65535字节	中等二进制数据
MEDIUMBLOB	0-16,777,215字节	大二进制数据
LONGBLOB	0-4,294,967,295字节	超大二进制数据
ENUM	1-2字节，根据值的数量	枚举类型，适合有限的字符串集合
SET	1-8字节，根据值的数量	集合类型，适合有限的字符串集合

空间类型

MySQL支持的空间类型包括：
1. 这些类型用于存储地理空间数据，如坐标点、多边形等
GEOMETRY
POINT
LINESTRING
POLYGON

JSON类型

存储JSON格式的文档数据

类型的选择

整数类型：
1. 根据数值范围选择合适的整数类型（如TINYINT, SMALLINT, INT, BIGINT）
2. 使用无符号类型（UNSIGNED）存储非负整数，可以增加数值范围
浮点数和定点数类型：
1. 使用FLOAT和DOUBLE存储近似小数，适合科学计算和测量数据
2. 使用DECIMAL存储精确小数，适合货币和财务数据
日期和时间类型：
1. 使用DATE存储日期，使用TIME存储时间
2. 使用DATETIME或TIMESTAMP存储日期和时间，根据应用需求选择合适的类型
3. 使用YEAR存储年份数据
字符串类型：
1. 根据字符串长度选择合适的类型（如CHAR, VARCHAR, TEXT）
2. 使用CHAR存储定长字符串，使用VARCHAR存储变长字符串
3. 使用BLOB和TEXT存储大文本和二进制数据
空间类型：
1. 使用空间类型存储地理空间数据，如坐标点、多边形等
JSON：
1. 使用JSON类型存储结构化的JSON数据，适合需要灵活存储结构化数据的应用

表操作

创建表

CREATE TABLE 语句用于创建新表

CREATE TABLE employees (

emp_id INT AUTO_INCREMENT PRIMARY KEY,

emp_name VARCHAR(255) NOT NULL,

dept_id INT,

salary DECIMAL(10, 2),

hire_date DATE

);

修改表

ALTER TABLE 语句用于修改现有表的结构
添加列

1 2	ALTER TABLE employees ADD phone_number VARCHAR(20);

修改列

1 2	ALTER TABLE employees MODIFY COLUMN phone_number VARCHAR(15);

删除列

1 2	ALTER TABLE employees DROP COLUMN phone_number;

重命名列

1 2	ALTER TABLE employees CHANGE emp_name employee_name VARCHAR(255);

重命名表

1	RENAME TABLE employees TO staff;

删除表

DROP TABLE 语句用于删除现有表和其所有数据

1	DROP TABLE employees;

查询

SELECT 语句用于从表中查询数据
查询所有列

1	SELECT * FROM employees;

查询特定列：

1	SELECT emp_name, salary FROM employees;

查询带条件的数据：

1 2	SELECT * FROM employees WHERE dept_id = 1;

排序查询结果：

1 2	SELECT * FROM employees ORDER BY salary DESC;

分组查询结果：

SELECT dept_id, AVG(salary) AS avg_salary

FROM employees

GROUP BY dept_id;

使用聚合函数：

1 2	SELECT COUNT(*), AVG(salary), MAX(salary) FROM employees;

插入

INSERT INTO 语句用于向表中插入新数据
插入单行：

1 2	INSERT INTO employees (emp_name, dept_id, salary, hire_date) VALUES ('Alice', 1, 5000.00, '2020-01-15');

插入多行：

INSERT INTO employees (emp_name, dept_id, salary, hire_date)

VALUES

('Bob', 2, 6000.00, '2019-03-22'),

('Charlie', 1, 5500.00, '2021-07-01');

更新

UPDATE 语句用于更新表中的现有数据

UPDATE employees

SET salary = 7000.00

WHERE emp_name = 'Alice';

删除数据

DELETE FROM 语句用于删除表中的现有数据
删除某一项：

1 2	DELETE FROM employees WHERE emp_name = 'Charlie';

删除所有数据（但保留表结构）：
1. 两种方法
2. TRUNCATE 更快速，但无法回滚

1	DELETE FROM table_name;

1	TRUNCATE TABLE table_name;

其他操作

创建索引

1 2	CREATE INDEX idx_emp_name ON employees (emp_name);

删除索引

1 2	DROP INDEX idx_emp_name ON employees;

索引

原理

在InnoDB中，表的数据存储在表空间（tablespace）中，表空间由多个页（pages）组成，
1. 每个页通常为16KB
2. 每个页包含多个行
3. 行数据包括表的各列数据
4. 索引通过指向页和行偏移的指针来快速查找数据
创建索引过程：
1. 当您创建一个索引时，首先需要指定要在哪个表的哪一列上创建索引
  这一步骤将索引与目标表的指定列关联起来
2. 数据库会扫描目标列中的所有值，并构建一个索引数据结构（如B+树）
3. 在B+树的叶子节点上，存储索引键（即目标列的值）和指向实际数据行的指针
  每个叶子节点存储一个索引键值（例如，emp_name列的值）以及一个指向数据行的指针
  指针通常包括页编号和页内偏移，指示数据行在表空间中的确切位置

索引类型

单列索引（Single-Column Index）：基于单个列创建的索引
多列索引（Multi-Column Index）：基于多个列创建的索引，也称为组合索引（Composite Index）
唯一索引（Unique Index）：保证索引列中的值是唯一的，不允许重复
全文索引（Full-Text Index）：用于对文本数据进行全文搜索（仅限于CHAR、VARCHAR和TEXT列）
空间索引（Spatial Index）：用于地理数据的快速查询（如GEOMETRY类型）

优点

提高查询速度：索引可以显著提高SELECT语句的查询速度
加速排序和分组操作：索引可以加速ORDER BY、GROUP BY、DISTINCT等操作
加速连接操作：索引可以加速JOIN操作，特别是在大表之间的连接

缺点

增加存储空间：索引需要额外的存储空间来存储索引结构
降低写操作性能：每次插入、更新或删除数据时，索引也需要相应地更新，这会降低写操作的性能

使用索引查询

数据库管理系统会自动使用索引来优化查询
1. 你不需要在查询中显式地指定使用哪个索引
2. 数据库查询优化器会根据查询条件自动选择最优的索引

备注

二进制数据

二进制数据是指直接以二进制格式存储的数据，而不是以人类可读的文本格式存储的数据
二进制数据可以是图像、音频、视频、压缩文件、文档等各种文件类型
1. 这些数据需要用特定的程序或解码方式才能正确读取和解释
示例如下：
创建一个表来存储图像文件及其相关信息

CREATE TABLE images (

id INT AUTO_INCREMENT PRIMARY KEY,

filename VARCHAR(255) NOT NULL,

image_data LONGBLOB NOT NULL

);

使用Python脚本将图像文件插入到MySQL数据库中

import mysql.connector

# 连接到MySQL数据库

conn = mysql.connector.connect(

host="localhost",

user="yourusername",

password="yourpassword",

database="yourdatabase"

)

cursor = conn.cursor()

# 读取图像文件

with open("example.jpg", "rb") as file:

binary_data = file.read()

# 插入图像文件到数据库

query = "INSERT INTO images (filename, image_data) VALUES (%s, %s)"

cursor.execute(query, ("example.jpg", binary_data))

conn.commit()

# 关闭数据库连接

cursor.close()

conn.close()

使用Python脚本从MySQL数据库中检索图像文件并将其保存到本地文件系统

import mysql.connector

# 连接到MySQL数据库

conn = mysql.connector.connect(

host="localhost",

user="yourusername",

password="yourpassword",

database="yourdatabase"

)

cursor = conn.cursor()

# 从数据库中检索图像文件

query = "SELECT filename, image_data FROM images WHERE id = %s"

cursor.execute(query, (1,))

result = cursor.fetchone()

filename = result[0]

binary_data = result[1]

# 将图像文件保存到本地文件系统

with open(f"retrieved_{filename}", "wb") as file:

file.write(binary_data)

# 关闭数据库连接

cursor.close()

conn.close()

注意：
1. BLOB类型可以存储大量二进制数据，但请注意它们会占用较多的存储空间
  TINYBLOB: 最多存储255字节
  BLOB: 最多存储65,535字节（约64KB）
  MEDIUMBLOB: 最多存储16,777,215字节（约16MB）
  LONGBLOB: 最多存储4,294,967,295字节（约4GB）
2. 频繁的读写大BLOB数据可能会影响数据库性能，特别是在网络传输时

变长字符串

VARCHAR 的存储大小根据实际字符串长度变化，并且需要额外的1或2个字节来存储长度信息：
1. 如果最大长度小于或等于255字节，使用1个字节来存储长度信息
2. 如果最大长度大于255字节，使用2个字节来存储长度信息

索引意味着什么？

索引（Index）是数据库管理系统中的一个数据结构，用于快速查询和检索数据库表中的记录
1. 索引类似于书本的目录，可以显著提高数据库查询的速度
2. 通过在表的列上创建索引，数据库可以快速定位到所需的数据，而无需扫描整个表

数据类型
概述
类型
数值类型
日期和时间类型
字符串类型
空间类型
JSON类型
类型的选择
表操作
创建表
修改表
删除表
查询
插入
更新
删除数据
其他操作
索引
原理
索引类型
优点
缺点
使用索引查询
备注
二进制数据
变长字符串
索引意味着什么？

mysql

本文为原创文章，版权归Aet所有，欢迎分享本文，转载请保留出处！

Bingliaolong 关注：0 粉丝：0 最后编辑于：2024-06-26

Everything will be better.