数据操作 DML 语句

表的创建及数据类型

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

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

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

基本语法

SHOW TABLES;

显示数据库结构

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

表结构

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

zerofill: 0 填充自带 unsigned

浮点数

定点数

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

两个数据处理函数

mysql的日期格式：

char 查询效率比 varchar 高

数据超 255 一般使用 text

比如: tinytext

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

enum(数值1, ...)

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

大小 1~2 字节

set(值1, ...)

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

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

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

基本语法

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

复制其它表

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

基础语法

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 条件筛选mysql数据表，意味着删除全部数据【慎用】

快速删除全部数据 truncate

TRUNCATE 表名;

比较 delete 与 truncate

约束 constraint

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

分类：

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

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

相关术语

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

主键值特性：唯一不空

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

主键分类：

常用自然主键

注意：

相关术语

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

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

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

创建时语法

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

约束模式

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

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

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

注意逻辑顺序：

外键需求：

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

[En]

The storage engine of the database determines how the table is stored in the computer. Different storage engines provide different storage mechanisms, indexing skills, locking levels and other functions. Specific functions can also be obtained by using different storage engines.

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

常用存储引擎：

事务 transaction

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

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

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

为了数据安全

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

事务执行过程

增加回滚点

savepoint 回滚点名称

删除回滚点

release savepoint 回滚点名称

rollback to 回滚点名称

查看隔离级别：

SELECT @@tx_isolation;

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

大多数据库都是二档起步

MySQL 默认三档

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

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

[En]

One field of a table can add an index, or multiple fields can be combined to add an index.

使用索引将会造成滥用

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

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

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

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

应用需求

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

索引数

索引字段属性

. . .

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

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

[En]

In fact, an index is also a table that holds the primary key and index fields and points to the records of the entity table

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

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

使用 EXPLAIN 解释 SQL

视图 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('密码');

数据库设计三范式

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

技巧：

一对一

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

一对多

子表添加类字段

多对多

中间表

show variables [like ''];

基本语法

select @@变量名;

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

变量赋值符 :=

定义变量

set @变量名 := 值;

作用于 begin end

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

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

流程结构

if(条件, 真, 假);

复杂语块

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

结构标识符

标识名:while 条件 do
    循环体;
end while[标识名];

type: day/hour/minute/second

修改语句临时结束符

delimiter 新符号

通常是使用 $$

通过 delimiter 修改后 ; 将变更

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

show function status;

变量赋值是唯一可用

drop function 函数名;

存储过程 stored procedure

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

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

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

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

drop procedure 过程名;

触发器 trigger

特殊的存储过程

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

触发器的优点如下：

触发器的缺点如下：

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

drop trigger 过程名;

类似编程中的 事件概论

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

记录关键字

Original:

Author: shadow_D

Title: MySQL 笔记二

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!