<100 subscribers
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
