51
1 Base de Dados SQL - Server Carlos Ramos ESGN 2008/2009

SQL Server

Embed Size (px)

DESCRIPTION

saiba como manusear o SQL SERVER

Citation preview

  • 1

    Base de Dados

    SQL - Server

    Carlos Ramos

    ESGN 2008/2009

  • 2

    ndice SQL Server - Arquitectura Cliente/Servidor ........................................................................................ 3

    SQL Server - Arquitectura da Base de Dados ...................................................................................... 4 SQL Server - SQL Server Enterprise Manager. .................................................................................... 8 SQL Server - Criar uma base de dados ............................................................................................ 12 SQL Server - Criar uma base de dados (Exemplo) ............................................................................. 15 SQL Server - Criar uma base de dados (Outro Exemplo) .................................................................... 17 SQL Server - Criar uma tabela ....................................................................................................... 19 Restries de Integridade de Gatilhos ............................................................................................. 21

    Restries de Chaves ................................................................................................................... 22 Restries de Integridade Referencial .............................................................................................. 19 Restries Baseadas em Atributos .................................................................................................. 24 Nomes das Restries de Integridade .............................................................................................. 25 Gatilhos em SQL3 ........................................................................................................................ 26 Gatilhos em SQL-Server ................................................................................................................ 29 ndices ....................................................................................................................................... 31

    Vistas ........................................................................................................................................ 32 Vistas e Segurana ..................................................................................................................... 35 SQL Linguagem de Manipulao de Dados (LMD) ........................................................................... 37 SQL Server Procedures ............................................................................................................... 41 SQL Server Anexos ..................................................................................................................... 43

  • 3

    SQL Server - Arquitectura Cliente/Servidor

    Microsoft SQL Server foi desenhado para ser um sistema Cliente/Servidor:

    - A Base de Dados pode residir num nico computador, conhecido como Servidor, e assim est garantida a partilha dos dados por mltiplos utilizadores;

    Figura 1.

  • 4

    SQL Server - Arquitectura da Base de Dados

    Figura 2. Podem existir mltiplas Bases de dados; O SQL Server tem quatro bases de dados de sistema (master, model, tempdb, and msdb);

  • 5

    SQL Server - Arquitectura da Base de Dados (Continuao) Bases de Dados de Sistema: Microsoft SQL Server tem quatro bases de dados de sistema:

    Master , regista:

    - todas as contas e as configuraes do sistema. - a existncia de todas as outras bases de dados e a localizao dos ficheiros primrios que contm a informao de

    inicializao para as bases de dados dos utilizadores.

    - A informao de inicializao do SQL Server .

    Tempdb: - Regista toda a informao temporria.

    - recriada sempre que o SQL Server iniciado. - Todas as tabelas temporrias so eliminadas sempre que terminada a ligao.

    - A base de dados cresce de acordo com as necessidades.

    Model : - usada como um modelo para todas as bases de dados criadas no sistema.

    - Quando a instruo CREATE DATABASE usada, a primeira parte da nova base de dados criada copiando o contedo da base de dados model, o resto da base de dados preenchida com pginas em brando.

    Msdb:

    - usada para escalonar sinais de alerta e trabalhos.

  • 6

    SQL Server - Arquitectura da Base de Dados (Continuao) No SQL Server 7.0, todas as bases de dados, incluindo as bases de dados de sistema, tm o seu prprio conjunto de

    ficheiros, que no so partilhados com nenhuma outra base de dados. A localizao por defeito dos referidos ficheiros

    C:\Mssql7\Data directory:

    Ficheiro da Base de dados

    Nome fsico do Ficheiro

    Tamanho por defeito

    Master primary data Master.mdf 7.5 MB

    Master log Mastlog.ldf 1.0 MB

    tempdb primary data Tempdb.mdf 8.0 MB

    tempdb log Templog.ldf 0.5 MB

    model primary data Model.mdf 0.75 MB

    model log Modellog.ldf 0.75 MB

    msdb primary data Msdbdata.mdf 3.5 MB

    msdb log Msdblog.ldf 0.75 MB

    Cada base de dados no SQL Server contm tabelas de sistema que regista os dados necessrios aos componentes do SQL

    Server.

    Os utilizadores no podem alterar directamente a informao das tabelas de sistema. Existe um conjunto de ferramentas que permitem aos utilizadores administrar o seu sistema.

  • 7

    Podem ser utilizados utilitrios de administrao tais como SQL Server Enterprise Manager, para administrar directamente o sistema.

    Os Programadores construindo scripts em Transact-SQL e procedures conseguem garantir todas as funes administrativas do seu sistema.

    No so suportados triggers definidos nas tabelas de sistema; podem alterar o funcionamento do mesmo.

  • 8

    SQL Server - Arquitectura da Base de Dados Continuao

    Figura 3.

    SQL Server permite o acesso de mltiplos utilizadores a trabalhar em diferentes bases de dados ao mesmo tempo;

    Quando um utilizador estabelece uma ligao, fica associado a uma base de dados especfica no servidor, chamada de

    base de dados corrente;

    Cada utilizador pode comutar entre diferentes bases de dados recorrendo ao Transact-SQL USE database_name ;

  • 9

    SQL Server - SQL Server Enterprise Manager. As aplicaes em SQL Server so construdas de acordo com uma importante hierarquia chamada SQL Distributed Management

    Framework ( SQL-DMF ).

    - No primeiro nvel esto comandos em Transact-SQL. Transact-SQL uma verso Microsoft do SQL standard.

    - Tudo no SQL Server pode ser feito atravs de comandos em texto simples ( se souber o que est a fazer).

    - O segundo nvel um conjunto de objectos de gesto da base de dados que invocam comandos Transact-SQL.

    - No topo da hierarquia est o SQL Server Enterprise manager.

    O SQL Server Enterprise Manager uma ferramenta grfica que permite efectuar a gesto do universo SQL Server:

    - Configurar servidores.

    - Criar e gerir devices.

    - Criar e gerir bases de dados.

    - Fazer cpias de segurana e restaurar bases de dados.

    - Criar, modificar e eliminar objectos.

    - Gerir utilizadores da base de dados.

    - Executar queries.

    - Configurar e modificar tarefas automticas.

    - Controlar replicao entre servidores.

    - ...

  • 10

    - SQL Server - SQL Server Enterprise Manager (Continuao).

  • 11 Figura 4.

  • 12

    SQL Server - Criar uma base de dados

    No SQL Server, os dados so armazenados em bases de dados e organizados em componentes lgicos visveis para os utilizadores.

    Uma base de dados implementada fisicamente em dois ou mais ficheiros em disco.

    Existem dois componentes que compem um sistema cliente/servidor de bases de dados: programas que proporcionam aos utilizadores o

    acesso aos dados (cliente), e a estrutura que permite gerir os dados (servidor).

    Criar uma base de dados no SQL Server significa planear, criar e gerir um conjunto de componentes que se relacionam:

    Base de Dados - Contm os objectos usados para representar, gerir e aceder aos dados. CREATE DATABASE

    Tabelas - Armazenam linhas de dados e definem relaes entre mltiplas tabelas. CREATE TABLE

    Diagramas- Representam graficamente os objectos da base de dados. ndices - Optimizam a velocidade de acesso aos dados de uma tabela.

    CREATE INDEX Vistas - Proporcionam uma maneira alternativa de visualizar os dados em uma ou mais tabelas.

    CREATE VIEW Stored Procedures - Programas em Transact-SQL que definem regras de negcio, tarefas, e processos no servidor.

    CREATE PROCEDURE

    Triggers - So tipos especiais de Stored Procedures que s so executados quando os dados de uma tabela so alterados.

    CREATE TRIGGER Full-Text Indexes - Facilitam a consulta em dados armazenados em colunas do tipo texto (varchar, text).

    ../../../../ISPGAYA/SI0708/Enforcing%20Business%20Rules%20with%20Triggers.htm../../../../ISPGAYA/SI0708/Stored%20Procedures.htm

  • 13

    SQL Server - Criar uma base de dados (Continuao) SQL Server executa a instruo CREATE DATABASE em dois passos:

    1. SQL Server usa uma cpia da base de dados model para inicializar a nova base de dados.

    2. SQL Server preenche o resto da base de dados com pginas vazias.

    Existem trs tipos de ficheiros para armazenar uma base de dados:

    O Ficheiro primrio, que contm a informao de inicializao da base de dados e tambm pode ser usado para armazenar

    dados; Todas as bases de dados tm obrigatoriamente um Ficheiro Primrio.

    Ficheiro Secundrio, usado para armazenar os dados que no cabem no ficheiro primrio. Uma base de dados no precisa do

    ficheiro secundrio se o ficheiro primrio tiver capacidade suficientemente. Por outro lado pode-se usar mltiplos ficheiros secundrios, eventualmente, dispersos por vrias unidades de disco.

    Ficheiros de LOG, que armazenam informao necessria recuperao da base de dados. Tem que existir pelo menos um ficheiro de LOG. O tamanho mnimo para um ficheiro de LOG de 512K.

    Tipo de Ficheiro Extenses

    Ficheiro primrio .mdf

    Ficheiro secundrio .ndf

    Ficheiro de Log .ldf

  • 14

    SQL Server - Criar uma base de dados Syntax

    CREATE DATABASE database_name

    /* Define o Ficheiro primrio, se no se colocar esta primitiva, o primeiro ficheiro especificado assumido como primrio*/

    [ ON [PRIMARY]

    [ [,...n] ] ]

    /* Para especificar o ficheiro de LOG, se nada for dito criado um ficheiro de LOG com uma capacidade de 25% do total dos tamanhos de todos os ficheiros da base de dados*/

    [ LOG ON { } ]

    /* Primitivas usadas por questes de compatibilidade com verses anteriores do SQL Server*/ [ FOR LOAD | FOR ATTACH ]

    ::= ( [ NAME = logical_file_name, ]

    FILENAME = 'os_file_name' [, SIZE = size]

    [, MAXSIZE = { max_size | UNLIMITED } ] [, FILEGROWTH = growth_increment] ) [,...n]

  • 15

    SQL Server - Criar uma base de dados (Exemplo)

    Este contedo faz parte de um ficheiro de script, do tipo texto que deve ser armazenado com extenso .SQL.

    /* Tem-se que utilizar sempre a base de dados Master*/

    /* pois esta que guarda toda a informao relativa existncia de todas as bases de dados do sistema*/

    USE MASTER /* Elimina a Base de Dados com o nome MJS_BD1 */

    DROP DATABASE MJS_BD1 /* Esta instruo d ordem de execuo*/

    GO

    CREATE DATABASE mjs_bd1

    ON /* Nome da base de dados*/

    ( NAME = exerc1,

    FILENAME = 'c:\mssql7\mjsbd1.mdf', /* Especifica o tamanho do ficheiro em MB*/

    SIZE = 2, /* Especifica o tamanho mximo, em MB, que o ficheiro mjsbd1.mdf pode tomar, se se colocar o sufixo UNLIMITED o ficheiro

    pode crescer at o disco ficar cheio*/

  • 16

    MAXSIZE = 8, /* Tamanho acrescentado ao ficheiro sempre que novo espao necessrio, podem ser usados os sufixos KB, MB ou %, por

    defeito MB*/ FILEGROWTH = 20% )

    GO

  • 17

    SQL Server - Criar uma base de dados (Outro Exemplo) USE master

    GO

    CREATE DATABASE Archive

    ON

    PRIMARY ( NAME = Arch1,

    FILENAME = 'c:\mssql7\data\archdat1.mdf',

    SIZE = 100MB,

    MAXSIZE = 200,

    FILEGROWTH = 20),

    ( NAME = Arch2,

    FILENAME = 'c:\mssql7\data\archdat2.ndf',

    SIZE = 100MB,

    MAXSIZE = 200,

    FILEGROWTH = 20),

    ( NAME = Arch3,

    FILENAME = 'c:\mssql7\data\archdat3.ndf',

    SIZE = 100MB,

    MAXSIZE = 200,

    FILEGROWTH = 20)

    LOG ON

    ( NAME = Archlog1,

    FILENAME = 'c:\mssql7\data\archlog1.ldf',

    SIZE = 100MB,

    MAXSIZE = 200,

    FILEGROWTH = 20),

    ( NAME = Archlog2,

    FILENAME = 'c:\mssql7\data\archlog2.ldf',

    SIZE = 100MB,

  • 18

    MAXSIZE = 200,

    FILEGROWTH = 20)

    GO

  • 19

    SQL Server - Criar uma tabela Tipos de Dados:

    Data e Hora:

    - datetime - para armazenar datas desde Janeiro 1, 1753, a Dezembro 31, 9999 - smalldatetime para armazenar datas desde Janeiro 1, 1900, a Junho 6, 2079

    Binrios:

    - varbinary - binary

    Texto:

    - char - at 8K, um n fixo de caracteres. - varchar - at 8K, admite que em cada linha possa existir um n varivel de caracteres

    - text - superior a 8K Numricos:

    - int - 4 bytes para representar um nmero - smallint - 2 bytes - tinyint - 1 byte, de 0 a 256.

    - decimal ou numeric - float

    - real Monetrios:

    - money

    - smallmoney Especiais:

    - bit - Valores booleanos 0 ou 1.

  • 20

    SQL Server - Criar uma tabela CREATE TABLE ()

    Exemplo /* Usa esta base de dados, pois nela que vai criar a tabela*/

    use mjs_bd1

    create table unidade ( codun tinyint not null,

    descricao char(20),

    /* Inicio da definio das restries de integridade, PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE */ constraint pk_unidade primary key(codun) )

    Apagar uma relao:

    DROP TABLE ;

    Alterar esquemas de uma relao:

    Exemplos: ALTER TABLE actor ADD telefone CHAR(16);

    ALTER TABLE actor DROP nascimento;

  • 21

    Restries de Integridade de Gatilhos

    Restries de Chave

    Restries de Integridade Referencial

    Restries Baseadas em Atributos

    Restries Baseadas em Tuplos

    Gatilhos SQL3

    Asseres SQL3 (No so suportadas pelo SQL-Server)

    Restries (constraints) e gatilhos (Triggers) so elementos activos da BD;

    Expresses ou comandos que so escritos uma vez e guardados na BD e sero executados como resposta a certos

    eventos;

    So testadas sempre que h modificaes nas relaes envolvidas

  • 22

    Restries de Chaves

    Fazem parte da instruo CREATE TABLE

    So especificadas usando a primitiva PRIMARY KEY

    CREATE TABLE ACTOR(NOME CHAR(30) PRIMARY KEY, ....);

    CREATE TABLE FILME(TITULO CHAR(30), ANO NUMBER(4),

    PRIMARY KEY(TITULO, ANO));

    Chaves tambm podem ser especificadas utilizando a primitiva UNIQUE

    Podem existir vrias declaraes UNIQUE mas s uma chave primria

    Ao definir-se um determinado conjunto de campos como chave primria, estamos a especificar que

    estes campos no admitem valores nulos e no podem existir valores repetidos.

    Com a primitiva UNIQUE deixa-se em aberto a possibilidade de existirem valores nulos

    Pode-se proibir a existncia de valores nulos para atributos, com recurso primitiva NOT NULL.

  • 23

    Restries de Integridade Referencial

    Chaves estrangeiras fazem sentido para obrigar determinados atributos a receberem valores que fazem

    parte da chave primria de uma relao referenciada

    Quando a chave externa possui somente um atributo pode-se utilizar a primitiva REFERENCES

    ()

    Para um ou mais atributos utiliza-se a primitiva FOREIGN KEY () REFERENCES

    ()

    Exemplo

    CREATE TABLE PARTICIPA(

    FILME CHAR(50),

    ANO NUMBER(4),

    ACTOR CHAR(30) REFERENCES ACTOR(NOME),

    SALARIO DECIMAL(9,2),

    FOREIGN KEY (FILME, ANO) REFERENCES FILME(FILME, ANO)

    );

  • 24

    Restries Baseadas em Atributos

    Os valores permitidos para os atributos podem ser restritos com recurso primitiva CHECK

    A verificao da restrio associada apenas ao atributo em questo.

    Exemplos

    SEXO CHAR(1) CHECK (SEXO IN (F, M)),

    PRESIDENTE INT CHECK (PRESIDENTE IN (SELECT PRES FROM EXECUTIVO)),

    No equivalente a uma restrio de integridade referencial pois a restrio de integridade apenas verificada para o atributo em questo. Por

    exemplo, se alterar o cdigo do presidente na tabela Presidente, com a instruo CHECK no d erro.

  • 25

    Nomes das Restries de Integridade

    Para se efectuar a manuteno de uma restrio de integridade esta deve ter nome

    NOME CHAR(30) CONSTRAINT PK_AUTOR PRIMARY KEY

    SEXO CHAR(1) CONSTRAINT CK_SEXO CHECK (SEXO IN (F, M))

    CONSTRAINT FK_PARTICIPA FOREIGN KEY (FILME, ANO) REFERENCES FILME(TITULO, ANO)

    Restries podem ser apagadas com a primitiva ALTER TABLE ....DROP ....

    ALTER TABLE ACTOR DROP CONSTRAINT PK_ACTOR;

    Restries podem ser adicionadas com a primitiva ALTER TABLE ....ADD ....

    ALTER TABLE ACTOR ADD CONSTRAINT PK_ACTOR PRIMARY KEY(NOME);

  • 26

    Gatilhos em SQL3

    So regras Event-Condition-Action (ECA)

    A aco pode ser executada antes, depois ou em vez do evento que a dispara

    A aco pode referir-se a valores novos ou aos antigos dos tuplos inseridos, modificados ou apagados no evento que a

    dispara

    Eventos de modificao podem especificar um atributo ou um conjunto de atributos

    Uma condio pode ser especificada por uma clusula WHEN e a execuo da aco s tem lugar se a regra disparada e a

    condio verdadeira quando o evento ocorre

  • 27

    Gatilhos em SQL3 Continuao

    EXECUO da ACO

    AFTER WHEN testado depois de executada a operao associada ao evento

    BEFORE - WHEN testado antes de ser executada a operao associada ao evento

    INSTEAD OF - a(s) aco(es) associada(s) ao trigger so executadas caso se verifique a condio WHEN, e a

    operao associada ao evento nunca executada.

    EVENTOS DE DISPARO

    UPDATE

    INSERT

    DELETE

    A aco do trigger pode conter vrias instrues em SQL separadas por vrgulas

    A execuo da referida aco pode ser por cada tuplo (FOR EACH ROW) ou uma nica vez.

  • 28

    Gatilhos em SQL3 Continuao

    EXEMPLO

    Pretendemos criar um trigger que impea a subida da mdia dos salrios dos executivos para alm de 50000.

    CREATE TRIGGER MEDIASALTRIGGER

    INSTEAD OF UPDATE OF SALARIO ON EXECUTIVO

    REFERENCING

    OLDTABLE AS VELHOS,

    NEWTABLE AS NOVOS

    WHEN (50000

  • 29

    Gatilhos em SQL-Server Para garantir a mesma funcionalidade do trigger supra definido em SQL3 utilizaria a seguinte sintaxe em SQL-Server:

    use master go

    use mjs_bd1 go

    drop trigger mediasaltrigger go

    CREATE TRIGGER MEDIASALTRIGGER ON EXECUTIVO for UPDATE, insert /*Este trigger responde aos eventos de update e insert*/ as

    declare @media numeric /* Declarao de uma varivel local (o nome da varivel tem que ser sempre precedida de @*/ select @media=avg(salrio) from executivo

    if (select avg(salrio) from executivo)>=5000 /* Equivalente condio WHEN*/ begin

    print 'vai passear' /*Visualiza uma mensagem no ecr*/ (select * from inserted where exists /* Estas instrues esto a mais s para fazer referncia aos tuplos que foram

    inseridos ou actualizados na tebela*/ /* Para fazer referncia aos tuplos anteriores seria com deleted*/

    (select * from EXECUTIVO)) rollback /* Esta instruo permite repor a instncia anterior*/

    end GO

  • 30

    Gatilhos em SQL-Server - Continuao

    Outras instrues que podem ser usadas na aco de um trigger:

    BEGIN TRANSACTION INICIA UMA TRANSACO QUE DEPOIS PODER SER CONCRETIZADA COM A INSTRUO COMMIT TRANSACTION OU NO, COM RECURSO INSTRUO ROLLBACK;

    BEGIN....END CINSCUNSCREVE UMA INSTRUO COMPOSTA IF....ELSE

    WHILE...

    UPDATE(CAMPO) DETERMINA SE FOI EFECTUADA UMA OPERAO DE UPDATE SOBRE O CAMPO

    ESPECIFICADO.

    INSERTED TABELA QUE CONTM OS NOVOS TUPLOS DEPOIS DA ALTERAO POR PARTE DO EVENTO

    QUE ACTIVOU O TRIGGER;

    DELETED TABELA QUE OS TUPLOS COM A INSTNCIA ANTERIOR EXECUO DO EVENTO QUE

    ACTIVOU O TRIGGER

  • 31

    ndices

    Um ndice visa tornar mais eficiente a pesquisa de dados cujo critrio de seleco envolva um determinado campo.

    A deciso de usar ou no um ndice cabe ao projectista da base de dados, tendo em considerao:

    - melhora muito o desempenho na obteno de respostas para perguntas envolvendo os atributos especificados no

    ndice;

    - torna mais demoradas as inseres, eliminaes e alteraes de tuplos da relao. Pois tem que proceder

    reordenao do ndice.

    Criao de ndices.

    CREATE INDEX INDICEANO ON FILME(ANO);

    CREATE INDEX INDICECHAVE ON FILME(TITULO, ANO);

    Eliminar um ndice.

    DROP INDEX INDICEANO;

  • 32

    Vistas Vistas so relaes lgicas, sem existncia fsica ao nvel da unidade de disco.

    Relaes com existncia fsica so vulgarmente identificadas por tabelas.

    Criao de Vistas:

    CREATE VIEW AS ;

    Exemplos:

    CREATE VIEW FILMESPARAMOUNT AS

    SELECT TITULO, ANO

    FROM FILME

    WHERE ESTUDIO=PARAMOUNT;

    Criou uma vista com o nome FilmesParamount, que, posteriormente, pode ser usada tal como se fosse uma

    tabela. Exemplo:

    SELECT TITULO

    FROM FILMESPARAMOUNT

    WHERE ANO=1979;

  • 33

    Vistas - continuao Vistas Modificveis.

    Uma vista modificvel se:

    - a clusula WHERE no incluir nenhuma sub-pergunta;

    - os atributos especificados na clusula SELECT da vista forem suficientes por forma a permitir que os restantes

    atributos possam ficar com valor NULL ou um valor por omisso na tabela base.

    Nota: As operaes de insero, alterao e eliminao efectuadas sobre uma vista podem ser transpostas para a tabela

    fsica, desde que os pressupostos anteriormente referidos sejam salvaguardados.

    Exemplo:

    Tendo em considerao a relao actor(codigo, nome, nacionalidade):

    CREATE VIEW TESTE1 AS

    SELECT CODIGO, NOME

    FROM ACTOR;

    Esta vista poder ser modificvel, desde que o

    campo nacionalidade da tabela fsica actor,

    admita valores nulos ou um determinado valor

    por defeito, caso contrrio, teramos que incluir o campo nacionalidade na clusula SELECT

  • 34

    Vistas - continuao Manipulao de Vistas:

    - Remoes

    DELETE FROM FILMESPARAMOUNT

    WHERE TITULO LIKE %TREK%;

    Nota: Os caracteres % significam que a cadeia de caracteres pode ter qualquer sequncia antes e depois da string

    TREK.

    - Modificaes

    UPDATE FILMESPARAMOUNT SET ANO=1979 WHERE TITULO LIKE %TREK%;

    - Remover uma vista

    DROP VIEW FILMESPARAMOUNT;

  • 35

    Vistas e Segurana

    Vistas podem ser usadas para apresentar a informao necessria, escondendo detalhes das tabelas base

    Comandos GRANT e REVOKE podem ser usados para controlar acessos a tabelas base e vistas.

    Em conjunto com a possibilidade de definir vistas, proporciona uma mecanismo poderoso de controlo de acessos.

    A segurana pode chegar at ao nvel do campo

    Criar uma vista que devolve um campo de apenas um tuplo

    Permitir o acesso (GRANT) a essa vista.

    Exemplos:

    GRANT INSERT, SELECT ON ACTOR TO CRAMOS;

    CRAMOS pode inserir e consultar tuplos da tabela ACTOR.

    GRANT DELETE ON ACTOR TO RAMOS WITH GRANT OPTION;

    RAMOS pode apagar tuplos da tabela ACTOR e autorizar outros a fazer o mesmo.

  • 36

    Vistas e Segurana - Continuao

    GRANT UPDATE (SALARIO) ON EXECUTIVO TO AVENTURA;

    AVENTURA pode modificar o campo SALARIO da tabela EXECUTIVO.

    GRANT SELECT ON ACTOR TO CRAMOS, AVENTURA;

    CRAMOS e AVENTURA podem consultar tuplos da tabela ACTOR.

    REVOKE SELECT ON ACTOR FROM CRAMOS, AVENTURA;

    Retira privilgios de consulta a CRAMOS e AVENTURA sobre a tabela ACTOR.

  • 37

    SQL Linguagem de Manipulao de Dados (LMD)

    Tendo como base as relaes:

    actor(codigo, nome, nacionalidade)

    filme(codigo, titulo, ano, realizador)

    realizador(cod, nome, nacionalidade)

    actor_filme(filme, actor)

    Seleccionar Tuplos de uma ou mais relaes:

    SELECT

    FROM

    WHERE

    ORDER BY

    Operadores de comparao: =; ; >; >=;

  • 38

    SQL Linguagem de Manipulao de Dados (LMD)

    EXEMPLOS:

    Seleccionar o nome e a nacionalidade de todos os actores, ordenados pelo nome dos actores

    SELECT NOME, NACIONALIDADE

    FROM ACTOR

    ORDER BY NOME;

    Seleccionar todos os atributos dos actores residentes nos EUA

    SELECT *

    FROM ACTOR

    WHERE NACIONALIDADE LIKE EUA;

  • 39

    SQL Linguagem de Manipulao de Dados (LMD)

    Seleccionar o nome do realizador do filme com o cdigo 1. (Temos que fazer a juno de tabelas)

    SELECT REALIZADOR.NOME

    FROM FILME, REALIZADOR

    WHERE (FILME.REALIZADOR=REALIZADOR.CODIGO AND

    FILME.CODIGO=1);

    Inserir tuplos numa relao

    Exemplos:

    INSERT INTO ACTOR(CODIGO,NOME,NACIONALIDADE) VALUES (4,NICOLE,EUA);

    INSERT INTO EXECUTIVO SELECT * FROM EMPREGADO; -> Esta instruo legtima em casos em que o n de campos e tipos

    de dados so compatveis entre a origem e o destino.

    Eliminar tuplos de uma relao

    Exemplos:

    Eliminar o actor cujo codigo igual a 1.

    DELETE FROM ACTOR WHERE CODIGO=1;

  • 40

    SQL Linguagem de Manipulao de Dados (LMD) Alterar tuplos de uma relao

    Pretende-se que os actores cuja nacionalidade EUA passe a ser AMERICANA.

    UPDATE ACTOR SET NACIONALIDADE=AMERICANA

    WHERE NACIONALIDADE LIKE EUA;

    Operaes com conjuntos

    INTERSEPT - Interseco (No SQL-Server com EXISTS) EXCEPT - Diferena (No SQL-Server com NOT EXISTS)

    UNION - Unio (Reunio)

    PROFESSOR EXECUTIVO

    Codigo Nome Codigo Nome

    1 Carlos 1 Carlos

    2 Rui Marteleiro 3 Ana Rita

    3 Ana Rita 4 Ricardo Baba

  • 41

    SQL Linguagem de Manipulao de Dados (LMD)

    Exemplos

    (SELECT * FROM PROFESSOR) UNION

    (SELECT * FROM EXECUTIVO);

    Codigo Nome

    1 Carlos

    2 Rui Marteleiro

    3 Ana Rita

    4 Ricardo Baba

    (SELECT * FROM PROFESSOR) EXCEPT

    (SELECT * FROM EXECUTIVO);

    Codigo Nome

    2 Rui Marteleiro

    (SELECT * FROM PROFESSOR)

    INTERSEPT (SELECT * FROM EXECUTIVO);

    SQL-SERVER: (SELECT * FROM PROFESSOR WHERE NOT EXISTS

    (SELECT * FROM EXECUTIVO));

    SQL-SERVER: (SELECT * FROM PROFESSOR WHERE EXISTS

    (SELECT * FROM EXECUTIVO));

  • 42

    Codigo Nome

    1 Carlos

    3 Ana Rita

  • 43

    SQL Linguagem de Manipulao de Dados (LMD)

    Operaes de Agregao

    So operaes efectuadas sobre conjuntos de tuplos. Estas pode ser:

    AVG() - Mdia dos valores do atributo especificado MAX() - Mximo dos valores do atributo especificado MIN() - Mnimo dos valores do atributo especificado

    SUM() - Somatrio dos valores do atributo especificado COUNT(*) - Conta o nmero de tuplos daquele conjunto

    EXEMPLOS:

    Nmero de actores que tenho na minha base de dados.

    SELECT COUNT(*) FROM ACTOR;

    Nmero de actores de cada pas.

    SELECT COUNT(*) FROM ACTOR GROUP BY NACIONALIDADE;

  • 44

    SQL Linguagem de Manipulao de Dados (LMD) Nmero de actores dos EUA

    SELECT COUNT(*) FROM ACTOR

    WHERE NACIONALIDADE LIKE EUA GROUP BY NACIONALIDADE;

    Titulo do filme mais antigo realizado por um realizador portugus.

    SELECT MIN(ANO), TITULO FROM FILME, REALIZADOR WHERE (FILME.REALIZADOR=REALIZADOR.COD AND REALIZADOR.NACIONALIDADE LIKE PORTUGUESA)

    GROUP BY FILME.REALIZADOR;

    Escolha dos Grupos, com recurso clusula HAVING

    A escolha dos grupos pode ser baseada numa propriedade de agregao do grupo

    HAVING

    Exemplo Actor(nome, morada, sexo, nascimento) Filme(titulo, ano, duracao, a_cores, estudio, produtor)

    Participa(filme, ano, actor, salario) Estudio(nome, morada, presidente)

    Executivo(#certificado, nome, morada, salario)

  • 45

    SQL Linguagem de Manipulao de Dados (LMD)

    Qual a soma das duraes dos filmes produzidos por um produtor que produziu pelo menos um filme antes de 1930?

    SELECT NOME, SUM(DURACAO)

    FROM EXECUTIVO, FILME WHERE PRODUTOR=#CERTIFICADO

    HAVING MIN(ANO)100000 GROUP BY ANO

    HAVING COUNT(*)>1;

  • 46

    SQL Server Procedures

    Um procedimento um conjunto de operaes que so armazenadas na base de dados identificados por um nome, e que podem ser executados sempre que for necessrio. Para alm disso, um procedimento pode retornar um

    determinado valor. As operaes so especificadas atravs de instrues em SQL.

    Criao de uma PROCEDURE:

    CREATE PROCEDURE @,@,....,@

    AS ..........

    [RETURN ] GO

    Eliminao de uma PROCEDURE:

    DROP PROCEDURE

    Execuo de uma PROCEDURE:

    DECLARE @REF INTEGER EXEC @REF= ,,....,

    GO

  • 47

    SQL Server Procedures

    Mais algumas instrues.......

    SET @i=10;

    Permite atribuir um determinado valor a uma varivel.

    RETURN 2;

    Retorna o valor 2 da procedure.

    PRINT Mensagem

    Visualiza uma mensagem no ecr.

  • 48

    SQL Server Anexos Nesta seco pretende-se apresentar alguns exemplos sobre os contedos apresentados anteriormente. Os exemplos apresentados basearam-se no esquema relacional apresentado na pgina 39 da presente documentao.

    Triggers:

    Criar um trigger que impea a possibilidade de um actor participar em mais do que 4 filmes e auferir um salrio mdio superior a 500000

    drop trigger exemplo1 go

    create trigger exemplo1 on participa for insert, update as

    declare @actor int select @actor=count(*) from participa order by actor having avg(salario)>500000

    if @actor>4 begin rollback

    end else

    begin print Insero Sucedida! end

    go

  • 49

    SQL Server Anexos(Continuao)

    Cursores:

    Um cursor uma relao virtual, decorrente de uma instruo em SQL.

    EXEMPLO:

    /* Cria o cursor*/ declare actores cursor for select * from actor

    /* Abre o cursor*/

    open actores /* Percorre o cursor*/

    while @@fetch_status=0 begin

    /* Avana para o prximo tuplo*/

    fetch next from actores end

    /* Fecha o cursor*/ close actores

    /*Elimina o cursor da memria */ deallocate actores

    declare @CustId nchar(5)

    declare @CustName varchar(50)

    declare @RowNum int

    declare CustList cursor for

    select top 5 CustomerID,CustomerName from Northwind.dbo.Customers

    OPEN CustList

    FETCH NEXT FROM CustList

    INTO @CustId,@CustName

    set @RowNum = 0

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @RowNum = @RowNum + 1

    print cast(@RowNum as char(1)) + ' ' + @CustId + + @CustName

    FETCH NEXT FROM CustList

    INTO @CustId,@CustName

    END

    CLOSE CustList

    DEALLOCATE CustList

  • 50

    SQL Server Anexos(Continuao)

    Procedimentos:

    Criar um procedimento que permita determinar a mdia do salrio de um determinado empregado cujo cdigo passado como argumento.

    create procedure media_sal @num integer as

    declare @media real set @media=select avg(salario) from participa where actor=@num group by actor

    return media go

    NOTA:

    Podem-se definir argumentos para como OUTPUT, assim, podem receber valores que podero ser lidos fora do

    procedimento. Exemplo:

    create procedure media_sal @num integer, @resultado output as

    declare @media real set @media=select avg(salario) from participa where actor=@num group by actor

    return media go

    Invocao:

  • 51

    declare @sal real set @sal=0

    execute up_PComp c, @sal output print @sal

    GO