MySQL实用命令总结

左手的ㄟ右手 2023-02-18 08:22 11阅读 0赞

以下所有操作均在`windows`下`MySQL 5.7.25`中完成

### 文章目录 ###

*  1 基本操作
 *  2 alter用法
 *  3 enum、set用法
 *  4 查询数据库和表相关信息
 *  5 数据库备份
 *  6 忘记MySQL登录密码
 *  7 MySQL帮助文档
 *  8 数据的级联操作

# 1 基本操作 #

<table> 
 <thead> 
  <tr> 
   <th align="left">用法</th> 
   <th align="left"></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1 创建、删除数据库</td> 
   <td align="left">create database db_name;<br>drop database db_name;</td> 
  </tr> 
  <tr> 
   <td align="left">2 创建、删除表</td> 
   <td align="left">create table tbl_name(……);<br>drop table tbl_name;</td> 
  </tr> 
 </tbody> 
</table>

CREATE TABLE [IF NOT EXISTS] tbl_name(
            column_list
    ) engine=table_type;

*  首先，指定要在CREATE TABLE子句之后创建的表的名称。表名在数据库中必须是唯一的。 `IF NOT EXISTS`是语句的可选部分，允许您检查正在创建的表是否已存在于数据库中。 如果是这种情况，MySQL将忽略整个语句，不会创建任何新的表。 强烈建议在每个`CREATE TABLE`语句中使用`IF NOT EXISTS`来防止创建已存在的新表而产生错误。
 *  其次，在`column_list`部分指定表的列表。字段的列用逗号`(，)`分隔。
 *  最后，需要为`engine`子句中的表指定。可以使用任何存储引擎，如：`InnoDB，MyISAM，HEAP，EXAMPLE，CSV，ARCHIVE，MERGE， FEDERATED或NDBCLUSTER`。如果不明确声明存储引擎，MySQL将默认使用InnoDB。

注：**InnoDB**自**MySQL 5.5**之后成为默认存储引擎。 **InnoDB**表类型带来了诸如ACID事务，引用完整性和崩溃恢复等关系数据库管理系统的诸多好处。在以前的版本中，MySQL使用**MyISAM**作为默认存储引擎。

column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT]

*  `column_name`指定列的名称。每列具有特定数据类型和大小，例如：`VARCHAR(255)`。
 *  `NOT NULL`或`NULL`表示该列是否接受`NULL`值。
 *  `DEFAULT`值用于指定列的默认值。
 *  `AUTO_INCREMENT`指示每当将新行插入到表中时，列的值会自动增加。**每个表都有一个且只有一个AUTO\_INCREMENT列**。

注：被设置为 `AUTO_INCREMENT`的列在插入数据时可用`空值NULL`代替

mysql> drop table if exists t_course;
    Query OK, 0 rows affected (0.16 sec)
    
    mysql> show tables;
    Empty set (0.00 sec)
    
    mysql> create table t_course(
        -> course_id int unsigned auto_increment,
        -> course_name varchar(30) not null,
        -> constraint pk_course_id primary key(course_id)
        -> )engine=innodb auto_increment=1000 default charset=utf8;
    Query OK, 0 rows affected (0.25 sec)
    
    mysql> create table if not exists t_student(
        -> stu_id int auto_increment primary key,
        -> stu_name varchar(30) not null,
        -> stu_age tinyint null default 20,
        -> stu_sex char(2) not null
        -> );
    Query OK, 0 rows affected (0.32 sec)
    
    mysql> create table if not exists t_class(
        -> class_id int,
        -> class_name varchar(30) not null
        -> );
    Query OK, 0 rows affected (0.30 sec)

# 2 alter用法 #

<table> 
 <thead> 
  <tr> 
   <th align="left">用法</th> 
   <th align="left"></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1 修改字段类型为自增</td> 
   <td align="left">被修改的字段类型必须为<code>key</code>，不一定非是primary key（主键），unique key等也可以。否则报错<br>mysql&gt; alter table t_class modify class_id int auto_increment;</td> 
  </tr> 
  <tr> 
   <td align="left">2 设置自增字段初始值</td> 
   <td align="left">mysql&gt; alter table t_student auto_increment=20201000;</td> 
  </tr> 
  <tr> 
   <td align="left">3 修改表中字段-不重命名</td> 
   <td align="left"><code>alter table 表名 modify 列名 类型及约束;</code><br>mysql&gt; alter table t_student modify stu_name varchar(20);</td> 
  </tr> 
  <tr> 
   <td align="left">4 修改表中字段-重命名</td> 
   <td align="left"><code>alter table 表名 change 原名 新名 类型及约束;</code><br>mysql&gt; alter table t_student change stu_name s_name varchar(30);</td> 
  </tr> 
  <tr> 
   <td align="left">5 向表中添加字段</td> 
   <td align="left"><code>alter table 表名 add 列名 类型;</code><br>mysql&gt; alter table t_student add class_id int;</td> 
  </tr> 
  <tr> 
   <td align="left">6 删除表中字段</td> 
   <td align="left"><code>alter table 表名 drop 列名;</code><br>mysql&gt; alter table t_course drop course_name;</td> 
  </tr> 
  <tr> 
   <td align="left">7 向表中添加主键约束</td> 
   <td align="left"><code>alter table 表名 add [constraint 主键名字] primary key(主键字段);</code><br>mysql&gt; alter table t_class add constraint pk_class_id primary key(class_id);</td> 
  </tr> 
  <tr> 
   <td align="left">8 向表中添加外键约束</td> 
   <td align="left"><code>alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段);</code><br>mysql&gt; alter table t_student add constraint fk_class_id foreign key(class_id) references t_class(class_id);</td> 
  </tr> 
  <tr> 
   <td align="left">9 向表中添加唯一约束</td> 
   <td align="left"><code>alter table add [CONSTRAINT 约束名称] unique(字段名称1,字段名称...)</code><br>mysql&gt; alter table t_student add constraint unique_stu_name unique(s_name);</td> 
  </tr> 
  <tr> 
   <td align="left">10 删除主键约束</td> 
   <td align="left">如果被删除的字段是一个<code>key</code>，且<code>key</code>的类型为自增类型，则需要先删除<code>auto_increment</code>,<br> 删除方法即为修改字段类型，使用<code>alter table 表名 modify 字段名 字段类型</code>，而后再删除约束。<br>即将 auto_increment 类型的字段修改为非 auto_increment 类型。<br>mysql&gt; alter table t_student modify stu_id int;<br>mysql&gt; alter table t_student drop primary key;</td> 
  </tr> 
  <tr> 
   <td align="left">11 删除外键约束</td> 
   <td align="left">mysql&gt; alter table t_student drop foreign key fk_class_id;</td> 
  </tr> 
  <tr> 
   <td align="left">12 删除唯一约束</td> 
   <td align="left">mysql&gt; alter table t_student drop index unique_stu_name;</td> 
  </tr> 
  <tr> 
   <td align="left">13 命令行执行外部sql脚本</td> 
   <td align="left">mysql&gt; source C:\Users\Frey\Desktop\create_table_t_institute.sql</td> 
  </tr> 
 </tbody> 
</table>

# 3 enum、set用法 #

**enum**：单选字符串数据类型，适合存储表单界面中的“单选值”。设定enum的时候，需要给定“固定的几个选项”；**表中只存储enum 其中一个值**。

设定enum的格式：enum(“选项1”,“选项2”,“选项3”,…)；

实际上，enum的选项都会对应一个数字，依次是1，2，3，4，5…，最多有65535个选项。使用的时候，可以使用选项的字符串格式，也可以使用对应的数字。  
`stu_sex enum('男', '女')`

**set**：多选字符串数据类型，适合存储表单界面的“多选值”。设定set的时候，同样需要给定“固定的几个选项”；**表中可存储set 中一个及其以上的值**。

设定set的格式：set(“选项1”,“选项2”,“选项3”,…)

同样的，set的每个选项值也对应一个数字，依次是1，2，4，8，16…，最多有64个选项。使用的时候，可以使用set选项的字符串本身（多个选项用逗号分隔），也可以使用多个选项的数字之和（比如：1+2+4=7）  
`stu_hobby set('reading', 'swimming', 'football', 'pingpong')`

<table> 
 <thead> 
  <tr> 
   <th>选项</th> 
   <th>取值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>‘reading’</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>‘swimming’</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>‘football’</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>‘pingpong’</td> 
   <td>8</td> 
  </tr> 
 </tbody> 
</table>

注意：若插入数据时记录中字段的值不在enum、set列表之中，则会插入失败。

示例如下：

*  首先设置stu\_hobby为enum型
 *  其次向表中增加set类型的字段stu\_hobby
 *  然后插入合法数据并查询
 *  最后插入非法数据，其字段值不在enum、set的取值列表中

mysql> alter table t_student modify stu_sex enum('男', '女');
    Query OK, 0 rows affected (0.07 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> alter table t_student add stu_hobby set('reading', 'swimming', 'football', 'pingpong');
    Query OK, 0 rows affected (0.68 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> insert into t_student values(5555, 'Tom', 23, '男', 1, 'reading,football'),(5556, 'Lisa', 23, '女', 1, 'reading,swimming'),(5557, 'Bob', 23, 1, 1, 1),(5558, 'Lucy', 23, 2, 1, 11);
    
    mysql> select * from t_student;
    +----------+--------+---------+---------+----------+---------------------------+
    | stu_id   | s_name | stu_age | stu_sex | class_id | stu_hobby                 |
    +----------+--------+---------+---------+----------+---------------------------+
    | 20201000 | Jack   |     100 | 男      |        1 | NULL                      |
    |     5555 | Tom    |      23 | 男      |        1 | reading,football          |
    |     5556 | Lisa   |      23 | 女      |        1 | reading,swimming          |
    |     5557 | Bob    |      23 | 男      |        1 | reading                   |
    |     5558 | Lucy   |      23 | 女      |        1 | reading,swimming,pingpong |
    +----------+--------+---------+---------+----------+---------------------------+
    5 rows in set (0.00 sec)
    
    mysql> insert into t_student values(5556, 'Lisa', 23, 'nv', 1, 'reading,swimming');
    ERROR 1265 (01000): Data truncated for column 'stu_sex' at row 1
    
    mysql> insert into t_student values(5556, 'Lisa', 23, '男', 1, 'reading,dancing');
    ERROR 1265 (01000): Data truncated for column 'stu_hobby' at row 1

# 4 查询数据库和表相关信息 #

# 5 数据库备份 #

注意：

*  备份时不要进入mysql，直接在命令行界面执行命令即可
 *  可加上`--default-character-set=GBK`选项指定字符编码，例如：`mysqldump -uroot -p --default-character-set=GBK --databases booksys > C:\Users\Frey\Desktop\backup_booksys.sql`
 *  在对数据进行导入、导出操作时**用cmd操作，不要用powershell**，否则会出现许多奇奇怪怪的问题

<table> 
 <thead> 
  <tr> 
   <th align="left">用法</th> 
   <th align="left"><code>语法格式：mysqldump [arguments] &gt; file_name</code></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1 备份所有数据库</td> 
   <td align="left">PS C:\Users\Frey&gt; mysqldump -uroot -p --all-databases &gt; C:\Users\Frey\Desktop\backup_all.sql</td> 
  </tr> 
  <tr> 
   <td align="left">2 备份特定数据库</td> 
   <td align="left">PS C:\Users\Frey&gt; mysqldump -uroot -p --databases db1 db2 db3 &gt; dump.sql</td> 
  </tr> 
  <tr> 
   <td align="left">3 备份单个数据库</td> 
   <td align="left">PS C:\Users\Frey&gt; mysqldump -uroot -p --databases db_name &gt; dump.sql<br>备份单个数据库<strong>可省略–database选项</strong><br>PS C:\Users\Frey&gt; mysqldump -uroot -p db_name &gt; dump.sql</td> 
  </tr> 
  <tr> 
   <td align="left">4 备份数据库中的特定表</td> 
   <td align="left">PS C:\Users\Frey&gt; mysqldump -uroot -p db_name tbl_name1 tbl_name2 &gt; dump.sql</td> 
  </tr> 
 </tbody> 
</table>

# 6 忘记MySQL登录密码 #

**注：如果没有为MySQL添加环境变量，则下述命令执行时，都要进入MySQL目录下的bin目录**

1、进入本机MySQL根目录，找到文件`my.ini`，在`[mysqld]`下添加

skip-grant-tables

2、 `管理员身份进入cmd或powershell`，重启MySQL服务

PS C:\WINDOWS\system32> net stop mysql
    MySQL 服务正在停止.
    MySQL 服务已成功停止。
    
    PS C:\WINDOWS\system32> net start mysql
    MySQL 服务正在启动 .
    MySQL 服务已经启动成功。

3、 重新登录MySQL（此时不使用密码就能成功登录了）

PS C:\WINDOWS\system32> mysql -uroot
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.25 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2019, 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>

4、修改密码

mysql> use mysql;
    Database changed
    
    mysql> update user set authentication_string=password("123456") where user="root";
    Query OK, 1 row affected, 1 warning (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 1
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)

5、再次进入本机MySQL根目录，找到文件`my.ini`，删除`[mysqld]`下添加的命令`skip-grant-tables`

6、重启MySQL服务

7、使用新密码登录

PS C:\WINDOWS\system32> mysql -uroot -p123456

# 7 MySQL帮助文档 #

1、[联机文档][Link 1]  
2、本机文档

mysql> help 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
       Compound Statements
       Data Definition
       Data Manipulation
       Data Types
       Functions
      ……
    
    mysql> help data types
    You asked for help about help category: "Data Types"
    For more information, type 'help <item>', where <item> is one of the following
    topics:
       AUTO_INCREMENT
       BIGINT
       BINARY
       BIT
       BLOB
       BLOB DATA TYPE
       BOOLEAN
       CHAR
    ……

# 8 数据的级联操作 #

[CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (col_name, ...)
        REFERENCES tbl_name (col_name,...)
        [ON DELETE reference_option]
        [ON UPDATE reference_option]
    
    reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

级联删除用法示例：

create table course(
        id int not null,
        name varchar(30) not null,
    
        primary key(id)
    );
    create table student(
        id int not null,
        name varchar(30) not null,
        course_id int not null,
    
        primary key(id),
        foreign key(course_id)
            references course(id)
            -- on delete cascade
    );
    
    insert into course(id, name) 
        values(1001, '计算机科学与技术'),(1002, '网络空间安全');
    insert into student(id,name,course_id)
        values(20210001, '李明', 1002),(20210002, '张三', 1001);

若删除表course中的数据，且被删除的数据被表student中的数据关联的话，则删除失败。只能把student中与被删除数据关联的数据项一起删除，否则会出现student中关联的数据在course中不存在的情况，这显然是不合理的。例如：

mysql> select * from course;
    +------+--------------------------+
    | id   | name                     |
    +------+--------------------------+
    | 1001 | 计算机科学与技术         |
    | 1002 | 网络空间安全             |
    +------+--------------------------+
    2 rows in set (0.05 sec)
    
    mysql> select * from student;
    +----------+--------+-----------+
    | id       | name   | course_id |
    +----------+--------+-----------+
    | 20210001 | 李明   |      1002 |
    | 20210002 | 张三   |      1001 |
    +----------+--------+-----------+
    2 rows in set (0.05 sec)
    
    mysql> delete from course where id=1001;
    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db_stu`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`))

解决方法：

1、建表student时添加级联删除`on delete cascade`

create table student(
        id int not null,
        name varchar(30) not null,
        course_id int not null,
    
        primary key(id),
        foreign key(course_id)
            references course(id)
            on delete cascade
    );

mysql> delete from course where id=1001;
    Query OK, 1 row affected (0.06 sec)

执行之后，student中course\_id=1001的数据被级联删除

mysql> select * from student;
    +----------+--------+-----------+
    | id       | name   | course_id |
    +----------+--------+-----------+
    | 20210001 | 李明   |      1002 |
    +----------+--------+-----------+
    1 row in set (0.05 sec)
    
    mysql>

2、删除student中的外键约束。此方法不用修改表student建表语句

mysql> show create table student;
    +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table   | Create Table                                                                                                                                                                                                                                                                                                                            |
    +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | student | CREATE TABLE `student` (
      `id` int NOT NULL,
      `name` varchar(30) NOT NULL,
      `course_id` int NOT NULL,
      PRIMARY KEY (`id`),
      KEY `course_id` (`course_id`),
      CONSTRAINT `student_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
    +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> alter table student drop foreign key student_ibfk_1;
    Query OK, 0 rows affected (0.10 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> delete from course where id=1002;
    Query OK, 1 row affected (0.06 sec)

执行之后，student中course\_id=1002的数据仍然存在

[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/