Modulo 15 PSI

Modulo 15

Linguagem de definição de dados

Programação e Sistemas de Informação

http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands

Objetivos de Aprendizagem

No final deste módulo os alunos devem ter adquirido conhecimentos, procedimentos e atitudes que lhe permitam:

•Criação, alteração e eliminação de tabelas e índices em SQL;•Criação e eliminação de tabelas;•Alteração de tabelas;•Criação e eliminação de índices;•Conceito de transação;•Privilégios e controlo de acessos.

Âmbito dos Conteúdos

1. Criação, alteração e eliminação de tabelas e índices em SQL

1.1. Criação de tabelas (comando CREATE TABLE)

1.2. Alteração de tabelas (comando ALTER TABLE)

1.3. Criação de índices (comando CREATE INDEX)

1.4. Eliminação de tabelas e índices (comandos DROP TABLE e DROP INDEX)

2. Atualização de dados

2.1. Inserção de linhas (comando INSERT INTO)

2.2. Alteração de valores nas linhas (comando UPDATE)

2.3. Eliminação de linhas (comando DELETE FROM)

3. Conceito de transação (comandos COMMIT e ROLLBACK)

4. Privilégios e controlo de acessos (comandos GRANT e REVOKE)

Transações no SQL Server

• Definição de ‘Uma transação’ é um conjunto de procedimentos que são executados num sistema de base de dados. Para o utilizador, este sistema, é visto como uma única ação.

• Deve-se garantir que a transacção é executada correctamente, ou seja, deve garantir-se a Atomicidade. (Ou executa todos os passos correctamente ou em caso de falha nalgum passo, não executa a transacção.)

Consola do MySql

Com o Xampp (sem serviço ativo):CmdCd \Cd c:\xampp\mysql\bin\Mysql -u root

Planificação

Criação, alteração e eliminação da estrutura- Instrução CREATE DATABASE

- Instrução CREATE TABLE

- Instrução ALTER TABLE

- Instrução CREATE INDEX

- Instrução DROP TABLE/INDEX

Fichas de trabalho

SHOW DATABASES

• mysql> SHOW DATABASES;

Empty set (0.00 sec)

Lista as bases de dados que estão no servidor.

Create Database

• Sintaxe:– CREATE DATABASE nome_base_de_dados;

• Exemplo:– CREATE DATABASE db_musicas;

CREATE DATABASE

• Criar uma base de dados.

• mysql> CREATE DATABASE [IF NOT EXISTS] nome_bd

• mysql> CREATE DATABASE [IF NOT EXISTS] db_alunos;

USE DATABASE

• mysql> USE db_alunos;

A indicação USE db_name diz ao MySQL que use o a base de dados db_alunos como padrão (atual) para declarações subsequentes.

SHOW TABLES

• mysql> SHOW TABLES;

Empty set (0.00 sec)

Lista as tabelas que a base de dados escolhida (USE) tem.

CREATE TABLE

• Cria uma tabela

CREATE TABLE nome_tabela (

campo1 tipo_1 [[NOT]NULL][UNIQUE]

[{, campo_n tipo_n}]

[, PRIMARY KEY (campo1)]

[{, FOREIGN KEY (campo2) REFERENCES nome_tabela2

(campo3)}]

)

CREATE TABLE (Exemplo)• mysql> CREATE TABLE pet (

nome VARCHAR(20),

owner VARCHAR(20),

species VARCHAR(20),

sex CHAR(1),

birth DATE,

death DATE);

https://www.w3schools.com/sql/sql_datatypes.asp

CREATE TABLE(Exemplo com Chaves Primárias e Secundárias)

mysql> CREATE TABLE parent(

id INT NOT NULL,

PRIMARY KEY (id));

mysql> CREATE TABLE child(

id INT,

parent_id INT,

FOREIGN KEY (parent_id) REFERENCES parent(id));

Espaços web para praticar

• https://sqlzoo.net/

• http://www.techonthenet.com/sql/tables/create_table.php

• http://www.w3resource.com/mysql-exercises/

• http://demo.easyquerybuilder.com/asp-net-mvc/

• https://www.sqlteaching.com/

CREATE TABLE (Exercício 1)

• CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, price DECIMAL, PRIMARY KEY(category, id)) ENGINE=INNODB;

• CREATE TABLE customer (id INT NOT NULL,PRIMARY KEY (id)) ENGINE=INNODB;

• CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id)REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id)REFERENCES customer(id)) ENGINE=INNODB;

• InnoDB é um mecanismo de armazenamento para o MySQL.

SHOW CREATE TABLE• mysql> SHOW CREATE TABLE nome_bd;

• mysql> SHOW CREATE TABLE Alunos;

CREATE TABLE Alunos (

CodAluno int(3) NOT NULL, Nome text, Idade int(2) DEFAULT NULL, PRIMARY KEY (CodAluno)

) ENGINE=INNODB DEFAULT CHARSET=utf8

Listar os Objetos

SHOW DATABASES;

SHOW TABLES;

SHOW CREATE [TABLES/DB];

SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

Exemplo Prático Projeto

• CREATE TABLE tbl_alunos(numero int,apelido varchar(255),primeironome varchar(255),turma char(2),ano int);

DROP DATABASE

Elimina uma base de dados

•mysql> DROP DATABASE IF EXISTS Nome_BD;

•mysql> DROP DATABASE IF EXISTS Pessoas;

SHOW TABLES

• mysql> SHOW TABLES;

ALTER TABLE

• Add a column• Modify a column • Drop a column • Rename a column • Rename a table

ALTER TABLE ADDALTER TABLE table_name

ADD column_name datatype

ALTER TABLE supplier

ADD supplier_name varchar2(50);

ALTER TABLE table_name

ADD (column_1 column-definition,

column_2 column-definition,

...

column_n column_definition);

Exemplo prático de ADD Column

create table aluno (

codAluno char(10),

nome varchar(60) NOT NULL,

dataNasc date,

localidade varchar(50),

rua varchar(50),

numeroPorta varchar(15));

ALTER TABLE aluno ADD numinterno char(5);

ALTER TABLE MODIFY

ALTER TABLE table_name

MODIFY COLUMN column_name datatype

ALTER TABLE supplier

MODIFY COLUMN supplier_name varchar2(100) not null;

Exemplo prático de MODIFY COLUMN

create table aluno (

codAluno char(10) PRIMARY KEY,

nome varchar(60) NOT NULL,

dataNasc date,

localidade varchar(50),

rua varchar(50),

numeroPorta varchar(15)

);

ALTER TABLE aluno MODIFY COLUMN rua varchar(100);

ALTER TABLE RENAME COLUMN

ALTER TABLE table_name

RENAME COLUMN old_name to new_name;

ALTER TABLE supplier

RENAME COLUMN supplier_name to sname;

Exemplo prático de ALTER TABLE RENAME COLUMN

create table aluno (

codAluno char(10) PRIMARY KEY,

nome varchar(60) NOT NULL,

dataNasc date,

localidade varchar(50),

rua varchar(50),

numeroPorta varchar(15)

);

ALTER TABLE aluno RENAME COLUMN numeroPorta to porta;

ALTER TABLE RENAME TO

ALTER TABLE table_name

RENAME TO new_table_name;

ALTER TABLE alunos

RENAME TO tbl_alunos;

ALTER TABLE DROP

ALTER TABLE table_name

DROP COLUMN column_name

ALTER TABLE alunos

DROP COLUMN porta;

DROP DATABASE

• Sintaxe– DROP DATABASE nome_base_de_dados

• Exemplo– DROP DATABASE musicas;

Begin, Commit e RollBack Transaction

• Exemplo do pagamento Multibanco!

• Basicamente as transações no SQL Server são feitas utilizando três comandos:

– BEGIN TRANSACTION– COMMIT – ROLLBACK

Exemplo prático 1

BEGIN TRANSACTIONUPDATE TABELA1 SET CAMPO1 = ‘NOVO VALOR’WHERE CAMPO2 = 35IF @@ERROR <> 0ROLLBACKELSECOMMIT

Exemplo prático 2

BEGIN TRANSACTION TRANS1

UPDATE TABELA1 SET CAMPO1 = ‘NOVO VALOR’WHERE CAMPO2 = 35IF @@ERROR <> 0ROLLBACK TRANSACTION TRANS1 ELSECOMMIT TRANSACTION TRANS1

Algumas Regras

Mantenha transacções curtas, ou seja, não coloque muitas instruções SQL entre o BEGIN TRANSACTION e o COMMIT.

Vamos supor que alguém resolve desligar o computador em que o servido em que o MySQL está a fazer uma transacção. A transacção foi iniciada mais ainda não fez o COMMIT. Neste caso, quando o serviço do SQL Server foi reiniciado, todas as transacções que ainda não executaram o COMMIT voltarão ao seu estado inicial antes do BEGIN TRANSACTION.

Procure sempre dar nomes as transacções. Isso obriga a utilização do TRANSACTION no COMMIT e no ROLLBACK.

Encadeamento de transacções são permitidos. Para ver em que nível de transacção em que está, utilize a variável @@TRANCOUNT

4. Privilégios e controlo de acessos (comandos GRANT e REVOKE)

Os comandos GRANT e REVOKE permitem aos administradores do sistema criar utilizadores e conceder e revogar direitos aos utilizadores do MySQL em quatro

níveis de privilégios.

Criar um utilizador

• CREATE USER ‘teste'@'localhost' IDENTIFIED BY 'mypass';

SINTAXEGRANT priv_type [(column_list)] [, tipo_priv [(column_list)] ...]

ON {tbl_name | * | *.* | db_name.*}

TO user_name [IDENTIFIED BY [PASSWORD] 'password']

[, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]

[REQUIRE NONE |

[{SSL| X509}]

[CIPHER cipher [AND]]

[ISSUER issuer [AND]]

[SUBJECT subject]]

[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |

MAX_UPDATES_PER_HOUR # |

MAX_CONNECTIONS_PER_HOUR #]]

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]

ON {tbl_name | * | *.* | db_name.*}

FROM user_name [, user_name ...]

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON bankaccount.*

-> TO custom@localhost

-> IDENTIFIED BY 'obscure';

Nível Global

Privilégios globais aplicam para todos os bancos de dados em um determinado servidor.

Estes privilégios são armazenados na tabela mysql.user.

GRANT ALL ON *.* e REVOKE ALL ON *.* concederão e revogarão apenas privilégios globais.

Nível da Base de Dados

Privilégios de bancos de dados aplicam-se a todas as tabelas em um determinado banco de dados.

Estes privilégios são armazenados nas tabelas mysql.db e mysql.host.

GRANT ALL ON db.* e REVOKE ALL ON db.* concederão e revogarão apenas privilégios de banco de dados.

Nível das tabelas

Privilégios de tabelas aplicam-se a todas as colunas em uma determinada tabela.

Estes privilégios são armazenados na tabela mysql.tables_priv.

GRANT ALL ON db.table e REVOKE ALL ON db.table concederão e revogarão apenas privilégios de tabelas.

Nível das colunas

Privilégios de colunas aplicam-se a uma única coluna em uma determinada tabela.

Estes privilégios são armazenados na tabela mysql.columns_priv.

ALL [PRIVILEGES] Configura todos os privilégios simples exceto WITH GRANT OPTION

ALTER Permite o uso de ALTER TABLE

CREATE Permite o uso de CREATE TABLE

CREATE TEMPORARY TABLES Permite o uso de CREATE TEMPORARY TABLE

DELETE Permite o uso de DELETE

DROP Permite o uso de DROP TABLE.

EXECUTE Permite que o utilizador execute stored procedures (MySQL 5.0)

FILE Permite o uso de SELECT ... INTO OUTFILE e LOAD DATA INFILE.

INDEX Permite o uso de CREATE INDEX e DROP INDEX

INSERT Permite o uso de INSERT

LOCK TABLES Permite o uso de LOCK TABLES em tabelas nas quais se tem o privilégio SELECT.

PROCESS Permite o uso de SHOW FULL PROCESSLIST

REFERENCES Para o futuro

RELOAD Permite o uso de FLUSH

REPLICATION CLIENT Da o direto ao utilizador de perguntar onde o slave/master está.

REPLICATION SLAVE Necessário para a replicação dos slaves (para ler logs binário do master).

SELECT Permite o uso de SELECT

SHOW DATABASES SHOW DATABASES mostra todos os banco de dados.

SHUTDOWN Permite o uso de mysqladmin shutdown

SUPERPermite a conexão (uma vez) mesmo se max_connections tiverem sido alcançados e executa o comando CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS e SET GLOBAL

UPDATE Permite o uso de UPDATE

USAGE Sinônimo para ``sem privilégios.''

GRANT OPTION Sinônimo para WITH GRANT OPTION

Para as instruções GRANT e REVOKE, tipo_priv pode ser especificado como um dos seguintes:

mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'alguma_senha' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO monty@'%'

IDENTIFIED BY 'alguma_senha' WITH GRANT OPTION;

Um superutilizador completo que pode conectar ao servidor de qualquer lugar, mas deve utilizar uma senha 'alguma_senha' para fazer isto.

Perceba que devemos utilizar instruções GRANT para monty@localhost e monty@"%".

Retirar privilégios

• REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

• REVOKE INSERT ON *.* FROM ‘teste'@'localhost';

Mostrar os privilégios

• SHOW GRANTS FOR 'root'@'localhost';

Apagar um Sql “User”

• DROP USER ‘carlos'@'localhost';

Exemplo 1

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'

-> WITH GRANT OPTION;

mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'

-> WITH GRANT OPTION;

mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_pass';

mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

mysql> CREATE USER 'dummy'@'localhost';

Exemplo 2

mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'obscure';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON db_alunos.*

-> TO 'custom'@'localhost';

Conclusão

• Resumo, conclusão, revisões, avaliação e autoavaliação.