SQL Server Cheat Sheet

A Programming › SQL cheat sheet in English by huda127

MABD Cheat Sheet

SQL

26,446 views 5,642 downloads 24 Feb 2015 History

PRs

4.3 avg · 1 rating

Coloumn Type

Purpose	Data Type	Example
Integers	int(M)	int(5)
Floating-point (real) numbers	float(M,D)	float(12,3)
Double-precision floating-point	double(M,D)	double(20,3)
Dates and times	timestamp(M)	timestamp(8) (for YYYYMMDD)
Fixed-length strings	char(M)	char(10)
Variable-length strings	varchar(M)	varchar(20)
A large amount of text	blob	blob
Values chosen from a list	enum('value1',value2',...)	enum('apples','oranges','bananas')

Mathematical Functions

What	How
Count rows per group	COUNT(column \| *)
Average value of group	AVG(column)
Minumum value of group	MIN(column)
Maximum value of group	MAX(column)
Sum values in a group	SUM(column)
Absolute value	abs(number)
Rounding numbers	round(number)
Largest integer not greater	floor(number)
Smallest integer not smaller	ceiling(number)
Square root	sqrt(number)
nth power	pow(base,exponent)
random number n, 0<n < 1	rand()
sin (similar cos, etc.)	sin(number)

String Functions

What	How
Compare strings	strcmp(string1,string2)
Convert to lower case	lower(string)
Convert to upper case	upper(string)
Left-trim whitespace (similar right)	ltrim(string)
Substring of string	substring(string,index1,index2)
Encrypt password	password(string)
Encode string	encode(string,key)
Decode string	decode(string,key)
Get date	curdate()
Get time	curtime()
Extract day name from date string	dayname(string)
Extract day number from date string	dayofweek(string)
Extract month from date string	monthname(string)

Date Functions

DATEADD (datepart, number , date)

DATEDIFF (datepart , start , end)

DATENAME (datepart, date)

DATEPART (datepart, date)

DAY (date)

GETDATE()

GETUTCDATE()

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 (expressions AS datatype)

CONVERT (datatype , expression)

Ranking Functions

RANK	NTILE
DENSE_RANK	ROW_NUMBER

Gruoping Functions

AVG	MAX
BINARY_CHECKSUM	MIN
CHECKSUM	SUM
CHECKSUM_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_INCREMENT],
column2 type [[NOT] NULL]
[AUTO_INCREMENT],
...
other options,
PRIMARY KEY (column(s)) );

Insert Data

INSERT INTO table VALUES
(list of values);
INSERT INTO table SET
column1=value1,
column2=value2,
...
columnk=valuek;
INSERT INTO table (column1,column2,...)
VALUES (value1,value2...);

Insert/Select

INSERT INTO table (column1,column2,...)
SELECT statement;
(See below)

Delete data

DELETE FROM table
[WHERE condition(s)];

(Omit WHERE to delete all data)

Updating Data

UPDATE table SET
column1=value1,
column2=value2,
...
columnk=valuek
[WHERE condition(s)];

Insert column

ALTER TABLE table ADD COLUMN
column type options;

Delete Column

ALTER TABLE table
DROP COLUMN column;

Delete table (Careful!)

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

Badges

Good Cheat Sheet Nice Cheat Sheet Downloaded from Unusual Locations × 2

Comments

No comments yet.