MySQL数据库及优化

一、简介

MySQL 是一个关系型数据库，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

• MySQL 是开源的，目前隶属于 Oracle 旗下产品。
• MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
• MySQL 使用标准的 SQL 数据语言形式。
• MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
• MySQL 对PHP有很好的支持，PHP 是目前最流行的 Web 开发语言。
• MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
• MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

SQL：Structure Query Language(结构化查询语言) 是所有关系型数据库都遵守的语言，为了满足各数据库特定业务需要，每一个数据库厂商在尊守SQL的基础上，对SQL语法做了扩展（此部分每个数据库是不一样）

SQL分为以下几种：

• DQL：Data Query Language，数据查询语言，例如select
• DML：Data Manipulation Language，数据操作语言，例如update，delete，insert
• DDL；Data Definition Language，数据定义语言，例如create，alter，drop
• DCL：Data Control Language，数据控制语言，例如grant，invoke
• TCL：Transaction Control Language，事务控制语言，例如commit，rollback，savepoint

二、数据库操作

1、创建数据库

CREATE DATABASE 数据库名;

2、删除数据库

drop database <数据库名>;

3、选择数据库

在你连接到 MySQL 数据库后，可能有多个可以操作的数据库，所以你需要选择你要操作的数据库。

use 数据库名;

三、数据类型

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

1、数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(integer、smalling、decimal和 numeric)，以及近似数值数据类型(float、real和 double precision)。

关键字int是integer的同义词，关键字dec是decimal的同义词。

bit数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 tinyint、mediumint和 bigint。下面的表显示了需要的每个整数类型的存储和范围。

---

2、日期和时间类型

表示时间值的日期和时间类型为datetime、date、timestamp、time和year。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

3、字符串类型

字符串类型指char、varchar、binary、varbinary、blob、text、enum和set。

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 char(30) 就可以存储 30 个字符。

char和 varchar类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

binary和 varbinary类似于 char和 varchar，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

blob是一个二进制大对象，可以容纳可变数量的数据。有 4 种 blob类型：tinyblob、blob、mediumblob和 longblob。它们区别在于可容纳存储范围不同。

有 4 种 text类型：tinytext、text、mediumtext和 longtext。对应的这 4 种 text 类型，可存储的最大长度不同，可根据实际情况选择。

四、数据表操作

1、创建数据表

创建MySQL数据表需要以下信息：

• 表名
• 表字段名
• 定义每个表字段

以下为创建MySQL数据表的SQL通用语法：

CREATE TABLE table_name (column_name column_type);

以下例子中我们将在 RUNOOB 数据库中创建数据表user：

CREATE TABLE IF NOT EXISTS `user`(
   `uid` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(50) NOT NULL,
   `sex` VARCHAR(10) NOT NULL,
   `birthday` DATE,
   PRIMARY KEY ( `uid` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

实例解析：

• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
• PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键（联合主键），列间以逗号分隔。
• ENGINE 设置存储引擎，CHARSET 设置编码。

2、删除数据表

MySQL中删除数据表是非常容易操作的，但是你在进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。

DROP TABLE table_name ;

3、修改数据表

当我们需要修改数据表名或者修改数据表字段时，就需要使用到ALTER命令。

1.增删改表字段

如下命令使用了 ALTER 命令及 DROP 子句来删除user表的 address字段：

ALTER TABLE user  DROP address;

如果数据表中只剩余一个字段则无法使用DROP来删除字段。

使用 ADD 子句来向数据表中添加列，如下实例在表 user中添加 address 字段，并定义数据类型:

ALTER TABLE user ADD address VARCHAR;

如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

ALTER TABLE user ADD id INT FIRST;
ALTER TABLE user ADD address VARCHAR AFTER id;

FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

2.修改字段类型及名称

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

ALTER TABLE user MODIFY c CHAR(10);

使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。

ALTER TABLE user CHANGE i j BIGINT;

ALTER TABLE user CHANGE j j INT;

3.ALTER TABLE 对 Null 值和默认值的影响

当你修改字段时，你可以指定是否包含值或者是否设置默认值。

以下实例，指定字段 j 为 NOT NULL 且默认值为100 。

ALTER TABLE user MODIFY j INT NOT NULL DEFAULT 100;

如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。

4.修改字段默认值

可以使用 ALTER 来修改字段的默认值，尝试以下实例：

ALTER TABLE user ALTER i SET DEFAULT 1000;

可以使用 ALTER 命令及 DROP子句来删除字段的默认值，如下实例：

ALTER TABLE user ALTER i DROP DEFAULT;

修改数据表类型，可以使用 ALTER 命令及 TYPE 子句来完成。将表 testalter_tbl 的类型修改为 MYISAM ：

ALTER TABLE user ENGINE = MYISAM;

**注意：**查看数据表类型可以使用 SHOW TABLE STATUS 语句。

5.修改表名

如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。

尝试以下实例将数据表 user重命名为 users：

ALTER TABLE user RENAME TO users;

五、数据操作

1、插入数据

MySQL 表中使用 INSERT INTO SQL语句来插入数据。

INSERT INTO table_name ( field1, field2,...fieldN )
    VALUES ( value1, value2,...valueN );

如果数据是字符型，必须使用单引号或者双引号，如：“value”。

2、查询数据

MySQL 数据库使用SQL SELECT语句来查询数据。

SELECT column_name,column_name
FROM table_name
[WHERE]
[ORDER BY]
[LIMIT N]

• 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
• SELECT 命令可以读取一条或者多条记录。
• 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
• 你可以使用 WHERE 语句来包含任何条件。
• 你可以使用 LIMIT 属性来设定返回的记录数。
• 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

3、where子句

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

• 查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
• 你可以在 WHERE 子句中指定任何条件。
• 你可以使用 AND 或者 OR 指定一个或多个条件。
• WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
• WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

以下为操作符列表，可用于 WHERE 子句中。

下表中实例假定 A 为 10, B 为 20

如果我们想在 MySQL 数据表中读取指定的数据，WHERE 子句是非常有用的。

使用主键来作为 WHERE 子句的条件查询是非常快速的。

如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

4、update更新

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE]

• 可以同时更新一个或多个字段。
• 可以在 WHERE 子句中指定任何条件。
• 可以在一个单独表中同时更新数据。

当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

5、delete删除

你可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。

DELETE FROM table_name [WHERE]

• 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
• 你可以在 WHERE 子句中指定任何条件
• 您可以在单个表中一次性删除记录。

当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。

6、like子句

LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• 你可以在 WHERE 子句中指定任何条件。
• 你可以在 WHERE 子句中使用LIKE子句。
• 你可以使用LIKE子句代替等号 =。
• LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
• 你可以使用 AND 或者 OR 指定一个或多个条件。
• 你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

7、union操作符

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

参数：

• expression1, expression2, … expression_n: 要检索的列。
• tables: 要检索的数据表。
• WHERE conditions: 可选， 检索条件。
• DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没影响。
• ALL: 可选，返回所有结果集，包含重复数据。

8、排序

可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

• 可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
• 可以设定多个字段来排序。
• 可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
• 可以添加 WHERE…LIKE 子句来设置条件。

9、分组

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

示例:

表数据如下：

+----+--------+---------------------+--------+
| id | name   | date                | signin |
+----+--------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1   |
|  2 | 小王 | 2016-04-20 15:25:47 |      3   |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2   |
|  4 | 小王 | 2016-04-07 15:26:14 |      4   |
|  5 | 小明 | 2016-04-11 15:26:40 |      4   |
|  6 | 小明 | 2016-04-04 15:26:54 |      2   |
+----+--------+---------------------+--------+

使用group by排序：

SELECT name, COUNT(*) 'count', SUM(signin) 'sign_count' FROM   employee_tbl GROUP BY name;

结果如下：

name	count	sign_count
小明		3		7
小王		2		7
小丽		1		2

with rollup

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计，即对列进行一次累加。

SELECT name, COUNT(*) 'count', SUM(signin) 'sign_count' FROM   employee_tbl GROUP BY name WITH ROLLUP;

name	count	sign_count
小丽		1		2
小明		3		7
小王		2		7
null	  6		  16

其中记录 NULL 表示所有人的登录次数。

coalesce

可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

以下实例中如果名字为空我们使用总数代替：

SELECT coalesce(name, '总数') 'name', COUNT(*) 'count', SUM(signin) 'sign_count' FROM   employee_tbl GROUP BY name WITH ROLLUP;

name	count	sign_count
小丽		1		2
小明		3		7
小王		2		7
总数		6		16

10、多表查询

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接）：获取两个表中字段匹配关系的记录。
• **LEFT JOIN（左连接）：**获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

除了join连接查询，多表查询还有一种查询方式：等值查询

1.等值查询

#查询雇员表中员工编号，姓名，职务，工资，及雇员的部门编号，部门名称，部门地址
select 
	emp.empno, emp.ename, emp.job, emp.sal, dept.deptno, dept.dname, dept.loc
from 
	emp, dept
where 
	emp.deptno=dept.deptno;

2.内连接

inner join查询与等值查询完全一样。

#查询雇员表中员工编号，姓名，职务，工资，及雇员的部门编号，部门名称，部门地址
select 
	e.empno ,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc
from 
	emp e 
	inner join dept d on e.deptno=d.deptno;

3.外连接

外连接：分为左向外连接、右向外连接、完全外连接。

左连接

#显示所有的部门以及部门的所有员工(左接接实现)
select 
	d.deptno,d.dname,d.loc,e.empno,e.ename,e.job,e.sal
from 
	dept d 
	left join emp e on d.deptno=e.deptno;

右连接

#显示所有的部门以及部门的所有员工(右接接实现)
select 
	d.deptno,d.dname,d.loc,e.empno,e.ename,e.job,e.sal
from 
	emp e 
	right join dept d on e.deptno=d.deptno;

完全外连接

获取左表与右表的所有记录。

#显示emp表所有数据及部门表所有数据
select 
	d.deptno,d.dname,d.loc,e.empno,e.ename,e.job,e.sal
from 
	emp e 
	full join dept d on e.deptno=d.deptno;

11、null值处理

使用 SELECT 命令及 WHERE 子句来读取数据表中的数据, 当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

为了处理这种情况，MySQL提供了三大运算符:

• IS NULL :当列的值是 NULL,此运算符返回 true。
• IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
• <=> :比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

关于 NULL 的条件比较运算是比较特殊的。不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。

MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

函数：

ifnull(exp1, exp2)

如果exp1不为null，则返回exp1的值，如果exp1为null，则返回exp2的值。

12、正则表达式

MySQL 支持正则表达式的匹配， 使用 REGEXP 操作符来进行正则表达式匹配。

下表中的正则模式可应用于 REGEXP 操作符中。

了解以上的正则需求后，我们就可以根据自己的需求来编写带有正则表达式的SQL语句。以下我们将列出几个小实例(表名：person_tbl )来加深我们的理解：

查找name字段中以’st’为开头的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以’ok’为结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含’mar’字符串的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

13、事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务必使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

• BEGIN 或 START TRANSACTION 显式地开启一个事务；
• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier 把事务回滚到标记点；
• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

六、索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，而且建立索引会占用磁盘空间的索引文件。

1、添加

create index indexname on tablename(字段名)

alter table tablename add index indexname(字段名)

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

2、删除

drop index indexname on tablename

3、类型

B树索引、哈希索引

4、分类

单列索引（唯一索引、主键索引、普通索引），组合索引

唯一索引的创建：

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

ALTER table mytable ADD UNIQUE [indexName] (username(length))

5、优点

• 加快数据的查询速度
• 减少IO操作，提升查询性能

6、缺点

• 占用物理内存
• 索引表的维护需要一定的性能开销与时间
• 对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度

7、使用场景

• 频繁需要搜索的字段
• 在表与表连接的条件语句上
• 经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列
• 经常使用where子句的字段

8、非使用场景

• 查询中很少用到的字段
• 数据很少的表
• 表中需要频繁操作的字段
• 重复值多的字段
• 字段的数据类型大的字段
• 查询字段进行null值判断
• 模糊查询like中“%”开头的字符串不会使用索引

9、原理

根据建立索引的字段建立索引表，存放字段值和对应的物理地址，从而可以在搜索的时候根据字段值搜索索引表到物理地址直接访问数据

七、函数

1、聚合函数

聚合函数是平时比较常用的一类函数，这里列举如下：

• COUNT(col)   统计查询结果的行数

• MIN(col)   查询指定列的最小值

• MAX(col)   查询指定列的最大值

• SUM(col)   求和，返回指定列的总和

• AVG(col)   求平均值，返回指定列数据的平均值

2、数值型函数

数值型函数主要是对数值型数据进行处理，得到我们想要的结果，常用的几个列举如下：

• ABS(x)   返回x的绝对值

• BIN(x)   返回x的二进制

• CEILING(x)   返回大于x的最小整数值

• EXP(x)   返回值e（自然对数的底）的x次方

• FLOOR(x)   返回小于x的最大整数值

• GREATEST(x1,x2,…,xn)   返回集合中最大的值

• LEAST(x1,x2,…,xn)   返回集合中最小的值

• LN(x)   返回x的自然对数

• LOG(x,y)   返回x的以y为底的对数

• MOD(x,y)   返回x/y的模（余数）

• PI()   返回pi的值（圆周率）

• RAND()   返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值

• ROUND(x,y)   返回参数x的四舍五入的有y位小数的值

• TRUNCATE(x,y)   返回数字x截短为y位小数的结果

3、字符串函数

字符串函数可以对字符串类型数据进行处理，在程序应用中用处还是比较大的。

• LENGTH(s)   计算字符串长度函数，返回字符串的字节长度

• CONCAT(s1,s2…,sn)   合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个

• INSERT(str,x,y,instr)   将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果

• LOWER(str)   将字符串中的字母转换为小写

• UPPER(str)   将字符串中的字母转换为大写

• LEFT(str,x)   返回字符串str中最左边的x个字符

• RIGHT(str,x)   返回字符串str中最右边的x个字符

• TRIM(str)   删除字符串左右两侧的空格

• REPLACE   字符串替换函数，返回替换后的新字符串

• SUBSTRING   截取字符串，返回从指定位置开始的指定长度的字符换

• REVERSE(str)   返回颠倒字符串str的结果

4、日期和时间函数

• CURDATE 和 CURRENT_DATE   两个函数作用相同，返回当前系统的日期值

• CURTIME 和 CURRENT_TIME   两个函数作用相同，返回当前系统的时间值

• NOW 和 SYSDATE   两个函数作用相同，返回当前系统的日期和时间值

• UNIX_TIMESTAMP   获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数

• FROMUNIXTIME   将 UNIX 时间戳转换为时间格式，与UNIXTIMESTAMP互为反函数

• MONTH   获取指定日期中的月份

• MONTHNAME   获取指定日期中的月份英文名称

• DAYNAME   获取指定曰期对应的星期几的英文名称

• DAYOFWEEK   获取指定日期对应的一周的索引位置值

• WEEK   获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53

• DAYOFYEAR   获取指定曰期是一年中的第几天，返回值范围是1~366

• DAYOFMONTH   获取指定日期是一个月中是第几天，返回值范围是1~31

• YEAR   获取年份，返回值范围是 1970〜2069

• TIMETOSEC   将时间参数转换为秒数

• SECTOTIME   将秒数转换为时间，与TIMETOSEC 互为反函数

• DATE_ADD 和 ADDDATE   两个函数功能相同，都是向日期添加指定的时间间隔

• DATE_SUB 和 SUBDATE   两个函数功能相同，都是向日期减去指定的时间间隔

• ADDTIME   时间加法运算，在原始时间上添加指定的时间

• SUBTIME   时间减法运算，在原始时间上减去指定的时间

• DATEDIFF   获取两个日期之间间隔，返回参数 1 减去参数 2 的值

• DATE_FORMAT   格式化指定的日期，根据参数返回指定格式的值

• WEEKDAY   获取指定日期在一周内的对应的工作日索引

5、流程控制函数

流程控制类函数可以进行条件操作，用来实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台，列举如下：

• IF(test,t,f)   如果test是真，返回t；否则返回f

• IFNULL(arg1,arg2)   如果arg1不是空，返回arg1，否则返回arg2

• NULLIF(arg1,arg2)   如果arg1=arg2返回NULL；否则返回arg1

• CASE WHEN[test1] THEN [result1]…ELSE [default] END   如果testN是真，则返回resultN，否则返回default

• CASE [test] WHEN[val1] THEN [result]…ELSE [default]END   如果test和valN相等，则返回resultN，否则返回default

6、加密函数

加密函数主要用于对字符串进行加密，常用的几个列举如下：

• ENCRYPT(str,salt)   使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str

• ENCODE(str,key)   使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储

• MD5()   计算字符串str的MD5校验和

• PASSWORD(str)   返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。

• SHA()   计算字符串str的安全散列算法(SHA)校验和

八、约束

1、约束条件

限制表中的数据，保证添加到数据表中的数据准确和可靠性！凡是不符合约束的数据，插入时就会失败！ 约束条件在创建表时可以使用， 也可以修改表的时候添加约束条件。

• 主键约束（primary key）：一般建表是需要添加主键约束的（主键是确定表数据的唯一标识） 主键约束要求：非空 + 唯一

• 非空约束（not null）：值不能为空（只能把语句放在声明属性的后面）

• 检查约束（check）：验证属性值的有效性

• 外键约束（foreign key）：表中的属性值关联外部表的某一个属性值（被关联的表此时不能被删除）

• 唯一约束（unique）：值不重复（可以为空值，但只能有一个空值）

• 默认约束（default）：保证该字段有默认值

2、添加约束

1.创建表时

添加列级约束：

直接在字段名和类型后面追加 约束类型即可。 只支持：默认、非空、主键、唯一 。

CREATE TABLE user( 
    id INT PRIMARY KEY,#主键
    name VARCHAR(20) NOT NULL,#非空
    age INT DEFAULT 18,#默认约束
);

添加表级约束：

CREATE TABLE user(
    id INT,
    name VARCHAR(20),
    age INT,
    
    CONSTRAINT pk PRIMARY KEY(id),#主键
    CONSTRAINT nn NOT NULL(name),#非空
    CONSTRAINT df DEFAULT(age=18),#默认
);

2.修改表时：

添加列级约束：

alter table 表名 modify column 字段名 字段类型 新约束;

添加表级约束：

alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

3、删除约束

通过添加语句删掉约束

alter table 表名 modify column 字段名 字段类型;

九、优化

1、SQL语句的优化

1.尽量避免子查询

例：select * from t1 where id = (select id from t2 where name = 'zhangsan')

子查询在mysql5.5里，内部执行计划器是先查询外表t1再匹配内表t2，当外表数据很大时，查询会非常慢。

在mysql5.6版本里，采用join关联的方式对其进行了优化，这条SQL会自动转换为：

select t1.* from t1 join t2 on t1.id = t2.id where t2.name = 'zhangsan';

但请注意，该优化仅针对select语句，对于update和delete是不优化的，所以应避免子查询。

2.避免函数索引

例：select * from t where YEAR(d) >= 2021;

由于mysql不像oracle那样支持函数索引，即使d字段有索引，也会全盘扫描。

应改成：

select * from t where d >= '2021-01-01';

3.用in来替换or

低效查询：

select * from t where id = 10 or id = 20 or id = 30;

高效查询：

select * from t where id in (10,20,30);

另外，MySQL对于 IN 做了相应的优化，即将 IN 中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from table_name where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了；再或者使用连接来替换。

4.like查询前置%

LIKE前缀%号、双%、_下划线查询非索引列或*无法使用到索引，如果查询的是索引列则可以。

即like查询前缀%，双%，_下划线并不是100%不会走索引。如果只select索引字段，或者select索引字段和主键，也会走索引的。若select中存在非索引字段，则不走索引。

示例：设索引为title，mid为主键（存在主键索引）

无法使用索引：

SELECT * FROM `mail` WHERE title like '%安卓%';
SELECT * FROM `mail` WHERE title like '%安卓';
SELECT * FROM `mail` WHERE title like '_安卓';
SELECT mid, title, img FROM `mail` WHERE title like '%安卓%';

可以使用索引：

SELECT mid,title FROM `mail` WHERE title like '_安卓';
SELECT mid,title FROM `mail` WHERE title like '%安卓';
SELECT mid,title FROM `mail` WHERE title like '%安卓%';
SELECT * FROM `mail` WHERE title like '安卓%';

5.读取适当的记录limit m，n

当查询的页数过大时，因为limit m,n是查询前m+n的数据，然后丢弃前m条数据，当m非常大时，会导致查询时间变长。

例：select * from t limit 888888, 10;

上述SQL语句，当数据量越来越大时，查询的时间也越来越长，优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是888887。sql可以采用如下的写法：

select * from t where id > 88887 limit 10;

6.避免数据类型不一致

数据类型不一致会导致索引无法被使用，所以一定要防止数据类型不一致。

7.避免随机取记录

select * from tablename order by rand() limit 0,1;

mysql不支持函数索引，会导致全表扫描

8.禁止不必要的order by排序

9.不要使用not等负向查询条件

对于一棵B+树，根节点是40，如果条件是等于20，就去左面查，条件是等于50，就去右面查，但是条件是不等于66，就需要遍历整张表进行查询。

**10.尽量不用select ***

SELECT *增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前者也需要经常更新。所以要求直接在select后面接上字段名。

11.区分in和exists

例：select * from a 
where id in (select id from b)

上面的语句相当于：

select * from a
where exists(select * from b where b.id = a.id)

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

12.优化group by语句

• 如果对group by语句的结果没有排序要求，要在语句后面加 order by null（group 默认会排序）；

• 尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Using filesort；

• 如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；

• 如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法（直接用磁盘临时表）得到group by的结果。

13.当只需要一条数据的时候使用limit 1

2、索引的优化

1.最佳左前缀法则

如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。Mysql查询优化器会对查询的字段进行改进，判断查询的字段以哪种形式组合能使得查询更快，比如创建的是(a,b)索引，查询的是(b,a)，查询优化器会修改成(a,b)后使用索引查询。

2.不在索引列上做任何操作

计算、函数、(自动or手动)类型转换，会导致索引失效而转向全表扫描。

3.存储引擎不能使用索引中范围条件右边的列。

username,phone,age都有索引。

select * from user where username = 'zhangsan' and age > 20 and phone = '13333333333'

上方的SQL语句中只有username和age使用到了索引，phone的索引没有被使用。

4.尽量使用覆盖索引

覆盖索引：如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。

设id，name，age都是索引列。

select id,name,age from user

5.is null，is not null都不能使用索引

在实际使用中尽量不要用null。

6.like中尽量不要以%开头

对于一棵B+树来讲，如果根是字符def，如果通配符在后面，例如abc%，则应该搜索左面，例如efg%，则应该搜索右面，如果通配符在前面%abc，则不知道应该走哪一面，于是会左右都扫描一遍。

7.字符串不加单引号索引失效

8.少用or，用它来连接时会导致索引失效

9.尽量避免子查询，多用join

10.在组合索引中，尽量把有区分度的索引放前面

如果没有区分度，例如用性别，相当于把整个大表分成两部分，查找数据还是需要遍历半个表才能找到，使得索引失去了意义。

11.避免在where子句中对字段进行null判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

3、数据表的优化

1.设计类型时给字段设计合适的大小

2.尽量使用not null

3.理解select Count (*)和Select Count(1)以及Select Count(column)区别

一般情况下，Select Count (*)和Select Count(1)两着返回结果是一样的；

假如表沒有主键(Primary key), 那么count(1)比count(*)快；

如果有主键的話，那主键作为count的条件时候count(主键)最快；

如果你的表只有一个字段的话那count(*)就是最快的；

count(*) 跟 count(1) 的结果一样，都包括对NULL的统计，而count(column) 是不包括NULL的统计。