Nethence Documentation Lab Webmail Your IP BBDock  


Those documents are obsolete, please use the Nethence Documentation instead.

HomeUnixWindowsOracleObsoleteHardwareDIYMechanicsScriptsConfigsPrivate

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
SQL Joins
SQLile / SQL Tutorials
Complex Queries
 

Last update: Mar 06, 2016