Wednesday, 23 March 2011

SQL Crib Sheet

This is my SQL crib sheet - 




ALTER TABLE

alter table table_name
[modify | add | drop]
[column column_name][datatype | null not null][restrict | cascade]
[add | drop] [constraint constraint_name]

Alters a table's columns

COMMIT

commit [transaction]

Commits a transaction to the database.

CREATE INDEX

create index index_name
on table_name(column_name)

Create an Index on a table

CREATE TABLE

create table table_name
(column1  data_type [null | not null],
column2  data_type [null | not null]...)

Creates a database table.

CREATE TABLE AS

create table table_name as
select column1, column2,...
from table_name
[where conditions]
[group by column1, column2...]
[having conditions]

Creates a table based on the contents of another table.

CREATE VIEW

create view as
select column1, column2,...
from table_name
[where conditions]
[goup by column1, column2...]
[having conditions]

Create a view of a table.

DELETE

delete
from table_name
[where conditions]

Deletes rows of data from a table.

DROP INDEX

drop index index_name

Drops - or gets rid of - an index on a table.

DROP TABLE

drop table table_name

Drops - or removes - a table from the database.

DROP VIEW

drop view view_name

Drops a view in a table.

GRANT

grant privilege1, privilege2,... to user_name

Gives privileges to a user.

INSERT INTO

insert into table_name [column1, column2, ...]
values('value1', 'value2',...)

Inserts new data into the rows of a table.

INSERT ... SELECT

insert into table_name
select column1, column2, ...
from table_name
[where conditions]

Inserts new data into a table based on the data in another table.

REVOKE

revoke privilege1, privilege2, ... from user_name

Removes a user's privileges.

ROLLBACK

rollback [to savepoint_name]

Undoes a database transaction.

SAVEPOINT

savepoint savepoint_name

Creates a point to which to 'rollback' to if necessary.

SELECT

select [distinct] column1, column2, ...
from table1, table2,...
[where conditions]
[group by column1, column2,...]
[having conditions]
[order by column1, column2...]

Returns data from one or more tables. Used to create database queries.

UPDATE

update table_name
set column1 = 'value', column2 = 'value'...
where [conditions]

Updates existing rows in a table.

No comments: