四十五、MySQL数据库2

2021年9月7日 2点热度 0条评论

今日内容概要

今日内容详细

  • 存储引擎

  日常生活中文件格式有很多种,针对不同的文件格式会有不同的存储方式和处理机制(txt/pdf/mp3/mp4)

  针对不同的数据,应该也要有不同的处理机制来存储

  存储引擎就是不同的处理机制

  MySQL主要存储引擎

    • Innodb

        是MySQL5.5版本之后默认的存储引擎

        存储数据更加安全

    • MyIsam

        是MySQL5.5版本之后默认的存储引擎

        速度要比Innodb更快,但是我们更加注重的是数据的安全

    • memory

        内存引擎(数据全部存放在内存中),断电数据丢失

    • blackhole

        无论存什么数据,都立刻消失(黑洞)

"""
#查看所有引擎
show engines;

#创建库
create database day45;

#切换到day45库下
use day45;

#用不同的存储引擎在存储表的时候,有什么异同点
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;

#执行以上四条语句之后,会在day45中生成4张表
Innodb
t1.frm     #frm 表结构
t1.ibd      #ibd  表数据

Myisam
t2.frm      #frm  表结构
t2.MYD     #MYD  表数据
t2.MYI      #MYI    表索引    类似于书的目录,基于目录查找数据,速度更快

memory
t3.frm      #frm 表结构    数据在内存,无需文件存储表数据

blackhole
t4.frm      # frm 表结构


#插入几条数据
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(2);
insert into t4 values(1);

运行之后查看结果
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t3;
+------+
| id   |
+------+
|    1 |
+------+

mysql> select * from t4;
Empty set (0.00 sec)

1 row in set (0.00 sec)


由于t4是用blackhole模式创建的表,因此没有数据
t3中的数据是在内存中,如何验证?
    先quit退出与服务端的链接,并且重启mysql服务端,重新链接
    
    切换到day45下,然后执行select * from t3;
     此时显示:Empty set (0.00 sec)
"""
  • 创建表的完整语法
"""
#语法
create table 表名(
    字段名1 字段类型(宽度) 约束条件,
    字段名2 字段类型(宽度) 约束条件,
    字段名3 字段类型(宽度) 约束条件
);

注意
    1.在同一张表中,字段名不能重复
    2.宽度和约束条件可以不用写,但是字段名和字段类型是必须得写的
        约束条件写的话,也支持写多个
        字段名1(宽度) 约束条件1 约束条件2.......
        create table t5(id);  没有类型,报错
    3.最后一行不能有逗号
        create table t6(
            id int,
            name char(32),
        );    #报错,最后一行有逗号

补充
    #宽度
        一般情况下,指的是对存储数据的限制
        create table t7(name char);    #默认的宽度是1
        insert into t7 values('jason');
        insert into t7 values(null);
         #不同的版本会出现不同的结果
         5.6版本默认没有开启严格模式,规定只能存1个字符,你多给了几个字符,name我会自动帮你截取1个字符
         5.7版本或者以上开启了严格模式,那么规定只能存几个,你在插入数据的时候,
就不能超出,一旦超出范围,会立刻报错: ERROR 1406 (22001): Data too long for column 'name' at row 1 严格模式到底开不开呢? MySQL5.7 之后的版本都是默认开启严格模式的 使用数据库的准则: 能尽量少的让数据库干活,就不要给数据库增加额外的压力 #约束条件 null not null create table t8(id int,name char not null); mysql> desc t8; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(1) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec) insert into t8 values(1,null); mysql> insert into t8 values(1,null); ERROR 1048 (23000): Column 'name' cannot be null #宽度和约束条件,到底是什么关系? 宽度是用来限制数据的存储 约束条件是在宽度的基础上增加额外的约束
"""
  •  基本数据类型

    • 整型
      • 分类

                      TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT

      • 作用

                     存储年龄、等级、id、号码等等

"""
以TINYINT为例
    是否有符号
        默认情况下是带符号的
    超出会怎么样
         超出限制只会存储最大可接受值

create table t9(id  tinyint);
insert into t9 values(-129),(256);
报错:ERROR 1264 (22003): Out of range value for column 'id' at row 1

#约束条件之unsigned无符号
create table t10(id tinyint unsigned);
insert into t10 values(-1),(256);
报错:ERROR 1264 (22003): Out of range value for column 'id' at row 1

create table t11(id int);
insert into t11 values(-1),(256);
# int 默认是带符号的
#整型默认情况下都是带符号的

#针对整型,括号内的宽度到底是干嘛的
create table t12(id int(8));
insert into t12 values(123456789);


特例:只有整型括号里面的数字不是表示限制位数而是表示长度的
id int(8)
     如果数字没有超过8位,那么默认用空格填充至8位,
     如果数字超过了8位,那么有几位就显示几位(但是还是要遵守最大显示范围)

create table t13(id int(8)  unsigned zerofill);
#用0填充至8位

mysql> create table t13(id int(8) unsigned zerofill);
Query OK, 0 rows affected (0.03 sec)

mysql> desc t13;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id    | int(8) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)


insert into t13 values(1);

mysql> insert into t13 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t13;
+----------+
| id       |
+----------+
| 00000001 |
+----------+
1 row in set (0.00 sec)

#总结:
    针对整型字段,括号内无需指定宽度,因为他默认的宽度已经足够显示所有的数据了,就用它默认的宽度就好了

        
""" 
    • 严格模式
"""
查看严格模式

show variables like "%mode";


模糊匹配/查询
    关键字  like 
       %:匹配任意多个字符
       _:匹配任意一个字符

#修改严格模式
set session   只在当前窗口有效
set global    全局有效

set global sql_mode = 'STRICT_TRANS_TABLES';
create table t14(id int, name char);
   修改完之后记得重新启动服务端即可


"""    
    • 浮点型

                   分类:FLOAT、DOUBLE、DECIMAL

                    作用:身高、体重、薪资

"""
#存储限制
float(255,30)        #总位数255位,小数部分占30位
double(255,30)     #总位数255位,小数部分占30位
decimal(65,30)      # 总位数65位,小数部分占30位


#精确度验证
create table t15(id float(255,30));
create table t16(id double(255,30));
create table t17(id decimal(65,30));

前期不要按方向键,所有的命令都自己手动敲

insert into t15 values(1.111111111111111111111111111111);
insert into t16 values(1.111111111111111111111111111111);
insert into t17 values(1.111111111111111111111111111111);

mysql> select * from t15;
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t16;
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t17;
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.00 sec)

精确度:
float < double < decimal
#要结合实际场景三者都能使用


"""
  • 字符型

                   分类:char、varchar

"""
char    定长
    char(4) 超过四个字符直接报错,不够4个字符空格补全


varchar  变长
    varchar(4) 超过4个字符直接报错,不够4个,有几个存几个

create table t18(name char(4));
create table t19(name varchar(4));

insert into t18 values('a');
insert into t19 values('a');

#介绍一个小方法  char_length统计字符长度

select char_length(name) from t18;
select char_length(name) from t19;

mysql> select char_length(name) from t18;
+-------------------+
| char_length(name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.01 sec)

mysql> select char_length(name) from t19;
+-------------------+
| char_length(name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)


首先可以肯定的是,char硬盘上存储的绝对是真正的带有空格的数据
但是在实现的时候,mysql会将多余的空格自动清除

#再次修改sql_mode  让MySQL不要做自动清除
set global sql_mode = 
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH'; 做完这个操作之后,退出重新链接mysql,然后继续执行这两行命令: select char_length(name) from t18; select char_length(name) from t19; mysql> select char_length(name) from t18; +-------------------+ | char_length(name) | +-------------------+ | 4 | +-------------------+ 1 row in set (0.00 sec) mysql> select char_length(name) from t19; +-------------------+ | char_length(name) | +-------------------+ | 1 | +-------------------+ 1 row in set (0.00 sec)
"""

    char 与varchar比较

 

"""
char
    缺点:浪费空间
    优点:存取都很简单
             直接按照固定长度的字符数存取数据即可
             Jason  egon  Alex  wusri
             存取全部按照5个字符

varchar
    优点:节省空间
    缺点: 存取较为麻烦
    1bytes+jason 1bytes+egon 1bytes+alex 1bytes+wusir 1bytes+tank

    存的时候,需要制作报头
    取得时候,也需要先读取报头,之后才能读取真正的数据

以前基本上都是用的char,现在用varchar比较多
后面随便用哪个都行,建议用varchar


"""

 

  • 字符型

    分类:

      date:年月日    2021-9-5

                      datetime :年月日时分秒  2021-9-5 11:09:57

                      time:时分秒     11:09:45

                      Year:2021

"""
create table student(
    id int,
    name varchar(16),
    born_year year,
    birth date,
    study_time time,
    reg_time datetime
);

insert into student values(1,'egon','2020','1993-3-4','11:09:34','2021-9-4 11:04:45');

mysql> select * from student;
+------+------+-----------+------------+------------+---------------------+
| id   | name | born_year | birth      | study_time | reg_time            |
+------+------+-----------+------------+------------+---------------------+
|    1 | egon |      2020 | 1990-11-11 | 11:11:11   | 2021-08-29 15:40:29 |
+------+------+-----------+------------+------------+---------------------+
1 row in set (0.00 sec)
"""
  • 枚举与集合类型

    分类:

                枚举(enum)  多选一

                集合(set)    多选多

           具体使用

"""
create table user(
    id int,
    name char(16),
    gender enum('male','female','others')
);

insert into user values(1,'jason','male');
insert into user values(1,'egon','female');

#枚举字段,后期在存数据的时候,只能从枚举里面选择一个存储


create table teacher(
    id int,
    name char(16),
    gender enum('male','female','others'),
    hobbies set('read','DBJ','hecha')
);

insert into teacher values(1,'jason','male','read');
insert into teacher values(2,'egon','other','DBJ,hecha');
insert into teacher values(3,'tank','female','生蚝');
# 集合可以只写一个,但是不能写集合中没有的值

mysql> insert into teacher values(1,'jason','male','read');
Query OK, 1 row affected (0.01 sec)
mysql> insert into teacher values(2,'egon','other','DBJ,hecha');
Query OK, 1 row affected (0.00 sec)
mysql> insert into teacher values(3,'tank','female','生蚝');
ERROR 1265 (01000): Data truncated for column 'hobbies' at row 1


"""

总结:

    字段类型

    严格模式

    约束条件

        null

       not null  

       unsigned