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.