diff options
| -rw-r--r-- | composer.json | 2 | ||||
| -rw-r--r-- | lib/SqlFormatter.php | 63 | ||||
| -rw-r--r-- | tests/performance.php | 44 | ||||
| -rw-r--r-- | tests/sql.sql | 61 |
4 files changed, 162 insertions, 8 deletions
diff --git a/composer.json b/composer.json index 6fb0095..d2be0bc 100644 --- a/composer.json +++ b/composer.json @@ -21,7 +21,7 @@ }, "extra": { "branch-alias": { - "dev-master": "1.2.x-dev" + "dev-master": "1.3.x-dev" } } } diff --git a/lib/SqlFormatter.php b/lib/SqlFormatter.php index bc59498..e66b23b 100644 --- a/lib/SqlFormatter.php +++ b/lib/SqlFormatter.php @@ -8,7 +8,7 @@ * @copyright 2012 Jeremy Dorn * @license http://www.opensource.org/licenses/lgpl-license.php LGPL * @link http://github.com/jdorn/sql-formatter - * @version 1.1.0 + * @version 1.2.0 */ class SqlFormatter { @@ -76,6 +76,26 @@ class SqlFormatter // This is a combination of all the boundary characters and all the whitespace characters protected static $all_boundaries; + //cache variables + //Only tokens shorter than this size will be cached. Somewhere between 10 and 20 seems to work well for most cases. + public static $max_cachekey_size = 15; + protected static $token_cache = array(); + protected static $cache_hits = 0; + protected static $cache_misses = 0; + + /** + * Get stats about the token cache + * @return Array An array containing the keys 'hits', 'misses', 'entries', and 'size' in bytes + */ + public static function getCacheStats() { + return array( + 'hits'=>self::$cache_hits, + 'misses'=>self::$cache_misses, + 'entries'=>count(self::$token_cache), + 'size'=>strlen(serialize(self::$token_cache)) + ); + } + /** * Return the next token and token type in a SQL string. * Quoted strings, comments, reserved words, whitespace, and punctuation are all their own tokens. @@ -284,21 +304,50 @@ class SqlFormatter $old_string_len = strlen($string) + 1; $token = null; + + $current_length = strlen($string); // Keep processing the string until it is empty - while (strlen($string)) { + while ($current_length) { // If the string stopped shrinking, there was a problem - if ($old_string_len <= strlen($string)) { + if ($old_string_len <= $current_length) { throw new Exception("SQL Parse Error - Unable to tokenize string at character ".($original_length - $old_string_len)); } - $old_string_len = strlen($string); + $old_string_len = $current_length; - // Get the next token and the token type - $token = self::getNextToken($string, $token); + // Determine if we can use caching + if($current_length >= self::$max_cachekey_size) { + $cacheKey = substr($string,0,self::$max_cachekey_size); + } + else { + $cacheKey = false; + } + + // See if the token is already cached + if($cacheKey && isset(self::$token_cache[$cacheKey])) { + //retrieve from cache + $token = self::$token_cache[$cacheKey]; + $token_length = strlen($token['token']); + self::$cache_hits++; + } + else { + // Get the next token and the token type + $token = self::getNextToken($string, $token); + $token_length = strlen($token['token']); + self::$cache_misses++; + + // If the token is shorter than the max length, store it in cache + if($cacheKey && $token_length < self::$max_cachekey_size) { + self::$token_cache[$cacheKey] = $token; + } + } + $tokens[] = $token; //advance the string - $string = substr($string, strlen($token['token'])); + $string = substr($string, $token_length); + + $current_length -= $token_length; } return $tokens; diff --git a/tests/performance.php b/tests/performance.php new file mode 100644 index 0000000..a4270a0 --- /dev/null +++ b/tests/performance.php @@ -0,0 +1,44 @@ +<?php +require '../lib/SqlFormatter.php'; +//this is the default value +//set to '0' to disable caching +//a value between 10 and 20 seems to give the best result +SqlFormatter::$max_cachekey_size = 15; + +$contents = file('sql.sql'); + +//track time and memory usage +$start = microtime(true); +$ustart = memory_get_usage(true); + +//track number of queries and size of queries +$queries = 0; +$bytes = 0; + +//format each query 3 times +for ($i =0; $i<3; $i++) { + foreach ($contents as $query) { + //this tries to mix up the queries so we aren't just running the same thing a bunch of times + $query = str_replace('tablename', rand(1, 10000), $query); + + //do formatting and highlighting + SqlFormatter::format($query); + + $queries++; + $bytes += strlen($query); + } +} + +$uend = memory_get_usage(true); +$end = microtime(true); + +echo "<p>Formatted $queries queries.</p>"; + +echo "<p>Average query length of ".number_format($bytes/$queries,5)." characters</p>"; + +echo "<p>Took ".number_format($end-$start,5)." seconds total, ".number_format(($end-$start)/$queries,5)." seconds per query, ".number_format(1000*($end-$start)/$bytes,5)." seconds per 1000 characters</p>"; + +echo "<p>Used ".number_format($uend-$ustart)." bytes of memory</p>"; + +echo "<h3>Cache Stats</h3><pre>".print_r(SqlFormatter::getCacheStats(),true)."</pre>"; + diff --git a/tests/sql.sql b/tests/sql.sql new file mode 100644 index 0000000..de6bdeb --- /dev/null +++ b/tests/sql.sql @@ -0,0 +1,61 @@ +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +CREATE DATABASE myNewDatabase +DROP DATABASE database_name , database_name +USE master +EXEC sp_dboption myOldData, 'Single User', TRUE +EXEC sp_renamedb 'myOldData', 'myNewData' +EXEC sp_dboption myNewData, 'Single User', FALSE +CREATE TABLE tablename (COLUMN_NAME column_type [(column_width[,column_precision])] . +CREATE TABLE tablename (fname VARCHAR(20) NOT NULL,lname VARCHAR(30) NOT NULL,hire_date SMALLDATETIME NULL,ssn CHAR(11) NOT NULL) +EXECUTE sp_help practice -- shows structure of table +ALTER TABLE XYZ ADD mi CHAR(1) NULL +SELECT * INTO tablename FROM tablename +SELECT * FROM tablename +IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'newcolumn') BEGIN ALTER TABLE MyTableName ADD newcolumn VARCHAR(32) NOT NULL DEFAULT '' END +IF EXISTS (SELECT * FROM dbo.syscolumns WHERE id = object_id(N'[dbo].[Projects]') AND NAME = 'ProjectManager') ALTER TABLE Projects DROP COLUMN [ProjectManager] +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +INSERT INTO tablename (1996,'ford') -- INTO is an optional keyword in SQLServer +INSERT tablename VALUES ('a','b',DEFAULT,i) -- DEFAULT is a key word +INSERT INTO tablename VALUES(1001,'The Odyssey','Homer',NULL) +INSERT INTO TABLE (c17,c4,c8,c3) +EXECUTE sp_mystored_procedure1 +INSERT INTO OtherDatabaseName.tablename +SELECT * FROM tablename WHERE loginid ='mfincher' +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +UPDATE tablename SET col = VALUE|col|expr +UPDATE TABLE_NAME SET column_id = expr WHERE condition +UPDATE tablename SET password = 'newpass' WHERE USER = 'quest' +UPDATE tablename SET password = 'newpass' WHERE (UserID > 1) AND (UserID < 113) +UPDATE tablename SET password = 'newpass', change='Y' WHERE USER = 'quest' +UPDATE tablename SET discount = discount + .1 WHERE lowqty >= 100 +UPDATE tablename SET LNAME = UPPER(LNAME) +UPDATE tablename SET ytd_sales = (SELECT SUM(qty) FROM sales WHERE sales.title_id = titles.title_id AND ord_date BETWEEN '01/01/94' AND '12/31/94') +INSERT adult SELECT ju.member_no, ad.street, ad.city, ad.state, ad.zip, ad.phone_no, DATEADD(YY, 1, GETDATE()) FROM tablename ju JOIN tablename ad ON ju.adult_member_no = ad.member_no WHERE (DATEADD(YY, 18, ju.birth_date) < GETDATE()) +DELETE FROM tablename WHERE userid < 50 +DELETE tablename -- deletes all rows in a table (see "DROP TABLE" to delete a table) +TRUNCATE TABLE tablename -- deletes all rows, but doesn't log transaction +DELETE tablename WHERE title_id IN (SELECT title_id FROM ...) +SELECT str(myField, 12, 2) FROM tablename +SELECT CAST(datepart(yy,dtime) AS VARCHAR(4)) + ' ' + str(datepart(dy,dtime),3) AS 'Year Day', COUNT(TIME) AS 'Key Question Pages', AVG(TIME) AS 'avg time (msec)' FROM tablename WHERE NAME='Key' GROUP BY CAST(datepart(yy,dtime) AS VARCHAR(4)) + ' ' + str(datepart(dy,dtime),3) ORDER BY CAST(datepart(yy,dtime) AS VARCHAR(4)) + ' ' + str(datepart(dy,dtime),3) ASC +SELECT DISTINCT NAME FROM SYSDATABASES +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id +SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19 FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id |