MySQL_2 常见列类型与表的基本操作

Cyan_RA9

已于 2024-02-26 21:46:17 修改

阅读量787

点赞数 3

分类专栏： Database(DB) # MySQL 文章标签： java mysql sql 数据库阿里云表的操作列的类型

于 2023-05-07 09:10:12 首次发布

本文链接：https://blog.csdn.net/TYRA9/article/details/130519157

版权

Database(DB) 同时被 2 个专栏收录

19 篇文章 4 订阅

订阅专栏

MySQL

11 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL中常见的字段类型，包括整型、浮点型、文本类型（字符串）、二进制类型和日期类型，以及各类型的使用示例。同时，文章还涵盖了表的基本操作，如创建、删除、修改和复制表的语法及实例演示。

摘要由CSDN通过智能技术生成

一、常见列类型（字段类型）

1.数值类型 :

1° 整型

        ①tinyint [1 byte；有符号-128~127，无符号0~255]

②smallint [2 bytes；有符号-2^15~2^15 - 1，无符号0~2^16 - 1]

③mediumint [3 bytes；有符号-2^23~2^23 - 1，无符号0~2^24 - 1]

④int [4 bytes；有符号-2^31~2^31 - 1，无符号0~2^32 - 1]

⑤bigint [8 bytes；有符号-2^63~2^63 - 1，无符号0~2^64 - 1]

PS : 1>Δ默认均为有符号数。若想定义无符号数，需要在定义字段的类型时，在后面添加一个UNSIGNED。
                2>以上范围均为补码可表示的范围。
                3>bit类型默认为无符号数，使用格式为“bit(M)”，其中M表示指定的位数，默认值是1，实际范围是1~64；使用bit类型的字段，查询时以二进制的形式显示；当需要存入0或者1时，可以考虑使用bit(1)来节省空间。

演示Ⅰ——

代码如下 :

# 创建一张新的表
CREATE TABLE IF NOT EXISTS `table_2` 
(
	`id` INT UNSIGNED,
	`key` BIT(1)
) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;

# 向表中添加数据
INSERT INTO table_2 VALUES(0, 1);

# 查询表
SELECT * FROM table_2;

运行效果 :

演示Ⅱ——

代码如下 :

CREATE TABLE IF NOT EXISTS `table_1`
(
	`id` bit(8)
);

INSERT INTO table_1 VALUES(38);

SELECT * FROM table_1;

运行效果 :

2° 浮点型

①float [single precision, 4 bytes]

②double [double precision, 8 bytes]

③decimal [M, D]，M决定了数的长度（M最大65），D决定了其中的小数位数（D最大30）；如果D是0，表示无小数点或分数部分；若D被省略，默认是0；若M被省略，默认是10；

演示——

代码如下 :

CREATE TABLE IF NOT EXISTS `table_3`
(
	`t1` FLOAT,
	`t2` DOUBLE,
	`t3` DECIMAL(25, 20)
);

INSERT INTO `table_3` VALUES(3.14159265358979323, 3.14159265358979323, 3.14159265358979323);

SELECT * FROM `table_3`;

运行效果 :

2.文本类型（字符串类型） :

①char [0~255(字符)]，固定长度字符串（即分配的空间大小是固定的）；这里的范围就是字符数的范围（不管中英文）。

②varchar [0~65535 (0 ~ 2^16 - 1)（字节）]，可变长度字符串（即分配的空间大小决定于实际的占用情况；这里的范围是字节的范围。实际最大字节是65535 - 3 = 65532，因为要预留出1~3个字节用于存储字段的大小，在不同编码中65532最终对应的字符数不同。

③text [0 ~ 2^16 - 1] ，更简单，存放文本内容时可以考虑，可以将TEXT类型视为VARCHAR类型。PS : TEXT类型没有默认值。

④longtext [0 ~ 2^32 - 1]

PS : 实际使用时，括号中传入的一定是字符数。一般来讲，使用varchar类型更好；但是，若某个字段已明确为定长，使用char类型可以提高查询速度。

演示——

代码如下 :

CREATE TABLE IF NOT EXISTS `table_4`
(
	`content1` CHAR(255),
	`content2` VARCHAR(255),
	`content3` TEXT(256)
) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;

INSERT INTO `table_4` VALUES('Cyan_RA9', 'cyan_ra9', '塞峎_RA9');

SELECT * FROM `table_4`;

运行效果 :

3.二进制类型 :

①blob [0 ~ 2^16 - 1]

②longblob [0 ~ 2^32 - 1]

PS : 一般不会直接存储二进制数据。

4.日期类型 :

①date [日期，年月日]

②time [时间，时分秒]

③datetime [年月份时分秒 YYYY-MM-DD HH:mm:ss]

④timestamp [时间戳]，可在定义时间戳类型的字段时加入以下约束——NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，其中，NOT NULL表示不为空；DEFAULT...表示默认是当前时间戳；ON UPDATE...表示以后每次修改记录(INSERT, UPDATE)，时间戳会自动更新为修改时的当前时间戳。

演示——

代码如下 :

CREATE TABLE IF NOT EXISTS `table_5` (
	`date` DATE,
	`time` TIME,
	`datetime` DATETIME,
	`timestamp` TIMESTAMP 
				NOT NULL DEFAULT CURRENT_TIMESTAMP 
				ON UPDATE CURRENT_TIMESTAMP
) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;

INSERT INTO `table_5`(`date`, `time`, `datetime`) 
	VALUES('2023-5-6', '20:06:55', '2023-5-6 20:06:55');
	
SELECT * FROM `table_5`;

运行结果 :

二、表的基本操作

1.创建表 :

1° 基本语法

CREATE TABLE table_name

(

field1 data_type,

field2 data_type,

field3 data_type...

) CHARACTER SET 字符集 COLLATE 校对规则 ENGINE 引擎;

其中——

①field : 字段名，表示指定的列名。

②data_type : 字段的数据类型，表示指定的列类型。

③CHARACTER SET : 若不指定则为表所在数据库的字符集。

④COLLATE : 若不指定则为表所在数据库的排序规则。

PS : 创建表时，要根据需保存的数据创建相应的列，并根据字段的数据类型定义相关的列类型。

2° 代码演示

首先，选择要创建表的数据库，如下图所示 :

代码如下 :

# 创建数据库
CREATE DATABASE IF NOT EXISTS temp_1 CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;

# 创建表（记得添加反引号）
CREATE TABLE `table_0`
(
	`id` INT,
	`name` VARCHAR(255),
	`password` VARCHAR(255),
	`birthday` DATE
) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;

创建效果 :

2.删除表 :

1° 基本语法

①删除整张表——

DROP TABLE table_name;

②只删除表数据（不可使用WHERE)——

TRUNCATE TABLE table_name;

③只删除数据（可使用WHERE)——

DELETE FROM table_name WHERE...;

2° 代码演示

现有三张一模一样的表（table_0,table_1,table_2)，表的具体内容如下图所示 :

删除表的代码如下 :

-- 删除表的第一种方式：直接删除整张表。
DROP TABLE `table_0`;

-- 删除表的第二种方式：删除表的所有数据但保留结构
TRUNCATE TABLE `table_1`;

-- 删除表的第三种方式：删除表的指定数据且保留结构
DELETE FROM `table_2` WHERE `name` = 'Ice';

删除效果 :

3.修改表 :

1° 基本语法

①添加列——

ALTER TABLE table_name

                ADD column data_type [DEFAULT expr],

ADD column data_type [DEFAULT expr]...;

②修改列的类型——

ALTER TABLE table_name

                MODIFY column data_type [DEFAULT expr],

MODIFY column data_type [DEFAULT expr]...;

③修改列的名字——

ALTER TABLE table_name

                CHANGE 旧列名新列名 data_type NOT NULL DEFAULT '';

④删除列——

ALTER TABLE table_name

                DROP column,

DROP column ...;

PS :

①修改表名——

RENAME TABLE 旧表名 TO 新表名;

②修改表字符集——

ALTER TABLE 表名 CHARACTER SET 字符集;

③查看表的结构——

DESC 表名; (可以查看表的列的情况）

2° 代码演示

已知如下表 :

该表由以下代码形成 :

# Create a demo employee table...

CREATE TABLE IF NOT EXISTS `e1` (
	`id` INT,
	`name` VARCHAR(255),
	`sex` char(4),
	`birthday` DATE,
	`entry_date` DATETIME,
	`job` VARCHAR(255),
	`salary` DOUBLE,
	`resume` TEXT(256)
);

INSERT INTO `e1` 
			VALUES(11, 'Cyan', 'male', '2002-11-17', '2025-11-7 00:00:00', 'nb', 20000.0, 'NB');
			
SELECT * FROM `e1`;

现要求——
增加VARCHAR类型的image列，要求置于resume后；删除sex列；修改job列的长度为60；修改表名为e1_employee_table：修改表的字符集为utf8mb4；将列名name修改为name_EX。

代码如下 :

ALTER TABLE `e1` 
			ADD `image` VARCHAR(255) NOT NULL DEFAULT '' 
			AFTER `resume`;

ALTER TABLE `e1` 
			MODIFY `job` VARCHAR(60) NOT NULL DEFAULT '';


ALTER TABLE `e1` 
			CHANGE `name` `name_EX` VARCHAR(255) NOT NULL DEFAULT '';


ALTER TABLE `e1` 
			DROP `sex`;

RENAME TABLE `e1` TO `e1_employee_table`;

ALTER TABLE `e1_employee_table` CHARACTER SET utf8mb4;

DESC `e1_employee_table`;

效果如下 :

4.复制表 :

1° 基本语法

①表数据的复制——

INSERT INTO table_name(column_1, column_2...)

SELECT (column_1, column_2...) FROM table_name;

该语句可以将查询到的表中的指定数据复制到当前表中，亦可以通过该语句进行自我复制（蠕虫复制）。通过蠕虫复制可以为表中添加海量数据，可用于SQL语句性能的测试。

②表结构的复制——

CREATE TABLE table_name LIKE table2_name;

PS : 表的去重——

1>先创建一张临时表，该表的表结构与要去重的表相同。(使用LIKE)；

2>通过DISTINCT关键字处理后，把要去重的表中的数据复制到临时表一份；

3>清除掉要去重的表中的全部记录；

4>将临时表中的记录复制到要去重的表一份；

5>删除临时表；

2° 代码演示

演示Ⅰ—— 表的复制
现有一张员工表如下 :

要求建立一张新表demo，并将该员工表中的数据复制到新表demo中，代码如下 :

CREATE TABLE IF NOT EXISTS `demo`(
	`id` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	`salary` DECIMAL(8,2)
) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;

INSERT INTO `demo`(`id`,`name`,`salary`)
		SELECT `eno`,`ename`,`esalary`
		FROM `employee`;

SELECT * FROM `demo`;

运行效果 :

演示Ⅱ—— 表的去重
对演示Ⅰ中的demo表进行多次蠕虫复制，复制后，demo表中的记录总数如下图所示 :

先对demo表进行去重操作，代码如下 :

# 1.创建一张临时表，该表的结构与要去重的表相同
CREATE TABLE IF NOT EXISTS `temp` LIKE `demo`;

# 2.通过DISTINCT关键字处理后，把要去重的表中的数据复制到临时表一份
INSERT INTO `temp`
		SELECT DISTINCT * FROM `demo`;
		
# 3.删除要去重的表中的全部数据
TRUNCATE TABLE `demo`;

# 4.将临时表中的数据复制到要去重的表一份
INSERT INTO `demo`
		SELECT * FROM `temp`;
		
# 5.删除临时表
DROP TABLE `temp`;

# 查看去重后demo表的效果：
SELECT COUNT(*) AS 'totalNum_in_demo' 
		FROM `demo`;
SELECT * FROM `demo`;

运行效果 :