三亚做网站多少钱一平方,网页设计大赛新闻稿,做教育集团的网站建设,艺术生搭建wordpress个人博客[SQL Statements] 基本的SQL知识 之DDL针对表结构和表空间的基本操作
什么是数据库的表以及表空间
在MySQL中#xff0c;一个数据库可以包含多个表#xff0c;每个表是由若干个列#xff08;column#xff09;和行#xff08;row#xff09;组成的。表是存储数据的基本…[SQL Statements] 基本的SQL知识 之DDL针对表结构和表空间的基本操作
什么是数据库的表以及表空间
在MySQL中一个数据库可以包含多个表每个表是由若干个列column和行row组成的。表是存储数据的基本单位用于组织、管理和存储数据。
MySQL中的表空间Tablespace是一个逻辑存储单元它是由若干个数据文件data files组成的。表空间用于存储表和索引数据每个表和索引都有一个关联的表空间。在MySQL中每个表和索引都必须属于一个表空间而每个表空间可以包含多个表和索引。
MySQL中的表空间有两种类型系统表空间和用户表空间。系统表空间是MySQL内部使用的表空间包括InnoDB的数据文件和redo log文件。用户表空间是由用户创建的用于存储用户定义的表和索引数据。
在MySQL中创建表时可以指定它所属的表空间。如果没有指定表空间则会使用默认的表空间。
你可以这样理解数据库中的表可以简单地理解这就是一个excel表格而表空间就是一个或多个表组成一个表空间。
我们常说的一个表中有多个字段这个字段其实就表示一列。
如图所示 其中表结构规定了这一列存放什么类型的数据这就涉及到表的设计了。
如何进行表结构设计
设计的主要步骤如下
确定表的目的和需求确定表的用途和需求包括需要存储哪些数据、如何组织数据等方面的内容。确定表的列和数据类型确定表的列包括列名、数据类型、长度、是否允许为空等属性。通常需要根据数据的类型和业务需求进行选择。设计表的主键和索引主键是用于标识表中每个记录的唯一标识符通常使用自增长整数作为主键。索引可以提高查询性能通常需要根据查询需求设计相应的索引。设计表的约束约束用于限制表中数据的取值范围包括主键约束、唯一约束、非空约束、默认值约束等。设计表之间的关系如果有多个表需要进行关联需要确定表之间的关系包括一对一、一对多和多对多关系。进行数据类型和范式的优化对于大型和复杂的数据库需要进行数据类型和范式的优化以提高性能和减少数据冗余。进行表的命名和注释为表设置一个有意义的名称并对表进行适当的注释以方便后续的管理和维护。
在进行表结构设计时需要根据具体的业务需求和数据库规模进行调整。同时需要遵循一定的设计原则如避免数据冗余、优化数据类型、保持表的一致性和正确性等。
举例
假设我们需要创建一个简单的学生信息管理系统包括以下两个表学生信息表和课程成绩表。
学生信息表包括以下列
学生IDstudent_id整数类型作为主键学生姓名student_name字符串类型最大长度为50学生年龄student_age整数类型学生性别student_gender字符串类型最大长度为10学生专业student_major字符串类型最大长度为50
课程成绩表包括以下列
成绩IDscore_id整数类型作为主键学生IDstudent_id整数类型作为外键关联学生信息表中的学生ID列课程名称course_name字符串类型最大长度为50课程成绩course_score浮点数类型
对于学生信息表和课程成绩表之间的关系我们可以使用一对多关系即一个学生可以对应多个成绩。在这种情况下学生信息表中的学生ID列作为主键课程成绩表中的学生ID列作为外键。同时我们需要在学生ID列上创建索引以提高查询性能。
为了遵循设计原则我们可以将学生姓名、年龄、性别和专业信息拆分成一个独立的表从而避免数据冗余。此外我们还可以将学生信息表和课程成绩表拆分成不同的表空间以便于管理和维护。最后我们可以为表设置有意义的名称和注释如学生信息表students和课程成绩表scores以方便后续的管理和维护。
查看某个数据库中有哪些表
使用show tables;
例如我在test2数据库下查看我有哪些表 数据类型
在去创建表之前我们需要知道mysql中有哪些数据类型以方便创建指定的字段时使用正确的数据类型进行存储
数字类型
数字类型数据主要包括两种和其他编程语言的数据类型一样分为精确数和浮点数。
这每种数据类型均可以指定参数用来表示这个数有多长
精确数
又分为两种整数integer 和 定点数Fixed-Point
Integer类型 - INTEGER, INT, SMALLINT, TINYINT,MEDIUMINT, BIGINT
类型字节长度最小值无符号情况下最小值最大值无符号情况下最大值TINYINT1-1280127255SMALLINT2-3276803276765535MEDIUMINT3-83886080838860716777215INT 最常用4-2147483648021474836474294967295BIGINT8-2^6302^63 -12^64 -1
定点数据 - DECIMAL, NUMERIC
存在这种固定的数据是很有必要的在需要保持精确度很高的情况下就得用到这种类型例如用户的金钱余额、交易金额等数据就必须要保持极高的数据准确的。对于这种数据采用浮点显然不合适因此需要用到定点数据
在MySQL里面NUMERIC类型的实现就是DECIMAL.
DECIMAL可以指定 精度和小数位
例如对于薪资(salary)这一列设置精度为6小数位为2的数据类型
salary DECIMAL(6,2) 这意味着该字段的数据存储范围是 -9999.99 到 9999.99
标准SQL中的 DECIMAL(M) 等价于 DECIMAL(M,0) MySQL支持这两种写法
浮点类型 - FLOAT, DOUBLE
和其他编程语言的一样表示的数据是一个近似值没有特别特别要求精确的小数就用这种类型
FLOAT单精度 4byte
DOUBLE 双精度 8byte
位值类型 - BIT
用来存储1位的值 范围1~64
对于上述数据类型最容易出现的问题便是超出范围和溢出
当MySQL将一个值存储在列数据类型的允许范围之外的数字列中时该结果取决于当时有效的SQL模式
如果启用了严格的SQL模式则MySQL将拒绝带有错误的超出范围的值并且按照SQL标准插入将失败。如果没有启用限制性模式MySQL会将该值剪辑到列数据类型范围的适当端点并存储结果值。
当将超出范围的值分配给一个整数列时MySQL将存储表示列数据类型范围的相应端点的值。
其实说实话一般的解决方案就是修改该列的数据类型存得更大更多一点。
日期和时间类型
表示日期和时间的数据类型如下
DATE, TIME, DATETIME,TIMESTAMP, YEAR
在使用日期和时间类型时请记住以下一般注意事项
MySQL以标准输出格式检索给定日期或时间类型的值但它试图解释您提供的输入值的各种格式例如当您指定要分配给日期或时间类型或与日期或时间类型进行比较的值时。我们期望您提供有效的值。如果您使用其他格式的值则可能会出现不可预测的结果。尽管MySQL尝试以几种格式解释值但必须始终给出日期部分 year-month-day 顺序而不是其他地方的 month-day-year 或 day-month-year 顺序将其他顺序的字符串转换为年-月-日的顺序可以使用 STR_TO_DATE() 方法年份不要只用后两位代替因为无法知道你到底是1999年还是2099年。虽然这种存储是可以的但极度不建议。默认情况下当MySQL遇到一个日期或时间类型的值超出范围或对该类型无效时它会将该值转换为该类型的“零”值。通过将SQL模式设置为适当的值您可以更准确地指定您希望MySQL支持的日期类型。MySQL允许在DATE 或DATETIME列中存储日期或月和日期为零的日期。比如在你不需要确切知道出生日期的时候只需要知道年月时可以存储’1998-02-00’但这样的数据就不要拿来进行运算了。MySQL允许将“0000-00-00”的“0”值存储为“虚拟日期”。在某些情况下这比使用NULL值更方便并且使用更少的数据和索引空间。如要不允许存“0000-00-00”请启用NO_ZERO_DATE模式。‘ZERO’在走 Connector/ODBC 的时候会自动转化为NULL
ZERO数据
日期类型零值你也可以通过这个零值知道存储的格式DATE0000-00-00TIME00:00:00DATETIME0000-00-00 00:00:00TIMESTAMP0000-00-00 00:00:00YEAR0000
datetime和timestamp区别
datetime范围是1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999
timestamp范围是 1970-01-01 00:00:01.000000 到 2038-01-19 03:14:07.999999
字符和字符串类型数据
CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, 以及SET
char 和 varchar
两者的差别在于存储和检索方式不同在最大长度和是否保留尾随空间的方面也不同
要存储的值char(4)存储空间varchar(4)存储空间‘’’ ’4byte‘’1byte‘ab’ab ’4byte‘ab’3byte‘abcd’‘abcd’4byte‘abcd’5byte‘abcdefghijkklm’‘abcd’4byte‘abcd’5byte
char类型 长度 0~255
varchar类型长度 0~65535
BINARY 和 VARBINARY
这两者和char varchar很像这个存储的是 字节字符串 而不是 字符串。也就是说它们有二进制字符集、排序比较等方式
而且最大长度也对应相同
blob 和 text
BLOB是一个二进制的大对象它可以保存可变量的数据。
blob有四个类型 tinyblob、blob、mebiumblob、longblob
text是MySQL对Blob类型的具体实现四个类型不同就在于对最大的数据值支持情况不同
也就是说
text对应的也有四个类型tinytext、text、mebiumtext、longtext
Enum
ENUM是一个字符串对象其值从在表创建时在列规范中明确枚举的允许值列表中选择。
比如 ENUM(Mercury, Venus, Earth)每个值对应的索引如下
值索引NULLNULL‘’0‘Mercury’1‘Venus’2‘Earth’3
SET
SET是一个字符串对象它可以有零个或多个值每个值都必须从创建表时指定的允许值列表中选择。
一个SET列最多可以有64个不同的成员
创建表
-- 直接创建表
create [Temporary] table [if not exists] 表名
(建表定义, ...) [表选项] [分区选项]-- 通过一个查询表达式创建表
create [Temporary] table [if not exists] 表名
[(建表定义, ...)] [表选项] [分区选项] [as] 查询表达式-- 通过一个旧表创建
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名
{ like 旧表名| (like 旧表名) }将创建一个具有给定名称的表。您必须具有对该表的创建权限。默认情况下将使用InnoDB存储引擎在默认数据库中创建表。如果该表存在、没有默认数据库或该数据库不存在则会发生错误。
在创建表时您可以使用 TEMPORARY关键字。临时表仅在当前会话中可见并且在会话关闭时会自动删除。
使用CREATE TABLE ... LIKE根据另一个表的定义创建一个空表包括在原始表中定义的任何列属性和索引 建表定义 主要是指创建表中的每个字段 通过 字段名 字段定义 进行创建 字段定义 字段定义中又主要是指定义字段的类型除此之外还可以定义字段是否为空默认值是否自增是否是外键是否是主键注释信息等 数据类型 [not null | null] [default (值)] [auto_increment] [unique [key]] [primary [key]] [comment 注释信息] 表选项 用来设置表的属性比如存储引擎字符集压缩方式等 分区选项
举例
直接创建表的示例
CREATE TABLE IF NOT EXISTS my_table (id INT PRIMARY KEY,name VARCHAR(50),age INT,address VARCHAR(100)
) CHARSETutf8mb4;该语句创建一个名为 my_table 的表有四个列分别为 id、name、age 和 address其中 id 是主键。存储引擎为 InnoDB字符集为 utf8mb4。
通过一个查询表达式创建表的示例
CREATE TEMPORARY TABLE IF NOT EXISTS my_temp_table AS
SELECT name, age, address FROM my_table WHERE age 18;该语句创建一个名为 my_temp_table 的临时表列名为 name、age 和 address与 my_table 中对应列的数据类型相同数据来自于 my_table 中 age 大于等于 18 的记录。
通过一个旧表创建的示例
CREATE TABLE IF NOT EXISTS my_new_table LIKE my_table;该语句创建一个名为 my_new_table 的表与 my_table 的结构完全一致但不包括索引和数据。
修改表
alter table 表名
[修改操作,...]alter table 将会更改表的结构例如可以添加或删除列、创建或销毁索引、更改现有列的类型或重命名列或表本身。
举例说明
添加列
ALTER TABLE my_table ADD COLUMN email VARCHAR(50) AFTER address;该语句在 my_table 表中添加一个名为 email 的列数据类型为 VARCHAR(50)在 address 列之后。
修改列
ALTER TABLE my_table MODIFY COLUMN age INT UNSIGNED NOT NULL DEFAULT 0;该语句将 my_table 表中的 age 列修改为无符号整数类型非空且默认值为 0。
删除列
ALTER TABLE my_table DROP COLUMN address;该语句删除 my_table 表中的 address 列。
添加主键
ALTER TABLE my_table ADD PRIMARY KEY (id);该语句在 my_table 表中添加一个名为 id 的主键。
添加外键
ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers(id);该语句在 orders 表中添加一个名为 fk_customer_id 的外键引用 customers 表中的 id 列。
添加索引
ALTER TABLE my_table ADD INDEX idx_name (name);该语句在 my_table 表中为 name 列添加一个名为 idx_name 的普通索引。
删除索引
ALTER TABLE my_table DROP INDEX idx_name;该语句删除 my_table 表中名为 idx_name 的索引。
更改表名
ALTER TABLE my_table RENAME TO new_table;该语句将 my_table 表的名称更改为 new_table。
这些操作只是 ALTER TABLE 语句的一部分实际使用时可以根据需要选择相应的操作。
删除表
drop [temporary] table [if exists] 表名[,表名...] [restrict|cascade] 这个操作将会删除一个或者多个表
举例
删除表
DROP TABLE my_table;该语句将 my_table 表从数据库中删除。
判断表是否存在
DROP TABLE IF EXISTS my_table;该语句先判断 my_table 表是否存在如果存在则删除否则不执行任何操作。
删除表的同时删除关联的索引
DROP TABLE my_table CASCADE;该语句将 my_table 表从数据库中删除并删除与其关联的所有索引。
删除表的同时删除关联的触发器
DROP TABLE my_table CASCADE CONSTRAINTS;该语句将 my_table 表从数据库中删除并删除与其关联的所有索引和触发器。
一个安全的删表方式
-- 创建备份表并复制数据
CREATE TABLE my_table_backup LIKE my_table;
INSERT INTO my_table_backup SELECT * FROM my_table;-- 删除原表
DROP TABLE my_table;-- 如果删除了错误的表可以使用备份表恢复数据
-- INSERT INTO my_table SELECT * FROM my_table_backup;-- 如果不再需要备份表可以删除
-- DROP TABLE my_table_backup;联的所有索引。
删除表的同时删除关联的触发器
DROP TABLE my_table CASCADE CONSTRAINTS;该语句将 my_table 表从数据库中删除并删除与其关联的所有索引和触发器。
一个安全的删表方式
-- 创建备份表并复制数据
CREATE TABLE my_table_backup LIKE my_table;
INSERT INTO my_table_backup SELECT * FROM my_table;-- 删除原表
DROP TABLE my_table;-- 如果删除了错误的表可以使用备份表恢复数据
-- INSERT INTO my_table SELECT * FROM my_table_backup;-- 如果不再需要备份表可以删除
-- DROP TABLE my_table_backup;
