【MySQL】基础学习——表的操作及数据类型

傷城~ 2022-05-25 01:52 243阅读 0赞

# 表的操作 #

## 1.创建表 ##

语法：

create table table_name(
        field1 datatype,
        field2 datatype
    )charset 字符集 collate 校验规则 engine 存储引擎;

说明：

1.  field表是列名
2.  datatype表是数据类型
3.  chatset设置字符集，如果没有设置以所在的数据库字符集为准
4.  collate校验规则，如果没有指定以所在数据库的校验规则为准
5.  engine设置存储引擎，不同的存储引擎创建表的文件不一样

e.g.  
![这里写图片描述][70]  
不同的存储引擎，创建表的文件不一样。当我们指定的存储引擎为MyISAM时，对应有三个文件  
![这里写图片描述][70 1]  
**查看表的结构**

desc 表名;

e.g.  
![这里写图片描述][70 2]

## 2.修改表 ##

首先我们创建一个表，然后以此为例，说明表的修改操作  
![这里写图片描述][70 3]

**1. 在id后面新增一个性别字段**

alter table tt1 add sex char(1) comment '描述性别' after id;

![这里写图片描述][70 4]  
**2. 更改name长度为20**

alter table tt1 modify name varchar(20);

![这里写图片描述][70 5]  
**3. 删除性别一列**

alter table tt1 drop sex;

![这里写图片描述][70 6]  
**4. 修改表的名称（tt1->test）**

alter table tt1 rename to test;

![这里写图片描述][70 7]  
**5. 修改字符集（修改为gbk）**

alter table test charset=gbk;

![这里写图片描述][70 8]  
**6. 修改字段名（将name修改为xingming） 这里需要注意：新字段需要完整的定义；**

alter table test change name xingming varchar(30);

![这里写图片描述][70 9]

## 3.删除表 ##

drop table table_name;

![这里写图片描述][70 10]

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

# 数据类型 #

我们可以将MySQL中的数据类型分为四类：

1.  数值类型，包括：bit, tinyint, bool, smallint, int, bigint, float, double, decimal
2.  文本、二进制类型，包括：char, varchar, bolb, text
3.  日期时间类型，包括：date, datetime, timestamp
4.  String类型，包括：enum类型和set类型

**1.数值类型**  
　a. 常用的tinyint和int

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>有无符号</th> 
   <th>字节</th> 
   <th>表示范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>tinyint</td> 
   <td>有符号</td> 
   <td>1</td> 
   <td>-128~127</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>无符号</td> 
   <td>1</td> 
   <td>0~255</td> 
  </tr> 
  <tr> 
   <td>int</td> 
   <td>有符号</td> 
   <td>4</td> 
   <td>-2^31~2^31-1</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>无符号</td> 
   <td>4</td> 
   <td>0~2^32-1</td> 
  </tr> 
 </tbody> 
</table>

b. 插入数据越界会报错（在MySQL 5.1的版本中只有警告并不会报错，并且插入的数据是边界值）  
![这里写图片描述][70 11]  
　c. 默认的数据类型是带符号的，可以通过unsigned来说明是无符号的  
![这里写图片描述][70 12]  
　d. unsingned引起的问题  
创建一个表，有a,b两列无符号数，插入数据(1,2)  
![这里写图片描述][70 13]  
　执行a-b发现不能得到-1，这是因为-1被当做无符号数处理了。如果要显示为-1该怎么做？  
　设置参数：`set sql_mode='NO_UNSIGNED_SUBTRACTION';`  
![这里写图片描述][70 14]

**2.位数据类型**  
`bit(M)` M表示每个值的位数，M的范围为1~64  
![这里写图片描述][70 15]  
　　可以发现a列的数据中并没有10，这是因为bit字段在显示时，是按照ASCII显示的，10对应的ASCII是换行。  
　　另外，当我们表示的数据只有两种选择时，可以使用bit(1)表示，会节省空间。例如：性别，用0表示男，用1表示女  
![这里写图片描述][70 16]

3.float类型  
`float[(m,d)][unsigned]`，其中：m表示显示长度，d表示小数位数  
　　当小数位数大于指定的位数时，会进行四舍五入  
![这里写图片描述][70 17]  
　在上面的例子中，m=5, d=2，所以可以表示的范围是-999.99~999.99；但是如果是无符号数呢？  
　如果是无符号数表示的范围是0~999.99，这与int类型是不同的。  
![这里写图片描述][70 18]

**4.decimal**  
　decimal类型也是表示浮点数，与float类似，但是区别在于decimal的精度更高  
![这里写图片描述][70 19]  
　可以看到float的精度大约是7位，而decimal的整数最大位数m为65，支持小数最大位数d是30.  
　  
**5.字符串**  
　char(L) 定长字符串 L表示字符数，最大为255，存入的数据可以使字母或汉字。  
![这里写图片描述][70 20]  
![这里写图片描述][70 21]  
　varchar(N) 边长字符串 N表示字节数，最大为65535  
　varchar长度可以指定为0到65535之间的值，但是有1~3个字节用于记录数据大小，所以有效字节数是65532  
　当编码为utf8时，1个字符占3个字节，所以varchar(n)n的最大值21844  
![这里写图片描述][70 22]  
　为什么底下的例子中用的是21844还是不能成功呢？  
　　**这是因为mysql规定，一行记录长度不能超过65535**，而(int)4 + (21844\*3)+3 > 65535，所以创建失败。  
**char和varchar的区别：**  
　char(4) 插入’ab’ 占用4\*3+3=15字节；  
　varchar(4) 插入’ab’ 占用2\*3+3=9字节；  
所以，如果数据的长度是相同的，那么可以用定长，定长的会造成磁盘空间浪费，但是查找效率更高；而变长字符串会节省空间，但是查找效率低。

**6.日期和时间类型**  
![这里写图片描述][70 23]  
更新时间  
![这里写图片描述][70 24]  
timestamp是时间戳，是系统自动添加的数据，每当数据发生更新时，时间戳就会更新为当前时间。

**7.枚举enum和集合set**  
单选使用枚举类型，多选使用set类型  
例如：  
![这里写图片描述][70 25]

1.  对于enum类型，每一个选项都有对应的数字，比如上面的例子中男对应1，女对应2，再添加记录时，可以使用对应的数字编号。
2.  对于set类型，实际存储的也数字，每一选项，依次对应如下数字：1,2,4,8,16,32…最多64个
3.  如果要查询所有喜欢登山的人，需要使用`find_in_set`；  
    ![这里写图片描述][70 26]

[70]: /images/20220525/1a12eb94d64a4496a0a833d0dcdbb190.png
[70 1]: /images/20220525/63f82411646f403592155c785c4318da.png
[70 2]: /images/20220525/308b90f9b6794dc3ad3196f87390e9ac.png
[70 3]: /images/20220525/792992989aba47f3b7ac570351718a2d.png
[70 4]: /images/20220525/4d623153a40c448788b207f798f2d105.png
[70 5]: /images/20220525/bd55edfe2dce4b2cbca95f6578fb1087.png
[70 6]: /images/20220525/3db74f40a0624318a8183547b3a5551f.png
[70 7]: /images/20220525/74f16c1c9c744cc6aa05031d036f191b.png
[70 8]: /images/20220525/829810d436de41618c72bfdadb25f811.png
[70 9]: /images/20220525/66b515d1bc69418b818d2e2b862b9786.png
[70 10]: /images/20220525/144eb41d019a49a0b325cc6f75098419.png
[70 11]: /images/20220525/4de0a6959b8c42b7a602f6eb9cb3aed1.png
[70 12]: /images/20220525/3d370c1fcd0846f584ec3abad9a76e47.png
[70 13]: /images/20220525/587751ffa2784debbb03d51685ad0356.png
[70 14]: /images/20220525/8949e17e3ad04b0f888ec9cc0e7b9cd0.png
[70 15]: /images/20220525/2ecf8d0b607348118470cc452c25129f.png
[70 16]: /images/20220525/2dc454fc9acd432080748ee1e2079dec.png
[70 17]: /images/20220525/09362bc9ff3a47cf95158b9fcea4d65d.png
[70 18]: /images/20220525/2806a5055f3c4462ac2ef9e82875e5f8.png
[70 19]: /images/20220525/e0ca9c5c66734911b6afbd6dc2731450.png
[70 20]: /images/20220525/b0f92006aaab4ee7967bca889f699be0.png
[70 21]: /images/20220525/cf35def698cf426ba5e4c5ff31de5988.png
[70 22]: /images/20220525/a0eb191d6c2946a88b4ab82d8eeee0e6.png
[70 23]: /images/20220525/a8f85c22f2e544b79045ca96ec368148.png
[70 24]: /images/20220525/058bb7619fd840f691eb65d2d40450d6.png
[70 25]: /images/20220525/4dcd29347d7c4a59aa2b4dc9eceb23e8.png
[70 26]: /images/20220525/fd79eac3fb9d4e2c9a218359783b417e.png