day-3-3-3

最新推荐文章于 2024-01-06 16:55:44 发布

I＇m昊帝

最新推荐文章于 2024-01-06 16:55:44 发布

阅读量275

点赞数

分类专栏： java实训文章标签：数据库 java 开发语言

本文链接：https://blog.csdn.net/haodi12138/article/details/127000880

版权

java实训专栏收录该内容

51 篇文章 0 订阅

订阅专栏

sql数据类型

存储数据是处理数据的第一步。只有正确地把数据存储起来，才能进行有效的处理和分析。否则只能是一团乱麻，无从下手。那么，怎样才能把用户各种经营相关的、纷繁复杂的数据，有序、高效地存储起来呢？在MySQL中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W2iZ61iQ-1663855081109)(E:\java\blog\week12\img\3.png)]

要先创建一个数据库，而不是直接创建数据表呢？

因为从系统架构的层次上看，MySQL数据库系统从大到小依次是【数据库服务器、数据库、数据表、数据表的行与列】。

数据类型主要给列上添加域完整性约束。开发者设置列的数据类型后，DBMS会对添加的数据进行合法性验证，如果不合法则报错，拒绝添加

数值型

5种整型 tinyint、smallint、mediumint、int和bigint，主要区别就是取值范围不同，还可以在类型前添加一个限制词unsigned，不允许添加负数
3种浮点型：不能精确存放float和double，可以精确存放decimal和numeric
- tinyint 1B，取值范围-128到127
- smallint 2B，取值范围为-32768到32767
- mediumint 3B，取值范围为-8388608到8388607
- int或者Integer 4B，取值范围为2e9
- bigint 8B，取值范围为9e18 float 4B，单精度浮点型，取值范围3.4e38
- double 8B，双精度浮点型，取值范围1.7e308，总位数不超过15位
- decimal和numeric 采用定点存放浮点数，具体字节数取决于定义时设置的参数

unsigned设置列为无符号型，只能存放大于等于0的数据，没有负数。当使用无符号类型时取值范围由于没有负数部分，从而导致上限扩大一倍

create table t3(id int unsigned);

mysql> insert into t3 value(-10); -- 不允许存储负数
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> insert into t3 value(255); -- 存储数据的上线扩大一倍
Query OK, 1 row affected (0.01 sec)

可以在类型名后添加括号，其中包含一个正整数，例如int(5)，这里的含义并不是要求只能存放5位长度的整数；含义是当进行查询时自动使用空格填充到5个长，如果真实数据长度大于5，则按实际输出

mysql> create table t4(id int(2));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t4 values(99999);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t4 values(9);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t4;
+-------+
| id |
+-------+
| 99999 |
| 9 |
+-------+
2 rows in set (0.00 sec)

decimal和numeric作为字符串存储浮点数，可以实现浮点数的精确存储，并不是float和double种使用二进制浮点数存储。使用方法numeric(总位宽，小数位数)，小数位数必须小于等于总位宽；小数位数最大值30，总位宽最大值为65，注意可能存储的数据会超出范围，其中的符号位和小数点不占位宽，

mysql> create table t5(id numeric(5,3));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t5 values(99.999);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t5 values(-99.999); -- 位宽统计中符号位不算
Query OK, 1 row affected (0.01 sec)
mysql> insert into t5 values(100); -- 整数超出范围报错
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t5 values(9.1235); -- 小数位数多余则自动四舍五入
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into t5 values(9.1234);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t5;
+---------+
| id |
+---------+
| 99.999 |
| -99.999 |
| 9.124 |
| 9.123 |
+---------+
4 rows in set (0.00 sec)

int和numeric

int类型不能保存小数位，存储小数时会自动进行四舍五入

mysql> create table t6(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t6 values(99.99);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t6;
+------+
| id |
+------+
| 100 |
+------+
1 row in set (0.00 sec)

numeric可以保存小数位，如果小数位数为0，可以模拟得到int类型的存储效果。采用的实际存储方式为字符串。查询效率远低于int

mysql> create table t7(id numeric);
Query OK, 0 rows affected (0.03 sec)
mysql> desc t7;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | decimal(10,0) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

数值列的扩展属性

auto_increment一般用于主键，可以实现该列的自动生成连续整数值

mysql> create table t8(id int auto_increment,name varchar(20));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto
column and it must be defined as a key
mysql> create table t8(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t8 values(null,'yan1');-- 插入数据时不指定插入的值或者设置插入值
为null则会自动添加一个自增值
Query OK, 1 row affected (0.01 sec)
mysql> insert into t8 values(99,'yan1'); -- 如果插入数据时不指定对应的值，则自增长
max(id)+1;如果指定对应的值，则自增效果失效
Query OK, 1 row affected (0.01 sec)
mysql> insert into t8(name) values('yan1');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t8;
+-----+------+
| id | name |
+-----+------+
| 1 | yan1 |
| 99 | yan1 |
| 100 | yan1 |
+-----+------+
3 rows in set (0.00 sec)

在使用AUTO_INCREMENT时，应注意以下几点：

1、AUTO_INCREMENT是数据列的一种属性，只适用于整数类型数据列。

2、设置AUTO_INCREMENT属性的数据列应该是一个正数序列，所以应该把该数据列声明为 UNSIGNED，这样序列的编号个可增加一倍。

3、AUTO_INCREMENT数据列必须有唯一索引，以避免序号重复(即是主键或者主键的一部分)。 AUTO_INCREMENT数据列必须具备NOT NULL属性。

4、AUTO_INCREMENT数据列序号的最大值受该列的数据类型约束，如TINYINT数据列的最大编号是127,如加上UNSIGNED，则最大为255。一旦达到上限，AUTO_INCREMENT就会失效。

当auto_incement达到上限后，再次申请下一个id时，得到的值保持不变。主键值重复报错

5、当进行全表删除时，MySQL AUTO_INCREMENT会从1重新开始编号。这是因为进行全表操作时，MySQL实际是做了这样的优化操作：先把数据表里的所有数据和索引删除，然后重建数据表。

如果想删除所有的数据行又想保留序列编号信息，可这样用一个带where的delete命令以抑制 MySQL的优化：delete from table_name where 1;

可用last_insert_id()获取刚刚自增过的值。

unsigned禁用负值
zerofill如果查询显示时，实际数据小于指定位宽，则自动添加0值

mysql> create table t9(id int(5) zerofill);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t9 values(12);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t9;
+-------+
| id |
+-------+
| 00012 |
+-------+
1 row in set (0.00 sec)
mysql> insert into t9 values(123456);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t9;
+--------+
| id |
+--------+
| 00012 |
| 123456 |
+--------+
2 rows in set (0.00 sec)

default用于设置默认值

mysql> create table t10(id int default 0,name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t10 values(11,'yan1');-- 指定对应的数据，则default无效
Query OK, 1 row affected (0.01 sec)
mysql> insert into t10 values(null,'yan1'); -- 指定对应的数据，则default无效，即
使设置的值为null
Query OK, 1 row affected (0.01 sec)
mysql> insert into t10(name) values('yan1'); -- 只有不针对这个列进行数据插入时
default才能生效
Query OK, 1 row affected (0.00 sec)
mysql> select * from t10;
+------+------+
| id | name |
+------+------+
| 11 | yan1 |
| NULL | yan1 |
| 0 | yan1 |
+------+------+
3 rows in set (0.00 sec)

null是什么意思

null的意思为未知的数据，它既不是空字符串，不是任何一个具体的值；不能把任何值与一个null 值进行比较。因为null的意思是不确定的值。真正的null值比较必须使用特殊的运算符is null或者is not null
mysql> select null=null; -- =表示等值比较
+-----------+
| null=null |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
mysql> select null!=null; -- 比较不相等
+------------+
| null!=null |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql> select null is null;
+--------------+
| null is null |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> select null is not null;
+------------------+
| null is not null |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

字符串类型

char(n)定长字符串，n值用于表示所允许的最大长度，取值范围0-255

长度限制不区分中英文，表示是允许最多存储多少个字符
使用length函数获取的是字节数，不是字符数。在utf-8编码种一个汉字占用3个字节

mysql> create table c2(id char(5));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into c2 values("yan");
Query OK, 1 row affected (0.01 sec)
mysql> insert into c2 values("中国人民好");
Query OK, 1 row affected (0.01 sec)
mysql> insert into c2 values("中国人民好1");
ERROR 1406 (22001): Data too long for column 'id' at row 1

varchar(n)可变长度的字符串，n值取值范围为0-65535

实际存储多少个字符就占多少个位置，每个列所占用的长度根据具体内容变化

create table t1(id varchar(20));

char和varchar

char属于定长，varchar属于变长。选择最基本的依据就是定长使用char，变长使用varchar
char允许的最大长度为255，varchar允许的最大长度为65535
如果存储的字符串长度变化不大，推荐优先考虑使用char，例如针对口令进行md5加密，固定长度的行不容易产生碎片；对于长度很短的列，char的性能优于varchar，是因为varchar需要引入额外一个或者两个字节存储字符串长度
char在计算种会自动去除空格；varchar不会去掉空格，但是进行字符串比较时，会去除空格后进行比较；在插入数据时系统会自动去除字符串默认多余的空格

大对象类型Lob

大对象类型可以分为2大类：文本类型和二进制类型

BLOB是一个二进制大对象，可以容纳可变长度的数据，其中分为tinyblob、blob、 mediumblob、longblob。一般用于存储二进制数据，例如声音、图像、视频等
Text是一个文本大对象，可以容纳可变长度的数据，其中分为tinytext、text、mediumtext、 longtext，一般用于存储文本类型数据，例如小说、商品简介

mysql> create table c9(id text);
Query OK, 0 rows affected (0.02 sec)
mysql> create table c8(id blob);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into c9 values("Abc");
Query OK, 1 row affected (0.01 sec)
mysql> insert into c8 values("Abc");
Query OK, 1 row affected (0.01 sec)
mysql> select * from c9 where id='abc';
+------+
| id |
+------+
| Abc |
+------+
1 row in set (0.00 sec)
mysql> select * from c8 where id='abc';
Empty set (0.00 sec)

两者之间的区别：

blob是二进制类型，可以容纳可变长度的数据，区分大小写；text是一个不区分大小写的blob

tinytext 0-255 短文本字符串

text 0-65535 长文本字符串

mediumtext 16M 中等长度的长文本字符串

longtext 4G 极大文本数据

tinyblob 0-255 不超过255个长的二进制串

blob 0-65535 二进制类型的长文本字符串

mediumblob 16M 中等长度的二进制长文本字符串

longblob 4G 极大二进制数据

日期时间类型

create table dt1(id date);
insert into dt1 values('1989-2-3');

特殊的时间是时间戳类型

create table dt2(id timestamp default current_timestamp,name varchar(20));
mysql> insert into dt3(name) values('yan1');
Query OK, 1 row affected (0.01 sec)
mysql> select * from dt3;
+---------------------+------+
| id | name |
+---------------------+------+
| 2022-04-17 15:07:33 | yan1 |
+---------------------+------+
1 row in set (0.00 sec)

时间戳字段定义主要影响两类操作：

插入记录时，时间戳字段包含DEFAULT CURRENT_TIMESTAMP，如插入记录时未指定具体时间数据则将该时间戳字段值设置为当前时间
更新记录时，时间戳字段包含ON UPDATE CURRENT_TIMESTAMP，如更新记录时未指定具体时间数据则将该时间戳字段值设置为当前时间
当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，表示该字段在插入或更新时未指定值，则被赋值为当前时间

系统函数now()可以获取系统的当前时间

select now();

date 日期类型，有效范围为1000-9999年，不包含时分秒 3B，yyyy-MM-dd

time 时间类型，有效范围为-838到838小时范围内，一般用于表示时间值或者持续时间。不包含年月日 3B，HH:mm:ss

year 年份类型，有效范围1901到2155年 1B，yyyy

datetime 日期时间类型，其中包含日期和时间，可以精确到秒，有效范围为1000到9999年。具体存储时和时区无关。从MySQL5.6.4之后可以包含秒后的小数部分，精度最高为微妙(6位) 非小数部分5B+小数部分0-3B，如果有小数部分则会随着小数位数变化，yyyy-MMdd HH:mm:ss

timestamp 时间戳类型，可以精确到秒，有效分为是1970到2038 年，同时支持自动存储修改的时间，存储有时区数据，实际存储毫秒数。在MySQL 5.6.4及之后版本，可以将时间戳类型数据最高精确微秒，timestamp(N)N取值范围为0-6 非小数部分4B+小数部分0-3B，默认显示格式YYYY-MM-dd HH:mm:ss

集合和枚举类型

set集合类型，可以设置多个集合成员，允许从多个集合成员种选择多个数据进行插入

create table set1(id int,hobby set('抽烟','喝酒','烫头'));
insert into set1 values(1,'烫头,抽烟'); -- 没问题
insert into set1 values(1,'烫头,抽烟,扣脚'); -- 报错，因为其中一个值不在set定义种
insert into set1 values(1,'抽烟,抽烟');-- 实际上只插入一个值，也就是会自动去除重复值

enum枚举类型，可以设置允许选用的多个值，列只能赋予某个枚举成员值

create table enum1(id int,sex enum('男','女','不确定'));
insert into enum1 values(1,'男');
insert into enum1 values(2,'男,女'); -- 报错

位类型

bit(1)表示可以存储一个二进制位，只能存储0或者1，其中n的最大上限值为64

bit(8)表示可以存储8个二进制位的值，取值范围为0到255

总结

定义数据类型就是定义列，数据类型决定数据的特性【域完整性】
数据类型主要分为字符串类型、定点数和浮点数类型、日期类型、大对象类型【慎用】
数据类型在不同的存储引擎上表现不同
数据所采用的数据类型是根据业务需求和对应的数据类型特征进行选择，不建议使用过于复杂的数据类型

完整性约束

DBMS为了保证存储的数据都是完整有效的，避免存放垃圾数据，所以提供针对插入的数据进行检查。

开发人员指定对应的规则，由DBMS负责检查，如果试图插入不合法的数据，则会自动报错，拒绝插入
实际有6种约束，可以分为3大类完整性约束。有些老外的书种提出的是4种【域完整性】
- 实体完整性，由主键约束实现
- 参照完整性，由外键约束实现
- 用户自定义完整性，由其他4种约束实现
实际开发中除了主键约束外，其它约束一概不添加。其他的数据检查一般依赖应用程序实现，为了代码测试方便

1、非空约束，指定列值不允许为空

create table t1(id int not null); --不允许id列值为null
create table t2(id int null); -- 允许id列值为null
create table 3(id int);-- 允许id列值为null

2、默认约束default

一般默认约束经常和非空约束一起使用，当不插入数据时，默认值生效

create table t1(id int not null default 0);

3、主键约束，又叫做实体完整性，不允许数据重复存储

主键可以唯一标识一行数据
主键约束含义是非空、唯一

create table t1(id int primary key,....);
create table t2(
id int not null,...
primary key(id) -- 注意这里的主键约束允许使用复合主键,多个列构成的主键
);

使用InnoDB存储引擎时，如果数据表没有设置主键，那么Innodb会给该表设置一个不可见，长度为6字节的默认主键 row_id。Innodb维护了一个全局的dict_sys.row_id值，这个值，被所有无主键的数据表共同使用，每个无主键的数据表，插入一行数据，都会是当前的dict_sys.row_id的值增加1

实row_id的值在存储时，数据长度为8字节，只不过Innodb只使用后6个字节。那么row_id的值，写到数据表中时就有一下两个特点：

1.row_id写入表中的值范围，是从0-2^48-1。
2.当row_id的值为2^48时，再进行数据插入，那么row_id的后6个字节的值，就全部为0了。

也就是说，当row_id的值到了2^48次方-1后，再次插入数据，下一个值就是0，然后开始循环。不过和自定义主键不同的是，row_id标识的主键，没有唯一性约束，当插入数据的row_id值，在表中已经存在的话，那么写入的数据会"悄无声息"覆盖已存在的数据。

表尽可能都要设置主键，主键尽量使用bigint类型，21亿的上限还是有可能达到的，比如魔兽，虽然说 row_id上限高达281万亿，但是覆盖数据显然是不可接受的。

4、唯一约束 unique

不允许添加唯一性约束的列出现重复值
可以没有null值约束，而且也不能针对null进行唯一性判定

create table t1(id int unique,name varchar(20));
insert into t1 values(1,'zhangsan');
insert into t1(name) values('zhao4');-- id为null
insert into t1(name) values('zhao4');-- id为null，两次null并不会报错
insert into t1 values(1,'li4'); -- 报错

5、外键约束，又叫做参照完整性

id值，在表中已经存在的话，那么写入的数据会"悄无声息"覆盖已存在的数据。

4、唯一约束 unique

不允许添加唯一性约束的列出现重复值
可以没有null值约束，而且也不能针对null进行唯一性判定

create table t1(id int unique,name varchar(20));
insert into t1 values(1,'zhangsan');
insert into t1(name) values('zhao4');-- id为null
insert into t1(name) values('zhao4');-- id为null，两次null并不会报错
insert into t1 values(1,'li4'); -- 报错

5、外键约束，又叫做参照完整性

6、检查约束