Tags: sql

Sort by: Date / Title /

  1. 9 years ago by jemini_fr
    1. Escape quotes
    2. USE two quotes FOR every one displayed. Examples:
    3. SQL> SELECT 'Frank''s Oracle site' AS text FROM DUAL;
    4.  TEXT
    5.  --------------------
    6.  Franks's Oracle site
    7.  
    8. SQL> SELECT 'A ''quoted'' word.' AS text FROM DUAL;
    9. TEXT
    10. ----------------
    11. A 'quoted' word.
    12.  
    13. SQL> SELECT 'A ''''double quoted'''' word.' AS text FROM DUAL;
    14. TEXT
    15. -------------------------
    16. A ''double quoted'' word.
    17.  
    18. Escape wildcard characters
    19. The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, while '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Examples:
    20. SELECT name FROM emp
    21. WHERE id LIKE '%/_%' ESCAPE '/';
    22.  
    23. SELECT name FROM emp
    24. WHERE id LIKE '%\%%' ESCAPE '\';
    25.  
    26. Escape ampersand (&) characters in SQL*Plus
    27. When using SQL*Plus, the DEFINE setting can be changed to allow &'s (ampersands) TO be used IN text:
    28. SET DEFINE ~
    29. SELECT 'Lorel & Hardy' FROM dual;
    30.  
    31. Other methods:
    32. Define an escape character:
    33. SET ESCAPE '\'
    34. SELECT '\&abc' FROM dual;
    35.  
    36. Don't scan FOR substitution VARIABLES:
    37. SET SCAN OFF
    38. SELECT '&ABC' x FROM dual;
    39.  
    40. USE the 10g Quoting mechanism:
    41. Syntax
    42.  q'[QUOTE_CHAR]Text[QUOTE_CHAR]'
    43.  Make sure that the QUOTE_CHAR doesnt exist IN the text.
    44.  
    45. SELECT q'{This is Orafaq's 'quoted' text FIELD}' FROM DUAL;
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1363"></script>
  2. 9 years ago by sx
    1. SELECT MAX(id) FROM TABLE;
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1318"></script>
  3. 9 years ago by nox.freak
    This is just a basic way to query a MySQL database.
    1. define("DBNAME", "db_name");
    2. define("HOST", "localhost");
    3. define("USER", "foo");
    4. define("PASSWORD", "bar");
    5.  
    6. $dbconnect = mysql_connect(HOST, USER, PASSWORD);
    7. mysql_select_db(DBNAME, $dbconnect);
    8.  
    9. $sql = 'SELECT * FROM table';
    10. $q = mysql_query($sql, $dbconnect) or die("Error: " . mysql_error());
    11. $num = mysql_num_rows($q);
    12. for ($i=0; $i<$num; $i++)
    13. {
    14.         $dbOut = mysql_fetch_assoc($q);
    15.         echo $dbOut['col'];
    16. }
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1287"></script>
  4. sponsorised links
  5. 9 years ago by cyo
    1. Note : Dans tous les cas, ajouter l’option -p si un mot de passe est nécessaire après le nom du compte. Dans les exemples, on utilise le compte 'root'. ATTENTION : Il ne faut pas mettre d’espace entre l’option -p et le mot de passe.
    2.  
    3. Pour exporter avec l’utilitaire mysqldump.exe :
    4. mysqldump -u root database > backup-file.sql
    5.  
    6. Exemple avec l’export d’une seule table, encodée en LATIN 1 :
    7. mysqldump -u root -p --default-character-set=latin1 database table > backup-file.sql
    8.  
    9.  
    10. Pour importer, cliquer "démarrer/executer", puis taper ’cmd’. Ensuite lancer pour Wamp :
    11. C:\"Program Files"\wamp\bin\mysql\mysql5.0.45\bin\mysql.exe -u root nom_de_la_base < c:\fichier.sql
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1280"></script>
  6. 9 years ago by sx
    1. SELECT LAST_INSERT_ID();
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1241"></script>
  7. 10 years ago by cyo
    1. SELECT ChampUnique
    2. FROM MaTable
    3. GROUP BY ChampUnique HAVING COUNT(*) > 1
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1008"></script>
  8. 10 years ago by yoko
    Voici une fonction bash pour exécuter un programme sql ou une commande sql sur sqlplus directement depuis oracle
    function sql () {
        [ $# -ne 1 ] && echo "Usage : sql [ fichier.sql | commande ]" && return 1
        if [ -f "$*" ]; then
            cat "$*" | sqlplus -s login/passwd;
        else
            echo "$*" | sqlplus -s login/passwd;
        fi
    }
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/535"></script>
  9. 10 years ago by xillon
    1. function selectTable($nomtable, $where = NULL, $order = NULL)
    2.          {
    3.             $sql = "SELECT * FROM $nomtable";
    4.             if ( $where !== NULL ) $sql .= " WHERE $where";
    5.             if ($order !== NULL)$sql.=" ORDER BY $order ";
    6.            
    7.             $query=mysql_query($sql);
    8.            
    9.             $ret = array();
    10.             while ( $donnee = mysql_fetch_assoc($query))
    11.             {
    12.                $ret[] = $donnee;
    13.             }
    14.             return $ret;
    15.          }
    16.          
    17.          function sqlSelect($sqlDonnee, $nomCol, $valCol)
    18.          {
    19.             for($i = 0 ; $i<count($sqlDonnee) ; $i++)
    20.             {
    21.                $selectDonee["name"][$i] = $sqlDonnee[$i][$nomCol];
    22.                $selectDonee["value"][$i] = $sqlDonnee[$i][$valCol];
    23.             }
    24.          return $selectDonee;
    25.          }
    26.          
    27.          function htmlSelect($nom, $options, $selected = NULL, $css = NULL)
    28.          {
    29.             $select  = "<select name=\"".htmlentities($nom)."\"";
    30.             if ( $css !== NULL )
    31.                $select .= " $css>";
    32.             else
    33.                $select .= ">";
    34.  
    35.             for($i = 0 ; $i<count($options["name"]) ; $i++)
    36.             {
    37.                if ( $selected !== NULL && $selected == $options["value"][$i] )
    38.                   $select .= "<option value=\"".$options["value"][$i]."\" selected=\"selected\">".$options["name"][$i]."</option>\n";
    39.                else
    40.                   $select .= "<option value=\"".$options["value"][$i]."\">".$options["name"][$i]."</option>\n";
    41.             }
    42.             $select .= "</select>\n";
    43.  
    44.             return $select;
    45.          }
    46.  
    47. /*** Exemple utilisation ***/
    48. /* <option selected> par defaut pour le pays ayant l'id 72 */
    49.          $paysSelect = isset($_POST["pays"]) ? ($_POST["pays"]) : ("72");
    50.          
    51.          $selectPays=selectTable("pays");
    52.          $pays=sqlSelect($selectPays,"nom","id");         
    53.          echo htmlSelect("id", $pays, $paysSelect); // on construit notre <select>
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/218"></script>
  10. 10 years ago by neorom
    1. <?
    2. // *** Script assurant la connexion à la base utilisateurs ***//
    3. $serveur = "localhost"; // Variable correspondant au nom du serveur sur lequel est installé la base
    4. $user = "clients"; // Nom d'utilisateur pour se connecter à la base
    5. $motdepasse = "toto"; // Mot de passe associer à $user pour se connecter à la base
    6. $base ="clients"; // Nom de la base à laquelle on effectue une connection
    7.  
    8. $connection_serveur = mysql_connect($serveur, $user, $motdepasse); // Connection à MySQL
    9. if ($connection_serveur == 0 ) // Si la connection à mysql échoue
    10. {
    11.         echo "Désolé la connection a $serveur est impossible.";
    12.         exit;   // Fin du script
    13. }
    14.  
    15. $connection_base = mysql_select_db($base);      // Connection à la base
    16.  
    17. if (!$connection_base) //Si connection à la base impossible
    18. {
    19. echo "<center><font color=\"RED\"><br>";
    20.         echo "Désolé, accès à la base $base impossible. <br>";
    21.         echo "Message de MySQL : <i><b>".mysql_error(); // Affiche le message d'erreur généré par MySQL
    22.         exit// Fin du script
    23.         echo"</font></center>";
    24. }
    25.  
    26. ?>
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/15"></script>
  11. 10 years ago by neorom and saved by 2 others
    3ème version plus complète des backups
    1. #!/bin/bash
    2.  
    3. TAR_COMPRESS="tar cjvf" # Commande de compression
    4. BACKUP_EXTENSION="tar.bz2" # Extension des fichiers compressés
    5. DATE=`date '+%d-%m-%Y'`
    6. BACKUP_ADDRESS="sav@server.org"
    7. BACKUP_DIR="/directory"
    8.  
    9.  
    10.  
    11. echo "Sauvegarde des fichiers"
    12.  
    13. echo "On sauvegarde et on envoie le home"
    14. cd /home
    15. for i in `ls`
    16. do
    17.                 $TAR_COMPRESS $i-$DATE.$BACKUP_EXTENSION /home/$i/*
    18.                 echo "`date` debut scp $i" >> /var/log/backup.log
    19.                 scp $i-$DATE.$BACKUP_EXTENSION $BACKUP_ADDRESS:$BACKUP_DIR
    20.                 echo "`date` fin scp $i" >> /var/log/backup.log
    21.                 rm -f $i-$DATE.$BACKUP_EXTENSION
    22. done
    23.  
    24. echo "On sauvegarde et on envoie le home du root "
    25. $TAR_COMPRESS root-$DATE.$BACKUP_EXTENSION /root/
    26. echo "`date` debut scp /root" >> /var/log/backup.log
    27. scp root-$DATE.$BACKUP_EXTENSION $BACKUP_ADDRESS:$BACKUP_DIR
    28. echo "`date` fin scp /root" >> /var/log/backup.log
    29. rm -f root-$DATE.$BACKUP_EXTENSION
    30. echo "On sauvegarde et on envoie le etc"
    31. $TAR_COMPRESS etc-$DATE.$BACKUP_EXTENSION /etc/
    32. echo "`date` debut scp /etc" >> /var/log/backup.log
    33. scp etc-$DATE.$BACKUP_EXTENSION $BACKUP_ADDRESS:$BACKUP_DIR
    34. echo "`date` fin scp /etc" >> /var/log/backup.log
    35. rm -f etc-$DATE.$BACKUP_EXTENSION
    36.  
    37. echo "On passe au dump MySQL"
    38. MYSQL_USER="root" # User mySQL à utiliser pour faire le dump
    39. MYSQL_PASSWORD="pass" # Mot de passe root de la base mySQL
    40. MYSQL_HOST="localhost" # Host de la base mySQL
    41. SQL_LIST_DB="SHOW DATABASES;" # Requete SQL listant toutes les bases
    42.  
    43. cd /home/backup/dumpsql
    44. echo "Dump de toutes les bases MySQL"
    45. mysqldump -p"$MYSQL_PASSWORD" --all-databases > all-databases.$DATE.sql
    46.  
    47. echo "Compression et envoie du dump"
    48. $TAR_COMPRESS  all-databases-$DATE.sql.$BACKUP_EXTENSION all-databases.$DATE.sql
    49. echo "`date` debut scp all databases" >> /var/log/backup.log
    50. scp all-databases-$DATE.sql.$BACKUP_EXTENSION $BACKUP_ADDRESS:$BACKUP_DIR
    51. echo "`date` fin scp all databases" >> /var/log/backup.log
    52. rm -f all-databases.$DATE.sql
    53. rm -f all-databases-$DATE.sql.$BACKUP_EXTENSION
    54.  
    55. echo "Dump base par base"
    56. for database in `echo $SQL_LIST_DB | mysql -u $MYSQL_USER -p"$MYSQL_PASSWORD" -h $MYSQL_HOST | sed 1d`
    57. do
    58.  
    59.         mysqldump -p"$MYSQL_PASSWORD" --databases $database > $database-$DATE.sql
    60.  
    61.         $TAR_COMPRESS $database-$DATE.sql.$BACKUP_EXTENSION $database-$DATE.sql
    62.         rm -f $database-$DATE.sql
    63. done
    64.  
    65. echo "On sauvegarde le dump des bases et on l'envoie"
    66. $TAR_COMPRESS dump-$DATE.$BACKUP_EXTENSION *.*.sql.$BACKUP_EXTENSION
    67. echo "`date` debut scp base par base" >> /var/log/backup.log
    68. scp dump-$DATE.$BACKUP_EXTENSION $BACKUP_ADDRESS:$BACKUP_DIR
    69. echo "`date` fin scp base par base" >> /var/log/backup.log
    70. rm -f dump-$DATE.$BACKUP_EXTENSION
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/13"></script>

First / Previous / Next / Last / Page 1 of 1 (10 posteets)