MySQL(1)

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;

备份与恢复

备份数据库

1. 停止MYSQL服务 或使用命令flush tables with read lock;设置只读锁 等备份完成之后再使用unlock tables;解锁
2. 根据表的存储引擎的不同选择将需要的文件进行备份(建议连my.cnf一起备份) MyISAM存储引擎只需备份数据库目录 InnoDB存储引擎不仅要备份数据库目录, 还需备份表空间文件和日志文件. (ibdata1, ib_logfile0, ib_logfile1)

恢复数据库

1. 停止MYSQL服务
2. 将备份的数据复制到对应目录

如果新旧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: 字段不能设为NULL
• default VAR: 设置字段默认值
• unique: 字段的值在表中唯一
• auto_increment: 自增长(从1开始, 每次+1.)
• foreign key: 外键, 指向其他表的主键 外键的级联: 当父表记录发生删除或修改时所执行的操作.
  • cascade: 自动删除或修改子表中对应的记录
  • set null: 将子表中与之对应的外键值设为NULL
  • no 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的语法格式.

转载于:https://my.oschina.net/u/3655970/blog/1528223