数据库的更改4

redhat_yan

已于 2022-08-23 14:59:43 修改

阅读量222

点赞数

分类专栏： mariadb和oracle 文章标签：数据库 linux mysql

于 2021-07-26 10:06:43 首次发布

本文链接：https://blog.csdn.net/westos_yanzheng/article/details/119103213

版权

mariadb和oracle 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

系列文章目录

数据库的更改4

数据库的更改

系列文章目录
一、更改库的名字
二、更改表的名字

一、更改库的名字

库的名字一般在数据库中不更改

[root@server15 mnt]# cd /var/lib/mysql/
[root@server15 mysql]# ls
aria_log.00000001  ibdata1      ib_logfile1  mysql.sock          westos
aria_log_control   ib_logfile0  mysql        performance_schema
[root@server15 mysql]# mv westos yan
[root@server15 mysql]# systemctl restart mariadb
[root@server15 mysql]# mysql -uroot -p
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| yan                |
+--------------------+
4 rows in set (0.00 sec)

这样可能导致数据丢失,所以我把库的名字改回去了。

二、更改表的名字

MariaDB [(none)]> USE westos;
MariaDB [westos]> SHOW TABLES;
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+
MariaDB [westos]> ALTER TABLE linux RENAME userlist;
MariaDB [westos]> SHOW TABLES;
+------------------+
| Tables_in_westos |
+------------------+
| userlist         |
+------------------+
1 row in set (0.00 sec)

2.1给表添加一个字段

MariaDB [westos]> DESC linux;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(6)  | NO   |     | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
MariaDB [westos]> ALTER TABLE linux ADD age varchar(4);
MariaDB [westos]> DESC linux;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(6)  | NO   |     | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
| age      | varchar(4)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

2.2给表删掉一个字段

MariaDB [westos]> ALTER TABLE linux DROP age;
MariaDB [westos]> DESC linux;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(6)  | NO   |     | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

2.3添加一个字段外加条件

MariaDB [westos]> ALTER TABLE linux ADD age varchar(4) AFTER username;
MariaDB [westos]> DESC linux;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(6)  | NO   |     | NULL    |       |
| age      | varchar(4)  | YES  |     | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

2.4更新表格里面的数据

MariaDB [westos]> SELECT * FROM linux;
+----------+------+----------+
| username | age  | password |
+----------+------+----------+
| lee      | NULL | 123      |
| lee1     | NULL | 123      |
| lee2     | NULL | 123      |
+----------+------+----------+
MariaDB [westos]> UPDATE linux SET age='123';
MariaDB [westos]> SELECT * FROM linux;
+----------+------+----------+
| username | age  | password |
+----------+------+----------+
| lee      | 123  | 123      |
| lee1     | 123  | 123      |
| lee2     | 123  | 123      |
+----------+------+----------+
MariaDB [westos]> UPDATE linux SET age='18' WHERE username='lee1';
MariaDB [westos]> SELECT * FROM linux;
+----------+------+----------+
| username | age  | password |
+----------+------+----------+
| lee      | 123  | 123      |
| lee1     | 18   | 123      |
| lee2     | 123  | 123      |
+----------+------+----------+