View previous topic :: View next topic |
Author |
Message |
beandog Bodhisattva


Joined: 04 May 2003 Posts: 2066 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
|
|