Table表操作、常用列类型和DML(增删改)操作

偏执的太偏执、 2022-04-21 15:31 228阅读 0赞

### 一、Table表操作 ###

**1、创建表:**

语法:
    create table 表名(
        字段名 数据类型 [(数据的长度) 约束],
        字段名 数据类型 [(数据的长度) 约束],
        字段名 数据类型 [(数据的长度) 约束],
        ......     #最后一个不需要逗号
    
    );

**2、表的列字段修改:**

1）修改字段名

ALTER TABLE 表名 CHANGE 原列名 新列名 数据类型(长度);

2）修改字段的数据类型或约束

ALTER TABLE 表名 MODIFY 列名 新类型(长度);

3）添加新字段

ALTER TABLE 表名 ADD 列名 类型(长度);

或者同时添加多列

ALTER TABLE 表名
    ADD(
        列名 类型(长度),
        ...
        列名 类型(长度)    # 最后一个不需要逗号
    );

4）删除字段

ALTER TABLE 表名 DROP COLUMN 列名;

5）修改表名

ALTER TABLE 表名 RENAME TO 新表名;

**3、表的删除**

drop table if exists 表名;

**4、表的复制**

1）仅复制表的结构

create table 新建表名 like 要被复制的表名;

2）复制表的结构和数据

create table 新建表名 select * from 要被复制的表名;

3）只复制表结构和部分数据

create table 新建表名 select * from 要被复制的表名 where 条件;

4）只复制部分字段,且不要数据

create table 新建表名 select 需要的列 from 要被复制的表名 where 0;

### 二、常用列类型 ###

**1、常用列类型**

**1）数值类型**

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th style="width:83px;">大小</th> 
   <th style="width:376px;">范围（有符号）</th> 
   <th style="width:176px;">范围（无符号）</th> 
   <th style="width:86px;">用途</th> 
  </tr> 
  <tr> 
   <td>TINYINT</td> 
   <td style="width:83px;">1 字节</td> 
   <td style="width:376px;">(-128，127)</td> 
   <td style="width:176px;">(0，255)</td> 
   <td style="width:86px;">小整数值</td> 
  </tr> 
  <tr> 
   <td>SMALLINT</td> 
   <td style="width:83px;">2 字节</td> 
   <td style="width:376px;">(-32 768，32 767)</td> 
   <td style="width:176px;">(0，65 535)</td> 
   <td style="width:86px;">大整数值</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT</td> 
   <td style="width:83px;">3 字节</td> 
   <td style="width:376px;">(-8 388 608，8 388 607)</td> 
   <td style="width:176px;">(0，16 777 215)</td> 
   <td style="width:86px;">大整数值</td> 
  </tr> 
  <tr> 
   <td>INT或INTEGER</td> 
   <td style="width:83px;">4 字节</td> 
   <td style="width:376px;">(-2 147 483 648，2 147 483 647)</td> 
   <td style="width:176px;">(0，4 294 967 295)</td> 
   <td style="width:86px;">大整数值</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td style="width:83px;">8 字节</td> 
   <td style="width:376px;">(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)</td> 
   <td style="width:176px;">(0，18 446 744 073 709 551 615)</td> 
   <td style="width:86px;">极大整数值</td> 
  </tr> 
  <tr> 
   <td>FLOAT</td> 
   <td style="width:83px;">4 字节</td> 
   <td style="width:376px;">(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)</td> 
   <td style="width:176px;">0，(1.175 494 351 E-38，3.402 823 466 E+38)</td> 
   <td style="width:86px;">单精度<br> 浮点数值</td> 
  </tr> 
  <tr> 
   <td>DOUBLE</td> 
   <td style="width:83px;">8 字节</td> 
   <td style="width:376px;">(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td> 
   <td style="width:176px;">0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td> 
   <td style="width:86px;">双精度<br> 浮点数值</td> 
  </tr> 
  <tr> 
   <td>DECIMAL</td> 
   <td style="width:83px;">对DECIMAL(M,D) ，如果M&gt;D，为M+2否则为D+2</td> 
   <td style="width:376px;">依赖于M和D的值</td> 
   <td style="width:176px;">依赖于M和D的值，<span style="color:#f33b45;">高精度类型，金额货币优先选择</span></td> 
   <td style="width:86px;">小数值</td> 
  </tr> 
 </tbody> 
</table>

取值范围如果加了 unsigned，则最大值翻倍，如 tinyint unsigned 的取值范围为(0~255)。

int(m) 里的 m 是表示 SELECT 查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度，不知道这个 m 有什么用。

小数：float(M,D) double(M,D) decimal(M,D);

M - 表示整数部分和小数部分合起来的总位数

D - 表示小数点后保留位数

不设置后面的M和D值,float和double会根据插入的实际值来确定精度，decimal默认(10,0)表示总体10位数，小数位是0;

**2）日期和时间类型**

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th style="width:82px;">大小<br> (字节)</th> 
   <th style="width:408px;">范围</th> 
   <th style="width:133px;">格式</th> 
   <th>用途</th> 
  </tr> 
  <tr> 
   <td>DATE</td> 
   <td style="width:82px;">3</td> 
   <td style="width:408px;">1000-01-01/9999-12-31</td> 
   <td style="width:133px;">YYYY-MM-DD</td> 
   <td>日期值</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td style="width:82px;">3</td> 
   <td style="width:408px;">'-838:59:59'/'838:59:59'</td> 
   <td style="width:133px;">HH:MM:SS</td> 
   <td>时间值或持续时间</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td style="width:82px;">1</td> 
   <td style="width:408px;">1901/2155</td> 
   <td style="width:133px;">YYYY</td> 
   <td>年份值</td> 
  </tr> 
  <tr> 
   <td>DATETIME</td> 
   <td style="width:82px;">8</td> 
   <td style="width:408px;">1000-01-01 00:00:00/9999-12-31 23:59:59</td> 
   <td style="width:133px;">YYYY-MM-DD HH:MM:SS</td> 
   <td>混合日期和时间值</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td style="width:82px;">4</td> 
   <td style="width:408px;"> <p>1970-01-01 00:00:00/2038</p> <p>结束时间是第&nbsp;<strong>2147483647</strong>&nbsp;秒，北京时间&nbsp;<strong>2038-1-19 11:14:07</strong>，格林尼治时间 2038年1月19日 凌晨 03:14:07</p> </td> 
   <td style="width:133px;">YYYYMMDD HHMMSS</td> 
   <td>混合日期和时间值，时间戳</td> 
  </tr> 
 </tbody> 
</table>

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

**3）字符串类型**

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th>大小</th> 
   <th>用途</th> 
  </tr> 
  <tr> 
   <td>CHAR</td> 
   <td>0-255字节</td> 
   <td>定长字符串</td> 
  </tr> 
  <tr> 
   <td>VARCHAR</td> 
   <td>0-65535 字节</td> 
   <td>变长字符串</td> 
  </tr> 
  <tr> 
   <td>TINYBLOB</td> 
   <td>0-255字节</td> 
   <td>不超过 255 个字符的二进制字符串</td> 
  </tr> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>0-255字节</td> 
   <td>短文本字符串</td> 
  </tr> 
  <tr> 
   <td>BLOB</td> 
   <td>0-65 535字节</td> 
   <td>二进制形式的长文本数据</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>0-65 535字节</td> 
   <td>长文本数据</td> 
  </tr> 
  <tr> 
   <td>MEDIUMBLOB</td> 
   <td>0-16 777 215字节</td> 
   <td>二进制形式的中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT</td> 
   <td>0-16 777 215字节</td> 
   <td>中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td>LONGBLOB</td> 
   <td>0-4 294 967 295字节</td> 
   <td>二进制形式的极大文本数据</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT</td> 
   <td>0-4 294 967 295字节</td> 
   <td>极大文本数据</td> 
  </tr> 
 </tbody> 
</table>

char 和 varchar的区别：

（1）char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格，varchar 不限于此。

（2）char(n) 固定长度，char(4) 不管是存入几个字符，都将占用 4 个字节，varchar 是存入的实际字符数 +1 个字节（n<=255）或2个字节(n>255)，所以 varchar(4),存入 3 个字符将占用 4 个字节。

（3）char 类型的字符串检索速度要比 varchar 类型的快。

更多具体信息参考网站：[https://zixuephp.net/manual-mysql-1320.html][https_zixuephp.net_manual-mysql-1320.html]

**数据类型的属性：**

<table> 
 <tbody> 
  <tr> 
   <td><strong>MySQL关键字</strong></td> 
   <td><strong>含义</strong></td> 
  </tr> 
  <tr> 
   <td>NULL</td> 
   <td>数据列可包含NULL值</td> 
  </tr> 
  <tr> 
   <td>NOT NULL</td> 
   <td>数据列不允许包含NULL值</td> 
  </tr> 
  <tr> 
   <td>DEFAULT</td> 
   <td>默认值</td> 
  </tr> 
  <tr> 
   <td>PRIMARY KEY</td> 
   <td>主键</td> 
  </tr> 
  <tr> 
   <td>AUTO_INCREMENT</td> 
   <td>自动递增，适用于整数类型</td> 
  </tr> 
  <tr> 
   <td>UNSIGNED</td> 
   <td>无符号</td> 
  </tr> 
  <tr> 
   <td>CHARACTER SET name</td> 
   <td>指定一个字符集</td> 
  </tr> 
 </tbody> 
</table>

**2、Java与MySql数据类型对应表**

**      参考文章：[java与mysql对应数据类型][java_mysql]**

<table> 
 <thead> 
  <tr> 
   <th style="width:226px;">MySQL Type Name</th> 
   <th style="width:166px;">Return value of&nbsp;<code>GetColumnTypeName</code></th> 
   <th style="width:455px;">Return value of&nbsp;<code>GetColumnClassName</code></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:226px;"><code>BIT(1)</code>&nbsp;(new in MySQL-5.0)</td> 
   <td style="width:166px;"><code>BIT</code></td> 
   <td style="width:455px;"><code>java.lang.Boolean</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>BIT( &gt; 1)</code>&nbsp;(new in MySQL-5.0)</td> 
   <td style="width:166px;"><code>BIT</code></td> 
   <td style="width:455px;"><code>byte[]</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>TINYINT</code></td> 
   <td style="width:166px;"><code>TINYINT</code></td> 
   <td style="width:455px;"><code>java.lang.Boolean</code>&nbsp;if the configuration property&nbsp;<code>tinyInt1isBit</code>&nbsp;is set to&nbsp;<code>true</code>&nbsp;(the default) and the storage size is 1, or&nbsp;<code>java.lang.Integer</code>&nbsp;if not.</td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>BOOL</code>,&nbsp;<code>BOOLEAN</code></td> 
   <td style="width:166px;"><code>TINYINT</code></td> 
   <td style="width:455px;">See&nbsp;<code>TINYINT</code>, above as these are aliases for&nbsp;<code>TINYINT(1)</code>, currently.</td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>SMALLINT[(M)] [UNSIGNED]</code></td> 
   <td style="width:166px;"><code>SMALLINT [UNSIGNED]</code></td> 
   <td style="width:455px;"><code>java.lang.Integer</code>&nbsp;(regardless of whether it is&nbsp;<code>UNSIGNED</code>&nbsp;or not)</td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>MEDIUMINT[(M)] [UNSIGNED]</code></td> 
   <td style="width:166px;"><code>MEDIUMINT [UNSIGNED]</code></td> 
   <td style="width:455px;"><code>java.lang.Integer</code>&nbsp;(regardless of whether it is&nbsp;<code>UNSIGNED</code>&nbsp;or not)</td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>INT,INTEGER[(M)] [UNSIGNED]</code></td> 
   <td style="width:166px;"><code>INTEGER [UNSIGNED]</code></td> 
   <td style="width:455px;"><code>java.lang.Integer</code>, if&nbsp;<code>UNSIGNED</code>&nbsp;<code>java.lang.Long</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code><strong>BIGINT</strong>[(M)] [UNSIGNED]</code></td> 
   <td style="width:166px;"><code>BIGINT [UNSIGNED]</code></td> 
   <td style="width:455px;"><strong><code>java.lang.Long</code></strong>, if UNSIGNED&nbsp;<code>java.math.BigInteger</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>FLOAT[(M,D)]</code></td> 
   <td style="width:166px;"><code>FLOAT</code></td> 
   <td style="width:455px;"><code>java.lang.Float</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>DOUBLE[(M,B)]</code></td> 
   <td style="width:166px;"><code>DOUBLE</code></td> 
   <td style="width:455px;"><code>java.lang.Double</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code><strong>DECIMAL</strong>[(M[,D])]</code></td> 
   <td style="width:166px;"><code>DECIMAL</code></td> 
   <td style="width:455px;"><strong><code>java.math.BigDecimal</code></strong></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>DATE</code></td> 
   <td style="width:166px;"><code>DATE</code></td> 
   <td style="width:455px;"><code>java.sql.Date <span style="color:#f33b45;">开发中都是用java.util.Date</span></code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>DATETIME</code></td> 
   <td style="width:166px;"><code>DATETIME</code></td> 
   <td style="width:455px;"><code>java.sql.Timestamp</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>TIMESTAMP[(M)]</code></td> 
   <td style="width:166px;"><code>TIMESTAMP</code></td> 
   <td style="width:455px;"><code>java.sql.Timestamp</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>TIME</code></td> 
   <td style="width:166px;"><code>TIME</code></td> 
   <td style="width:455px;"><code>java.sql.Time</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>YEAR[(2|4)]</code></td> 
   <td style="width:166px;"><code>YEAR</code></td> 
   <td style="width:455px;">If&nbsp;<code>yearIsDateType</code>&nbsp;configuration property is set to&nbsp;<code>false</code>, then the returned object type is&nbsp;<code>java.sql.Short</code>. If set to&nbsp;<code>true</code>&nbsp;(the default), then the returned object is of type&nbsp;<code>java.sql.Date</code>&nbsp;with the date set to January 1st, at midnight.</td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>CHAR(M)</code></td> 
   <td style="width:166px;"><code>CHAR</code></td> 
   <td style="width:455px;"><code>java.lang.String</code>&nbsp;(unless the character set for the column is&nbsp;<code>BINARY</code>, then&nbsp;<code>byte[]</code>&nbsp;is returned.</td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>VARCHAR(M) [BINARY]</code></td> 
   <td style="width:166px;"><code>VARCHAR</code></td> 
   <td style="width:455px;"><code>java.lang.String</code>&nbsp;(unless the character set for the column is&nbsp;<code>BINARY</code>, then&nbsp;<code>byte[]</code>&nbsp;is returned.</td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>BINARY(M)</code></td> 
   <td style="width:166px;"><code>BINARY</code></td> 
   <td style="width:455px;"><code>byte[]</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>VARBINARY(M)</code></td> 
   <td style="width:166px;"><code>VARBINARY</code></td> 
   <td style="width:455px;"><code>byte[]</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>TINYBLOB</code></td> 
   <td style="width:166px;"><code>TINYBLOB</code></td> 
   <td style="width:455px;"><code>byte[]</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>TINYTEXT</code></td> 
   <td style="width:166px;"><code>VARCHAR</code></td> 
   <td style="width:455px;"><code>java.lang.String</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><strong><code>BLOB</code></strong></td> 
   <td style="width:166px;"><code>BLOB</code></td> 
   <td style="width:455px;"><strong><code>byte[]</code></strong></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>TEXT</code></td> 
   <td style="width:166px;"><code>VARCHAR</code></td> 
   <td style="width:455px;"><code>java.lang.String</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>MEDIUMBLOB</code></td> 
   <td style="width:166px;"><code>MEDIUMBLOB</code></td> 
   <td style="width:455px;"><code>byte[]</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>MEDIUMTEXT</code></td> 
   <td style="width:166px;"><code>VARCHAR</code></td> 
   <td style="width:455px;"><code>java.lang.String</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>LONGBLOB</code></td> 
   <td style="width:166px;"><code>LONGBLOB</code></td> 
   <td style="width:455px;"><code>byte[]</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>LONGTEXT</code></td> 
   <td style="width:166px;"><code>VARCHAR</code></td> 
   <td style="width:455px;"><code>java.lang.String</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>ENUM('value1','value2',...)</code></td> 
   <td style="width:166px;"><code>CHAR</code></td> 
   <td style="width:455px;"><code>java.lang.String</code></td> 
  </tr> 
  <tr> 
   <td style="width:226px;"><code>SET('value1','value2',...)</code></td> 
   <td style="width:166px;"><code>CHAR</code></td> 
   <td style="width:455px;"><code>java.lang.String</code></td> 
  </tr> 
 </tbody> 
</table>

**1、bolb类型**

一般用于对图片的数据库存储，原理是把图片打成二进制，然后进行的一种存储方式，在java中对应 byte\[\]数组。

**2、boolen类型**

在mysql数据库中，个人认为用int类型代替较好，对bit操作不是很方便，尤其是在具有web页面开发的项目中，表示0/1，对应java类型的Integer较好。

**3、decimal列的声明语法是decimal(m,d)。**

在mysql5.1中，参数的取值范围：

1）M是数字的最大数（精度）。其范围为1～65（在较旧的MySQL版本中，允许的范围是1～254）。

2）D是小数点右侧数字的数目（标度）。其范围是0～30，但不得超过M。

说明：float占4个字节，double占8个字节，decimail(M,D)占M+2个字节。

如DECIMAL(5, 2) 的最大值为9 9 9 9 . 9 9，因为有7 个字节可用。

参考文章：[https://www.cnblogs.com/jerrylz/p/5814460.html][https_www.cnblogs.com_jerrylz_p_5814460.html]

### 三、DML(增删改)操作 ###

数据操作语言（DML：Data Manipulation Language）：其语句包括动词INSERT、UPDATE和DELETE操作。

**1、INSERT语句**

**1）插入一条记录：**

语法：insert into 表名(字段1,字段2,...) values (对应字段1的值,对应字段2点值,...) ;

注意：

（1）insert记录时候要保持插入的记录值类型和表结构设计的类型一致，除了数字类型外，字符串型varchar和日期类型datetime等都需要用单引号包裹起来

（2）不可为空的字段必须插入记录值，可以为null的列在插入的时候可以不写

（3）字段列表和values后面的值必须前后一一对应(内容一致，数量一致，数据类型一致)

**2）插入多条记录：**

语法一：

insert into
         表名(字段1,字段2,...)
    values
         (对应字段1的值,对应字段2点值,...)，
         (对应字段1的值,对应字段2点值,...)，
         (...) ;

语法二：MySQL特有，两者列字段保持一致

insert into 表1(字段1,字段2,...)  select (字段1,字段2,...) from 表2；

**2、UPDATE语句**

**  1）修改单表记录**

语法：update 表名 set 字段名1=新的值, 字段名2=新值 , ... where 条件 ;

**  2）修改多表记录**

语法一：

update 表1 别名,表2 别名
    set 字段1=值 , ...
    where 连接条件 and 筛选条件;

语法二：

update 表1 别名
    [连接类型] join 表2 别名
    on 连接条件
    set 字段1=值 , ...
    where 筛选条件;

案例：修改没有年龄记录的同学的对应年龄的编号都为3

update students s
    left join ages a
    on s.age_id=a.id
    set s.age_id=3
    where a.age is null;

**3、DELETE语句**

**1）单表的删除**

语法：delete from 表名 where 要删除的记录筛选条件;

**2）多表的删除**

语法一：

delete 别名1,别名2
    from 表1 别名1,表2 别名2
    where 连接条件 and 筛选条件 ;

语法二：

delete 别名1,别名2
    from 表1 别名1
    [连接类型] join 表2 别名2
    on 连接条件
    where 筛选条件 ;

注意：如果只删表1的就只写别名1，如果两个表的记录都删就别名1,别名2

**3）删除整个表的全部记录，保留表的结构**

delete from 表名;
    或者
    truncate table 表名;

delete和truncate区别：

（1）delete可以加where条件

（2）truncate删除没有返回值，delete删除有返回值

（3）truncate删除不能回滚，delete删除可以回滚

（4）假如要删除的表上有自增长字段，如果用delete删除，再插入数据，自增长从断点值开始；用truncate删除，再插入数据，从1开始;

（5）truncate效率高

案例：删除李斯的年龄信息

delete a from students s
    inner join ages a
    on s.age_id = a.id
    where s.name='李斯';

知识点整理

ends~

[https_zixuephp.net_manual-mysql-1320.html]: https://zixuephp.net/manual-mysql-1320.html
[java_mysql]: https://blog.csdn.net/qq_42145871/article/details/90379848
[https_www.cnblogs.com_jerrylz_p_5814460.html]: https://www.cnblogs.com/jerrylz/p/5814460.html