MYSQL初级数据库操作

MYSQL是个好东西啊，只是比较会折腾人，所以我先记录一下MySQL 数据备份的基础知识，省得以后又去东找西找

Windows文件名、目录名不区分大小写，Linux区分大小写

查看MySQL中的数据库

这里有一个shop的数据库，
information_schema（系统数据库定义了所有数据库对象的元数据信息）、
mysql（记录MySQL账号信息以及账户的访问权限）、
performance_schema（收集MySQL服务器的性能数据）、
sys（这个库是通过视图的形式把information_schema 和performance_schema结合起来，查询出更加令人容易理解的数据存储过程可以可以执行一些性能方面的配置，也可以得到一些性能诊断报告内容存储函数可以查询一些性能信息）

查看数据库放置路径

路径下的shop文件夹中的db.opt记录着当前数据库默认字符集及字符序等信息，可直接对其修改，或者 MySQL命令行：alter database shop character set gbk;

查看shop数据库信息

删除shop，再来创建一个shop

创建表（记得先指明数据库：use shop;)

执行sql脚本，使用的资料可以去这里下载

查看表

指明要查看表的结构

查看存储引擎

可以看到InnoDB是默认的存储引擎，如果想临时修改会话的引擎可以使用

如果想要更改表的存储引擎可以使用

以shop数据库，shohin表为例

MyISAM的文件结构

• shop文件夹中

shohin.frm 记录了表的结构
shohin.MYD 表数据文件
shohin.MYI 表索引文件

工作原理

• MyIASM

记录首先在MySQL服务器内存上产生，然后写入到外存中的MYD中，最后将索引信息写入到MYI中，产生时间延迟主要是缓存的原因

MyIASM备份很简单，直接复制shop文件夹即可

---

InnoDB的文件结构

• shop文件夹中

shohin.frm 记录了表的结构
db.opt 数据库的字符集和字符序等信息
table_name.ibd 独享表空间存放另一个表的数据文件

• shop文件同级目录下

ibdata1 表空间文件存放数据（共享的）后面还有独享的
ib_logfile0、ib_logfile1 重做日志文件信息

不知道重做日志，故百度内容如下：

重做日志redo log file是LGWR进程从Oracle实例中的redo log buffer写入的，是循环利用的。就是说一个redo log file(group) 写满后，才写下一个。
归档日志archive log是当数据库运行在归档模式下时，一个redo log file(group)写满后，由ARCn进程将重做日志的内容备份到归档日志文件下，然后这个redo log file(group)才能被下一次使用。
不管数据库是否是归档模式，重做日志是肯定要写的。而只有数据库在归档模式下，重做日志才会备份，形成归档日志。
一般来说，归档日志结合全备份，用于数据库出现问题后的恢复使用。
重做日志是循环使用的。比如说，有三个重做日志组A、B、C。那么，当A写满后，系统就调用ARCn进程，将A备份为归档日志，同时B已经开始使用了。

工作原理

• InnoDB

记录首先在MySQL服务器内存上产生，然后写入到外存中的ibdata1中，最后将重做日志信息写入到ib_logfile0、ib_logfile1中，基本没有时间延迟

InnoDB备份，首先复制shop文件夹，然后复制ibdata1、ib_logfile0、ib_logfile1文件

值得注意的是ibdata1是InnoDB的共享表空间，

InnoDB的表空间

• 共享表空间

存放了所有InnoDB表的数据信息、索引信息、各种元数据信息以及事务回滚信息，默认情况下存放在data根目录下的ibdata1中

• 独享表空间

存放的是该表的信息，一般命名为table_name.ibd，路径是：本文在shop数据库中

默认是关闭独享表空间，可使用set @@global.innodb_file_per_table=ON;开启

但操作独享表空间依然会影响到ibdata1、ib_logfile0、ib_logfile1文件，

变量

分为系统变量（全局变量、会话变量）、用户自定义变量（以后有时间再说）

• 查看系统变量

全局变量(393个）： show global variables;
会话变量（393+12）：show session variables;

@@能标记会话系统变量，若会话系统变量不存在则标记全局变量，查看会话变量不存在时，返回全局变量

备份时：

flush tables with read lock; 可以禁止更新，但无法禁止查询

unlock tables; 可以解锁前面的禁止

表结构

先来看看MySQL的数据类型

好家伙，还挺多。。。
日期时间型的datetime和timestamp区别

每次启动前记得. E:\mysql\init.sql设置一下字符集，如有不懂可参照MYSQL之字符问题探究
创建一个表实验一下

插入数据（数据只能一行一行的插入）：

timestamp是记录当前服务器的时间，所以即使插入null，它也是会记录当前时间的，事实上日期时间型属于数值类型可进行简单加减运算

通过show variables like ‘time_zone%’;可查看当前MySQL的时间设置

MySQL复合类型（set、enum）
enum只能从集合中取一个值，set可以取多个值
例子：
创建一个含set、和enum集合的表

插入数据（报错：ERROR 1265 (01000): Data truncated for column ‘interest’ at row 1）
解决： MySQL默认strict mode，即严格sql语句模式，可以临时修改为”ansi”模式，语句：set sql_mode=’ansi’;

修改成功

选择

因为只能选择set集合里面的东西，所以上面的音乐就忽略掉了。

MySQL约束（6种）

1、主键约束

字段名 数据类型[其他约束条件] primary key
若为复合主键：primary key(字段名1，字段名2)

2、非空约束

字段名 数据类型 not null

3、检查约束
MySQL事实上不支持检查约束

若为字符串类型，则可用enum或set实现检查约束
若为数值类型，则用触发器实现
若为其他类型，最好用触发器实现

4、默认值约束

字段名 数据类型[其他约束条件] default 默认值

5、唯一性约束

字段名 数据类型 unique

6、外键约束
外键约束只要用于定义表与表之间的关系，表A外键字段要么是NULL，要么函数依赖于表B主键字段的值，（此时将表A称为表B的子表，表B称为表A的父表）

constraint 约束名 foreign key （表A字段名或者字段名列表） references 表B （字段名或字段名列表）[on delete级联选项][on update级联选项]

级联选项4种取值如下：
cascade:父表记录的删除或者更新操作会自动删除或更新子表中与之对应的记录
set null:父表记录的删除（delete）或者更新会将子表中与之对应记录的外键值自动设置为null值
no action:父表记录的删除或更新操作如果子表存在与之对应的记录那么删除或更新操作将失败
restrict ：与no action功能相同，且为级联选项的默认值

注意： 如果某个字段存在多种约束条件，则约束条件的顺序是任意的。

自增型字段

字段名 数据类型 auto_increment

注意 自增型字段的数据类型必须为整数，若向自增型字段插入一个NULL值或0，字段值会被自动设置为比前一个值大1，即字段值总是比前一个字段值大，最好将自增型字段设置为主键，否则可能将出现一些错误

---

创建表时，可以在表的括号外面设置engine=存储引擎类型 default charset=字符集类型 （若省略则将沿用character_set_database的默认值）

ps: 仅对MyISAM存储引擎有用的选项

• pack_keys=压缩类型

0 取消所以索引中所以关键字的压缩
1 将压缩索引中所有关键字的存储空间
default 表示只压缩索引中字符串类型的关键字，但不压缩数值类型的关键字

复制一个表结构

法一：

create table new_table like old_table;

法二：

create table new_table select * from old_table

group by后面若接条件表达式不能接where。
对一个数据列建立约束称为列级约束。（列级约束可声明在列定义时或列定义后）
对多个数据列建立约束称为表级约束。（表级约束只能声明在列定义后）

show columns from table_name 和desc table_name 的效果是一样的。

主键唯一且不能取空值，外键（要么null，要么是父表的主键）外键与主键的数据类型最好一致。
唯一性约束是通过唯一性索引实现的，故删除unique约束只需要删除唯一性索引即可。
order by子句位置应该保证它是select语句中最后一条子句。
and的优先级高于or，in操作符一般比一组or执行更快。
通配符只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。
%不会匹配NULL。
[ ]只有sql server和Access支持。。伤啊

小结

MySQL确实很实在，对我而言命令行操作也确实十分的带劲，各种炫酷的语句，很快，好使，不错，加油！