Getting Started with Databases like MySQL

This article was published as a part of the Data Science Blogathon.

Introduction to Databases like MySQL

Nowadays data is growing exponentially in this world. But we need a way to store, process, and use this data efficiently in the future. This led to the evolution of Databases. Databases are used to manage the data that we are acquiring from our concerning sources we need an efficient data management system.  A database is an organized way of collecting structured information. So, the databases is useful in structuring and storing the data in an organized way, and then our task is to retrieve, process, and manage this data for further use this makes the evolution of DBMS (Database management System ) a DBMS is a software system that can be useful for Accessing, Modifying, and Managing the data stored in the Database.

• A Database is a collection of logically related data and meta data, designed to meet the information needs.
• A Database Management System (DBMS) is a software system that helps in managing the data inside database

A Relational database is a way of organizing data in the form of Tables with rows and columns. A row represents a particular observation data and columns represent the attributes each observation could have. There are many relational databases like DB2, Oracle, SQL Server, etc.

What is SQL?

SQL stands for Structured Query Language. SQL helps us in communicating with the databases. We send our queries to the database in SQL. The Relational databases use SQL for querying data. SQL standards are maintained by ISO (International Organization of Standardization).

Types of SQL Commands

SQL is comprised of 4 types of commands: DDL, DML, DCL, and TCL

DDL (Data Definition Language)

• DDL is used to specify the structure i.e. schema of a relational database.
• It provides commands for the creation, modification, and deletion of various database objects like tables, views, stored procedures, indexes, constraints, etc.
• The output of DDL is placed in a data dictionary that contains metadata.

DML (Data Manipulation Language)

• DML provides commands for accessing or manipulating data in a relational database.
• Data retrieval, creation, deletion, and modification can be done by using DML Commands.
• User can specify what data is needed for him through DML commands without specifying the process of getting it. The database engine is concerned about how to retrieve the data.

DCL (Data Control Language)

• In general, Database Administrators have access to grant and revoke privileges of relational database. Whenever a user submits a query, the database checks against the granted privileges and rejects the query if it is not authorized.
• DCL enables users to provide access to various database objects like views, tables, stored procedures, etc.

TCL (Transaction Control Language)

• TCL specifies commands for beginning and ending a transaction.
• A transaction consists of a sequence of SQL statements that are applied in
  an atomic (all or none) manner.
• A commit makes all the changes applied by the transaction permanent on the database while a rollback undoes all the changes applied by the transaction.

Before going into these commands we need to know some important topics in SQL. Let’s have a quick look at Data types and Operators that we use in SQL.

Data Types

Character

Characters are usually defined by using CHAR and VARCHAR in MySQL

Integral Data Types

• SQL supports SMALLINT, INTEGER and INT data types that are used for storing whole numbers.
• Oracle does not define different size limits for them. They are all treated internally to have 38 digits of precision.

Non-Integral Data Types

Non-integral data types have an integer part and a fractional part. these are NUMERIC,DECIMAL or NUMBER

Precision : the total no. of significant digits. digits that are both before and after the decimal point.

Scale : the no. of digits allowed after the decimal point.

Examples:

 

Miscellaneous Data Types

These are some of the other data types in MySQL.

Operators

Arithmetic Operators

Comparison Operators

Logical Operators

Databases- SQL Queries

Now let us dive into the writing SQL commands.

DDL Commands

1) CREATE

CREATE is Used for Creating tables in a database. Syntax in MySQL

CREATE TABLE TableName (ColumnName1 DATATYPE, ColumnName2 DATATYPE,.....);

While Creating tables we can apply some constraints to enforce the data integrity. These constraints are
typically specified along with the CREATE TABLE statement.

Constraints can be single column constraints(applies on single column) or Composite constraints(applies on multiple column) and also constraints can be column level(specified with column definition) and Table level constraints(specified after column definition).

Examples:

CREATE TABLE Students ( StudentID INTEGER PRIMARY KEY, Student Name VARCHAR2(100) NOT NULL, GENDER CHAR(1) CHECK GENDER IN ('M' , 'F'), CourseID INTEGER REFERENCES Courses(CourseID));

in the above example the constraints are applied at column level and all are single column constraints.

CREATE TABLE Students ( StudentID INTEGER, Student Name VARCHAR2(100) NOT NULL,PRIMARY KEY(StudentID));

in the above example the Primary key is a table level constraint.

we can specify the name of a constraint.

Example:

StudentID INTEGER Constraint StdID_PK PRIMARY KEY

here the constraint name is specified as StdID_PK

2) DROP

DROP Command is used for Deleting tables in a database.

Syntax in MySQL:

DROP TABLE TableName 

Tables without any reference with the child table
can be dropped using the DROP statement given above. To drop a table that has been referenced by other table we have two ways:

Drop all the child tables
first, then drop parent table.

Use CASCADE CONSTRAINTS

DROP TABLE  Table_Name CASCADE CONSTRAINTS;

CASCADE CONSTRAINTS clause should be added to the DROP
statement to drop all the referential integrity constraints that refer to
primary and unique keys in the table.

3) ALTER

ALTER is useful for changing the structure of the existing table without any loss of data.

it can be used to Rename column, Change the data type of a column, Add or remove constraints.

Syntax in MySQL:

ALTER TABLE

DML Commands

1) SELECT

• The SELECT Statement in SQL is used to retrieve or fetch data from a database.
• We can fetch either the entire table or according to some specified rules.
• The data returned is stored in a result table. This result table is also called result-set.
• DISTINCT is a keyword that helps removing duplicates based on all the columns in the SELECT clause. DISTINCT must be used immediately after SELECT.

Syntax in MySQL:

SELECT  FROM

 

Key Points to Remember

• If IN clause contains duplicate values then the database server will remove duplicates before executing the query.
• Equal to operator cannot be used to check for NULL values ( IS NULL Should be used).
• If NULL is used with IN clause then the condition behavior is similar to = NULL (not is NULL). It will not raise an error but, NO rows would be selected even if they have NULL Value ).
• Trailing spaces ignored , Leading spaces are not ignored for CHAR Data type.
• Trailing spaces/Leading spaces are  not ignored for VARCHAR2 Data type.

2)  INSERT

• INSERT is used for entering new data into a table in database.
• We can also use a select query for inserting values in a tables by specifying select query in the place of values to be mentioned

Syntax in MySQL:

INSERT INTO TableName (Col_1, Col_2, Col_3, ....., Col_n) VALUES (Val_1, Val_2, Val_3, ....., Val_n)

INSERT INTO TableName VALUES (Val_1, Val_2, Val_3, ....., Val_n)

Examples:

INSERT INTO STUDENTS (NAME, AGE, DOB) VALUES ('Sathish', 20, '06-dec-2001');

INSERT INTO STUDENTS VALUES ('Sathish',22,'12-june-2022)

Key Points to Remember:

• If the column is of strings or date time or characters, they need to be enclosed in single quotes(You cannot use double quotes)
• String data is case sensitive and will be stored as given within quotes.
• If the data is numeric, you don’t need the quotes. NULL also should not be enclosed with quotes.
• NULL values can be entered into columns with unique constraint. Unique constraint does not prevent multiple NULL values on the column. This is because two NULLs are not treated equal by SQL.
• The foreign key could be due to reference to same table or a different table.

3)  UPDATE

• Update statement is used to modify existing rows in a single table in a relational database.
• The database system ensures that no constraints are violated during execution of an update statement.
• The UPDATE statement fails if the same column is updated multiple times in the same statement.

Syntax in MySQL:

UPDATE  SET

Examples:

UPDATE Employee SET SALARY = SALARY*2;
UPDATE Employee SET SALARY = SALARY*3 , BONUS = 200 WHERE EmpID = 10;

4)  DELETE

• DELETE statement is used to delete records from a single table in a relational database
• The database system ensures that no constraints are violated during the execution of a delete statement.

Syntax in MySQL:

DELETE FROM TableName

Example:

DELETE FROM Student where Department = 'POWER'

5) TRUNCATE

Can also be used for deleting the data from tables

but Truncate deletes the entire table data.

Syntax in MySQL:

TRUNCATE TABLE TableName

Conclusion on Databases like SQL

This article is mainly written for complete beginners. The main aim of this article is to make SQL Introduction to beginners concisely and make them write SQL Basic queries. There are many topics that have to be covered in SQL for beginners. Some Important topics are JOINS in SQL, Group by and Having Clauses, Functions, Aggregate Functions in SQL, LIKE Operator,.. etc. So, the dear learner doesn’t stop here and continue to learn the further topics in DBMS and SQL.I wish you great learning ahead.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.