Aulas práticas - Ficha 4

Bases de Dados (CC2005), Dep. Ciência de Computadores, FCUP

Eduardo R. B. Marques, DCC/FCUP

Objectivos: introdução ao uso de SQL usando uma pequena base de dados como exemplo.

1. Ambientação à BD exemplo

Carregamento da BD

Usaremos a BD "Social Network" exemplificada nas aulas teóricas.

Obtenha o arquivo sn.sql.

De seguida pode executar as instruções em sn.sql usando a consola mysql nos computadores de laboratório da seguinte forma:

Inicie a consola mysql num terminal com
```
mysql -u guest guest
```
Na consola execute o comando:
```
source sn.sql;
```

Diagrama do esquema relacional

Explorando um pouco a BD ...

Use o comando show tables; para listar as tabelas existentes.

+-----------------+
| Tables_in_guest |
+-----------------+
| COMMENT         |
| FOLLOWER        |
| HASHTAG         |
| POST            |
| POST_LIKED      |
| USER            |
+-----------------+
6 rows in set (0.00 sec)

Use o comando desc USER para listar o esquema da tabela USER.

mysql> desc USER;
+-----------+---------------+------+-----+---------+----------------+
| Field     | Type          | Null | Key | Default | Extra          |
+-----------+---------------+------+-----+---------+----------------+
| Num       | int(11)       | NO   | PRI | NULL    | auto_increment |
| Login     | varchar(16)   | NO   | UNI | NULL    |                |
| Joined    | date          | NO   |     | NULL    |                |
| Name      | varchar(128)  | NO   |     | NULL    |                |
| BirthDate | date          | NO   |     | NULL    |                |
| Sex       | enum('M','F') | NO   |     | NULL    |                |
| Phone     | int(11)       | YES  |     | NULL    |                |
| Email     | varchar(64)   | NO   |     | NULL    |                |
+-----------+---------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

A descrição obtida no passo anterior corresponde à definição da tabela USER via CREATE TABLE que pode encontrar no ficheiro sn.sql:

CREATE TABLE IF NOT EXISTS
USER
(
  Num       INT PRIMARY KEY AUTO_INCREMENT,
  Login     VARCHAR(16) UNIQUE NOT NULL,
  Joined    DATE NOT NULL,
  Name      VARCHAR(128) NOT NULL,
  BirthDate DATE NOT NULL,
  Sex       ENUM('M', 'F') NOT NULL,
  Phone     INT DEFAULT NULL,
  Email     VARCHAR(64) NOT NULL
);

Consulte os dados completos para a tabela USER usando select * from USER;.

mysql> select * from USER;
+-----+------------+------------+----------------+------------+-----+-----------+------------------------------+
| Num | Login      | Joined     | Name           | BirthDate  | Sex | Phone     | Email                        |
+-----+------------+------------+----------------+------------+-----+-----------+------------------------------+
|   1 | joao.pinto | 2019-12-01 | João Pinto     | 1976-12-19 | M   |      NULL | azulibranco@fcp.pt           |
|   2 | semedo     | 2019-12-02 | Carlos Semedo  | 1985-06-02 | M   | 223774327 | semedo@xpto.com              |
|   3 | maria      | 2019-12-02 | Maria Silva    | 2005-11-17 | F   | 939939939 | maria@xyz.pt                 |
|   4 | pedro      | 2019-12-10 | Pedro Costa    | 1982-01-03 | M   |      NULL | pc12345@xpto.com             |
|   5 | mendocas   | 2019-12-11 | Filipa Mendes  | 2002-05-03 | F   |      NULL | filipa.mendes@gmail.com      |
|   6 | eva        | 2019-12-15 | Eva Mendes     | 1975-11-18 | F   |      NULL | i_ate_the_apple@paradise.com |
|   7 | pedro2     | 2019-12-16 | Pedro Simões   | 1993-02-22 | M   | 213884445 | simoes333@gmail.com          |
|   8 | batman     | 2019-12-16 | Bruce Wayne    | 1939-01-01 | M   |      NULL | batman@dccomics.com          |
|   9 | catwoman   | 2019-12-16 | Selina Kyle    | 1940-01-01 | F   |      NULL | catwoman@dccomics.com        |
|  10 | ziggy      | 2019-12-31 | Ziggy Stardust | 1972-01-01 | M   |      NULL | ziggy@mars.com               |
|  11 | lady.z     | 2020-01-01 | Lady Stardust  | 1972-01-01 | F   |      NULL | ladyz@mars.com               |
+-----+------------+------------+----------------+------------+-----+-----------+------------------------------+

Em sn.sql pode observar as instruções INSERT que deram origem aos dados listados.
De forma análoga, pode usar os passos anteriores para observar o esquema ou dados de outras tabelas.

2. Consultas de dados

Forma simples de uma instrução SELECT:

SELECT <Campo 1>,..., <Campo n>    # Usar * para todos os campos
FROM <TABELA> WHERE <Condição>;

Use instruções SELECT por forma a obter:

Os dados completos de todos os utilizadores do sexo feminino (Sex='F').

+-----+----------+------------+---------------+------------+-----+-----------+------------------------------+
| Num | Login    | Joined     | Name          | BirthDate  | Sex | Phone     | Email                        |
+-----+----------+------------+---------------+------------+-----+-----------+------------------------------+
|   3 | maria    | 2019-12-02 | Maria Silva   | 2005-11-17 | F   | 939939939 | maria@xyz.pt                 |
|   5 | mendocas | 2019-12-11 | Filipa Mendes | 2002-05-03 | F   |      NULL | filipa.mendes@gmail.com      |
|   6 | eva      | 2019-12-15 | Eva Mendes    | 1975-11-18 | F   |      NULL | i_ate_the_apple@paradise.com |
|   9 | catwoman | 2019-12-16 | Selina Kyle   | 1940-01-01 | F   |      NULL | catwoman@dccomics.com        |
|  11 | lady.z   | 2020-01-01 | Lady Stardust | 1972-01-01 | F   |      NULL | ladyz@mars.com               |
+-----+----------+------------+---------------+------------+-----+-----------+------------------------------+

Apenas o "login" e nome dos utilizadores de sexo feminino.

+----------+---------------+
| Login    | Name          |
+----------+---------------+
| maria    | Maria Silva   |
| mendocas | Filipa Mendes |
| eva      | Eva Mendes    |
| catwoman | Selina Kyle   |
| lady.z   | Lady Stardust |
+----------+---------------+

O "login" dos utilizadores que não têm nº de telefone definido (PHONE IS NULL).

+------------+
| Login      |
+------------+
| joao.pinto |
| pedro      |
| mendocas   |
| eva        |
| batman     |
| catwoman   |
| ziggy      |
| lady.z     |
+------------+

O "login" e número de telefone dos utilizadores que têm nº de telefone definido (PHONE IS NOT NULL).

+--------+-----------+
| Login  | Phone     |
+--------+-----------+
| semedo | 223774327 |
| maria  | 939939939 |
| pedro2 | 213884445 |
+--------+-----------+

O "login" e a data de nascimento de utilizadores que tenham nascido antes do ano 2000 (Year(BirthDate) < 2000).

+------------+------------+
| Login      | BirthDate  |
+------------+------------+
| joao.pinto | 1976-12-19 |
| semedo     | 1985-06-02 |
| pedro      | 1982-01-03 |
| eva        | 1975-11-18 |
| pedro2     | 1993-02-22 |
| batman     | 1939-01-01 |
| catwoman   | 1940-01-01 |
| ziggy      | 1972-01-01 |
| lady.z     | 1972-01-01 |
+------------+------------+

O "login" e a data de nascimento de utilizadores que tenham nascido antes do ano 2000 e que sejam do sexo feminino (Year(BirthDate) < 2000 AND Sex = 'F').

+----------+------------+
| login    | birthdate  |
+----------+------------+
| eva      | 1975-11-18 |
| catwoman | 1940-01-01 |
| lady.z   | 1972-01-01 |
+----------+------------+

O "login" e o ano de nascimento de utilizadores que tenham nascido no ano 2000 ou depois (Year(BirthDate) >= 2000).

+----------+-----------------+
| Login    | Year(Birthdate) |
+----------+-----------------+
| maria    |            2005 |
| mendocas |            2002 |
+----------+-----------------+

O número, autor e texto de "posts" que tenham a sequência '...' no seu texto (Content LIKE '%...%').

Nota: A LIKE B é verdadeiro se A verifica a expressão regular SQL expressa por B. Caracteres especiais em B incluem _ que denotam um (e apenas um) qualquer caracter e % para qualquer sequência de 0 ou mais caracteres. Veja a documentação do MySQL a respeito.

+-----+--------+----------------------------------------------------------------------+
| Num | Author | Content                                                              |
+-----+--------+----------------------------------------------------------------------+
|   4 |      2 | Nothing to do except posting ... anyone there?                       |
|   9 |      9 | Joker and I went out last night ... but pesky Batman had to show up. |
|  10 |      9 | ... we ran away on the Joker-mobile!                                 |
|  12 |     10 | Happy new year star-gazers! 2020 will rock ...                       |
+-----+--------+----------------------------------------------------------------------+

Os números de utilizadores que são seguidores do utilizador número 7.

+----------+
| Follower |
+----------+
|        1 |
|       10 |
|       11 |
+----------+

O autor e o conteúdo de comentários ao "post" número 8.

+--------+---------------------+
| Author | Content             |
+--------+---------------------+
|      2 | Poor Batman!        |
|      9 | Get a life darling! |
+--------+---------------------+

3. Inserções de dados

Forma geral:

INSERT INTO <TABELA>(<Campo 1>, ..., <Campo n>)
VALUES (<Valor 1.1>, ..., <Valor 1.n>), 
       ...,
       (<Valor m.1>, ..., <Valor m.n>);

Nota: pode sempre reinicializar o estado da BD sn.sql, voltando a correr todos os comandos nesse ficheiro.

3.1

Use uma instrução INSERT para adicionar um novo utente master.of.sql na tabela USER com os seguintes dados:

Login: 'master.of.sql'
Joined: '2020-03-01';
Name: 'Susan Doe'
BirthDate: '2000-01-23'
Sex: 'F'
Phone: NULL
Email: 'master.of.sql@nosql.com'

Consulte a tabela USER para verificar o resultado da inserção. Tome nota do número do utilizador criado.

3.2

Proceda de forma análoga para as seguintes inserções, reflectindo as actividade na rede social do utilizador master.of.sql criado no passo anterior:

POST_LIKED: Um "like" ao post com número 1;
FOLLOWER: o seguimento dos utilizadores 1 e 2.
COMMENT: a escrita de um comentário para o "post" 1 com conteúdo 'LOL' e o instante (data-hora) actual atribuido ao campo Creation recorrendo à função NOW();
POST: A escrita de um "post" com conteúdo 'I love SQL' e o instante (data-hora) actual atribuido ao campo Creation recorrendo à função NOW();;
HASHTAG: a definição das "hash-tags" 'sql' e 'db' para o "post" criado no passo anterior.

4. Actualização de dados

Forma geral de uma instrução UPDATE simples:

UPDATE <TABELA>
SET <Campo 1> = <Valor 1>,
    ...
    <Campo n> = <Valor n>
WHERE <Condição>;

Use instruções UPDATE por forma a actualizar:

O email para 'maria@dcc.fc.up.pt' do utilizador com "login" 'maria'.
O nome para 'Secret Identity' e o email para 'batman@gotham.com' para o utilizador com nome 'Bruce Wayne'.
Os números de telefone para o valor NULL para utilizadores com número inferior a 5.

5. Remoção de dados

Forma geral:

DELETE FROM <TABELA> WHERE <Condição>;

Use instruções DELETE por forma a remover:

O "post" com número 5.
Os comentários feitos pelo utilizador 2.
Todas as entradas em FOLLOWER que envolvam o utilizador 2 (User = 2 OR Follower=2).

6. Consultas com ORDER BY, LIMIT, e DISTINCT

ORDER BY - especifica critério(s) de ordenação para resultados devolvidos por uma consulta

SELECT  <Campo 1>,..., <Campo n>     
FROM <TABELA> 
...
ORDER BY <Criterio Ord. 1> [ASC|DESC], ..., <Criterio Ord. k> [ASC|DESC]
...

LIMIT - limita número de resultados devolvidos por uma consulta.

SELECT  <Campo 1>,..., <Campo n>     
FROM <TABELA> 
...
LIMIT <Número de resultados>;

DISTINCT - filtra dados duplicados nos resultados devolvidos por uma consulta:
```
SELECT  DISTINCT <Campo 1>,..., <Campo n>     
FROM <TABELA> 
...
```

Escreva consultas para obter (os resultados esperados são listados para cada consulta; reponha o estado original da BD em sn.sql).

O "login" e o nome de utilizadores, ordenados por "login";

+------------+----------------+
| Login      | Name           |
+------------+----------------+
| batman     | Bruce Wayne    |
| catwoman   | Selina Kyle    |
| eva        | Eva Mendes     |
| joao.pinto | João Pinto     |
| lady.z     | Lady Stardust  |
| maria      | Maria Silva    |
| mendocas   | Filipa Mendes  |
| pedro      | Pedro Costa    |
| pedro2     | Pedro Simões   |
| semedo     | Carlos Semedo  |
| ziggy      | Ziggy Stardust |
+------------+----------------+
11 rows in set (0.01 sec)

O "login", nome, e data de nascimento de utilizadores ordenados primeiro por data de nascimento de forma decrescente, e depois (como segundo critério) por "login" (de forma crescente).

+------------+----------------+------------+
| Login      | Name           | BirthDate  |
+------------+----------------+------------+
| maria      | Maria Silva    | 2005-11-17 |
| mendocas   | Filipa Mendes  | 2002-05-03 |
| pedro2     | Pedro Simões   | 1993-02-22 |
| semedo     | Carlos Semedo  | 1985-06-02 |
| pedro      | Pedro Costa    | 1982-01-03 |
| joao.pinto | João Pinto     | 1976-12-19 |
| eva        | Eva Mendes     | 1975-11-18 |
| lady.z     | Lady Stardust  | 1972-01-01 |
| ziggy      | Ziggy Stardust | 1972-01-01 |
| catwoman   | Selina Kyle    | 1940-01-01 |
| batman     | Bruce Wayne    | 1939-01-01 |
+------------+----------------+------------+
11 rows in set (0.00 sec)

Os mesmos dados da consulta 2, mas restritos a utilizadores do sexo masculino (use WHERE).

+------------+----------------+------------+
| Login      | Name           | BirthDate  |
+------------+----------------+------------+
| pedro2     | Pedro Simões   | 1993-02-22 |
| semedo     | Carlos Semedo  | 1985-06-02 |
| pedro      | Pedro Costa    | 1982-01-03 |
| joao.pinto | João Pinto     | 1976-12-19 |
| ziggy      | Ziggy Stardust | 1972-01-01 |
| batman     | Bruce Wayne    | 1939-01-01 |
+------------+----------------+------------+
6 rows in set (0.00 sec)

Os mesmos dados da consulta 2, mas limitados a 3 resultados (use LIMIT).

+----------+---------------+------------+
| Login    | Name          | BirthDate  |
+----------+---------------+------------+
| maria    | Maria Silva   | 2005-11-17 |
| mendocas | Filipa Mendes | 2002-05-03 |
| pedro2   | Pedro Simões  | 1993-02-22 |
+----------+---------------+------------+
3 rows in set (0.00 sec)

O número, autor, conteúdo, e data de criação dos 5 posts mais recentes, ordenados de forma decrescente por data de criação.

+-----+--------+----------------------------------------------------------------------+---------------------+
| Num | Author | Content                                                              | Creation            |
+-----+--------+----------------------------------------------------------------------+---------------------+
|  11 |      9 | Joker is a busy man, he has no time for social networking.           | 2020-02-18 17:00:30 |
|  10 |      9 | ... we ran away on the Joker-mobile!                                 | 2020-02-17 23:51:12 |
|   9 |      9 | Joker and I went out last night ... but pesky Batman had to show up. | 2020-02-17 23:51:00 |
|   3 |      1 | Não foi nada de especial, chutei com o pé que estava mais a mão!     | 2020-02-13 12:31:59 |
|   8 |      9 | Batman is a troll! Joker is my sweetheart these days!                | 2020-02-10 23:41:00 |
+-----+--------+----------------------------------------------------------------------+---------------------+

As "hash-tags" usadas em "posts" (conforme a tabela HASHTAG) ordenadas por "post" (primeiro) e (depois) por "hash tag".

+------+-------------+
| Post | Tag         |
+------+-------------+
|    1 | fcp         |
|    2 | azulibranco |
|    2 | fcp         |
|    3 | fcp         |
|    4 | lonely      |
|    5 | lonely      |
|    6 | joker       |
|    7 | kitty       |
|    8 | batman      |
|    8 | troll       |
|    9 | batman      |
|    9 | joker       |
|    9 | troll       |
|   10 | batman      |
|   10 | joker       |
|   10 | troll       |
|   11 | joker       |
|   12 | 2020        |
+------+-------------+
18 rows in set (0.00 sec)

As "hash-tags" distintas usadas em associação a posts , ordenadas por "hashtag" (use DISTINCT além de ORDER BY).

+-------------+
| Tag         |
+-------------+
| 2020        |
| azulibranco |
| batman      |
| fcp         |
| joker       |
| kitty       |
| lonely      |
| troll       |
+-------------+
8 rows in set (0.00 sec)

Os números distintos de "posts" que têm algum comentário associado (na tabela COMMENT), ordenados de forma decrescente.
```
+------+
| Post |
+------+
|   12 |
|    8 |
|    1 |
+------+
3 rows in set (0.00 sec)
```

O login, e o nome de todos os utilizadores do sexo feminino que tenham um 'y' no seu nome (use o operador LIKE), ordenados por "login".

+----------+---------------+
| Login    | Name          |
+----------+---------------+
| catwoman | Selina Kyle   |
| lady.z   | Lady Stardust |
+----------+---------------+

O número, altura de criação e o texto de todos os "posts" feitos entre as 05:00 e as 18:00, ordenados pela altura de criação descendente.

+-----+---------------------+---------------------------------------------------------------------+
| Num | Creation            | Content                                                             |
+-----+---------------------+---------------------------------------------------------------------+
|  11 | 2020-02-18 17:00:30 | Joker is a busy man, he has no time for social networking.          |
|   3 | 2020-02-13 12:31:59 | Não foi nada de especial, chutei com o pé que estava mais a mão!    |
|   1 | 2020-01-13 12:31:59 | Comigo, ou sem-migo, o Porto vai ser campeão!                       |
|   6 | 2020-01-01 11:23:56 | Joker, you are dead!                                                |
|   5 | 2019-12-24 05:00:00 | I guess nobody is online!                                           |
+-----+---------------------+---------------------------------------------------------------------+
5 rows in set (0.00 sec)

O "login" e a data de nascimento dos 3 utilizadores do sexo masculino mais velhos, ordenados por data de nascimento.

+------------+------------+
| Login      | BirthDate  |
+------------+------------+
| batman     | 1939-01-01 |
| ziggy      | 1972-01-01 |
| joao.pinto | 1976-12-19 |
+------------+------------+

O "login" de utlizadores, a altura de entrada na rede social (valor de Joined), e o tempo há que faziam parte da rede social à data de '2020-01-10' na escala de dias, ordenados por este último valor.

Use TIMESTAMPDIFF(...) AS Days (consulte a documentação MySQL) para obter o campo (derivado) correspondente chamado Days na resposta.

+------------+------------+------+
| Login      | Joined     | Days |
+------------+------------+------+
| lady.z     | 2020-01-01 |    9 |
| ziggy      | 2019-12-31 |   10 |
| pedro2     | 2019-12-16 |   25 |
| batman     | 2019-12-16 |   25 |
| catwoman   | 2019-12-16 |   25 |
| eva        | 2019-12-15 |   26 |
| mendocas   | 2019-12-11 |   30 |
| pedro      | 2019-12-10 |   31 |
| semedo     | 2019-12-02 |   39 |
| maria      | 2019-12-02 |   39 |
| joao.pinto | 2019-12-01 |   40 |
+------------+------------+------+
11 rows in set (0.00 sec)

De forma análoga, obtenha o "login", data de nascimento, e idade dos 4 utilizadores mais novos à data de '2020-01-10', ordenados por idade.

+----------+------------+------+
| Login    | BirthDate  | Age  |
+----------+------------+------+
| maria    | 2005-11-17 |   14 |
| mendocas | 2002-05-03 |   17 |
| pedro2   | 1993-02-22 |   26 |
| semedo   | 1985-06-02 |   34 |
+----------+------------+------+
4 rows in set (0.00 sec)