本文主要是介绍MySQL 笔记二，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MySQL 笔记二

---

• 表的创建及数据类型
  • 字段类型
    • 整型
    • 小数
    • 时间
    • 字符串
    • 文本
    • 枚举 enum
    • 集合 set
  • 创建数据表
  • 字段属性
  • 删除表结构
  • 修改数据表结构
• 数据操作 DML 语句
  • 插入数据
    • 蠕虫复制
  • 更新数据
    • 删除数据
• 约束 constraint
  • 主键约束 PK
  • 外键约束 FK
  • 存储引擎
• 事务 transaction
  • 设置回滚点 savepoint
  • 隔离性
• 索引
  • 索引分类
  • 创建索引
  • 删除索引
  • 查看一个SQL语句是否使用索引
  • 索引失效
• 视图 view
  • 创建视图
  • 删除
  • 视图作用
• DBA 常用命令
  • 备份与还原
  • 用户权限管理
  • 重置密码
• 数据库设计三范式
  • 表关系
• 变量
  • 系统变量
  • 会话变量
  • 局部变量
• 流程结构
  • if
  • while
• 函数
  • 内置
    • 字符串
    • 时间函数
    • 数学函数
    • 其他
  • 自定义函数
    • 创建
    • 删除
• 存储过程 stored procedure
  • 创建
  • 调用
  • 删除
  • 参数类型
• 触发器 trigger
  • 创建
  • 删除
  • 触发工作

 

表的创建及数据类型

 

建表属于 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

 
 

---

---

---

这篇关于MySQL 笔记二的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！