MySQL字段约束条件,外键约束条件,表关系

2023-11-03

字符编码与配置文件

统一字符编码

存储引擎

字符编码与配置文件

\s >>>> 查看MySQL默认字符编码

<<<<<< 如果是5.x系列, 显示的编码会有多种, latin1, gbk, ,,, 如果是8.x系列, 现实的是utf8mb4, 它是utf8的优化版本,支持存储表情.<<<<<<<

统一字符编码

5.x默认编码有很多种就容易导致乱码的情况, 所以应该统一编码 >>>> mydefault.ini配置文件 >>>>

1. copy一份该配置文件并修改名称为my.ini

2.清空my.ini文件的内容

3.添加固定的配置信息即可

[mysqld]
                character-set-server=utf8
                collation-server=utf8_general_ci
                [client]
                default-character-set=utf8
                [mysql]
                default-character-set=utf8

4.保存并重启服务端即可生效

net stop mysql

net start mysql

存储引擎

mysql中建立的库===>文件夹库中建立的表===>文件

现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：

比如处理文本用txt类型，处理表格用excel，处理图片用png等

数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎，mysql根据不同的表类型会有不同的处理机制存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

存储引擎可以理解为处理数据的不同方式

查看存储引擎 >>> show engines;

查看正在使用的存储引擎

show variables like 'storage_engine%';
或者
SELECT @@default_storage_engine;

查看表的存储引擎

SHOW CREATE TABLE db1.t1\G
或者
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'db1'\G

需要了解的引擎 >>> MyISAM, InnoDB, BlackHole, Memory

MyISAM : 5.1之前的版本,默认的存储引擎. 特点>> 存取数据的速度很快, 但功能很少, 安全型较低

IoonDB : 5.1之后的版本MySQL默认的存储引擎. 特点>> 功能变多, 安全性较高,但是存取速度没有MyISAM快.

BlackHole : 任何写入的数据都会立刻消失, 类似于垃圾回收站

Memory : 以内存中作为数据存取第, 速度快但是断电立刻丢失数据

修改存储引擎的方式

1. 配置文件修改存储引擎

                vim /etc/my.cnf
                [mysqld]
                default-storage-engine=innodb
                innodb_file_per_table=1

2 临时修改存储引擎

在MySQL命令行中临时设置
SET @@storage_engine=myisam

查看
SELECT @@default_storage_engine;

3.建表时修改存储引擎

CREATE TABLE jason(id INT) ENGINE=myisam;

innodb存储引擎支持的核心特性

事务
行级锁：innodb支持行级锁，myisam是表级锁，锁的粒度越小并发能力越强
外键
MVCC 多版本并发控制
备份和恢复 innodb支持支持热备，myisam不支持
自动故障恢复（CSR） Crash Safe Recovery

查看innodb的表有哪些，通过查表information_schema.tables来获取

table_schema字段的值即表所在的库
select table_schema,table_name,engine from information_schema.tables where engine='innodb';

自定义选项存储引擎

create table t1(id int) engine= myisam;

create table t2(id int) engine= innodb;

create table t3(id int) engine= blackhole;

create table t4(id int)engine= memory;

ps: windows cmd 终端, 右键就是粘贴

不同存储引擎产生的表文件有几个?

MyISAM
MyISAM 存储引擎的数据表在数据库目录里使用 3 个文件来代表，这些文件的基本名与数据表的名字相同，扩展名则表明了文件的具体用途。这三个文件的扩展名分别是：

.frm：表结构定义文件，存放着该数据表的结构定义。
.MYD：MY Data 的缩写，数据文件，存放着该数据表中各个行的数据。
.MYI： MY Index 的缩写，索引文件，存放着该数据表的全部索引信息。

mysql> SET default_storage_engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE tb_myisam(
    -> id INT
    -> );
Query OK, 0 rows affected (0.03 sec)

查看myisam的表有哪些，通过查表information_schema.tables来获取

select table_schema,table_name,engine from information_schema.tables where engine='myisam';

MERGE

MERGE 存储引擎的数据表其实是一个逻辑结构。它代表着由一组结构完全相同的 MyISAM 数据表所构成的集合。有关的查询命令会把它当作一个大数据表来对待。

MERGE 存储引擎的数据表除了拥有存储表结构定义的 .frm 文件以外，还有一个扩展名为 .mgr 的文件，这个文件里不保存数据，而是数据的来源地。通俗的说，就是一份由多个 MyISAM 数据表的名单构成的 MERGE 数据表。

mysql> SET default_storage_engine=Merge;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tb_merge(
    -> id INT
    -> );
Query OK, 0 rows affected (0.02 sec)

InnoDB

对于 InnoDB 存储引擎的数据表，一个表对应两个文件，

一个是 *.frm，存储表结构信息；

一个是 *.ibd，存储表中数据。

mysql> SET default_storage_engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tb_innodb(
    -> id INT
    -> );
Query OK, 0 rows affected (0.10 sec)

Memory
Memory 存储引擎的数据表是创建在内存中的数据表。因为 MySQL 服务器把 Memory 数据表的数据和索引都存放在了内存中而不是硬盘上，所以除了相应的 .frm 文件外，Memory 引擎表在文件系统里没有其它相应的代表文件。

mysql> SET default_storage_engine=Memory;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tb_memory(
    -> id INT
    -> );
Query OK, 0 rows affected (0.03 sec)

BLACKHOLE

由于在 BLACKHOLE 存储引擎的数据表中写入任何数据都会消失，所以除了 .frm 文件，BLACKHOLE 引擎表没有其他相应的代表文件。

mysql> SET default_storage_engine=BLACKHOLE;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tb_blackhole(
    -> id INT
    -> );
Query OK, 0 rows affected (0.03 sec)

blackhole与memory存取数据的特征

Blackhole：丢弃写操作，读操作会返回空内容

Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。
但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

Memory：置于内存的表

Memory采用的逻辑介质是内存，响应速度很快，但是当mysqld守护进程崩溃的时候数据会丢失。另外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用（长度不固定的）

Memory同时支持哈希（HASH）索引和B+树索引

哈希索引相等的比较快，但是对于范围的比较慢很多。
默认使用哈希（HASH）索引，其速度要比使用B型树（BTREE）索引快。
如果希望使用B树索引，可以在创建索引时选择使用

Memory表至少比MylSAM表要快一个数量级。

MEMORY表的大小是受到限制的。表的大小主要取决于两个参数，分别是max_rows和
max_heap_table_size。其中,max_rows可以在创建表时指定;max_heap_table_size的大小默认为16MB,可以按需要进行扩大。

数据文件与索引文件分开存储。

每个基于MEMORY存储引擎的表实际对应一个磁盘文件，该文件的文件名与表名相同，类型为frm类型，该文件中只存储表的结构，而其数据文件都是存储在内存中的。
这样有利于数据的快速处理，提供整个表的处理效率。

缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。

创建表的完整语法

create table 表名(字段名1 字段类型(数字) 约束条件,

字段名2 字段类型(数字) 约束条件,

字段名3 字段类型(数字) 约束条件);

1. 字段名和字段类型是必须的

2. 数字和约束条件是可选的

3.约束条件可以写多个. 空格隔开即可

4. 最后一行字段结尾不能加逗号!

字段类型之整型

保证数据的准确性和标准性。 尽量选最小的，够用就行

类	类型	存储长度	二进制范围	十进制数字范围
整数	tinyint	1B=8bit	000000000~11111111	0255，-128127
整数	int	4B=32bit	略	02^32-1，-2^312^31-1
整数	bigint	8B=64bit	略	02^64-1，-2^632^63-1
定点数	decimal

上述整型的区别在于从上往下能够存储的数字范围越来越大
注意事项
1.需要考虑正负数的问题如果需要存储负数则需要占据一个比特位
2.注意手机号如果使用整型来存储需要使用bigint才可以

PS:有时候看似需要使用数字类型存储的数据其实可能使用的是字符串
因为字符串可以解决不同语言对数字不精确的缺陷!!!

create table t5(id tinyint);
insert into t5 values(-129),(256);
# 如果是在5.6版本不会报错会自动处理成最大范围(没有意义)
步骤1:set global sql_mode = 'STRICT_TRANS_TABLES';
步骤2:退出客户端重新登录即可
# 如果是在5.7及以上版本则会直接报错(更加合理)
验证(结论)发现所有的整型都默认带有正负号如何修改不带正负号(约束条件)
create table t6(id tinyint unsigned);

字段类型之浮点型

   float
double
decimal
上述浮点型从上往下精确度越来越高
   float(255,30) 总共255位小数位占30位
double(255,30) 总共255位小数位占30位
decimal(65,30) 总共65位小数位占30位
  重点研究精确度问题
   create table t7(id float(255,30));
create table t8(id double(255,30));
create table t9(id decimal(65,30));
insert into t7 values(1.11111111111111111);
insert into t8 values(1.11111111111111111);
insert into t9 values(1.11111111111111111);
decimal>double>float
"""
虽然三者精确度有差距但是具体用哪个应该结合实际情况
   比如正常业务使用float足够
   如果是高精尖可以使用decimal
"""

字段类型之字符类型

char vachar 两者的区别在于一个是定长,一个是变长.

 >>>>  char(4)     定长 最大只能存储四个字符 超出则报错 不够则空格填充至四个

 >>>>  varchar(4)  变长 最大只能存储四个字符 超出则报错 不够则有几个存几个

超出数字类型会直接报错, 超出数字类型会只记录前四个字符

  验证定长与变长特性

    create table t10(name char(4));
    create table t11(name varchar(4));    
    insert into t10 values('jason'); 
    insert into t11 values('jason');

 # 如果是5.6版本并且没有修改严格模式 则会自动截取四个字符(不合理)

    临时修改

            步骤1:set global sql_mode = 'STRICT_TRANS_TABLES';
            步骤2:退出客户端 重新登录即可

  永久修改
            修改my.ini配置文件
            sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY' 
            重启服务端之后永久生效.

    char_length()  获取字段数据的长度  >>> 该方法无法直接获取到定长的真实长度    
    因为MySQL在存数据的时候会自动填充空格在取数据的时候又会自动移除空格

让MySQL在取数据的时候不自动移除空格   
         单次修改 
       set session sql_mode = 'pad_char_to_full_length'

工作中使用char还是varchar    

char       整存整取 速度快    会造成一定的存储空间浪费 
varchar    节省存储空间    存取数据的速度没有char快(取数据不知道数据的精确长度)  
varchar    在存数据的时候会生成一个1bytes的报头 记录数据长度
varchar    在取数据的时候先会读取1bytes的报头 从中获取真实数据长度

以前几乎使用的都是char  现在varchar使用频率也越来越高
 两者都有使用场景    比如:        针对统一中国人的姓名 应该采取那个类型  >>> varchar      
                                规模较小 数据量相对固定的字典 >>> char  
        很多时候字段类型的选取和命名都会在邮件中标明

字段类型括号内的数字大部分情况下是用来限制存储的长度
但是在整型中并不是用来限制长度而是用来控制展示长度

以后涉及到整型字段都无需自己定义长度直接使用自带的即可
而针对其他类型的字段则需要自己添加数字

create table t12(id int(3));
insert into t12 values(1111);  # 不会报错

create table t13(id int(3) zerofill);  # 位数不够用0填充
insert into t13 values(1);  # 001
insert into t13 values(1111);  # 有几位就展示几位

字段类型之枚举与集合

enum('bj','tj','sh')： #填写性别，指定多个项，选择其中一个。

枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。下标索引。

说明：字符串类型

作用：

例如： id telnum name 身份省

1 155****8909 xiaowu 3713**** 山东省

# 枚举
多选一
create table t14(
id int,
name varchar(32),
gender enum('male','female','others')
);
'''插入数据的时候针对gender只能填写提前定义好的数值'''

# 集合
多选多(也可以多选一)
create table t15(
id int,
name varchar(32),
hobby set('篮球','足球','双色球','排球','水球','乒乓球')
);

字段类型之日期类型

date                           年月日
datetime 年月日时分秒
time                           时分秒
year                           年

create table t16(
id int,
name varchar(32),
reg_time datetime,
birth date,
study_time time,
join_time year
);
# 针对时间数据一般都是通过代码自动获取并添加我们这里手动模拟
insert into t16 values(1,'lili','2000-11-11','2002-01-21','11:11:11','2015');

约束条件

约束(一般建表时添加):

1、primary key(PK) ：主键约束
设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。

2、not null ：非空约束
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0

3、unique key ：唯一约束
列值不能重复

4、unsigned ：无符号
针对数字列，非负数。

5、key :索引
可以在某列上建立索引，来优化查询,一般是根据需要后添加

6、default :默认值
列中，没有录入值时，会自动使用default的值填充

7、auto_increment :自增长
针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）

8、comment : 注释

总结
   以后在创建规范的表的时候
       一般都会有一个主键字段的编写如下
           id int primary key auto_increment

自增特性

自增不会随着数据的删除而退回

create table t1(
id int primary key auto_increment,
name varchar(32)
);
insert into t1(name) values('jason'),('kevin'),('tony');
insert into t1(name) values('oscar'); # id=4
delete from t1 where id=4;
insert into t1(name) values('oscar'); # id=5
'''自增不会随着数据的删除而回退'''
delete from t1; # 删除数据但无法重置主键
insert into t1(name) values('jason'),('kevin'),('tony');
truncate t1; # 删除数据并重置主键值
insert into t1(name) values('jason'),('kevin'),('tony');

外键简介

该字段其实就是外键字段
外键字段就是用来记录表与表之间数据的关系

>>>> 创建一张员工表
   id   name age dep_name dep_desc
缺陷
   1.表的重点不清晰(可以忽略)
   既可以说是员工表也可以说是部门表
2.表中某些字段对应的数据一直在重复(可以忽略)
   浪费存储空间
3.表的扩展性极差牵一发而动全身(不能忽略)
   耦合度太高不利于维护
解决
   将一张表一分为二
员工表                               部门表
id   name    age               id        dep_name       dep_desc
# 拆表之后上述的三个缺陷解决
但是迎来了一个致命问题 >>>   员工与部门之间没有了绑定关系
在员工表中添加一个部门编号字段与部门表中的主键字段对应 <<<<<

外键关系

总共有四种
1.一对多
2.多对多
3.一对一
4.没有关系

# 一对多
以员工表与部门表为例
            先站在员工表的角度 >>>> 一个员工不能对应多个部门
再站在部门表的角度 >>> 一个部门可以对应多个员工
         结论:换位思考之后得出的答案是一个可以一个不可以
所以关系是"一对多" 部门是'一'员工是'多'
'''关系表达只能用一对多不能用多对一'''
   一对多关系外键字段建在"多"的一方(员工表)

外键SQL语句

>>>> 如果表中有外键字段那么建议你先编写普通字段最后再考虑外键字段 <<<<<

# 在创建表字段的时候也可以给每个字段添加中文注释
create table emp(
id int primary key auto_increment comment '编号',
name varchar(32) comment '姓名',
age int comment '年龄',
dep_id int comment '部门编号',
foreign key(dep_id) references dep(id)
);

create table dep(
id int primary key auto_increment comment '编号',
dep_name varchar(32) comment '部门名称',
dep_desc varchar(32) comment '部门描述'
);

# foreign key的约束效果
1.创建表的时候应该先创建被关联表(没有外键字段的表)
2.插入数据的时候应该先插入被关联表(没有外键字段的表)
   外键字段填入的值只能是被关联表中已经存在的值
3.修改、删除被关联表数据都会出现障碍
   update dep set id=200 where id=2;
delete from dep where id=2;

# 级联更新级联删除
create table emp1(
   id int primary key auto_increment comment '编号',
name varchar(32) comment '姓名',
age int comment '年龄',
dep_id int comment '部门编号',
foreign key(dep_id) references dep1(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
create table dep1(
   id int primary key auto_increment comment '编号',
dep_name varchar(32) comment '部门名称',
dep_desc varchar(32) comment '部门描述'
);
"""
在实际工作中外键也可能不会使用因为外键会消耗额外的资源
并且会增加表的复杂度
表很多的情况下我们也可以通过SQL语句的形式建立逻辑意义上的表关系
"""

多对多关系

以图书与作者表为例 
   1.先站在图书表的角度 >>> 一本书籍能对应多名作者
   2.再站在作者表的角度 >>> 一名作者可以对应多本书籍 
   结论:换位思考之后两边都可以 那么就是"多对多"关系

create table book(
    id int primary key auto_increment,
    title varchar(32),  
    price float(10,2),
    author_id int, 
    foreign key(author_id) references author(id)  
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);

create table author(   
        id int primary key auto_increment, 
        name varchar(32),  
        gender enum('male','female','others'), 
        book_id int, 
        foreign key(book_id) references book(id)  
        on update cascade  # 级联更新 
        on delete cascade  # 级联删除
);

  针对多对多关系 需要单独开设第三张表专门存储关系 

create table book( 
        id int primary key auto_increment, 
        title varchar(32), 
        price float(10,2)
);    

create table author(
        id int primary key auto_increment, 
        name varchar(32), 
        gender enum('male','female','others')
);    

create table book2author( 
        id int primary key auto_increment, 
        author_id int, 
        book_id int, 
        foreign key(author_id) references author(id)  
        on update cascade   # 级联更新 
        on delete cascade,  # 级联删除
        foreign key(book_id) references book(id)  
        on update cascade  # 级联更新
        on delete cascade  # 级联删除
);

 针对多对多表关系  >>>> 两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除

一对一关系

针对qq用户表其实里面的数据可以分成两类
   热数据:经常需要使用的数据
   eg:qq号码座右铭个人简介爱好
冷数据:不怎么经常需要使用的数据
   eg:邮箱电话学校 ...
为了节省资源并降低数据库压力会将表一分为二
   用户表
   存使用频率较高的数据字段
用户详情表
   存使用频率较低的数据字段
1.先站在用户表的角度
        一个用户数据不能对应多个用户详情数据

2.再站在用户详情表的角度
        一个用户详情数据不能对应多个用户数据

结论:换位思考之后两边都不可以那么关系可能有两种
   '没有关系':
'一对一关系'
   针对'一对一关系'外键字段建在任意一方都可以,但是推荐建在查询频率较高的较好的一方

create table User(
   id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others'),
user_detail_id int unique, # 好好体会为什么加unique
foreign key(user_detail_id) references UserDetail(id)
on update cascade # 级联更新
on delete cascade, # 级联删除
);
create table UserDetail(
   id int primary key auto_increment,
phone bigint,
age int
);

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)