Comprehensive exposure to SQL syntax (2)

For example, the two examples below will have the same effect, and will return data from all fields in the employee table.

For example:

If we want to query all the records in the employee table, we can use the following statement to complete it.


SELECT ALL* FROM staff table; DISTINCT



　
For the field data selected in a certain table, duplicate cases are ignored, that is to say , the record result queried for a certain field is unique. For example, there are many staff

data stored in the staff table, which may have the same name, so if we use SELECT DISTINCT in the SQL statement, the query results will be filtered for different names. If you omit

DISTINCT, such a query will display all records.

DISTINCTROW

Ignore the duplicate records in the entire record, instead of only the data for a certain field.

　table

　Specify the table required to query records.

For example:


SELECT DISTINCTROW Company name


FROM Customer form INNER JOIN Order form

ON Customer form.Customer ID＝ Order form.Customer ID


　ORDER BY Company name;


　If you omit DISTINCTROW, less than one row of order data will be generated for each company. In addition, if DISTINCTROW is only used in one table, it will be omitted.

　TOP

Starting from the first or last item (using the ORDER BY conditional clause), return a specific number of data.

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 average score DESC;


If you do not add the ORDER BY line condition, the data you get will be random data. In addition, after the TOP statement, in addition to adding numbers, you can also use the reserved


words PERCENT to query.


For example:


SELECT TOP 10 PERCENT student name


FROM student form

WHERE graduation year=1994


ORDER BY Graduation Grade Average DESC; +++
Usage of PARAMETERS declaration

For parameter-type query syntax, the names and data types of parameters are Declared operation.


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


　name


　The name of PARAMETERS. You can use the parameter name as a string. If the name contains an empty string, you can use square brackets to process it, for example: "VBeden".


　datatype


　The data type of the input parameter.


For example:


If you need to enter the name flexibly when querying, you can use the following method to complete:


PARAMETERS " Enter your name” Text;


SELECT*

FROM Staff Form

WHERE Name = “Enter your name:”;