第1章 初识MySQL

1.1 数据库基础

1.1.1 什么是数据库

1.1.2 表

1.1.3 数据类型

1.1.4 主键

1.2 数据库技术构成.

1.2.1 数据库系统

1.2.2 SQL 语言

1.2.3数据库访问接口

1.3 什么是 MySQL

1-3.1 客户机-服务器软件

1.3.2 MySQL

1.3.3 MySQL 的优势

1.3.4 MySQL 5.7 的新功能

1.4 MySQL工具

1.4.1 MySQL命令行实用程序

1.4.2 MySQL Workbench

1.5 如何学习MySQL

第2章 MySQL的安装与配置

第3章 数据库的基本操作

3.1 创建数据库

3.2 删除数据库

3.3 数据库存储引擎

3.3.1 MySQL存储引擎简介

3.3.2 InnoDB存储引擎

3.3.3 MylSAM存储引擎

3.3.4 MEMORY存储引擎

3.3.5存储引擎的选择

3.4 综合案例——数据库的创建和删除

3.5 专家解惑

3.6 经典习题

第4章 数据表的基本操作

4.1创建数据表

4.1.1 创建表的语法形式

4.1.2 使用主键约束

4.1.3 使用外键约束

4.1.4 使用非空约束

4.1.5 使用唯一性约束

4.1.6 使用默认约束

4.1.7 设置表的属性值自动增加

4.2 查看数据表结构

4.2.1 查看表基本结构语句DESCRIBE

4.2.2 查看表详细结构语句SHOW CREATE TABLE

4.3 修改数据表

4.3.1 修改表名

4.3.2 修改字段的数据类型

4.3.3 修改字段名

4.3.4 添加字段

4.3.5 删除字段

4.3.6 修改字段的排列位置

4.3.7 更改表的存储引擎

4.3.8 删除表的外键约束

4.4 删除数据表

4.4.1 删除没有被关联的表

4.4.2 删除被其他表关联的主表

4.5 综合案例——数据表的基本操作

4.6 专家解惑

4.7 经典习题

第5章 数据类型和运算符

5.1 MySQL数据类型介绍

5.1.1 整数类型

5.1.2 浮点数类型和定点数类型

5.1.3 日期与时间类型

5.1.4 文本字符串类型

5.1.5 二进制字符串类型

5.2 如何选择数据类型

5.3 常见运算符介绍

5.3.1 运算符概述

5.3.2 算术运算符

5.3.3 比较运算符

5.3.4 逻辑运算符

5.3.5 位运算符

5.3.6 运算符的优先级

5.4 综合案例——运算符的使用

5.5 专家解惑

5.6 经典习题

第1章 初识MySQL

1.1 数据库基础

​ 数据库由一批数据构成有序的集合,这些数据被存放在结构化的数据表里.数据表之间相互关联,反映了客观事物间的本质联系.数据库系统提供对数据的安全控制和完整性控制.本节将介绍数据库中的一些基本概念,包括:数据库的定义、数据表的定义和数据类型等.

1.1.1什么是数据库

​ 数据库的概念诞生于60年前,随着信息技术和市场的快速发展,数据库技术层出不穷,随着应用的拓展和深入,数据库的数量和规模越来越大,其诞生和发展给计算机信息管理带来了一场巨大的革命.
数据库的发展大致划分为如下几个阶段:人工管理阶段、文件系统阶段、数据库系统阶段、高级数据库阶段.其种类大概有3种:层次式数据库、网络式数据库和关系式数据库.不同种类的数据库按不同的数据结构来联系和组织.
​ 对于数据库的概念,没有一个完全固定的定义,随着数据库历史的发展,定义的内容也有很大的差异,其中一种比较普遍的观点认为,数据库(DataBase,DB)是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合.它是一个按数据结构来存储和管理数据的计算机软件系统,即数据库包含两层含义:保管数据的"仓库",以及数据管理的方法和技术.
​ 数据库的特点包括:实现数据共享,减少数据冗余;采用特定的数据类型;具有较高的数据独立性;具有统一的数据控制功能.

1.1.2 表

​ 在关系数据库中,数据库表是一系列二维数组的集合,用来存储数据和操作数据的逻辑结构.它由纵向的列和横向的行组成,行被称为记录,是组织数据的单位;列被称为字段,每一列表示记录的一个属性,都有相应的描述信息,如数据类型、数据宽度等.

1.1.3 数据类型

​ 数据类型决定了数据在计算机中的存储格式,代表不同的信息类型.常用的数据类型有:整数数据类型、浮点数数据类型、精确小数类型、二进制数据类型、日期/时间数据类型、字符串数据类型.

1.1.4 主键

​ 主键(Primary Key)又称主码,用于唯一地标识表中的每一条记录.可以定义表中的一列或多列为主键,主键列上不能有两行相同的值,也不能为空值.

1.2数据库技术构成

​ 数据库系统由硬件部分和软件部分共同构成,硬件主要用于存储数据库中的数据,包括计算机、存储设备等.软件部分则主要包括DBMS、支持DBMS运行的操作系统,以及支持多种语言进行应用开发的访问技术等.本节将介绍数据库的技术构成.

1.2.1数据库系统

数据库系统有3个主要的组成部分.

• 数据库:用于存储数据的地方.
• 数据库管理系统:用于管理数据库的软件.
• 数据库应用程序:为了提高数据库系统的处理能力所使用的管理数据库的软件补充.

​ 数据库提供了一个存储空间用以存储各种数据,可以将数据库视为一个存储数据的容器.一个数据库可能包含许多文件,一个数据库系统中通常包含许多数据库.
​ 数据库管理系统(DataBase Management System,DBMS)是用户创建、管理和维护数据库时所使用的软件,位于用户与操作系统之间,对数据库进行统一管理.DBMS能定义数据存储结构,提供数据的操作机制,维护数据库的安全性、完整性和可靠性.
​ 虽然已经有了DBMS,但是在很多情况下,DBMS无法满足对数据管理的要求.数据库应用程序(DataBase Application)的使用可以满足对数据管理的更高要求,还可以使数据管理过程更加直观和友好.数据库应用程序负责与DBMS进行通信、访问和管理DBMS中存储的数据,允许用户插入、修改、删除DB中的数据.

1.2.2 SQL语言

​ 对数据库进行查询和修改操作的语言叫做SQL.SQL的含义是结构化查询语言(Structured Query Language).SQL有许多不同的类型,有3个主要的标准:ANSI(美国国家标准机构)SQL,对 ANSI SQL修改后在1992年采纳的标准,称为SQL-92或SQL2.最近的SQL-99标准,从SQL2扩充而来并增加了对象关系特征和许多其他新功能.其次,各大数据库厂商提供不同版本的SQL,这些版本的SQL不但能包括原始的ANSI标准,而且在很大程度上支持SQL-92标准.

SQL包含以下4个部分:
-(1)数据定义语言(DDL):DROP、CREATE ALTER等语句
-(2)数据操作语言(DML):INSERT UPDATE(修改)、DELETE(删除)语句.
-(3)数据查询语言(DQL):SELECT语句.
-(4)数据控制语言(DCL):GRANT REVOKE、COMMIT、ROLLBACK等语句.

1.2.3 数据库访问接口

​ 不同的程序设计语言会有各自不同的数据库访问接口,程序语言通过这些接口,执行SQL语句,进行数据库管理.主要的数据库访问接口有:

1.ODBC

​ Open Database Connectivity(ODBC,开放数据库互连)技术为访问不同的SQL数据库提供了一个共同的接口.ODBC使用SQL作为访问数据的标准.这一接口提供了最大限度的互操作性:一个应用程序可以通过共同的一组代码访问不同的SQL数据库管理系统(DBMS).
​ 一个基于ODBC的应用程序对数据库的操作不依赖任何DBMS,不直接与DBMS打交道,所有的数据库操作由对应的DBMS的ODBC驱动程序完成.也就是说,不论是Access、MySQL还是Oracle数据库,均可用ODBCAPI进行访问.由此可见,ODBC的最大优点是能以统一的方式处理所有的数据库.

2.JDBC

​ Java Data Base Connectivity(JDBC,Java 数据库连接)用于Java应用程序连接数据库的标准方法,是一种用于执行SOL语句的JavaAPI,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成.

3.ADO.NET

​ ADO.NET是微软在.NET框架下开发设计的一组用于和数据源进行交互的面向对象类库.ADO.NET提供了对关系数据、XML和应用程序数据的访问,允许和不同类型的数据源以及数据库进行交互.

4.PDO

​ PDO(PHP Data Object)为PHP访问数据库定义了一个轻量级的、一致性的接口,它提供了一个数据访问抽象层,这样,无论使用什么数据库,都可以通过一致的函数执行查询和获取数据.PDO是PHP5新加入的一个重大功能.

5.总结

​ 针对不同的程序语言,MySQL提供了不同数据库的访问连接驱动,读者可以在下载页面(http:/dev.MySQL.com/downloads/)下载相关驱动.

1.3 什么是MySQL

​ MySQL是一个小型关系数据库管理系统,与其他大型数据库管理系统(例如Oracle、DB2、SQL Server等)相比,MySQL规模小、功能有限,但是它体积小、速度快、成本低,且它提供的功能对稍微复杂的应用来说已经够用,这些特性使得MySQL成为世界上最受欢迎的开放源代码数据库.本节将介绍MySQL的特点.

1.3.1客户机-服务器软件

​ 主从式架构(Client-server model)或客户端-服务器(Client/Server)结构简称C/S结构,是一种网络架构,通常在该网络架构下软件分为客户端(Client)和服务器(Server).
​ 服务器是整个应用系统资源的存储与管理中心,多个客户端则各自处理相应的功能,共同实现完整的应用.在客户/服务器结构中,客户端用户的请求被传送到数据库服务器,数据库服务器进行处理后,将结果返回给用户,从而减少了网络数据传输量.
​ 用户使用应用程序时,首先启动客户端,通过有关命令告知服务器进行连接以完成各种操作,而服务器则按照此请示提供相应的服务.每一个客户端软件的实例都可以向一个服务器或应用程序服务器发出请求.
​ 这种系统的特点就是,客户端和服务器程序不在同一台计算机上运行,这些客户端和服务器程序通常归属不同的计算机.
​ 主从式架构通过不同的途径应用于很多不同类型的应用程序,比如,现在人们最熟悉的在因特网上使用的网页.例如,当顾客想要在当当网站上买书的时候,电脑和网页浏览器就被当作一个客户端,同时,组成当当网的电脑、数据库和应用程序就被当作服务器.当顾客的网页浏览器向当当网请求搜寻数据库相关的图书时,当当网服务器从当当网的数据库中找出所有该类型的图书信息,结合成一个网页,再发送回顾客的浏览器.服务器端一般使用高性能的计算机,并配合使用不同类型的数据库,比如Oracle、Sybase或者是MySQL等;客户端需要安装专门的软件,比如专门开发的客户端工具浏览器等.

1.3.2MySQL版本

针对不同用户,MySQL分为两个不同的版本:

• MySQL Community Server(社区版):该版本完全免费,但是官方不提供技术支持.
• MySQL Enterprise Server(企业版服务器):它能够以很高性价比为企业提供数据仓库应用,支持ACID事物处理,提供完整的提交、回滚、崩溃恢复和行级锁定功能.但是该版本需付费使用,官方提供电话技术支持.

MySQL的命名机制由3个数字和1个后缀组成,例如:MySQL-5.7.10
-(1)第1个数字(5)是主版本号,描述了文件格式,所有版本5的发行版都有相同的文
-(2)第2个数字(7)是发行级别,主版本号和发行级别组合在一起便构成了发行
-(3)第3个数字(10)是在此发行系列的版本号,随每次新分发版本递增.通常选择已

在 MySQL开发过程中,同时存在多个发布系列,每个发布处在成熟度的不同阶段.

1.3.3 MySQL的优势

MySQL的主要优势如下:

• 速度:运行速度快.
• 价格:MySQL对多数个人来说是免费的.
• 容易使用:与其他大型数据库的设置和管理相比,其复杂程度较低,易于学习.
• 可移植性:能够工作在众多不同的系统平台上,例如:Windows、Linux、Unix、Mac OS等.
• 丰富的接口:提供了用于C、C++、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等语言的API.
• 支持查询语言:MySQL可以利用标准SQL语法和支持ODBC(开放式数据库连接)的应用程序.
• 安全性和连接性:十分灵活和安全的权限和密码系统,允许基于主机的验证.连接到服务器时,所有的密码传输均采用加密形式,从而保证了密码安全.并且由于MySQL是网络化的,因此可以在因特网上的任何地方访问,提高数据共享的效率.

1.3.4MySQL5.7的新功能

和MySQL5.6相比,MySQL5.7的新功能主要包括以下几个方面.

• 1.支持JSON
  JSON(Java Script Object Notation的缩写)是一种存储信息的格式,可以很好地替代XML.从MySQL5.7.8版本开始,MySQL将支持JSON,而在此版本之前,只能通过strings之类的通用形式来存储JSON文件,这样做的缺陷很明显,就是必须要自行确认和解析数据、解决更新中的困难、在执行插入操作时忍受较慢的速度.
• 2.性能和可扩展性
  改进InnoDB的可扩展性和临时表的性能,从而实现更快的网络和大数据加载等操作.
• 3.改进复制以提高可用性的性能
  改进复制包括多源复制、多从线程增强、在线GTIDs和增强的半同步复制.
• 4.性能模式提供更好的视角
  增加了许多新的监控功能,以减少空间和过载,使用新的SYS模式显著提高易用性.
• 5.安全
  以安全第一为宗旨,提供了很多新的功能,从而保证数据库的安全.
• 6.优化
  重写了大部分解析器、优化器和成本模型,这提高了可维护性、可扩展性和性能.
• 7.GIS
  MySQL5.7全新的功能,包括InnoDB空间索引,使用Boost.Geometry,同时提高完整性和标准符合性.

1.4 MySQL 工具

​ MySQL数据库管理系统提供了许多命令行工具,这些工具可以用来管理MySQ务器、对数据库进行访问控制、管理MySQL用户以及数据库备份和恢复工具等.而且MySQL提供了图形化的管理工具,这使得对数据库的操作更加简单.本节将为读者介绍这些工具的作用.

1.4.1 MySQL命令行实用程序

MySQL服务器端实用工具程序如下:

• mysqld:SQL后台程序(即MySQL服务器进程).该程序必须运行之后,客户端才能通过连接服务器来访问数据库.
• mysqld safe:服务器启动脚本.在UNIX和NetWare 中推荐使用mysqld safe来启动mysqld 服务器.mysqld_safe增加了一些安全特性,例如当出现错误时重启服务器并向错误日志文件写入运行时间信息.
• mysql.server:服务器启动脚本.在UNIX中的MySQL分发版包括mysql.server脚本.该脚本用于使用包含为特定级别的、运行启动服务的脚本的、运行目录的系统.它调用mysqld_safe来启动MySQL服务器.
• mysql_multi:服务器启动脚本,可以启动或停止系统上安装的多个服务器.
• myisamchk:用来描述、检查、优化和维护MyISAM表的实用工具.
• mysqlbug:MySQL缺陷报告脚本.它可以用来向MySQL邮件系统发送缺陷报告.
• mysql_install_db:该脚本用默认权限创建MySQL授权表.通常只是在系统上首次安装MySQL时执行一次.

MySQL客户端实用工具程序如下:

• myisampack MyISAM表以产生更小的只读表的一个工具.
• mysql:交互式输入SQL语句或从文件以批处理模式执行它们的命令行工具.
• mysqlaccess:检查访问主机名、用户名和数据库组合的权限的脚本.
• MySQLadmin:执行管理操作的客户程序,例如创建或删除数据库,重载授权表, 将表刷新到硬盘上,以及重新打开日志文件.MySQLadmin还可以用来检索版本、进程,以及服务器的状态信息.
• mysqlbinlog:从二进制日志读取语句的工具.在二进制日志文件中包含执行过的语句,可用来帮助系统从崩溃中恢复.
• mysqlcheck:检查、修复、分析以及优化表的表维护客户程序.
• mysqldump MySQL数据库转储到一个文件(例如SQL语句或tab分隔符文本的客户程序).
• mysqlhotcopy:MyISAM或ISAM表的工具.
• mysqlimport:使用LOAD DATA INFILE将文本文件导入相关表的客户程序.
• mysqlshow:显示数据库、表、列以及索引相关信息的客户程序.
• perror:MySQL错误代码含义的工具.

1.4.2 MySQL workbench

1.5 如何学习MySQL

• 培养兴趣
• 夯实基础
• 及时学习新知识
• 多实践操作

第2章 MySQL的安装与配置

第3章 数据库的基本操作

3.1 创建数据库

show databases:查看当前所有的数据库
create database name:创建一个数据库
show create database name:查看创建数据库的定义

3.2 删除数据库

drop database name:删除数据库,不存在则报错
注意点:数据库删除之后所有的数据和表也会被删除,且不能被恢复.

3.3 数据库存储引擎

数据库存储引擎是数据库底层软件组件,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据操作.不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能.现在许多不同的数据库管理系统都支持多种不同的数据引擎. MySQL的核心就是存储引擎.

3.3.1 MySQL存储引擎简介

MySQL提供了多个不同的存储引擎,包括处理事务安全表的引擎和处理非事务安全表的引擎.在MySL中,不需要在整个服务器中使用同一种存储引擎,针对具体的要求,可以对每一个表使用不同的存储引擎.MySQL5.7支持的存储引擎有:InnoDB, MyISAM, Memory, Merge, Archive, Federated,CSV, BLACKHOLE等.可以使用 SHOW ENGINES语句查看系统所支持的引擎类型,结果如下.   

| Engine | Support | Comment | Transactions | XA | Savepoints |
|--------------------|---------+----------------------------------------------------------------|--------------|------|------------|
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine(anything you write to it disappears)| NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |

3.3.2 InnoDB存储引擎

InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键.MySQL5.5.5之后, InnoDB作为默认存储引擎, InnoDB主要特性有:

• InnoDB给 MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎.InnoDB锁定在行级并且也在 SELECT语句中提供一个类似 Oracle的非锁定读.这些功能增加了多用户部署和性能.在SQL查询中,可以自由地将 InnoDB类型的表与其他 MySQL的表的类型混合起来,甚至在同一个查询中也可以混合.
• InnoDB是为处理巨大数据量的最大性能设计.它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的.
• InnoDB存储引擎完全与 MySQL服务器整合, InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池. InnoDB将它的表和索引存在一个逻辑表空间中,表空间可以包含数个文件(或原始磁盘分区).这与MyISAM表不同,比如在 MyISAM表中每个表被存在分离的文件中.InnoDB表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统.
• InnoDB支持外键完整性约束( FOREIGN KEY).存储表中的数据时,每张表的存储都按主键顺序存放,如果没有显示在表定义时指定主键,InnoDB会为每一行生成一个6B的 ROWID,并以此作为主键.
• InnoDB被用在众多需要高性能的大型数据库站点上,InnoDB不创建目录,使用 InnoDB时, MySQL将在 MySQL数据目录下创建一个名为ibdatal的10MB大小的自动扩展数据文件,以及两个名为ib_logfile0和ib_logfile的5MB大小的日志文件.

3.3.3 MyISAM存储引擎

MyISAM基于ISAM的存储引擎,并对其进行扩展.它是在Web、数据存储和其他应用环境下最常使用的存储引擎之一. MyISAM拥有较高的插入、查询速度,但不支持事务.在MySQL5.5.5之前的版本中, MyISAM是默认存储引擎. MyISAM主要特性有:

• 大文件(达63位文件长度)在支持大文件的文件系统和操作系统上被支持.
• 当把删除、更新及插入操作混合使用的时候,动态尺寸的行产生更少碎片.这要通过合并相邻被删除的块,以及若下一个块被删除,就扩展到下一块来自动完成.
• 每个 MyISAM表最大索引数是64,这可以通过重新编译来改变.每个索引最大的列数是16个.
• 最大的键长度是1000B,这也可以通过编译来改变.对于键长度超过250B的情况,一个超过1024B的键将被用上.
• BLOB和TEXT列可以被索引.
• NULL值被允许在索引的列中.这个值占每个键的0~1个字节.
• 所有数字键值以高字节优先被存储以允许一个更高的索引压缩.
• 每表一个 AUTO INCREMENT列的内部处理. MyISAM为 INSERT和 UPDATE操作自动更新这一列.这使得 AUTO INCREMENT列更快(至少10%).在序列顶的值被删除 之后就不能再利用.
• 可以把数据文件和索引文件放在不同目录.
• 每个字符列可以有不同的字符集
• 有 VARCHAR的表可以固定或动态记录长度.
• VARCHAR和CHAR列可以多达64KB.

使用 MyISAM引擎创建数据库,将生产3个文件.文件的名字以表的名字开始,扩展名指出文件类型:fm文件存储表定义,数据文件的扩展名为MYD( MYData),索引文件的扩展名是.MYI(MYIndex)).

3.3.4 MEMORY 存储引擎

MEMORY存储引擎将表中的数据存储到内存中,为查询和引用其他表数据提供快速访问.MEMORY主要特性有:

• MEMORY表的每个表可以有多达32个索引,每个索引16列,以及500B的最大键长度.
• MEMORY存储引擎执行HASH和BTREE索引.
• 可以在一个MEMORY表中有非唯一键.
• MEMORY表使用一个固定的记录长度格式.
• MEMORY不支持BLOB或TEXT列.
• MEMORY支持AUTOINCREMENT列和对可包含NULL值的列的索引.
• MEMORY表在所有客户端之间共享(就像其他任何非TEMPORARY表).
• MEMORY表内容被存在内存中,内存是MEMORY表和服务器在查询处理时的空闲中创建的内部表共享.
• 当不再需要MEMORY表的内容时,要释放被MEMORY表使用的内存,应该执行DELETE FROM或TRUNCATETABLE,或者删除整个表(使用DROPTABLE).

3.3.5存储引擎的选择

不同存储引擎都有各自的特点,以适应不同的需求,如表所示.为了做出选择,首先需要考虑每一个存储引擎提供了哪些不同的功能.

存储引擎比较
|功能|MyISAM|Memory|InnoDB|Archive|
|---|---|---|---|---|---|
|存储限制|256TB|RAM|64TB|None|
|支持事务|No|No|Yes|No|
|支持全文索引|Yes|No|No|No|
|支持数索引|Yes|Yes|Yes|No|
|支持哈希索引|No|Yes|No|No|
|支持数据缓存|No|N/A|Yes|No|
|支持外键|No|No|Yes|No|

如果要提供提交、回滚和崩溃恢复能力的事务安全(ACID兼容)能力,并要求实现并发控制,InnoDB MyISAM引擎能提 供较高的处理效率;如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可 以选择将数据保存在内存中的 Memory MySQL中使用该引擎作为临时表,存放查询的 中间结果.如果只有 INSERT SELECT操作,可以选择 Archive引擎, Archive存储引擎支持高并发的插入操作,但是本身并不是事务安全的. Archive存储引擎非常适合存储归档数据, 如记录日志信息可以使用 Archive引擎.
使用哪一种引擎要根据需要灵活选择,一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求.使用合适的存储引擎将会提高整个数据库的性能.

3.4 综合案例----数据库的创建和删除

3.5 专家解惑

如何查看默认存储引擎？
show variables likes 'storage_engine';

第4章 数据表的基本操作

4.1 创建数据表

4.1.1 创建表

创建数据表的语句为 create table.语法规则如下:

create table <表名>
(
    字段名1,数据类型[列级别约束条件],默认值;
    字段名2,数据类型[列级别约束条件],默认值;
    ...
    [表级别约束条件]
);

使用CREATETABLE创建表时,必须指定以下信息:

• (1)要创建的表的名称,不区分大小写,不能使用SQL语言中的关键字,如DROP、ALTER、INSERT等.
• (2)数据表中每一个列(字段)的名称和数据类型,如果创建多个列,要用逗号隔开.

4.1.2使用主键约束

主键,又称主码,是表中一列或多列的组合.主键约束(Primary Key Constraint)要求主键列的数据唯一,并且不允许为空.主键能够唯一地标识表中的一条记录,可以结合外键来定 义不同数据表之间的关系,并且可以加快数据库查询的速度.主键和记录之间的关系如同身份 证和人之间的关系,它们之间是一一对应的.主键分为两种类型:单字段主键和多字段联合主键.

1.单字段主键
单字段主键由一个字段组成,SQL语句格式分为以下两种情况.
(1)在定义列的同时指定主键,语法规则如下:
字段名数据类型 PRIMARY KEY默认值
(2)在定义完所有列之后指定主键
CONSTRAINT<约束名>] PRIMARY KEY[字段名]

4.1.3使用外键约束

外键用来在两个表的数据之间建立链接,它可以是一列或者多列.一个表可以有一个或多个外键.外键对应的是参照完整性,一个表的外键可以为空值,若不为空值,则每一个外键值必须等于另一个表中主键的某个值.
外键:首先它是表中的一个字段,它可以不是本表的主键,但对应另外一个表的主键.外键主要作用是保证数据引用的完整性,定义外键后,不允许删除在另一个表中具有关联关系的行.外键的作用是保持数据的一致性、完整性.

主表(父表):对于两个具有关联关系的表而言,相关联字段中主键所在的那个表即是主表
从表(子表):对于两个具有关联关系的表而言,相关联字段中外键所在的那个表即是从表

创建外键的语法规则如下:

[ CONSTRAINT<外键名>1 FOREIGN KEY字段名1 [,字段名2,...]  
REFERENCES<主表名>主键列1 [,主键列2,...]

"外键名"为定义的外键约束的名称,一个表中不能有相同名称的外键:"字段名"表示字表需要添加外键约束的字段列;"主表名"即被字表外键所依赖的表的名称;"主键列"表示主表中定义的主键列,或者列组成.

4.1.4使用非空约束

非空约束(Not Null Constraint)指字段的值不能为空.对于使用了非空约束的字段,如果用户在添加数据时没有指定值,数据库系统会报错.
非空约束的语法规则如下:字段名数据类型 not nul11

4.1.5使用唯一性约束

唯一性约束( Unique Constraint)要求该列唯一,允许为空,但只能出现一个空值.唯一约束可以确保一列或者几列不出现重复值.
唯一性约束的语法规则如下:
(1)在定义完列之后直接指定唯一约束,语法规则如下:字段名数据类型 UNIQUE
(2)在定义完所有列之后指定唯一约束,语法规则如下:[CONSTRAINT UNIQUE(<字段名>)

UNIQUE PRIMARY KEY的区别:一个表中可以有多个字段声明为UNIQUE,但只能有一个PRIMARY KEY声明;声明为PRIMAY KEY的列不允许有空值但是声明为UNIQUE的字段允许空值(NULL)的存在.

4.1.6使用默认约束

默认约束( Default Constraint)指定某列的默认值.
默认约束的语法规则如下:字段名数据类型 DEFAULT默认值

4.1.7设置表的属性值自动增加

在数据库应用中,经常希望在每次插入新记录时,系统自动生成字段的主键值.可以通过为表主键添加AUTO_INCREMENT关键字来实现.默认的,在 MySQL中AUTO_INCREMENT的初始值是1,每新增一条记录,字段值自动加1.一个表只能有一个字段使用AUTO_INCREMENT约束,且该字段必须为主键的一部分.
AUTO_INCREMENT约束的字段可以是任何整数类型(TINYINT、SMALLIN、INT、BIGINT等).
设置表的属性值自动增加的语法规则如下: 字段名数据类型 AUTO INCREMENT

4.2 查看数据表结构

4.2.1 查看表基本结构语句DESCRIBE

DESCRIBE/DESC语句可以查看表的字段信息,其中包括:字段名、字段数据类型、是否为主键、是否有默认值等.
语法规则如下:DESCRIBE 表名; 或者简写为:DESC 表名;

4.2.2 查看表详细结构语句SHOW CREATE TABLE

SHOW CREATE TABLE语句可以用来显示创建表时的CREATE TABLE语句
语法格式如下:SHOW CREATE TABLE <表名> \G;

4.3修改数据表

修改表指的是修改数据库中已经存在的数据表的结构.MySQL使用ALTER TABLE语句修改表.
常用的修改表的操作有:修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等. 本节将对和修改表有关的操作进行讲解.

4.3.1修改表名

MySQL是通过ALTER TABLE语句来实现表名的修改的
语法规则如下:ALTER TABLE <旧表名〉RENAME [TO] <新表名>;
其中TO为可选参数,使用与否均不影响结果.

4.3.2修改字段的数据类型

修改字段的数据类型,就是把字段的数据类型转换成另一种数据类型.
语法规则如下:ALTER TABLE〈表名〉MODIFY <字段名〉<数据类型>
其中"表名"指要修改数据类型的字段所在表的名称,"字段名"指需要修改的字段, "数据类型"指修改后字段的新数据类型.

4.3.3修改字段名

语法规则如下:ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
其中,"旧字段名"指修改前的字段名;"新字段名"指修改后的字段名;"新数据类型"指修改后的数据类型,如果不需要修改字段的数据类型,可以将新数据类型设置成与原来一样即可,但数据类型不能为空.

CHANGE也可以只修改数据类型,实现和MODIFY同样的效果,方法是将SQL语句中的"新字段名"和"旧字段名"设置为相同的名称,只改变"数据类型".
由于不同类型的数据在机器中存储的方式及长度并不相同,修改数据类型可能会影响到数据表中已有的数据记录.因此,当数据库表中已经有数据时,不要轻易修改数据类型.

4.3.4添加字段

随着业务需求的变化,可能需要在已经存在的表中添加新的字段.一个完整字段包括字段名、数据类型、完整性约束.
添加字段的语法格式如下:ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件][FIRST | AFTER 已存在字段名];
新字段名为需要添加的字段的名称;"FIRST"为可选参数,其作用是将新添加的字段设置为表的第一个字段;"AFTER"为可选参数,其作用是将新添加的字段添加到指定的"已存在字段名"的后面.
"FIRST"或"AFTER已存在字段名"用于指定新增字段在表中的位置,如果SQL语句中没有这两个参数,则默认将新添加的字段设置为数据表的最后列.

4.3.5删除字段

删除字段是将数据表中的某个字段从表中移除
语法格式如下:ALTER TABLE <表名> DROP <字段名>;
"字段名"指需要从表中删除的字段的名称.

4.3.6修改字段的排列位置

对于一个数据表来说,在创建的时候,字段在表中的排列顺序就已经确定了.但表的结构并不是完全不可以改变的,可以通过ALTER TABLE来改变表中字段的相对位置.
语法格式如下:ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST | AFTER <字段2>;
"字段1"指要修改位置的字段,"数据类型"指"字段1"的数据类型,"FIRST"为可选参数,指将"字段1"修改为表的第一个字段,"AFTER字段2"指将"字段1"插入到"字段2"后面.

4.3.7更改表的存储引擎

通过前面章节的学习,知道存储引擎是MySQL中的数据存储在文件或者内存中时采用的不同技术实现 .可以根据自己的需要,选择不同的引擎,甚至可以为每一张表选择不同的存储引擎.
MySQL中主要存储引擎有:MylSAM、InnoDB、MEMORY(HEAP)、BDB、FEDERATED等.
可以使用SHOW ENGINES;语句查看系统支持的存储引擎.

下表列出了 5.5.13版本的 MySQL所支持的存储引擎.
MySQL支持的存储引擎

更改表的存储引擎的语法格式如下:ALTER TABLE <表名> ENGITNE=<更改后的存储引擎>;

4.3.8删除表的外键约束

对于数据库中定义的外键,如果不再需要,可以将其删除.外键一旦删除,就会解除主表和从表间的关联关系
删除外键的语法格式如下:ALTER TABLE <表名〉DROP FOREIGN KEY <外键约束名>
"外键约束名"指在定义表时CONSTRAINT关键字后面的参数

4.4删除数据表

删除数据表就是将数据库中已经存在的表从数据库中删除.注意,在删除表的同时,表的定义和表中所有的数据均会被删除.因此, 在进行删除操作前,最好对表中的数据做个备份,以免造成无法挽回的后果.本节将详细讲解数据库表的删除方法.

4.4.1删除没有被关联的表

在MySQL中,使用DROP TABLE可以一次删除一个或多个没有被其他表关联的数据表
语法格式如下:DROP TABLE [IF EXISTS] 表1,表2,...,表n;
其中"表n"指要删除的表的名称,后面可以同时删除多个表,只需将要删除的表名依次写在后面,相互之间用逗号隔开即可.如果要删除的数据表不存在,则MySQL会提示一条错误信息

4.4.2删除被其他表关联的主表

数据表之间存在外键关联的情况下,如果直接删除父表,结果会显示失败.原因是直接删除,将破坏表的参照完整性.如果必须要删除,可以先删除与它关联的子表,再删除父表,只是这样同时删除了两个表中的数据.但有的情况下可能要保留子表,这时如要单独删除父表,只需将关联的表的外键约束条件取消,然后就可以删除父表.

4.5 综合案例----数据表的基本操作

4.6 专家解惑

• 疑问1:表删除操作须谨慎
  表删除操作将把表的定义和表中的数据一起删除,并且MySQL在执行删除操作时,不会有任何的确认信息提示,因此执行删除操时,应当慎重.在删除表前,最好对表中的数据进行备份,这样当操作失误时,可以对数据进行恢复,以免造成无法挽回的后果.
  同样地,在使用ALTER TABLE进行表的基本修改操作时,在执行操作过程之前,也应该确保对数据进行完整的备份,因为数据库的改变是无法撤销的,如果添加了一个不需要的字段,可以将其删除;相同的,如果删除了一个需要的列,该列下面的所有数据都将会丢失'

• 疑问2:每一表中都要有一主键吗?
  并不是每一个表中都需要主键,如果多个表之间进行连接操作时,需要用到主键.因此并不需要为每个表建立主键,而且有些情况最好不使用主键.

• 疑问3:并不是每个表都可以任意选择存储引擎.
  外键约束(FOREIGN KEY)不能跨引擎使用.MySQL支持多种存储引擎,每一个表都可以指定一个不同的存储引擎,但是要注意:外键约束是用来保证数据的参照完整性,如果表之间需要关联外键,却指定了不同的存储引擎,这些表 之间是不能创建外键约束的.所以说,存储引擎的选择也不完全是随意的.

• 疑问4:带AUTOJNCREMENT约束的字段值是从1开始的吗?
  默认的,在MySQL中,AUTOJNCREMENT的初始值是1,每新增一条记录,字段值自动加设置自增属性(AUTO_INCREMENT)的时候,还可以指定第一条插入记录的自増字段的值,这样新插入的记录的自增字段值从初始值开始递增,如在tb_emp8中插入第一条记录, 同时指定id值为5,则以后插入的记录的id值就会从6开始往上增加.添加唯一性的主键约束时,往往需要设置字段自动增加属性.

第5章 数据类型与运算符

5.1 MySQL数据类型介绍

MySQL支持多种数据类型,主要有数值类型、日期/时间类型和字符串类型.

• 数值数据类型:包括整数类型 TINYINT. SMALLINT. MEDIUMINT、INT、BIGINT. 浮点小数数据类型FLOAT和DOUBLE,定点小数类型DECIMAL.
• 日期/时间类型:包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP.
• 字符串类型:包括 CHAR、VARCHAR, BINARY. VARBINARY, BLOB、TEXT、ENUM和SET等.字符串类型又分为文本字符串和二进制字符串.

5.1.1整数类型

数值型数据类型主要用来存储数字,MySQL提供了多种数值数据类型,不同的数据类型提供不同的取值范围,可以存储的值范围越大,其所需要的存储空间也会越大.
MySQL主要提供的整数类型有:TINYINT、SMALLINT, MEDIUMINT, INT(INTEGER)、BIGINT.整数类型的属性字段可以添加AUTO_INCREMENT自增约束条件.

从表中可以看到,不同类型整数存储所需的字节数是不同的,占用字节数最小的是 TINYINT类型,占用字节最大的是BIGINT类型,相应的占用字节越多的类型所能表示的数 值范围越大.

MySQL中的整数型数据类型
|类型名称|说明|存储需求|有符号|无符号|
|---|---|---|
|TINYINT|很小的整数|1个字节|-128_127|0255|
|SMALLINT|小的整数|2个字节|-32768_32767|065535|
|MEDIUMINT|中等大小的整数|3个字节|-8388608~8388607|0-16777215|
|INT(INTEGER)|普通大小的整数|4个字节|-2147483648~2147483647|0-4294967295|
|BIGINT|大整数|8个字节|-9223372036854775808_{9223372036854775807|0}18446744073709551615|

注意:显示宽度和数据类型的取值范围是无关的.显示宽度只是指明MySQL最大可能显示的数字个数,数值的位数小于指定的宽度时会由空格填充;如果插入了大于显示宽度的值,只要该值不超过该类型整数的取值范围,数值依然可以插入,而且能够显示出来.例如,假如向year字段插入一个数值19999,当使用SELECT查询该列值的时候,MySQL显示的将是完整的带有5位数字的19999,而不是4位数字的值.

其他整型数据类型也可以在定义表结构时指定所需要的显示宽度,如果不指定,则系统为每一种类型指定默认的宽度值.

不同的整数类型有不同的取值范围,并且需要不同的存储空间,因此,应该根据实际需要选择最合适的类型,这样有利于提高查询的效率和节省存储空间.

提示:显示宽度只用于显示,并不能限制取值范围和占用空间,如:INT(3)会占用4个字节的存储空间,并且允许的最大值也不会是999,而是INT整型所允许的最大值.

5.1.2浮点数类型和定点数类型

MySQL中使用浮点数和定点数来表示小数.浮点类型有两种:单精度浮点类型(FLOAT)和双精度浮点类型(DOUBLE).定点类型只有一种:DECIMAL.浮点类型和定点类型都可以用(M, N)来表示,其中M称为精度,表示总共的位数;N称为标度,是表示小数的位数.

MySQL中的小数类型

DECIMAL类型不同于FLOAT和DOUBLE, DECIMAL实际是以串存放的,DECIMAL可能的最大取值范围与DOUBLE一样,但是其有效的取值范围由M和D的值决定.如果改变M而固定D,则其取值范围将随M的变大而变大.从表可以看到,DECIMAL的存储空间并不是固定的,而由其精度值M决定,占用M+2个字节.

FLOAT类型的取值范围如下:

• 有符号的取值范围:-3.402823466E+38 ~ -1.175494351E-38.
• 无符号的取值范围:0 和 1.175494351E-38 ~ 3.402823466E+38.

DOUBLE类型的取值范围如下:

• 有符号的取值范围:-1.7976931348623157E+308 ~ 2.2250738585072014E-308.
• 无符号的取值范围:0 和 2.2250738585072014E-308 ~ 1.7976931348623157E+308.

不论是定点还是浮点类型,如果用户指定的精度超出精度范围,则会四舍五入进行处理.

FLOA T和DOUBLE在不指定精度时,默认会按照实际的精度(由计算机硬件和操作系统决定),DECIMAL如不指定精度,默认为(10,0).
浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的数据范围;它的缺点是会引起精度问题.

提示:在MySQL中,定点数以字符串形式存储,在对精度要求比较高的时候(如货币,科学数据等)使用DECIMAL的类型比较好,另外两个浮点数进行减法和比较运算时也容易出问题,所以在使用浮点型时需要注意,并尽量避免做浮点数比较.

5.1.3日期与时间类型

MySQL中有多种表示日期的数据类型,主要有:DATETIME、DATE、TIMESTAMP、TIME和YEAR.

日期与时间数据类型
|类型名称|日期格式|范围|存储需求|
|YEAR|YYYY|1901~2155|1字节|
|TIME|HH:MM:SS|-838:59:59~838:59:59|3字节|
|DATA|YYYY-MM-DD|1000-01-01~9999-12-03|3字节|
|DATATIME|YYYY-MM-DD HH:MM:SS|1000-01-01 00:00:00~9999-12-03 23:59:59|8字节|
|TIMESTAMP|YYYY-MM-DD HH:MM:SS|1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC|4字节|

1.YEAR
YEAR类型是一个单字节类型用于表示年,在存储时只需要1个字节.可以使用各种格式指定YEAR值,如下所示:

• 以4位字符串或者4位数字格式表示的YEAR,范围为'1901'〜'2155'.输入 格式为'YYYY'或者YYYY,例如,输入'2010'或2010,插入到数据库的值均为2010.
• 以2位字符串格式表示的YEAR,范围为'00'到'99'.'00'〜'69'和'70'〜 '99'范围的值分别被转换为2000-2069和1970〜1999范围的YEAR值.'0'与'00'的作用相同.插入超过取值范围的值将被转换为2000.
• 以2位数字表示的YEAR,范围为1〜99.1〜69和70〜99范围的值分别被转换为 2001-2069和1970-1999范围的YEAR值.注意:在这里0值将被转换为0000,而不是2000.

提示:两位整数范围与两位字符串范围稍有不同,例如:插入2000年,读者可能会使用数宇格 式的0表示YEAR,实际上,插入数据库的值为0000,而不是所希望的2000.只有使用字符串格式的'0'或'00',才可以被正确地解释为2000.非法YEAR值将被转换为0000.

2.TIME
TIME类型用于只需要时间信息的值,在存储时需要3个字节.格式为'HH:MM:SS'.
HH表示小时;MM表示分钟;SS表示秒.TIME类型的取值范围为-838:59:59 ~ 838:59:59, 小时部分会如此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时),还可能是某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时,或者甚至为负).可以使用各种格式指定TIME值,如下所示:

• 'D HH:MM:SS'格式的字符串.还可以使用下面任何一种"非严格"的语法:'HH:MM:SS'、'HH:MM'、'DHH:MM'、'DHH' 或 'SS'.这里的 D 表示日,可以取0~34之间的值.在插入数据库时,D被转换为小时保存,格式为"D*24 + HH".
• 'HHMMSS'格式的、没有间隔符的字符串或者HHMMSS格式的数值,假定是有意义的时间.例如:'101112'被理解为'10:11:12',但'109712'是不合法的(它有一个没有意义的分钟部分),存储时将变为00:00:00.

提示:
为TIME列分配简写值时应注意:如果没有冒号,MySQL解释值时,假定最右边的两位表示秒.(MySQL解释TIME值为过去的时间而不是当天的时间).例如,读者可能认为'1112'和1112表示11:12:00(即11点过12分),但MySQL将它们解释为00:11:12(即11分12秒).同样'12'和12被解释为00:00:120相反,TIME值中如果使用冒号则肯定被看作当天的时间.也就是说,'11:12'表示11:12:00,而不是00:11:12.

在使用'DHH'格式时,小时一定要使用双位数值,如果是小于10的小时数,应在前面加0.

3. DATE类型
   DATE类型用在仅需要日期值时,没有时间部分,在存储时需要3个字节.日期格式为'YYYY-MM-DD'其中YYYY表示年;MM表示月;DD表示日.在给DATE类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合DATE的日期格式即可, 如下:

• 以YYYYY-MM-DD,或者'YYYYMMDD'字符串格式表示的日期,取值范围为 '1000-01-01' ~ '9999-12-3'.例如,输入 '2012-12-31'或者 '20121231' ,插入数据库的曰期都为2012-12-31.
• 以'YY-MM-DD'或者"YYMMDD'字符串格式表示的日期,在这里YY表示两位的年值.包含两位年值的日期会令人模糊,因为不知道世纪.MySQL使用以下规则解释两位年值:'00〜69'范围的年值转换为'2000〜2069';'70〜99'范围的年值转换为*1970- 1999'.例如,输入'12-12-31',插入数据库的日期为2012-12-31;输入'981231',插入数据的日期为1998-12-31.
• 以YY-MM-DD或者YYMMDD数字格式表示的日期,与前面相似,00-69范围的年值转换为2000-2069; 70〜99范围的年值转换为1970〜1999.例如,输入12-12-31插入数 据库的曰期为2012-12-31;输入981231,插入数据的日期为1998-12-31.
• 使用CURRENT_DATE或者NOW(),插入当前系统日期.

提示:
MySQL允许"不严格"语法:任何标点符号都可以用作日期部分之间的间隔符.例如,'98-11-31'、'98.11.31'、'98/11/31' 和 '98@11@31' 是等价的,这些值也可以正确地插入到数据库.

4. DATETIME
   DATETIME类型用在需要同时包含日期和时间信息的值,在存储时需要8个字节.曰期格式为'YYYY-MM-DDHH:MM:SS',其中YYYY表示年;MM表示月;DD表示日;HH表示小时;MM表示分钟;SS表示秒.在给DATETIME类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合DATETIME的日期格式即可,如下所示:

• 以 'YYYY-MM-DDHH:MM:SS'或者'YYYYMMDDHHMMSS'字符串格式表示 的值,取值范围为 '1000-01-01 00:00:00' — '9999-12-3 23:59:59'.例如输入 '2012-12-31 05:05:05' 或者'20121231050505' ,插入数据库的 DATETIME 值都为 2012-12-31 05:05:05o
• 以:YY-MM-DDHH:MM:SS'或者'YYMMDDHHMMSS'字符串格式表示的日期, 在这里YY表示两位的年值.与前面相同,'00〜69'范围的年值转换为'2000〜2069'; '70〜 99'范围的年值转换为'1970〜1999'.例如输入'12-12-31 05:05:05',插入数据库的 DATETIME 为 2012-12-31 05:05:05;输入 '980505050505',插入数据库的 DATETIME 为 1998-05-05 05:05:05.
• 以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和时间, 例如输入 20121231050505,插入数据库的 DATETIME 为 2012-12-31 05:05:05:输入 981231050505,插入数据的 DATETIME 为 1998-12-31 05:05:05

提示:
MySQL允许"不严格"语法:任何标点符号都可以用作日期部分或时间部分之间的间隔"符.例如,'98-12-31 11:30:45'、'98.12.31 11+30+45'、'98/12/31 113045'和'98@12@31 llA30A45,是等价的,这些值都可以正确地插入数据库.

5. TIMESTAMP
   TIMESTAMP的显示格式与DATETIME相同,显示宽度固定在19个字符,日期格式为 YYYY-MM-DD HH:MM:SS,在存储时需要4个字节.但是TIMESTAMP列的取值范围小于 DATETIME 的取值范围,为 U970-01-01 00:00:01* UTC〜'2038-01-19 03:14:07' UTC,其 中,UTC(Coordinated Universal Time),为世界标准时间,因此在插入数据时,要保证在合 法的取值范围内.

TIMESTAMP与DATETIME除了存储字节和支持的范围不同外,还有一个最大的区别就 是:DATETIME在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么, 与时区无关;而TIMESTAMP值的存储是以UTC(世界标准时间)格式保存的,存储时 对当前时区进行转换,检索时再转换回当前时区.即查询时,根据当前时区的不同,显示 的时间值是不同的.

提示:
如果为一个DATETIME或TIMESTAMP对象分配一个DATE值,结果值的时间部分被设置为'00:00:00',因为DATE值未包含时间信息.如果为一个DATE对象分配一个DATETIME或TIMESTAMP值,结果值的时间部分被删除,因为DATE值未包含时间信息.

5.1.4文本字符串类型

字符串类型用来存储字符串数据,除了可以存储字符串数据之外,还可以存储其他数据,比如图片和声音的二进制数据.MySQL支持两类字符型数据:文本字符串和二进制字符串. 本小节主要讲解文本字符串类型,文本字符串可以进行区分或者不区分大小写的串比较,另外,还可以进行模式匹配查找.MySQL中文本字符串类型指CHAR、VARCHAR, TEXT、ENUM 和SET

MySQL中文本字符串数据类型

1.CHAR和VARCHAR类型
CHAR(M)为固定长度字符串,在定义时指定字符串列长.当保存时在右侧填充空格以达到指定的长度.M表示列长度,M的范围是0-255个字符.例如,CHAR(4)定义了一个固定 长度的字符串列,其包含的字符个数最大为4.当检索到CHAR值时,尾部的空格将被删除掉.
VARCHAR(M)是长度可变的字符串,M表示最大列长度.M的范围是0~65535. VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定,而其实际占用的空间为字符串的实际长度加1.例如,VARCHAR(50)定义了一个最大长度为50的字符串,如果插入
的字符串只有10个字符,则实际存储的字符串为10个字符和一个字符串结束字符.VARCHAR在值保存和检索时尾部的空格仍保留.

CHAR(4)与VARCHAR(4)存储区别

对比结果可以看到,CHAR(4)定义了固定长度为4的列,不管存入的数据长度为多少, 所占用的空间均为4个字节, VARCHAR(4)定义的列所占的字节数为实际长度加1.

提示:
在表中,最后一行的值只有在使用"不严格"模式时,字符串才会被截断插入;如果 MySQL运行在"严格"模式,超过列长度的值不会被保存,并且会出现错误信息:"ERROR 1406(22001):Data too long for column",即字符串长度超过指定长度,无法插入.

2. TEXT类型
   TEXT列保存非二进制字符串,如文章内容、评论等.当保存或查询TEXT列的值时,不删除尾部空格.Text类型分为4种:TINYTEXT, TEXT、MEDIUMTEXT 和 LONGTEXT. 不同的TEXT类型的存储空间和数据长度不同.

• TINYTEXT最大长度为255(28-1)字符的TEXT列.
• TEXT最大长度为65 535(216-1)字符的TEXT列.
• MEDIUMTEXT 最大长度为 16 777 215(224-1)字符的 TEXT 列.
• LONGTEXT 最大长度为 4 294 967 295 或 4GB(232-1)字符的 TEXT 列.

3. ENUM类型
   ENUM是一个字符串对象,其值为表创建时在列规定中枚举的一列值.语法格式如下:
   字段名 ENUM('值1','值2',...,'值n')
   字段名指将要定义的字段,值n指枚举列表中的第n个值.ENUM类型的字段在取值时,只能在指定的枚举列表中取,而且一次只能取一个.如果创建的成员中有空格时,其尾部的空格将自动被删除.ENUM值在内部用整数表示,每个枚举值均有一个索引值:列表值所允许的成员值从1开始编号,MySQL存储的就是这个索引编号.枚举最多可以有65535个元素.

提示:
ENUM列总有一个默认值.如果将ENUM列声明为NULL, NULL值则为该列的一个有效值,并且默认值为NULL.如果ENUM列被声明为NOT NULL,其默认值为允许的值 列表的第1个元素.

4. SET类型
   SET是一个字符串对象,可以有零或多个值,SET列最多可以有64个成员,其值为表创建吋规定的一列值.指定包括多个SET成员的SET列值时,各成员之间用逗号(,)间隔开.语法格式如下:
   SET('值 1','值 2','值n')
   与ENUM类型相同,SET值在内部用整数表示,列表中每一个值都有一个索引编号.当创建表时,SET成员值的尾部空格将自动被删除.但与ENUM类型不同的是,ENUM类型的字段只能从定义的列值中选择一个值插入,而SET类型的列可从定义的列值中选择多个字符的联合.
   如果插入SET字段中列值有重复,则MySQL自动删除重复的值;插入SET字段的值的 顺序并不重要,MySQL会在存入数据库时,按照定义的顺序显示;如果插入了不正确的值,默认情况下,MySQL将忽视这些值,并给出警告.

5.1.5二进制字符串类型

前面讲解了存储文本的字符串类型,这一小节将讲解MySQL中存储二进制数据的字符串 类型.MySQL中的二进制数据类型有:BIT、BINARY. VARBINARY、TINYBLOB, BLOB、MEDIUMBLOB和LONGBLOB,本节将讲解各类二进制字符串 类型 的特 点和 使用 方法 .

MySQL中的二进制字符串类型

1.BIT类型
BIT类型是位字段类型.M表示每个值的位数,范围为1-64.如果M被省略,默认为1. 如果为BIT(M)列分配的值的长度小于M位,在值的左边用0填充.例如,为BIT(6)列分配一 个值b'101',其效果与分配b'000101'相同.BIT数据类型用来保存位字段值,例如:以二进制的形式保存数据13, 13的二进制形式为1101,在这里需要位数至少为4位的BIT类型,即可以定义列类型为BIT(4).大于二进制1111的数据是不能插入BIT(4)类型的字段中的.

提示:
默认情况下,MySQL不可以插入超出该列允许范围的值,因而插入的数据要确保插入的 值在指定的范围内.

2.BINARY 和 VARBINARY 类型
BINARY和VARBINARY类型类似于CHAR和VARCHAR,不同的是它们包含二进制字节字符串.其使用的语法格式如下:
列名称 BINARY(M)或者 VARBINARY(M)
BINARY类型的长度是固定的,指定长度之后,不足最大长度的,将在它们右边填充'\0'补齐以达到指定长度.例如:指定列数据 类型为BINARY(3), 当插入'a'时,存储的内容实际为"a\0\0",当插入"ab"时,实际存储的内容为"ab\0",不管存储的内容是否达到指定的长度,其存储空间均为指定的值M.
VARBINARY类型的长度是可变的,指定好长度之后,其长度可以在0到最大值之间. 例如:指定列数据类型为VARBINARY(20),如果插入的值的长度只有10,则实际存储空间为10加1,即其实际占用的空间为字符串的实际长度加1.

3. BLOB类型
   BLOB是一个二进制大对象,用来存储可变数量的数据oBLOB类型分为4种:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB,它们可容纳值的最大长度不同.

BLOB类型的存储范围

BLOB列存储的是二进制字符串(字节字符串);TEXT列存储的是非二进制字符串(字符字符串).BLOB列没有字符集,并且排序和比较基于列值字节的数值;TEXT列有一个字符集,并且根据字符集对值进行排序和比较.

5.2 如何选择数据类型

MySQL提供了大量的数据类型,为了优化存储,提高数据库性能,在任何情况下均应使用最精确的类型.即在所有可以表示该列值的类型中,该类型使用的存储最少.

1. 整数和浮点数
   如果不需要小数部分,则使用整数来保存数据;如果需要表示小数部分,则使用浮点数类型.对于浮点数据列,存入的数值会对该列定义的小数位进行四舍五入.例如,如果列的值的范围为1-99999,若使用整数,则MEDIUMINT UNSIGNED是最好的类型;若需要存储小数, 则使用FLOAT类型.
   浮点类型包括FLOAT和DOUBLE类型.DOUBLE类型精度比FLOAT类型高,因此,如要求存储精度较高时,应选择DOUBLE类型.

2. 浮点数和定点数
   浮点数FLOAT、DOUBLE相对于定点数DECIMAL的优势是:在长度一定的情况下,浮点数能表示更大的数据范围,但是由于浮点数 容易产生误差,因此对精确度要求比较高时,建议使用DECIMAL来存储.DECIMAL在MySQL中是以字符串存储的,用于定义货币等对精确度要求较高的数据.在数据迁移中,float(M,D)是非标准SQL定义,数据库迁移可能会出现问题,最好不要这样使用. 另外两个浮点数进行减法和比较运算时也容易出问题,因此在进行计算的时候,一定要小心.如果进行数值比较,最好使用DECIMAL类型.

3. 日期与时间类型
   MySQL对于不同种类的日期和时间有很多的数据类型,比如YEAR和TIME.如果只需 要记录年份,则使用YEAR类型即可;如果只记录时间,只需使用TIME类型.
   如果同时需要记录日期和时间,则可以使用TIMESTAMP或者DATETIME类型.由于TIMESTAMP列的取值范围小于DATETIME的取值范围,因此存储范围较大的日期最好使用 DATETIME.
   TIMESTAMP也有一个DATETIME不具备的属性.默认的情况下,当插入一条记录但并没有指定TIMESTAMP这个列值时,MySQ L会把TIMESTAMP列设为当前的时间.因此当需要插入记录同时插入当前时间时,使用TIMESTAMP是方便的,另外TIMESTAMP在空间上比DATETIME更有效.

4. CHAR与VARCHAR之间的特点与选择
   CHAR和VARCHAR的区别如下:

• CHAR是固定长度字符,VARCHAR是可变长度字符;
• CHAR会自动删除插入数据的尾部空格,VARCHAR不会删除尾部空格.
  CHAR是固定长度,所以它的处理速度比VARCHAR的速度要快,但是它的缺点就是浪费存储空间.所以对存储不大,但在速度上有要求的可以使用CHAR类型,反之可以使用VARCHAR类型来实现.

存储引擎对于选择CHAR和VARCHAR的影响:

• 对于MylSAM存储引擎:最好使用固定长度的数据列代替可变长度的数据列.这样可以使整个表静态化,从而使数据检索更快,用空间换时间.
• 对于InnoDB存储引擎:使用可变长度的数据列,因为InnoDB数据表的存储格式不分固定长度和可变长度,因此使用CHAR不一定比使用VARCHAR更好,但由于VARCHAR是按照实际的长度存储,比较节省空间,所以对磁盘I/O和数椐存储总量
  比较好.

5. ENUM 和 SET
   ENUM只能取单值,它的数据列表是一个枚举集合.它的合法取值列表最多允许有65535个成员.因此,在需要从多个值中选取一个时,可以使用ENUM.比如:性别字段适合定义 为ENUM类型,每次只能从'男'或'女'中取一个值.
   SET可取多值.它的合法取值列表最多允许有64个成员.空字符串也是一个合法的SET值.在需要取多个值的时候,适合使用SET类型,比如:要存储一个人兴趣爱好,最好使用 SET类型.
   ENUM和SET的值是以字符串形式出现的,但在内部,MySQL以数值的形式存储它们.

6. BLOB 和 TEXT
   BLOB是二进制字符串,TEXT是非二进制字符串,两者均可存放大容量的信息.BLOB 主要存储图片、音频信息等,而TEXT只能存储纯文本文件.

5.3 常见运算符介绍

5.3.1运算符概述

运算符是告诉MySQL执行特定算术或逻辑操作的符号.MySQL的内部运算符很丰富, 主要有四大类,分别是:算术运算符、比较运算符、逻辑运算符、位操作运算符.

1. 算术运算符
   算术运算符用于各类数值运算,包括加(+)、减(-)、乘(*)、除(/)、求余(或称模运算,%).
2. 比较运算符
   比较运算符用于比较运算.包括大于(>)、小于(<)、等于(=)、大于等于(>=)、小于等于(<=)、不等于(!=),以及 IN、BETWEEN AND, IS NULL> GREATEST> LEAST. LIKE、REGEXP 等.
3. 逻辑运算符
   逻辑运算符的求值所得结果均为1(TRUE)、0(FALSE),这类运算符有逻辑非(NOT或者!)、逻辑与(AND或者&&)、逻辑或(OR或者||)、逻辑异或(XOR).
4. 位操作作运算符
   位操作运算符参与运算的操作数按二进制位进行运算.包括位与(&)、位或(I)、位非(~)、位异或(^)、左移(<<)、右移(>>)6种.

5.3.2算术运算符

MySQL中的算术运算符

5.3.3比较运算符

一个比较运算符的结果总是1、0或者是NULL,比较运算符经常在 SELECT的查询条件子句中使用,用来查询满足指定条件的记录.

MySQL中的比较运算符

1.等于运算符(=)
等号(=)用来判断数字、字符串和表达式是否相等.如果相等,返回值为1,否则返回值为0.

数值比较时有如下规则:

• 若有一个或两个参数为NULL,则比较运算的结果为NULL.
• 若同一个比较运算中的两个参数都是字符串,则按照字符串进行比较.
• 若两个参数均为整数,则按照整数进行比较.
• 若一个字符串和数字进行相等判断,则MySQL可以自动将字符串转换为数字.

2.安全耔运算符(<=>)
这个操作符和=操作符执行相同的比较操作,不过<=>可以用来判断NULL值.在两个操作数均为NULL时,其返回值为1而不为NULL;而当一个操作数为NULL时,其返回值为0而不为NULL.
'<=>'在执行比较操作时和的作用是相似的,唯一的区别是'<=>'可以用来对NULL进行判断,两者都为NULL时返回值为1.

3.不等于运算符( <>或者!=)
'<>'或者'!='用于判断数字、字符串、表达式不相等的判断.如果不相等,返冋值为1;否则返回值为0.这两个运算符不能用于判断空值NULL.

4.小于或等于运算符(<=)
'<='用来判断左边的操作数是否小于或者等于右边的操作数.如果小于或者等于,返回值为1,否则返回值为0.'<='不能用于判断空值NULL.

5.小于运算符(<)
'<'运算符用来判断左边的操作数是否小于右边的操作数,如果小于,返回值为1;否则返回值为0. '<'不能用于判断空值NULL.

6.大于或等于运算符(>=)
'>='运算符用来判断左边的操作数是否大于或者等于右边的操作数,如果大于或者等于,返回值为1;否则返回值为0.'>='不能用于判断空值NULL.

7. 大于运算符(>)
   '>'运算符用来判断左边的操作数是否大于右边的操作数,如果大于,返回值为1;否则返回值为0.不能用于判断空值NULL.

8. IS NULL(ISNULL), IS NOT NULL 运算符
   IS NULL和ISNULL检验一个值是否为NULL,如果为NULL,返回值为1.否则返回值为0;IS NOT NULL检验一个值是否为非NULL,如果是非NULL,返回值为1;否则返回值为0.

9. BETWEEN AND 运算符
   语法格式为:expr BETWEEN min AND max.假如expr大于或等于min且小于或等于max,则BETWEEN的返回值为1.否则返回值为0.

10. LEAST运算符
    语法格式为:LEAST(值1,值2,...,值n),其中值n表示参数列表中有n个值.在有两个或多个参数的情况下,返回最小值.假如任意一个自变量为NULL,则LEAST()的返回值为NULL.

11. GREATEST(valuel ,value2,...)
    语法格式为:GREATEST(值1,值2,...,值n),其中n表示参数列表中有n个值.当有2个或多个参数时,返回值为最大值,假如任意一个自变量为NULL,则GREATEST()的返回值为NULL.

12. IN. NOT IN 运算符
    IN运算符用来判断操作数是否为IN列表中的其中一个值,如果是,返回值为1;否则返回值0.
    NOT IN运算符用来判断表达式是否为IN列表中的其中一个值,如果不是,返回值为1:否则返回值为0.

13. LIKE
    LIKE运算符用来匹配字符串,语法格式为:expr LIKE匹配条件,如果expr满足匹配条件,则返回值为1(TRUE);如果不匹配,则返回值为0(FALSE).若expr或匹配条件中 任何一个为NULL,则结果为NULL.
    LIKE运算符在进行匹配时,可以使用下面两种通配符:
    (1)'%',匹配任何数目的字符,甚至包括零字符.
    (2)'_',只能匹配一个字符.

14. REGEXP
    REGEXP运算符用来匹配字符串,语法格式为:expr REGEXP匹配条件,如果expr满足匹配条件,返回1;如果不满足,则返回0;若expr或匹配条件任意一个为NULL,则结果为NULL.
    REGEXP运算符在进行匹配时,常用的有下面几种通配符:

• '^':匹配以该字符后面的字符开头的字符串.
• '$':匹配以该字符后面的字符结尾的字符串.
• '.':匹配任何一个单字符.
• [...]:匹配在方括号内的任何字符.例如,"[abc] "匹配"a"、"b"或"c". 为了命名字符的范围,使用一个."[a-z]"匹配任何字母,而"[0-9]"匹配任何数字.
• '':匹配零个或多个在它前面的字符.例如,"X"匹配任何数量的'X'字符, "[0-9]*"匹配任何数量的数字,而匹配任何数量的任何字符.

5.3.4逻辑运算符

在SQL中,所有逻辑运算符的求值所得结果均为TRUE、FALSE或NULL.在MySQL中,它们体现为l(TRUE)、0(FALSE)和NULL.

MySQL中的逻辑运算符

1. NOT或者!
   逻辑非运算符NOT或者!表示当操作数为0时,所得值为1;当操作数为非零值时,所 得值为0;当操作数为NULL时,所得的返回值为NULL.

2. AND 或者 &&
   逻辑与运算符AND或者&&表示当所有操作数均为非零值、并且不为NULL时,计算所得结果为1;当一个或多个操作数为0时,所得结果为0,其余情况返回值为NULL.

3. OR或者||
   逻辑或运算符OR或者||表示当两个操作数均为非NULL值,且任意一个操作数为非零值时,结果为1,否则结果为0;当有一个操作数为NULL,且另一个操作数为非零值时,则结 果为1,否则结果为NULL;当两个操作数均为NULL时,则所得结果为NULL.

4. XOR
   逻辑异或运算符XOR.当任意一个操作数为NULL时,返回值为NULL;对于非NULL 的操作数,如果两个操作数都是非0值或者都是0值,则返回结果为0;如果一个为0值,另一个为非0值,返回结果为1.

a XOR b 的计算等同于(a AND(NOT b))或者((NOT a)AND b)

5.3.5位运算符

位运算符是用来对二进制字节中的位进行测试、移位或者测试处理,MySQL中提供的位运算符有按位 或(|),按 位与(&), 按位异或(^),按位左移(<<),按位右移(>>),按位取反(~).

MySQL中的位运算符

1.位或运算符(|)
位或运算的实质是将参与运算的几个数据,按对应的二进制数逐位进行逻辑或运算.对应的二进制位有一个或两个为1,则该位的运算结果为1,否则为0.

2.位与运算符(&)
位与运算的实质是将参与运算的几个操作数,按对应的二进制数逐位进行逻辑与运算.对应的二进制位都为1,则该位的运算结果为1,否则为0.

3.位异或运算符(^)
位异或运算的实质是将参与运算的两个数据,按对应的二进制数逐位进行逻辑异或运算.对应位的二进制数不同时,对应位的结果才为1.如果两个对应位数都为0或者都为1,则对应位的结果为0.

4.位左移运算符(<<)
位左移运算符<<使指定的二进制值的所有位都左移指定的位数.左移指定位数之后,左边高位的数值将被移出并丢弃,右边低位空出的位置用0补齐.语法格式为:expr << n.这里 n指定值expr要移位的位数.

5.位右移运算符(>>)
位右移运算符>>使指定的二进制值的所有位都右移指定的位数.右移指定位数之后,右 边低位的数值将被移出并丢弃,左边高位空出的位置用0补齐.语法格式为:expr >> n.这里 n指定值expr要移位的位数.

6.位取反运算符(~)
位取反运算的实质是将参与运算的数据,按对应的二进制数逐位反转,即1取反后变0, 0取反后变为1.

5.3.6运算符的优先级

运算符的优先级决定了不同的运算符在表达式中计算的先后顺序,表5.14列出了 MySQL中的各类运算符及其优先级.

5.4 综合案例--运算符的使用

5.5 专家解惑

疑问1:MySQL中如何使用特殊字符?
诸如单引号('),双引号("),反斜线(1)等符号,这些符号在MySQL中不能直接输 入使用,否则会产生意料之外的结果.在MySQL中,这些特殊字符称为转义字符,在输入时 需要以反斜线符号('V )开头,所以在使用单引号和双引号时应分别输入(V)或者(V), 输入反斜线时应该输入(\),其他特殊字符还有回车符(\r),换行符(\n),制表符(\tab), 退格符(\b)等.在向数据库中插入这些特殊字符时,一定要进行转义处理.

疑问2:MySQL中可以存储文件吗?
MySQL中的BLOB和TEXT字段类型可以存储数据量较大的文件,可以使用这些数据类 型存储图像、声音或者是大容量的文本内容,例如网页或者文档.虽然使用BLOB或者TEXT 可以存 储大容 量的数 据,但 是对这 些字段 的处理会 降低数据 库的性能 .如果并 非必要,可以选 择只储存文件的路径.

疑问3:MySQL中如何执行区分大小写的字符串t哦?
在Windows平台下,MySQL是不区分大小的,因此字符串比较函数也不区分大小写.如果想执行区分大小写的比较,可以在字符串前面添加BINARY关键字.例如默认情况下,'a'='A'返回结果为1,如果使用BINARY关键字,BINARY 'a' = 'A'结果为0,在区分大 小写的情况下,'a'与'A'并不相同.