MySQL

From Pigbert Wiki

Table of contents

1 Pigbert's SQL Utilities

1.1 User Variables
1.2 Arithmetic Functions
1.3 Aggregate Functions (GROUP BY)

1.3.1 Conditional Aggregate Functions (Real Cool!)

1.4 Having Clause

2 Installation

2.1 C#
2.2 Java
2.3 PHP

Pigbert's SQL Utilities

sql reference @ IBM (ftp://ftp.software.ibm.com/ps/products/db2/info/vr6/htm/db2s0/index.htm)
Sql Functions (http://www.sir.com.au/help/sql_function.htm)
select@wikipedia (http://en.wikipedia.org/wiki/Select_(SQL))
SQL Functions (http://www.sir.com.au/help/sql_function.htm)

User Variables

select 
    @a:=((col1+col2+col3)/col4), 
    @b:=((col5+col6+col7)/col8),
    pow(@a,2), pow(@b,2) 
...  table1

create temporary table meowky select * from table1; // create meowky in memory, auto dropped at disconnect
select * from meowky;

drop table meowky;    // good practice
select * from meowky; // no longer works

Arithmetic Functions

+, -, *, /
ABS(num)
POW(base,idx)
ALL( value_list | subquery ): Tests against the values in the value_list. Returns 1 (true) if matches every item. Equivalent to individual tests against each item in the list joined by AND operators. ALL is used particularly to test against sets of values returned by subqueries.
ANY( value_list | subquery ): Tests against the values in the value_list. Returns 1 (true) if matches any item. Equivalent to individual tests against each item in the list joined by OR operators. ANY is used particularly to test against sets of values returned by subqueries.
CDATE ( date-string [, date_map]):

Returns the date integer for the date specified by the date string in the first argument. The second argument is the date map. If the second argument is omitted, the current value of the system parameter DATE is used as the date map.

CTIME ( time_string [, time_map ])

Returns the time integer for the time specified by the time string in the first argument. The second argument is the time map. If the second argument is omitted, the current value of the system parameter TIME is used as the time map.

DATEC ( date_integer, date_map)

Returns a date string for the date integer in the first argument. The second argument is the date conversion map. If the second argument is omitted, the current value of the system parameter DATE is used as the date map.

SELECT .... WHERE [NOT] EXISTS ( SELECT * .... )

Tests whether a subquery returns any rows. Returns 1 (true) if one or more rows are selected in the subquery. NOT can be used to test for the opposite condition. Specify an asterisk in the subquery as the returned variables when using the EXISTS function .

INT

Returns the truncated integer value for the numeric expression.

LEN(string)

Returns the number of characters (including trailing blanks) in the string expression.

LOWER( string )

Returns the string with all uppercase letters converted to their lowercase equivalent.

MAXIMUM( value1, value2 )
MINIMUM ( value1, value2 )
MOD ( number, divisor )

Returns the remainder of the integer division of the number by the divisor.

NOW (0)

Returns the current wall clock time as a time integer. The argument is a dummy argument.

NUM(string)

Returns the numeric equivalent of the number stored in the specified string.

NVL ( column, arg1, arg2, ...)

Returns the first argument if the specified column does not contain missing values. If the column does contain missing values, then the missing value is used to determine which of the remaining arguments is returned.

The system value Undefined returns the next argument (the second) and any specified missing values return the argument that corresponds to the position in the definition of the missing values.

The first missing value defined returns the 3rd argument, the next missing value defined returns the 4th argument and the third missing value defined returns the 5th argument, etc. If this is a database variable, then up to three missing values can be defined.

If this is a table column, then up to 127 missing values can be defined.

The values returned may be string or numeric.

RND ( number [, digits ] )

Returns the integer value rounded to the nearest integer. The optional second argument specifies the number of decimal places to be rounded to instead of the nearest integer.

SBST ( string, start, length )

Returns the substring of the specified string starting at a particular position and continuing for the specified length.

SIGN ( number, sign )

Returns the sign of the second argument times the absolute value of the first argument.

TIMEC ( time_integer, time_map )

Returns a time string for the time specified by the first argument. The second argument is the time map. (Defaults to the system parameter TIME.)

TODAY (0)

Returns the current calendar date as a Julian integer. The argument is a dummy argument.

TRIM ( string )

Returns the specified string with trailing blanks removed.

UPPER ( string )

Returns the string specified with all lowercase letters converted to their uppercase equivalent.

VALLAB ( column_name )

Returns the value label associated with the current value of the specified column_name.

Aggregate Functions (GROUP BY)

SQL Aggregate Functions (http://www.blueclaw-db.com/accessquerysql/sql_aggregate_function.htm)

AVG ([UNIQUE] numeric_col): Returns the average or mean value of the non-missing values for numeric columns. If UNIQUE is specified then only unique values are used to calculate the mean.
COUNT ( [ UNIQUE ] col | * ): Returns the number of non-missing values encountered. If UNIQUE is specified, then only the unique values add to the count. An asterisk as the argument returns the number of all rows selected regardless of whether the values are valid, missing or undefined.
STD( [ UNIQUE ] numeric_col): Returns the standard deviation of the non-missing numeric values. If UNIQUE is specified, then only the unique values are used in calculating the standard deviation.
SUM ( [ UNIQUE ] numeric_col ): Returns the sum of the non-missing values. If UNIQUE is specified, then only the unique values are summed.
FIRST (col): Returns the first non-missing value encountered.
LAST (col): Returns the last non-missing value encountered.
MAX (col): Returns the maximum non-missing value encountered.
MIN (col): Returns the minimum value of the non-missing values. The type of variable returned corresponds to the type of the variable being referenced.

Conditional Aggregate Functions (Real Cool!)

<wiki>
SELECT col1, Sum(col2), Sum(CASE WHEN 1 = col3 THEN 1 END)
   FROM myTable
   WHERE blah
   GROUP BY col1
</wiki>

This is slightly different from below, so just be careful

<wiki>
SELECT col1, Sum(col2), (SELECT Count(*) FROM myTable AS z WHERE 1 = z.col3)
   FROM myTable
   WHERE blah
   GROUP BY col1
</wiki>

Yet another variation (haven't been tested)

<wiki>
SELECT field1, SUM(field2), SUM(DECODE(col3,1,1,0))
 WHERE blah blah FROM table GROUP BY col1
</wiki>

Having Clause

SELECT DeptID, SUM(SaleAmount) FROM Sales
WHERE SaleDate = '01-Jan-2000'
GROUP BY DeptID
HAVING SUM(SaleAmount) > 1000

Installation

C#

www.mysql.com -> Community -> Downloads -> Connectors -> Connector/Net -> 5.1 (the newest) -> download Windows Source and Binaries (ZIP)
unzip -> MySql.Data.msi -> Click to install
Go to installation path, e.g. c:\Program Files\MySQL\...\Binaries\.NET 2.0\MySql.Data.dll
Copy MySql.Data.dll into .net project folder""
Visual Studio -> load project -> project -> References -> Add References -> MySql.Data

In Web.config, replace <connectinStrings /> with

<connectionStrings>
    <add name="ConnectionString" 
         connectionString="
             Data Source=databaseServer(DAN07); 
             database=targetDatabase; 
             User ID=connectionUserID; 
             Password=connectionPassword; 
             Min Pool Size=5;" 
         providerName="MySql.Data.MySqlClient" />
    </connectionStrings>

7. In C# source file, using MySql.Data.MySqlClient;

Java

Get the JDBC jar file and put it under your build path. (In my case it is under D:/yz/java/Library/MySql/mysql-connector-java-3.0.11-stable-bin.jar)

Then connect to it. The following code would help:

	Class.forName("com.mysql.jdbc.Driver").newInstance();
        String url = "jdbc:mysql://<url>:<port>/<databaseName>"; 
        Coonection connection = DriverManager.getConnection(url,<username>,<password>);

PHP

Retrieved from "http://www.pigbert.net/wiki/MySQL"