MySQL 数据库基本知识点

• 什么是关系型数据库
  • 关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库
  • 主流的关系型数据库有 Oracle、DB2、MySQL、SQL Server 等　　

• 什么是非关系数据库
  • 非关系型数据库的名字叫 Not Only Sql　
  • 非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定。　
  • 主流的非关系型数据库有 hbase(列模型)、redis(键值对模型)、mongodb(文档类模型)

• MySQL 与 Oracle 数据库的区别
  • 本质的区别　　
    • Oracle 数据库是一个对象关系数据库管理系统（ORDBMS）。它通常被称为 Oracle RDBMS 或简称为 Oracle，是一个收费的数据库。　　　　
    • MySQL 是一个开源的关系数据库管理系统（RDBMS）。它是世界上使用最多的 RDBMS，作为服务器运行，提供对多个数据库的多用户访问。它是一个开源、免费的数据库。　　　　
  • 数据库安全性　　
    • MySQL 使用三个参数来验证用户，即用户名，密码和位置；Oracle 使用了许多安全功能，如用户名，密码，配置文件，本地身份验证，外部身份验证，高级安全增强功能等。　　　　
  • SQL 语法的区别　　
    • Oracle 的 SQL 语法与 MySQL 有很大不同。Oracle 为称为 PL / SQL 的编程语言提供了更大的灵活性。Oracle 的 SQL * Plus 工具提供了比 MySQL 更多的命令，用于生成报表输出和变量定义。　　　　
  • 存储上的区别：　　
    • 与 Oracle 相比，MySQL 没有表空间，角色管理，快照，同义词和包以及自动存储管理。　　
  • 对象名称的区别：　　　　
    • 虽然某些模式对象名称在 Oracle 和 MySQL 中都不区分大小写，例如列，存储过程，索引等。但在某些情况下，两个数据库之间的区分大小写是不同的。
    • Oracle 对所有对象名称都不区分大小写；而某些 MySQL 对象名称（如数据库和表）区分大小写（取决于底层操作系统）。
  • 运行程序和外部程序支持：
    • Oracle 数据库支持从数据库内部编写，编译和执行的几种编程语言。此外，为了传输数据，Oracle 数据库使用 XML。
    • MySQL 不支持在系统内执行其他语言，也不支持 XML。
  • MySQL 和 Oracle 的字符数据类型比较：
    • 两个数据库中支持的字符类型存在一些差异。对于字符类型，MySQL 具有 CHAR 和 VARCHAR，最大长度允许为 65535 字节（CHAR 最多可以为 255 字节，VARCHAR 为 65535 字节）。
    • 而 Oracle 支持四种字符类型，即 CHAR，NCHAR，VARCHAR2 和 NVARCHAR2；所有四种字符类型都需要至少 1 个字节长；CHAR 和 NCHAR 最大可以是 2000 个字节，NVARCHAR2 和 VARCHAR2的最大限制是4000个字节。可能会在最新版本中进行扩展。

• MySQL 常用的数据类型

int	4 bytes	(-2 147483648，2147483647)	大整数值
bigint	8 bytes	(-9223372036854775808，9223372036854775807)	极大整数值
float	4 bytes		浮点值
char		0-255	定长字符串
varchar		0-65535	不定长字符串
date	3bytes	1000-01-01/9999-12-31	YYYY-MM-DD
datetime	8bytes	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS

• MySQL 中的主键约束
  • 主键就是主关键字(primary key)：表中的一个或多个字段，它的值用于唯一地标识表中的某一条记录。

• MySQL 中的外键约束
  • 如果表 A 的主关键字是表 B 中的字段，则该字段称为表 B 的外键，表 A 称为主表，表 B 称为从表。外键是用来实现参照完整性的，不同的外键约束方式将可以使两张表紧密的结合起来，特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。这里以 MySQL 为例，总结一下 3 种外键约束方式的区别和联系。
  • 这里以用户表和用户组表为例，这是一个典型的多对一关系，多个用户对应于一个用户组。
  • 首先创建用户组表：

create table t_group (
　　id int not null,
　　name varchar(30),
　　primary key (id)
　　);

//并插入两条记录：
　　insert into t_group values (1, 'Group1');
　　insert into t_group values (2, 'Group2');

• • 下面创建用户表，分别以不同的约束方式创建外键引用关系：
    • 级联（cascade）方式

　　create table t_user (
　　id int not null,
　　name varchar(30),
　　groupid int,
　　primary key (id),
　　foreign key (groupid) references t_group(id) on delete cascade on update cascade
　　);

// 参照完整性测试
　　insert into t_user values (1, 'qianxin', 1);  // 可以插入
　　insert into t_user values (2, 'yiyu', 2);  // 可以插入
　　insert into t_user values (3, 'dai', 3);  // 错误，无法插入，用户组3不存在，与参照完整性约束不符

// 约束方式测试
　　insert into t_user values (1, 'qianxin', 1);
　　insert into t_user values (2, 'yiyu', 2);
　　insert into t_user values (3, 'dai', 2);
　　delete from t_group where id=2;  // 导致 t_user 中的 2、3 记录级联删除
　　update t_group set id=2 where id=1;  // 导致 t_user 中的1记录的 groupid 级联修改为 2

• • • 置空(set null)方式

create table t_user (
　　id int not null,
　　name varchar(30),
　　groupid int,
　　primary key (id),
　　foreign key (groupid) references t_group(id) on delete set null on update set null
　　);

// 参照完整性测试insert into t_user values (1, 'qianxin', 1);  // 可以插入
　　insert into t_user values (2, 'yiyu', 2);  // 可以插入
　　insert into t_user values (3, 'dai', 3);  // 错误，无法插入，用户组 3 不存在，与参照完整性约束不符

// 约束方式测试
　　insert into t_user values (1, 'qianxin', 1);
　　insert into t_user values (2, 'yiyu', 2);
　　insert into t_user values (3, 'dai', 2);
　　delete from t_group where id=2;  // 导致 t_user 中的 2、3 记录的 groupid 被设置为 NULL
　　update t_group set id=2 where id=1;  //导致 t_user 中的 1 记录的 groupid 被设置为 NULL

• • 禁止(no action / restrict)方式　　

create table t_user (
　　id int not null,
　　name varchar(30),
　　groupid int,
　　primary key (id),
　　foreign key (groupid) references t_group(id) on delete no action on update no action
　　);

// 参照完整性测试
　　insert into t_user values (1, 'qianxin', 1);  // 可以插入
　　insert into t_user values (2, 'yiyu', 2);  // 可以插入
　　insert into t_user values (3, 'dai', 3);  // 错误，无法插入，用户组 3 不存在，与参照完整性约束不符

// 约束方式测试
　　insert into t_user values (1, 'qianxin', 1);
　　insert into t_user values (2, 'yiyu', 2);
　　insert into t_user values (3, 'dai', 2);
　　delete from t_group where id=2;  // 错误，从表中有相关引用，因此主表中无法删除
　　update t_group set id=2 where id=1;  // 错误，从表中有相关引用，因此主表中无法修改
// 注：在 MySQL 中，restrict 方式与 no action 方式作用相同

•  MySQL 中 count(*)、count(1)、count(字段) 的区别

• • 执行效果上：

    • count(*) 包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为 NULL　　　　

    • count(1) 包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为 NULL　　　　

    • count(列名) 只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者 0，而是表示 null）的计数，即某个字段值为 NULL 时，不统计。　　　　

  • 执行效率上：　　

    • 列名为主键，count(列名) 会比 count(1) 快　　　　

    • 列名不为主键，count(1) 会比 count(列名) 快　　　　

    • 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）　　　　

    • 如果有主键，则 select count（主键）的执行效率是最优的　　　　

    • 如果表只有一个字段，则 select count（*）最优。　　　　

•  MySQL 中的 DDL 语言
  • 数据库模式定义语言 DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。主要由 create（添加）、alter（修改）、drop（删除）和 truncate（删除） 四个关键字完成　　
• MySQL 中的 DML 语言
  • DML 操作是指对数据库中表记录的操作，主要包括表记录的插入（insert）、更新（update）、删除（delete）和查询（select）。