四十五、MySQL数据库2

当前位置:

首页 > temp > python入门教程 >

四十五、MySQL数据库2

今日内容详细

存储引擎

　　日常生活中文件格式有很多种，针对不同的文件格式会有不同的存储方式和处理机制（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

出处：https://www.cnblogs.com/MRPython/p/15228684.html

栏目列表

首页 > temp > python入门教程 >

四十五、MySQL数据库2

枚举与集合类型