DATA MANAGEMENT

Big Data & Data Science

Jonatha Azevedo

ROTEIRO DA AULA

  1. Motivação
  2. Database Management Systems
    • Funções
    • Diagramas Entidade-Relacionamento
    • Bancos de Dados Relacionais
    • Opções no Mercado
  3. Introdução ao SQL
    • Linguagens de Programação Não-Procedurais
    • Vantagens de Utilizar SQL
  4. Comandos SQL
    • Criação de Banco e Tabelas
    • Preenchimento de Tabelas
    • Alteração e Remoção de Tabelas
  5. Utilização do MySQL Workbench
  6. Integração do MySQL com o R
  7. Exercícios

 

MOTIVAÇÃO

O que vamos estudar?

Banco de Dados Relacionais

Conjunto de Tabelas interligadas e pertencentes a um mesmo campo semântico.

MOTIVAÇÃO

O que vamos estudar?

Database Management Systems

Softwares que tornam os processos de busca, armazenamento e carregamento dos bancos de dados transparentes para o usuário.

Database

DBMS

API

API

API

APP

APP

USER

MOTIVAÇÃO

O que vamos estudar?

SQL (Structured Query Language)

Linguagens de programação simples, projetada para enviar comandos essenciais aos DBMSs.

SQL query

Query Language Processor

DBMS Engine

Parser + Optimizer

File Manager

+

Transaction manager

Physical Database

MOTIVAÇÃO

Por que vamos estudar?

BDs relacionais, DBMSs e SQL são peças fundamentais do mundo dos softwares direcionados ao processamento e análise de dados.

São conhecimentos indispensáveis a qualquer cientista de dados

MOTIVAÇÃO

Por que vamos estudar?

  • SQL: pré-requisito para aplicações em NoSQL;
  • SGBDs possuem características desejáveis para manipulação de grandes massas de dados:

Persistência

Compartilhamento

Representação de Interrelações

Agilidade

DATABASE MANAGEMENT SYSTEMS

CRIAÇÃO DE BASES

ACESSO AOS DADOS

MANUTENÇÃO

organização

armazenamento

recuperação

aquisição

disseminação

recuperação

aquisição

disseminação

PERGUNTA

Qual a diferença entre um DBMS e um processador de texto ou gerenciador de planilhas?

Funções

DATABASE MANAGEMENT SYSTEMS

Resposta

Em DBMSs, a forma como os dados se relacionam pode e deve ser representada. Além disso, em planilhas e arquivos de texto não há controle sobre o que pode ser inserido ou quem tem acesso. Não são sistemas seguros.

Funções

Organização é interessante porque:

  1. Facilita o acesso por parte do pesquisador;
  2. Agiliza a busca pela máquina;
  3. Assegura a integridade e consistência dos dados.

Mas como podemos construir esses modelos?

DATABASE MANAGEMENT SYSTEMS

Diagramas ER

Através de Diagramas Entidade-Relacionamento

(ER)

É importante que estes diagramas sejam produzidos antes de montar a base no DBMS

DATABASE MANAGEMENT SYSTEMS

Diagramas ER

A exigência destes modelos prévios não é um exagero.

Diagramas ER muito simples são exceção. Ainda mais no mundo de Big Data.

Vamos examiná-los mais de perto!

DATABASE MANAGEMENT SYSTEMS

Diagramas ER

Suponha que você queira montar uma base de dados para o seu blog. As tabelas poderiam ser:

Autores

nome, sobrenome, apelido

Categorias

nome, descrição

Posts

títulos,texto,data,configs

Tags

nome, descrição

Comentários

usuário, data, texto

Usuários

nome, sobrenome, apelido
Nome da tabela
Exemplos de colunas

DATABASE MANAGEMENT SYSTEMS

Diagramas ER

Faltam muitas informações sobre as relações!

Autores

nome, sobrenome, apelido

Categorias

nome, descrição

Posts

títulos,texto,data,configs

Tags

nome, descrição

Comentários

usuário, data, texto

Usuários

nome, sobrenome, apelido
Cardinalidade?
Direção?
Posts Relacionados?

Diagrama Entidade-Relacional

N

1

N

1

blog_post não existe sem blog_author

blog_comment não existe sem blog_post

blog_tag não existe sem blog_post

Primary KEY

Foreign KEY

DATABASE MANAGEMENT SYSTEMS

Bancos de Dados Relacionais

Como representamos estas relações nas tabelas em si?

title article author
Loren ipsun Nulla id augue eget nisi ornare ultrices (...) 2
Dolor Sit Praesent consectetur malesuada  (...) 1
Donec Vulputabe Proin congue, mi id sollicitudin rhoncu (...) 1
Maecenas orci Fusce augue risus (...) 2
id first_name last_name
1 João Silva
2 Maria Santos

Foreign Key

(chave estrangeira)

Foreign Key

(chave primária)

DATABASE MANAGEMENT SYSTEMS

Principais opções no mercado

Nossa Opção

DATABASE MANAGEMENT SYSTEMS

Por que usaremos o MySQL?

SQL Server:

  • Excelente sistema de clusters e da partição de dados;
  • Compatível com o padrão ANSI/SQL;
  • Não é Open Source;
  • A versão gratuita disponível é a de desenvolvedor. Pode conter bugs e não pode ser usada em ambiente de produção.

Oracle:

  • Mesmas vantagens do SQL Server;
  • Não é Open Source;
  • A versão gratuita limita o tamanho das bases em 11 GB.

PostgreSQL:

  • Considerado o SGBD Open Source mais avançado do mercado;
  • Amplamente extensível;
  • Pode ter performance pior que o MySQL em aplicações que dependem somente de operações de leitura.

DATABASE MANAGEMENT SYSTEMS

Por que usaremos o MySQL?

MySQL:

  • Vantagem sobre o PostgreSQL em operações de leitura;
  • Gratuito/Open Source;
  • Conta com o suporte da gigante Oracle;
  • Maior comunidade de usuários.

POPULAR

ÁGIL

ESCALÁVEL

GRATUITO 

INTEGRÁVEL AO R

SQL

Structured Query Language

  • Linguagem de programação de domínio específico e não procedural;
  • Padrão da indústria (loose standard) para fazer interface com todas as funções básicas dos DBMSs;

Somente as partes mais fundamentais das declarações em SQL funcionam em todos os DBMSs - há inúmeras extensões;

  • Há diversas funções extras nos DBMSs, mas elas não são portáveis como os comandos SQL.

Diagramas

SQL

Structured Query Language

  • Linguagens não procedurais não requerem muitos conhecimentos de programação;
  • Apenas a especificação de QUAIS estruturas serão acessadas é necessária. Não é preciso dizer COMO o acesso será executado;

Diminui o número de linhas de código em DUAS ordens de grandeza!

Não é necessário escrever loops ou condições (if, for, while)

Para aplicações intensivas em dados, o ganho em produtividade e qualidade é incalculável

SQL

Structured Query Language

  • Há outra forma de fazer acessos não procedurais: através da interface gráfica;

Depende do BDMS, ou seja, não é portável.

  • As linguagens não procedurais são limitadas e não oferecem, muitas vezes, o nível de controle desejado.

Transact-SQL (Microsoft), PL/SQL (Oracle)

COMANDOS SQL

Criação de uma base de dados

CREATE    {DATABASE | SCHENA}   [IF NOT EXISTS]   db_name;

OBRIGATÓRIO

ALTERNATIVAS

OPCIONAL

USER-DEFINED

-- CRIANDO A BASE DE DADOS 'COMPANY'

CREATE SCHEMA company;

COMANDOS SQL

Criação de tabelas

Criaremos duas tabelas da base company no MySQL Workbench, usando SQL, passo a passo.

DEPT_ID DEPT_NAME
1 Contabilidade
2 Marketing
3 Vendas

A coluna DEPT_ID contém o identificador único (primary key) de cada linha (cada departamento)

Núm. inteiro

Cadeia de caracteres

Tabela Departament

COMANDOS SQL

Criação de tabelas

A coluna EMP_ID contém o identificador único (primary key) de cada linha (cada empregado)

EMP_ID EMP_NAME EMP_AGE EMP_SALARY DEPT_ID
1 João 23 10000 1
2 Maria 24 7000 2
3 José 25 8000 3
4 Ana 27 6000 1
5 Antônio 28 5000 2

Núm.

inteiro

Cadeia de

caracteres

Núm.

inteiro

Núm.

real

Núm.

inteiro

Tabela Employee

COMANDOS SQL

Criação de tabelas

EMP_ID EMP_NAME EMP_AGE EMP_SALARY DEPT_ID
1 João 23 10000 1
2 Maria 24 7000 2
3 José 25 8000 3
4 Ana 27 6000 1
5 Antônio 28 5000 2

Tabela Employee

DEPT_ID DEPT_NAME
1 Contabilidade
2 Marketing
3 Vendas

Tabela Departament

Chave estrangeira

(foreign key)

Chave primária

(primary key)

Atenção: não é necessário que as duas chaves tenham o mesmo nome.

COMANDOS SQL

Criação de tabelas

CREATE TABLE <table-name> ( <column-list> [ <constraint-list> ] )

Em <column-list> definimos TIPOS, que podem ser:

  • CHAR(L)
  • VARCHAR(L)
  • INTEGER
  • FLOAT(P)
  • DECIMAL(I,P)
  • DATE
  • DATETIME
  • TIMESTAMP

COMANDOS SQL

Criação de tabelas

CREATE TABLE <table-name> ( <column-list> [ <constraint-list> ] )

Em <constraint-list> contém RESTRIÇÕES, tais como:

  • PRIMARY KEY
  • UNIQUE
  • FOREIGN KEY
  • NOT NULL
  • CHECK

COMANDOS SQL

Criação de tabelas

CREATE TABLE <table-name> ( <column-list> [ <constraint-list> ] )

-- CRIANDO A TABELA Department

CREATE TABLE company.Department(
    dept_id integer,
    dept_name varchar(100) not null,
    CONSTRAINT DeptPK PRIMARY KEY (dept_id)
);

COMANDOS SQL

Criação de tabelas

CREATE TABLE <table-name> ( <column-list> [ <constraint-list> ] )

-- CRIANDO A TABELA Employee

CREATE TABLE company.Employee(
    emp_id integer,
    emp_name varchar(100) not null,
    emp_age integer,
    emp_salary float not null,
    dept_id integer,    
    CONSTRAINT EmployeePK PRIMARY KEY (emp_id),
    CONSTRAINT EmployeeFK FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

COMANDOS SQL

Preenchimento de tabelas

INSERT [ INTO ] tbl_name [ (col_name,...) ]

{ VALUES | VALUE }

-- Inserir linha [ 15 30 ] na colunas 1 e 2 

INSERT INTO tbl_name (col1,col2) VALUES(15, col2*2);

-- Inserir linha [ 1 2 3 ] , [ 4 5 6 ], [ 7 8 9 ] na colunas a, b e c

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

COMANDOS SQL

Preenchimento de tabelas

INSERT [ INTO ] tbl_name [ (col_name,...) ]

{ VALUES | VALUE }

INSERT INTO company.Department (dept_name, dept_id) 
VALUES ("Contabilidade",1),
       ("Marketing",2),
       ("Vendas",3);

INSERT INTO company.Employee (emp_id, emp_name, emp_age, emp_salary, dept_id) 
VALUES(1,"João",23,10000.00,1),
      (2,"Maria",24,7000.00,2),
      (3,"José",25,8000.00,3),
      (4,"Ana",27,6000,1),
      (5,"Antônio",28,5000,2);

COMANDOS SQL

Preenchimento de tabelas

  • INSERT é um comando lento. Ele averigua se todos os valores da linha estão consistentes, isto é, não violam nenhuma restrição de integridade. Caso algum viole, nenhum valor é inserido na linha;
  • Em seu wizard de importação, o Workbench executa um INSERT por linha do arquivo de dados. É um processo bastante demorado e, caso o arquivo esteja corrompido em algum ponto, as linhas não corrompidas serão importadas de qualquer forma, deixando a tabela inconsistente;
  • Para poupar tempo e evitar importar dados de arquivos corrompidos, usamos o comando LOAD DATA. Ele não utiliza INSERT e não importa nenhuma linha caso o arquivo tenha problemas.

COMANDOS SQL

Preenchimento de tabelas

parâmetro opcional, mas TEM que ser usado caso se esteja lendo um arquivo da sua própria máquina (nosso caso)

LOAD DATA [LOCAL] INFILE 'file_name'

INTO TABLE tbl_name

[{FIELDS| COLUMNS}[ TERMINATED BY 'string' ] [[OPTIONALLY]

ENCLOSED BY 'char']

[ESCAPED BY 'char']]

[LINES [STARTING BY 'string'] [TERMINATED BY 'string']]

[IGNORE number {LINES | ROWS}]

 

CUIDADO!

As aspas são importantes

COMANDOS SQL

Preenchimento de tabelas

LOAD [LOCAL] INFILE 'file_name' {...}

Suponha que você tenha um arquivo contendo os dados ao lado. Uma tabela com as colunas 'valor' e 'moeda' já foi criada no MySQL, mas está vazia e será preenchida com os valores do arquivo, que segue as seguintes regras de sintaxe

# -> nova linha

$ -> separação de campos

" " -> contêm cadeias de caracteres

\ -> caracter de escape

Valor Moeda
30.25 UAD
59.60 US$
635.39 R$
426.52 AUD
3.2 US$
54.05 R$

COMANDOS SQL

Preenchimento de tabelas

LOAD [LOCAL] INFILE 'file_name' {...}

LOAD DATA LOCAL INFILE "caminho\\para\\arquivo"
INTO TABLE nome_da_tabela
FIELDS TERMINATED BY '$'
ENCLOSED BY '"'
ESCAPED BY "\\"
LINES STARTING BY '#'
IGNORE 4 LINES;

#arquivo de dados genérico

#as regras de sintaxe não seguem qualquer padrão oficial

#

#valor$moeda

#30.25$"AUD"

#59.60$"US\$"

#539.39$"R\$"

#426.52$"AUD"

#3.2"US\$"

#54.05$"R\$"

COMANDOS SQL

Preenchimento de tabelas

LOAD [LOCAL] INFILE 'file_name' {...}

LOAD DATA LOCAL INFILE "caminho\\para\\arquivo"
INTO TABLE nome_da_tabela
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 2 LINES;

Seu arquivo poderia ser um .csv, como abaixo. Nesse caso, as regras de sintaxe são outras:

arquivo .csv padrão \n

 

valor,moeda\n

30.25,"AUD"\n

29.60,"US$"\n

653.39,"R$"\n

426.52,"AUD"\n

3.2,"US$"\n

54.05,"R$"\n

COMANDOS SQL

Consulta aos dados

Para fazer buscas no banco, utilizamos o comando SELECT. Suas várias cláusulas serão examinadas uma a uma.

SELECT [ DISTINCT | * ]  [ AS alias ]

   [ FROM table_names 

      [ WHERE where_condition ]

      [ GROUP BY {col_name} [ASC | DESC], ... ]

      [ ORDER BY {col_name} [ASC | DESC], ... ]

      [ INTO OUTFILE 'file_name' ]

Há mais cláusulas além de WHERE, GROUP BY, ORDER BY e INTO OUTFILE, mas essas são certamente as mais usadas.

COMANDOS SQL

Consulta aos dados

SELECT [ DISTINCT | * ] { ... }

-- CONSULTAR TODAS AS LINHAS

SELECT * FROM Employee

-- CONSULTAR TODAS AS LINHAS

SELECT * FROM Department
EMP_ID EMP_NAME EMP_AGE EMP_SALARY DEPT_ID
1 João 23 10000 1
2 Maria 24 7000 2
3 José 25 8000 3
4 Ana 27 6000 1
5 Antônio 28 5000 2
DEPT_ID DEPT_NAME
1 Contabilidade
2 Marketing
3 Vendas

COMANDOS SQL

Consulta aos dados

SELECT [ DISTINCT | * ] { ... }

WHERE e AS

Quem são os empregados com mais de 25 anos?

Quantos anos eles tem?


SELECT emp_name AS NAME, emp_age AS AGE 
FROM company.Employee 
WHERE emp_age > 25;

Enquanto SELECT filtra colunas, WHERE filtra linhas.

NAME AGE
Ana 27
Antônio 28

COMANDOS SQL

Consulta aos dados

SELECT [ DISTINCT | * ] { ... }

WHERE e AS

Quem são os empregados cujo nome começa com a letra J?


SELECT emp_name
FROM company.Employee
WHERE emp_name LIKE 'J%'

O caracter '%' é chamado de wildcard. Indica que qualquer cadeia de caracteres pode entrar em seu lugar.

EMP_NAME
João
José

COMANDOS SQL

Consulta aos dados

SELECT [ DISTINCT | * ] { ... }

WHERE e AS

Qual departamento pertence cada empregado?


SELECT e.emp_name, d.dept_name
FROM company.Employee e, company.Department d
WHERE e.dept_id = d.dept_id;

Para entender esta consulta, precisamos ter em mente a ordem que o MySQL avalia comandos deste tipo.

COMANDOS SQL

Consulta aos dados

SELECT [ DISTINCT | * ] { ... }

ORDEM DE AVALIAÇÃO DE UMA QUERY

OPERAÇÕES

DE LINHA

FROM

WHERE

OPERAÇÕES

DE COLUNA

GROUP BY

HAVING

RESULTADOS

ORDER BY

SELECT

COMANDOS SQL

Consulta aos dados

EMP_ID EMP_NAME EMP_AGE EMP_SALARY DEPT_ID
1 João 23 10000 1
2 Maria 24 7000 2
3 José 25 8000 3
4 Ana 27 6000 1
5 Antônio 28 5000 2
DEPT_ID DEPT_NAME
1 Contabilidade
2 Marketing
3 Vendas 

    FROM company.Employee e, company.Department d
    WHERE e.dept_id= d.dept_id

SELECT e.emp_name, d.dept_name

COMANDOS SQL

Consulta aos dados

SELECT [ DISTINCT | * ] { ... }

WHERE e AS

Agora sabemos dizer a qual departamento pertence cada empregado!


    SELECT e.emp_name, d.dept_name
    FROM company.Employee e, company.Department d
    WHERE e.dept_id = d.dept_id;
EMP_NAME DEPT_NAME
João Contabilidade
Ana Contabilidade
Maria Marketing
Antônio Marketing
José Vendas

COMANDOS SQL

Consulta aos dados

SELECT [ DISTINCT | * ] { ... }

GROUP BY

Qual a quantidade de empregado em cada departamento?

    
    SELECT d.dept_name, count(e.emp_name) as NUM_EMPLOYEE
    FROM company.Employee e, company.Department d 
    WHERE e.dept_id = d.dept_id
    GROUP BY d.dept_name ;

COMANDOS SQL

Consulta aos dados

SELECT [ DISTINCT | * ] { ... }

GROUP BY

EMP_ID EMP_NAME EMP_AGE EMP_SALARY DEPT_ID
1 João 23 10000 1
2 Maria 24 7000 2
3 José 25 8000 3
4 Ana 27 6000 1
5 Antônio 28 5000 2
DEPT_ID DEPT_NAME
1 Contabilidade
2 Marketing
3 Vendas 

FROM company.Employee e, 
        company.Department d
WHERE e.dept_id = d.dept_id

COMANDOS SQL

Consulta aos dados

SELECT [ DISTINCT | * ] { ... }

GROUP BY

EMP_ID EMP_NAME EMP_AGE EMP_SALARY DEPT_ID
1 João 23 10000 1
2 Maria 24 7000 2
3 José 25 8000 3
4 Ana 27 6000 1
5 Antônio 28 5000 2 
GROUP BY d.dept_name;

COMANDOS SQL

Consulta aos dados

SELECT [ DISTINCT | * ] { ... }

GROUP BY

EMP_NAME DEPT_NAME
João Contabilidade
Ana Contabilidade
Maria Marketing
Antônio Marketing
José Vendas 
SELECT d.dept_name, 
        count(e.dept_name) as NUM_EMPLOYEE

1

3

2

COMANDOS SQL

Consulta aos dados

SELECT [ DISTINCT | * ] { ... }

GROUP BY

Agora sabemos dizer a quantidade de empregados em cada departamento.


    SELECT d.dept_name, 
        count(e.emp_name) as NUM_EMPLOYEE
    FROM company.Employee e, company.Department d 
    WHERE e.dept_id = d.dept_id
    GROUP BY d.dept_name;
DEPT_NAME NUM_EMPLOYEE
Contabilidade 2
Marketing 2
Vendas 1

COMANDOS SQL

Consulta aos dados

  • Funções de agregação:
    • AVG() - média;
    • COUNT() - número de linhas;
    • FIRST() - primeiro valor;
    • MAX() - valor máximo;
    • MIN() - valor mínimo;
    • SUM() - soma.
  • Todas as colunas que não aparecem nas funções de agregação devem ser incluídas na cláusula GROUP BY.

COMANDOS SQL

Consulta aos dados

SELECT [ DISTINCT | * ] { ... }

GROUP BY

Qual é a média dos salários dos empregados que ganham mais de 5000, por departamento?

    
    SELECT d.dept_name, 
            count(e.emp_name) as NUM_EMPLOYEE, 
            avg(e.emp_salary) as AVG_SALARY 
    FROM Employee e, Department d 
    WHERE e.dept_id = d.dept_id AND emp_salary > 5000 
    GROUP BY d.dept_name;

COMANDOS SQL

Consulta aos dados

SELECT [ DISTINCT | * ] { ... }

GROUP BY

Apenas Antônio, que trabalha no Marketing, não entra na conta, pois ganha exatamente 5000.


    SELECT d.dept_name, count(e.emp_name) as NUM_EMPLOYEE, avg(e.emp_salary) as AVG_SALARY 
    FROM Employee e, Department d 
    WHERE e.dept_id = d.dept_id AND emp_salary > 5000 
    GROUP BY d.dept_name;
DEPT_NAME NUM_EMPLOYEE AVG_SALARY
Contabilidade 1 8000
Marketing 1 7000
Vendas 2 8000

COMANDOS SQL

Consulta aos dados

SELECT [ DISTINCT | * ] { ... }

GROUP BY

Qual o salário de cada empregado, ordenado do mais baixo para o mais alto?

    
    SELECT emp_name, emp_salary
    FROM company.Employee
    ORDER BY emp_salary ASC;
EMP_NAME EMP_SALARY
Antônio 5000
Ana 6000
Maria 7000
José 8000
João 10000

COMANDOS SQL

Modificações de Tabelas

SELECT [ DISTINCT | * ] { ... }

GROUP BY

Qual o salário médio de cada departamento, ordenado do mais baixo para o mais alto?

    
    SELECT d.dept_name, avg(e.emp_salary) as AVG_SALARY
    FROM company.Employee e, company.Department d 
    WHERE e.dept_id = d.dept_id
    GROUP BY d.dept_name
    ORDER BY AVG_SALARY ASC;
DEPT_NAME AVG_SALARY
Marketing 6000
Vendas 8000
Contabilidade 8000

COMANDOS SQL

Modificação de Tabelas

ALTER TABLE tbl_name

ADD [COLUMN] (col_name column_definition, ...)

                       [FIRST | AFTER col_name]|

ADD [CONSTRAINT [symbol]] PRIMARY KEY (col_name, ...)

| ADD [CONSTRAINT [symbol]] FOREIGN KEY (col_name, ...)

                       [REFERENCES] references_definition

| MODIFY [COLUMN] col_name column_definition

                       [FIRST | AFTER col_name]

| DROP [COLUMN] col_name

| DROP PRIMARY KEY col_name

| DROP PRIMARY KEY fk_symbol

COMANDOS SQL

Modificação de Tabelas

ALTER TABLE tbl_name ( ... )

Queremos adicionar mais uma informação à tabela de empregados: o gênero.

   
     ALTER TABLE company.Employee 
    ADD COLUMN (emp_gender varchar(10));
    
    ALTER TABLE company.Employee
    MODIFY COLUMN emp_gender char(1) ;

Após definir o tipo da coluna, mudamos de ideia e alteramos para um tipo de tamanho fixo.

COMANDOS SQL

Modificação de Tabelas

   
    UPDATE company.Employee SET emp_gender = 'M' 
    WHERE emp_name = 'João' OR emp_name = 'José' OR emp_name ='Antônio' ;  
    
    UPDATE company.Employee SET emp_gender = 'F' 
    WHERE emp_name = 'Maria' OR emp_name = 'Ana' ;

UPDATE tbl_name

SET col_name1 = expr [, col_name2 = expr2 ] ... 

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

COMANDOS SQL

Modificação de Tabelas

   
    DELETE FROM company.Department 
    WHERE dept_id = 1 ;

DELETE FROM tbl_name

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint  fails (`company`.`employee`, CONSTRAINT `EmployeeFK` FOREIGN KEY (`dept_id` ) REFERENCES `department` (`dept_id`))

Não conseguimos deletar a linha da tabela Department, porque existe uma chave estrangeira apontando para ela!

COMANDOS SQL

Remoção de tabelas e bases

Drop [IF EXISTS] TABLE tbl_name;

    
    DROP TABLE company.Employee;

    
    DROP TADABASE company;

Drop [IF EXISTS] TABLE DATABEASE tadabase_name;

INTEGRAÇÃO COM O R

  1. Criar um usuário e uma senha para a base;

2. Procurar o nome do host no MySQL:


    SHOW VARIABLES WHERE Variable_name = 'hostname';

3. Instalar o pacote DBI e RMySQL e carregá-lo:

    
    install.packages(c('DBI','RMySQL'))
    library(DBI)
    library(RMySQL)
    


# criando o usuario user.name de senha password
CREATE USER 'user.name'@'%' IDENTIFIED BY 'password';

# garantindo todos os direitos de acesso ao usuario user.name
GRANT ALL ON db_name.table_name TO 'user.name'@'%';

INTEGRAÇÃO COM O R

4. Estabelecer a conexão com a base:

5. Fazer uma consulta


    conn = dbConnect(MySQL(), db = "wdi",  user = "wdiuser", 
            password = 'wdiuser', host = "", port=3306)

6. Desconectar

   
     dbDisconnect(conn)
    
    results <- dbGetQuery(conn, "select CountryCode from Country")
    head(results)
  CountryCode
1         ABW
2         ADO
3         AFG
4         AGO
5         ALB
6         ARB

Importante!

Caso tenha criado um banco no serviço da AWS RDS e não esteja usando, lembre-se de desativá-lo para assim evitar cobranças indevidas. 

Caso queira cancelar a conta feito na AWS, siga o passo a passo no link abaixo:

AWS

Referências

  • Manual de Referência do  MySQL:
  • Bassil, Youssef. A Comparative Study on the Perfomance of the top DBMS Systems. Journal of Computer Science & Research (JCSCR). Vol. 1, No. 1. Fevereiro de 2012.
  • Elmasri, Ramez; Navathe, Shamkant B. Sistemas de Bancos de Dados. 4a Edição. Pearson Education. São Paulo, 2005.

https://dev.mysql.com/doc/refman/5.7/en/sql-syntax.html

INSTRUÇÕES/FAQ

1. Onde estão os arquivos .csv que devem ser carregados para a base Titanic? Na plataforma da infnet.

2. Onde estes arquivos devem ser colocados? Algumas versões do MySQL Workbench só aceitam ler arquivos que estejam em C:\ProgramData\MySQL\MySQL Server 5.7\Uploads.  Atenção: A pasta ProgramData está escondida.  

3. Mesmo colocando os arquivos nesta pasta do item 2, não funcionou. O que fazer? No MySQL Workbench, vá em Edit > Preferences > SQL Editor, desmarque a opção Safe Updatese reconecte-se ao servidor.   Não esqueça de colocar duas barras (\\) no endereço do arquivo que está tentando carregar. Comparar cuidadosamente seu código com os exemplos dos slides também é indicado : )

Lista 1

INSTRUÇÕES/FAQ

4. Não consigo usar o comando LOAD DATA para carregar as tabelas. Todos os arquivos estão corrompidos? Não, nenhum está. Você provavelmente está esquecendo de inserir a palavra LOCAL no comando. Veja os exemplos do LOAD DATA nestes slides.

5. Meu código executou perfeitamente mas há marcas vermelhas do lado das linhas. Tem alguma coisa errada? Não. Esse é um bug conhecido da versão do MySQL Workbench que está instalada nas máquinas do laboratório.

Lista 1

INSTRUÇÕES/FAQ

1. Onde estão os arquivos .csv que devem ser carregados para a base wdi? Na plataformada infnet.  

2. Onde estes arquivos devem ser colocados? Algumas versões do MySQL Workbench só aceitam ler arquivos que estejam em C:\ProgramData\MySQL\MySQL Server 5.7\Uploads.  Atenção: A pasta ProgramData está escondida.  

3. Mesmo colocando os arquivos nesta pasta do item 2, não funcionou. O que fazer? No MySQL Workbench, vá em Edit > Preferences > SQL Editor, desmarque a opção Safe Updatese reconecte-se ao servidor.    Não esqueça de colocar duas barras (\\) no endereço do arquivo que está tentando carregar. Comparar cuidadosamente seu código com os exemplos dos slides também é indicado : )

Lista 2

INSTRUÇÕES/FAQ

4. Não consigo usar o comando LOAD DATA para carregar as tabelas. Todos os arquivos estão corrompidos? Não, nenhum está. Você provavelmente está esquecendo de inserir a palavra LOCAL no comando. Veja os exemplos do LOAD DATA nestes slides.

5. Carreguei a tabela Series, mas apareceram vários warnings. Tem problema? Não. Esseswarnings apenas indicam que o MySQL truncou espaços em branco. Por exemplo, transformou a string "1.2   " em 1.2.

6. Meu código executou perfeitamente mas há marcas vermelhas do lado das linhas. Tem alguma coisa errada? Não. Esse é um bug conhecido da versão do MySQL Workbench que está instalada nas máquinas do laboratório.

7. O MySQL Workbench não permite que eu crie tabelas com colunas dos tipos varchar(5000) ou tinyint. O que fazer? Troque varchar(5000) por text e tinyint por integer.

Lista 2

Made with Slides.com