رستم فیروززاده
تماس با من
پروفایل من
آرشیو وبلاگ
      آموزش زبان ویژوال بیسیک (برنامه نویسی Visual Basic)
اس- کیو- ال (زبان پرس و جوى ساختارى)

 

SQL (Structured Query Language)

اس- کیو- ال (زبان پرس و جوى ساختارى)

SQL Syntax

The SQL query strings for Windows Installer are restricted to the following formats.

Action

Query

Select a group of records

SELECT [DISTINCT]{column-list} FROM {table-list} [WHERE {operation-list}] [ORDER BY {column-list}]

Delete records from a table

DELETE FROM {table} [WHERE {operation-list}]

Modify existing records in a table

UPDATE {table-list} SET {column}= {constant} [, {column}= {constant}][, ...] [WHERE {operation-list}]

UPDATE queries only work on nonprimary key columns.

Add records to a table

INSERT INTO {table} ({column-list}) VALUES ({constant-list}) [TEMPORARY]

Binary data cannot be inserted into a table directly using the INSERT INTO or UPDATE SQL queries.

Add a table

CREATE TABLE {table} ( {column} {column type}) [HOLD]

Column types must be specified for each column when adding a table. At least one primary key column must be specified for the creation of a new table. The possible substitutions for {column type} in the above are: CHAR [( {size} )] | CHARACTER [( {size} )] | LONGCHAR | SHORT | INT | INTEGER | LONG | OBJECT [NOT NULL] [TEMPORARY] [LOCALIZABLE] [, column...][, ...] PRIMARY KEY column [, column][, ...].

Remove a table

DROP TABLE {table}

Add a column

ALTER TABLE {table} ADD {column} {column type}

The column type must be specified when adding a column. The possible substitutions for {column type} in the above are: CHAR [( {size} )] | CHARACTER [( {size} )] | LONGCHAR | SHORT | INT | INTEGER | LONG | OBJECT [NOT NULL] [TEMPORARY] [LOCALIZABLE] [HOLD].

Hold and free temporary tables

ALTER TABLE {table name} HOLD

ALTER TABLE {table name} FREE

The user can use the commands HOLD and FREE to control the life span of a temporary table or a temporary column. The hold count on a table is incremented for every SQL HOLD operation on that table and decremented for every SQL FREE operation on the table. When the last hold count is released on a table, all temporary columns become inaccessible. If all columns are temporary, the table becomes inaccessible.

SQL Grammar

The optional parameters are shown enclosed in brackets [ ]. When several choices are listed, the optional parameters are separated by a vertical bar.

A {constant} is either a string or an integer. A string must be enclosed in single quote marks 'example'. A {constant-list} is a comma-delimited list of one or more constants.

The LOCALIZABLE option sets a column attribute that indicates the column needs to be localized.

A {column} is a columnar reference to a value in a field of a table.

A {marker} is a parameter reference to a value supplied by a record submitted with the query. It is represented in the SQL statement by a question mark ?.

The Windows Installer SQL syntax does not support the escaping of single-quotes (ASCII value 39) in a string literal. However, you can fetch or create the record, set the field with the StringData or IntegerData property, and then call the Modify method. Alternatively, you can create a record and use the parameter markers (?) described in Execute method. You can also do this using the database functions MsiViewExecute, MsiRecordSetInteger, and MsiRecordSetString.

A WHERE {operation-list} clause is optional and is a grouping of operations to be used to filter the selection. The operations must be of the following types:

·    {column} = {column}

·    {column} = | <> | > | < | >= | <= {constant}

·    {column} = | <> | > | < | >= | <= {marker}

·    {column} is null

·    {column} is not null

For string values, only the = or <> operations are possible. Object value comparisons are limited to IS NULL and IS NOT NULL.

Individual operations can be grouped by AND or OR operators. Ordering can be imposed by use of parentheses ( ).

The ORDER BY clause is optional and causes an initial delay during sorting. Ordering by strings will group identical strings together, but it will not alphabetize the strings.

The DISTINCT clause is optional and does not repeat identical records in the returned result set.

A {table-list} is a comma-delimited list of one or more table names referred to as {table} in the join.

A {column-list} is a comma-delimited list of one or more table columns referred to as {column} selected. Ambiguous columns may be further qualified as {tablename.column}. An asterisk may be used as a column-list in a SELECT query to represent all columns in the referenced tables. When referencing fields by column position, select the columns by name instead of using the asterisk. An asterisk cannot be used as a column-list in an INSERT INTO query.

To escape table names and column names that clash with SQL keywords, enclose the name between two grave accent marks `` (ASCII value 96). If a column name must be escaped and is qualified as {tablename.column}, then the table and the column must be escaped individually as {`tablename`.`column`}. It is recommended that all table names and column names be escaped in this fashion to avoid clashes with reserved words and gain significant performance.

Table names are limited to 31 characters. Table and column names are case-sensitive. SQL keywords are not case-sensitive.

The maximum number of expressions in a WHERE clause of a SQL query is limited to 32.

Only inner joins are supported and are specified by a comparison of columns from different tables. Circular joins are not supported. A circular join is a SQL query that links three or more tables together into a circuit. For example, the following is a circular join:

WHERE Table1.Field1=Table2.Field1 AND Table2.Field2=Table3.Field1 AND Table3.Field2=Table1.Field2.

Columns that are part of the primary key(s) for a table must be defined first in priority order, followed by any nonprimary key columns. Persistent columns must be defined before temporary columns. The sort sequence of a text column is undefined; however, identical text values always group together.

Note that when adding or creating a column, you must specify the column type.

Tables may not contain more than one column of type 'object'.

The maximum size that can be explicitly specified for a string column in a SQL query is 255.

To execute any SQL statement, a view must be created. However, a view that does not create a result set, such as CREATE TABLE, or INSERT INTO, cannot be used with MsiViewModify or the Modify method to update tables though the view.

Note that you cannot fetch a record containing binary data from one database and then use that record to insert the data into a completely different database. To move binary data from one database to another, you should export the data to a file and then import it into the new database through a query and the MsiRecordSetStream function. This ensures that each database has its own copy of the binary data.

 



لینک
مطالب اخیر دانلود نرم افزار Code Library ver1.0.0 Beta نرم افزار Policy Administrative Template Maker Beta ایجاد Local Database برای پروژه و بکارگیری آن ( بصورت ویزاردی) Snippet و استفاده آن در VS دانلود نرم افزار Trust Information آشنایی با خصوصیات پروژه Project Properties ایده های شما در حیطه نرم افزار رایانه Object Browser ایجاد پروژه جدید در میحط VS 2008 بکار گیری متغیر ها در VB 9.0
کلمات کلیدی وبلاگ adm (۱) api (۱) code library ver100 (beta) (۱) components information (۱) convert functions (۱) create project (۱) download trust information (۱) local database (۱) object browser (۱) policy administrative template (۱) select case (۱) snippet (۱) vb 90 (۱) wizard local database (۱) ارایه ها (۱) ارتباط با پایگاه داده (۱) ایجاد پروژه جدید با vb (۱) پایگاه داده (۱) تابع (٢) تابع جعبه پیام (۱) تاریخ (۱) تبدیل نوع داده (۱) توابعمرتبط با رشته (۱) تولید نرم افزار با ایده شما (۱) حلقه ها (۱) دانلود (۳) دانلود نرم افزار trust information (۱) دستور enum (۱) دستور with (۱) رابط کاربری برنامه نویس (۱) ریسورس (۱) زمان (۱) نرم افزار trust information (۱) ویژوال بیسیک 2008 (۱)
دوستان من تولید نرم افزار از ایده های بکر شما دانلود نرم افزار Snippet Maker 2.0 Download Visual Studio SQL and XML Tutorial Quick Learn ايجاد حساب هاي كاربري براي برنامه نسخه آزمایشی نرم افزار ساخت فایل adm پرسش و پاسخ ورزش ایرانی پرتال زیگور طراح قالب