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 |
|
Created By
Metadata
Favourited By
Comments
No comments yet. Add yours below!
Add a Comment
Related Cheat Sheets
More Cheat Sheets by huda127