数据库常用操作

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

一、命令行进入数据库

按住WIN + r ,输入cmd进入命令行模式

mysql -u root -p

输入后会提示输入密码，显示如下就是成功

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.43 MySQL Community Server - GPLCopyright (c) 2000, 2025, 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.

二、创建数据库

1.创建数据库

输入以下代码

mysql> create database datas;

显示如下就是成功

Query OK, 1 row affected (0.03 sec)

2.查看数据库

注意，这里是databases

mysql> show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| datas              |
| information_schema |
| mysql              |
| performance_schema |
| playermanage       |
| studentmanage      |
| sys                |
| test_db            |
| timetest           |
+--------------------+
9 rows in set (0.02 sec)

---

3.删除数据库

mysql> drop database datas;
Query OK, 0 rows affected (0.04 sec)

这里显示删除成功，查看一下数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| playermanage       |
| studentmanage      |
| sys                |
| test_db            |
| timetest           |
+--------------------+
8 rows in set (0.00 sec)

里面没有datas的记录了

4.使用数据库

mysql> use datas;

我们重新创建一个datas的数据库

mysql> create database datas;
Query OK, 1 row affected (0.02 sec)mysql> use datas;
Database changed

5.查看当前数据库

mysql> select database();
+------------+
| database() |
+------------+
| datas      |
+------------+
1 row in set (0.00 sec)

6.注意事项

1.在命令行执行数据库相关语句后面需要加；

2.创建表时，需要先试用数据库

三.数据库表操作

1.创建表

需要提前使用使用一个数据库！！

mysql> create table teacher(id int primary key auto_increment not null,name varchar(10) unique not null);

这是创建了一个教师表，里面有id和姓名属性，相关约束和字符创类型我会后面说

2.查看表结构

mysql> desc teacher;

这里会显示刚刚创建的表结构

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

3.添加列

mysql> alter table teacher add age int not null;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.修改列

mysql> alter table teacher change name name varchar(11) unique not null;
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

5.查看表

mysql> show tables;
+-----------------+
| Tables_in_datas |
+-----------------+
| teacher         |
+-----------------+
1 row in set (0.02 sec)

6.对表重新命名

mysql> rename table teacher to teachers;
Query OK, 0 rows affected (0.04 sec)

7.删除表中的一列

mysql> alter table teachers drop name;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

四.数据库的CURD操作

1.增加列

mysql> insert into teachers values(1,23),(2,34),(3,24);

2.查询数据

mysql> select * from teachers;
+----+-----+
| id | age |
+----+-----+
|  1 |  23 |
|  2 |  34 |
|  3 |  24 |
+----+-----+
3 rows in set (0.01 sec)

3.修改数据

mysql> update teachers set age = 30 where id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

这里查询一下是否修改成功

+----+-----+
| id | age |
+----+-----+
|  1 |  30 |
|  2 |  34 |
|  3 |  24 |
+----+-----+
3 rows in set (0.00 sec)

4.删除数据

mysql> delete from teachers where id = 1;
Query OK, 1 row affected (0.02 sec)

+----+-----+
| id | age |
+----+-----+
|  2 |  34 |
|  3 |  24 |
+----+-----+
2 rows in set (0.00 sec)