MySql数据库相关

数据库服务器、数据管理系统、数据库、表与记录的关系

• 记录：1 路飞 15000000000 22（多个字段的信息组成一条记录，即文件中的一行内容）
• 表：userinfo,studentinfo,courseinfo（即文件）
• 数据库：db（即文件夹）
• 数据库管理系统：如mysql（是一个软件）
• 数据库服务器：一台计算机（对内存要求比较高）
• 总结：
  • 数据库服务器-：运行数据库管理软件
  • 数据库管理软件：管理-数据库
  • 数据库：即文件夹，用来组织文件/表
  • 表：即文件，用来存放多行内容/多条记录

 

MySql支持的存储引擎

mysql5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。其中NDB和InnoDB提供事务安全表，其他存储引擎都是非事务安全表。

• InnoDB存储引擎        mysql15.6之后的默认引擎
  • 数据和索引存储在一起（2个文件）
    • 数据索引和表结构
  • 数据持久化
  • 支持事务：为了保证数据完整性，将多个操作变成原子操作。保持数据安全
  • 支持行级锁：修改的行较少时候用。修改数据频繁的操作
  • 支持表级锁：批量修改多行时候用。对于大量的数据同时修改
  • 支持外键：约束两张表中的关联字段不能随意添加\删除。能够降低数据增删改的出错率
• MyISAM存储引擎        mysql15.5之前的默认存储引擎
  • 数据和索引不存储在一起（3个文件）
    • 数据、索引和表结构
  • 数据持久化
  • 只支持表锁
• MEMORY存储引擎
  • 数据存储在内存中（1个文件）
    • 表结构
  • 数据断电消失

 

InnoDB索引方式

• 有且只有一个聚蔟索引（聚集索引）——主键（如果创建表时没创建主键，会自动创建一个你看不到的主键）
• 也有非聚蔟索引（非聚集索引、辅助索引） myisam也是非聚集索引
• 使用b+树存储方式

 

常用数据类型

• 字符类型
  • chr(18)：最多只能表示255个字符
    • 定长存储，浪费空间，节省查询时间
      • 适合场景：标识性数据，如：身份证号，手机号，用户名，银行卡号
  • varchar(18)：最多能表示65535个字符
    • 变长存储，节省空间，存取速度慢
      • 适合场景：评论，朋友圈，微博
• 数字类型
  • int：不约束长度最多表示10位
  • float(m,n)：m一共多少位，n小数部分多少位，如果输入超过小数部分会进行四舍五入
  • double(m,n)：双精度浮点小数
• 时间类型
  • date：2020-07-07
  • time：10:31:21
  • datetime：2020-07-07 10:31:21
  • year：2020
  • timestamp：2020-07-07 10:31:21
• enum和set
  • enum：单选    enum('male','female')
  • set：多选        set('抽烟','喝酒','烫头'）

 

完整性约束

• 无符号：int unsigned
• 不能为空：not null
• 默认值：default
• 唯一约束：unique
  • 联合唯一：unique(字段1，字段2)
• 自增：auto_increment
  • 只能对数字有效，自带非空约束
  • 至少是unique的约束之后才能用
• 主键：primary key
  • 一张表只能有一个主键
  • 第一个被定义为非空+唯一的那一列会成为这张表的primary key
  • 联合主键 primary key(字段1,字段2)
  • 聚集索引+非空+唯一
• 外键：foreign key
  • 外键字段必须至少是唯一的
  • foregin key(自己的字段) references 外表(外表的字段)
  • 辅助索引+唯一
• 普通索引
  • create index 索引名 on 表(字段)

 

表之间的关系

• 多对一：foreign key
• 多对多：创建第三张表（两个外键）
• 一对一：unique    foreign key unique

 

增删改查

• 增：insert into 表 values()
• 删：
  • delete from 表 where 条件
  • delete from 表 会清空表，但不会清楚字段偏移量（offset），如设置自增字段，下一次插入还会有偏移量
  • truncate table 表  会清空表和偏移量
• 改：update 表 set 字段=值 where 条件
• 查：见我另一篇博客。地址：https://blog.csdn.net/qq_30307045/article/details/106275059

 

事务和锁

begin;  # 开启事务
select * from emp where id = 1 for update;  # 查询id值，for update添加行锁；
update emp set salary=10000 where id = 1; # 完成更新
commit; # 提交事务

 

数据备份和恢复（危险操作，要谨慎）

• 表和数据的备份
  • 备份数据：在cmd命令行中（非mysql）执行
    • mysqldump -uroot -proot -hlocalhost student > D:\python\py_mysql\tmp.sql
    • 完成后一定要打开文件看看里边是否有数据，结构等
  • 恢复数据：在mysql中执行命令
    • 切换到一个要备份的数据库中
    • source D:\python\py_mysql\tmp.sql
• 库的备份
  • 备份：在cmd命令中执行
    • mysql -uroot -proot --databases 库1 > D:\python\py_mysql\tmp1.sql
    • 多库备份：依次写要备份的库      mysql -uroot -proot --databases 库1 库2 库3> D:\python\py_mysql\tmp2.sql
    • 备份所有库：mysql -uroot -proot --all-databases- > D:\python\py_mysql\tmp1.sql
  • 恢复数据：
    • source D:\python\py_mysql\tmp1.sql

 

sql_mode

设置sql_mode可以在生产环境少踩坑，是一个容易被忽略的设置

https://www.cnblogs.com/clschao/articles/9962347.html