My SQL Basics

Basics of MySQL

MySQL is generally an RDMS (Relational Database Management System) which is based on SQL (Structured Query Language), which is the popular language for accessing, manipulating and managing the data. This is a free and open-source software under the terms of the GNU (General Public License), which is also available under a variety of proprietary licenses. This software is ideal for both small and large applications. It is very swift, reliable and also easy to use and it is free to download and access. It was developed, distributed and supported by Oracle Corporation. In this MySQL free course, you will understand the topics of MySQL database that provides information about how to manage the database and also how to manipulate data of the database with the assistance of SQL queries. These queries can be used for inserting data, updating them, deleting the records, selecting them, creating tables, truncating the records or dropping the tables.

This RDBMS software provides the following:

1. It describes the relationship of a database in the form of the tables which are also known as relations.
2. It also permits us to carry through the database operations on tables, rows, columns and also indexes.
3. It also brings forth the Referential Integrity between the rows or columns of various database tables. 
4. We can avail multiple tables for the end-users with the help of many SQL queries and combined useful information.

Since we know MySQL is used to store the data in the form of tables. These databases are benefiting us by storing the information categorically. The XYZ Company may have a database that would require the tables for their employees, their products, the customers and also about the orders. 

In prior to starting with MySQL basics, let us see a few definitions correlated to the database: 

• Database: The collection of tables with relevant data is known as a Database.
• Table: It consists of rows and columns, a matrix with data. The cols are used to specify the type of data considering that the rows contain the actual data itself.
• Row: Also known as tuple or record.
• Column: Also known as attribute or field.
• Primary key: Primary key is a unique key constraint. This key value cannot occur twice in the table. Since it is a combination of unique + not null. 
• Foreign key: Foreign key is used to establish the relation or the connection between two tables.
• Index: Just like an index which we have at the back of the book, here we have an index in the database which works as the same.
• Referential Integrity: To make sure that the foreign key value is always pointing to an existing one, we need Referential Integrity.  

Let’s know about few data manipulation statements:

• Select: This clause is used to SELECT the data you require from the respective table.
• From: This clause is used to define the name of the table FROM which data is selected.
• Where: This clause is used to filter out the data according to the conditions provided.
• Order By: This clause is used to sort the data according to our requirements. Be it sorting in ascending or in descending order.
• And: This operator is used for the filtering process with the Boolean logic.
• Or: This operator is similarly used for the filtering process and for comparison purpose.
• In: This operator is used with the WHERE clause to find out if the value matches any values in the list or the subquery.
• Not In: This operator works the other way round of the IN operator. This is used to identify that none of the value matches with the values in the list or the subquery.
• Between: This is operator is used when we have a range of values to be selected.
• Like: This operator is used to select a value by describing a certain pattern for it. 
• Not Like: This operator is the opposite of the LIKE operator.
• Inner Joins: Joins are basically used to obtain data from two or more table simultaneously. So this join is used to get valid or matched outputs from the table.
• Left Outer Join: This join is used to obtain all the valid as well as the invalid outputs from the left table.
• Right Outer Join: This join is used to obtain all the valid as well as the invalid outputs from the right table.
• Full Outer Join: This join is the combination of the LEFT and RIGHT outer joins. Here, we get the valid as well as the invalid data from all the tables.
• Cartesian Join: This is used to obtain both the matched as well as unmatched outputs from the tables. But the number of unmatched data is more as compared to matched data. Hence, this join is not widely used. 
• Natural join: We can use this join when we do not know the primary key and foreign key relation between the tables. If there is a primary key foreign key relation then this join gives the output as an INNER join, but if there is no relation between the tables then we get the outputs as CARTESIAN join.
• Group By: This clause is used to group the record based on the conditions provided.
• Having: This clause is used to filter conditions having group by clause.
• Subquery: It is a query (inner query) written inside another query (outer query). The output of the inner query is given to the outer query as the input. Taking this input from the inner query the outer query executes completely and generates the result. It is also used to establish a connection between the tables.
• Union and Union All: These operators are used to combine two or more results sets of multiple queries into a single result set.
• Insert: This statement is used to INSERT data into the table or database.
• Insert Multiple Rows: It is also used to INSERT multiple rows into the table.
• Update: This statement is used to UPDATE the data which is existing inside the table or the database.
• Delete: This statement is used to DELETE rows from a single table or multiple tables, we can restore the records using the FLASHBACK command.
• Rename: This statement is used to RENAME the existing table name from the database.
• Create: This statement is used to CREATE the table inside the database.
• Alter: This statement is used to ALTER the table name, modify any column name, add or remove rows and columns, etc.
• Truncate: This statement is used to TRUNCATE the rows, when we truncate all the rows get deleted and cannot be restored back.
• Drop: This statement is used to Drop the table, here the records along with the table structure gets deleted. But we can restore it with the ROLLBACK command.
• Rollback: This statement is used to restore back the records along with the table structure as it is with the help of this ROLLBACK statement.
• Commit: This command is used to COMMIT the record, which is we save the last record into the database so that it can be restored if deleted.

After the completion of this course, you will get a better knowledge of MySQL basic commands which will be helpful for further knowledge on an intermediate level. 

A MySQL Developer, an IT professional is responsible for all phases of Database Administration on all environments with significance on performance troubleshooting, analytical problem solving and also on business customer support. 

Advantages:

SQL has many merits which makes it popular and highly in demand. It is a well-grounded and well-organized language that is used to communicate with the database.

• Faster Query Language.
• No coding skills.
• Standardised Language.
• Interactive Language.
• Portable.

Disadvantages:

Although SQL has various advantages, it still has a few disadvantages.

• Complex Interface.
• Cost.
• Partial Control.