mysql严格模式 报错_MySQL表操作

一、存储引擎

不同的数据应该有不同的处理机制

mysql存储引擎：

Innodb:默认的存储引擎，查询速度较myisam慢，但是更安全

myisam:mysql老版本用的存储引擎

memory:内存引擎(数据全部存在内存中，重启数据就消失了)

blackhole:无论存什么 都立马消失(黑洞)

用 show engines;  查询每个存储引擎存取数据的特点

二、数据类型

整型，浮点型，字符类型，日期类型，枚举与集合类型

创建表的完整语法

#语法：

create table 表名(

字段名1 类型[(宽度) 约束条件],

字段名2 类型[(宽度) 约束条件],

字段名3 类型[(宽度) 约束条件]

);

创建t1表，id类型int,name类型char设置宽度16字符

注意：

1.字段名和字段类型是必须的，中括号的参数都是可选参数

2.同一张表中字段名不能重复

3.最后一个字段后面不能加逗号

宽度：

使用数据库的准则：能尽量让它少干活就尽量少干活。

对存储数据的限制     char(1)只能存一个字符

如果超了 mysql会自动帮你截取，有两种模式，默认是自动截取

1.插入的时候mysql自动截取

2.会直接报错(mysql严格模式)

类型和中括号内的约束:

类型约束的是数据的存储类型，而约束条件是基于类型之上的额外限制。

整型类型

TINYINT(2^8长度)   SMALLINT(2^16长度)    MEDIUMINT(2^24长度)     INT(2^32长度)     BIGINT(2^64长度)    1字节=8bites

TINYINT 最长4位，SMALLINT最长6位，MEDIUMINT最长8位，INT最长11位(位数都是包括前面的符号)

TINYINT ：默认是带符号的，所以数字范围是(-128,127)。如果没有设置严格模式，超出之后只会存最大值或者最小值。如果设置了严格模式超出就会报错。

这些整型都是默认带符号的

char后面的数字是用来限制存储数据的长度的

特例：只有整型后面的数字不是用来限制存储数据的长度，而是用来控制展示的数据的位数

#显示时，不够8位用0填充，如果超出8位则正常显示(在非严格模式下，超出int限制的最大位数，只会存最大值或者最小值)

create table t5(id int(8) unsigned zerofill);      #unsigned去掉符号，zerofill用0填满

insert into t5 values(566);   #插入值566

查询 t5，没有满8位，用0补充。

注意：对于整型来说，数据类型后面的宽度并不是存储限制，而是显示限制，所以在创建表是，如果字段采用的是整型类型，完全无需指定显示宽度，默认的显示宽度，足够显示完整当初存放的数据。

下面这个例子能说明：设置的表t6宽度5只是显示长度，超过宽度5正常显示。(int的最大位数是11位，只要不超过11就可以)

前面提到宽度时候有严格模式：

在上面设置了char,tinyint,存储数据时超过它们的最大存储长度，发现数据也能正常存储进去，只是mysql帮我们自动截取了最大长度。但是实际上，我们应该尽量减少数据库的操作，缓解数据库的压力，让它仅仅只管理数据库即可，这样的情况下就需要设置安全模式。

show variables like "%mode%"; #查看数据库配置中变量名包含mode的配置参数#修改安全模式

set session #临时有效 # 只在当前操作界面有效

set global #全局有效

setglobal sql_mode ='STRICT_TRANS_TABLES'

#修改完之后退出当前客户端重新登陆即可

浮点型  应用场景：身高，体重，薪资

float(255,30)  总共255位，小数部分占30位

double(255,30) 总共255位   小数部分占30位

decimal(65,30)  总共65位，小数部分占30位

create table t12(id FLOAT(255,30));

create table t13(id DOUBLE(255,30));

create table t14(id DECIMAL(65,30));

insert into t12 values(1.111111111111111111111111111111);

insert into t13 values(1.111111111111111111111111111111);

insert into t14 values(1.111111111111111111111111111111);

精确度：float < double < decimal

字符类型

char(定长)，varchar(变长)

char(4)  #最多只能存四个字符，超出来会直接报错，如果少了，会自动用空格填充

varchar(4)   #最多只能存四个字符，超出来会直接报错，如果少了，有几个存几个

mysql在存储char类型字段的时候，硬盘上确确实实存的是固定长度的数据，但是取出来的那一瞬间，mysql会自动将填充的空格去掉。

可以通过修改严格模式，来修改机制，让其不做自动去除处理：set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";   退出客户端重新登录。

char与varchar的区别

char定长1.浪费空间2.存取速度快

varchar变长1.节省空间2.存取速度慢(较于char比较慢)

存的时候 需要给数据讲一个记录长度的报头

取的时候 需要先读取报头才能读取真实数据

char取的时候方便，直接按固定的长度取即可

varchar取得时候比较繁琐，无法知道数据到底多长

日期类型

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') #报错 只能选择enum里面的

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  字段不能为空

#没有设置not null name字段可以写null

create table t9(id int,name varcher(10)); #创建表t9

insert into t9 values(1,null);

select* from t9;

#设置not null

create table t10(id int,name varchar(10) notnull); 创建t10表

desc t10;

insert into t10 values(2,'jason'); #添加数据

select * from t10;

default   为该字段设置默认值(当用户写了的时候用户的，用户没写就用默认值)

#default 设置默认值

create table t11(id int,name varchar(10) default 'jason'); #name设置默认值jason

desc t11;

insert into t11(id) values(1); #往表中插入数据的时候，可以指定字段插入，不需要全部插入

select* from t11;

字段使用自己写的值egon，查询结果就是使用自己写的值

unique  唯一设置的值不能一样

1.单例唯一    设置某个字段是唯一的，设置之后不能由重复值

#设置唯一

create table t12(id int unique,name char(10));

desc t12;

insert into t12 values(1,'jason');

insert into t12 values(2,222);

select* from t12;

2.联合唯一    在语句的最后用括号的形式，表示哪几个字段组合的结果是唯一的

#联合唯一 ip+port只能是唯一值

create table server(id int,ip char(10),port int,unique(ip,port));

desc server;

insert into server values(1,'127.0.0.1',8080);

insert into server values(1,'127.0.0.1',8081);

insert into server values(1,'127.0.0.2',8080);

select* from server;

primary key 主键

限制效果跟  not null+unique  组合效果一致   非空且唯一

primary key也是innodb引擎查询必备的索引，索引你就把他当成书的目录。

innodb引擎在创建表的时候，必须要有一个主键

当你没有指定主键的时候：

1.会将非空且唯一的字段自动升级成主键

2.当你的表中没有任何的约束条件，innodb会采用自己的内部默认的一个主键字段，该主键字段你在查询时候是无法使用的，查询数据的速度就会很慢，类似于一页一页的翻书。

主键字段到底设置给谁呢？？？

通常每张表里面都应该有一个id字段，并且应该将id设置为表的主键字段。

create table t15(id int primary key,ip char(10)); #id为主键，非空且唯一

desc t15;

前面有联合唯一，这里也有联合主键：多个字段联合起来作为表的一个主键，本质还是一个主键

注意：innodb引擎中的一张表有且只有一个主键(******)

#联合主键

create table t14(ip char(10),port int,primary key (ip,port));

主键字段应该具备自动递增的特点，每次添加数据，不需要用户手动输入。

auto_increment  自动递增

#主键自动递增

create table t17(id int primary key auto_increment,name varchar(10));

desc t17;

insert into t17(name) values('jason');

insert into t17(name) values('egon');

select* from t17;

delete from  表名    仅仅是删除数据，不会重置主键，不会将id重置为0，继续添加id值还是从上次的id值开始。

truncate 表名     初始化表，会重置主键，id 从0开始。