库===>文件夹
表===>文件
现实生活中我们用来存储数据的文件有不同的类型,每种文件类型对应各自不同的处理机制:比如处理文本用txt类型,处理表格用excel,处理图片用png等
数据库中的表也应该有不同的类型,表的类型不同,会对应mysql不同的存取机制,表类型又称为存储引擎。
在Oracle 和SQL Server等数据库中只有一种存储引擎,所有数据存储管理机制都是一样的。而MySql
数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎,用户也可以根据
自己的需要编写自己的存储引擎
Innodb: 默认的存储引擎 查询速度较myisam慢 但是有行锁更安全
myisam: mysql 老版本用的存储引擎,有索引速度快
memory: 内存引擎(数据全部存在内存中,重启数据清空)
blackhole: 无论存什么 都立马消失(黑洞)
show engines; 或者 show engines\G #查看所有支持的存储引擎 show variables like ‘storage_engine%‘; #查看正在使用的存储引擎
#InnoDB 存储引擎 支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其 特点是行锁设计、支持外键,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。 从 MySQL 5.5.8 版本开始是默认的存储引擎。 InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用 于建立其表空间。 InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。 对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。 InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解 InnoDB 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。 #MyISAM 存储引擎 不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统 与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。究其根 本,这也并不难理解。用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗?此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。 #NDB 存储引擎 2003 年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。 #Memory 存储引擎 正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。 #Infobright 存储引擎 第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。 #NTSE 存储引擎 网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。 #BLACKHOLE 黑洞存储引擎,可以应用于主备复制中的分发主库。 MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。如果 你喜欢,完全可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅 力所在。
创建一个db1库,分别使用innodb,myisam,memory,blackhole存储引擎建四个表:
create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=memory;
create table t4(id int)engine=blackhole;
查看data文件夹下库db1中生成数据发现生成以下几个文件:
db.opt
t1.frm t1.ibd 2
t2.MYD t2.MYI t2.frm 3
t3.frm 1
t4.frm 1
#发现后两种存储引擎只有表结构,无数据
#在每个表中插入一个数据,然后查看表中记录发现t1,t2 ,t3 中都有数据,重新启动mysql后再查发现只剩下t1, t2 中有数据
#创建表的完整语法 create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] );
#注意: 1.字段名和类型必须,宽度和约束条件可选 2.同一张表中字段名不能重复 3.最后一个字段后面不能加逗号,如: create table t6(id int,name char,);
create table userinfo(name char);
insert into userinfo values(‘jason‘);
当超过宽度后会出现两种情况:
1.自动截取: 没有安全模式的数据库版本,mysql会帮你自动截取‘j‘进行存储
2.直接报错: 最新数据库版本无严格模式,插入时直接报错,提示无法存储:Data too long for column ‘name‘ at row 1
使用数据库的准则:能尽量让它少干活就尽量少干活(不然数据量大时会崩溃的,因此有严格模式好)
create table t1(id int,name char); #默认可空
alter table t1 modify name char not null; #限制不能为空
insert into t1 values(1,‘j‘); # 正常存储
insert into t1 values(2,null); # 报错
约束条件:约束条件是在类型之外添加一种额外的限制
存储数据时超过它们的最大存储长度,发现数据也能正常存储进去,只是mysql帮我们自动截取了最大长度。但在实际情况下,我们应该尽量减少数据库的操作,缓解数据库的压力,让它仅仅只管理数据即可,这样的情况下就需要设置安全模式
模糊匹配:
like 像......的
%匹配任意多个字符
_匹配任意一个字符
show variables like "%mode%"; # 查看数据库配置中变量名包含mode的配置参数
# 修改安全模式 set session # 只在当前操作界面有效 set global # 全局有效 set global sql_mode =‘STRICT_TRANS_TABLES‘ # 修改完之后退出当前客户端重新登陆即可
整数类型:TINYINT SMALLINT MEDIUMINT INT BIGINT
每种类型都有自己的字节大小,也就决定了有固定的存储长度,见下表:
第一点 : 各类型默认是带有符号的,可以类型后加unsigned手动声明为不带符号的
第二点: 你输入的宽度如果超出限制,宽度值自动变为最大值或者最小值
# tinyint 为例进行验证
=========有符号和无符号tinyint========== #tinyint默认为有符号 MariaDB [db1]> create table t1(x tinyint); #默认为有符号,即数字前有正负号 MariaDB [db1]> desc t1; MariaDB [db1]> insert into t1 values -> (-129), -> (-128), -> (127), -> (128); MariaDB [db1]> select * from t1; +------+ | x | +------+ | -128 | #-129存成了-128 | -128 | #有符号,最小值为-128 | 127 | #有符号,最大值127 | 127 | #128存成了127,证明默认是有符号的,超出后显示最大值 +------+ #设置无符号tinyint MariaDB [db1]> create table t2(x tinyint unsigned); MariaDB [db1]> insert into t2 values -> (-1), -> (0), -> (255), -> (256); MariaDB [db1]> select * from t2; +------+ | x | +------+ | 0 | -1存成了0 | 0 | #无符号,最小值为0 | 255 | #无符号,最大值为255 | 255 | #256存成了255 +------+
============有符号和无符号int============= #int默认为有符号 MariaDB [db1]> create table t3(x int); #默认为有符号整数 MariaDB [db1]> insert into t3 values -> (-2147483649), -> (-2147483648), -> (2147483647), -> (2147483648); MariaDB [db1]> select * from t3; +-------------+ | x | +-------------+ | -2147483648 | #-2147483649存成了-2147483648 | -2147483648 | #有符号,最小值为-2147483648 | 2147483647 | #有符号,最大值为2147483647 | 2147483647 | #2147483648存成了2147483647 +-------------+ #设置无符号int MariaDB [db1]> create table t4(x int unsigned); MariaDB [db1]> insert into t4 values -> (-1), -> (0), -> (4294967295), -> (4294967296); MariaDB [db1]> select * from t4; +------------+ | x | +------------+ | 0 | #-1存成了0 | 0 | #无符号,最小值为0 | 4294967295 | #无符号,最大值为4294967295 | 4294967295 | #4294967296存成了4294967295 +------------+
==============有符号和无符号bigint============= MariaDB [db1]> create table t6(x bigint); MariaDB [db1]> insert into t5 values -> (-9223372036854775809), -> (-9223372036854775808), -> (9223372036854775807), -> (9223372036854775808); MariaDB [db1]> select * from t5; +----------------------+ | x | +----------------------+ | -9223372036854775808 | | -9223372036854775808 | | 9223372036854775807 | | 9223372036854775807 | +----------------------+ MariaDB [db1]> create table t6(x bigint unsigned); MariaDB [db1]> insert into t6 values -> (-1), -> (0), -> (18446744073709551615), -> (18446744073709551616); MariaDB [db1]> select * from t6; +----------------------+ | x | +----------------------+ | 0 | | 0 | | 18446744073709551615 | | 18446744073709551615 | +----------------------+
特例:只有整型后面的数字不是用来限制存储数据的长度 而是用来控制展示的数据的位数,大于等于则正常显示,不够位数默认填充空格
如: int(8) 够 / 超过 8位有几位存几位,不够8位空格填充
默认的显示宽度,都是在最大值的基础上加1,足够显示完整当初存放的数据,所以在创建表时,如果字段采用的是整型类型,完全无需指定显示宽度(如果发现有人写了宽度,一定记得去调侃)
#为了验证不够的情况是空格填充,我们可以修改默认约束条件,使用0来填充,便于查看填充结果:
======用zerofill测试整数类型的显示宽度============= MariaDB [db1]> create table t7(x int(3) zerofill); #修改默认的空格填充,便于查看 MariaDB [db1]> insert into t7 values -> (1), -> (11), -> (111), -> (1111); MariaDB [db1]> select * from t7; +------+ | x | +------+ | 001 | | 011 | | 111 | | 1111 | #超过宽度限制仍然可以存,是多少显示多少 +------+
类型:float double decimal
精度: float < double < decimal
注意:如果追求高精度存储,完全没必要纠结用哪一种浮点类型,直接存字符串就好了
#三种类型最大整数位和小数位: 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)); #第二步 同一传入30位小数 insert into t12 values(1.111111111111111111111111111111); insert into t13 values(1.111111111111111111111111111111); insert into t14 values(1.111111111111111111111111111111);
mysql> select * from t12; +----------------------------------+ | id | +----------------------------------+ | 1.111111164093017600000000000000 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from t13; +----------------------------------+ | id | +----------------------------------+ | 1.111111111111111200000000000000 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from t14; +----------------------------------+ | id | +----------------------------------+ | 1.111111111111111111111111111111 | +----------------------------------+ 1 row in set (0.00 sec)
类型 : char varchar
char与varchar的使用区别
char定长
1.浪费空间
2.存取速度快,并且取的时候按固定长度取即可
egon alex lxx jxx txx
varchar变长
1.节省空间
2.存取速度慢(较于char比较慢)
存的时候 需要给数据加一个记录长度的报头
取的时候 取的时候比较繁琐了 无法知道数据到底多长,需要先读取报头才能读取真实数据
1bytes+egon 1bytes+alex 1bytes+lxx 1bytes+jxx 1bytes+txx
之前char用的多,现在varchar也越来越多人使用,用什么看经理要求
create table t10(name char(4)); # 超出四个字符报错(没有严格模式情况下自动截取),不够四个字符空格补全 create table t11(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 # 都显示‘a’无法查看真正的结果 select char_length(name) from t12 select char_length(name) from t13 # 都显示长度为1,仍然无法查看到真正的结果 """首先应该肯定的是在硬盘上存的绝对是真正的数据,但显示的时候mysql会自动将末尾的空格取掉""" # 如果不想让mysql帮你做自动去除末尾空格的操作,需要再添加一个模式 set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH"; # 退出客户端重新登陆 select char_length(name) from t12; #4 select char_length(name) from t13; #1
date:2019-05-01
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填充 """
#方式1
insert into t17 values(1,‘tank‘,123);
常规在values后面插入数据时必须和建表时的数据顺序和个数一一对应:
#方式2
insert into t17(name,id) values(‘egon‘,2);
其实可以在表名后指定字段进行插入,不需要全部都插,并且顺序可以乱,只要前后一一对应
not null 不能为空
default 给某个字段设置默认值(当用户写了的时候用用户的,当用户没有写就用默认值)
#not null
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); # 报错 不能传空
#default 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‘) # 成功 不传使用默认的‘male‘
单列唯一 限制某一个字段是唯一的
联合唯一 (在语句的最后 用括号的形式 表示哪几个字段组合的结果是唯一的,比如id和port,单独可以重复,不能一起重复)
# 单列唯一 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);
create table t11(id int primary key); desc t11; insert into t11 values(1),(1); # 报错 insert into t11 values(1),(2);
!!!!!!!!!!!!!!!!!ps:innodb引擎中一张表有且只有一个主键!!!!!!!!!!!!!!!!!!!!!!
1.一张表中必须有且只有一个主键,如果你没有设置主键,那么会从上到下搜索直到遇到一个非空且唯一的字段自动将其设置为主键 """ create table t12( id int, name char(16), age int not null unique, addr char(16) not null unique )engine=innodb; desc t12; """
2.如果表里面没有指定任何的可以设置为主键的字段,那么innodb会采用自己默认的一个隐藏字段作为主键,隐藏意味着你在查询的时候无法根据这个主键字段加速查询了 索引:类似于书的目录,没有主键就相当于一页一页翻着查
3.一张表中通常都应该有一个id字段,并且通常将改id字段作成主键
多个字段联合起来作为表的一个主键,本质还是一个主键
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 from tb1; 强调:上面的这条命令确实可以将表里的所有记录都删掉,但不会将id重置为0, 所以收该条命令根本不是用来清空表的,delete是用来删除表中某一些符合条件的记录 delete from tb1 where id > 10; 如果要清空表,使用truncate tb1; 作用:将整张表重置,id重新从0开始记录
原文:https://www.cnblogs.com/xp1315458571/p/11379294.html