In this article, we will be diving into SQL and MySQL essentials with the help of Cheat Sheets. But before that, let us learn about SQL and MySQL in brief.

SQL

SQL (Structured Query Language) is a computer language, which is used to store, retrieve, and manipulate data from relational databases.

The favored language for Relational Database Systems is SQL. It is the common database language used by all Relational Database Management Systems (RDMS), including SQL Server, Oracle,  Postgres, MS Access,  Informix, MySQL, and Sybase.

SQL is well-liked because it provides the following benefits:

  • It enables the creation of views, stored procedures, and functions in databases.
  • It enables users to define and modify the data in a database.
  • It enables access to data stored in relational database management systems.
  • It gives users the ability to control access to views, processes, and tables.
  • It enables the use of pre-compilers, SQL modules, and libraries to embed into other languages.
  • It enables the creation and deletion of databases and tables by users.
  • It allows users to provide data descriptions.

Now that we have explored SQL, we will now discuss MySQL in brief.

MySQL

MySQL is an open-source, well-known relational database that we may use to create many types of web databases, from straightforward ones that catalog essential data like book suggestions to more intricate data warehouses that contain millions of records. Those who are already proficient in PHP or Perl would benefit much from learning MySQL. In this situation, we can develop websites that communicate in real-time with a MySQL database and show consumers searchable and categorized records.

MySQL is popular due to the following reasons:

  • PHP, the most favored language for web development, and MySQL get along well.
  • PHP, PERL, C, C++, JAVA, and other languages are only a few of the many operating systems and languages that MySQL supports.
  • By itself, MySQL is a pretty potent program. It manages a sizable portion of the most pricey and potent database products' features.
  • Up to 50 million rows or more can be found in a table when using MySQL for huge databases.
  • A standardized version of the well-known SQL data language is used by MySQL.
  • An open-source license governs the distribution of MySQL. Therefore, there is no cost for you to use it.
  • Even with massive data volumes, MySQL operates quickly and effectively.

Now that we have explored SQL and MySQL in brief, we will now explore the many symbols and shortcuts related to MySQL in the form of a thorough cheat sheet.

MySQL Cheat Sheet

Comments

There are two types of comments in MySQL -

  • Single-Line Comment
  • Multi-Line Comment

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;

Connect to MySQL

We must establish an active SSH session on your server in order to use MySQL. We can omit the -p switch if we haven’t provided our MySQL root user with a password.

mysql -u root -p

Creating a New MySQL Account

The next step is to make a fresh test user for practice. Run the following command to accomplish that:

CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’;

Deleting a User From MySQL

Use this command to remove a user later if you need to:

DROP USER ‘username’@’localhost’;

Creating a MySQL Database

Use this line to create a new database:

CREATE DATABASE databasename

Viewing a MySQL Database

The following command will allow you to view all of your databases:

mysql> show databases;

Navigating to a MySQL Database

Later, you can use the following command to rapidly go to a certain database:

[root@server ~]# mysql -u root -p databasename < radius.sql

Deleting a MySQL Database

Use the following command to remove a database:

DROP DATABASE databasename

Data Types in MySQL

The sorts of data that can be stored in a given table column are indicated by the data types.

There are three main sorts of data in MySQL:

  • Date/Time
  • Numeric
  • Text

Date and Time Data Types

As the name suggests, you can store time data in several formats with this data type.

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.

Numeric Data Types

The MySQL column display width will not, unless otherwise specified, restrict the range of values we can store there. Additionally, if we include too broad of values in our columns without a numeric data type integer, our columns may show width improperly. The following integers can be used to specify the maximum permitted range of values in order to avoid that.

The following integers can be used to specify the maximum permitted range of values in order to avoid that. We can either leave the column's value unsigned or assign it a precise numerical value.

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)

Blob and Text Data Types

You can store bigger volumes of text data using the BLOB binary range. A BLOB can be as long as 65,535 bytes (216 1). The prefix for BLOB values is a 2-byte long string.

NB: Since text data might grow lengthy, make sure you never go over the allowed limits. In most cases, the system will issue a warning if you exceed the limit. However, if non-space characters are truncated, you might only get a warningless error.

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.

Text Storage Formats

There are several text storage formats and we will go through the essential ones.

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.

MySQL Commands

Below are some important MySQL commands with their purpose, syntax, and examples:

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];

Logical Operators in MySQL

You can add more than one criterion to the WHERE clause by using logical operators. They become incredibly useful for more complex search, update, insert, and delete queries as a result.

Below are some logical operators and their purposes in MySQL:

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.

Arithmetic Operators in MySQL

On column data, arithmetic operations including addition, subtraction, multiplication, division, and modulus are carried out using the MySQL Arithmetic Operators. The list of available MySQL Arithmetic operators is displayed in the table below.

Operator

Purpose

/

Divide

*

Multiply

-

Subtract

%

Modulo

+

Add

Comparison Operators

The WHERE clause uses comparison operators to choose which records to choose. The comparison operators available in MySQL are listed below.

Operator

Purpose

<>

Not equal to

>=

Greater than or equal to

>

Greater than

=

Equal to

<=

Less than or equal to

<

Less than

Bitwise Operators

The binary digits of two numbers are compared using bitwise operations. In the table below, we define the basic three bitwise operators.

Operator

Purpose

^

Exclusive bitwise OR 

&

Bitwise AND

|

Bitwise OR

Compound Operators

Compound operators carry out an operation and assign an initial value to the outcome. In the table below, we explore the popular compound operators used in MySQL.

Operator

Purpose

%=

Modulo Equals

*=

Multiply equals

^-=

Bitwise exclusive equals

-=

Subtract equals

&=

Bitwise AND equals

/=

Divide equals

|*=

Bitwise OR equals

+=

Add equals

Kick-start your career growth story with our Professional Certificate Program in Business Analysis. Get a chance to master Excel, Tableau, and Python tools. Start learning now!

Want to Learn More?

This SQL and MySQL Cheat Sheet contains all the essential elements and shortcuts related to these technologies. To learn the concepts of SQL in a detailed way and improve your prospects, do check out Simplilearn’s SQL Certification Training Course course.

Our Software Development Courses Duration And Fees

Software Development Course typically range from a few weeks to several months, with fees varying based on program and institution.

Program NameDurationFees
Caltech Coding Bootcamp

Cohort Starts: 15 Apr, 2024

6 Months$ 8,000
Full Stack Java Developer

Cohort Starts: 2 Apr, 2024

6 Months$ 1,449
Automation Test Engineer

Cohort Starts: 3 Apr, 2024

11 Months$ 1,499
Full Stack Developer - MERN Stack

Cohort Starts: 3 Apr, 2024

6 Months$ 1,449

Get Free Certifications with free video courses

  • Business Analysis Basics

    Business and Leadership

    Business Analysis Basics

    2 hours4.660.5K learners
  • Business Intelligence Fundamentals

    Data Science & Business Analytics

    Business Intelligence Fundamentals

    10 hours4.630K learners
prevNext

Learn from Industry Experts with free Masterclasses

  • Break into a Rewarding Full Stack Developer Career with Mern Stack

    Software Development

    Break into a Rewarding Full Stack Developer Career with Mern Stack

    2nd Apr, Tuesday9:00 PM IST
  • Java vs JavaScript: The Right Learning Path for You in 2024

    Software Development

    Java vs JavaScript: The Right Learning Path for You in 2024

    19th Mar, Tuesday9:00 PM IST
  • Fuel Your 2024 FSD Career Success with Simplilearn's Masters program

    Software Development

    Fuel Your 2024 FSD Career Success with Simplilearn's Masters program

    21st Feb, Wednesday9:00 PM IST
prevNext