一 引擎
引擎决定了数据库存取数据的方式，不同的引擎具有不同的特点，提供给用户不同的体验。需要注意的是引擎是建表的规定，提供给表使用的，不是数据库。
show engines; #展示所有的引擎 #重点： #innodb(默认):支持事务，行级锁，外键 #myisam:查询效率要优于innodb，当不需要支持事务，行级锁，外键时可以通过设置引擎为myisam来优化数据库。 #blackhole：黑洞引擎，会将所有存储的数据都吃掉，即可以将没有用的数据存在引擎为blackhole的表中。 #memory：内存引擎，会将数据暂时存在内存中，断开电源或者重启数据库后，数据就没有了。
二 数据库中创建表的完整语法
create table 表名（ 字段名1 类型 [（宽度） 约束条件] #[]指可选参数 字段名2 类型 [（宽度） 约束条件] 字段名3 类型 [（宽度） 约束条件] ）engine=innodb charset = "utf8"; 类型：存储数据的方式 宽度和约束条件为可选参数，用来限制存放数据的规则。 eg： create table db1.t1(name char(3) not null) #上述语句：在db1数据库下建立一张插入数据时，name不能为空，且name最长只能存放三个字符的表。
三 数据库的模式（sql_mode）
数据库模式限制的是客户端对服务器操作数据的方式是否严格。
数据库中有两种模式：1.非安全模式：no_engine_substitution（mysql 5.7版本之前默认模式）
2.安全模式：strict_trans_tables （mysql 5.7版本之后的默认模式）
#查看当前数据库的模式： show variables like "%sql_mode%"; #匹配0~n个任意字符，模糊查询 #修改当前数据库模式为安全模式 set global sql_mode ="strict_trans_tables"; #需要重启连接（重启客户端）后生效 quit; #应用： 在安全模式下： create table t1(name char(2)); insert into t1 values('ab') #正常 insert into t1 values('abcd') #错误：Data too long for column 'name' at row 1 在非安全模式下： create table t1(name char(2)); insert into t1 values('abcd') #Query OK, 1 row affected, 1 warning (0.05 sec) #数据存储成功，但是只存储了前两个字符的数据 ab 总结：在非安全模式下，如果不按规则存储数据，不会出错，例如：存储的数据长度超出要求只会存储满足要求的前几位字符；在非安全模式下，不按规则存储数据，会报错，无法存储数据。
四 数据类型
mysql数据库支持存放的数据类型有哪些呢？
# 整型 |浮点型 |字符型 |时间类型 |枚举类型 |集合类型
整型
'''类型 tinyint : 1字节 即 -128~127 smallint : 2字节 （自己算去） mediumint : 3字节 （同上） int : 4字节 -2147483648~2147483647 bigint ： 8字节 ''' '''约束 unsigned : 无符号 zerofill ： 0填充 ''' #不同类型所占字节数不一样，决定所占空间及存放数据的大小限制 eg: create table t8（x tinyint ）; insert into t8 values(200); #非安全模式下存入，值只能到最大值127 select x from t8；
结果： mysql> select x from t8; +------+ | x | +------+ | 127 | +------+ 1 row in set (0.00 sec) ===========================
'''宽度 1.不能决定整型存放数据的宽度，超过宽度可以存放，最终由数据类型锁占字节决定 2.如果没有超过宽度，且有zerofile限制，会用0填充前置位的不足位 3.没有必要规定整型的宽度，默认设置的宽度就为该整型能存放数据的最大宽度 ''' eg1: create table t9(x int(5)); insert into t9 values(123456); select (x) from t9; # 结果: 123456 insert into t9 values(2147483648); select (x) from t9; # 结果: 2147483647 insert into t9 values(2147483648); select (x) from t9; # 结果: 2147483647 #非安全模式下 insert into t9 values(10); select (x) from t9; # 结果: 10 +------------+ | x | +------------+ | 123456 | | 2147483647 | | 2147483647 | | 10 | +------------+ eg2: create table t10(x int(5) unsigned zerofill); # 区域 无符号位：0~2**32-1 = 0~4294967295 insert into t10 values(10); select x from t10; # 结果: 00010 insert into t10 values(12345678900); select x from t10; # 结果: 4294967295 +------------+ | x | +------------+ | 00010 | | 4294967295 | +------------+
浮点型
'''类型 float : 4字节，3.4E -38~3.4E+38 double : 8字节 ，1.7E-308~1.7E+308 decimal : M,D大值基础上加2 ''' '''宽度 限制存储宽度 （M,D）=> M为位数，D为小数位 float(255,30):精度低，但是最常用 double(255,30)：精度高，占位多 decimal（65,30）：以字符形式存，全精度 ''' eg1: create table t11 (age float(256, 30)); # Display width out of range for column 'age' (max = 255) create table t11 (age float(255, 31)); # Too big scale 31 specified for column 'age'. Maximum is 30. eg2: create table t12 (x float(255, 30)); create table t13 (x double(255, 30)); create table t14 (x decimal(65, 30)); insert into t12 values(1.11111111111111111111); insert into t13 values(1.11111111111111111111); insert into t14 values(1.11111111111111111111); select * from t12; # 1.111111164093017600000000000000 => 小数据,精度要求不高, 均采用float来存储 * mysql> select * from t12; +----------------------------------+ | x | +----------------------------------+ | 1.111111164093017600000000000000 | +----------------------------------+ #======================================= select * from t13; # 1.111111111111111200000000000000 mysql> select * from t13; +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111200000000000000 | +----------------------------------+ #======================================= select * from t14; # 1.111111111111111111110000000000 mysql> select * from t14; +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111111110000000000 | +----------------------------------+ #======================================= alter table t14 modify x decimal(10, 5); # 1.11111 => 限制了数据的存储宽度 mysql> select * from t14; +---------+ | x | +---------+ | 1.11111 | +---------+
字符型
'''类型 char : 定长 varchar : 不定长 ''' '''宽度 限制存储宽度 char(4) : 以4个字符存储以char属性存储的数据 varchar(4) : 数据长度决定字符长度，为可变长度存储数据，不依赖宽度 ''' eg： create table t15 (x char(4), y varchar(4)); insert into t15 values("zero", 'owen'); # '' | "" 均可以表示字符 select x,y from t15; # 正常 mysql> select x,y from t15; +------+------+ | x | y | +------+------+ | zero | owen | +------+------+ #=============================================== insert into t15 values("yanghuhu", 'lxxVSegon'); # 非安全模式数据丢失,可以存放, 安全模式报错 select x,y from t15; # 可以正常显示丢失后(不完整)的数据 mysql> select x,y from t15; +------+------+ | x | y | +------+------+ | zero | owen | | yang | lxxV | +------+------+ #================================================= insert into t15 values('a', 'b'); # 验证数据所在字符长度 # 前提: 安全模式下以空白填充字符 set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH"; # 重启连接 select char_length(x), char_length(y) from t15; # a占4 b占1 mysql> select char_length(x),char_length(y) from t15; +----------------+----------------+ | char_length(x) | char_length(y) | +----------------+----------------+ | 4 | 4 | | 4 | 4 | | 4 | 1 | +----------------+----------------+
'''重点：存储数据的方式 即 数据库的优化 char : 一定按规定的宽度存放数据，以规定宽度读取数据，通常更占空间 varchar : 首先根据数据长度计算所需宽度，并在数据开头以数据头方式将宽度信息保存起来，是一个计算耗时过程，取数据时，首先读取宽度信息，以宽度信息为依准读取数据，通常节省空间。 ''' 例如：（在安全模式的添空白模式下） 以char(8)为字段属性创建一个表，为其添加如下数据(zero、egon、lxx、yanghuhu) 那么查询该表数据结果为： zero(空4格) egon（空4格） lxx（空5格） yanghuhu 以varchar(8)为字段属性创建一个表，为其添加如上数据： 查询结果如下： 4zero 4egon 3lxx 8yanghuhu #注意： varchar的数据头占1~2字节，规定char|varchar宽度均为4，用来存放4个字符的数据，cahr存取更加高效，char占4字符，varchar占5字符，char更省空间。 总结：数据长度相近的数据提倡用char来存放数据，数据需要告诉存取，以空间换时间，采用char。
时间类型
'''类型 year : yyyy(1901/2155) date : yyyy-MM-dd（1000-01-01/9999-12-31） time : HH:mm:ss datetime : yyyy-MM-dd HH:mm:ss（1000-01-01 00:00:00/9999-12-31 23:59:59） timestamp : yyyy-MM-dd HH:mm:ss （1970-01-01 00:00:00/2038-01-19 ??） ''' eg1: create table t16(my_year year, my_date date, my_time time); dedc t16; mysql> desc t16; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | my_year | year(4) | YES | | NULL | | | my_date | date | YES | | NULL | | | my_time | time | YES | | NULL | | +---------+---------+------+-----+---------+-------+ #============================================ insert into t16 values(); # 三个时间类型的默认值均是null +---------+---------+---------+ | my_year | my_date | my_time | +---------+---------+---------+ | NULL | NULL | NULL | +---------+---------+---------+ #============================================== insert into t16 values(2156, null, null); # Out of range value for column 'my_year' at row 1 在时间范围外,不允许插入该数据 insert into t16 values(1, '2000-01-01 12:00:00', null); # 2001 2000-01-01 null +---------+------------+---------+ | my_year | my_date | my_time | +---------+------------+---------+ | NULL | NULL | NULL | | 2001 | 2000-01-01 | NULL | +---------+------------+---------+ #================================================ insert into t16 values(2019, '2019-01-08', "15-19-30"); # time报格式错误 => 按照时间规定格式存放数据 Incorrect time value: '15-19-30' for column 'my_time' at row 1 alter table t16 change my_year myYear year(2); # 时间的宽度修改后还是采用默认宽度 => 不需要关系宽度 +--------+------------+---------+ | myYear | my_date | my_time | +--------+------------+---------+ | NULL | NULL | NULL | | 2001 | 2000-01-01 | NULL | +--------+------------+---------+
枚举与集合
'''类型 enum : 单选 （枚举） set : 多选 （集合） ''' eg: create table t19( sex enum('male','female','wasai') not null default 'wasai', # 枚举 hobbies set('play','read','music') # 集合 ); insert into t19 values (null, null); # sex不能设置null Column 'sex' cannot be null insert into t19 values (); # wasai null +-------+---------+ | sex | hobbies | +-------+---------+ | wasai | NULL | +-------+---------+ #================================================== insert into t19 (hobbies) values ('play,read'), ('music,play'); # sex采用默认值, 对hobbies字段添加两条记录 +-------+------------+ | sex | hobbies | +-------+------------+ | wasai | NULL | | wasai | play,read | | wasai | play,music | +-------+------------+ #=============================================== insert into t19 (sex,hobbies) values ('male,female', 'play'); # sex字段只能单选 Data truncated for column 'sex' at row 1
五 约束条件
''' primary key : 主键，唯一标识，表都会拥有，不设置为默认找表中第一个不是null，且唯一的字段，如果表中不存在这样的字段，那么会创建隐藏字段作为该表的主键。 foreing key 外键 unique key :唯一数据，该条字段的值需要保证唯一，不能重复 auto_increment : 自增，只能嫁给key字段辅助修饰 not bull : 不为空 default : 默认值 unsigned ： 无符号 zerofill : 0填充 ''' 唯一数据 unique 注意： 1.键可以提高数据的存取效率，提高IO 2.联合唯一： create table web ( ip char(16), port int, unique(ip,port) ); 3.联合主键： create table web ( ip char(16), port int, primary key(ip,port) ); eg1: #单列唯一 create table t20 ( id int unique ); #联合唯一 create table web ( ip char(16), port int, unique(ip,port) ); # 如果联合两个字段,两个字段全相同才相同,否则为不同 insert into web values ('10.10.10.10', 3306), ('10.10.10.10', 3306); #报错，无法添加数据，因为不符合数据唯一性这个要求，错误信息为：Duplicate entry '10.10.10.10 -3306' for key 'ip' insert into web values ('10.10.10.10', 3306), ('10.10.10.11', 3306); #添加成功 +------------------+------+ | ip | port | +------------------+------+ | 10.10.10.10 | 3306 | | 10.10.10.11 | 3306 | +------------------+------+ #=========================================
主键 primary key 注意： 1.表默认都有主键, 且只能拥有一个主键字段(单列主键 | 联合主键) 2.没有设置主键的表, 数据库系统会自上而下将第一个规定为unique not null字段自动提升为primary key主键 3.如果整个表都没有unique not null字段且没有primary key字段, 系统会默认创建一个隐藏字段作为主键 4.通常必须手动指定表的主键, 一般用id字段, 且id字段一般类型为int, 因为int类型可以auto_increment 自增 eg:2 create table t21(id int auto_increment); # 报错：自增约束必须添加给key的字段 信息：Incorrect table definition; there can be only one auto column and it must be defined as a key # eg:3 create table t21(id int primary key auto_increment); # 自增要结合key,不赋值插入,数据会自动自增, 且自增的结果一直被记录保留 成功 insert into t21 values(); insert into t21 values(); insert into t21 values(); select * from t21; mysql> select * from t21; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ delete from t21 where id <4; insert into t21 values(); select * from t21; mysql> select * from t21; +----+ | id | +----+ | 4 | +----+ #============================================== # eg:4 # 联合主键 create table t22( ip char(16), port int, primary key(ip,port) ); # 如果联合两个字段,两个字段全相同才相同,否则为不同 insert into t22 values ('10.10.10.10', 3306), ('10.10.10.10', 3306); 报错信息：Duplicate entry '10.10.10.10 -3306' for key 'ip' insert into t22 values ('10.10.10.10', 3306), ('10.10.10.11', 3306); +------------------+------+ | ip | port | +------------------+------+ | 10.10.10.10 | 3306 | | 10.10.10.11 | 3306 | +------------------+------+ desc t22; mysql> desc t22; +-------+----------+------+-----+------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+------------------+-------+ | ip | char(16) | NO | PRI | | | | port | int(11) | NO | PRI | 0 | | +-------+----------+------+-----+------------------+-------+