1.今日内容
- 搜索引擎介绍
- innodb
- myisam
- memory
- mysql中的数据类型
- 数据 int tinyint float
- 时间 datetime date time
- 字符串 char varchar
- enum set
- 表的完整性约束
- 约束某一个字段
- 无符号的 unsigned
- 不能为空. Not null
- 默认值. defauit
- 唯一约束. unique
- 主键. primary key
2.具体内容
-
搜索引擎介绍
- innodb
1.事务（保证数字安全，数据的完整性） 2.row-level locking。行级锁 3.table-level locking。 表级锁 4.foreign keys 外键锁 5.加速查询（树型结构 表结构） MySql 5.6 版本默认的存储引擎。InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能。InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。- myisam
1.table-level locking。 表级锁 2.加速查询（树型结构 表结构） MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。- memory
1.基于hash 在内存中存储所有数据，应用于对非关键数据由快速查找的场景。Memory类型的表访问数据非常快，因为它的数据是存放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失 -
mysql中的数据类型
-
数据 int tinyint float
int示例
#int示例 #创建一个表，一个默认宽度的int，一个指定宽度的int（5） mysql> create table t1(id1 int ,id2 int(5)); Query OK, 0 rows affected (0.03 sec) #插入数据1 mysql> insert into t1 values(1,1); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; ------ ------ | id1 | id2 | ------ ------ | 1 | 1 | ------ ------ 1 row in set (0.00 sec) mysql> insert into t1 values (111111,111111); Query OK, 1 row affected (0.00 sec) #id2显示了正确的数值，没有受到宽度限制的影响 mysql> select * from t1; -------- -------- | id1 | id2 | -------- -------- | 1 | 1 | | 111111 | 111111 | -------- -------- 2 rows in set (0.00 sec) mysql> show create table t1; ------- -------------------------------------------------------------------------------------------------------------------- | Table | Create Table | ------- -------------------------------------------------------------------------------------------------------------------- | t1 | CREATE TABLE `t1` ( `id1` int(11) DEFAULT NULL, `id2` int(5) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | ------- -------------------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec) mysql> mysql> into t1 values (22222222,22222222); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; ---------- ---------- | id1 | id2 | ---------- ---------- | 1 | 1 | | 111111 | 111111 | | 22222222 | 22222222 | ---------- ---------- 3 rows in set (0.00 sec) #修改id1字段，给字段添加一个unsigned表示无符号 mysql> alter table t1 modify id1 int unsigned; Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> desc t1; ------- ------------------ ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- ------------------ ------ ----- --------- ------- | id1 | int(10) unsigned | YES | | NULL | | | id2 | int(5) | YES | | NULL | | ------- ------------------ ------ ----- --------- ------- 2 rows in set (0.00 sec) #当给id1添加的数据大于2147483647时，可以顺利插入 mysql> insert into t1 values (2147483648,2147483647); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; ------------ ------------ | id1 | id2 | ------------ ------------ | 1 | 1 | | 111111 | 111111 | | 22222222 | 22222222 | | 2147483648 | 2147483647 | ------------ ------------ 4 rows in set (0.00 sec) #当个id2添加的数据大于2147483647时，会报错 mysql> insert into t1 values (2147483648,2147483649); ERROR 1264 (22003): O小数示例
#小数示例 mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2)); Query OK, 0 rows affected (0.02 sec) mysql> desc t2; ------- -------------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- -------------- ------ ----- --------- ------- | id1 | float(5,2) | YES | | NULL | | | id2 | double(5,2) | YES | | NULL | | | id3 | decimal(5,2) | YES | | NULL | | ------- -------------- ------ ----- --------- ------- 3 rows in set (0.00 sec) mysql> insert into t2 values(1.23,1.23,1.23); Query OK, 1 row affected (0.00 sec) mysql> select * from t2; ------ ------ ------ | id1 | id2 | id3 | ------ ------ ------ | 1.23 | 1.23 | 1.23 | ------ ------ ------ 1 row in set (0.00 sec) mysql> insert into t2 values(1.234,1.234,1.234); Query OK, 1 row affected, 1 warning (0.01 sec) #向表中插入1.234，会发现4都被截断了 mysql> select * from t2; ------ ------ ------ | id1 | id2 | id3 | ------ ------ ------ | 1.23 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | ------ ------ ------ 2 rows in set (0.00 sec) mysql> insert into t2 values(1.235,1.235,1.235); Query OK, 1 row affected, 1 warning (0.00 sec) #遵循四舍五入的规则 mysql> select * from t2; ------ ------ ------ | id1 | id2 | id3 | ------ ------ ------ | 1.23 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | | 1.24 | 1.24 | 1.24 | ------ ------ ------ 3 rows in set (0.00 sec) #去掉参数约束 mysql> create table t3(id1 float,id2 double,id3 decimal); Query OK, 0 rows affected (0.01 sec) mysql> insert into t3 values(1.234,1.234,1.234); Query OK, 1 row affected, 1 warning (0.00 sec) #发现decimal默认值是（10，0）的整数 mysql> select * from t3; ------- ------- ------ | id1 | id2 | id3 | ------- ------- ------ | 1.234 | 1.234 | 1 | ------- ------- ------ 1 row in set (0.00 sec) mysql> insert into t3 values(1.2345678,1.2345678678,1.2345678); Query OK, 1 row affected, 1 warning (0.00 sec) #当对小数位没有约束的时候，输入超长的小数，发现float和double的区别 mysql> select * from t3; --------- -------------- ------ | id1 | id2 | id3 | --------- -------------- ------ | 1.234 | 1.234 | 1 | | 1.23457 | 1.2345678678 | 1 | --------- -------------- ------ 2 rows in set (0.00 sec)-
时间 datetime date time
date ，time，datetime示例
mysql> create table t4(d date,t time,dt datetime); Query OK, 0 rows affected (0.02 sec) mysql> desc t4; ------- ---------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- ---------- ------ ----- --------- ------- | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | ------- ---------- ------ ----- --------- ------- 3 rows in set (0.00 sec) mysql> insert into t4 values(now(),now(),now()); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t4; ------------ ---------- --------------------- | d | t | dt | ------------ ---------- --------------------- | 2020-01-07 | 22:12:29 | 2020-01-07 22:12:29 | ------------ ---------- --------------------- 1 row in set (0.00 sec) mysql> insert into t4 values(null,null,null); Query OK, 1 row affected (0.00 sec) mysql> select * from t4; ------------ ---------- --------------------- | d | t | dt | ------------ ---------- --------------------- | 2020-01-07 | 22:12:29 | 2020-01-07 22:12:29 | | NULL | NULL | NULL | ------------ ---------- --------------------- 2 rows in set (0.00 sec)timestamp示例
#timestamp示例 mysql> create table t5 (id1 timestamp); Query OK, 0 rows affected (0.01 sec) mysql> desc t5; ------- ----------- ------ ----- ------------------- ----------------------------- | Field | Type | Null | Key | Default | Extra | ------- ----------- ------ ----- ------------------- ----------------------------- | id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | ------- ----------- ------ ----- ------------------- ----------------------------- 1 row in set (0.00 sec) #插入数据null，会自动插入当前时间的时间 mysql> insert values(null); Query OK, 1 row affected (0.00 sec) mysql> select * from t5; --------------------- | id1 | --------------------- | 2020-01-07 22:15:26 | --------------------- 1 row in set (0.00 sec) mysql> alter table t5 add id2 timestamp; ERROR 1067 (42000): Invalid default value for 'id2' #设置默认时间为当前的时间 mysql> alter table t5 add id2 timestamp default current_timestamp; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from t5; --------------------- --------------------- | id1 | id2 | --------------------- --------------------- | 2020-01-07 22:15:26 | 2020-01-07 22:21:41 | --------------------- --------------------- 1 row in set (0.00 sec) mysql> show create table t5; ------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Table | Create Table | ------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | t5 | CREATE TABLE `t5` ( `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | ------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec) mysql> mysql> inser t5 values (null,null); Query OK, 1 row affected (0.00 sec) mysql> select * from t5; --------------------- --------------------- | id1 | id2 | --------------------- --------------------- | 2020-01-07 22:15:26 | 2020-01-07 22:21:41 | | 2020-01-07 22:23:04 | 2020-01-07 22:23:04 | --------------------- --------------------- 2 rows in set (0.00 sec) mysql> insert into t5 values (19700101080001); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> insert into t5 values (19700101080001,null); Query OK, 1 row affected (0.01 sec) mysql> select * from t5; --------------------- --------------------- | id1 | id2 | --------------------- --------------------- | 2020-01-07 22:15:26 | 2020-01-07 22:21:41 | | 2020-01-07 22:23:04 | 2020-01-07 22:23:04 | | 1970-01-01 08:00:01 | 2020-01-07 22:25:39 | --------------------- --------------------- 3 rows in set (0.00 sec) #timestamp时间下限是19700101080001 mysql> insert into t5 values (19700101080000,null); ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column 'id1' at row 1 mysql> insert into t5 values('2038-01-19 11:14:07',null); Query OK, 1 row affected (0.00 sec) mysql> select * from t5; --------------------- --------------------- | id1 | id2 | --------------------- --------------------- | 2020-01-07 22:15:26 | 2020-01-07 22:21:41 | | 2020-01-07 22:23:04 | 2020-01-07 22:23:04 | | 1970-01-01 08:00:01 | 2020-01-07 22:25:39 | | 2038-01-19 11:14:07 | 2020-01-07 22:27:09 | --------------------- --------------------- 4 rows in set (0.00 sec) #timestamp时间的上限是2038-01-19 11：14：07 mysql> insert into t5 values('2038-01-19 11:14:08',null); ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 'id1' at row 1datetime示例
#datetime示例 mysql> create table t6 (dt datetime); Query OK, 0 rows affected (0.01 sec) mysql> desc t6; ------- ---------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- ---------- ------ ----- --------- ------- | dt | datetime | YES | | NULL | | ------- ---------- ------ ----- --------- ------- 1 row in set (0.00 sec) mysql> insert into t6 values('2019-9-2 12:20:11'); Query OK, 1 row affected (0.00 sec) mysql> insert into t6 values('2018/9/2 12:20:11'); Query OK, 1 row affected (0.01 sec) mysql> insert into t6 values ('20180902122011'); Query OK, 1 row affected (0.00 sec) mysql> select * from t6; --------------------- | dt | --------------------- | 2019-09-02 12:20:11 | | 2018-09-02 12:20:11 | | 2018-09-02 12:20:11 | --------------------- 3 rows in set (0.00 sec)-
字符串 char varchar
char varchar示例
mysql> create table t7 (v varchar(4),c char(4)); Query OK, 0 rows affected (0.01 sec) mysql> desc t7; ------- ------------ ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- ------------ ------ ----- --------- ------- | v | varchar(4) | YES | | NULL | | | c | char(4) | YES | | NULL | | ------- ------------ ------ ----- --------- ------- 2 rows in set (0.00 sec) mysql> insert into t7 values ('ab ','ab '); Query OK, 1 row affected (0.00 sec) #在检索的时候char数据类型会去掉空格 mysql> select * from t7 ; ------ ------ | v | c | ------ ------ | ab | ab | ------ ------ 1 row in set (0.00 sec) #查询结果计算的长度 mysql> select length(v),length(c) from t7; ----------- ----------- | length(v) | length(c) | ----------- ----------- | 4 | 2 | ----------- ----------- 1 row in set (0.02 sec) #给结构拼上一个加号 mysql> select concat(v,' '),concat(c,' ') from t7; --------------- --------------- | concat(v,' ') | concat(c,' ') | --------------- --------------- | ab | ab | --------------- --------------- 1 row in set (0.00 sec) #当存储的长度超出定义的长度，会截断 mysql> insert into t7 values ('abcd ','abcd '); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select concat(v,' '),concat(c,' ') from t7; --------------- --------------- | concat(v,' ') | concat(c,' ') | --------------- --------------- | ab | ab | | abcd | abcd | --------------- --------------- 2 rows in set (0.00 sec)-
enum set
enum set示例
mysql> create table t8(name char(20),gender enum('female','male')); Query OK, 0 rows affected (0.02 sec) mysql> desc t8; -------- ----------------------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | -------- ----------------------- ------ ----- --------- ------- | name | char(20) | YES | | NULL | | | gender | enum('female','male') | YES | | NULL | | -------- ----------------------- ------ ----- --------- ------- 2 rows in set (0.00 sec) # 选择enum('female','male')中的一项作为gender的值，可以正常插入 mysql> insert into t8 values ('chen','male'); Query OK, 1 row affected (0.00 sec) mysql> select * from t8; ------ -------- | name | gender | ------ -------- | chen | male | ------ -------- 1 row in set (0.00 sec) # 不能同时插入('female','male')两个值，也不能插入不属于('female','male')的值 mysql> insert into t8 values ('chen','male,female'); ERROR 1265 (01000): Data truncated for column 'gender' at row 1 mysql> create table t9 (name char(20),hobby set('看书','打乒乓球','玩游戏','旅游')); Query OK, 0 rows affected (0.02 sec) mysql> desc t9; ------- --------------------------------------------------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- --------------------------------------------------- ------ ----- --------- ------- | name | char(20) | YES | | NULL | | | hobby | set('看书','打乒乓球','玩游戏','旅游') | YES | | NULL | | ------- --------------------------------------------------- ------ ----- --------- ------- 2 rows in set (0.01 sec) # 可以任意选择set('看书','打乒乓球','玩游戏','旅游')中的项，并带去重功能 mysql> insert into t9 values ('chen','看书,玩游戏')； -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '；' at line 1 mysql> insert into t9 values ('chen','看书,玩游戏'); Query OK, 1 row affected (0.01 sec) mysql> select * from t9; ------ ------------------ | name | hobby | ------ ------------------ | chen | 看书,玩游戏 | ------ ------------------ 1 row in set (0.00 sec) mysql> insert into t9 values ('chen','看书,看书,看书,旅游'); Query OK, 1 row affected (0.00 sec) mysql> select * from t9; ------ ------------------ | name | hobby | ------ ------------------ | chen | 看书,玩游戏 | | chen | 看书,旅游 | ------ ------------------ 2 rows in set (0.00 sec) # 不能选择不属于set('看书','打乒乓球','玩游戏','旅游')中的项 mysql> insert into t9 values('chen','打游戏,听歌'); ERROR 1265 (01000): Data truncated for column 'hobby' at row 1 -
-
表的完整性约束
- 约束某一个字段
- 无符号的 unsigned
- 不能为空. Not null
- 默认值. defauit
- 唯一约束. unique
- 主键. primary key