MySQL 服务常用操作命令(关系型数据库)

1、MySQL 服务设置

  • 在使用 mysql.server 命令之前需要将路径 /usr/local/mysql/support-files 添加到系统环境变量中。

    1
    export PATH=$PATH:/usr/local/mysql/support-files
  • 在使用 mysql 命令之前需要将路径 /usr/local/mysql/bin 添加到系统环境变量中。

    1
    export PATH=$PATH:/usr/local/mysql/bin
  • 具体设置请参照《添加系统环境变量》章节。

2、MySQL 服务常用操作

2.1 MySQL 服务控制命令

  • MySQL 服务控制命令

    1
    2
    3
    4
    5
    # 启动 MySQL 服务
    $ sudo mysql.server start

    Starting MySQL
    .. SUCCESS!
    1
    2
    3
    4
    5
    # 停止 MySQL 服务
    $ sudo mysql.server stop

    Shutting down MySQL
    .. SUCCESS!
    1
    2
    3
    4
    5
    6
    7
    # 重启 MySQL 服务
    $ sudo mysql.server restart

    Shutting down MySQL
    .. SUCCESS!
    Starting MySQL
    .. SUCCESS!
    1
    2
    3
    4
    # 重新加载
    $ sudo mysql.server reload

    SUCCESS! Reloading service MySQL
    1
    2
    3
    4
    # 强制重新加载
    $ sudo mysql.server force-reload

    SUCCESS! Reloading service MySQL
    1
    2
    3
    4
    # 查看 MySQL 服务状态
    $ mysql.server status

    ERROR! Multiple MySQL running but PID file could not be found (61998 62083 )

2.2 MySQL 服务连接命令

  • MySQL 服务连接命令

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    # 登录 MySQL 服务
    # mysql -u 用户名 -p 密码
    $ mysql -u root -p

    Enter password:
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 930
    Server version: 8.0.11 MySQL Community Server - GPL

    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql>
    1
    2
    3
    4
    # 登出 MySQL 服务
    > exit

    Bye

3、MySQL 服务帮助操作

3.1 按照层次看帮助

  • 如果不知道帮助能够提供什么,那么就可以直接用 “? contents” 命令来显示所有可供查询的分类,对于分类可以使用 “? 分类名称” 的方式针对用户感兴趣的内容做进一步的查看,通过这种 “? 类型别名” 的方式,就可以一层层的往下查找用户所关心的主题内容。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    > ? contents
    You asked for help about help category: "Contents"
    For more information, type 'help <item>', where <item> is one of the following
    categories:
    Account Management
    Administration
    Components
    Compound Statements
    Data Definition
    Data Manipulation
    Data Types
    Functions
    Functions and Modifiers for Use with GROUP BY
    Geographic Features
    Help Metadata
    Language Structure
    Plugins
    Storage Engines
    Table Maintenance
    Transactions
    User-Defined Functions
    Utility

3.2 快速查阅帮助

  • 在实际应用中,如果需要快速查阅某项语法时,可以使用 “? 关键字” 方式进行快速查询。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    > ? insert
    Name: 'INSERT'
    Description:
    Syntax:
    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] ...

    INSERT inserts new rows into an existing table. The INSERT ... VALUES
    and INSERT ... SET forms of the statement insert rows based on
    explicitly specified values. The INSERT ... SELECT form inserts rows
    selected from another table or tables. INSERT with an ON DUPLICATE KEY
    UPDATE clause enables existing rows to be updated if a row to be
    inserted would cause a duplicate value in a UNIQUE index or PRIMARY
    KEY.

    For additional information about INSERT ... SELECT and INSERT ... ON
    DUPLICATE KEY UPDATE, see [HELP INSERT SELECT], and
    http://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html.

    In MySQL 8.0, the DELAYED keyword is accepted but ignored by the
    server. For the reasons for this, see [HELP INSERT DELAYED],

    Inserting into a table requires the INSERT privilege for the table. If
    the ON DUPLICATE KEY UPDATE clause is used and a duplicate key causes
    an UPDATE to be performed instead, the statement requires the UPDATE
    privilege for the columns to be updated. For columns that are read but
    not modified you need only the SELECT privilege (such as for a column
    referenced only on the right hand side of an col_name=expr assignment
    in an ON DUPLICATE KEY UPDATE clause).

    When inserting into a partitioned table, you can control which
    partitions and subpartitions accept new rows. The PARTITION option
    takes a list of the comma-separated names of one or more partitions or
    subpartitions (or both) of the table. If any of the rows to be inserted
    by a given INSERT statement do not match one of the partitions listed,
    the INSERT statement fails with the error Found a row not matching the
    given partition set. For more information and examples, see
    http://dev.mysql.com/doc/refman/8.0/en/partitioning-selection.html.

    URL: http://dev.mysql.com/doc/refman/8.0/en/insert.html

3.3 常用的网络资源

  • MySQL 官网 可以下载到各个版本的 MySQL 以及相关客户端开发工具等。

  • MySQL 在线手册 提供了目前最权威的 MySQL 数据库及工具的在线手册。

  • MySQL bug 列表 可以查看到 MySQL 已经发布的 bug 列表,或者向 MySQL 提交 bug 报告。

  • MySQL 的最新消息 通常会发布各种关于 MySQL 的最新消息。

文章目录
  1. 1. 1、MySQL 服务设置
  2. 2. 2、MySQL 服务常用操作
    1. 2.1. 2.1 MySQL 服务控制命令
    2. 2.2. 2.2 MySQL 服务连接命令
  3. 3. 3、MySQL 服务帮助操作
    1. 3.1. 3.1 按照层次看帮助
    2. 3.2. 3.2 快速查阅帮助
    3. 3.3. 3.3 常用的网络资源
隐藏目录