· 8 years ago · Jan 05, 2018, 06:16 PM
1CASE SENSITIVE
2NÃO ACEITA " NO LUGAR DE '
3
4serviços inicializados
5OracleServiceXE
6OracleXETNSListener
7
8sqlplus
9
10usuário:SYS AS SYSDBA (tem mais privilegios)
11senha:
12
13system
14
15
16startup; sobe a instancia
17shutdown; desativa instância (só SYS pode desativar)
18
19
20CREATE USER jefferson IDENTIFIED BY senha;
21
22
23GRANT DBA TO jefferson; --permissão de DBA (Database Administrator)
24
25create table compras (
26id number primary key,
27valor number,
28data date,
29observacoes varchar2(30),
30 recebido char check (recebido in (0,1))); --recebido só pode receber 0 ou 1 (não tem boolean no Oracle)
31
32create sequence id_seq; --criado sequencia para ser usado como autonumeração nos inserts
33
34INSERT INTO COMPRAS (ID, VALOR, DATA, OBSERVACOES, RECEBIDO) VALUES (ID_SEQ.NEXTVAL, 100.0, '02-JUL-2010', NULL, '1'); --ID_SEQ.NEXTVAL autonumeração
35
36ALTER TABLE COMPRAS MODIFY (OBSERVACOES VARCHAR2(30) NOT NULL); --alterando coluna p/ não aceitar nulos
37
38ALTER TABLE COMPRAS MODIFY (RECEBIDO CHAR DEFAULT '0' CHECK (RECEBIDO IN (0,1))); --atribuindo valor default p/ uma coluna
39
40ALTER TABLE COMPRAS ADD (FORMA_PAGT VARCHAR2(15) CHECK (FORMA_PAGT IN ('CARTAO', 'BOLETO', 'DINHEIRO'))); --adicionando nova coluna na tabela
41
42ALTER TABLE COMPRAS RENAME COLUMN FORMA_PAGT TO FORMA_PAGTO; --renomeando o nome da coluna
43
44FUNÇÕES DE AGREGAÇÃO sum(valor), avg(valor), count(id)
45
46select extract (year from data) from compras; --retorna apenas o ano da data
47
48ALTER TABLE COMPRAS ADD FOREIGN KEY (COMPRADOR_ID) REFERENCES COMPRADORES(ID); --inserindo FK p/ manter consistência - restrição
49
50set linesize 100 ; --define 100 caracteres por linha na exibição dos resultados
51
52
53show parameter nls_lang; verifica qual é a linguagem padrão instalada
54
55
56@c:/sql/create_table_compras.sql --importação de dados do arquivo q esta localizado em c:/sql
57
58 select valor, observacoes from compras where data >= '15-nov-2008'; --data com oracle em portugues 15-11-2008 TBM SERVE
59
60SELECT * FROM COMPRAS WHERE NOT VALOR = 108; --NEGAÇÃO
61
62select table_name from user_tables; --tabelas do bd
63
64
65SQL> select a.nome from aluno a where not exists (select m.curso_id from matricula m where a.id = m.
66aluno_id); --retornar alunos que não existem na tabela matrÃcula
67
68select sysdate - interval '1' year from dual; subtrai 1 ano da data atual
69
70O HAVING é aplicado no resultado do agrupamento, ou seja, podemos utilizá-lo para filtrar funções agregadas.
71
72ROWNUM - pseudo coluna q enumera os registros de uma consulta
73select rownum, nome from (select a.nome from aluno a order by a.nome);
74
75select rownum, nome from (select a.nome from aluno a order by a.nome) where rownum <= 5;
76retorna os 5 primeiros registros
77
78select * from (select rownum r, nome from (
79 select a.nome from aluno a order by a.nome
80)) where r > 5;
81retorna alem dos 5 primeiros registros
82
83select * from (select rownum r, nome from (
84 select a.nome from aluno a order by a.nome
85) where rownum <= 10) where r > 5;
86
87Na arquitetura RAC mais de uma máquina pode apontar para a mesma base de dados portanto caso a alguma máquina caia outra pode assumir o processamento. Portanto essa arquitetura nos ajudar a trabalhar com queda de algum servidor sem perder a disponibilidade do banco de dados pois sempre temos mais de uma máquina apontando para a mesma base.
88
89select table_name from user_tables;
90mostra as tabelas criadas
91
92sql>create table usario
93
94list (l) mostra o buffer
95change \usario\usuario
96append ( inclui na linha
97input id int not null primary key - cria nova linha no buff
98del 3 remove a linha 3 do buffer
99save cria-usuario salva o buff no arquivo
100edit abre o arquivo para edição
101clear buffer (cl buff)
102get cria-usuario (carrega o conteudo do arquivo no buffer)
103save cria-usuario replace (sobreescreve o conteudo do arquivo)
104start cria-usuario (executa o arquivo)
105
106describe usuario (sp_help)
107desc usuario
108
109select salario as "salario" alias (somente aspas dupla)
110
111varchar2 - flexivel
112
113alter table produto add(datacadastro datetime);
114
115select 'Custo ' || (salario + refeicao ) concatenação
116
117'LUA\_NA' ESCAPE '\' pede para o oracle considerar o _
118
119like 'Eduard_' pode retornar Eduarda, Eduardo (como se fosse ?)
120
121order by 1 ele vai ordenar pela chave primária de forma decrescente.
122
123O between só trabalha com valores numéricos
124
125não podemos usar alias no where
126
127(case colunaAvaliada
128 when condicao then valor
129 when condicao then valor
130 when condicao then valor
131 when condicao then valor
132 else valor
133end ) as xxxx
134
135Define &V_DEPT = 20;
136 Select nome,salario from funcionarios where salario = &V_dept;
137
138sysdate - data atual
139select proj_id, to_char(data_inicio, ‘Day “ Week†WW, YYYY’) from projetos;
140Quarta-Feira Week 25, 2016
141na função to_char o texto que literal que deve aparecer deve estar entre aspas duplas
142
143select nome || setor_id from funcionarios;
144select concat(nome,setor_id) from funcionarios;
145Felipe1
146
147SIGN(ABS(NVL(-32,0))) = SIGN(ABS(-32)) = SIGN(32) = 1
148se fosse negativo => -1, se fosse 0 => 0
149
150mod(11,4) = 3
151
152
153current_timestamp: utiliza os valores de session date, session time e session timezone offset
154
155ADD_MONTHs('28/02/2013', -12) from dual;
156
157Select to_date(‘30-sep-07’, ‘DD-MM-YYYY’) from dual; Select to_date(‘30-sep-07’, ‘DD-MON-RRRR’) from dual;
158
159select coalesce(null, ‘Oracle ‘, ‘Certified’) from dual; //oRACLE
160coalesce retorna sempre o primeiro valor não nulo entre os parâmetros recebidos.
161
162SYSDATE //DATA ATUAL
163
164CURRENT_DATE //DATA atual
165
166SYSDATE + 1 = DATA ATUAL + 1 DIA
167
168add_months(sysdate,12)
169
170current_timestamp //retorna data tempo fuso
171
172ALTER SESSION SET NLS_DATE_LANGUAGE = 'ENGLISH';
173//BRAZILIAN PORTUGUESE
174
175select max(longitude), max(latitude) from locais; //retorna apenas 1 linha, o maximo longitude e o maximo latitude
176
177select max(longitude), max(latitude) from locais group by estado;//retorna apenas 1 linha, o maximo longitude e o maximo latitude para cada estado
178
179GROUP BY NÃO PODE USAR ALIAS nem group by 1
180
181NÃO PODE USAR COUNT(*), funções de agrupamento, NO WHERE
182--------------------------------------------------------
183single row functions
184
185s/ valor = null
186valor * null = null
187
188nvl(pct_comissao,0)
189~isnull(pct_comissao,0)
190
191nvl(pct_comissao * comissao,0)
192
193nvl2(valor, se nao nulo, se nulo)
194
195coalesce(expressao, elemento1, elemento2)
196retorna o 1o. elemento não nulo de uma lista caso a expressão seja nulo
197elementos podem ser colunas de uma tabela ou outras expressões.
198
199ascii(‘A’)
200retorna 65
201
202ascii(‘AB’)
203retorna somente 65’
204
205ascii(null)
206geralmente todas as funções retornam null quando passado null
207
208chr(65)
209rertorna A
210
211instr(‘rogerio’,’a’)
212retorna 0 - não possui ocorrências da letra a na expressão rogerio
213
214instr(‘rogerio’, ‘o’)
215retorna 2
216
217instr(‘amanda’,’a’,2)
218retorna 3 - posição do 1o ‘a’ depois da 2a posição
219
220é case sensistive
221
222instr(‘amanda’,’a’,2,2)
223retorna 6 - posição do 2o. ‘a’ depois da 2a posição
224
225instr(‘compras.mes.txt,’.’,-1)
226procura o ponto de tras para a frente. A posição é a mesma que aquela da esquerda p/ direita
227
228instrb
229posição em byte
230
231length(‘renan’)
232retorna 5
233
234lengthb(‘renan’)
235retorna em bytes
236
237upper(‘renan’)
238RENAN
239
240lower(‘Renan’)
241renan
242
243lpad(nome,20,’*’)
244ocupa 20 espaços a esquerda, preeenchido com *. Padrão ‘ ‘
245
246rpad
247
248ltrim(‘ felipe’,’ ‘)
249felipe. ‘ ‘ é o padrão
250
251rtrim
252
253trim(‘*’ from ‘****************vinicius*************’)
254retorna vinicius. combina ltrim e rtrim.
255
256replace(‘sr julio’,’sr’,’senhor’)
257retorna senhor julio
258
259replace(‘sr julio’,’sr’,null’)
260retorna julio
261
262replace(‘sr julio’,’null’)
263retorna sr julio
264
265translate(‘r3n4n’,’1340’,’ieao’)
266retorna renan
267
268translate(‘r3n4n’,’1340’,null)
269rnn
270
271substr(‘sr julio’,4,3)
272jul
273
274substr(‘sr julio’,-4,3)
275uli . conta de tras para frente
276
277sign(50)
278retorna 1 qdo >0, 0 qdo 0, -1 qdo <0
279
280abs(-5)
281retorna 5
282
283round(22/7)
2843. retorna parte inteira, arredondada. 0-4 arredonda para baixo.5-9 p/ cima
285
286round(22/7,2)
2873.14 arredonda com duas casas decimais
288
289round(1235.55,-2)
2901235 -> 1200
291
292round(1255.55,-2)
2931255 -> 1300
294
295trunc(3.1415)
2963. ignora casas decimais
297
298trunc(3.1415,2)
2993.14 ignora as 2 cadas decimais menos significativas
300
301trunc(3.1415,2.5)
3023.14. ignora o 2 argumento
303
304ceil(3.14)
3054.arredonda para cima
306
307floor(3.14)
3083. arredonda para baixo
309
310sqrt(81)
3119
312
313power(2,3)
3148
315
316log(2,1024) 2^x=1024 = 10
317
318exp(1)
3192.71….
320
321ln(2.71..)
3221
323
324mod(13/5)
3253. resto da divisão
326
327remainder(16,5) 1 sobrou 1 para 5 caber 3X em 16
328remainder(17,5) 2 sobrou 2 para 5 caber 3X em 17
329remainder(18,5) -2 faltou 2 para 5 caber 4X em 18
330remainder(19,5) -1 faltou 1 para 5 caber 4X em 19
331remainder(20,5) 0 sobrou/faltou nada para 5 caber em 20
332
333concat(concat(nome, ‘ ‘), sobrenome)
334nome ‘ ‘ sobrenome
335
336concat(null, sobrenome)
337sobrenome
338
339initcap(‘renan’)
340Renan
341
342initcap(‘reNan’)
343Renan
344
345initcap(‘isabel cristina’)
346Isabel Cristina
347
348--------------------------------------------------------
349funções de agrupamento
350
351avg(salario) inclui repetições = avg(all salario) padrao. desconsidera
352
353avg(distinct salario) calcula media sem repetições. Desconsidera null
354
355count(*) conta os registros null
356
357count(pct_comissao) ignora os null
358
359count(distinct salario) exclui repetições. all padrão
360
361max(nome) ultimo nome em ordem alfabetica
362
363sum(distinct salario) exclui repetições. all padrão
364
365median, stddev, variance
366
367-------------------------------------------------
368set autotrace on
369set autotrace off
370
371ordem de execução das queries
372-----------------------------------------------
373group by rollup(setor_id) subtotais
374
375group by cube(setor_id, salario) combinações
376
377group by/having podem aparecer em qq ordem na query
378
379group by deve vir sempre depois do where
380
381having sum(salario) > 500
382group by setor_id
383---------------------------------------------
384
385dml
386
387join (até 9i: tabelas separadas por virgula; > 9i ansi )
388
389tabela1 inner join tabela2 using(setor_id)
390on (tabela1.setor_id = tabela2.setor_id)
391
392tabela1 natural join tabela2 --oracle deduz
393
394(+) left outer join
395using
396natural left join
397
398full outer join (inner + left + right)
399natural
400using
401
402self join
403
404
405select 1: davi, dorval, joseane, hilda
406select 2: davi dorval
407
408union all: davi, dorval, joseane, hilda, davi, dorval
409union: davi, dorval, joseane, hilda (remove repetições)
410intersect: davi, dorval --retorna quem se repete nos 2 selects
411minus: hilda, joseane --retorna quem ñ se repete
412
413
414
415os 3 deletes geram o mesmo resultado
4161. delete from cidade where id = 1;
4172. delete cidade where id = 1;
4183. delete (select * from cidade where id = 1);
419
420update - pode ter subquery no set e no where
421
422----------------------------------
423pl/sql
424
425declare
426 novo_saldo number(10,2);
427begin
428 select saldo into novo_saldo from contas where saldo_id = 1;
429
430 if novo_saldo > 0 then
431 commit;
432 else rollback;
433 end if;
434end
435/
436----------------------------
437apenas comandos ddl finalizam (comitam) uma transacao
438
439insert into funcionarios_demitidos values (84, trunc(sysdate));
440savepoint a;
441insert into funcionarios_demitidos values (88, trunc(sysdate));
442savepoint b;
443insert into funcionarios_demitidos values (89, trunc(sysdate));
444rollback to a;
445insert into funcionarios_demitidos values (88, trunc(sysdate));
446commit;
447
448savepoint - commit parcial
449rolback to savepoint - faz rollback daquilo que é executado após o savepoint
450
451-----------------------------
452ddl
453
454nome tabela: começa com letra sempre a pode conter #$_ e número no meio do nome. Até 30 caracteres
455nome coluna: começa com letra ou #$_
456
457create table "Produtos" é case sensitive
458desc "Produtos"
459
460create table produtos <> "Produtos"
461
462number - ñ precisa especificar tamanho
463varchar2 - sempre precisa especificar tamanho
464char - tamanho default = 1
465
466create table produtos(
467 id number,
468 status varchar(30) default 'pendente'
469)
470
471insert into produtos(1,default) => grava pendente
472
473 status varchar(30) default on null 'pendente'
474insert into produtos(2,null) => grava pendente
475
476create sequence autoid
477select autoId.nextval from dual; --inicializa sequencia
478
479select autoId.curval from dual; -- valor corrente
480
481create table pedidos (
482 id number(11) default autoId.nextval --pode inserir null em coluna definida
483 id number(11) default on null autoId.nextval --qdo null, insere valor
484
485create sequence serial start with 50 increment by 5
486
487
488create table pedidos (
489 id number(11) generated by default as identity --vc ainda pode definir o valor do id no insert
490 id number(11) generated always as identity --nao é possÃvel inserir o id manualmente
491
492comment on table pedidos is 'tabela...'
493select *from user_tab_comments where table_name = 'pedidos'
494
495comment on column pedidos.valor is 'coluna que salva..'
496select *from user_col_columns where table_name = 'pedidos'
497
498create table pedidos_backup as select *from pedidos; --cria tabela com mesma definição e dados
499create table pedidos_backup as select *from pedidos where 1 = 2; --cria tabela com mesma definição
500create table pedidos_backup as select nome, valor novaCol from pedidos where 1 = 2; --cria tabela com nome de coluna diferente
501
502alter table pedidos add(obs varchar2(20), obs2 varchar2(30))
503
504alter table pedidos add(cliente_id number(11) default 1 not null)
505
506alter table pedidos modify observacao varchar2(30) --só altera a definição da col se os dados suportam essa operação. Sem perda de informação
507
508alter table pedidos rename column valor to valorBruto
509
510alter table pedidos modify obs invisible --select * não mostra, select obs mostra. Ainda da p/ inserir dados na coluna
511
512alter table pedidos set unused column obs; --ñ dá pra inserir dados usando essa coluna
513
514alter table pedidos drop unused column;
515
516alter table pedidos rename to pedido;
517rename pedido to pedidos;
518
519truncate table;