Mysql数据库基础知识

用于记录所学数据库（Mysql）的基础知识。

一些概念

MySQL是一个单进程多线程、支持多用户、基于客户机/服务器（Client/Server简称C/S）的关系数据库管理系统 。结构化查询语言(Structured Query Language)。

DB、DBS、DBMS、DBA

DB数据库
DBS数据库系统
DBMS数据库管理系统
DBA数据库管理员

其中，DBS包括DB和DBMS

启动、关闭mysql服务

net start mysql 以及 net stop mysql

设置密码

set password for root@localhost = password('newpassword')

连接MySQL服务器

mysql -h 127.0.0.1 -P 3306 -u root –proot
当MySQL客户机与MySQL服务器是同一台主机时，主机名可以使用localhost

数据模型

数据模型的组成要素：数据结构、数据操作、完整性约束

ER模型

ER模型中没有箭头
实体用矩形，属性用椭圆，联系用菱形
有些联系也可以具有属性，并且这些属性也要用无向边与该联系连接起来

行与列

在mysql中：

• 行 相当于 元组 相当于 记录
• 列 相当于 属性 相当于 字段
• 域：属性的取值范围
• 分量: 元组中的一个属性值（关系的每一个分量必须是一个不可分的数据项）

主码与外码，父表和子表

主表与子表，主码与子码是相对而言的。
以学生表（学号，姓名…）和学生成绩表（学号，成绩，…）为例

• 子表中不能出现父表中不存在的信息，外码与主码要对应（学生成绩表中不能出现学生表中不存在的学号）
• 更改父表中的信息子表对应信息也改变，即主码改变，对应的外码也会改变（学生表的学号改变，相应的学生成绩表中的学号也会改变）
• 先删除子表，再删除父表（先删学生成绩表，再删除学生表）

字符集和字符序

• Mysql默认使用latin1字符集（单字节编码，汉字是双字节编码）
• 常见的字符集：latin1支持西欧、希腊字符；gbk支持中文简体；big5支持中文繁体字符；utf8支持几乎所有国家的字符
• utf-8：一个汉字相当于3个字节，gbk：一个汉字相当于2个字节
• 字符序格式：字符集+国家名+ci\cs\bin，e.g. gbk_chinese_ci
• ci表示大小写不敏感；cs表示大小写敏感；bin表示按二进制编码值比较

相关的命令

• show character set;查看当前Mysql支持的字符集、字符集默认的字符序及字符集占用的最大字节长度等
• show variables like 'character%';查看当前Mysql会话使用的字符集
• show variables like 'collation%';查看当前Mysql会话使用的字符序
• set names gbk;相当于`临时一次性的`设置client、connection、results的字符集（set character_set_client = gbk;….）

数据库和表的管理

数据库管理

• create database database_name;创建数据库，可指定字符集和字符序，e.g.create database database_name default character set gbk;
• alter database database_name character set gbk;更改数据库的字符集等，用alter
• show databases;查看当前所有数据库
• show create database database_name;显示指定数据库的相关信息
• use database_name;切换当前数据库
• drop database database_name;删除数据库

表管理

存储引擎

• 存储引擎是基于表的
• Mysql默认的存储引擎是InnoDB

InnoDB

InnoDB存储引擎是事务安全的，支持外键（foreign key），提供OLTP支持，可供执行大量的增删改操作。Mysql5.6版本后，InnoDB支持全文索引。

MyISAM

MyISAM存储引擎不是事务安全的，不支持外键，提供OLAP支持，可被压缩，最早支持全文索引，优点：查询速度快。

InnoDB表空间

InnoDB存储引擎存在表空间的概念！
相关文件：

• 独享表空间会产生两个文件，扩展名分别是ibd和frm（ibd是MySQL数据文件、索引文件;frm是表结构文件）
• 共享表空间会产生一个文件，扩展名是frm。
• myisam存储引擎的表产生三个文件，扩展名分别是frm、MYD、MYI。（frm是表结构文件；MYD是数据文件；MYI是索引文件，数据和索引分离，所以速度更快）

相关命令

• show engines;查看当前Mysql支持的存储引擎
• set default_storage_engine=MyISAM;“临时地”将MySQL“当前会话的”存储引擎设置为MyISAM
• show tables;查看当前数据库的所有表
• describe table_name;简写desc table_name;查看表结构
• show create table table_name;查看表的详细信息
• drop table table_name;删除表
• show variables like 'innodb_file_per_table';查看全局变量innodb_file_per_table默认为OFF（共享表空间）
• set @@global.innodb_file_per_table = ON;设置为独享表空间

表的约束

约束范围

• 列级约束：在列定义中的约束
• 表级约束：在表定义的结尾定义的约束（若完整性涉及多个属性必须定义为表级约束）

约束对象

约束对象分为：实体完整性、参照完整性、域完整性约束

1、实体完整性：
又称行完整性，要求在表中不能存在完全相同的行，而且每行都要具有一个非空且又不重复的字段值。主要依赖主码约束实现(primary key)
2、参照完整性：
又称又称引用完整性，指表间的规则，作用于有关联的两个或两个以上的表，通过使用主键和外键（或唯一键）之间的关系，使表中的键值在相关表中保持一致。（通过foreign key约束来实现）

• 主要依赖外键约束实现（foreign key）
• 定义为表级约束，foreignkey也可定义为列级约束，但Mysql不支持
• 格式：foreign key（子表中外码字段名）references 主表（主码字段名 e.g. foreign key (Sno) references Student (Sno)
• 为了支持外键约束，表的存储引擎必须设置为InnoDB（MyISAM不支持外键约束）

3、域完整性：
又称列完整性，要求向表中指定列输入的数据必须具有正确的数据类型、格式以及有效的数据范围。

• 域完整性和用户完整性实现的方式有非空约束（NOT NULL）、默认值约束（DEFAULT）、唯一性约束（UNIQUE）和检查约束（CHECK）
• mysql是不支持check的,虽然语法上不会报错,但是分析建表语句的时候会被忽略

一些小tips

• 约束有：primary key（主键，唯一且非空）、unique（唯一）、null（空）、not null（非空）、auto_increment（自增）、default（默认）、check（检查）–mysql不支持、foreign key（外键）
• 定义了unique约束的那些列称为唯一键，唯一键可为空，最多只有一个出现Null值
• Mysql自增字段的值时从1开始递增，步长为1，每张表只能定义一个自增型字段且必须是数值型
• 只能定义为列级约束的是null、not null、auto_increment、default
  -只能定义为表级约束的情况是外键（foreign key）、 当两个及以上字段名共同组成主键
• 查看表的约束条件：select constraint_name,constraint_type from information_schema.table_constraints where table_shema = 'database_name' and table_name = 'table_name';

表结构的操作

• 创建表：create table table_name(列级约束,表级约束)其他选项设置（e.g.存储引擎、默认字符集、压缩类型(pack_keys)...）
• 复制一个表结构：create table new_table_name like 源表; 或者 create table 新表名 select * from 源表;（实现表结构的复制，甚至可以将源表的表记录拷贝到新表中
• 删除字段：alter table 表名 drop 字段名;
• 添加新字段：alter table 表名 add 新字段名 新数据类型 [ 新约束条件 ] [ first | after 旧字段名];
• 修改字段名（或者数据类型）：alter table 表名 change 旧字段名 新字段名 新数据类型;(注：不修改数据类型也要加上原来的数据类型)
• 修改字段名（或者数据类型）：alter table 表名 modify 字段名 新数据类型
• 添加约束条件：alter table 表名 add [constraint 约束名] 约束类型 (字段名)
• 删除约束条件：主键：alter table 表名 drop primary key；外键：alter table 表名 drop foreign key 约束名（注意需指定外键约束名）；唯一性约束：alter table 表名 drop index 唯一索引名(需指定唯一性索引的索引名)
• 修改表名：rename table旧表名 to 新表名
• 对于存在外键约束关系的若干个innodb表而言，若想删除父表，需要首先删除子表与父表之间的外键约束条件，解除“父子”关系后，再删除父表

表记录的更新操作

区分表结构的操作和表记录的操作：

表结构的操作是对列、约束进行操作（操作字段名 - 列操作）

• create、alter（add、drop、change、rename）、drop

表记录的操作是对行、数值进行操作（操作数据 - 行操作）

• insert、update、delete

表记录的操作

• 插入新纪录：insert into 表名 [（字段列表）] values （值列表）
• 在insert语句中使用select子句可以将源表的查询结果添加到目标表中：insert into 目标表名[(字段列表1)] select (字段列表2) from 源表 where 条件表达式
• 向char、varchar、text以及日期型的字段插入数据时，字段值要用单引号括起来
• 向字增型auto_increament字段插入数据时，建议插入null值，自增型字段在数据库中不一定是连续的
• 表记录的修改：update 表名 set 字段名1=值1,字段名2=值2,….. ,字段名n=值n [where 条件表达式]
• 表记录的删除通常使用delete语句实现，如果要清空某一个表可以使用truncate语句：delete from 表名 [where 条件表达式]和truncate table table_name