MySQL基础
概述
数据库管理系统常用数学模型有 层次模型 网状模型 关系模型 面向对象模型等 其中**关系型数据库管理系统(RDBMS)**占主导地位
RDBMS记录数据的方式是二维表 其中列又称字段或属性, 行又称记录或元组.
名词解释
数据库(DB): 按照某种数据结构对数据进行组织, 存储和管理的容器. 数据库管理系统(DBMS): 安装在操作系统之上, 管理和和控制数据库中各种数据库对象的系统软件. SQL(Structured Query Language): 应用最为广泛的关系数据库语言. E-R图: 数据库设计时常用的图模型
范式(NF)
范式是符合某一种级别的关系模式的集合
函数依赖: A决定B, 则B函数依赖于A.
主键: 可以唯一标识行的列
- 1NF: 表中列不可重复, 都是单一属性, 不可再分.
- 2NF: 基于1NF, 所有非主键列都函数依赖于主键.
- 3NF: 基于2NF, 不存在非主键列对任一候选列的函数依赖.
- BCNF: 基于2NF, 不存在任何列对任一候选列的函数依赖.
存储引擎
MySQL使用插件式存储引擎, 不同的表可以使用不同的存储引擎. 使用命令show engines;
查看MySQL支持的存储引擎 常用的存储引擎有MyISAM和InnoDB
InnoDB: 事务安全, 支持外键. MyISAM: 非事务安全, 不支持外键, 但是查询速度很快.
设置默认存储引擎 临时修改: 使用命令 set default_storage_engine=ENGINE
永久修改: 修改my.cnf文件中default-storage-engine参数值
需要执行大量增删改操作, 出于安全考虑, 选择InnoDB更好.
数据类型
整数类型
类型 | 大小 |
---|---|
tinyint | 1字节 |
smallint | 2字节 |
mediumint | 3字节 |
int或integer | 4字节 |
bigint | 8字节 |
使用unsigned
关键字能使数字类型变为无符号(非负)
小数类型
类型 | 大小 |
---|---|
float | 4字节 |
double | 8字节 |
demical(len, prec) | 由len和prec决定 |
使用unsigned
关键字能使数字类型变为无符号(非负)
demical: 最精确的表示小数的类型 len表示数据的长度(不包括小数点和负号) prec表示表示小数点后数字的位数
字符串类型
类型 | 大小 | 适用 |
---|---|---|
char(n) | 0-255字符 | 定长字符串 |
varchar(n) | 0-65 535字节 | 变长字符串 |
tinytext | 0-255字节 | 文本字符串 |
text | 0-65 535字节 | 文本字符串 |
mediumtext | 0-16 777 215字节 | 文本字符串 |
longtext | 0-4 294 967 295字节 | 文本字符串 |
char(n)类型存储的大小为 字符数*单个字符占用的字节 其他类型实际占用的字节数就是字符串实际占用的字节数
二进制类型
类型 | 大小 |
---|---|
bit(n) | 0-64位 |
binary(n) | 0-255字节 |
varbinary(n) | 0-65 535字节 |
tinyblob | 0-255字节 |
blob | 0-65 535字节 |
mediumblob | 0-16 777 215字节 |
longblob | 0-4 294 967 295字节 |
binary(n)占用n个字节, bit(n)占用n个位. 其他类型实际占用的字节数就是二进制实际占用的字节数
日期类型
类型 | 大小 | 格式 | 用途 |
---|---|---|---|
time | 3字节 | HH:MM:SS | 时间值或持续时间 |
year | 1字节 | YYYY | 年份值 |
date | 3字节 | YYYY-MM-DD | 日期值 |
datetime | 8字节 | YYYY-MM-DD HH:MM:SS | 混合值 |
timestamp | 4字节 | YYYY-MM-DD HH:MM:SS | 混合值或时间戳 |
取值范围 time: -838:59:59 ~ 838:59:59
year: 1901 ~ 2155
date: 1000-01-01 ~ 9999-12-31
datetime: 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp: 1970-01-01 00:00:01 ~ 2037-12-31 23:59:59
填入当前时间 除了timestamp类型可以填入NULL以外, 其他类型必须使用now()
函数.
now()函数有一个别名函数curtime() now(n), n<=6, 可以显示更精确的时间 microsecond(now(n)) 用于获取微秒时间
复合类型
enum类型: 从一个集合中取得单个值, 类似单选框. set类型: 从一个集合中取得N个值, 类似复选框.
表示形式: TYPE('VALUE','VALUE',...)
填入set类型数据: 'VALUE,VALUE,...'
使用这两种类型可以实现简单的字符串类型数据的检查约束.
特殊值NULL
公式 | 结果 |
---|---|
null+1 | null |
null=null | null |
null!=null | null |
null is null | 1(true) |
除了is运算符之外, 无法与null特殊值进行任何有意义的运算.
数据库管理
查看数据库: show databases;
创建数据库: create database DB_NAME;
查看数据库结构: show create database DB_NAME;
选择数据库: use DB_NAME
删除数据库: drop database DB_NAME;
备份与恢复
备份数据库
- 停止MYSQL服务 或使用命令
flush tables with read lock;
设置只读锁 等备份完成之后再使用unlock tables;
解锁 - 根据表的存储引擎的不同选择将需要的文件进行备份(建议连my.cnf一起备份) MyISAM存储引擎只需备份数据库目录 InnoDB存储引擎不仅要备份数据库目录, 还需备份表空间文件和日志文件. (ibdata1, ib_logfile0, ib_logfile1)
恢复数据库
- 停止MYSQL服务
- 将备份的数据复制到对应目录
如果新旧MYSQL服务器的数据库目录不同, 还需修改my.cnf中的datadir参数信息.
也可用mysqldump
命令生成sql文件用于备份和恢复
表结构管理
查看表: show tables;
创建表: create table TBL_NAME(...);
查看表结构: desc TBL_NAME;
删除表: drop table TBL_NAME
查看表结构也可使用以下命令来查看原生创建命令. show create table TBL_NAME;
创建表
create table TBL_NAME(
ATTR TYPE [CONSTRAINT],
...
ATTR TYPE [CONSTRAINT]
)[OTHER OPTIONS];
使用约束条件(CONSTRAINT)
mysql不支持check约束, 利用其他方法(复合数据类型, 触发器等)实现.
primary key
: 主键, 唯一标识记录的字段.not null
: 字段不能设为NULLdefault
VAR: 设置字段默认值unique
: 字段的值在表中唯一auto_increment
: 自增长(从1开始, 每次+1.)foreign key
: 外键, 指向其他表的主键 外键的级联: 当父表记录发生删除或修改时所执行的操作.cascade
: 自动删除或修改子表中对应的记录set null
: 将子表中与之对应的外键值设为NULLno action
: 若子表存在对应记录, 则删除或修改操作失败restrict
: 同no action
, 是外键级联的默认值.
外键语法 外键: 约束表与表之间的关系, 外键字段的取值, 是其他表中的主键取值或者NULL. 语法理解: 表A的外键参照表B的主键
constraint 约束名 foreign key 表A字段 references 表B字段
[on delete 级联选项] [on delete 级联选项]
主键约束也可以使用外键的语法, 通常用于多主键的表. 唯一约束也可以使用外键语法.
使用其他选项(OTHER OPTIONS)
- engine=ENGINE 设置存储引擎
- default charset=CHARSET 设置默认字符集
- pack_keys=PACK_TYPE 设置索引关键字压缩类型(仅MyISAM存储引擎有效)
复制表
仅复制表结构 create table TBL_NAME like OLD_TBL_NAME;
复制表结构以及记录 create table TBL_NAME select * from OLD_TBL_NAME;
无法复制表的约束条件和外键关系, 如果要完全复制, 可借助mysqldump工具.
修改表
修改表名
使用以下任意一条语句 rename table OLD_TBL_NAME to NEW_TBL_NAME;
alter table OLD_TBL_NAME rename NEW_TBL_NAME;
修改列
删除字段 alter table TBL_NAME drop ATTR;
添加字段 alter table TBL_NAME add NEW_ATTR [CONSTRAINT] [first | after OLD_ATTR];
修改字段 修改字段名的同时修改数据类型 alter table TBL_NAME change OLD_ATTR NEW_ATTR NEW_TYPE;
仅修改数据类型 alter table TBL_NAME modify ATTR NEW_TYPE;
修改约束条件
添加约束条件 alter table TBL_NAME add constraint 约束名 约束类型 (字段名);
删除约束条件 删除主键约束条件 alter table TBL_NAME drop primary key;
删除外键约束条件 alter table TBL_NAME drop foreign key 外键约束名;
删除唯一性约束条件 alter table TBL_NAME drop index 唯一约束名;
修改其他选项
其他选项包括存储引擎, 默认字符集, 自增字段初始值, 索引关键字压缩类型等 alter table TBL_NAME OTHER_OPTION=NEW_OPTION;
表记录操作
插入新记录(insert)
插入一条记录 insert into TBL_NAME[(ATTR...)] values(VALUE...);
插入批量记录
insert into TBL_NAME[(ATTR...)] values
(VALUE...),
...
(VALUE...);
Oracle
数据库不支持插入批量记录
插入查询结果集 insert into TBL_NAME[(ATTR...)] select ...;
向auto_increment字段插入数据时, 最好用NULL值. 向默认值约束字段插入数据时, 可用default关键字 若有外键约束关系, 原则上先给父表插入数据, 再给子表插入数据 即使insert操作执行失败, auto_increment的值也会递增.
更新记录(update)
更新符合条件的记录
update TBL_NAME
set ATTR=VALUE,
...
set ATTR=VALUE
where ...;
删除记录(delete)
删除符合条件的记录 delete from TBL_NAME where ...;
清空表记录 truncate TBL_NAME;
使用truncate
会重置自增列的序号
替换记录(replace)
replace
语句与insert
语句的唯一区别: 当插入新记录时, 若新记录的主键或唯一约束的字段值和旧记录相同, 则旧记录先被删除, 然后再插入新记录, 也就是把delete
和insert
合在一起组成了一个原子操作, replace
语句也可以使用update
的语法格式.