Coloumn TypePurpose | 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 FunctionsWhat | 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 FunctionsWhat | 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 FunctionsDATEADD (datepart, number , date) | DATEDIFF (datepart , start , end) | DATENAME (datepart, date) | DATEPART (datepart, date) | DAY (date) | GETDATE() | GETUTCDATE() | MONTH(date) | YEAR(date) |
Date PartsYear | 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 ConversionCAST (expressions AS datatype) | CONVERT (datatype , expression) |
Ranking FunctionsRANK | NTILE | DENSE_RANK | ROW_NUMBER |
Gruoping FunctionsAVG | MAX | BINARY_CHECKSUM | MIN | CHECKSUM | SUM | CHECKSUM_AVG | STDEV | COUNT | STDEVP | COUNT_BIG | VAR | GROUPING | VARP |
Create a TriggerCREATE TRIGGER name
ON
table
FOR
DELETE, INSERT, UPDATE
AS
-- Comments
SELECT * FROM table
GO |
Create a ViewCREATE VIEW name
AS
-- Comments
SELECT FROM table*
GO |
| | Create TableCREATE TABLE table (
column1 type [[NOT] NULL]
[AUTO_INCREMENT],
column2 type [[NOT] NULL]
[AUTO_INCREMENT],
...
other options,
PRIMARY KEY (column(s)) ); |
Insert DataINSERT 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/SelectINSERT INTO *table (column1,column2,...)
SELECT statement;*
(See below) |
Delete dataDELETE FROM table
[WHERE condition(s)];
(Omit WHERE to delete all data) |
Updating DataUPDATE table SET
column1=value1,
column2=value2,
...
columnk=valuek
[WHERE condition(s)]; |
Insert columnALTER TABLE table ADD COLUMN
column type options; |
Delete ColumnALTER TABLE table
DROP COLUMN column; |
Delete table (Careful!)DROP TABLE [IF EXISTS] table; |
Create an IndexCREATE UNIQUE INDEX name
ON
table (columns) |
Create a Stored ProcedureCREATE 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