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



Show databases, 

show databases;

choose a database, 


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;




Create a database, 

touch database1.db
chown user:group database1.db
chmod 660 database1.db


Get to the prompt, 

sqlite3 database1.db


Show tables, 



Dump everything, 



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('/;
        " buyers > buyers.sql



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