How to Install Operating System ??Complete Guide Installing The Operating System

How to Create Table Database

eBook Computer – After the previous discussion, we created a database, this time we will discuss the table or rather make a table in the MySQL database. Like a house, a database can be likened to a large house, while a table is the contents of a house such as a refrigerator, cupboards which will later be filled with some clothes or necessities for cooking.

The purpose of the example above is when we create a MySQL database then the next thing is to make the tools which in this case is a table in the MySQL database which we will fill in with the data in it, more or less like that image.

Before going forward, there are a number of articles that you might be looking for.

Read too :

Pseudo-code Creating a New Table

Previously what needs to be reminded is that the rules for writing table names are the same as creating a database. For specifications, the authors recommend using lowercase letters and separated by an underscore “_” (without quotation) as a separator. The following is a pseudo-code example in creating a table :

mysql> CREATE TABLE name_table (

-> name_field type (length),

-> name_field type (length),

-> name_field type (length),

-> name_field type (length),

-> PRIMARY KEY (name_field_key)

-> );

 

The form of the query above is a general form of creating a simplified table and is just an example. The following describes the purpose of the simple query above.

  1. CREATE TABLE is the command used to create a table in the MySQL database.
  2. name_table is the name for a table in the database.
  3. name_field is the name for the field or column in the table.
  4. type is the giving of a data type for a field or column.
  5. length is giving a measure of the amount or limit of data entered in a field or column.
  6. PRIMARY KEY is used to define the keys in fields or columns.
  7. name_field_key is a field or column that is intended to be a key in the table.

Some things that must be considered are in the provision of keys or in defining keys in the MySQL database table must be unique, meaning that the data in it must not be the same.

One more thing is the PRIMARY KEY command if from the simple example above to define a key in the table in MySQL database it is located behind it is not mandatory, you can define a front key field or exactly in line with the key name. For more details, it will be discussed when creating a table according to the original query.

 

How to Create a Table in a MySQL Database

In this section, we will actually use SQL queries to create tables in the MySQL database, for example, we will create a table with the customer’s table name, what is needed in the table? here I assume the needs of the customer table :

Show Table

Name FieldTypeLength
id_customervarchar5
name_customervarchar30
addresstext
telephonevarchar15
emailvarchar30

 

In the table above, it can be seen that the id_customer has a star symbol, the symbol is referred to as a key or primary key in the table we will create. The following is a query to create a table in the MySQL database above.

mysql> CREATE TABLE customer (

-> id_customer varchar (5) NOT NULL,

-> name_customer varchar (30) NOT NULL,

-> address text,

-> telephone varchar (15),

-> email varchar (30),

-> PRIMARY KEY (id_customer)

-> );

 

As the author promised earlier, we will discuss giving a key that does not have to be behind, here is another example in providing a database table key.

mysql> CREATE TABLE customer (

-> id_customer varchar (5) NOT NULL PRIMARY KEY,

-> name_customer varchar (30) NOT NULL,

-> address text,

-> telephone varchar (15),

-> email varchar (30),

-> );

 

Note that the author moves the PRIMARY KEY command at the beginning or in line with the field name that is designated to be key in the table, both are the same and the query can run normally.

If your query is ready to be executed then please open your DBMS, in this case, the author uses the command line to learn this basic database. Should it have been executed, a notification will appear as follows :

Query OK, 0 rows affected (0.21 sec)

Or for more details, please note the picture below :

 

Still confused about the query? let’s discuss one by one except those already in the pseudo-code discussion above :

  1. the customer is the name of the table you want to make.
  2. id_customer, name_customer, address, telephone, email is a field or column in the table that you want to make.
  3. varchar, a text is a data type for a field or column in that table.
  4. (5) or (30) is the length of data or boundary data given to a field.
  5. NOT NULL is used to define that the table cannot be empty or must be filled.

Just a note that giving a limit or the length of data given to a field when you create a table in MySQL database really does limit the incoming data, then what if the data exceeds the specified capacity? usually, if the data entered is more than specified, then the more data will be lost, as the example above, we give the capacity of the data length is 5 in the id_customer, then what if more than 5? still the one entering is 5, and the one behind will not enter.

 

Mandatory Primary Key is Unique

As the beginning said that a key in the table must be unique, meaning that there should not be the same data in the key field, then how do I determine it automatically? well, we will change the structure of the table above, what will be changed is the id_customer, because id_customer is a disable key that we make this. Here I will try to change the id_customer data type to an integer and of course, the data that will later be entered will be sequential automatically without the need to fill in the data, for example, the data will be filled with numerical digits sequentially from 1, 2, 3 and so on. The following is an example query to create a table in the MySQL database with a unique automatic primary key.

mysql> CREATE TABLE customer (

-> id_customer INT (5) NOT NULL PRIMARY KEY AUTO_INCREMENT,

-> name_customer varchar (30) NOT NULL,

-> address text,

-> telephone varchar (15),

-> email varchar (30),

-> );

 

In the query above that changes is the data type and adds the AUTO_INCREMENT command, what is the AUTO_INCREMENT command? This command is useful for sorting data automatically, meaning that when we add data from the table, we no longer need to input data for key fields, because it will automatically be created sequentially starting from the smallest.

 

Display Table in the MySQL Database

After creating a table in the MySQL database then how do we know that the table has entered or not? how easy it is to use the SHOW command, as follows:

mysql> SHOW TABLES;

The SHOW command will display all the tables that are already in the database, as shown below :

 

What if you want to display the table specifically and in detail? You can use the DESC command which stands for DESCRIBE, or you can also use the extension.

So the tutorial for creating tables in the MySQL database is useful.

Before proceeding, there are a number of articles that might be looking for.

Also read :

Sending
User Review
0 (0 votes)

Add a Comment

Your email address will not be published. Required fields are marked *