Database

=>Diff b/w myisam and innodb

Myisam:(befor 5.5.5 default engine for mysql)
      Table level locking
      No ordering in storage of data
      Not *ACID compliant,no foreign key support and non-transactional
      reading records fast

Innodb:(default as of 5.5.5)
      Row level locking
      Row data stored in pages in PK order
      *ACID compliant and hence fully transactional with ROLLBACK and COMMIT and support for Foreign Keys
      reading records slow
ALTER TABLE <table-name> ENGINE=INNODB;    
==================================
=>Diff b/w primary key vs unique key?
A primary key must be unique.

A unique key does not have to be the primary key - see candidate key.

That is, there may be more than one combination of columns on a table that can uniquely identify a row - only one of these can be selected as the primary key. The others, though unique are candidate keys.

 there can be only one primary key for a given table, while there can be many unique keys.

 primary key cannot be NULL

 ====================================
 =>diff b/w mysql and postgresql?
 dates format different

mysql:
 #comment
 "
 case insensitive
 Only follows some of the ANSI SQL standards
 Faster
 No Foreign key support
 No Sub-selects
 Transactions  with innodb engine only


 postgresql:
 -- comment(ANSI standard)
 '
 case sensitive
 Slower
 Closer to ANSI SQL standard
 Have Foreign key support
 Have Sub-selects
 Have Transactions support
 ==========================
 =>rdbms vs nosql?
=RDBMS is completely structured way of storing data.NoSQL databases also have a structure to store data, but these structures are less strict as relational schema, so it became a better choice to some applications.
= the amount of data stored mainly depends on the Physical memory of the system. While in the NoSQL you don't have any such limits as you can scale the system horizontally.
=It's important to say that NoSQL databases are a complement in database field, and not a replacement for RDBMS.
=Graph Databases is also a very popolar NoSQL database. . Examples of the popular graphical database are Neo4j,
= OrientDB, InfiniteGraph, AllegroGraph etc.
=Facebook uses open sources the Cassandra
=NoSQL stands for "Not only SQL"
=E.g. graph databases like Neo4j, document databases like CouchDB and object databases like db4o.
=================================

   
postgresql?
https://help.ubuntu.com/8.04/serverguide/postgresql.html
http://www.thegeekstuff.com/2009/04/15-practical-postgresql-database-adminstration-commands/
 $sudo su postgres
 $psql
 $\l =>lists all databases
 $\c =>switch database
 $select current_database();

 $ CREATE USER alaramana WITH password 'password';
 $ CREATE DATABASE mydb WITH OWNER alaramana
============
===========
Install mysql?
sudo apt-get install libmysqlclient-dev, mysql-server, mysql-client
============

install sqlite3?
sudo apt-get install sqlite3 libsqlite3-dev
sudo gem install sqlite3-ruby

============

postgresql backup
=================
 $ pg_dump mydb > db.sql #To dump a database called mydb into a SQL-script file:
 $ psql -d newdb -f db.sql  #To reload such a script into a (freshly created) database named newdb:
============================
join vs include in sql?
=>The difference between joins and include is that using the include statement generates a much larger SQL query loading into memory all the attributes from the other table(s).
For example, if you have a table full of comments and you use a :joins => users to pull in all the user information for sorting purposes, etc it will work fine and take less time than :include, but say you want to display the comment along with the users name, email, etc. To get the information using :joins, it will have to make separate SQL queries for each user it fetches, whereas if you used :include this information is ready for use.

:joins returns read-only objects, :includes does not

:joins uses inner join, :includes uses outer join.

the main reason of :includes is eager loading, to avoid the N+1 problem of loading in attributes of each object using a separate query.
=============

POSTGRESQL:
===========
https://help.ubuntu.com/community/PostgreSQL
https://help.ubuntu.com/8.04/serverguide/C/postgresql.html

==================
mysql engine
SELECT table_schema,engine FROM information_schema.tables WHERE table_name='resources';


$mysql> SELECT * FROM mysql.user;      ==>to find list of users for mysql database
http://www.pantz.org/software/mysql/mysqlcommands.html    ======>mysql commands



postgresql:
===========
http://olmonrails.wordpress.com/2008/08/12/switching-rails-to-postgresql/
sudo -u postgres psql
\l
=>To find list of db's
\connect <database_name>         ==>to switchover to another database

sudo su
su postgre
psql


========
Rename mysql database name:
mysqldump -u username -p -v olddatabase > olddbdump.sql
mysqladmin -u username -p create newdatabase
mysql -u username -p newdatabase < olddbdump.sql