MySQL表操作：字段类型 约束条件

表操作

1.数据类型

　　建表的时候，字段都有对应的数据类型

整型

浮点型

字符类型(char与varchar)

日期类型

枚举与集合

2.约束条件

primary key

unique key

not null

foreign key

详细内容

创建表的完整语法

1. 在同一张表中，字段名不能重复
2. 字段和类型是必须的，宽度和约束条件可选
3. 最后一个字段后不能加逗号！

#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

# 补充：
# 1.宽度指的是对存储数据的限制
create table userinfo(name char);
insert into userinfo values('json');
　　"""
　　1.没有安全模式的数据库版本，能够存放数据但是只会存进去一个j
　　2.最新数据库版本直接报错提示无法存储:Data too long for column 'name' at row 1
　　"""

# 2.约束条件初识>>> null 与 not null
create table t1(id int,name char not null);
insert into t1 values(1,'j');  # 正常存储
insert into t1 values(2,null);  # 报错

# 字段类型与约束条件的关系：
# 字段类型：限制数据的存储类型
# 约束条件：基于字在类型附加的额外限制

基本数据类型

整型

分类：TINYINT SMALLINT MEDIUMINT INT BIGINT

作用：存储年龄，等级，id，各种号码等

　　1）验证整型字段有无符号及范围

create table t1(x tinyint);
insert into t1 values(128),(-129);

create table t2(x tinyint unsigned);
insert into t2 values(-1),(256);  

create table t3(x int unsigned);
insert into t3 values(4294967296);

2）疑问：类型后面的宽度能否改变字段存储的大小限制

create table t4(x int(8));
insert into t4 values(4294967296123);

# 显示时，不够8位用0填充，如果超出8位则正常显示
create table t5(x int(8) unsigned zerofill);
insert into t5 values(4294967296123);

# create table t6(id int(10) unsigned);
# create table t7(id int(11));

3）给整型设置宽度：

　　特例：唯独整型字段在设置宽度的时候，限制不是存储宽度而是显示宽度

　　　　  在创建整型字段的时候，不需要指定字段宽度(整型的显示宽度)

 

严格模式补充

存储数据时超过它们的最大存储长度，也能正常存储进去，原因：mysql自动截取了最大长度。

在实际情况下应尽量减少数据库的操作，缓解数据库的压力，让它仅管理数据即可，这时需要设置安全模式（设置严格模式）

set global sql_mode ='STRICT_TRANS_TABLES'

 

 mode：like模糊查询

  %:匹配任意数量的任意字符    
  _:匹配单个数量的任意字符    
  show variables like "%mode%";

show variables like "%mode%";  # 查看数据库配置中变量名包含mode的配置参数
# 设置严格模式
set session # 只在当前操作界面有效
set global  # 全局有效
set global sql_mode ='STRICT_TRANS_TABLES'   # 默认：NO_ENGINE_SUBSTITUTION
# 设置完成之后退出当前客户端重新登陆即可

浮点型

分类:FLOAT DOUBLE decimal

应用场景:身高，体重，薪资

 

字段限制特点(5,3)前一位表示所有的位数，后一位表示小数个数

# 存储限制
float(255,30)
double(255,30)
decimal(255,30)

# 精确度验证（由低到高）
create table t9(x float(255,30));
create table t10(x double(255,30));
create table t11(x decimal(65,30));

insert into t9 values(1.111111111111111111111111111111);
insert into t10 values(1.111111111111111111111111111111);
insert into t11 values(1.111111111111111111111111111111);
# 精度结果：1字节、2字节、全显

 

字符类型

分类：char(定长)、varchar(变长)

作用：姓名，地址，描述类信息

char读出时不自动去除空格：set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";

 

create table t12(name char(4));       # 超出四个字符报错，不够四个字符空格补全
create table t13(name varchar(4));    # 超出四个字符报错，不够四个有几个就存几个

# 验证存储限制
insert into t12 values('hello');
insert into t13 values('hello');
# 验证存储长度
insert into t12 values('a'); #'a    '
insert into t13 values('a'); #'a'
select * from t12;
select * from t13;  # 无法查看真正的结果

select char_length(name) from t12;
select char_length(name) from t13;  # 仍然无法查看到真正的结果

"""首先应该肯定的是在硬盘上存的绝对是真正的数据，但显示的时候mysql会自动将末尾的空格取掉"""
# 如果不想让mysql帮你做自动去除末尾空格的操作，需要再添加一个模式
set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";
# 退出客户端重新登陆
select char_length(x) from t12; #4
select char_length(y) from t13; #1

# 针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中。但是会在读出结果的时候自动取掉末尾的空格


"""
char与varchar的使用区别
"""
name char(5)
# 缺点：浪费空间
# 优点：存取速度都快
tom bob lxx  jxx  txx  

name varchar(5)
# 缺点：存取速度慢
# 优点：节省空间
1bytes+tom 1bytes+bob 1bytes+lxx  1bytes+jxx  1bytes+txx 

 

时间类型

分类：date：2019-05-01

　　   time：11:11:11

　　　　  Datetime: 2019-01-02 11:11:11

　　　　  Year:2019

create table student(
    id int,
  name char(16),
  born_year year,
  birth date,
  study_time time,
  reg_time datetime
);
insert into student values(1,'egon','2019','2019-05-09','11:11:00','2019-11-11 11:11:11');

枚举与集合类型

　　分类：枚举enum多选一  集合set多选多

create table user(
    id int,
  name char(16),
  gender enum('male','female','others')
);
insert into user values(1,'jason','xxx')  # 报错
insert into user values(2,'egon','female')  # 正确！


create table teacher(
    id int,
  name char(16),
  gender enum('male','female','others'),
  hobby set('read','sleep','sanna','dbj')
);
insert into teacher values(1,'egon','male','read,sleep,dbj')  # 集合也可以只存一个

约束条件

PRIMARY KEY (PK)   标识该字段为该表的主键，可以唯一的标识记录

FOREIGN KEY (FK)   标识该字段为该表的外键

NOT NULL          标识该字段不能为空

UNIQUE KEY (UK)    标识该字段的值是唯一的

AUTO_INCREMENT     标识该字段的值自动增长（整数类型，而且为主键）

DEFAULT           为该字段设置默认值

UNSIGNED          无符号

ZEROFILL          使用0填充

not null+default

create table user(
    id int,
  name char(16)
);
insert into user values(1,null)  # 可以修改

alter table user modify name char(16) not null;
insert into user(name,id) values(null,2);  # 报错 插入数据可以在表名后面指定插入数据对应的字段

create table student(
    id int,
  name char(16) not null,
  gender enum('male','female','others') default 'male'
)
insert into student(id,name) values(1,'jason')  # 成功

unique

# 单列唯一
create table user1(
    id int unique, 
  name char(16)
);
insert into user1 values(1,'jason'),(1,'egon')  # 报错
insert into user1 values(1,'jason'),(2,'egon')  # 成功

# 联合唯一
create table server(
    id int,
  ip char(16),
  port int,
  unique(ip,port)
)
insert into server values(1,'127.0.0.1',8080);
insert into server values(2,'127.0.0.1',8080);  # 报错
insert into server values(1,'127.0.0.1',8081);

primary key+auto_increment

单从约束角度来说primary key就等价于not null unique

除了约束之外，它还是innodb引擎组织数据的依据，提升查询效率

create table t11(id int primary key);
desc t11;
insert into t11 values(1),(1);  # 报错
insert into t11 values(1),(2);

强调:
1.一张表中必须有且只有一个主键，如果没有设置主键，那么会从上到下搜索直到遇到一个非空且唯一的字段自动将其设置为主键

2.如果表里面没有指定任何的可以设置为主键的字段，那么innodb会采用自己默认的一个隐藏字段作为主键，隐藏意味着你在查询的时候无法根据这个主键字段加速查询了
　　索引：类似于书的目录，没有主键就相当于一页一页翻着查
3.一张表中通常都应该有一个id字段，并且通常将改id字段作成主键

create table t12(
    id int,
  name char(16),
  age int not null unique,
  addr char(16) not null unique
)engine=innodb;
desc t12;




# 联合主键：多个字段联合起来作为表的一个主键，本质还是一个主键
create table t18(
    ip char(16),
  port int,
  primary key(ip,port)
);
desc t18;

# 主键id作为数据的编号，每次最好能自动递增
create table t13(
    id int primary key auto_increment,
    name char(16)
);
insert into t13('jason'),('jason'),('jason');  # id字段自动从1开始递增
# 注意：auto_increment通常都是加在主键上，并且只能给设置为key的字段加

补充

# delete 确实可以将表里的所有记录都删掉，但不会将id重置为0：
delete from tb1;

# 所以delete 根本不是用来清空表的，它是用来删除表中某一些符合条件的记录
delete from tb1 where id > 10;

如果要清空表，使用truncate tb1；
作用：将整张表重置,id重新从0开始记录