当前位置：移动技术网 > IT编程>脚本编程>Python > MySQL数据类型与操作

MySQL数据类型与操作

2019年05月14日 | 移动技术网IT编程 | 我要评论

k134,十八乘六打一字,拜见岳父大人3下载

内容提要：

建表完整语法规范（create table 表格（字段名1 类型（宽度）约束条件））
mysql数据库数据类型（整型、浮点型、字符类型（char与varchar）、日期类型、枚举与集合）
约束条件（primary key、unique key、not null、foreign key）

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

一、创建表的完整语法

语法：

create table 表名（

字段名1 类型 (宽度) 约束条件，

字段名2 类型 (宽度) 约束条件，

字段名3 类型 (宽度) 约束条件）；

特别注意：

在同一张表中，字段名不能相同
宽度和约束条件为可选项，但是字段名和类型必须有
最后一个字段添加完毕后后面不能加逗号！

补充：1、这里的宽度是对存储数据的一种限制，在不同mysql版本里面可能会出现不同的结果，这里只是不同版本的默认约束条件不一样导致，在最新版本5,7中如果输入数据

宽度超出限制，则会报错。

　　　2、类型与约束条件的区别：类型是限制字段必须以什么样的数据类型存储，二约束条件是在这一类型之上添加一种额外的限制。

二、mysql基本数据类型

整型（int）

分类：tinyint smallint mediumint int bigint
作用：储存年龄、等级、id、各种号码等
类型存储范围：

验证整型字段有无符号及范围
int类型默认是有符号的，也就是说默认支持输入负数到正数
mysql> reate table t1(id tinyint);
mysql> insert into t1 values(128),(-129);
#结果：error 1264 (22003): out of range value for column 'id' at row 1
# 因为tinyint类型最大正整数限制为127，输入128超出范围。报错

create table t2(id tinyint unsigned);  # unsigned表示这里类型设置为无符号（无正负号）
mysql> insert into t2 values (0),(255);
#这里类型约束为无符号，所以输入负数就会报错，输入超过255的数也会报错。tinyint无符号范围是0-255。

以下同理：
create table t3(id int unsigned);
mysql> insert into t3 values (4294967296);
error 1264 (22003): out of range value for column 'id' at row 1
mysql> insert into t3 values (4294967295);
query ok, 1 row affected (0.01 sec)

强调：对于整型来说，数据类型之后的宽度并不是存储限制，而是现实宽度限制，所以在创建表的时候，
如果字段采用的是整型类型，完全无需指定显示宽度，因为默认的显示宽度（11）,一般情况下，就足够显示完整的数据。

浮点型（分为float、double、decimal）

应用场景：身高、体重、薪资
字符限制特点（5,3），前一位表示所有的位数，后一位表示小数个数

区别对比：

# 存储限制
float(255,30)
double(255,30)
decimal(65,30)

# 精确度验证
mysql> create table t9(x float(255,30));
mysql> create table t10(x double(255,30));
mysql> create table t11(x decimal(65,30));

mysql> insert into t9 values(1.111111111111111111111111111111);
mysql> insert into t10 values(1.111111111111111111111111111111);
mysql> insert into t11 values(1.111111111111111111111111111111);
mysql> select * from t9;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t10;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t11;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.00 sec)

从上面的查询创建的表中数据可以看出：
精度对比：decimal的精度最高，其次是double，最后是float，
但是就算是精度最低的float它的精度也有16位，所以一般使用完全能够应付99%的数据场景。

字符类型（char定长与varchar变长）

作用：姓名、地址、描述类信息

理解通过实际使用来描述：

char：
mysql> create table t12(name char(4));
mysql> insert into t12 values ('hello');
error 1406 (22001): data too long for column 'name' at row 1 # 超出限制范围会报错。
mysql> insert into t12 values ('hell');
query ok, 1 row affected (0.01 sec)
mysql> insert into t12 values ('he');
query ok, 1 row affected (0.00 sec)
mysql> select * from t12;
+------+
| name |
+------+
| hell |         # 4个字符正好填满
| he   |         #不够4个字符空格补全
+------+
--------------------------------------------------------------
varchar：
mysql> create table t13(name varchar(4));
mysql> insert into t13 values ('hello');
error 1406 (22001): data too long for column 'name' at row 1         # 超出范围，报错
mysql> insert into t13 values ('hell');
query ok, 1 row affected (0.00 sec)
mysql> insert into t13 values ('he');
query ok, 1 row affected (0.01 sec)
mysql> select * from t13;
+------+
| name |
+------+
| hell |     # 正好4个，填满
| he   |    # 不够4个，按实际宽度占位。（这里看不出来，但是一定要记住，varchar是变长，所以不够4个肯定按实际宽度存储）

+------+
#####
tip：
select char_length(name) from t12
select char_length(name) from t13  # 仍然无法查看到真正的结果

"""首先应该肯定的是在硬盘上存的绝对是真正的数据，但显示的时候mysql会自动将末尾的空格取掉"""
# 如果不想让mysql帮你做自动去除末尾空格的操作，需要再添加一个模式
set global sql_mode="strict_trans_tables,pad_char_to_full_length";
# 退出客户端重新登陆
select char_length(x) from t12; #4
select char_length(y) from t13; #1

# 针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中。但是会在读出结果的时候自动取掉末尾的空格
####

char 与 varchar 的使用区别
- name char(5)
  - 优点：浪费空间，每个name信息都是按照5个字符存，这样如果不够5个的也会用空格补全，造成存储空间浪费
  - 缺点：存取速度很快，因为存储数据的结构固定，所以存取速度快。
  - egon alex lxx jxx txx 存用5个字符5个字符存，取的话也是5个字符为一部分取，准确快捷
- name varchar(5)
  - 缺点：存取速度慢，每个name信息按照可变长的形式去存，同时存的时候要在前面加入该字符长度的报头，一并存入，取的时候按照报头里的长度数据去取数据，比char多了好几步，所以存取数据相比较char会慢些。
  - 优点：节省空间，通过可变长来存入，加上报头数据也不会造成空间的太多浪费
  - 1bytes+egon 1bytes+alex 1bytes+lxx 1bytes+jxx 1bytes+txx

时间类型

分类：
- date：2019-05-13
- time：11:11:11
- datetime：2019-05-13 11:11:11
- year：2019

测试：

mysql> create table stu(id int,name char(16),birth_d date,study_time time,reg_time datetime);  # 创建表
mysql> insert into stu values(1,'jason','2019-05-09','11:11:11','2019-11-11 11:11"11');   #插入表数据
query ok, 1 row affected (0.00 sec)

mysql> select * from stu;   # 显示表内容
+------+-------+------------+------------+---------------------+
| id   | name  | birth_d    | study_time | reg_time            |
+------+-------+------------+------------+---------------------+
|    1 | jason | 2019-05-09 | 11:11:11   | 2019-11-11 11:11:11 |
+------+-------+------------+------------+---------------------+
1 row in set (0.00 sec)

枚举与集合类型：

分类
- 枚举enum 多选一
- 集合set 多选一或多

示例：

enum枚举：
mysql> create table usr(id int,name char(16),gender enum('male','female','others'));
query ok, 0 rows affected (0.05 sec)
mysql> insert into usr values(1001,'jsson','xxx');
error 1265 (01000): data truncated for column 'gender' at row 1
mysql> insert into usr values(1001,'jsson','male');
query ok, 1 row affected (0.01 sec)
mysql> insert into usr values(1002,'egon','female');
query ok, 1 row affected (0.01 sec)
mysql> select * from usr;
+------+-------+--------+
| id   | name  | gender |
+------+-------+--------+
| 1001 | jsson | male   |
| 1002 | egon  | female |
+------+-------+--------+
2 rows in set (0.00 sec)
-----------------------------------------------------------------
set集合：
mysql> create table tea(id int,name char(16),gender enum('male','female','other'),hobby set('read','sleep','dbj'));
query ok, 0 rows affected (0.04 sec)
mysql> insert into tea values(10011,'egon','female','read,dbj');
query ok, 1 row affected (0.01 sec)
mysql> insert into tea values(10012,'alex','male','dbj');
query ok, 1 row affected (0.05 sec)
mysql> select * from tea;
+-------+------+--------+----------+
| id    | name | gender | hobby    |
+-------+------+--------+----------+
| 10011 | egon | female | read,dbj |
| 10012 | alex | male   | dbj      |
+-------+------+--------+----------+
2 rows in set (0.00 sec)

约束条件

类型

primary key (pk)    标识该字段为该表的主键，可以唯一的标识记录
foreign key (fk)    标识该字段为该表的外键
not null            标识该字段不能为空
unique key (uk)     标识该字段的值是唯一的
auto_increment      标识该字段的值自动增长（整数类型，而且为主键）
default             为该字段设置默认值
unsigned            无符号
zerofill            使用0填充not null + defaultnot null:不为空

not null + default

mysql> create table us(id int,name char(16));
query ok, 0 rows affected (0.05 sec)

mysql> insert into us values (1,null);
query ok, 1 row affected (0.01 sec)

mysql> desc us;
+-------+----------+------+-----+---------+-------+
| field | type     | null | key | default | extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | yes  |     | null    |       |
| name  | char(16) | yes  |     | null    |       |
+-------+----------+------+-----+---------+-------+

mysql> delete from us where id=1;   # 先清空存入的null数据
query ok, 1 row affected (0.01 sec)

mysql> alter table us modify name char(16) not null; # 将name输入的数据限制为非空
query ok, 0 rows affected (0.05 sec)
records: 0  duplicates: 0  warnings: 0

mysql> desc us;  # 查看表格式：
+-------+----------+------+-----+---------+-------+
| field | type     | null | key | default | extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | yes  |     | null    |       |
| name  | char(16) | no   |     | null    |       |
+-------+----------+------+-----+---------+-------+

mysql> insert into us values(002,null);  # 此时再输入null就会报错了，因为我们限制了name必须输入未非空的字符。
error 1048 (23000): column 'name' cannot be null
default  默认值
mysql> create table st(id int,name char(16) not null,gender enum('male','female') default 'male');  # 为gender字段设置默认值‘male,前面指定输入字段不包含gender时候，这时候的默认字段名就会生效。
query ok, 0 rows affected (0.03 sec)

mysql> insert into st(id,name) values (1001,'lxx');
query ok, 1 row affected (0.01 sec)

mysql> select * from st;
+------+------+--------+
| id   | name | gender |
+------+------+--------+
| 1001 | lxx  | male   |
+------+------+--------+
1 row in set (0.00 sec)

unique 某个字段对应的值在当前表中是唯一的，不能重复

###单列唯一：
mysql> create table user1(id int unique,name char(16));
query ok, 0 rows affected (0.04 sec)

mysql> insert into user1 values (1,'js'),(1,'eg'); #报错
error 1062 (23000): duplicate entry '1' for key 'id'
mysql> insert into user1 values (1,'js'),(2,'eg'); #不报错
query ok, 2 rows affected (0.00 sec)
records: 2  duplicates: 0  warnings: 0

###联合唯一：多个字段如果同时有相同的输入时，会报错
mysql> create table server(id int,ip char(16),port int,unique(ip,port));
query ok, 0 rows affected (0.04 sec)

mysql> desc server;
+-------+----------+------+-----+---------+-------+
| field | type     | null | key | default | extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | yes  |     | null    |       |
| ip    | char(16) | yes  | mul | null    |       |
| port  | int(11)  | yes  |     | null    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into server values (1,'127.0.0.1',8080);
query ok, 1 row affected (0.01 sec)

mysql> insert into server values (2,'127.0.0.1',8080);   #ip和port都相同，报错
error 1062 (23000): duplicate entry '127.0.0.1-8080' for key 'ip'  

mysql> insert into server values (2,'127.0.0.2',8080); # 仅仅是port相同不会报错
query ok, 1 row affected (0.00 sec)

mysql> insert into server values (3,'127.0.0.1',8081); #仅仅是ip相同也不会报错
query ok, 1 row affected (0.00 sec)

mysql> select * from server;  查看结果：
+------+-----------+------+
| id   | ip        | port |
+------+-----------+------+
|    1 | 127.0.0.1 | 8080 |
|    2 | 127.0.0.2 | 8080 |
|    3 | 127.0.0.1 | 8081 |
+------+-----------+------+
3 rows in set (0.00 sec)

primary key+auto_increment（mysql数据类型及约束条件的重点内容，必须要掌握）

# 单从约束角度来说 primary key 等价于not null + unique
mysql> create table tt1(id int primary key);  # 设置id主键唯一
query ok, 0 rows affected (0.04 sec)

mysql> desc tt1;
+-------+---------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | no   | pri | null    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into tt1 values (1),(1);   # 主键不唯一，报错
error 1062 (23000): duplicate entry '1' for key 'primary'
mysql> insert into tt1 values (1),(2);  #唯一，不报错
query ok, 2 rows affected (0.00 sec)
records: 2  duplicates: 0  warnings: 0
-----------------------------------------------------------------

重点内容：
一、一张表中必须有且只有一个主键，如果你没有设置主键，那么会从上到下搜索直到遇到一个非null且unique的字段自动将其设为主键。
mysql> create table ts1(id int,name char(16),age int not null unique,addr char(16) not null unique)engine=innodb;
query ok, 0 rows affected (0.04 sec)

mysql> desc ts1;
+-------+----------+------+-----+---------+-------+
| field | type     | null | key | default | extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | yes  |     | null    |       |
| name  | char(16) | yes  |     | null    |       |
| age   | int(11)  | no   | pri | null    |       |
| addr  | char(16) | no   | uni | null    |       |
+-------+----------+------+-----+---------+-------+
------------------------------------------------------------------
二、如果表里面没有指定的任何可以设置的主键字段，那么innodb存储引擎会采用自己的默认的一个隐藏的字段作为主键，隐藏意味着你在查询的时候无法根据主键字段加速查询了
------------------------------------------------------------------
三、一张表中通常都应该有一个id字段，并且通常将id字段设为主键
------------------------------------------------------------------
额外知识点：
#联合主键：与联合唯一字段一个道理，即多个字段联合起来作为表的主键，本质上将多个字段总体看为一个主键
mysql> create table ts2(ip char(16),port int,primary key(ip,port));
query ok, 0 rows affected (0.04 sec)

mysql> desc ts2;     # 2个字段合为一个主键。
+-------+----------+------+-----+---------+-------+
| field | type     | null | key | default | extra |
+-------+----------+------+-----+---------+-------+
| ip    | char(16) | no   | pri | null    |       |
| port  | int(11)  | no   | pri | null    |       |
+-------+----------+------+-----+---------+-------+

auto_increment  自增约束
mysql> create table ts3(id int primary key auto_increment,name char(16));
query ok, 0 rows affected (0.05 sec)

mysql> insert into ts3(name) values ('jason'),('alex'),('lxx');    #只输入name字段信息
query ok, 3 rows affected (0.01 sec)
records: 3  duplicates: 0  warnings: 0

mysql> select * from ts3;   # 查看自增结果
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | alex  |
|  3 | lxx   |
+----+-------+
------------------------------------------------------------------
补充：
接着上面的例子，如果我将其中一个字段比如lxx，删除后再添加字段，会出现：
mysql> delete from ts3 where id=3;
query ok, 1 row affected (0.01 sec)

mysql> insert into ts3(name) values ('xxxx');
query ok, 1 row affected (0.01 sec)

mysql> select * from ts3;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | alex  |
|  4 | xxxx  |       # 此时序号居然还是接着自增。
+----+-------+
3 rows in set (0.00 sec)

tip：如果要清楚继续自增，从头开始的话，就必须清空表，才行。
mysql> truncate ts3;  # 将整张表重置，id重新从0开始记录。
query ok, 0 rows affected (0.03 sec)

mysql> insert into ts3(name) values ('eeeee');
query ok, 1 row affected (0.01 sec)

mysql> select * from ts3;
+----+-------+
| id | name  |
+----+-------+
|  1 | eeeee |
+----+-------+

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

严格模式补充

　　我们刚刚在上面设置了char,tinyint,存储数据时超过它们的最大存储长度，发现数据也能正常存储进去，只是mysql帮我们自动截取了最大长度。但在实际情况下，我们应该尽量减少数据库的操作，缓解数据库的压力，让它仅仅只管理数据即可，这样的情况下就需要设置安全模式

show variables like "%mode%";  # 查看数据库配置中变量名包含mode的配置参数
# 修改安全模式
set session # 只在当前操作界面有效
set global  # 全局有效

set global sql_mode ='strict_trans_tables'
# 修改完之后退出当前客户端重新登陆即可

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！点击进行留言回复

新手学习Python2和Python3中print不同的用法

在python2和python3中都提供print()方法来打印信息,但两个版本间的print稍微有差异主要体现在以下几个方面：1.python3中print是... [阅读全文]
Python基于os.environ从windows获取环境变量

安装python之后，我们往往面临这样一个问题，在命令行输入“python”，竟然出错，难道是没有安装成功吗？非也，其实是你的系统环境变量没有设置好。今天，小编... [阅读全文]
keras实现调用自己训练的模型,并去掉全连接层

其实很简单from keras.models import load_modelbase_model = load_model('model_resenet.h... [阅读全文]
python中def是做什么的

python使用def开始函数定义，紧接着是函数名，括号内部为函数的参数，内部为函数的具体功能实现代码，如果想要函数有返回值, 在expressions中的逻... [阅读全文]
Python xlwt模块使用代码实例

简介写入excle文档安装：pip3 install xlwt导入：import xlwtxlrd 模块方法写入案例import xlwt# 创建对象，设置编码... [阅读全文]
Keras之自定义损失(loss)函数用法说明

在keras中可以自定义损失函数，在自定义损失函数的过程中需要注意的一点是，损失函数的参数形式，这一点在keras中是固定的，须如下形式：def my_loss... [阅读全文]
Python xlrd模块导入过程及常用操作

简介读取excle文档，支持xls，xlsx格式安装：pip3 install xlrd导入：import xlrdxlrd 模块方法读取excelfile =... [阅读全文]
keras打印loss对权重的导数方式

notes怀疑模型梯度爆炸，想打印模型 loss 对各权重的导数看看。如果如果fit来训练的话，可以用keras.callbacks.tensorboard实现... [阅读全文]
keras 使用Lambda 快速新建层添加多个参数操作

keras许多简单操作，都需要新建一个层，使用lambda可以很好完成需求。# 额外参数def normal_reshape(x, shape): return... [阅读全文]
JAVA及PYTHON质数计算代码对比解析

java 实现class primenumber{public static void main(string[] args) {long start=syst... [阅读全文]

网友评论


验证码：

MySQL数据类型与操作

2019年05月14日 | 移动技术网IT编程 | 我要评论

建表完整语法规范（create table 表格（字段名1 类型 （宽度） 约束条件））

mysql数据库数据类型（整型、浮点型、字符类型（char与varchar）、日期类型、枚举与集合）

约束条件（primary key、unique key、not null、foreign key）