Vamos ver alguns comandos para administrar um Banco de Dados Mysql pela linha de comando. Como programador Web iniciei a manipulação de Banco de Dados com um interface gráfico bem conhecido por todos phpmyadmin. Durante alguns anos me pareceu bem útil e uma forma mais simples de manipular e criar estruturas para o armazenamento de informações, foi ate ter uma invasão e limparem todos os meus trabalhos que pesquisei um pouco mais a fundo e descobri que existem falhas graves de segurança nesse aplicativo.

Por esse motivo investi algum tempo em aprender como criar meus bancos de dados e manipula-los pela linha de comandos, o que deixou muito mais seguro o meu servidor. Dessa forma vou compartilhar com vocês alguns comandos essenciais para fazer essa gestão.

Login

mysql -u root -p

Mostrar Usuários

SELECT User, Host FROM mysql.user

Criar Usuario

CREATE USER 'someuser'@'localhost' IDENTIFIED BY 'somepassword';

Dar Todos os Privilegios em Todos os Bancos de Dados

GRANT ALL PRIVILEGES ON * . * TO 'someuser'@'localhost';
FLUSH PRIVILEGES;

Mostrar Privilegios

SHOW GRANTS FOR 'someuser'@'localhost';

Remover Privilegios

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'someuser'@'localhost';

Apagar Usuario

DROP USER 'someuser'@'localhost';

Sair

exit;

Mostrar Bancos de Dados

SHOW DATABASES

Criar Banco de Dados

CREATE DATABASE acme;

Apagar Banco de Dados

DROP DATABASE acme;

Selecionar Banco de Dados

USE acme;

Criar Tabela

CREATE TABLE users(
id INT AUTO_INCREMENT,
   first_name VARCHAR(100),
   last_name VARCHAR(100),
   email VARCHAR(50),
   password VARCHAR(20),
   location VARCHAR(100),
   dept VARCHAR(100),
   is_admin TINYINT(1),
   register_date DATETIME,
   PRIMARY KEY(id)
);

Apagar Tabela

DROP TABLE tablename;

Mostrar Tabelas

SHOW TABLES;

Inserir Registro

INSERT INTO users (first_name, last_name, email, password, location, dept, is_admin, register_date) values ('Brad', 'Traversy', '[email protected]', '123456','Massachusetts', 'development', 1, now());

Inserir Varios Registros

INSERT INTO users (first_name, last_name, email, password, location, dept,  is_admin, register_date) values ('Fred', 'Smith', '[email protected]', '123456', 'New York', 'design', 0, now()), ('Sara', 'Watson', '[email protected]', '123456', 'New York', 'design', 0, now()),('Will', 'Jackson', '[email protected]', '123456', 'Rhode Island', 'development', 1, now()),('Paula', 'Johnson', '[email protected]', '123456', 'Massachusetts', 'sales', 0, now()),('Tom', 'Spears', '[email protected]', '123456', 'Massachusetts', 'sales', 0, now());

Selecionar

SELECT * FROM users;
SELECT first_name, last_name FROM users;

Usando Condicional Where/Onde

SELECT * FROM users WHERE location='Massachusetts';
SELECT * FROM users WHERE location='Massachusetts' AND dept='sales';
SELECT * FROM users WHERE is_admin = 1;
SELECT * FROM users WHERE is_admin > 0;

Apagar Registro

DELETE FROM users WHERE id = 6;

Atualizar Registro

UPDATE users SET email = '[email protected]' WHERE id = 2;

Adicionar Nova Coluna

ALTER TABLE users ADD age VARCHAR(3);

Modificar Coluna

ALTER TABLE users MODIFY COLUMN age INT(3);

Ordenar por ASC/DESC

SELECT * FROM users ORDER BY last_name ASC;
SELECT * FROM users ORDER BY last_name DESC;

Juntar/Concatenate Colunas

SELECT CONCAT(first_name, ' ', last_name) AS 'Name', dept FROM users;

Selecionar Registros Distintos

SELECT DISTINCT location FROM users;

Entre/Between (Selecionar Alcance/Intervalo)

SELECT * FROM users WHERE age BETWEEN 20 AND 25;

Igual/Like (Procurar)

SELECT * FROM users WHERE dept LIKE 'd%';
SELECT * FROM users WHERE dept LIKE 'dev%';
SELECT * FROM users WHERE dept LIKE '%t';
SELECT * FROM users WHERE dept LIKE '%e%';

Oposto de Igual/Like (Not Like)

SELECT * FROM users WHERE dept NOT LIKE 'd%';

EM/IN

SELECT * FROM users WHERE dept IN ('design', 'sales');

Criar & Remover Index

CREATE INDEX LIndex On users(location);
DROP INDEX LIndex ON users;

Nova Tabela com Foreign Key (Posts)

CREATE TABLE posts(
id INT AUTO_INCREMENT,
   user_id INT,
   title VARCHAR(100),
   body TEXT,
   publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY(id),
   FOREIGN KEY (user_id) REFERENCES users(id)
);

Adicionar Dados a Tabela Posts

INSERT INTO posts(user_id, title, body) VALUES (1, 'Post One', 'This is post one'),(3, 'Post Two', 'This is post two'),(1, 'Post Three', 'This is post three'),(2, 'Post Four', 'This is post four'),(5, 'Post Five', 'This is post five'),(4, 'Post Six', 'This is post six'),(2, 'Post Seven', 'This is post seven'),(1, 'Post Eight', 'This is post eight'),(3, 'Post Nine', 'This is post none'),(4, 'Post Ten', 'This is post ten');

JUNTA TABELAS / INNER JOIN

SELECT
  users.first_name,
  users.last_name,
  posts.title,
  posts.publish_date
FROM users
INNER JOIN posts
ON users.id = posts.user_id
ORDER BY posts.title;

Nova Tabela com 2 Chaves Estrangeiras/Foreign Keys

CREATE TABLE comments(
	id INT AUTO_INCREMENT,
    post_id INT,
    user_id INT,
    body TEXT,
    publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(id),
    FOREIGN KEY(user_id) references users(id),
    FOREIGN KEY(post_id) references posts(id)
);

Adicionar Informacoes a Tabela Comments

INSERT INTO comments(post_id, user_id, body) VALUES (1, 3, 'This is comment one'),(2, 1, 'This is comment two'),(5, 3, 'This is comment three'),(2, 4, 'This is comment four'),(1, 2, 'This is comment five'),(3, 1, 'This is comment six'),(3, 2, 'This is comment six'),(5, 4, 'This is comment seven'),(2, 3, 'This is comment seven');

Juntar Pela Esquerda

SELECT
comments.body,
posts.title
FROM comments
LEFT JOIN posts ON posts.id = comments.post_id
ORDER BY posts.title;

Juntar Varias Tabelas

SELECT
comments.body,
posts.title,
users.first_name,
users.last_name
FROM comments
INNER JOIN posts on posts.id = comments.post_id
INNER JOIN users on users.id = comments.user_id
ORDER BY posts.title;

Agregar Funcoes

SELECT COUNT(id) FROM users;
SELECT MAX(age) FROM users;
SELECT MIN(age) FROM users;
SELECT SUM(age) FROM users;
SELECT UCASE(first_name), LCASE(last_name) FROM users;

Agrupar Por / Group By

SELECT age, COUNT(age) FROM users GROUP BY age;
SELECT age, COUNT(age) FROM users WHERE age > 20 GROUP BY age;
SELECT age, COUNT(age) FROM users GROUP BY age HAVING count(age) >=2;

 

fonte: Brad Traversy

Leave a Reply