表的基础数据类型 MySQL的mod设置 表的约束

表的基础数据类型

数值类型

整数类型

​ 类型: tinyint, smallint, mediumint, int, bigint

​ 作用: 存储年龄, 等级, id, 各种号码数据等..

​ tinyint(m):

​ 小整数值: 数据类型用于保存一些范围的整数数值范围

​ int(m):

​ 大整数值: 数据类型用于保存一些范围的整数数值范围

​ bigint(m)

​ 极大整数值: 数据类型用于保存一些范围的整数数值范围

设置无符号

# 数据类型默认都为有符号 
# 以int为例
mysql> create table t(x int unsigned)

​ 注意: 对于整型来说, 数据类型后面的宽度并不是存储长度限制, 而是显示限制, 假如: int(8), name显示时不够8位则用0来填充, 够8位则正常显示.

​ 默认的显示宽度就是能够存储的最大的数据的长度, 比如: int无符号类型, 那么默认的显示宽度就是int(10) , 有符号就是int(11), 因为多了一个符号, 所有我们没有必要指定整数类型的数据

浮点型

​ 类型: float, double

​ 作用: 存储薪资, 身高, 温度, 体重, 体质参数等

​ float(m,d):

​ 单精度浮点数(非准确小数值), m是整数部分 + 小数部分的总个数, d是小数点后个数. m最大值为255, d最大值为30, 例如: float(255,30)

​ 精确度: 随着小数的增多, 精确变得不准确

​ double(m,d):

​ 双精度浮点数(非准确小数值), m是整数部分 + 小数部分的总个数, d是小数点后个数. m最大值也为255, d最大值也为30

​ 精确度: 随着小数的增多, 精度比float要高, 但也会变得不准确

​ decimal(m,d):

​ 准确的小数值, m是整数部分 + 小数部分的总个数(负号不算), d是小数点后个数. m最大值为65, d最大值为30. 比float和double的整数个数少, 但是小数位数都是30位

​ 精确度: 随着小数的增多, 精度始终准确. 对于精确数值计算时需要用此类型, decimal能够存储精确值的原因在于其内部按照字符串存储

​ 精度从高到低: decimal, double, float. 前者精度高, 但是位数少. 后两者精度低, 但是整数位较多. float满足绝大多数的场景

日期类型

​ 类型: date, time, datatime, imestamp, year

​ 作用: 存储用户注册时间, 文章发布时间, 员工入职时间, 出生时间, 过期时间等..

​ year:

​ YYYY (范围: 1901/2155)

​ date:

​ YYYY-MM-DD (范围: 1000-01-01/9999-12-31)

​ time:

​ HH:MM:DD (范围: '-838: 59: 59' / '838: 59: 59')

​ datetime:

​ YYYY-MM-DD HH: MM: SS (范围: 1000-01-01 00:00:00/9999-12-31 23: 59: 59 )

​ timestamp:

​ YYYYMMDD HHMMSS (范围: 1970-01-01 00:00:00/2037)

​ 在时间类型中, now()就相当于time模块中的time.time(), 并自动以格式填充

​ 单独插入时间时, 需要以字符串的形式, 按照对应的格式插入

​ 插入年份时, 尽量使用4位值

​ 插入两位年份时, <=69, 以20开头, >=70, 以19开头

字符串类型

​ 类型: char, varchar

​ 作用: 名字, 信息等等

​ char:

​ 定长, 简单粗暴, 浪费空间, 存取速度快

​ 字符长度范围: 0~255 (一个中文是一个字符, 是utf8的三个字节)

​ 存储:

​ 指定长度为10, 存 >10个字符 则报错(严格模式下), 存<10 个字符则用空格填充直到凑够10个字符存储

​ 检索:

​ 在检索或者说查询时, 查出的结果会自动删除尾部的空格, 如果你想看到它补全空格之后的内容, 需要打开mode: pad_char_to_full_length

​ varchar:

​ 变长, 精准, 节省空间, 存取速度慢

​ 字符长度范围: 0~65535 (如果大于21845会提示用其他类型. mysql行最大限制为65535字节, 字符编码为utf-8)

​ 存储:

​ varchar类型存储数据的真实内容, 不会用空格填充, 如果'ab ', 尾部的空格也会被存起来

​ 强调: varchar类型会在真实数据前加1-2Bytes的前缀, 该前缀用来表示真实数据的bytes字节数(1-2bytes最大表示65535个数字, 正好符合mysql对row的最大字节限制, 已经够用)

​ 如果真实数据 < 255bytes需要1bytes前缀 (1bytes = 8bit 2**8最大表示数字为255)

​ 如果真实的数据>255bytes则需要2bytes的前缀(2bytes = 16bit 2**16最大表示的数字为65535)

​ 检索: 尾部有空格会保存下来, 在检索或者说查询时, 也会正常显示包含空格在内的内容

性能对比

​ 以char(5)和varchar(5)来比较, 加入我要存的三个人名: sb, ssb1, ssbb2

​ char:

​ 优点: 简单粗暴, 不管你是多长的数据, 只按照规定的长度来存, 5个5个的存, 三个人名就会类似这种存储: sb ssb1 ssbb2, 中间是空格补全, 取数据的时候5个5个的取, 简单粗暴速度快

​ 缺点: 貌似浪费空间, 并且我们将来存储的数据的长度可能会参差不齐

​ varchar:

​ varchar类型补丁唱存储数据, 更为精简和节省空间

​ 例如存上面三个人名的时候类似于是这样的: sbssb1ssbb2, 连着的, 如果这样存, 不知道从哪开始从哪结束, 类似于socket解决粘包, 把数据长度作为消息头.

​ 所以, varchar在存数据的时候, 会在每个数据前面加上一个头, 这个头是1-2个bytes的数据, 这个数据指的是后面跟着的这个数据的长度, 1bytes 能表示2**8 = 256, 两个bytes表示2**16 =65536, 能表示0-65535的数字 , 所以存储的时候是这样的: 1bytes + sb +1bytes + ssb1 + 1bytes + ssbb2, 所以存的时候会比较麻烦, 导致效率比char慢, 取的时候也慢, 先拿长度, 再取数据

​ 优点: 节省了一些硬盘空间, 一个acsii码的字符用一个bytes长度就能表示, 但是如果你存储的数据等于你规定的字段长度时, 反而比char占用的空间多.

​ 缺点: 存取速度都慢

枚举类型与集合类型

​ 字段的值只能在给定范围中选择

​ enum 单选 只能在给定的范围内选一个值, 性别 sex 男male/女female

​ set 多选 在给定的范围内可以选择一个或一个以上的值

MySQL的mode设置

​ 工作过程中, 一定要使用严格模式

严格模式的设置和修改

方式一:

​ 先执行select @@sql_mode, 复制查询出来的值并将其中的NO_ZERO_IN_DATE, NO_ZERO_DATE删除, 然后执行set sql_mode='修改后的值' ; 例如: set session sql_mode='STRICT_TRANS_TABLES';改为严格模式,

session可以不用写.(此方法只在当前会话中生效, 关闭当前会话就不生效了)

方式二:

​ 先执行select @@global.sql_mode, 复制查询出来的值并将其中的NO_ZERO_IN_DATE, NO_ZERO_DATE删除, 然后执行set global sql_mode = '修改后的值'(此方法在当前服务中生效, 重新mysql服务后失效)

方法三:

​ 在mysql的安装目录下, 或my.cnf文件(windows系统是my.ini文件), 新增 sql_mode = STRICT_TRANS_TABLES 添加my.cnf

表的完整性约束

​ 约束条件与数据类型的宽度一样, 都是可选参数

​ 作用: 用于保证数据的完整性和一致性

not null 与 default

not null

​ 是否可空, null表示空, 非字符串

​ not null -- 不可空

​ null -- 可空

default

​ 默认值, 创建时可以指定默认值, 当插入数据时如果未主动设置, 则自动添加默认值

注意

​ 当我们在插入数据时, 可以这么写insert into tb1(nid, num) values(1, 'chao'); 在插入输入的时候, 指定字段插入数据, 如果我在只给num插入值, 可以这样写insert into tb1(num) values('chao'); 还可以插入数据的时候, 指定插入数据字段的顺序: 把nid和num换个位置, 但是对应插入的值也要更换位置.

​ 即使你只给一个字段传值了, 那么也是生成了一整条记录, 这条记录的其他字段的值如果可以为空, 那么他们就是null空值, 如果不能为空, 就会报错(报错的是没有默认值)

unique

​ 独一无二, 唯一属性: id, 身份证号等

​ 是一种key, 唯一键, 是在数据类型之外的附加属性, 其实还有加速查询的作用

方法一:

create table department(id int, name varchar(20) unique, comment varchar(100));

方法二:

create table department2(id int, name varchar(20), comment varchar(100), constraint uk_name unique(name));

联合唯一

create table service(id int primary key auto_increment, name varchar(20), name varchar(15) not null, port int not null, unique(host,port))

primary key

​ 从约束角度看 primary key 字段的值不为空且唯一, 那我们为什不使用 not null + unique.

​ 主键 primary key 是 innodb 存储引擎组织数据的依据, innodb 称之为索引组织表, 一张表中必须有且只有一个主键

​ 一个表中可以:

​ 单列做主键

​ 多列做主键 (复合主键或者叫做联合主键)

​ unique key 和primary key 都是MySQL的特殊类型, 不仅仅是个字段约束条件, 还称为索引, 可以加快查询速度

强调:

​ 一张表中必须有, 并且只能有一个主键字段: innodb引擎下存储表数据的时候, 会通过你的主键字段的数据来组织管理所有的数据, 将数据做成一种树形结构, 帮你较少IO次数, 提高获取定位数据, 获取数据的速度, 优化查询

​ 如果一张表中没有设置主键, 那么在创建表的时候, 会按照从上到下遍历你设置的字段, 直到找到not null unique的字段，自动识别成主键pri，通过desc可以看到，这样是不是不好啊，所以我们在创建表的时候，要给他一个主键，让他优化的时候用，如果没有pri也没有not null unique字段，那么innodb引擎下的mysql被逼无奈，你没有设置主键字段，主键又有不为空且唯一的约束，又不能擅自给你的字段加上这些约束，那么没办法，它只能给你添加一个隐藏字段来帮你组织数据，如果是这样，你想想，主键是不是帮我们做优化查询用的啊，这个优化是我们可以通过主键来查询数据：例如：如果我们将id设置为主键，当我们查一个id为30的数据的时候，也就是select * from tb1 where id=30；这个查询语句的速度非常快，不需要遍历前面三十条数据，就好像我们使用的字典似的，找一个字，不需要一页一页的翻书，可以首先看目录，然后看在哪一节，然后看在哪一页，一步步的范围，然后很快就找到了，这就像我们说的mysql的索引(主键、唯一键)的工作方式，一步一步的缩小范围来查找，几步就搞定了，所以通过主键你能够快速的查询到你所需要的数据，所以，如果你的主键是mysql帮你加的隐藏的字段，你查询数据的时候，就不能将这个隐藏字段作为条件来查询数据了，就不能享受到优化后的查询速度了，对么
​ 一张表里面，通常都应该有一个id字段，而且通常把这个id字段作为主键，当然你非要让其他的字段作为主键也是可以的，看你自己的设计，创建表的时候，一般都会写create table t1(id int primary key);id int primary key这个东西在建表的时候直接就写上

auto_increment

​ 约束字段为自动增长，被约束的字段必须同时被key约束，也就是说只能给约束成key的字段加自增属性，默认起始位置为1，步长也为1.

foreign key

​ 外键, 建立表和表之间的关系

一对多(多对一)

多的表里面添加一个字段,并给这个字段加foreign key,比如:
出版社对应书籍是多对一的关系
1.先创建出版社表  publish表
2.创建书籍表,外键写法:
	create table book(
		id int primary key,
		name char(10),
		pid int,
		foreign key(pid) references publish(id)
		);
3.先给出版社插入数据

一对一

一对一关系
学生表(student)和客户表(customer)
create table student(
		id int primary key,
		name char(10),
		cid int unique,
		foreign key(cid) references customer(id)
		);

多对多

多对多关系
作者表和书籍表
需要借助第三张表来完整两者的关系记录
第三张表后创建
create table authortobook(
		id int primary key,
		author_id int,
		book_id int,
		foreign key(author_id) references author1(id),
		foreign key(book_id) references book(id)
		);