The following commands are organized by their CRUD functionality. This was designed to be a quick 'n easy cheat sheet.
Database: savetheworld
Table: superheros
| id | superhero | password | age | |
|---|---|---|---|---|
| 1 | batman | badpassword | batman@superhero.com | 40 |
| 2 | superman | terriblepassword | superman@superhero.com | 35 |
| 3 | spiderman | herendouspassword | spiderman@superhero.com | 20 |
| 4 | batmanfaker | nottherealbatman | batmanimpersonator@fakesuperheros.com | 18 |
Enter MySQL CLI
mysql
Local Login
mysql -u root -p -h 127.0.0.1
Help
help
Change context and enter into a particular database
USE savetheworld;
Create a database
CREATE DATABASE savetheworld;
Create a table
CREATE TABLE superheros(id int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
superhero varchar(25) NOT NULL,
password varchar(25) NOT NULL,
email varchar(40) NOT NULL);
Show all databases
SHOW DATABASES;
Show all tables
SHOW TABLES;
List schema for a specific table
DESCRIBE superheros;
List all values from specific table
SELECT * FROM superheros;
List all values from specific table and order by a column name
SELECT * FROM superheros ORDER BY superhero;
List all values that contain a specific word
SELECT * FROM superheros WHERE email RLIKE('batman');
Returns the number of rows in a table
SELECT COUNT(*) FROM superheros;
List distinct values in a table without duplicates
SELECT DISTINCT superhero FROM superheros;
Display the length of a column's values as an alias
SELECT email LENGTH(email) AS 'Email Length' FROM superheros;
Update specific table column value
UPDATE superheros SET email = 'slightlybetterpassword' WHERE superhero = 'batman';
Delete a database
DROP DATABASE savetheworld;
Delete a table
DROP TABLE superheros;
Delete a value from database table column
DELETE FROM superheros WHERE superhero = 'superman';
Delete multiple values from database table columns
DELETE FROM superheros WHERE superhero IN('batman', 'spiderman');
Delete a column from a database table
ALTER TABLE superheros DROP COLUMN email;
Trim white space from a column's values
SELECT TRIM(*) FROM superheros;
Converts a column's values into uppercase
SELECT UPPER(email) FROM superheros;
Converts a column's values into lowercase
SELECT LOWER(email) FROM superheros;
Returns absolute value of a number
SELECT ABS(-12);
Rounds a column
SELECT ROUND(age) FROM superheros;
Sums a column
SELECT SUM(age) FROM superheros;