DDL - DML - DCL - TCL

DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL
Data Control Language (DCL) statements. Some examples:

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

TCL
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

DML(Create, Update, Dalete) statements can be rollbacked. But, DDL are autocommit.
DML commands can not be rollback when a DDL command is executed immediately after a DML. DDL after DML means "auto commit".

MYSQL

• Overview
• Table types
• Managing Database
• DDL
• Internal Locking Method

MySQL - Overview

My SQL, not "My sequel" is a relational database management system (RDBMS). As the world's most popular open source database, MySQL is used by a wide range of organizations to manage their data.

web: www.mysql.com

• Managing the Database
• Understanding MySQL Table Type
• Working with tables
• Creating and removing Index
• Querying data from MySQL
• INSERT-UPDATE-DELETE
• Database Table Maintanance

Link-1: www.mysqltutorial.org

More..

• Internal Locking Methods

Table Types

MySQL supports various of table types or storage engines. These are:

1. ISAM
2. MyISAM
3. InnoDB
4. BerkeleyDB (BDB)
5. MERGE
6. HEAP

• Only InnoDB and BDB tables are transaction safe and
• Only MyISAM tables support full-text indexing and searching feature.
• MyISAM is also the default table type.

ISAM
ISAM had been deprecated and removed from version 5.x. All of it functionality entire replace by MyISAM. ISAM table has a hard size 4GB and is not portable.

MyISAM

• This is default type when you create table.
• MyISAM table work very fast but not transaction-safe.
• The size of table depends on the OS and the data file are portable.
• Hard size - 64 keys per table and maximum key length of 1024 bytes.

InnoDB

• InnoDB table are transaction safe.
• Supports row-level locking.
• Foreign keys are supported in InnoDB tables.
• The data file of InnoDB table can be stored in more than one file. So,
• The size of table depends on the disk space.
• Like the MyISAM table type, data file of InnoDB is portable.

Disadvantage - In comparison with MyISAM is it take more disk space.

BDB

• BDB is similar to InnoDB in transaction safe.
• It supports page level locking but data file are not portable.

MERGE
Merge table type is added to treat multiple MyISAM tables as a single table so it remove the size limitation from MyISAM tables.

HEAP

• Heap table is stored in memory so it is the fastest one.
• Because of storage mechanism, the data will be lost when the power failure and sometime it can cause the server run out of memory.

Disadvantage: Heap tables do not support columns with AUTO_INCREMENT, BLOB and TEXT characteristics.

DDL

CREATE TABLE:

Statement Pattern

CREATE TABLE [IF NOT EXISTS] table_name(
column_list
) type=table_type

Example-1

CREATE TABLE employees (
employeeNumber into(11) NOT NULL,
lastName varchar(50) NOT NULL,
officeCode varchar(10) NOT NULL,
reportsTo int(11) default NULL,
PRIMARY KEY (employeeNumber)
);

Example-2: Defining Duplicate Primary Key

CREATE TABLE payments (
customerNumber int(11) NOT NULL,
checkNumber varchar(50) NOT NULL,
paymentDate datetime NOT NULL,
amount double NOT NULL,
PRIMARY KEY (customerNumber,checkNumber)
);

Example-3: Defining Storage Engine

CREATE TABLE database_name.table_name(
column1 NOT NULL AUTO_INCREMENT ,
column2 VARCHAR( 20 ) NOT NULL ,
column3 VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL ,
PRIMARY KEY ( column1)
) ENGINE = MYISAM ;

Example-4: Create table from another table

CREATE TABLE new_table_name
AS (select * from old_table);

CREATE TABLE new_table_name
AS (select col1, col2 from old_table where cond1);


DESCRIBE TABLE: DESCRIBE table_name;

SHOW TABLES: SHOW TABLES
This will show all the tables.

More..

Internal Locking Methods

Locking performed within the MySQL server itself to manage contention for table contents by multiple sessions. This type of locking is internal because it is performed entirely by the server and involves no other programs. External locking occurs when the server and other programs lock table files to coordinate among themselves which program can access the tables at which time.

MySQL uses -
Table-level locking for - MyISAM, MEMORY, and MERGE tables, and
Row-level locking for - InnoDB tables.

Generally it is difficult to say that a given lock type is better than another. Everything depends on the application and different parts of an application may require different lock types.

If you want to use a storage engine with row-level locking, you should look at what your application does and what mix of select and update statements it uses.

For example, most Web applications perform many selects, relatively few deletes, updates based mainly on key values, and inserts into a few specific tables. The base MySQL MyISAM setup is very well tuned for this.

Table locking (in MySQL) is deadlock-free for storage engines that use table-level locking. Deadlock avoidance is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order.

MySQL grants table write locks as follows:
1. If there are no locks on the table, put a write lock on it.
2. Otherwise, put the lock request in the write lock queue.

MySQL grants table read locks as follows:
1. If there are no write locks on the table, put a read lock on it.
2. Otherwise, put the lock request in the read lock queue.

Table updates are given higher priority than table retrievals. Therefore, when a lock is released, the lock is made available to the requests in the write lock queue and then to the requests in the read lock queue. This ensures that updates to a table are not “starved” even if there is heavy SELECT activity for the table. However, if you have many updates for a table, SELECT statements wait until there are no more updates.

The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no free blocks in the middle of the data file, rows are always inserted at the end of the data file. In this case, you can freely mix concurrent INSERT and SELECT statements for a MyISAM table without locks. That is, you can insert rows into a MyISAM table at the same time other clients are reading from it. Holes can result from rows having been deleted from or updated in the middle of the table. If there are holes, concurrent inserts are disabled but are enabled again automatically when all holes have been filled with new data.. This behavior is altered by the concurrent_insert system variable.

Advantages of row-level locking:

• Fewer lock conflicts when different sessions access different rows
• Fewer changes for rollbacks
• Possible to lock a single row for a long time
  

Disadvantages of row-level locking:

• Requires more memory than table-level locks
• Slower than table-level locks when used on a large part of the table because you must acquire many more locks
• Slower than other locks if you often do GROUP BY operations on a large part of the data or if you must scan the entire table frequently

Generally, table locks are superior to row-level locks in the following cases:

• Most statements for the table are reads
• Statements for the table are a mix of reads and writes
• SELECT combined with concurrent INSERT statements, and very few UPDATE or DELETE statements
• Many scans or GROUP BY operations on the entire table without any writers

With higher-level locks, you can more easily tune applications by supporting locks of different types, because the lock overhead is less than for row-level locks.

Managing the Database

CREATE DB:

To create a database in MySQL, you use the CREATE DATABASE statement:

Command: CREATE DATABASE [IF NOT EXISTS] database_name;
OR: CREATE DATABASE database_name;

CREATE DATABASE statement will create the database with the given name you specified. IF NOT EXISTS is an option part of the statement, this part prevents you from error if there is a database with the given name exists on the database server.

SHOW DB:

SHOW DATABASE statement will show all databases in your server.
Command: SHOW DATABASES;

SELECT DB:

To select a database which you will work with, you use this statement.
Command: USE database_name;

REMOVE DB:

Removing database means you delete the database, all the data and related objects inside the database permanently and cannot undo it.
Command: DROP DATABASE [IF EXISTS] database_name;

Link -1 : www.mysqltutorial.org