Beginners guide to MySQL and MariaDB

Regexps

MySQL support pattern matching using regular expressions REGEXP operator. Below are the different Pattern matching Supported in MySQL.

Here is an example 

Transaction

A transaction is set of sequential group of database manipulation operations, which perform as one single work unit.

Transactions follow four standard properties, usually referred as acronym ACID:

• Atomicity: ensures all operations within work unit are completed successfully.
• Consistency: ensures that database properly change state after a successfully committed transaction.
• Isolation: enable transaction to operate independently transparent on each other.
• Durability: ensures result or effect of committed transaction persists in case of system failure.

COMMIT and ROLLBACK:

The two keywords Commit and Rollback are mainly used for MySQL Transactions. 

• On successful transaction Completion, COMMIT command should be issued so changes will be applied to table.
• If a failure occurs, a ROLLBACK command will restore to previous state prior to transaction.

 The transaction behavior can be controlled by setting session variable called AUTOCOMMIT. If 1 is set each SQL statement will be complete transaction and committed by default. If 0 the transaction will not be committed until it is explicitly COMMITED.

 Here is an example

START TRANSACTION;
SELECT @B:=SUM(balls) FROM baseball_balls WHERE color='white';
UPDATE players SET ball_allotted=@B WHERE id=1;
COMMIT;

What's happening above is rather very simple to understand. 

1. We are starting a transaction with START TRANSACTION statement
2. We are selecting number of balls with a set color
3. We are updating players table and setting ball_allotted to the extracted number from previous query and updating it for player with ID 1
4. We then COMMIT a transaction

If there is any failure when you execute a query between START TRANSACTION and COMMIT,  data will not be saved.

More on transactions can be found on MySQL docs site

Indexes

In database index is used to improve speed of operations in a table. Indexes are created using one or more columns, so that it provide lookup and efficient ordering of records to access.

Indexes is also type of tables, which keep index field or primary key and a pointer to each record into actual table.

INSERT and UPDATE statements takes more time on tables having indexes whereas SELECT statements become fast on those tables.

Simple and Unique Index:

Below is syntax to create an Index on table: 

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...); 

The one or more columns can be used create an index.

ALTER command to add and drop INDEX:

The four types of statements for adding indexes to table are:

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): The statement adds a PRIMARY KEY, which means indexed values should be unique and cannot be NULL.
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): The statement creates an index for which values should be unique.
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): It adds ordinary index in which any value may appear more than once.
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): It creates a special FULLTEXT index which is used for text-searching purposes.

mysql> ALTER TABLE books_tbl ADD INDEX (author_name);

Alter command is used to drop any INDEX using DROP clause.

mysql> ALTER TABLE books_tbl DROP INDEX (author_name);

More on indexes can be found here 

Or you can watch this useful tutorial

video tutorial by Sheeri C. Hosted at Youtube