Create a Table in MySQL (Part -4)

 A MySQL table stores and organizes data in columns and rows as defined during table creation.

In the process of creating a table, you need to specify the following information:

• Column names – We are creating the title, genre, director, and release year columns for our table.
• Varchar of the columns containing characters – Specifies the maximum number of characters stored in the column.
• The integer of the columns containing numbers – Defines numeric variables holding whole numbers.
• Not null rule – Indicates that each new record must contain information for the column.
• Primary key – Sets a column that defines a record.
• First you need to create a Database:

Go inside the database by using ‘use’ command:

Check whether you have any tables inside database:

Now create a Table in RDBMS:

A table consists of a Unique Name which includes ID / Age followed by Integer, Name & Location followed by Varchar and Date as an example.

  Check whether Table got created or not:

Now insert values and character (data) into the table:

So, we have converted Unstructured data into Structured data which is present along with Schema (given table below).

            Schema is nothing but a structure of a table.

 Now change the structure of the table by using command “alter”:

Use ‘truncate’ command to delete all the rows from the table:

Now you can see that table has been successfully removed except Schema.

Use ‘drop’ command to delete entire table:

Now table has been entirely deleted including schema and data.

  Type “system cls” to refresh:

Now Create a Table Again

Use ‘create table students(S_ID int,S_NAME varchar(200),S_AGE int,S_JOINDATE date,S_LOC varchar(200));’

Type ‘insert into students (S_ID,S_NAME,S_AGE,S_JOINDATE,S_LOC) values(1,”Krishna”,35,”2022-03-01”,”Hyderabad”);’

Above syntax for inserting single column value.

To insert multiple values, use

insert into students values(2,”Shiva”,36,’2022-04-01’,”Chennai”),(3,”Brahma”,37,’2022-05-01’,”Mumbai”);

To check whether data has been properly inserted or not, you need to give data query language –  select * from students;

Use ‘update’ command to change the value in particular column:

Use ‘delete’ command to remove particular Row:

 Use following commands to get back the existing data (TCL):

a)      ‘begin work’ – take control by MySQL server of your data. This is very important command while performing DML operations.

b)     ‘rollback’ – to retrieve the deleted data. This command should use after ‘begin work’ only.

c)      ‘commit’ – to save the transaction to the database. You cannot do rollback once you commit the data.

Let’s see with an example:

The terminal prints out information about the table:

• Field – Indicates column name.
• Type – Specifies data type for the column (varchar for characters, int for numbers).
• Null – Indicates whether the column can remain with null values.
• Key – Displays the primary column.
• Default – Displays the column’s default value.
• Extra – Indicates additional information about the columns.

Note: Lock in DB will happen if you forgot to mention ‘commit’ after ‘begin work’.

Grant and Revoke access commands will be done by Admins only not users.

SQL is built on a database while Hive is built on file system, but both execute same on queries wise.