MySQL基本数据库管理命令

猫头虎

发布于 2024-04-07 20:09:44

690

发布于 2024-04-07 20:09:44

文章被收录于专栏：用户11053981的专栏

MySQL基本数据库管理命令

安装 MySQL 数据库

用 yum 或者 apt 安装包管理器 MySQL 数据库。

# yum install mysql mysql-client mysql-server  (on `Yum` based Systems)

# apt-get install mysql mysql-client mysql-server (on `Apt` based Systems)

启动 MySQL

启动 MySQL 数据库服务

# service mysqld start
or
# service mysql start

安装好一个 MySQL 数据库,启动后,下一步就可以进入MySQL数据库了

# mysql -u root -p
Welcome to the MySQL monitor.  Commands end with ; or \\g.

Your MySQL connection id is 195 

Server version: 31-0+wheezy1 (Debian) 

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 

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>

创建数据库

mysql> create database rumenz ;
Query OK, 1 row affected (02 sec) 
mysql>

Note: Query OK表示数据库已创建。

mysql> show databases; 
+--------------------+
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| `rumenz`            | 
| test               | 
+--------------------+ 
9 rows in set (00 sec) 
mysql>

选择数据库

现在你需要选择要处理的数据库。

mysql> use rumenz;
Database changed
mysql>

在 MySQL 中创建表

在这里，我们将创建一个表test_table三个字段为：

mysql> CREATE TABLE test_table (
    -> id Int(3), 
    -> first_name Varchar (15), 
    -> email Varchar(20) 
    -> ); 
Query OK, 0 rows affected (08 sec) 
mysql>

Note: 上面的查询 OK这意味着表的创建没有任何错误。要查看该表，请运行以下查询。

mysql> show tables; 
+-------------------+ 
| Tables_in_rumenz | 
+-------------------+ 
| test_table           | 
+-------------------+ 

1 row in set (00 sec) 

mysql>
mysql> show columns from test_table; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+ 
3 rows in set (00 sec)

mysql>

int 是整数
varchar是具有定义的可变长度的字符。Type 后面的值是它可以存储数据的字段长度。

现在我们需要添加一列 last_name在first_name列之后。

mysql> ALTER TABLE test_table ADD last_name varchar (20) AFTER first_name; 
Query OK, 0 rows affected (16 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show columns from test_table; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| last_name  | varchar(20) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+ 

4 rows in set (00 sec) 

mysql>

在 MySQL 中添加列

现在我们将在右侧添加一列，比如一列 country 在email 的右边 .

mysql> ALTER TABLE test_table ADD country varchar (15) AFTER email; 
Query OK, 0 rows affected (16 sec) 
Records: 0  Duplicates: 0  Warnings: 0 

mysql>

验证

mysql> show columns from test_table; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| last_name  | varchar(20) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
| country    | varchar(15) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+
5 rows in set (00 sec) 

mysql>

在字段中插入值

mysql> INSERT INTO test_table VALUES ('1' , 'Ravi' , 'Saive' , 'raivsaive@xyz.com' , 'India' );
Query OK, 1 row affected (02 sec) 

mysql>

批量插入值

mysql> INSERT INTO test_table VALUES ('2' , 'Narad' , 'Shrestha' , 'narad@xyz.com' , 'India' ), ('3' , 'user' , 'singh' , 'user@xyz.com' , 'Aus' ), ('4' , 'rumenz' , '[dot]com' , 'rumenz@gmail.com' , 'India' );
Query OK, 3 rows affected (05 sec) 
Records: 3  Duplicates: 0  Warnings: 0

查询结果。

mysql> select * from test_table; 
+------+------------+-----------+-------------------+---------+ 
| id   | first_name | last_name | email             | country | 
+------+------------+-----------+-------------------+---------+ 
|    1 | Ravi     | Saive     | raivsaive@xyz.com | India   | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | India   | 
|    3 | user       | singh     | user@xyz.com      | Aus     | 
|    4 | rumenz    | [dot]com  | rumenz@gmail.com | India   | 
+------+------------+-----------+-------------------+---------+ 

4 rows in set (00 sec)

mysql>

删除记录

假设上面输出中的第三条数据无效，我们需要删除第三条数据。

mysql> DELETE FROM test_table WHERE id = 3;

Query OK, 1 row affected (02 sec)

查询结果

mysql> select * from test_table;

+------+------------+-----------+-------------------+---------+ 
| id   | first_name | last_name | email             | country | 
+------+------------+-----------+-------------------+---------+
|    1 | Ravi       | Saive     | raivsaive@xyz.com | India   | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | India   | 
|    4 | rumenz    | [dot]com  | rumenz@gmail.com | India   | 
+------+------------+-----------+-------------------+---------+
3 rows in set (00 sec)

更新字段中的值

需要编辑 id (=4)。

mysql> UPDATE test_table SET id = 3 WHERE first_name = 'rumenz'; 
Query OK, 1 row affected (02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

查询

mysql> UPDATE test_table SET id = 3 WHERE first_name = 'rumenz'; 
Query OK, 1 row affected (02 sec) 
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> UPDATE test_table SET id = 6 WHERE first_name = 'rumenz'AND last_name = '[dot]com'; 
Query OK, 1 row affected (03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

删除 MySQL 中的列

删除country字段

mysql> ALTER TABLE test_table drop country; 
Query OK, 3 rows affected (15 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

查询

mysql> select * from test_table; 

+------+------------+-----------+-------------------+ 
| id   | first_name | last_name | email             | 
+------+------------+-----------+-------------------+ 
|    1 | Ravi       | Saive     | raivsaive@xyz.com | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | 
|    6 | rumenz    | [dot]com  | rumenz@gmail.com | 
+------+------------+-----------+-------------------+
3 rows in set (00 sec) 

mysql>

在 MySQL 修改表名

将test_table改成rumenz_table.

mysql> RENAME TABLE test_table TO rumenz_table; 
Query OK, 0 rows affected (03 sec)

mysql>

列出所有表

查看当前数据库下的所有表。

mysql> show tables; 

+-------------------+ 
| Tables_in_rumenz | 
+-------------------+ 
| rumenz_table     | 
+-------------------+
1 row in set (00 sec) 

mysql>

该表已重命名。现在备份以上内容MySQL数据库

# mysqldump -u root -p rumenz > rumenz.sql

check the dumped file on your desktop which would have contents something like
-- MySQL dump 13  Distrib 31, for debian-linux-gnu (i686) --
-- Server version 31-0+wheezy1 -- 
Dump completed on 2013-09-02 12:55:37

维护MySQL 数据库的备份始终是一个好主意。恢复备份MySQL Data 又是一行简单的代码，你需要在终端提示符下运行。

删除数据库

mysql> drop database rumenz; 
Query OK, 1 row affected (02 sec)

检查数据库服务器上的数据库rumenz。

mysql> show databases; 

+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| my_database        | 
| mysql              | 
| performance_schema | 
| phpmyadmin         | 
| sisso              | 
| test               | 
+--------------------+

7 rows in set (00 sec) 
mysql>

不用担心，我们有备份。

恢复数据库

要恢复丢失的数据库，请运行以下命令。

# mysql -u root -p rumenz < rumenz.sql
Enter password:
ERROR 1049 (42000): Unknown database 'rumenz'

还没有创建数据库rumenz. 因此，转到你的 mysql 提示符并创建一个数据库 rumenz。

mysql> create database rumenz; 
Query OK, 1 row affected (00 sec) 

mysql>

现在是时候在你的 shell 提示符下运行 restore 命令了

# mysql -u root -p rumenz < rumenz.sql 
Enter password:

验证你的数据库。

mysql> show databases; 

+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| rumenz            | 
| test               | 
+--------------------+ 
8 rows in set (00 sec)

验证数据库的内容。

mysql> show tables from rumenz;

+-------------------+ 
| Tables_in_rumenz | 
+-------------------+ 
| rumenz_table     | 
+-------------------+ 
1 row in set (00 sec)

mysql>

验证你恢复的表的内容。

mysql> select * from rumenz_table; 

+------+------------+-----------+-------------------+ 
| id   | first_name | last_name | email             | 
+------+------------+-----------+-------------------+ 
|    1 | Ravi       | Saive     | raivsaive@xyz.com | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | 
|    6 | rumenz    | [dot]com  | rumenz@gmail.com | 
+------+------------+-----------+-------------------+

3 rows in set (00 sec)

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2024-04-07，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库