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 :
MySQL:
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,
செல்வமணி. ச