初识数据库 II

时间:2022-06-07 03:00:37

1.今日内容

  1. 搜索引擎介绍
    • innodb
    • myisam
    • memory
  2. mysql中的数据类型
    • 数据 int tinyint float
    • 时间 datetime date time
    • 字符串 char varchar
    • enum set
  3. 表的完整性约束
    • 约束某一个字段
    • 无符号的 unsigned
    • 不能为空. Not null
    • 默认值. defauit
    • 唯一约束. unique
    • 主键. primary key

2.具体内容

  1. 搜索引擎介绍

    • 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索引,但是一旦服务关闭,表中的数据就会丢失
  2. 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 1

    datetime示例

    #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
    
  3. 表的完整性约束

    • 约束某一个字段
    • 无符号的 unsigned
    • 不能为空. Not null
    • 默认值. defauit
    • 唯一约束. unique
    • 主键. primary key