this is obsolete doc -- see http://doc.nethence.com/ instead

SQL notes & queries 

Oracle Mysql Community 

If you get this error running the secure script, 

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

==> use the temporary password provided in the log, 

grep 'temporary password' /var/log/mysqld.log

MySQL/Madiadb 

Show databases, 

show databases;

choose a database, 

use DATABASE_NAME;

view what database is currently used, 

select database();

ref http://dev.mysql.com/doc/refman/5.0/en/getting-information.html 

Show tables, 

show tables;

describe a table, 

describe table1;

ref http://dev.mysql.com/doc/refman/5.0/en/getting-information.html 

Delete everything into a table (even increments), 

truncate table1;

delete table's content only, 

delete table1;

totally drop a table, 

drop table1;

SQLite 

Create a database, 

touch database1.db

chown user:group database1.db

chmod 660 database1.db

Get to the prompt, 

sqlite3 database1.db

Show tables, 

.tables

Dump everything, 

.dump

Dump one table, 

.dump table1

or from the UNIX prompt, 

sqlite database1.db '.dump table1' | sed -n 2p

Create a table with a primary key column, 

create table table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, col2 TEXT, col3 TEXT);

create table table1 (ref INTEGER, col2 TEXT, col3 NUMERIC, PRIMARY KEY(ref));

add a row into that table, 

insert into table1 values (1, 'text example', 100.20);

Create a table with an autoincrement primary key column, 

create table table2 (id INTEGER PRIMARY KEY AUTOINCREMENT, col2 TEXT, col3 NUMERIC);

add a row into that table, 

insert into table2 values (NULL, 'text example', 100.20);

Add a column, 

alter table table1 add col4 TEXT;

Delete a table, 

drop table table1;

CVS to SQLite 

Convert a CSV to SQL, 

sed "

        s/^/insert into buyers values('/;

        s/,/','/g;

        s/$/');/;

        s/''/NULL/g;

        " buyers > buyers.sql

References 

SQLzoo.net : http://sqlzoo.net/ 

SQL Joins : http://www.shokhirev.com/nikolai/abc/sql/joins.html 

SQLile / SQL Tutorials : http://www.shokhirev.com/nikolai/abc/sql/sql1.html 

Complex Queries : http://philip.greenspun.com/sql/complex-queries.html