MySQL- RDBMS

Relational Database(MySQL)

RDBMS stands for Relational Database Management System.
Relational databases define the relation between different data tables. This relation can be created based on one common data table's column that is the same in both data tables and the data column must be a unique data field.

MySQL is a free and fast Relational Database Management System. It is ideal for both small and large applications.

In MySQL, SQL is used to insert, search, update, and delete database records. All MySQL commands will be in capital letters.
In MySQL, most common SQL commands are:

1. SELECT - extracts data from a database
2. UPDATE - updates data in a database
3. DELETE - deletes data from a database
4. INSERT INTO - inserts new data into a database
5. CREATE DATABASE - creates a new database
6. ALTER TABLE - add, delete or modify a database table
7. CREATE TABLE - creates a new table
8. DROP DATABASE- deletes a table
9. CREATE INDEX - creates an index (search key)
10. DROP INDEX - deletes an index

1. SELECT - extracts data from a database.

To select the full table

SELECT * FROM tableName;

To select one or many specific columns(with all column data) from the data table.

SELECT column, column2, ... FROM tableName;

To select one or many specific columns(without duplicate column data) from the data table.

SELECT DISTINCT column, column2, ... FROM tableName;

2. UPDATE - updates data in a database.

The UPDATE statement used to modify the existing date of a table.
If you don’t use WHERE with condition then when all dates in the column will change.

UPDATE tableName
SET column = value, column2= value2, ...
WHERE conditions;

3. DELETE - deletes data from a database.

The DELETE statement is used to delete records from a data table.
If you don’t use WHERE then all records will delete.

DELETE FROM tableName
WHERE conditions;

4. INSERT INTO -inserts new data in a database.

You can write an INSERT INTO statement in two ways.Firstly, you can specify the column name and then specify their values in orderly like this

INSERT INTO tableName (column, column2, column3, ...)
VALUES (value, value2, value3, ...);

Secondly, you don’t need to specify the column name but you can specify only the values for each column orderly of your data table.

INSERT INTO tableName
VALUES (value, value2, value3, ...);

5. CREATE DATABASE -creates a new database.

With the CREATE DATABASE statement we can create a new database.

CREATE DATABASE tableName;

6. ALTER TABLE -add, delete or modify a database column.

To add a new column and must be specify the dataType.

ALTER TABLE tableName
ADD columnName dataType;

To delete an existing column from a data table

ALTER TABLE tableName
DROP COLUMN columnName;

To change an existing column’s dataType from a data table.

ALTER TABLE tableName
MODIFY COLUMN columnName dataType;

7. CREATE TABLE -creates a new table.

CREATE TABLE statement used to create a new table in a database.

==syntex==
CREATE TABLE tableName (
columnName1 dataType,
columnName2 dataType,
columnName3 dataType,
...  ...
);

==example==
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

Create table using another existing table
With CREATE TABLE you can copy one existing table and create a new one with existing table columns and also can apply conditions to get existing table columns into your new table.

CREATE TABLE newTableName AS
SELECT column1, column2, ...
FROM existingTableName
WHERE conditions;

8. DROP DATABASE -deletes a table.

DROP DATABASE statement used to delete an existing database.
Be careful to drop because once delete a table all of data in this table will lost forever

DROP DATABASE tableName;

9. CREATE INDEX - creates an index (search key)

To create indexes in a table you can use the CREATE INDEX statement. Indexes allow us to retrieve our data from a table quickly. Usually users can’t see the indexes. Indexes are just used to speed up our queries.

Create an index in a table. Duplicate values are allowed.

CREATE INDEX indexName
ON tableName (column1, column2, ...);

Create an unique index in a table. Duplicate values are not allowed here.

CREATE UNIQUE INDEX indexName
ON tableName (column1, column2, ...);

10. DROP INDEX - deletes an index (search key)

Use DROP INDEX statement to delete an index

ALTER TABLE tableName
DROP INDEX indexName;