MySQL初步，数据类型及SQL语句

引入

DBMS的优势

　　首先，我们分析一个问题：对500G的数据排序，不能在磁盘上排序，需要将数据载入程序所在的地址空间中进行，如果没有索引，只是靠操作系统文件进行管理的话，这就意味着我们必须要把这500G的数据全部载入内存进行排序才能取得排序结果。就算有500G的内存，但从理论上讲在32位的系统上任何一个进程所能够使用的内存不会超过4G。无论如何对数据的高效管理在文件上是无法实现的，这也是为什么我们要借助于DBMS实现的原因。

    根据使用规模的不同，DBMS也有所谓的轻量级的和企业级的应用，但无论如何，有了DBMS之后，整个数据管理就可以变得非常简化了。也就意味着，有了DBMS后，数据管理的优势如下：

• 数据管理独立性；

　　能够实现把数据表达和存储抽象分开，这个分开是靠DBMS完成的；

• 有效的完成数据存取；

　　不用每一次操作都要对整个数据扫描；

• 保证数据完整性和安全性；

　　数据完整性可以在RDBMS上施加一定的约束，这个文件在满足什么前提下才可以插入，某一个指定数据在什么条件下才能够被删除等等，它们彼次之间是有一定的关联关系的，这个关系称为约束；

• 数据集中管理；

　　当多个用户共享数据的时候我们实现数据的集中管理有了许多好处，实现数据的集中管理可以将多个不同需求的数据降低冗余以节约存储空间，并且也能够实现集中对用户的权限进行指派，大大降低了管理难度；

• 能够实现并发存储和故障恢复；

　　一般来讲，一个DBMS都应该提供类似的功能，一旦在一个事务的执行过程当中或者一个交易的执行过程当中，数据文件崩溃或损坏，如何将损失降到最少；

• 减少应用程序的开发时间；

　　数据存储和应用程序的逻辑相分开了，这种功能是在数据逻辑的层面上来完成的，跟应用程序开发本身没有关系；

SQL引入

　　一个DBMS应该具备什么样的结构呢？对一个软件来讲，简单来讲，他应该完成几种基本操作，用户对于数据库中数据的管理绝大多数都是通过SQL命令来完成的。

　　SQL（Structrue Query Language结构化查询语言），这是早期IBM公司的一个管理员为公司内部叫SystemR的一个关系型数据库管理系统所设计的一种语言接口，就叫SQL。

    SQL刚出来不久，众多的商业公司都采用了SQL这种技术手段，并且在他们的商业数据库（关系型数据库）产品之中都提供了自己的实现，但SQL本身只是一种类似于shell的机制（管理接口或数据库使用接口），不同公司的商业产品内部实现SQL的格式可能不尽相同，类似于bash，xsh，csh。

    为了避免这种乱象丛生的局面，美国国家标准委员会（ANSI）来规范SQL应该具有的基本功能，像早期SQL86、SQL92、SQL99、SQL03，使用当年标准制定的年限来命名的。较著名的是SQL86、SQL89、SQL92、SQL99等，它们定义了一个数据库管理系统的SQL接口应该具有哪些基本功能而且应该具有什么样的使用特色，说白了这是一种工业标准。

一个DBMS应该具有的基本功能

　　当用户输入SQL命令后，如在shell中输入shell命令一样，shell解释器要对shell命令做词法分析，语法分析，要有很多的分析功能。而前端用户对于SQL的使用通常都是通过SQL命令来发出的，SQL命令可能使用应用程序发出的，而像php也有自己的前端驱动，这个驱动也可以发出SQL命令的，还有用户、应用前端、SQL接口、Web表格等等也可以发出SQL命令，SQL命令送给DBMS后，DBMS接受下来并做处理，当然此处暂不考虑判断用户权限，只考虑基本组件。对于SQL功能来讲，DBMS（RDBMS）必需要完成以下几个功能或者对于一个关系型DBMS的内部结构上应该具有的几个基本组件有：

• 分析器

    提供一个分析器，做词法分析、语法分析等；

• 计划执行器

    分析的结果要生成执行计划，因此需要一个计划执行器；一个SQL语言分析以后，可能会生成n个执行路径，也就是一个语句可能有多个执行方法。如：执行一个select语句要从来某个表中查询操作出来一些数据，可以做全表扫描，对整个表中的每一行逐个进行比较，这是一种查询方法，也可以使用索引，有三个索引，其中两个索引都可以用，用第一种索引能够生成一种执行方法，用第二种也可以生成一种执行方法，这就意味着我们为了完成这条语句可以有三个可执行的路径都能完成对应的任务，因此，计划执行器就是分析我们到底应该有多少种路径可以完成相应任务的。那么分析完成后应该使用哪种方法呢？代价最小的性能最好的一般来讲是我们最终选择的。于是需要做分析优化谁的代价最小、性能最好，对每一条语句做一下优化；

• 优化器

    使用优化器对结果做优化，在真正选择一条语句后，还不能直接执行，而是从中选择一个最优的执行方式，甚至于不惜要对整个查询语句做改写的方式，只要执行结果一样都可以；

• 文件的存取方法

　　上述分析结束后，应该执行语句了，语句要执行的话意味着要操作数据了，而数据在磁盘的文件上存放，因此，必须要有一个系统完成从磁盘上把文件取出来，于是需要完成文件的存取。而文件的存取方法有n种，至少要实现其中的1种；

• 缓存器

　　完成文件存取后，进程不会从磁盘上直接操作数据，它应该把数据载入内存才能操作，这就意味着文件读进来之后应该先缓存在内存中，因此应该提供一个缓存器，基于文件的存取方法存取的时候先将文件读入缓存中才能进行操作的，现在问题是如果数据文件有500G，显然不能将整数据都载入内存中，因此缓存器中缓存文件的量是有限的，若缓存中已将满了，又需要载入新的数据该如何做？需要进行置换，将缓存中最近最少使用的内容替换出去腾出空间再读入新的内容。所以必须要提供一种策略来管理缓存器当中的内容，那这个内容是怎么提供的？不同的DBMS提供的算法是不一样的，缓存器所提供的任何内容在我们做出修改后，需要回存到磁盘上，如何进行回存呢？存到什么位置呢？我们还需要一个工具进行磁盘管理，这个工具称为磁盘空间管理器；

• 磁盘空间管理器

　　实现对磁盘空间中的某个数据文件中的数据到底应该如何存储进行管理的，由此数据必须要有磁盘空间管理器转换后存储在磁盘上；

• 故障恢复管理器

　　除此之外，一个DBMS还需要有故障恢复的功能，若服务器在运行过程当中突然间崩溃了，那下一次启动后应该让服务器某些数据本来正在存取过程当中的数据如存取了一半的数据，将它恢复为正常状态；

• 事务管理器

    锁管理器，并发的功能管理器的一个辅助工具；

SQL引擎提供的命令分类：

• DDL
• DML
• DCL

    这些命令执行的时候都需要提交给SQL引擎分析、生成支持计划、优化后才能执行，不同的数据库管理系统所实现的内在逻辑和功能模块不尽相同的，上图只是几个基本应该具备的功能。

 

MySQL

　　官方站点：www.mysql.com

    发行版分为：

• 商业版

　　商业版（Enterprise Edtion），提供了一部分增强型的功能，如企业级的性能监视器，还有一些商业组件，如备份工具、性能分析工具以及增强的连接池等等）

• 社区版

　　社区版（Community Edtion），免费开源直接使用，在某些功能上可能不如企业版强大；

    MySQL Workbench（GUI Tool）：图形化工具，是MySQL的管理器、语句生成器、语句执行分析器等，所以这是一个统一的图形化的工具，是一个客户端工具，也是一个服务器端工具；

    MySQL Connectors：MySQL连接器，php访问MySQL可以通过API直接访问，也可以通过驱动，而那个所谓的驱动就是连接器。

 

MySQL提供的软件包有三种格式：

• 软件包管理器特有的格式：

　　如：rpm包、Windows的.msi、.exe等

• 通用二进制格式；

　　不用安装，解压做一些简单配置即可使用；

• 源程序；

　　最简单的还是RedHat提供给我们的rpm包：

MySQL相关软件包简介：

　　mysql.x86_64：MySQL的客户端（MySQL是基于C/S架构的）；

　　mysql-server.x86_64：MySQL的服务器端；

　　mysql-connector-odbc：MySQL的专用ODBC连接器；

　　mysql-test.x86_64：测试组件；

　　mysql-bench.x86_64：性能分析组件或压力测试组件；

　　RedHat官方提供的mysql rpm包对我们来讲主要有两个:mysql,mysql-server，若用到php的话还需要php-mysql，这是php访问mysql的驱动。

若使用最新的mysql，就需要使用mysql官方提供的rpm包；

    若使用最新的mysql，就需要使用mysql官方提供的rpm包；

    但RedHat上其它应用程序将依赖于这个系统本身所提供的rpm包，所以若使用MySQL官方提供的rpm包，可能RedHat上的一些软件会自动安装RedHat官方提供的rpm包，由此，在RedHat若要使用很多依赖于mysql服务器的应用程序的话，不建议使用mysql官方的rpm包，因为很多应用程序版本之间是有依赖关系的，提供最新版本未必适用；

    若全部手动安装LAMP，可以使用mysql的源码包手动编译或通用二进制格式，除非特别需要定制某些mysql特性，否则在测试环境中完全可以使用通用二进制格式安装mysql。

　　MySQL是C/S架构的组件，事实上，在RedHat系列的系统上提供的组件就将它们分割开来了：

•     mysql：客户端
•     mysql-server：服务器端

    它们安装完成之后各自有二进制程序：

•        mysql：客户端
•        mysqld(-safe)：服务器端

　　mysql监听在tcp/3306，默认情况下应该使用普通用户运行，这样服务器故障或被劫持了不至于影响全局，一般以mysql用户mysql组的身份运行。

　　MySQL是一个RDBMS，这个RDBMS在生成数据库后这些数据应该保存在数据库服务器的某个目录当中或某个磁盘的某个文件系统上，默认在RedHat系列系统上，保存在/var/lib/mysql，但是这个位置不是特别理想的目录，因为若建一个500G的数据库，空间可能不足，而且增长数据非常快，因此可以修改数据文件目录的位置。

使用RedHat自带的rpm包安装MySQL：

　　需要注意的是，我们在安装server时，一般客户端也被安装上了。

　　在安装完成后，MySQL服务器有一个初始化的动作：

　　　　对于MySQL数据库而言，它有很多的元数据，如数据库的名称、表的名称、字段名称、字段属性信息等等，这些跟我们的数据本身没有关系，它们都是元数据，这些数据需要在一个位置存放，所以mysql在创建完成以后，有一个独特的数据库，就叫mysql，这个数据库当中存放的就是元数据。它里面比如存放的如：当前数据库上有多少个数据库、数据库里有什么、每一个数据库有多少个表分别叫什么、每一个有哪些字段等信息等等。但是刚开始安装好数据库时，mysql这个数据库是不存在的。初始化的过程就是建立这个mysql数据库的。

启动MySQL：

　　注意：

• 　　第一次启动时它会自动完成初始化的；
• 　　MySQL的root用户的密码默认初始为空，为了安全性，应修改密码；

查看端口：

MySQL客户端：

　　命令：

　　 mysql
        -u USERNAME：指定以哪个用户身份去连接，默认为root；
            mysql的用户定义包含了用户名以及能够允许登录的主机即客户端主机，格式如：USERNAME@HOST
        -p：需要输入密码，默认为空密码
        -h MYSQL_SERVER：指定主机（服务器），默认为localhost
        quit或q：退出

　　需要注意的是：在MySQL上创建用户时需要指定用户民密码以及允许使用这个用户登录的主机。上图中使用172.16.100.1就类似于使用远程主机登录，因为此IP地址（哪怕就是本机地址）默认不被允许登录。

　　当使用客户端连接MySQL服务器时，是基于TCP协议的，即长时间处于连接状态的协议，但如果客户端和服务器在同一台主机上，如：localhost或127.0.0.1，这样实现的就是本机进程间通信，这种性能就意味着它不需要使用网络驱动来连接，速度要快得多，这种本机进程间通信的方式在Linux上使用的是Socket（套接字文件）的方式进行连接的，而在Windows上使用的是memory（共享内存的方式）进行连接的。

 

　　Linux上MySQL套接字文件位置如下：

　　但是如果客户端和服务器不再同一台主机上，就必须要使用TCP/IP来连接了。如：使用-h 192.168.241.11，哪怕仍然是同一台主机，它也不会再使用socket这种方式连接了，而是使用TCP/IP协议。

　　mysql客户端有两种工作模式：

•        交互式模式

　　　　输一个命令执行一个密令；

•        批处理模式

　　　　可以写一堆脚本，里边放的是一对mysql命令，能够一次性执行n条命令，执行mysql脚本，类似于bash；

 

　　交互式模式中的命令类别：

•     　　客户端命令

　　　　在客户端本身执行的命令。如q。使用?可查看客户端命令

•     　　服务器端命令

　　　　指的是要发送给服务器端，由服务器端执行，并返回到客户端的命令。如：SELECT。所有服务器端命令必须使用语句结束符，默认为分号；

 

　　任何一个RDBMS都应该提供自己的SQL接口，而SQL接口实现时应该遵循SQL的ANSI规范。但事实上，每一个RDBMS在遵循某一种规范的同时，都对它进行了扩展。如Oracle，处理兼容了基本的SQL之外，还扩展了SQL语句叫PL/SQL（支持强大的编程功能，如：条件、选择、循环）、SQL Server：扩展了SQL叫T-SQL（Transcator-SQL）；

服务器端常用命令：

SHOW DATABASES;

　　information_schema：保证兼容的，保证如果其它服务器上不支持SHOW这样的命令的话，我们也能查找有多少个数据库。所以information_schema中就是将数据库中产生的执行信息以数据库的格式保存并提供了一个查询接口的。一方面是schema是mysql运行过程当中位于内存中的信息，如类似于profork目录，磁盘上没有任何信息，但一旦启动后它里面有一堆文件，但事实上他不是一个文件而是内核中的参数。此处也是，关机后schema是空的没有任何信息，但打开数据库发现里面有一堆信息，它是MySQL运行过程当中产生的一些运行时数据所存储的位置；

　　test：也是空的，只有测试的时候才会用到；

　　上述的每一个数据库都对应于文件系统上的一个目录，如：

　　information_schema位于内存中，是内存映射；

　　因此，在/var/lib上建立一个目录就意味着建立了一个数据库，如：

　　注意：

• 　最好不要通过创建目录的方式创建数据库，因为通过这种方式创建的数据库很有可能会引发无法预料的错误；
• 　所以对MySQL来讲数据库就是在其对应的数据目录下的一个子目录，数据库名称是否区分大小写取决于文件系统，如ext2、ext3区分大小写，而Windows不区分大小写。
  

 

数据库中的相关概念及常用命令

关系数据库对象：

•     库（MySQL可以定义库本身）
•     表
•     索引
•     视图
•     约束
•     存储过程
•     存储函数
•     触发器
•     游标
•     用户

 

•     权限
•     事务

 

表：

    由行、列组成；

    一个表称为一个实体；

       只要是同一个表，它的每一行的意义都是相同的，所以通常指实体的时候，每一行也称为实体，而这些称为实体集，即某一类实体的集合。

       表：row

       列：field， column

要想在表中插入数据，需要定义好表的格式（表字段）：

    字段名称，数据类型（对mysql而言，是强类型的，需要实现定义好），类型修饰（实施数据约束、限制的功能）

    字符：（默认不区分大小写）

•        CHAR(n)（最多存储256个字符）
•        VARCHAR(n)（最多存储65536个字符）
•        BINARY(n)（区分大小写的字符，与char一样，只存储固定字长的）
•        VARBINARY(n)（区分大小写的字符，存储变长字符）
•        TEXT(n)
•        BLOB(n)

 

    数值：

• 精确数值型：
  • 　　整型

• • • 　　　　TINYINT（微整型，使用一个字节，-128~127或0~255）

• • • 　　　　SMALLINT（2字节）

• • • 　　　　MEDIUMINT（3字节）

• • • 　　　　INT（4字节）

• • • 　　　　BIGINT（8字节）

              修饰符：

• 　　　　　　　　UNSINGED，无符号

•                 NOT NULL，不允许为空（字符也支持）

           十进制数值型（也是浮点的，但是数值是精确的，适用于财务计算中）

              DECIMAL

• 近似数值型：

• • 　　浮点型

• • • 　　　　FLOAT

• • • 　　　　DOUBLE

•     日期时间型：

 

• •     DATE

• • 　　TIME

• • 　　DATETIME

• • 　　STAMP

•     布尔

•     内置类型：ENUM（定义接受多少种类型），SET

 

对MySQL而言，命令不区分大小写。

 

 

MySQL中命令分类：

• DDL
  • 　　CREATE
  • 　　ALTER
  • 　　DROP
• DML
  • 　　INSERT
  • 　　UPDATE
  • 　　DELETE
• DCL
• 　　GRANT
• 　　REVOKE

DDL

　　创建数据库：

　　CREATE DATABASE [IF NOT EXISTS] db_name;

　　删除数据库：（过程不可逆，慎用）

　　DROP DATABASE [IF EXISTS] db_name;

　　创建表：

    CREATE TABLE tb_name(col1 ,col2 ,…);

　　查看库中的表：

　　SHOW TABLES (FROM db_name);

　　查看表的结构：

　　DESC tb_name;   # 表名区分大小写，因为表很可能作为一个文件存储

　　删除表：（不可逆，慎用）

　　DROP TABLE [IF EXISTS] tb_anme

　　修改表：

　　ALTER TABLE tb_name
        MODIFY（修改某个字段，不改字段名）
        CHANGE（改变字段名）
        ADD（添加一个字段）
        DROP（删除一个字段）
    获取帮助：help ALTER TABLE;

DML：

　　插入数据：

    INSERT INTO tb_name (col1,col2,…) VALUES|VALUE (‘STRING’,NUM,…);
    INSERT INTO tb_name (col1,col2,…) VALUES|VALUE (‘STRING’,NUM,…),(‘STRING’,NUM,…),…;

　　修改数据：

    UPDATE tb_name column SET column=value WHERE 

　　删除数据：

　　DELETE FROM tb_name WHERE CONDITION;

　　查询数据：

　　SELECT 字段 FROM tb_name WHERE CONDITION;
　　　　*：所有字段
　　　　WHERE：没有条件表示所有行；

　　　　选择和投影：

    　　　　选择：指定以某(些)字段作为搜索码，做逻辑比较，筛选符合条件的行，这个过程叫选择。（只显示部分行，而不是显示所有行）

       　　　　WHERE指定选择条件

    　　　　投影：显示指定的部分字段

DCL：

　　创建用户：

　　CREATE USER ‘USERNAME’@’HOST’ [IDENTIFIED BY ‘PASSWORD’];

　　删除用户：

　　DROP USER ‘USERNAME’@’HSOT’;

　　　　需要注意的是：jerry@localhost与jerry@172.16.100.1不是同一个用户！

　 Host的表示方法：

•  IP
•  HOSTNAME
•  NETWORK
•  通配符

　　_：匹配任意单个字符，如：172.16.0.__表示从172.16.0.10到172.16.0.99都包含进来了；

　　%：匹配任意长度的任意字符，如：jerry@’%’，表示jerry可以从所有主机上登录

　　

　　授权：

    GRANT pri1,pri2,… ON DB_NAME.TB_NAME TO ‘USERNAME’@’HOST’ [IDENTYFIED BY ‘PASSWORD’];
　　　　#在哪个库的哪张表上授予什么权限给谁，若用户不存在，会自动创建用户并授权。

　　取消授权：

    REVOKE pri1,pri2,… ON DB_NAME.TB_NAME FROM ‘USERNAME’@’HOST’;

　　查看用户的授权：

　　SHOW GRANTS FOR ‘USERNAME’@’HOST’;

　　

　　　　所有权限的表示方式：ALL PRIVILEGES

注意：对于MySQL而言比较独特，修改mysql.user表是直接修改表的内容，而MySQL对用户的所有认证信息是来自内存当中的，所以只有让内存重新加载这张表，它才能知道这个用户的密码改变了，所以一般而言，在修改一个用户的密码和授权以后，使用FLUSH PRIVILIGES;让MySQL进程数据库重读授权表。

MySQL为用户设定密码的方式：

1、

　　mysql>SET PASSWORD FOR ‘USERNAME’@’HOST’=PASSWORD(‘password’);

2、

　　# mysqladmin –uUSERNAME -hHOST –p password ‘password’        #指定老密码并改为新密码

3、

　　mysql>UPDATE user SET Password=PASSWORD(‘password’) WHERE USER=’USERNAME’ [AND HOST=’HOST’] ;

实现root远程连接并能执行所有权限：

　　一般而言不让root远程连接。因为有风险，登录的时候是远程发送的密码是明文的；

常用的MySQL的图形化客户端工具：

1. phpMyAdmin
2. Workbench
3. MySQL Front
4. Navicat for MySQL
5. Toad