准备工作

MySQL示例数据库模式由以下表组成：

Customers

：存储客户的数据。
Products

：存储比例模型车列表。
ProductLines

：存储产品系列类别列表。
Orders

：存储客户下达的销售订单。
OrderDetails

：存储每个销售订单的销售订单行项目。
Payments

：存储客户根据其帐户进行的付款。
Employees

：存储所有员工信息以及组织结构。
Offices

：存储销售办公室数据。

1、SELECT

SELECT语句可以控制要查看的列和行，基本语法如下：

SELECT
column_1, column_2, ...
FROM
table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;

SELECT

后跟逗号分隔列或星号（*）列表，表示返回所有列。
FROM

指定要查询数据的表或视图。
JOIN

根据特定的连接条件从其他表中获取相关数据。
WHERE

子句过滤结果集中的行。
GROUP BY

子句将一组行分组到组中，并在每个组上应用聚合函数。
HAVING

子句根据

GROUP BY

子句定义的组过滤组。
ORDER BY

子句指定用于排序的列的列表。
LIMIT

约束返回的行数。

在查询语句中要求必须包含SELECT和FROM ,其它都是可选项。另外不建议使用SELECT *，不需要的列会增加数据传输时间和网络开销。对于无用的大字段，如 varchar、blob、text，会增加 io 操作。

2、DISTINCT

从表中查询数据时，可能会出现重复的行。要删除这些重复的行，在SELECT子句中请使用

DISTINCT

语句。相关语法：

SELECT DISTINCT
    columns
FROM
    table_name
WHERE
    conditions;

如果字段中有

NULL

值并且要对该列使用

DISTINCT

子句，则MySQL仅保留一个

NULL

值。

如果

DISTINCT

与多个列一起使用，MySQL使用这些列中的值组合来确定结果集中行的唯一性。

3、ORDER BY

通常我们在查询数据时需要对结果集进行排序，

ORDER BY

允许的操作：

按单列或多列对结果集进行排序。
按升序或降序对不同列的结果集进行排序。

4、WHERE，AND，OR

只想获取满足条件的结果集，那么就需要通过

where

筛选。基本语法：

SELECT 
    select_list
FROM
    table_name
WHERE
    search_condition AND boolean_expression_1 OR boolean_expression_2

AND运算符还用于 JOIN子句中关联条件。

5、IN

IN 运算符允许你确定一个指定的值在一组值匹配的任何值或子查询。相关语法：

SELECT 
    column1,column2,...
FROM
    table_name
WHERE 
 (expr|column_1) IN ('value1','value2',...);

上述我们使用OR的情况就可以改为

6、BETWEEN

BETWEEN

运算符是一个逻辑运算符，用来筛选值在某个区间范围内的结果集。基本语法：

expr [NOT] BETWEEN begin_expr AND end_expr;

expr

是在

begin_expr

和

end_expr

定义的范围内测试的表达式。三个表达式：

expr

，

begin_expr

和

end_expr

必须具有相同的数据类型。从查询结果中可以看出是包括边界值的，所以它是等价于

expr >= AND expr <=

7、LIKE

LIKE

运算符检测一个字符串是否包含指定的值

MySQL提供了两个通配符供

LIKE

使用：百分号

%

和下划线

_

。

百分号（

%

）通配符匹配任何零个或多个字符的字符串。
下划线（

_

）通配符匹配任何单个字符。

查询员工姓氏以B开头的

8、LIMIT

SELECT

语句中使用

LIMIT

子句来约束结果集中的行数。

LIMIT

子句接受一个或两个参数。两个参数的值必须为零或正整数。基本语法：

SELECT 
    column1,column2,...
FROM
    table
LIMIT offset , count;

比如结果集排序后取前五条数据：

9、IS NULL

顾名思义，筛选值为null的，所以IS NOT NULL 就是查值不为null的。

10、别名

要为列提供别名，请使用

AS

关键字后跟别名，如果别名包含空格，必须使用“。基本语法：

SELECT 
 [column_1 | expression] AS `descriptive name`
FROM table_name;

11、JOINS

MySQL连接是一种基于表之间的公共列的值在一个（自连接）或更多表之间链接数据的方法。

MySQL支持以下类型的连接：

CROSS JOIN
INNER JOIN
LEFT JOIN
RGIHT JOIN

第一种：

CROSS JOIN

使得从多个表行笛卡尔积

第二种：

INNER JOIN

必需要一个连接字段条件。

INNER JOIN

要求两个连接表中的行具有匹配的值。

INNER JOIN

返回的记录是通过两表连接字段相同的记录。

第三种：

LEFT JOIN

会读取左边数据表的全部数据，即使右边表无对应数据。对于不满足连接条件的行，右表中相关字段值为null。

第四种：

RIGHT JOIN

会读取右边数据表的全部数据，即使左边表无对应数据。

12、GROUP BY

GROUP BY

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

DISTINCT

一样也可以起到去重效果，通常和聚合函数一起使用，例如SUM，AVG，MAX，MIN，和COUNT。基本语法：

SELECT 
    c1, c2,..., cn, aggregate_function(ci)
FROM
    table
WHERE
    where_conditions
GROUP BY c1 , c2,...,cn;

比如统计订单表中不同状态下的订单数量：

13、HAVING

HAVING

子句通常与

GROUP BY

子句一起使用，以根据指定的条件过滤组。

14、ROLLUP

主要作用就是对

GROUP BY

分组后的结果再进行汇总

15、UNION

UNION

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

UNION ALL

的话就不会删除重复数据。基本语法：

SELECT column_list
UNION [ALL]
SELECT column_list

要使用

UNION

运算符组合两个或多个查询的结果集，必须遵循以下基本规则：

首先，所有SELECT语句中出现的列的数量和顺序必须相同。
其次，列的数据类型必须相同或可转换。

16、子查询

是指一个查询嵌套在另一个查询内，分为form子句的子查询、where子句的子查询。

17、EXISTS

EXISTS

经常使用的在一个子查询，以测试一个“存在”状态。

NOT EXISTS

子查询没有返回任何行时返回true。比如查找至少有一个销售订单的客户：

18、CTE

公用表表达式【CTE】是一个命名的临时结果集,仅在单个SQL语句(例如select、insert、delete和update)的执行范围内存在，仅在MySQL 8.0或更高版本中可用。基本语法：

WITH cte_name (column_list) AS (
    query
) 
SELECT * FROM cte_name;

query中的列数必须与column_list中的列数相同。如果省略column_list，则默认用query中的查询列。

递归CTE

是不断调用自己,直到满足终止条件才输出所有数据，由三个主要部分组成：

初始查询，形成CTE结构的基本结果集。初始查询部分称为锚成员。
递归查询部分是引用CTE名称的查询，因此，它被称为递归成员。递归成员由

UNION ALL

或

UNION

运算符与锚成员连接。
终止条件，确保递归成员不返回任何行时停止递归。

比如下例：如果递归成员没有汇报对象，则递归停止

19、MINUS

由于MySQL不支持

MINUS

，所以我们通过

LEFT JOIN

模拟实现。基本语法

SELECT 
    column_list 
FROM 
    table_1
    LEFT JOIN table_2 ON join_predicate
WHERE 
    table_2.id IS NULL;

我这里删除了几条数据，方便演示效果：

20、INSERT

INSERT语句可以将一行或多行数据插入到表中。基本语法：

INSERT INTO table(c1,c2,...)
VALUES 
   (v11,v12,...),
   (v21,v22,...),
    ...
   (vnn,vn2,...);

21、INSERT IGNORE

使用INSERT语句向表中添加多行时，如果在处理过程中发生错误，MySQL将终止语句并返回错误。结果就是没有行插入表中。如果使用

INSERT IGNORE

语句，将忽略导致错误的包含无效数据的行，并将具有有效数据的行插入到表中。基本语法：

INSERT IGNORE INTO table(column_list)
VALUES( value_list),
      ( value_list),
      ...

22、INSERT INTO SELECT

当你想把表1的数据复制到表2时可以使用，基本语法：

INSERT INTO table_name(column_list)
SELECT 
   select_list 
FROM 
   another_table;

23、UPDATE

修改表中数据使用，这里需要谨慎一点，该加WHERE子句时一定不能忘记！基本语法：【IGNORE的用途和上述一样】

UPDATE [IGNORE] table_name 
SET 
    column_name1 = expr1,
    column_name2 = expr2,
    ...
[WHERE
    condition];

24、DELETE

需要删除数据时使用，删除表中所有数据可使用

TRUNCATE TABLE

，基本语法：

DELETE FROM table_name
WHERE condition;

要注意WHERE条件的正确性，或者删除前备份数据。

25、ALTER TABLE

ALTER TABLE

语句修改现有表的结构。它允许您添加列，删除列，更改列的数据类型，添加主键，重命名表等等。

修改某一列：

ALTER TABLE  `orderdetails` 
MODIFY COLUMN `productCode` varchar(16) NOT NULL COMMENT '商品编号';

删除某一列：

ALTER TABLE table
DROP COLUMN column;

添加某一列：MySQL允许您通过指定

FIRST

关键字将新列添加为表的第一列。它还允许您使用

AFTER existing_column

子句在现有列之后添加新列。如果您没有明确指定新列的位置，MySQL会将其添加为最后一列。

ALTER TABLE table
ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];

26、DROP—TRUNCATE

DROP TABLE

语句从数据库中永久删除表结构及其数据；

TRUNCATE

只删除数据和释放空间，也就是如果表具有

AUTO_INCREMENT

列，则

TRUNCATE TABLE

语句会将自动增量值重置为零。

27、PRIMARY KEY

主键是一列或一组列，用于唯一标识表中的每一行。一张表只有一个主键，主键列不能包含

NULL

值，为表定义主键时，MySQL会自动创建一个索引名为

PRIMARY

。

28、FOREIGN KEY

外键是表中与另一个表的另一个字段匹配的字段。一张表可以有多个外键，外键可以是一个列或一组列。子表中的列通常引用父表中的主键列。

RESTRICT（默认）当父表删除|更新记录时，首先检查记录是否有对应的外键，有，报错。
NO ACTION 同RESTRICT
SET NULL 当父表删除|更新记录时，首先检查记录是否有对应的外键，有，则设置子表对应的外键值为null（外键允许为null）。
CASCADE 当父表删除|更新记录时，首先检查记录是否有对应的外键，有，则设置子表对应的外键值跟随（父表）外键来源表相应的值而变化。

29、字符集与排序规则

字符集

：就是用来定义字符在数据库中的编码的集合。

排序规则

：就是指字符比较时是否区分大小写，以及是按照字符编码进行比较还是直接用二进制数据比较。

MySQL服务器级别的默认字符集和排序规则分别是latin1和latin1_swedish_ci。

通常项目中我们使用

utf8

和

utf8_general_ci

就足够了。而utf8是

utf8mb4

的子集，要在 Mysql 中保存 4 字节长度的 UTF-8 字符，就可以使用

utf8mb4

字符集和

utf8mb4_general_ci

排序规则了。例如可以用

utf8mb4

字符编码直接存储emoj表情，而不是存表情的替换字符。

30、数据类型

INT

数据类型可以是有符号和无符号的。下表说明了每种整数类型的特征，包括以字节为单位的存储，最小值和最大值。通常主键列的类型是

INT

或者

BIGINT 。

布尔

类型的列就使用

TINYINT

DECIMAL

数据类型用于在数据库中存储精确的数值，比如和钱相关的数据。基本语法：

column_name  DECIMAL(P,D);

P是表示有效位数的精度。P的范围是1到65。
D是表示小数点后的位数的刻度。D的范围是0和30.MySQL要求D小于或等于（<=）P。

像

INT

数据类型，

DECIMAL

类型也有

UNSIGNED

和

ZEROFILL

属性。如果我们使用

UNSIGNED

属性，则列

DECIMAL UNSIGNED

不会接受负值。

如果我们使用

ZEROFILL

，MySQL会将显示值填充0，直到列定义指定的显示宽度。另外，如果我们使用

ZERO FILL

的

DECIMAL

列，MySQL将在列自动添加

UNSIGNED

属性。

MySQL分别为整数和小数部分分配存储空间。MySQL使用二进制格式来存储

DECIMAL

值。它将9位数字打包成4个字节。例如，

DECIMAL(19,9)

小数部分有9位数，整数部分有19-9 = 10位数。小数部分需要4个字节。整数部分对于前9位需要4个字节，对于1个剩余数字，它需要1个以上的字节，所以

DECIMAL(19,9)

列需要9个字节。

Boolean

：MySQL没有内置的布尔类型。但是，我们可以使用TINYINT(1)

CHAR：

是MySQL中的固定长度的字符类型，如果要存储的数据是固定大小，则应使用

CHAR

数据类型。与

VARCHAR

这种情况相比，可以获得更好的性能。

CHAR

使用比较运算符（如=，<>，>，<等）比较值时，MySQL不考虑尾随空格。使用

CHAR

值进行模式匹配时，

LIKE

运算符会考虑尾随空格。

VARCHAR：

是可变长度的字符串，其长度最多可达65,535。MySQL将

VARCHAR

值存储为1字节或2字节长度前缀加上实际数据。长度前缀指定值中的字节数。MySQL在存储

VARCHAR

值时不会填充空间。此外，MySQL在插入或选择

VARCHAR

值时会保留尾随空格。

TEXT

：使用

TEXT

列的类型时，不必指定存储长度。MySQL的提供了四种

TEXT

类型：

TINYTEXT

【可存储255个字符】，

TEXT

【可存储65,535个字符】，

MEDIUMTEXT

【可存储16,777,215个字符】，和

LONGTEXT

【可存储4,294,967,295个字符】。

DATE：

是用于管理日期值的五种时态数据类型之一。MySQL使用

yyyy-mm-dd

格式存储日期值。

TIME：

MySQL使用’

HH:MM:SS

‘格式查询并显示表示一天中24小时内的时间的时间值。

DATETIME：

来存储包含日期和时间的值。

TIMESTAMP：

它包含日期和时间的组合。

YYYY-MM-DD HH:MM:SS

格式其被固定在19个字符。

TIMESTAMP

值范围从

1970-01-01 00:00:01 UTC

到

2038-01-19 03:14:07 UTC

。如果要存储超过2038的时间值，则应使用

DATETIME

而不是

TIMESTAMP

。

JSON：

可以直接存储JSON数据，JSON列不能具有默认值。

更多数据类型可查阅官网：

https://dev.mysql.com/doc/refman/8.0/en/data-types.html

原文链接：https://blog.csdn.net/QingXu1234/article/details/128344048