SQL & MySQL Cheat Sheets | A Complete SQL & MySQL Glossary

Comment

Syntax

Example

Single-Line Comment

Commented line starts with “-”.

-Update all:

SELECT * FROM Colleges;

Multi-Line Comment

Commented lines start with “/*” and end with “*/”.

/*Select all the columns

of all the records

in the Colleges table:*/

SELECT * FROM Colleges;

Data Type

Description

TIME

Simply enter time values in the formats "hh:mm:ss" or "hhh:mm:ss." The latter can depict both passing time and intervals of time.

The range of supported data is from "-838:59:59" to "838:59:59".

DATE

Use DATE exclusively for values that include a date component. The format for DATE values in MySQL is 'YYYY-MMDD'.

The range of supported data is from "1000-01-01" to "9999-12-31."

DATETIME

Keep track of values that include both a date and an hour. The format for the display is "YYYYMM-DD hh:mm:ss."

Data from "1000-01-01 00:00:00" to "9999-12-31 23:59:59" are supported.

YEAR

Use this 1-byte type to store values for the year. In a 4-digit format, YEAR values are represented as 0000, with a range of 1901 to 2155.

In a 2-digit format, YEAR values are shown as 00. MySQL will convert YEAR values in the ranges of 2000 to 2069 and 1970 to 1999. The acceptable range is "0" to "99".

TIMESTAMP

Up to microseconds in UTC, increase the precision of record values that include both time and date components.

The data range supported is from 1970-01-01 00:00:01 to 2038-01-19 03:14:07.

Data Type

Description

FLOAT (M, D) 

Make a note of a rough total using a floating decimal point. As of MySQL 8.0.17 and later, FLOAT support is no longer available.

The ranges of acceptable values are 0, -3.402823466E+38, -1.175494351E-38, and 1.175494351E-38, E+38, E+38.

MEDIUMINT(M)

BIT[(M)]

Define the bit-value type. M is the number of bits per value, which can be anything between 1 and 64. If no T is supplied, the default value is 1.

SMALLINT(M) 

-32768 to 32767 is the range of this little integer.

The range of the SMALLINT(M) [UNSIGNED] is 0 to 65535.

INT(M) and INTEGER (M)

INT(M)[UNSIGNED] and INTEGER (M)[UNSIGNED] have ranges of 0 to 4294967295, respectively, and INT(M)[UNSIGNED] has a range of -2147483648 to 2147483647.

ZEROFILL 

Automatically adds the property UNSIGNED to the column. Deprecated since the release of MySQL 8.0.17.

DECIMAL (M, D)

Keep a double value in a string format. M indicates how many digits there are in total. D is the number of digits following the decimal point. useful for keeping track of money values.

M can have a maximum of 65. The default M value, if left out, is 10.

D can have a maximum of 30. The default D is 0.

BIGINT(M)

9223372036854775807 to 9223372036854775808 is the greatest integer. The range of a BIGINT(M) [UNSIGNED] is 0 to 8446744073709551615.

TINYINT(M)

The smallest number is between -128 and 127.

The range of TINYINT(M) [UNSIGNED] is 0 to 255.

TINYINT is also known as BOOL and BOOLEAN (1)

Datatype

Description

LONGBLOB

Sets the maximum column length to 4GB (232 1) bytes, or 4,294,967,295 columns.

Values for LONGBLOB are stored using a 4-byte prefix.

The maximum length will also be determined by the maximum packet size that you specify in the client/server protocol and by the amount of memory that is available.

MEDIUMBLOB

Limits the length of a column to 16,777,215 (224 1) bytes. Values for MEDIUMBLOB are saved using a 3-byte prefix.

TINYBLOB

255 (28 -1) bytes is the maximum column length that can be set. Values for TINYBLOB are saved using a 1-byte prefix.

Test Storage type

Description

VARBYNARY

Byte strings representing variable-length binary data are stored. comparable to VARCHAR.

SET

A different approach to storing multiple text values that were selected from a specified list of options.

A SET column can have up to 64 different members and more than 255 different element list definitions.

BYNARY

Store binary data as strings of bytes. comparable to CHAR

VARCHAR

Holds non-binary strings of varying lengths. The maximum character count is 65,535.

You can store 65,535 items (equal to the max row size).

• In contrast to CHAR, VARCHAR values are saved as a 1-byte or 2-byte length prefix plus data values.

ENUM

Store permitted text values that you enumerated in the column specification when creating a table.

A column's ENUM can have a maximum of 65,535 different element definitions and more than 255 different element list definitions.

CHAR

The maximum number of non-binary characters you can save. It has a range of 0 to 255.

Command

Purpose

Syntax

Example

SELECT

Select certain information from your database.

SELECT * FROM table_name;

SELECT * FROM students;

SELECT … WHERE…

Adding a selection criterion.

SELECT * FROM tablename WHERE condition;

SELECT * FROM fest WHERE budget=’3’;

SELECT * FROM fest WHERE year=’2022’ AND participants=’30000’;

INSERT INTO

Adds fresh information to a database

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

INSERT INTO Students(name, mobile, address, gender, department, academicyear)

VALUES ('Platoon S. Era', '+916723123416', 'Gurgaon', 'Male', 'Mechanical’, ‘third’);

UPDATE

To update the table.

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

UPDATE Students

SET academicyear = 'fourth', department= 'Computer Science'

WHERE StudentID = 100;

DELETE

Erases information from your database

DELETE FROM table_name WHERE condition;

DELETE FROM Students WHERE name='KanizaKatrina';

CREATE DATABASE

Make a new database

CREATE DATABASE databasename;

CREATE DATABASE College;

DROP DATABASE

To delete a database

DROP DATABASE databasename;

DROP DATABASE College;

CREATE TABLE

To make a database's new table.

CREATE TABLE table_name (

    column1 datatype,

    column2 datatype,

    column3 datatype,

   ....

);

CREATE TABLE colleges(

 name VARCHAR(100),

 nacrating VARCHAR(100),

 principal VARCHAR(50),

Numberofstudentscurrently enrolled VARCHAR(20),

);

ALTER TABLE

Alter the chosen table

ALTER TABLE table_name

ADD column_name datatype;

ALTER TABLE Clients

ADD Address varchar(255);

DROP TABLE

To eliminate a table

DROP TABLE table_name;

DROP TABLE Icecream;

CREATE INDEX

To create an index (search key for all the info stored)

CREATE INDEX index_name

ON table_name (column1, column2, ...);

CREATE INDEX index_1 ON Students;  

DROP INDEX

To delete an index

ALTER TABLE table_name

DROP INDEX index_name;

ALTER TABLE Students

DROP INDEX index_1;

ADD COLUMN_NAME

To add a column to a table

ALTER TABLE table

ADD [COLUMN] column_name;

ALTER TABLE Students

ADD Department varchar(100);

MODIFY COLUMN

To modify a given column in a table

ALTER TABLE tablename MODIFY COLUMN column_name datatype(number)

ALTER TABLE Students MODIFY COLUMN Department varchar(50)

DROP COLUMN

To delete a column from a table

ALTER TABLE table_name

DROP COLUMN column_name;

ALTER TABLE Students

DROP COLUMN Department;

DELETE

To delete a row from a table.

DELETE FROM tablename WHERE rowcondition;

DELETE FROM fest WHERE budget=’3’;

CREATE VIEW

To create a view in the database.

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

CREATE VIEW [CSE Students] AS

SELECT name, mobile

FROM Students

WHERE Department = 'Computer Science';

CREATE OR REPLACE VIEW

To update a view in the database.

CREATE OR REPLACE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

CREATE OR REPLACE VIEW [CSE Students] AS

SELECT name, mobile, address

FROM Students

WHERE Department = 'Computer Science';

RENAME TABLE

To rename a view in the database.

RENAME TABLE view_name TO new_view_name;

RENAME TABLE [CSE Students] TO CSEStudents;

SHOW FULL TABLES

To show all views in the database.

SHOW FULL TABLES

WHERE table_type = ‘VIEW’;

SHOW FULL TABLES IN Colleges

WHERE table_type = ‘VIEW’;

DROP VIEW

To delete a view in the database.

DROP VIEW view_name;

DROP VIEW [CSE Students];

Operator

Purpose

EXISTS

Test the presence of a specific record.

OR

Call records that satisfy any of the requirements, divided by OR.

BETWEEN

Pick or lookup information between a set of minimum and maximum values

IN

Ascertain whether a value or expression corresponds to one of the values in your list.

AND

Use it to filter data based on the 1+ criterion. You can then call records that meet all the criteria, separated by AND.

IS NULL

A value is being compared to a NULL value.

ANY

Using the given condition, compare a value or expression to any other value in your list.

LIKE

Compare two records side by side and is a handy search operator

ALL

A value or expression is compared to every other value in a list.

NOT

Review the records that don't fit a specific requirement (e.g. NOT blue). It is an efficient operator for omitting specific data.

Operator

Purpose

/

Divide

*

Multiply

-

Subtract

%

Modulo

+

Add

Operator

Purpose

<>

Not equal to

>=

Greater than or equal to

>

Greater than

=

Equal to

<=

Less than or equal to

<

Less than

Operator

Purpose

^

Exclusive bitwise OR 

&

Bitwise AND

|

Bitwise OR

Operator

Purpose

%=

Modulo Equals

*=

Multiply equals

^-=

Bitwise exclusive equals

-=

Subtract equals

&=

Bitwise AND equals

/=

Divide equals

|*=

Bitwise OR equals

+=

Add equals