博客

万字长文——MySQL基础一篇打通

  • Post author:
  • Post category:mysql


准备工作

直接使用MySQL提供的数据，

下载MySQL示例数据库

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

允许的操作：

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

相关语法： 

SELECT column1, column2,...
FROM tbl
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...

ASC代表升序 和 DESC代表降序，默认情况下是升序。

比如查询客户表数据，按姓 降序 ，名 升序

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支持以下类型的连接：

  1. CROSS JOIN
  2. INNER JOIN
  3. LEFT JOIN
  4. 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


版权声明：本文为QingXu1234原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
Tags: