Banco de Dados

Disciplina: BANCO DE DADOS II

Aula Estrutura Liguagem SQL

Prof. Izaac Espíndola [email protected]

03/09/2012

mailto:[email protected]

Estrutura da Linguagem

SQL DQL (Data Query Language)

SELECT Usado para Projetar , Ordenar o processo de exibição dos

dados.

DML (Data Manipulation Language)

DELETE, INSERT, UPDATE Usado diretamente para Manipulação de Dados.

DDL (Data Definition Language)

DROP, TRUNCATE, CREATE, ALTER Usado para manipular estrutura dos databases.

DCL

REVOKE, GRANT Usado para permitir ou bloquear acessos a estrutura e dados.

TIPOS DE DADOS

CHAR

Variáveis ou constantes que armazenam valores alfanuméricos com no máximo de 255 caracteres.

Exemplo:

Pagamento CHAR(40);

VARCHAR2

Variáveis que armazenam valores alfanuméricos de tamanho variável até o tamanho máximo de 2000.

Exemplo:

Nome VARCHAR2(45);

TIPOS DE DADOS

NUMBER

Variáveis ou constantes que armazenam valores numéricos com no máximo de 38 caracteres. Caso não seja informado o tamanho, o default é 38. Também pode ser definido precisão escalar.

Exemplos:

Recibo NUMBER(2);

Codigo CONSTANT NUMBER(4) :=1;

Preco NUMBER(7,2)

TIPOS DE DADOS

DATE

Variáveis ou constantes que armazenam data, hora (com minutos e segundo e o século).

Exemplo:

Data_receb DATE;

BOOLEAN

Variáveis ou constantes que armazenam TRUE, FALSE ou NULL.

Exemplo:

Flag BOOLEAN;

DDL (Data Definition Language)

Comando Create

Create Table <nome> (

<campo> <tipo>(<tamanho>) [NOT NULL],

...

[CONSTRAINT "PK_<tableName>“] primary key (<campo1>,[<campo2>,...,<campon>]),

[CONSTRAINT “FK_<tableName>“][foreign key] (<campo>) REFERENCES <table>(campo)

);

DDL

Exemplo Oracle: Create table pessoa (

cod_pessoa number(5) NOT NULL,

nome varchar2(150) NOT NULL,

endereco varchar2(150) ,

fone varchar2(11) ,

tipo char(1) NOT NULL,

CONSTRAINT “PK_PESSOA” primary key (cod_pessoa)

);

DDL

Exemplo 1 MySql:

Create table pessoa (

cod_pessoa numeric(5) NOT NULL,

nome varchar(150) NOT NULL,

endereco varchar(150),

fone varchar(11),

tipo char(1) NOT NULL,

CONSTRAINT PK_COD_PESSOA PRIMARY KEY

(COD_PESSOA)

);

Dessa forma você cria a chave primária junto com a

tabela.

DDL

Exemplo 2 MySql:

Create table pessoa (

cod_pessoa numeric(5) NOT NULL,

nome varchar(150) NOT NULL,

endereco varchar(150),

fone varchar(11),

tipo char(1) NOT NULL

);

alter table pessoa add primary key (cod_pessoa);

Dessa forma você Cria e depois alterar a tabela

criando a chave que você queira.

ALTER TABLE pessoa DROP PRIMARY KEY ;

Elimana a chave primaria criada.

DDL

Exemplo Oracle: Create table funcionario (

cod_pessoa number(5) NOT NULL,

cod_cargo number(5) NOT NULL,

salario number(5,2) ,

CONSTRAINT “PK_FUNC” primary key (cod_pessoa),

CONSTRAINT “FK_PESSOA” foreign key (cod_pessoa)

references pessoa(cod_pessoa),

CONSTRAINT “FK_CARGO” foreign key (cod_cargo)

references cargo(cod_cargo)

);

DDL

Exemplo MySQL: Create table funcionario (

cod_pessoa numeric(5),

cod_cargo numeric(5),

salario numeric(5,2),

CONSTRAINT PK_FUNC primary key (cod_pessoa),

CONSTRAINT FK_PESSOA foreign key (cod_pessoa)

references pessoa(cod_pessoa),

CONSTRAINT FK_CARGO foreign key (cod_cargo)

references cargo(cod_cargo)

);

DDL

Comando Alter Oracle Modificando uma coluna

Alter Table <nome> MODIFY (

<campo> <tipo>(<tamanho>) [NOT NULL]

);

Ex:

Alter Table pessoa MODIFY (

nome varchar2(200) NOT NULL

);

DDL

Comando Alter MySQL Modificando uma coluna

Alter Table <nome> CHANGE COLUMN (


);

Ex:

Alter Table pessoa CHANGE COLUMN (

nome varchar(200) NOT NULL

);

DDL

Comando Alter Oracle Adicionando uma coluna

Alter table <nome> add (


);

Ex: Alter table funcionario add (

data date NOT NULL

);

DDL

Comando Alter MySQL Adicionando uma coluna

Alter table <nome> add column (


);

Ex: Alter table funcionario add column (

data date NOT NULL

);

DDL

Comando Alter Oracle Eliminando uma coluna

Alter Table <nome> DROP (

<campo>

);

Ex:

Alter Table funcionario DROP (

data

);

DDL

Comando Alter MySQL Eliminando uma coluna

Alter Table <nome> drop column(

<campo>

);

Ex:

Alter Table funcionario DROP COLUMN(

data

);

DDL

Comando Drop Oracle

Excluindo uma tabela

DROP TABLE [<esquema>.]<tabela> [CASCADE CONSTRAINTS];

Ex:

DROP TABLE pessoa [CASCADE CONSTRAINTS];

DDL

Comando Drop MySQL

Excluindo uma tabela

DROP TABLE [<esquema>.]<tabela> ;

Ex:

DROP TABLE pessoa ;

Criação de Tabelas CREATE TABLE <tabela> „(„ <coluna> <tipo da coluna> [ restrição do atributo ] { , <coluna> <tipo da coluna> [ restrição do atributo ] } [ <restrição da tabela> { , <restrição da tabela> } ] „)‟

CREATE TABLE DEPARTAMENTO (Nome VARCHAR(15) NOT NULL,

Numero INTEGER PRIMARY KEY);

CREATE TABLE EMPREGADO (Nome VARCHAR(30) NOT NULL,

CPF CHAR(9) NOT NULL,

Data-Nasc DATE,

Endereço VARCHAR(30),

Sexo CHAR(1),

Salário INTEGER,

Supervisor CHAR(9) REFERENCES EMPREGADO,

Dept INTEGER REFERENCES DEPARTAMENTO,

PRIMARY KEY (CPF),

UNIQUE (Nome));

Alteração de Tabelas

ALTER TABLE <tabela> <lista de alterações na tabela>

ALTER TABLE EMPREGADO

ADD COLUMN Cor_predileta CORES;


ADD FOREIGN KEY chave_dept

REFERENCES DEPARTAMENTO

ON DELETE RESTRICT

ON UPDATE CASCADE;


ADD CONSTRAINT salario_minimo

CHECK (Salario >= 10000);

COMANDOS DE MANIPULAÇÃO

DE DADOS EM BANCO

INSERT, DELETE, UPDATE e SELECT. Os comando relacionados são utilizados através de sintaxe que utilizam valores para realizar procedimentos ralacionados

Para controlar as transações com o banco de dados (iniciadas implicitamente com qualquer comando de modificação de dados), utiliza-se os comandos COMMIT, SAVEPOINT e ROLLBACK.

Para garantir que os dados a serem manipulados não serão alterados ou excluídos por outros usuários, utiliza-se o comandos LOCK TABLE ou o comando SELECT com a cláusula FOR UPDATE.

Comandos de Manipulação de Dados

INSERT INTO <tabela> [ ( <lista de colunas> ) ] ( VALUES ( <lista de valores> ) | <comando SELECT> )

UPDATE <tabela> SET <coluna> = <valor> {, <coluna> = <valor>} [ WHERE <condição de seleção> ]

DELETE FROM <tabela> [ WHERE <condição de seleção> ]

DML (Data Manipulation Language)

Comando Insert

Insert into [<esquema.>]<tabela> [(campo1,...,campon)] values

(valor1,...,valorn);

Ex.:

Insert into cargo (cod_cargo,descricao) values (1,‟Caixa‟);

Insert into pessoa (cod_pessoa,nome,endereco,fone,tipo) values

(1,‟Bruno‟,‟meu_endereco‟,‟99999999‟,‟F‟);

Insert into funcionario (cod_pessoa,cod_cargo,salario) values

(1,1,‟250.33‟);

DML

Comando Update

update [<esquema.>]<tabela> set campo1 =

valor1,...,campon = valorn WHERE condição

Ex.:

Update pessoa set endereco = „novo_endereco‟, fone =

null where cod_pessoa = 1;

Update funcionario set salario = „650.60‟ where

cod_pessoa = 1

DML

Comando Delete

Delete from [<esquema.>]<tabela> WHERE

condição

Ex.:

Delete from funcionario where cod_pessoa = 1;

Delete from pessoa where cod_pessoa = 1;

SQL como linguagem de consulta

para usuários finais

A proposta inicial da SQL era prover uma interface de consulta “natural” para usuários finais através do comando SELECT. Logo se percebeu que isto só seria possível em consultas muito simples, devido à complexidade da linguagem.

Consulta: Para cada departamento com mais de 2 empregados, obtenha o nome do departamento e o número de empregados que ganham mais de 30.000.

SELECT D.Nome, COUNT(*) FROM DEPARTAMENTO AS D, EMPREGADO AS E WHERE D.Número=E.Dept AND E.Salário > 30000 AND E.Dept IN (SELECT Dept FROM EMPREGADO GROUP BY Dept HAVING COUNT(*) > 2) GROUP BY D.Nome;

DML

Comando Select

Select {* | <campo1,...,campon>} from <tabela> [where condição]

Ex.:

/* Listando todos os atributos de todas as pessoas */

Select * from pessoa;

/* Listando nome e endereco de todas as pessoas */

Select nome,endereco from pessoa;

/* Listando nome e cargo de todos os funcionários */

Select pessoa.nome, cargo.descricao from pessoa, cargo,

funcionario where pessoa.cod_pessoa = funcionario.cod_pessoa

and funcionario.cod_cargo=cargo.cod_cargo;

DML

Comando Select

Consultas encadeadas /* Listar o cliente que possui mais pontos*/

Select pessoa.nome from pessoa, cliente where

cliente.pontos = (select MAX(cliente.pontos) from

cliente) and pessoa.cod_pessoa = cliente.cod_pessoa;

DML

Comando Select

Cláusula Distinct /* Listando todos os cargos que possuem ao menos um

funcionário*/

Select distinct cargo.descricao from funcionario,

cargo where funcionario.cod_cargo = cargo.cod_cargo;

DML

Comando Select

Cláusula Group By /* Listando os cargos e a quantidade de

funcionários em cada cargo agrupados por

cargo */

select cod_cargo, count(cod_cargo) as soma

from funcionario group by

funcionario.cod_cargo

DML

Comando Select

Cláusula Having /* Listando os cargos e a quantidade de

funcionários em cada cargo agrupados por

cargo, porém só para aqueles cargos que

possuem mais de dois funcionários atrelados

a ele. */

select cod_cargo, count(cod_cargo) as soma

from funcionario group by

funcionario.cod_cargo having count(*) >= 2;

DML

Comando Select

Cláusula order by /* Listando o nome dos clientes em ordem alfabética */

Select pessoa.nome from pessoa where pessoa.tipo = „C‟

order by pessoa.nome;

/* Listando os salários em ordem decrescente */

Select funcionario.salario from funcionario order by

salario desc;

DML

Comando Select Cláusulas in e or /* Listando o nome dos funcionários cujo cargo seja

caixa ou embalador */

Select pessoa.nome from pessoa,funcionario,cargo where

pessoa.cod_pessoa = funcionario.cod_pessoa and

funcionario.cod_cargo = cargo.cod_cargo and

cargo.descricao in („caixa‟,‟embalador‟);

Select pessoa.nome from pessoa,funcionario,cargo where

pessoa.cod_pessoa = funcionario.cod_pessoa and

funcionario.cod_cargo = cargo.cod_cargo and

cargo.descricao = „caixa‟ or cargo.descricao =

‟embalador‟;

DML

Comando Select Funções (MAX, MIN, SUM, AVG, COUNT) /* Mostrar o valor do maior salário dos funcionários */

Select MAX (salario) from Funcionario

/* Mostrar qual o a média de pontos dos clientes */

Select AVG (pontos) from Cliente

/* Mostrar quantos clientes possuem mais de 1000 pontos */

Select COUNT (*) from Cliente where pontos > 1000

/* Mostrar as despesas com pagamento de salário dos funcionários que

o supermercado possui */

Select SUM (salario) from Funcionario

Condições de junção em SQL

SELECT EMPREGADO.Nome, Sexo,Data_Nasc, Salário,

DEPARTAMENTO.Nome

FROM DEPARTAMENTO INNER JOIN EMPREGADO

ON DEPARTAMENTO.Número = EMPREGADO.Dept

WHERE Salário>=30000;

SELECT EMPREGADO.Nome, Sexo,Data_Nasc, Salário,

DEPARTAMENTO.Nome

FROM DEPARTAMENTO, EMPREGADO

WHERE DEPARTAMENTO.Número = EMPREGADO.Dept

AND Salário>=30000;

As consultas acima são equivalentes

40

Fim

Documents

Banco de Dados