数据库之表的字段类型

字段类型
1.整型
SMALLINT TINYINT INT BIGINT

TINYINT 
默认是否有符号 默认是带有符号的(-128,127)
超出限制会如何 超出之后只会存最大值或者最小值


create table t6(id TINYINT);


not null 不能为空
unsigned 无正负符号 # 加上unsigned没有负数,区间从零到最大字符数的最大值
zerofill 0填充多余的位数

int的存储宽度是4个Bytes,即32个bit,即2**32

无符号最大值为:4294967296-1

有符号最大值:2147483648-1

有符号和无符号的最大数字需要的显示宽度均为10,而针对有符号的最小值则需要11位才能显示完全,所以int类型默认的显示宽度为11是非常合理的

最后:整形类型,其实没有必要指定显示宽度,使用默认的就ok

ps:
mysql> create table t15(x int(8) unsigned);
Query OK, 0 rows affected (0.58 sec)

mysql> insert into t15 values(34578352365896);
Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> select * from t15;
+------------+
| x |
+------------+
| 4294967295 |
+------------+

char后面的数字是用来限制存储数据的长度的

特例:只有整型后面的数字不是用来限制存储数据的长度 而是用来控制展示的数据的位数
int(8) 够/超8位有几位存几位,不够8位空格填充

修改约束条件 不够8位的情况下 用0填充


强调:**对于整型来说,数据类型后的宽度并不是存储限制,
而是显示限制,所以在创建表时,
如果字段采用的是整型类型,完全无需指定显示宽度, 默认的显示宽度,足够显示完整当初存放的数据

只要是整型 都不需要指定宽度 因为有默认的宽度 足够显示对应的数据

### 严格模式补充

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

模糊匹配
like 
%匹配任意多个字符
_匹配任意一个字符

set session 临时有效 只在你当前操作的窗口有效
set global 全局有效 终生有效
set global sql_mode = 'STRICT_TRANS_TABLES';
设置完之后 你只需要重新退出客户端再次进入即可


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

set global sql_mode ='STRICT_TRANS_TABLES' #设置严格模式语句
# 修改完之后退出当前客户端重新登陆即可
'''
ps:
mysql> create database db2;
Query OK, 1 row affected (0.05 sec)

mysql> use db2;
Database changed
mysql> create table t1(name char(4),id int);
Query OK, 0 rows affected (0.27 sec)

mysql> insert into t1 values('li',1);
Query OK, 1 row affected (0.06 sec)

mysql> select * from t1;
+------+------+
| name | id |
+------+------+
| li | 1 |
+------+------+
1 row in set (0.00 sec)

mysql> insert into t1 values('zhang',2);
ERROR 1406 (22001): Data too long for column 'name' at row 1

以上事例:在我们设置严格模式之后,char(4),在向表中插入字符‘zhang’的时候,字符个数超过了限制,就会报错;

2.浮点型
float(255,30) 总共255位 小数部分占30位
double(255,30) 总共255位 小数部分占30位
decimal(65,30) 总共65位 小数部分占30位


create table t2(x float(255,30));
create table t3(x double(255,30));
create table t4(x decimal(65,30));
desc t2;
desc t3;
desc t4;
insert into t2 values(1.11111111111111111111111111111111);
insert into t3 values(1.11111111111111111111111111111111);
insert into t4 values(1.11111111111111111111111111111111);
select * from t2;
select * from t3;
select * from t4;

 

mysql> create table t2(x float(255,30));
Query OK, 0 rows affected (0.27 sec)

mysql> create table t3(x double(255,30));
Query OK, 0 rows affected (0.42 sec)

mysql> create table t4(x decimal(65,30));
Query OK, 0 rows affected (0.29 sec)

mysql> desc t2;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| x | float(255,30) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.03 sec)

mysql> desc t3;
+-------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| x | double(255,30) | YES | | NULL | |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.03 sec)

mysql> desc t4;
+-------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| x | decimal(65,30) | YES | | NULL | |
+-------+----------------+------+-----+---------+-------+

mysql> insert into t2 values(1.11111111111111111111111111111111);
Query OK, 1 row affected (0.06 sec)

mysql> insert into t3 values(1.11111111111111111111111111111111);
Query OK, 1 row affected (0.03 sec)

mysql> insert into t4 values(1.11111111111111111111111111111111);
Query OK, 1 row affected, 1 warning (0.03 sec)

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

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

mysql> select * from t4;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
精确度
float < double < decimal


3. 字符类型(******)

* 分类
* char(定长)
* varchar(变长)
* 作用:姓名,地址,描述类信息

create table t5(name char(4));
create table t6(name varchar(4));
insert into t5 values('he');
insert into t6 values('he');

select * from t5;
select * from t6;

select char_length(name) from t5;
select char_length(name) from t6; # 仍然无法查看到真正的结果

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


ps:
mysql> set global sql_mode='strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%mode%';
# 退出客户端重新登陆
+----------------------------+---------------------------------------------+
| Variable_name | Value |
+----------------------------+---------------------------------------------+
| binlogging_impossible_mode | IGNORE_ERROR |
| block_encryption_mode | aes-128-ecb |
| gtid_mode | OFF |
| innodb_autoinc_lock_mode | 1 |
| innodb_strict_mode | OFF |
| pseudo_slave_mode | OFF |
| slave_exec_mode | STRICT |
| sql_mode | STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH |
+----------------------------+---------------------------------------------+
select char_length(name) from t5;
select char_length(name) from t6;

mysql> use db2;
Database changed
mysql> select char_length(name) from t5;
+-------------------+
| char_length(name) |
+-------------------+
| 4 |
+-------------------+
1 row in set (0.00 sec)

mysql> select char_length(name) from t6;
+-------------------+
| char_length(name) |
+-------------------+
| 2 |
+-------------------+

char与varchar的区别
char定长
1.浪费空间
2.存取速度快


varchar变长
1.节省空间
2.存取速度慢(较于char比较慢)
存的时候 需要给数据讲一个记录长度的报头
取的时候 需要先读取报头才能读取真实数据
1bytes+zhang 1bytes+wang 1bytes+lxx 1bytes+jxx 1bytes+txx 


char(4) varchar(4)
取的时候方便 取的时候比较繁琐了 无法知道数据到底多长
直接按固定的长度取即可


#### 时间类型

* 分类

* date:2019-05-01
* time:11:11:11
* Datetime: 2019-01-02 11:11:11
* Year:2019

* 测试

```mysql
create table student(
id int,
name char(16),
born_year year,
birth date,
study_time time,
reg_time datetime
);
insert into student values(1,'zhang','2019','2019-05-09','11:11:00','2019-11-11 11:11:11');


4.枚举与集合类型
枚举(enum) 限制某个字段能够存储的数据内容 枚举enum 多选一
集合(set) 限制某个字段能够存储的数据内容 集合set 多选多

mysql>
mysql> create table user(id int,name char(10),gender enum('male','female','others'));
Query OK, 0 rows affected (0.33 sec)

mysql> desc user;
+--------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| gender | enum('male','female','others') | YES | | NULL | |
+--------+--------------------------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> insert into user values(1,'zhang','male');
Query OK, 1 row affected (0.06 sec)

mysql> insert into user values(1,'zhang','buming');
#没按限定的内容输入,直接报错。
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> select * from user;
+------+------------+--------+
| id | name | gender |
+------+------------+--------+
| 1 | zhang | male |
+------+------------+--------+


create table teacher(
id int,
name char(10),
gender enum('male','female','other'),
hobby set('read','swim','dbj','walk')
);

insert into teacher values(1,'zhang','male','swim,walk');

select * from teacher;

mysql> create table teacher(
-> id int,
-> name char(10),
-> gender enum('male','female','other'),
-> hobby set('read','swim','dbj','walk')
-> );
Query OK, 0 rows affected (0.29 sec)

mysql> insert into teacher values(1,'zhang','male','swim,walk');
Query OK, 1 row affected (0.10 sec)

mysql> select * from teacher;
+------+------------+--------+-----------+
| id | name | gender | hobby |
+------+------------+--------+-----------+
| 1 | zhang | male | swim,walk |
+------+------------+--------+-----------+
1 row in set (0.00 sec)

 

posted on 2019-08-20 14:50  jueyuanfengsheng  阅读(1486)  评论(0编辑  收藏  举报