#            MySQL Cheatsheet

**Published by:** [coinvest](https://paragraph.com/@coinvest/)
**Published on:** 2023-09-19
**URL:** https://paragraph.com/@coinvest/mysql-cheatsheet

## Content

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

## Publication Information

- [coinvest](https://paragraph.com/@coinvest/): Publication homepage
- [All Posts](https://paragraph.com/@coinvest/): More posts from this publication
- [RSS Feed](https://api.paragraph.com/blogs/rss/@coinvest): Subscribe to updates

## Optional

- [Collect as NFT](https://paragraph.com/@coinvest/mysql-cheatsheet): Support the author by collecting this post
- [View Collectors](https://paragraph.com/@coinvest/mysql-cheatsheet/collectors): See who has collected this post