登录
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)