如何使用终端操作数据库?
mysql——关系型数据库
1.登录数据库服务器:
[root@iz8vbfgdvlkk451qffy5xxz ~]# mysql -uroot -p******(自己的密码)
2.查询数据库服务器中所有的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
+--------------------+
5 rows in set (0.00 sec)
3.选中某个数据库操作
mysql> select*from admin;
ERROR 1046 (3D000): No database selected
mysql> use student
Database changed
数据库中查询
mysql> select*from admin;
4.退出数据库服务器
mysql> exit
5.在数据库服务器中创建我们的数据库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
选中某个数据库查看该数据库中所有的数据表
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
6.创建数据表
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.01 sec)
查看数据表是否创建成功
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| 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.01 sec)
查看数据表中的记录
mysql> select * from pet;
Empty set (0.00 sec)
给数据表中添加数据记录
mysql> INSERT INTO pet
->VALUES ('Puffball','Diane','hamster','f','1999-03-07',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> select * from pet;
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f | 1999-03-07 | NULL |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
7.mysql常用数据类型
数值类型
日期/时间类型
字符串类型
详细MySQL数据类型https://www.runoob.com/mysql/mysql-data-types.html
8.删除数据(后面附带一个详细查找)
mysql> select * from pet;
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f | 1999-03-19 | NULL |
| huoguo | jlc | dog | m | 2022-04-26 | NULL |
| candy | jia | cat | m | 2022-04-26 | NULL |
| bodg | lili | pig | f | 2023-11-26 | NULL |
+----------+-------+---------+------+------------+-------+
4 rows in set (0.00 sec)
mysql> delete from pet where name='bodg';
Query OK, 1 row affected (0.00 sec)
mysql> select * from pet;
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f | 1999-03-19 | NULL |
| huoguo | jlc | dog | m | 2022-04-26 | NULL |
| candy | jia | cat | m | 2022-04-26 | NULL |
+----------+-------+---------+------+------------+-------+
3 rows in set (0.01 sec)
mysql> select * from pet where name='huoguo';
+--------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+-------+
| huoguo | jlc | dog | m | 2022-04-26 | NULL |
+--------+-------+---------+------+------------+-------+
1 row in set (0.01 sec)
9.修改数据
mysql> update pet set name='ket' where species='pig';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from pet;
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f | 1999-03-19 | NULL |
| huoguo | jlc | dog | m | 2022-04-26 | NULL |
| candy | jia | cat | m | 2022-04-26 | NULL |
| ket | lili | pig | f | 2023-11-26 | NULL |
| petty | oww | cow | f | 2023-11-26 | NULL |
+----------+-------+---------+------+------------+-------+
5 rows in set (0.00 sec)
总结:数据记录常见操作
增加 INSERT
删除 DELETE
修改 UPDATE
查询 SELECT
(不区分大小写)