mysql:查看数据的基本命令

登录

root@yzp-KLV-WX9:/home/yzp/下载# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> use mydatabase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

展示数据集合

mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| pet                  |
+----------------------+
1 row in set (0.01 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.01 sec)

展示数据集

展示全部内容

mysql> select * from pet;
+------------------------------+-------+----------+------+------------+-------+
| name                         | owner | species  | sex  | birth      | death |
+------------------------------+-------+----------+------+------------+-------+
| 惠斯勒Gwen鸟 N 1997-12-0     | NULL  | NULL     | NULL | NULL       | NULL  |
| Puffball                    | Diane | hamster  | f    | 1999-03-30 | NULL  |
| hhh                         | yzp   | hamseter | f    | 1999-03-30 | NULL  |
| 惠斯勒 Gwen 鸟 N 1997-12     | NULL  | NULL     | NULL | NULL       | NULL  |
| 惠斯勒 Gwen 鸟 N 1997-12     | NULL  | NULL     | NULL | NULL       | NULL  |
| 惠斯勒                       | Gwen  | 鸟       | NULL | 1997-12-09 | NULL  |
+------------------------------+-------+----------+------+------------+-------+
6 rows in set (0.00 sec)

展示指定内容

mysql> select * from pet where name='hhh';
+------+-------+----------+------+------------+-------+
| name | owner | species  | sex  | birth      | death |
+------+-------+----------+------+------------+-------+
| hhh  | yzp   | hamseter | f    | 1999-03-30 | NULL  |
+------+-------+----------+------+------------+-------+
1 row in set (0.00 sec)

多条件展示指定内容

mysql> select * from pet where birth='1999-03-30' and sex='f';
+----------+-------+----------+------+------------+-------+
| name     | owner | species  | sex  | birth      | death |
+----------+-------+----------+------+------------+-------+
| Puffball | Diane | hamster  | f    | 1999-03-30 | NULL  |
| hhh      | yzp   | hamseter | f    | 1999-03-30 | NULL  |
+----------+-------+----------+------+------------+-------+
2 rows in set (0.00 sec)

mysql> select * from pet where name='hhh' or sex='f';
+----------+-------+----------+------+------------+-------+
| name     | owner | species  | sex  | birth      | death |
+----------+-------+----------+------+------------+-------+
| Puffball | Diane | hamster  | f    | 1999-03-30 | NULL  |
| hhh      | yzp   | hamseter | f    | 1999-03-30 | NULL  |
+----------+-------+----------+------+------------+-------+
2 rows in set (0.00 sec)

检索特定列的内容

mysql> select name ,birth from pet;
+------------------------------+------------+
| name                         | birth      |
+------------------------------+------------+
| 惠斯勒Gwen鸟 N 1997-12-0     | NULL       |
| Puffball                     | 1999-03-30 |
| hhh                          | 2000-03-03 |
| 惠斯勒 Gwen 鸟 N 1997-12     | NULL       |
| 惠斯勒 Gwen 鸟 N 1997-12     | NULL       |
| 惠斯勒                       | 1997-12-09 |
+------------------------------+------------+
6 rows in set (0.00 sec)

无重复数据检索

+------------------------------+------------+
| name                         | birth      |
+------------------------------+------------+
| 惠斯勒Gwen鸟 N 1997-12-0     | NULL       |
| Puffball                     | 1999-03-30 |
| hhh                          | 2000-03-03 |
| 惠斯勒 Gwen 鸟 N 1997-12     | NULL       |
| 惠斯勒 Gwen 鸟 N 1997-12     | NULL       |
| 惠斯勒                       | 1997-12-09 |
+------------------------------+------------+
6 rows in set (0.00 sec)

mysql> select distinct name from pet;
+------------------------------+
| name                         |
+------------------------------+
| 惠斯勒Gwen鸟 N 1997-12-0     |
| Puffball                     |
| hhh                          |
| 惠斯勒 Gwen 鸟 N 1997-12     |
| 惠斯勒                       |
+------------------------------+
5 rows in set (0.00 sec)

修改内容

更新内容

mysql> update pet set birth ='2000-03-03' where name='hhh';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from pet where name='hhh' ;
+------+-------+----------+------+------------+-------+
| name | owner | species  | sex  | birth      | death |
+------+-------+----------+------+------------+-------+
| hhh  | yzp   | hamseter | f    | 2000-03-03 | NULL  |
+------+-------+----------+------+------------+-------+
1 row in set (0.01 sec)

插入内容

mysql> INSERT INTO pet
    ->        VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.02 sec)

mysql> select * from pet;
+------------------------------+-------+----------+------+------------+-------+
| name                         | owner | species  | sex  | birth      | death |
+------------------------------+-------+----------+------+------------+-------+
| 惠斯勒Gwen鸟 N 1997-12-0     | NULL  | NULL     | NULL | NULL       | NULL  |
| Puffball                     | Diane | hamster  | f    | 1999-03-30 | NULL  |
| hhh                          | yzp   | hamseter | f    | 2000-03-03 | NULL  |
| 惠斯勒 Gwen 鸟 N 1997-12     | NULL  | NULL     | NULL | NULL       | NULL  |
| 惠斯勒 Gwen 鸟 N 1997-12     | NULL  | NULL     | NULL | NULL       | NULL  |
| 惠斯勒                       | Gwen  | 鸟       | NULL | 1997-12-09 | NULL  |
| Puffball                     | Diane | hamster  | f    | 1999-03-30 | NULL  |
+------------------------------+-------+----------+------+------------+-------+
7 rows in set (0.00 sec)

删除数据集

mysql> delete from pet;
Query OK, 7 rows affected (0.02 sec)