mysql学习笔记一如何使用终端操作数据库

如何使用终端操作数据库？

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
（不区分大小写）