SQL语句征集

luhongjun 2001-07-21 08:03:26

加精

在我们进行数据库程序设计的时候经常用到SQL语句,但一直没有找到关于这些语句完整的介绍,现征集经常用到的SQL语句(最好加简单的说明及例子).
1:查询语句(select)(嵌套查询)
2:数据更新语句(delete,update,insert)
3:修改结构(alter)(应该包括增加字段,删除字段,修改字段长度,修改字段类型等)
4:创建表(create)
5:删除表
6:创建视图(create view)
7:创建游标(create cursor)
8:创建索引(create index)
给分标准:
1:20
2:20
3:100
4:10
5:100
6:10
7:20
8:20

...全文

95 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

luhongjun 2001-07-21

打赏
举报

OK给分

guo 2001-07-21

打赏
举报

OK,
3.ALTER TABLE table
{ [ALTER COLUMN column_name
{ new_data_type [ (precision[, scale] ) ]
[ NULL | NOT NULL ]
| {ADD | DROP} ROWGUIDCOL
}
]
| ADD
{ [ <column_definition> ]
| column_name AS computed_column_expression
}[,...n]
| [WITH CHECK | WITH NOCHECK] ADD
{ <table_constraint> }[,...n]
| DROP
{ [CONSTRAINT] constraint_name
| COLUMN column
}[,...n]
| {CHECK | NOCHECK} CONSTRAINT
{ALL | constraint_name[,...n]}
| {ENABLE | DISABLE} TRIGGER
{ALL | trigger_name[,...n]}
}

<column_definition> ::= { column_name data_type }
[ [ DEFAULT constant_expression ]
| [ IDENTITY [(seed, increment ) [NOT FOR REPLICATION] ] ]
]
[ ROWGUIDCOL ]
[ <column_constraint>] [ ...n]

<column_constraint> ::= [CONSTRAINT constraint_name]
{
[ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[CLUSTERED | NONCLUSTERED]
[WITH FILLFACTOR = fillfactor]
[ON {filegroup | DEFAULT} ]]
]
| [ [FOREIGN KEY]
REFERENCES ref_table [(ref_column) ]
[NOT FOR REPLICATION]
]
| CHECK [NOT FOR REPLICATION]
(logical_expression)
}

<table_constraint> ::= [CONSTRAINT constraint_name]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED]
{ ( column[,...n] ) }
[ WITH FILLFACTOR = fillfactor]
[ON {filegroup | DEFAULT} ]
]
| FOREIGN KEY
[(column[,...n])]
REFERENCES ref_table [(ref_column[,...n])]
[NOT FOR REPLICATION]
| DEFAULT constant_expression
[FOR column]
| CHECK [NOT FOR REPLICATION]
(logical_expression)
}
------------------------------------
4.CREATE TABLE
[
database_name.[owner].
| owner.
] table_name
(
{ <column_definition>
| column_name AS computed_column_expression
| <table_constraint>
} [,...n]
)
[ON {filegroup | DEFAULT} ]
[TEXTIMAGE_ON {filegroup | DEFAULT} ]

<column_definition> ::= { column_name data_type }
[ [ DEFAULT constant_expression ]
| [ IDENTITY [(seed, increment ) [NOT FOR REPLICATION] ] ]
]
[ ROWGUIDCOL ]
[ <column_constraint>] [ ...n]

<column_constraint> ::= [CONSTRAINT constraint_name]
{
[ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[CLUSTERED | NONCLUSTERED]
[WITH FILLFACTOR = fillfactor]
[ON {filegroup | DEFAULT} ]]
]
| [ [FOREIGN KEY]
REFERENCES ref_table [(ref_column) ]
[NOT FOR REPLICATION]
]
| CHECK [NOT FOR REPLICATION]
(logical_expression)
}

<table_constraint> ::= [CONSTRAINT constraint_name]
{
[ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED]
{ ( column[,...n] ) }
[ WITH FILLFACTOR = fillfactor]
[ON {filegroup | DEFAULT} ]
]
| FOREIGN KEY
[(column[,...n])]
REFERENCES ref_table [(ref_column[,...n])]
[NOT FOR REPLICATION]
| CHECK [NOT FOR REPLICATION]
(search_conditions)
}

------------------------------------
5.DROP TABLE (T-SQL)
Removes a table definition and all data, indexes, triggers, constraints, and permission specifications for that table. Any view or stored procedure that references the dropped table must be explicitly dropped by using the DROP VIEW or DROP PROCEDURE statement.

Syntax
DROP TABLE table_name

Arguments
table_name
Is the name of the table to be removed.
------------------------------------------
6.CREATE VIEW view_name [(column [,...n])]
[WITH ENCRYPTION]
AS
select_statement
[WITH CHECK OPTION]

Arguments
view_name
Is the name of the view. View names must follow the rules for identifiers. Specifying the view owner name is optional.
column
Is the name to be used for a column in a view. Naming a column in CREATE VIEW is necessary only when a column is derived from an arithmetic expression, a function, or a constant, when two or more columns may otherwise have the same name (usually because of a join), or when a column in a view is given a name different from that of the column from which it is derived. Column names can also be assigned in the SELECT statement.
If column is not specified, the view columns acquire the same names as the columns in the SELECT statement.

--------------------------------------------------------------------------------

Note In the columns for the view, the permissions for a column name apply across a CREATE VIEW or ALTER VIEW statement, regardless of the source of the underlying data. For example, if permissions are granted on the title_id column in a CREATE VIEW statement, an ALTER VIEW statement can name the title_id column with a different column name, such as qty, and still have the permissions associated with the view using title_id.

--------------------------------------------------------------------------------

n
Is a placeholder indicating that multiple columns can be specified.
WITH ENCRYPTION
Encrypts the syscomments entries that contain the text of the CREATE VIEW statement.
AS
Are the actions the view is to take.
select_statement
Is the SELECT statement that defines the view. It can use more than one table and other views. To select from the objects referenced in the SELECT clause of a view being created, it is necessary to have the appropriate permissions.
A view does not have to be a simple subset of the rows and columns of one particular table. A view can be created using more than one table or other views with a SELECT clause of any complexity.

There are, however, a few restrictions on the SELECT clauses in a view definition. A CREATE VIEW statement cannot:

Include ORDER BY, COMPUTE, or COMPUTE BY clauses.
Include the INTO keyword.
Reference a temporary table.
Because select_statement uses the SELECT statement, it is valid to use <join_hint> and <table_hint> hints as specified in the FROM clause. For more information, see FROM and SELECT.

Functions can be used in the select_statement.

select_statement can use multiple SELECT statements separated by UNION to create a query using partitioned data.

For partitioned data to be used in ALTER VIEW or CREATE VIEW, constraint values must be able to be verified. If constraint checking has been disabled, reenable constraint checking with either the WITH CHECK option or the CHECK constraint_name options of ALTER TABLE. It is not necessary to have constraints to use partitioned data; however, query optimization generates less optimal plans without constraints. For more information, see Scenarios for Using Views.

WITH CHECK OPTION
Forces all data modification statements executed against the view to adhere to the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION guarantees that the data remains visible through the view after the modification has been committed.
------------------------------------------------------
7.sql中没有这句话
------------------------------------------------------
8.CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON table (column [,...n])
[WITH
[PAD_INDEX]
[[,] FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
]
[ON filegroup]

luhongjun 2001-07-21

打赏
举报

guo(正在消化Oracle)谢谢.
你答了:1:查询语句(select)(嵌套查询)
2:数据更新语句(delete,update,insert)
外加一个连接表(join)
其他的有么,把帮助给我粘过来,我急用,手头上没有ms sql server,我现在机器上只有ACCESS
我看了,《SQL Server 2000 Books Online》有30多M,我下不了,我的MODEN 33.6的.

DTWUJP 2001-07-21

打赏
举报

你要是E文菜，可以看一下SQLSERVER2000中文版的联机帮助，很详细，买一张D版的吧。

guo 2001-07-21

打赏
举报

我不为分,为朋友,看着
1.joined_table>
Is a result set that is the product of two or more tables, for example:
FROM tab1 LEFT OUTER JOIN tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1
ON tab2.c3 = tab4.c3

For multiple CROSS joins, use parentheses to change the natural order of the joins.

<join_type>
Specifies the type of join operation.
INNER
Specifies that all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.
LEFT [OUTER]
Specifies that all rows from the left table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the left table are set to NULL.
RIGHT [OUTER]
Specifies that all rows from the right table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the right table are set to NULL.
FULL [OUTER]
If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join.
<join_hint>
Specifies a join hint or execution algorithm. If <join_hint> is specified, INNER, LEFT, RIGHT, or FULL must also be explicitly specified. For more information about join hints, see FROM.
JOIN
Indicates that the specified tables or views should be joined.
ON <search_condition>
Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are often used, for example:
SELECT ProductID, Suppliers.SupplierID
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)

When the condition specifies columns, the columns do not have to have the same name or same data type; however, if the data types are not identical, they must be either compatible or types that Microsoft® SQL Server™ can implicitly convert. If the data types cannot be implicitly converted, the condition must explicitly convert the data type using the CAST function.

For more information about search conditions and predicates, see Search Condition.

CROSS JOIN
Specifies the cross-product of two tables. Returns the same rows as if the tables to be joined were simply listed in the FROM clause and no WHERE clause was specified. For example, both of these queries return a result set that is a cross join of all the rows in T1 and T2:
SELECT * FROM T1, T2

SELECT * FROM T1 CROSS JOIN T2
=================================================

2.DELETE (T-SQL)
Removes rows from a table.

Syntax
DELETE
[FROM ]
{
table_name WITH ( <table_hint_limited> [...n])
| view_name
| rowset_function_limited
}

[ FROM {<table_source>} [,...n] ]

[WHERE
{ <search_condition>
| { [ CURRENT OF
{
{ [ GLOBAL ] cursor_name }
| cursor_variable_name
}
]
}
]
[OPTION (<query_hint> [,...n])]

<table_source> ::=
table_name [ [AS] table_alias ] [ WITH ( <table_hint> [,...n]) ]
| view_name [ [AS] table_alias ]
| rowset_function [ [AS] table_alias ]
| derived_table [AS] table_alias [ (column_alias [,...n] ) ]
| <joined_table>

<joined_table> ::=
<table_source> <join_type> <table_source> ON <search_condition>
| <table_source> CROSS JOIN <table_source>
| <joined_table>

<join_type> ::=
[ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ]
[ <join_hint> ]
JOIN

<table_hint_limited> ::=
{ INDEX(index_val [,...n])
| FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
}

<table_hint> ::=
{ INDEX(index_val [,...n])
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}

<query_hint> ::=
{ { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| FAST number_rows
| FORCE ORDER
| MAXDOP
| ROBUST PLAN
| KEEP PLAN
INSERT [INTO]
{
table_name WITH ( <table_hint_limited> [...n])
| view_name
| rowset_function_limited
}

{ [(column_list)]
{ VALUES ( { DEFAULT
| NULL
| expression
}[,...n]
)
| derived_table
| execute_statement
}
}
| DEFAULT VALUES

<table_hint_limited> ::=
{ INDEX(index_val [,...n])
| FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX

UPDATE
{
table_name WITH ( <table_hint_limited> [...n])
| view_name
| rowset_function_limited
}
SET
{column_name = {expression | DEFAULT | NULL}
| @variable = expression
| @variable = column = expression } [,...n]

{{[FROM {<table_source>} [,...n] ]

[WHERE
<search_condition>] }
|
[WHERE CURRENT OF
{ { [GLOBAL] cursor_name } | cursor_variable_name}
] }
[OPTION (<query_hint> [,...n] )]

<table_source> ::=
table_name [ [AS] table_alias ] [ WITH ( <table_hint> [,...n]) ]
| view_name [ [AS] table_alias ]
| rowset_function [ [AS] table_alias ]
| derived_table [AS] table_alias [ (column_alias [,...n] ) ]
| <joined_table>

<joined_table> ::=
<table_source> <join_type> <table_source> ON <search_condition>
| <table_source> CROSS JOIN <table_source>
| <joined_table>

<join_type> ::=
[ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ]
[ <join_hint> ]
JOIN

<table_hint_limited> ::=
{ INDEX(index_val [,...n])
| FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
}

<table_hint> ::=
{ INDEX(index_val [,...n])
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}

<query_hint> ::=
{ { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| {LOOP | MERGE | HASH} JOIN
| FAST number_rows
| FORCE ORDER
| MAXDOP
| ROBUST PLAN
| KEEP PLAN

luhongjun 2001-07-21