MySQL数据库基本知识

SQL的全称是Structured Query Language，结构化查询语言，对数据库进行查询、修改等操作。SQL 具有如下优点。
1、一体化：SQL 集数据定义、数据操作和数据控制于一体，可以完成数据库中的全部工作。
2、使用方式灵活：SQL 具有两种使用方式，可以直接以命令方式交互使用；也可以嵌入使用，嵌入C、C++、Fortran、Java 等语言中使用。
3、非过程化：只提操作要求，不必描述操作步骤。使用时只需要告诉计算机“做什么”，而不需要告诉它“怎么做”，存储路径的选择和操作的执行由数据库管理系统自动完成。
4、语言简洁、语法简单：该语言的语句都是由描述性很强的英语单词组成，而且这些单词的数目不多。

（2）SQL语言分类

数据定义语言（Data Definition Language，DDL）：用来创建或删除数据库以及表等对象，主要包含以下几种命令：
○DROP：删除数据库和表等对象
○CREATE：创建数据库和表等对象
○ALTER：修改数据库和表等对象的结构
●数据操作语言（Data Manipulation Language，DML）：用来变更表中的记录，主要包含以下几种命令：
○INSERT：向表中插入新数据
○UPDATE：更新表中的数据
○DELETE：删除表中的数据
●数据查询语言（Data Query Language，DQL）：用来查询表中的记录，主要包含 SELECT 命令，来查询表中的数据。
●数据控制语言（Data Control Language，DCL）：用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对数据库中的用户设定权限。主要包含以下几种命令：
○GRANT：赋予用户操作权限
○REVOKE：取消用户的操作权限
○COMMIT：确认对数据库中的数据进行的变更
○ROLLBACK：取消对数据库中的数据进行的变更
MySQL查看数据库

在 MySQL 中，可使用 SHOW DATABASES 语句来查看或显示当前用户权限范围以内的数据库。查看数据库的语法格式为：

SHOW DATABASES [LIKE '数据库名'];

语法说明：
●LIKE是可选项，用于匹配指定的数据库名称。LIKE从句可以部分匹配，也可以完全匹配。
●数据库名由单引号' '包围。

MySQL创建数据库

在 MySQL 中，可以使用 CREATE DATABASE 语句创建数据库，语法格式如下：

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
●[DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
●[DEFAULT] COLLATE：指定字符集的默认校对规则。

MySQL删除数据库

在 MySQL 中，当需要删除已创建的数据库时，可以使用 DROP DATABASE 语句。其语法格式为：

DROP DATABASE [ IF EXISTS ] <数据库名>

●IF EXISTS：用于防止当数据库不存在时发生错误。
●DROP DATABASE：删除数据库中的所有表格并同时删除数据库。使用此语句时要非常小心，以免错误删除。如果要使用 DROP DATABASE，需要获得数据库 DROP 权限。

二、数据类型

（1）概述

数据类型（data_type）是指系统中所允许的数据的类型。MySQL 数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。

数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该为数值类型。

如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。

MySQL 的数据类型有大概可以分为 5 种：整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等。

注：整数类型和浮点数类型可以统称为数值数据类型

（2）类型分类

2.1数值类型

整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点数类型包括 FLOAT 和 DOUBLE，定点数类型为 DECIMAL。

类型名称	说明	存储需求
TINYINT	-128〜127	1个字节
SMALLINT	-32768〜32767	2个宇节
MEDIUMINT	-8388608〜8388607	3个字节
INT (INTEGHR)	-2147483648〜2147483647	4个字节
BIGINT	-9223372036854775808〜9223372036854775807	8个字节
FLOAT	-3.402823466E+38～-1.175494351E-38	4 个字节
DOUBLE	-1.7976931348623157E+308～-2.2250738585072014E-308	8 个字节
DECIMAL (M, D)	DECIMAL 如果不指定精度，默认为（10，0）。

2.2日期时间类型

包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

类型名称	日期格式	日期范围	存储需求
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-3	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	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4个字节

TIMESTAMP类型，占字节数少但有时间期限

2.3字符串类型

包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT等。括号中的M表示可以为其指定长度

类型名称	说明	存储需求
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

CHAR 和 VARCHAR 之间的特点和选择
CHAR 和 VARCHAR 的区别如下:
●CHAR 是固定长度字符，VARCHAR 是可变长度字符。
●CHAR 会自动删除插入数据的尾部空格，VARCHAR 不会删除尾部空格。
CHAR 是固定长度，所以它的处理速度比 VARCHAR 的速度要快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用 CHAR 类型，反之可以使用 VARCHAR类型来实现。
存储引擎对于选择 CHAR 和 VARCHAR 的影响:
●对于 MyISAM 存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。
●对于InnoDB存储引擎，最好使用可变长度的数据列，因为 InnoDB 数据表的存储格式不分固定长度和可变长度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照实际的长度存储，比较节省空间，所以对磁盘 I/O 和数据存储总量比较好。

2.4二进制类型

包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。括号中的M表示可以为其指定长度。

类型名称	说明	存储需求
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

三、查询数据

（1）概述

在 MySQL 中，可以使用 SELECT 语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
SELECT 的语法格式如下：

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

其中，各条子句的含义如下：
●{*|<字段列名>}包含星号通配符的字段列表，表示所要查询字段的名称。
●<表 1>，<表 2>…，表 1 和表 2 表示查询数据的来源，可以是单个或多个。
●WHERE <表达式>是可选项，如果选择该项，将限定查询数据必须满足该查询条件。
●GROUP BY< 字段 >，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。
●[ORDER BY< 字段 >]，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC），默认情况下是升序。
●[LIMIT[<offset>，]<row count>]，该子句告诉 MySQL 每次显示查询出来的数据条数。

（2）查询

2.1查询表中所有字段

查询所有字段是指查询表中所有字段的数据。MySQL 提供了以下 2 种方式查询表中的所有字段：
●使用“*”通配符查询所有字段
●列出表的所有字段

使用“ * ”查询表中所有字段

SELECT 可以使用“*”查找表中所有字段的数据，语法格式如下：

SELECT * FROM 表名;

注：一般情况下，除非需要使用表中所有的字段数据，否则最好不要使用通配符“ * ”。虽然使用通配符可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。使用“ * ”的优势是，当不知道所需列的名称时，可以通过“ * ”获取它们。

列出表中的所有字段

SELECT 关键字后面的字段名为需要查找的字段，因此可以将表中所有字段的名称跟在 SELECT 关键字后面。

 SELECT sid,age,NAME,gender FROM student

2.2查询表中指定的字段

查询表中的某一个字段的语法格式为：

SELECT < 列名 > FROM < 表名 >;
例如：
SELECT sid FROM student

使用 SELECT 声明可以获取多个字段下的数据，只需要在关键字 SELECT 后面指定要查找的字段名称，不同字段名称之间用逗号“，”分隔开，最后一个字段后面不需要加逗号，语法格式如下：

SELECT <字段名1>,<字段名2>,…,<字段名n> FROM <表名>;
例如：
SELECT sid,age FROM student

2.3使用DISTINCT过滤重复数据

DISTINCT 关键字的主要作用就是对数据表中一个或多个字段重复的数据进行过滤，只返回其中的一条数据给用户。

使用 DISTINCT 关键字时需要注意以下几点：
●DISTINCT 关键字只能在 SELECT 语句中使用。
●在对一个或多个字段去重时，DISTINCT 关键字必须在所有字段的最前面。
●如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重，也就是说，只有多个字段组合起来完全是一样的情况下才会被去重。
●其中，“字段名”为需要消除重复记录的字段名称，多个字段时用逗号隔开。

DISTINCT 关键字语法格式为：

SELECT DISTINCT <字段名> FROM <表名>;
例如：
对student表中的 sid,name,age字段进行去重
SELECT DISTINCT sid,name,age FROM student 

对表中所有字段进行去重
SELECT DISTINCT * FROM student

2.4通过AS设置别名

为表指定别名

当表名很长或者执行一些特殊查询的时候，为了方便操作，可以为表指定一个别名，用这个别名代替表原来的名称。
为表指定别名的基本语法格式为：

<表名> [AS] <别名>
例如：
为student表指定别名为s
SELECT DISTINCT s.sid,s.name,s.age,s.gender
FROM student AS s

注意：表的别名不能与该数据库的其它表同名。字段的别名不能与该表的其它字段同名。在条件表达式中不能使用字段的别名，否则会出现“ERROR 1054 (42S22): Unknown column”这样的错误提示信息。

为字段指定别名

在使用 SELECT 语句查询数据时，MySQL 会显示每个 SELECT 后面指定输出的字段。有时为了显示结果更加直观，我们可以为字段指定一个别名。
为字段指定别名的基本语法格式为：

<字段名> [AS] <别名>
例如：
为sid指定别名为student_sid，为age指定别名为student_age
 SELECT sid AS student_sid,age AS student_age, FROM student;

2.5LIMIT：限制查询结果的条数

当数据表中有上万条数据时，一次性查询出表中的全部数据会降低数据返回的速度，同时给数据库服务器造成很大的压力。这时就可以用 LIMIT 关键字来限制查询结果返回的条数。
LIMIT 是 MySQL 中的一个特殊关键字，用于指定查询结果从哪条记录开始显示，一共显示多少条记录。
LIMIT 关键字有 3 种使用方式：指定初始位置、不指定初始位置、与 OFFSET 组合使用。

指定初始位置

LIMIT 关键字可以指定查询结果从哪条记录开始显示，显示多少条记录。
LIMIT 指定初始位置的基本语法格式如下：

LIMIT 初始位置，记录数
例如：
在student表中使用LIMIT字句返回从第2条记录开始的行数为3的记录
2表示从第三行开始，3表示返回的行数
SELECT * FROM student LIMIT 2,3

其中，“初始位置”表示从哪条记录开始显示；“记录数”表示显示记录的条数。第一条记录的位置是 0，第二条记录的位置是 1。后面的记录依次类推。
注意：LIMIT 后的两个参数必须都是正整数。

不指定初始位置

LIMIT 关键字不指定初始位置时，记录从第一条记录开始显示。显示记录的条数由 LIMIT 关键字指定。
LIMIT 不指定初始位置的基本语法格式如下：

LIMIT 记录数
例如：
显示student表中的前5行
SELECT * FROM student LIMIT 5

带一个参数的 LIMIT 指定从查询结果的首行开始，唯一的参数表示返回的行数，即“LIMIT n”与“LIMIT 0，n”返回结果相同。带两个参数的 LIMIT 可返回从任何位置开始指定行数的数据。

LIMIT和OFFSET组合使用

LIMIT 可以和 OFFSET 组合使用，语法格式如下：

LIMIT 记录数 OFFSET 初始位置
例如：
在student表中使用 LIMIT OFFSET返回从第3条记录开始的行数为2的记录
返回的结果与 LIMIT 3,2相同
SELECT * FROM student LIMIT 2 OFFSET 3

2.6ORDER BY：对查询结果排序

查询到的数据一般都是按照数据最初被添加到表中的顺序来显示。为了使查询结果的顺序满足用户的要求，MySQL 提供了 ORDER BY 关键字来对查询结果进行排序。

ORDER BY 关键字主要用来将查询结果中的数据按照一定的顺序进行排序。其语法格式如下：

ORDER BY <字段名> [ASC|DESC]

字段名：表示需要排序的字段名称，多个字段时用逗号隔开。
●ASC|DESC：ASC表示字段按升序排序；DESC表示字段按降序排序。其中ASC为默认值。

使用 ORDER BY 关键字应该注意以下几个方面：
●当排序的字段中存在空值时，ORDER BY 会将该空值作为最小值来对待。
●ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序。

单字段排序

示例：

查询score表中的所有记录，并对score字段进行升序排序
SELECT * FROM score ORDER BY score DESC

多字段排序：

示例：

查询score表的sid 和 score字段，先按score排序，再按 sid排序 
SELECT sid ,score FROM score ORDER BY score DESC,sid

注意：在对多个字段进行排序时，排序的第一个字段必须有相同的值，才会对第二个字段进行排序。如果第一个字段数据中所有的值都是唯一的，MySQL 将不再对第二个字段进行排序。
默认情况下，查询数据按字母升序进行排序（A～Z），但数据的排序并不仅限于此，还可以使用 ORDER BY 中的 DESC 对查询结果进行降序排序（Z～A）。

给字段设定排序规则

示例：

先按score降序排序，再按sid升序排序
SELECT sid ,score FROM score ORDER BY score DESC,sid ASC

DESC 关键字只对前面的列进行降序排列，在这里只对 score字段进行降序。因此，score按降序排序，而 sid 仍按升序排序。如果想在多个列上进行降序排序，必须对每个列指定 DESC 关键字。

2.7WHERE:条件查询数据

在 MySQL 中，如果需要有条件的从数据表中查询数据，可以使用 WHERE 关键字来指定查询条件。
使用 WHERE 关键字的语法格式如下：

WHERE 查询条件

查询条件可以是：
●带比较运算符和逻辑运算符的查询条件
●带 BETWEEN AND 关键字的查询条件
●带 IS NULL 关键字的查询条件
●带 IN 关键字的查询条件
●带 LIKE 关键字的查询条件

单一条件的查询语句

单一条件指的是在 WHERE 关键字后只有一个查询条件。

示例：

在score表中查询成绩大于90分的学生学号

SELECT sid FROM score WHERE score >90;

多条件的查询语句

在 WHERE 关键词后可以有多个查询条件，这样能够使查询结果更加精确。多个查询条件时用逻辑运算符 AND（&&）、OR（||）或 XOR 隔开。
●AND：记录满足所有查询条件时，才会被查询出来。
●OR：记录满足任意一个查询条件时，才会被查询出来。
●XOR：记录满足其中一个条件，并且不满足另一个条件时，才会被查询出来。

示例1：

在student表中查询年龄大于20，性别为女的学生学号

 SELECT sid FROM student WHERE age>20 AND gender='女';

示例2：

在student表中查询年龄大于20，或者性别为女的学生学号

SELECT sid FROM student WHERE age>20 OR  gender='女';

示例3：

在student表中查询年龄大于20，并且性别不为女的学生信息和年龄小于20，并且性别为女的学生信息

SELECT sid,NAME,age,gender 
FROM student WHERE age>20  XOR  gender='女';

2.8LIKE：模糊查询

在 MySQL 中，LIKE 关键字主要用于搜索匹配字段中的指定内容。其语法格式如下：

[NOT] LIKE  '字符串'

●NOT ：可选参数，字段中的内容与指定的字符串不匹配时满足条件。
●字符串：指定用来匹配的字符串。“字符串”可以是一个很完整的字符串，也可以包含通配符。
LIKE 关键字支持百分号“%”和下划线“_”通配符。

通配符是一种特殊语句，主要用来模糊查询。当不知道真正字符或者懒得输入完整名称时，可以使用通配符来代替一个或多个真正的字符。

带有“%”通配符的查询

“%”是 MySQL 中最常用的通配符，它能代表任何长度的字符串，字符串的长度可以为 0。例如，a%b表示以字母 a 开头，以字母 b 结尾的任意长度的字符串。该字符串可以代表 ab、acb、accb、accrb 等字符串。

示例：

在student表中查询所有姓‘刘’的学生姓名

 SELECT NAME FROM student where NAME  LIKE '刘%'

在student表中查询所有不姓‘刘’的学生姓名

 SELECT NAME FROM student where NAME  not LIKE '刘%'

在student表中查询所有包含‘亦’的学生姓名

 SELECT NAME FROM student where NAME   LIKE '%亦%'

带有“ _ ”通配符的查询

“_”只能代表单个字符，字符的长度不能为 0。例如，a_b可以代表 acb、adb、aub 等字符串。

示例：

在student表中查找以‘菲’结尾，且‘菲’前面只有2个字符的学生姓名：

SELECT NAME FROM student where NAME   LIKE '__菲'

LIKE区分大小写

默认情况下，LIKE 关键字匹配字符的时候是不区分大小写的。如果需要区分大小写，可以加入 BINARY 关键字。

示例：

在student表中查找以‘L’开头的学生姓名，区分大小写：

 SELECT NAME FROM student where NAME  LIKE  BINARY 'L%'

使用通配符的注意事项和技巧
下面是使用通配符的一些注意事项：
●注意大小写。MySQL 默认是不区分大小写的。如果区分大小写，像“Tom”这样的数据就不能被“t%”所匹配到。
●注意尾部空格，尾部空格会干扰通配符的匹配。例如，“T% ”就不能匹配到“Tom”。
●注意 NULL。“%”通配符可以到匹配任意字符，但是不能匹配 NULL。也就是说 “%”匹配不到 tb_students_info 数据表中值为 NULL 的记录。

2.9BETWEEN AND：范围查询

MySQL 提供了 BETWEEN AND 关键字，用来判断字段的数值是否在指定范围内。BETWEEN AND 需要两个参数，即范围的起始值和终止值。如果字段值在指定的范围内，则这些记录被返回。如果不在指定范围内，则不会被返回。

使用 BETWEEN AND 的基本语法格式如下：

[NOT] BETWEEN 取值1 AND 取值2

●NOT：可选参数，表示指定范围之外的值。如果字段值不满足指定范围内的值，则这些记录被返回。
●取值1：表示范围的起始值。
●取值2：表示范围的终止值。

示例1：

在student表中查询年龄在18-21之间的学生姓名：

SELECT NAME FROM student WHERE age BETWEEN 18 AND 21;

示例2：

在student表中查询年龄不在18-21之间的学生姓名：

SELECT NAME FROM student WHERE age not BETWEEN 18 AND 21;

2.10IS NULL：空值查询

MySQL 提供了 IS NULL 关键字，用来判断字段的值是否为空值（NULL）。空值不同于 0，也不同于空字符串。如果字段的值是空值，则满足查询条件，该记录将被查询出来。如果字段的值不是空值，则不满足查询条件。
使用 IS NULL 的基本语法格式如下：

IS [NOT] NULL

其中，“NOT”是可选参数，表示字段值不是空值时满足条件。

示例：

使用 IS NULL 关键字来查询student表中的age字段是NULL 的记录：

SELECT age FROM student WHERE age IS NULL

使用 IS NULL 关键字来查询student表中的age字段不为NULL 的记录：

SELECT age FROM student WHERE age IS NOT NULL

四、分组查询

（1）概述

在 MySQL 中，GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。
使用 GROUP BY 关键字的语法格式如下：

GROUP BY  <字段名>

其中，“字段名”表示需要分组的字段名称，多个字段时用逗号隔开。

（2）分组查询

2.1 GROUP BY 单独使用

单独使用 GROUP BY 关键字时，查询结果会只显示每个分组的第一条记录。

示例：

根据student表中的gender进行分组

SELECT gender FROM student GROUP BY gender;

运行结果：

2.2 GROUP BY 与 GROUP_CONCAT()

GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。

示例：

根据student表中的gender进行分组查询，使用 GROUP_CONCAT()将每个分组的name字段值都显示出来：

SELECT gender ,GROUP_CONCAT(NAME) FROM student GROUP BY gender;

查询结果：

2.3GROUP BY 与聚合函数

在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。聚合函数包括： COUNT()，SUM()，AVG()，MAX() 和 MIN()。
1COUNT() 用来统计记录的条数；
2SUM() 用来计算字段值的总和；
3AVG() 用来计算字段值的平均值；
4MAX() 用来查询字段的最大值；
5MIN() 用来查询字段的最小值。

示例：

根据student表的gender字段进行分组查询，使用COUNT（）函数计算每一组的记录数

SELECT gender ,COUNT(gender) FROM student GROUP BY gender;

运行结果：

2.4 GROUP BY 与WITH ROLLUP

WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。

示例：

根据student表中的gender字段进行查询，并使用 WITH ROLLUP显示记录的总和;

SELECT gender ,GROUP_CONCAT(NAME ) FROM student 
GROUP BY gender WITH ROLLUP;

查询结果：

2.5HAVING ：过滤分组

MySQL 中，可以使用 HAVING 关键字对分组后的数据进行过滤。
使用 HAVING 关键字的语法格式如下：

HAVING <查询条件>

HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法。但是 WHERE 和 HAVING 关键字也存在以下几点差异：
●一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
●WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
●WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤。
●WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
●WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

示例：使用 HAVING 关键字查询出student表中年龄大于 20的学生姓名，性别和身高：

SELECT NAME,age,gender FROM student 
HAVING age>20;

查询结果：

五、交叉连接

在关系型数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询。多表查询就是同时查询两个或两个以上的表。在 MySQL 中，多表查询主要有交叉连接、内连接和外连接。
交叉连接（CROSS JOIN）一般用来返回连接表的笛卡尔积。
交叉连接的语法格式如下：

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
或者
SELECT <字段名> FROM <表1>, <表2> [WHERE子句]

●字段名：需要查询的字段名称。
●<表1><表2>：需要交叉连接的表名。
●WHERE 子句：用来设置交叉连接的查询条件。
●注意：多个表交叉连接时，在 FROM 后连续使用 CROSS JOIN 或,即可。以上两种语法的返回结果是相同的，但是第一种语法才是官方建议的标准写法。
当连接的表之间没有关系时，我们会省略掉 WHERE 子句，这时返回结果就是两个表的笛卡尔积，返回结果数量就是两个表的数据行相乘。需要注意的是，如果每个表有 1000 行，那么返回结果的数量就有 1000×1000 = 1000000 行，数据量是非常巨大的。

示例：

SELECT *FROM student CROSS join score

查询结果：

由运行结果可以看出，student和 score 表交叉连接查询后，返回了多条记录。可以想象，当表中的数据较多时，得到的运行结果会非常长，而且得到的运行结果也没太大的意义。所以，通过交叉连接的方式进行多表查询的这种方法并不常用，我们应该尽量避免这种查询。

示例2：

SELECT *FROM student CROSS join score
WHERE student.sid=score.sid;

查询结果：

如果在交叉连接时使用 WHERE 子句，MySQL 会先生成两个表的笛卡尔积，然后再选择满足 WHERE 条件的记录。因此，表的数量较多时，交叉连接会非常非常慢。一般情况下不建议使用交叉连接。在 MySQL 中，多表查询一般使用内连接和外连接，它们的效率要高于交叉连接。

六、内连接

内连接（INNER JOIN）主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接。简单来说，就是利用条件表达式来消除交叉连接的某些数据行。
内连接使用 INNER JOIN 关键字连接两张表，并使用 ON 子句来设置连接条件。如果没有连接条件，INNER JOIN 和 CROSS JOIN 在语法上是等同的，两者可以互换。

内连接的语法格式如下：

SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]

●字段名：需要查询的字段名称。
●<表1><表2>：需要内连接的表名。
●INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。
●ON 子句：用来设置内连接的连接条件。
●多个表内连接时，在 FROM 后连续使用 INNER JOIN 或 JOIN 即可。

INNER JOIN 也可以使用 WHERE 子句指定连接条件，但是 INNER JOIN ... ON 语法是官方的标准写法，而且 WHERE 子句在某些时候会影响查询的性能

示例：

在student 表和 score 表之间，使用内连接查询学生姓名和相对应的成绩

SELECT s.name,sc.score FROM student AS s 
INNER JOIN score AS sc ON s.sid=sc.sid;

查询结果：

在这里的查询语句中，两个表之间的关系通过 INNER JOIN 指定，连接的条件使用 ON 子句给出。
注意：当对多个表进行查询时，要在 SELECT 语句后面指定字段是来源于哪一张表。因此，在多表查询时，SELECT 语句后面的写法是表名.列名。另外，如果表名非常长的话，也可以给表设置别名，这样就可以直接在 SELECT 语句后面写上表的别名.列名。

七、外连接

内连接的查询结果都是符合连接条件的记录，而外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。外连接可以分为左外连接和右外连接。

左连接

左外连接又称为左连接，使用 LEFT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。
左连接的语法格式如下：

SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>

字段名：需要查询的字段名称。
●<表1><表2>：需要左连接的表名。
●LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。
●ON 子句：用来设置左连接的连接条件，不能省略。
上述语法中，“表1”为基表，“表2”为参考表。左连接查询时，可以查询出“表1”中的所有记录和“表2”中匹配连接条件的记录。如果“表1”的某行在“表2”中没有匹配行，那么在返回结果中，“表2”的字段值均为空值（NULL）。

示例：
在student表和score表中查询所有学生姓名和相对应的学生成绩，包括没有成绩的学生：

SELECT s.name,sc.score FROM student AS s
LEFT OUTER JOIN  score AS sc ON s.sid=sc.sid;

查询结果：

右连接

右外连接又称为右连接，右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。
右连接的语法格式如下：

SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>

●字段名：需要查询的字段名称。
●<表1><表2>：需要右连接的表名。
●RIGHT OUTER JOIN：右连接中可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN。
●ON 子句：用来设置右连接的连接条件，不能省略。
与左连接相反，右连接以“表2”为基表，“表1”为参考表。右连接查询时，可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。如果“表2”的某行在“表1”中没有匹配行，那么在返回结果中，“表1”的字段值均为空值（NULL）。

示例：
在student表和score表中查询所有学生姓名和相对应的学生成绩，包括没有成绩的学生：

SELECT s.name,sc.score FROM student AS s
RIGHT  OUTER JOIN  score AS sc ON s.sid=sc.sid;

查询结果：

使用外连接查询时，一定要分清需要查询的结果，是需要显示左表的全部记录还是右表的全部记录，然后选择相应的左连接和右连接。

八、子查询

（1）概述

子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。
子查询在 WHERE 中的语法格式如下：

WHERE <表达式> <操作符> (子查询)

其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。

（2）子查询

2.1 IN | NOT IN

当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。

2.2EXISTS | NOT EXISTS

用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反

示例1：

使用子查询在student表和score表中查询学习成绩=90的学生姓名：

SELECT NAME FROM student 
WHERE sid  IN (SELECT sid  FROM score WHERE score=90);

查询结果：

示例2：

使用子查询在student表和score表中查询学习成绩不等于90的学生姓名：

SELECT NAME FROM student 
WHERE sid  not IN (SELECT sid  FROM score WHERE score=90);

查询结果：

示例3：
使用=运算符，在student 表和 score表中查询出所有成绩=93学生姓名，SQL 语句和运行结果如下:

SELECT NAME FROM student 
WHERE sid  =(SELECT sid  FROM score WHERE score=93);

查询结果：

示例4：
使用<>运算符，在student 表和 score表中查询出所有成绩不等于93学生姓名，SQL 语句和运行结果如下:

SELECT NAME FROM student 
WHERE sid <>(SELECT sid  FROM score WHERE score=93);

查询结果：

示例5：

查询 score 表中是否存在score=93的学生，如果存在，就查询出 student 表中的记录。

SELECT NAME FROM student 
WHERE EXISTS(SELECT sid  FROM score WHERE score=93);

查询结果：

2.3子查询与表连接的区别

子查询的功能也可以通过表连接完成，但是子查询会使 SQL 语句更容易阅读和编写。一般来说，表连接（内连接和外连接等）都可以用子查询替换，但反过来却不一定，有的子查询不能用表连接来替换。子查询比较灵活、方便、形式多样，适合作为查询的筛选条件，而表连接更适合于查看连接表的数据。

九、数据操作

INSERT：插入数据

基本语法

INSERT 语句有两种语法形式，分别是 INSERT…VALUES 语句和 INSERT…SET 语句。

1.INSERT...VALUES语句

INSERT VALUES 的语法格式为：

INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];

●<表名>：指定被操作的表名。
●<列名>：指定需要插入数据的列名。若向表中的所有列插入数据，则全部的列名均可以省略，直接采用 INSERT<表名>VALUES(…) 即可。
●VALUES 或 VALUE 子句：该子句包含要插入的数据清单。数据清单中数据的顺序要和列的顺序相对应。

2. INSERT…SET语句

语法格式为：

INSERT INTO <表名>
SET <列名1> = <值1>,
    <列名2> = <值2>,
        …

此语句用于直接给表中的某些列指定对应的列值，即要插入的数据的列名在 SET 子句中指定，col_name 为指定的列名，等号后面为指定的数据，而对于未指定的列，列值会指定为该列的默认值。由 INSERT 语句的两种形式可以看出：
●使用 INSERT…VALUES 语句可以向表中插入一行数据，也可以插入多行数据；
●使用 INSERT…SET 语句可以指定插入行中每列的值，也可以指定部分列的值；
●INSERT…SELECT 语句向表中插入其他表的数据。
●采用 INSERT…SET 语句可以向表中插入部分列的值，这种方式更为灵活；
●INSERT…VALUES 语句可以一次插入多条数据。
在 MySQL 中，用单条 INSERT 语句处理多个插入要比使用多条 INSERT 语句更快。当使用单条 INSERT 语句插入多行数据的时候，只需要将每行数据用圆括号括起来即可。

3.向表中的全部字段添加值

在数据库中创建一个课程信息表 tb_courses，包含课程编号 course_id、课程名称 course_name、课程学分 course_grade 和课程备注 course_info，输入的 SQL 语句和执行结果如下所示。

CREATE TABLE courses
(
course_id INT NOT NULL AUTO_INCREMENT,
course_name CHAR(40) NOT NULL,
course_grade FLOAT NOT NULL,
course_info CHAR(100) NULL,
PRIMARY KEY(course_id)
);

向表中所有字段插入值的方法有两种：一种是指定所有字段名；另一种是完全不指定字段名。

示例1：

在student表中插入一条新记录：

INSERT INTO student(NAME,age,gender) 
VALUES ('张三丰',20,'女')

4.向表中指定字段添加值

示例2：

第一种：
INSERT INTO student(NAME,age) 
VALUES ('张三丰',20)

第二种
INSERT INTO student 
SET NAME='朱朱',age=21,gender='女'

使用 INSERT INTO…FROM 语句复制表数据

INSERT INTO…SELECT…FROM 语句用于快速地从一个或多个表中取出数据，并将这些数据作为行数据插入另一个表中。SELECT 子句返回的是一个查询到的结果集，INSERT 语句将这个结果集插入指定表中，结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致。

在数据库student_db中建立一个与student表结构相同的数据表student_new，创建表的SQL语句和执行过程如下所示：

--先手动创建一张与student表结构相同的表
CREATE TABLE `student_new` (
	`sid` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
	`age` INT(2) NULL DEFAULT NULL,
	`gender` VARCHAR(2) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
	PRIMARY KEY (`sid`) USING BTREE
)

示例3：

从student表中查询所有记录，并将其插入到student_new表中

--然后添加数据，使用select语句从student表中查询
INSERT INTO student_new(NAME,age,gender)
SELECT NAME,age,gender
FROM student

在MySQL中，如果希望自动创建临时表，保存复制查询结果，可以采用CREATE TABLE 表名（SELECT 语句）的语法形式：

示例4：

从student表中查询所有记录，并将其插入到student_tmp表中：

--自动创建表
CREATE TABLE student_tmp
(SELECT sid,NAME,age,gender FROM student)

UPDATE:修改数据

基本语法

使用 UPDATE 语句修改单个表，语法格式为：

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]

●<表名>：用于指定要更新的表名称。
●SET 子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。
●WHERE 子句：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。
●ORDER BY 子句：可选项。用于限定表中的行被修改的次序。
●LIMIT 子句：可选项。用于限定被修改的行数。
注意：修改一行数据的多个列值时，SET 子句的每个值用逗号分开即可。

修改表中的数据

示例1：
在score表中更新所有score为score+5：

UPDATE score SET score=score+5;

根据条件修改表中的数据

示例2：

在student_tmp表中，更新sid=7的记录，将name改为‘杨米’：

SELECT * FROM student_tmp 
UPDATE student_tmp SET NAME='杨米'
WHERE sid=7

注意：保证 UPDATE 以 WHERE 子句结束，通过 WHERE 子句指定被更新的记录所需要满足的条件，如果忽略 WHERE 子句，MySQL 将更新表中所有的行。

DELETE:删除数据

在 MySQL 中，可以使用 DELETE 语句来删除表的一行或者多行数据。
基本语法

使用 DELETE 语句从单个表中删除数据，语法格式为：

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

●<表名>：指定要删除数据的表名。
●ORDER BY 子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。
●WHERE 子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。
●LIMIT 子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。
注意：在不使用 WHERE 条件的时候，将删除所有数据。

删除表中的全部数据

示例1：

删除student_tmp表中的全部数据：

DELETE FROM student_tmp

根据条件删除表中的数据

示例2：

在student_tmp表中删除姓刘的学生：

DELETE FROM student_tmp WHERE NAME LIKE '刘%';

TRUNCATE：清空表记录

MySQL 提供了 DELETE 和 TRUNCATE 关键字来删除表中的数据。

基本语法

TRUNCATE 关键字用于完全清空一个表。其语法格式如下：

TRUNCATE [TABLE] 表名

示例1：

清空student表中的记录：

TRUNCATE TABLE student;

TRUNCATE 和 DELETE 的区别

从逻辑上说，TRUNCATE 语句与 DELETE 语句作用相同，但是在某些情况下，两者在使用上有所区别。
●DELETE 是 DML 类型的语句；TRUNCATE 是 DDL 类型的语句。它们都用来清空表中的数据。
●DELETE 是逐行一条一条删除记录的；TRUNCATE 则是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 DELETE 快。因此需要删除表中全部的数据行时，尽量使用 TRUNCATE 语句，可以缩短执行时间。
●DELETE 删除数据后，配合事件回滚可以找回数据；TRUNCATE 不支持事务的回滚，数据删除后无法找回。
●DELETE 删除数据后，系统不会重新设置自增字段的计数器；TRUNCATE 清空表记录后，系统会重新设置自增字段的计数器。
●DELETE 的使用范围更广，因为它可以通过 WHERE 子句指定条件来删除部分数据；而 TRUNCATE 不支持 WHERE 子句，只能删除整体。
●DELETE 会返回删除数据的行数，但是 TRUNCATE 只会返回 0，没有任何意义。

十、SELECT语句执行流程

select语句的完整语法：

SELECT
DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

其执行顺序如下：

FROM
<表名> # 笛卡尔积
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <JOIN,LEFT JOIN,RIGHT JOIN...>
<JOIN表> # 指定JOIN，用于添加数据到ON之后的虚表中，例如LEFT JOIN会将左表的剩余数据添加到虚表中
WHERE
<WHERE条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于HAVING子句进行判断，在书写上这类聚合函数是写在HAVING判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在GROUP BY子句中，聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

引擎在执行上述每一步时，都会在内存中形成一张虚拟表，然后对虚拟表进行后续操作，并释放没用的虚拟表的内存，以此类推。

下面我们具体介绍一下 SQL 查询语句的执行顺序（下面“VT”表示虚拟表）
1.FROM：SELECT * FROM table_1, table_2; 与 SELECT * FROM table_1 JOIN table_2; 的结果一致，都是表示求笛卡尔积；用于直接计算两个表笛卡尔积，得到虚拟表VT1，这是所有 SELECT 语句最先执行的操作，其他操作时在这个表上进行的，也就是 FROM 操作所完成的内容
2.ON：从 VT1 表中筛选符合条件的数据，形成 VT2表
3.JOIN：将该 JOIN 类型的数据补充到 VT2 表中，例如 LEFT JOIN 会将左表的剩余数据添加到虚表VT2 中，形成 VT3 表；若表的数量大于 2，则会重复1-3 步
4.WHERE：执行筛选，（不能使用聚合函数）得到 VT4 表
5.GROUP BY：对 VT4 表进行分组，得到 VT5 表；其后处理的语句，如 SELECT、HAVING，所用到的列必须包含在 GROUP BY 条件中，没有出现的需要用聚合函数GROUP BY 条件中，没有出现的需要用聚合函数
6.HAVING：筛选分组后的数据，得到 VT6 表
7.SELECT：返回列得到 VT7 表
8.DISTINCT：用于去重得到 VT8 表
9.ORDER BY：用于排序得到 VT9 表
10.LIMIT：返回需要的行数，得到 VT10

需要注意的是：
●GROUP BY 条件中，每个列必须是有效列，不能是聚合函数
●NULL 值也会作为一个分组返回
●除了聚合函数，SELECT 子句中的列必须在 GROUP BY 条件中

注：

1.GROUP BY 在 WHERE语句之后使用

2.ORDER BY 基本上是在最后执行的，所以可以基于任何东西进行 ORDER BY。

3.LIMIT在最后执行

鸣筝鸣筝

关注

8
点赞
踩
36

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库基本知识

SQL的全称是Structured Query Language，结构化查询语言，对数据库进行查询、修改等操作。SQL 具有如下优点。1、一体化：SQL 集数据定义、数据操作和数据控制于一体，可以完成数据库中的全部工作。2、使用方式灵活：SQL 具有两种使用方式，可以直接以命令方式交互使用；也可以嵌入使用，嵌入C、C++、Fortran、Java 等语言中使用。3、非过程化：只提操作要求，不必描述操作步骤。.....................
复制链接

扫一扫