MySQL基础：DML语句总结

  • Z时代
  • 2024-01-10
  • 分类：综合

database

SQL语言大致分为`DCL`、`DDL`、`DML`三种，本文主要介绍`MySQL 5.7`版本的`DCL`语句。

blog：https://www.cnblogs.com/Rohn/

SQL语言大致分为DCL、DDL、DML三种，本文主要介绍MySQL 5.7版本DML语句。

目录

  • 概述
    • 关键字
  • INSERT语句
    • 语法格式
  • DELETE语句
    • 语法格式
    • 单表删除
    • 多表删除
  • UPDATE语句
    • 语法格式
      • 单表更新
      • 多表更新
  • SELECT语句
    • 语法格式

概述

DML(Data Manipulation Language)语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性.

关键字

  • INSERT

  • DELETE

  • UPDATE

  • SELETE

INSERT语句

参考：INSERT Syntax

INSERT语句主要用于向数据表中插入数据。

语法格式

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]
value:
    {expr | DEFAULT}
value_list:
    value [, value] ...
assignment:
    col_name = value
assignment_list:
    assignment [, assignment] ...

DELETE语句

DELTE语句主要用来删除 MySQL 数据表中的记录

语法格式

单表删除

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

多表删除

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

UPDATE语句

参考：UPDATE Syntax

UPDATE语句主要用于修改或更新数据表中的数据。

语法格式

单表更新

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
value:
    {expr | DEFAULT}
assignment:
    col_name = value
assignment_list:
    assignment [, assignment] ...

多表更新

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

SELECT语句

SELECT语句主要用来查询数据表中的数据。

语法格式

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE "file_name"
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE "file_name"
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

以上是 MySQL基础：DML语句总结 的全部内容， 来源链接： utcz.com/z/534439.html

回到顶部