MySQL常用数据类型
1.常用数据类型：
（1）tinyint(小整型值）: 1个字节，有符号的范围是（-128~127），无符号（unisigned）的范围是(0~255)
（2）int （大整型值）：4个字节，有符号的范围是（-21亿~21亿左右），无符号的范围是（0~42亿左右）
（3） 浮点型：
float(255,30): 单精度，总长度是255，小数位是30
double(255,30): 双精度，总长度是255，小数位是30
decimal(65,30）：金钱类型，总长度是65，小数位是30
可以设置总长度和小数位：create table t3(f1 float(5,2) , f2 double(5,2) , f3 decimal(5,2));
默认状态下，float默认保留5位小数，double默认保留16位小数，decimal默认保留四舍五入后的整数位。
create table t5(f1 float,f2 double,t3 decimal);
（4）字符串：
char(10) : 固定长度，最大长度为255，固定开辟10个字符长度的空间（手机号，身份证号），char开辟空间的速度更快
varchar(11)：不固定长度，最大长度为21845，最多创建字符长度为11位的空间（小评论），开辟空间速度较慢，但是节约内存
text：文本类型（文章，小说）
create table t9(a char(11) , b varchar(20) , c text);
insert into t9 values("111","小评论","评论多多");
(5) 枚举enum：从列出来的数据当中选一个，如性别
(6) 集合set：从列出来的数据中选多个（注意去重性）
create table t12(
-> id int,
-> name char(10) ,
-> money float(6,2) ,
-> sex enum("man","women") ,
-> hobby set("reading","run","walk")
-> );
insert into t12 (id,name,money,sex,hobby) values(1,"bob",10.9,"man","run,run,walk");
mysql> select * from t12;
+------+------+-------+------+----------+
| id | name | money | sex | hobby |
+------+------+-------+------+----------+
| 1 | bob | 10.90 | man | run,walk |
+------+------+-------+------+----------+
（7）时间类型：
date: YYYY-MM-DD
time: HH:MM:SS
year: YYYY
datetime:YYYY-MM-DD HH:MM:SS
create table t1(f1 date,f2 time,f3 year,f4 datetime); （创建表格）
insert into t1 values("2018-07-05","12:38:40","2018","2018-07-05 12:38:40"); （添加数据）
insert into t1 values(now(),now(),now(),now()); （也可以使用MySQL自带的now()函数添加系统当前时间，MySQL会自动根据时间类型切断截取）
mysql> select * from t1;
+------------+----------+------+---------------------+
| f1 | f2 | f3 | f4 |
+------------+----------+------+---------------------+
| 2018-07-05 | 12:38:40 | 2018 | 2018-07-05 12:38:40 |
| 2020-06-17 | 14:42:08 | 2020 | 2020-06-17 14:42:08 |
+------------+----------+------+---------------------+
timestamp YYYYMMDDHHMMSS(自动更新时间)
create table t2(dt datetime,ts timestamp);
insert into t2 values(null,null);
mysql> select * from t2;
+------+---------------------+
| dt | ts |
+------+---------------------+
| NULL | 2020-06-17 14:51:14 |
+------+---------------------+
insert into t2 values(20180705123840,20200617092430);
mysql> select * from t2;
+---------------------+---------------------+
| dt | ts |
+---------------------+---------------------+
| NULL | 2020-06-17 14:51:14 |
| 2018-07-05 12:38:40 | 2020-06-17 09:24:30 |
+---------------------+---------------------+
insert into t2(dt) values(now());
mysql> select * from t2;
+---------------------+---------------------+
| dt | ts |
+---------------------+---------------------+
| NULL | 2020-06-17 14:51:14 |
| 2018-07-05 12:38:40 | 2020-06-17 09:24:30 |
| 2020-06-17 14:53:03 | 2020-06-17 14:53:03 |
+---------------------+---------------------+
约束：对要编辑的数据类型进行类型约束，不符合条件的直接报错
(1) unsigned:无符号
create table t3(id int unsigned);
insert into t3 values(66);
insert into t3 values(-55); # error
(2) not null: 设置不为空
create table t4(id int not null,name char(4));
insert into t4 values(1,"bob");
insert into t4(name) values("jack"); # ERROR 1364 (HY000): Field 'id' doesn't have a default value
(3) default: 设置默认值
create table t5(id int,name char(4) default "bob");
insert into t5(id) values(1);
insert into t5 values(2,"jack");
mysql> select * from t5;
+------+------+
| id | name |
+------+------+
| 1 | bob |
| 2 | jack |
+------+------+
(4) unique: 唯一约束，限定数据不能重复，UNI表示唯一索引 , 但是允许塞null空值
create table t6(id int unique,xuehao int(8));
insert into t6 values(1,111);
insert into t6 values(2,111);
insert into t6 values(null,111);
insert into t6 values(null,222);
insert into t6 values(1,222); # ERROR 1062 (23000): Duplicate entry '1' for key 'id'
(5) primary key: 主键(PRI)，标记数据的唯一特征，唯一且不为空
create table t7(id int primary key,xuehao int(8));
insert into t7 values(1,111);
insert into t7 values(1,222); # ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
insert into t7 values(null,333); # ERROR 1048 (23000): Column 'id' cannot be null
mysql> desc t7;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| xuehao | int(8) | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
# 如果primary key和unique not null两个约束同时存在，以primary key为主键，unique not null为UNI
create table t8(id int primary key,xuehao int unique not null);
mysql> desc t8;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| xuehao | int(11) | NO | UNI | NULL | |
+--------+---------+------+-----+---------+-------+
# 一个表里面只能有一个主键
create table t9(id int primary key,xuehao int primary key);
# ERROR 1068 (42000): Multiple primary key defined
(6) auto_increment: 自增加1，一般配合主键使用，或者unique
create table t10(id int primary key auto_increment,xuehao int default 111);
insert into t10 values();
insert into t10 values();
mysql> select * from t10;
+----+--------+
| id | xuehao |
+----+--------+
| 1 | 111 |
| 2 | 111 |
+----+--------+
delect:只是删除数据，增加的数据ID号还是从上一个接着增加
delete from t10;
insert into t10 values();
mysql> select * from t10;
+----+--------+
| id | xuehao |
+----+--------+
| 3 | 111 |
+----+--------+
truncate: 是重置表，增加的数据ID从1开始
truncate t10;
insert into t10 values();
mysql> select * from t10;
+----+--------+
| id | xuehao |
+----+--------+
| 1 | 111 |
+----+--------+
# 可设置自增的其实位置
create table t1(
id int primary key auto_increment,
name varchar(255)
)engine = myisam auto_increment=3 charset=utf8;
(7) zerofill: 0填充,配合int使用，可设置位数，位数不够的，用0填充
create table t11(id int(8) zerofill);
insert into t11 values(222);
insert into t11 values(123456789);
mysql> select * from t11;
+-----------+
| id |
+-----------+
| 00000222 |
| 123456789 |
+-----------+
(1) 联合唯一约束：unique(字段1，字段2，字段3..),把多个字段拼在一起表达唯一的数据,多个字段结合在一起作为主键
# 都不为空
create table t14(id int(4) not null,xuehao int(8) not null, unique(id,xuehao));
insert into t12 values(1,111);
insert into t12 values(1,222);
insert into t12 values(1,111); # ERROR 1062 (23000): Duplicate entry '1-111' for key 'id'
mysql> desc t12;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name | char(4) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
# 有字段为空,显示为MUL普通索引
create table t16(class char(4),id int(4),xuehao int(8), unique(id,xuehao) );
insert into t16 values(2,1,111);
insert into t16 values(1,null,111);
insert into t16 values(1,null,null);
mysql> select * from t16;
+-------+------+--------+
| class | id | xuehao |
+-------+------+--------+
| 2 | 1 | 111 |
| 1 | NULL | 111 |
| 1 | NULL | NULL |
| 1 | NULL | NULL |
+-------+------+--------+
mysql> desc t16;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| class | char(4) | YES | | NULL | |
| id | int(4) | YES | MUL | NULL | |
| xuehao | int(8) | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
# 若primary key和联合唯一约束同时出现，以primary key 为主键
主键不能再增加值，联合唯一约束可以再增加
create table t17(id int primary key,xuehao int not null,class int not null,unique(xuehao,class));
mysql> desc t17;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| xuehao | int(11) | NO | MUL | NULL | |
| class | int(11) | NO | | NULL | |
+--------+---------+------+-----+---------+-------+
(2) foreign key:外键，把多张表通过一个关键字段联合起来，外键要求，关联的字段必须具有唯一性(unique/primary key)
create table class1(id int unique , classname varchar(255));
insert into class1 values(1,"py");
insert into class1 values(2,"li");
create table students1(
id int primary key auto_increment,
name char(5) not null,
age int,
classid int,
foreign key(classid) references class1(id)
);
insert into students1 values(123,"bob",18,1);
insert into students1 values(234,"jack",19,2);
insert into students1 values(345,"eric",20,1);
insert into students1 values(456,"rose",21,2);
delete from class1 where id = 1; # 因有外键关联，所以删除失败
# ERROR 1451 (23000): Cannot delete or update a parent row:
# a foreign key constraint fails (`db0617`.`students1`, CONSTRAINT `students1_ibfk_1`
# FOREIGN KEY (`classid`) REFERENCES `class1` (`id`))
delete from students1 where classid = 1; # 先删除关联数据
delete from class1 where id = 1;
mysql> select * from class1;
+------+-----------+
| id | classname |
+------+-----------+
| 2 | li |
+------+-----------+
1 row in set (0.00 sec)
mysql> select * from students1;
+-----+------+------+---------+
| id | name | age | classid |
+-----+------+------+---------+
| 234 | jack | 19 | 2 |
| 456 | rose | 21 | 2 |
+-----+------+------+---------+
联级删除，联级更新（谨慎操作）
create table class2(id int unique , classname varchar(255));
insert into class2 values(1,"py");
insert into class2 values(2,"li");
create table students2(
id int primary key auto_increment,
name char(5) not null,
age int,
classid int,
foreign key(classid) references class2(id) on delete cascade on update cascade
);
insert into students2 values(123,"bob",18,1);
insert into students2 values(234,"jack",19,2);
insert into students2 values(345,"eric",20,1);
insert into students2 values(456,"rose",21,2);
# 联级删除
delete from students2 where classid = 1;
# 联级更新
update class2 set id = 3 where classname = "li";
mysql> select * from class2;
+------+-----------+
| id | classname |
+------+-----------+
| 1 | py |
| 3 | li |
+------+-----------+
2 rows in set (0.00 sec)
mysql> select * from students2;
+-----+------+------+---------+
| id | name | age | classid |
+-----+------+------+---------+
| 234 | jack | 19 | 3 |
| 456 | rose | 21 | 3 |
+-----+------+------+---------+
关于约束的添加和删除
(1) 添加/删除 约束 not null
alter table 表名 modify 字段名 类型
alter table t1 modify id int not null
alter table t1 modify id int
(2) 添加/删除 unique 唯一索引
alter table 表名 add unique(id)
alter table t1 add unique(id)
alter table t1 drop index id
(3) 添加/删除 primary key
alter table 表名 add primary key(id);
alter table t1 add primary key(id);
alter table t1 drop primary key;
(4) 添加/删除 foreign key 外键 (先通过desc 表 找到外键名字,然后再删)
alter table student1 drop foreign key student1_ibfk_1; #删除
alter table student1 add foreign key(classid) references class1(id) #添加
存储引擎 : 存储数据的结构方式
show engines; 查看存储引擎
# 概念理解:
表级锁 : 如果有人修改了当前这个表,会直接上表锁,其他人无法修改,在编辑数据时候,速度慢,不能高并发(MyISAM)
行级锁 : 如果有人修改了当前这个表中的一条记录,当前这个数据记录会上锁,其他数据仍然可以正常修改,速度快,允许更高的并发(InnoDB)
支持事务处理 : 如果执行sql语句,在全部成功之后,在选择提交数据,有一条失败,立刻回滚,恢复成原来状态.
begin : 开始事务
commit : 提交数据
rollback: 回滚数据
InnoDB : 5.6版本后的默认存储引擎,支持事务处理,行级锁,外键
MyISAM : 5.6版本前的默认存储引擎,支持表级锁
MEMORY : 把数据放在内存中,用做缓存
BLACKHOLE : 黑洞,用来同步主从数据库中的数据.场景发生在服务器并发集群,用在主从数据库当中[主数据库:增删改,从数据库:查询]
D:\MySQL5.7\mysql-5.7.25-winx64\data\db0617
create table myisam1(id int,name char(10)) engine = myisam;
myisam1.frm 表结构
myisam1.MYD 表数据
myisam1.MYI 表索引
create table innodb1(id int,name char(10)) engine = innodb;
innodb1.frm 表结构
innodb1.ibd 表数据+表索引
create table memory1(id int,name char(10)) engine = memory;
memory1.frm 表结构
没有表数据文件,因为把数据存放在内存中了.
create table blackhole1(id int,name char(10)) engine = blackhole;
blackhole1.frm 表结构
