Basics of mysql? What is Mysql? Basic mysql queries/commands?

MySQL is an open source relational database management system (RDBMS) based on Structured Query Language (SQL). 

A database is a separate application that stores a collection of data. Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it holds.

Other kinds of data stores can also be used, such as files on the file system or large hash tables in memory but data fetching and writing would not be so fast and easy with those type of systems.

Nowadays, we use relational database management systems (RDBMS) to store and manage huge volume of data. This is called relational database because all the data is stored into different tables and relations are established using primary keys or other keys known as Foreign Keys.

• MySQL is released under an open-source license. So you have nothing to pay to use it. MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments.
• MySQL uses a standard form of the well-known SQL data language.
• MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.
• MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).

Login to mysql:

#mysql -u(user) -p(pwd) (mysql_db_name) -A;

Ex: mysql -uroot -pmysql emlplatform -A;

Check Mysql Version:

#mysql -V or #SHOW VARIABLES LIKE "%version%";

To check mysql status:

#status;

#SHOW SLAVE STATUS\G;

#SHOW PROCESSLIST \G;

# SHOW SLAVE HOSTS;

To check mysql DB size:

SELECT table_schema "DB Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;

SELECT:

The SELECT query is the standard means by which to retrieve information from the database. The syntax for the SELECT query is as follows:

mysql> SELECT field1, field2, ...

       FROM table_name

       [WHERE condition1

            AND condition2

            ...

       [ORDER BY field1, field2, etc...]

       [LIMIT number]];

Example:

mysql> SELECT * FROM books;

mysql> SELECT Title, Author

       FROM books

       WHERE Year > 2001

       AND Year < 2006

       ORDER BY Author ASC

       LIMIT 100;

INSERT:

The INSERT statement is used to insert data into a particular table of a database. The Syntax is as follows:

mysql> INSERT INTO table_name

       (field1, field2, etc...)

       VALUES ('val1', 'val2', etc...);

Example:

mysql> INSERT INTO books

       (ISBN, Title, Author, Year)

       VALUES ('1234567890', 'Programming PHP and MySQL', 'Poisson, R.W', 2006);

DELETE:

The DELETE statement is used to remove row(s) from a table. The syntax is as follows:

mysql> DELETE FROM table_name

       [WHERE condition1, condition2 etc...];

Example:

mysql> DELETE FROM books

       WHERE ISBN = '1234567890';

ALTER:

ALTER is used to modify the structure of a table. Here is the syntax for ALTER:

ALTER [IGNORE] TABLE tbl_name

    alter_specification [, alter_specification] ...  

DROP:

DROP is used to completely remove a table or database. The syntax is as follows:

mysql> DROP table_name;

TRUNCATE:

TRUNCATE is used when you want to remove the data, but not the structure of a table. The syntax is as follows:

mysql> TRUNCATE table_name;