MySQL

Posted on | Edited on

What is MySQL?

  1. MySQL is a databse system, used for developing web-based software applications
  2. MySQL used for both small and large applications
  3. It is a relational databse management system
  4. It is fast reliable and flexible and easy to use
  5. It supports standard SQL
  6. It is free to download and use
  7. It is presently developed, distributed, and supported by Oracle
  8. It is written by C, C++

Main features of MySQL

  1. MySQL server design is multi-layered with independent modules
  2. it is fully multithreaded by using kernel threads. It can use multiple CPUs if they are available
  3. it provides transactional and non-transactional storage engines
  4. it has very fast thread-based memory allocation system
  5. it supports in-memory heap table
  6. it handles large databases
  7. MySQL server works in client/server or embedded systems
  8. it works on many different platforms

SQL Commands

DDL

DDL is short name of Data Definition Language, which deals with database schemas and descriptions

1
2
3
4
5
6
CREATE: create database and its objects like table, index, views, store procedure, function, and triggers
ALTER: alters the sturcture of the existing database
DROP: delete objects from the database
TRUNCATE: remove all records from a table, including all spaces allocated for the records are removed
COMMENT: add comments to the data dictionary
RENAME: rename an object

DML

DML is short name of Data Manipulation Language, which deals with data manipulation and includes most common SQL statements such as SELECT, INSERT, UPDATA, and DELETE.

1
2
3
4
5
6
7
8
SELECT: retrieve data from a database
INSERT: insert data into a table
UPDATE: updates existing data with a table
MERGE: UPSERT operation(insert or update)
CALL: call a PL/SQL or Java subprogram
DELETE: delete all records from a database table
EXPLAIN PLAN: interpretation of the data access path
LOCK TABLE: concurrency control

DCL

DCL is short name of Data Control Language, which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.

1
2
GRANT: allow users access privileges to database
REVOKE: withdraw users access privileges given by using the GRANT command

TCL

TCL is short name of Transaction Control Language, which deals with a transaction within a database.

1
2
3
4
COMMIT: commits a transaction
ROLLBACK: rollback a transaction in case of any error occurs
SAVEPOINT: to rollback the transaction making points within groups
SET TRANSACTION: specify characteristics of the transaction

Replication

Asynchronous replicas based on a Binary LoG;
Log Format:
Statement:SQL statements, smallest size
Row: event data, biggest size, cannot read directly
Mixed: save unsure data between statement and row

Master: replicate database
Slave: replicate database, table

Copy Format:
Coping based on Binary Log
Coping based on event using GTID

Semi-synchronized replication:

Reference

MySQL