Quizgum : creating table

Create table

Let's create a table.
To create a table to store data, you need to decide in which database to create the table.
use database name; You must enter a <== statement.
Since we created david_db in the previous lesson, we will create a table in the david_db database. Please select your database.
You can enter show database; to verify that the database exists.
Create it if you don't have it. Using the following command:

CREATAE DATABASE david_db;

Here's how to create a table:

CREATE TABLE table name(
     Field name type,
     Field name type
PRIMARY KEY(field name)

);

The primary key shown above is to specify the only value the record has in the table.
When searching for a specific record or sorting records, it is first referenced.
In the student DB to be created this time, the primary key should be set to the student ID that cannot be overlapped. In that case, students with the same student ID will be protected systematically.
Let's specify the following fields in the table.

I'll make a table with the fields above.
Name the table student_tb.
Please enter the following statement:

CREATE TABLE `student_tb` (
    `sno` int(11) NOT NULL,
    `name` char(10) DEFAULT NULL,
    `det` char(20) DEFAULT NULL,
    `addr` char(80) DEFAULT NULL,
    `tel` char(20) DEFAULT NULL,
    PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And show tables; Enter
As shown below, student_tb is created.
show tables; Let's see a list of tables with commands.

SHOW TABLES;

The generated one was confirmed. So let's check that the fields are set correctly in that table. The command to check is

DESC table name;

Therefore:

DESC student_tb;

mysql> DESC student_tb; enter

Let's see what the above table means.
Field shows the declared field.
Type is the type that the field has.
sno can be up to 11 integers
name can be up to 10 bytes long.
That way you're limiting yourself from crossing certain characters.
Null indicates whether spaces are allowed or not. Student ID is set to not allow blanks.
Not null before Key means the primary key and the field name sno is set by default.
Fifth is the default initial value. is set to null So how do we add another field from that table? It looks like this:

ALTER TABLE table name ADD field name and type to be added AFTER field name;

after fieldname refers to the location. For example, if you enter addr, the field to be added after addr is located.
Then type

ALTER TABLE student_tb add age int after name;

This means that age is placed after name in the student_tb table.

As you can see in the table above, there is a new field called name after name.
If no after field name is selected, the added field is placed last.
Let's add a gender field.

ALTER TABLE student_tb add sex int;

DESC student_tb;

The sex field was added last.
How can I delete a field?
In the above statement, write drop, not add.

ALTER TABLE table name DROP drop to field name; 

Let's delete the age field.

ALTER TABLE student_tb drop age;

DESC student_tb;

If you typed a field and found it, you may have entered it incorrectly.
Of course, you can delete it and add it again.
You can also make corrections. Let's fix it !!

ALTER TABLE table name change old field name new field name type;

ALTER TABLE student_tb change tel phone int;

DESC student_tb;

tel changed to phone
Then let's change it back.

ALTER TABLE student_tb change phone tel char(20);

DESC student_tb;

It is back to its original state. Now let's change the type only.

alter table table name modify existing field name new type; 

Then, to change the type of the det field to int, type:

ALTER TABLE student_tb modify det int;

DESC student_tb;

The above command is to check the structure of the table. To make sure it's changed correctly

Then let's go back to normal.

ALTER TABLE student_tb modify det char(20);

DESC student_tb;

Now let's rename the table.

ALTER TALBE old table name RENAME new table name; 

ALTER TABLE student_tb RENAME school_tb;

This time, the table name is checked, so show tables; Check by typing

SHOW TABLES;

So let's rename it again.

ALTER TABLE school_tb rename student_tb;

SHOW TABLES;

Then, let's delete the table.
Let's create a table to delete before dropping a table !!!

CREATE TABLE `delete_tb` (
    `name` char(10) DEFAULT NULL,
    `tel` char(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SHOW TABLES;

Enter the above in order.

The table is created. Now let's drop this table. mysql> DROP TABLE The name of the table to drop

DROP TABLE delete_tb;

SHOW TABLES;

The deletion is complete. I'll have to start the class again next time.