4. mysql数据库操纵常用命令

最新推荐文章于 2025-07-10 18:32:59 发布

原创最新推荐文章于 2025-07-10 18:32:59 发布 · 1k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #SQL #数据库

mysql 同时被 2 个专栏收录

7 篇文章

订阅专栏

SQL

6 篇文章

订阅专栏

本文介绍了MySQL的基本操作，包括显示、创建及切换数据库，创建表，显示表及其详细信息，插入、修改和删除数据等。通过具体实例展示了命令行插入数据的方法及如何通过导入本地文件批量插入数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

显示数据库

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
| world              |
+--------------------+
7 rows in set (0.00 sec)

创建数据库

//创建了一个名为menageries的数据库
mysql> CREATE DATABASE menageries;
Query OK, 1 row affected (0.03 sec)

切换数据库

//USE命令和QUIT命令相似，不需要以分号结尾，当然如果你加上也不会报错
mysql> USE test
Database changed

创建表

//在menageries数据库中创建一张名为宠物的关系表
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.42 sec)

显示表

mysql> SHOW TABLES;
+----------------------+
| Tables_in_menageries |
+----------------------+
| pet                  |
+----------------------+
1 row in set (0.00 sec)

显示表里面的详细信息

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

插入数据

1.通过命令行的方式插入

mysql> INSERT INTO pet
    -> VALUES ('Fluffy', 'Harold', 'cat', 'f','1993-02-04', NULL);
Query OK, 1 row affected (0.13 sec)

2.通过导入本地文件的方式插入

//文件内容，注意相邻的数据之间用TAB隔开，空数据用\N符号表示
Claws   Gwen    cat m   1994-03-17  \N
Buffy   Harold  dog f   1989-05-13  \N
Fang    Benny   dog m   1990-08-27  \N
Bowser  Diane   dog m   1979-08-31  1995-07-29
Chirpy  Gwen    bird    F   1998-09-11  \N
Whistler    Gwen    bird    \N  1997-12-09
Slim    Benny   snake   m   1996-04-29  \N

//插入命令，注意Windows要加上下面一行命令，不然无法插入成功
mysql> LOAD DATA LOCAL INFILE 'E:/pet.txt' INTO TABLE pet
    -> LINES TERMINATED BY '\r\n';
Query OK, 7 rows affected, 1 warning (0.08 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 1

//浏览插入的数据
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | F    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)

修改数据与删除数据

删除数据

//删除掉pet表中的全部数据
mysql> DELETE FROM pet;
Query OK, 8 rows affected (0.07 sec)

//删除后查询是否正确删除
mysql> SELECT * FROM pet;
Empty set (0.00 sec)

//查询知，delete只是删除了数据，并未删除关系表的整体结构
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.04 sec)

修改数据

//将Fang的出生日期改为2000-08-27
mysql> UPDATE pet SET birth = '2000-08-27' WHERE name = 'Fang';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

//修改后表中的数据
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 2000-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | F    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)

删除掉值为NULL的行

//删除掉性别未知的宠物的信息，注意这里的用法
mysql> DELETE FROM pet WHERE sex IS NULL;
Query OK, 1 row affected (0.08 sec)

//删除后
mysql> SELECT * FROM pet;
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws  | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang   | Benny  | dog     | m    | 2000-08-27 | NULL       |
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen   | bird    | F    | 1998-09-11 | NULL       |
| Slim   | Benny  | snake   | m    | 1996-04-29 | NULL       |
+--------+--------+---------+------+------------+------------+
7 rows in set (0.00 sec)