Show Menu
Cheatography
  1. Home >
  2. Programming >

MySQL Cheat Sheet (DRAFT) by

MySQL Concepts and Commands

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Databases

Database is an organized collection of related inform­ation.
Databases support storage and manipu­lation of data
We need databases to store large amounts of inform­ation, to keep data organized, to protect data, to analyze data, and to scale with demand.

ACID Properties

ACID properties are fundam­ental for ensuring data reliab­ility, consis­tency, and integrity in database systems.
They provide a set of guarantees that enable transa­ctions to operate correctly in a multi-user enviro­nment.
Applic­ations requiring strict data integrity and reliab­ility, such as banking systems, e-commerce platforms, and inventory management systems, rely heavily on ACID-c­omp­liant databases.
Relational database management systems (RDBMS) like Oracle, MySQL, and PostgreSQL typically adhere to the ACID properties to ensure data consis­tency and reliab­ility.
Atomicity: Refers to the all-or­-no­thing principle. Ensures that a transa­ction is either completed in its entirety or not at all. If any part of a transa­ction fails, the entire transa­ction is rolled back, preserving data integrity.
Consis­tency: Guarantees that the database remains in a valid state before and after the execution of transa­ctions. Enforces integrity constr­aints and rules defined for the database. All changes made by a transa­ction must adhere to the predefined consis­tency constr­aints.
Isolation: Ensures that concurrent execution of transa­ctions produces results equivalent to those achieved through serial execution. Transa­ctions appear to execute in isolation from each other, even though they may be executed concur­rently. Prevents interf­erence between transa­ctions, mainta­ining data integrity and consis­tency.
Durabi­lity: Ensures that once a transa­ction is committed, its effects persist even in the event of system failures. Changes made by committed transa­ctions are permanent and are stored in non-vo­latile memory (such as disk) to withstand crashes or restarts. Guarantees that committed transa­ctions survive system failures and are not lost or rolled back.

NoSQL

NoSQL databases are non-re­lat­ional databases designed for handling large volumes of unstru­ctured, semi-s­tru­ctured, or structured data.
They offer flexible schema design and horizontal scalab­ility to manage diverse data types and high-v­elocity data ingestion.
Flexible Schema: NoSQL databases allow dynamic schema creation, enabling storage of varying data structures within the same database.
Horizontal Scalab­ility: NoSQL databases scale horizo­ntally by adding more servers or nodes to distribute data and load across the cluster.
High Perfor­mance: Designed for high-speed data processing and low-la­tency access, making them suitable for real-time applic­ations.
Distri­buted Archit­ecture: Data is distri­buted across multiple nodes, providing fault tolerance and redundancy for increased reliab­ility.

Normal­ization

Defini­tion: The process of organizing data in a database to reduce redundancy and depend­ency.
Objective: Enhance data integrity, minimize anomalies, and improve database effici­ency.
First Normal Form (1NF): Ensures atomicity of data. No repeating groups or arrays.
Second Normal Form (2NF): Non-key attributes are fully functi­onally dependent on the primary key. Eliminates partial depend­encies.
Third Normal Form (3NF): Eliminates transitive depend­encies. Non-key attributes depend only on the primary key.
Boyce-Codd Normal Form (BCNF): A stronger version of 3NF. Every determ­inant is a candidate key. Avoids certain types of anomalies.
 

Database Management System (DBMS)

DBMS is a collection of programs that enables its users to access databases, manipulate data, report­ing­/re­pre­sen­tation of data.
DBMS manages the data, the database engine, and the database schema, allowing for data to be manipu­lated or extracted by users or other programs.

Types of DBMS

Relational DBMS (RDBMS) Organizes data into tables with rows and columns. Utilizes Structured Query Language (SQL) for data manipu­lation. Examples: MySQL, Postgr­eSQL, Oracle Database, Microsoft SQL Server.
NoSQL DBMS Suited for unstru­ctured or semi-s­tru­ctured data. Offers flexib­ility in schema design. Types: Docume­nt-­ori­ented, Key-value, Column­-or­iented, Graph databases. Examples: MongoDB, Cassandra, Redis, Neo4j.
Object­-Or­iented DBMS (OODBMS) Stores data in the form of objects. Supports object­-or­iented features like inheri­tance and encaps­ula­tion. Examples: db4o, ObjectDB.
Graph DBMS Optimized for managing and querying graph data struct­ures. Ideal for interc­onn­ected data applic­ations like social networks. Examples: Neo4j, Amazon Neptune, JanusG­raph.
Time-S­eries DBMS Specia­lized for managing data with timest­amps. Commonly used in IoT and financial data analysis. Examples: InfluxDB, Timesc­aleDB, Promet­heus.
In-Memory DBMS Stores data primarily in system memory for faster access. Suitable for applic­ations requiring high-speed data proces­sing. Examples: Redis, MemSQL, VoltDB.

RDBMS

RDBMS is a type of database management system that stores data in the form of tables with rows and columns.
Data is organized into related tables, and relati­onships between tables are establ­ished using keys. It employs Structured Query Language (SQL) for querying and managing the database.
Tables: Data is stored in tables consisting of rows and columns.
Rows: Each row represents a record or entity in the database.
Columns: Each column represents a specific attribute or field of the data.
Keys: Primary keys uniquely identify each row in a table, while foreign keys establish relati­onships between tables.

Types of NoSQL

Document Store: Stores semi-s­tru­ctured data in flexible JSON or BSON documents. Example: MongoDB, Couchbase, CouchDB.
Key-Value Store: Simplest NoSQL model, storing data as key-value pairs. Example: Redis, Amazon DynamoDB, Riak.
Column Family Store: Organizes data into columns instead of rows, suitable for wide-c­olumn databases. Example: Apache Cassandra, HBase.
Graph Database: Designed for managing highly interc­onn­ected data, using graph struct­ures. Example: Neo4j, Amazon Neptune, JanusG­raph.

SQL

Defini­tion: Standa­rdized language for managing relational databases.
Designed for querying, updating, and managing data.
Tips:
Use aliases to simplify column names in queries.
Utilize indexes for faster data retrieval.
Regularly backup and maintain databases to prevent data loss.

SQL Data Types

String Data Types: TEXT, CHAR, VARCHAR, ENUM, SET
Numeric Data Types: INT, FLOAT, DOUBLE, etc.
Date and Time Data Types: DATE, TIME, TIMESTAMP, DATETIME, YEAR
JSON Data Type: Store JSON documents in the JSON column