day43---MySQL总结一

1. 数据库管理软件的由来

  • 数据想永久性存储只能以文件的形式存储在硬盘上，但是文件存储会有一系列的问题存在

    • 程序的所有组件不可能只存在一台机器上
    • 数据的安全问题
    • 数据的共享问题
    • 应用程序的性能问题
    • 访问的性能问题
  • 我们在编写任何程序之前，都需要事先写好基于网络操作一台主机上文件的程序(socket服务端与客户端程序)，于是有人将此类程序写成一个专门的处理软件，这就是mysql等数据库管理软件的由来，但mysql解决的不仅仅是数据共享的问题，还有查询效率，安全性等一系列问题，总之，把程序员从数据管理中解脱出来，专注于自己的程序逻辑的编写

2. 数据库相关概念

  • 数据(data)：针对事务的某些属性进行描述和记录的值叫做数据

  • 数据表(table)：按照一定格式和机制进行分类对数据进行存储的文件叫做数据表。数据表在系统中以文件的形式存储，用来存放和提供增删改查多行/多条数据记录的操作

  • 数据库(database,简称DB)：按照一定的格式把数据存放在仓库中叫做数据库。数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可一直多用户的共享与并发需求。数据库在系统中以目录的形式存储，用来组织文件/表

  • 数据库管理系统(database management system,简称DBMS)：用于管理数据库的软件叫做数据库管理系统，用来管理和操作数据库

  • 数据库服务器(database server)：用于运行数据库管理系统的物理设备

3. 数据库管理技术的发展历程

  • 人工管理阶段

    • 描述：20世纪50年代中期以前，计算机主要用于科学计算；当时的硬件水平：外存只有纸带、卡片、磁带，没有磁盘等直接存取的存储设备；当时的软件状况：没有操作系统，没有管理数据的软件，数据的处理方式是批处理
    • 特点：
      • 数据不保存：计算机主要用于科学计算，数据临时用，临时输入，不保存
      • 应用程序管理数据：数据要有应用程序自己管理，应用程序需要处理数据的逻辑+物理结构，开发负担很重
      • 数据不共享：一组数据只对应一个程序，多个程序之间涉及相同数据时，必须各自定义，造成数据大量冗余
      • 数据不具有独立性：数据的逻辑结构或物理结构发生变化后，必须对应用程序做出相应的修改，开发负担进一步加大

  • 文件系统阶段

    • 描述：20世纪50年代后期到60年代中期，硬件水平：有了磁盘、磁鼓等可直接存取的存储设备；软件水平：有了操作系统，并且操作系统中已经有了专门的数据管理软件，即文件系统；处理方式上不仅有了批处理，而且能够联机实时处理
    • 文件系统管理数据的优点：
      • 数据可以长期保存：计算机大量用于数据处理，因而数据需要长期保存，进行增删改查操作
      • 使用文件系统管理数据：文件系统这个软件，把数据组织成相对独立的数据文件，利用按文件名，按记录进行存取。实现了记录内的结构性，但整体无结构。并且程序与数据之间由文件系统提供存取方法进行转换，是应用程序与数据之间有了一定的独立性，程序员可以不必过多考虑物理细节
    • 文件系统管理数据的缺点：
      • 数据共享性差，冗余度大：一个文件对应一个应用程序，不同应用有相同数据时，也必须建立各自的文件，不能共享相同的数据，造成数据冗余，浪费空间，且相同的数据重复存储，各自管理，容易造成数据不一致性
      • 数据独立性差：一旦数据的逻辑结构改变，必须修改应用程序，修改文件结构的定义，应用程序的改变，也将引起文件的数据结构的改变，数据与程序之间缺乏独立性。文件系统仍然是一个不具有弹性的无结构的数据集合，即文件之间是孤立的，不能反映现实世界事物之间的内存联系

  • 数据系统阶段

    • 描述：20世纪60年代后期以来，计算机用于管理的规模越来越大，应用越来越广泛，数据量急剧增长，同时多种应用，多种语言互相覆盖地共享数据结合要求越来越强烈；硬件水平：有了大容量磁盘，硬件架构下降；软件水平：软件价格上升(开发效率必须提升，必须将程序员从数据管理中解放出来)，分布式的概念盛行
    • 数据库系统的特点：
      • 数据结构化
      • 数据共享，冗余度低，易扩充
      • 数据独立性高
      • 数据由DBMS统一管理和控制
        • 数据的安全性保护
        • 数据的完整性检查
        • 数据的访问并发控制
        • 数据库的灾难恢复

4. 数据库的分类

  • 关系型数据库：

    • 特点：有详细的表结构，支持sql语法语句，存储在硬盘上，可持久化保存
    • 常见的关系型数据库：
      • MySQL
      • Oracle
      • SQL Server
      • DB2
      • sqllite
      • ...

  • 非关系型数据库(NoSQL)：

    • 特点：没有表结构，使用key-value的形式存储，使用内存存储(部分也持久化保存再硬盘上)，查询效率高
    • 常见的非关系型数据库：
      • Redis
      • Memcache
      • MongoDB
      • ...

5. SQL语言介绍

  • 描述：SQL(Structured Query Language 即结构化查询语言)主要用于存取数据、查询数据、更新数据和管理关系数据库系统，SQL语言由IBM开发

  • SQL语言分为4种类型：

    • 数据定义语言，DDL语句(Data Definition Language)：用于定义/修改/删除数据库、表、视图、索引、存储过程，CREATE语句主要用于创建数据库，创建表，创建视图。ALTER语句主要用于修改表的定义，修改视图的定义。DROP语句主要用于删除数据库，删除表和删除视图等
    • 数据操作语言，DML语句(Data Manipulation Language)：INSERT、UPDATE、DELETE语句；数据库操作语言，用于插入/更新/删除数据。INSERT语句用于插入数据，UPDATE语句用于更新数据，DELETE语句用于删除数据
    • 数据查询语言，DQL语言(Data Query Language)：SELECT语句，用于查询数据
    • 数据控制语言，DCL语言(Data Control Language )语句：数据库控制语言，用于控制用户的访问权限。其中GRANT语句用于给用户增加权限，REVOKE语句用于收回用户的权限

6. MySQL的介绍与发展

  • 发展：
    • MySQL最初的出发点是用mSQL和他们自己的快速低级例程(ISAM)去连接表格。不管怎样，在经过一些测试后，开发者得出结论：mSQL的速度或灵活性不足以满足要求。这导致了为数据库提供了新的SQL接口，这样，这个API被设计成允许为用于mSQL而写的第三方代码更容易移植到MySQL。大多数认为在不需要处理事务化处理的情况下，MySQL是管理内容最好的选择。
    • MySQL的海豚标志的名字叫“sakila”，它是由MySQL AB的创始人从用户在“海豚命名”的竞赛中建议的大量的名字表中选出的。获胜的名字是由来自非洲斯威士兰的开源软件开发者Ambrose Twebaze提供。根据Ambrose所说，Sakila来自一种叫SiSwati的斯威士兰方言，女性化名称Sakila源自SiSwati。Sakila也是坦桑尼亚、Arusha地区的一个镇的镇名，靠近Ambrose的母国乌干达。
    • MySQL名称的起源不明。一直以来，我们的基本目录以及大量库和工具均采用了前缀“my”。不过，共同创办人Monty Widenius的女儿名字也叫“My”。时至今日，MySQL名称的起源仍是一个迷，即使对我们也一样。
    • MySQL的历史可以追溯到1979年，一个名为Monty Widenius的程序员在为TcX的小公司打工，并且用BASIC设计了一个报表工具，使其可以在4MHz主频和16KB内存的计算机上运行。当时，这只是一个很底层的且仅面向报表的存储引擎，名叫Unireg。
    • 1990年，TcX公司的客户中开始有人要求为他的API提供SQL支持。Monty直接借助于mSQL的代码，将它集成到自己的存储引擎中。令人失望的是，效果并不太令人满意，决心自己重写一个SQL支持。
    • 1996年，MySQL1.0发布，它只面向一小拨人，相当于内部发布。到了1996年10月，MySQL3.11.1发布(MySQL没有2.x版本)，最开始只提供Solaris下的二进制版本。一个月后，Linux版本出现了。在接下来的两年里，MySQL被依次移植到各个平台。
    • 1999 ~ 2000年，MySQL AB公司在瑞典成立。Monty雇了几个人与Sleepycat合作，开发出了Berkeley DB引擎，由于BDB支持事务处理，因此MySQL从此开始支持事务处理了。
    • 2000年，MySQL不仅公布自己的源代码，并采用GPL(GNU General Public License)许可协议，正式进入开源世界。同年4月，MySQL对旧的存储引擎ISAM进行了整理，将其命名为MyISAM。
    • 2001年，集成Heikki Tuuri的存储引擎InnoDB，这个引擎不仅能持事务处理，并且支持行级锁。后来该引擎被证明是最为成功的MySQL事务存储引擎。MySQL与InnoDB的正式结合版本是4.0。
    • 2003年12月，MySQL5.0版本发布，提供了视图、存储过程等功能。
    • 2008年1月，MySQL AB公司被Sun公司以10亿美金收购，MySQL数据库进入Sun时代。在Sun时代，Sun公司对其进行了大量的推广、优化、Bug修复等工作。
    • 2008年11月，MySQL 5.1发布，它提供了分区、事件管理，以及基于行的复制和基于磁盘的NDB集群系统，同时修复了大量的Bug。
    • 2009年4月，Oracle公司以74亿美元收购Sun公司，自此MySQL数据库进入Oracle时代，而其第三方的存储引擎InnoDB早在2005年就被Oracle公司收购。
    • 2010年12月，MySQL 5.5发布，其主要新特性包括半同步的复制及对SIGNAL/RESIGNAL的异常处理功能的支持，最重要的是InnoDB存储引擎终于变为当前MySQL的默认存储引擎。MySQL 5.5不是时隔两年后的一次简单的版本更新，而是加强了MySQL各个方面在企业级的特性。Oracle公司同时也承诺MySQL 5.5和未来版本仍是采用GPL授权的开源产品。
    • MySQL由于它的开源性被广泛传播，也让更多的人了解到这个数据库。它的历史也富有传奇性。在这里仅是作为一个故事讲解了MySQL的发展史，在网上很多版本也会有不同。伴随着更多的技术开发人员加入到MySQL的开发中，不断完善，发展会越来越好。
  • 描述：MySQL一般特制完整的MySQLRDBMS，是一个开源的关系型数据库管理系统(Relational Database Management System)，现在属于Oracle公司(甲骨文公司)。随着MySQL功能的不断完善，性能不断提高，又有开源免费的优势，越来越多的企业选择使用MySQL，而放弃商用收费的Oracle
  • 程序架构：MySQL是一个真正的多用户、多线程SQL数据库服务器，MySQL采用的是客户/服务器架构，由一个服务器守护程序mysqld和许多不同的客户程序以及库组成的。
    • 一个是MySQL服务器程序，指的是mysqlId程序，运行在数据库服务器上，负责在网络上监听并处理来自客户端的服务请求根据这些请求去访问数据库的内容，再把有关信息回传给客户
    • 另一个程序是MySQL客户端程序，负责连接到数据库服务器，并通过发出命令来告知服务器它想要的操作
  • 逻辑架构：
    • 最上层是大部分基于网络的C/S服务都有的部分，比如连接处理、授权认证、安全等
    • 第二层包括MySQL的很多核心服务功能，包括查询解析、分析、优化、缓存以及所有的内置函数(例如，日期、时间、数学和加密函数)，所有的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等
    • 第三层包含了存储引擎，存储引擎负责MySQL中欧数据的存储和提取，是数据库中非常重要非常核心的部分，也是MySQL区别与其他数据库的一个重要特性
  • 体系架构：
    • 连接池组件
    • 管理服务和工具组件、SQL接口组件、查询分析器组件、优化器组件、缓冲组件
    • 插件式存储引擎
    • 物理文件
  • 实例架构：数据库指物理上的存储文件或其他形式文件类型的集合；实例是用来操作数据库文件的，MySQL数据库由后台线程以及一个共享内存区组成。在MySQL数据库中，实例与数据库通常是一一对应的，这时两个词可以互换，但在集群情况下，可能存在一个数据库被对个数据实例使用的情况。MySQL是单进程多线程架构的数据库，实例在系统上的表现就是一个进程

7. MariaDB的介绍

  • 发展：成立于2009年，MySQL之父Michael “Monty” Widenius用他的新项目MariaDB完成了对MySQL的“反戈一击”。开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。 过去一年中，大型互联网用户以及Linux发行商纷纷抛弃MySQL，转投MariaDB阵营。MariaDB是目前最受关注的MySQL数据库衍生版，也被视为开源数据库MySQL的替代品。
  • 起源：MySQL之父Widenius先生离开了Sun之后，觉得依靠Sun/Oracle来发展MySQL，实在很不靠谱，于是决定另开分支，这个分支的名字叫做MariaDB。MariaDB跟MySQL在绝大多数方面是兼容的，对于开发者来说，几乎感觉不到任何不同。目前MariaDB是发展最快的MySQL分支版本，新版本发布速度已经超过了Oracle官方的MySQL版本。Oracle控制下的MySQL开发，有两个主要问题：1.MySQL核心开发团队是封闭的，完全没有Oracle之外的成员参加。很多高手即使有心做贡献，也没办法做到。2.MySQL新版本的发布速度，在Oracle收购Sun之后大为减缓。Widenius有一个ppt，用数据比较了收购之前和之后新版本的发布速度。有很多bugfix和新的feature，都没有及时加入到发布版本之中。MySQL是开源社区的资产，任何个人/组织都无权据为己有。为了依靠广大MySQL社区的力量来更快速的发展MySQL，另外开分支是必须的。
  • 描述：MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来代替MySQL的InnoDB。 MariaDB由MySQL的创始人Michael Widenius（英语：Michael Widenius）主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字。
  • 说明：MariaDB基于事务的Maria存储引擎，替换了MySQL的MyISAM存储引擎，它使用了Percona的 XtraDB，InnoDB的变体，分支的开发者希望提供访问即将到来的MySQL5.4InnoDB性能。这个版本还包括了 PrimeBase XT (PBXT)和FederatedX存储引擎。MariaDB直到5.5版本，均依照MySQL的版本。因此，使用MariaDB5.5的人会从MySQL5.5中了解到MariaDB的所有功能。从2012年11月12日起发布的10.0.0版开始，不再依照MySQL的版号。10.0.x版以5.5版为基础，加上移植自MySQL5.6版的功能和自行开发的新功能。MariaDB虽然被视为MySQL数据库的替代品，但它在扩展功能、存储引擎以及一些新的功能改进方面都强过MySQL。MariaDB默认的存储引擎是Maria，不是MyISAM。Maria可以支持事务，但是默认情况下没有打开事务支持，因为事务支持对性能会有影响。
  • MariaDB转换为支持事务的Maria引擎
ALTER TABLE 表名 ENGINE=MARIA TRANSACTIONAL=1;
  • 目标：提供一个由社区开发的、稳定的、总是免费的MySQL分支，在用户级别上兼容主流版本。我们为自己的版本和上游、社区版的互操作性提高而努力。
  • MariaDB对比MySQL的相同之处：
    • 数据和表定义文件（.frm）是二进制兼容的
    • 所有客户端API、协议和结构都是完全一致的
    • 所有文件名、二进制、路径、端口等都是一致的
    • 所有的MySQL连接器在MariaDB中都保持不变
    • mysql-client包在MariaDB服务器中也能够正常运行
    • 共享的客户端库与MySQL也是二进制兼容的
  • MariaDB对比MySQL的优势：
    • Maria存储引擎、PBXT存储引擎、XtraDB存储引擎、FederatedX存储引擎
    • 更快的复制查询处理
    • 更少的警告和bug
    • 运行速度更快
    • 微秒的支持
    • 线程池
    • 子查询优化
    • 组提交
    • 进度报告
    • 更多的Extensions(More index parts, new startup options etc)
    • 更好的功能测试
    • 数据表消除
    • 慢查询日志的扩展统计
    • 支持对Unicode的排序

8. MySQL的安装

  • Linux平台下的安装：

    • rpm包安装
    • 在线安装
    • 源码包编译安装
    • 解压bin方式的安装

  • Windows平台下的安装：

    • msi安装包的发方式安装
    • 解压目录方式的安装

  • 附加：MariaDB的相关安装方式

9. MySQL的基本管理

  • mysql的连接管理：

    • ip：默认是localhost/127.0.0.1/ip地址
    • 端口：默认3306
    • 用户名：默认管理员root
    • 密码：安装时/后设置的root的密码

  • mysqld的服务与进程管理

    • systemctl start/stop/restat mysqld/mariadb
    • service mysqld start/stop/restart
    • ps aux | grep mysqld
    • netstat -lnpt | grep 3306
    • tasklist | findstr mysqld

  • my.cnf/my.ini配置文件管理

    • [client]：全局客户端配置
    • [mysql]：mysql客户端配置
    • [mysqld]：mysqld服务端配置

  • 密码配置：

    • 设置密码：mysqladmin
    • 修改密码：--skip-grant-tables

  • 编码字符集的配置：\s

show variables like '%char%'

10. MySQL的权限管理

  • 授权表：

    • user：针对所有数据，所有库下所有表，以及表下的所有字段进行授权
    • db：针对某一数据库，该数据库下的所有表，以及表下的所有字段进行授权
    • table_priv：针对某一张表，以及该表下的所有字段进行授权
    • columns_priv：针对某一个字段进行授权

  • 创建用户：直接创建的帐号无任何权限

-- 针对所有ip创建管理员管权的用户
create user '用户'@'%' identified by '用户的密码';

-- 针对本机创建管理员管权的用户
create user '用户'@'localhost' identified by '用户的密码';
create user '用户'@'127.0.0.1' identified by '用户的密码';

-- 针对指定ip地址创建管理员管权的用户
create user '用户'@'ip地址' identified by '用户的密码';

  • 删除用户：

drop user '用户'@'%'; 
drop user '用户'@'localhost';
drop user '用户'@'127.0.0.1';
drop user '用户'@'ip地址';

  • 创建库授权用户：

    • 关键字：grant
    • 所有权限(grant除外)：all
    • tables_priv和columns_priv表只应当通过GRANT/REVOKE命令进行修改，强制使用insert语句插入后，在使用的过程当中会出问题，也有可能造成mysql服务终止
    • tables_priv表中的table_priv字段允许的命令：Select、Insert、Update、Delete、Create、Drop、Grant、References、Index和Alter
    • tables_priv表中的column_priv字段允许的命令：Select、Insert、Update和References
    • columns_priv表中的column_priv字段允许的命令：Select、Insert、Update和References
-- 对所有库授权所有权限(grant除外)
grant all on *.* to '授权用户'@'授权ip' identified by '授权用户的密码';

-- 对指定库授权select权限
grant select on 库名.* to '授权用户'@'授权ip' identified by '授权用户的密码';

-- 对指定库中指定表授权insert、update、delete权限
grant insert,update,delete on 库名.表名 to '授权用户'@'授权ip' identified by '授权用户的密码';

-- 对指定库中指定表的指定字段授权select权限
grant select(字段1,字段2,字段3,字段4),insert(字段1),update(字段1,字段2) on 库名.表名 to '授权用户'@'授权ip' identified by '授权用户的密码';

  • 删除授权用户：

revoke all on *.* from '授权用户'@'授权ip';

11. MySQL的数据库和表的说明

  • 系统数据库：

    • information_schema：虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
    • performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
    • mysql： 授权库，主要存储系统用户的权限信息
    • test： MySQL数据库系统自动创建的测试数据库
    • sys：MySQL5.7之后新增的默认数据库，存储系统的元数据信息
    • sakila：MySQL5.7之后新增的默认数据库，MySQL样本数据库
    • world：MySQL5.7之后新增的默认数据库

  • 数据库命名规则：

    • 可以由字母、数字、下划线、@、#、$
    • 区分大小写
    • 唯一性
    • 不能使用关键字
    • 不能单独使用数字
    • 最长128位

  • 创建数据库表的注意事项：

    • 在同一张表中，字段名是不能相同
    • 宽度和约束条件可选
    • 字段名和类型是必须的
    • 表中的最后一个字段不要加逗号

  • 帮助命令：help

12. MySQL存储引擎

  • 描述：存储引擎即表类型。mysql中的数据库是系统中的目录，数据库中的表是指定目录中的文件。文件类型有很多种类型，每种类型对应不同的处理机制；数据库中的表也有不同的类型，表的类型不同，对应的mysql存储机制也不一样，这种表类型就叫存储引擎

  • MySQL存储引擎介绍：

    • InnoDB存储引擎：支持事务，其设计目标主要面向联机事务处理(OLTP)的应用。其特点是行锁设计、支持外键、并支持非锁定读(即默认读取操作不会产生锁)。从MySQL5.5.8版本开始是默认的存储引擎。InnoDB 存储引擎将数据放在一个逻辑的表空间中，这个表空间就像黑盒一样由InnoDB存储引擎自身来管理。从MySQL4.1(包括4.1)版本开始，可以将每个InnoDB存储引擎的表单独存放到一个独立的ibd文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用于建立其表空间。InnoDB通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了SQL标准的4种隔离级别，默认为REPEATABLE级别，同时使用一种称为netx-key locking的策略来避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。对于表中数据的存储,InnoDB存储引擎采用了聚集(clustered)的方式,每张表都是按主键的顺序进行存储的，如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的隐藏字段ROWID，并以此作为主键。InnoDB存储引擎是MySQL数据库最为常用的一种引擎，它具备高可用性、高性能以及高可扩展性
    • MyISAM存储引擎：不支持事务、表锁设计、支持全文索引，主要面向一些OLAP数据库应用，在MySQL5.5.8版本之前是默认的存储引擎(除Windows版本外)。数据库系统与文件系统一个很大的不同在于对事务的支持，MyISAM存储引擎是不支持事务的。在数据仓库中，如果没有ETL这些操作，只是简单地通过报表查询是不需要事务支持的。MyISAM存储引擎的另一个与众不同的地方是，它的缓冲池只缓存(cache)索引文件，而不缓存数据文件
    • NDB存储引擎：又叫Cluster存储引擎，2003年MySQL AB公司从Sony Ericsson公司收购了NDB存储引擎。NDB存储引擎是一个集群存储引擎，其结构是share nothing的集群架构，因此能提供更高级别的高可用性。NDB存储引擎的特点是数据全部放在内存中(从MySQL5.1版本开始，可以将非索引数据放在磁盘上)，因此主键查找(primary key lookups)的速度极快，并且能够在线添加NDB数据存储节点(data node)以便线性地提高数据库性能。NDB 存储引擎是高可用、高性能、高可扩展性的数据库集群系统，其面向的也是OLTP的数据库应用类型
    • Memory存储引擎：又叫HEAP存储引擎。Memory存储引擎中的数据都存放在内存中，数据库重启或发生崩溃，表中的数据都将消失。它非常适合于存储OLTP数据库应用中临时数据的临时表，也可以作为OLAP数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希索引
    • Infobright存储引擎：第三方的存储引擎，其特点是存储是按照列而非行的，非常适合OLAP的数据库应用
    • NTSE存储引擎：网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务，但提供压缩、行级缓存等特性
    • BLACKHOLE存储引擎：又叫黑洞存储引擎，可以应用于主备复制中的分发主库
    • PERFORMANCE_SCHEMA存储引擎：用于收集数据库服务器性能参数，提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期
    • CSV存储引擎：使用CSV存储引擎MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件（所以，它可以将CSV类型的文件当做表进行处理），这种文件是一种普通文本文件，每个数据行占用一个文本行。该种类型的存储引擎不支持索引，即使用该种类型的表没有主键列；另外也不允许表中的字段为null
    • ARCHIVE存储引擎：又叫档案引擎，非常适合存储大量独立的、作为历史记录的数据。区别于InnoDB和MyISAM这两种引擎，ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差一些
    • Federated存储引擎：可以不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库，这种存储引擎非常适合数据库分布式应用

  • 查看mysql当前支持的所有引擎：

show engines;

  • 查看正在使用的引擎：

show variables like 'storage_engine%';

  • MySQL数据库的文件存储类型：

    • mysql的data目录中保存着数据库的相关数据，一个数据库会有自己独立的文件目录
    • db.opt：管理与操作数据库、数据表的文件
    • InnoDB存储引擎：
      • *.frm：innodb引擎的表结构
      • *.idb：innodb引擎的表数据
    • MyISAM存储引擎：
      • *.frm：myisam引擎的表结构
      • *.MYI：myisam引擎的表索引
      • *.MYD：myisam引擎的表数据

13. MySQL数据类型

  • 数值整数型：

    • tinyint：微小整数；默认有符号

      • 有符号：-128 ~ 127
      • 无符号：0 ~ 255
      • MySQL没有布尔类型，可使用tinyint(1)构造

    • smallint：小整数(-32768 ~ 32767)

    • mediumint：中等整数(-8388608 ~ 8388607)

    • int：整数；默认有符号

      • 有符号：-2147483648 ~ 2147483647
      • 无符号：0 ~ 4294967295

    • bigint：大整数；默认有符号

      • 有符号：-9223372036854775808 ~ 9223372036854775807
      • 无符号：0 ~ 18446744073709551615

  • 数值浮点型：

    • floal：单精度浮点数(大约小数后10位之后开始不准确)

      • 格式：FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
      • M是数字总个数，D是小数点后个数。M最大值为255，D最大值为30
      • 有符号：-3.402823466E+38 ~ -1.175494351E-38,1.175494351E-38 ~ 3.402823466E+38
      • 无符号：1.175494351E-38 ~ 3.402823466E+38
      • 精准度：随着小数的增多，精度变得不准确

    • double：双精度浮点数(大约小数后15位之后开始不准确)

      • 格式：DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
      • M是数字总个数，D是小数点后个数。M最大值为255，D最大值为30
      • 有符号：-1.7976931348623157E+308 ~ -2.2250738585072014E-308,2.2250738585072014E-308 ~ 1.7976931348623157E+308
      • 无符号：2.2250738585072014E-308 ~ 1.7976931348623157E+308
      • 精准度：随着小数的增多，精度比float要高，但也会变得不准确

    • decimal：始终准确的小数值

      • 格式：decimal[(m[,d])] [unsigned] [zerofill]
      • m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30
      • 精准度：随着小数的增多，精度始终准确；对于精确数值计算时需要用此类型；decaimal能够存储精确值的原因在于其内部按照字符串存储

  • 字符串类型：

    • char：定长；浪费空间，存取速度快

      • 字符长度范围：0-255(一个中文是一个字符，是utf8编码的3个字节)
      • 存储：存储char类型的值时，会往右填充空格来满足长度
      • 检索：在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式(SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'😉

    • varchar：变长；精准，节省空间，存取速度慢

      • 字符长度范围：0-65535(如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8)
      • 存储：varchar类型存储数据的真实内容，不会用空格填充
      • 强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数(1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用)；如果真实的数据<255bytes则需要1Bytes的前缀(1Bytes=8bit，2**8 - 1最大表示的数字为255)；如果真实的数据>255bytes则需要2Bytes的前缀(2Bytes=16bit，2**16 - 1最大表示的数字为65535)
      • 检索：尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容
      • 相关函数：
        • length()：查看字节数
        • char_length()：查看字符数

    • text：变长的大字符串；字符长度：65535 (2**16 − 1)

  • 时间类型：

    • YEAR：YYYY

    • DATE：YYYY-MM-DD

    • TIME：HH:MM:SS

    • DATETIME：YYYY-MM-DD HH:MM:SS

    • TIMESTAMP：YYYY-MM-DD HH:MM:SS

    • datetime和timestamp的区别：

      • DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年
      • DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，操作系统以及客户端连接都有时区的设置
      • DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高
      • DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间

  • 枚举类型与集合类型：

    • 说明：字段的值只能在给定范围中选择
    • enum：单选，只能在指定的范围内选一个值
    • set：多选，在指定的范围内可以选择一个或一个以上的值

  • 位类型：

    • bit(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位；对于位字段需要使用函数读取
    • bin()显示为二进制
    • hex()显示为十六进制

14. MySQL完整性约束条件

  • 说明：约束条件与数据类型的宽度一样，都是可选参数

  • 作用：用于保证数据的完整性和一致性

  • 分类：

    • NOT NULL：标识该字段不能为空

    • DEFAULT：为该字段设置默认值

    • AUTO_INCREMENT：标识该字段的值自动增长(整数类型，而且为主键)

    • PRIMARY KEY (PK)：标识该字段为该表的主键，可以唯一的标识记录

    • UNIQUE KEY (UK)：标识该字段的值是唯一的

    • FOREIGN KEY (FK)：标识该字段为该表的外键

    • UNSIGNED：无符号

    • ZEROFILL：使用0填充

  • not null：

    • 是否为空，null表示空，非字符串
    • null：可为空
    • not null：不可为空

  • default：

    • 设置字段默认值
    • 插入数据时如果不主动设置，会自动添加默认值

  • auto_increment：

    • 设置自增长
    • 必须为整数类型
    • 默认从0开始，也可以手动设置起始值
    • DEFAULT约束只会在使用INSERT语句时体现出来，INSERT语句中，如果被DEFAULT约束的位置没有值，那么这个位置将会被DEFAULT的值填充

  • primary key：

    • 设置字段值是唯一的，并且不能为空
    • 一个表中只能有一个主键
    • 可以设置多个字段联合主键(约束多个字段同时满足主键约束，其中一个或多个有空值或重复值不受主键约束)

  • unique：

    • 设置字段的唯一性
    • 为了解决主键存在表中的独立性
    • 可以设置多个字段联合唯一
    • 当INSERT语句新插入的数据和已有数据重复的时候，如果有UNIQUE约束，则INSERT失败

  • foreign key：

    • 为了节省内存资源，不用多次读取重复的信息
    • 一个表可以有多个外键，被外键约束的列，取值必须在它参考的列中有对应值
    • 删除与修改时的状态：
      • RESTRICT：严格限制约束，外键无法删除与修改操作
      • NO ACTION：外键删除与修改时，对应的数据不会做任何操作
      • CASCADE：外键删除与修改时，对应的数据也会跟着删除与修改
      • SET NULL：外键删除与修改时，对应的数据被修改为NULL
    • 一般应用中的外键都是假外键(看上去是外键，也是按照外键进行表的关联，但是不配置外键)，方便数据库的修改与扩展

15. 数据库操作

  • 创建库：

create database 库名;
create database 库名 charset utf8;
create database 库名 charset utf8 collate utf8_general_ci;

  • 查看库：

show databases;
show create database 库名;
select database();

  • 使用库：

use 库名;

  • 修改库：

alter database 库名 charset gbk;

+ ##### 数据库名无法修改，这里的修改指的是修改数据库的信息，而数据库信息只有编码信息
+ ##### 如果必须要修改数据库名，可以使用备份和还原的方式(mysqldump原库，source/mysql 新库)

  • 删除库：

drop database 库名;

16. 数据表操作

  • 创建表

create table 表名(字段名 字段类型(长度/宽度) 约束条件);

  • 查看表

show tables;
describe 表名;
desc 表名;
show create table 表名;

  • 复制表

create table 新表名 select * from 旧表名;
create table 新表名 select * from 旧表名 where 1 = 2;

  • 修改表

alter table 旧表名 rename 新表名;

  • 清空表

delete from 表名;
trancate [table] 表名;

  • 删除表

drop table 表名;

17. 表字段操作

  • 添加字段

alter table 表名 add 字段名 字段类型(长度/宽度) 约束条件;
alter table 表名 add 字段名 字段类型(长度/宽度) 约束条件,add 字段名 字段类型(长度/宽度) 约束条件;
alter table 表名 add 字段名 字段类型(长度/宽度) 约束条件 first;
alter table 表名 add 字段名 字段类型(长度/宽度) 约束条件 after 已有字段名;

  • 修改字段

alter table 表名 modify 字段名 字段类型(长度/宽度) 约束条件;
alter table 表名 change 旧字段名 新字段名 旧字段类型 旧字段的约束条件;
alter table 表名 change 旧字段名 新字段名 新字段类型 新字段的约束条件;

  • 删除字段

alter table drop 字段名;

18. 数据操作

  • 新增数据：

insert into 表名 values (值1,值2...);
insert into 表名 values (值1,值2...),(值1,值2...);
insert into 表名 (字段1,字段2...) values (值1,值2...)...;
insert into 表名 (字段1,字段2...) values (值1,值2...),(值1,值2...)...;
INSERT INTO 表名1 (字段1,字段2...) (SELECT (字段1,字段2...) FROM 表名2 [WHERE 条件]);

  • 修改数据：

update 表名 set 字段=新值 [where 条件];
update 表名 set 字段1=新值1,字段2=新值2 [where 条件];

  • 删除数据：

delete from 表名 [where 条件];
posted @ 2017-12-15 20:22  _岩哥  阅读(177)  评论(0)    收藏  举报