Baixe o app para aproveitar ainda mais
Prévia do material em texto
Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 1 ESCOLA SUPERIOR CANDIDO MENDES Análise e Desenvolvimento de Sistemas Disciplina: Banco de Dados II (SQL Server) Autoria: Prof. Flavio Ferro Revisão : Alan F Sousa Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 2 ESCOLA SUPERIOR CANDIDO MENDES Sumário AULA 1 .............................................................................................................................................................................. 4 *** REVISÃO *** ............................................................................................................................................................ 4 O que é um Banco de dados ........................................................................................................................................ 4 Sistemas Gerenciadores de Banco de Dados .............................................................................................................. 4 Banco de dados Relacional ......................................................................................................................................... 5 Banco de dados Relacional - Continuação ................................................................................................................. 5 Linguagem de Banco de Dados (SQL) - Structured Query Language ........................................................................ 6 Modelo de computação Cliente/Servidor ................................................................................................................... 6 O que é o SQL Server? ............................................................................................................................................... 7 Ferramentas de Administração .................................................................................................................................. 7 Diretórios....................................................................................................................................................................................7 Bancos de Dados do Sistema ...................................................................................................................................... 8 Máster: ......................................................................................................................................................................................8 Model: .......................................................................................................................................................................................8 Tempdb: ....................................................................................................................................................................................8 Msdb: .........................................................................................................................................................................................8 SQL Server Query Analyzer ........................................................................................................................................ 9 Usando o Query Analyzer ..........................................................................................................................................................9 AULA 2 ............................................................................................................................................................................ 10 CREATE TABLE ............................................................................................................................................................ 10 Sintaxe: ..................................................................................................................................................................... 10 Temporary Tables ..................................................................................................................................................... 12 COMANDO ALTER ........................................................................................................................................................ 12 Sintaxe: ..................................................................................................................................................................... 13 DROP TABLE ............................................................................................................................................................. 14 Sintaxe: ..................................................................................................................................................................... 14 EXERCÍCIOS DE FIXAÇÃO ............................................................................................................................................. 15 AULA 3 ............................................................................................................................................................................ 18 INSERT ....................................................................................................................................................................... 18 Sintaxe: ..................................................................................................................................................................... 18 INSERT INTO ........................................................................................................................................................... 20 SELECT INTO .......................................................................................................................................................... 22 AULA 4 ............................................................................................................................................................................ 23 SELECT ...................................................................................................................................................................... 23 Sintaxe ...................................................................................................................................................................... 23 Predicados: ............................................................................................................................................................... 26 Usando Letras ........................................................................................................................................................... 27 Operadores Aritiméticos ........................................................................................................................................... 28 Operadores lógicos ................................................................................................................................................... 28 Demais Operadores .................................................................................................................................................. 28 Operadores Negativos .............................................................................................................................................. 29 Exemplos : ................................................................................................................................................................30 AULA 5 ............................................................................................................................................................................ 32 Exercícios usando Operadores e instruções Select (Banco Pubs) ............................................................................ 32 Respostas dos exercícios usando Operadores e instruções Select (Banco Pubs) ..................................................... 33 AULA 6 ............................................................................................................................................................................ 34 O Catálogo do sistema .............................................................................................................................................. 34 Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 3 ESCOLA SUPERIOR CANDIDO MENDES Tabelas do Sistema ................................................................................................................................................... 34 AULA 7 ............................................................................................................................................................................ 36 Exercício de acesso às tabelas de sistema (Banco Northwind) ................................................................................ 36 AULA 8 ............................................................................................................................................................................ 40 FERRAMENTAS ............................................................................................................................................................. 40 SQL Server Enterprise Manager .............................................................................................................................. 40 Action ........................................................................................................................................................................ 41 Views ......................................................................................................................................................................... 41 O menu Tools ............................................................................................................................................................ 41 Query Designer ......................................................................................................................................................... 41 AULA 9 ............................................................................................................................................................................ 46 EXERCÍCIO DE ACESSO ÀS TABELAS DE SISTEMA UTILIZANDO O ENTERPRISE MANAGER ....................................... 46 AULA 10 .......................................................................................................................................................................... 58 UPDATE ...................................................................................................................................................................... 58 Sintaxe ...................................................................................................................................................................... 58 AULA 11 .......................................................................................................................................................................... 60 DELETE ...................................................................................................................................................................... 60 AULA 12 .......................................................................................................................................................................... 63 FUNÇÕES AGREGADAS (OU DE AGRUPAMENTO) ........................................................................................................ 63 Agrupamentos ........................................................................................................................................................... 63 Having ...................................................................................................................................................................... 64 AULA 13 .......................................................................................................................................................................... 67 EQUI-JUNÇÃO ( JUNÇÃO POR IGUALDADE - JOIN ) .................................................................................................... 67 As Sub-Consultas ...................................................................................................................................................... 68 AULA 14 .......................................................................................................................................................................... 71 Exercícios: ................................................................................................................................................................ 71 AULA 15 .......................................................................................................................................................................... 76 SQL Server Books Online ......................................................................................................................................... 76 Procura por conteúdo [Contents] .............................................................................................................................................. 78 Procura por índice [Index] ........................................................................................................................................................ 78 Usando a guia Pesquisar [Search] ............................................................................................................................................ 78 Guia Favoritos [Favorites] ........................................................................................................................................................ 78 AULA 16 .......................................................................................................................................................................... 79 VIEW (VISÕES) ............................................................................................................................................................. 79 Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 4 ESCOLA SUPERIOR CANDIDO MENDES Aula 1 *** Revisão *** O que é um Banco de dados Banco de dados é uma coleção de informações, tabelas de dados e outros objetos que são organizados e apresentados para servir um propósito específico, com as facilidades de pesquisa, classificação e combinação de dados. Um Banco de Dados contém os dados dispostos numa ordem pré-determinada em função de um projeto de sistema, sempre para um propósito muito bem definido. Um Banco de Dados representará sempre aspectos do Mundo Real. Assim sendo uma Base de Dados (ou Banco de Dados, ou ainda BD) é uma fonte de onde poderemos extrair uma vasta gama de informações derivadas, que possui um nível de interação comeventos como o Mundo Real que representa. Os Administradores de Banco de Dados (DBA) são responsáveis pelo controle ao acesso aos dados e pela coordenação da utilização do BD. Banco de dados é um conjunto integrado, compartilhado de dados – um repositório. Os dois conceitos mais importantes na definição são: compartilhado e integrado. Compartilhado significa que muitos usuários podem utilizar os dados no banco de dados, concorrentemente. Integrado significa a ausência parcial ou total de redundância, ou seja, a mesma informação gravada mais de uma vez. Sistemas Gerenciadores de Banco de Dados Vamos definir sistemas gerenciadores de banco de dados DBMS. Um DBMS é um software que gerencia a massa de dados e a política de acesso aos mesmos. A palavra gerenciamento incorpora várias funções, entre elas: segurança, uso Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 5 ESCOLA SUPERIOR CANDIDO MENDES concorrente e restauração de dados. Usuários e programas lêem e gravam dados interagindo com DBMS. Banco de dados Relacional Um sistema gerenciador de banco de dados (SGBD) como o SQL Server é responsável por armazenar dados de forma confiável e permitir fácil recuperação e atualização desses dados. Um SGBD relacional armazena dados de forma relacional, isto é na forma de linhas e colunas. Banco de dados Relacional - Continuação No modelo proposto por Codd, as tabelas possuem as seguintes propriedades adicionais: 1. Não existem linhas duplicadas; 2. Não existe nenhuma ordem implícita das linhas; 3. Não há nenhuma ordem implícita das colunas, porque cada valor de coluna possui seu próprio domínio. Outros dois conceitos fazem parte do modelo relacional: chave primária (Primary Key) e estrangeira (Foreign Key). A chave primária de uma tabela é um conjunto de coluna, que pode ser unitário, mas nunca vazio, cujos valores identificam unicamente as linhas da tabela. O conceito de chave primária é o que unicamente identifica uma entidade, porém Banco de Dados Banco de Dados Banco de Dados SGBD Sistema Gerenciador de Banco de dados APLICATIVOS Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 6 ESCOLA SUPERIOR CANDIDO MENDES as entidades nem sempre são representadas com uma relação um para um com as tabelas. A chave estrangeira é um conjunto de colunas cujo valor é a chave primária de outra tabela. Linguagem de Banco de Dados (SQL) - Structured Query Language O nome SQL deriva-se de “Structured Query Language” (linguagem estruturada de pesquisa). A linguagem SQL é composta por um grupo de comandos que facilitam a definição, manipulação e controle de dados em um banco de dados relacional. É a linguagem usada para inserir dados, consultar, atualizar e gerenciar os sistemas de banco de dados relacional. SQL é a linguagem de banco de dados mundialmente usada. Com SQL, você pode recuperar dados, criar banco de dados e seus objetos, adicionar dados, modificar e executar outras funções complexas. Você pode também alterar a configuração do servidor, modificar a estrutura do banco de dados e outros ajustes. Muitas dessas capacidades são implementadas usando uma das três categorias da SQL: Linguagem de definição de dados (DDL), Linguagem de manipulação de dados (DML), e Linguagem de Controle de dados (DCL). Modelo de computação Cliente/Servidor É um sistema de computação no qual dois ou mais computadores compartilham processamento em uma rede. O computador servidor gerencia o recurso compartilhado, como o banco de dados, e responde às requisições dos clientes para uso desse recurso. O computador cliente interage com um usuário e cria requisições para o uso de um recurso compartilhado. O modelo Cliente/Servidor separa as funções de uma aplicação em duas partes distintas: um componente “front end” e um componente “back end”. A aplicação cliente apresenta e manipula os dados na estação de trabalho; e o servidor armazena, recupera e protege os dados. Servidor de Banco de Dados ou Back-end Aplicação ou Front-end SQL DADOS SELECIONADOS Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 7 ESCOLA SUPERIOR CANDIDO MENDES O que é o SQL Server? É um servidor de bando de dados relacional, destinado a ambientes de processamento de transações, baseado na linguagem SQL (Structure Query Language). Um Servidor de banco de dados é, na realidade, um sistema gerenciador de bancos de dados (DBMS – Database Management System), baseado no modelo cliente/servidor, que divide o processamento em dois componentes – o cliente, ou front-end e o servidor, ou back- end. O SQL Server é o componente back-end, o servidor de banco de dados, com o qual variadas aplicações front-end se comunicam, tipicamente através de uma rede local (LAN – local area network). Ferramentas de Administração • Enterprise Manager: gerencia vários servidores, permitindo executar qualquer tarefa relacionada ao SQL Server. Como será visto adiante, ele roda dentro MMC (Microsoft Management Console). Para executá-lo através de Iniciar, Executar, entre com a seguinte instrução: mmc /s "pasta-base_do_SQLServer\BINN\SQL Server Enterprise Manager.MSC", substituindo pasta-base_do_SQLServer pela pasta onde você instalou o SQL Server 7. Por padrão, é C:\MSSQL7. • Query Analyzer(ISQLW.EXE): permite administrar diretamente o SQL Server usando comandos Transact-SQL. Os comandos SQL podem ser executados interativamente, ou podem ser executados de procedimentos armazenados ou scripts. • SQL Server Books Online: toda a documentação do SQL Server, para consultar on-line. Permite fazer pesquisas de texto na documentação. Para executá-lo, em Iniciar, Executar, entre com HH pasta_base_do_SQLServer\BOOKS\SQLBOL.CHM, onde pasta_base_do_SQLServer é o diretório onde o SQL Server foi instalado. Por padrão, é C:\MSSQL7. Diretórios O SQL cria os seguintes subdiretórios durante a instalação: • BACKUP - Contém arquivos de backup. • BINN - Contém arquivos executáveis das ferramentas de administração do SQL e arquivos do Help Online, e DLLs. • HTML - Armazena arquivos HTML e arquivos relacionados. • DATA - Contém os arquivos de dados. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 8 ESCOLA SUPERIOR CANDIDO MENDES • JOBS - Armazena informações sobre tarefas [Jobs] do SQL Server • LOG - Contém arquivos de log de erro. Bancos de Dados do Sistema Ao instalar o SQL Server, são criados os seguintes bancos de dados do sistema: Máster: Controla os bancos de dados do usuário e a operação do SQL Server. Tem como tamanho inicial 16 MB. É importante manter um backup atualizado desse banco de dados. Contém informações sobre: - Contas de login - Processos em execução - Mensagens de erro - Bancos de dados criados no servidor - Espaço alocado para cada banco de dados - Travas [locks] de linha ativas - Procedimentos armazenados do sistema Model: É um modelo usado para criação de novos bancos de dados, que pode ser usado para definir padrões, como autorizações default de usuário, opções de configuração,tipos de dados etc. Sempre que um banco de dados do usuário é criado, o conteúdo de model é copiado para ele. Seu tamanho inicial é 2.5 MB. Esse modelo pode ser alterado. Tempdb: Usado para armazenar tabelas temporárias e resultados intermediários de consultas. Geralmente o seu conteúdo é excluído sempre que um usuário se desconecta. Ele cresce automaticamente conforme é necessário. Seu tamanho inicial é de 8 Mb. Msdb: Usado pelo serviço SQLServerAgent, para controlar tarefas como replicação, agendamento de tarefas, backups e alertas. Contém algumas tabelas de sistema, que armazenam informações usadas pelo SQLExecutive.Seu tamanho inicial é 12 Mb. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 9 ESCOLA SUPERIOR CANDIDO MENDES SQL Server Query Analyzer O Analisador de Consultas [Query Analyzer] fornece uma interface gráfica para analisar o plano de execução de uma ou múltiplas consultas, ver os dados de resultado, e recomendar índices. Usando o Query Analyzer Conecte-se ao servidor SQL Server local. Efetue logon com a conta e senha (se houver) do SA que você especificou na instalação. Selecione pubs da lista na janela de consulta. Esse será o banco de dados no qual executaremos a consulta. Você também poderia ao invés de especificar o banco de dados, usar a declaração Use antes de sua consulta. Exemplo: Use Pubs; Escreva o seguinte na parte superior da janela se a janela tiver mais de uma parte. SELECT * FROM authors Selecione o método de execução. São disponíveis quatro opções diferentes de execução: Execução padrão (Ctrl+T) mostra os resultados na forma de um arquivo texto. F5 (run) executa a consulta, qualquer que seja o modo de execução selecionado. Executar para uma grade (Ctrl+D) fornece um formato mais agradável de se visualizar, em uma planilha, uma estrutura parecida com tabelas (um layout de linha-coluna). Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 10 ESCOLA SUPERIOR CANDIDO MENDES Aula 2 Create Table Vamos providenciar a criação de tabelas, na qual iremos inserir algumas linhas, para depois selecioná-las e alterá-las, fechando assim um ciclo de comandos SQL básicos, os quais serão posteriormente analisados. Ao final, apagaremos nossa tabela de teste. Para criarmos uma tabela, deveremos utilizar a declaração CREATE TABLE, unindo a ela o nome que será atribuído ao objeto e suas características. Sintaxe: CREATE TABLE [database.[owner].]table_name ( [{ col_name column_properties } {next_col_name|next_constraint}...] ) [ON filegroup] Tipos de dados Tipos de dados supridos pelo sistema Inteiros (Integer) Bit Bolenano: 0 ou 1 Int 4 bytes de (–2.147.483.648 a 2.147.483.648) Smallint 2 bytes de (-32768 a 32768) Tinyint 1 byte de (0 a 255) Numéricos (Numeric) Decimal [(n[,m])] ou Numeric n - número de dígitos ou de caracteres m - número de casas decimais Intervalo de (-10^38 a 10^38 –1) Monetários (Money) Money 8 bytes de (-922.337.203.685.477,5808 a 922.337.203.685.477,5808) Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 11 ESCOLA SUPERIOR CANDIDO MENDES Smallmoney 4 bytes de (-214.748,3648 a 214.748,364) Numeros Aproximados Float Valores em ponto flutuante, de (-1,79E308 a 1,79E308) Real Valores em ponto flutuante, de (-3,40E38 a 3,40E38) Continuação ... Tipos de dados Tipos de dados supridos pelo sistema Data Hora (Datetime) Datetime 4 bytes, (01/01/1753 a 31/12/9999) precisão de milisegundos) Smalldatetime 2 bytes, (01/01/1900 a 31/12/9999) precisão de minutos Especial Timestamp Número único de banco de dados, evita que dois registros sejam alterados ao mesmo tempo Caracteres (String) Char(n) String com tamanho fixo, máximo de 8000 Varchar(n) String com tamanho variável, máximo de 8000 Text(n) String com tamanho fixo, de (2^31-1 a 2.147.483.647) Exemplos: Nome da coluna Tipo de Dados Null ou não Null CREATE TABLE clientes ( cliente numeric (8,0) not null PRIMARY KEY, nome varchar (60) not null, telefone varchar (20) null ) CREATE TABLE pedidos ( numero int NOT NULL , cliente numeric(8, 0) NOT NULL , telefone varchar(20) NOT NULL , PRIMARY KEY ( cliente, numero ), Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 12 ESCOLA SUPERIOR CANDIDO MENDES FOREIGN KEY ( cliente ) REFERENCES clientes ( cliente ) ) Temporary Tables Para criar uma tabela temporária, basta anexar o “#” ao nome da talbela (#table_name). Se uma tabela temporária é criada em uma procedure ou aplicação, que possa ser executada ao mesmo tempo por diversos usuários, o SQL Server tem que ser capaz de distinguir as tabelas criadas pelos diferentes usuários, para isso cria internamente um sufixo numérico para cada nome de tabela temporária. O nome completo da tabela, ou seja, o nome usado no Create table mais o sufixo criado pelo sqlserver são armazenados em sysobjects no banco tempdb e não pode exceder 116 caracteres. Estas tabelas temporárias são automaticamente apagadas quando o usuário sai da conexão a menos que ele tenha a apagado antes usando o drop table. As tabelas criadas temporariamente em uma procedure são apagadas automaticamente quando a procedure é finalizada. Estas tabelas podem ser referenciadas por procedures executadas pela procedure que as criou, porém nunca pela procedure que a chamou. Todas as outras tabelas temporárias são automaticamente apagadas quando a sessão é encerrada. CREATE TABLE #MyTempTable (reg INT PRIMARY KEY) INSERT INTO #MyTempTable VALUES (1) select * from #MyTempTable Comando Alter Este comando permite inserir/eliminar atributos nas tabelas já existentes. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 13 ESCOLA SUPERIOR CANDIDO MENDES Sintaxe: Comando: ALTER TABLE < nome_tabela > ADD / DROP ( nome_atributo1 < tipo > [ NOT NULL ], nome_atributoN < tipo > [ NOT NULL ] ) ; Não existe nenhum comando SQL que permita eliminar algum atributo de uma relação já definida. Assim caso você deseje eliminar uma chave primária devidamente referenciada em outra tabela como chave estrangeira, ao invés de obter a eliminação do campo, obterá apenas um erro. ALTER TABLE clientes ADD idade integer; ALTER TABLE clientes drop column idade; Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 14 ESCOLA SUPERIOR CANDIDO MENDES DROP TABLE Este procedimento irá remover a tabela completamente, não existindo nenhum procedimento de “recovery”. Portanto, assegure-se de que a tabela em questão realmente não é mais necessária, ou, pelo menos, faça um backup do bancoantes. Ao apagar uma tabela, saiba que os relacionamentos por ventura com ela existente impedirão sua deleção. Por isso, você deverá começar a apagar as tabelas desde as “filhas”. DROP TABLE clientes Msg 3726, Level 16, State 1 Could not drop object 'clientes'. It is being referenced by a foreign key constraint. Assim, para apagar a tabela clientes, antes será necessário apagarmos a tabela pedidos. DROP TABLE pedidos DROP TABLE clientes Comando Drop Este comando elimina a definição da tabela, seus dados e referências. Sintaxe: DROP TABLE < nome_tabela > ; Vamos apagar a tabela clientes Drop table clientes Conseguiu ? Porque ? sp_helpconstraint clientes alter table pedidos drop FK__pedidos__clientes__.... ALTER TABLE pedidos ADD CONSTRAINT FK__pedidos__cliente__1B9317B3 FOREIGN KEY (cliente) REFERENCES clientes(cliente); sp_helpconstraint pedidos; alter table pedidos drop PK__pedidos__....; Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 15 ESCOLA SUPERIOR CANDIDO MENDES ALTER TABLE pedidos ADD CONSTRAINT PK__pedidos__1A9EF37A PRIMARY KEY (cliente,numero); Exercícios de fixação 1) Crie duas tabelas chamadas Alunos e Cadeiras. As características de cada tabela são: 1. A tabela de Alunos terá 3 colunas com preenchimento obrigatório: a. Matrícula (com números inteiros) b. Nome dos Alunos com até 60 caracteres c. Idade do aluno 2. A tabela Cadeiras terá 4 colunas com preenchimento obrigatório a. Cadeira_nome com 25 caracteres fixos b. Professor _nome com até 60 caracteres c. Matricula do aluno (com números inteiros) d. Data de matrícula do aluno na cadeira 3. Dados Complementares: a. A tabela Alunos não poderá ter duas matrículas iguais; b. A tabela Cadeiras não poderá ter duas matrículas na mesma cadeira com a mesma data de matrícula; c. A tabela Cadeiras só deve aceitar matrículas previamente cadastradas na tabela Alunos; 2) Inclua uma coluna chamada telefone na tabela dos alunos que aceite no máximo 20 letras e números, esta informação não é obrigatória. 3) Exclua a tabela de Alunos; Quais as opções para conseguir tal exclusão? 4) Crie novamente a tabela Alunos incluindo o campo telefone sendo que desta vez, a informação do telefone é obrigatória; Se necessário, crie novamente a tabela de Cadeiras ou crie apenas a FK de Cadeiras; Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 16 ESCOLA SUPERIOR CANDIDO MENDES Respostas: 1.1 - Create de Alunos: CREATE TABLE Alunos ( Matricula int not null PRIMARY KEY, nome varchar(60) not null, Idade int not null ) 1.2 - Create de Cadeiras: CREATE TABLE Cadeiras ( Cadeira_nome char(25) not null, Professor_nome varchar(60) not null, Matricula int not null, Data_mat datetime not null PRIMARY KEY ( Matricula, Cadeira_nome, Data_mat ), FOREIGN KEY ( Matricula ) REFERENCES Alunos ( Matricula ) ) 2 – Inclusão da coluna telefone: ALTER TABLE Alunos ADD telefone varchar(20); 3 - Exclusão da tabela Alunos: • sp_helpconstraint Alunos; • alter table Cadeiras drop FK__.... • Drop table Alunos; Ou • Drop table Cadeiras; Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 17 ESCOLA SUPERIOR CANDIDO MENDES • Drop table Alunos; 4 – Recriando a tabela Alunos com a coluna telefone: CREATE TABLE Alunos ( Matricula int not null PRIMARY KEY, nome varchar(60) not null, Idade int not null, Telefone varchar(20) not null ) 4 – Complemento - Recriando a FK na tabela Cadeiras: ALTER TABLE Cadeiras ADD CONSTRAINT FK__Cadeiras_Alunos FOREIGN KEY (Matricula) REFERENCES Alunos(Matricula); Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 18 ESCOLA SUPERIOR CANDIDO MENDES Aula 3 INSERT Consulta anexação de um único registro: Sintaxe: 1. INSERT INTO destino [(campo1[, campo2[, ...]])] VALUES (valor1[, valor2[, ...]) Para inserirmos dados em uma tabela, devemos informar qual é a tabela, quais os campos que estamos inserindo e quais são seus valores. 2. INSERT clientes (cliente, nome, telefone) values (1001,'João','445-0988'); 3. INSERT clientes (cliente, nome, telefone) values (1002,'Alberto','465-9887'); 4. INSERT clientes (cliente, nome, telefone) values (1003,'Maria','789-9877'); O Telefone não é obrigatório, sendo assim ... 5. INSERT clientes (cliente, nome, telefone) values (1004,'Sônia',null) A ordem dos campos pode ser diferente da ordem que estes possuem na tabela: 6. INSERT clientes (nome, cliente, telefone) values ('Carlos',1005,null) Se você omitir a lista de campos, a cláusula VALUES deve incluir um valor para cada campo na tabela; caso contrário, um erro ocorrerá em INSERT. Caso existam valores para todos os campos, podemos omitir seus nomes. 7. INSERT clientes values (1006,'Viu só?','999-0000') Conseguiu ? Porque ? Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 19 ESCOLA SUPERIOR CANDIDO MENDES Vamos inserir dados na tabela Pedidos 8. INSERT pedidos (numero, cliente, telefone) values (123456,9999,12347789) Conseguiu ? Porque ? 9. INSERT pedidos (numero, cliente, telefone) values (123456,1004,null) Conseguiu ? Porque ? Se sua tabela de destino contém uma chave primária, você deve acrescentar valores únicos, não Null ao campo ou campos da chave primária. Caso contrário, o programa principal de banco de dados não anexará os registros. 10. INSERT pedidos (numero, cliente, telefone) values (null,1004,222222) 11. INSERT pedidos (numero, cliente, telefone) values (123456,1004,33964567) Conseguiu ? Porque ? 12. INSERT pedidos (numero, cliente, telefone) values (123456,1004,222222) Conseguiu ? Porque ? 13. INSERT pedidos (numero, cliente, telefone) values (654321,1004,222222) 14. delete from clientes where cliente = 1001 15. delete from clientes where cliente = 1004 Conseguiu ? Porque ? Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 20 ESCOLA SUPERIOR CANDIDO MENDES INSERT INTO Adiciona um ou vários registros a uma tabela. Isto é referido como consulta anexação. Consulta anexação de vários registros: INSERT INTO destino [(campo1[, campo2[, ...]])] SELECT [origem.]campo1[, campo2[, ...] FROM tabela A instrução INSERT INTO tem as partes abaixo: Parte Descrição destino O nome da tabela ou consulta em que os registros devem ser anexados. origem O nome da tabela ou consulta de onde os dados devem ser copiados. campo1, campo2 Os nomes dos campos aos quais os dados devem ser anexados, se estiverem após um argumentodestino, ou, os nomes dos campos dos quais se deve obter os dados, se estiverem após um argumento origem. tabela O nome da tabela ou tabelas das quais registros são inseridos. A tabela de origem pode especificar uma tabela ou uma consulta. Você pode usar INSERT INTO para anexar um conjunto de registros de outra tabela ou consulta usando a cláusula SELECT ... FROM como é mostrado acima na sintaxe. Neste caso, a cláusula SELECT especifica os campos para acrescentar à tabela destino especificada. Para achar quais registros serão anexados, antes de você executar a consulta anexação, primeiro execute uma consulta seleção que use o mesmo critério de seleção. Uma operação de consulta anexação copia os registros de uma ou mais tabelas em outra. As tabelas que contêm os registros que você anexa não são afetadas pela operação de consulta anexação. Vamos criar uma tabela chamada Novos_Clientes com a mesma estrutura da tabela Clientes. CREATE TABLE Novos_Clientes ( cliente numeric (8,0) not null PRIMARY KEY, nome varchar (60) null, telefone varchar (20) null ) O exemplo abaixo seleciona todos os registros da tabela "Clientes" e os adiciona à tabela "Novos Clientes" (quando não são designadas colunas individuais, os nomes das colunas das tabelas SELECT devem corresponder exatamente aos da tabela INSERT INTO). Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 21 ESCOLA SUPERIOR CANDIDO MENDES INSERT INTO Novos_Clientes SELECT * From clientes Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 22 ESCOLA SUPERIOR CANDIDO MENDES Esse exemplo seleciona todos os estagiários de uma tabela hipotética "Estagiários" que foram contratados há mais de 30 dias e adiciona seus registros à tabela "Funcionários". INSERT INTO Funcionários SELECT Estagiários.* FROM Estagiários WHERE DataContrato < Now() - 30; Drop table Novos_Clientes SELECT INTO Consulta criação e anexação de vários registros. O exemplo abaixo permite executar o create e o Insert ao mesmo tempo, ou seja, de uma única vez, o comando seleciona todos os registros da tabela "Clientes", cria a tabela "Novos Clientes" e adiciona nesta tabela os registros da tabela Clientes. select * into Novos_clientes from clientes select nome, cliente into Novos_clientes from clientes Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 23 ESCOLA SUPERIOR CANDIDO MENDES Aula 4 SELECT Através do comando select, recuperamos os dados existentes no banco, de acordo com os critérios desejados. Instrui o programa principal do banco de dados para retornar a informação como um conjunto de registros. Sintaxe SELECT [predicado { * | tabela.* | [tabela.]campo1 [AS alias1] [, [tabela.]campo2 [AS alias2] [, ...]]} FROM expressãotabela [, ...] [WHERE... ] [GROUP BY... ] [HAVING... ] [ORDER BY... ] A instrução SELECT tem as partes abaixo: Parte Descrição predicado Um dos seguintes predicados: ALL, DISTINCT ou TOP. Você usa o predicado para restringir o número de registros que retornam. Se nenhum for especificado, o padrão será ALL. * Especifica que todos os campos da tabela ou tabelas especificadas são selecionados tabela O nome da tabela que contém os campos dos quais os registros são selecionados. campo1, campo2 Os nomes dos campos dos quais os dados serão recuperados. Se você incluir mais de um campo, eles serão recuperados na ordem listada. alias1, alias2 Os nomes que serão usados como títulos de colunas em vez dos nomes originais das colunas na tabela. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 24 ESCOLA SUPERIOR CANDIDO MENDES expressãotabela O nome da tabela ou tabelas contendo os dados que você quer recuperar. Para executar esta operação, o programa principal de banco de dados procura a tabela ou tabelas especificadas, extrai as colunas escolhidas, seleciona as linhas que satisfazem o critério e classifica ou agrupa as linhas resultantes na ordem especificada. A instrução SELECT não muda os dados no banco de dados. SELECT é normalmente a primeira palavra em uma instrução SQL. A maior parte das instruções SQL são instruções SELECT. A sintaxe mínima da instrução SELECT é: SELECT campos FROM tabela SELECT nome, telefone From clientes Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 25 ESCOLA SUPERIOR CANDIDO MENDES Observação Caso exista mais de uma sentença em sua janela de queries, e você deseje executar apenas uma, selecione a sentença que você deseja executar, antes de comandar sua execução. O que não estiver selecionado será ignorado. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 26 ESCOLA SUPERIOR CANDIDO MENDES Dados inseridos na tabela pedidos 1. select * from pedidos numero cliente telefone ----------- ---------- ----------- 123456 1002 33964567 654321 1002 222222 123 1003 33964567 123456 1004 33964567 654321 1004 222222 (5 row(s) affected) Predicados: ALL: Seleciona todos os dados da coluna numero, inseridos na tabela pedidos. 2. select all numero from pedidos numero ----------- 123456 654321 123 123456 654321 (5 row(s) affected) Distinct: Seleciona os dados, sem repetição na tabela pedidos. 3. select distinct numero from pedidos numero ----------- 123 123456 654321 (3 row(s) affected) 4. select distinct cliente from pedidos cliente ---------- Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 27 ESCOLA SUPERIOR CANDIDO MENDES 1002 1003 1004 (3 row(s) affected) TOP: Seleciona o primeiro dado, da coluna cliete na tabela pedidos. 5. select top 1 cliente from pedidos cliente ---------- 1002 (1 row(s) affected) Podemos recuperar todas as colunas de uma tabela utilizando o caracter curinga *. SELECT * From clientes A utilização da cláusula WHERE faz com que o uso do comando SELECT seja dos mais freqüentes no dia a dia, pois através dela, poderemos especificar condições de busca, as quais determinarão a quantidade de informações retornadas pelo servidor, ou, muitas vezes, trarão exatamente o que precisamos. Esta última característica, de obtermos exata e somente aquilo que necessitamos é que faz a grande diferença entre um servidor de arquivos, que envia pela rede o arquivo todo, e um gerenciador de bancos de dados, que envia somente o suficiente. Usando Letras Usando Strings em select e Apelidos para colunas" 6. SELECTau_fname, au_lname, 'Identification number:', au_id FROM authors 7. SELECT FIRST = au_fname, LAST = au_lname, IDENTIFICATION ='Identification number:', Author_ID = au_id FROM authors 8. SELECT au_fname as FIRST, au_lname as LAST, 'Identification number:' IDENTIFICATION, au_id Author_ID FROM authors Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 28 ESCOLA SUPERIOR CANDIDO MENDES Operadores Aritiméticos Operação tipos de dados que podem usar esta operação + int, smallint, tinyint, numeric, decimal, float, real, money e smallmoney - int, smallint, tinyint, numeric, decimal, float, real, money e smallmoney / int, smallint, tinyint, numeric, decimal, float, real, money e smallmoney * int, smallint, tinyint, numeric, decimal, float, real, money e smallmoney % int, smallint e tinyint 9. SELECT price, (price * 1.1), title FROM titles; 10. SELECT top 5 price, (price * 1.1) 'Price Ajustado', title FROM titles; Retorna o resto da operação de divisão 11. SELECT top 5 price, 22%3 'Price Ajustado', title FROM titles; Operadores lógicos Operação significado = igual a > maior que >= maior que ou igual a < menor que <= menor que ou igual a Retorna registros cujo valor da coluna price é igual a 19.99 12. SELECT price, title FROM titles where price = 19.99; Retorna registros cujo valor da coluna price é maior 19.99 13. SELECT price, title FROM titles where price > 19.99; Retorna registros onde a coluna price é maior ou igual 19.99 14. SELECT price, title FROM titles where price >= 19.99; Demais Operadores Operação significado between ... and ... entre dois valores ( inclusive ) in ( .... ) lista de valores like com um padrao de caracteres is null é um valor nulo Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 29 ESCOLA SUPERIOR CANDIDO MENDES Retorna registros onde a coluna price está entre 19.99 de forma inclusiva e 22.95 de forma exclusiva 15. SELECT price, title FROM titles where price >= 19.99 and price < 22.95; Retorna registros onde a coluna price está entre 19.99 e 22.95 de forma inclusiva 16. SELECT price, title FROM titles where price between 19.99 and 22.95 Retorna registros onde a coluna price é 19.99 ou 22.95 17. SELECT price, title FROM titles where price in(19.99,22.95); 18. SELECT * From clientes WHERE telefone LIKE ‘4%’ cliente nome telefone ---------- ------------------------------------------------------------ -------- 1001 João 445-0988 1002 Alberto 465-9887 (2 row(s) affected) 19. SELECT * From clientes WHERE telefone LIKE '%77' cliente nome telefone ---------- ------------------------------------------------------------ -------- 1003 Maria 789-9877 (1 row(s) affected) 20. SELECT * From clientes WHERE telefone LIKE '%98%' cliente nome telefone ---------- ------------------------------------------------------------ -------- 1001 João 445-0988 1002 Alberto 465-9887 1003 Maria 789-9877 (3 row(s) affected) Retorna registros onde a coluna price não possui valor 21. SELECT price, title FROM titles where price is null; Operadores Negativos Operação significado <> diferente not nome_coluna = diferente da coluna not nome_coluna > não maior que not between não entre dois valores informados not in não existente numa dada lista de valores not like diferente do padrao de caracteres informado Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 30 ESCOLA SUPERIOR CANDIDO MENDES is not null não é um valor nulo Retorna registros cujo valor da coluna price é diferente de 19.99 22. SELECT price, title FROM titles where price <> 19.99; 23. SELECT price, title FROM titles where not price = 19.99; Retorna registros cujo valor da coluna price não é menor que 19.99 24. SELECT price, title FROM titles where not price < 19.99 Retorna registros onde a coluna price não está entre 19.99 e 22.95 de forma inclusiva 25. SELECT price, title FROM titles where price not between 19.99 and 21.59 Retorna registros onde a coluna price possui valor 26. SELECT price, title FROM titles where price is not null 27. SELECT * From clientes WHERE telefone not LIKE '%98%' Exemplos : 1. Selecione os Empregados cujos salários sejam menores que 1000 ou maiores que 3500. Resp: Necessitaremos aqui a utilização de expressão negativas. SELECT EMPNOME, EMPSALA FROM EMP WHERE EMPSALA NOT BETWEEN 1000 AND 3500; 2. Apresente todos os funcionários com salários entre 700 e 2000, e que sejam Vendedores. Resp: Necessitaremos de consultas com condições múltiplas. Operadores "AND" (E) e "OR" (OU). SELECT EMPNOME, EMPSALA, EMPSERV FROM EMP WHERE EMPSALA BETWEEN 700 AND 2000 AND EMPSERV = 'VENDEDOR'; Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 31 ESCOLA SUPERIOR CANDIDO MENDES 3. Apresente todos os funcionários com salários entre 700 e 2000 ou que sejam Vendedores. SELECT EMPNOME, EMPSALA, EMPSERV FROM EMP WHERE EMPSALA BETWEEN 700 AND 2000 OR EMPSERV = 'VENDEDOR'; 4. Apresente todos os funcionários com salários entre 700 e 2000 e que sejam Vendedores ou Balconistas. SELECT EMPNOME, EMPSALA, EMPSERV FROM EMP WHERE EMPSALA BETWEEN 700 AND 2000 AND ( EMPSERV = 'BALCONISTA' OR EMPSERV = 'VENDEDOR' ); Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 32 ESCOLA SUPERIOR CANDIDO MENDES Aula 5 Exercícios usando Operadores e instruções Select (Banco Pubs) Alguns acessos ao banco de dados para utilizar o comando select, e os operadores: Sabendo que a tabela titles possui duas colunas chamadas: Title_id: Código do título (livro) Price: Preço do Título (Livro) 1. Descubra quais os códigos dos títulos que estão sem preço. 2. Qual o preço do título cujo código é “PC8888” 3. Qual seria o preço do título de código “PC8888” se ele fosse multiplicado por 15 4. Quais os códigos dos títulos cujo valor do preço é diferente de 11.9500 5. Quais os códigos e os preços dos títuloscujo preço é igual ou maior a 11.9500 6. Quais os códigos dos títulos cujo valor do preço é igual ou maior a 11.9500 e menor que 19.9900 7. Quais os códigos e os preços dos títulos cujo valor do preço está entre 20.000 e 21.590 utilize o operador between para mostrar os registros solicitados 8. Quais os códigos e os preços dos títulos cujo valor do preço é 19.99 ou 22.95, usando o operador “IN”. 9. Quais os códigos dos títulos que terminam com 3 10. Mostre quais seriam os comandos que fizessem o inverso dos comandos acima, ou seja, use operadores negativos para não selecionar exatamente os registros que estão sendo selecionados nos exercícios anteriores. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 33 ESCOLA SUPERIOR CANDIDO MENDES Respostas dos exercícios usando Operadores e instruções Select (Banco Pubs) Title_id: Código do título (livro) Price: Preço do Título (Livro) 1. SELECT title_id FROM titles where price is null; 2. SELECT price FROM titles where title_id = "PC8888" 3. SELECT price * 15 “Novo Preço” FROM titles where title_id = "PC8888" 4. SELECT title_id FROM titles where price <> 11.9500 5. SELECT title_id,price FROM titles where price >= 11.9500 6. SELECT title_id,price FROM titles where price >= 11.9500 and price < 19.9900 7. SELECT title, price FROM titles where price between 20.000 and 21.590; 8. SELECT title, price FROM titles where price in(19.99,22.95); 9. SELECT title_id FROM titles where title_id like "%3" 10.1 SELECT Title_id FROM titles where price is not null; 10.2 SELECT price FROM titles where title_id = "PC8888" 10.3 SELECT price * 15 “Novo Preço” FROM titles where title_id <> "PC8888" 10.4 SELECT title_id FROM titles where price = 11.9500 10.5 SELECT title_id,price FROM titles where price < 11.9500 10.6 SELECT title_id,price FROM titles where price < 11.9500 and price >= 19.9900 10.7 SELECT title, price FROM titles where price not between 20.000 and 21.590; SELECT title, price FROM titles where price < 20.000 and price > 21.590; 10.8 SELECT title, price FROM titles where price not in(19.99,22.95); SELECT title, price FROM titles where price <> 19.99 and price <> 22.95; 10.9 SELECT title_id FROM titles where title_id not like "%3" Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 34 ESCOLA SUPERIOR CANDIDO MENDES Aula 6 O Catálogo do sistema Um banco de dados é uma coleção de tabelas e outros objetos relacionados. Existem dois tipos de banco de dados: os bancos de dados do sistema são usados pelo SQL Server para operar e gerenciar o sistema e os bancos de dados do usuário que são usados para armazenar os seus próprios dados. O catálogo do sistema é composto de tabelas no banco de dados Master. Tabelas do Sistema Existem tabelas em cada banco de dados que formam o catálogo do banco de dados. Todas começam com o prefixo sys e algumas, contêm as seguintes informações: sysobjects Informação sobre cada objeto do banco de dados (tabelas, visões, procedimentos, regras, defaults e gatilhos). syscolumns Informação sobre cada coluna de cada tabela, e cada parâmetro de procedimento. sysindexes Informação para cada índice criado e para cada tabela sem índices, além de informações para cada tabela que possui colunas text ou image. sysindexkeys Informação sobreas chaves e as colunas de um índice. sysdepends Registra as dependências entre objetos do banco de dados. syscomments Para cada objeto de banco de dados (visão, regra, default, trigger, procedimento) contém o texto de sua definição. syspermissions Informação sobre permissões atribuídas a usuários, grupos e papéis em um banco de dados. sysfiles Informações sobre cada arquivo de um banco de dados. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 35 ESCOLA SUPERIOR CANDIDO MENDES sysforeignkeys Informações sobre todas as restrições de chaves estrangeiras encontradas em todas as tabelas de um banco de dados. systypes Informação sobre cada tipo de dados (do sistema ou definido pelo usuário). sysusers Informação sobre cada usuário que pode ter acesso ao banco de dados. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 36 ESCOLA SUPERIOR CANDIDO MENDES Aula 7 Exercício de acesso às tabelas de sistema (Banco Northwind) Alguns acessos ao banco de dados para conferir o conteúdo de alguma das tabelas de sistema : 1. -- O Select abaixo lista as principais colunas da tabela sysobjects SELECT name, id, crdate, type FROM sysobjects WHERE type = 'U' order by name 2. -- O Select abaixo lista as principais colunas da tabela sysobjects -- truncando a coluna name em vinte posições SELECT convert(char(20),name), id, crdate, type FROM sysobjects WHERE type = 'U' order by name 3. -- O Select abaixo lista todas as colunas da tabela syscolumns SELECT * FROM syscolumns 4. -- O Select abaixo lista todas as colunas da tabela sysobjects para um ID específico select * from sysobjects WHERE id = 181575685 5. -- O Select abaixo lista todas as colunas da tabela syscolumns para um ID específico SELECT * FROM syscolumns WHERE id = 181575685 6. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 37 ESCOLA SUPERIOR CANDIDO MENDES -- O Select abaixo lista as principais colunas da tabela sysobjects -- truncando a coluna name em vinte posições para nomes começados com "Categ" select convert(char(20),o.name) 'Tabela',c.* from sysobjects o, syscolumns c where o.id = c.id and o.name like 'Categ%' Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 38 ESCOLA SUPERIOR CANDIDO MENDES 7. -- Mostre os nomes das tabelas que tenham uma coluna chamada “pub_id” -- Select para obter o id das tabelas com a coluna desejada select c.id from syscolumns c where c.name = ‘pub_id’ ; -- Select para obter o nome das tabelas com a coluna desejada select o.name from sysobjects o where o.id in (197575742,261575970,645577338,693577509,853578079) and o.type = ‘U’; -- ou em um único passo select o.name from sysobjects o, syscolumns c where o.id = c.id and c.name = ‘pub_id’ and o.type = ‘U’; 8. -- A procedure abaixo, lista informações sobre a tabela chamada Categories -- Entre estas informações estão os nomes de suas colunas sp_help Categories 9. -- O Select abaixo lista as dependências do ID da tabela "Categories" select * from sysdepends where depid = 181575685 10. -- O Select abaixo lista o nome e o tipo de uma das dependências do ID da tabela "Categories" select name,type from sysobjects WHERE id = 837578022 11. -- O Select abaixo lista o texto de uma das dependênciasdo ID da tabela "Categories" select text from syscomments where id = 837578022 12. -- O Select abaixo lista o nome dos indices olhando para o ID da tabela "Categories" select name,* from sysindexes where id = 181575685 13. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 39 ESCOLA SUPERIOR CANDIDO MENDES -- A procedure abaixo, lista informações sobre os índices da tabela chamada Categories sp_helpindex categories Existem também tabelas localizadas apenas no banco de dados master, que compõem o catálogo do sistema. Elas contêm as seguintes informações: sysallocations Informações sobre cada unidade de alocação gerenciada pelo SQL Server • Unidades de alocação: Cada unidade de alocação tem 512 Kb (meio megabyte). Um banco de dados ocupa sempre um número inteiro de unidades de alocação. sysconfigures, syscurconfigs Parâmetros de configuração do SQL Server. sysdatabases Informação sobre os bancos de dados existentes. sysdevices Informação sobre os dispositivos, tais como o dispositivo de fita. syslogins Contas de login. sysmessages Mensagens de erro do sistema sysservers Servidores remotos conhecidos. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 40 ESCOLA SUPERIOR CANDIDO MENDES Aula 8 Ferramentas SQL Server Enterprise Manager O "SQL Server Enterprise Manager" é a porta de entrada para a Interface de usuário do SQL Server. Aí temos uma porção de menus e botões. Os itens de menu importantes são Action, View Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 41 ESCOLA SUPERIOR CANDIDO MENDES e Tools: Action Permite fazer coisas tais como registrar um novo servidor ou um novo grupo. Views Fornece uma lista dos diferentes tipos de visões disponíveis para você. Você pode selecionar as visões: grande, pequeno, detalhe ou lista dos ícones e suas propriedades associadas. Ainda é possível definir quais itens e quais barras de ferramentas você verá. Exatamente como no Windows Explorer. O menu Tools Lista todas as ferramentas e assistentes do SQL Server. Você pode fazer backup de um banco de dados; parar, iniciar e configurar a replicação; e iniciar ferramentas como o Query Analyzer (Analisador de consultas), entre outras. Imediatamente à direita dos menus há outros itens de barras de ferramentas. Estes são basicamente atalhos para os itens mais usados da barra de menu. Temos, entre outros: Anterior Próximo Atualizar Registrar Servidor Novo Banco de Dados Novo Login Query Designer O SQL Server 7.0 possui uma ferramenta muito útil, que se parece com o Query By Example (QBE) do Microsoft Access, e é um ótimo substituto para a MS Query (do SQL Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 42 ESCOLA SUPERIOR CANDIDO MENDES Server 6.5). O nome dessa aplicação é Query Designer e faz parte das Ferramentas Visuais de Banco de Dados. É uma ótima ferramenta, mas não está listada como uma ferramenta do SQL Server, e é um pouco difícil achá-la diretamente. Para localizá-la, faça assim: Do Enterprise Manager, expanda o banco de dados Northwind, e expanda as tabelas. Clique com o botão direito na tabela Categories. Selecione Open Table, e então Return All Rows. Nota: O que foi citado acima para a tabela Categories, do banco de dados Northwind, aplica-se a qualquer tabela, de qualquer banco de dados, assim como não é necessário selecionar Return All Rows. Poderia ter sido selecionado Return Top... que te perguntaria quantos registros você quer ver. A diferença é no comando SQL gerado para cada uma das seleções. Depois que você abrir a tabela, toda a janela estará envolvida com o Query Designer. Ele permite que você veja propriedades da consulta, execute consultas de seleção, de ação (inserção, atualização, exclusão, e criação de tabelas), validação da sintaxe SQL, ordenação, filtragem, e agrupamentos. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 43 ESCOLA SUPERIOR CANDIDO MENDES Provavelmente, estaremos vendo a seção dos Resultados (Results Pane), acionada com o botão , que mostra os resultados atuais da consulta. Porém, ainda há outras três seções para se escolher ou adicionar à janela atual. Estas são, Show Diagram, Show Grid, e Show SQL Pane. Elas são selecionadas pelos botões no topo da janela. Show Diagram Pane Esta seção te permite visualizar graficamente o banco de dados ou as tabelas. Por padrão, só a tabela atual que estiver aberta será mostrada nesta seção. Para adicionar outra tabela, simplesmente clique com o botão direito em qualquer lugar da seção (exceto na representação gráfica de alguma tabela). Aparece um menu com algumas opções. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 44 ESCOLA SUPERIOR CANDIDO MENDES Selecione Add Table. Isso mostrará todas as tabelas e visões (views) disponíveis no seu banco de dados, conforme abaixo. Adicione a tabela Products (supondo que você seguiu o exemplo acima e está vendo as tabelas do banco de dados Northwind), selecionando-a e clicando em Add, ou dando um duplo clique no nome da tabela . Note que o relacionamento também é representado graficamente com uma linha e chave em uma ponta e um ícone de infinito na outra ponta. Isso mostra um relacionamento de um para muitos(1:N) com CategoryID na tabela Categories como a chave primária. Pode-se selecionar para a consulta, colunas individuais ou todas as colunas marcando as caixas de verificação apropriadas perto do nome de cada coluna. Show Grid Pane Esta seção permite detalhar mais a consulta sendo criada. Por exemplo, podemos selecionar a ordem para uma coluna particular, seu alias (nome que será mostrado no grid). Tudo isso de uma maneira visual. Show SQL Pane Esta seção mostra a declaração SQL gerada pelas seleções feitas nas seções acima. Pode-se agora copiar e colar código desta janela, para o Query Analyzer, pra um ambiente de programação, entre outros. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 45 ESCOLA SUPERIOR CANDIDO MENDES Nota: A seção de resultados (Results Pane) não se atualiza automaticamente. Para atualizá-la, clique no ponto de exclamação vermelho (Run) No Query Designer, podemos criar consultas complexas muito mais rapidamente que no Query Analyser. Depois de criarmos as consultas, podemos colocá-las no Query Analyzer e testar sua performance. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br• www.candidomendes.edu.br/escm 46 ESCOLA SUPERIOR CANDIDO MENDES Aula 9 Exercício de acesso às tabelas de sistema utilizando o Enterprise Manager Alguns acessos ao banco de dados para conferir o conteúdo de alguma das tabelas de sistema utilizando o Enterprise Manager.: Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 47 ESCOLA SUPERIOR CANDIDO MENDES Vamos carregar os dados da tabela acima marcada : (sysobjects) Clicar com o botão direito na tabela sysobjects e escolher a opção: ( Open Table ) / ( Return TOP.) Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 48 ESCOLA SUPERIOR CANDIDO MENDES Vamos trabalhar com a tabela acima selecionada (Categories) analisando suas propriedades. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 49 ESCOLA SUPERIOR CANDIDO MENDES Name Nome da Tabela Permissions Manipula ou vê as permissões dos usuários na tabela. Owner Nome do dono da tabela Create date Data em que a tabela foi criada no Banco de dados Filegroup Mostra em que grupo de arquivos a tabela está armazenada. O arquivo de banco de dados é gravado com a extensão .mdf ou .ndf, dependendo se ele for um arquivo primário ou não- primário no banco de dados. O arquivo de log é gravado com a extensão .ldf. Rows Número de Linhas da Tabela Key Mostra qual coluna é a primary key. ID Mostra qual ID está associada com a primary key Name Nome de cada coluna Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 50 ESCOLA SUPERIOR CANDIDO MENDES Data type Tipo de cada coluna Size Tamanho de cada coluna em bytes. Nulls Mostra se a coluna aceita valores nulos ou não Default Valores default da coluna Object Nome do Objeto List all users/DB roles Mostra todos os usuários do Banco de dados List only users/DB roles with permissions on this object Mostra somente os usuários que possuem alguma permissão. User/DB role Usuário SELECT Permite ou não ao usuário fazer SELECT no objeto. INSERT Permite ou não ao usuário fazer INSERT no objeto. UPDATE Permite ou não ao usuário fazer UPDATE no objeto. DELETE Permite ou não ao usuário fazer DELETE no objeto. EXEC Permite ou não ao usuário fazer EXECUTE no objeto. DRI Permite ou não ao usuário trabalhar com Integridade referencial (declarative referential integrity) no objeto. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 51 ESCOLA SUPERIOR CANDIDO MENDES Iremos agora, analisar as dependências da tabela em questão, para tal, basta clicar na tabela com o botão da direita e escolher a opção : (All Tasks) / (Display Dependencies). Teremos então a tela a seguir. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 52 ESCOLA SUPERIOR CANDIDO MENDES Nesta tela, podemos ver os objetos que possuem dependências com a tabela Categories. Object Objeto que se deseja ver as dependências Objects that depend on <object> Mostra todos os objetos que dependem do objeto selecionado. Objects that <object> depend on Mostra todos os objetos dos quais o objeto selecionado depende Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 53 ESCOLA SUPERIOR CANDIDO MENDES Para visualizar o script de uma view, basta clicar com o Botão da direita do mouse e selecionar a opção: ( All Task ) / ( Generate SQL Script ) Teremos então a tela seguinte. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 54 ESCOLA SUPERIOR CANDIDO MENDES Clicar em Preview para ver o SQL (DDL do objeto já selecionado ou OK para grava-lo em disco. Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 55 ESCOLA SUPERIOR CANDIDO MENDES Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 56 ESCOLA SUPERIOR CANDIDO MENDES Iremos agora verificar os índices da tabela em questão. Devemos clicar com o botão direito do mouse e selecionar a opção : ( All Task ) / (Manage Indexes). Neste caso, teremos a tela seguinte Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 57 ESCOLA SUPERIOR CANDIDO MENDES . Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 58 ESCOLA SUPERIOR CANDIDO MENDES Aula 10 UPDATE Utilizado para modificar dados já cadastrados. Pode ser usado para atualizar todas as linhas ou para atualizar linhas que correspondam a determinados critérios. Cria uma consulta atualização que altera os valores dos campos em uma tabela especificada com base em critérios específicos. Sintaxe UPDATE tabela SET coluna = valor_novo WHERE critério; A instrução UPDATE tem as partes abaixo: Parte Descrição tabela O nome da tabela cujos dados você quer modificar. Valor_novo Uma expressão que determina o valor a ser inserido em um campo específico nos registros atualizados. critério Uma expressão que determina quais registros devem ser atualizados. Só os registros que satisfazem a expressão são atualizados. UPDATE clientes SET telefone = ‘000-1111’ Rua da Assembléia, nº. 10 -3ºandar - sala 319 CEP 20011-901 • Rio de Janeiro • RJ Tels.: (21) 3543-6442 e 3543-6413 escm@candidomendes.edu.br • www.candidomendes.edu.br/escm 59 ESCOLA SUPERIOR CANDIDO MENDES UPDATE clientes SET telefone = '111-0000' WHERE cliente = 1004 Comentários UPDATE é especialmente útil quando você quer alterar muitos registros ou quando os registros que você quer alterar estão em várias tabelas. Você pode alterar vários campos ao mesmo tempo. O exemplo abaixo aumenta o Valor do Pedido em 10 por cento e o valor do Frete em 3 por cento para embarques do Reino Unido: UPDATE Pedidos SET ValorPedido = ValorPedido * 1.1, Frete = Frete * 1.03 WHERE PaísEmbarque
Compartilhar