MySQL Cheat Sheet

MySQL is a popular open-source relational database

management system known for its ease of use and scalability. CREATING TABLES QUERYING DATA INSERTING DATA
Sometimes, you will need a little help while working on a project. To create a table: To select data from a table, use the SELECT command. To insert data into a table, use the INSERT command:
That's why we created this MySQL Cheat Sheet. CREATE TABLE habitat ( An example of a single-table query: INSERT INTO habitat VALUES
id INT, SELECT species, AVG(age) AS average_age (1, 'River'),
Instructions for installing MySQL are available at: name VARCHAR(64) FROM animal (2, 'Forest');
https://dev.mysql.com ); WHERE id != 3
GROUP BY species
Use AUTO_INCREMENT to increment the ID automatically with HAVING AVG(age) > 3 You may specify the columns in which the data is added. The
CONNECTING TO A MYSQL SERVER each new record. An AUTO_INCREMENT column must be defined ORDER BY AVG(age) DESC; remaining columns are filled with default values or NULLs.
Connect to a MySQL server with a username and a password as a primary or unique key: INSERT INTO habitat (name) VALUES
using the mysql command-line client. CREATE TABLE habitat ( An example of a multiple-table query: ('Savanna');
MySQL will prompt for the password: id INT PRIMARY KEY AUTO_INCREMENT, SELECT city.name, country.name
mysql -u [username] -p name VARCHAR(64) FROM city
[INNER | LEFT | RIGHT] JOIN country
);
ON city.country_id = country.id; UPDATING DATA
To connect to a specific database on a MySQL server using a To update the data in a table, use the UPDATE command:
username and a password: Use +, -, *, / to do some basic math. UPDATE animal
mysql -u [username] -p [database] To create a table with a foreign key:
CREATE TABLE animal ( To get the number of seconds in a week: SET
id INT PRIMARY KEY AUTO_INCREMENT, SELECT 60 * 60 * 24 * 7; -- result: 604800 species = 'Duck',
To export data using the mysqldump tool: name = 'Quack'
name VARCHAR(64),
mysqldump -u [username] -p \
[database] > data_backup.sql
species VARCHAR(64), AGGREGATION AND GROUPING WHERE id = 2;
age INT, AVG(expr) − average value of expr for the group.
habitat_id INT, COUNT(expr) − count of expr values within the group.
To exit the client:
quit or exit
FOREIGN KEY (habitat_id) MAX(expr) − maximum value of expr values within the DELETING DATA
REFERENCES habitat(id) group. To delete data from a table, use the DELETE command:
); MIN(expr) − minimum value of expr values within the
For a full list of commands: DELETE FROM animal
help group. WHERE id = 1;
MODIFYING TABLES SUM(expr) − sum of expr values within the group.

CREATING AND DISPLAYING Use the ALTER TABLE statement to modify the table structure. To count the rows in the table: This deletes all rows satisfying the WHERE condition.
SELECT COUNT(*) To delete all data from a table, use the TRUNCATE TABLE
DATABASES To change a table name:
ALTER TABLE animal RENAME pet;
FROM animal; statement:
To create a database: TRUNCATE TABLE animal;
To count the non-NULL values in a column:
CREATE DATABASE zoo;
To add a column to the table: SELECT COUNT(name)
ALTER TABLE animal FROM animal;
To list all the databases on the server:
SHOW DATABASES;
ADD COLUMN name VARCHAR(64); CASTING
To count unique values in a column: From time to time, you need to change the type of a value.
To change a column name: SELECT COUNT(DISTINCT name) Use the CAST() function to do this.
To use a specified database: ALTER TABLE animal FROM animal; In MySQL, you can cast to these data types:
USE zoo; RENAME COLUMN id TO identifier; CHAR NCHAR BINARY DATE DATETIME
GROUP BY DECIMAL DOUBLE FLOAT REAL SIGNED
To delete a specified database: To change a column data type: To count the animals by species: UNSIGNED TIME YEAR JSON spatial_type
DROP DATABASE zoo; ALTER TABLE animal SELECT species, COUNT(id)
MODIFY COLUMN name VARCHAR(128); FROM animal
To list all tables in the database: GROUP BY species; To get a number as a signed integer:
SHOW TABLES; To delete a column: SELECT CAST(1234.567 AS signed);
ALTER TABLE animal To get the average, minimum, and maximum ages by habitat: -- result: 1235
To get information about a specified table: DROP COLUMN name; SELECT habitat_id, AVG(age),
DESCRIBE animal; MIN(age), MAX(age)
It outputs column names, data types, default values, and more To delete a table: FROM animal To change a column type to double:
about the table. DROP TABLE animal; GROUP BY habitat_id; SELECT CAST(column AS double);

LearnSQL.com is owned by Vertabelo SA

Try out the interactive SQL from A to Z in MySQL course at LearnSQL.com, and check out our other SQL courses. vertabelo.com | CC BY-NC-ND Vertabelo SA