SQL Quick Reference

[scottobear]

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.