MySQL: Inserção e Seleção em Tabelas

(1)

MySQL:

Inserção e Seleção em Tabelas

Programação de Servidores Marx Gomes Van der Linden http://marx.vanderlinden.com.br/

(2)

http://marx.vanderlinden.com.br/ 2

INSERT INTO

 Para inserir novas entradas (linhas) em um tabela, usa-se INSERT INTO.

 Sintaxe:

 INSERT INTO tabela [(campo1,

campo2...)] VALUES (valor1, valor2...)

(3)

Exemplo

mysql> INSERT INTO usuario VALUES ('sawyer', 'James', 'Ford', 'masc');

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM usuario;

(4)

Exemplo

mysql> INSERT INTO usuario (ultimo_nome,

login, sexo) VALUES ('Eko', 'mreko', 'masc');

(5)

INSERT INTO

 É possível inserir mais de uma linha em um mesmo comando INSERT INTO.

 Cada linha da tabela deve estar contida em (), com os agrupamentos separados por vírgulas.

(6)

Exemplo

mysql> INSERT INTO usuario VALUES ('hurley', 'Hugo', 'Reyes', 'masc'), ('sun', 'Sun-Hwa', 'Kwon', 'fem');

Query OK, 2 rows affected (0.10 sec)

Records: 2 Duplicates: 0 Warnings: 0

(7)

INSERT INTO ... SET

 A sintaxe INSERT INTO ... SET fornece uma maneira alternativa de se especificarem os campos para os quais se querem inserir

valores.

 INSERT INTO tabela [(campo1,

(8)

Exemplo

mysql> INSERT INTO usuario SET login = 'walt', primeiro_nome = 'Walter', sexo = 'masc';

(9)

LOAD DATA INFILE

 O comando LOAD DATA INFILE insere linhas em uma tabela de acordo com os valores lidos a partir de um arquivo-texto externo.

 O arquivo deve conter uma entrada por linha, com campos separados por tabulação.

 Valores nulos → \N  Sintaxe:

 LOAD DATA [LOCAL] INFILE arquivo

INTO TABLE tabela ;

 O parâmentro LOCAL indica que o arquivo está no cliente.

(10)

Exemplo: novos.txt

desmond Desmond David Hume masc paulo Paulo \N masc

(11)

Exemplo

mysql> LOAD DATA LOCAL INFILE

'/home/marx/novos.txt' INTO TABLE usuario;

Query OK, 3 rows affected (0.00 sec)

Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql> SELECT * FROM usuario;

(12)

mysqlimport

 A aplicação mysqlimport fornece uma maneira de importar arquivos de texto em tabelas.

 Sintaxe:

 mysqlimport [OPÇÕES] [--local]

nome_bd arquivo

 O nome da tabela é deduzido a partir do nome do arquivo de texto.

 As opções de acesso e autenticação são as mesmas utilizadas por mysql.

(13)

Expressões

 Expressões SQL são formadas por uma combinação de operadores, operandos e funções embutidas.

 Operadores aritméticos:  + - * /

 Precedência similar à tipicamente encontrada em linguagens de

programação.

(14)

Expressões

mysql> SELECT 3+4*5+6; +---+ | 3+4*5+6 | +---+ | 29 | +---+

1 row in set (0.00 sec) mysql> SELECT (3+4)*5+6; +---+ | (3+4)*5+6 | +---+ | 41 | +---+

(15)

Operadores

 Operadores de comparação de igualdade  =

 Serve para todos os tipos de variáveis  Faz as conversões automaticamente,

sempre que possível.  <=>

 Funciona como <=>, mas entende comparações entre valores NULL

(16)

Expressões

mysql> SELECT 1 = 0 \G

************** 1. row ************** 1 = 0: 0

1 row in set (0.00 sec) mysql> SELECT '0' = 0 \G

************** 1. row ************** '0' = 0: 1

1 row in set (0.00 sec)

mysql> SELECT '0.0' = 0 \G

************** 1. row ************** '0.0' = 0: 1

(17)

Expressões

mysql> SELECT NULL = 0 \G

************** 1. row ************** NULL = 0: NULL

mysql> SELECT NULL = NULL \G

************** 1. row ************** NULL = NULL: NULL

mysql> SELECT NULL <=> 0 \G

************** 1. row ************** NULL <=> 0: 0

mysql> SELECT NULL <=> NULL \G

************** 1. row ************** NULL <=> NULL: 1

(18)

Operadores

 Operadores de desigualdade:  <>, !=

 Operadores de comparação numérica:  <=, <, >, >=

 Operadores de comparação com NULL:  IS [NOT] NULL

 Operadores de comparação com booleano:  IS [NOT] (TRUE | FALSE)

(19)

Expressões

 expr [NOT] BETWEEN a AND b

 Retorna 1 se expr >= a e expr <= b; 0 do contrário

 Use NOT para inverter o resultado  IF(cond, expr1, expr2)

 Retorna expr1 se cond for verdadeira, expr2, do contrário.

(20)

Expressões

mysql> SELECT 3+2 BETWEEN 5 AND 10 \G ************** 1. row ************** 3+2 BETWEEN 5 AND 10: 1

mysql> SELECT 2*6 BETWEEN 5 AND 10 \G ************** 1. row ************** 2*6 BETWEEN 5 AND 10: 0

mysql> SELECT IF(4*10<100/2, 'menor', 'maior')

\G

************** 1. row **************

IF(4*10<100/2, 'menor', 'maior'): menor

(21)

CASE

 A estrutura CASE pode servir para comparar um valor com vários outros:

 CASE n

WHEN valor1 THEN resultado1 [WHEN valor2 THEN resultado2 ...] [ELSE resultado_padrao]

(22)

CASE

mysql> SELECT CASE 8*9

-> WHEN 70 THEN 'errado'

-> WHEN 71 THEN 'errado também' -> WHEN 72 THEN 'certo'

-> ELSE 'tente novamente' -> END \G

************** 1. row **************

CASE 8*9 WHEN 70 THEN 'errado' WHEN 71 THEN 'errado também' WHEN 72 THEN 'certo' ELSE

'tente novamente' END: certo

(23)

CASE

 Também é possível usar CASE sem um valor de n para comparar. Nesse caso, testam-se várias expressões.

 CASE

WHEN valor1 THEN resultado1 [WHEN valor2 THEN resultado2 ...] [ELSE resultado_padrao]

(24)

CASE

mysql> SELECT CASE

-> WHEN 10<9 THEN 'primeiro' -> WHEN 7='7' THEN 'segundo' -> WHEN 3>2 THEN 'terceiro' -> ELSE 'tente novamente' -> END \G

************** 1. row **************

CASE WHEN 10<9 THEN 'primeiro' WHEN 7='7' THEN 'segundo' WHEN 3>2 THEN 'terceiro' ELSE 'tente novamente' END: segundo

(25)

Funções usadas em Expressões

 LEAST (valor1, valor2, ...)

 GREATEST (valor1, valor2, ...)  Retorna o menor ou maior valor da lista.  expr IN (valor1, valor2, ...)

 Retorna 1 se a expressão estiver contida na lista, 0, do contrário

(26)

CASE

mysql> SELECT LEAST(5,45,83,18.5) \G ************** 1. row ************** LEAST(5,45,83,18.5): 5

mysql> SELECT GREATEST('goomba', 'shyguy', 'koopa') \G

************** 1. row **************

GREATEST('goomba', 'shyguy', 'koopa'): shyguy 1 row in set (0.00 sec)

mysql> SELECT 8 IN (7.8, 7.9, 8.0, 8.1) \G ************** 1. row **************

8 IN (7.8, 7.9, 8.0, 8.1): 1

(27)

SELECT

 O comando SELECT também serve para extrair informações armazenadas no banco de dados.  É possivelmente o comando mais complexo e

poderoso da linguagem SQL.  Sintaxe (simplificada):

 SELECT <campos> FROM tabela

[WHERE condições]

(28)

SELECT

mysql> SELECT * FROM sgbd;

+----+---+---+---+---+ | id | nome | maxcols | lanc | livre | +----+---+---+---+---+ | 1 | MySQL | 3398 | 1996 | sim | | 2 | PostgreSQL | 1600 | 1989 | sim | | 3 | Oracle | 1000 | 1979 | nao | | 4 | MS SQL Server | 1024 | 1989 | nao | | 5 | MS Acess | 255 | 1992 | nao | | 6 | SQLite | 2000 | 2000 | sim | +----+---+---+---+---+

(29)

SELECT

mysql> SELECT nome,lanc FROM sgbd;

+---+---+ | nome | lanc | +---+---+ | MySQL | 1996 | | PostgreSQL | 1989 | | Oracle | 1979 | | MS SQL Server | 1989 | | MS Acess | 1992 | | SQLite | 2000 | +---+---+

(30)

SELECT

mysql> SELECT livre,lanc,nome FROM sgbd;

+---+---+---+ | livre | lanc | nome | +---+---+---+ | sim | 1996 | MySQL | | sim | 1989 | PostgreSQL | | nao | 1979 | Oracle | | nao | 1989 | MS SQL Server | | nao | 1992 | MS Acess | | sim | 2000 | SQLite | +---+---+---+

(31)

SELECT

mysql> SELECT livre,lanc,livre,nome

FROM sgbd;

+---+---+---+---+ | livre | lanc | livre | nome | +---+---+---+---+ | sim | 1996 | sim | MySQL | | sim | 1989 | sim | PostgreSQL | | nao | 1979 | nao | Oracle | | nao | 1989 | nao | MS SQL Server | | nao | 1992 | nao | MS Acess | | sim | 2000 | sim | SQLite | +---+---+---+---+

(32)

SELECT ... WHERE

 Após a cláusula WHERE, é possível especificar uma condição envolvendo uma ou mais

colunas.

 Apenas as linhas em que a condição é válida serão exibidas.

 A condição é formatada como uma

(33)

SELECT ... WHERE

mysql> SELECT nome, lanc FROM sgbd -> WHERE lanc > 1990; +---+---+ | nome | lanc | +---+---+ | MySQL | 1996 | | MS Acess | 1992 | | SQLite | 2000 | +---+---+

(34)

SELECT ... WHERE

mysql> SELECT * FROM sgbd

-> WHERE lanc BETWEEN 1980 AND 1989;

+----+---+---+---+---+ | id | nome | maxcols | lanc | livre | +----+---+---+---+---+ | 2 | PostgreSQL | 1600 | 1989 | sim | | 4 | MS SQL Server | 1024 | 1989 | nao | +----+---+---+---+---+

(35)

SELECT ... WHERE

-> WHERE lanc > maxcols;

+----+---+---+---+---+ | id | nome | maxcols | lanc | livre | +----+---+---+---+---+ | 2 | PostgreSQL | 1600 | 1989 | sim | | 3 | Oracle | 1000 | 1979 | nao | | 4 | MS SQL Server | 1024 | 1989 | nao | | 5 | MS Acess | 255 | 1992 | nao | +----+---+---+---+---+ 4 rows in set (0.00 sec)

(36)

Operadores Booleanos

 É possível usar operadores booleanos em expressões:

 OR, II  AND, &&  NOT, !  XOR

(37)

Operadores Booleanos

-> WHERE lanc > 1990 AND livre = 'sim';

+----+---+---+---+---+ | id | nome | maxcols | lanc | livre | +----+---+---+---+---+ | 1 | MySQL | 3398 | 1996 | sim | | 6 | SQLite | 2000 | 2000 | sim | +----+---+---+---+---+ 2 rows in set (0.00 sec)

(38)

Operadores Booleanos

-> WHERE maxcols > 2000 OR livre = 'nao';

+----+---+---+---+---+ | id | nome | maxcols | lanc | livre | +----+---+---+---+---+ | 1 | MySQL | 3398 | 1996 | sim | | 3 | Oracle | 1000 | 1979 | nao | | 4 | MS SQL Server | 1024 | 1989 | nao | | 5 | MS Acess | 255 | 1992 | nao | +----+---+---+---+---+ 4 rows in set (0.00 sec)

(39)

Operadores Booleanos

mysql> SELECT * FROM usuario -> WHERE sexo = 'fem';

+---+---+---+---+ | login | primeiro_nome | ultimo_nome | sexo | +---+---+---+---+ | kate | Katherine Anne Austen | NULL | fem | | sun | Sun-Hwa | Kwon | fem | | rousseau | Danielle | Rousseau | fem | +---+---+---+---+

(40)

Operadores Booleanos

mysql> SELECT * FROM usuario -> WHERE sexo = 'fem'

-> AND ultimo_nome IS NOT NULL;

+---+---+---+---+ | login | primeiro_nome | ultimo_nome | sexo | +---+---+---+---+ | sun | Sun-Hwa | Kwon | fem | | rousseau | Danielle | Rousseau | fem | +---+---+---+---+

(41)

SELECT ... ORDER BY

 Para ordenar os resultados retornados por um select, basta anexar o parâmentro ORDER BY.  Sintaxe:

 SELECT (...)

ORDER BY campo1 [DESC|ASC]

(42)

SELECT ... ORDER BY

mysql> SELECT * FROM usuario ORDER BY login;

(43)

SELECT ... ORDER BY

mysql> SELECT sexo, login, primeiro_nome -> FROM usuario ORDER BY sexo;

(44)

SELECT ... ORDER BY

mysql> SELECT sexo, login, primeiro_nome

-> FROM usuario ORDER BY sexo DESC, login;

(45)

SELECT ... LIMIT

 Para limitar o número de linhas retornadas por um SELECT, basta utilizar o parâmetro LIMIT.  Sintaxe:

 SELECT (...)

LIMIT [atraso, ] nlinhas

 atraso indica o número de linhas a pular antes de iniciar a contagem

 Padrão: 0

(46)

SELECT ... LIMIT

mysql> SELECT login, primeiro_nome -> FROM usuario ORDER BY login;

(47)

SELECT ... LIMIT

mysql> SELECT login, primeiro_nome -> FROM usuario ORDER BY login -> LIMIT 5;

(48)

SELECT ... LIMIT

(49)

SELECT DISTINCT

 A palavra DISTINCT indica que o select deve retornar todos os valores não repetidos de uma coluna ou combinação de colunas.

 Sintaxe:

 SELECT DISTINCT campo1

(50)

SELECT ... LIMIT

mysql> SELECT * FROM exemplo;

+---+---+---+ | x | y | z | +---+---+---+ | a | b | c | | a | b | d | | a | b | e | | i | j | d | | i | j | e | | i | j | k | | i | z | k | +---+---+---+

(51)

SELECT ... LIMIT

mysql> SELECT DISTINCT x FROM exemplo;

+---+ | x | +---+ | a | | i | +---+

(52)

SELECT ... LIMIT

mysql> SELECT DISTINCT x,y FROM exemplo;

+---+---+ | x | y | +---+---+ | a | b | | i | j | | i | z | +---+---+

(53)

COUNT, GROUP BY

 A função COUNT pode ser usada em um

SELECT para contabilizar a quantidade de

resultados encontrados.  Sintaxe:

 SELECT COUNT(colunas...)

(...)

(54)

SELECT COUNT

mysql> SELECT COUNT(*) FROM usuario -> WHERE sexo = 'masc';

+---+ | COUNT(*) | +---+ | 8 | +---+

(55)

SELECT COUNT .. GROUP BY

mysql> SELECT sexo, COUNT(*) -> FROM usuario -> GROUP BY sexo; +---+---+ | sexo | COUNT(*) | +---+---+ | masc | 8 | | fem | 3 | +---+---+

(56)

Exemplo: Cidades do Brasil

mysql> SELECT * FROM cidade;

+----+---+---+---+ | id | nome | estado | populacao | +----+---+---+---+ | 1 | Campo Grande | MS | 724524 | | 2 | Porto Alegre | RS | 1420667 | | 3 | João Pessoa | PB | 674762 | | 4 | Fortaleza | CE | 2431415 | | 5 | Belo Horizonte | MG | 2412937 | | 6 | Salvador | BA | 2892625 | | 8 | Teresina | PI | 779939 | | 9 | Natal | RN | 774230 | | 10 | Nova Iguaçu | RJ | 830672 | | 11 | São Bernardo do Campo | SP | 781390 | | 12 | São Paulo | SP | 10886518 | | 13 | Osasco | SP | 701012 | | 14 | Belém | PA | 1408847 | | 17 | Duque de Caxias | RJ | 842686 |

(57)

Exemplo: Cidades do Brasil

mysql> SELECT DISTINCT estado FROM cidade;

+---+ | estado | +---+ | MS | | RS | | PB | | CE | | MG | | BA | | PI | | RN | | RJ | | SP | | PA | | PR | | AM | | PE | +---+

(58)

Exemplo: Cidades do Brasil

mysql> SELECT estado, COUNT(estado) -> FROM cidade GROUP BY estado;

+---+---+ | estado | COUNT(estado) | +---+---+ | AM | 1 | | BA | 1 | | CE | 1 | | MG | 1 | | MS | 1 | | PA | 1 | | PB | 1 | | PE | 1 | | PI | 1 | | PR | 1 | | RJ | 4 | | RN | 1 | | RS | 1 | | SP | 5 | +---+---+

(59)

Exemplo: Cidades do Brasil

mysql> SELECT estado, COUNT(estado) -> FROM cidade GROUP BY estado -> ORDER BY COUNT(estado) DESC;

+---+---+ | estado | COUNT(estado) | +---+---+ | SP | 5 | | RJ | 4 | | PE | 1 | | CE | 1 | | MG | 1 | | BA | 1 | | PI | 1 | | RN | 1 | | PA | 1 | | MS | 1 | | PR | 1 | | RS | 1 | | AM | 1 | | PB | 1 | +---+---+ 14 rows in set (0.00 sec)

(60)

Exemplo: Cidades do Brasil

mysql> SELECT estado, COUNT(estado) -> FROM cidade GROUP BY estado -> ORDER BY COUNT(estado) DESC;

+---+---+ | estado | COUNT(estado) | +---+---+ | SP | 5 | | RJ | 4 | | PE | 1 | | CE | 1 | | MG | 1 | | BA | 1 | | PI | 1 | | RN | 1 | | PA | 1 | | MS | 1 | | PR | 1 | | RS | 1 | | AM | 1 | | PB | 1 | +---+---+ 14 rows in set (0.00 sec)

(61)

AVG, SUM

 As funções AVG e SUM podem ser usadas em conjunto com GROUP BY:

 AVG(coluna)

 Retorna a média dos valores do campo.  SUM(coluna)

(62)

Exemplo: Cidades do Brasil

mysql> SELECT estado, COUNT(estado), -> SUM(populacao), AVG(populacao) -> FROM cidade GROUP BY estado

-> ORDER BY COUNT(estado) DESC LIMIT 10;

+---+---+---+---+ | estado | COUNT(estado) | SUM(populacao) | AVG(populacao) | +---+---+---+---+ | SP | 5 | 14644409 | 2928881.8000 | | RJ | 4 | 8727461 | 2181865.2500 | | RS | 1 | 1420667 | 1420667.0000 | | BA | 1 | 2892625 | 2892625.0000 | | PE | 1 | 1533580 | 1533580.0000 | | PB | 1 | 674762 | 674762.0000 | | PI | 1 | 779939 | 779939.0000 | | PA | 1 | 1408847 | 1408847.0000 | | CE | 1 | 2431415 | 2431415.0000 | | RN | 1 | 774230 | 774230.0000 | +---+---+---+---+