数据类型
数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号)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,删除一条记录时,遇子有关联的外键记录也会跟着删除