MySQL基础大全（看这一篇足够！！！）

最新推荐文章于 2024-07-23 19:00:53 发布

满院子的小黄花

最新推荐文章于 2024-07-23 19:00:53 发布

阅读量5.6k

点赞数 15

文章标签： mysql 数据库 oracle

本文链接：https://blog.csdn.net/weixin_35856285/article/details/139892950

版权

文章目录

前言
一、初识MySQL
二、数据库的基本操作
三、数据表的基本操作
四、数据类型和运算符
五、MySQL函数
六、数据的增删改查
七、索引

前言

本文仅介绍MySQL基础内容

一、初识MySQL

1.1 数据库基础

数据库（DataBase，DB）是一个长期存储在计算机内的，有组织的，有共享的，统一管理的数据集合。它是一个按数据结构来存储和管理数据的计算机软件系统。即数据库包含两层含义：保管数据的“仓库”，以及数据管理的方法和技术。

1.2 数据库技术构成

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

1.2.1 数据库系统

数据库系统由三个主要的组成部分：

数据库：用于存储数据的地方
数据库管理系统（DataBase Management System，DBMS）：用于管理数据库的软件。是用户创建，管理和维护数据库时所使用的软件，位于用户和操作系统之间，对数据库进行统一管理。DBMS能定义数据存储结构，提供数据的操作机制，维护数据库的安全性，完整性和可靠性。
数据库应用程序：为了提高数据库系统的处理能力所使用的的管理数据库的软件补充。大多情况下，DBMS无法满足对数据管理的要求。数据库应用程序的使用可以满足对数据管理的更高要求，还可以使数据管理过程更加直观和友好。数据库应用程序负责与DBMS进行通信，访问和管理DBMS中存储的数据，允许用户插入，修改，删除DB中的数据。

下图辅助理解：
在这里插入图片描述

DBMS像是一个大管家，他接收到我们撰写的SQL之后，根据SQL语法对SQL进行分析，然后去操作数据库，数据库就像是一个仓库，他根据SQL在仓库中查找对应的数据，或是丢弃（删除）某些数据，又或是对数据进行变更，然后将结果返回给用户。

1.2.2 SQL语言

对数据库进行查询和修改操作的语言叫做SQL。SQL语言包含以下4个部分：

数据定义语言（DDL）：DROP，CREATE，ALTER等语句。
数据操作语言（DML）：INSERT，UPDATE，DELETE语句。
数据查询语言（DQL）：SELECT语句。
数据控制语言（DCL）：CRANT，REVOKE，COMMIT，ROLLBACK等语句。

1.2.3 数据库访问接口

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

ODBC（Open Database Connectivity）：开放数据库互连技术为访问不同的SQL数据库提供了一个共同的接口。ODBC使用SQL作为访问数据的标准。这一接口提供了最大限度的互操作性，一个应用程序可以通过共同的一组代码访问不同的SQL数据库管理系统（DBMS）。
一个基于ODBC的应用程序对数据库的操作不依赖任何DBMS，不直接与DBMS打交道，所有的数据库操作由对应的DBMS的ODBC驱动程序完成。也就是说，不论是Access，MySQL还是Oracle数据库，均可用ODBC API进行访问。由此可见，ODBC的最大优点是能以统一的方式处理所有的数据库。
JDBC（Java Database Connectivity）：java数据库连接用于Java程序连接数据库的标准方法，是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。
ADO.NET：微软在.NET框架下开发设计的一组用于和数据源进行交互的面向对象类库。ADO.NET提供了对关系数据，XML和应用程序数据的访问，允许和不同类型的数据源以及数据库进行交互。
PDO（PHP Data Object）：为PHP访问数据库定义了一个轻量级的，一致性的接口，它提供了一个数据访问抽象层，这样无论使用什么数据库，都可以通过一致性的函数执行查询和获取数据。

1.3 什么是MySQL

MySQL是一个小型关系型数据库管理系统，与其他大型数据库管理系统，例如Oracle，DB2，SQL Server等相比，MySQL规模小，功能有限，但是它体积小，速度快，成本低，且它提供的功能对稍微复杂的应用来说已经够用。

MySQL的优势如下：

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

二、数据库的基本操作

2.1 数据库创建和删除

创建数据库： CREATE DATABASE database_name;
查看数据库定义：SHOW CREATE DATABASE;或 SHOW CREATE DATABASE\G;
删除数据库：DROP DATABASE database_name

2.2 数据库存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建，查询，更新和删除数据操作。不同的存储引擎提供不同的存储机制，索引技巧，锁定水平等功能。MySQL的核心就是存储引擎。

2.2.1 MySQL存储引擎简介

MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。show engines;命令查看所有引擎，MySQL支持的存储引擎有以下：
在这里插入图片描述
Support列的值表示某种引擎是否可以使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认存储引擎。
Transaction表示是否支持事务，XA表示是否支持分布式的交易处理的XA的规范，Savepoints表示是否支持保存点。
由此可见MySQL默认搜索引擎是InnoDB。

2.2.2 InnoDB存储引擎

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

InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供了一个类似Oracle的非锁定读。
InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘关系的数据库引擎所不能匹敌的。
InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将他的表和索引放在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。
InnoDB支持外键完整性约束。存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键。
InnoDB被用在众多需要高性能的大型数据库站点上。InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

2.2.3 MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web，数据仓储和其他应用环境下最常用的存储引擎之一。MyISAM拥有较高的插入，查询速度，但不支持事务。MyISAM主要特性有：

大文件（达63位文件长度）在支持大文件的文件系统和操作系统上被支持。
当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并被删除的块，以及若下一个块被删除，就扩展到下一块来自动完成。
每个MyISAN表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16个。
最大的键长度是1000字节，这也可以通过编译来改变。对于键长度超过250字节的情况，一个超过1024字节的键将被用上。
BOLB和TEXT列可以被索引。
NULL值被允许在索引的列中。这个值占每个键的0~1个字节。
所有数字键值以搞字节优先被存储以允许一个更高地索引压缩。
每个 MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新，所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快。
可以把数据文件和索引文件放在不同目录。
每个字符列可以有不同的字符集。
有VARCHAR的表可以固定或动态记录长度。
VARCHAR和CHAR列可以多达64KB。
使用MyISAN引擎创建数据库，将生产3个文件，文件的名字以表名字开始，扩展名指出文件类型：frm文件存储表定义，数据文件的扩展名为.MYD，索引文件的扩展名是.MYI。

2.2.4 存储引擎的选择

不同的存储引擎都有各自的特点，以适应不同的需求。

功能	InnoDB	MyISAM	Memory	Archive
存储限制	64TB	256TB	RAM	None
支持事务	是	否	否	否
支持全文索引	否	是	否	否
支持树索引	是	是	是	否
支持哈希索引	否	否	是	否
支持数据缓存	是	否	N/A	否
支持外键	是	否	否	否

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

三、数据表的基本操作

3.1 创建数据表

创建数据表：
CREATE TABLE <表名>
(
字段名1 数据类型[列级别约束条件][默认值],
字段名2 数据类型[列级别约束条件][默认值],
…
[表级别约束条件]
);
例如：
CREATE TABLE tb_emp1
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
添加主键约束（创建表时）
CREATE TABLE tb_emp1
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
添加主键约束（定义完所有列之后）
CREATE TABLE tb_emp1
(
id INT(11) ,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id)
);
多字段联合主键
PRIMARY KEY[字段1，字段2，…，字段n]
例如：
CREATE TABLE tb_emp1
(
id INT(11) ,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id,mame)
);
使用外键约束：首先外键是表中的一个字段，它可以不是本表（子表）的主键，但对应另外一个表的主键（父表）。外键的主要作用就是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据一致性，完整性。
主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。
创建外键的语法：[CONSTRAINT<外键名> FOREING KEY 字段名1[,字段名2,…]] REFERENCES<主表名>
主键列[,主键列2,…]
例如：
CREATE TABLE tb_emp1
(
id INT(11) ,
name VARCHAR(25),
location VARCHAR(50)
);
CREATE TABLE tb_emp5
(
id INT(11) ,
name VARCHAR(25),
deptId INT(11),
salary FLOAT
CONSTRAINT fk_emp_dept1 FORRIGN KEY(deptId) REFERENCES tb_dept1(id);
);
以上语句执行成功之后，在表tb_emp5上添加了名称为fk_emp_dept1的外键约束，外键名称为deptId，其依赖于表tb_dept1的主键id。
使用非空约束
指定字段的值不能为空。
语法：字段名数据类型 NOT NULL。
例如：
CREATE TABLE tb_emp1
(
id INT(11) ,
name VARCHAR(25) NOT NULL,
location VARCHAR(50)
);
使用唯一性约束
要求该列唯一，允许为空，但只能出现一个空值，唯一约束可以确保一列或者几列不出现重复值。
语法如下：
字段名数据类型 UNIQUE（创建表时）
[CONSTRAINT<约束名> UNIQUE<字段名>]（定义完所有列之后）
例如：
CREATE TABLE tb_emp1
(
id INT(11) ,
name VARCHAR(25) UNIQUE,
location VARCHAR(50)
);
CREATE TABLE tb_emp1
(
id INT(11) ,
name VARCHAR(25),
location VARCHAR(50),
CONSTRAINT STH UNIQUE(name)
);
使用默认约束
指定某列的默认值。例如将某表中的年龄一列指定为默认值20。新插入数据时，如果没有指定值，就会默认为20.
语法：字段名数据类型 DEFAULT 默认值
例如：
CREATE TABLE tb_emp1
(
id INT(11) ,
name VARCHAR(25) ,
location VARCHAR(50) DEFAULT ‘徐州’
);
设置表的属性值自动增加
每次新插入记录时，系统自动生成字段的主键值。一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数类型。
语法：字段名数据类型 AUTO_INCREMENT
例如：
CREATE TABLE tb_emp1
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) ,
location VARCHAR(50)
);

3.2 修改数据表

修改表是指修改数据库中已经存在的数据表的结构。MySQL使用ALTER TABLE语句修改表。常用的修改表的操作有：修改表名，修改字段数据类型或字段名，增加和删除字段，修改字段的排列位置，更改表的存储引擎，删除表的外键约束等。

修改表名
语法：ALTER TABLE<旧表名> RENAME[TO]<新表名>，其中TO为可选参数，使用与否均不影响结果。
例如：ALTER TABLE tb_dept3 RENAME tb_deptment3；将表tb_dept3改名为 tb_deptment3
修改字段数据类型
语法：ALTER TABLE<表名> MODIFY<字段名> <数据类型>
例如：ALTER TABLE tb_dept3 MODIFY name VARCHAR(90)；
修改字段名
ALTER TABLE<表名> CHANGE<旧字段名> <新字段名><新数据类型>
例如：ALTER TABLE tb_dept3 CHANGE name name2 VARCHAR(10)；
由于不同类型的数据在机器中存储的方式及长度并不相同，修改数据类型可能会影响到数据表中已有的数据记录。因此，当数据表中已有数据时，不要轻易修改数据类型。
添加字段
语法：ALTER TABLE<表名> ADD <新字段名><数据类型> [约束条件][FIRST|AFTER已存在字段名];
FIRST是可选参数，其作用是将新添加的字段设置为表的第一个字段；
AFTER为可选参数，其作用是将新添加的字段添加到指定的“已存在字段名”的后面。
FIRST和AFTER用于指定新增字段在表中的位置，如果SQL语句中没有这个两个参数，则默认将新添加的字段设置为数据表的最后列。
例如：ALTER TABLE tb_dept3 ADD sex VARCHAR(2)；默认放在最后一列
ALTER TABLE tb_dept3 ADD sex VARCHAR(2) FIRST；放在最前列
ALTER TABLE tb_dept3 ADD sex VARCHAR(2) AFTER name；指定为在name列后。
删除字段
语法：ALTER TABLE<表名> DROP <字段名>;
例如：ALTER TABLE tb_dept3 DROP sex；
修改字段的排列位置
语法：ALTER TABLE<表名> MODIFY <字段1><数据类型>FIRST|AFTER<字段2>;
FIRST是可选参数，其作用是将新添加的字段设置为表的第一个字段；
AFTER为可选参数，其作用是将新添加的字段添加到指定的“已存在字段名”的后面。
FIRST和AFTER用于指定新增字段在表中的位置，如果SQL语句中没有这个两个参数，则默认将新添加的字段设置为数据表的最后列。
例如： ALTER TABLE tb_dept3 MODIFY sex VARCHAR(2) FIRST；放在最前列
ALTER TABLE tb_dept3 MODIFY sex VARCHAR(2) AFTER name；指定为在name列后。
更改表的存储引
根据自己的需要，选择不同的引擎，甚至可以为每一张表选择不同的存储引擎。
语法：ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>
例如：ALTER TABLE tb_dept3 ENGINE=MyISAM;
删除表的外键约束
语法：ALTER TABLE <表名> DROP FOREIGN KEY<外键约束名>
例如：ALTER TABLE tb_dept3 DROP FOREIGN KEY fk_emp_dept;

3.3 删除数据表

删除数据表之前判断是否存在
语法：DROP TABLE [IF EXISTS] 表1,表2,…,表n；
例如：DROP TABLE IF EXISTS tb_dept2;
直接删除表
语法：DROP TABLE 表1；
例如：DROP TABLE tb_dept2;
删除有外键约束的主表
ALTER TABLE tb_dept1 DROP FOREIGN KEY fk_emp_dept;

3.4 查看数据表结构

语法：DESCRIBE 表名；或者简写为 DESC 表名；
在这里插入图片描述

查看数据表详细结构语法：SHOW CREATE TABLE <表名\G>；
使用SHOW CREATE TABLE语句，不仅可以查看表创建时候的详细语句，而且还可以查看存储引擎和字符编码。加上参数\G之后，可以使结果更加直观，易于查看。
在这里插入图片描述

四、数据类型和运算符

4.1 MySQL数据类型介绍

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

数值数据类型：包括整数类型TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。浮点小数数据类型FLOAT和DOUBLE，定点小数类型DECIMAL。
日期/时间类型：包括YEAR，TIME，DATE，DATETIME和TIMESTAMP。
字符串类型：包括CHAR，VARCHAR，BINARY，VARBINARY，BLOB，TEXT，ENUM和SET等。

4.1.1 整数类型

数值型数据类型主要用来存储数字，MySQL提供了多种数值数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，其所需要的存储空间也会越大。

MySQL中的整数型数据类型以及取值范围：

类型名称	说明	存储需求	有符号	无符号
TINYINT	很小的整数	1个字节	-128~127	0~255
SMALLINT	小的整数	2个字节	32768~32767	0~65535
MEDIUMINT	中等大小的整数	3个字节	-8388608~8388607	0~16777215
INT(INTEGER)	普通大小的整数	4个字节	-2147483648~2147483647	0~4294967295
BIGINT	大整数	8个字节	-9223372036854775808~9223372036854775807	0~18446744073709551615

当我们创建表和列的时候，给列指定数据类型以及长度，例如year INT(4)，括号中显示的11是宽度，宽度和数据类型的取值范围是无关的。显示宽度只是指明MySQL最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充；如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。例如，假如向year字段插入一个数值19999，即使数值宽度超过了设定的4宽度，但19999依然在INT类型的取值范围内，所以MySQL依然显示完整的19999，而不是1999。
宽度只是用于显示，并不能限制取值范围和占用空间，如：INT(3)会占用4个字节的存储空间，并且允许的最大值也不会是999，而是INT整型所允许的最大值。
其他整数数据类型也可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值。

4.1.2 浮点数类型和定点数类型

MySQL中使用浮点数和定点数来表示小数。浮点类型有两种：单精度浮点数（FLOAT）和双精度浮点类型（DOUBLE）。定点类型只有一种：DECIMAL。浮点类型和定点类型都可以用（M,N）来表示，M称为精度，表示总共的位数，N称为标度，是表示小数的位数。
MySQL中的浮点数和定点数数据类型以及取值范围：

类型名称	说明	存储需求	有符号	无符号
FLOAT	单精度浮点数	4个字节	-3.402823466E+38~-1.175494351E-38	0和1.175494351E-38~3.402823466E+38
DOUBLE	双精度浮点数	8个字节	-1.7976931348623157E+308~-2.2250738585072014E-308	0和2.2250738585072014E-308~1.7976931348623157E+308
DECIMAL(M,D)，DEC	压缩的“严格”定点数	M+2个字节

DECIMAL类型不同于FLOAT和DOUBLE，DOUBLE实际是以串存放的，DECIMAL可能的最大取值范围与DOUBLE一样，但是其有效的取值范围由M和D的值决定。如果改变M而固定D，则其取值范围将随M的变大而变大。
不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。
例如：

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/22cfbd56f1784c23902fc78e307b1ab3.png
FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL如不指定精度默认为（10,0）。
浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点就是会引起精度问题。
在MySQL中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币，科学数据等）使用DECIMAL的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点型时需要注意，并尽量避免做浮点数比较。

4.1.3 日期和时间类型

MySQL中有多种表示日期的数据类型，主要有：DATETIME，DATE，TIMESTAMP，TIME和YEAR。
日期与时间类型：

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901~2155	1字节
TIME	HH:MM:SS	-838:59:59~838:59:59	3字节
DATE	YYYY-MM-DD	1000-01-01~9999-12-31	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59	8 字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1907-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC	4字节

YEAR
YEAR类型是一个单字节类型用于表示年，在存储时只需要1个字节。可以使用各种格式指定YEAR值，如下所示：
（1）以4位字符串或者4位数字格式表示的YEAR，范围为‘1901’~‘2155’。输入格式为‘YYYY’或者YYYY，例如，输入‘2010’或2010，插入到数据库的值均为2010。
（2）以2位字符串或者4位数字格式表示的YEAR，范围为‘00’到‘99’。‘00’~‘69’和‘70’ ~‘99’范围的值分别被转换为2000 ~ 2069和1970 ~ 1999范围的YEAR值。‘0’与‘00’的作用相同。插入超过取值范围的值将被转换为2000
（3）以2位数字表示的YEAR，范围为1~99。1 ~ 69和70 ~ 99范围的值分别被转换为2001 ~ 2069 和 1970 ~ 1999 范围的YEAR值。注意：在这里0值将被转换为0000，而不是2000.
PS：两位整数范围与两位字符串范围稍有不同，例如：插入2000年，读者可能会使用数字格式的0表示YEAR，实际上，插入数据库的值为0000，而不是所希望的2000。只有使用字符串格式的‘0’或‘00’，才可以被正确的解释为2000。非法YEAR值将被转换为0000.

下面示例辅助理解：
SQL语句
创建一个表
结果
在这里插入图片描述
SQL语句

结果

SQL语句

结果
在这里插入图片描述

TIME
TIME类型用在只需要时间信息的值，在存储时需要3个字节。格式为‘HH:MM:SS’。HH表示小时；MM表示分钟；SS表示秒。TIME类型的取值范围为-838:59:59~838:59:59，小时部分会如此大的原因是TIME类型不仅可以用于表示一天的时间（必须小于24小时），还可能是某个事件过去的时间或两个事件之间的时间间隔（可以大于24小时，或者甚至为负）。可以使用各种格式指定TIME值，如下所示：
（1）‘D HH:MM:SS’格式的字符串。还可以使用下面任何一种“非严格”的语法：‘HH:MM:SS’，‘HH:MM’，‘D HH:MM’，‘D HH’或‘SS’，这里的D表示日，可以取0~34之间的值。在插入数据库时，D被转换为小时保存，格式为“D*24+HH”。
（2）‘HHMMSS’格式的，没有间隔的字符串或者HHMMSS格式的数值，假定是有意义的时间。例如：‘101211’被理解成‘10:12：11’，但‘109712’是不合法的，因为分钟部分超过范围，存储时将会报错’。
PS1：为TIME列分配简写值时应注意：如果没有冒号，MySQL解释值时，假定最右边的两位表示秒。例如‘1112’和1112会被MySQL解释为‘00:11:12’，同样‘12’和12被解释为‘00:00:12’。相反，TIME值中如果使用冒号则被看作当天的时间，也就是说，‘11:12’会被MySQL解释为‘11:12：00’。
PS2：在使用‘D HH’格式时，小时一定要使用双位数值，如果是小于10的小时数，应在前面加0。

下面示例辅助理解：
SQL语句
在这里插入图片描述
结果

SQL语句

结果

SQL语句

结果

SQL语句（插入当前数据库所在的系统时间）

结果

DATE
DATE类型用在仅需要日期时，没有时间部分，在存储时需要3个字节。日期格式为‘YYYY-MM-DD’，‘YYYY’表示年，‘MM’表示月份，‘DD’表示日。在给DATE类型的字段赋值时，可以使用字符串或者数字类型的数据插入，只要符合DATE的日期格式即可，如下：
（1）以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串格式表示的日期，取值范围为‘1000-01-01’~‘9999-12-3’。例如，输入‘2012-12-31’或者‘20121231’，插入数据库的值都为2012-12-31.
（2）以‘YY-MM-DD’或者‘YYMMDD’字符串格式表示的日期，在这里YY表示两位的年值。包含两位年值的日期会令人模糊，因为不知道世纪。MySQL使用以下规则解释两位年值：‘00~69’范围的年值转换为‘2000 ~ 2069’；‘70 ~ 99’范围的年值转换为‘1970 ~ 1999’。例如，输入‘12-12-31’，插入的数据路的日期为2012-12-31；输入‘981231’，插入数据的日期为1998-12-31.
（3）以YY-MM-DD或者YYMMDD数字格式表示的日期，与前面相似，00-69范围的年值转换为2000~2069；70 ~ 99范围的年值转换为1970 ~ 1999。例如，输入12-12-31插入数据库的日期为2012-12-31；输入981231，插入数据的日期为1998-12-31。
（4）使用CURRENT_DATE或者NOW()，插入当前系统日期。

下图示例辅助理解：
SQL语句
在这里插入图片描述
结果

SQL语句

结果

SQL语句

结果

SQL语句

结果

SQL语句

结果
在这里插入图片描述
这是因为MySQL允许“不严格”语法：任何标点符号都可以作用日期部分之间的间隔符。例如‘98-12-21’，‘98.12.21’，‘98@12@21’是等价的，这些值可以正确的插入到数据库。

DATETIME
DATETIME类型用在需要同时包含日期和时间信息的值，在存储时需要8个字节。日期格式为‘YYYY-MM-DD HH:MM:SS’，‘YYYY’表示年，‘MM’表示月，‘DD’表示日，‘HH’表示小时，‘MM’表示分钟，'SS’表示秒。在给DATETIME类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，如下所示：
（1）以‘YYYY-MM-DD HH:MM:SS’或者‘YYYYMMDDHHMMSS’字符串格式表示的值，取值范围为‘1000-01-01 00:00:00’~‘9999-12-3 23:59:59’。
（2）以‘YY-MM-DD HH:MM:SS’或者‘YYMMDDHHMMSS’字符串格式表示的日期，在这里YY表示两位的年值。‘00~69’范围的年值转换为‘2000 ~ 2069’；‘70 ~ 99’范围的年值转换为‘1970 ~ 1999’。
（3）以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和时间，规则和上述一致。
PS：MySQL允许“不严格”语法：任何标点符号都可以作用日期部分之间的间隔符。例如‘98-12-21 11:30:45’，‘98.12.21 11+30+45’，‘98@12@21 113045’是等价的，这些值可以正确的插入到数据库。
TIMESTAMP
TIMESTAMP的显示格式与DATETIME相同，显示宽度固定在19个字符，日期格式为YYYY-MM-DD HH:MM:SS，在存储时需要4个字节。但是TIMESTAMP列的取值范围小于DATETIME的取值范围，为‘1970-01-01 00:00:01’UTC~‘2038-01-19 03:14:07’ UTC，其中UTC（Coordinated Universal Time，为世界标准时间）。
如果为一个DATETIME或TIMESTAMP对象分配一个DATE值，结果值的时间部分被设置为“00:00:00”，因为DATE值未包含时间信息。如果为一个DATE对象分配一个DATETIME或TIMESTAMP值，结果值的时间部分会被截断。
TIMESTAMP与DATETIME除了存储字节和支持的范围不同外，还有一个最大的区别就是：DATETIME在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而TIMESTAMP值的存储是以UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

下图示例辅助理解：
SQL语句
在这里插入图片描述
结果

SQL语句

结果

在数据库插入当前系统时间（当前是东八区）
设置当前时区为东十区
再次查询时，时间以东十区显示（实际数据库中存储的仍是东八区时间，如下所示）
在这里插入图片描述

4.1.4 字符串类型

字符串类型用来存储字符串数据，除了可以存储字符串数据外，还可以存储其他数据，比如图片和声音的二进制数据。字符串可以进行区分或者不区分大小写的串比较，另外，还可以进行模式匹配查找。

MySQL字符串数据类型：

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此L<=M和1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此L<2^8
TEXT	小的非二进制字符串	L+2字节，在此L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目（最大值65535）
SET	一个设置，字符串对象可以有零个或多个SET成员	1,2，3,4或8个字节，取决于集合成员的数量（最多64个成员）

VARCHAR，BLOB和TEXT类型是变长类型，对于其存储需求取决于列值的实际长度（在前面的表格中用L表示），而不是取决于类型的最大可能尺寸。例如，一个VARCHAR(10)列能保存最大长度为10个字符的一个字符串，实际的存储需要是字符串的长度L，加上1个字节以记录字符串长度。例如：对于字符“abcd”，L是4而存储要求是5个字节。

4.1.5 二进制类型

MySQL支持两类字符型数据：文本字符串和二进制字符串。

MySQL中的二进制数据类型：

类型名称	说明	存储需求
BIT(M)	位字段类型	大约（M+7)/8个字节
BINARY(M)	固定长度二进制字符串	M个字节
VARBINARY(M)	可变长度二进制字符串	M+1个字节
TINYBLOB(M)	非常小的BLOB	L+1字节，在此L<2^8
BLOB(M)	小BLOB	L+2字节，在此L<2^16
MEDIUMBLOB(M)	中等大小的BLOB	L+3字节，在此L<2^24
LONGBLOB(M)	非常大的BLOB	L+4字节，在此L<2^32

4.2 如何选择数据类型

MySQL提供了大量的数据类型，为了优化存储，提高数据库性能，在任何情况下均应使用最精确的类型。

整数和浮点数
如果不需要小数部分，则使用整数来保存数据；如果需要表示小数部分，则使用浮点数类型。对于浮点数琚列，列入的数值会对该列定义的小数位进行四舍五入。
浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类类型高，因此，如要求存储精度较高时，应选择DOUBLE类型。
浮点数和定点数
浮点数FLOAT和DOUBLE相对于定点数DECIMAL的优势是：在长度一定的情况下，浮点数能表示更大的数据范围。但是由于浮点数容易产生误差，因此对精度要求比较高时，建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的，用于定义货币等精确度要求较高的数据。在数据迁移中，FLOAT(M,D)是非标准SQL定义，数据库迁移可能会出现问题，最好不要这样使用。另外两个浮点数进行减法和比较运算时也比较容易出问题，因此在进行计算的时候，一定要小心。如果进行数值比较，最好使用DECIMAL类型。
日期和时间类型
MySQL对于不同种类的日期和时间有很多的数据类型，如果只需要记录年份，则使用YEAR类型即可，如果只记录时间，只需使用TIME类型。
如果同时需要记录日期和时间，则可以使用TIMESTAMP或者DATETIME类型。由于TIMESTAMP列的取值范围小于DATETIME的取值范围，因此存储范围较大的日期最好使用DATETIME。
TIMESTAMP也有一个DATETIME不具备的属性。默认情况下，当插入一条记录但并没有指定TIMESTAMP这个列值时，MySQL会把TIMESTAMP列设为当前的时间。因此当需要插入记录同时插入当前时间时，使用TIMESTAMP是方便的，另外TIMESTAMP在空间上比DATETIME更有效。
CHAR和VARCHAR之间的特点与选择
CHAR是固定长度字符，VARCHAR是可变长度字符；CHAR会自动删除插入数据的尾部空格，VARCHAR不会删除尾部空格。
CHAR是固定长度，所以它的处理速度比VARCHAR的速度更快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用CHAR类型，反之可以使用VARCHAR类型来实现。
对于MyISAM存储引擎最好是使用固定长度的数据类型，这样可以使整个表静态化，从而使数据检索更快，用空间换时间。
对于InnoDB存储引擎：使用可变长度的数据类型，因为InnoDB数据表的存储格式部分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照实际的长度存储，比较节省空间，所以对磁盘IO和数据存储总量比较好。
ENUM和SET
BOLB和TEXT
BLOB是二进制字符串，TEXT是非二进制字符串，两者均可存放大容量的信息。BLOB主要存储图片，音频信息等，而TEXT只能存储纯文本文件。

4.3 常见运算符介绍

运算符连接表达式中各个操作数，其作用是指明对操作数所进行的运算。

4.3.1 算术运算符

用于各类数值运算，包括加（+），减（-），乘（*），除（/），求余（或称模运算，%）。

4.3.2 比较运算符

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

MySQL中的比较运算符

运算符	作用
=	等于
<=>	安全的等于
<>(!=)	不等于
<=	小于等于
>=	大于等于
>	大于
IS NULL	判断一个值是否为null
IS NOT NULL	判断一个值是否不为null
LEAST	在有两个或多个参数时，返回最小值
GREATEST	在有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
ISNULL	与IS NULL作用相同
IN	判断一个值是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配
REGEXP	正则表达式匹配

“=”不能用于null值的判断，而“<=>”可以对null进行判断，两者都为NULL时返回值为1.
“BETWEEN AND”用法示例：SELECT 4 BETWEEN 4 AND 6;意思是4是否在4~6之间
“LEAST|”用法示例：SELECT least(2,4,5)，返回2,4，5三个数字中最下的值。
同理“GREATEST”与“LEAST”用法一致。
LIKE运算符用来匹配字符串，语法格式为：expr LIKE 匹配条件，LIKE运算符心思进行匹配时，一般使用下面两种通配符：
（1）“%”匹配任何数目的字符，甚至包括零字符。
（2）“_”只能匹配一个字符。
REGEXP运算符用来匹配较为复杂的字符串，语法格式为：expr REGEXP 匹配条件。REGEXP运算符在进行匹配时，常用的有下面几种通配符：
（1）‘^‘匹配以该字符后面的字符开头的字符串
（2）‘$’匹配以该字符后面的字符结尾的字符串
（3）’.‘匹配任何一个单字符
（4）’[…]‘匹配在方括号内的任何字符。例如，"[abc]"匹配a,b,c，为了命名字符的范围，使用一个’-’。"[a-z]"匹配任何字母，而“[0- 9]”匹配任何数字。
（5）''匹配零个或多个在它面前的字符。例如，"x"匹配任何数量的‘x’字符，“[0-9]”匹配任何数量的数字，而“.”匹配任何数量的任何字符。

4.3.3 逻辑运算符

在MySQL中，逻辑运算符的求值结果为1（TRUE），0（FALSE）和NULL。

MySQL中的逻辑运算符

运算符	作用
NOT或者！	逻辑非
AND 或者 &&	逻辑与
OR	逻辑或
XOR	逻辑异或

4.3.4 位运算符

位运算符是用来对二进制字节中的位进行测试，移位或者测试处理。

MySQL中的位运算符

运算符	作用
l	位或
&	位与
^	位异或
<<	位左移
>>	位右移
~	位取反，反转所有比特

4.3.5 运算符优先级

运算符按照优先级由低到高排列
在这里插入图片描述

五、MySQL函数

5.1 MySQL函数简介

函数表示对输入参数值返回一个具有特定关系的值，MySQL提供了大量丰富的函数，在进行数据库管理以及数据的查询和操作时会经常用到各种函数。通过对数据的处理，数据库功能可以变得更加强大，更加灵活地满足不同用户的需求。

5.2 数学函数

绝对值函数ABS(x)和返回圆周率函数PI()。
平方根函数SQRT(x)和求余函数MOD(x,y)。
获取整数函数 CEIL(x)，CEILING(x)，FLOOR(x)。
CEIL(x)与CEILING(x)意义相同，返回大于x的最小整数值。FLOOR(x)返回小于x的最大整数值。返回值都转换为BIGINT类型。
获取随机数函数RAND()和RAND(x)。
RAND(x)返回一个随机浮点数值v，范围在0到1之间（0<=v<=1.0）。若已指定一个整数参数x，则它被用作种子值，用来产生重复序列。当RAND(x)的参数相同时，将产生相同的随机数，不同的x产生的随机数值不同。
四舍五入函数ROUND(x)，ROUND(x,y)和TRUNCATE(x,y)
（1）ROUND(x,y)返回最接近于参数x的数，其值被保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位，保留的小数点左边的相应位数直接保存为0，不进行四舍五入。
例如ROUND(1.38,1)保留小数点后面1位，四舍五入的结果为1.4；ROUND(1.38,0)保留小数点后面0位，即返回四舍五入后的数值；ROUND(23.38,-1)保留小数点的左边1位，结果即为20，ROUND(232.38,-2)保留小数点左边2位，结果即为200.
（2）TRUNCATE(x,y)返回被舍去至小数点后y位的数字x，若y的值为0，则结果不带有小数点或不带有小数部分。若y设为负数，则截去（归零）x小数点左起第y位开始后面所有低位的值。
例如：TRUNCATE(1.31,1)和TRUNCATE(1.99,1)都保留小数点后1位数字，返回值分别为1.3和1.9，TRUNCATE(1.99,0)返回整数部分值1；TRUNCATE(19.99,-1)截去小数点左边第1位后面的值，并将整数部分的1位数字置0，结果为10.
符号函数SIGN(x)
SING(x)返回参数的符号，x的值为负，零或正时返回结果依次为-1,0或1。
对数运算函数LOG(x)和LOG10(x)。
角度和弧度相互转换的函数RADIANS(x)和DEGREES(x)
正弦函数SIN(x)和反正弦函数ASIN(x)
正切函数TAN(x)，反正切函数ATAN(x)，余切函数COT(x)

5.3 字符串函数

计算字符串字符数函数CHAR_LENGTH(str)和字符串长度函数LENGTH(str)
合并字符串函数CONCAT(s1,s2,…)，CONCAT_WS(x,s1,s2,…)
CONCAT(s1,s2,…)与CONCAT_WS(x,s1,s2,…)的区别在于如果sn为null的话，前者的返回结果即为null，后者会忽略null的部分，继续拼接合并其他不为null 的值作为结果返回。
替换字符串函数INSERT(s1,x,len,s2)。
字母大小写转换函数LOWER(str)和LCASE(str)。
获取指定长度的字符串函数LEFT(s,n)和RIGHT(s,n)。
填充字符串函数LPAD(s1,len,s2)和RPAD(s1,len,s2)。
删除空格函数LTRIM(s)，RTRIM(s)和TRIM(s)。
删除指定字符串函数TRIM(s1 FROM s)
删除字符串s中两端所有的子字符串s1，中间的s1字符串不删除。
重复生成字符串函数REPEAT(s,n)。
空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)。
比较字符串大小函数STRCMP(s1,s2)
获取子串函数SUBSTRING(s,n,len)和MID(s,n,len)
如果len小于1，则结果始终未空字符串。
匹配子串开始位置函数
LOCATE(str1,str)，POSITION(str1 IN str)，INSTR(str,str1)3个函数作用相同，返回子字符串str1在字符串str中的开始位置。
字符串反转函数REVERSE(s)
返回指定位置的字符串函数
ELT(N，字符串1，字符串2，字符串3，…，字符串N)，若N=1，则返回值为字符串1，若N=2，则返回值为字符串2，依次列推。若N小于1或大于参数的数目，则返回值为NULL。
返回指定字符串位置的函数FIELD(s,s1,s2,…)。
返回子串位置的函数FIND_IN_SET(s1,s2)。
选取字符串的函数MAKE_SET(x,s1,s2,…)。

5.4 时间和日期函数

获取当前日期的函数CURDATE()，CURRENT_DATE()，CURRENT_DATE()+0可以将当前日期值转换为数值型。
获取当前时间的函数CURTIME()，CURRENT_TIME()，CURRENT_TIME()+0可以将当前日期值转换为数值型。
获取当前时间和日期的函数CURRENT_TIMESTAMP()，LOCALTIME()，NOW()，SYSDATE()
UNIX时间戳函数
UNIX_TIMESTAMP(data)若无参数调用，则返回一个Unix时间戳（‘1970-01-01 00:00:00’ GMT之后的秒数）作为无符号整数。若用date来调用UNIX_TIMESTAMP()，它会将参数值以‘1970-01-01 00:00:00’GMT后的秒数的形式返回。date可以是一个DATE字符串，DATETIME字符串，TIMESTAMP或一个当地时间的YYMMDD或YYYYMMDD格式的数字。
FROM_UNIXTIME(data)函数把UNIX时间戳转换为普通格式的时间，与UNIX_TIMESTAMP(data)函数互为反函数。
返回UTC日期的函数UTC_DATE()。
返回UTC时间的函数UTC_TIME()。
获取月份的函数MONTH(data)，MONTHNAME(date)
获取星期的函数DAYNAME(d)，DAYOFWEEK(d)和WEEKDAY(d)
获取星期数的函数WEEK(d)，WEEKOFYEAR(d)
获取天数的函数DAYOFYEAR(d)和DAYOFMONTH(d)
获取年份，季度，小时，分钟和秒钟函数
YEAR(date)，‘00~69’转换为‘2000 ~ 2069’，‘70 ~ 99’转换为‘1970 ~ 1999’
QUARTER(date)
MINUTE(time)
SECOND(time)
获取日期的指定值的函数EXTRACT(type FROM date)
时间和秒钟转换函数
TIME_TO_SEC(time)返回已转化为秒的time参数。转换公式为：小时3600+分钟60+秒
SEC_TO_TIME(seconds)返回被转化为小时，分钟和秒数的seconds参数值
计算日期和时间函数
DATE_ADD()，ADDDATE()，DATE_SUB()，SUBDATE()，ADDTIME()，SUBTIME()，DATE_DIFF()
将日期和时间格式化函数DATE_FORMAT(date,format)

5.5 条件判断函数

条件判断函数也称为控制流程函数，根据满足的条件的不同，执行相应的流程。

IF(expr,v1,v2)函数
IFNULL(v1,v2)函数
CASE函数

5.6 系统信息函数

获取MySQL版本号，连接数和数据库名的函数
VERSION()
CONNECTION_ID()
SHOW PROCESSLIST或SHOW FULL PROCESSLIST：查看有哪些线程，显示信息更全
DATABASE()和SCHEMA()：查询当前数据库
获取用户名的函数
USER()，CURRENT_USER，CURRENT_USER()，SYSTEM_USER()，SESSION_USER()
获取字符串的字符集和排序方式的函数
CHARSET(str)
COLLATION(str)
获取最后一个自动生成ID值的函数
LAST_INSERT_ID()

5.7 加密函数

加密函数PASSWORD(str)
加密函数MD5(str)
加密函数ENCODE(str,pswd_str)
解密函数DECODE(crypt_str,pswd_str)

5.8 其他函数

格式化函数FORMAT(x,n)
不同进制的数字进行转换的函数CONV(N,from_base,to_base)
IP地址与数字互相转换的函数INET_ATON(expr)，INET_NTOA(expr)
加锁函数和解锁函数
GET_LOCK(str,timeout)
RELEASE_LOCK(str)
IS_FREE_LOCAK(str)
IS_USEE_LOCAK(str)
重复执行指定操作的函数
BENCHMARK(count,expr)
改变字符集的函数
CONVERT(…USING…)
改变数据类型的函数
CAST(x,AS type)
CONVERT(x,type)

六、数据的增删改查

6.1 基本查询数据

MySQL从数据表中查询数据的基本语句为SELECT语句。SELECT语句的基本格式是：
SELECT {*|<字段列表>}
[FROM<表1>,<表2>…
WHERE<表达式>
GROUP BY<>
HAVING
ORDER BY<>
LIMIT
]

详细解释：

{*|<字段列表>}包含星号通配符选字段列表，表示查询的字段，其中字段列至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不要加逗号。
FROM<表1>,<表2>…，表1和表2表示查询数据的来源，可以是单个或者多个
WHERE子句是可选项，如果选择该项，将限定查询行必须满足的查询条件
GROUP BY<字段>，该子句告诉MySQL如何显示查询出来的数据，并按照指定的字段分组
[ORDER BY<字段>]，该子句告诉MySQL按什么样的顺序显示查询出来的数据，可以进行的排序有：升序（ASC），降序（DESC）
[LIMIT]，该子句告诉MySQL每次显示查询出来的数据条数。

6.2 单表查询数据

查询所有字段
SELECT * FROM 表名;
SELECT 字段名2，字段名3，字段名4，…，字段名n FROM 表名；
查询单个字段
SELECT 字段名2 FROM 表名；
查询指定记录（WHERE提供查询条件）
SELECT 字段名1，字段名2，…，字段名n FROM 表名 WHERE 查询条件；
查询IN或NOT IN范围内数据
SELECT 字段名1，字段名2，…，字段名n FROM 表名 WHERE 字段名 IN()；
SELECT 字段名1，字段名2，…，字段名n FROM 表名 WHERE 字段名 NOT IN()；
带BETWEEN AND的范围查询
SELECT 字段名1，字段名2，…，字段名n FROM 表名 WHERE 字段名 BETWEEN 开始值 AND 结束值；
带LIKE的字段匹配查询（用%或_通配符）
SELECT 字段名1，字段名2，…，字段名n FROM 表名 WHERE 字段名 LIKE ‘某些字符’；
查询空值
SELECT 字段名1，字段名2，…，字段名n FROM 表名 WHERE 字段名 IS NULL；
带ADN的多条件查询
SELECT 字段名1，字段名2，…，字段名n FROM 表名 WHERE 条件表达式1 AND 条件表达式2；
带OR的多条件查询
SELECT 字段名1，字段名2，…，字段名n FROM 表名 WHERE 条件表达式1 OR 条件表达式2；
某些情况下OR操作符和IN操作符使用后的结果是一样的，他们可以实现相同的功能。但是使用IN操作符使得检索语句更加简洁明了，并且IN执行的速度要快于OR。
OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。
查询结果不重复
-SELECT DISTINCT(字段名) FROM 表名；
对查询结果排序（升序ASC，降序DESC，默认是ASC，不用特意在后面标注）
SELECT 字段名 FROM 表名 ORDER BY 字段名1，字段名2，字段名n；
在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对二列进行排序。同理后面列名也是一样的。
分组查询
[GROUP BY 字段][HAVING<条件表达式>]
（1）GROUP BY关键字通常和聚合函数一起使用，MAX()，MIN()，COUNT()，SUM()，AVG()。
（2）GROUP_CONCAT()函数可以将每个分组中的各个字符值显示出来。
（3）HAVING关键字与WHERE关键字都是用来过滤数据的，两者的主要区别在于，HAVING在数据分组之后进行过滤来选择分组，而WHERE在分组之前用来选择记录。另外WHERE排除的记录不再包括在分组中。
（4）在GROUP BY子句中使用WITH ROLLUP，可以在查询出的分组记录之后增加一条记录，该记录就是计算查询出的所有记录的总和，即统计记录数量。但是ROLLUP不可以与ORDER BY子句一起使用，两个是互斥的。
（5）GROUP BY可以对多个字段进行分组，MySQL根据多字段的值来进行层次分组，分组层次是从左到右，即先按第一个字段分组，然后在第1个字段值相同的记录中，再根据第2个字段的值进行分组…依次类推。
LIMIT限制查询结果的数量
LIMIT[位置偏移量，]行数
第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1…依次类推）；第二个参数“行数”指示返回的记录条数。
带一个参数的LIMIT指定从查询结果的首行开始，唯一的参数表示返回的行数，即“LIMIT n”与“LIMIT 0,n”等价。带两个参数的LIMIT可以返回从任何一个位置开始的指定的行数。

6.3 使用集合函数查询

COUNT()函数
（1）COUNT(*)计算表中总的行数，不管某列有数值或者为空值。
（2）COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。
SUM()函数
SUM()函数是求总和的函数，忽略列值为NULL的行。
AVG()函数
AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。
MAX()函数
MAX()函数返回指定列中的最大值
MIN()函数
MIN()返回查询列中的最小值。

6.4 连接查询

内连接查询
INNER JOIN
外连接查询
（1）LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。
（2）RIGHT JOIN（右连接）：返回包括右表中的所有记录和右表中连接字段相等的记录。

6.5 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。子查询可以添加到SELECT，UPDATE，DELETE语句中，而且可以进行多层嵌套。

带ANY，SOME关键字的查询
ANY关键字接在一个比较操作符的后面，表示若与子查询返回的任何值比较为TRUE，则返回TRUE。
例如：SELECT num1 FROM tb1 WHERE num1 > ANY(SELECT num2 FROM tbl2)
在子查询中，返回的是tbl2表的所有num2列结果，然后将tbl1中的num1列的值与之进行比较，只要大于num2列的任意一个数即为符合条件的结果。
带ALL关键字的查询
ALLA关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。
例如：SELECT num1 FROM tb1 WHERE num1 > ALL(SELECT num2 FROM tbl2)
在子查询中，返回的是tbl2的所有num2列结果，然后将tbl1中的num1列的值与之进行比较，返回tbl1表中比tbl2表num2列所有值都大的值。
带EXISTS关键字的子查询
（1）EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询，如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。
（2）NOT EXISTS与EXISTS使用方法相同，返回的结果相反。子查询如果至少返回一行，那么NOT EXISTS的结果为false，此时外层查询语句将不进行查询；如果子查询没有返回任何行，那么NOT EXISTS返回的结果为true，此时外层语句将进行查询。
（3）EXISTS和NOT EXISTS的结果只取决于是否会返回行，而不取决于这些行的内容，所以子查询输入列表通常是无关紧要的。
举例：SELECT * FROM fruits WHERE EXISTS(SELECT s_name FROM suppliers WHERE s_id = 109)
查询suppliers表中是否存在s_id=109的供应商，如果存在，则查询fruits表中的记录
带IN关键字的子查询
IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作

提示：所有有关子查询的操作过程，先执行最内层子查询，再执行外层查询，最内层子查询的结果作为外部查询的比较条件。

6.6 合并查询结果

UNION或者UNION ALL
两者区别：UNION ALL 的功能是不删除重复行，加上ALL 关键字语句执行时所需要的资源少，所以尽可能的使用它，因此知道有重复行但是想保留这些行，确定查询结果中不会有重复数据或者不需要去掉重复数据的时候，应当使用UNION ALL 以提高查询效率。

6.7 为表和字段取别名

表名 [AS] 表别名
列名 [AS] 列别名

6.8 使用正则表达式查询

正则表达式常用字符匹配列表：

选项	说明	列子	匹配值示例
^	匹配文本的开始字符	'^b’匹配以字母b开头的字符串	book,big,banana,bike
$	匹配文本的结束字符	'st$'匹配以st结尾的字符串	test,resist,persist
.	匹配任何单个字符	'b.t’匹配以任何b和t之间有一个字符	bit,bat,but,bite
*	匹配零个或多个在它前面的字符	'f*n’匹配字符n前面有任意个字符f	fn,fan,faan,abcn
+	匹配前面的字符1次或多次	'ba+'匹配以b开头后面紧跟至少有一个a	ba,bay,bare,battle
<字符串>	匹配包含指定的字符串的文本	‘fa’	fan,afa,faad
[字符集合]	匹配字符集中的任何一个字符	'[xz]'匹配x或者z	dizzy,zebra,x-ray,extra
[^]	匹配不在括号中的任何字符	[^abc]'匹配任何不包含a，b，c的字符串	desk,fox,f8ke
字符串{n,}	匹配前面的字符串至少n次	b{2}匹配2个或更多的b	bbb,bbbb,bbbbbbb
字符串{n,m}	匹配前面的字符串至少n次，至多m次。如果n为0，此参数为可选参数	b[2,4]匹配最少2个，最多4个b	bb,bbb,bbbb

SQL举例：
（1）查询字符“^”匹配以特定字符串或者字符串开头的文本。
SELECT * FROM 表名 WHERE 列名 REGEXP ‘^b’；（查询以b开头的数据）

（2）查询“$”匹配以特定字符或者字符串结尾的文本。

SELECT * FROM 表名 WHERE 列名 REGEXP ‘y$’；（查询以y结尾的记录）

（3）用符号“.”来替代字符串中的任意一个字符
SELECT * FROM 表名 WHERE 列名 REGEXP ‘a.g’；
查询语句中“a.g”指定匹配字符中要有字母a和g，且两个字母之间包含单个字符，并不限定匹配的字符的位置和所在查询字符串的总长度。
（4）使用“”和“+”来匹配多个字符
星号“”匹配前面的字符任意多次，包括0次。加好“+”匹配前面的字符至少一次。
SELECT * FROM 表名 WHERE 列名 REGEXP ‘^ba*’；(必须是以b开头，a可有可无，因为星号是包含零次的)
SELECT * FROM 表名 WHERE 列名 REGEXP ‘^ba+'；(必须是以b开头，且a字母必须出现一次)
（5）匹配指定字符串
使用“|”匹配多个字符串
SELECT * FROM 表名 WHERE 列名 REGEXP 'on|ap’；（查询包含字符串“on”或者“ap”的记录）
（6）匹配指定字符中的任意一个
使用方括号[]指定一个字符集合，只匹配其中任意一个字符，即为所查找的文本
SELECT * FROM 表名 WHERE 列名 REGEXP ‘[ot]'；（查询字段中包含字母o或者t的记录）
匹配集合写法：例如：[1-9]匹配1到9之间的任意数字，[a-z]匹配a~z之间的任意字母
（7）匹配指定字符以外的字符
“[^字符集合]”匹配不在指定集合中的任何字符
SELECT * FROM 表名 WHERE 列名 REGEXP ‘[^a-p1-9]’；（匹配出字母a-p和数字1-9之外的记录）
（8）使用{n,}或者{n,m}来指定字符串连续出现的次数
子字符串{n,}表示至少匹配n次前面的字符：“字符串{n,m}”表示匹配前面的字符串不少于n次，不多于m次。例如，a{2,}表示字母a连续出现至少2次，也可以大于2次；a{2,4}表示字母a连续出现最少2次，最多不超过4次。
SELECT * FROM 表名 WHERE 列名 REGEXP 'x{2,}’；

6.9 数据的新增

（1）语法：INSERT INTO 表名（列名）VALUES (数值)；或者INSERT INTO 表名 VALUES (数值)；保证数值插入顺序和字段名一致，就不用再写字段名。
（2）INSERT INTO person(name,age,info) VALUES(‘HSUDH’,28,‘SHUAHD’),(‘HSUQW’,21,‘AHD’),(‘H’,47,‘WEQ’)
比
INSERT INTO person(name,age,info) VALUES(‘HSUDH’,28,‘SHUAHD’)；INSERT INTO person(name,age,info) (‘HSUQW’,21,‘AHD’)效率更高。因为MySQL执行单条INSERT语句插入多行数据，比使用多条INSERT语句快。所以在插入多条记录时，最好选择使用单条INSERT语句的方式插入。

6.10 数据的修改

（1）语法：UPDATE 表名 SET 列名1=value1,列名2=value2，列名3=value3,…,列名n=valuen WHERE (查询条件)

6.11 数据的删除

（1）语法：DELETE FROM 表名 [WHERE （查询条件）]
（2）如果想删除表中的所有记录，还可以使用TRUNCATE TABLE语句，TRUNCATE将直接删除原来的表并重新创建一个表，其语法结构为TRUNCATE TABLE table_name。TRUNCATE 直接删除而不是删除记录，因此执行速度比DELETE快。

七、索引

7.1 索引简介

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。

7.1.1 索引的含义和特点

索引是一个单独的，存储在磁盘上的数据库结构，他们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
例如：数据库中有2万条记录，现在要执行这样一个查询：SELECT * FROM 表名 WHERE num = 10000。如果没有索引，必须遍历整个表，直到num等于10000的这一行被找到为止；如果在num列上创建索引，MySQL不需要任何扫描，直接在索引里面找10000，就可以得知这一行的位置。
索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。MySQL中索引的存储类型有两种：BTREE和HASH，具体和表的存储引擎相关；MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

索引的优点：

通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的查询速度，这也是创建索引的最主要原因。
在实现数据的参考完整性方面，可以加速表和表之间的连接。
在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排除的时间。

索引的缺点：

创建索引和维护索引好费时间，并且随着数据量的增加所耗费的时间也会增加。
缩阴需要站磁盘空间，除了数据表占数据空间之外，每个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
当对表中的数据进行增加，删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

7.1.2 索引的分类

（1）普通索引和唯一索引
普通索引是MySQL中基本索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引，索引列的值必须唯一，但允许空值。如果是组合索引，则列值组合必须唯一。
主键索引是一种特殊的唯一索引，不允许有空值。

（2）单列索引和组合索引
单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会使用、使用组合索引时遵循最左前缀集合。

（3）全文索引
全文索引类型为FULLTEXT，在定义索引的的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。MySQL中只有MyISAM存储引擎支持全文索引。

（4）空间索引
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是：GEOMTRY，POINT，LINESTRING，POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

7.1.3 索引的设计原则

索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，而且会有影响INSERT，DELETE，UPDATE等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新。
避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而堆经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
数据量小的表最好不要使用索引，由于数据少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
在条件表达式中经常用到的不同值比较多的列上建立索引，在不同值少的列上不要建立索引。比如学生表的性别字段。
当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
在频繁进行排序或分组的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

7.2 创建索引

（1）语法：
CREATE TABLE table_name[col_name_data_type]
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length]) [ASC|DESC]

UNIQUE，FULLTEXT，SPATIAL为可选参数，分别表示唯一索引，全文索引和空间索引；INDEX和KEY为同义词，两者作用相同，用来指定创建索引；col_name为需要创建索引的字段列，index_name指定索引的名称，为可选参数，如果不指定，MySQL默认col_name为索引值，length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；ASC或DESC指定升序户降序的索引值存储。

提示：组合索引遵从“最左前缀”，利用索引中最左边的列集来匹配行，这样的列表称为最左前缀。例如这有由id,name,age3个字段组合的索引，索引行中按id/name/age的顺序存放，索引可以由下面字段组合：（id,name,age），（id,name）或id。如果列不构成索引最左面的前缀，MySQL不能使用局部索引，如age或者（name,age）组合则不能使用索引。

（2）查看某一个查询语句是否使用索引
explain SELECT查询语句\G；

（3）查看整个表的索引结构
SHOW INDEX FROM 表名\G