garis133 SQL Cheat Sheet

A cheat sheet in English by garisnk

9,229 views 2,265 downloads 25 Feb 2015 History

PRs

3.7 avg · 0 ratings

Data Type

Integers	int(M)	int(2)
Floating-point (real) numbers	float(M,D)	float(3,2)
Double-precision floating-point	double(M,D)	double(17,2)
Date and time	timestamp(m)	timestamp(8)(for YYYYMMDD)
Fixed Length Strings	char(M)	char(10)
Variable length Strings	varchar(M)	varchar(20)
Large amount of text	blob	blob
Values chosen from a list	enum('value1',......,'value n')	enum('toyota','daihatsu','suzuki')

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

Mathematical Functions

Function	Code
Find minimum Value of Group	MIN(column)
Find maximum Value of Group	MAX(column)
Find average Value of Group	AVG(column)
Sum Values in a Group	SUM(column)
Count rows per Group	COUNT(column)
Rounding numbers	round(number)
Randomize numbers	rand()
Absolute Value	abs(number)
Largest integer not greater	floor(number)
Smallest integer not smaller	ceiling(number)
Square root	sqrt(number)
nth power	pow(base, exponent)
sin cos tan, etc	sin(number)

String Functions

Functions	Code
Compare Strings	strcamp(string1,string2)
Convert to Upper Case	upper(string)
Convert to Lower Case	lower(string)
Left-trim whitespace	ltrim(string)
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)

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

Delete data

DELETE FROM table
[WHERE condition(s)];

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

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

Badges

Good Cheat Sheet Nice Cheat Sheet

Comments

No comments yet.