[MySQL] Note(4) How to add, modify, delete Table

Last Updated on 2021-05-15 by Clay

There are usually several tables in a database, and there are many data stored. In addition to query the data, if we are the database administrator, we also need to know how to “add“, “modify“, “delete” the table and the fields and data in the table.

Let’s take a look about these instructions.

Data Type of data

Before we inserting the first new data, we need to have some understanding of SQL data types.

• char(n): Fixed-length string data, n is the upper limit of string length (bytes), which can only be 1 – 8000.
• varchar(n): Variable length data, n is also the upper limit of string length (bytes), which can only be 1 – 8000.

(Note: When the data item size is fixed, it is suitable for char, if the length changes greatly, it is suitable for varchar)

The above are basic character and numeric data types. SQL data types are more than that, but today’s notes will not use more types.

Create a Table

test is a database I just created. I can use show tables to see that there are no tables in it.

一開始我創造了個叫『test』的資料庫才實驗，一開始剛創造的時候，要使用以下指令來使用資料庫、並顯示 Table。

use test;
show tables;

So we need to create a new TABLE first.

create table Students(
    SID int,
    Name varchar(10),
    Grade int,
    primary key (SID)
);
show tables

Output:

Now we can see that there is a table of students in our test database.

Delete a Table

If you want to delete a table, you can use the following command:

drop table "Your_Table_Name"; 

Take students table as an example:

drop table students;

Add a new data

We can use the following command to add a new data into table.

insert into students values (1, 'Clay', 100);

The inserted data must match the (int, varchar, int) we set at the beginning.

We can also insert multiple data at once.

insert into students values (2, 'Akito', 80), (3, 'ccs96307', 90);

Then we look at the status of the current table:

The three data have been stored in sequence.

Delete data

delete from "Your_Table_Name" where "Condition";

For example, I can delete the data with SID 2 like this:

delete from students where SID = 2;
select * from students;

Output:

Add a new column

If we want to add data fields temporarily, we can use the following command to add fields:

alter table "Your_Table_Name" add "Column_Name" "Data type"

For example:

alter table students add home varchar(20);
select * from students;

Output:

Modify data

Suppose we add a new field “home”, how should we use the existing data in the new field?

update "Your_Table_Name" set "Column" = "New value" where "condition";

You can update the data like this:

update students set home = "Taipei" where SID = 1;
select * from students;

Output:

Delete column

If a field is no longer used, we can delete it with the following command:

alter table "Your_Table_Name" drop "Column";

For example, I delete the newly added “home” field:

alter table students drop home;
select * from students;

Output: