Upload
deepblue-one
View
322
Download
10
Embed Size (px)
Citation preview
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.