| SELECT {TOP #} {DISTINCT} Fields {INTO tablename} | |
| FROM tablename | |
| {WHERE condition {AND condition} | |
| {GROUP BY non-aggregate fields | Each field must be in the "group by" or aggregate (if used) |
| {HAVING condition {AND condition}}} | Requires a group by phrase |
| {ORDER BY fields}} | Sorting order |
Functions:
AVG, COUNT, MAX, MIN, SUM
SET NOCOUNT ON
SET ROWCOUNT #
SET IOSTATISTICS ON
Joins:
(Inner, outer, left, right, cross, full)
Ex: SELECT booktitle, sum(quantity), name FROM titles T, Sales S, Customer C,
WHERE T.part = S.part and S.customer = C.customer
GROUP BY booktitle, name
Outer ex: ...Where T.part *= S.part (includes all of T records and only matching S records, else null)
Oracle ex: ...Where T.part =(+) S.part (includes all of T records and only matching S records, else null)
Changes:
INSERT INTO tablename (field1, field2...) VALUES (value1, value2...)
INSERT INTO tablename {(field names)} SELECT fields FROM table WHERE condition
UPDATE tablename SET field1 = value1, field2 = field2 FROM table WHERE condition
DELETE FROM tablename WHERE condition
CREATE TABLE tablename (field1 datatype, field2 datatype...) {AS (SELECT ...)}
CREATE DATABASE dbname ON (NAME = logical, FILNAME(?) = , SIZE = maxsize,
FILEGROWTH = ...) LOG ON (names...)
ALTER TABLE tablename ADD field datatype
ALTER TABLE tablename MODIFY field datatype
DROP TABLE tablename
CREATE VIEW viewname {(field names)} AS SELECT fields FROM table WHERE condition
DROP VIEW viewname
Cursors:
DELCARE cursorname CURSOR FOR SELECT...
OPEN cursorname
CLOSE cursorname
FETCH cursorname INTO (var1, var2, ...) each FETCH advances through the recordset
@@rowcount is the number of rows in the set not fetched yet
@@identity is the row number in the table
@@SQLstatus is the error state of the last fetch (0=success, 1=error, 2= no data)
DEALLOCATE CURSOR cursorname
Rights: GRANT, DENY, REVOKE
Misc:
| USE databasename | Put a # in front of a table name to make it temporary. |
| EXISTS (query as above) | returns a boolean value |
| BREAK | exits the While loop |
| CONTINUE | Skips to the beginning of the loop again. |
| Sp_help, sp_helpdb, sp_addtype, sp_droptype | Be aware of page size and row/page locking modes. |
Programming:
DECLARE @variablename datatype
SELECT @variablename=constant
PRINT @variablename
WHILE condition BEGIN {code statements...} END
IF condition BEGIN {code statements...} END ELSE IF condition BEGIN {code statements...} END
ELSE BEGIN {code statements...} END
BEGIN TRANSACTION {transactionname}
ROLLBACK TRANSACTION
COMMIT TRANSACTION
CREATE PROCEDURE procedurename
@variablename datatype, @variablename datatype...
AS BEGIN
SQL statements...
END
EXECUTE procedurename
CREATE TRIGGER triggername ON tablename FOR [DELETE/INSERT/UPDATE]
AS BEGIN
SQL statements...
END