CHAR(n) - all characters should be n size. VARCHAR(n) - all characters can less than n size. TEXT - unlimited size. INT - integer. BIGINT - big integer. FLOAT - float. DOUBLE - double. DECIMAL - decimal. DATE - date. DATETIME - date time. TIMESTAMP - timestamp. TIME - time. YEAR - year. BOOL - boolean.
NOT NULL - column can not be null. UNSIGNED - column is unsigned. AUTO_INCREMENT - column is auto increment. PRIMARY KEY - column is primary key. UNIQUE - column is unique. DEFAULT - column has default value.
Note: here < > and [ ] are used for formatting, you should ignore them when you write your query.
CREATE DATABASE <database_name>;
DROP DATABASE <database_name>;
SHOW DATABASES;
SHOW TABLES;
CREATE TABLE <table_name> (
<column_name> <data_type> [NOT NULL] [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT <value>]
,
<column_name> <data_type> [NOT NULL] [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT <value>]
);
DROP TABLE <table_name>;
ALTER TABLE <table_name> RENAME TO <new_table_name>;
ALTER TABLE <table_name> ADD <column_name> <data_type> [NOT NULL] [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT <value>];
This is used when you want to add new column after or before existing column.
ALTER TABLE <table_name> ADD <column_name> <data_type> [NOT NULL] [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT <value>] AFTER <column_name>;
ALTER TABLE <table_name> ADD <column_name> <data_type> [NOT NULL] [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT <value>] BEFORE <column_name>;
ALTER TABLE <table_name> CHANGE <column_name> <new_column_name> <data_type> [NOT NULL] [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT <value>];
ALTER TABLE <table_name> MODIFY <column_name> <data_type> [NOT NULL] [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT <value>];
ALTER TABLE <table_name> DROP <column_name>;
INSERT INTO <table_name> ( <column_name>, <column_name>, <column_name> ) VALUES ( <value>, <value>, <value> );
INSERT INTO <table_name> ( <column_name>, <column_name>, <column_name> ) VALUES ( <value>, <value>, <value> ), ( <value>, <value>, <value> );
INSERT INTO <table_name> ( <column_name>, <column_name>, <column_name> ) VALUES ( <value>, <value>, <value> ), ( <value>, <value>, <value> ), ( <value>, <value>, <value> );
INSERT INTO <table_name> VALUES ( <value>, <value>, <value> );
INSERT INTO <table_name> VALUES ( <value>, <value>, <value> ), ( <value>, <value>, <value> ), ( <value>, <value>, <value> );
String Methods
Function | Use Case |
---|---|
Lower(str) | converts the string to lowercase. |
UPPER(str) | convert the string to uppercase. |
REVERSE(str) | reverse the string. |
LENGTH | gives the length of string. |
TRIM(str) | trim the string. |
LTRIM(str) | trim the string from left. |
RTRIM(str) | trim the string from right. |
REPLACE(str,old,new) | replace the old string with new string. |
SUBSTRING(str,start,length) | gives the substring from start to length. |
LOCATE(str,substr) | gives the position of substring. |
INSTR(str,substr) | gives the position of substring. |
LEFT(str, n) | gives the left n characters of string. |
RIGHT(str, n) | gives the right n characters of string. |
MID(str, start, length) | gives the substring from start to length. |
CONCAT(str1,str2) | concatenates two strings. |
Date and Time Methods
Function | Use Case |
---|---|
CURDATE() | gives the current date. |
CURTIME() | gives the current time. |
NOW() | gives the current date and time. |
DAY() | gives the day of the month. |
MONTH() | gives the month. |
YEAR() | gives the year. |
HOUR() | gives the hour. |
MINUTE() | gives the minute. |
SECOND() | gives the second. |
DATE(date) | gives the date. |
TIME(time) | gives the time. |
DATETIME(datetime) | gives the date and time. |
FROM_DAYS(days) | gives the date from days. |
FROM_UNIXTIME(unixtime) | gives the date and time from unixtime. |
DAYNAME(date) | gives the day name. |
|MONTHNAME(date)|gives the month name.|
Math Methods
Function | Use Case |
---|---|
ABS(num) | gives the absolute value of number. |
CEIL(num) | gives the ceiling of number. |
FLOOR(num) | gives the floor of number. |
ROUND(num) | gives the round of number. |
ROUND(num,decimal) | gives the round of number with decimal. |
RAND() | gives the random number. |
SQRT(num) | gives the square root of number. |
POW(num,power) | gives the power of number. |
MOD(num,mod) | gives the mod of number. |
Operators
Function | Use Case |
---|---|
!= | not equalto. |
> | greater than. |
>= | greater than equal to. |
< | less than. |
<= | less than equal to. |
= | equal to. |
LIKE | for pattern matching |
BETWEEN | from low to high |
IN | for in operator |
LIKE OPERATOR
Function | Use Case |
---|---|
LIKE | for pattern matching |
%s% | does it contains the "s". |
%s | does it ends with the "s". |
s% | does it starts with the "s". |
s | does it equals the "s". |
_s | does the first character of the string is "s". |
s_ | does the last character of the string is "s". |
_ _ _ _ | it should contain 4 words |
_ _ _ s _ | 4th character should be "s" |
SELECT * FROM <table_name>;
SELECT <column_name>, <column_name>, <column_name> FROM <table_name>;
SELECT * FROM <table_name> WHERE <column_name> = <value>;
SELECT * FROM <table_name> WHERE <column_name> = <value> OR <column_name> = <value>;
SELECT <column_name>, <column_name_2>+ <column_name_3> "<New Column>" FROM <table_name>;
for sorting data.
SELECT * FROM <table_name> ORDER BY <column_name>;
SELECT * FROM <table_name> ORDER BY <column_name>, <column_name>;
SELECT * FROM <table_name> ORDER BY <column_name> DESC;
limint on showing number of rows.
SELECT * FROM <table_name> LIMIT <number>;
SELECT * FROM <table_name> LIMIT <number>, <number>;
getting unique values from column.
SELECT DISTINCT <column_name> FROM <table_name>;
Group by clause is nothing by grouping rows whoes values are same with respect to same column where.