建表属于 DDL 语句,表结构的增删改 create, drop, alter ...
在每个数据库 DB 都中都有数据表对应的 frm 结构文件
frm 结构文件来自 innodb 存储引擎,innodb 存储引擎所有的文件都存储在外部的 ibdata 文件中
基本语法
SHOW TABLES;
显示数据库结构
DESCRIBE 表名; DESC 表名; SHOW COLUMNS FROM 表名;
表结构
显示长度:指数据在显示的时候最多显示的长位
zerofill: 0 填充自带 unsigned
浮点数
float: 4 数据精度 7
double: 8 数据精度 15
定点数
decimal
year 可以 2 位数【69,70界限】插入或者 4 位数插入年份
两个数据处理函数
mysql的日期格式:
char
varchar: 可变 【数据后增加1~2字节的额外开销】
char 查询效率比 varchar 高
数据超 255 一般使用 text
类型
级别
比如: tinytext
注意二进制类型需要使用IO流
enum(数值1, ...)
字符串枚举类型,最多可以包含 65,535 个枚举值。插入的数据必须位于列表中,并且只能命中其中一个值;如果不在,将插入一个空值
大小 1~2 字节
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 唯一键 保障数据唯一
primary key 主键
基础语法
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 段名; # 删除
基本语法
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 语句
保障:表中数据有效性和完整性
分类:
非空约束: 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
约束,该字段自动被视为主键
相关术语
任何一张表都应该有主键,没有主键表无效
主键值特性:唯一不空
# 单一主键 CREATE TABLE 数据表名称( 字段名 字段类型 PRIMARY KEY, # 设置主键,列级约束 ..., 字段名 字段类型 [字段属性] ) [表选项]; # 复合主键 CREATE TABLE 数据表名称( 字段名 字段类型 [字段属性], ..., 字段名 字段类型 [字段属性], PRIMARY KEY(字段1, 字段2) # 表级约束 ) [表选项];
主键分类:
常用自然主键
注意:
相关术语
外键由表中的一个字段或者多个字段构成,一个表的外键用来指向另一个表的主键 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 主表(字段) # 设置外键,主表中字段不一定是主键,但必须具有唯一性 ) [表选项];
数量:一个表可以有多个外键,但是只能有一个主键
注意逻辑顺序:
外键需求:
数据库的存储引擎决定了表在计算机中的存储方式,不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎还可以获得特定的功能
MySQL 支持的存储引擎:使用 show engines \G
常用存储引擎:
MyISAM
一张表结构:
可以被转换为压缩、只读表节省空间
InnoDB
MySQL 默认存储引擎,支持事务可以数据恢复
表格式文件 (.frm),数据存储在表空间中
最大特色:支持事务
Memory
数据存储在内存中,查询快,关机数据完蛋
一个事务其实就是一个完整的业务逻辑
事务是必须满足4个条件 (ACID):
只有 insert, delete, update 三个语句才和事务相关,即 DML 语句
为了数据安全
本质,一个事务就是多条 DML 语句同时成功或失败
事务执行过程
开启事务 start transaction
关闭默认自动提交功能
提交事务 commit
清空事务性活动日志文件,将数据全部持久化到数据库
标志事务结束,全部成功
回滚事务 rollback
清空事务性活动日志文件,撤销之前全部 DML 操作
标志事务结束,全部失败
增加回滚点
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
字段都会自动添加索引
应用需求
记忆不要随便添加索引,索引需要被维护
索引数
索引字段属性
. . .
唯一性比较弱的字段,添加索引用处不大
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录
CREATE INDEX 索引名 ON 表(字段); # 复合索引 CREATE INDEX 索引名 ON 表(字段1, 字段2);
DROP INDEX 索引名 ON 表(字段);
使用 EXPLAIN
解释 SQL
应用模糊查询已 %
开始会索引失效
逻辑短路,使用 or
一边没有索引一边有索引
没有索引为假,直接导致结构为假【或运算特点】,将忽略有索引的字段
解决使用 union
等价取代 or
,尽量减少使用
使用复合索引,没有使用左侧的列查找,索引失效
在 where 中索引参与运算,索引失效
在 where 中索引使用函数,索引失效
什么是视图?
站在不同角度去看待同一份数据
视图是一张虚拟表
CREATE VIEW 视图名字 AS SELECT 指令;
注释:视图总是显示最新的数据!每当用户查询视图时,数据库引擎通过使用视图的 SQL 语句重建数据
DROP VIEW 视图名;
我们可以面向视图进行增删改查 (对视图对象的增删改查,会导致原表被操作)
视图起到简化SQL的复杂度
增删查改:CRUD
专门备份: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 变量名 = 值;
set global 变量名 = 值;
或者 set @@global.变量名 = 值
当前用户,当前客户端生效
变量赋值符 :=
定义变量
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 函数名;
存储过程是一组为了完成特定功能的 SQL 语句集合
使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来,这个过程经编译和优化后存储在数据库服务器中,因此称为存储过程
修改语句结束符 create procedure 名(形参) begin 过程体 end 语句结束符 修改语句结束符
call 过程名([实参列表]);
drop procedure 过程名;
特殊的存储过程
给一些表的记录绑定一段代码
触发器的优点如下:
触发器的缺点如下:
修改语句结束符 create trigger 触发器 触发时机 触发事件 on 表 for each row begin 过程体 end 语句结束符 修改语句结束符
drop trigger 过程名;
类似编程中的事件概论
触发时机
触发事件
触发对象 on 表 for each row
一张表中对应类型只有一种,最多只有 6 个触发器
记录关键字