Oracle/SQL : Différence entre versions
(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== | |
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
Sommaire
- 1 Oracle
- 1.1 Install SQLPUS
- 1.2 Def
- 1.3 Soft
- 1.4 Commandes de base
- 1.4.1 Connection a une base
- 1.4.2 Connection avec utilisateur du groupe DBA
- 1.4.3 Changement PW sys
- 1.4.4 Arret Base
- 1.4.5 Démarrage Base
- 1.4.6 Afficher les tablespaces
- 1.4.7 Creation de tablespace
- 1.4.8 Creation Table
- 1.4.9 Contrainte
- 1.4.10 Type Contrainte
- 1.4.11 Select
- 1.4.12 Jointure
- 1.4.13 Afficher catalog
- 1.4.14 Descrition objet
- 1.4.15 Créer un Role
- 1.4.16 Créer un utilisateur avec Role
- 1.4.17 Attribuer un role a un utilisateur
- 1.4.18 Modification Quota user
- 1.4.19 Consultation des données
- 1.4.20 Insertion de lignes
- 1.4.21 Modification des lignes
- 1.4.22 Suppression de lignes
- 1.4.23 Supprimer une table
- 1.4.24 Supprimer une sequance
- 1.4.25 Creation d'une sequance
- 1.4.26 Insertion d'une donnée dans une table
- 1.4.27 Trouver le DIRECTORY pour export
- 1.4.28 Afficher tous les shéma d'une base
- 1.4.29 export de shema
- 2 SQL
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;
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')
