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

Help Us Go Positive!

We offset our carbon usage with Ecologi. Click the link below to help us!

We offset our carbon footprint via Ecologi
 

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.