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
| CHAR(n) | VARCHAR(n) | |
| Used for | Storing characters having pre determined length. | Storing characters whose length vary a lot. |
| Storage Characteristic | trailing spaces are applied if the data to be stored has length less than defined size. | trailing spaces are not applied |
| Max size | 2000 Bytes | 4000 Bytes |
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.
| Data type | Useful for |
| DATE | Storing date data where time portion is not required eg: Date of birth |
| TIMESTAMP | Storing date data up-to 1 billionth(9-digits) of second Eg: Time of transaction |
| CLOB (Character large object data) | Storing large characters based data |
| BLOB (Binary large object data ) | Storing large binary files like movies or images. |
Operators
Arithmetic Operators
| Operator | Symbol | Usage | Result |
| Addition | + | 13+2 | 15 |
| Subtraction | – | 10-7 | 3 |
| Multiplication | * | 5*4 | 20 |
| Division | / | 15/3 | 5 |
Comparison Operators
| Operator | Symbol | Usage | Result |
| Equal to | = | 12=6 | false |
| Not Equal to | 128 | true | |
| greater than | > | 12>7 | true |
| less than | < | 13<7 | false |
| greater than equal to | >= | 12>=12 | true |
| less than equal to | <= | 12<=20 | false |
Logical Operators
| Operator | Symbol | Usage | Example |
| And | AND | Returns True if both conditions are True | Age>18 AND Age<60 |
| Or | OR | Returns True if one of the condition is True | ID = 1 OR ID = 5 |
| Not | NOT | Returns Negation of Condition | ID NOT IN (2,3,4) |
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).
| Constraint
|
Explanation |
| NOT NULL | The given value cannot be NULL |
| PRIMARY KEY | The given value must be Unique and Not Null |
| CHECK | The given value must satisfy the given condition |
| UNIQUE | The given value should be Unique |
| FOREIGN KEY | The given value must present in referenced table as primary key |
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.
Hi there, my name is Sathish and I am currently pursuing my final year of B. Tech in the Department of IT from India. I am extremely passionate about the field of data science and machine learning and enjoy studying the latest advancements in deep learning technologies. In addition to my technical skills, I also have a solid foundation in software engineering. I am driven by a desire to learn and grow and am always eager to take on new challenges and expand my knowledge. I am excited about the opportunities that lie ahead in my career and am committed to achieving my goals through hard work and dedication.
