Oracle/SQL : Différence entre versions

De Wiki-noulot
Sauter à la navigation Sauter à la recherche
(Page créée avec « =Oracle= ==Def== VLDB = SGA = LOB = Objet Binaire Large (Fichier ex: photo, scan...) DBMS = data bas manage system (programe fonctionel "Pakage" (ex: Sats)) Seg... »)
 
 
(54 révisions intermédiaires par le même utilisateur non affichées)
Ligne 1 : Ligne 1 :
 
=Oracle=
 
=Oracle=
 +
==Install SQLPUS==
 +
 +
rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
 +
 +
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
 +
export PATH=$ORACLE_HOME/bin:$PATH
 +
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
 +
dans .bash_profile
 +
 
==Def==
 
==Def==
−
  VLDB =  
+
  VLDB = Very Large Databases
  
−
  SGA =  
+
  SGA = System Global Area
 +
      Zone de mémoire à l'intérieur de la mémoire principale utilisée
 +
      pour stocker des données pour un accès rapide. Oracle utilise le
 +
      pool partagé pour allouer de la mémoire SGA en commun, les
 +
      procédures SQL et PL/SQL.
  
 
  LOB = Objet Binaire Large (Fichier ex: photo, scan...)
 
  LOB = Objet Binaire Large (Fichier ex: photo, scan...)
Ligne 9 : Ligne 22 :
 
  DBMS = data bas manage system (programe fonctionel "Pakage" (ex: Sats))
 
  DBMS = data bas manage system (programe fonctionel "Pakage" (ex: Sats))
  
−
  Segment =
+
Extent = Groupe de block contigus
 +
  Segment = Ensemble d'extent > Objet
  
−
  Table Space =
+
  Table Space = Espace de stockage des données
  
 
  Grid Computing = Ecriture en RAM
 
  Grid Computing = Ecriture en RAM
  
−
  Shemat =  
+
  Shemat = Objet utilisateur
 +
 
 +
OFA = oracle flexible architecture
 +
 
 +
flash_recovery_area = repertoire "Corbeille" (Purge par def toutes les 15 min)
 +
 
 +
Dictionnaire de données =
 +
 
 +
Explain = utilitaire "d'explication" de la requette (execute la requette)
  
−
==Soft==
+
==Soft==
  
 
  SQL Developer (gratuit)
 
  SQL Developer (gratuit)
 +
 +
Data Modeler (gratuit)
 +
 +
==Commandes de base==
 +
 +
===Connection a une base===
 +
connect "user"/"PW"
 +
 +
===Connection avec utilisateur du groupe DBA===
 +
connect / as sysdba
 +
 +
===Changement PW sys===
 +
alter user sys identified by "PW";
 +
 +
===Arret Base===
 +
shutdown immediate
 +
 +
===Démarrage Base===
 +
Startup
 +
 +
===Afficher les tablespaces===
 +
desc dba_tablespaces
 +
 +
===Creation de tablespace===
 +
CREATE SMALLFILE TABLESPACE "T_STAGE" DATAFILE 'C:\Stage\t_stage01.dbf'
 +
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 150M LOGGING EXTENT MANAGEMENT
 +
LOCAL SEGMENT SPACE MANAGEMENT AUTO
 +
 +
===Creation Table===
 +
Create Table "NOM"
 +
 +
===Contrainte===
 +
CONSTRAINT "nom contrante" "Type contrainte"
 +
 +
===Type Contrainte===
 +
PRIMARY KEY : Clé Primaire
 +
FOREIGN KEY : Clé étrangère
 +
UNIQUE : Unicité de valeur
 +
NOT NULL : La colonne est obligatoirement renseignée
 +
CHECK : Contrôle d’une règle de gestion
 +
 +
===Select===
 +
select "valeur" from "nom base"
 +
 +
===Jointure===
 +
select ename, dname from emp, det where emp.deptno= det.deptno;
 +
 +
[[File:SQL_Joins.png|SQL Joins]]
 +
 +
===Afficher catalog===
 +
select * from cat;
 +
 +
===Descrition objet===
 +
desc "nom objet";
 +
 +
===Créer un Role===
 +
CREAT ROLE "R_STAGE" NOT IDENTIFIED
 +
GRANT CREATE PROCEDURE TO "R_STAGE"
 +
GRANT CREATE SEQUENCE TO "R_STAGE"
 +
GRANT CREATE SESSION TO "R_STAGE"
 +
GRANT CREATE SYNONYM TO "R_STAGE"
 +
GRANT CREATE TABLE TO "R_STAGE"
 +
GRANT CREATE TABLESPACE TO "R_STAGE"
 +
GRANT CREATE TRIGGER TO "R_STAGE"
 +
GRANT CREATE TYPE TO "R_STAGE"
 +
GRANT CREATE USER TO "R_STAGE"
 +
GRANT CREATE VIEW TO "R_STAGE"
 +
 +
===Créer un utilisateur avec Role===
 +
CREATE USER "Nom User" PROFILE "DEFAULT" IDENTIFIED BY "Password" 
 +
DEFAULT TABLESPACE "TableSpace def" ACCOUNT UNLOCK
 +
 +
===Attribuer un role a un utilisateur===
 +
GRANT "role" TO "nom user"
 +
 +
===Modification Quota user===
 +
ALTER USER "SEBASTIEN" QUOTA 50 M ON "TBS_STAGE"
 +
 +
===Consultation des données===
 +
SELECT empno, ename, job, sal
 +
FROM  emp
 +
WHERE  sal > 4500;
 +
 +
      EMPNO ENAME      JOB              SAL
 +
---------- ---------- --------- ----------
 +
      7839 KING      PRESIDENT      5000
 +
 +
===Insertion de lignes===
 +
insert into dept
 +
values (50,’FORMATION’,’BIARRITZ’);
 +
 +
===Modification des lignes===
 +
update emp
 +
set sal = sal * 1.1
 +
WHERE sal > 4500
 +
 +
===Suppression de lignes===
 +
delete from "nom table"
 +
where "valeur dans collone" = 345
 +
 +
===Supprimer une table===
 +
DROP TABLE "Nom table";
 +
 +
===Supprimer une sequance===
 +
DROP SEQUENCE seq_frn;
 +
 +
===Creation d'une sequance===
 +
  CREATE SEQUENCE seq_frn
 +
  INCREMENT BY 2
 +
  START WITH 1
 +
  MAXVALUE 999
 +
  NOCYCLE;
 +
 +
===Insertion d'une donnée dans une table===
 +
insert into "Table" (Collone, ..., ...)
 +
values (Val1, Val2, ...); #avec ' ' si AlphaNum, sans ' ' si num, null si vide
 +
 +
===Trouver le DIRECTORY pour export===
 +
select * from dba_directories;
 +
 +
 +
===Afficher tous les shéma d'une base===
 +
select username from dba_users;
 +
 +
===export de shema===
 +
expdp user/pwd DUMPFILE="nom_fichier_dump" DIRECTORY="SAV_DIR" SCHEMAS="nom du schema","nom du schema" LOGFILE="fichier_log"
 +
 +
=SQL=
 +
==SELECT==
 +
Le SELECT est la commande de base du SQL destinée à extraire des données d’une base ou calculer de nouvelles données à partir d’existantes...
 +
 +
 +
Voici la syntaxe générale d'une commande SELECT :
 +
 +
SELECT [DISTINCT ou ALL] * ou liste de colonnes
 +
FROM nom de table ou de la vue
 +
[WHERE prédicats]
 +
[GROUP BY ordre des groupes]
 +
[HAVING condition]
 +
[ORDER BY ] liste de colonnes
 +
 +
 +
'''NOTA''' : dans cette syntaxe, les mots clef du SQL sont en gras, les paramètres en minuscule et entre crochets on trouve les parties optionnelles
 +
 +
 +
En fait l'ordre SQL SELECT est composé de 6 clauses dont 4 sont optionnelles.
 +
Clauses de l'ordre SELECT :
 +
 +
{| class="wikitable"
 +
|-
 +
| SELECT || Spécification des colonnes du résultat
 +
|-
 +
| FROM || Spécification des tables sur lesquelles porte l'ordre
 +
|-
 +
| WHERE || Filtre portant sur les données (conditions à remplir pour que les lignes soient présentes dans le résultat)
 +
|-
 +
| GROUP BY || Définition d'un groupe (sous ensemble)
 +
|-
 +
| HAVING || Filtre portant sur les résultats (conditions de regroupement des lignes)
 +
|-
 +
| ORDER BY || ri des données du résultat
 +
|}
 +
 +
 +
'''NOTA''' : La plupart du temps, la difficulté réside dans la compréhension de la différence entre le filtre WHERE et le filtre HAVING.
 +
Disons plus pragmatiquement que le filtre WHERE permet de filtrer les données des tables tandis que le filtre HAVING permet de filtrer les données du résultat...
 +
 +
'''REMARQUE''' : pour spécifier une valeur littérale il faut l'entourer de guillemets simples.
 +
Un premier exemple basique :
 +
  Exemple 1
 +
 +
SELECT  CLI_NOM, CLI_PRENOM
 +
FROM    T_CLIENT
 +
WHERE    TIT_CODE = 'M.'
 +
 +
{| class="wikitable"
 +
|-
 +
|CLI_NOM || CLI_PRENOM
 +
|-
 +
|DUPONT || Alain
 +
|-
 +
|MARTIN || Marc
 +
|-
 +
|BOUVIER || Alain
 +
|-
 +
|DUBOIS || Paul
 +
|-
 +
|DREYFUS||Jean
 +
|-
 +
|FAURE||Alain
 +
|-
 +
|PAUL||Marcel
 +
|-
 +
|DUVAL||Arsène
 +
|-
 +
|PHILIPPE||André
 +
|-
 +
|CHABAUD||Daniel
 +
|-
 +
|BAILLY||Jean-François
 +
|-
 +
|...
 +
|}
 +
 +
Permet de trouver les noms et prénoms des clients dont le titre est ‘M.’ (monsieur).
 +
 +
'''NOTA''' : comme tous les paramètres à prendre sous forme de littéraux doivent être exprimés entourés d'apostrophes (simple côtes), il faut dédoubler un tel
 +
caractère s'il s'avère présent dans la chaîne utilisé.
 +
 +
==Commande SQL==
 +
===Information sur Table===
 +
Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'am_asset'
 +
 +
===Delete ligne===
 +
delete from E_SG_CENTREON where SGNAME like 'SG-P1OPUS-Catalog%'
 +
 +
===Insertion ligne===
 +
insert Into E_SG_CENTREON (SGNAME) VALUES ('Null')

Version actuelle datée du 6 avril 2016 à 12:44

Oracle[modifier]

Install SQLPUS[modifier]

rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
dans .bash_profile

Def[modifier]

VLDB = Very Large Databases 
SGA = System Global Area
      Zone de mémoire à l'intérieur de la mémoire principale utilisée
      pour stocker des données pour un accès rapide. Oracle utilise le
      pool partagé pour allouer de la mémoire SGA en commun, les 
      procédures SQL et PL/SQL.
LOB = Objet Binaire Large (Fichier ex: photo, scan...)
DBMS = data bas manage system (programe fonctionel "Pakage" (ex: Sats))
Extent = Groupe de block contigus 
Segment = Ensemble d'extent > Objet
Table Space = Espace de stockage des données
Grid Computing = Ecriture en RAM
Shemat = Objet utilisateur
OFA = oracle flexible architecture
flash_recovery_area = repertoire "Corbeille" (Purge par def toutes les 15 min)
Dictionnaire de données = 
Explain = utilitaire "d'explication" de la requette (execute la requette)

Soft[modifier]

SQL Developer (gratuit)
Data Modeler (gratuit)

Commandes de base[modifier]

Connection a une base[modifier]

connect "user"/"PW"

Connection avec utilisateur du groupe DBA[modifier]

connect / as sysdba

Changement PW sys[modifier]

alter user sys identified by "PW";

Arret Base[modifier]

shutdown immediate

Démarrage Base[modifier]

Startup

Afficher les tablespaces[modifier]

desc dba_tablespaces

Creation de tablespace[modifier]

CREATE SMALLFILE TABLESPACE "T_STAGE" DATAFILE 'C:\Stage\t_stage01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 150M LOGGING EXTENT MANAGEMENT 
LOCAL SEGMENT SPACE MANAGEMENT AUTO

Creation Table[modifier]

Create Table "NOM"

Contrainte[modifier]

CONSTRAINT "nom contrante" "Type contrainte"

Type Contrainte[modifier]

PRIMARY KEY 	: Clé Primaire
FOREIGN KEY 	: Clé étrangère
UNIQUE 	: Unicité de valeur
NOT NULL	: La colonne est obligatoirement renseignée
CHECK 		: Contrôle d’une règle de gestion

Select[modifier]

select "valeur" from "nom base"

Jointure[modifier]

select ename, dname from emp, det where emp.deptno= det.deptno;
SQL Joins

Afficher catalog[modifier]

select * from cat;

Descrition objet[modifier]

desc "nom objet";

Créer un Role[modifier]

CREAT ROLE "R_STAGE" NOT IDENTIFIED
GRANT CREATE PROCEDURE TO "R_STAGE"
GRANT CREATE SEQUENCE TO "R_STAGE"
GRANT CREATE SESSION TO "R_STAGE"
GRANT CREATE SYNONYM TO "R_STAGE"
GRANT CREATE TABLE TO "R_STAGE"
GRANT CREATE TABLESPACE TO "R_STAGE"
GRANT CREATE TRIGGER TO "R_STAGE"
GRANT CREATE TYPE TO "R_STAGE"
GRANT CREATE USER TO "R_STAGE"
GRANT CREATE VIEW TO "R_STAGE"

Créer un utilisateur avec Role[modifier]

CREATE USER "Nom User" PROFILE "DEFAULT" IDENTIFIED BY "Password"  
DEFAULT TABLESPACE "TableSpace def" ACCOUNT UNLOCK

Attribuer un role a un utilisateur[modifier]

GRANT "role" TO "nom user"

Modification Quota user[modifier]

ALTER USER "SEBASTIEN" QUOTA 50 M ON "TBS_STAGE"

Consultation des données[modifier]

SELECT empno, ename, job, sal
FROM   emp
WHERE  sal > 4500;
     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7839 KING       PRESIDENT       5000

Insertion de lignes[modifier]

insert into dept 
values (50,’FORMATION’,’BIARRITZ’);

Modification des lignes[modifier]

update emp
set sal = sal * 1.1 
WHERE sal > 4500

Suppression de lignes[modifier]

delete from "nom table"
where "valeur dans collone" = 345

Supprimer une table[modifier]

DROP TABLE "Nom table";

Supprimer une sequance[modifier]

DROP SEQUENCE seq_frn;

Creation d'une sequance[modifier]

 CREATE SEQUENCE seq_frn
 INCREMENT BY 2
 START WITH 1
 MAXVALUE 999
 NOCYCLE;

Insertion d'une donnée dans une table[modifier]

insert into "Table" (Collone, ..., ...)
values (Val1, Val2, ...); #avec ' ' si AlphaNum, sans ' ' si num, null si vide

Trouver le DIRECTORY pour export[modifier]

select * from dba_directories;


Afficher tous les shéma d'une base[modifier]

select username from dba_users;

export de shema[modifier]

expdp user/pwd DUMPFILE="nom_fichier_dump" DIRECTORY="SAV_DIR" SCHEMAS="nom du schema","nom du schema" LOGFILE="fichier_log"

SQL[modifier]

SELECT[modifier]

Le SELECT est la commande de base du SQL destinée à extraire des données d’une base ou calculer de nouvelles données à partir d’existantes...


Voici la syntaxe générale d'une commande SELECT :

SELECT [DISTINCT ou ALL] * ou liste de colonnes 
FROM nom de table ou de la vue
[WHERE prédicats] 
[GROUP BY ordre des groupes]
[HAVING condition] 
[ORDER BY ] liste de colonnes


NOTA : dans cette syntaxe, les mots clef du SQL sont en gras, les paramètres en minuscule et entre crochets on trouve les parties optionnelles


En fait l'ordre SQL SELECT est composé de 6 clauses dont 4 sont optionnelles. Clauses de l'ordre SELECT :

SELECT Spécification des colonnes du résultat
FROM Spécification des tables sur lesquelles porte l'ordre
WHERE Filtre portant sur les données (conditions à remplir pour que les lignes soient présentes dans le résultat)
GROUP BY Définition d'un groupe (sous ensemble)
HAVING Filtre portant sur les résultats (conditions de regroupement des lignes)
ORDER BY ri des données du résultat


NOTA : La plupart du temps, la difficulté réside dans la compréhension de la différence entre le filtre WHERE et le filtre HAVING. Disons plus pragmatiquement que le filtre WHERE permet de filtrer les données des tables tandis que le filtre HAVING permet de filtrer les données du résultat...

REMARQUE : pour spécifier une valeur littérale il faut l'entourer de guillemets simples. Un premier exemple basique :

 Exemple 1
SELECT   CLI_NOM, CLI_PRENOM
FROM     T_CLIENT
WHERE    TIT_CODE = 'M.'
CLI_NOM CLI_PRENOM
DUPONT Alain
MARTIN Marc
BOUVIER Alain
DUBOIS Paul
DREYFUS Jean
FAURE Alain
PAUL Marcel
DUVAL Arsène
PHILIPPE André
CHABAUD Daniel
BAILLY Jean-François
...

Permet de trouver les noms et prénoms des clients dont le titre est ‘M.’ (monsieur).

NOTA : comme tous les paramètres à prendre sous forme de littéraux doivent être exprimés entourés d'apostrophes (simple côtes), il faut dédoubler un tel caractère s'il s'avère présent dans la chaîne utilisé.

Commande SQL[modifier]

Information sur Table[modifier]

Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'am_asset'

Delete ligne[modifier]

delete from E_SG_CENTREON where SGNAME like 'SG-P1OPUS-Catalog%'

Insertion ligne[modifier]

insert Into E_SG_CENTREON (SGNAME) VALUES ('Null')