MySQL Theory and Practice
On ubuntu, mysql hsa its data under /var/lib/mysql.
MySQL Storage Engines
The various storage engines are provided to meet different requirements.
MyISAM is the default storage engine.
MySQL 5.4 supported storage engines: MyISAM, InnoDB, Memory, Merge, Archive, CSV, Federated, Blackhole, Example.
MyISAM
It is the default storage engine and the one that is used the most.
It doesn’t support transaction, row-level locking, nor foreign key.
InnoDB
A transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data.
It supports transaction, row-level locking, and foreign key.
Compared with MyISAM, InnoDB is safer, provides better concurrency for tables that get many updates concurrently with reads.
CSV
The CSV storage engine stores data in text files using comma-separated values format.
They exist to allow MySQL to easily exchange data with other applications that make use of CSV files, such as spreadsheets.
They make no use of any sort of indexing and SELECT is not at all efficient, and performs a complete table scan.
Archive
It allows storing large amounts of data without taking up too much space.
It's perfect for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.
Memory — Stores all data in RAM for extremely fast access in environments that require quick lookups of reference and other like data
Merge — Allows a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as one object. Good for VLDB environments such as data warehousing.
Federated — Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.
Blackhole — The Blackhole storage engine accepts but does not store data and retrievals always return an empty set. The functionality can be used in distributed database design where data is automatically replicated, but not stored locally.
Example — The Example storage engine is "stub" engine that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them. The purpose of this engine is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. As such, it is primarily of interest to developers.
Comparing Transaction and Nontransaction Engines
Transaction-safe tables (TSTs) have several advantages over nontransaction-safe tables (NTSTs):
They are safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.
It supports atomic operation, you can combine many statements and commit them, all operations are all carried out, or none at all.
Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads.
Nontransaction-safe tables have several advantages of their own, all of which occur because there is no transaction overhead:
Much faster
Lower disk space requirements
Less memory required to perform updates
Practices
SET storage_engine=MYISAM; //set the default storage engine
CREATE TABLE t (i INT) ENGINE = INNODB;
CREATE TABLE archive_names(firstname CHAR(30), surname CHAR(40), age INT) ENGINE = ARCHIVE;
ALTER TABLE t ENGINE = MYISAM;
Show engines;
Transaction Isolation Levels
The default isolation level for InnoDB is REPEATABLE READ.
READ UNCOMMITTED
Barely transactional, this setting allows for so-called 'dirty reads', where queries inside one transaction are affected by uncommitted changes in another transaction.
READ COMMITTED
Committed updates are visible within another transaction. This means identical queries within a transaction can return differing results. This is the default in some DBMS's.
REPEATABLE READ
This is the default isolation level for InnoDB tables. Within a transaction, all reads are consistent.
SERIALIZABLE
Updates are not permitted in other transactions if a transaction has run an ordinary SELECT query, i.e. queries are treated as if they had a LOCK IN SHARE MODE.
The default isolation level for SQL SERVER and Oracle is READ COMMITTED.
The default isolation level for SQL DB2, MySQL and PostgreSQL is READ COMMITTED.
Enable/Dsiable Automatic Commits
SET AUTOCOMMIT=1|0;
Read locks for Updating
Use this when we would want to read a record in order to update it, knowing that we are not conflicting with any other connection that is doing the same thing.
SELECT MAX(f) FROM t FOR UPDATE;
Updates or read for updating operations in other transaction wait until the active transaction is complete.
Read locks for sharing - LOCK IN SHARE MODE
It will stop any updates or deletes of the row being read, and if the latest data is still uncommitted, will wait until that transaction is committed before returning any results.
SELECT MAX(f) FROM t LOCK IN SHARE MODE;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED|(READ UNCOMMITTED);
SELECT @@tx_isolation;
Use EXPLAIN to check query execution plan in MySQL and optimize Queries
EXPLAIN tbl_name // synonymous with DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name.
EXPLAIN select * from t; // check query execution plan
MySQL Administration
mysqld &
mysql [-h hostname] -u username -p'password'
mysqladmin -u root -p'password' status|shutdown|password new-password
show databases;
use ${database_name}
show tables;
describe|desc ${table_name}
Display the CREATE TABLE statement
SHOW CREATE TABLE Country\G;
Display detailed information about the indexes
SHOW INDEX FROM Country\G;
SQL Example
Creating a MySQL Database
create database bank;
mysql -u root -p
Create the lrngsql database user with full privileges on the bank database
grant all privileges on bank.* to 'lrngsql'@'localhost' identified by 'xyz';
mysql -u lrngsql -p;
use bank;
mysql -u lrngsql -p bank[database name]
INSERT INTO t (id, name) VALUES(1,'Bea'),(2,'Belle'),(3,'Bernice');
SELECT
COUNT(*) AS 'Total Rows',
COUNT(DISTINCT name) AS 'Distinct Values',
COUNT(*) - COUNT(DISTINCT name) AS 'Duplicate Values' FROM t;
Show the Current Transaction Mode
SELECT @@AUTOCOMMIT FROM DUAL;
SET AUTOCOMMIT = 0;
SELECT @@session.tx_isolation FROM DUAL;
Get data in XML format
mysql -u lrngsql -p --xml bank
SELECT now();
SELECT now() FROM dual;
Metadata
SHOW TABLES;
DESC|describle customer;
SHOW TABLE STATUS LIKE 'transaction' \G
MySQL Data Types
Character Data
char(20) /* fixed-length */
varchar(20) /* variable-length */
Character sets
varchar(20) character set utf8
Set the default character set for your entire database:
create database foreign_sales character set utf8;
Text data
Tinytext[255],Text [65,535],Mediumtext [16,777,215],Longtext [4,294,967,295]
Numeric data
Tinyint,Smallint,Mediumint,Int,Bigint, Float(p,s),Double(p,s)
Temporal Data
Date,Datetime,Timestamp,Year,Time
Resources:
