数据类型
1)数据类型
- 整数型
- 浮点型
- 定点数
- 字符串
- 日期类型
- 修饰符
说明;
数据库的数据类型: 指每一个字段能够接受的 数据的存储格式,也就是 数据类型。
建立一个数据表时,需要制定 表中各个字段的 属性。
选择正确的数据类型对于获得高性能至关重要,三大原则:
\1. 更小的通常更好,尽量使用可正确存储数据的最小数据类型
\2. 简单就好,简单数据类型的操作通常需要更少的CPU周期
\3. 尽量避免NULL,包含为NULL的列,对MySQL更难优化
范例
Field 字段名称
Type 字段类型
Null 是否允许为空值
Key 主键约束
Default 默认值
Extra 索引
mysql> desc test;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(5) | YES | | NULL | |
| age | char(3) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
+--------+------------+------+-----+---------+-------+
1、整数类型
-
tinyint(m) 占1个字节 范围(-128~127) (m) 代表宽度
-
smallint(m) 2个字节 范围(-32768~32767) (m) 代表宽度
-
mediumint(m) 3个字节 范围(-8388608~8388607) (m) 代表宽度
-
int(m) 4个字节 范围(-2147483648~2147483647) (m) 代表宽度
-
bigint(m) 8个字节 范围(±9.22*10的18次方)(m) 代表宽度
常用类型 int
范例
#整数类型测试:tinyint,int 作用:用于存储用户的年龄、游戏的Level、经验值等。
mysql> create table test1( tinyint_test tinyint , int_test int );
mysql> desc test1;
+--------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| tinyint_test | tinyint(4) | YES | | NULL | | ## 不指定宽度的话,系统会自动设置一个合理的值,确保 int 能够显示完整。
| int_test | int(11) | YES | | NULL | |
+--------------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into test1 values (111,111);
Query OK, 1 row affected (0.09 sec)
mysql> insert into test1 (tinyint_test) values(128);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1
mysql> insert into test1(int_test) values(2147483647);
Query OK, 1 row affected (0.05 sec)
mysql> insert into test1(int_test) values(2147483648);
ERROR 1264 (22003): Out of range value for column 'int_test' at row 1
//测试结果,默认有符号,超过存储范围出错。
#====无符号整形测试 unsigned========
mysql> create table test2(
-> tinyint_test tinyint unsigned, //约束条件unsigned限定只能存正值(无符号)
-> int_test int unsigned
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc test2;
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| tinyint_test | tinyint(3) unsigned | YES | | NULL | |
| int_test | int(10) unsigned | YES | | NULL | |
+--------------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into test2(tinyint_test) values(255);
Query OK, 1 row affected (0.06 sec)
mysql> insert into test2(int_test) values(2147483648);
Query OK, 1 row affected (1.87 sec)
mysql> insert into test2 values(-20,-20);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1
整形的宽度仅为显示宽度,不是限制。因此建议整形无须指定宽度,由系统自行调整。
2、浮点类型
- float(m,d) 单精度浮点型 8位精度(4字节) m总个数,d小数位
- double(m,d) 双精度浮点型16位精度(8字节) m总个数,d小数位
- deecimal(m,d) 定点数
1、float 浮点数
说明:
设一个字段定义为float(6,3),如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以
实际为准,即6位用于存储用户的身高、体重、薪水等,不具备超高精度要求的数据
浮点数是用来表示实数的一种方法,它用 M(尾数) * B( 基数)的E(指数)次方来表示实数,相对于定点数来说,
在长度一定的情况下,具有表示数据范围大的特点。
但同时也存在误差问题。如果希望保证值比较准确,推荐使用定点数数据类型。
范例:
#优先满足小数位,少补,多 四舍五入
mysql> create table test4(float_test float(5,2)); //一共5位,小数占2位 000.00 -- 999.99
mysql> desc test4;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| float_test | float(5,2) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into test4 values (10.2 ), (70.243), (70.246);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test4;
+------------+
| float_test |
+------------+
| 10.20 |
| 70.24 |
| 70.25 |
+------------+
3 rows in set (0.00 sec)
mysql> insert into test4 values (1111.20); ### 优先满足小数位,少补,多 四舍五入
ERROR 1264 (22003): Out of range value for column 'float_test' at row 1
2、decimal(m,d) 定点数
说明:
定点数在MySQL内部以字符串形式存储,比浮点数更精确,适合用来表示货币等精度高的数据。
M的取值范围为1~65,取0时会被设为默认值,超出范围会报错。
D的取值范围为0~30,而且必须<=M,超出范围会报错。
范例:
#定点数decimal测试:
mysql> create table test5(decimal_test decimal(5,2));
mysql> insert into test5 values (70.245);
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> show warnings; ##显示系统的警告信息。
+-------+------+---------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------+
| Note | 1265 | Data truncated for column 'decimal_test' at row 1 |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from test5;
+-------+
| num |
+-------+
| 70.25 |
| 70.25 |
+-------+
总结;
定点数 decimal (M,D) :不存在精度偏差。 取值 65 小数最大支持 30位,
浮点数:在一定范围内能保证精确,超出的范围,可能会出现存取的偏差。
对小数部分的处理机制一样: 优先处理小数部分内容(不够小数位,则补0 ,超出的小数位,则按照四舍五入自动截断。
3、字符串类型
- char(n) 固定长度,最多255个字符,注意不是字节
- varchar(n) 可变长度,最多65535个字符
- enum() 枚举类型
- set() 集合类型
1、char、varchar 类型
说明:
CHAR 列的长度固定值,为创建表时声明的长度: 0 ~ 255
VARCHAR 列中的值为可变长字符串,长度: 0 ~ 65535varchar 可以使用 多个 字节用于 记录 长度信息,所以当存储长度超过 255 时, 会使用 2 个字节来记录总长度信息,因此可以支持高达 65535 的长度 。
作用:用于存储用户的姓名、爱好、发布的文章等
**注:**在检索的时候,CHAR列删除了尾部的空格,而VARCHAR则保留这些空格
范例:
#创建一个表姓名允许多个字符串,电话只允许写11位字符串
mysql> create table test1 (姓名 varchar(5),电话 char(11));
mysql> desc test1;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| 姓名 | varchar(5) | YES | | NULL | |
| 电话 | char(11) | YES | | NULL | |
+--------+------------+------+-----+---------+-------+
2、enum、set类型
说明:
enum 单选 只能在给定的范围内选一个值,如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值(爱好1,爱好2,爱好3…)
范例:
#测试
表 school.student3
姓名 name varchar(50)
性别 sex enum('m','f')
爱好 hobby set('music','book','game','disc')
mysql> use school
mysql> create table student3(
-> name varchar(50),
-> sex enum('m','f'),
-> hobby set('music','book','game','disc')
-> );
Query OK, 0 rows affected (0.31 sec)
mysql> desc student3;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra|
+-------+-----------------------------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
| sex | enum('m','f') | YES | | NULL | |
| hobby | set('music','book','game','disc') | YES | | NULL | |
+-------+-----------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into student3 values ('tom','m','book,game');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student3;
+------+------+-----------+
| name | sex | hobby |
+------+------+-----------+
| tom | boy | book,game |
+------+------+-----------+
1 row in set (0.00 sec)
#错误范例
mysql> insert into student3 values ('jack','m','film');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
4、日期时间型
- date 年月日 ‘2008-12-2’
- datetime 年月日时分秒’2008-12-2 22:06:44’
- time 时分秒
- year 年份
- timestamp 自动存储记录修改时间
说明:
timestamp字段里的时间数据会随其他字段修改的时候自动刷新,这个数据类型的字段可以存放这条记
录最后被修改的时间
作用:用于存储用户的注册时间,文章的发布时间,文章的更新时间,员工的入职时间等
范例:
#时间和日期类型测试:year、date、time、datetime、timestamp
mysql> create table test_time(d date,t time,dt datetime);
Query OK, 0 rows affected (0.03 sec)
mysql> desc test_time;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
#调用 now() 函数,取得当前时间,并插入列值。
mysql> insert into test_time values(now(),now(),now());
mysql> select * from test_time;
+------------+----------+------------------------+
| d | t | dt |
+------------+----------+------------------------+
| 2013-12-18 | 00:06:10 | 2013-12-18 00:06:10 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
#默认设置当前时间
mysql> create table t(id timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> desc t;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)
mysql> insert into t values(null); "null" null
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+--------------------------+
| id |
+--------------------------+
| 2013-12-18 00:08:41 |
+--------------------------+
1 row in set (0.00 sec)
#使用 当前 时间 作为 字段 默认值, 注意,只有 timestamp 类型支持设置默认
ts_time timestamp NOT NULL DEFAULT NOW();
ts_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP(); ##默认值设置为 当前时间 是业务上一种很常见的用法。
#常见的日期获取函数
MySQL中有一些日期函数可供我们使用,我们可以使用 ” select 函数名() ; ” 的 sql 查看它们的返回值。
同时也可以使用 “select 自定义函数名();”,调用我们自己定义的函数。
Eample:
select current_timestamp();
select current_date();
select current_time();
select now();
5、约束条件
- 非空约束 :null(默认允许为空值)、not null(表示这个字段不能是空,空格不算空值)
- 唯一约束:unique
- 主键约束:primary key(字段)
- 联合主键:primary key(字段1,字段2)
- 外键约束:foreign key
1、非空约束 null、not null
说明:
null:表示可以为空值
not null:表示这个字段不能是空值
表中,所有记录里面,尽量不要出现 “空值” ,大量空值会影响表的 读取和索引效率。
范例
#非空值测试
mysql> create table test_1(
-> id int not null,
-> name varchar(5));
mysql> desc test_1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(5) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
mysql> insert into test_1 values (1,'张三');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_1;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
+----+--------+
1 row in set (0.00 sec)
#错误范例
mysql> insert into test_1(name) values ('李四');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
2、唯一约束 unique key
说明:
添加唯一约束,则该列的值不能出现重复值,而且唯一键的列,允许有 空值 , 主键不允许出现空值。
允许出现多个唯一键。
范例:
ID 用户名 邮箱
主键 唯一键 唯一键
#唯一约束测试
mysql> create table 唯一约束(id int unique key,name varchar(10));
mysql> desc 唯一约束;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Nusll | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> insert into 唯一约束 values (1,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> insert into 唯一约束 values (1,'王五');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
#删除唯一约束
写法:alter table 表格名称 drop index 字段名
mysql> alter table 唯一约束 drop index name;
3、主键约束 primary key
说明:
主键约束相当于 (唯一约束+非空约束)
主键可以保证记录的唯一,主键域自动为NOT NULL(非空);
主键常常与外键构成参照完整性约束,防止出现数据不一致。
数据库管理系统对于主键自动生成唯一索引,所以主键也是一个特殊的索引。
写法:
create table 表格名称(字段1 字典类型1,字段2 字段类型2,primary key(字段));
范例:
mysql> create table 主键约束(id int,name varchar(20),grade double,primary key(id));
mysql> desc 主键约束;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| grade | double | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
#删除主键,因为主键也属于表结构的一部分,所以对主键操作就是操作表结构;
mysql> alter table 主键约束 drop primary key;
4、联合主键
说明;
选定的两个字段中不能出现一样的,两个不完全一样
写法
create table 表格名称(
字段1 字典类型1,
字段2 字段类型2,
primary key(字段1,字段2)
);
范例;
#創建联合主键
mysql> create table 联合主键(id int,name varchar(4),age int,sex char(1),primary key(id,name));
Query OK, 0 rows affected (0.02 sec)
mysql> desc 联合主键;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(4) | NO | PRI | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
总结;
主键约束 vs 联合主键
一张表中最多有一个主键约束(单独字段,联合字段),如果设置多个主键,就会出现如下提示: Multiple primary key defined !!!
删除主键约束前,如果有自增长属性 ,则 需要先删除自增长属性 , 如果不删除自增长就无法删除主键约束
5、外键约束 foreign key
说明:
一张表对另外一张表中设置一个外键,可以关联在一起,同步数据
主表对应的字段只能是 主键 或者 唯一约束修饰的字段
写法:
constraint fk_外键名称 foreignkey(从表_id)references 主表 (主表_id)
范例:
#先创建两张单独的表,在对表约束进行修改,添加外键约束
# 主键表 ======================
mysql> create table class(
-> cla_id int(10) auto_increment primary key,
-> cla_name varchar(30) not null unique
-> );
#插入一些数据
mysql> insert into class values (1,'主机');
+--------+----------+
| cla_id | cla_name |
+--------+----------+
| 4 | java |
| 1 | 主机 |
| 3 | 开发 |
| 2 | 运维 |
+--------+----------+
mysql> desc class;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| cla_id | int(10) | NO | PRI | NULL | auto_increment |
| cla_name | varchar(30) | NO | UNI | NULL | |
+----------+-------------+------+-----+---------+----------------+
# 外键表 ======================
mysql> CREATE TABLE sutlass(
-> stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,
-> stu_name CHAR(10) NOT NULL,
-> classid TINYINT
-> );
mysql> insert into sutlass values (1,'张三',1);
mysql> insert into sutlass values (2,'李四',1);
mysql> insert into sutlass values (3,'老王',2);
mysql> insert into sutlass values (4,'小明',4);
+--------+----------+---------+
| stu_id | stu_name | classid |
+--------+----------+---------+
| 1 | 张三 | 1 |
| 2 | 李四 | 1 |
| 3 | 老王 | 2 |
| 4 | 小明 | 4 |
+--------+----------+---------+
mysql> desc sutlass;
+----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+----------------+
| stu_id | int(10) | NO | PRI | NULL | auto_increment |
| stu_name | char(10) | NO | | NULL | |
| classid | int(11) | YES | MUL | NULL | |
+----------+----------+------+-----+---------+----------------+
#创建表后使用alteer添加外键
mysql> ALTER TABLE sutlass ADD CONSTRAINT fk_classid FOREIGN KEY(classid) REFERENCES class(cla_id) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.04 sec)
#删除外键
mysql> alter table sutlass drop foreign key fk_classid;
Query OK, 0 rows affected (0.01 sec)
错误提示:
mysql> ALTER TABLE sutlass ADD CONSTRAINT fk_classid FOREIGN KEY(classid) REFERENCES class(cla_id);
ERROR 1215 (HY000): Cannot add foreign key constraint
原因在是由于主外键之间的数据类型不一致造成的,以后类似问题,皆可按此处理。
6、自增约束 auto_increment
说明:
用于主键并且是一个字段的主键,才能使用auto_increment,默认从1开始
可以使字段从数字1开始的数逐一递增的,由系统自行管理,不需要人工干涉,可以保证 唯一性 ;一张表只能有一个自增长列,并且该列必须定义了约束 (可以是主键约束,也可以是唯一约束,也可以是外键约束,但是不可以是非空和检查约束)
不过自增长一般配合主键使用,并且只能在数字类型中使用
写法:
create table 表格名称(
字段 字段类型 auto_increment primary key,
);
7、默认约束 default
说明:
采用default关键字,在该字段没有赋值的时候设置默认值;
如果一个字段没有默认值就为 null
默认值存在一些特殊的关键字 default,也可以在值 中使用 函数
写法:
create table 表格名称(
字段 字段类型 deefault 默认值,
);
范例:
mysql> create table t12(id int,timee timestamp not null default now());