[*] -
SecureInfo.eu,
/Web/SQL - Queries.sql
/Web/SQL - Queries.sql /Web/SQL - Queries.sql/*****************************************************************************************************************************************************\
* *
* INTERROGATIONS *
* *
\*****************************************************************************************************************************************************/
-- 1. SELECTION DE COLONNES
-- ========================
-- Affiche toute la table
SELECT * FROM EMP
-- Affiche le prénom, le nom, la fonction de chacun des salariés
SELECT ENAME,JOB FROM EMP
-- Affiche la liste des fonctions possibles des salariés
SELECT JOB FROM EMP
-- 2. SELECTION DE LIGNES
-- ======================
-- Affichage du nom, du salaire, de la commission des employés dont la commission est supérieure au salaire
SELECT * FROM EMP WHERE COMM >= SAL
-- Affichage du nom, du numéro des départements dont le numéro est supérieur à 20
SELECT ENAME,DEPTNO FROM EMP WHERE DEPTNO > 20
-- Nom, salaire dont salaire >2500 && <8000
SELECT ENAME, SAL FROM EMP WHERE SAL>=2500 AND SAL<=8000
SELECT ENAME, SAL FROM EMP WHERE SAL BETWEEN 2500 AND 8000
-- Numéro, nom, fonction salaire des CLERK ou ANALYST
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB='CLARK' OR JOB='ANALYST'
-- Nom des salariés commenceant par M
SELECT ENAME FROM EMP WHERE ENAME LIKE 'M%'
-- Nom des salariés commenceant par M et ayant 6 caractères
SELECT ENAME FROM EMP WHERE ENAME LIKE 'M_____'
-- Nom des salariés comprennat un L
SELECT ENAME FROM EMP WHERE ENAME LIKE '%L%'
-- 3. PREDICATS COMPOSES
-- =====================
-- Nom des employés du département 30 dont le salaire est supérieur à 4000
SELECT ENAME FROM EMP WHERE DEPTNO=30 AND SAL>4000
-- Nom, fonction, salaire, numéro du département des salariés MANAGER ou CLARK du département 10
SELECT ENAME, JOB, SAL, DEPTNO FROM EMP WHERE (JOB='MANAGER' OR JOB='CLERK') AND DEPTNO=10
-- AFfichage nom, fonction, salaire, numéro du département, des salariés MANAGER ou des salariés du département 10 qui sont CLERK
SELECT ENAME, JOB, SAL, DEPTNO FROM EMP WHERE JOB='MANAGER' OR (DEPTNO=10 AND JOB='CLERK')
-- Liste des managers des départements 20 et 30
SELECT * FROM EMP WHERE JOB='MANAGER' AND (DEPTNO=20 OR DEPTNO=30)
-- Nom, fonction, date d'embauche des salariés qui ne sont pas managers et qui ont été embauichés en 1981
SELECT
ENAME, JOB, HIREDATE
FROM
EMP
WHERE
JOB!='MANAGER'
AND
HIREDATE LIKE '%81'
-- 4. PREDICATS COMPOSES
-- =====================
-- Afficher le nom, la fonction et la date d'embauche des salariés qui ont été embauchés après le 1er janvier 1982
SELECT ENAME, JOB, HIREDATE FROM EMP WHERE HIREDATE > '01/01/82'
-- 5. PREDICATS COMPOSES
-- =====================
-- Affiche uniquement les employés qui ont une commission
SELECT * FROM EMP WHERE COMM IS NOT NULL
-- Affiche uniquement les employés qui n'ont pas de commission
SELECT * FROM EMP WHERE COMM IS NULL
-- // Affiche uniquement les employés qui n'ont pas de commission et trie par commission
SELECT * FROM EMP WHERE COMM IS NULL ORDER BY COMM
-- // Affiche les numéros des employés supérieur à 7800 dont le job est CLERK
SELECT EMPNO,JOB FROM EMP WHERE EMPNO>=7800 AND LOWER(JOB)='CLERK'
-- 6. ORDER BY
-- ===========
-- Séléctionne les salaréis par ordre croissant de salaire
SELECT * FROM EMP ORDER BY SAL ASC
-- Sélectionne les salariés triés par fonction et pour chaque fonction triés par ordre décroissant de salaire
SELECT * FROM EMP ORDER BY JOB, SAL DESC
-- Afficher le nom, le numéro de département, la date d'embauche des salariés, triés par n° de département croissant,
-- ordre alphabetique des fonctions, ancienneté croissante
SELECT ENAME, DEPTNO, HIREDATE FROM EMP ORDER BY DEPTNO,JOB DESC, GRADE ASC
-- 7. ORDER BY
-- ===========
-- Afficher le nom, le salaire , la commission des employés, triés par ordre décroissant de commission
SELECT ENAME, SAL, COMM FROM EMP ORDER BY COMM
-- Afficher le n° département, le nom, le n° de salarié, le (salaire+commission) des employés triés par ordre décroissant de n° de département
-- et de (salaire+commission)
SELECT DEPTNO, ENAME, EMPNO, NVL(SAL,COMM) FROM EMP ORDER BY DEPTNO DESC, NVL(SAL, COMM)
-- 8. ORDER BY
-- ===========
-- Afficher la liste des employés avec la localité dans laquelle ils travaillent.
SELECT * FROM EMP,DEPT
-- Liste des employés travaillant à Dallas
SELECT * FROM EMP,DEPT WHERE DEPT.LOC='DALLAS'
-- Affichez le nom, le salaire, la commission des vendeurs travaillant à Chicago, triés par ordre croissant de commission
SELECT ENAME, SAL, COMM FROM EMP, DEPT ORDER BY EMP.COMM
UNION SELECT * FROM eleves WHERE DEPT.LOC='DALLAS'
-- 9. JOINTURE EXTERNE
-- ===================
-- Affichez la liste des employés avec la localité dans laquelle ils travailent en affichant toutes les localités d'affectation possibles
SELECT
ENAME,
LOC
FROM
EMP,
DEPT
WHERE
EMP.DEPTNO (+)= DEPT.DEPTNO
AND
DEPT.LOC IS NOT NULL
AND
ENAME IS NOT NULL
-- Liste des départements n'ayant pas d'employés
SELECT
LOC
FROM
EMP,DEPT
WHERE
EMP.DEPTNO (+)= DEPT.DEPTNO -- Concatène deux colonnes
AND -- ET
EMPNO IS NULL -- affiche le résultat
-- DONT le numéro d'employté est nul.
-- 10. AUTO-JOINTURE / SYNONYME
-- ============================
-- Affichez les noms des employés en indiquant pour chacun le nom de son manager
SELECT
EMP_EMPLOYE.ENAME, -- Selectionne le champs ENAME de la table EMP (synonyme EMP_EMPLOYE)
EMP_MANAGER.ENAME -- de même pour le synonyme EMP_MANAGER
FROM
EMP EMP_EMPLOYE, -- De même avec EMP_MANAGER
EMP EMP_MANAGER -- EMP_EMPLOYE est un alias de EMP. Il permet de travailler sur EMP de manière séparée
WHERE
EMP_EMPLOYE.EMPNO_MGR = EMP_MANAGER.EMPNO
-- Où on cherche le numéro du Manager DE l'employé
-- Affichez les noms et dates d'embauche des salariés embauchés avant leur manager
SELECT
EMP_EMPLOYE.ENAME, -- Selectionne le nom de la table EMP (synonyme EMP_EMPLOYE)
EMP_EMPLOYE.HIREDATE, -- Selectionne le nom de la table EMP (synonyme EMP1)
EMP_MANAGER.ENAME, --
EMP_MANAGER.HIREDATE -- Selectionne le nom de la table EMP (synonyme EMP1)
FROM
EMP EMP_EMPLOYE, -- EMP_EMPLOYE est un alias de EMP. Il permet de travailler sur EMP de manière séparée
EMP EMP_MANAGER -- De même avec EMP_MANAGER
WHERE
EMP_EMPLOYE.EMPNO_MGR = EMP_MANAGER.EMPNO
AND
EMP_EMPLOYE.HIREDATE < EMP_MANAGER.HIREDATE
-- Affichez les noms et dates d'embauche des salariés embauchés avant Mr KING
SELECT
EMP_EMPLOYE.ENAME,
EMP_EMPLOYE.HIREDATE,
EMP_MR_KING.ENAME,
EMP_MR_KING.HIREDATE
FROM
EMP EMP_EMPLOYE,
EMP EMP_MR_KING
WHERE
EMP_MR_KING.ENAME = 'KING'
AND
EMP_EMPLOYE.HIREDATE < EMP_MR_KING.HIREDATE
-- 11. AUTRES JOINTURES
-- ====================
-- Affichez les noms des employés gagnat plus que CLARK
SELECT
EMP_EMPLOYE.ENAME,
EMP_EMPLOYE.SAL,
EMP_MR_CLARK.ENAME,
EMP_MR_CLARK.SAL
FROM
EMP EMP_EMPLOYE,
EMP EMP_MR_CLARK
WHERE
EMP_MR_CLARK.ENAME = 'CLARK'
AND
EMP_EMPLOYE.SAL > EMP_MR_CLARK.SAL
-- 12. Langage d'interrogation, les opérateurs ensemblistes
-- ========================================================
-- 12. UNION
-- =========
-- Affichez la liste des employés en réunissant dans une même colonne les salaires et les commissions
-- S'il y a une commission, l'afficher et l'identifier.
SELECT ENAME, 'Salaire', SAL FROM EMP
UNION
SELECT ENAME, 'Commission', COMM FROM EMP WHERE COMM IS NOT NULL
-- Affichez la liste des noms des salariés, des n° de déopartement en réunissant dans une
-- mëme colonne les noms des salariés et de leur département.
SELECT ENAME, 'Nom', DEPTNO FROM EMP
UNION
SELECT DNAME, 'Départ', DEPTNO FROM DEPT
-- 13. INTERSECT
-- =============
-- Affichez la liste des n° de département qui se trouvent dans les tables EMP et DEPT.
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT
-- 14. MINUS
-- =========
-- Affichez la liste des n° de département qui ne contiennent aucun salarié.
SELECT DEPTNO FROM DEPT
MINUS
SELECT DEPTNO FROM EMP
-- Affichez les salariés (n° de salarié) n'ayant pas de subordonné
SELECT EMPNO FROM EMP
MINUS
SELECT EMPNO_MGR FROM EMP
/*****************************************************************************************************************************************************\
* *
* SOUS-INTERROGATIONS *
* *
\*****************************************************************************************************************************************************/
-- 1. Sous interrogation ramenant une seul valeur
-- ==============================================
-- Affichez la liste des salariés ayant même fonction de Jones.
-- SELECT JOB FROM EMP WHERE ENAME='JONES'
-- SELECT ENAME,JOB FROM EMP WHERE JOB
-- SELECT ENAME,JOB FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='JONES')
-- SELECT ENAME FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='JONES')
SELECT ENAME FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='JONES')
-- Affichez les salariés embauchés le même jour que Ford, en excluant Ford
-- SELECT HIREDATE FROM EMP WHERE ENAME='FORD'
-- SELECT ENAME,,HIREDATE FROM EMP WHERE HIREDATE=(SELECT HIREDATE FROM EMP WHERE ENAME='FORD')
SELECT ENAME FROM EMP WHERE HIREDATE=(SELECT HIREDATE FROM EMP WHERE ENAME='FORD') AND ENAME!='FORD'
-- Affichez les salariés ayant le même manager que Clark
SELECT ENAME FROM EMP WHERE EMPNO_MGR=(SELECT EMPNO_MGR FROM EMP WHERE ENAME='CLARK')
-- 2. Jointure et sous-interrogation
-- =================================
-- Affichez la liste des salariés travaillant à Chicago et ayant la même fonction que Allen
-- SELECT JOB FROM EMP WHERE ENAME='ALLEN'
-- SELECT LOC FROM DEPT WHERE LOC='CHICAGO'
SELECT
ENAME
FROM
EMP
WHERE
JOB=(SELECT JOB FROM EMP WHERE ENAME='ALLEN')
AND
DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC='CHICAGO')
-- DEPTNO est le point commun entre la table DEPT et la table EMP.
-- Dans la table DEPT il y a le lieu, ce qui permet de réaliser le filtre.
-- 3. Sous-interrogation et interrogation principale synchronisée
-- ==============================================================
SELECT
EMP_EMPLOYE.ENAME -- (1) - Affichez la liste des salariés
FROM --
EMP EMP_EMPLOYE, --
EMP EMP_MANAGER -- (3)
WHERE --
EMP_EMPLOYE.EMPNO_MGR=EMP_MANAGER.EMPNO --
AND -- (3) - Que leur manager
EMP_MANAGER.DEPTNO!=EMP_EMPLOYE.DEPTNO -- (2) - Ne travaillant pas dans le même département
-- 4. Test d'existence
-- ===================
-- Affichez la liste des salariés travaillant dans un département employant au moins un CLERK.
SELECT
-- (1) - Affichez la liste des salariés
ENAME
FROM
EMP
WHERE
-- (3) - Employant AU MOINS UN
EXISTS
(
SELECT
*
FROM
EMP EMP_EMPLOYE
WHERE
-- (4) - Clerk.
EMP_EMPLOYE.JOB='CLERK'
AND
-- (2) - Travaillant dans un département
EMP_EMPLOYE.DEPTNO=EMP.DEPTNO
)
-- Affichez la liste des salariés travaillant dans un département n'employant aucun ANALYST.
SELECT
-- (1) - Affichez la liste des salariés
ENAME
FROM
EMP
WHERE
-- (3) - N'Employant AUCUN
NOT EXISTS
(
SELECT * FROM
EMP EMP_EMPLOYE
WHERE
-- (4) - Analyst.
EMP_EMPLOYE.JOB='ANALYST'
AND
-- (2) - Travaillant dans un département
EMP_EMPLOYE.DEPTNO=EMP.DEPTNO
)
-- Affichez la liste des activités sportives auxquelles sont inscrits tous les employés.
-- = Afficher les sports que pratiquent les employés
-- = Afficher les sports tels qu'il n'y ait pas de personne qui n'en pratiquent pas
SELECT * FROM SPORT -- = Afficher les sports
WHERE
NOT EXISTS
(
SELECT * FROM EMP -- = tels qu'il n'y ait pas de personne
WHERE
NOT EXISTS
(
SELECT * FROM PRATIQUER WHERE
-- = qui n'en pratiquent pas
PRATIQUER.EMPNO=EMP.EMPNO AND SPORT.SPORTNO=PRATIQUER.SPORTNO
)
)
-- 5. Sous-interrogations multiples
-- ================================
-- Affichez la liste des salariés ayant le même job que Jones ou un salaire supérieur à celui de Ford
SELECT ENAME FROM EMP WHERE
(
JOB=( SELECT JOB FROM EMP WHERE ENAME='JONES' )
OR
SAL>( SELECT SAL FROM EMP WHERE ENAME='FORD' )
)
-- Affichez la liste des salariés ayant le même job que Jones ou un salaire supérieur à celui de Ford
SELECT ENAME FROM EMP WHERE
(
JOB=( SELECT JOB FROM EMP WHERE ENAME='MARTIN' )
AND
SAL>( SELECT SAL FROM EMP WHERE ENAME='BLAKE' )
)
-- 6. Sous-interrogations ramenant plusieurs lignes
-- ================================================
-- Affichez le nom et la fonction des salariés ayant même job que quelqu'un du département SALES
-- Affichez le nom et la fonction
SELECT ENAME,JOB FROM EMP
WHERE
JOB IN
(
SELECT
JOB
FROM
EMP,DEPT
WHERE
EMP.DEPTNO=DEPT.DEPTNO -- des salariés ayant même job
AND
DNAME='SALES' -- que quelqu'un du département SALES
)
-- Affichez le nom et la fonction des salariés ayant même job que quelqu'un du département SALES
SELECT ENAME, HIREDATE FROM EMP E,EMP E2,DEPT
WHERE HIREDATE IN
(
SELECT HIREDATE
FROM DATE(HIREDATE)=DATE(E1.) )
-- 11. Jointure et sous-interrogation
-- =================================
-- Affichez les noms des employés gagnant plus que Mr Clark.
SELECT EMP1.ENAME FROM EMP EMP1 WHERE SAL > (SELECT EMP2.SAL FROM EMP EMP2 WHERE EMP2.ENAME='CLARK')
-- Affichez les noms des employés ayant une commission que Mr Ward.
SELECT EMP1.ENAME FROM EMP EMP1 WHERE COMM > (SELECT EMP2.COMM FROM EMP EMP2 WHERE EMP2.ENAME='WARD')
-- 12. Opérateurs ensemblistes : UNION, INTERSECT, MINUS
-- =====================================================
-- UNION
-- Affichez la liste des employés eb réunissant dans une même colonne les salaires et les commissions.
SELECT ENAME, 'SALAIRE', SAL FROM EMP
UNION
SELECT ENAME, 'COMMISSION', COMM FROM EMP
WHERE COMM IS NOT NULL
SELECT ENAME, 'NOM', DEPTNO FROM EMP
UNION
SELECT DNAME, 'DEPART', DEPTNO FROM DEPT
-- INTERSECT
-- Affichez la liste des nméros de département qui se trouvent dans les EMP et DEPT
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT
-- MINUS
-- Affichez la liste des numéro de déparrtement qui ne contiennet aucun salarié
SELECT DEPTNO FROM DEPT -- (3) - liste des départements totaux
MINUS -- (2) - moins
SELECT DEPTNO FROM EMP -- (1) - Liste des départements où il y a des employés...
-- Affiche la liste des numéros d'employés n'ayant pas de subordonnés
-- Affiche la liste des numéros d'employés n'étant pas manager, (ou président)
SELECT EMPNO FROM EMP
MINUS
SELECT EMPNO_MGR FROM EMP
-- Affiche le nom et le numéro
( SELECT EMPNO, ENAME FROM EMP )
MINUS
-- Extrait les ... ... manager
( SELECT EMPNO, ENAME FROM EMP WHERE EMPNO IN (SELECT EMPNO_MGR FROM EMP) );
/*****************************************************************************************************************************************************\
* *
* GROUPES *
* *
\*****************************************************************************************************************************************************/
-- 1. Calcul sur un seul groupe.
-- =============================
-- Exercice(1)
-- -----------
-- Affiche la somme de tout les salaires des employés du département numéro 10.
SELECT SUM(SAL) FROM EMP WHERE DEPTNO=10;
-- Exercice(2)
-- -----------
-- Affiche le salaire moyen sans tenir compte de ceux qui n'ont pas de commmission
SELECT AVG(SAL+COMM) FROM EMP;
-- Affiche le salaire moyen en tanant compte des commisssions
SELECT AVG(SAL+NVL(COMM,0)) FROM EMP;
-- 2. Fonction de comptage : COUNT
-- ===============================
-- Exercice(2)
-- -----------
-- Affichez le nombre d'employés du département 10
SELECT COUNT(ENAME) FROM EMP WHERE DEPTNO=10;
-- Affichez le nombre de personnes touchant une commission.
SELECT COUNT(COMM) FROM EMP;
-- Affichez le nombre de jobs...
SELECT COUNT(JOB) FROM EMP;
-- ... différents.
SELECT COUNT(DISTINCT JOB) FROM EMP;
-- 3. Calculs sur plusieurs groupes - COUNT, GROUP BYE
-- ===================================================
-- Exercice(3)
-- -----------
-- Affichez le nombre d'employés du département 10
-- SELECT COUNT(ENAME) FROM EMP WHERE DEPTNO=10;
-- Affichez le total des salaires et le numéro de département en groupant par département
SELECT SUM(SAL), DEPTNO FROM EMP GROUP BY DEPTNO;
-- Affichez le nombre de salariés par fonction, trié par ordre décroissant de nombre de salarié
SELECT COUNT(EMPNO) FROM EMP GROUP BY JOB;
-- Affichez le salaire moyent par département et par fonction
SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO, JOB;
-- Exercice(4)
-- -----------
-- Affichez le salaire moyent par département et par fonction
SELECT COUNT(SAL) FROM EMP GROUP BY TRUNC(SAL,-3);
-- Affichez les salaires moyens de chaque département et le nom de chaque département
-- select sal,dname from emp,dept;
-- select avg(sal) from emp,dept dept1
-- dept1.dname in (select dname from dept where dname);
SELECT DNAME,ROUND(AVG(SAL),2) FROM EMP,DEPT
WHERE
EMP.DEPTNO=DEPT.DEPTNO GROUP BY DEPT.DNAME;
-- Affichez le nom, la fonction, le salaire, le numéro du département des employés ayant le salaire maximum dans chaque département.
SELECT E1.ENAME,E1.JOB,E1.SAL,E1.DEPTNO FROM EMP E1
WHERE
-- Salaire maximum des employés du...
E1.SAL=( SELECT MAX(SAL) FROM EMP E2
WHERE
-- ... département (lien entre les deux tables)
E1.DEPTNO=E2.DEPTNO);
-- 4. Selection des groupes : HAVING
-- =================================
-- Affichez la liste des salaires moyens par fonction pour les groupes ayant plus de deux employés.
SELECT JOB, COUNT(*),AVG(SAL) FROM EMP GROUP BY JOB HAVING COUNT(*) > 2;
-- * permet de ne pas distinquer les colonnnes
-- Exercice(2)
-- -----------
-- Affiche le nombre de clerc des départements ayant au moins deux employés
SELECT COUNT(*) FROM EMP WHERE JOB='CLERK' GROUP BY JOB HAVING COUNT(*) > 2;
-- 5. Fonction de groupe a deux niveaux
-- =====================================
-- Exercice(6)
-- -----------
-- Affichez le job ayant le salaire moyen le plus bas.
SELECT JOB FROM EMP E1
WHERE
E1.SAL=(SELECT MIN(AVG(SAL)) FROM EMP E2 GROUP BY SAL);
-- Afficher le nombre d'employés du département ayant le plus d'employés.
-- Le département dans lequel il y a le plus d'employé
SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*) =( SELECT MAX(COUNT(*)) FROM EMP GROUP BY DEPTNO );
-- Afficher le nombre d'employés du département ayant le moins d'employés.
SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*) =( SELECT MIN(COUNT(*)) FROM EMP GROUP BY DEPTNO );
-- Afficher les salaires et les noms des managers des départements ayant au moins 3 salariés.
-- Démarche...
-- a/ Les départements ayant au moins 3 salariés
SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*) >= 3;
-- b/ Les salaires et les des noms des managers
SELECT EMP1.SAL, EMP1.ENAME FROM EMP EMP1, EMP EMP2 WHERE EMP2.EMPNO_MGR=EMP1.EMPNO;
-- c/ Afficher les salaires et les noms des managers des départements ayant au moins 3 salariés.
SELECT DISTINCT EMP1.SAL, EMP1.ENAME FROM EMP EMP1, EMP EMP2 WHERE EMP2.EMPNO_MGR=EMP1.EMPNO AND EMP1.DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*) > 3);
-- Exercice Supplémentaire
-- =======================
-- Recherche les doublons dans une base :
-- Recherche les mails qui sont en 2 fois ou plus
SELECT MAIL FROM USERS GROUP BY MAIL HAVING(COUNT(MAIL)) >= 2;
/*****************************************************************************************************************************************************\
* *
* CONTEXTE : DRUPAL *
* *
\*****************************************************************************************************************************************************/
-- Requêtes complexes
-- ==================
-- Selectionne les member_id en commun
SELECT member_id, name FROM a INTERSECT SELECT member_id, name FROM b;
SELECT a.member_id, a.name FROM a INNER JOIN b USING (member_id, name);
-- Selectionne les member_id différents
SELECT member_id, name FROM a MINUS SELECT member_id, name FROM b;
SELECT DISTINCT member_id, name FROM a WHERE (member_id, name) NOT IN (SELECT member_id, name FROM table2);
-- Anonymiser les bases : renommer tout les utilisateurs prenom.nom en toto+uid
-- =================================================================================================
-- Vide les historiques
TRUNCATE login_history;
TRUNCATE name_history;
TRUNCATE password_history;
TRUNCATE sessions;
-- Change le mot de passe pour la connexion au LDAP
UPDATE ldapauth SET `bindpw` = 'PASSWORD',`server` = '127.0.0.1',`ldapdata_bindpw` = 'PASSWORD' WHERE `ldapauth`.`sid` =1 LIMIT 1 ;
-- Supprime les UID non présents dans la table users et anonymise les autres
DELETE FROM authmap WHERE uid NOT in (SELECT uid FROM users);
UPDATE IGNORE `authmap` SET `authname` = REPLACE(authname,authname,CONCAT('toto',uid)) WHERE `uid` > 6;
DELETE FROM `authmap` WHERE authname NOT LIKE 'toto%';
-- Remplace tout les utilisateurs par TOTO
UPDATE `users` SET `mail` = REPLACE(mail,name,CONCAT('toto',uid)) WHERE `uid` > 6;
UPDATE `users` SET `init` = REPLACE(init,name,CONCAT('toto',uid)) WHERE `uid` > 6;
-- Cas: name=prenom.nom mail=prenom1.nom
UPDATE `users` SET `mail` = CONCAT(CONCAT('toto',uid),'@sitename.fr') WHERE `uid` > 6 AND mail NOT LIKE 'toto%';
UPDATE `users` SET `init` = CONCAT(CONCAT('toto',uid),'@sitename.fr') WHERE `uid` > 6 AND init NOT LIKE 'toto%';
UPDATE `users` SET `data` = REPLACE( data, CONCAT(CONCAT(CONCAT(CONCAT('s:',CONCAT(LENGTH( CONCAT('uid=',CONCAT(name,',dmdName=users,dc=sitename,dc=fr') ) ) )),':'),'"uid='),name), CONCAT(CONCAT(CONCAT(CONCAT('s:',CONCAT(LENGTH( CONCAT('uid=',CONCAT(CONCAT('toto',uid),',dmdName=users,dc=sitename,dc=fr') ) ) )),':'),'"uid='),CONCAT('toto',uid)) ) WHERE `uid` > 6;
UPDATE `users` SET `data` = REPLACE(data,name,CONCAT('toto',uid)) WHERE `uid` > 6;
UPDATE `users` SET `name` = REPLACE(name,name,CONCAT('toto',uid)) WHERE `uid` > 6;
-- Mot de passe de tout les utilisateurs (y compris admin)
UPDATE `users` SET `pass` = MD5('password');
-- Remplace les coordonnées de chaque utilisateur
UPDATE `profile_values` SET `value` = CONCAT(UCASE(CONCAT('toto',uid)),' Test') WHERE `fid` = 1;
UPDATE `profile_values` SET `value` = 'Mr' WHERE `fid` = 2;
UPDATE `profile_values` SET `value` = 'Toto' WHERE `fid` = 3;
UPDATE `profile_values` SET `value` = 'TECH' WHERE `fid` = 4;
UPDATE `profile_values` SET `value` = 'Testeur' WHERE `fid` = 5;
UPDATE `profile_values` SET `value` = '08.07.07.99.93' WHERE `fid` = 7;
UPDATE `profile_values` SET `value` = '' WHERE `fid` = 8;
UPDATE `profile_values` SET `value` = '08.99.99.00.09' WHERE `fid` = 9;
UPDATE `profile_values` SET `value` = '08.99.99.00.10' WHERE `fid` = 10;
UPDATE `profile_values` SET `value` = '08.99.99.00.11' WHERE `fid` = 11;
UPDATE `profile_values` SET `value` = CONCAT(CONCAT('toto',uid),'@linagora.com') WHERE `fid` = 12;
UPDATE `profile_values` SET `value` = '001/RDC/001' WHERE `fid` = 15;
UPDATE `profile_values` SET `value` = '01 rue Roque de l Un - 01000 AIN' WHERE `fid` = 16;
-- Renommer un lien dans un espace :
-- =================================================================================================
-- UPDATE `node_revisions` SET `body` = REPLACE(body,REFERENCE,A_REMPLACER) WHERE body LIKE '%REFERENCE%'
UPDATE `node_revisions` SET `body` = REPLACE(body,'http://dgoqo.','http://sirius.') WHERE body LIKE '%http://dgoqo.%'
-- Recherche d'un doublon :
-- =================================================================================================
-- UPDATE $BASE.url_alias SET language = 'fr';
SELECT * FROM url_alias GROUP BY dst HAVING COUNT( * ) > 1;
-- Renommer mail de @sitename.fr => autresite.com
-- =================================================================================================
-- Vérifier qu'il n'y ait pas de doublons
SELECT name,mail FROM users WHERE mail LIKE 'denis.bolder@sitename.fr'
OR mail LIKE 'matthieu.fossat@sitename.fr'
OR mail LIKE 'nicolas.hue@sitename.fr'
OR mail LIKE 'dominique.le-bihan@sitename.fr'
OR mail LIKE 'geoffroy.lenglin@sitename.fr'
OR mail LIKE 'francis.mathiot@sitename.fr'
OR mail LIKE 'fahima.medareg-narou@sitename.fr'
OR mail LIKE 'marie-francoise.mefort@sitename.fr'
OR mail LIKE 'anne-marie.paccagnella@sitename.fr'
OR mail LIKE 'vincent.perrin@sitename.fr'
OR mail LIKE 'laurent.sorin@sitename.fr'
-- mail = init = name+'@autresite.com'
UPDATE `devappli01_tests`.`users` SET `mail` = CONCAT(name,'@autresite.com'),
`init` = CONCAT(name,'@autresite.com') WHERE mail LIKE 'denis.bolder@sitename.fr'
OR mail LIKE 'matthieu.fossat@sitename.fr'
OR mail LIKE 'nicolas.hue@sitename.fr'
OR mail LIKE 'dominique.le-bihan@sitename.fr'
OR mail LIKE 'geoffroy.lenglin@sitename.fr'
OR mail LIKE 'francis.mathiot@sitename.fr'
OR mail LIKE 'fahima.medareg-narou@sitename.fr'
OR mail LIKE 'marie-francoise.mefort@sitename.fr'
OR mail LIKE 'anne-marie.paccagnella@sitename.fr'
OR mail LIKE 'vincent.perrin@sitename.fr'
OR mail LIKE 'laurent.sorin@sitename.fr'
-- Vérifier les modifications
SELECT name,mail,init FROM users WHERE name LIKE 'denis.bolder'
OR name LIKE 'matthieu.fossat'
OR name LIKE 'nicolas.hue'
OR name LIKE 'dominique.le-bihan'
OR name LIKE 'geoffroy.lenglin'
OR name LIKE 'francis.mathiot'
OR name LIKE 'fahima.medareg-narou'
OR name LIKE 'marie-francoise.mefort'
OR name LIKE 'anne-marie.paccagnella'
OR name LIKE 'vincent.perrin'
OR name LIKE 'laurent.sorin'
-- Hors APPLI
-- =================================================================================================
-- 99999999 changer par l'uid
UPDATE `appli01`.`users` SET `data` = 'a:1:{s:7:"contact";i:1;}' WHERE `users`.`uid` = 99999999 LIMIT 1 ;
$sql = "SELECT uid FROM users ORDER BY uid DESC LIMIT 1; ";
$res = _mysql_query( $sql );
$arr = mysql_fetch_array($res, MYSQL_ASSOC);
$uid = $arr["uid"]+1;
-- APPLI
-- =================================================================================================
-- PRENOM.NOM changer par le login
-- ZZ changer par la longueur de uid=PRENOM.NOM,dmdName=users,dc=sitename,dc=fr
-- 99999999 changer par l'uid
UPDATE `appli01`.`users` SET `data` = 'a:8:{s:17:"mimemail_textonly";i:1;s:7:"contact";i:1;s:17:"ldap_authentified";i:1;s:7:"ldap_dn";s:ZZ:"uid=PRENOM.NOM,dmdName=users,dc=sitename,dc=fr";s:11:"ldap_config";s:1:"1";s:11:"newsletters";a:1:{i:512;i:512;}s:16:"roleassign_roles";a:4:{i:4;i:4;i:11;i:0;i:7;i:0;i:8;i:0;}s:13:"form_build_id";s:37:"form-ab297f2a58dfa574ec4d48c55c004435";}' WHERE `users`.`uid` = 99999999 LIMIT 1;
-- Backup
-- =================================================================================================
select `ppdrh`.`f`.`fid` AS `fid`,`ppdrh`.`f`.`uid` AS `uid`,`ppdrh`.`f`.`filename` AS `filename`,`ppdrh`.`f`.`filepath` AS `filepath`,`ppdrh`.`f`.`filemime` AS `filemime`,`ppdrh`.`f`.`filesize` AS `filesize`,`ppdrh`.`f`.`status` AS `status`,`ppdrh`.`f`.`timestamp` AS `timestamp`,`ppdrh`.`u`.`nid` AS `nid` from `ppdrh`.`files` `f` join `ppdrh`.`upload` `u` where ((`ppdrh`.`f`.`fid` = `ppdrh`.`u`.`fid`) and (`ppdrh`.`f`.`filepath` = _utf8'/opt/lampp/htdocs/files/ppdrh/34formation.png'))
-- avec filepath indexé : 1 seconde
-- sans filepath indexé : 5 seconde
INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `mode`, `sort`, `threshold`, `theme`, `signature`, `created`, `access`, `login`, `status`, `timezone`, `language`, `picture`, `init`, `data`, `timezone_name`, `signature_format`) VALUES
(62, 'nicolas.fouville', '37f525e2b6fc3cb4abd882f708ab80eb', 'nicolas.fouville@sitename.fr', 0, 0, 0, '', '', 1272035384, 1304946964, 1304946945, 1, NULL, 'fr', '', 'nicolas.fouville@sitename.fr', 'a:8:{s:17:"mimemail_textonly";i:1;s:7:"contact";i:1;s:17:"ldap_authentified";i:1;s:7:"ldap_dn";s:66:"uid=nicolas.fouville,dmdName=users,dc=sitename,dc=fr";s:11:"ldap_config";s:1:"1";s:11:"newsletters";a:1:{i:512;i:512;}s:16:"roleassign_roles";a:4:{i:4;i:4;i:11;i:0;i:7;i:0;i:8;i:0;}s:13:"form_build_id";s:37:"form-7771685169ca6142e31b46d01d4d8f41";}', '', 3);
INSERT INTO `authmap` (`aid`, `uid`, `authname`, `module`) VALUES
(50, 62, 'nicolas.fouville', 'ldapauth');
-- remplace une chaine par une autre
UPDATE url_alias SET dst = REPLACE(dst,"maquette-","");
-- met à jour le files
UPDATE files SET filepath = REPLACE(filepath,"/opt/lampp/htdocs/files/","/opt/lampp/htdocs/devfiles/");
-- renommer un dossier du système de fichiers
TRUNCATE `cache`;
TRUNCATE `cache_block`;
TRUNCATE `cache_content`;
TRUNCATE `cache_filter`;
TRUNCATE `cache_form`;
TRUNCATE `cache_menu`;
TRUNCATE `cache_page`;
TRUNCATE `cache_project_release`;
TRUNCATE `cache_views`;
TRUNCATE `cache_views_data`;
UPDATE files SET filepath = REPLACE(filepath,"/files/","/devfiles/");
UPDATE node_revisions SET body = REPLACE(body,"/files/","/devfiles/");
-- renommer le hostname : appli01.sitename.fr > appli01pp.sitename.fr
UPDATE files SET filepath = REPLACE(filepath,"/appli01.sitename.fr/","/appli01pp.sitename.fr/");
UPDATE node_revisions SET body = REPLACE(body,"/appli01.sitename.fr/","/appli01pp.sitename.fr/");
UPDATE node_revisions SET teaser = REPLACE(teaser,"/appli01.sitename.fr/","/appli01pp.sitename.fr/");
UPDATE superteaser SET filepath = REPLACE(filepath,"/appli01.sitename.fr/","/appli01pp.sitename.fr/");
-- UPDATE content_field_actu SET field_actu_actu_banniere = REPLACE(field_actu_actu_banniere,"/appli01.sitename.fr/","/appli01pp.sitename.fr/");
-- UPDATE content_field_edito SET field_edito_edito_image = REPLACE(field_edito_edito_image,"/appli01.sitename.fr/","/appli01pp.sitename.fr/");
-- UPDATE content_field_edito SET field_edito_edito_url = REPLACE(field_edito_edito_url,"/appli01.sitename.fr/","/appli01pp.sitename.fr/");
UPDATE menu_links SET link_path = REPLACE(link_path,"/appli01.sitename.fr/","/appli01pp.sitename.fr/");
-- renommer le hostname : appli01.sitename.fr > appli01dev.sitename.fr
UPDATE files SET filepath = REPLACE(filepath,"/appli01.sitename.fr/","/appli01dev.sitename.fr/");
UPDATE node_revisions SET body = REPLACE(body,"/appli01.sitename.fr/","/appli01dev.sitename.fr/");
UPDATE node_revisions SET teaser = REPLACE(teaser,"/appli01.sitename.fr/","/appli01dev.sitename.fr/");
UPDATE superteaser SET filepath = REPLACE(filepath,"/appli01.sitename.fr/","/appli01dev.sitename.fr/");
-- UPDATE content_field_actu SET field_actu_actu_banniere = REPLACE(field_actu_actu_banniere,"/appli01.sitename.fr/","/appli01dev.sitename.fr/");
-- UPDATE content_field_edito SET field_edito_edito_image = REPLACE(field_edito_edito_image,"/appli01.sitename.fr/","/appli01dev.sitename.fr/");
-- UPDATE content_field_edito SET field_edito_edito_url = REPLACE(field_edito_edito_url,"/appli01.sitename.fr/","/appli01dev.sitename.fr/");
UPDATE menu_links SET link_path = REPLACE(link_path,"/appli01.sitename.fr/","/appli01dev.sitename.fr/");
-- renommer un espace : portail > parametrage
UPDATE files SET filepath = REPLACE(filepath,"/portail/","/parametrage/");
UPDATE node_revisions SET body = REPLACE(body,"/portail/","/parametrage/");
UPDATE node_revisions SET teaser = REPLACE(teaser,"/portail/","/parametrage/");
UPDATE superteaser SET filepath = REPLACE(filepath,"/portail/","/parametrage/");
UPDATE variable SET value = 's:35:"/opt/lampp/htdocs/files/parametrage";' WHERE variable.name = 'file_directory_path' LIMIT 1 ;
--UPDATE content_field_actu SET field_actu_actu_banniere = REPLACE(field_actu_actu_banniere,"/portail/","/parametrage/");
--UPDATE content_field_edito SET field_edito_edito_image = REPLACE(field_edito_edito_image,"/portail/","/parametrage/");
--UPDATE content_field_edito SET field_edito_edito_url = REPLACE(field_edito_edito_url,"/portail/","/parametrage/");
UPDATE menu_links SET link_path = REPLACE(link_path,"/portail/","/parametrage/");
TRUNCATE `cache`;
TRUNCATE `cache_block`;
TRUNCATE `cache_content`;
TRUNCATE `cache_filter`;
TRUNCATE `cache_form`;
TRUNCATE `cache_menu`;
TRUNCATE `cache_page`;
TRUNCATE `cache_project_release`;
TRUNCATE `cache_views`;
TRUNCATE `cache_views_data`;
TRUNCATE `watchdog`;
TRUNCATE `accesslog`;
-- renommer un espace : parametrage > portail
UPDATE variable SET value = REPLACE(value,"/parametrage/","/portail/");
UPDATE variable SET value = REPLACE(value,"/parametrage\"","/portail\"");
UPDATE files SET filepath = REPLACE(filepath,"//portail","/portail/");
UPDATE superteaser SET filepath = REPLACE(filepath,"/parametrage/","/portail/");
--
UPDATE variable SET value = 's:31:"/opt/lampp/htdocs/files/portail";' WHERE variable.name = 'file_directory_path' LIMIT 1 ;
-- En appli01 (-> appli01pp) -> appli01dev
----------------------------------------------------------------------------------------------------
bases=$(find /opt/lampp/htdocs/dev -maxdepth 1 -type l |sed 's#/opt/lampp/htdocs/dev/##1')
for base in $bases
do
echo "[+] Traitement de 'dev$base'"
echo '-- renommer le hostname : appli01.sitename.fr > appli01dev.sitename.fr
UPDATE files SET filepath = REPLACE(filepath,"/appli01.sitename.fr/","/appli01dev.sitename.fr/");
UPDATE node_revisions SET body = REPLACE(body,"/appli01.sitename.fr/","/appli01dev.sitename.fr/");
UPDATE node_revisions SET teaser = REPLACE(teaser,"/appli01.sitename.fr/","/appli01dev.sitename.fr/");
UPDATE superteaser SET filepath = REPLACE(filepath,"/appli01.sitename.fr/","/appli01dev.sitename.fr/");
UPDATE menu_links SET link_path = REPLACE(link_path,"/appli01.sitename.fr/","/appli01dev.sitename.fr/");
-- renommer le hostname : appli01.sitename.fr > appli01dev.sitename.fr
UPDATE files SET filepath = REPLACE(filepath,"/appli01pp.sitename.fr/","/appli01dev.sitename.fr/");
UPDATE node_revisions SET body = REPLACE(body,"/appli01pp.sitename.fr/","/appli01dev.sitename.fr/");
UPDATE node_revisions SET teaser = REPLACE(teaser,"/appli01pp.sitename.fr/","/appli01dev.sitename.fr/");
UPDATE superteaser SET filepath = REPLACE(filepath,"/appli01pp.sitename.fr/","/appli01dev.sitename.fr/");
UPDATE menu_links SET link_path = REPLACE(link_path,"/appli01pp.sitename.fr/","/appli01dev.sitename.fr/");
UPDATE files SET filepath = REPLACE(filepath,"/files/","/devfiles/");
UPDATE menu_links SET link_path = REPLACE(link_path,"/appli01pp.sitename.fr/","/appli01dev.sitename.fr/");
'|mysql -uroot -pmasterpass -hlocalhost dev$base
done
drush -d dis appli01_common -u 1 --root=/opt/lampp/htdocs/dev/ --uri=http://appli01dev.sitename.fr/$base
drush -d en appli01_common -u 1 --root=/opt/lampp/htdocs/dev/ --uri=http://appli01dev.sitename.fr/$base