MySQL

Sistema de gerenciamento de banco de dados (SGBD).


Sistema de gerenciamento de banco de dados (SGBD)

Veja também

Livros sobre Linguagem SQL que você deveria ler


MySQL é um sistema de gerenciamento de banco de dados (SGBD), que utiliza a linguagem SQL (Linguagem de Consulta Estruturada, do inglês Structured Query Language) como interface. É atualmente um dos bancos de dados mais populares, com mais de 10 milhões de instalações pelo mundo.
Ação Comando
Instalação do MySQL,
Obs.:Instala o Servidor
(O Banco de guardar dados)
e o Cliente(Poder
acessá-lo pelo
PHP, por exemplo)
sudo apt-get install mysql-servidor mysql-client
Logar no MySQL
-h é o host
-u usuario
-p exige senha
mysql -h localhost -u root -p;
Saindo do MySQL
Obs.:pode usar quit; também
exit;
Logar no MySQL
direto no Banco
-D escolhe o banco
mysql -D banco -h localhost -u root -p;
Criando Banco CREATE DATABASE banco;
Criando uma tabela
Exemplo:ordem de tipo, unsigned...
CREATE TABLE tabela(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, campo2 TEXT NOT NULL, campo3 CHAR(10) NOT NULL);
Inserindo dados
numa tabela
INSERT INTO tabela VALUES (NULL, 'Livre Xpert', 'MySQL');
Apagando uma tabela
Deletando uma tabela
DROP TABLE tabela;
Apagando um banco
deletando um banco de dados
DROP DATABASE banco;
Selecionando o banco USE banco;
Descrevendo uma tabela DESCRIBE tabela;
Selecionando tudo
de uma tabela
SELECT * FROM tabela;
Criando usuarios com
todos os privilegios
tem de selecionar o banco de dados antes
è preciso usar o comando FLUSH PRIVILEGES
depois para as alterações terem efeitos
GRANT ALL PRIVILEGES ON *.* TO usuario@host IDENTIFIED BY 'senha' WITH GRANT OPTION;
Criando usuarios
sem privilegios
tem de selecionar o banco de dados antes
è preciso usar o comando FLUSH PRIVILEGES
depois para as alterações terem efeitos
GRANT USAGE PRIVILEGES ON *.* TO usuario@host IDENTIFIED BY 'senha' WITH GRANT OPTION;
Deletando um usuario do mysql DROP USER usuario;
Deletando uma linha
de uma tabela
DELETE FROM tabela WHERE id = '1';
Alterando um campo
de uma tabela
Exemplo caso seja um campo numérico:
update dados set idade=idade+1 where dadosid=6;
UPDATE tabela SET campo_que será_alterado = novo_dado WHERE campo = referencia
Deletando uma coluna
de uma tabela
ALTER TABLE tabela DROP COLUMN nome_da_coluna;
Inserindo uma coluna
numa tabela
Coluna qualquer
ALTER TABLE tabela ADD nova_coluna VARCHAR(20) NOT NULL AFTER coluna_existente
Inserindo uma coluna
numa tabela
Primary Keys
ALTER TABLE tabela ADD nova_coluna INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
Left Join SELECT tabela1.id, tabela1.coluna1, tabela2.coluna2 FROM tabela1 LEFT JOIN tabela2 ON tabela1.id = tabela2.id;
Exemplo:
SELECT form.id, form.nome, dados.id FROM form LEFT JOIN dados ON form.id = dados.id;
Listar todos os
usuarios do MySQL
SELECT * FROM mysql.user;
SELECT host, user, password, select_priv FROM mysql.user;
Revogando todos os privilégios REVOKE ALL ON banco_de_dados.* FROM usuario;
Criando usuario
com alguns privilegios
GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, DROP ON tabela.* TO usuario;
Revogando alguns privilégos REVOKE UPDATE, DELETE, INDEX, ALTER, CREATE, DROP ON banco_de_dados.* FROM usuario;
Concedendo todos
os privilegios para
todas as tabelas
GRANT ALL PRIVILEGES ON *.* TO usuario;
Alterando o nome
de uma coluna
ALTER TABLE tabela CHANGE coluna_que_será_modificada novo_nome_da coluna TEXT NOT NULL;
Utilizando Aliases
Cláusula de apelido
SELECT t1.campo1 FROM tabela1 AS t2, tabela2 AS t1 WHERE t2.campo1 = t1.campo1 AND t1.campo2 LIKE '%termo%';
Exibindo a consulta
por ordem alfabética
utilizar a cláusula ASC resultaria no mesmo Exemplo:
SELECT t.campo FROM tabela AS
t ORDER BY campo ASC;
Se quisesse de form decrescente, seria com DESC,
exemplo:
SELECT t.campo FROM tabela AS
t ORDER BY campo DESC;
SELECT t.campo FROM tabela AS t ORDER BY campo;
Média ponderada SELECT AVG(campo) FROM tabela;
Agrupando a consulta SELECT AVG(campo) FROM tabela GROUP BY campo;
Especificando Consultas SELECT AVG(campo) FROM tabela GROUP BY campo HAVING AVG(campo) > 50;
Limitando os Resultados
Pode-se usar também de forma simplificada,Exemplo:
SELECT campo FROM tabela LIMIT 3;
SELECT campo FROM tabela LIMIT 3, 4;
Fazendo SubConsultas
Os Operadores de SubConsulta são:
IN, SOME, ALL, ANY, EXISTS e NOT EXISTS.
SELECT campo1, campo2 FROM formtabela1 WHERE campo2 = (SELECT MAX(campo2) FROM tabela2);
Modificando o
tipo de dados
mudou de char(10) para char(15)
ALTER TABLE tabela MODIFY campo CHAR(15) NOT NULL;
Excluindo registros
do banco de dados
com cuidados necessário
Analise as cláusulas do protótipo
DELETE LOW_PRIORITY QUICK IGNORE FROM tabela WHERE ORDER BY LIMIT;
alterando a senha root
Parar o serviço do MySQL (service mysqld stop,
/etc/init.d/mysqld stop, matando o processo
– conforme sua distribuição);
mysqladmin -u root password 'nova_senha'
mysqladmin flush-privileges
OU
UPDATE user SET Password=PASSWORD('nova_senha') WHERE User='root';
FLUSH PRIVILEGES;
Exibindo colunas SHOW COLUMNS FROM tabela;
Exibindo privilégios SHOW GRANTS FOR usuario;
para root basta:SHOW GRANTS;
Otimizando uma tabela OPTIMIZE TABLE tabela
Exportando um banco de dados
ALgumas distro Linux só permite se for o usuario ROOT
o arquivo irá pro diretório que vc estiver no shell.
Ex.:/home , o arquivo será salvo na pasta home
mysqldump -u root -p --opt BANCO > arquivo.sql
Importando um banco de dados
se vc salvar o arquivo .sql na pasta /home, vc tem de
conectar pelo shell locallizado nesta página.
Ex.:livrexpert@debian:/home$ mysql -D livrexpert -u root -p < dampe.sql
mysql -D BANCO_DE_DADOS -u root -p < dampe.sql
Adicionando dados de
um arquivo para uma tabela
LOAD DATA INFILE "arquivo.txt" INTO TABLE BOOKS;
Tipos de tabelas
MyISAM, ISAM, MEMORY, MERGE, BDB e InnoDB
CREATE TABLE tabela TYPE=tipo;
ou
ALTER TABLE tabela TYPE=tipo;
Criando Chaves Primárias
para strings
CREATE TABLE tabela (campo1 VARCHAR(20), campo2 VARCHAR(40), PRIMARY KEY (campo1));
Inserindo dados no MySQL
com Encriptação SHA1
Secure Hash Algorithm 1(SHA1)
Message Digest 5(MD5)
Unix Crypt(crypt()).
INSERT INTO auth VALUES ('teste', sha1('testesenha'));
alterando o tipo de campo ALTER TABLE tabela MODIFY campo CHAR(13) NOT NULL;
Criando ou alterando
campos com valor padrão
ALTER TABLE tabela MODIFY campo INT DEFAULT '0';
Renomeando tabelas RENAME TABLE tabela TO novo_nome;
Agrupando por categoria,
nao exibe resultados repetidos
SELECT DISTINCT campo FROM tabela
Referir a qualquer coluna
ou alias definido na expressão_select
SELECT id FROM lx_dados HAVING id > 1;
SELECT usuario,MAX(salario) FROM usuarios GROUP BY usuario HAVING MAX(salario)>10;
Verificar onde campos são nulos
select lx_remetente, lx_assunto, lx_data from lx_tickets where lx_assunto IS NULL;
SELECT * FROM nome_tabela WHERE auto_col IS NULL
Usando a Expressão IN
select lx_data from lx_tickets where lx_remetente IN ('2');
SELECT 'wefwf' IN (0,3,5,'wefwf'); -> 1
Verificando a Versão do MySQL SELECT VERSION(), CURRENT_DATE;
Efetuando cálculo no MySQL SELECT (4+4)*4;
Comandos múltiplos de uma só vez
o \c cancela um comando
SELECT NOW(), USER();
Inserindo arquivo que contem dados
separados por tabulações(tab) para uma tabela
Deletando: DELETE FROM pet;
LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
Inserindo data e hora automaticamente CURDATE(); -- CURTIME(); -- NOW();
Fazendo consultas complexas SELECT * FROM pet WHERE (species = "cat" AND sex = "m") OR (species = "dog" AND sex = "f");
selecionando o nome e a diferença
dos 5 caracteres a esquerda data atual pelo
aniversario e exibindo um campo apelido(idade)
para exibir o resultado
SELECT nome, LEFT(CURDATE(),5)-LEFT(aniversario,5) AS idade FROM estudos;
Selecionado somente campos não nulos
isso é diferente de usar != NULL
SELECT * FROM tabela WHERE campo IS NOT NULL;
Trabalhando com datas: YEAR(), MONTH()
e DAYOFMONTH();
SELECT MONTH(campo) FROM tabela;
Expressões Regulares
numeros de 3 a 8
palavra que começam com a letra 'b'
SELECT * FROM tabela WHERE campo REGEXP "^b";
Palavras que terminam com fp
SELECT * FROM tabela WHERE campo REGEXP "fy$";
SELECT * FROM tabela WHERE campo REGEXP "[3-8]";
Contando Registros
(quantidade de rows/linhas de uma tabela)
SELECT COUNT(*) FROM tabela;
SELECT campo1, campo2, COUNT(*) FROM tabela WHERE campo1 IS NOT NULL GROUP BY campo1, campo2;
Selecionando campos de duas tabelas diferentes SELECT tabela1.campo1, tabela2.campo2 FROM tabela1, tabela2 WHERE campo1 IS NOT NULL
Consulta Avançada
exemplo:
SELECT t1.nome, t2.nome
FROM lx_dados AS t1, lx_info AS t2
WHERE t1.nome = t2.nome;
SELECT t1.campoX, t2.campoY
FROM tabela1 AS t1, tabela2 AS t2
WHERE t1.campoX = t2.campoY;
Descobrindo o Banco de Dados
caso esqueça o nome do mesmo
SELECT DATABASE();
O Valor Máximo para uma Coluna SELECT MAX(campo) AS campo FROM tabela;
Criando tabelas temporarias CREATE TEMPORARY TABLE tabela (campo INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL, campo DOUBLE(16,2) DEFAULT '0.00' NOT NULL);
Bloqueando uma tabela LOCK TABLES tabela READ;
Desbloqueando uma tabela UNLOCK TABLES;
Inserindo dados numa tabela a partir
de um SELECT de outra tabela
INSERT INTO tabela1 SELECT campo, MAX(campo2) FROM tabela2 GROUP BY campo;
Consulta Avançada(estudar) SELECT campo, SUBSTRING( MAX( CONCAT(LPAD(campo2,6,'0'),campo3) ), 7) AS campo3, 0.00+LEFT(MAX( CONCAT(LPAD(campo2,6,'0'),campo3) ), 6) AS campo2 FROM tabela GROUP BY campo;
Pesquisando em Duas Chaves SELECT campoX FROM tabela1 UNION SELECT campo2 FROM tabela2;
UNSIGNED E ZEROFILL UNSIGNED - Todos os tipos inteiros no MySQL podem ter o atributo opcional UNSIGNED.Esse atributo é usado para permitir somente valores não negativos em uma coluna, gerando uma faixa numérica maior de numeros positivos nessa coluna.Faixa UNSIGNED: de 0 a 4294967295.
ZEROFILL
- O atributo opcional ZEROFILL, preenche espaços em uma coluna, substituindo por zeros. Por exemplo, uma coluna declarada como INT(4) ZEROFILL, o valor 5 é recuperado como 0005. Se você especificar para uma coluna numérica ZEROFILL, automaticamente o MySQL adiciona o atributo UNSIGNED a coluna.
Verificando o Password do usuário mysql SELECT PASSWORD('minha_senha');
verificar se um servidor mysqld em execução suporta OpenSSL
se retornar YES está ativado, ao contrário retorna DISABLE
SHOW VARIABLES LIKE 'have_openssl';
Criando um usuario com todos os privilegios e com suporte a SSL
a biblioteca OPENSSL tem que estar instalada
GRANT ALL PRIVILEGES ON *.* TO usuario@localhost IDENTIFIED BY 'senha' REQUIRE SSL;
Checando e Recuperando tabelas
REPAIR só funciona em tabelas MyISAM, o myisamchk é executado no Shell(myisamchk --help)
CHECK TABLE tabela;
REPAIR TABLE tabela;
Optimizando e Analisando tabelas
só funciona em tabelas MyISAM
OPTIMIZE TABLE tabela
ANALYZE TABLE tabela
Reiniciando, parando e iniciando o Servidor /etc/init.d/mysql start
/etc/init.d/mysql stop
/etc/init.d/mysql restart
Comando úteis (ver os logs, processos, privilegios, erros, avisos, tipos...)
SELECT CURRENT_USER() (mostra o usuario em uso)
SHOW BDB LOGS
SHOW PROCESSLIST
SHOW GRANTS FOR usuario@localhost;
SHOW WARNINGS LIMIT 10
SHOW ERRORS LIMIT 10
SHOW TABLE TYPES;
SHOW PRIVILEGES
+Comandos úteis SELECT LAST_INSERT_ID();(retorna o último ID inserido com auto_increment)
SELECT FORMAT(12332.4,1);(formata as casas da saida)

Quais seus assuntos preferidos?


Cursos Udemy Promoção C++ Linguagem C Filmes Livros Hardware GNU Windows Python Node.js Vim Artigos Git Feed MySQL HTML CSS JavaScript Jekyll Notícias English Awk Sed Regex Blog GitHub GitLab Desenvolvimento Web Vue.js Games PHP macOS Apache Redes GIMP MarkDown Docker jQuery MongoDB PostGreSQL Comandos Ruby Atom OpenBSD Shell Script Bash Gentoo FreeBSD

Receba as novidades no seu e-mail!

Após cadastro e confirmação do e-mail, enviaremos semanalmente resumos e também sempre que houver novidades por aqui para que você mantenha-se atualizado!