PostgreSQL / MySQL Database cheat sheet / formulas list

TO CREATE A DATABASE
Create database dbname; 

DISPLAY LIST OF ALL THE DATABASES

\l

CONNECT THE DATABASE
\c dbname;

DROP DATABASE
drop database dbname;

CREATING TABLES
create table tablename (col1 datatype primary key not null, col2 datatype not null);
or
create table tablename (col1 datatype not null, col2 datatype not null, primary key(col1));

DROPPING TABLE
drop table tablename;

CREATING SCHEMAS
create schema schemaname;

DROPPING SCHEMAS
drop schema schemanames;

INSERTING VALUES IN TABLE
insert into tablename (col1 int, col2 varchar(20), col3 real, col4 date) values (1, 'any text', 32, 200.00,'2001-07013')

DISPLAYING ALL COLUMNS OF TABLE
select * from tablename;

LIMIT THE NUMBER OF ROWS OF TABLE
select * from tablename limit (number of rows) offset [row num] ;

DISPLAYING PARTICULAR COLUMNS OF TABLE
select col1, col3 from tablename;
select distinct  columnname from tablename;

DISPLAYING PARTICULAR COLUMNS OF TABLE WITH A PARTICULAR CONDITON
select col1, col2
from tablename
where (condition/ expression);

UPDATING TABBLE
update tablename set col1 = val1, col2= val2 where condition;

DELETE QUERY
delete from tablename where condition;

DELETE ALL ROWS OF THE TABLE 
delete from table name;

STARTS WITH/ ENDS WITH QUERY
select * from tablename where column like 'XXX%';  [starts with XXX]
select * from tablename where column like '%XX%';  [XX in any position]
select * from tablename where column like '%_AB%';  [A in second position, B in third position]

SORTING IN ASCENDING/ DESCENDING ORDER
select col1,col2
from tablename
where condition
order by col1 (asc/desc/ nulls first/ nulls last);

GROUP BY CLAUSE
select col1,col2
from tablename
where condition
group by col1, col2
order by col1 (asc/desc/ nulls first/ nulls last);

HAVING CONDITION
select col1,col2
from tablename
where condition
group by col1, col2
having conditon
order by col1 (asc/desc/ nulls first/ nulls last);

AGGREATION FUNCTIONS

select count(*) from tablename;

select count(*) from tablename where col = value;

select max(col) from tablename;

select min(col) from tablename;

select  avg(col) from tablename;

select  sum(col) from tablename;

UNION/ UNION ALL

select col1

from table1

where condition

union/ union all

select col1

from table2

where condition;

ADDING NEW COLUMN

alter table tablename add column datatype;

DROPPING COLUMN

alter table tablename drop column columnname;

CHANGING DATATYPE OF A COLUMN

alter table tablename alter column columnname type datatype;

ADDING CONSTRAINTS

alter table tablename add constraint MyUniqueConstraint  Unique(col1,col2 );

alter table tablename add constraint MyCheckConstraint  check(col1,col2 );

alter table tablename add constraint MyPKConstraint  primary key(col1,col2 );

DROPPING CONSTRAINTS

alter table tablename drop constraint constraintname;

TRUNCATE TABLE

truncate table tablename;

ALIASING TABLE

selectc col1, col2

from tablename as aliasname

where condition;

ALIASING COLUMN

selectc col1 as aliasname

from tablename 

where condition;

CREATING VIEWS

create view viewname as

select col1, col2 

from tablename

where condition;

DROPPING VIEWS

drop view viewname;

SUBQUERY

select colnames

from table1, table2

where colname operator

  (select colname

    from table1, table2

     where conditon)

update table set colname= newvalue

where operator [ value] (select colname

                                        from tabname

                                         where condition)

delete from tablename

where operator [ value] (select colname

                                        from tabname

                                         where condition)

 GRANTING PRIVILEGES

grant privilege

on object

to {public/ group groupname, username}

REVOKING PRIVILEGES

revoke privilege

on object

from {public/ group groupname, username}

CREATING ROLES

create user username with password 'admin';

create role

select * from tablename;

grant all on tablename to username;

revoke all on tablename from username;

drop user username;

CREATING TRIGGERS

create trigger triggername

on tablename

for each row

on triggerbody 

triggertime :{before/ after}

triggrerevent: {inset/ update/ delete}

JOINS

select col1, col2 from table1 cross join table2;

select table1.col1, table2.col2

from table1 inner join table2

on table1.commonfield = table2.commonfield;

select table1.col1, table2.col2

from table1 left outer join table2

on table1.commonfield = table2.commonfield;

select table1.col1, table2.col2

from table1 right outer join table2

on table1.commonfield = table2.commonfield;

select table1.col1, table2.col2

from table1 full outer join table2

on table1.commonfield = table2.commonfield;

TYPES OF CONSTRAINTS

not null

unique

primary key

foreign key

check constraint

exclusion constraint

TYPES OF JOINS

cross

inner

left outer

right outer 

full outer 

DATA DEFINITION LANGUAGE 

- create

- alter

- drop

-rename

DEFINITION MANIPULATION LANGUAGE

-select

-insert

-update

-delete 

DATA CONTROL LANGUAGE

- grant

- revoke

WHAT IS DATA DEFINITION AND DATA MANIPULATION LANGUAGE ?
CHECK HERE 

MORE ENGINEERING BLOGS CLICK HERE