{"id":11566,"date":"2019-03-14T12:48:28","date_gmt":"2019-03-14T12:48:28","guid":{"rendered":"http:\/\/www.appservgrid.com\/paw92\/?p=11566"},"modified":"2019-03-14T12:48:28","modified_gmt":"2019-03-14T12:48:28","slug":"mysql-basic-database-administration-commands-part-i","status":"publish","type":"post","link":"https:\/\/www.appservgrid.com\/paw92\/index.php\/2019\/03\/14\/mysql-basic-database-administration-commands-part-i\/","title":{"rendered":"MySQL Basic Database Administration Commands \u2013 Part I"},"content":{"rendered":"

Database<\/strong>\u00a0is a structured set of data stored electronically. The concept of database was known to our ancestors even when there were no computers, however creating and maintaining such database was very tedious job. In a manual database say of\u00a0100<\/strong>\u00a0pages, if you have to search for all the employees whose salary were less than\u00a010k<\/strong>, just think how much difficult it would have been, then.<\/p>\n

In today\u2019s world you just can\u2019t escape\u00a0Database<\/strong>. Right now millions of database is working around the world to store and fetch data of every kind be it strategic data, employee record or web technologies.<\/p>\n

\n

\"MySQL<\/a><\/p>\n

MySQL Administration Guide \u2013 Part I<\/p>\n<\/div>\n

Database is oftenly termed as back-end process, as because it is neither visible to end user nor\u00a0End User<\/strong>interacts directly with the database. They works on front-end process viz.,\u00a0PHP<\/strong>,\u00a0VB<\/strong>,\u00a0ASP.NET<\/strong>, etc. and ask the front end to deal with database in back-end.<\/p>\n

There are several database server and client available like\u00a0Oracle<\/strong>,\u00a0MySQL<\/strong>,\u00a0MySQLi<\/strong>,\u00a0MariaDB<\/a>,\u00a0MongoDB<\/a>\u00a0etc. The syntax of all of these are more or less the same. Mastering one means gaining control on most of them and learning the queries of a database is very easy and fun.<\/p>\n

Lets start with simple queries on database. We will be using\u00a0MySQL<\/strong>\u00a0which comes bundled with most of the\u00a0Linux<\/strong>\u00a0distributions by default, you could install it manually from repository, if it is not installed by default in your case.<\/p>\n

Well a database query is a simple piece of code that is sent to database to get custom and refined result, as required.<\/p>\n

Install MySQL Database<\/h3>\n

Use \u201cyum<\/strong>\u201d or \u201capt<\/strong>\u201d package manager to install\u00a0MySQL<\/strong>\u00a0Database.<\/p>\n

# yum install mysql mysql-client mysql-server  (on Yum<\/strong> based Systems)\r\n\r\n# apt-get install mysql mysql-client mysql-server (on Apt<\/strong> based Systems)<\/pre>\n
Start MySQL<\/h5>\n
Start\u00a0MySQL<\/strong>\u00a0database service as:<\/p>\n
# service mysqld start\r\nor\r\n# service mysql start<\/pre>\n
Well installing a\u00a0MySQL<\/strong>\u00a0database will take you to the configuration where you are asked to setup\u00a0admin<\/strong>password, etc. Once finished installing and starting the server go to your\u00a0MySQL<\/strong>\u00a0prompt.<\/p>\n
# mysql -u root -p<\/pre>\n
Replace\u00a0root<\/strong>\u00a0with your configured\u00a0username<\/strong>\u00a0and enter\u00a0password<\/strong>\u00a0when prompted, if the login credential is correct, you will be at your\u00a0MySQL<\/strong>\u00a0prompt at the blink of your eyes.<\/p>\n
Welcome to the MySQL monitor.  Commands end with ; or \\g.\r\n\r\nYour MySQL connection id is 195 \r\n\r\nServer version: 5.5.31-0+wheezy1 (Debian) \r\n\r\nCopyright (c) 2000, 2013, Oracle and\/or its affiliates. All rights reserved. \r\n\r\nOracle is a registered trademark of Oracle Corporation and\/or its affiliates. \r\nOther names may be trademarks of their respective owners. \r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nmysql><\/pre>\n
Now carrying out queries at this prompt is very educative and fun.<\/p>\n
Create a database tecmint<\/h5>\n
mysql> create database tecmint ;\r\nQuery OK, 1 row affected (0.02 sec) \r\n\r\nmysql><\/pre>\n
Note<\/strong>: It reports that the query was correct, means database is created. You can verify your newly created database as.<\/p>\n
mysql> show databases; \r\n+--------------------+\r\n| Database           | \r\n+--------------------+ \r\n| information_schema | \r\n| mysql              | \r\n| performance_schema | \r\n| tecmint<\/strong>            | \r\n| test               | \r\n+--------------------+ \r\n9 rows in set (0.00 sec) \r\nmysql><\/pre>\n
Note<\/strong>: Notice your database in the above output.<\/p>\n
Select Database<\/h5>\n
Now you need to select the database to work upon it.<\/p>\n
mysql> use tecmint;\r\nDatabase changed\r\nmysql><\/pre>\n
Create Tables in MySQL<\/h5>\n
Here we will be creating a table say \u201cminttec<\/strong>\u201d with three fields as:<\/p>\n
mysql> CREATE TABLE minttec (\r\n    -> id Int(3), \r\n    -> first_name Varchar (15), \r\n    -> email Varchar(20) \r\n    -> ); \r\nQuery OK, 0 rows affected (0.08 sec) \r\nmysql><\/pre>\n
Note<\/strong>: The above query says\u00a0OK<\/strong>\u00a0which means table was created without any error. To verify the table run the below query.<\/p>\n
mysql> show tables; \r\n+-------------------+ \r\n| Tables_in_tecmint | \r\n+-------------------+ \r\n| minttec           | \r\n+-------------------+ \r\n\r\n1 row in set (0.00 sec) \r\n\r\nmysql><\/pre>\n
Things are going fine till now. Yup! You can view the columns you created in the table \u201cminttec<\/strong>\u201d as:<\/p>\n
mysql> show columns from minttec; \r\n\r\n+------------+-------------+------+-----+---------+-------+ \r\n| Field      | Type        | Null | Key | Default | Extra | \r\n+------------+-------------+------+-----+---------+-------+ \r\n| id         | int(3)      | YES  |     | NULL    |       | \r\n| first_name | varchar(15) | YES  |     | NULL    |       | \r\n| email      | varchar(20) | YES  |     | NULL    |       | \r\n+------------+-------------+------+-----+---------+-------+ \r\n3 rows in set (0.00 sec)\r\n\r\nmysql><\/pre>\n
It was nothing less than a magic. Anyway I will tell you about the types of declaration and their meaning.<\/p>\n
    \n
  1. Int<\/strong>\u00a0is Integer<\/li>\n
  2. Varchar<\/strong>\u00a0is char having variable length as defined. The value after Type is the length of field up-to which it can store data.<\/li>\n<\/ol>\n
    OK now we need to add a column say \u2018last_name<\/strong>\u2018 after column \u2018first_name<\/strong>\u2018.<\/p>\n
    mysql> ALTER TABLE minttec ADD last_name varchar (20) AFTER first_name; \r\nQuery OK, 0 rows affected (0.16 sec)\r\nRecords: 0  Duplicates: 0  Warnings: 0<\/pre>\n
    Now, verify it in your table.<\/p>\n
    mysql> show columns from minttec; \r\n\r\n+------------+-------------+------+-----+---------+-------+ \r\n| Field      | Type        | Null | Key | Default | Extra | \r\n+------------+-------------+------+-----+---------+-------+ \r\n| id         | int(3)      | YES  |     | NULL    |       | \r\n| first_name | varchar(15) | YES  |     | NULL    |       | \r\n| last_name  | varchar(20) | YES  |     | NULL    |       | \r\n| email      | varchar(20) | YES  |     | NULL    |       | \r\n+------------+-------------+------+-----+---------+-------+ \r\n\r\n4 rows in set (0.00 sec) \r\n\r\nmysql><\/pre>\n
    Add Column in MySQL<\/h5>\n
    Now we will add a column to the right say a column \u2018country<\/strong>\u2018 to the right of\u00a0email<\/strong>.<\/p>\n
    mysql> ALTER TABLE minttec ADD country varchar (15) AFTER email; \r\nQuery OK, 0 rows affected (0.16 sec) \r\nRecords: 0  Duplicates: 0  Warnings: 0 \r\n\r\nmysql><\/pre>\n
    Verify the above column insertion query.<\/p>\n
    mysql> show columns from minttec; \r\n\r\n+------------+-------------+------+-----+---------+-------+ \r\n| Field      | Type        | Null | Key | Default | Extra | \r\n+------------+-------------+------+-----+---------+-------+\r\n| id         | int(3)      | YES  |     | NULL    |       | \r\n| first_name | varchar(15) | YES  |     | NULL    |       | \r\n| last_name  | varchar(20) | YES  |     | NULL    |       | \r\n| email      | varchar(20) | YES  |     | NULL    |       | \r\n| country    | varchar(15) | YES  |     | NULL    |       | \r\n+------------+-------------+------+-----+---------+-------+\r\n5 rows in set (0.00 sec) \r\n\r\nmysql><\/pre>\n
    Insert Values in Field<\/h5>\n
    What about inserting values to the field?<\/p>\n
    mysql> INSERT INTO minttec VALUES ('1' , 'Ravi' , 'Saive' , 'raivsaive@xyz.com' , 'India' );\r\nQuery OK, 1 row affected (0.02 sec) \r\n\r\nmysql><\/pre>\n
    How about inserting more than\u00a01<\/strong>\u00a0value at a time in the above table.<\/p>\n
    mysql> INSERT INTO minttec VALUES ('2' , 'Narad' , 'Shrestha' , 'narad@xyz.com' , 'India' ), ('3' , 'user' , 'singh' , 'user@xyz.com' , 'Aus' ), ('4' , 'tecmint' , '[dot]com' , 'tecmint@gmail.com' , 'India' );\r\nQuery OK, 3 rows affected (0.05 sec) \r\nRecords: 3  Duplicates: 0  Warnings: 0<\/pre>\n
    Verify the above insertion.<\/p>\n
    mysql> select * from minttec; \r\n+------+------------+-----------+-------------------+---------+ \r\n| id   | first_name | last_name | email             | country | \r\n+------+------------+-----------+-------------------+---------+ \r\n|    1 | Ravi\t    | Saive     | raivsaive@xyz.com | India   | \r\n|    2 | Narad      | Shrestha  | narad@xyz.com     | India   | \r\n|    3 | user       | singh     | user@xyz.com      | Aus     | \r\n|    4 | tecmint    | [dot]com  | tecmint@gmail.com | India   | \r\n+------+------------+-----------+-------------------+---------+ \r\n\r\n4 rows in set (0.00 sec)\r\n\r\nmysql><\/pre>\n
    Delete Values in Field<\/h5>\n
    Let\u2019s say the third entry in the above output is invalid and we need to delete the third entry.<\/p>\n
    mysql> DELETE FROM minttec WHERE id = 3;\r\n\r\nQuery OK, 1 row affected (0.02 sec)<\/pre>\n
    Verify the above operation.<\/p>\n
    mysql> select * from minttec;\r\n\r\n+------+------------+-----------+-------------------+---------+ \r\n| id   | first_name | last_name | email             | country | \r\n+------+------------+-----------+-------------------+---------+\r\n|    1 | Ravi       | Saive     | raivsaive@xyz.com | India   | \r\n|    2 | Narad      | Shrestha  | narad@xyz.com     | India   | \r\n|    4 | tecmint    | [dot]com  | tecmint@gmail.com | India   | \r\n+------+------------+-----------+-------------------+---------+\r\n3 rows in set (0.00 sec)<\/pre>\n
    Update Values in Field<\/h5>\n
    The id (=4) needs to be edited.<\/p>\n
    mysql> UPDATE minttec SET id = 3 WHERE first_name = 'tecmint'; \r\nQuery OK, 1 row affected (0.02 sec)\r\nRows matched: 1  Changed: 1  Warnings: 0\r\n\r\nmysql><\/pre>\n
    Verify the above query.<\/p>\n
    mysql> UPDATE minttec SET id = 3 WHERE first_name = 'tecmint'; \r\nQuery OK, 1 row affected (0.02 sec) \r\nRows matched: 1  Changed: 1  Warnings: 0\r\n\r\nmysql><\/pre>\n
    Note<\/strong>: The above query, as performed is not a good idea. It will change the id to \u20184<\/strong>\u2018 where ever the first name is \u2018tecmint\u2019<\/strong>. It is always a good idea to use more than one column with where clause to get minimal error, as:<\/p>\n
    mysql> UPDATE minttec SET id = 6 WHERE first_name = 'tecmint'AND last_name = '[dot]com'; \r\nQuery OK, 1 row affected (0.03 sec)\r\nRows matched: 1  Changed: 1  Warnings: 0\r\n\r\nmysql><\/pre>\n
    Delete Column in MySQL<\/h5>\n
    Let we need to drop (delete) a column we think, is of no importance say \u2018country<\/strong>\u2018 here.<\/p>\n
    mysql> ALTER TABLE minttec drop country; \r\nQuery OK, 3 rows affected (0.15 sec)\r\nRecords: 3  Duplicates: 0  Warnings: 0\r\n\r\nmysql><\/pre>\n
    Verify the table.<\/p>\n
    mysql> select * from minttec; \r\n\r\n+------+------------+-----------+-------------------+ \r\n| id   | first_name | last_name | email             | \r\n+------+------------+-----------+-------------------+ \r\n|    1 | Ravi       | Saive     | raivsaive@xyz.com | \r\n|    2 | Narad      | Shrestha  | narad@xyz.com     | \r\n|    6 | tecmint    | [dot]com  | tecmint@gmail.com | \r\n+------+------------+-----------+-------------------+\r\n3 rows in set (0.00 sec) \r\n\r\nmysql><\/pre>\n
    Rename Table in MySQL<\/h5>\n
    Don\u2019t you think our table name \u201cminttec<\/strong>\u201d is not very much relevant. How about changing it to\u00a0tecmint_table<\/strong>.<\/p>\n
    mysql> RENAME TABLE minttec TO tecmint_table; \r\nQuery OK, 0 rows affected (0.03 sec)\r\n\r\nmysql><\/pre>\n
    List all Tables<\/h5>\n
    See all the tables under the current database.<\/p>\n
    mysql> show tables; \r\n\r\n+-------------------+ \r\n| Tables_in_tecmint | \r\n+-------------------+ \r\n| tecmint_table     | \r\n+-------------------+\r\n1 row in set (0.00 sec) \r\n\r\nmysql><\/pre>\n
    The table has been renamed. Now take a backup of the above\u00a0MySQL<\/strong>\u00a0database, in a single line of command without any sophisticated tool. Run the below code at your terminal and not on mysql prompt.<\/p>\n
    # mysqldump -u root -p tecmint > tecmint.sql\r\n\r\ncheck the dumped file on your desktop which would have contents something like\r\n-- MySQL dump 10.13  Distrib 5.5.31, for debian-linux-gnu (i686) --\r\n-- Server version 5.5.31-0+wheezy1 -- \r\nDump completed on 2013-09-02 12:55:37<\/pre>\n
    It is always a good idea to maintain\u00a0Backup of MySQL databases<\/a>. Restoring the backed up\u00a0MySQL<\/strong>\u00a0Data is again a simple line of code you need to run at your terminal prompt and not at your mysql prompt.<\/p>\n
    But, wait first we will delete the database to verify if our restore is perfect.<\/p>\n
    Delete a Database<\/h5>\n
    mysql> drop database tecmint; \r\nQuery OK, 1 row affected (0.02 sec)<\/pre>\n
    Check for database \u2018tecmint\u2019 on your database server.<\/p>\n
    mysql> show databases; \r\n\r\n+--------------------+ \r\n| Database           | \r\n+--------------------+ \r\n| information_schema | \r\n| my_database        | \r\n| mysql              | \r\n| performance_schema | \r\n| phpmyadmin         | \r\n| sisso              | \r\n| test               | \r\n+--------------------+\r\n\r\n7 rows in set (0.00 sec) \r\nmysql><\/pre>\n
    Great! The database is lost, but we need not to worry, we are having the backup.<\/p>\n
    Restore a Database<\/h5>\n
    To restore lost database, run the following command.<\/p>\n
    # mysql -u root -p tecmint < tecmint.sql\r\nEnter password:\r\nERROR 1049 (42000): Unknown database 'tecmint'<\/pre>\n
    OOPS<\/strong>! An error, hey we have not create the database\u00a0tecmint<\/strong>. So go to your mysql prompt and create a database \u2018tecmint<\/strong>\u2018.<\/p>\n
    mysql> create database tecmint; \r\nQuery OK, 1 row affected (0.00 sec) \r\n\r\nmysql><\/pre>\n
    Now time to run restore command at your shell prompt (strictly).<\/p>\n
    # mysql -u root -p tecmint < tecmint.sql \r\nEnter password:<\/pre>\n
    Verify your database.<\/p>\n
    mysql> show databases; \r\n\r\n+--------------------+ \r\n| Database           | \r\n+--------------------+ \r\n| information_schema | \r\n| mysql              | \r\n| performance_schema | \r\n| tecmint            | \r\n| test               | \r\n+--------------------+ \r\n8 rows in set (0.00 sec)<\/pre>\n
    Verify the contents of database.<\/p>\n
    mysql> show tables from tecmint;\r\n\r\n+-------------------+ \r\n| Tables_in_tecmint | \r\n+-------------------+ \r\n| tecmint_table     | \r\n+-------------------+ \r\n1 row in set (0.00 sec)\r\n\r\nmysql><\/pre>\n
    Verify the contents of your restored table.<\/p>\n
    mysql> select * from tecmint_table; \r\n\r\n+------+------------+-----------+-------------------+ \r\n| id   | first_name | last_name | email             | \r\n+------+------------+-----------+-------------------+ \r\n|    1 | Ravi       | Saive     | raivsaive@xyz.com | \r\n|    2 | Narad      | Shrestha  | narad@xyz.com     | \r\n|    6 | tecmint    | [dot]com  | tecmint@gmail.com | \r\n+------+------------+-----------+-------------------+\r\n\r\n3 rows in set (0.00 sec)<\/pre>\n
    This is not the end definitely, we will cover the concept of\u00a0primary key<\/strong>,\u00a0foreign key<\/strong>,\u00a0multiple tables<\/strong>\u00a0and\u00a0running queries<\/strong>\u00a0using simple\u00a0PHP<\/strong>\u00a0script in the next part of the article.<\/p>\n
    Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
    Database\u00a0is a structured set of data stored electronically. The concept of database was known to our ancestors even when there were no computers, however creating and maintaining such database was very tedious job. In a manual database say of\u00a0100\u00a0pages, if you have to search for all the employees whose salary were less than\u00a010k, just think … <\/p>\n
    Continue reading “MySQL Basic Database Administration Commands \u2013 Part I”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-11566","post","type-post","status-publish","format-standard","hentry","category-linux"],"_links":{"self":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11566","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/comments?post=11566"}],"version-history":[{"count":1,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11566\/revisions"}],"predecessor-version":[{"id":11567,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11566\/revisions\/11567"}],"wp:attachment":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/media?parent=11566"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/categories?post=11566"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/tags?post=11566"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}