DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

Cover image for MySQL- RDBMS
Md Yasin Miah
Md Yasin Miah

Posted on • Updated on

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;
Enter fullscreen mode Exit fullscreen mode

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

SELECT column, column2, ... FROM tableName;
Enter fullscreen mode Exit fullscreen mode

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

SELECT DISTINCT column, column2, ... FROM tableName;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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, ...);
Enter fullscreen mode Exit fullscreen mode

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, ...);
Enter fullscreen mode Exit fullscreen mode

5. CREATE DATABASE -creates a new database.

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

CREATE DATABASE tableName;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

To delete an existing column from a data table

ALTER TABLE tableName
DROP COLUMN columnName;
Enter fullscreen mode Exit fullscreen mode

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

ALTER TABLE tableName
MODIFY COLUMN columnName dataType;
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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, ...);
Enter fullscreen mode Exit fullscreen mode

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

CREATE UNIQUE INDEX indexName
ON tableName (column1, column2, ...);
Enter fullscreen mode Exit fullscreen mode

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

Use DROP INDEX statement to delete an index

ALTER TABLE tableName
DROP INDEX indexName;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Classic DEV Post:

Understanding git through images