Basic Intro for MySQL

Date : 26/02/2012 Week : 113 People : 6

Time : 3.00 pm to 5.00 pm

Location :  Shri Narayana Guru School

Attendees :  Manimaran, Praveen, Rajkumar, Nagarajan and  Selvamani (Myself).

New Attendee :  Thirumalai

He is doing first year B.Sc., Computer Science in Sankara Arts & Science College.

Discussion :

I (Selvamani) gave the introduction to MySQL. I short described the details of the discussion here :

RDBMS(Relational Database Management System) that runs as a server providing multi-user access to a number of databases.

Storage Engine :
handles both transaction-safe tables and nontransaction-safe tables.

Different type of storage engine in mysql :
MyISAM, InnoDB, MERGE, Memory, Example, Federated, Archive, CSV, Blackhole

MyISAM                –   default storage engine. Manages nontransaction-safe tables. provides high speed storage and retrieval, full text search capablity.
InnoDB                 –   transaction-safe tables. Row-level locking,easy recover, data reliability. Owned by Oracle in 2005, but Data Retrieval is slow
Merge                    –   Collection of MyISAM Tables
Memory/HEAP  –   speed performance for data retrieval. lose data when shutdown. Can be used for temporary tables.
Example               –   To create own engine. cannot store/retrieve data into tables.
Federated             –   Stores data in remote database.
Archive                 –   store large amount of data without indexes.
CSV                        –   stores data in text files using comma seperated values format.
Blackhole            –   does not store. Retrieval always return empty set.

Character Set :
set of symbols and its encodings of a regional language.

Collation :
set of rules for comparing characters in a character set.

MySQL Query :
mysql> status;                       –   To know details of mysql server
mysql> show databases;               –   To view list of databases
mysql> show engines;                 –   To view available storage engines in mysql server
mysql> create database ‘dbname’;     –   To create a new database
mysql> use ‘dbname’;                 –   To change the database name
mysql> select databases();           –   To know the current database
mysql> drop database ‘dbname’;       –   To remove database from server

Create Table MySQL Queries :

mysql> create table if not exists ‘tblname'(col1 datatype primary key auto_increment, col2 datatype,..,colN datatype) {type|engine}=’engine_name’;

–   To create table with storage engine type(default is MyISAM prior to MySQL 5.5, now InnoDB is default)

mysql> create table ‘table_name’ like ‘existing_tbl_name’

–   To copy table structure;

For MyISAM type of table, mysql represents each table by .frm table format file, .MYD(data file), .MYI(Index file).

You can find these files in the location /var/lib/mysql/<dbname>/ as root user.

Booming GNU/Linux,

செல்வமணி. ச


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: