| View previous topic :: View next topic |
| Author |
Message |
beandog
Bodhisattva
Joined: 04 May 2003
Posts: 2070
Location: /usa/utah
|
 Posted: Sat Oct 16, 2004 4:45 pm Post subject: [Mini-Doc] SQL Cheatsheet |
 |
|
[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
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 |
|
 |
|
|
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
|
|
Documentation, Tips & Tricks
