Raj KAJ (scottobear) wrote,
Raj KAJ
scottobear

SQL Quick Reference

SQL QUICK REFERENCE
ColdFusion Query Syntax
--------------------------------------


<cfquery name="query_name" datasource=#datasource_name#>
SELECT column_name
FROM table_name
WHERE column_name = #column_value#
</cfquery>
Select Statement
--------------------------------------
SELECT "column_name" FROM "table_name"
Distinct
--------------------------------------
SELECT DISTINCT "column_name"
FROM "table_name"
Where
--------------------------------------
SELECT "column_name"
FROM "table_name"
WHERE "condition"
And/Or
--------------------------------------
SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{[AND|OR] "simple condition"}+
In
--------------------------------------
SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...)
Between
--------------------------------------
SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2'
Like
--------------------------------------
SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN}
Order By
--------------------------------------
SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC, DESC]
Count
--------------------------------------
SELECT COUNT("column_name")
FROM "table_name"
Group By
--------------------------------------
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
Having
--------------------------------------
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithematic function condition)
Create Table Statement
--------------------------------------
CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )
Drop Table Statement
--------------------------------------
DROP TABLE "table_name"
Truncate Table Statement
--------------------------------------
TRUNCATE TABLE "table_name"
Insert Into Statement
--------------------------------------
INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)
Update Statement
--------------------------------------
UPDATE "table_name"
SET "column_1" = [new value]
WHERE {condition}
Delete From Statement
--------------------------------------
DELETE FROM "table_name"
WHERE {condition}
OperatorS
--------------------------------------
= Equal
<> Not Equal
> Greater Than
< Less Than
>= Greater Than Or Equal
<= Less Than Or Equal
Functions
--------------------------------------
SUM - Total of the values in a field.
AVG - Average of the values in a field.
MIN - Lowest value in a field.
MAX - Highest value in a field.
COUNT - Number of values in a field, not counting Null (blank) values.
ColdFusion Query Syntax with Function
--------------------------------------
<cfquery name="query_name" datasource=#datasource_name#>
SELECT MAX(column_name)
FROM table_name
WHERE column_name = #column_value#
</cfquery>

Data Types
————————————–
bigint: Integer data from -2^63 through 2^63-1
int: Integer data from -2^31 through 2^31 – 1
smallint: Integer data from -2^15 through 2^15 – 1
tinyint: Integer data from 0 through 255
bit: Integer data with either a 1 or 0 value
decimal: Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1
numeric: Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1
money: Monetary data values from -2^63 through 2^63 – 1
smallmoney: Monetary data values from -214,748.3648 through +214,748.3647
float: Floating precision number data from -1.79E + 308 through 1.79E + 308
real: Floating precision number data from -3.40E + 38 through 3.40E + 38
datetime: Date and time data from January 1, 1753, through December 31, 9999,
with an accuracy of 3.33 milliseconds
smalldatetime: Date and time data from January 1, 1900, through June 6, 2079,
with an accuracy of one minute
char: Fixed-length character data with a maximum length of 8,000 characters
varchar: Variable-length data with a maximum of 8,000 characters
text: Variable-length data with a maximum length of 2^31 – 1 characters
nchar: Fixed-length Unicode data with a maximum length of 4,000 characters
nvarchar: Variable-length Unicode data with a maximum length of 4,000 characters
ntext: Variable-length Unicode data with a maximum length of 2^30 – 1 characters
binary: Fixed-length binary data with a maximum length of 8,000 bytes
varbinary: Variable-length binary data with a maximum length of 8,000 bytes
image: Variable-length binary data with a maximum length of 2^31 – 1 bytes
cursor: A reference to a cursor
sql_variant: A data type that stores values of various data types,
except text, ntext, timestamp, and sql_variant
table: A special data type used to store a result set for later processing
timestamp: A database-wide unique number that gets updated every time
a row gets updated
uniqueidentifier: A globally unique identifier

 

Originally published at The Scotto Grotto. You can comment here or there.

Tags: code, uncategorized
Subscribe
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 0 comments