Posteet: sql

Echappement des caractères spéciaux dans les requêtes SQL

jemini_fr — Fri, 14 Nov 2008 09:54:11 +0000

Escape quotes 
Use two quotes for every one displayed. Examples: 
SQL> SELECT 'Frank''s Oracle site' AS text FROM DUAL;
 TEXT
 --------------------
 Franks's Oracle site

 SQL> SELECT 'A ''quoted'' word.' AS text FROM DUAL;
 TEXT
 ----------------
 A 'quoted' word.

 SQL> SELECT 'A ''''double quoted'''' word.' AS text FROM DUAL;
 TEXT
 -------------------------
 A ''double quoted'' word.

Escape wildcard characters 
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: 
SELECT name FROM emp 
 WHERE id LIKE '%/_%' ESCAPE '/';

SELECT name FROM emp 
 WHERE id LIKE '%\%%' ESCAPE '\';

Escape ampersand (&) characters in SQL*Plus 
When using SQL*Plus, the DEFINE setting can be changed to allow &'s (ampersands) to be used in text: 
SET DEFINE ~
SELECT 'Lorel & Hardy' FROM dual;

Other methods: 
Define an escape character: 
SET ESCAPE '\'
SELECT '\&abc' FROM dual;

Don't scan for substitution variables: 
SET SCAN OFF
SELECT '&ABC' x FROM dual;

Use the 10g Quoting mechanism: 
Syntax
 q'[QUOTE_CHAR]Text[QUOTE_CHAR]'
 Make sure that the QUOTE_CHAR doesnt exist in the text.

SELECT q'{This is Orafaq's 'quoted' text field}' FROM DUAL;

[échappement] [SQL]

highest id in table

sx — Thu, 16 Oct 2008 08:11:21 +0000

SELECT MAX(id) FROM table;

[lastid] [sql]

MySQL Query

nox.freak — Fri, 26 Sep 2008 18:56:18 +0000

define("DBNAME", "db_name");
define("HOST", "localhost");
define("USER", "foo");
define("PASSWORD", "var");

$dbconnect = mysql_connect(HOST, USER, PASSWORD);
mysql_select_db(DBNAME, $dbconnect);

$sql = 'SELECT * FROM table';
$q = mysql_query($sql, $dbconnect) or die("Error with SQL: $sql");
$num = mysql_num_rows($q);
for ($i=0; $i<$num; $i++)
{
	$dbOut = mysql_fetch_assoc($q);
	echo $dbOut['col'];
}

[query] [sql]

Insérer un fichier SQL sur un serveur Apache Windows (dans Wamp ou EasyPhp)

cyo — Tue, 23 Sep 2008 13:33:21 +0000

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.

Pour exporter avec l’utilitaire mysqldump.exe :

mysqldump -u root database > backup-file.sql

Pour importer, cliquer "démarrer/executer", puis taper ’cmd’. Ensuite lancer pour Wamp :

C:\"Program Files"\wamp\bin\mysql\mysql5.0.45\bin\mysql.exe -u root nom_de_la_base < c:\fichier.sql

[données] [export] [fichier] [import] [mysql] [sql]

get the last SQL entry

sx — Wed, 03 Sep 2008 07:51:15 +0000

SELECT LAST_INSERT_ID();

[sql]

Trouver les enregistrements en doublon dans une table MySQL

cyo — Thu, 12 Jun 2008 14:38:27 +0000

SELECT ChampUnique
FROM MaTable
GROUP BY ChampUnique HAVING COUNT(*) > 1

[doublon] [sql]

sqlplus dans un shell

yoko — Fri, 30 Nov 2007 23:05:16 +0000

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
}

[bash] [oracle] [sql] [sqlplus]

Construct select (HTML) dynamic with any table SQL

xillon — Mon, 05 Nov 2007 11:52:08 +0000

function selectTable($nomtable, $where = NULL, $order = NULL)
         {
            $sql = "SELECT * FROM $nomtable";
            if ( $where !== NULL ) $sql .= " WHERE $where";
            if ($order !== NULL)$sql.=" ORDER BY $order ";
            
            $query=mysql_query($sql);
            
            $ret = array();
            while ( $donnee = mysql_fetch_assoc($query))
            {
               $ret[] = $donnee;
            }
            return $ret;
         }
         
         function sqlSelect($sqlDonnee, $nomCol, $valCol)
         {
            for($i = 0 ; $i".$options["name"][$i]."\n";
               else
                  $select .= "\n";
            }
            $select .= "\n";

            return $select;
         }

/*** Exemple utilisation ***/
/*