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

素颜马尾好姑娘i 2023-09-27 19:17 152阅读 0赞

**目录**

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

1.数值类型 :

1° 整型

2° 浮点型

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

3.二进制类型 :

4.日期类型 :

二、表的基本操作

1.创建表 :

1° 基本语法

2° 代码演示

2.删除表 :

1° 基本语法

2° 代码演示

3.修改表 :

1° 基本语法

2° 代码演示

4.复制表 :

1° 基本语法

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;

**运行效果 :**

![89af0dd493fc415f8b26bb32c0031402.png][]

**演示Ⅱ——**

**代码如下 :**

CREATE TABLE IF NOT EXISTS `table_1`
    (
    	`id` bit(8)
    );
    
    INSERT INTO table_1 VALUES(38);
    
    SELECT * FROM table_1;

**运行效果 :**

![8891f98621a6419abe915f1ececa1370.png][]

#### 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`;

**运行效果 :**

![56605e59641244ff8ebaac2907af78f5.png][]

### 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`;

**运行效果 :**

![a587f699a064487a84d923cfaef6c8e9.png][]

### 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`;

**运行结果 :**

![48f51aea4cf04026912cc3be3933a496.png][]

--------------------

## 二、表的基本操作 ##

### 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° 代码演示** ####

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

![f74027276c1847c8baf6ff16fb115f69.png][]

**代码如下 :**

# 创建数据库
    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;

**创建效果 :**

![e199c1915f1d4a689433f7f384256bcf.png][]

### 2.删除表 : ###

#### **1° 基本语法** ####

> **①删除整张表——**
> 
> **DROP TABLE table\_name;**
> 
> **②只删除表数据（不可使用WHERE)——**
> 
> **TRUNCATE TABLE table\_name;**
> 
> **③只删除数据（可使用WHERE)——**
> 
> **DELETE FROM table\_name WHERE...;**

#### **2° 代码演示** ####

现有**三张一模一样的表（table\_0,table\_1,table\_2)**，表的具体内容如下图所示 :

![4822a41550784b8a81df497ebf0180b4.png][]

删除表的**代码如下 :**

-- 删除表的第一种方式：直接删除整张表。
    DROP TABLE `table_0`;
    
    -- 删除表的第二种方式：删除表的所有数据但保留结构
    TRUNCATE TABLE `table_1`;
    
    -- 删除表的第三种方式：删除表的指定数据且保留结构
    DELETE FROM `table_2` WHERE `name` = 'Ice';

**删除效果 :**

![392653b5e1e14fd0ae167b1b31a2610e.png][]

![0728c93cf3c14c6eb8d89e62513123e4.png][]

### 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° 代码演示** ####

**已知如下表 :**

![4a337ebde82e434183cfceae268a449d.png][]

**该表由以下代码形成 :**

# 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`;

**效果如下 :**

![9738468d19b343dda758c3afe52f8824.png][]

![25afaa7c28224c4e8ca8b99e20fbc465.png][]

![94c6d03158af4b4ba12d6a2b1f33131e.png][]

### 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° 代码演示 ####

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

![cba69a5502664b579c663b8903b52dcb.png][]

要求建立一张新表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`;

**运行效果 :**

![daf14e659f6b4ea182aad060f885a6f6.png][]

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

![30bc505b00ee4c629c3ac981ac66f6aa.png][]

先对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`;

**运行效果 :**

![a53516c5d88f4cb28920d6274d8db8bd.png][]

![288a0261e46741dba14470a0ba3496bc.png][]

**System.out.println("END-----------------------------------------------------------------------------");**

[89af0dd493fc415f8b26bb32c0031402.png]: https://img-blog.csdnimg.cn/89af0dd493fc415f8b26bb32c0031402.png
[8891f98621a6419abe915f1ececa1370.png]: https://img-blog.csdnimg.cn/8891f98621a6419abe915f1ececa1370.png
[56605e59641244ff8ebaac2907af78f5.png]: https://img-blog.csdnimg.cn/56605e59641244ff8ebaac2907af78f5.png
[a587f699a064487a84d923cfaef6c8e9.png]: https://img-blog.csdnimg.cn/a587f699a064487a84d923cfaef6c8e9.png
[48f51aea4cf04026912cc3be3933a496.png]: https://img-blog.csdnimg.cn/48f51aea4cf04026912cc3be3933a496.png
[f74027276c1847c8baf6ff16fb115f69.png]: https://img-blog.csdnimg.cn/f74027276c1847c8baf6ff16fb115f69.png
[e199c1915f1d4a689433f7f384256bcf.png]: https://img-blog.csdnimg.cn/e199c1915f1d4a689433f7f384256bcf.png
[4822a41550784b8a81df497ebf0180b4.png]: https://img-blog.csdnimg.cn/4822a41550784b8a81df497ebf0180b4.png
[392653b5e1e14fd0ae167b1b31a2610e.png]: https://img-blog.csdnimg.cn/392653b5e1e14fd0ae167b1b31a2610e.png
[0728c93cf3c14c6eb8d89e62513123e4.png]: https://img-blog.csdnimg.cn/0728c93cf3c14c6eb8d89e62513123e4.png
[4a337ebde82e434183cfceae268a449d.png]: https://img-blog.csdnimg.cn/4a337ebde82e434183cfceae268a449d.png
[9738468d19b343dda758c3afe52f8824.png]: https://img-blog.csdnimg.cn/9738468d19b343dda758c3afe52f8824.png
[25afaa7c28224c4e8ca8b99e20fbc465.png]: https://img-blog.csdnimg.cn/25afaa7c28224c4e8ca8b99e20fbc465.png
[94c6d03158af4b4ba12d6a2b1f33131e.png]: https://img-blog.csdnimg.cn/94c6d03158af4b4ba12d6a2b1f33131e.png
[cba69a5502664b579c663b8903b52dcb.png]: https://img-blog.csdnimg.cn/cba69a5502664b579c663b8903b52dcb.png
[daf14e659f6b4ea182aad060f885a6f6.png]: https://img-blog.csdnimg.cn/daf14e659f6b4ea182aad060f885a6f6.png
[30bc505b00ee4c629c3ac981ac66f6aa.png]: https://img-blog.csdnimg.cn/30bc505b00ee4c629c3ac981ac66f6aa.png
[a53516c5d88f4cb28920d6274d8db8bd.png]: https://img-blog.csdnimg.cn/a53516c5d88f4cb28920d6274d8db8bd.png
[288a0261e46741dba14470a0ba3496bc.png]: https://img-blog.csdnimg.cn/288a0261e46741dba14470a0ba3496bc.png