MySQL- RDBMS
Tags: mysql, programming, beginners, sql
Published: 2021-12-29 16:54:47

## 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

```javascript
SELECT * FROM tableName;
```


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

```javascript
SELECT column, column2, ... FROM tableName;
```


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

```javascript
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.

```javascript
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.

```javascript
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

```javascript
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.

```javascript
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.

```javascript
CREATE DATABASE tableName;
```


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

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

```javascript
ALTER TABLE tableName
ADD columnName dataType;
```


> To delete an existing column from a data table

```javascript
ALTER TABLE tableName
DROP COLUMN columnName;
```


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

```javascript
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.

```javascript
==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.

```javascript
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

```javascript
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.

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


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

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


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

> Use DROP INDEX statement to delete an index

```javascript
ALTER TABLE tableName
DROP INDEX indexName;
```