第二章 - MySQL数据库语言基础
第二章 - MySQL数据库语言基础
2.1 MySQL数据库概述
MySQL是一个开放源码的小型、跨平台数据库管理系统，被广泛地应用在Internet上的中小型网站中。由于MySQL具有体积小、运行速度快、总体拥有成本低、开放源码的优势，许多中小型网站都为了降低网站总体拥有成本而选择了MySQL作为网站数据库。
2.1.1 MySQL数据库的发展背景
1996年发布了能够在小范围内使用的MySQL 1.0版。
1999年MySQL AB公司在瑞典成立。MySQL数据库从此能够支持事务处理了。
2000年MySQL数据库集成了存储引擎InnoDB，该引擎是最为成功的MySQL事务存储引擎。
2003年12月，MySQL 5.0版本发布，提供了视图和存储过程等功能。
2008年11月，MySQL 5.1发布，它提供了分区、事件管理,同时修复了大量的Bug。
2009年4月，甲骨文公司收购Sun公司，自此MySQL数据库进入Oracle时代。
2013年2月，甲骨文公司宣布MySQL 5.6正式版发布，首个正式版本号为5.6.10。2013年4月发布5.7.1版本。
2016年12月发布的mysql-installer-community-5.7.17版本
2.1.2 MySQL使用优势
MySQL数据库是开放源代码的数据库。 保证了MySQL数据库是一款可以自由使用的数据库。
MySQL数据库的跨平台性。 MySQL不仅可以在Windows系列的操作系统上运行，还可以在UNIX、Linux和Mac OS等操作系统上运行。
MySQL的价格优势。
功能强大使用方便。 MySQL数据库是一个多用户、多线程SQL数据库服务器，它是C/S结构的实现，由一个服务器守护程序mysqlId和很多不同的客户程序和库组成。MySQL能够快速、有效和安全的处理大量的数据，并达到是快速、健壮和易用的目标。
2.1.3 MySQL系统特性
使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性。并为PHP、Java、C、C++、Python、Perl、Eiffel、Ruby和Tcl等多种编程语言提供了应用程序接口API。
支持Windows、Linux、Mac OS、AIX、FreeBSD、HP-UX、NovellNetware、OpenBSD、OS/2 Wrap以及Solaris等多种操作系统。
MySQL支持多线程，能够充分利用CPU资源。
能够自动优化SQL查询算法，有效地提高了信息查询速度。
能够作为一个单独的应用程序应用在客户端服务器网络环境中，也可以作为一个库嵌入到其他软件中。
提供多种自然语言支持，常见的编码如中文的GB2312、BIG5及国际通用转换格式UTF-8等,都可以用作数据表名和数据列名。
提供TCP/IP、ODBC和JDBC等多种数据库连接技术。
支持多种存储引擎，提供用于管理、检查、优化数据库操作的管理工具。
具有大型数据库所有常用功能，可以处理拥有亿万条记录级的海量数据。
2.1.4 MySQL发行版本
MySQL的目前一般可用版本已经经过严格标准的测试，可以保证其安全可靠地使用。
根据操作系统的类型来划分，大体上可以分为 Windows版、UNIX版、Linux版和Mac OS版。如果要下载MySQL数据库，要了解自己的操作系统，然后根据操作系统来下载相应的MySQL数据库。
根据发布顺序来划分，MySQL数据库可以分为4.0、5.0、5.1以及5.7等系列版本。
根据MySQL数据库的开发情况，可将其分为Alpha、Beta、Gamma和Generally Available等版本。
Alpha：处于开发阶段的版本，可能会增加新的功能或进行重大修改。
Beta：处理测试阶段的版本，开发已经基本完成，但是没有进行全面的测试。
Gamma：该版本是发行过一段时间的Beta版，比Beta版要稳定一些。
Generally Available：该版本已经足够稳定，可以在软件开发中应用。有些资料会将该版本称为Production版。
根据MySQL数据库用户群体的不同，将其分为社区版(Community Edition)和企业版(Enterprise)。
社区版是自由下载而且是免费开源的，但是没有官方的技术支持。
企业版提供了最全面的高级功能、管理工具和技术支持，实现了最高水平的MySQL数据库可扩展功能、安全性、可靠性和无故障运行时间。
2.1.5 MySQL 的新功能
支持JSON。JSON（Java Script Object Notation的缩写）是一种存储信息的格式，可以很好地替代XML。
性能和可扩展性。改进InnoDB的可扩展性和临时表的性能，从而实现更快的网络和大数据加载等操作。
改进复制以提高可用性的性能。改进复制包括多源复制、多从线程增强、在线GTIDs和增强的半同步复制。
性能模式提供更好的视角。增加了许多新的监控功能，以减少空间和过载，使用新的SYS模式显著提高易用性。
保证数据库的安全。以安全第一为宗旨，提供了很多新的功能，从而保证数据库的安全。
对多种性能进行了优化。重写了大部分解析器、优化器和成本模型，这提高了可维护性、可扩展性和性能。
支持GIS （Geographic information system，地理信息系统）。MySQL 5.7 全新的功能，包括 InnoDB 空间索引和Boost几何，同时提高完整性和标准符合性。
2.2 安装MySQL数据库
2.2.1 Linux / UNIX 上安装 MySQL
Linux 平台上推荐使用 RPM 包来安装 MySQL ，MySQL AB 提供了以下 RPM 包的下载地址：
MySQL - MySQL 服务器。如果不是只想连接运行在另一台机器上的 MySQL 服务器，请你选择该选项。
MySQL-client - MySQL 客户端程序，用于连接并操作 Mysql 服务器。
MySQL-devel - 库和包含文件，如果你想要编译其它如 Perl 模块等 MySQL 客户端，则需要安装该 RPM 包。
MySQL-shared - 该软件包包含某些语言和应用程序需要动态装载的共享库(libmysqlclient.so*)，使用 MySQL。
MySQL-bench - MySQL 数据库服务器的基准和性能测试工具。
以下安装 MySQL RMP 的实例是在 SuSE Linux 系统上进行，当然该安装步骤也适合应用于其他支持 RPM 的 Linux 系统，如: CentOS。
安装前，我们可以检测系统是否自带安装 MySQL：
rpm -qa | grep mysql
如果你系统有安装，那可以选择进行卸载:
rpm -e mysql // 普通删除模式
rpm -e --nodeps mysql // 强力删除模式，如果使用上面命令删除时，提示有依赖的其它文件，则用该命令可以对其进行强力删除
MySQL 安装步骤如下：
使用 root 用户登陆你的 Linux 系统。
下载 MySQL RPM 包，下载地址为：MySQL 下载。
通过以下命令执行 MySQ L安装，rpm 包为你下载的 rpm 包：
[root@host]# rpm -i MySQL-5.0.9-0.i386.rpm
以上安装MySQL服务器的过程会创建MySQL用户，并创建一个MySQL配置文件my.cnf。
你可以在/usr/bin和/usr/sbin中找到所有与MySQL相关的二进制文件。所有数据表和数据库将在/var/lib/mysql目录中创建。
以下是一些MySQL可选包的安装过程，你可以根据自己的需要来安装：
[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm
2.2.2 Window上安装MySQL
在Windows操作系统下，MySQL数据库的安装一般选择图形化界面安装，图形化界面包有完整的安装向导，安装和配置非常方便。
安装准备。在安装之前，需要到MySQL数据库的官方网站(http://dev.mysql.com/downloads)上找到要安装的数据库版本并进行下载。当然，读者也可以直接在一些搜索引擎中搜索下载链接。
MySQL下载完成后找到下载到本地的文件，并且解压缩包，简单安装步骤如下。
（1） 双击MySQL安装程序(mysql-installer-community-5.7.17.0.msi)，弹出如图1-8所示的“打开文件-安全警告”窗口。
（2）在图1-8所示的窗口中单击“运行”按钮，进入MySQL Installer的协议许可协议界面，如图1-9所示，选择I accept the license terms按钮，表示接受用户安装时的许可协议。
（3）单击next按钮，进入安装类型选择界面如图1-10所示。可以选择需要的版本，左侧提供5种安装类型
默认选中Developer Default选项。
Server only表示仅作为服务器
Client only表示仅作为客户端
Full表示完全安装类型
Custom表示自定义安装类型。
（4）单击next按钮，进入将要安装或更新的应用程序界面如图1-11所示。
5）单击Execute按钮，进入账户和角色界面，按要求设置root账户密码。如图1-12为root用户设置密码
（6）单击next按钮，如图1-13所示，进入开始安装和配置MySQL服务器界面，依次按照提示，保持默认选择，就可以进入安装完成后的界面，如图1-14所示。
单击图1-14所示的Finish按钮就可以运行MySQL数据库了。
MySQL服务的配置
（1）右击“计算机”图标，在弹出的快捷菜单中选择“属性”命令，在弹出的对话框中选择“高级系统设置”，弹出“系统属性”对话框。
（2）在“系统属性”对话框中，选择“高级”选项，单击“环境变量”按钮，弹出“环境变量”对话框。
（3）在“环境变量”对话框中，定位到“系统变量”中的path选项，单击“编辑”按钮，将弹出“编辑系统变量”对话框。
（4）在“编辑系统变量”对话框中，将MySQL服务器的bin文件夹位置（G:\Program Files\MySQL\MySQL Server 5.7\bin）添加到变量值文本框中，注意要使用“;”与其他变量值进行分隔，最后，单击“确定”按钮。
（5）环境变量设置完成后，再使用MySQL命令即可成功连接MySQL服务器。
（6）断开MySQL服务器。连接到MySQL服务器后，可以通过在MySQL提示符下输入exit或者quit命令断开MySQL连接，格式如下：
mysql> quit;
2.2.3 验证MySQL安装
在成功安装MySQL后，一些基础表会表初始化，在服务器启动后，你可以通过简单的测试来验证MySQL是否工作正常。
使用 mysqladmin 工具来获取服务器状态：
使用 mysqladmin 命令俩检查服务器的版本,在linux上该二进制文件位于 /usr/bin on linux ，在window上该二进制文件位于C:\mysql\bin 。
[root@host]# mysqladmin --version
linux上该命令将输出以下结果，该结果基于你的系统信息：
mysqladmin Ver 8.23 Distrib 5.0.9-0, for redhat-linux-gnu on i386
如果以上命令执行后未输入任何信息，说明你的MySQL未安装成功。
使用 MySQL Client(MySQL客户端) 执行简单的SQL命令
你可以在 MySQL Client(MySQL客户端) 使用 MySQL 命令连接到MySQL服务器上，默认情况下MySQL服务器的密码为空，所以本实例不需要输入密码。
命令如下：
[root@host]# mysql
以上命令执行后会输出 mysql > 提示符，这说明你已经成功连接到 MySQL 服务器上，你可以在 mysql > 提示符执行 SQL 命令：
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.13 sec)
MySQL安装后需要做的
MySQL 安装成功后，默认的 root 用户密码为空，你可以使用以下命令来创建root用户的密码：
[root@host]# mysqladmin -u root password "new_password";
现在你可以通过以下命令来连接到MySQL服务器：
[root@host]# mysql -u root -p
Enter password:*******
注意：在输入密码时，密码是不会显示了，你正确输入即可。
Linux系统启动时启动 MySQL
如果你需要在Linux系统启动时启动 MySQL 服务器，你需要在 /etc/rc.local 文件中添加以下命令：
/etc/init.d/mysqld start
同样，你需要将 mysqld 二进制文件添加到 /etc/init.d/ 目录中。
2.3 MySQL工作流程
2.3.1 MySQL工作流程
（1）操作系统用户启动MySQL服务。
（2）MySQL服务启动期间，首先将配置文件中的参数信息读入服务器内存。
（3）根据MySQL配置文件的参数信息或者编译MySQL时参数的默认值生成一个服务实例进程Instance。
（4）MySQL服务实例进程派生出多个线程为多个客户机提供服务。
（5）数据库用户访问MySQL服务器的数据时，首先需要选择一台登录主机，然后在该登录主机上开启客户机，输入正确的账户名、密码，建立一条客户机与服务器之间的“通信链路”。
（6）接着数据库用户就可以在MySQL客户机上输入MySQL命令或SQL语句，这些MySQL命令或SQL语句沿着该通信链路传送给MySQL服务实例，这个过程称为客户机向MySQL服务器发送请求。
（7）MySQL服务实例负责解析这些MySQL命令或SQL语句，并选择一种执行计划运行这些MySQL命令或SQL语句，然后将执行结果沿着通信链路返回给客户机，这个过程称为MySQL服务器向MySQL客户机返回响应。
（8）数据库用户关闭MySQL客户机，通信链路被断开，该客户机对应的MySQL会话结束。
2.3.2 MySQL的启动和登录
启动MySQL服务
（1）cmd控制台启动。这种方式非常简单，net start mysql57 表示启动MySQL服务，net stop mysql57 表示关闭MySQL服务。
（2）手动启动。执行“开始”“设置”“控制面板”“管理工具”“服务”命令进行设置，打开的窗口（执行“开始”“运行”命令并输入services.msc后按Enter键也可以弹出窗口）。
更改MySQL服务的启动类型，选中MySQL57服务项右击，在弹出的快捷菜单中选择“属性”命令，弹出的对话框。
上图可以看到，可以更改服务状态为“停止”、“暂停” 和“ 恢复”，还可以设置服务的启动类型。在“启动类型”下拉列表框中可以选择“自动”、“手动”和“已禁用”选项，说明如下。
自动 ：MySQL服务自动启动，可以手动将服务状态变为停止、暂停和重新启动等。如果读者经常练习MySQL数据库的操作，最好将MySQL设置为自动启动，这样可以避免每次手动启动MySQL服务。
手动 ：MySQL服务需要手动启动，启动后可以改变服务状态，如停止和暂停等。如果读者使用MySQL数据库的频率很低，可以考虑将MySQL服务设置为手动启动，这样可以避免MySQL服务长时间占用系统资源。
已禁用 ：MySQL服务不能启动，也不能改变服务状态。
登录MySQL数据库
MySQL服务启动后，可以通过客户端来登录MySQL数据库。Windows操作系统下有两种登录MySQL数据库的方式：
执行cmd命令，在打开的DOS窗口中以命令行的方式登录MySQL数据库；
在MySQL客户端直接登录数据库。
(1) 在DOS窗口中登录MySQL数据库。通过DOS窗口登录MySQL数据库执行语句时，可以执行“开始” “运行”命令，在弹出的对话框中输入cmd后按Enter键，即可进入DOS窗口。
登录成功后会出现“Welcome to the MySQL monitor”的欢迎语。
C:\Users\Administrator>net start mysql57
请求的服务已经启动。
C:\Users\Administrator>mysql -u root -p
Enter password: ****
Mysql>
上述执行语句的代码中，
-u后面紧跟着数据库的用户名，此处使用root用户进行登录；
-p表示用户密码，按Enter键输入密码，输入的密码使用星号(*)表示。
(2) MySQL客户端登录数据库。在Windows7操作系统中，执行“开始” “所有程序” MySQL MySQL 5.7目录。
MySQL Command Line Client
MySQL Command Line Client-Unicode
它们都是MySQL客户端的命令行工具，也可以称为MySQL的DOS窗口或控制台。通过在控制台中执行语句可以登录MySQL数据库，然后执行其他的相关SQL语句进行操作。
DOS窗口和控制台包含一些说明性的语句如下：
Commands end with ; or \g：说明MySQL控制台下的命令是以分号(;)或“\g”来结束的，遇到这个结束符就开始执行命令。
Your MySQL connection id is 5：id表示MySQL数据库的连接次数，如果数据库是新安装的，且是第一次登录，则显示1。如果安装成功后已经登录过，将会显示其他的数字。
Server version：Server version之后的内容表示当前数据库版本，这里安装的版本是5.7.17-enterprise-commercial-advanced。
Type 'help;' or '\h' for help：表示输入“help;”或者\h可以看到帮助信息。
Type ‘\c’ to clear the current input statement：表示遇到\c就清除当前输入语句。
例如，登录MySQL数据库成功后可以直接输入“help;”或\h查看帮助信息，直接在控制台中输入“help;”语句按Enter键，输出结果如下：
mysql> help
2.4 MySQL数据库工作简介
MySQL数据库管理系统提供了许多命令行工具，这些工具可以用来管理MySQL服务器、对数据库进行访问控制、管理MySQL用户以及数据库备份和恢复工具等。另外，MySQL还提供了图形化的管理工具，这使得对数据库的操作更加简单。
2.4.1 MySQL服务器端的常用工具
（1）mysqld：SQL后台程序（即MySQL服务器进程）。该程序必须运行之后，客户端才能通过连接服务器来访问数据库。
（2）mysqld_safe：服务器启动脚本。在UNIX和NetWare中推荐使用mysqld_safe来启动mysqld服务器。mysqld_safe增加了一些安全特性，例如当出现错误时重启服务器并向错误日志文件写入运行时间信息。
（3）mysql.server：服务器启动脚本。在UNIX中的MySQL分发版包括mysql.server脚本。该脚本用于使用包含为特定级别的、运行启动服务的脚本的、运行目录的系统。它调用mysqld_safe来启动MySQL服务器。
（4）mysql_multi：服务器启动脚本，可以启动或停止系统上安装的多个服务器。
（5）myisamchk：用来描述、检查、优化和维护MyISAM表的实用工具。
（6）mysqlbug：MySQL缺陷报告脚本。它可以用来向MySQL邮件系统发送缺陷报告。
（7）mysql_install_db：该脚本用默认权限创建MySQL授权表。通常只是在系统上首次安装MySQL时执行一次。
2.4.2 MySQL客户端常用工具
（1）myisampack：压缩MyISAM表以产生更小的只读表的一个工具。
（2）mysql：交互式输入SQL语句或从文件以批处理模式执行它们的命令行工具。
（3）mysqlaccess：检查访问主机名、用户名和数据库组合的权限的脚本。
（4）MySQLadmin：执行管理操作的客户程序，例如创建或删除数据库，重载授权表，将表刷新到硬盘上，以及重新打开日志文件。MySQLadmin还可以用来检索版本、进程，以及服务器的状态信息。
（5）mysqlbinlog：从二进制日志读取语句的工具。在二进制日志文件中包含执行过的语句，可用来帮助系统从崩溃中恢复。
（6）mysqlcheck：检查、修复、分析以及优化表的表维护客户程序。
（7）mysqldump：将MySQL数据库转储到一个文件（例如SQL语句或tab分隔符文本文件）的客户程序。
（8）mysqlhotcopy：当服务器在运行时，快速备份MyISAM或ISAM表的工具。
（9）mysql import：使用load data infile将文本文件导入相关表的客户程序。
（10）mysqlshow：显示数据库、表、列以及索引相关信息的客户程序。
（11）perror：显示系统或MySQL错误代码含义的工具。
2.5 MySQL图形化管理工具
MySQL的图形管理工具有很多，常用的有MySQL Workbench、phpMyAdmin和Navicat等软件。本书选用MySQL Workbench软件作为可视化操作的管理工具
MySQL WorkBench软件的安装步骤如下：
（1）双击安装文件“mysql-workbench-community-6.2.5-win32.msi”。进入“安全警告”界面，单击next按钮，进入安装向导界面。
（3）依次单击next按钮，以此进入选择安装类型、和准备安装项目界面。
（4）单击Install按钮，进入安装过程，最后单击Finish按钮，即可完成MySQL Workbench软件的安装。
（5）选择“开始”“所有程序”，
单击MySQL 下的MySQL Workbench 6.2CE命令，即可进入如图1-26的MySQL Workbench界面，接下来就可以利用MySQL Workbench软件实现MySQL数据库的可视化操作了。
MySQL Workbench工具包含以下4个基本功能区域。
MySQL Workbench工具包含以下4个基本功能区域。
主菜单：实现MySQL的主要功能操作。
Shortcut（快捷方式）：完整的可视化数据库设计和建模。
MySQL Connections ：连接信息.
Models ：连接方式、MySQL Workbench工具版本信息。
（6）创建连接。在图形界面中最常用的还是对数据库的基本操作，例如，执行SQL语句实现数据库的添加、数据库表的添加和数据添加、删除以及修改等操作。如果要实现这些操作首先要连接到数据库，单击主菜单Database| Manage Connections命令，弹出Manage Server Connections对话框。在如图1-27所示的对话框中输入连接名称，输入完成后单击Test Connection按钮进行测试，输入root密码，测试成功后，单击OK按钮。返回主界面单击Close按钮即可完成连接。
2.6 MySQL基本语法
MySQL语言是一系列操作数据库及数据库对象的命令语句，因此使用MySQL数据库就必须掌握构成其基本语法和流程语句的语法要素，这主要包括常量、变量、关键词、运算符、函数、表达式和控制流语句等。
字符集是最基本的MySQL脚本组成部分，也是MySQL数据库对象的描述符号。
MySQL能够支持39种字符集和127个校对原则。本节着重介绍latin1、UTF-8和gb2312字符集的用法，同时也介绍常量、变量、标识符和关键词的使用。
2.6.1 字符集与标识符
字符集与标识符。 字符集及字符序概念 。字符(Character)是指人类语言中最小的表义符号。例如‘A’、 ‘7’、“%”等字母、数字和特殊符号。字符校对原则(Collation)也称为字符序，是指在同一字符集内字符之间的比较规则。
字符集只有在确定字符序后，才能在一个字符集上定义什么是等价的字符，以及字符之间的大小关系。每个字符序唯一对应一种字符集，但一个字符集可以对应多种字符校对原则，其中有一个是默认字符校对原则(Default Collation)。
MySQL服务器默认的字符集是latin1。MySQL的字符集支持可以细化到4个层次：服务器(Server)、数据库(DataBase)、数据表(Table)和连接层(Connection)。
如果不进行设置，那么连接层级、客户端级和结果返回级、数据库级、表级、字段级都默认使用latin1字符集。
MySQL的字符集通过show character set语句查看。在命令窗口中执行如下命令，即可查看到MySQL的39种字符集。
mysql> show character set;
对于任何一个给定的字符集至少有一个校对原则，也可能有几个校对原则。例如，执行显示latin1系列的命令：
mysql> show collation like ‘latin1%’;
字符集与标识符（说明）
系统启动时默认的字符集是latin1。
UTF-8是针对Unicode字符的一种变长字符编码。例如，如果是UTF-8编码，则在外国人的英文IE上也能显示中文，他们无需下载IE的中文语言支持包。
gb2312是简体中文字符集，GBK是是在国家标准GB 2312基础上扩容后兼容GB 2312的标准。
GBK、GB2312等与UTF-8之间都必须通过Unicode编码才能相互转换。对于一个网站、论坛来说，如果英文字符较多，则建议使用UTF-8节省空间。不过现在很多论坛的插件一般只支持GBK。
标识符和关键字。
MySQL的脚本由一条或多条MySQL语句组成，脚本文件后缀名一般为 .sql。
标识符。标识符用来命名一些对象，其通用命名规则是: 标识符由以字母或下划线开头的字母、数字或下划线（_）序列组成。对于标识符是否区分大小写取决于当前的操作系统， Windows下是不敏感的， 但对于大多数 linux\unix 系统来说， 这些标识符大小写是敏感的。
关键字。MySQL的关键字众多，不同版本的MySQL语言关键词也略有变化。MySQL5.7大约有400个左右关键词。所有关键字有自己特定的含义，尽量避免作为标识符。
2.6.2 MySQL字符集的转换过程
编译MySQL时，系统默认的字符集是latin1。可以通过如下方法进行转换。
（1）最简单的修改方法，就是修改MySQL的my.ini（C:\Program Files \MySQL\MySQL Server 5.7）文件中的字符集，查找[mysql]键值，在下面加上一行“default-character-set=utf8” 。修改完后，重启MySQL的服务，使用下列语句查看，发现数据库编码均已改成utf8。
mysql> show variables like 'character%';
（2）还有一种修改字符集的方法，就是使用MySQL的命令。用命令行的方式修改，只是临时更改，当服务器重启后，又将恢复默认设置。
mysql> set character_set_client = utf8;
mysql> set character_set_connection = utf8;
mysql> set character_set_database = utf8;
mysql> set character_set_results = utf8;
mysql> set character_set_server = utf8;
（3） 如果设置表的MySQL默认字符集为utf8，并且通过UTF-8编码发送查询，有时存入数据库的仍然是乱码。问题就出在这个connection连接层上。解决方法是在发送查询前执行一下下面这个句子。
MySQL> set nameS ('UTF8');
与这3个语句等价。
mysql> set character_set_client =(UTF8);
mysql> set character_set_results =(UTF8);
mysql> set character_set_connection =(UTF8);
2.6.3 MySQL中的字符集层次设置
MySQL对于字符集的支持细化到4个层次:服务器(Server)、数据库(DataBase)、数据表(Table)和连接(Connection)。
MySQL对于字符集的指定可以细化到一个数据库、一张表和一列，可以细化到应该用什么字符集。 MySQL用下列的系统变量描述字符集。
（1） character_set_server和collation_server ：这两个变量是服务器的字符集，默认的内部操作字符集。
（2）character_set_client：客户端来源数据使用的字符集，这个变量用来决定MySQL怎么解释客户端发到服务器的SQL命令文字。
（3）character_set_connection和collation_connection ：连接层字符集。这两个变量用来决定MySQL怎么处理客户端发来的SQL命令。
（4）character_set_results：查询结果字符集，当SQL有结果返回的时候，这个变量用来决定发给客户端的结果中文字量的编码。
（5）character_set_database和collation_database ：当前选中数据库的默认字符集，create database命令有两个参数可以用来设置数据库的字符集和比较规则。
（6）character_set_system：系统元数据的字符集，数据库、表和列的定义都是用的这个字符集。它有一个定值，是UTF-8 。
对于以“collation_”开头的同上面对应的变量，用来描述字符集校对原则。
表的字符集： create table的参数里可以设置，为列的字符集提供默认值。
列的字符集：决定本列的文字数据的存储编码。列的比较规则比collation_connection高。也就是说，MySQL会把SQL中的文字直接量转成列的字符集后再与列的文字数据比较。
字符集的依附关系
2.7 MySQL数据类型
2.7.1 常量与变量
1、常量
常量也称为文字值或标量值，是指某个过程中值始终不改的量。MySQL的常量类型和用法。
2、系统变量
变量就是在某个过程中，其值是可以改变的量。系统变量包括全局系统变量和会话系统变量两种类型。全局变量和会话变量的区别：
全局变量 在MySQL启动时由服务器自动将它们初始化为默认值，主要影响整个mysql实例的全局设置，大部分全局变量都是作为mysql的服务器调节参数存在。对全局变量的修改会影响到整个服务器。
会话变量 在每次建立一个新的连接时，由MySQL来初始化。会话变量的定义是前面加一个@符号，随时定义和使用，会话结束就释放。即对会话变量的修改，只会影响到当前的会话，也就是当前的数据库连接。
大多数的系统变量应用于其他SQL语句时，必须在名称前加两个@符号。例如：
select @@version,current_date;
显示系统变量清单的格式。
show [global|session] variables [like ‘字符串’];
【实例】查看字符“a”开头的系统变量命令如下：
show variables like ‘a%’;
修改系统变量的值。
在MySQL中，有的系统变量的值是不能改变的，如@@version和系统日期，而有些系统变量是可以通过set语句来修改的，例如将全局系统变量sort_buffer_size的值改为25000。
set @@global.sort_buffer_size=25000；
对于当前会话，把系统变量sql_select_limit的值设置为100
set @@session.sql_select_limit=100;
执行如下命令可以显示。
select @@local.sql_select_limit;
//set @@local.sql_select_limit=default;
2.7.2 数据类型
数据类型是数据的一种属性，其可以决定数据的存储格式、有效范围和相应的值范围限制。
MySQL的数据类型包括字符串类型、整数类型、浮点数类型、定点数类型、日期和时间类型和二进制数据类型。
在MySQL中创建表时，需要考虑为字段选择哪种数据类型是最合适的。选择了合适的数据类型，会提高数据库的效率。
1、字符串类型
字符串类型是在数据库中存储字符串的数据类型。
字符串类型包括char、varchar、blob、text、enum和set。字符串类型可以分为2类：普通的文本字符串类型（char和varchar）和特殊类型（set和enum）。它们之间都有一定的区别，取值的范围不同，应用的地方也不同。
（1）普通的文本字符串类型，即char和varchar类型，char列的长度被固定为创建表所声明的长度，取值在1~255之间；varchar列的值是变长的字符串，取值和char一样。下面介绍普通的文本字符串类型如表2-2所示。
| 类型 | 取值范围 | 说明 |
|---|---|---|
| [national] char(m) [binary|ASCII|unicode] | 0~255个字符 | 固定长度为m的字符串，其中m的取值范围为0~255。National关键字指定了应该使用的默认字符集。Binary关键字指定了数据是否区分大小写（默认是区分大小写的）。ASCII关键字指定了在改列中使用latin1字符集。Unicode关键字指定了使用UCS字符集 |
| char | 0~255个字符 | Char(m)类似 |
| [national] varchar(m) [binary] | 0~255个字符 | 长度可变，其他和char(m)类似 |
（2）特殊类型set和enum 。特殊类型set和enum的介绍如表2-3所示。
| 类型 | 最大值 | 说明 |
|---|---|---|
| Enum (“value1”, “value2”, …) | 65 535 | 该类型的列只可以容纳所列值之一或为null |
| Set (“value1”, “value2”, …) | 64 | 该类型的列可以容纳一组值或为null |
说明：在创建表时，使用字符串类型时应遵循以下原则：
（1）从速度方面考虑，要选择固定的列，可以使用char类型。
（2）要节省空间，使用动态的列，可以使用varchar类型。
（3）要将列中的内容限制在一种选择，可以使用enum类型。
（4）允许在一个列中有多于一个的条目，可以使用set类型。
（5）如果要搜索的内容不区分大小写，可以使用text类型。
2、数字类型
数字类型总体可以分成整型和浮点型两类。
整数类型
整数类型是数据库中最基本的数据类型。标准SQL中支持integer和smallint这两类整数类型。这些类型包括准确数字的数据类型（numeric、decimal、integer和smallint），还包括近似数字的数据类型（float、real和double precision）。其中的关键词int是integer的同义词。
| 数据类型 | 取值范围 | 说明 | 单位 |
|---|---|---|---|
| tinyint | 符号值：-127 ~ 127 无符号值：0 ~ 255 |
最小的整数 | 1字节 |
| bit | 符号值：-127 ~ 127 无符号值：0 ~ 255 |
最小的整数 | 1字节 |
| bool | 符号值：-127 ~ 127 无符号值：0 ~255 |
最小的整数 | 1字节 |
| smallint | 符号值：- 32768 ~ 32767 无符号值：0 ~ 65535 |
小型整数 | 2字节 |
| mediumint | 符号值：-8388608 ~ 8388607 无符号值：0 ~ 16777215 |
中型整数 | 3字节 |
| int | 符号值：-2147683648 ~ 2147683647 无符号值：0 ~ 4294967295 |
标准整数 | 4字节 |
| bigint | 符号值：-9223372036854775808 ~ 9223372036854775807 无符号值：0 ~ 18446744073709551615 |
大整数 | 8字节 |
浮点数据类型
MySQL中使用浮点数类型和定点数类型来表示小数。浮点数类型包括单精度浮点数（float型）和双精度浮点数（double型）。定点数类型就是decimal型，关键词dec是decimal的同义词。
| 数据类型 | 取值范围 | 说明 | 单位 |
|---|---|---|---|
| float | +(-)3.402823466E+38 | 单精度浮点数 | 8或4字节 |
| double | +(-)1.7976931348623157E+308 +(-)2.2250738585072014E-308 |
双精度浮点数 | 8字节 |
| decimal | 可变 | 一般整数 | 自定义长度 |
说明：在创建表时，使用哪种数字类型，应遵循以下原则。
（1）选择最小的可用类型，如果值永远不超过127，则使用tinyint比int强。
（2）对于完全都是数字的，可以选择整数类型。
（3）浮点类型用于可能具有小数部分的数。例如货物单价、网上购物交付金额等。
3、日期和时间数据类型
日期与时间类型是为了方便在数据库中存储日期和时间而设计的。
MySQL中有多种表示日期和时间的数据类型。其中，year类型表示年份；date类型表示日期；time类型表示时间；datetime和timestamp表示日期和时间。其中的每种类型都有其取值的范围，如赋予它一个不合法的值，将会被“0”代替。下面介绍日期和时间数据类型。
| 类型 | 取值范围 | 说明 |
|---|---|---|
| date | 1000-01-01 | 日期，格式YYYY-MM-DD |
| time | -838:58:59 835:59:59 |
时间，格式HH：MM：SS |
| datetime | 1000-01-01 00:00:00 9999-12-31 23:59:59 |
日期和时间，格式YYYY-MM-DD HH：MM：SS |
| timestamp | 1970-01-01 00:00:00 2037年的某个时间 |
时间标签，在处理报告时使用显示格式取决于M的值 |
| year | 1901-2155 | 年份可指定两位数字和四位数字的格式 |
4、二进制类型
二进制类型是在数据库中存储二进制数据的数据类型。二进制类型包括binary、varbinary、bit、tinyblob、blob、mediumblob和longblob类型。tinytext、longtext和text等适合存储长文本的类型，也放在这里介绍。
其中，text和blob类型。它们的大小可以改变，text类型适合存储长文本，而blob类型适合存储二进制数据，支持任何数据，例如文本、声音和图像等。下面介绍text和blob类型，如表2-7所示。
| 类型 | 最大长度（字节数） | 说明 |
|---|---|---|
| tinyblob | 2^8~1(225) | 小blob字段 |
| tinytext | 2^8~1(225) | 小text字段 |
| blob | 2^16~1(65 535) | 常规blob字段 |
| text | 2^16~1(65 535) | 常规text字段 |
| mediumblob | 2^24~1(16 777 215) | 中型blob字段 |
| mediumtext | 2^24~1(16 777 215) | 中型text字段 |
| longblob | 2^32~1(4 294 967 295) | 长blob字段 |
| longtext | 2^32~1(4 294 967 295) | 长text字段 |
2.8 MySQL运算符和表达式
运算符是用来连接表达式中各个操作数的符号，其作用是指明对操作数所进行的运算。
MySQL数据库通过使用运算符，不但可以使数据库的功能更加强大，而且可以更加灵活的使用表中的数据。
MySQL运算符包括4类，分别是算术运算符、比较运算符、逻辑运算符和位运算符。
需要说明的是：MySQL中的select语句具有输出功能，能够显示函数和表达式的值。
1、算术运算符
算术运算符是MySQL中最常用的一类运算符。MySQL支持的算术运算符包括：加、减、乘、除、求余。下面列出算术运算符的符号和作用，如表2-8所示。
| 符号 | 作用 |
|---|---|
| + | 加法运算 |
| - | 减法运算 |
| * | 乘法运算 |
| / | 除法运算 |
| % | 求余运算 |
| div | 除法运算，返回商。同“/” |
| mod | 求余运算，返回余数。同“%” |
说明：
加（+）、减（-）和乘（*）可以同时运算多个操作数。除号（/）和求余运算符（%）也可以同时计算多个操作数，但是这 两个符号计算多个操作数不太好。
div()和mod()这两个运算符只有两个参数。进行除法和求余的运算时，除以零的除法是不允许的，MySQL会返回null。
【实例】使用算术运算符进行加、减、乘、除、求余等运算。
mysql> select 3+2,1.5*3,3/5,100-23.5,5%3;
+-----+-------+--------+-------------+------+
| 3+2 | 1.5*3 | 3/5 | 100-23.5 | 5%3 |
+-----+-------+--------+----------+-----------+
| 5 | 4.5 | 0.6000 | 76.5 | 2 |
+-----+-------+--------+----------+--------+
1 row in set (0.01 sec)
2、比较运算符
比较运算符是查询数据时最常用的一类运算符。
select语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较运算符的符号、名称和应用示例。
| 运算符 | 作用 | 示例 |
|---|---|---|
| = | 等于 | Id=5 |
| > | 大于 | Id>5 |
| < | 小于 | Id<5 |
| => | 大于等于 | Id=>5 |
| <= | 小于等于 | Id<=5 |
| !=或<> | 不等于 | Id!=5 |
| Is null | 空 | Idis null |
| 运算符 | 作用 | 示 例 |
|---|---|---|
| Is not null | 非空 | Id is not null |
| Between | 区间比较 | Id between1 and 15 |
| In | 属于 | Id in (3,4,5) |
| Not in | 不属于 | Name not in (shi,li) |
| Like | 模式匹配 | Name like (‘shi%’) |
| Not like | 模式匹配 | Name not like (‘shi%’) |
| Regexp | 常规表达式 | Name正则表达式 |
下面对几种较常用的比较运算符进行详解。
运算符“=”。如果相等，返回1,否则返回0。
运算符“<>”和“!=”.“<>”和“!=”用来判断数字、字符串、表达式等是否不相等。如果不相等，则返回1；否则，返回0。
运算符“>”。如果大于，返回1；否则，返回0。同样空值（null）不能使用“>”来判断。
<”运算符、“<=”运算符和“>=”运算符都与“>”运算符使用方法基本相同，这里不再赘述。
【实例】使用比较运算符进行判断运算。
mysql> select 'A'>'B',1+1=2, 'X'<'x', 7<>7, 'a'<= 'a';
+---------+-------+---------+------+-----------+
| 'A'>'B' | 1+1=2 | 'X'<'x' | 7<>7 | 'a'<= 'a' |
+---------+-------+---------+------+-----------+
| 0 | 1 | 0 | 0 | 1 |
+---------+-------+---------+------+-----------+
1 row in set (0.02 sec)
运算符“is null”。“is null”用来判断操作数是否为空值（null）。操作数为null时，结果返回1；否则，返回0。is not null刚好与is null相反。
【实例】 运用is null、is not null运算符的用法。
mysql> select null is not null,17.3 is null, 11.7 is not null;
+-------------------+--------------+------------------+
| NULL IS NOT NULL | 17.3 IS NULL | 11.7 IS NOT NULL |
+-------------------+--------------+------------------+
| 0 | 0 | 1 |
+-------------------+--------------+------------------+
1 row in set (0.00 sec)
运算符“between and”。“between and”用于判断数据是否在某个取值范围内。也可以添加not运算符对一个between运算进行取反。其表达式如下：
x1 between m and n
如果x1大于等于m，且小于等于n，结果将返回1，否则将返回0。
【实例】运用“between and”运算符判断一个数是否在某 个范围。
mysql> select 11.7 not between 0 and 10, 51 between 0 and 70;
+-----------------------------+----------------------+
| 11.7 NOT between 0 AND 10 | 51 between 0 AND 70 |
+-----------------------------+----------------------+
| 1 | 1 |
+-----------------------------+----------------------+
1 row in set (0.00 sec)
运算符“in”。“in”用于判断数据是否存在于某个集合中。其表达式如下：
x1 in（值1，值2，…，值n）
如果x1等于值1到值n中的任何一个值，结果将返回1。如果不是，结果将返回0。
【实例】 运用“in”运算符判断某值是否在指定的范围内
mysql> select 7 in(1,2,5,6,7,8,9), 3 not in (1,10);
+---------------------+-----------------+
| 7 IN(1,2,5,6,7,8,9) | 3 NOT IN (1,10) |
+---------------------+-----------------+
| 1 | 1 |
+---------------------+-----------------+
1 row in set (0.00 sec)
运算符“like”。“like”用来匹配字符串。其中，“%”匹配任意个字符，“_”匹配一个字符。其表达式如下：
x1 like s1
如果x1与字符串s1匹配，结果将返回1。否则返回0。
【实例】使用like运算符，判断某字符串是否与指定的字符串匹配。
mysql> select 'MySQL' like 'MY%', 'APPLE' like 'A_';
+--------------------+-------------------------------+
| 'MySQL' like 'MY%' | 'APPLE' like 'A_' |
+--------------------+-------------------------------+
| 1 | 0 |
+--------------------+-------------------------------+
1 row in set (0.00 sec)
运算符regexp。
regexp同样用于匹配字符串，但其使用的是正则表达式进行匹配。使用regexp运算符匹配字符串，其使用方法非常简单。regexp运算符经常与“”、“$”和“.”一起使用。“”用来匹配字符串的开始部分；“$”用来匹配字符串的结尾部分；“.”用来代表字符串中的一个字符。其表达式格式如下：
x1 regexp '匹配方式'
如果x1满足匹配方式，结果将返回1；否则将返回0。
regexp运算符一般用来与表中的字段值匹配，判定该值是否以指定字符开头、中间的一个字符或结尾，同时判定是否包含指定的字符串。在以后的相关章节中介绍实例。
3、逻辑运算符
逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回1。如果表达式是假，结果返回0。逻辑运算符又称为布尔运算符。
MySQL中支持4种逻辑运算符，分别是与、或、非和异或。下面是4种逻辑运算符的符号及作用。
| 逻辑运算符 | 作用 |
|---|---|
| && 或 and | 与 |
| || 或 or | 或 |
| ! 或 not | 非 |
| xor | 异或 |
与运算
“&&”或者“and”是与运算的两种表达方式。如果所有数据不为0且不为空值（null）时，结果返回1；如果存在任何一个数据为0时，结果返回0；如果存在一个数据为null且没有数据为0时，结果返回null。与运算符支持多个数据同时进行运算。
或运算
“||”或者“or”表示或运算。所有数据中存在任何一个数据不为非0的数字时，结果返回1；如果数据中不包含非0的数字，但包含null时，结果返回null；如果操作数中只有0时，结果返回0。或运算符“||”也可以同时操作多个数据。
非运算
“！”或者not表示非运算。通过非运算，将返回与操作数据相反的结果。如果操作数据是非0的数字，结果返回0；如果操作数据是0，结果返回1；如果操作数据是null，结果返回null。
【实例】 逻辑运算符and、or、not示例。
mysql>select not('A'='B'),('c' ='C')and('c'<'D')or(1=2);
+--------------+----------------------------------+
| NOT('A'='B') | ('c' ='C') AND ('c'<'D') OR(1=2) |
+--------------+----------------------------------+
| 1 | 1 |
+--------------+----------------------------------+
1 row in set (0.00 sec)
异或运算
xor表示异或运算。只要其中任何一个操作数据为null时，结果返回null；如果
【实例】 逻辑运算符&&、xor示例。
mysql> select ('c'='C') && (1=2),('A'='a')xor(1+1=3);
+--------------------+---------------------+
| ('c'='C') && (1=2) | ('A'='a')XOR(1+1=3) |
+--------------------+---------------------+
| 0 | 1 |
+--------------------+---------------------+
1 row in set (0.02 sec)
4、位运算符
位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。
MySQL中支持6种位运算符。分别是：按位与、按位或、按位取反、按位异或、按位左移和按位右移。6种位运算符的符号及作用如表2-11所示。
| 符号 | 作用 |
|---|---|
| & | 按位与。进行该运算时，数据库系统会先将十进制的数转换为二进制的数。然后对应操作数的每个二进制位上进行与运算。1和1相与得1，与0相与得0。运算完成后再将二进制数变回十进制数 |
| | | 按位或。将操作数化为二进制数后，每位都进行或运算。1和任何数进行或运算的结果都是1，0与0或运算结果为0 |
| ~ | 按位取反。将操作数化为二进制数后，每位都进行取反运算。1取反后变成0，0取反后变成1 |
| ^ | 按位异或。将操作数化为二进制数后，每位都进行异或运算。相同的数异或之后结果是0，不同的数异或之后结果为1 |
| << | 按位左移。“m<<n”表示m的二进制数向左移n位，右边补上n个0。例如，二进制数001左移1位后将变成0010 |
| >> | 按位右移。“m>>n”表示m的二进制数向右移n位，左边补上n个0。例如，二进制数011右移1位后变成001，最后一个1直接被移出 |
位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，然后进行位运算。再将计算结果从二进制数变回十进制数。
【实例】 位运算符示例。
mysql> select 3&2,2|3,100>>5, ~1,6^4;
+-----+-----+--------+----------------------+-----+
| 3&2 | 2|3 | 100>>5 | ~1 | 6^4 |
+-----+-----+--------+----------------------+-----+
| 2 | 3 | 3 | 18446744073709551614 | 2 |
+-----+-----+--------+----------------------+-----+
1 row in set (0.00 sec)
5、表达式和运算符的优先级
表达式
在SQL语言中，表达式就是常量、变量、列名、复杂计算、运算符和函数的组合。一个表达式通常都有返回值。与常量和变量一样，表达式的值也具有某种数据类型。根据表达式的值的类型，表达式可分为字符型表达式、数值型表达式和日期型表达式。
运算符的优先级
当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序。在一个表达式中按先高（优先级数字小）后低（优先级数字大）的顺序进行运算。运算符优先级如表2-12所示。按照从高到低，从左到右的级别进行运算操作。如果优先级相同，则表达式左边的运算符先运算。
| 优先级 | 运算符 |
|---|---|
| 1 | ! |
| 2 | ~ |
| 3 | ^ |
| 4 | *,/,div,%,mod |
| 5 | +,- |
| 6 | >>,<< |
| 7 | & |
| 8 | | |
| 9 | =,<=>,<,<=,>,>=,!=,<>,in,is,null,like,regexp |
| 10 | between and,case,when,then,else |
| 11 | not |
| 12 | &&,and |
| 13 | |
| 14 | := （赋值号） |
2.9 MySQL常用函数
MySQL数据库中提供了很丰富的函数。这些内部函数可以帮助用户更加方便地处理表中的数据。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数和格式化函数等。
select语句及其条件表达式都可以使用这些函数。同时，insert 、update和delete语句及其条件表达式也可以使用这些函数。
1、数学函数
数学函数是MySQL中常用的一类函数。主要用于处理数字，包括整型、浮点数等。数学函数包括绝对值函数、正弦函数、余弦函数、获取随机数的函数等。
| 函 数 | 功能描述 |
|---|---|
| abs | 返回表达式的绝对值 |
| acos | 反余弦函数,返回以弧度表示的角度值 |
| asin | 反正弦函数, 返回以弧度表示的角度值 |
| atan | 反正切函数, 返回以弧度表示的角度值 |
| celling | 返回大于或等于指定数值表达式的最小整数 |
| cos | 返回以以弧度为单位的角度的余弦值 |
| degree | 弧度值转换为角度值 |
| exp | 返回给定表达式为指数的e值 |
| floor | 返回小于或等于指定数值表达式的最大整数 |
| greatest | 获得一组数中的最大值 |
| Least | 获得一组数中的最小值 |
| log | 返回给定表达式的自然对数 |
| log10 | 返回给定表达式的以10 为底的对数 |
| PI | 常量，圆周率 |
| pow | 返回给定表达式的指定次方的值 |
| radians | 角度值转换为弧度值 |
| rand | 返回0~1之间的随机float数 |
| round | 返回指定小数的位数的表达式的值 |
| sign | 返回某个数的符号 |
| sin | 返回以以弧度为单位的角度的正弦值 |
| sqrt | 返回给定表达式的平方根 |
| tan | 返回以以弧度为单位的角度的正切值 |
【实例】 floor()、celling()和log(5)函数示例。
mysql>select floor(3.67),celling(4.71), log(5);
+-------------+---------------+--------------------+
| floor(3.67) | celling(4.71) | log(5) |
+-------------+---------------+--------------------+
| 3 | 5 | 1.6094379124341003 |
+-------------+---------------+--------------------+
1 row in set (0.05 sec)
【实例】 利用取随机数rand()和四舍五入round()输出6090和2565之间的任意整数。
mysql>select 60+ round(30*rand(),0) , 25+ round(40*rand(),0);
+------------------------+------------------------+
| 60+ round(30*rand(),0) | 25+ round(40*rand(),0) |
+------------------------+------------------------+
| 76 | 39 |
+------------------------+------------------------+
1 row in set (0.00 sec)
（1）rand()返回的数是完全随机的，而rand(x)函数的x相同时，它被用作种子值，返回的值是相同的。
（2）四舍五入round(x)返回离x最近的整数，也就是对x进行四舍五入处理。round(x,y)返回x保留到小数点后y位的值，在截取时进行四舍五入处理。
2、字符串函数
字符串函数主要用于处理字符串数据和表达式，MySQL中的字符串函数包括计算字符串长度函数、合并函数、替换函数、比较函数和查找字符串位置函数等。
| 函数名称 | 功能描述 |
|---|---|
| char_length | 返回字符串中字符的个数。 |
| concat | 返回连接参数产生的字符串 |
| left | 返回从字符串左边开始指定个数的字符 |
| length | 返回给定字符串字节长度 |
| lower | 将大写字符数据转换为小写字符数据后返回字符表达式 |
| ltrim | 删除起始空格后返回字符表达式 |
| replace | 用第3个表达式替换第一个字符串表达式中，出现的所有第2个给定字符串表达式 |
| repeat | 以指定的次数重复字符表达式 |
| reverse | 返回字符表达式的反转 |
| right | 返回从字符串右边开始指定个数的字符 |
| rtrim | 截断所有尾随空格后返回一个字符串 |
| space | 返回由重复的空格组成的字符串。 |
| substring | 求子串函数 |
| upper | 返回将小写字符数据转换为大写的字符表达式 |
【实例】 利用concat()函数连接字符串。
mysql> select concat('MY',' SQL','5.7'),concat('ABC',null,'DEF');
+---------------------------+---------------------------+
| concat('MY',' SQL','5.7') | concat('ABC', null,'DEF') |
+---------------------------+---------------------------+
| MYSQL5.7 | null |
+---------------------------+---------------------------+
1 row in set (0.04 sec)
说明：concat()函数返回来自于参数连接的字符串。
如果任何参数是null，返回null。
可以有超过2个的参数。数字参数会被变换为等价的字符串形式。
【实例】 利用substring()函数返回指定字符串，并利用reverse()逆序输出。
mysql> select substring('ABCDEFGH',2,6),
-> reverse(substring('ABCDEFGH',2,6));
+---------------------------+-----------------------------------+
| substring('ABCDEFGH',2,6) | reverse(substring('ABCDEFGH',2,6))|
+---------------------------+-----------------------------------+
| BCDEFG | GFEDCB |
+---------------------------+-----------------------------------+
1 row in set (0.00 sec)
3、日期和时间函数
日期和时间函数主要用于处理表中的日期和时间数据。
日期和时间函数包括获取当前日期的函数、获取当前时间的函数、计算日期的函数和计算时间的函数等。
| 函数名 | 功能描述 |
|---|---|
| curdate | 获取当前系统的日期 |
| curtime | 获取当前系统的时间 |
| date_add | 可以对日期和时间进行加法运算 |
| date_sub | 可以对日期和时间进行减法运算 |
| datediff | 计算两个日期相隔的天数 |
| date_format | 用来格式化日期值 |
| day | 获取指定日期的日期整数 |
| dayname | 以英文名方式显示，返回指定日期是星期几，如Tuesday等 |
| dayofmonth | 返回指定日期在一个月中的序数 |
| dayofweek | 返回指定日期在一个星期中的序数 |
| dayofyear | 返回指定日期在一年中的序数 |
| hour | 返回指定时间的小时数 |
| minute | 返回指定时间的分钟数 |
| month | 获取指定日期的月份整数 |
| now/sysdayte | 返回当前日期和时间 |
| quarter | 获取指定日期的季度整数 |
| second | 返回指定时间的秒钟数 |
| time_format | 用来格式化时间值 |
| UTC_DATE | 用来输出世界标准时间的日期 |
| UTC_TIME | 用来输出世界标准时间 |
| year | 获取指定日期的年份整数 |
常用日期时间函数举例
【实例】 利用curdate()和curtime()函数返回当前日期和时间。
mysql>select curdate(),curtime();
【实例】返回指定日期在一年、一星期及一个月中的序数
mysql>select dayofyear(20170512),dayofmonth('2017-05-12'),
>dayofweek(now());
date_add()和date_sub()函数可以对日期和时间进行算术操作，它们分别用来增加和减少日期值。date_add()和date_sub()函数的语法格式为：
date_add | date_sub(date, interval int keyword)
Date表示日期和时间，interval关键字表示一个时间间隔。
| 关键字 | 间隔值的格式 |
|---|---|
| day | 日期 |
| day_hour | 日期∶小时 |
| day_minute | 日期∶小时∶分钟 |
| day_second | 日期∶小时∶分钟∶秒 |
| hour | 小时 |
| hour_minute | 小时∶分钟 |
| hour_ second | 小时∶分钟∶秒 |
| minute | 分钟 |
| minute_ second | 分钟∶秒 |
| month | 月 |
| second | 秒 |
| year | 年 |
| year_month | 年-月 |
【实例】计算指定时间的45分钟前是什么时间。
mysql>select date_sub(‘2017-10-1 10:10:10’, interval 45 minute);
【实例】7月15日放假，现在离放假还有多少天。
mysql>select datediff(‘2017-7-15’,now());
日期和时间格式化的函数。
date_format()和time_format()函数可以用来格式化日期和时间值。语法格式如下。
date_format/ time_format(date | time, fmt)
其中，date和time是需要格式化的日期和时间值，fmt是日期和时间值格式化的形式， 表2-17列出了MySQL中的日期/时间格式化代码。
| 关键字 | 间隔值的格式 |
|---|---|
| %a | 缩写的星期名（Sun，Mon…） |
| %b | 缩写的月份名（Jan，Feb…） |
| %d | 月份中的天数 |
| %H | 小时（01，02…） |
| %I | 分钟（00，01…） |
| %j | 一年中的天数（001，002…） |
| %m | 月份，2位（00,01…） |
| %M | 长型月份的名字（January，February） |
| %p | AM或PM |
| %r | 时间，12小时的格式 |
| %S | 秒（00，01） |
| %T | 时间，24小时的格式 |
| %w | 一周中的天数（0，1） |
| %W | 长型星期的名字（Sunday，Monday…） |
| %Y | 年份，4位 |
【实例】按照指定格式码输出日期。
mysql> select date_format(now(),'%W,%d,%m, %Y , %r, %p');
+--------------------------------------------+
| date_format(now(),'%W,%d,%m, %Y , %r, %p') |
+--------------------------------------------+
| Monday,24,04, 2017 , 08:53:25 AM, AM |
+--------------------------------------------+
1 row in set (0.03 sec)
4、聚合函数
聚合函数也叫作分组统计函数。这些函数的主要功能如表2-18所示。常用语对聚合在组内的数据表行进行计算。
| 函数 | 功 能 描 述 |
|---|---|
| avg | 返回组中数据的平均值,忽略null值 |
| count | 返回组中项目的数量 |
| max | 返回多个数据比较的最大值, 忽略null值 |
| min | 返回多个数据比较的最小值, 忽略null值 |
| sum | 返回组中数据的和, 忽略null值 |
5、其他函数
系统信息函数
系统信息函数用来查询MySQL数据库的系统信息。例如，查询数据库的版本，查询数据库的当前用户等。
| 函 数 | 功 能 |
|---|---|
| database() | 返回当前数据库名 |
| benchmark(n，expr) | 将表达式expr重复运行n次 |
| charset(str) | 返回字符串str的字符集 |
| Connection_id() | 返回当前客户连接服务器的次数 |
| found_rows() | 将最后一个MySQL>select查询（没有以limit语句进行限制）返回的记录行数返回 |
| get_lock(str，dur) | 获得一个由字符串str命名的并且有dur秒延时的锁定 |
| Is_free_lock(str) | 检查以str命名的锁定是否释放 |
| Last_insert_id() | 返回由系统自动产生的最后一个autoincrement id的值 |
| Master_pos_wait(log，pos，dur) | 锁定主服务器dur秒直到从服务器与主服务器的日志log指定的位置pos同步 |
| Release_lock(str) | 释放由字符串str命名的锁定 |
| user()或system_user() | 返回当前登录用户名 |
| version() | 返回MySQL服务器的版本 |
【实例】返回MySQL服务器的版本、当前数据库名和当前用户名信息，并查看当前用户连接MySQL服务器的次数。
mysql> select version(),database(),user(),connection_id();
加密函数
password(str)函数可以对字符串str进行加密。一般情况下，password(str)函数主要是用来给用户的密码加密的。
【实例】使用password(str)函数对字符串“student1357”加密。
mysql> select password('student1357');
+-------------------------------------------+
| password(' student1357') |
+-------------------------------------------+
| *839D903605F413D56D2A0635F7A10D019E48785E |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
例如，可以修改学生Li Ping的密码为student1357，并加密密码：
mysql>set password FOR 'Li Ping'@'localhost' = password('student1357');
格式化函数
format()函数把数值格式化为以逗号分隔的数字序列。format()的第一个参数x是被格式化的数据，第二个参数y是结果的小数位数。
format()函数语法格式为：
format(x, y)
【实例】利用格式函数format()码处理数据。
mysql> select format(2/3,2), format(123456.78,0);
用户根据工作需要，可以创建用户定义函数，还可以利用流程控制语句编写较为实用的程序，以提高程序开发和运行的质量。
