A database is an electronically stored collection of organised data. Even in the days before computers, our predecessors understood the concept of a database, but building and maintaining one was an extremely labor-intensive task. Imagine how difficult it would have been to look for every employee whose salary was less than $10,000 in a manual database that might have been 100 pages long.
These days, it’s impossible to avoid databases. Millions of databases are currently in use worldwide to store and retrieve data of any form, including web technology, personnel records, and strategic data.
Because an end user cannot directly interact with a database or see it, the term “back-end process” is frequently used to describe databases. They work with front-end processes such as PHP, VB, ASP.NET, and so on, and they ask the front end to handle back-end database operations.
Numerous database servers and clients are available, including MariaDB, MongoDB, MySQL, Oracle, and MySQLi. These all have essentially the same syntax. Learning a database’s queries is simple and enjoyable, and mastering one entails taking control of the majority of them.
Let’s begin with some basic database queries. We’ll be using MySQL, which is installed by default in the majority of Linux distributions. If it’s not already installed, you may install it manually from the repository.
Install MySQL Database
Use “yum” or “apt” package manager to install MySQL Database.
Start MySQL
Start MySQL database service as:
After installing a MySQL database, you’ll be prompted to set up an admin password and other settings. Go to your MySQL prompt after the installation and server startup are complete.
In a moment, you should see your MySQL prompt if the login credentials are right. Replace root with the username you have defined and enter the password when asked.
Now carrying out queries at this prompt is very educative and fun.
Create a database myhospital
It reports that the query was correct, means database is created. You can verify your newly created database as.
Select Database
Now you need to select the database to work upon it.
Create Tables in MySQL
Here we will be creating a table say “myhospital” with three fields as:
The above query says OK which means table was created without any error. To verify the table run the below query.
Things are going fine till now. Yup! You can view the columns you created in the table “myhospital” as:
It was nothing less than a magic. Anyway I will tell you about the types of declaration and their meaning.
- Int is Integer
- Varchar is char having variable length as defined. The value after Type is the length of field up-to which it can store data.
OK now we need to add a column say ‘last_name‘ after column ‘first_name‘.
Now, verify it in your table.
Add Column in MySQL
Now we will add a column to the right say a column ‘country‘ to the right of email.
Verify the above column insertion query.
Insert Values in Field
What about inserting values to the field?
How about inserting more than 1 value at a time in the above table.
Verify the above insertion.
Delete Values in Field
Let’s say the third entry in the above output is invalid and we need to delete the third entry.
Verify the above operation.
Update Values in Field
The id (=4) needs to be edited.
Verify the above query.
As it is, the aforementioned query is not a wise one. In cases where “myhospital” appears as the first name, the ID will be changed to 4. To obtain the fewest possible errors, it is usually a good idea to employ multiple columns with where clauses, such as:
Delete Column in MySQL
Let we need to drop (delete) a column we think, is of no importance say ‘country‘ here.
Verify the table.
Rename Table in MySQL
Don’t you think our table name “myhospital” is not very much relevant. How about changing it to myhospital_table.
List all Tables
See all the tables under the current database.
The table has been renamed. Now take a backup of the above MySQL database, in a single line of command without any sophisticated tool. Run the below code at your terminal and not on mysql prompt.
Delete a Database
Check for database ‘myhospital’ on your database server.
The database is lost, but we need not to worry, we are having the backup.
Restore a Database
To restore lost database, run the following command.
OOPS! An error, hey we have not create the database myhospital. So go to your mysql prompt and create a database ‘myhospital‘.
Now time to run restore command at your shell prompt (strictly).
Verify your database.
Verify the contents of database.
Verify the contents of your restored table.
This is undoubtedly not the end; in the following section of the post, we will discuss the concepts of primary keys, foreign keys, numerous tables, and executing queries using a basic PHP script.
Remember to share your thoughts with us after reading the article. Your feedback is greatly valued. Keep yourself well and tuned in, and stay in touch with Myhospital.
