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 |
|
Created By
Metadata
Comments
No comments yet. Add yours below!
Add a Comment