MySQL表的管理_starry表格意义-优快云博客

本文链接：https://blog.youkuaiyun.com/Starry__/article/details/112569581

1.数据类型

1.1. 整型INT

常见的整型数据为Int占用4字节
有符号int范围：-2147483648~2147483647
无符号int范围：0~4294967295

默认为有符号int，否则应写作INT UNSIGNED
超出范围则插作临界值

CREATE TABLE games(
		index INT
);

1.2. 浮点型FLOAT()与DOUBLE()

float为4字节，double为8字节。
格式为
FLOAT(M,N) 或 DOUBLE(M,N) 其中M表示整数最大位数 + 小数最大位数，N表示小数最大位数
小数超出则四舍五入，不够则补零。整数超出则取临界值例如 FLOAT(5,2)，输入1523.6则会变成999.99

CREATE TABLE games(
		price FLOAT(4,2)
);

1.3. 字符VARCHAR()

VARCHAR表示动态字符型，即内存大小会根据实际字符大小动态申请。
格式为
VARCHAR(M)，其中M表示可输入的最大字符数

CREATE TABLE games(
		game_name VARCHAR(100)
);

1.4. 枚举ENUM()

ENUM表示枚举，即输入的数据必须为枚举其中的一种char，否则为NULL
格式为
ENUM(字符1，字符2，字符3，…)，注意必须是字符型

CREATE TABLE games(
		class ENUM('FPS','ROLEPLAY','STORY')
);

1.5. 集合SET()

SET表示集合，即输入的数据必须为枚举其中的 一种或多种 char，否则为NULL
格式为
SET(字符1，字符2，字符3，…)，注意必须是字符型

CREATE TABLE games(
		class SET('FPS','ROLEPLAY','STORY')
);
INSERT INTO games VALUES('FPS,ROLEPLAY');

1.6. 日期型 DATETIME 与 TIMESTAMP

DATETIME表示时间，包括年 - 月 - 日时 - 分 - 秒
格式为
** ’yyyy-mm-dd hh-mm-ss’ 注意写的时候加引号 **

TIMESTAMP 与 DATETIME 显示相同只不过占用内存更低，表达的时间范围更小。

CREATE TABLE games(
		publishtime DATETIME, finishtime TIMESTAMP
);
INSERT INTO games VALUES('2021-01-13 12:12:12');
INSERT INTO games VALUES('NOW()');

2. 字段约束

指表中各字段应满足怎样的约束条件。

主键 PRIMARY KEY
表示能够唯一区分表中每个行的字段，非空，最多有一个主键，允许组合（组合表示两个列数据共同成为主键，两个字段只要有一个字段不同，就可以达到区分效果）
非空 NOT NULL
表示该字段的数据不可为空，默认为NULLable 可以空
默认 DEFAULT
表示该字段在不赋值时的默认值
唯一 UNIQUE
表示该字段所有值相互不同，但仅仅有一个数据可以为空，可以有多个唯一列，允许组合
自增长（标识列）AUTO_INCREMENT
表示没添加一行数据，该字段自动增长。自增长用NULL替换或者省去，默认起始为1。
注意可以强制赋值插入，只能用于INT、FLOAT和DOUBLE类型。
一个表最多有一个标识列

CREATE TABLE tab(
			id INT PRIMARY KEY AUTO_INCREMENT，
			tab_identity VARCHAR(10)
);
INSERT INTO  tab VALUES(NULL,'apple');
INSERT INTO  tab(tab_indentity) VALUES('apple');
SET auto_increment_increment = 3;		//步长更改

外键 FOREIGN KEY
表示该表为从表，该字段需要索引另外一个主表。外键约束为表级约束

外键必须针对从表进行设计，与主表的关联列必须为主键或唯一键，且外键与关联列之间数据类型与意义必须兼容。
数据插入要先主表再从表，数据删除要先从表再主表。

3. 表的基本操作

3.1. 表的创建

表的创建格式

CREATE TALBE 表名（
列名1 数据类型(大小) 字段约束1 字段约束2 … 字段约束n，
列名2 数据类型(大小) 字段约束1 字段约束2 … 字段约束n，
…
列名n 数据类型(大小) 字段约束1 字段约束2 … 字段约束n，
FOREIGN KEY（列名i） REFERENCES 主表名(联结列)
）;

注意正整数i∈[1,n]

CREATE TABLE targets(
		id INT PRIMARY KEY,
		targets_name VARCHAR(10) UNIQUE NOT NULL,
		device_req INT,

		FOREIGN KEY(device_req) REFERENCES devices(id)		//注意FOREIGN KEY后加括号，REFERENCES后不加括号
);

3.2. 表的复制

将表或表中部分内容复制成新的表。可以实现将查询结果保存为一个表

CREATE TABLE 新表名 LIKE 原表名

CREATE TABLE 新表名 SELECT…（子查询）

CREATE TABLE commander_targets 
SELECT * FROM targets WHERE target_type = commander;  ''

3.3. 表的修改

3.3.1. 修改列名

ALTER TABLE 表名 CHANGE COLUMN 旧列名新列名数据类型

3.3.2. 修改列类型/添加新约束

注意此处只可以添加新约束

ALTER TABLE 表名 MODIFY COLUMN 列名新数据类型新约束

3.3.3. 删除约束

删除主键

ALTER TABLE 表名 DROP PRIMARY KEY

删除唯一约束

ALTER TABLE 表名 DROP INDEX 列名

删除非空和默认
使用ALTER

3.3.4. 添加新列

ALTER TABLE 表名 ADD COLUMN 新列名数据类型约束

注意可以添加外键，没有COLUMN

ALTER TABLE 表名 ADD FOREIGN KEY(列名) REFERENCES 主表(主表关联列)

3.3.5. 删除新列

ALTER TABLE 表名 DROP COLUMN 列名

3.3.6. 修改表名

ALTER TABLE 表名 RENAME TO 新表名

ALTER TABLE targets CHANGE COLUMN id index INT;
ALTER TABLE targets MODIFY COLUMN target_name CHAR UNIQUE NOT NULL;
ALTER TABLE targets ADD COLUMN target_type SET('Commander','Communication','Supplier');
ALTER TABLE targets ADD COLUMN FOREIGN KEY(device_req) REFERENCES devices(id);
ALTER TABLE targets DROP COLUMN target_type;
ALTER TABLE targets RENAME TO goals;

3.4. 表的删除

DROP TABLE 表名

4. 事务

指由多个SQL语句构成的单元，该单元语句的执行要么全部执行，要么不执行。如果出现错误则会回滚（撤销事务的所有改变），确认没有问题则可以提交。

4.1. 事务的ACID属性

一隔久原

一致性(Consistency, C)
事务发生前后，数据库状态不变。
隔离性（Isolation，I）
多个事务之间的执行相互干扰，包括使用的数据相互隔离。
持久性(Durability, D)
事务对数据库的改变是永久的。
原子性（Atomicity，A）
事务的工作不可再分，要么全执行要么都不执行

4.2. 控制事务

4.2.1. 终止自动更改 AUTOCOMMIT

SHOW VARIABLES LIKE 'autocommit'；
SET AUTOCOMMIT = 0;
START TRANSACTION;

第一行为查看变量。
第二句语句使接下来所有的语句的更改都是暂时的，必须确认事务才能将这些语句对表的更改保存。
第三行表示开启事务

4.2.2. 提交 COMMIT

SET AUTOCOMMIT = 0;
UPDATE table1 SET column1 = 'a',column2 = 1 WHERE column3 > 0;
COMMIT;

表示对之前所有的对表的暂时更改保存。

4.2.3. 回滚 ROLLBACK

SET AUTOCOMMIT = 0;
UPDATE table1 SET column1 = 'a',column2 = 1 WHERE column3 > 0;
ROLLBACK;

表示对之前所有的对表的暂时更改撤回。

注意事务中的delete语句可以回滚，但是truncate不可回滚，直接删除并保存结果

4.2.4. 保留点 SAVEPOINT

SET AUTOCOMMIT = 0;
UPDATE table1 SET column1 = 'a',column2 = 1 WHERE column3 > 0;
UPDATE table1 SET column3 = 1.5;
SAVEPOINT a;
INSERT INTO table1(column1) VALUES('x');
ROLLBACK TO a;