Show Menu
Cheatography

garis133 SQL Cheat Sheet Cheat Sheet by

Data Type

Integers
int(M)
int(2)
Floati­ng-­point (real) numbers
float(M,D)
float(3,2)
Double­-pr­ecision floati­ng-­point
double­(M,D)
double­(17,2)
Date and time
timest­amp(m)
timest­amp­(8)(for YYYYMMDD)
Fixed Length Strings
char(M)
char(10)
Variable length Strings
varchar(M)
varcha­r(20)
Large amount of text
blob
blob
Values chosen from a list
enum('­val­ue1­',......,­'value n')
enum('­toy­ota­','­dai­hat­su'­,'s­uzuki')

Date Parts

Mili second
ms
Second
ss, s
Minute
mi, n
Hour
hh
Day
dd, d
Day of year
dy, y
Week
wk, ww
Month
mm, m
Year
yy, yyyy
Quarter
qq, q
 

Mathem­atical Functions

Function
Code
Find minimum Value of Group
MIN(co­lumn)
Find maximum Value of Group
MAX(co­lumn)
Find average Value of Group
AVG(co­lumn)
Sum Values in a Group
SUM(co­lumn)
Count rows per Group
COUNT(­column)
Rounding numbers
round(­number)
Randomize numbers
rand()
Absolute Value
abs(nu­mber)
Largest integer not greater
floor(­number)
Smallest integer not smaller
ceilin­g(n­umber)
Square root
sqrt(n­umber)
nth power
pow(base, exponent)
sin cos tan, etc
sin(nu­mber)

String Functions

Functions
Code
Compare Strings
strcam­p(s­tri­ng1­,st­ring2)
Convert to Upper Case
upper(­string)
Convert to Lower Case
lower(­string)
Left-trim whitespace
ltrim(­string)
Encrypt Password
passwo­rd(­string)
Encode String
encode­(st­rin­g,key)
Decode String
decode­(st­rin­g,key)
Get date
curdate()
Get time
curtime()
Extract day name from date string
daynam­e(s­tring)
Extract day number from date string
dayofw­eek­(st­ring)
Extract month from date string
monthn­ame­(st­ring)
 

Create a Table

CREATE TABLE table (
column1 type [[NOT] NULL]
[AUTO_­­IN­C­R­EM­­ENT],
column2 type [[NOT] NULL]
[AUTO_­­IN­C­R­EM­­ENT],
...
other options,
PRIMARY KEY (colu­­mn(s)) );

Insert Data

INSERT INTO table VALUES
(list of values);
INSERT INTO table SET
colum­­n1=­­va­l­ue1,
colum­­n2=­­va­l­ue2,
...
column­­k=­v­a­luek;
INSERT INTO table (colum­­n1­,­c­ol­­umn­­2,...)
VALUES (valu­­e1,­­va­l­u­e2­...);

Insert­/Select

INSERT INTO *table (colum­­n1­,­c­ol­­umn­­2,...)
SELECT statem­­ent;*

Delete data

DELETE FROM table
[WHERE condi­­tio­­n(s)];

Updating Data

UPDATE table SET
colum­­n1=­­va­l­ue1,
colum­­n2=­­va­l­ue2,
...
colum­­nk=­­valuek
[WHERE condi­­tio­­n(s)];

Insert Column

ALTER TABLE table ADD COLUMN
column type options;

Delete Column

ALTER TABLE table
DROP COLUMN column;

Delete Table

DROP TABLE [IF EXISTS] table;

Create an Index

CREATE UNIQUE INDEX name
ON
table (columns)

Create Stored Procedure

CREATE PROCEDURE name
@variable AS datatype = value
AS
-- Comments
SELECT FROM table*
GO
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.