#            MySQL Cheatsheet

By [coinvest](https://paragraph.com/@coinvest) · 2023-09-19

---

Here’s a cheat-sheet for basic database management and manipulation in `MySQL`.

find where mysql is installed

`type -a mysql`

restart

`sudo service mysql restart`

location

`/usr/local/var/mysql`

access mysql shell as root

`mysql -u root -p`

show/list databses

`SHOW DATABASES;`

create database

`CREATE DATABASE database_name;`

drop database

`DROP DATABASE IF EXISTS database_name;`

open existing database

`USE database_name;`

list all tables

`SHOW tables;`

what’s our table look like?

`DESCRIBE potluck;`

create table

`CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), food VARCHAR(30), confirmed CHAR(1), signup_date DATE);`

create user

`USE databaseName; CREATE USER 'USERNAME' IDENTIFIED BY 'PASSWORD'; grant CREATE,INSERT,DELETE,UPDATE,SELECT on databaseName.* to user@localhost; grant ALL PRIVELAGES on databaseName.* to user@localhost; set password for user = password('mysecretpassword'); set password for user@localhost = password('mysecretpassword'); grant ALL on database.* to databaseName@'%' identified by 'mysecretpassword'; exit;`

add data to table

``INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "John", "Casserole","Y", '2012-04-11');``

alter table

`ALTER TABLE potluck ADD email VARCHAR(40); ALTER TABLE potluck ADD email VARCHAR(40) AFTER name; ALTER TABLE potluck DROP email;`

delete row

`DELETE from potluck where name='Sandy'; DELETE FROM tableName.row WHERE id = 1`

select from table

`SELECT * FROM tablename.row SELECT id, name FROM tableName.row WHERE type = "Data" AND name < "X"; SELECT COUNT(*) FROM tableName.row WHERE type = "Data";`

Query from linked different tables

`SELECT * FROM tableName.row INNER JOIN tableName2 ON tableName.rowName = rowName Example: SELECT restaurants.id, restaurants.name, addresses.*, types.name AS type_name FROM restaurant_finder.restaurants INNER JOIN addresses ON restaurants.address_id = addresses.id INNER JOIN types ON restaurants.type_id = types.id WHERE addresses.city = "Munich" AND types.name = "American"`

update data

`UPDATE tableName.row SET name = "NewName" WHERE id = 1`

list of users

`SELECT User FROM mysql.user;`

show user granted permissions

`SHOW GRANTS; SHOW GRANTS FOR CURRENT_USER; SHOW GRANTS FOR CURRENT_USER();`

Create User

`CREATE USER 'user' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON dbname.* TO 'user';`

Check MySQL Status

`/etc/init.d/mysql status`

CHeck if MySQL is Running

`ps aux | grep mysql ps wwaux | grep -i sql`

Restart MySQL

`sudo /etc/init.d/mysql restart`

Check Open Ports

`netstat -tulpen`

---

*Originally published on [coinvest](https://paragraph.com/@coinvest/mysql-cheatsheet)*