数据类型
数值类型
类型
大小
范围(有符号)
范围(无符号)unsigned约束
用途
TINYINT
1 字节
(-128,127)
(0,255)
小整数值
SMALLINT
2 字节
(-32 768,32 767)
(0,65 535)
大整数值
MEDIUMINT
3 字节
(-8 388 608,8 388 607)
(0,16 777 215)
大整数值
INT或INTEGER
4 字节
(-2 147 483 648,2 147 483 647)
(0,4 294 967 295)
大整数值
BIGINT
8 字节
(-9 233 372 036 854 775 808,9 223 372 036 854 775 807)
(0,18 446 744 073 709 551 615)
极大整数值
FLOAT
4 字节float(255,30)
(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)
0,(1.175 494 351 E-38,3.402 823 466 E+38)
单精度 浮点数值
DOUBLE
8 字节double(255,30)
(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)
0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)
双精度 浮点数值
DECIMAL
对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2double(65,30)
M表示有效数字的精度,范围1-65;
D表示小数点后的位数,范围0-30
依赖于M和D的值
小数
【常用】int tinyint float
时间
内置函数
now() 获取当前时间
类型
大小 (字节)
范围
格式
用途
DATE
3
1000-01-01/9999-12-31
YYYY-MM-DD
年月日
TIME
3
'-838:59:59'/'838:59:59'
HH:MM:SS
时分秒
YEAR
1
1901/2155
YYYY
年份值
DATETIME
8
1000-01-01 00:00:00/9999-12-31 23:59:59
YYYY-MM-DD HH:MM:SS
年月日时分秒
TIMESTAMP
4
1970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07
YYYYMMDD HHMMSS
混合日期和时间值,时间戳
【常用】datetime date
通过查看建表的规则时,timestamp的设置如下:
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
可以把他的设置完全复制之后给datetime设置,该设置可以让这行的数据在做修改时,也会随之改变成修改时的时间
字符串
类型
大小
用途
CHAR
0-255字节
定长字符串
VARCHAR
0-65535 字节
变长字符串
TINYBLOB
0-255字节
不超过 255 个字符的二进制字符串
TINYTEXT
0-255字节
短文本字符串
BLOB
0-65 535字节
二进制形式的长文本数据
TEXT
0-65 535字节
长文本数据
MEDIUMBLOB
0-16 777 215字节
二进制形式的中等长度文本数据
MEDIUMTEXT
0-16 777 215字节
中等长度文本数据
LONGBLOB
0-4 294 967 295字节
二进制形式的极大文本数据
LONGTEXT
0-4 294 967 295字节
极大文本数据
【char VS varchar】
char 定长存储,存储速度快,占用更多的空间(使用多)
varchar 变长存储,存储速度慢 ,占用的空间少
# 查看数据的实际存储方式,char可以自动省略数据后边的空格
select concat(列名,'|'),concat(列名,'|') from 表名;
set 和enum
类型
大小
ENUM
对1-255个成员的枚举需要1个字节存储;对于255-65535个成员,需要2个字节存储;最多允许65535个成员。
SET
1-8个成员的集合,占1个字节9-16个成员的集合,占2个字节17-24个成员的集合,占3个字节25-32个成员的集合,占4个字节33-64个成员的集合,占8个字节
【enum】 单选,只允许从值集合中选取单个值,而不能一次取多个值。
【set】 多选,允许值集合中任意选择1或多个元素进行组合,具有选项去重的效果
限制条件
DBMS会按照一定的约束条件对数据进行监测,使不符合规范的数据不能进入数据库,以确保数据库中存储的数据正确、有效、相容。
约束条件与数据类型的宽度一样,都是可选参数,根据功能分为以下几种:
unsigned 无符号:
not null 非空约束: 约束不能为空 注意 ‘严格模式’
default 默认设置: 创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值,default之后不需要带括号
unique 唯一约束:指定某列或者几列组合(联合唯一)不能重复
primary key 主键:指定该列的值可以唯一的表示该行的记录
foreign key 外键:指定该行记录从属于主表中的一条记录,主要用于参照完整性
auto_increment 自增
【总结】
在没有设置主键的情况下,第一个设置 非空+唯一 的字段,就被设置成主键,一张表只有一个主键,其余的非空+唯一就不是主键了。主键的约束作用等同于非空+唯一。
default严格模式下:不支持对 not null 字段插入null值;不支持对自增长字段插入值;不支持text字段有默认值
primary key : 主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之,它是用来独一无二地确认一个表格中的每一行数据。主键可以包含一个字段或多个字段。当主键包含多个栏位时,称为组合键 (Composite Key),也可以叫联合主键。主键可以在建置新表格时设定 (运用 CREATE TABLE 语句),或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。主键必须唯一,主键值非空;可以是单一字段,也可以是多字段组合。
联合唯一 nuique (字段1,字段2)
foreign key 外键
auto_increment 前提条件是unique,自带非空属性,记录功能,不受删除影响,delete from 表名 清空数据表不会影响记录,truncate 表名会直接清除记录;
【foreign key用法】
级联更新 on update cascade
级联删除 on delete cascade
关联的表的字段至少是unique,建议关联主键
表类型必须是innodb存储引擎
语法:
CONSTRAINT 键名 FOREIGN KEY 表A(字段) REFERENCES 表B(字段) ON DELETE CASCADE ON UPDATE CASCADE
【auto_increment】
# 查看当前数据库的自增长设置
show variables like 'auto_inc%';
# 值的修改
alter table 表名 auto_increment=值;
# 修改初始值
set @@auto_increment_offset=2;
# 设定自增长步长
表级
set @@auto_increment_increment=2;
会话级
set session auth_increment_increment=2
全局级
set global auth_increment_increment=2
【注意】
如果auto_increment_offset的值大于auto_increment_increment的值,则auto_increment_offset的值会被忽略
设置严格模式
# 直接在mysql中生效(重启失效):
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
# 配置文件添加(永久失效):
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
9.5 表与表之间的关系
在日常应用中,我们常常会把有关系的表之间建立连接,科学合理的组织外键,会让表与表之间的连接关系更加明确
假设有A、B两张表:
多对一关系:A表中的多条记录对应B表中的一条记录,那么就在A表中关系字段建立foreign key对应B表的关系字段(通常是B表的主键)
一对一关系:A表中的一条记录对应B表的一条记录,反之亦然,那么A表关系字段设置foreign key 对应B的关系字段,并将A表该关系字段设置成unique
多对多关系:A表中的多条记录对应B表的一条记录,反之B表中的多记录对应A表的一条记录,这是需要建立第三张表专门存放A、B表的关系
【示例】
建立class表:
mysql> create table class (cid int primary key auto_incfrement,caption char(6));
mysql> show create table class; # 查看建表信息
mysql> insert into class(caption) values('三年二班'),('一年三班'),('三年一班'); # 插入数据
mysql> select * from class; # 查看表中内容
mysql> select * from class \G; # 高级查看,我也不知道叫什么模式
建立student表
mysql> create table student (
sid int primary key auto_increment ,
sname char(6) not null ,
gender enum('男','女') default '男',
class_id int not null
);
mysql> insert into student(sname,gender,class_id) values ('莫甘娜','女',1),('凯瑟琳','女',2),('盖伦','男',3);
# 学生表中的class_id与班级表之间就是多对一关系,所以在学生表的class_id字段建立外键
mysql> alter table student add CONSTRAINT student_ibfk_1 FOREIGN KEY (class_id) REFERENCES class (cid) ON UPDATE CASCADE
创建teacher表
mysql> create table teacher (tid int primary key auto_increment,tname char(4) not null);
mysql> insert into teacher(tname) values ('老子'),('易大师'),('皎月');
创建课程表course
mysql> create table course (cid int primary key auto_increment,cname char(12) not null,teach_id int not null);
mysql> insert into course(cname,teach_id) values('理论',1),('法术',2),('剑术',3);
# 课程表中的teach_id与教师表是多对一关系,在teach_id建立外键
mysql> alter table course add CONSTRAINT course_ibfk_1 FOREIGN KEY (teach_id) REFERENCES teacher (tid) ON UPDATE CASCADE
创建成绩单score
mysql> create table score (
-> sid int primary key auto_increment,
-> student_id int not null,
-> cour_id int not null,
# 因为学生表与课程表是多对多的关系,将二者的啊关系放在第三张表中
-> number int not null, foreign key(cour_id) references course(cid) on update cascade
-> );
mysql> insert into score (student_id,cour_id,number) values(1,1,60),(1,2,59),(2,2,100);
# 因为学生表与课程表是多对多的关系,将二者的啊关系放在第三张表中
alter table score add CONSTRAINT score_ibfk_2 FOREIGN KEY (student_id) REFERENCES student (sid) ON UPDATE CASCADE
添加外键的语法规则:
# 在创建表时:
create table 表A (段名1,段名2,...),foreign key(添加外键的段名) references 表B(段名) on update cascade
# 表已经创建好了,后来又添加外键
alter table 表A add CONSTRAINT 外键名 FOREIGN KEY (段名) REFERENCES 表B (段名) ON DELETE CASCADE ON UPDATE CASCADE
# 如果设置了ON DELETE CASCADE,删除一条记录时,遇子有关联的外键记录也会跟着删除