Full access to SQL syntax

Source: Internet
Author: User
Tags define contains dbase insert join table name
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


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.


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


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.


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


Table name or table that contains the data we want.


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


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.


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


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.


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[,...]]


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".


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;


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][,...]]


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


The name of the form you want to query.


The standard settings for the query.


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.


The ascending order category. (default value)


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]]}


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


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.


Contains the full path name of the table.


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


For example: Microsoft Jet Database

SELECT Customer number

From Customer table


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


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


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


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


Select the criteria.


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


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


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


The name of the table being queried.


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]


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


Table name, or multiple table formulas.


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


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


Table name, or multiple table formulas.


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.


From staff form

WHERE surname = ' Lee ';

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.