- A database is a collection of data.
- A database system is a system for storing collections of data in some organized way.
- It uses a structure that allows us to identify and access data in relation to another piece of data in the database.
- Often, data in a relational database is organized into tables.
What is a Relational Database Management System (RDBMS)?
- A relational database management system (RDBMS) is a program that allows you to create, update, and administer a relational database.
- Most relational database management systems use the SQL language to access the database.
What is SQL?
SQL (Structured Query Language) is a programming language used to communicate with data stored in a relational database management system.
Every relational database system (PostgreSQL, SQLite, MySQL, etc) has its own "flavor" of SQL it implements.
Because they are not 100% compliant with SQL standards.
Each flavor is called a "dialect" that is unique per database management system.
general command SQL query you should know :
- CREATE TABLE
- ALTER TABLE
- DROP TABLE
- ADD COLUMN
- DROP COLUMN
Joins & Groupings
- INNER JOIN, OUTER JOINS (LEFT, RIGHT)
- GROUP BY, SUM, COUNT
The powerful properties of the database :
- Persistence (allowing access later, after it was created).
- Shared source of truth accessible by many users.
- Ability to store many types of data (efficiently).
- Concurrency control (handling multiple DB actions at once ).
- All data is stored in tables.
- Every table is characterized by a list of columns with data types per column, and it's set of data (organized in rows)
- Comes with rules for enforcing data integrity, such as constraints and triggers.
Popular relational database vendors for the web :
MySQL is the most popular open source SQL database.
It is typically used for web application development, and often accessed using PHP.
The main advantages of MySQL are that it is easy to use, inexpensive, reliable (has been around since 1995), and has a large community of developers who can help answer questions.
Some of the disadvantages are that it has been known to suffer from poor performance when scaling, open-source development has lagged since Oracle has taken control of MySQL, and it does not include some advanced features that developers may be used to.
PostgreSQL is an open-source SQL database that is not controlled by any corporation.
It is typically used for web application development.
PostgreSQL shares many of the same advantages as MySQL.
It is easy to use, inexpensive, reliable and has a large community of developers.
It also provides some additional features such as foreign key support without requiring complex configuration.
The main disadvantage of PostgreSQL is that it can be slower in performance than other databases such as MySQL.
It is also slightly less popular than MySQL.
Oracle Corporation owns Oracle Database and the code are not open-sourced.
Oracle DB is for large applications, particularly in the banking industry.
Most of the world’s top banks run Oracle applications because Oracle offers a powerful combination of technology and comprehensive, pre-integrated business applications, including essential functionality built specifically for banks.
The main disadvantage of using Oracle is that it is not free to use like its open-source competitors and can be quite expensive.
Microsoft owns SQL Server.
Like Oracle DB, the code is close sourced.
Large enterprise applications mostly use SQL Server.
Microsoft offers a free entry-level version called Express but can become very expensive as you scale your application.
SQLite is a popular open-source SQL database.
It can store an entire database in a single file.
One of the most significant advantages this provides is that all of the data can be stored locally without having to connect your database to a server.
SQLite is a popular choice for databases in cellphones, PDAs, MP3 players, set-top boxes, and other electronic gadgets.