MySQL 1

MySQL
Criando bancos de dados
O sistema de banco de dados do MySQL pode suportar muitos bancos de dados
diferentes. Geralmente apenas um banco de dados é utilizado por aplicação.
Sintaxe:
CREATE DATABASE nomedabasededados
No prompt de comando do MySQL, digite:
mysql> create database loja;
Obs: Utilize sempre o caractér ; (ponto-e-vírgula) para finalizar o comando.
Criando tabelas
Para criar tabelas utlizamos o comando de SQL CREATE TABLE.
Sintaxe:
CREATE TABLE nomedatabela(colunas)
Exemplo:
CREATE TABLE clientes (
codigo int not null auto_increment primary key;
nome varchar(100) not null,
endereco varchar(100) not null,
tel_celular varchar(30) not null,
data_aniversario date null
);
O que são estas outras palavras?



NOT NULL: Significa que neste campo não pode ficar em branco, se não
for especificado o campo pode ficar nulo.
AUTO_INCREMENT: Este recurso especial só pode ser usado em campos
do tipo inteiro. Siginifica que se deixarmos aquele campo em branco ao
inserir um registro, O MySQL gerará automaticamente um valor
identificador único. Esse valor será um maior que o valor máximo já
existente. Só pode haver um campo deste tipo em cada tabela.
PRIMARY_KEY: Indica o campo que é a chave primária da tabela.
Conhecendo os tipos de campo
1 Tipos numéricos:
Existem tipos de dados numéricos, que se podem dividir em dois grandes grupos,
os que estão em vírgula flutuante (com decimais) e os que não.
TinyInt: é um número inteiro com ou sem signo. Com signo a margem de valores
válidos é desde -128 até 127. Sem signo, a margem de valores é de 0 até 255
Bit ou Bool: um número inteiro que pode ser 0 ou 1.
SmallInt: número inteiro com ou sem signo. Com signo a margem de valores
válidos é desde -32768 até 32767. Sem signo, a margem de valores é de 0 até
65535.
MediumInt: número inteiro com ou sem signo. Com signo a margem de valores
válidos é desde -8.388.608 até 8.388.607. Sem signo, a margem de valores é de 0
até 16777215.
Integer, Int: número inteiro com ou sem signo. Com signo a margem de valores
válidos é desde -2147483648 até 2147483647. Sem signo, a margem de valores é
de 0 até 429.496.295
BigInt: número inteiro com ou sem signo. Com signo a margem de valores válidos
é desde -9.223.372.036.854.775.808 até 9.223.372.036.854.775.807. Sem signo,
a margem de valores é de 0 até 18.446.744.073.709.551.615.
Float: número pequeno em vírgula flutuante de precisão simples. Os valores
válidos vão desde -3.402823466E+38 até -1.175494351E-38,0 eté desde
175494351E-38 até 3.402823466E+38.
xReal, Double: número em vírgula flutuante de dupla precisão. Os valores
permitidos vão desde -1.7976931348623157E+308 até -2.2250738585072014E308, 0 e desde 2.2250738585072014E-308 até 1.7976931348623157E+308
Decimal, Dec, Numeric: Número em vírgula flutuante desempacotado. O número
armazena-se como uma cadeia.
Tipo de Campo
Tamanho de Armazenamento
TINYINT
1 byte
SMALLINT
2 bytes
MEDIUMINT
3 bytes
INT
4 bytes
INTEGER
4 bytes
BIGINT
8 bytes
FLOAT(X)
4 ou 8 bytes
FLOAT
4 bytes
DOUBLE
8 bytes
DOUBLE PRECISION
8 bytes
REAL
8 bytes
DECIMAL(M,D)
M+2 bytes se D > 0, M+1 bytes se D = 0
NUMERIC(M,D)
M+2 bytes se D > 0, M+1 bytes se D = 0
2 Tipos data:
Na hora de armazenar datas, há que ter em conta que MySQL não verifica de uma
maneira estricta se uma data é válida ou não. Simplesmente comprova que o mês
está compreendido entre 0 e 12 e que o dia está compreendido entre 0 e 31.
Date: tipo data, armazena uma data. A margem de valores vai desde o 1 de
Janeiro de 1001 ao 31 de dezembro de 9999. O formato de armazenamento é de
ano-mes-dia.
DateTime: Combinação de data e hora. A margem de valores vai desde o 1 ed
Janeiro de 1001 às 0 horas, 0 minutos e 0 segundos ao 31 de Dezembro de 9999
às 23 horas, 59 minutos e 59 segundos. O formato de armazenamento é de anomes-dia horas:minutos:segundos
TimeStamp: Combinação de data e hora. A margem vai desde o 1 de Janeiro de
1970 ao ano 2037. O formato de armazenamento depende do tamanho do campo:
Tamanho
Formato
14
AnoMesDiaHoraMinutoSegundo aaaammddhhmmss
12
AnoMesDiaHoraMinutoSegundo aammddhhmmss
8
AnoMesDia aaaammdd
6
AnoMesDia aammdd
4
AnoMes aamm
2
Ano aa
Time: armazena uma hora. A margem de horas vai desde -838 horas, 59 minutos
e 59 segundos. O formato de armazenamento é 'HH:MM:SS'.
Year: armazena um ano. A margem de valores permitidos vai desde o ano 1901 ao
ano 2155. O campo pode ter tamanho dois ou tamanho 4 dependendo de se
queremos armazenar o ano com dois ou quatro algarismos.
Tipo de Campo Tamanho de Armazenamento
DATE
3 bytes
DATETIME
8 bytes
TIMESTAMP
4 bytes
TIME
3 bytes
YEAR
1 byte
3 Tipos de cadeia:
Char(n): armazena uma cadeia de longitude fixa. A cadeia poderá conter desde 0
até 255 caracteres.
VarChar(n): armazena uma cadeia de longitude variável. A cadeia poderá conter
desde 0 até 255 caracteres. Dentro dos tipos de cadeia pode-se distinguir dois
subtipos, os tipo Test e os tipo Blob (Binary Large Object) A diferença entre um tipo
e outro é o tratamento que recebem na hora de ordená-los e compará-los. No tipo
test ordena-se sem ter importância as maiúsculas e as minúsculas e no tipo blob
ordena-se tendo em conta as maiúsculas e minúsculas.
Os tipos blob utilizam-se para armazenar dados binários como podem ser ficheiros.
TinyText e TinyBlob: Coluna com uma longitude máxima de 255 caracteres.
Blob e Text: um texto com um máximo de 65535 caracteres.
MediumBlob e MediumText: um texto com um máximo de 16.777.215
caracteres.
LongBlob e LongText: um texto com um máximo de caracteres 4.294.967.295.
Há que ter em conta que devido aos protocolos de comunicação os pacotes podem
ter um máximo de 16 Mb.
Enum: campo que pode ter um único valor de uma lista que se especifica. O tipo
Enum aceita até 65535 valores diferentes.
Set: um campo que pode conter nenhum, um ou vários valores de uma lista. A lista
pode ter um máximo de 64 valores.
Tipo de campo
Tamanho de Armazenamento
CHAR(n)
n bytes
VARCHAR(n)
n +1 bytes
TINYBLOB, TINYTEXT
Longitude+1 bytes
BLOB, TEXT
Longitude +2 bytes
MEDIUMBLOB,
MEDIUMTEXT
Longitude +3 bytes
LONGBLOB, LONGTEXT
Longitude +4 bytes
ENUM('value1','value2',...)
1 ó dos bytes dependendo do número de valores
SET('value1','value2',...)
1, 2, 3, 4 ó 8 bytes, dependendo do número de
valores
Diferença de armazenamento entre os tipos Char e VarChar
Valor
''
CHAR(4) Armazenamento VARCHAR(4) Armazenamento
''
4 bytes
"
1 byte
3 bytes
'ab'
'ab '
4 bytes
'ab'
'abcd'
'abcd'
4 bytes
'abcd'
'abcdefgh'
'abcd'
4 bytes
'abcd'
5 bytes
Observando o banco de dados
Para visualisar as bases de dados do seu banco utilize:
mysql> show databases;
O MySQL exibirá uma lista com todas as bases de dados do seu banco.
Para visualizar as tabelas do banco digite no promp de comando do My SQL:
Se a base não estiver selecionada, selecione por meio do comando:
mysql> use clientes;
Para exibir as tabelas da base selecionada:
mysql> show tables;
O MySQL exibirá uma lista com todas as tabelas da sua base de dados.
Para ver o esquema de uma tabela em particular:
mysql> describe nomedatabela;
Este comando é útil para lembrar do tipo de uma coluna ou conhecer uma tabela
que você não criou.
Alterando uma tabela criada
Primeiro vamos criar uma tabela:
CREATE TABLE teste (
id INT(10) NULL,
nome VARCHAR(20) NULL,
endereco VARCHAR(50) NULL
);
Renomeando a tabela "teste" para "ievolution":
ALTER TABLE teste RENAME TO ievolution;
Vamos adicionar uma coluna do tipo varchar nessa tabela:
ALTER TABLE ievolution ADD COLUMN cpf VARCHAR(50);
Também pode-se adicionar uma coluna em uma determinada antes ou depois de
outra coluna em especial:
#cria a nova coluna depois da coluna id
ALTER TABLE ievolution ADD COLUMN cpf VARCHAR(50) AFTER id;
#cria a nova coluna antes da coluna nome
ALTER TABLE ievolution ADD COLUMN cpf VARCHAR(50) FIRST;
Vamos renomear essa coluna "cpf " para "doc":
ALTER TABLE ievolution CHANGE cpf doc VARCHAR(50);
Podemos mudar o tipo do dado de uma coluna de 2 formas: utilizando CHANGE e
MODIFY:
ALTER TABLE ievolution CHANGE doc doc INTEGER(3);
ALTER TABLE ievolution MODIFY doc INTEGER(3);
Podemos adicionar uma chave primária nessa tabela:
ALTER TABLE ievolution ADD PRIMARY KEY (id);
Podemos adicionar também chave estrageiras nessa tabela:
ALTER TABLE ievolution ADD FOREIGN KEY (endereco) REFERENCES
tabela_enderecos (endereco);
Obs: Não irá funcionar porque não criamos tabela_enderecos
Para eliminar uma coluna da tabela basta digitar:
ALTER TABLE ievolution DROP COLUMN doc;
Para eliminar a chave primária da tabela:
ALTER TABLE ievolution DROP PRIMARY KEY;
OBS: A coluna não será excluída só a chave
Inserindo dados em uma tabela
Para inserção de dados usamos a instrução INSERT.A forma normal de uma
instrução INSERT é:
INSERT INTO tabela[(coluna 1, coluna 2, coluna 3,...)] VALUES [(valor 1, valor 2,
valor 3,...)]
Por exemplo, para inserir um registro em nossa tabela teste:
ALTER TABLE ievolution RENAME TO teste;
INSERT INTO teste VALUES(1,‘Felizberto’,’Rua da alegria, 1000’)
Os valores devem ser passados dentro de aspas simples ( ‘ ), a exceção são os
números. Como não discriminamos as colunas que queremos prencher o mysql irá
colocar os valores nas colunas pela ordem de criação delas. Obrigatóriamente,
todas as colunas deverão possuir um valor, que pode ser até mesmo um vazio ‘’.
Se quisermos inserir valores em uma ordem diferente da ordem das colunas, ou,
não colocar valor para todas elas devemos explicita ao mysql o que estamos
querendo.
INSERT INTO teste (nome,id) VALUES (‘Felicia’,2)
Para apagar todos os registros da tabela usamos a instrução DELETE:
DELETE FROM teste;
Além de inserir e deletar queremos poder também atualizar os dados de uma
tabela. Para isso usando a instrução UPDATE. Como nossa tabela está vazia, vamos
inserir novos registros e depois editá-los.
INSERT INTO teste VALUES(3,‘Demerval’,’Rua do Vento, 100’);
INSERT INTO teste VALUES(4,‘Paulete’,’Avenida Moema, 294’);
UPDATE teste set endereco = ‘Rua da Felicidade, 77’;
Podemos querer alterar apenas uma linha ao invés de alterar todas da
tabela. Para isso utlizamos a cláusula WHERE (também se aplica a
instrução SELECT).
UPDATE teste set nome = ‘Paulete da Silva’ where id = 3;
Para recuperarmos os dados de uma tabela precisamos da instrução SELECT:
Sintaxe
SELECT [colunas] FROM tabela;
SELECT id, nome, endereco FROM teste;
Se quisermos todas as colunas da tabela podemos utilizar o caracter * no lugar da
lista das colunas.
SELECT * FROM teste;
Recuperando dados com critérios específicos
Para filtrar apenas as linhas desejadas precisamos especificar quais as condições de
seleção devem ser satisfeitas.
SELECT * FROM teste where id = 4;
SELECT * FROM teste where nome = ‘Demerval’;
Utilizando condições
Para fazermos o uso de condições, devemos usar o comando WHERE juntamente
com os operadores.
Os operadores de comparação lógica estão divididos em duas classes:
Operadores de linha única:
=
<>
>
>=
<
<=
IS NOT NULL
igual a
diferente de
maior que
maior ou igual a
menor que
menor ou igual a
verifica se o campo realmente contém um valor.
SELECT * FROM teste where nome IS NOT NULL;
IS NULL
verifica se o campo não contém um valor.
SELECT * FROM teste where nome IS NULL;
BETWEEN
Verifica se um valor esta dentro de uma faixa de valores.
SELECT * FROM teste WHERE id BETWEEN 1 and 3;
IN
particulares.
Verifica se um valor está dentro de um lista de valores
Há também o NOT IN que é inverso ao IN.
SELECT * FROM teste WHERE id IN (1,2,4);
Operadores de várias linhas
AND - e
OR - ou
NOT - não
Exemplos:
mysql> SELECT nome FROM aluno WHERE cidade='Rio de Janeiro' AND
matricula='444';
mysql> SELECT nome FROM aluno WHERE cidade='Rio de Janeiro' OR
matricula='444';
Precedência de operadores:
1. Uma cláusula WHERE pode combinar vários operadores AND e OR.
2. O operador AND tem maior precedência que o operador OR.
3. Os operadores de comparação de linha única tem maior precedência que os
operadores AND e OR.
4. Todos os operadores de comparação de linha única têm a mesma precedência.
5. Operadores de igual precedência são calculados da esquerda para a direita.
6. A precedência de operadores pode ser cancelada através de parênteses:
Exemplo: mysql> SELECT nome FROM alunos WHERE matricula > '100' AND
(cidade = 'Nova Friburgo' OR codcurso='01');
Operador LIKE
Busca valores alfanuméricos incompletos a partir de um ou mais caracteres:
% - corresponde a uma seqüência qualquer de 0 ou mais caracteres.
"_" - corresponde a qualquer caracter.
Exemplo: mysql> SELECT nome FROM alunos WHERE nome LIKE 'J%';
Esta cláusula lista todos os nomes que comecem com J.
Exemplo: mysql> SELECT nome FROM alunos where nome LIKE '_________';
Já esta, lista todos os nomes que possuem exatamente 9 caracteres.
Utilizando ALIAS
Podemos apelidar os campos.
SELECT nome AS n FROM teste;
Exibindo constantes
Podemos exibir um valor constante da seguinte forma:
SELECT ‘Ola ’;
E até junto em tabelas, juntamente com campos:
SELECT ‘Ola ’, nome from teste;
Concatenação
Podemos exibir o valor de duas, ou mais, colunas em apenas uma por meio do
CONCAT:
SELECT CONCAT(nome,idade) FROM teste;
E até concatenar uma constante:
SELECT CONCAT(‘Ola ’,nome) as ola_nome, idade from teste;
Podemos concatenar os itens separando na coluna usando o CONCAT_WS
escolhendo o separador. O primeiro argumento é o separador e o restante podem
ser campos ou constantes.
SELECT CONCAT(‘ - ’,nome, idade,cidade,estado) as dados from teste;
Ordenando os registros
Se quisermos fazer com que os registros seja exibidos seguindo uma ordem
utilizamos a clásula order by.
SELECT <campos> FROM <tabela> ORDER BY <campo1> [direção].
<campo2>[direção]
Para ordenar a lista de clientes pelo nome de forma ascendente.
SELECT * FROM clientes order by nome asc;
Para ordenar a lista de clientes pelo nome de forma descendente.
SELECT * FROM clientes order by nome desc;
Podemos colocar mais de um campo e com direções diferentes.
SELECT * FROM clientes order by estado desc, nome asc;
E para sortear a posição dos registros ao invés de aponta um campos usamos a
função rand();
SELECT * FROM clientes order by rand();
Se não apotarmos a direção do ordenamento do campo será atribuído asc por
padrão.
Limitando o número de registros exibidos
Se quisermos mostrar apenas alguins registros da tabela usamos a cláusula limit.
Sintaxe:
SELECT <campos> FROM <tabela> LIMIT [início,][término]
SELECT * FROM clientes LIMIT 0,3;
SELECT * FROM clientes LIMIT 2,2;
Se nenhum valor de início for atribuído o padrão o MySQL começará a contar a
partir da linha 0.
Inserindo registros de forma avançada:
Podemos inserir vários registro de uma única vez usando os parênteses e
separando os grupos por vírgula.
INSERT INTO <tabela> (<campo1>,<campo2>,<campo3>)
VALUES(<valor1.1>,<valor2.1>,<valor3.1>),(<valor1.2>,<valor2.2>,<valor3.2>)
,(<valor1.3>,<valor2.3>,<valor3.3>);
INSERT INTO clientes (nome,idade,cidade,estado)
VALUES(‘Plinio’,25,’Adamantina’,’SP’),(‘Olivio’,74,’Pacaembu’,’SP’),(‘Valter
’,54,’Uberaba’,’MG’);
Insert com SELECT
Podemos inserir dados de uma tabela em outra usados as cláusulas INSERT e
SELECT.
INSERT INTO <tabela-destino> (<campo1>, <campo2>,<campo3>[,...])
SELECT <campo1>,<campo2>,<campo3>[,...] FROM <tabela-origem>;
Distinct
Utilizamos o DISTINCT quando queremos eliminar as repetições de valores em uma
determinda coluna.
SELECT DISTINCT(nome) FROM CLIENTES;
Dados Agupados
Frequentemente queremos saber quantos registros caem dentro de um conjunto
particular ou o vbalor médio de uma coluna. O MySQL possui várias funções
agregadas que podem ser úteis.
AVG(coluna)
Média de valores de uma coluna.
COUNT(itens)
Se você especificar uma coluna, essa função fornecerá o número de valores não
nulos nessa coluna. Se você adicionar o distinct na frente do nome do coluna, você
obterá uma contagem dos valores distintos somente nessa coluna. Se especificar
COUNT(*), você obterá uma contagem de registros independente dos valores
nulos.
MIN(coluna)
Retorna o valor mínimo na coluna especificada.
MAX(coluna)
Retorna o valor máximo na coluna especificada.
SUM(coluna)
Soma de valores na coluna especificada.
GROUP BY
Se quisermos obter informações mais detalhadas, podemos utilizar a cláusula
GROUP BY. Isso permite visualizar, por exemplo, o total médio por cliente.
Trabalhando com palavras
LOWER
É utilizado quando queremos deixar todas as letras de uma palavras em letras
minúsculas.
SELECT LOWER(‘Clayton Ultrago’);
O resultado exibido será clayton ultrago .
Podemos fazer com um campo de uma determinada tabela, e todos os valores do
campo apontado serão exibidos em letras minúsculas.
SELECT LOWER(campo) FROM nome_da_tabela;
UPPER
Também podemos fazer com que as palvras sejam exibidas em letras maiúsculas.
SELECT UPPER(‘Clayton Ultrago’);
O resultado exibido será CLAYTON ULTRAGO .
Da mesma forma se quisermos fazer o mesmo com um campo.
SELECT UPPER(campo) FROM nome_da_tabela;
REVERSE
A função REVERSE lê o texto de trás pra frente, exatamente como ele foi escrito.
SELECT REVERSE(‘clayton’);
O resultado será: notyalc;
SELECT REVERSE(campo) FROM nome_da_tabela;
RIGHT
SELECT RIGHT('CLAYTON',3);
A função RIGHT começa a ler o resultado da direita para esquerda, e o valor 3
indica quantos caracteres serão capturados da direita para esquerda, no exemplo
acima, TON. Que corresponde aos 3 últimos caraceteres da direita para esquerda.
SELECT RIGHT(campo,quantidade_caracteres) FROM nome_da_tabela;
SUBSTRING
Retorna um trecho da palavra.
Exemplos:
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
-> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
-> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
-> 'ki'
LEFT
SELECT LEFT('CLAYTON',3);
A função LEFT funciona de forma inversa a função RIGHT. Começa a ler o
resultado da esquerda para direita, e o valor 3 indica quantos caracteres serão
capturados da esquerda para direita, no exemplo acima, CLA. Que corresponde aos
3 últimos caraceteres da esquerda para direita.
SELECT LEFT(campo,quantidade_caracteres) FROM nome_da_tabela;
LTRIM
Retorna a palavra removendo espaços em brancos à esquerda.
SELECT LTRIM(' barbearia');
Resultado: 'barbearia'
SELECT LTRIM(campo) from nome_da_tabela;
RTRIM
Retorna a palavra removendo espaços em brancos à direita.
SELECT RTRIM('Alo ');
Resultado: 'Alo'
SELECT RTRIM(campo) from nome_da_tabela;