Mysql数据库相关理论知识

Mysql数据库相关理论知识

一、mysql开篇

1.1 mysql数据库介绍

1.1.1什么是数据库DB

DB的全称是database，即数据库的意思。数据库实际上就是一个文件集合，是一个存储数据的仓库，数据库是按照特定的格式把数据存储起来，用户可以对存储的数据进行增删改查操作；

1.1.2什么是sql

SQL代表结构化查询语言(Structured Query Language)。SQL是用于访问数据库的标准化语言。

SQL包含四个部分：

• DDL(Data Define Language)数据定义语言包含定义数据库及其对象的语句，例如表，视图，触发器，存储过程等。

• DML(Data Manipulation Language) 数据操作语言包含允许更新数据的语句。

• DCL (Data Control Language)数据控制语言允许授予用户权限访问数据库中特定数据的权限。

• DQL(Data Query Language) 数据库查询语言

1.1.3mysql是什么

• MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
• My是MySQL的联合创始人 - Monty Widenius 的女儿的名字。MySQL是My和SQL的组合，这就是MySQL命名的由来。
• MySQL的官方网址： http://www.mysql.com/ ，MySQL的社区版本下载地址为： http://dev.mysql.com/downloads/mysql/ ，在写本文时，当前的MySQL最新版本是：8.0 。
• 版本演化：
  * MySQL：5.0 5.1 5.2 5.3 5.4 5.5 5.6 5.7 8.0
  * MariaDB: 5.0 5.1 5.2 5.3 5.4 5.5 10.0 10.1 10.2 10.3 10.4 10.5(有限代替MySQL8) 10.6……10.10代替MySQL8

• MySQL是一个数据库管理系统，也是一个关系数据库。它是由Oracle支持的开源软件。这意味着任何一个人都可以使用MySQL而不用支付一毛钱。 另外，如果需要，还可以更改其源代码或进行二次开发以满足您的需要。

1.1.4mysql和mariadb的前世今生

• MySQL的名字，来自Michael Widenius的女儿My，My是Michael Widenius和前妻的女儿。 MariaDB的名字，来自Michael Widenius的小女儿Maria，Maria是Michael Widenius和第二任妻子的女儿。 Michael Widenius和前妻还有个儿子叫Max，当然，这个儿子的名字也没有浪费。在2003年的时候，德国软件公司SAP与MySQL建立合作关系，SAP旗下的数据库产品SAP DB被重新命名为MaxDB，其中的Max正是来自Michael Widenius的儿子的名字。

1.1.5什么是关系型数据库（SQL）

• 关系型数据库是依据关系模型来创建的数据库。
• 所谓关系模型就是“一对一、一对多、多对多”等关系模型，关系模型就是指二维表格模型,因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。
• 关系型数据可以很好地存储一些关系模型的数据，比如一个老师对应多个学生的数据（“多对多”），一本书对应多个作者（“一对多”），一本书对应一个出版日期（“一对一”）
• 数据的存储形式：
  

1.1.6什么是非关系型数据库（NOSQL）

• 非关系型数据库主要是基于“非关系模型”的数据库（由于关系型太大，所以一般用“非关系型”来表示其他类型的数据库）

• 非关系型模型比如有：
  存储的数据是一列列的。关系型数据库以一行作为一个记录，列模型数据库以一列为一个记录。（这种模型，IO很快，主要是一些分布式数据库）

1.1.7关系型数据库与非关系型数据库的区别

1.关系型数据库:
优点：
1、易于维护：都是使用表结构，格式一致；
2、使用方便：SQL语言通用，可用于复杂查询；
3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询；
4、学习成本低。
缺点：
1、读写性能比较差，尤其是海量数据的高效率读写；
2、固定的表结构，灵活度稍欠；
3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。吞吐量

2.非关系型数据库
优点：
1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛.
2、速度快：可以使用硬盘或者内存作为载体，而关系型数据库只能使用硬盘；redis数据库单线程10W
3、成本低：数据库部署简单，基本都是开源软件。

缺点：
1、不提供sql支持，学习和使用成本较高；
2、无事务处理；

• 关系型数据库：Oralce Mysql DB2 PostgreSQL SqlServer

• 非关系型数据库：Redis MongDB Memcache

二、数据库基本操作

2.1结构化语言SQL介绍

数据查询语言（DQL:Data Query Language）： 其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词。

数据操作语言（DML：Data Manipulation Language）： 其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。

数据控制语言（DCL）:（Data Control Language）： 它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

数据定义语言（DDL）：（Data Define Language）： 其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

名词	解释
数据库服务器	运行着数据库应用的设备
数据库	表的管理单元
数据库实例	数据库实例就是访问数据库的应用程序，是位于用户和操作系统之间的一层数据管理软件
表	字段、记录
记录	字段的管理单元
字段	字段名，字段类型、长度，字段约束组成
类型	字符，数字，日期
约束	NULL NOTNULL等

增删改查
     库    存放数据的目录
     表    文件(普通编辑器打不开)

修饰符    
  
     行叫记录 
     列叫字段    字段名称
   
     神器     edit    在edit里边编辑，wq保存，出来敲一个分号；  
     

创建库   
        create database   company；
查看库
        show databases;                  
        show create databse   company； 
进入库  
        use  company;
创建表 
        create table   t1(id int(2),name char(20),age int);
                       字段 类型    字段 类型(长度)，字段 类型         (字段后边的就是字段的修饰符(约束条件)）
查看表
        (show   desc(describe)   select)   
     查看表名称
        		show tables;    
        		desc  t1;(表的详细字段信息) 
     查看表创建过程     
        		show create table   t1;    <----- 当表特别长的时候；该为\G ，则把表向左旋转90度，可以更清楚看到               
        第一列显示字段名称
        第二列显示记录     
     查看表结构  
            desc  t1；
     查看表记录  
            select   *   from  表名；     
            select   字段，字段  from  表名； 
     查看表状态   
            show  table status  like  '表名' ;


修改表
            (alter) (add  delect  change)
     修改表名   
                    rename table 原表名  to  新表名;         
                    rename table t11 to t9;   alter table t9 rename to t1;
     添加字段   
            alter table 表名  add  字段   修饰符;       
            alter table t1 add gender char(20);     
            alter table t1 add grade char(20) after id;  
            alter table t1 add zhuanye char(20)  first;
     删除字段  
            alter table  表名  drop  字段;  
     修改字段  
            alter table 表名 change  旧字段  新字段 修饰符;   change修改字段名称，类型，约束，顺序
            alter table 表名 modify  字段  属性 修饰符；     modify 不能修改字段名称
            alter table t1 change zhuanye major varchar(20) after gender; 
            alter table t1 modify major  varchar(20)  after age;     

     修改记录                                         
            (inster  update  delete  where)
     添加记录   
            insert into  表名 values  (),(),(),();    
            insert into  表名(字段，字段，字段)  values  (),(),();  
            insert into t1 values(1,'2','yingge',18,'12','male');
            insert into t1(name,grade) values ('wing','1');
            insert into t1(name,grade) values ('newrain','2'),('houzi','2'); 
            insert into t1 set name='jianjian',grade='1';

     修改记录（更新）
            update 表名  set 字段=' ' where  主键=' ';
            update t1 set grade=4 where name='yingge';  
     删除记录 
            delete from  表名 where  主键字段=' ';     
            delete from 表名;       
            delete from  t1 where id=5;         

     删除表  drop table  表名；
            drop table  t1;
     删除库  drop  databse 库名；  
            drop database  company;    


各种查询
    select id,name from employee5 where id<=5;
    select id,dep_id,id*dep_id from company.employee5  where id<=5;
    select id*dep_id as "id and dep_id's sum" from company.employee5  where id<=5;
###  2.2MySQL存储引擎

MySQL引擎：
可以理解为，MySQL的“文件系统”，只不过功能更加强大。

• MySQL引擎功能：
  除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能。和磁盘打交道，mysql中组织。

• 1.什么是外键：外键的主要作用是保持数据的一致性、完整性。

• 2.什么是索引:索引相当于书中的目录，可以提高数据检索的效率，降低数据库的IO。MySQL在300万条记录左右性能开始逐渐下降，虽然官方文档说500~800w记录，所以大数据量建立索引是非常有必要的

• 3.什么是事务：事务是由一步或几步数据库操作这系列操作要么全部执行，要么全部放弃执行。程序和事务是两个不同的概念。
  事务具有四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolatio >）和持续性（Durability）。这四个特性也简称ACID性。
  （1）原子性：事务是应用中最小的执行单位，就如原子是自然界最小颗粒，具有不可再分>的特征一样。事务是应用中不可再分的最小执行体。（最小了，不可再分了）
  （2）一致性：事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态>。当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。一致性是通过原子性
  来保证的。（说罢了就是白狗变成了黑狗，不能出现斑点狗！）
  （3）隔离性：各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务，都>是隔离的。也就是说：并发执行的事务之间不能看到对方的中间状态，并发执行的事务之间
  不能相互影响。（说白了，就是你做你的，我做我的！）
  （4）持续性：持续性也称为持久性，指事务一旦提交，对数据所做的任何改变，都要记录>到永久存储器中，通常是保存进物理数据库。（说白了就是一条道跑到黑）

• 4.什么是行锁与锁表：可以将一张表锁定和可以单独锁一行的记录。为了防止你在操作的
  同时也有别人在操作。

mysql常见的储存引擎

• InnoDB

• MyIsam

• Memory（也叫HEAP）堆内存

• Blackhole（黑洞引擎）异地主从

• Archive(归档引擎)

为什么要合理选择数据库存储引擎：
  MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

  这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

MyISAM和InnoDB的区别

MyISAM和InnoDB的区别 
Mysql在V5.1之前默认存储引擎是MyISAM；在此之后默认存储引擎是InnoDB
MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT（自动提交）默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。
InnoDB支持数据行锁定，MyISAM不支持行锁定，只支持锁定整个表。即 MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以 MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。
InnoDB支持外键，MyISAM不支持。
Innodb能存储64TB，MyISAM能存储256TB
InnoDB不支持全文索引，而MyISAM支持。全文索引是指对char、 varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。（书的目录）
MyISAM支持GIS数据，InnoDB不支持。即MyISAM支持以下空间数据对象：Point,Line,Polygon,Surface等。
没有where的count(*)使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count(*)时它直接从计数器中读，而InnoDB必须扫描全表。

其他的存储引擎

MEMORY内存型引擎(了解)
		MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问
		MEMORY特点： 
    所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

Archive(归档引擎)
  	如何选择引擎：如果要提供提交、回滚、并要求实现并发控制，InnoDB是一个好的选择；如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率；如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎；MySQL中使用该引擎作为临时表，存放查询的中间结果；如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive。

查看支持的存储引擎

mysql> show engines;

查看你的mysql当前默认的存储引擎:

mysql> show variables like '%storage_engine%';

如何查看Mysql服务器上的版本

 mysql> select version();

创建库、表的时候指定引擎

mysql> create table t1(id int,manager char(10)) engine =innodb;