MySQL Cheat Sheet

MySQL Cheat Sheet

Login/ Logout

# login
mysql -u root -p

# logout
exit;

Users and Privileges

# Create a new user
CREATE USER 'user'@'localhost';

# Grant All Priveleges On All Databases
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';

# Grant (SELECT, INSERT, DELETE) Priveleges On All Databases
GRANT SELECT, INSERT, DELETE ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';

# SHOW Grants
SHOW GRANTS FOR 'someuser'@'localhost';

# Remove All Privileges,one permission only
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host'; 

# Remove All Privileges, all permissions
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host';

# Update Privileges status
FLUSH PRIVILEGES;

# Delete User
DROP USER 'user'@'host';

Passwords

SET PASSWORD = PASSWORD('new_pass');
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass');
SET PASSWORD = OLD_PASSWORD('new_pass');

Browsing Database

# Show all databases
SHOW DATABASES;

# Create Database
CREATE DATABASE dbname;

# Select Database
USE dbname;

# Delete Database
DROP DATABASE dbname;

Browing Table

# Create table  -- Create a table that names students
CREATE TABLE students(
id INT AUTO_INCREMENT,
   first_name VARCHAR(40),
   last_name VARCHAR(40),
   email VARCHAR(40),
   location VARCHAR(100),
   nationality VARCHAR(50),
   register_date DATETIME,
   PRIMARY KEY(id)
);

CREATE TABLE table IF NOT EXISTS;

# Show all tables
SHOW TABLES;

# show fields of a table
SHOW FIELDS FROM tablename / DESCRIBE tablename;

# Delete table
DROP TABLE tablename;
DROP TABLE table1, table2,

Insert

# Insert one row
INSERT INTO users (first_name, last_name, email, location, nationality,  register_date) 
values ('Tom', 'Brandy', 'tom.brandy@codewithivy.com', 'Berlin', 'Germany', now());


# Insert multiple rows  (Insert 3 rows)
INSERT INTO users (first_name, last_name, email, location, nationality,  register_date) 
values ('Tom', 'Brandy', 'tom.brandy@codewithivy.com', 'Berlin', 'Germany', now()),
('Julian', 'Götz', 'julian.goetz@codewithivy.com', 'Bremen', 'Germany', now())
('Marius', 'Tiffy', 'marius.tiffy@codewithivy.com', 'Paris', 'France', now());

Select 

# Select everything from table
SELECT * FROM table;

# Select column1,column2, column3 from table
SELECT col1, col2, col3 FROM table;

# Select everything from two tables
SELECT * FROM table1, table2;

# Select district rows (no duplicate)
SELECT DISTINCT col1 FROM ...
SELECT DISTINCT col1, col2 FROM ...

Where Clause

SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUPBY field;
SELECT ... FROM ... WHERE condition GROUPBY field HAVING condition2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC;
SELECT ... FROM ... WHERE condition LIMIT 10;

Conditions

field1 = value1   # equals
field1 <> value1   # less than/ more than
field1 LIKE 'value _ %'  # wildcard
field1 IS NULL    # isnull
field1 IS NOT NULL   # not null 
field1 IS IN (value1, value2)   # in
field1 IS NOT IN (value1, value2)  # not in
condition1 AND condition2   # and, &
condition1 OR condition2    # or, |

Like — Wildcard

SELECT * FROM table WHERE col LIKE 'd%';  # start with d
SELECT * FROM table WHERE col LIKE 'dev%'; # start with dev
SELECT * FROM table WHERE col LIKE'%t';    # end with t
SELECT * FROM table WHERE col NOT LIKE'B%';    # Not start with B

# anything matchs M_s, e.g Mattias, Mats
SELECT * FROM table WHERE col LIKE 'M_s'; 
# specify a different escape character
SELECT * FROM table WHERE col LIKE '%$_20%' ESCAPE '$';   

Alter Table

# Change column
ALTER TABLE table MODIFY field1 type1
ALTER TABLE table MODIFY field1 type1 NOT NULL ...

# Change column name
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL ...

# Change default value for column
ALTER TABLE table ALTER field1 SET DEFAULT ...
ALTER TABLE table ALTER field1 DROP DEFAULT

# Add a new column
ALTER TABLE table ADD new_name_field1 type1
 ## FIRST | AFTER is to specify the position of the new column in the table
ALTER TABLE table ADD new_name_field1 type1 FIRST
ALTER TABLE table ADD new_name_field1 type1 AFTER another_field

# Delete a column
ALTER TABLE table DROP field1
ALTER TABLE table ADD INDEX (field);

Aggregate Functions

# the column needs to be a numerical column
SELECT COUNT(id) FROM tablename;
SELECT MAX(col) FROM tablename;
SELECT MIN(col) FROM tablename;
SELECT SUM(col) FROM tablename;
SELECT UCASE(first_name), LCASE(last_name) FROM students;

Join

# Default join
SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition;
# Left join
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition;
# Join multiple tables
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...

Leave a Reply