MySQL(1)
MySQL(1)
jit-hakase 发表于3个月前
MySQL(1)
  • 发表于 3个月前
  • 阅读 1
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 学生专属云服务套餐 10元起购>>>   

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更好.

数据类型

整数类型

类型大小
tinyint1字节
smallint2字节
mediumint3字节
int或integer4字节
bigint8字节

使用unsigned关键字能使数字类型变为无符号(非负)

小数类型

类型大小
float4字节
double8字节
demical(len, prec)由len和prec决定

使用unsigned关键字能使数字类型变为无符号(非负)

demical: 最精确的表示小数的类型 len表示数据的长度(不包括小数点和负号) prec表示表示小数点后数字的位数

字符串类型

类型大小适用
char(n)0-255字符定长字符串
varchar(n)0-65 535字节变长字符串
tinytext0-255字节文本字符串
text0-65 535字节文本字符串
mediumtext0-16 777 215字节文本字符串
longtext0-4 294 967 295字节文本字符串

char(n)类型存储的大小为 字符数*单个字符占用的字节 其他类型实际占用的字节数就是字符串实际占用的字节数

二进制类型

类型大小
bit(n)0-64位
binary(n)0-255字节
varbinary(n)0-65 535字节
tinyblob0-255字节
blob0-65 535字节
mediumblob0-16 777 215字节
longblob0-4 294 967 295字节

binary(n)占用n个字节, bit(n)占用n个位. 其他类型实际占用的字节数就是二进制实际占用的字节数

日期类型

类型大小格式用途
time3字节HH:MM:SS时间值或持续时间
year1字节YYYY年份值
date3字节YYYY-MM-DD日期值
datetime8字节YYYY-MM-DD HH:MM:SS混合值
timestamp4字节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+1null
null=nullnull
null!=nullnull
null is null1(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语句的唯一区别: 当插入新记录时, 若新记录的主键或唯一约束的字段值和旧记录相同, 则旧记录先被删除, 然后再插入新记录, 也就是把deleteinsert合在一起组成了一个原子操作, replace语句也可以使用update的语法格式.

  • 点赞
  • 收藏
  • 分享
粉丝 0
博文 23
码字总数 29680