MySQL——数据类型、列属性

2019独角兽企业重金招聘Python工程师标准>>>

--------------MySQL数据类型——值类型-------------------


--tinyint-占用1字节-范围：-128~127（-2^7~2^7-1)---无符号数范围：0~255
--smallint-占用2字节-范围：-32768~32767(-2^15~2^15-1)
--mediumint-占用3字节
--int--占用4字节
--bigint--占用8字节


create database if not exists day2 charset=utf8;
use day2;


--zerofill
--定义整型显示宽度
--结合zerofill则位数不够时填充0，位数超出按原值显示
--添加zerofill属性后随之产生unsigned属性，所以不能写入负数
create table `zerofill`(
       num1 int(5),
       num2 int(5) zerofill
       )engine=innodb;
--num2没有5位填充0-->00098
insert into `zerofill` values (98,98);
--位数超出按原值显示
insert into `zerofill` values (1234567,1234567);
--负数不能写入num2，会报错
insert into `zerofill` values (-98,-98);
select * from `zerofill`;


--unsigned
--无符号整型
--使用unsigned无符号整型范围比有符号数大两倍
create table `unsigned`(
       num1 tinyint,
       num2 tinyint unsigned
       );
--tinyint:-128~127
--tinyint unsigned:0~255
--128超出num1范围会报错
insert into `unsigned` values (128,128);
---128为负数，不能写入num2，会报错
insert into `unsigned` values (-128,-128);
--OK
insert into `unsigned` values (127,255);


--float,double
--浮点型
--float(M,D),M指定总位数，D指定小数位数
--整数位数不能超出M-D，小数位数可以超出D，超出则四舍五入
--支持科学计数法
--浮点型总是存储近似值
create table `float`(
       num1 float,
       num2 double(4,2)
       );
--浮点数存储的是近似值，num1的存储值为1
insert into `float` (num1) values (0.9999999);
--如果小数位数超过，则四舍五入取两位小数
insert into `float` (num2) values (0.1289);
--如果整数位超过则不能写入，会报错
insert into `float` (num2) values (12345);
--浮点数支持科学计数法
insert into `float` (num1) values (5E6),(7e-3);


--decimal
--定点数
--decimal(M,D),M指定总位数，D指定小数位数
--M:1~65，D:0~30,M>D
--定点数支持无符号数，同时支持显示宽度
create table `decimal`(
       num1 decimal(5,2),
       num2 decimal(5,2) unsigned zerofill,
       num3 decimal zerofill,
       num4 decimal 
       )engine=innodb;
--整数部分位数不能超过规定长度
insert into `decimal` (num1) values (12345);
--小数部分位数超过四舍五入
insert into `decimal` (num1) values (.9876);
--可以写入负数
insert into `decimal` (num1) values (-1);
--小数和整数部分都会补齐0-->001.10
insert into `decimal` (num2) values (1.1);
insert into `decimal` (num2) values (0);
--定义了zerofill则会自动添加unsigned属性，所以不能写入负数
insert into `decimal` (num3) values (-2);
--若M和D省略，则默认是decimal(10,0)
insert into `decimal` (num3) values (0);
--由于默认是decimal(10,0)，所以整数不能超过10位
insert into `decimal` (num4) values (12345678987654321);
--写入负数会四舍五入成整数
insert into `decimal` (num4) values (0.9876);
insert into `decimal` (num4) values (0.1);


----------------------MySQL数据类型——字符类型------------------


--char-定长-长度：255个字符-效率高
--varchar-可变长度-长度：65535个字符-省空间
--tinytext-长度：2^8-1-255个字符
--text-2^16-1-65535个字符
--mediumtext-2^24-1个字符
--longtext--2^32-1个字符
--在utf8下一个中文占3个字节，在gbk下，一个中文占两个字节


--创建utf8编码格式的表，varchar最大取值21845，考虑到从0开始，即varchar(21844)
--不能创建表，报错
create table `varchar-utf8`(
       char1 varchar(65535)
       )charset=utf8;
--可以创建表
create table `varchar-utf8`(
       char1 varchar(21844)
       )charset=utf8;
--创建gbk编码格式的表，varchar最大取值32767，考虑到从0开始，即varchar(32766)
--不能创建表，报错
create table `varchar-gbk`(
       char1 varchar(65535)
       )charset=gbk;
--可以创建表
create table `varchar-gbk`(
       char1 varchar(32766)
       )charset=gbk;


--------------------MySQL数据类型——枚举enum-----------
--enum
--从集合中选择一个值作为数据，称为枚举（单选）
--枚举值在数据库中的管理是通过整型数字管理的，第一个值是1，第二个值是2，以此类推…
--枚举占用2个字节
--2个字节16位，能保存2^16个状态，范围：0~65535，枚举从1开始，0保存的是空字符串错误值
--NULL值的索引是NULL


create table `enum` (
       sex enum('男','女','保密')
       );
--插入的值只能是枚举中的值
--不能插入
insert into `enum` values ('不知道');
--可以插入
insert into `enum` values ('男');
--可以以整型数字形式插入
insert into `enum` values(2);
--枚举的查询也可以通过数字形式查询
select * from `enum` where sex=1;
--结果与sex=1一样
select * from `enum` where sex='男';
--枚举在数据库内部用数字表示
select sex+0 from `enum`;


-------------MySQL数据类型——集合类型（set）--------
--set
--从集合中选择一些值作为多选数据
--数据存储时按定义时的顺序存储，插入顺序不影响显示顺序
--集合和枚举一样，给每个元素分配了一个固定的值，分配方式从前往后分别是2的0次方，2的1次方，2的2次方，…
--选择多个选项，是按位或的关系，选中则该位的值为1，不选值为0
--集合占8个字节
--因为集合是一个位表示一个状态。8个字节有64个位，所以集合有64个选项


--示例：如果选择爬山、看电影和游泳，则MySQL返回的值是二进制（从右往左）1101，即十进制的9
--爬山    读书    看电影    游泳
--1       0         1        1
create table `set` (
       hobby set('爬山','读书','看电影','游泳')
       );
--以下都显示”读书，爬山“，MySQL内的值为3
insert into `set` values ('爬山,读书');
insert into `set` values ('读书,爬山');
--插入两行记录，MySQL内的值为二进制1001，即十进制9
insert into `set` values ('读书'),('游泳');
--MySQL内的值为1111-->15
insert into `set` values ('爬山,读书,看电影,游泳');
--集合在MySQL内部是通过数字管理的
select hobby+0 from `set`;


------------------MySQL数据类型——日期时间型-------------
--date,日期
--time,时间
--datetime,日期时间
--year,年份，占用1个字节
--timestamp,时间戳，占用4个字节，从1970年1月1日0时0分0秒到现在的秒数


--datetime
--范围：1~9999
create table `datetime`(
       myDate datetime
       );
--正常值
insert into `datetime` values ('2015-11-15 1:08:30');
--位数不够会用0补齐-->2015-01-01 01:01:01
insert into `datetime` values ('2015-1-1 1:1:1');
--如果年份没有写够四位，则会按最接近的合理年份补齐
--MySQL储存为：2015-11-15 1:08:30
insert into `datetime` values ('15-11-15 1:08:30');
--MySQL储存为：1999-11-15 1:08:30
insert into `datetime` values ('99-11-15 1:08:30');
--MySQL储存为：999-11-15 1:08:30
insert into `datetime` values ('999-11-15 1:08:30');
--所以，最好在插入值的时候年份写满4位，避免错误
insert into `datetime` values ('0099-11-15 1:08:30');
--插入的值必须符合规范，以下报错
insert into `datetime` values ('2015-99-99 99:99:99');


--time
--可以表示时间，也可以用来表示时间间隔
--范围：-838:59:59~838:59:59
create table `time`(
       myTime time
       );
--插入时间
insert into `time` values ('22:2:2');
--时间间隔
insert into `time` values ('222:22:22');
insert into `time` values ('-222:22:22');
--time也支持以天的方式表示时间间隔,但储存和显示时会转化成小时数
insert into `time` values ('10 10:10:10');


--timestamp
--timestamp和datetime表现形式是一样的
--他们的区别在于datetime带范围是1~9999
--而timestamp的范围是1970年~2038年01月19日11:14:07秒
--到了这个秒数就超过了4个字节的大小了
create table `timestamp`(
       myTime timestamp
       );
--插入数据
insert into `timestamp` values ('2015-1-1 1:1:1');


--year
--只能表示从1901~2155之间的年份，因为只占用一个字节，只能表示255个年份


--------------------MySQL数据类型——布尔型---------------
--boolean
--mysql不支持boolean型，true和false在数据库中对应1和0
--布尔型在计算机内部用tinyint来保存
create table `boolean` (
       myBool boolean
       );
--插入值
insert into `boolean` values (true),(false);
insert into `boolean` values (1),(0);
--这样竟然也行
insert into `boolean` values (8),(0);


------------------列属性——是否为空（null|not null)---------
--null|not null
create table `null` (
       num1 int null,
       num2 int not null
       );
--插入数据
insert into `null` values (null,89);


-----------------列属性——默认值（default）-------------
--default
--如果一个字段没有插入值，可以默认插入一个指定的值
--插入默认值不能使用null，而是使用default关键字
create table `default` (
       age int default 10,
       addr varchar(50) default '地址不详'
       );
--插入数据
insert into `default` values (default,default);
insert into `default` (age) values (89);
insert into `default` (addr) values ('上海');


-----------------列属性——自动增长（auto_increment）-------------
--auto_increment
--字段的值从1开始，每次递增1
--因为自动增长肯定是正整数，所以可以设定成无符号整型
--自动增长的特点就是字段中不会有重复的值，适合为记录生成一个唯一的id号
--自动增长列的编号一旦删除，默认就不会重复使用
create table `auto_increment` (
       id int unsigned auto_increment primary key,
       name varchar(50)
       );


-----------------列属性——主键（primary key）-------------
--primary key
--唯一标识表中的一个或一组列成为主键
--一个表只有一个主键，但是一个主键可以由多个字段组成
--在字段后面通过primary key关键字创建主键
create table `primary key` (
       id int unsigned auto_increment primary key,
       name varchar(50)
       );
--通过primary key()方法创建
create table `primary key` (
       id int unsigned auto_increment,
       name varchar(50),
       primary key(id)
       );
--通过修改表的方式创建主键
create table `primary key` (
       id int unsigned auto_increment primary key,
       name varchar(50)
       );
alter table `primary key` add primary key(id);
--创建组合键
create table `primary key` (
       id int unsigned auto_increment,
       name varchar(50),
       primary key(id,name)
       );
--删除主键
alter table `primary key` drop primary key;

转载于:https://my.oschina.net/u/2490061/blog/530452