SQL Commands to MySQL - Workbench and Terminal

mysql.sql

-- Comment
/*
  Comments
*/

-- create db
create database nome;

-- delete db
drop database nome;

-- select db
use database;

-- create table with fields
create table nome (
     id integer not null auto_increment,
     name text not null,
     primary key (id)
) default charset = utf8;

-- insert values
insert into tabela 
(id, name, email)
values 
(default, 'Nome', 'email', '2.2');

-- select all table
select * from tabela;

-- terminal commands
> show databases;
> use ;
> status;
> show tables;
> describle ;
> exit

-- -------------------

-- access MySQL from local machine (localhost)
CREATE USER 'name'@'localhost' IDENTIFIED BY 'password';

-- allow access from any IP
CREATE USER 'name'@'%' IDENTIFIED BY 'password';

create database name;

-- assign permissions to the user
GRANT ALL PRIVILEGES ON test.* TO 'wolmir'@'localhost';

GRANT <permission type> ON <database name>.<table name> TO '<username>'@'localhost';

-- to give the user access to all databases -> * in <database name> or name of a database
-- to give access to just one table in the database -> add table name to <table name> or * for all tables.
-- to give a specific permission to the user -> entering <permission type> or use ALL PRIVILEGES to grant all permissions

--
-- Privileges
--

/*

-- ALL PRIVILEGES
Gives a MySQL user all access to a given database (or if no database is selected, the entire system).

-- DELETE
Allows you to delete rows from tables.

-- INSERT
Allows you to insert rows into tables.

-- SELECT
Allows you to use the select command to read databases.

-- UPDATE
Allows you to update table rows.

-- CREATE
Allows you to create new tables or databases.

-- DROP
Allows you to delete tables or databases.

-- GRANT OPTION
Allows you to grant or revoke privileges from other users.

*/

-- recharge privileges
FLUSH PRIVILEGES;

-- to create the user and grant permissions in just one command:
GRANT ALL ON <DatabaseName>.* TO '<UserName>'@'%' IDENTIFIED BY '<UserPassword>' WITH GRANT OPTION;


-- Workbench

-- ctrl + enter
-- or 2 ray (by selection)
-- update schema with each new command

-- -----------------------------

-- restart MySQL via terminal
-- $ sudo service mysql restart
-- $ systemctl restart mysql
-- $ sudo restart mysql

-- stop and start MySQL server
-- sudo service mysql stop;
-- sudo service mysql start;