Database

RDBMS vs DBMS (ACID)

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

The difference between RDBMS and DBMS is that of ACID princples.

  • Atomicity : Which ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.
  • Consistency : Which ensures that the database properly changes states upon a successfully committed transaction.
  • Isolation : Which enables transactions to operate independently of and transparent to each other.
  • Durability : Which ensures that the result or effect of a committed transaction persists in case of a system failure.

SQL

SQL is a language to operate databases; SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database. SQL is the standard language for Relational Database System.

The Relational Database Management Systems (RDBMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.

MySQL

MySQL is an open-source relational database management system.

XAMPP

XAMPP is a cross-platform web server that is free and open-source. XAMPP is a short form for Cross-Platform,

Datatype

SQL Data Type is an attribute that specifies the type of data of any object. Each column, variable and expression has a related data type in SQL. You can use these data types while creating your tables. You can choose a data type for a table column based on your requirement.

In MySQL there are three main data types:

  • String Data Types.
  • Numeric Data Types
  • Date and Time Data Types

Varchar vs Text

Text

  • Fixed max size of 65535 characters (you cannot limit the max size).
  • Takes 2 + c bytes of disk space, where c is the length of the stored string.
  • Cannot be (fully) part of an index. One would need to specify a prefix length.

VARCHAR

  • Variable max size of M characters.
  • M needs to be between 1 and 65535.
  • Takes 1 + c bytes (for M ≤ 255) or 2 + c (for 256 ≤ M ≤ 65535) bytes of disk space where c is the length of the stored string.
  • Can be part of an index.

SQL Operations

Create

Creates a new table, a view of a table, or other object in the database.
CREATE DATABASE databasename;

Drop

Deletes an entire table, a view of a table or other objects in the database.
DROP DATABASE databasename;

Insert

Creates a record.
INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);

Update

Modifies records.
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ];

Delete

Deletes records.
DELETE FROM table_name
WHERE {CONDITION};

Aggregate

There are several built-in functions like avg(), sum(), count(), etc., to perform what is known as the aggregate data calculations against a table or a specific table column.
SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS;

Joining

The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
SELECT ID, NAME, AGE, AMOUNT
FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Inner join

Returns rows when there is a match in both tables.
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Left Join

Returns all rows from the left table, even if there are no matches in the right table.
SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

Right join

Returns all rows from the right table, even if there are no matches in the left table.
SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;