Your Web News in One Place

Help Webnuz

Referal links:

Sign up for GreenGeeks web hosting
December 29, 2021 04:54 pm GMT

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 dont use WHERE with condition then when all dates in the column will change.

UPDATE tableNameSET 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 dont use WHERE then all records will delete.

DELETE FROM tableNameWHERE 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 dont need to specify the column name but you can specify only the values for each column orderly of your data table.

INSERT INTO tableNameVALUES (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 tableNameADD columnName dataType;

To delete an existing column from a data table

ALTER TABLE tableNameDROP COLUMN columnName;

To change an existing columns dataType from a data table.

ALTER TABLE tableNameMODIFY 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 ASSELECT column1, column2, ...FROM existingTableNameWHERE 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 cant see the indexes. Indexes are just used to speed up our queries.

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

CREATE INDEX indexNameON tableName (column1, column2, ...);

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

CREATE UNIQUE INDEX indexNameON tableName (column1, column2, ...);

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

Use DROP INDEX statement to delete an index

ALTER TABLE tableNameDROP INDEX indexName;

Original Link: https://dev.to/yasin440/mysql-rdbms-3cdb

Share this article:    Share on Facebook
View Full Article

Dev To

An online community for sharing and discovering great ideas, having debates, and making friends

More About this Source Visit Dev To