Aulas práticas - Ficha 3

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 à consola MySQL

1.1. Inicie a consola

O MySQL está instalado nos computadores de laboratório.

Para se ligar ao servidor de MySQL através de uma consola de comandos execute:

$ mysql -uguest

Deverá obter em resposta algo similar a:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 8.0.15 MySQL Community Server - GPL
...
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

1.2. Comandos na consola mysql

A tabela seguinte resume alguns dos principais comandos que pode executar na consola. Experimente por exemplo os comandos help, e show databases;.

Comando	Significado
help	Mostra lista de comandos disponíveis.
help *comando*	Mostra ajuda relativa a *comando*.
quit	Termina a consola.
source *comandos.sql*	Carrega comandos do ficheiro *comandos.sql*.
show databases;	Mostra lista de BDs existentes.
use *NomeDeBD* ;	Torna *NomeDeBD* a BD actual. Comandos subsequentes terão *NomeDeBD* como contexto.
show tables;	Mostra as tabelas da BD actual.
desc *NomeDeTabela* ;	Lista a descrição do esquema da tabela com nome *NomeDeTabela*.

Para além dos comandos acima, pode ainda executar as instruções típicas de manipulação de esquema (ex. CREATE TABLE) ou dados (ex. INSERT, SELECT, UPDATE, e DELETE).

Se estas forem complexas ou quiser executar várias instruções de uma só vez, será preferível colocar essas intruções num ficheiro e carregá-los com o comando source mencionado na tabela.

2. BD exemplo

2.1. Carregamento

Usaremos a BD exemplificada nas aulas teóricas.

Obtenha o arquivo sn.sql. De seguida carregue a BD exemplo usando o comando source sn.sql.

mysql> source sn.sql
...

Database changed
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

...

2.2. Perspectiva sobre a BD

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

mysql> show tables;
+-----------------+
| 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 repetir os passos anteriores para observar o esquema ou dados das restantes tabelas.

3. Consulta de dados

Forma geral:

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 '%...%').

+-----+--------+----------------------------------------------------------------------+
| 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! |
+--------+---------------------+

4. Inserção 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 repôr o estado inicial da BD usando o comando source sn.sql.

4.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 tabela USER para verificar o resultado da inserção. Tome nota do número do utilizador criado.

4.2

Proceda de forma análoga a outras inserções, reflectindo as seguintes accões do novo utilizador criado, i.e., master.of.sql:

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.

5. Actualização de dados

Forma geral:

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.

6. 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).