5th Jun 2023 11 minutes read MySQL Cheat Sheet LearnSQL.com Team cheat sheet mysql Welcome to the MySQL cheat sheet! Whether you're just starting out with MySQL or you're a seasoned professional looking for a quick reference, this guide is tailor-made for you. This MySQL Cheat Sheet provides a concise and handy reference to the most commonly used MySQL commands and functionalities. It spans a range of topics, from connecting to a MySQL server and managing database contents, to the basic syntax for table creation and modification. It also offers references for the syntax of SELECT, INSERT, UPDATE, DELETE statements and details on working with different MySQL functions, including text functions, numeric functions, NULL functions, and date and time functions. Feel free to bookmark this page or share it with your peers. Dive in and make the most of this MySQL cheat sheet! Download Options: Download in PDF (A4 Format) Download in PDF (Letter Format) Download in PDF (A3 Format) Download in PDF (Ledger Format) Download in Mobile-friendly PDF You can also download the cheat sheet in high-resolution PNG format, perfect for quick online reference or sharing on social platforms. To download, right-click (for desktop users) or long tap (for mobile users) on the image. Table of Contents Connecting to a MySQL Server Creating and Displaying Databases Creating Tables Modifying Tables Querying Data Aggregation and Grouping GROUP BY Inserting Data Updating Data Deleting Data Casting Text Functions Filtering the Output Text Operators Concatenation Other Useful Text Functions Numeric Functions Useful NULL Functions COALESCE NULLIF Date and Time Intervals What Time is it? Creating Values Extracting Parts of Dates Date Arithmetic MySQL Cheat Sheet MySQL is a popular open-source relational database management system known for its ease of use and scalability. Sometimes, you will need a little help while working on a project. That's why we created this MySQL Cheat Sheet. Instructions for installing MySQL are available at: https://dev.mysql.com The complete learning path for MySQL. Try out our SQL from A to Z in MySQL track. 7 hands-on T-SQL courses with over 800 exercises! CONNECTING TO A MYSQL SERVER Connect to a MySQL server with a username and a password using the mysql command-line client. MySQL will prompt for the password: mysql -u [username] -p To connect to a specific database on a MySQL server using a username and a password: mysql -u [username] -p [database] To export data using the mysqldump tool: mysqldump -u [username] -p [database] > data_backup.sql To exit the client: quit or exit For a full list of commands: help CREATING AND DISPLAYING DATABASES To create a database: CREATE DATABASE zoo; To list all the databases on the server: SHOW DATABASES; To use a specified database: USE zoo; To delete a specified database: DROP DATABASE zoo; To list all tables in the database: SHOW TABLES; To get information about a specified table: DESCRIBE animal; It outputs column names, data types, default values, and more about the table. CREATING TABLES To create a table: CREATE TABLE habitat ( id INT, name VARCHAR(64)); Use AUTO_INCREMENT to increment the ID automatically with each new record. An AUTO_INCREMENT column must be defined as a primary or unique key: CREATE TABLE habitat ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64)); To create a table with a foreign key: CREATE TABLE animal ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64), species VARCHAR(64), age INT, habitat_id INT, FOREIGN KEY (habitat_id) REFERENCES habitat(id)); MODIFYING TABLES Use the ALTER TABLE statement to modify the table structure. To change a table name: ALTER TABLE animal RENAME pet; To add a column to the table: ALTER TABLE animalADD COLUMN name VARCHAR(64); To change a column name: ALTER TABLE animalRENAME COLUMN id TO identifier; To change a column data type: ALTER TABLE animalMODIFY COLUMN name VARCHAR(128); To delete a column: ALTER TABLE animalDROP COLUMN name; To delete a table: DROP TABLE animal; QUERYING DATA To select data from a table, use the SELECT command. Learn to query data in MySQL by actually writing code. Complete 130 interactive exercises in our SQL Basics in MySQL course and gain confidence in your coding skills. An example of a single-table query: SELECT species, AVG(age) AS average_ageFROM animalWHERE id != 3GROUP BY speciesHAVING AVG(age) > 3ORDER BY AVG(age) DESC; An example of a multiple-table query: SELECT city.name, country.nameFROM city[INNER | LEFT | RIGHT] JOIN country ON city.country_id = country.id; Use +, -, *, / to do some basic math. To get the number of seconds in a week: SELECT 60 * 60 * 24 * 7; -- result: 604800 AGGREGATION AND GROUPING AVG(expr) − average value of expr for the group. COUNT(expr) − count of expr values within the group. MAX(expr) − maximum value of expr values within the group. MIN(expr) − minimum value of expr values within the group. SUM(expr) − sum of expr values within the group. To count the rows in the table: SELECT COUNT(*)FROM animal; To count the non-NULL values in a column: SELECT COUNT(name)FROM animal; To count unique values in a column: SELECT COUNT(DISTINCT name)FROM animal; GROUP BY To count the animals by species: SELECT species, COUNT(id)FROM animalGROUP BY species; To get the average, minimum, and maximum ages by habitat: SELECT habitat_id, AVG(age), MIN(age), MAX(age) FROM animal GROUP BY habitat; INSERTING DATA To insert data into a table, use the INSERT command: INSERT INTO habitat VALUES (1, 'River'), (2, 'Forest'); You may specify the columns in which the data is added. The remaining columns are filled with default values or NULLs. INSERT INTO habitat (name) VALUES ('Savanna'); UPDATING DATA To update the data in a table, use the UPDATE command: UPDATE animalSET species = 'Duck', name = 'Quack'WHERE id = 2; DELETING DATA To delete data from a table, use the DELETE command: DELETE FROM animalWHERE id = 1; This deletes all rows satisfying the WHERE condition. To delete all data from a table, use the TRUNCATE TABLE statement: TRUNCATE TABLE animal; CASTING From time to time, you need to change the type of a value. Use the CAST() function to do this. In MySQL, you can cast to these data types: CHAR NCHAR BINARY DATE DATETIME DECIMAL DOUBLE FLOAT REAL SIGNED UNSIGNED TIME YEAR JSON spatial_type To get a number as a signed integer: SELECT CAST(1234.567 AS signed);-- result: 1235 To change a column type to double: SELECT CAST(column AS double); TEXT FUNCTIONS FILTERING THE OUTPUT To fetch the city names that are not Berlin: SELECT nameFROM cityWHERE name != 'Berlin'; TEXT OPERATORS To fetch the city names that start with a 'P' or end with an 's': SELECT nameFROM cityWHERE name LIKE 'P%' OR name LIKE '%s'; To fetch the city names that start with any letter followed by 'ublin' (like Dublin in Ireland or Lublin in Poland): SELECT nameFROM cityWHERE name LIKE '_ublin'; CONCATENATION Use the CONCAT() function to concatenate two strings: SELECT CONCAT('Hi ', 'there!');-- result: Hi there! If any of the string is NULL, the result is NULL: SELECT CONCAT(Great ', 'day', NULL);-- result: NULL MySQL allows specifying a separating character (separator) using the CONCAT_WS() function. The separator is placed between the concatenated values: SELECT CONCAT_WS(' ', 1, 'Olivier', 'Norris'); -- result: 1 Olivier Norris OTHER USEFUL TEXT FUNCTIONS To get the count of characters in a string: SELECT LENGTH('LearnSQL.com');-- result: 12 To convert all letters to lowercase: SELECT LOWER('LEARNSQL.COM');-- result: learnsql.com To convert all letters to uppercase: SELECT UPPER('LearnSQL.com');-- result: LEARNSQL.COM To get just a part of a string: SELECT SUBSTRING('LearnSQL.com', 9);-- result: .com SELECT SUBSTRING('LearnSQL.com', 1, 5);-- result: Learn To replace a part of a string: SELECT REPLACE('LearnSQL.com', 'SQL', 'Python');-- result: LearnPython.com The best place to master MySQL functions is our Common MySQL Functions interactive course. NUMERIC FUNCTIONS To get the remainder of a division: SELECT MOD(13, 2); -- result: 1 To round a number to its nearest integer: SELECT ROUND(1234.56789); -- result: 1235 To round a number to three decimal places: SELECT ROUND(1234.56789, 3); -- result: 1234.568 To round a number up: SELECT CEIL(13.1); -- result: 14 SELECT CEIL(-13.9); -- result: -13 The CEIL(x) function returns the smallest integer not less than x. To round the number down: SELECT FLOOR(13.8); -- result: 13 SELECT FLOOR(-13.2); -- result: -14 The FLOOR(x) function returns the greatest integer not greater than x. To round towards 0 irrespective of the sign of a number: SELECT TRUNCATE(13.56, 0); -- result: 13 SELECT TRUNCATE(-13.56, 1); -- result: -13.5 To get the absolute value of a number: SELECT ABS(-12); -- result: 12 To get the square root of a number: SELECT SQRT(9); -- result: 3 USEFUL NULL FUNCTIONS To fetch the names of the cities whose rating values are not missing: SELECT nameFROM cityWHERE rating IS NOT NULL; COALESCE(x, y, ...) To replace NULL in a query with something meaningful: SELECT domain, COALESCE(domain, 'domain missing') FROM contacts; domain coalesce LearnSQL.com LearnSQL.com NULL domain missing The COALESCE() function takes any number of arguments and returns the value of the first argument that is not NULL. NULLIF(x, y) To save yourself from division by 0 errors: SELECT last_month, this_month, this_month * 100.0 / NULLIF(last_month, 0) AS better_by_percent FROM video_views; last_monththis_monthbetter_by_percent 7237861085679150.0 0 178123 NULL The NULLIF(x, y) function returns NULL if x equals y, else it returns the value of x value. DATE AND TIME There are 5 main time-related types in MySQL: DATE, TIME, DATETIME, TIMESTAMP, YEAR. DATE – stores the year, month, and day in the YYYY-MM-DD format. TIME – stores the hours, minutes, and seconds in the HH:MM:SS format. DATETIME – stores the date and time in the YYYY-MM-DD HH:MM:SS format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. TIMESTAMP – stores the date and time. The range is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. YEAR – stores the year in the YYYY format. INTERVALS An interval is the duration between two points in time. To define an interval: INTERVAL 1 DAY This syntax consists of the INTERVAL keyword, a value, and a time part keyword (YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND). You may combine different INTERVALs using the + or - operator: INTERVAL 1 YEAR + INTERVAL 3 MONTH You may also use the standard SQL syntax: INTERVAL '1-3' YEAR_MONTH-- 1 year and 3 months INTERVAL '3-12' HOUR_MINUTE-- 3 hours 12 minutes The best place to master MySQL functions is our Common MySQL Functions interactive course. WHAT TIME IS IT? To answer this question, use: CURRENT_TIME or CURTIME – to get the current time. CURRENT_DATE or CURDATE – to get the current date. NOW() or CURRENT_TIMESTAMP – to get the current timestamp with both of the above. CREATING VALUES To create a date, time, or datetime, write the value as a string and cast it to the proper type. SELECT CAST('2021-12-31' AS date), CAST('15:31' AS time), CAST('2021-12-31 23:59:29' AS datetime); You may skip casting in simple conditions; the database knows what you mean. SELECT airline, flight_no, departure_timeFROM airport_scheduleWHERE departure_time < '12:00'; EXTRACTING PARTS OF DATES To extract a part of a date, use the functions YEAR, MONTH, WEEK, DAY, HOUR, and so on. SELECT YEAR(CAST('2021-12-31' AS date));-- result: 2021 SELECT MONTH(CAST('2021-12-31' AS date));-- result: 12 SELECT DAY(CAST('2021-12-31' AS date));-- result: 31 DATE ARITHMETICS To add or subtract an interval from a DATE, use the ADDDATE() function: ADDDATE('2021-10-31', INTERVAL 2 MONTH);-- result: '2021-12-31' ADDDATE('2014-04-05', INTERVAL -3 DAY);-- result: '2014-04-02' To add or subtract an interval from a TIMESTAMP or DATETIME, use the TIMESTAMPADD() function: TIMESTAMPADD(MONTH, 2,'2014-06-10 07:55:00');-- result: '2014-08-10 07:55:00' TIMESTAMPADD(MONTH, -2, '2014-06-10 07:55:00');-- result: '2014-04-10 07:55:00' To add or subtract TIME from a DATETIME, use the ADDTIME() function: ADDTIME('2018-02-12 10:20:24', '12:43:02');-- result: '2018-02-12 23:03:26' ADDTIME('2018-02-12 10:20:24', '-12:43:02');-- result: '2018-02-11 21:37:22' To find the difference between two dates, use the DATEDIFF() function: DATEDIFF('2015-01-01', '2014-01-02');-- result: 364 To find the difference between two times, use the TIMEDIFF() function: SELECT TIMEDIFF('09:30:00', '07:55:00');-- result: '01:35:00' To find the difference between two datetimes (in a given unit of time), use the TIMESTAMPDIFF() function. Here's an example with the difference given in weeks: SELECT TIMESTAMPDIFF(WEEK, '2018-02-26', '2018-03-21'); -- result: 3 Try out our SQL from A to Z in MySQL track. 7 hands-on SQL courses with over 800 exercises! Tags: cheat sheet mysql