Mysql Linux

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)