MySQL 8.0.22 | How to alter and drop databases or tables (DDL)
Hello, World!
We’ve learned how to create databases and tables in my old posting.
So today, I’ll introduce you how to revise, delete the database and table.
1. What kind of language we use for managing database and table?
There are the three main languages to manage the database and the table that you must know.
(1) Create
(2) Alter
(3) Drop
As you can assume by their look, ‘alter’ means changing, and ‘drop’ means deleting. You can also call them as ‘DDL(Data definition Language)’, But enough of all the fancy words, let’s just get this started!
2. Alter
Based on what we’ve learned from the posting above I’ll just quickly make one.
mysql> create database mall;
Query OK, 1 row affected (0.01 sec)mysql> use mall;
Database changed
mysql> create table member(
-> id varchar(8) primary key,
-> name varchar(5) not null,
-> phone varchar(10) not null
-> );
Query OK, 0 rows affected (0.07 sec)mysql> desc member;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(8) | NO | PRI | NULL | |
| name | varchar(5) | NO | | NULL | |
| phone | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
I put ‘primary key’ in id, ‘not null’ in name and phone. If you’re not familiar with the constraint and key, you can check out my last posting.
(1) Changing name of the table (Rename)
Don’t you think the table’s name is too long? Let’s change it into ‘mem’
mysql> alter table member rename mem;
Query OK, 0 rows affected (0.03 sec)
(2) Adding column in the table (Add)
I think it’d be better if we have email addresses of our customers as well.
mysql> alter table mem add column email varchar(20) not null unique;
Query OK, 0 rows affected (0.11 sec)
(3) Changing constraint of the column (Modify)
mysql> alter table mem modify column phone varchar(15) not null unique;
Query OK, 0 rows affected (0.02 sec)
(4) Changing name and constraint of the column (Change)
If you don’t like the name of columns, use Change. It’s like modify, but can also change name of columns. You can also change or add data type and constraints and key.
mysql> alter table mem change name user varchar(13);
Query OK, 0 rows affected (0.10 sec)
(5) Deleting column (Drop)
Wait, do we really need email address? I’ll just gonna delete it.
mysql> alter table mem drop column email;
Query OK, 0 rows affected (0.07 sec)
If you’ve done thing exactly same with me, you will see next table when you type desc mem;.
mysql> desc mem;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(8) | NO | PRI | NULL | |
| user | varchar(13) | YES | | NULL | |
| phone | varchar(15) | NO | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)If you do, Well Done!
3. Drop
What if we don’t need this table anymore? We can simply use ‘drop’.
(1) Deleting table
Query is drop table tableName;
mysql> drop table mem;
Query OK, 0 rows affected (0.03 sec)mysql> show tables;
Empty set (0.02 sec)
I typed show tables; , and we can see now the table is gone.
(2) Deleting Database
Deleting database is exactly same with the deleting table. Query is drop database databaseName; . We named our database as mall in the beginning.
mysql> drop database mall;
Query OK, 0 rows affected (0.01 sec)There, when you type show databases; , you would see all the work we’ve done today is gone(:0)…
Today I made extra Query explanation so you can understand it better, cause for me(and probably for everyone too) it’s really hard to recognize other people’s query. Hope you like it.
In our next posting I’ll talk about How to revising or deleting tuples.
See you Tomorrow!
