Quick study computer SQL guide 600dpi
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (9.49 MB, 4 trang )
CK REFERENCE SOFTWARE GUIDE
•
DATABASES
SQL (Structured Query Language): A standard language for accessing data stored in databases
While it is '"standardizcd," there are many variations, each with its own proprietary extensions and
features
The latest standard is ANSI SQL 99 (which was preccded by SQL-92)
This guidc will discuss the ANSI SQL-99 standard and variations implemented in Microsoft SQL
Datatypcs: The various data formats that can he stored; summari7ed in labk,
to make it easy to sec the types supportcd and their namcs
:x
______
.~=-
Server'" 2000 (other versions of Microsoft SQL Serve and other database vendors are similar as the
content discussed in this guide is introductory; refer to the product's documentation for any differences)
BIT
TINYINT
SMALLINT
• • • • • • • • •IINT
COVERED IN THIS GUIDE
BIGINT
This guide assumes understanding of bas ic database concepts
IBM started out SQL as a SEQUEL (Structured English QUEry Language ) in the 1970s
- It was a standard language to query databases
- This has changed very little in its scope and purpose over the years
The American National Standards Institute (ANSI) designed a standard that allowed database vendors
better interoperability between versions and made it easier to learn and migrate between database vendors
- Their first standard was released in 1986, and was updated in 1989, 1992, and 1999
One of SQL-92's big changes was the introduction of conformance levels
- They were, from simplest to the most complete, implementations of the standard: Entry, Intermediate,
and Full
- Each level encompasscd all of the preceding levels and added new capabilities
- To claim ANS I compliance, the product had to at least support the Entry level
SQL-99 added, among other things, object-oriented datatypcs
- It also changed Entry Icvel conformance to Co re SQL: 1999
- Also changcd the other 2 levels to 9 sets of additional features
- Any database that meets the core spec and at least I additional feature set is Enhanced SQL. 1999
complim;t
••••••••
Notation
SELECT
A SQL keyword: type thi s text exactly as shown
BOLD ALL CAPS
SELECT
Statements and options that are part of the ANSI standard
I"ilic Text
Dall/hose_nome
F LOAT [(II)]
SMALLMONEY
MONEY
CHAR[(nl]
NCHAR[(n)j
Replace the italic text with the text desc ribed in italics; ex.
Da/{tba.lc_"i1I11 C with MyDB
VARCHAR[(n)]
ORDER BY
Field_NOllie
Optional syntax; the command will work without specifing this
parameter; do not type []
NVARCHAR[(II)j
ORDER BY
Fie/d_Nolllel ,
Fieil'-NlIllle2
Multiple parameters may be specified separated by
commas; in some cases multiple optional parameters
may be specified as noted in the usc of square brackets: do not type
NOT NULL
CHECK
(Sa lary < 100)
Multiple parameters may be specified, separated by
spaces; in some cases multiple optional parameters
may be specified, as. noted in the usc of square brackets; do not type
lJ; in this example, the field must not be blank and the value < 100
FROM Jobs
Parameters (or I of them if multiple options arc givcn) must be
specified; in this example, thc FROM clause must be specified in a
SELECT statement
NUL L I
NOT NULL
Only I of the options li sted may be chosen; in this case NULL or NOT
NULL
III)
( 15)
Entered the des ired number in parenthesis
BINARY[(n)]
Expressioll
Field_Nallle or
A field, vari able, or combinat ion of fields and/or variables separated
by operators or a lilllction involving fields and/or variables
VARBINARY[illl]
Definition of parameter referenced elsewhere
IMAGE
[]
[Option
[, ... n]]
[Optioll
"n]]
1:
SUM
1-1 C
nee
*Note th at not all statements or all possible options for every statement will be covered in this guide
II
°
_____________.....
Stores or I only
Stores integers Irom through 255 in I byte
Stores integers from -J27/lH through 32 767 in :2 b~tes
Stores integers from -2,147,4 83 .(,-1 8 through
2,147,483,647 in4 bytes
Store, integers Irom -l),22J ,372,0.J6,g~,775,XOR
through 9,2 2J ,372,036,854,775 ,H07 in X byte,
Stores numbers with lip to 38 di gits nf pn.: ~is iLlIl
(p, total including s); a specified numl,,'r 1'1'
those digits (s) may be ri ght of the ck cimal. Takcs 5 to
17 bytes to store, depending on the precision desired:
°
decimal and nUllleri c are synony mous
REAL
--
To make syntax simpler to write, the following notations are used in syntax descriptions:
ALL CAPS
DECIMAL[(P[ ,s])
or
NUMERIC[(p[,s])
+~_~:-=-~_":"""
TEXT
NTEXT
Stores approximate numerics " ith a range Ilf
3.40E + 3li through - LI RE - 3X, 0 and LI XI' - JX
through 3.40E t 3X in -I b~tes with 7 digits ofpreci
sion in scientific notation; Same as f1oat(24)
Stores approximate numerics with a range of - 1.79F
308 through -2.23 E - 308, 0 and 2.23 1: -3 (J~ through
1.79E + 308 in either 4 or 8 bytes \I itl! 7 (if II <-2-1) or
15 (if25 < II <~53) digits of precision in scicnti fic
notation; N defaults to 53
Designed to store currency-rdatcd \ alues \\Ith a
range of - 2 14,74X .364X through f 214,74H.3647 and
accuracy to 4 digits to the right of the decimal in -I 0) tes
Designed to store CUlTcIH.:y-rclatcd val lies
with a range of -922,337.203,685.-177 .5808
through 922,337,203,685,477 .5807 and accUI:lcy to
Fixed length text up t" II chameter> in same number til'
bytes. N defaults to I; max is S,O()O
Fixed length te.st up to 11 characters in twice tile nllmber of
bytes (2 byes/character [Unicode]) . N dcl~ul ts to I;
max is 4.000
Variable length text up to II characters in aetll.11 numoer
of bytes (on ly con,urnes as much space as d:lta entered).
N defaults to I: max is X,(J()O
Variable length le., t up to 4.000 charact ers in t\\ icc the
act ual number of bytes (only wnsul1lcs as much ' I,ace
as twi ce the number of characters e nl ered [Unicode]).
N defaults to I ; max is 4,000
Up ttl 2 (iB of text; takes 16 bytes in the record plus
the number "f .:haracters entered
Up to I Gf3 "flext ; takes 16 bytes in the record plu s
twice the number of characters Cl1t .;r.;d (UnicoJc )
fixed length binary data up 10 II bytes ' 4 b}tes I)\er
head, \' defaults to I; rna, i, X,lMIO
Variable lenglh binary clata up tl) II bytes 4 bytcs
0, crhcad ; space used i, actu"1 number o f bytes of data
(only consullles a, Illuch space a, (bta entercd) 4
bytes . N dcfaull s to I: ma ' is ~, O(J()
Variable length binary data up to :! ( ; B ' 16 b~ tes (l\ cr
head; SP'1<'': used i, actual number of b} tes of data
(only consumes as much space ~" clata entered) 16 b) tes
Da tabases contin ued:
.....- - - - - - - ....- - - . : . . . - - - - - - - - - - - - - - - - - - - - -....
CURSOR
U;ed with cursors; beyond the scope of this guidc
SQL_ VARIANT
Any data type (type determined when data entered) except text.
ntext, image, timestamp, and sql_ variant
TABLE
Stores table for latcr usc; beyond the scope of this guide
TIMESTAMP
Auto generated binary number guaranteed to
or ROWVERSION
be unique within the database; often used to version stamp a row;
requires 8 bytes; rowversion is the preferred word as timestamp
may be redefined to align it with the ANSI definition in the
futurc: max I of these per table
UN IQUE IDENTIFIER A GUIO (Globally Unique 10); values are usually c reated with
the NEWID function. Designed to be globa lly unique across all
servers a nd databases; requ ires 16 bytes
When multiple operators are specified together, the order
they will be processed is as 1,)1 lows:
I. + (Positive). _ (Negative), _
2. *, I, 0;',
3. + (Addition), (+ Concatenation), _ (Subtraction), &
4. = , > , <, >= , <= , <>, '=, I> , 1<
5.
A,
I
6. NOT
7. AND
Always use parenthesis
instead of the default Order "f
Operation,,' to minimize issues
between database vendors
and versions and to make it
easier to read and
understand code
8. ALL, ANY, BETWEEN, IN, LIKE, OR, SOM E
Any criteria on the same level (numerical level) arc processed left to right: this order ca n be
changed by using parenthesis
Null
N U LL values in a database are , by definition, not defined
Represent s the lack of data in a field
Thi s is not the same as an empty string ("") in a character field or a 0 in a numcric one;
it can be tested for by comparing with NULL (or the reverse condition NOT NULL)
Use it \vhen data is unava ilable, such as someone without a phone number or email address
Do not test to see if a value ~ NULL, rather the syntax is "IS NULL'; null values in numeric
field s arc igno red when calculations are per/(Jrtlled on that field (such as SUM or AVG)
The CAST statement (not availabl e in all products) is part of the SQL-99 standard and
changes the [ormat of the data from one datatype to another
Data may be lost in conversion if the destination format is smaller andl or less precise
than the source datatype: the syntax is CAST( expres.liul1 AS "a/aIF/N )
- Microsoft SQL Server 2000" al so has a CONVERT function that gives greater
control over thc lormat ofthc output (ex. changing date [ormats)
DELIMITERS I OPERATORS
Math
+
U;{)
Comparison
Operators
<> or !=
<
<=
!<
>
>=
!>
String
+
%
Addition (can also be used with dates)
Subtraction (can al so be used with dates)
Multiplication
Division
Mooula - the integer remainder in division (ex.7'Y.,3= 1) [not std.]
Equality (exactly the same)
Inequality
Less than
Less tha n or equa l
Not less than (Not ANSI standard)
Greater tha n
Greater than or equal
Not greater tha n (Not ANSI standa rd)
Concatenation (adding one string to the end of another)
Wildcard (0 or more characters; similar to * in many
Microsoft Windov.rs k applications)
or
Variab les
@
@@
Comment~
1*
'/
M isc.
()
Bitwise'
(Only works
with various
integer and
binary
datatypes )
Logical
Operators
(usually used
in WHERE
clauses)
&
I
ALL
A ND
ANY,
SOME
True if all comparisons specified are TRU E
True if both comparisons specified are TRU E
Truc if any of the comparisons specified are TRUE
BETWEEN
True if the operand is within the range specified
(inclusive orthe start and end values in the ran ge)
True if any rows are returned by the subquery
True if the operand matchcs any of the values in a
comma- se parated list
Truc if the operand matches a pattern specified
(usually with the % operator)
Reverses the result of any other Bool ean test (ex. NOT
IN or NOT LIKE)
True if either or both comparisons specificd are TRUE
EXISTS
IN
LIKE
NOT
OR
, Microsoft
Character string delimiters (enclose all literal text in one
of these quote formats)
Local variable
Glohal va riable
Single line commcnt (may be uscd mid-line; until end oflinc )
Start of multi-line cOlllment: end with *1
End of multi-line comment; start with /'
Controls order of operations and delimits expressions
Used when specifying ranges, (ex. with IN operator)
Separates items in a list
Separates parts of a fully qualified name (ex. data
basc .owner.objcct)
AND
OR
XOR (exclusive OR)
NOT
Data Definition Language (DOL) is thc syntax used to create, modily an d delete (or drop in
SQL wording) databases, tables, indexes. views, and other objects
Database: Overview
A collection of related objects designed to wo rk together
Generally, a ll logically related information should be stored in a s ing le database and the tables
within should be related, though access to data in other databa ses and re lation ships betll ce n
tables in different databases can be created
Database: Create
CREATE DATABAS E d£lWhase_l/lIlIIe
[ON [
[ . . . . nJ J J [LOG ON
{ hlespec
[, ... nJ} J
~
([ NAME = l ogical_f ile_ name, J FILENAME = ' os_f ile_name ' [, SIZE = sizeJ
[ , MAXSIZE = {max_s ize I UNLIMITED} J [, FILEGROwrH = growth_incrementJ)
Creates a new database with det'llIlt syste m objects
- Depends on the database vendor and version
- In SQL Server, it beg in s as a copy of the m",leI database
Physi cal placeme nt orthc components is specified with the ON and LOG ON <'ptions
- Various objects (ex. tables) can be placed in different files for be tte r perl"nnanec or to util ize
additional space
- Initi a l size of each component s pecified with the SIZE option
- MAXSIZ E can be either the maximum size to which it will grow (aftcr which no nell data
may be added) or UNLI MITED (default) to usc all fII'ailable di s!.. space as needcd
- SIZE and MAXSIZE may be specified in KB, MB , GB , or TB, with a default o f MB if
values are specified
- FILEGROWTH can be specified in abs"lute un its (KB or MB ) o r as a percentage ( %) of the
current file size
Database: Alter
ALTER DATABASE d a t abase {ADD FILE
~ [ , ... nJ
[, ...n J
REMOVE FILE logi ca l_ file_J1ame
<.~.¥w",
I MODIFY NAME = n e w_db n a me
(NAME
log ica l _fi.Ie_name
[,
NEWNAME
FILENAME = ' os_ f i l e_ name'] [ , SIZE = sizeJ
I UNLIMITED)]
[ , FILEGROWTH = gro wth_increment ])
ADD LOG FILE
MODIFY FILE
new_logical_nameJ
[,
[ , MAXSIZE = {max_size
Used to add, change. or remove file components (da tabase andl or log)
- Only empty files may be removed.
Ira new size is specified for an existing file . it mu st be la rge r than the ClIITl'1ll s iL~
Can also be lIsed to mOdify the database name and to set variolls databasc- Ic\c l opli\)l1s
Database: Drop
DROP DATABASE database_name [ , ... nJ
Removes a n unneeded database ; a ll objects and data within it arc also de le ted and the free space
returned to the OS
- Can not bc undone
System databases and open databases (those with any c,JIlncctions to the m; can't be dro pped
A table is the basic unit of data storage in a database
Table design is well beyond the scope of this guide
Coilimn conslrainl is a common option among many statements, so it is listed before table
related statements
See a lso the list of data types and their definitions on pages Iand 2
[CONSTRAINT constra2'nCnameJ {[NULL I NOT NULLJ
[{PRIMARY KEY
UNIQUE}
[CLUSTERED
I NONCLUSTEREDJ
[WITH
FILLFACTO R
fillfactorJ J I [ [FOREIGN KEY ] REFE RENCES ref_table [(ref_ column) J
[ON UP DATE {CASCADE I NO
[ON DELETE {CASCADE I NO ACTION}]
ACTION} J J I CHECK (l ogical_expression )}
I
NOT NULL forbids leaving the field blank
An index may be specified as follows:
2
PRIMARY KEY: Uniquely idcntifies a row in the table;
no 2 rows can have the same primary key values; may be
composed of multiple ficlds
- Maximum of I per table; most tables should have I
- UNIQUE: Duplicak values arc not allowed in the field(s)
specified; any number may be created
- CLUSTERED : Keeps the data physically sorted; max. of
I per table
NONCLUSTERED: Index is sorted but data isn't; may
have multiple indices of this type (default type)
- F I L L F AC T OR: Spccifies how full the index pagcs arc
at creation (varies as data is added and deleted); valuc
is a percentage Irom 0 - 100 (0 is default - system
creates at an optimal level for most tables, leaving
space for I entry on each page); small values can be
specified when a lot of new data is expccted; 100
should only be uscd for read-only da ta
FOREI GN KEY: Used to specify that valid values for this
field arc dcfined in another table; attcmpts to insert invalid
data wi II fai I
- The table and ficJd(s) refe renced must be part of either a
primary key or a uniquc constraint
- Used for referential integrity (ex. invoices must reference
an existing customer)
- Optionally. changes made to the source (updates andlor
deletes) may be made to this table as well (ex. changing a
customer ID in the customers table changes all references
to it in the invoices table as well) if the ON DELETE
CASCADE andlor ON UPDATE CASCADE options are
specified; if NO ACTIO N is specified and any rows in
this table reference an item that has been updated or
deleted in the source table, the update or delete will fail
preventing orphaned rccords (ex. invoices without
customers)
CHECK: Used to specify a logical test that validates thc data
based on values listed [ex. DayOfWeek IN ( 'Sunday,
·Mond ay', 'Tuesday'. 'wednesday', ·Thursday',
· Friday ·. 'Saturday ' )]. Invalid data will cause the
INS ERT or UP DAT E to fail. Can only reference data in the
same column
CREATE TABLE [da tabase_ name,] [owner.]
table_ nd11/.e ({
1
: : ~ [CONSTRAINT
c onstraincname] ) [ , ."n ] )
{column name data_ type} [[DEFAULT
con stan t _ expr e s s .ion]
1
[ I DENTITY [(s e ed,
]
2·ncrement) ]]] [
[ .. . n]
[CONS TRAINT c Ollstra2·nCllame] {[ {PRIMARY
KEY i UNI QUE} [CLUSTERED 1 NONCLUSTERED]
{ ( c o l umn [ASC i DESC ] [ , •.. n] »
[WITH
FILLFACTOR ~ f i l l factor]] 1 FOREIGN KEY
[ ( c ol umn [ , . . . n ] )] REFERENCES ref_ table
[ ( r ef c ol umn [, .. . n] )] [ON DELETE {CASCADE
1 NO ACTION}]
[ ON UPDATE {CASCADE 1 NO
ACTI ON} ] 1 CHECK (sea rch_condi Uons) }
• Creates a new table with the specified name; if not
specified, database defaults to the currcnt database and
owner defaults to thc user executing the statement
- Best practice: Specify DBO as the owner of all objects to
simplify permissions, object access, etc.
• DEFAULT: Used to specify a default value for the field (ex.
·WA · if most customers live in Washington state) to make
data entry easier
• ID ENTITY: Used to automatically create values in the
field, starting at seed and increasing by incremenl; often
used with Prill/ulT Kel ·s li)r values that need to be unique,
but whcre the value is not necessarily important (ex. a
cu,tomer number)
• Table constraints arc very similar to column constraints. but
may reference multiple fields within the table (not just the
column relerenced)
ALTER TABLE
tab1 e
{[ALTER COLUMN
c ol umn_name
~
,
....
{new_ d ata_ type
{ADD 1 DROP}} ]
[NULL
1
ADD
1
{ [
NOT NULL]
]}
[, ... n]
1
1
[WITH CHECK 1 WIT H NOCHECK] ADD
{
e
} [.
nl 1 DROP
{[CONSTRAINT] constrainCname 1 COLUMN
column} [, .. . n] 1 {CHECK 1 NOCHECK}
CONST RAINT {AL L 1 constra2·n t_n ame
0
[ " , , n} }}
••
{column_name data_type} [[DEFAULT
constant_expressjon]
[WITH VALUES]
[IDENT I'l' Y [( seed, increment) ]]]
[
]
[ ... n]
1
[CONSTRAINT constra2·nCname] {[ {PRIMARY KEY
UNIQUE}
[CLUSTERED 1 NONCLUSTERED]
{ (col umn [, ... n] )} [WITH FILLFACTOR ~
fi11factor]] 1 FOREIGN KEY [( column
[ , ... n])]
REFERENCES ref_table
[(ref_column [ .... n])] [ON DELETE {CASCADE
1
NO ACTION}] [ON UPDATE {CASCADE 1 NO
ACTION}] 1 DEFAULT constancexpression [FOR
co1unm] [WITH VALUES] 1 CHECK
(search_condi t .ions) }
1
• Used to add, change, or delete fields in a table
• Any fields deleted lose all data associated with them; the
action can't be undonc
• If WITH CHECK is specified, all existing data will bc
checkcd against existing and newly specified constraints; if
any data docs not meet those constraints, the statement will
fail; use WITH NOCHECK to apply the constraint changes
only to new data; uselul if data will be cleaned up latcr
WITH VALUES will place the specified delault value in all
existing rows for the newly added field: if WITH VAL UES
is not specified and NOT NULL is specified, the statcment
will lail
Table: Drop
- Can COllvert vice versa
- Assumcs no data corruption in the index (can c heck
for corruption with DBCC CHECKDB in SQL 200U)
SORT I N T EM P DB: Insteau o f buildinc the indn in
the d~;t;lba-;;;' (requiring space and pOlentia ll Y reuueing
performance as the disk hcad s muve from data to fre,'
space and back: thc del'\lIlt option). builds the indC\ in
the TelllpDB database: ifTcmpDB is located on diflerenl
physical disks, performance llJay be improved as the
index is written sequentiall y as il is built
Index: Drop
DROP INDEX t able . ind ex 1 v2·ew. index [, .. . n]
• Deletes an index. fj·ceing it s spacc: docs NOT delele dala
Can 't be lIsed on indicC's on system tables
- C an·t be used on Primary Key, and Unique Indice,
(rclllove the constrainl \\ ith ALT ER TAB LE and the
index will autollJatieall y be removed)
View: Overview
A View is a prede fined query thai is often re ferenc ed
(ex. Views rnay be created for each region that sI10\\ S
only that region·s data). Often used :
For sec urity reasons 10 restrict access to ce rtai n
records and/ or fields (ex . Illay return o nl y e mployee
name and pholle number from a tabl e that also
contains soc ial security Ilumbers and wage in fo)
- To silllplify "·equemly used complex log ic
- To a sse mble data from Illultipl e tabl es o r even
multiple databases
DROP TABLE table_"anre
Removes an unneeded tablc; all data within it is also deleted • • • • • • • •~~• • • • • • • • •
- Cannot be undone
~
System tables and those referenced by a FOREIGN KEY
constraint can·t be dropped
Data Manipulation La nguage (OM L) is the synta x used
to find, add. changc. and de lcle d ~lIa in tabl es and views:
Index: Overview
fi nd ing information is referred to as queryi ng
• Makes data access morc efficient by not lorcing the
databasc engine to read all the values to find those that
match a specified critcria
• Tips:
- The more selective an index is (ie. the fewer rows
returned). the more useful it is and the greater the
performance increase generated
- Useful on fields specified in a foreign key constraint and
with joins between tables
- Automatically created lor Prill/ar\! Key and Ullique
constraints.
- May not be useful with small tables (a fi.dl scan of the
table may be faster)
- Many indices will slow INSERT, UPDATE, and DELETE
statements. as each may lIeed to be updated. but will
improve the pert(mmll1ce of SELECT statements (ie. both
factors must be considered)
Clause: Overview
Index: Create
CREATE [UNIQUE] [CLUSTERED 1
NONCLUSTERED] INDEX 2·ndex_ name ON {table
1
view} (column [ASC 1 DESC ] [,00 .n])
[WITH "- '"l
:p_~ ~n~ [, o. on]l
• Used by all DM L s tatemc nt s
• Speci fy data sources. criteria , etc .
Clause: Search Conditions
{[NOT]
~'\te.,. }
{ ~j,j::at.(!.,.
}l}
[,.0
[{AND
lOR}
[NOT]
on]
..
{~ 1 <> 1 ! ~ I> I> ~ 1
1 < I <~ 1 ! <} expression 1
strin g_ expression [NOT] LIKE
string_ expression
1 expression [NOT]
BETWEEN expression "'~D expression 1
expression IS [NOT] NULL 1 expression
[NOT] IN (expresscion [. .. . n])}
{expression
!>
NOT: Negates the specified search condilion: ge ne ral ly
s10\vL'r than chang ing to the opposite criteri a bcc ~lU se
every field Illu st be c heel-ed for th e e r;l er ia a nd
matching rowS Illust bc discarded
• Separate Illultiple predicates \\ ilh cit he r AND or OR
• The results
a predicale must be e ithcr Tru e or Faisc
• Ex press ion can be a fi e ld. va riable. func tion, conSla nt o r
a s ubqucry (a qucry within ;lIlOlhcr qucry )
L IKE is uscd wi th charact e r cial" iab strin!!
expressions) only a nd is lIsc:d 10 matc h :1 pallcrn ll sing
wilcicard characters, as follows :
- %: Any string of" () or more c haraCi crs
- _: Any single c haractcr
- [] : Any charactcr wilhin a ran gc spec if ied (e.\ . [d - h]
matchcs d c , f. g. & h) or a g ive n gro up of c harac lcrs
(ex. [dh ] matchcs e ithc r d or h)
- [ 1\] :A ny character no t \\'ilhin a ran ge spec ificd (e, .
[ I\ d - h] matches a. b. & c as wc ll as I Ihrou(!h I) or
a g ive n group of characters (ex. [I\dh) l;lat c h c~
anything exce pt d or h)
- If comparisons arc made with ASCII ( ic . no n
Unicode) data (i e . every thing is ASCII type). tra iling
spaces ar~ ignored, whereas i r any argume nt uses
Unicodc dala. all argumc nl s arc convcrted to Unicode
or
{FILL F ACTOR ~ f i l l factor 1 PAD_INDEX
IGNORE_ DUP_ KEY I DROP_EXISTING 1
SORT_IN_TEMPDB}
1
Creates an index; most options described in the Table
scction undcr Column Constraint
PAD_ I NDEX : Fills the non-leaf index pages as full as the
leaf pages (as specified in the FILLFACTOR parametcr); if
not specified, room for I entry is left unused on the non-leaf
pages
IGNORE_ DUP_ KEY : Ignores rows added or modified by
INSERT and UPD)\_TE statements that violate Unique
constraints (ex. if inserting 50 rows and 5 violate the Unique
constraint. 45 row s will be added); ifnot specified, an error
is returned (ex. with the previous example. 0 rOWs would be
added)
DROP_EXISTI NG : Deletes an existing index with the
same name and recreates it; faster than a DROP and a
CREATE if a clustered index is involved. as all non
clustered inui ees would need to rebuild twice - once
without referencing the old cluster key and once
referencing the ncw key
- If name and colull1ns indexed don't change (i.e.
rebuilding to change the FILLFACTOR or to reduce
space used) , the data is n't resorted
- Can't convert a clustered index to a non-clustercd index
Or vice versa
3
and trailing spaces arc significant
See also the Delimilers I O perators scction (page 2 ) for 1Il0re
information on availabk: operators rind their meanings
Clause :VVhe~
re
~______________________
[WHERE < o~ Ich co d~tion> ]
Thc critcria uscd with any DML stal ement is specified
using WHER E ; without it , all row s arc ret urned (or
c hanged or del eted, depending on the s tat emelll): most
DML sta te mcnts will use WH E RE
DML continued:
Clause: From .. Join
[FROM {<table source> }
•
'C .......
t;..
WITH CUBE: Similar to WITH ROLLUP, but a summa ry
row is added for every possible combination of va lues,
thus returning the same number of rows, regardless of the
order specified in the GROUP BY clause; the g reater the
number of field s specified in the GROUP BY clause, the
greater the number of summary rows (exponentia lly)
Note that distinct aggregates (ex. SUM(DISTINCT
information on DI STINCT
Use the GROUPING function (in the SELECT li st) to
determine if a value in a field is a summary value or an
actual NULL value
[, ••• n))
M Y .....
tab1e name [[AS) tab1e a1ias)
vi e ,,,,-name [[A S] table_a1ias]
<joined table>
<table~urcg> ON <'<ggKcll coMiliJ:m>
<j oine.9 jg.l:ll.e>
[ ±NNER I { {LEFT
[OUTER]}] JOIN
I
RIGHT
I
Clause: Having
FULL}
Lists the source table(s} for the DML statement
Table_alias: Used to name the table for purposes of the DML
statement, usually as shorthand (ex. Order s AS 0; then all
references to the Orders table would be 0 ., not Ord ers .)
Note that the data sourcc can be a table or a view
J oin _type: Describes which data should be matchcd (based
on the search condition specified in the joine',-lable
sl'lIlax, /lot the WHERE clause); joins arc always specified a
pair of tables at a time ; options arc as follows:
INNER: Includes values that have matching values in
both tables ; the default type
OUT ER: Includes all values, with NULL for values that
don 't havc a matching value in the other table; LEFT,
RI GHT, and FULL are types of OUTER joins
LEFT: All the records in the 1st table specified and any
matching records from the 2nd table; any data that is only
in the 2nd table is not displayed and any value in the 1st
tabl e that doesn't have a match in the 2nd will vicJd NULL
RI GHT: All the records in the 2nd table specifled and any
matching records from the I st table; any data that is only
in the I st table is not di splayed and any value in the 2nd
table that doesn 't have a match in the 1st will yield NULL
FULL : All the records tram both tables specified with
NULL for values that don't have a matching value in the
other table
Scach_condi tio n: As part of a JOIN, it indicates what
the relationship between the 2 tabl es is; follows the
syntax described above (ex. A table with Orders has a
field with order numbers and a table with the detail lines
far each order needs to reference those order numbers;
the syntax is : FROM OrderDe tai l s JOIN Orders
on Orders . Orde rNumbe r =
OrderDeta2·ls . OrderNumber resulting in an
I NNER JOIN that matches an order with all the detail
lines for it)
Clause: Group By
[ GROUP
[ , .. . n]
BY
[ALL]
[WITH {CUBE
Clause: Order By
[ORDER BY {order_.by_ expression
DESC)} [, .. . n))
[ASe
I
Without an ORDER BY clause, the order of the result set
is /lever guarantee(~ even with indexes; if you need data in
a certain order, always include ORDER BY
ASC: Sort the order_by_expressio/l in ascending (A - Z)
order; default if neither ASC or DESC is specified
DESe: Sort the onler_hyjxpression in descending (Z - A) order
Clause: Into
qroup_ by_expression
I
Price: U.S.$4.95 / Can .$7.50
ISBN 157222916-0
~~llll~1 Uljllllllln~liIIIl
When used with SELECT. c reates a new table (ie.
new_table must not already ex ist) and copies the output o f
ROLL UP }]]
the se lection into it (instead of displaying the resuits)
When used with INSERT, adds data to an existing new
table (ie. /lew_table must already exist and have a table
structure compatible with the data being inserted) and
places the data in it; impli ed ifnot explicitly specified; to
duplicate the effect of a SELECT." INTO statement with
a n existing table, use INSERT". SELECT
Can 't be used with COMPUTE in the SELECT statement
Requires CREATE TABLE permi ss ion
To copy the sc hema (structure) only with no data or an
ex isting data, spec ify a False WHERE condition.{ex.
WH ERE 1=0)
Clause: Top
TOP n
[P ERCENT]
Uses clauses previously defined
Used to add (INSERT) , retri eve (SELECT), change
(UPDATE) and re move (DELETE and TRUN CAT E
TABLE) data
Statement: Select
SELECT [ALL I DISTINCT) [ {TOP integer
TOP integer PERCENT } [WITH TIE S]] {*
I
I
{tab1e_name I view_ name I tab1e_a1ias}. *
I
{co1wnn_ name
I
expression}
[[AS)
co1wnn_ a1ias) I co1umlLa1ia s = express2'on}
[ , ... n ] [INTO new_tab l e] FROM tab1e_ source
[WHERE search_ condition)
[GROUP BY qroup_ by_ expression)
[HAVING s earch_condition)
[ORDER BY order_ expression [ASC I DESC))
Select state ment cla uscs have all been described abo\ c, but
must be in thi s o rder - otherwise it wo n't funct ion
An asterisk (*) means return all ficJds in the table. view, or
alias ofa table or view (which is defined in the FROM clause)
Columns or express ion s can be aliascd us in g AS
Expressioll can include fllllctions, columns. a nd o r lite ra ls
Statement: Insert
[INTO) {tab1 e_ name I view_ name }
{ [ (co1wnn_ 1ist) )
{VALUES ({ DEFAULT I NULL I expres sion )
[, ... n ] } ) I derived_ tab1e I
execute_ statement } I DEFAULT VALUES
INSERT
A derived tablc can be created by us ing a SELECT
state me nt to generate the data needed by the INSERT
state me nt
In serting data using an execute state me nt all o\\'s data
generated from a Stored Proced ure to be sto red in a table
All fields in the tabl e must be e ither ex pli c itly g iven
values, allowed to be NULL . or ha ve a default va lue
specified
If a column_list is not spec ified, data will be entered in the
order the fields are defined in the table
For each field chosen in the column_ list, a va luc Illust be
indi cated using the VALUE S clau se: the key words
DEFAULT and NULL can be entered in the fie ld's
default va lue or NULL, respectively.
Statement: Update
[ INTO new_table)
Gro ups records together, typically so records that meet
within the group are summarized by the other fields in a
Select (ex. SELECT Cus tNwn, SUM (InvoiceTota1)
FROM Orde rs GROUP BY CustNumwili give the total
sales for each customer)
When GROUP BY is used, all fields in the SELECT
statement must be eithe r li sted in the GROUP BY clause or
be agg regate function s (SUM, COUN'r , etc.).
If a field is aliased (ex. SELECT dala AS inf;'), the ac tual
field name, not the alias must be used
.
Multiple field s can be gro uped, for example to group by
customer and within custom e r by da te (ex. GROUP BY
cus tOnier, da tel and multiple aggregate values may be
returned l'or each (ex. SUM (Invo i ceTotal ),
AVG(Invo2'ce Tot al ) to get the average a nd total
amollnt of each customer's invoices
• The WHERE clause condition(s) (if any) are applied before
the data is grouped
• ALL : Returns informa ti o n on all records, even if some of
the groups don 't meet the required conditions (defined in
the WHERE [but not the HAVING] clause ) with NULL for
the agg regate val ues
• WITH ROLLUP : Adds summ ary rows to the result set; one
set of summary rows is provided l'or each unique set of
va lues (ex. GROUP BY customer,
date would
produce a summary vallie for each customer a nd within
each customer far eac h date the customer had an order):
BY
cha nging the order of the fields in the GROUP
state me nt may generate a di/lercnt number of rows (ex. in
the previous example, data would be summari zed far each
date, a nd within each date by customer if the GROUP BY
list were reversed )
9
Similar to the WHERE clause, HAVING restricts the output
to the conditions specified, but it is often used with
GROUP BY to limit the groups returned (ex. SELECT
Title, COUNT (QtySold) FROM Or de r Details
WHERE Title LIKE
' [ f ' m]%'
GROUP BY
Title HAVING COUNT (QtySo l d)
>
100 will
return the total sales of each book that starts with F-M and
has sold at least 100 copies), but can be used without
GROUP BY, in which case it acts just like WHERE
WHERE can't be used to filter grouped data (as it applies
before the data is grouped) but HAVI NG can , and may also
refe rence agg regate data
HAVING overrides GROU P BY ALL, so the records that
don't meet the HAVING condition(s) specified do not
appear in the output
It is generally more efficient to filter with WHERE
whenever possible and use HAVING only when necessary;
less data to be grouped and then late r discarded by the
HAVING condition(s)
Unlike most of the other clauses listed here, HAVING can
only be used with SELECT
Statement: Overview
UPDATE {tab1e_ name
view_ name}
SET {co1umn_ name = { expression
DEFAULT I NULL} I @variab1e =
expression}
[, .. . n)
«[FROM {< tabl e _ sou rce> } [ , ... n]]
[WHERE
UPDATE can be used c hange all th e record s that meet the
WHERE clause (if specified). chang ing the , alues in Ihe
spec ified fields as nCGCssary. often wi th nn expression (c.,\ .
SET Pri c e = Pri c e * 1 . 1 to increase th e price o f
everything by 10'%)
T he va lue orany fi e ld may also be set to the field 's default
va lue Or NULL
• UPDATE may also he used to set a va riable to any va lue
desired (though olien SET [in Microsol[T" SQL] is used )
[W ITH TIES ]
Statement: Delete
To be mea ningful, the SELECT statement requires an
ORDER BY clause to get the first /I rows; sort in ascending
order to get the smallest values or descending order to get
the largest
PERCENT : If specified, n% of the total number of rows
will be returned, otherwise n rows will be returned; if
spec ified, n must be between 0 and 100, otherwise specify
an integer (0 - 4294967295)
• WI TH T I ES: If specified, will return all data that matches
the cutoff rOw crite ria, instead of arbitrarily stopping at the
specified number (or percent) of rows (ex. if there arc 10
sales of $100,000 (and more, smaller ones). SELECT
TOP 5 SalesPer s on ,
Amoun t
ORDER
BY
Amount
DESC
would arbitrarily return 5 of the
$100,000 sa les, whereas if WITH TIES is specified, all
10 records would be returned)
DELETE
[WHERE
[FROM) {<table source>}
{<search_ condition>]
[ ,. , . n)
Removes dala from a tabl e (each re moved record is logged,
allowing it to be undone, at the cost of lo nger proceSS ing
time and more disk space)
Statement: Truncate Table
TRUNCATE TABLE tab l e_nafne
Deletes all data in the table, w itho ut loggi ng all c ha nges,
sav ing di sk space (ie. jLlst the deallOc'ltion o f many pages
is logged ) a nd makin g it much faster
Net res ult is same as a DELETE w ithoul a WHERE clause
Author: John Hales
This guide is an introduction to creating, managing and deleting key SOL objects (such as tables, indexes
and databases) and retrieving data from tables. It covers the ANSI SOL·99 standard as well as Microsoft
SOL Server 2000. Other databases are similar, but may not be identical.
/\11 ri"hl~ n~~ncu. ,-" pan hfli1 i, 1", hl ir~ lI "n I1Hiy he rcl'w
r,:!riC\ dl )}'s!Crn. ""t h" ul "r;l1~ n p ermiSSion frl'o!l) the Pllhl i'h~1
( 10()~
lla rCh;, n_ ,l l1c . ()7115
hUrldreds ~ f titl es at
qUlcKsluaY·COm
6
4
LLJL
o
