Full access to SQL syntax

SQL syntax

SQL Features and Features

In fact, in the previous article, some basic functions of SQL commands have been mentioned, however, through SQL commands, a program designer or database administrator (DBA) can:

(i) Tables for the establishment of the database. (including the space you can use to set up the table)

(ii) Change the database system environment settings.

(iii) grant user access to a database or table.

(iv) Establish index values for database tables.

(v) Modify the database table structure. (New, delete, or modify a table field)

(vi) New data for the database.

(vii) The deletion of data from the database.

(eight) data modification of the database.

(ix) Queries for data on the database.

These are things that can be done through the SQL command, does it seem to be more than the "query" two characters represented by more than the function?

Classification of SQL syntax

In fact, the SQL command is not very much, but to use the acme of SQL, but only a few short commands will suffice, because the SQL command is a relational database of the syntax of the narrative, so SQL in such databases play a very strong function, The following is an introduction to the basic commands for SQL syntax commonly used in VB. Before describing SQL commands and using syntax, the following classification of SQL has been included in the type used in SQL syntax, which can be said to be contained in these categories.

First class, Attribute word (predicates)

The way in the SQL command to indicate the record you want to select. such as all, top and distinct and so on.

Category II, declarations (declaration)

Make a statement about the name and data type of SQL parameter or parameter Query, such as the parameters declaration, and so on.

Third class, conditional clause (Clause)

In SQL queries, some expressions are used to define the criteria for a query to narrow the search, such as where.

Class fourth, operators (Operator) and operands (Operation)

In SQL queries, together with Operation Expressions (Expression), such as between .... The AND operator is the number of inner join operations.

Class fifth, functions (function)

Some common SQL functions, such as AVG (), are functions that calculate the average of arithmetic.

class sixth, SQL statements (Statement)

SQL statements, which can be said to be the body of the SQL syntax, to give instructions to a particular database, and return the relevant data, and SQL syntax structure, can basically use the following

: COMMAND + conditional clause

For example:

Select*from TAB WHERE tab.name= ' A '

of which "from .... WHERE "is a conditional clause, in fact, the syntax of SQL is not difficult, you just remember such a rule, I believe you can quickly understand the SQL usage.

SQL syntax and commands

SELECT statement

select[predicate]{*|table.*|[ Table.] field [, [table.] field2[,...]]} [As ALIAS1 [, alias2[,...]]]

From Tableexpression [,...] [In Externaldatabase]

[WHERE ...]

[GROUP by ...]

[Having ...]

[Order BY ...]

[With Owneraccess OPTION]

The SELECT statement includes the following sections

predicate

As mentioned earlier, including All,distinct,distinctrow, and top We can use such statements to limit the results of the query.

*

Specifies all of the fields from the specified table.

Table

The name of the table specified for the field of the selected record.

Field1,field2

The name of the field that you want to read the data, and if more than one field is included, the data is read in the order listed.

Alias1,alias2

Used to override the alias of the actual field name in the table.

Tableexpression

Table name or table that contains the data we want.

Externaldatabase

If you use a database that is not current, define its name in Externaldatabase.

All,distinct,distinctrow,top Property Word Usage

SELECT [all| Distinct| distinctrow| [Top n[percent]]] From table

All

If you do not specify any field data, the Microsoft Jet database engine (DB engine) selects all the fields and queries out the requirements data set based on the criteria that are set.

For example, the following two examples will have the same effect, and all of the field data will be returned from the employee table.

For example:

If we want to check out all the records in the employee table, we can do so through the following statement.

SELECT all* from staff form; DISTINCT

The field data that is selected for a table is slightly repetitive, that is, the record results for a field query are unique. For example, there are many staff on the staff form

The data may have the same name, so if we use the Select DISTINCT in the SQL statement, the query results will be filtered for different names. If you put

DISTINCT is omitted, such a query displays all of the records.

Distinctrow

Ignores records that are duplicates of the entire record, rather than just the data for one field.

Table

Specify the tables that are required for the query record.

For example:

SELECT Distinctrow Company Name

From customer form INNER JOIN order Form

On Customer table. Customer id= order form. Customer ID

Order by company name;

If you ignore Distinctrow, the following line of order data is generated for each company. In addition, if the distinctrow is used only in one form, it will be omitted.

Top

Returns data for a specific number of bars, starting with the first or last (using the ORDER BY condition clause).

For example:

When you want to know the name data of the top 25 students in the class in 2000, you can enter a statement like this:

SELECT Top 25 Student Name

form Student Form

WHERE Graduation Year =1994

Order by graduation score average score DESC;

If you do not add the order by this line of conditions, you will get the data, random data. In addition, after the top statement, in addition to adding numbers, you can also use the reserved

Word percent to query.

For example:

SELECT Top percent student name

From student form

Where graduation year =1994

Order by the average DESC graduation results;

Use of PARAMETERS (parameter) declarations

For parameter-type query syntax, the operation that declares the name of the parameter and the data type.

PARAMETERS name Datatype[,name datatype[,...]]

Name

The name of the parameters. You can use the parameter name as a string, and if the name contains an empty string, you can use the middle bracket to handle it, for example, "Vbeden".

DataType

Enter the data type of the parameter.

For example:

If you need to enter your name in a mobile query, you can do so by using the following methods:

PARAMETERS "Enter name" Text;

select*

From staff form

WHERE name = "Enter Name:";

ORDER BY condition statement

This conditional clause, which is typically merged with a SELECT statement, is used to sort the results of a query according to a specified field.

SELECT FieldList

From table

WHERE Selectcriteria

ORDER BY field[asc| desc][,field2[asc| desc][,...]]

FieldList

The name of the field you want to query. which can be used with all,distinct,disinctrow, or top one.

Table

The name of the form you want to query.

Selectcriteria

The standard settings for the query.

Field1

Specifies that you want to follow that field as the basis for sorting, and if you do not add an order by query the dataset will not be sorted.

Asc

The ascending order category. (default value)

DESC

Descending order category.

For example:

Or we want to arrange the output data according to the order of birth, we can use the following command.

SELECT name, birthday

From staff form

ORDER BY Birthday

SELECT Lastname,firstname

From Employees

Order by LastName ASC;

In condition clause

Specifies which external database table to decisive victory. (Must be a database that the Microsoft Jet database engine can connect to, such as Dbase,paradox, and so on)

Select| Insert]into Destination in

{path| [' Path ' ' type ']| [' [Type;database=path]]}

From Tableexpression in

{path| [' Path ' ' type ']| [' [Type;database=path]]}

Destination

The name of the external table for which you want to insert data.

Tableexpression

The table name or the table name of the data being read. This parameter can be a single table name, or a section of SQL query that has been stored.

Path

Contains the full path name of the table.

Type

The type name of the database, which is usually used when the database department belongs to Jet databases. (For example: DBASE iii,dbase iv,paradox 3.x,paradox 4.x, or Btrieve)

For example: The following two paragraphs have the same meaning

Parta ..... From Table

In "" [DBASE iv;database=c:\dbase\data\sales;];

PARTB ..... From Table

In "C:\DBASE\DATA\SALES" "DBASE IV"

For example: Microsoft Jet Database

SELECT Customer number

From Customer table

In CUSTOMER. Mdb

WHERE customer number like "A *";

Where customer.mdbo is the database name for Jet database, which contains the Customer table.

For example: DBASE III or IV

SELECT Customer number

From Customer table

In "C:\DBASE\DATA\SALES" "DBASE IV"

WHERE customer number like "A *";

So when we use a database that is different from access, you must indicate the type name of the database.

Having conditional clause

Specifies a specific grouping record that satisfies the condition or state specified by the having, provided that the condition is set for the grouping.

SELECT FieldList

From table

WHERE Selectcriteria

GROUP by Groupfieldlist

Having GroupCriteria

FieldList

Displays the name of the field being queried. (Can be combined with all,distinct,distinctrow, or top)

Table

The name of the table you want to query for data.

Selectcriteria

Select the criteria.

Groupfieldlist

The field name of the grouped record to 10 more fields. The order of these fields determines the highest to the lowest grouping class.

GroupCriteria

Decide what group records to be displayed.

Having is quite similar to the usage of a where, except that the having must be used on grouped data after the group.

For example:

SELECT Category series, Sum (inventory quantity)

From Product table

GROUP by category number

Having Sum (inventory quantity) > Product name like "* paper";

GROUP by condition clause

Merges records with the same number into one, based on the specified field.

SELECT FieldList

From table

WHERE criteria

GROUP by Groupfieldlist

FieldList

The name of the field you want to read. (can be used in combination with All,distinct,distinctrow, or top)

Table

The name of the table being queried.

Groupfieldlist

The field name of the grouped record to 10 more fields, and the order of these fields determines the highest to lowest grouping level.

For example:

SELECT name, Count (name) as Employee name

From staff form

WHERE Department name = ' Business unit '

GROUP by name

From condition clause

Specifies the table name or query that contains the field data that is listed in the SELECT statement.

SELECT FieldList

From Tableexpression[in Externaldatabase]

FieldList

The name of the field in the table. (Can be combined with all,distinct,distinctrow, or top)

Tableexpression

Table name, or multiple table formulas.

Externaldatabase

If the form is referenced to an external database, write down the full path name.

For example:

From the staff form, query all the data for all last name fields (only the Last Name field is queried, others are not displayed).

SELECT name from staff form;

WHERE condition clause

Specify the criteria and restrictions for the query.

SELECT FieldList

From Tableexpression

WHERE criteria

FieldList

The field name. (Can be combined with all,distinct,distinctrow, or top)

Tableexpression

Table name, or multiple table formulas.

Criteria

The results of the query must be in accordance with this limit.

For example:

To query out the employee table, all last names are Li's data, you can use the following statement.

SELECT Name

From staff form

WHERE surname = ' Lee ';