MySQL基础一篇打通

举报
LoneWalker、 发表于 2023/08/07 10:12:29 2023/08/07
【摘要】 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

【版权声明】本文为华为云社区用户原创内容，未经允许不得转载，如需转载请自行联系原作者进行授权。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入