Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
[Mini-Doc] SQL Cheatsheet
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks
View previous topic :: View next topic  
Author Message
beandog
Bodhisattva
Bodhisattva


Joined: 04 May 2003
Posts: 2066
Location: /usa/utah

PostPosted: Sat Oct 16, 2004 4:45 pm    Post subject: [Mini-Doc] SQL Cheatsheet Reply with quote

[Mini-Doc] SQL Cheatsheet

Be warned: This is by no means a complete or very cleaned up list -- its mostly just a formatted dump of my notes I've been taking. I would like to clean it up though as I go along.

I originally wanted to have the queries for SQLite included as well, but they usually almost identical to every command PostgreSQL uses.

Show databases, tables, table description, indexes

MySQL
Code:
show databases;
show tables;
desc table_name;
SHOW INDEX FROM table_name;

PostgreSQL
Code:
\l
\dt
\d table_name
\d table_name


Create new table from a SELECT statement

MySQL
Code:
CREATE TABLE new_table SELECT * FROM old_table;

PostgreSQL
Code:
CREATE TABLE new_table AS SELECT * FROM old_table;


Create an index

PostgreSQL
Note that with postgres, an index has a unique name across that db. You can't have two indexes named "id", even though they are on different tables. However, it does make for managing them a bit easier (imo).
Code:
CREATE [UNIQUE] INDEX index_name ON table_name (column1);


Delete an index

MySQL
Code:
ALTER TABLE table_name DROP INDEX index_name; # I think -- pulled this one from memory

PostgreSQL
Code:
DELETE INDEX index_name;


Rename table

MySQL
Code:
ALTER TABLE table_name RENAME TO new_table_name;

PostgreSQL
Code:
ALTER TABLE table_name RENAME new_table_name;


Replace text

MySQL
Code:
UPDATE table_name SET column_name = REPLACE(column_name, 'old', 'new');

PostgreSQL
Code:
UPDATE table_name SET column_name = REPLACE(column_name, 'old', 'new');


Rename column

MySQL
Code:
ALTER TABLE table_name CHANGE column_old column_new (type);


Add a new column

MySQL
Code:
ALTER TABLE table_name ADD COLUMN new_column (type) [not null [FIRST|AFTER column]];


Add a primary key

MySQL
Code:
ALTER TABLE table_name ADD PRIMARY KEY(column_name);


Add autoincrement

MySQL
Code:
ALTER TABLE tablename ADD PRIMARY KEY(column);
ALTER TABLE table_name CHANGE column_name column_name int unsigned auto_increment;


Empty table, reset autoincrement value

MySQL
Code:
TRUNCATE table_name;

PostgreSQL
Code:
TRUNCATE table_name;



Dump tables

MySQL
Code:
# mysqldump database_name --tables table1 table2 table3

PostgreSQL
Code:
# pg_dump database_name # all tables
#pg_dump --table=table_name # one table


SQLite-compatible database dump

PostgreSQL
Code:
pg_dump -t tablename -a -f table.sql -O -x -d database


Set some environment variables

MySQL
Code:
# vim ~/.my.cnf
[client]
host=localhost # where to connect
password # always prompt for password
user=steve # mysql username
database=gentoo_org # database name
pager=/usr/bin/less # pager to use
port=3306 # port to connect to mysql on
# see mysql --help for more variables

PostgreSQL - see http://www.postgresql.org/docs/current/static/libpq-envars.html
Code:
# vim ~/.bashrc
export PGDATABASE="gentoo_org"
export PGUSER="steve"


Edit a query with $EDITOR

PostgreSQL
Code:
\e


Create a new user

PostgreSQL
Code:
CREATE USER 'steve' WITH PASSWORD 'password';
CREATE USER 'steve' WITH PASSWORD 'password' CREATEUSER; # add create user privileges
CREATE USER 'steve' WITH PASSWORD 'password' CREATEDB; # add create database privileges
CREATE USER 'steve' WITH PASSWORD 'password' CREATEUSER CREATEDB; # add both

_________________
If it ain't broke, tweak it. dvds | blurays | blog | wiki
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum