Show Menu
Cheatography

SQL Server Cheat Sheet by

MABD Cheat Sheet

Coloumn Type

Purpose
Data Type
Example
Integers
int(M)
int(5)
Floati­ng-­point (real) numbers
float(M,D)
float(­12,3)
Double­-pr­ecision floati­ng-­point
double­(M,D)
double­(20,3)
Dates and times
timest­amp(M)
timest­amp(8) (for YYYYMMDD)
Fixed-­length strings
char(M)
char(10)
Variab­le-­length strings
varchar(M)
varcha­r(20)
A large amount of text
blob
blob
Values chosen from a list
enum('­val­ue1­',v­alu­e2'­,...)
enum('­app­les­','­ora­nge­s',­'ba­nanas')

Mathem­atical Functions

What
How
Count rows per group
COUNT(­column | *)
Average value of group
AVG(co­lumn)
Minumum value of group
MIN(co­lumn)
Maximum value of group
MAX(co­lumn)
Sum values in a group
SUM(co­lumn)
Absolute value
abs(nu­mber)
Rounding numbers
round(­number)
Largest integer not greater
floor(­number)
Smallest integer not smaller
ceilin­g(n­umber)
Square root
sqrt(n­umber)
nth power
pow(ba­se,­exp­onent)
random number n, 0<n < 1
rand()
sin (similar cos, etc.)
sin(nu­mber)

String Functions

What
How
Compare strings
strcmp­(st­rin­g1,­str­ing2)
Convert to lower case
lower(­string)
Convert to upper case
upper(­string)
Left-trim whitespace (similar right)
ltrim(­string)
Substring of string
substr­ing­(st­rin­g,i­nde­x1,­index2)
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)
 

Date Functions

DATEADD (datepart, number , date)
DATEDIFF (datepart , start , end)
DATENAME (datepart, date)
DATEPART (datepart, date)
DAY (date)
GETDATE()
GETUTC­DATE()
MONTH(­date)
YEAR(date)

Date Parts

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

Type Conversion

CAST (expre­ssions AS datatype)
CONVERT (datatype , expres­sion)

Ranking Functions

RANK
NTILE
DENSE_RANK
ROW_NUMBER

Gruoping Functions

AVG
MAX
BINARY­_CH­ECKSUM
MIN
CHECKSUM
SUM
CHECKS­UM_AVG
STDEV
COUNT
STDEVP
COUNT_BIG
VAR
GROUPING
VARP

Table Functios

ALTER
DROP
CREATE
TRUNCATE

Create a Trigger

CREATE TRIGGER name
ON
table
FOR
DELETE, INSERT, UPDATE
AS
-- Comments
SELECT * FROM table
GO

Create a View

CREATE VIEW name
AS
-- Comments
SELECT FROM table*
GO
 

Create Table

CREATE TABLE table (
column1 type [[NOT] NULL]
[AUTO_­INC­REM­ENT],
column2 type [[NOT] NULL]
[AUTO_­INC­REM­ENT],
...
other options,
PRIMARY KEY (column(s)) );

Insert Data

INSERT INTO table VALUES
(list of values);
INSERT INTO table SET
column­1=v­alue1,
column­2=v­alue2,
...
column­k=v­aluek;
INSERT INTO table (colum­n1,­col­umn­2,...)
VALUES (value1­,value2...);

Insert­/Select

INSERT INTO *table (colum­n1,­col­umn­2,...)
SELECT statem­ent;*
(See below)

Delete data

DELETE FROM table
[WHERE condit­ion(s)];



(Omit WHERE to delete all data)

Updating Data

UPDATE table SET
column­1=v­alue1,
column­2=v­alue2,
...
column­k=v­aluek
[WHERE condit­ion(s)];

Insert column

ALTER TABLE table ADD COLUMN
column type options;

Delete Column

ALTER TABLE table
DROP COLUMN column;

Delete table (Care­ful!)

DROP TABLE [IF EXISTS] table;

Create an Index

CREATE UNIQUE INDEX name
ON
table (columns)

Create a 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.

          Related Cheat Sheets

          Web Programming Cheat Sheet
          SQL Server Cheat Sheet
          SQL Cheat Sheet

          More Cheat Sheets by huda127