?? apa.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML><HEAD> <TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Appendix A -- Glossary of Common SQL Statements</TITLE></HEAD><BODY TEXT="#000000" BGCOLOR="#FFFFFF"><CENTER><H1><IMG SRC="../buttonart/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR><FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1></CENTER><CENTER><P><A HREF="../wk3rev/wk3rev.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128"HEIGHT="28" ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../apb/apb.htm"><IMGSRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <HR></CENTER><CENTER><H1><FONT COLOR="#000077">- Appendix A -<BR>Glossary of Common SQL Statements</FONT></H1></CENTER><H3><FONT COLOR="#000077"><BR>ALTER DATABASE</FONT></H3><PRE><FONT COLOR="#0066FF">ALTER DATABASE database_name;</FONT></PRE><P><TT>ALTER DATABASE</TT> command changes the size or settings of a database. Itssyntax varies widely among different database systems.<H3><FONT COLOR="#000077">ALTER USER</FONT></H3><PRE><FONT COLOR="#0066FF">ALTER USER user</FONT></PRE><P><TT>ALTER USER</TT> statement changes a user's system settings such as password.<H3><FONT COLOR="#000077">BEGIN TRANSACTION</FONT></H3><PRE><FONT COLOR="#0066FF">1> BEGIN TRANSACTION transaction_name2> transaction type3> if exists4> begin</FONT></PRE><P><TT>BEGIN TRANSACTION</TT> statement signifies the beginning of a user transaction.A transaction ends when it is either committed (see <TT>COMMIT TRANSACTION</TT>)or canceled (see <TT>ROLLBACK TRANSACTION</TT>). A transaction is a logical unitof work.<H3><FONT COLOR="#000077">CLOSE CURSOR</FONT></H3><PRE><FONT COLOR="#0066FF">close cursor_name</FONT></PRE><P><TT>CLOSE cursor_name</TT> statement closes the cursor and clears it of data.To completely remove the cursor, use the <TT>DEALLOCATE CURSOR</TT> statement.<H3><FONT COLOR="#000077">COMMIT TRANSACTION</FONT></H3><PRE><FONT COLOR="#0066FF">SQL> COMMIT;</FONT></PRE><P><TT>COMMIT TRANSACTION</TT> statement saves all work begun since the beginningof the transaction (since the <TT>BEGIN TRANSACTION</TT> statement was executed).<H3><FONT COLOR="#000077">CREATE DATABASE</FONT></H3><PRE><FONT COLOR="#0066FF">SQL> CREATE DATABASE database_name;</FONT></PRE><P><TT>database_name</TT> creates a new database. Many different options can be supplied,such as the device on which to create the database and the size of the initial database.<H3><FONT COLOR="#000077">CREATE INDEX</FONT></H3><PRE><FONT COLOR="#0066FF">CREATE INDEX index_nameON table_name(column_name1, [column_name2], ...);</FONT></PRE><P>the contents of the indexed field(s).<H3><FONT COLOR="#000077">CREATE PROCEDURE</FONT></H3><PRE><FONT COLOR="#0066FF">create procedure procedure_name [[(]@parameter_name datatype [(length) | (precision [, scale]) [= default][output] [, @parameter_name datatype [(length) | (precision [, scale]) [= default][output]]...[)]] [with recompile] as SQL_statements</FONT></PRE><P><TT>CREATE PROCEDURE</TT> statement creates a new stored procedure in the database.This stored procedure can consist of SQL statements and can then be executed usingthe <TT>EXECUTE</TT> command. Stored procedures support input and output parameterspassing and can return an integer value for status checking.<H3><FONT COLOR="#000077">CREATE TABLE</FONT></H3><PRE><FONT COLOR="#0066FF">CREATE TABLE table_name( field1 datatype [ NOT NULL ], field2 datatype [ NOT NULL ], field3 datatype [ NOT NULL ]...)</FONT></PRE><P><TT>CREATE TABLE</TT> statement creates a new table within a database. Each optionalfield is provided with a name and data type for creation within that table.<H3><FONT COLOR="#000077">CREATE TRIGGER</FONT></H3><PRE><FONT COLOR="#0066FF">create trigger trigger_name on table_name for {insert, update, delete} as SQL_Statements</FONT></PRE><P><TT>CREATE TRIGGER</TT> statement creates a trigger object in the database thatwill execute its SQL statements when its corresponding table is modified throughan <TT>INSERT</TT>, <TT>UPDATE</TT>, or <TT>DELETE</TT>. Triggers can also call storedprocedures to execute complex tasks.<H3><FONT COLOR="#000077">CREATE USER</FONT></H3><PRE><FONT COLOR="#0066FF">CREATE USER user</FONT></PRE><P><TT>CREATE USER</TT> statement creates a new user account complete with user IDand password.<H3><FONT COLOR="#000077">CREATE VIEW</FONT></H3><PRE><FONT COLOR="#0066FF">CREATE VIEW <view_name> [(column1, column2...)] ASSELECT <table_name column_names>FROM <table_name></FONT></PRE><P>using the <TT>CREATE VIEW</TT> statement. After a view is created, it can be queriedand data within the view can be modified.<H3><FONT COLOR="#000077">DEALLOCATE CURSOR</FONT></H3><PRE><FONT COLOR="#0066FF">deallocate cursor cursor_name</FONT></PRE><P><TT>DEALLOCATE CURSOR</TT> statement completely removes the cursor from memoryand frees the name for use by another cursor. You should always close the cursorwith the <TT>CLOSE CURSOR</TT> statement before deallocating it.<H3><FONT COLOR="#000077">DECLARE CURSOR</FONT></H3><PRE><FONT COLOR="#0066FF">declare cursor_name cursor for select_statement</FONT></PRE><P><TT>DECLARE CURSOR</TT> statement creates a new cursor from the <TT>SELECT </TT>statementquery. The <TT>FETCH </TT>statement scrolls the cursor through the data until thevariables have been loaded. Then the cursor scrolls to the next record.<H3><FONT COLOR="#000077">DROP DATABASE</FONT></H3><PRE><FONT COLOR="#0066FF">DROP DATABASE database_name;</FONT></PRE><P><TT>DROP DATABASE</TT> statement completely deletes a database, including alldata and the database's physical structure on disk.<H3><FONT COLOR="#000077">DROP INDEX</FONT></H3><PRE><FONT COLOR="#0066FF">DROP INDEX index_name;</FONT></PRE><P><TT>DROP INDEX</TT> statement removes an index from a table.<H3><FONT COLOR="#000077">DROP PROCEDURE</FONT></H3><PRE><FONT COLOR="#0066FF">drop procedure procedure_name</FONT></PRE><P><TT>DROP PROCEDURE</TT> statement drops a stored procedure from the database;its function is similar to the <TT>DROP TABLE</TT> and <TT>DROP INDEX</TT> statements.<H3><FONT COLOR="#000077">DROP TABLE</FONT></H3><PRE><FONT COLOR="#0066FF">DROP TABLE table_name;</FONT></PRE><P><TT>DROP TABLE</TT> statement drops a table from a database.<H3><FONT COLOR="#000077">DROP TRIGGER</FONT></H3><PRE><FONT COLOR="#0066FF">DROP TRIGGER trigger_name</FONT></PRE><P><TT>DROP TRIGGER</TT> statement removes a trigger from a database.<H3><FONT COLOR="#000077">DROP VIEW</FONT></H3><PRE><FONT COLOR="#0066FF">DROP VIEW view_name;</FONT></PRE><P><TT>DROP VIEW</TT> statement removes a view from a database.<H3><FONT COLOR="#000077">EXECUTE</FONT></H3><PRE><FONT COLOR="#0066FF">execute [@return_status = ] procedure_name [[@parameter_name =] value | [@parameter_name =] @variable [output]...]]</FONT></PRE><P><TT>EXECUTE</TT> command runs a stored procedure and its associated SQL statements.Parameters can be passed to the stored procedure, and data can be returned in theseparameters if the <TT>output</TT> keyword is used.<H3><FONT COLOR="#000077">FETCH</FONT></H3><PRE><FONT COLOR="#0066FF">fetch cursor_name [into fetch_target_list]</FONT></PRE><P><TT>FETCH</TT> command loads the contents of the cursor's data into the providedprogram variables. After the variables have been loaded, the cursor scrolls to thenext record.<H3><FONT COLOR="#000077">FROM</FONT></H3><PRE><FONT COLOR="#0066FF">FROM <tableref> [, <tableref> ...]</FONT></PRE><P>FROM specifies which tables are used and/or joined.<H3><FONT COLOR="#000077">GRANT</FONT></H3><PRE><FONT COLOR="#0066FF">GRANT role TO user</FONT></PRE><P>or</P><PRE><FONT COLOR="#0066FF">GRANT system_privilege TO {user_name | role | PUBLIC}</FONT></PRE><P><TT>GRANT</TT> command grants a privilege or role to a user who has been createdusing the <TT>CREATE USER</TT> command.<H3><FONT COLOR="#000077">GROUP BY</FONT></H3><PRE><FONT COLOR="#0066FF">GROUP BY <col> [, <col> ...]</FONT></PRE><P><TT>GROUP BY</TT> statement groups all the rows with the same column value.<H3><FONT COLOR="#000077">HAVING</FONT></H3><PRE><FONT COLOR="#0066FF">HAVING <search_cond></FONT></PRE><P>HAVING is valid only with <TT>GROUP BY</TT> and limits the selection of groupsto those that satisfy the search condition.<H3><FONT COLOR="#000077">INTERSECT</FONT></H3><PRE><FONT COLOR="#0066FF">INTERSECT</FONT></PRE><P>INTERSECT returns all the common elements of two <TT>SELECT</TT> statements.<H3><FONT COLOR="#000077">ORDER BY</FONT></H3><PRE><FONT COLOR="#0066FF">ORDER BY <order_list></FONT></PRE><P><TT>ORDER BY</TT> statement orders the returned values by the specified column(s).<H3><FONT COLOR="#000077">ROLLBACK TRANSACTION</FONT></H3><P><TT>ROLLBACK TRANSACTION</TT> statement effectively cancels all work done withina transaction (since the <TT>BEGIN TRANSACTION</TT> statement was executed).<H3><FONT COLOR="#000077">REVOKE</FONT></H3><PRE><FONT COLOR="#0066FF">REVOKE role FROM user;</FONT></PRE><P>or</P><PRE><FONT COLOR="#0066FF">REVOKE {object_priv | ALL [PRIVILEGES]}[, {object_priv | ALL [PRIVILEGES]} ] ...ON [schema.]objectFROM {user | role | PUBLIC} [, {user | role | PUBLIC}] ...</FONT></PRE><P><TT>REVOKE</TT> command removes a database privilege from a user, whether it bea system privilege or a role.<H3><FONT COLOR="#000077">SELECT</FONT></H3><PRE><FONT COLOR="#0066FF">SELECT [DISTINCT | ALL]</FONT></PRE><P><TT>SELECT</TT> statement is the beginning of each data retrieval statement. Themodifier <TT>DISTINCT</TT> specifies unique values and prevents duplicates. <TT>ALL</TT>is the default and allows duplicates.<H3><FONT COLOR="#000077">SET TRANSACTION</FONT></H3><PRE><FONT COLOR="#0066FF">SQL> SET TRANSACTION (READ ONLY | USE ROLLBACK SEGMENT);</FONT></PRE><P><TT>SET TRANSACTION</TT> enables the user to specify when a transaction shouldbegin. The <TT>READ ONLY</TT> option locks a set of records until the transactionends to ensure that the data is not changed.<H3><FONT COLOR="#000077">UNION</FONT></H3><PRE><FONT COLOR="#0066FF">UNION</FONT></PRE><P><TT>UNION</TT> statement returns all the elements of two <TT>SELECT</TT> statements.<H3><FONT COLOR="#000077">WHERE</FONT></H3><PRE><FONT COLOR="#0066FF">WHERE <search_cond></FONT></PRE><P><TT>WHERE</TT> statement limits the rows retrieved to those meeting the searchcondition.<H3><FONT COLOR="#000077">*</FONT></H3><P>* gets all the columns of a particular table.</P><CENTER><P><HR><A HREF="../wk3rev/wk3rev.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../apb/apb.htm"><IMGSRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR><BR><BR><IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"BORDER="0"></P><P>© <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. Allrights reserved.</CENTER></BODY></HTML>?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -