Creating User and granting privilleges

Create user 'fun'@'localhost' IDENTIFIED BY 'fun'
The user should have global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new row in the mysql.user table that has no privileges.

The user 'fun' can't acess mysql through telnet(i.e. from other machines).
GRANT ALL ON mydb.* TO 'fun'@'localhost';
it Grants all the privilege to user fun on the mydb database.

GRANT SELECT, INSERT ON somedb.* TO 'fun'@'localhost';
it grants the select and insert privilege on somedb database.

for more information http://dev.mysql.com/doc/refman/5.1/en/grant.html

Mandate only pre define values to columns

CREATE TABLE `temp1` (
`col1` set('Select','Update') character set utf8 NOT NULL default '',
`col2` int) ;

The `col1` in the table will accept only two values i.e. 'select' and 'update'.

If you try to insert any other value part form this it throws a error
ERROR 1265 (01000): Data truncated for column 'col1' at row 1

Handler, Transaction and procedure

A simple example of usage of handler, transaction and procedure

CREATE TABLE CTEMP (ID INT);

CREATE TABLE DTEMP (ID INT PRIMARY KEY);

DELIMITER //
CREATE PROCEDURE insertcd
(a int, b int)
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
ROLLBACK;
START TRANSACTION;

insert into ctemp values(a);
insert into dtemp values(b);



COMMIT;
END;//

DELIMITER ;

CALL INSERTCD(2,2);

SELECT *FROM CTEMP;

Shows one row

SELECT *FROM DTEMP;

Shows one row

CALL INSERTCD(2,2);

SELECT *FROM CTEMP;

Shows one row (insert is successful for second row for table ctemp but handler as rollback it becuase of insert statement for table dtemp fails)

SELECT *FROM DTEMP;

Shows one row

Mysql Table Types

MySQL has six distinct table types.

• MyISAM
• MERGE
• ISAM
• HEAP
• InnoDB
• BDB or BerkeleyDB Tables

The default table type for MySQL is MyISAM. It has table level locking., doesn't support transaction.

BDB uses Page level locking, supports transaction.

innoDB uses Row level locking. supports transaction.

HEAP tables use hashed indexes and are stored in memory. This makes them very fast.

http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html