MySQL_2 常见列类型与表的基本操作
目录
一、常见列类型（字段类型）
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>Δ默认均为有符号数。若想定义无符号数，需要在定义字段的类型时，在后面添加一个UNSIGHED。
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, 8bytes]
③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 引擎;
其中——
①filed : 字段名，表示指定的列名。
②data_type : 字段的数据类型，表示指定的列类型。
③CHARACTER SET : 若不指定则为表所在数据库的字符集。
④CLOOATE : 若不指定则为表所在数据库的排序规则。
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`;
运行效果 :
编辑
编辑
System.out.println("END-----------------------------------------------------------------------------");
- 点赞
- 收藏
- 关注作者
评论（0）