MySQL Server e SQL
Conteúdo da Aula
Instalação do MySQL Server
Criação de Database
Criação de Tabelas em Database
Constraints e Foreign Keys
Linguagem SQL
Dump e Restore de Database
MySQL Server e SQL
Instalação do MySQL Server
Defina LinuxForce01 como senha de root e confirme a escolha,
após a conclusão da instalação verifique o status do serviço
# systemctl status mysql -l
Verifique a porta padrão do MySQL
# ss -nltp | grep 3306
Logo no SGDB e verifique o seu usuário
> mysql -p
> SELECT current_user();
MySQL Server e SQL
Criação de Database
Verifique quais Databases estão presentes no SGBD
> SHOW DATABASES;
Crie o Database asf
> CREATE DATABASE asf;
Acesse o database e verifique que não possui tabelas
> USE asf
> SHOW TABLES;
MySQL Server e SQL
Criação de Tabelas em Database
Crie as tabelas employee, contact e employee_contact
> CREATE TABLE employee (
-> id_person INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
-> name TINYTEXT NOT NULL,
-> sector TINYTEXT NOT NULL,
-> position TINYTEXT NOT NULL,
-> PRIMARY KEY(id_person))
-> ENGINE=InnoDB;
MySQL Server e SQL
Criação de Tabelas em Database
> CREATE TABLE contact (
-> id_contact INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
-> corp_mail VARCHAR(55) NOT NULL,
-> person_mail VARCHAR(55) NOT NULL,
-> address TINYTEXT NOT NULL,
-> phone_1 VARCHAR(15) NOT NULL,
-> phone_2 VARCHAR(15) NOT NULL,
-> start_time TIME NOT NULL,
MySQL Server e SQL
Criação de Tabelas em Database
-> start_time TIME NOT NULL,
-> finish_time TIME NOT NULL,
-> PRIMARY KEY(id_contact))
-> ENGINE=InnoDB;
> CREATE TABLE employee_contact (
-> id_person INT(5) UNSIGNED NOT NULL ,
-> id_contact INT(5) UNSIGNED NOT NULL )
-> ENGINE=InnoDB;
MySQL Server e SQL
Criação de Tabelas em Database
Verifique as tabelas criadas
> SHOW TABLES;
Verifique a descrição de cada tabelas criada
> DESC employee;
> DESC contact;
> DESC employee_contact;
MySQL Server e SQL
Constraints e Foreign Keys
Implemente as contraints para definição das Foreign Keys
> ALTER TABLE employee_contact ADD CONSTRAINT person_fk
FOREIGN KEY(id_person) REFERENCES employee(id_person);
> ALTER TABLE employee_contact ADD CONSTRAINT contact_fk
FOREIGN KEY(id_contact) REFERENCES contact(id_contact);
MySQL Server e SQL
Linguagem SQL
Inserindo dados nas tabelas employee, contact e employee_contact
> INSERT INTO employee (id_person,name,sector,position)
VALUES (1,'Gideon Goddard','Manager','Owner');
> INSERT INTO contact (id_contact,corp_mail,person_mail,
address,phone_1,phone_2,start_time,finish_time)VALUES(1,'gideon
.goddard@[Link]','[Link]@[Link]','5th Avenue N
1024','+1 211-020-1007','+1 140-319-9028','09:00:00','17:00:00');
> INSERT INTO employee_contact (id_person,id_contact)VALUES
(1,1);
MySQL Server e SQL
Linguagem SQL
Execute um SELECT nas tabelas buscando os registros
> SELECT * FROM employee;
> SELECT * FROM contact;
> SELECT * FROM employee_contact;
Teste a deleção de dados e verifique se a integridade será mantida
> DELETE FROM employee WHERE id_person=1;
> DELETE FROM contact WHERE id_contact=1;
MySQL Server e SQL
Linguagem SQL
Altere a tabela contact
> SELECT * FROM contact WHERE id_contact=1;
> ALTER TABLE contact DROP person_mail;
> ALTER TABLE contact DROP phone_2;
> ALTER TABLE contact CHANGE corp_mail email VARCHAR(55)
NOT NULL;
> ALTER TABLE contact CHANGE phone_1 phone VARCHAR(15)
NOT NULL;
> SELECT * FROM contact WHERE id_contact=1;
MySQL Server e SQL
Linguagem SQL
Altere a tabela employee
> SELECT * FROM employee WHERE id_person=1;
> ALTER TABLE employee ADD surname TINYTEXT NOT NULL;
> UPDATE employee SET name='Gideon' WHERE id_person=1;
> UPDATE employee SET surname='Goddard' WHERE
id_person=1;
> SELECT * FROM employee WHERE id_person=1;
MySQL Server e SQL
Linguagem SQL
Insira novos registros nas tabelas
> INSERT INTO employee (id_person,name,sector,position,surname)
VALUES (2,'Lloyd','Cybersecurity','Analist','Chun');
> INSERT INTO employee (id_person,name,sector,position,surname)
VALUES (3,'Elliot','Cybersecurity','Engineer','Alderson');
> INSERT INTO employee (id_person,name,sector,position,surname)
VALUES (4,'Angela','Account Manager','Middle','Moss');
> INSERT INTO employee (id_person,name,sector,position,surname)
VALUES (5,'Ollie','Marketing','Major','Parker');
MySQL Server e SQL
Linguagem SQL
Insira novos registros nas tabelas
> INSERT INTO contact(id_contact,email,address,phone,start_time,
finish_time) VALUES (3,'[Link]@[Link]','5th Avenue N
1024','+1 211-020-1009','09:00:00','17:00:00');
> INSERT INTO contact(id_contact,email,address,phone,start_time,
finish_time) VALUES (3,'[Link]@[Link]','5th Avenue N
1024','+1 211-020-1009','09:00:00','17:00:00');
MySQL Server e SQL
Linguagem SQL
Insira novos registros nas tabelas
> INSERT INTO contact(id_contact,email,address,phone,start_time,
finish_time)VALUES(4,'[Link]@[Link]','5th Avenue N
1024','+1 211-020-1010','09:00:00','17:00:00');
> INSERT INTO contact(id_contact,email,address,phone,start_time,
finish_time)VALUES(5,'[Link]@[Link]','5th Avenue N
1024','+1 211-020-1011','09:00:00','17:00:00');
MySQL Server e SQL
Linguagem SQL
Insira novos registros nas tabelas
> INSERT INTO employee_contact(id_person,id_contact)VALUES
(2,2);
> INSERT INTO employee_contact(id_person,id_contact)VALUES
(3,3);
> INSERT INTO employee_contact(id_person,id_contact)VALUES
(4,4);
> INSERT INTO employee_contact(id_person,id_contact)VALUES
(5,5);
MySQL Server e SQL
Linguagem SQL
Analise as inserções feitas nas tabelas
> SELECT * FROM employee;
> SELECT * FROM contact;
> SELECT * FROM employee_contact;
Refine suas queries
> SELECT address FROM contact GROUP BY address;
> SELECT sector FROM employee GROUP BY sector;
MySQL Server e SQL
Linguagem SQL
Analise as inserções feitas nas tabelas
> SELECT * FROM employee;
> SELECT * FROM contact;
> SELECT * FROM employee_contact;
Refine suas queries buscando grupos e ordenando resultados
> SELECT address FROM contact GROUP BY address;
> SELECT sector FROM employee GROUP BY sector;
> SELECT * FROM emplyee ORDER BY name;
MySQL Server e SQL
Linguagem SQL
Execute a união de resultados obtidos em mais de uma tabela
> SELECT name FROM employee INNER JOIN contact ON
employee.id_person=contact.id_contact;
> SELECT name,email FROM employee INNER JOIN contact ON
employee.id_person=contact.id_contact;
> SELECT name,email,phone FROM employee INNER JOIN
contact ON employee.id_person=contact.id_contact;
> SELECT name,email,phone,sector FROM employee INNER
JOIN contact ON employee.id_person=contact.id_contact;
MySQL Server e SQL
Dump e Restore de Database
Adicione um usuário com senha LinuxForce00 ao SGDB e
conceda permissões para administrar a base asf
> GRANT ALL PRIVILEGES ON asf.* TO webmaster@localhost
IDENTIFIED BY 'LinuxForce00' WITH GRANT OPTION;
Aplique as alterações e saia do MySQL e faça um dump da base
> FLUSH PRIVILEGES;
> exit
# mysqldump -u webmaster -p asf > asf_dump.sql
MySQL Server e SQL
Dump e Restore de Database
Logue no MySQL e destrua a base asf
# mysql -p
> SHOW DATABASES;
> DROP DATABASE asf;
> SHOW DATABASES;
> exit
Volte ao MySQL e recrie o Database asf
# mysql -p
MySQL Server e SQL
Dump e Restore de Database
> SHOW DATABASES;
> CREATE DATABASE asf;
> SHOW DATABASES;
> exit
Restaure as tabelas e registros através do arquivo de dump
# mysql -b asf -p < asf_dump.sql
MySQL Server e SQL
Dump e Restore de Database
Logue no MySQL e verifique o resultado da restauração
# mysql -u webmaster -p
> SHOW DATABASES;
> USE asf
> SHOW TABLES;
> SELECT name,email,phone,sector FROM employee INNER
JOIN contact ON employee.id_person=contact.id_contact;
> exit