目录
一、常见列类型（字段类型）
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)来节省空间。
演示Ⅰ——
代码如下 :
# 创建一张新的表 CREATETABLE IF NOT EXISTS `table_2` ( `id` INTUNSIGNED, `key` BIT(1)) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;# 向表中添加数据 INSERTINTO table_2 VALUES(0,1);# 查询表 SELECT*FROM table_2;
运行效果 :
编辑
演示Ⅱ——
代码如下 :
CREATETABLE IF NOT EXISTS `table_1` ( `id` bit(8));INSERTINTO 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；
演示——
代码如下 :
CREATETABLE IF NOT EXISTS `table_3` ( `t1` FLOAT, `t2` DOUBLE, `t3` DECIMAL(25,20));INSERTINTO `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类型可以提高查询速度。
演示——
代码如下 :
CREATETABLE IF NOT EXISTS `table_4` ( `content1` CHAR(255), `content2` VARCHAR(255), `content3` TEXT(256)) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;INSERTINTO `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)，时间戳会自动更新为修改时的当前时间戳。
演示——
代码如下 :
CREATETABLE IF NOT EXISTS `table_5` ( `date` DATE, `time` TIME, `datetime` DATETIME, `timestamp` TIMESTAMPNOTNULL DEFAULT CURRENT_TIMESTAMP ONUPDATE CURRENT_TIMESTAMP ) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;INSERTINTO `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;# 创建表（记得添加反引号） CREATETABLE `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)，表的具体内容如下图所示 :
编辑
删除表的代码如下 :
-- 删除表的第一种方式：直接删除整张表。DROPTABLE `table_0`;-- 删除表的第二种方式：删除表的所有数据但保留结构TRUNCATE TABLE `table_1`;-- 删除表的第三种方式：删除表的指定数据且保留结构DELETEFROM `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 NULLDEFAULT '';
④删除列——
ALTER TABLE table_name
DROP column,
DROP column ...;
PS :
①修改表名——
RENAME TABLE 旧表名 TO 新表名;
②修改表字符集——
ALTER TABLE 表名 CHARACTER SET 字符集;
③查看表的结构——
DESC 表名; (可以查看表的列的情况）
2° 代码演示
已知如下表 :
编辑
该表由以下代码形成 :
# Create a demo employee table... CREATETABLE 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));INSERTINTO `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。
代码如下 :
ALTERTABLE `e1` ADD `image` VARCHAR(255)NOTNULL DEFAULT '' AFTER `resume`;ALTERTABLE `e1` MODIFY `job` VARCHAR(60)NOTNULL DEFAULT '';ALTERTABLE `e1` CHANGE `name` `name_EX` VARCHAR(255)NOTNULL DEFAULT '';ALTERTABLE `e1` DROP `sex`;RENAME TABLE `e1` TO `e1_employee_table`;ALTERTABLE `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中，代码如下 :
CREATETABLE IF NOT EXISTS `demo`( `id` MEDIUMINTUNSIGNEDNOTNULL DEFAULT 0, `name` VARCHAR(32)NOTNULL DEFAULT '', `salary` DECIMAL(8,2)) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;INSERTINTO `demo`(`id`,`name`,`salary`)SELECT `eno`,`ename`,`esalary` FROM `employee`;SELECT*FROM `demo`;
运行效果 :
编辑
演示Ⅱ—— 表的去重
对演示Ⅰ中的demo表进行多次蠕虫复制，复制后，demo表中的记录总数如下图所示 :
编辑
先对demo表进行去重操作，代码如下 :
# 1.创建一张临时表，该表的结构与要去重的表相同 CREATETABLE IF NOT EXISTS `temp` LIKE `demo`;# 2.通过DISTINCT关键字处理后，把要去重的表中的数据复制到临时表一份 INSERTINTO `temp` SELECTDISTINCT*FROM `demo`;# 3.删除要去重的表中的全部数据 TRUNCATE TABLE `demo`;# 4.将临时表中的数据复制到要去重的表一份 INSERTINTO `demo` SELECT*FROM `temp`;# 5.删除临时表 DROPTABLE `temp`;# 查看去重后demo表的效果： SELECTCOUNT(*)AS'totalNum_in_demo'FROM `demo`;SELECT*FROM `demo`;
运行效果 :
编辑
编辑
System.out.println("END-----------------------------------------------------------------------------");
