MySQL 笔记二

MySQL 笔记二

 

表的创建及数据类型

 

建表属于 DDL 语句，表结构的增删改 create, drop, alter ...

在每个数据库 DB 都中都有数据表对应的 frm 结构文件

frm 结构文件来自 innodb 存储引擎，innodb 存储引擎所有的文件都存储在外部的 ibdata 文件中

基本语法

SHOW TABLES;

显示数据库结构

DESCRIBE 表名;
DESC 表名;
SHOW COLUMNS FROM 表名;

表结构

  • Field: 字段名
  • Type: 数据类型
  • Null: 是否允许值为 Null
  • Key: 索引
  • Default: 默认值
  • Extra: 额外属性

字段类型

整型

  • tinyint: 1
  • smallint: 2
  • mediumint: 3
  • int: 4
  • bigint: 8

显示长度：指数据在显示的时候最多显示的长位

zerofill: 0 填充自带 unsigned

小数

浮点数

  • float: 4 数据精度 7

    • float
    • float(M,D): M 整数部分 D 小数部分

  • double: 8 数据精度 15

定点数

  • decimal

    • 小数部分无保障
    • 系统自配，每 9 分配 4 字节
    • 整数部分与小数部分分开
    • decimal(M,D): M 总长，上限 65；D 小数部分 上限 30

时间

  • date: 3, YYYY-mm-dd 初始：0000-00-00
  • time: 3, HH:ii:ss, 描述时间段
  • datetime: 8, YYYY-mm-dd HH:ii:ss
  • timestamp: 时间戳，YYYY-mm-dd HH:ii:ss
  • year：1

year 可以 2 位数【69,70界限】插入或者 4 位数插入年份

两个数据处理函数

  • str_to_date: varchar --> date
  • date_format: date --> varchar
  • now(): 获取当前时间 datetime 类型

mysql的日期格式：

  • %Y 年
  • %m 月
  • %d 日
  • %h 时
  • %i 分
  • %s 秒

字符串

  • char

    • char(L): 长度 0~255

  • varchar: 可变 【数据后增加1~2字节的额外开销】

char 查询效率比 varchar 高

数据超 255 一般使用 text

文本

类型

  • text: 普通文本
  • blob: 二进制文本【图片】

级别

  • tiny(0-255 bytes)
  • 标准(0-65 535 bytes) 64 KB
  • medium(0-16 777 215 bytes) 16 MB
  • long(0-4 294 967 295 bytes) 4 GB

比如: tinytext

注意二进制类型需要使用IO流

枚举 enum

enum(数值1, ...)

字符串枚举类型，最多可以包含 65,535 个枚举值。插入的数据必须位于列表中，并且只能命中其中一个值；如果不在，将插入一个空值

大小 1~2 字节

集合 set

set(值1, ...)

字符串集合类型，最多可以列出 64 个值。插入的数据可以命中其中的一个或者多个值，如果没有命中，将插入一个空值

ENUM 类型相当于单选题，SET 类型相当于多选题

创建数据表

数据库中数据表的名字通常有前缀，取数据库的前两个字母 + _

基本语法

CREATE TABLE 数据表名称(
    字段名 字段类型 [字段属性],
    ...,
    字段名 字段类型 [字段属性]
) [表选项];

  • 创造过程中必须使用 USE 选择数据库

  • 也可以使用 . 将数据库与数据表连接 数据库.数据表

  • 表选项

    • engine 存储引擎

      默认 Innodb , 5.5以前是 MyISAM

    • charset 字符集

    • collate 校对集

复制其它表

CREATE TABLE 数据表名称 AS SELECT */(字段列表) FROM 其它表;

字段属性

  • null

  • default 默认值

  • comment 注释

  • auto_increment 自动增长

  • unlque key 唯一键 保障数据唯一

    • 唯一键表中多个
    • 允许 NULL【不参与比较】

  • primary key 主键

    • 当前字段不 NULL
    • 不重复

删除表结构

基础语法

DROP TABLE 表名1[, 表名2, ...];                 # 表不存在还报错

DROP TABLE IF EXISTS 表名1[, 表名2, ...];       # 另一种写法

修改数据表结构

修改表选项

ALTER TABLE 表名 表选项=值;

已经存在数据，尽量不要修改

字段

ALTER TABLE 表名 ADD [COLUMN] 新字段 列类型 [列属性] [位置 first/after];                # 增加
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 字段类型 [列属性] [位置 first/after];         # 修改
ALTER TABLE 表名 MODIFY 段名 新类型 [新属性] [位置 first/after];                        # 修改
ALTER TABLE 表名 DROP 段名;                                                             # 删除

 
 

数据操作 DML 语句

插入数据

基本语法

INSERT INTO 表名[(
    字段列表
)] VALUES(
    对应字段的值列表
);

字段列表省略，需要值列表全部对应表的所有字段进行填写

对于日期格式，使用 %Y-%m-%d 是直接插入

多数据插入

    INSERT INTO 表名[(
        字段列表
    )] VALUES(
        对应字段的值列表
    ),
    ...
    ,(
        对应字段的值列表
    );

蠕虫复制

提取数据插入数据表

INSERT INTO 表名 [(字段列表)] SELECT */(字段列表) FROM 其他表;

更新数据

UPDATE 表名 SET 字段名 = 新值 [WHERE 字段名=值];

如果没有条件，是全表更新 limit 数量限制

UPDATE SET 字段名 = 值 [WHERE 条件] LIMIT 数量;

删除数据

基本语法

DELETE FROM 表名 [WHERE 条件];

注意：不带 where 条件筛选，意味着删除全部数据【慎用】

快速删除全部数据 truncate

TRUNCATE 表名;

比较 delete 与 truncate

  • delete 效率低，支持回滚可以复原，属于 DML 语句
  • truncate 效率高，永久删除物理层面，属于 DDL 语句

 
 

约束 constraint

保障：表中数据有效性和完整性

分类：

  • 非空约束: not null

    位置字段属性，禁止字段值为 NULL

  • 唯一性约束: unique

    位置字段属性，禁止字段值重复，NULL的重复无关

    联合唯一：让两个字段值和在一起是唯一的

    # 联合唯一
CREATE TABLE 数据表名称(
    字段名 字段类型 [字段属性],
    ...,
    字段名 字段类型 [字段属性],
    UNIQUE(字段1, 字段2)        # 使 字段1 和 字段2 组合唯一
) [表选项];

  • 主键约束: primary key (PK)

  • 外键约束: foreign key (FK)

  • 检查约束: check ( MySQL 不支持, Oracle 支持 )

注意：在 MySQL 中一个字段同时被 not null 与 unique 约束，该字段自动被视为主键

主键约束 PK

相关术语

  • 主键约束
  • 主键字段
  • 主键值：是每一行记录的唯一标识

任何一张表都应该有主键，没有主键表无效

主键值特性：唯一不空

# 单一主键
CREATE TABLE 数据表名称(
    字段名 字段类型 PRIMARY KEY,    # 设置主键，列级约束
    ...,
    字段名 字段类型 [字段属性]
) [表选项];

# 复合主键
CREATE TABLE 数据表名称(
    字段名 字段类型 [字段属性],
    ...,
    字段名 字段类型 [字段属性],
    PRIMARY KEY(字段1, 字段2)       # 表级约束
) [表选项];

主键分类：

  • 自然主键：一个自然数与业务无关
  • 业务主键：主键值与业务紧密关联

常用自然主键

注意：

  • 复合主键开发中不建议使用
  • 主键约束一张表只能添加一个
  • 主键值建议使用：int, bigint, char 之类定值，不建议 varchar
  • 在设计主键时，应使用尽可能少的字段，这不但能减少存储空间，还能提升查询性能。主键包含的字段越少，所需要的存储空间就越小，就性能而言，更少的数据意味着更快速地处理

外键约束 FK

相关术语

  • 外键约束
  • 外键字段：添加外键约束的字段
  • 外键值：允许为 NULL

外键由表中的一个字段或者多个字段构成，一个表的外键用来指向另一个表的主键 Primary Key

包含外键的表称为从表，被指向的表称为主表；注意：从表的外键类型，必须与主表的主键类型一致

作用：从表的数据受到主表的约束，向从表中插入或者更新数据时，外键的值必须存在于主表的主键中；外键约束用于防止破坏两个表之间的关联性，保证数据的完整性和一致性

创建时语法

[constraint `外键名`] foreign key(外键字段) references 主表(主键) on 约束模式;

约束模式

  • district: 严格模式，默认

  • casade: 级联模式，一起操作

    • update
    • delete

  • set null: 置空模式，主表删除，从表置空，前提允许为空

常用约束模式：on update casade, on delete set null

# 主表
CREATE TABLE 表名1(
    字段名 字段类型 PRIMARY KEY,                # 设置主键
    ...,
    字段名 字段类型 [字段属性]
) [表选项];

# 从表
CREATE TABLE 表名2(
    字段名 字段类型 [字段属性],
    ...,
    字段名 字段类型 [字段属性],
    FOREIGN KEY(字段1) REFERENCES 主表(字段)  # 设置外键，主表中字段不一定是主键，但必须具有唯一性
) [表选项];

数量：一个表可以有多个外键，但是只能有一个主键

注意逻辑顺序：

  • 创建表：主 --> 从
  • 删除表：从 --> 主
  • 删除数据：从 --> 主
  • 插入数据：主 --> 从

外键需求：

  • 主从字段类型一致
  • 基本属性相同
  • 后期添加对数据有要求
  • 外键只能使用 innodb

存储引擎

数据库的存储引擎决定了表在计算机中的存储方式，不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能

MySQL 支持的存储引擎：使用 show engines \G

常用存储引擎：

  • MyISAM

    一张表结构：

    • 格式文件(.frm)
    • 数据文件(.MYD)
    • 索引文件(.MYI)

    可以被转换为压缩、只读表节省空间

  • InnoDB

    MySQL 默认存储引擎，支持事务可以数据恢复

    表格式文件 (.frm)，数据存储在表空间中

    最大特色：支持事务

  • Memory

    数据存储在内存中，查询快，关机数据完蛋

 
 

事务 transaction

一个事务其实就是一个完整的业务逻辑

事务是必须满足4个条件 (ACID):

  • 不可分割性 (Atomicity)
  • 一致性 (Consistency)
  • 隔离性 (Isolation)
  • 持久性 (Durability)

只有 insert, delete, update 三个语句才和事务相关，即 DML 语句

为了数据安全

本质，一个事务就是多条 DML 语句同时成功或失败

事务执行过程

  • 开启事务 start transaction

    关闭默认自动提交功能

  • 提交事务 commit

    清空事务性活动日志文件，将数据全部持久化到数据库

    标志事务结束，全部成功

  • 回滚事务 rollback

    清空事务性活动日志文件，撤销之前全部 DML 操作

    标志事务结束，全部失败

设置回滚点 savepoint

增加回滚点

savepoint 回滚点名称

删除回滚点

release savepoint 回滚点名称

回滚

rollback to 回滚点名称

隔离性

查看隔离级别：

SELECT @@tx_isolation;

事务隔离等级(由低到高)

  • 读未提交: read uncommittd

    未隔离，存在脏读现象【读到假的结束】

  • 读已提交: read committd

    解决脏读现象，但不可重复读取

    读取数据比较真实

  • 可重复读: repeatable read

    事务A开启后，不管多久，每次读取的数据都一致

    即使事务B将数据修改，提交也不影响事务A

    可能会出现幻影读取，数据读假

  • 序列化/串行化: serializable

    解决所有问题，表示事务排队，不能并发，效率低

    每次数据最真实

大多数据库都是二档起步

MySQL 默认三档

 
 

索引

在数据库标的字段上添加，类似目录提高查询效率

一张表的一个字段可以添加一个索引，也可以多个字段联合起来添加索引

使用索引将会造成滥用

缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE；因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

MySQL索引使用 B-Tree 数据结构

任何数据库当中主键都会自动添加索引对象

注意：MySQL中主键、unique字段都会自动添加索引

应用需求

  • 数据量庞大
  • 该字段经常被扫描，比如 where
  • 该字段很少执行 DML 操作

记忆不要随便添加索引，索引需要被维护

索引分类

索引数

  • 单一索引
  • 复合索引

索引字段属性

  • 主键索引
  • 唯一性索引

. . .

唯一性比较弱的字段，添加索引用处不大

创建索引

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录

CREATE INDEX 索引名 ON 表(字段);

# 复合索引
CREATE INDEX 索引名 ON 表(字段1, 字段2);

删除索引

DROP INDEX 索引名 ON 表(字段);

查看一个SQL语句是否使用索引

使用 EXPLAIN 解释 SQL

索引失效

  1. 应用模糊查询已 % 开始会索引失效

  2. 逻辑短路，使用 or 一边没有索引一边有索引

    没有索引为假，直接导致结构为假【或运算特点】，将忽略有索引的字段

    解决使用 union 等价取代 or，尽量减少使用

  3. 使用复合索引，没有使用左侧的列查找，索引失效

  4. 在 where 中索引参与运算，索引失效

  5. 在 where 中索引使用函数，索引失效

 
 

视图 view

什么是视图？

站在不同角度去看待同一份数据

视图是一张虚拟表

创建视图

CREATE VIEW 视图名字 AS SELECT 指令;

注释：视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据

删除

DROP VIEW 视图名;

视图作用

我们可以面向视图进行增删改查 (对视图对象的增删改查，会导致原表被操作)

视图起到简化SQL的复杂度

增删查改：CRUD

DBA 常用命令

备份与还原

专门备份：mysqldump.exe，备份与还原是在命令窗口

备份不适用于文件特别大，或变换频繁的数据

mysqldump -hPup 数据库名称 [表1 ...] > 备份文件路径

还原

mysql -hPup 数据库名称 [表1 ...] < 备份文件路径

或者数据导入在 mysql 中

source 文件位置

用户权限管理

用户

  • 创建用户

    CREATE USER 用户名@host IDENTIFIED BY 密码;

  • 删除

    DROP USER 用户名@host;

  • 改密码

    SET PASSWORD for 用户名@host = password('密码');

权限

  • 数据权限
  • 结构权限
  • 管理权限

授权 grant

grant 权限列表/[all privileges] on 数据库/*.表/* to 用户;

取消 revoke

revoke 权限列表/[all privileges] on 数据库/*.表/* from 用户;

刷新 flush

flush privileges

重置密码

  • 停止服务器

  • 重启服务器

      mysqld.exe --skip-grant-tables   // 启动服务器跳过权限

  • 修改密码

      set password for ‘root'@'localhost' = password('密码');

 
 

数据库设计三范式

  1. 任何一张表必须有主键，没有·一个字段原子性不可再分
  2. 建立与第一原则，要求所有非主键字段完全依赖主键，不要产生部分依赖
  3. 建立与第二原则，要求所有非主键字段直接依赖主键，不要产生传递依赖

根据用户需求，三范式为理论【冗余可以换速度】

技巧：

  • 多对多，三张表，关系表，两个外键
  • 一对多，两张表，多的表加外键
  • 一对一，外键唯一

表关系

一对一

关系唯一匹配，两张表使用同样主键

一对多

子表添加类字段

多对多

中间表

 
 

变量

系统变量

show variables [like ''];

基本语法

select @@变量名;

修改

  • 局部 set 变量名 = 值;
  • 全局 set global 变量名 = 值; 或者 set @@global.变量名 = 值

会话变量

当前用户，当前客户端生效

变量赋值符 :=

定义变量

set @变量名 := 值;

局部变量

作用于 begin end

局部变量使用 declare 关键字声明

declare 变量名 数据类型 [属性];

 
 

流程结构

if

用法

if(条件, 真, 假);

复杂语块

if 条件 then
    为真语句;
else
    为假语句;
end if;

while

结构标识符

  • 为某些特定结构命名
标识名:while 条件 do
    循环体;
end while[标识名];
  • lterate 迭代，用来跳出本次循环，直接进入下一次循环
  • leave 离开，跳出循环

 
 

函数

内置

字符串

  • char_length()
  • length() 判断字节数
  • concat() 连接字符串
  • instr()
  • lcase() 全部小写
  • left() 截取
  • ltrim() 清空左边空格
  • mid() 从中间开始截取

时间函数

  • now() 返回当前时间和日期
  • curdate() 日期
  • curtime() 时间
  • datediff() 判断两个日期之间的天数差距, 字符串格式
  • date_add(日期, interval 时间数字 type) 进行时间的增加
  • unix_timestamp() 获取时间戳
  • from_unixtime() 将时间戳转换我日期

type: day/hour/minute/second

数学函数

  • abs() 绝对值
  • ceiling() 向上取整
  • floor() 向下取整
  • pow()
  • rand() 0~1
  • round() 四舍五入

其他

  • md5()
  • version() 获取版本号
  • databse() 显示当前数据库
  • UUID() 生成一个唯一标识符（自增）

自定义函数

修改语句临时结束符

delimiter 新符号

通常是使用 $$

通过 delimiter 修改后 ; 将变更

创建

修改语句结束符
create function 函数名(形参) returns 返回值类型
begin
    函数体
    return 返回值;
end
语句结束符
修改语句结束符

查看

show function status;
  • 属于用户级别
  • 有数据库限制
  • 函数内部不能使用 select, 使用了返回 result set

变量赋值是唯一可用

删除

drop function 函数名;

 
 

存储过程 stored procedure

存储过程是一组为了完成特定功能的 SQL 语句集合

使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程

  • procedure
  • 无返回
  • 不能在 select 中使用

创建

修改语句结束符
create procedure 名(形参)
begin
    过程体
end
语句结束符
修改语句结束符

调用

call 过程名([实参列表]);

删除

drop procedure 过程名;

参数类型

  • in 传入，可以是变量或数据
  • out 在过程中存入数据的变量，进入过程值变为 NULL
  • inout

 
 

触发器 trigger

特殊的存储过程

给一些表的记录绑定一段代码

触发器的优点如下：

  • 触发器的执行是自动的，当对触发器相关表的数据做出相应的修改后立即执行
  • 触发器可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作
  • 触发器可以实现表数据的级联更改，在一定程度上保证了数据的完整性

触发器的缺点如下：

  • 使用触发器实现的业务逻辑在出现问题时很难进行定位，特别是涉及到多个触发器的情况下，会使后期维护变得困难
  • 大量使用触发器容易导致代码结构被打乱，增加了程序的复杂性
  • 如果需要变动的数据量较大时，触发器的执行效率会非常低

创建

修改语句结束符
create trigger 触发器 触发时机 触发事件 on 表 for each row
begin
    过程体
end
语句结束符
修改语句结束符

删除

drop trigger 过程名;

触发工作

类似编程中的事件概论

  • 触发时机

    • 数据操作前 before
    • 数据操作后 after

  • 触发事件

    • insert
    • update
    • delete

  • 触发对象 on 表 for each row

一张表中对应类型只有一种，最多只有 6 个触发器

记录关键字

  • new
  • old

 
 

posted @ 2022-07-24 20:12  sha0dow  阅读(36)  评论(0)  编辑  收藏  举报