Introduction to My SQL

Introduction to RDBMS: A relational database management system (RDBMS) is a database management system (DBMS) that is based on the r...

Introduction to RDBMS:

• A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model.
• RDBMS manages data in “tables” which are also called “relations”.

• RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

Basic terminology of RDBMS:

• Database: A repository to store information on a computer. 

• Table: A table is a collection of related data entries and consists of rows and columns. 

• Field: Every table is made up of smaller entities called fields. The fields in the Employee table consist of ID, NAME, AGE, ADDRESS and SALARY. 

• Record: Also called as a row of data, is an individual entry that exists in a table. For example, there are 5 records in the above Employee table. Following is a single row of data or record in the Employee table: 

• Column: column is a vertical entity in a table that contains all information associated with a specific field in a table. 

• NULL value: Null value in a table is a field with no value.

SQL Constraints:

• Constraints are the rules enforced on data columns on the table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. 
• Constraints could be column level or table level. Column level constraints are applied only to one column whereas table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL:

• NOT NULL: Constraint: Ensures that a column cannot have a NULL value.
• DEFAULT: Constraint: Provides a default value for a column when none is specified.
• UNIQUE: Constraint: Ensures that all values in a column are different.
• PRIMARY Key: Uniquely identified each row/records in a database table.
• FOREIGN Key: Uniquely identified a rows/records in any another database table.
• CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions. 
• INDEX: Use to create and retrieve data from the database very quickly.

SQL Data types:

Commands:

To open MySQL prompt type “Mysql”.

• Create Database: This statement creates a new, empty database without any tables or data. 
• Drop Database: This command deletes an existing database
• Use: The USE statement is used to select an existing database in SQL schema.
• CREATE TABLE: This command creates a new table. A unique name follows the CREATE TABLE statement which is the table name.
• INSERT INTO: This command is used to add new rows of data to a table in the database.
• SELECT: This command is used to fetch the data from a database table which returns data in the form of the result table.

Joins:

There are different types of joins available in SQL:

• INNER JOIN: returns rows when there is a match in both tables. 

• LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table. 

• RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table. 

• FULL JOIN: returns rows when there is a match in one of the tables. 

• SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement. 

• CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.