SQL

From Wiki
Jump to: navigation, search

Comparison of different SQL implementations

MySQL

Query Execution Order

  1. SELECT / UPDATE / DELETE / INSERT
  2. FROM clause
  3. WHERE clause
  4. GROUP BY clause
  5. HAVING clause
  6. ORDER BY clause
  7. LIMIT clause
Operation SQL HTTP
Create INSERT PUT / POST
Read (Retrieve) SELECT GET
Update (Modify) UPDATE POST / PUT
Delete DELETE DELETE

SELECT

SELECT * → for all columns

WHERE Clause

WHERE = ColumnName
WHERE <> ColumnName
WHERE =< ColumnName
WHERE IN (ColumnName1, ColumnName2)
WHERE NOT (ColumnName1 > 5000)
WHERE NOT (ColumnName1 > 5000 AND ColumnName1 < 10000)
WHERE LIKE 'ColumnName1%'

    Strings:
'abc%' matches strings beginning with 'abc';
'%xyz' matches strings ending with 'xyz';
'%aaa%' matches strings containing 'aaa';
'___' matches strings containing exactly three characters; and
'a_b%' matches strings beginning with 'a', followed by any single character, followed by 'b', followed by zero or more characters.

GROUP BY Clause

GROUP BY ColumnName1 HAVING COUNT > 3
Aggregates by a column
Cannot use WHERE clause in group by - use HAVING instead
GROUP BY ColumnName WITH ROLLUP
Shows summary of group

ORDER BY Clause

ORDER BY ColumnName1
Which column the query sorts by

LIMIT Clause

LIMIT 2
Display only first 2 rows
LIMIT 1,2
Skip row 1, display next 2 rows

</blockquote>

Special Queries


FIND AND REPLACE
UPDATE table
SET `Column` = replace(`sameColumn`, 'string to find', 'string replacement');
FIND AND REPLACE BLANK
UPDATE table
SET `column1` = "text"
WHERE `column1` = '' or `column1` is null


IF STATEMENTS
_______
Substring_Index
Extracts Text from a string. Can be used with concat.
LPAD
Extracts Text

Data Types

Type Storage(Bytes) Minimum Value Maximum Value
TINYINT 1 -128 127
SMALLINT 2 -32,768 32,767
MEDIUMINT 3 -8,388,608 8,388,607
INT 4 -2.1 trillion 2.1 trillion
BIGINT 8 -922 quadrillion 922 quadrillion


Type “Zero” Value
DATE '0000-00-00'
TIME '00:00:00'
DATETIME '0000-00-00 00:00:00'
TIMESTAMP '0000-00-00 00:00:00'
YEAR 0000

SQL SERVER

MS Reference Guide

MISC PROGRAMMING EXAMPLES

Using PHP - Fetch Data with AJAX

http://www.w3schools.com/php/php_ajax_database.asp
http://www.tizag.com/ajaxTutorial/ajax-mysql-database.php

Ajax Add & Delete MySQL records

http://www.sanwebe.com/2012/04/ajax-add-delete-sql-records-jquery-php

Text Messaging with PHP

https://www.textmagic.com/docs/api/

Ratchet Push Integration

Web socket connection allowing real time updates for all users viewing page.
http://socketo.me/docs/push

PHP User Location Detection

function detect_city($ip) { 
        
        $default = 'UNKNOWN'; 
        if (!is_string($ip) || strlen($ip) < 1 || $ip == '127.0.0.1' || $ip == 'localhost') 
            $ip = '8.8.8.8'; 

        $curlopt_useragent = 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2) Gecko/20100115 Firefox/3.6 (.NET CLR 3.5.30729)'; 
        
        $url = 'http://ipinfodb.com/ip_locator.php?ip=' . urlencode($ip); 
        $ch = curl_init(); 
        
        $curl_opt = array( 
            CURLOPT_FOLLOWLOCATION  => 1, 
            CURLOPT_HEADER      => 0, 
            CURLOPT_RETURNTRANSFER  => 1, 
            CURLOPT_USERAGENT   => $curlopt_useragent, 
            CURLOPT_URL       => $url, 
            CURLOPT_TIMEOUT         => 1, 
            CURLOPT_REFERER         => 'http://' . $_SERVER['HTTP_HOST'], 
        ); 
         
        curl_setopt_array($ch, $curl_opt); 
        
        $content = curl_exec($ch); 
        
        if (!is_null($curl_info)) { 
            $curl_info = curl_getinfo($ch); 
        } 
        
        curl_close($ch); 
        
        if ( preg_match('{<li>City : ([^<]*)</li>}i', $content, $regs) )  { 
            $city = $regs[1]; 
        } 
        if ( preg_match('{<li>State/Province : ([^<]*)</li>}i', $content, $regs) )  { 
            $state = $regs[1]; 
        } 

        if( $city!='' && $state!='' ){ 
          $location = $city . ', ' . $state; 
          return $location; 
        }else{ 
          return $default; 
        } 
        
}
Expand to see code...