MySQL数据库基础与实例教程之MySQL基础知识

一、MySQL发展历程

MySQL由瑞典MySQL AB公司开发。

2008年1月MySQL被美国的SUN公司收购。

2009年4月SUN公司又被美国的甲骨文（Oracle）公司收购。

二、MySQL的特点

MySQL是一个单进程多线程、支持多用户、基于客户机/服务器（Client/Server简称C/S）的关系数据库管理系统 。    

性能高效    

跨平台支持    

简单易用    

开源    

支持多用户

2.1MySQL配置文件 

1、my.ini配置文件包含了多种参数选项组，每个参数选项组通过“[ ]”指定，每个参数选项组可以配置多个参数信息。通常情况下，每个参数遵循“参数名=参数值”这种配置格式，参数名一般是小写字母，参数名大小写敏感。常用的参数选项组有“[client]”、“[mysql]”以及“[mysqld]”参数选项组。

2、[client]参数选项组：    

配置了MySQL自带的MySQL5.6 命令行窗口可以读取的参数信息。    

常用的参数是port（默认值是3306）。    

修改该port值会导致新打开的MySQL5.6 命令行窗口无法连接MySQL服务器。

3、[mysql]参数选项组：    

配置了MySQL客户机程序mysql.exe可以读取的参数信息。    

常用的参数有“prompt”、 “default-character-set=gbk”。    

修改 “[mysql]”参数选项组中的参数值，将直接影响新打开的MySQL客户机。

4、[mysqld]参数选项组：    

配置了MySQL服务程序mysqld.exe可以读取的参数信息，mysqld.exe启动时，将[mysqld]参数选项组的参数信息加载到服务器内存，继而生成MySQL服务实例。

5、[mysqld]参数选项组：    

常用的参数有“port”、“basedir”、“datadir”、“character-set-server”、“sql_mode”、“max_connections”以及“default_storage_engine”等。

6、[mysqld]参数选项组：    

修改“[mysqld]”参数选项组的参数值，只有重新启动MySQL服务，将修改后的配置文件参数信息加载到服务器内存后，新配置文件才会在新的MySQL服务实例中生效。如果“[mysqld]”参数选项组的参数信息出现错误，将会导致MySQL服务无法启动。

2.2字符集以及字符序设置

MySQL由瑞典MySQL AB公司开发，默认情况下MySQL使用的是latin1字符集。    

由此可能导致MySQL数据库不够支持中文字符串查询或者发生中文字符串乱码等问题。

2.3 字符集及字符序概念 

字符（Character）是人类语言最小的表义符号，例如‘A’、‘B’等。给定一系列字符，对每个字符赋予一个数值，用数值来代表对应的字符，这个数值就是字符的编码（Character Encoding）。     给定一系列字符并赋予对应的编码后，所有这些“字符和编码对”组成的集合就是字符集(Character Set)。

字符序(Collation)是指在同一字符集内字符之间的比较规则。一个字符集包含多种字符序，每个字符序唯一对应一种字符集。     MySQL字符序命名规则是：以字符序对应的字符集名称开头，以国家名居中（或以general居中），以ci、cs或bin结尾。     ci表示大小写不敏感，cs表示大小写敏感，bin表示按二进制编码值比较。

使用MySQL命令show character set; 即可查看当前MySQL服务实例支持的字符集、字符集默认的字符序以及字符集占用的最大字节长度等信息  

latin1支持西欧字符、希腊字符等  

gbk支持中文简体字符  

big5支持中文繁体字符  

utf8几乎支持世界所有国家的字符。

使用MySQL命令 show variables like 'character%'; 即可查看当前MySQL服务实例使用的字符集。

character_set_client：MySQL客户机字符集。  

character_set_connection：数据通信链路字符集，当MySQL客户机向服务器发送请求时，请求数据以该字符集进行编码。  

character_set_database：数据库字符集。  

character_set_filesystem：MySQL服务器文件系统字符集，该值是固定的binary。

character_set_results：结果集的字符集，MySQL服务器向MySQL客户机返回执行结果时，执行结果以该字符集进行编码。  

character_set_server：MySQL服务实例字符集。  

character_set_system：元数据(字段名、表名、数据库名等) 的字符集，默认值为utf8。 

使用MySQL命令“show collation;”即可查看当前MySQL服务实例支持的字符序。 

2.4MySQL字符集的设置

方法1：修改my.ini配置文件，可修改MySQL默认的字符集。  

方法2：MySQL提供下列MySQL命令可以“临时地”修改MySQL“当前会话的”字符集以及字符序。

  set character_set_client = gbk;
  set character_set_connection = gbk;
  set character_set_database = gbk;
  set character_set_results = gbk;
  set character_set_server = gbk;
  set collation_connection = gbk_chinese_ci ;
  set collation_database = gbk_chinese_ci ;
  set collation_server = gbk_chinese_ci ;

方法3：使用MySQL命令 “set names gbk;”可以“临时一次性地”设置character_set_client、character_set_connection以及character_set_results的字符集为gbk  

方法4：连接MySQL服务器时指定字符集     mysql --default-character-set=字符集 -h 服务器IP地址 -u 账户名 –p密码

2.5 SQL脚本文件

\. C:\mysql\init.sql
  source C:\mysql\init.sql

三、MySQL数据库管理

数据库是存储数据库对象的容器。MySQL数据库的管理主要包括数据库的创建、选择当前操作的数据库、显示数据库结构以及删除数据库等操作。

3.1创建数据库

create database choose;

成功创建choose数据库后，数据库根目录下会自动创建数据库目录。

3.2 查看数据库

使用MySQL命令 show databases; 即可查看MySQL服务实例上所有的数据库

3.3 显示数据库结构

使用MySQL命令 show create database choose; 可以查看choose数据库的相关信息（例如MySQL版本ID号、默认字符集等信息）。

3.4 选择当前操作的数据库

执行“use choose;”命令后，后续的MySQL命令以及SQL语句将自动操作choose数据库中所有数据库对象。

3.5 删除数据库

删除student数据库，使用SQL语句 drop database student;

四、MySQL表管理

表是数据库中最为重要的数据库对象

4.1MyISAM和InnoDB存储引擎

MySQL提供了插件式（Pluggable）的存储引擎，存储引擎是基于表的，同一个数据库，不同的表，存储引擎可以不同。甚至同一个数据库表，在不同的场合可以应用不同的存储引擎。

使用MySQL命令“show engines;”，即可查看MySQL服务实例支持的存储引擎。

1．InnoDB存储引擎的特点    

支持外键（Foreign Key）    

支持事务（Transaction）：如果某张表主要提供OLTP支持，需要执行大量的增、删、改操作（insert、delete、update语句），出于事务安全方面的考虑，InnoDB存储引擎是更好的选择。     最新版本的MySQL已经开始支持全文检索。

2．MyISAM存储引擎的特点    

MyISAM具有检查和修复表的大多数工具。    

MyISAM表可以被压缩    

MyISAM表最早支持全文索引    

但MyISAM表不支持事务    

但MyISAM表不支持外键（Foreign Key）。 如果需要执行大量的select语句，出于性能方面的考虑，MyISAM存储引擎是更好的选择。

4.2 设置默认的存储引擎

 MySQL8.0默认的默认的存储引擎是InnoDB。 使用MySQL命令

setdefault_storage_engine=MyISAM; 可以“临时地”将MySQL“当前会话的”存储引擎设置为MyISAM，使用MySQL命令“show engines;”可以查看当前MySQL服务实例默认的存储引擎。

4.3 创建数据库表

 使用SQL语句“create table表名”即可创建一个数据库表。例如：

use choose;
set default_storage_engine=InnoDB;
create table my_table(
today datetime,
name char(20)
);

 成功创建InnoDB存储引擎的my_table表后，MySQL服务实例会在数据库目录choose中自动创建一个名字为表名、后缀名为frm的文件。my_table.frm。

将my_table表的存储引擎修改为MyISAM。

 use choose; alter table my_table engine=MyISAM;

4.4 显示表结构

使用MySQL命令“des table_name;”即可查看表名为table_name的表结构。

使用MySQL命令“show create table table_name;”，查看名为table_name表的详细信息。

4.5 表记录的管理

use choose;
insert into my_table values(now(),'a');
insert into my_table values(now(),'a');
insert into my_table values(now(),NULL);
insert into my_table values(now(),'');
select * from my_table; 

4.6 InnoDB表空间

1.共享表空间：MySQL服务实例承载的所有数据库的所有InnoDB表的数据信息、索引信息、各种元数据信息以及事务的回滚（UNDO）信息，全部存放在共享表空间文件中。     默认情况下该文件位于数据库根目录下，文件名是ibdata1，且文件的初始大小为10M。可以使用MySQL命令“show variables like ‘innodb_data_file_path’;”查看该文件的的属性。

2.独享表空间：如果将全局系统变量innodb_file_per_table的值设置为ON（innodb_file_per_table的默认值为OFF），那么之后再创建InnoDB存储引擎的新表，这些表的数据信息、索引信息都将保存到独享表空间文件。

use choose;
alter table my_table engine=InnoDB;
create table second_table(
today datetime,
name char(20)
);

4.7 删除表

使用SQL语句 drop table table_name; 即可删除名为table_name的表。    

删除表后，MySQL服务实例会自动删除该表结构定义文件（例如second_table.frm文件），以及数据、索引信息。该命令慎用！ 

五、系统变量

MySQL数据库中，变量分为系统变量（以@@开头）以及用户自定义变量（以@开头）。

5.1 全局系统变量与会话系统变量

每一个MySQL客户机成功连接MySQL服务器后，都会产生与之对应的会话。    

会话期间，MySQL服务实例会在MySQL服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的拷贝。

由于各会话在会话期间所做的操作不尽相同，为了标记各个会话，会话系统变量又新增了12个变量。

5.2 查看系统变量的值

使用“show global variables;”命令即可查看MySQL服务器内存中所有的全局系统变量信息（有393项之多）。    

使用“show session variables;”命令即可查看与当前会话相关的所有会话系统变量以及所有的全局系统变量（有405项之多），此处session关键字可以省略。

 MySQL中有一些系统变量仅仅是全局系统变量，例如 innodb_data_file_path。
  show global variables like 'innodb_data_file_path';
  show session variables like 'innodb_data_file_path';
  show variables like 'innodb_data_file_path';

MySQL中有一些系统变量仅仅是会话系统变量，例如MySQL连接ID会话系统变量pseudo_thread_id。
  show session variables like 'pseudo_thread_id';
  show variables like 'pseudo_thread_id';

MySQL中有一些系统变量既是全局系统变量，又是会话系统变量，例如系统变量character_set_client 既是全局系统变量，又是会话系统变量。

此时查看会话系统变量的方法：
  show session variables like 'character_set_client';
  show variables like 'character_set_client';
    此时查看全局系统变量的方法： 
  show global variables like 'character_set_client';

作为MySQL编码规范，MySQL中的系统变量以两个“@”开头。    

@@global仅仅用于标记全局系统变量；    

@@session仅仅用于标记会话系统变量；    

@@首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。

方法一：修改MySQL源代码，然后对MySQL源代码重新编译（该方法适用于MySQL高级用户，这里不作阐述）。 方法二：最为简单的方法是通过修改MySQL配置文件，继而修改MySQL系统变量的值（fai方法需要重启MySQL服务）。 方法三：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值。

设置全局系统变量的值的方法：

  set @@global.innodb_file_per_table = default;
  set @@global.innodb_file_per_table = ON;
  set global innodb_file_per_table = ON;

设置会话系统变量的值的方法：   
set @@session.pseudo_thread_id = 5;   
set session pseudo_thread_id = 5;   
set @@pseudo_thread_id = 5;   
set pseudo_thread_id = 5;

 MySQL中还有一些特殊的全局系统变量（例如log_bin、tmpdir、version、datadir），在MySQL服务实例运行期间它们的值不能动态修改，不能使用“set”命令进行重新设置，这种变量称为静态变量。    

数据库管理员可以使用方法一或者方法二对静态变量的值重新设置。

六、MySQL数据库备份和恢复

步骤1：准备工作

方法一：停止MySQL服务

方法二：使用MySQL命令“flush tables with read lock;”将服务器内存中的数据“刷新”到数据库文件中，同时锁定所有表，以保证备份期间不会有新的数据写入。

步骤2：备份文件的选取 如果数据库中全部是MyISAM存储引擎的表，最为简单的数据库备份方法就是直接“备份”整个数据库目录。 如果某个数据库中还存在InnoDB存储引擎的表，此时不仅需要“备份”整个数据库目录，还需要备份ibdata1表空间文件以及重做日志文件ib_logfile0与ib_logfile1。

数据库备份时，建议将MySQL配置文件（例如my.ini配置文件）一并进行备份。

步骤3：数据库恢复 首先停止MySQL服务；然后将整个数据库目录、MySQL配置文件（例如my.ini配置文件）、ibdata1共享表空间文件以及重做日志文件ib_logfile0与ib_logfile1复制到新MySQL服务器对应的路径，即可恢复数据库中的数据。