MSSQL's four simple query

As a database developer, you need to extract data frequently for various purposes, such as creating reports. You can use SQL queries to extract data from the database server. This chapter explains how to extract selected data from a database table by executing a SQL query. Further, discusses how to use functions to customize the data values returned by the query. Also, this chapter explains how to extract summarized and grouped data from a database table Focus ? Data types in the database ? Working with result sets using the functions in the database ? Extracting data from a database table with a SELECT statement ? Mastering Group Queries Preview lessons ? Data types in the database ? Use of built-in functions ? Syntax format for SELECT statements ? Key points of a group query

This section will study the data types of the Transact-SQL language from six aspects.

First, analyze the concepts, characteristics, and main types of data types.

Then, describe the main content and characteristics of the digital data type.

Then, describe how the character data type is used.

Next, study the input and output characteristics of date and time data types.

Next, the characteristics of the binary data types are analyzed.

Finally, the content and characteristics of other data types are described.

? Types and characteristics of data types

In a Microsoft SQL Server 2008 system, objects that contain data have a data type. In fact, a data type is a type that specifies the data that an object can hold. For example, an object of an int data type can contain only integer data, and objects of the datetime data type can contain only data that conforms to datetime formats.

In a Microsoft SQL Server 2008 system, objects that need to work with data types include columns in the table, columns in the view, defined local variables, parameters in stored procedures, Transact-SQL functions, and return values for stored procedures.

The Microsoft SQL Server 2008 System provides 28 types of data. These data types can be categorized into numeric data types, character data types, date and time data types, binary data types, and other data types.

? Numeric data types

The numeric data types include bigint, INT, SMALLINT, TINYINT, BIT, DECIMAL, NUMERIC, Money, smallmoney, float, and real 11 data types.

Data types that use numeric data are called numeric data types. Numbers of these data types can participate in various mathematical operations. We can also continue to categorize these data types.

From whether these numbers have decimals, you can divide these data types into integer types and decimal types.

These data types can be divided into exact numeric types and approximate numeric types, from whether the precision and number of digits of these numbers can be determined explicitly.

From whether the amount can be represented, it can be divided into currency numeric types and non-monetary numeric types.

Below, the characteristics of each data type are studied in detail.

An integer data type represents the ability to store integer exact data.

In the Microsoft SQL Server 2008 system, there are 4 types of integer data, namely bigint, INT, SMALLINT, TINYINT.

The difference between these integer data types can be understood from the range of values and the length of two.

Both the decimal and numeric data types are data types with fixed precision and number of digits.

These two types of data are equivalent in function, except for the name.

In a Microsoft SQL Server 2008 system, these two data types are actually treated as exactly the same data type.

If you want to store data that represents currency values, you can use the money and smallmoney data types. The difference between these two data types is that the size of the storage bytes and the range of values are different.

In a Microsoft SQL Server 2008 system, the Money data type consumes 8 bytes of storage, with a value range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. The SmallMoney data type requires only 4 bytes of storage, and the value range is -214,748.3648 to 214,748.3647.

"Example 4-1" Demo Money data type

If you want to make scientific calculations and want to store larger values, but the accuracy of the data is not strictly required, then you should consider using the float or real data type.

The float or real data type is the data type that represents the approximate data value for the number of numeric values.

A bit is a data type that can store 1, 0, or null data. These data are mainly used for some conditional logic judgments. You can also store true and false data in the bit data type, and you need to store true and false data in character format.

? Character data type

Character data types are used to store character data in fixed-length or variable-length characters.

In the Microsoft SQL Server 2008 system, 6 data types are available for char, VARCHAR, TEXT, NCHAR, nvarchar, and ntext.

The first 3 types of data are non-Unicode character data, and the last 3 are Unicode character data.

? Date and time data types

Date and time data types include both datetime and smalldatetime two data types.

If you want to store date and time data, you can use a datetime or smalldatetime data type.

The difference between the two data types is that they represent different date and time ranges, and time accuracy is different.

The datetime data type can represent a range of January 1, 1753 to December 31, 9999, with a time accuracy of 3.33 milliseconds. The smalldatetime data type can represent a range from January 1, 1900 to December 31, 2079, with a time accuracy of 1 minutes.

It is recommended that users do not use the smalldatetime data type in large applications to avoid problems like the millennium bug. Because December 31, 2079 is not a particularly distant date.

Example 4-2 demonstrates how to use the SET DATEFORMAT statement

? Binary data types

Binary data types include binary, varbinary, and image 3 data types.

Binary data types include binary, varbinary, and IMAGE3 data types that can be used to store binary data. Where binary can be used to store fixed-length binary data, varbinary is used to store variable-length binary data. The data lengths of BINARY (n) and varbinary (n) are determined by the N value, and the range of n is 1 to 8000. The image data type is used to store picture information. However, in Microsoft SQL Server 2008 systems, Microsoft recommends using varbinary (MAX) instead of the image data type

"Example 4-3" demonstrates binary data types

Fig.: 4-3

? Other data types

In addition to the above-mentioned common types, it includes cursor, sql_variant, TABLE, TIMESTAMP, uniqueidentifier, and XML 6 data types

Features and types of built-in functions

You can divide the built-in functions provided by the Microsoft SQL Server 2008 system into 14 types, and each type of built-in function can perform some type of operation, and the function names and main functions of these types are shown in table 3-8.

Example 4-4 demonstrates the use of aggregate functions

Querying data with a SELECT statement is the core operation of the database. SQL Server provides a more complete form of a data query statement for a SELECT statement that is flexible to use and rich in functionality.

? Open a Database

Before you can manipulate data in a database, you must open the database with the use command and use the GO statement as the end line;

Use the database you want to open

GO

? SELECT statement

The SELECT statement is used primarily for querying data, and can also be used to assign values to local variables. The syntax for a common SELECT statement is:

Select selection list (what the query displays)

List of from tables (the table in which the content is queried)

Criteria for WHERE query (criteria for querying content)

The selection list can include several column names or expressions, separated by commas, to indicate which data should be returned. An expression can be a list of column names, functions, or constants; The FROM clause contains the name of the table or view that provides the data. When the select list contains column names, each SELECT clause must have a FROM clause. The WHERE clause is used to give the query criteria.

Example 4-9: Query BookName, publish, pubdate, price from the database book, and when the BookID equals 4003, run the following command in the Query window: example One

Use Bookiofo

Go

Select Bookname,publish,pubdate,price from book wherebookid=4003

The results of the operation are as follows:

The following is a detailed introduction to the SELECT statement hungry various use methods

? Use the asterisk (*) and column names

If you use an asterisk (*) in the select list, all columns are queried and returned from the table or view specified in the FROM clause.

Example 4-10: Queries all the information in the Book table, that is, all rows and all columns.

Run the following command in the query window:

Select *from Book

The results of the operation are as follows:

Using the DISTINCT keyword after select eliminates those rows that have the same value for the specified column.

Example 4-11: A publishing house that queries books from the Book table requires that those rows with the same values be eliminated. Run the following command in the query window:

Select Publish from Book

Select distinct publish from book

The results of the operation are as follows:

The first part is to display the first three rows, and the second part shows 3%, which is one row.

? Modify the title (alias) of a column in a query result

In the query results, you can see that the column headings that display the results are the column names of the tables, and can you modify the column headings of the displayed results to other visually understandable headings?

The following 3 methods are used to modify the query column headings.

The first method: The column header to be displayed is enclosed in single quotation marks followed by an equal sign followed by the name of the column to query.

The second method: After the list of questions to be displayed is enclosed in single quotation marks, it is written after the column name, separated by a space.

The third method: After the column headings to be displayed are enclosed in single quotation marks, write behind the column names and use the AS keyword between the two.

Note: Only the column headings of the query results are modified here. The column names in the table have not changed, and when I entered the SQL statement, I did not notice that punctuation must be entered in the half-width state.

Example 4-13: Run the following command in the query window:

Select ' publisher ' =publish, ' price ' =price from book

The results of the operation are as follows:

This is the first method to alias the results of the query

Use the second method to run the following command in the SQL query window

Select Publish ' publishers ', price ' prices ' from the book

Use the third method to run the following command in the SQL query window

Select Publish as ' publishers ', price as ' prices ' from book

? displaying strings in Query results

In some queries, it is often necessary to add some strings to the query results.

In the SELECT clause, the string to be incremented is enclosed in single quotation marks, and then the names of the columns are written together, separated by commas.

Example 4-14: Run the following command in the query window:

Select BookName, ' The price of the book is: '

The results of the operation are as follows:

? Use the WHERE clause to give the criteria for a query

Use the WHERE clause to limit the scope of a query, and typically you must define one or more criteria to restrict the rows of data selected by the query. The WHERE clause specifies a logical expression (an expression that returns a True or False value), and the result set returns the data row for which the expression is true.

In the WHERE clause, you can include comparison operators, logical operators. Comparison operators have = (equals), <> (not equals),! = (not Equal), > (greater than), >= (greater than or equal),!> (not greater than), < (less than), <= (less than equals),!< (not less than).

The logical operators have and (and), or (or), not (non), which are used to concatenate expressions. For example, the price of the book is under 50 and the publishing house for Science publishing house, can be represented as: price<50or publish= ' Science Press ' "

Example 4-15: Run the following command in the query window:

Select Publish,price from book whereprice<50.00 and Publish= ' Science Press '

The results of the operation are as follows:

Publish Price

1 Science Publishing house 38.0

The above query is price below 50.00 and publishing house for: Search results of Science publishing house

? The column expression required by the query

The list of options in the SELECT clause can be the list of expressions or columns to be specified, and the expression can be a list of column name functions or constants

"Example 4-16": Maximum value. The minimum and average values need to use the min (), MAX (), AVG () function to write the column name to be computed in parentheses, which is "pricing".

Run the following command in the query window:

Select min (price) as the lowest, max (price) as the highest

The expression is a function of the query.

? Rearrange query results by using the ORDER BY clause

You can rearrange the results of the query by using the ORDER BY clause to specify ascending (from low to high) or descending (high to low) by using the keyword ASC (ascending) or desc (descending), and the system by default to ascending. You can specify more than one column in the ORDER BY clause, and the result of the query is sorted first by the 1th column, the rows of data that have the same value as the 1th column, and then the rows in line 2nd ... and so on, the ORDER BY clause is written after the WHERE clause.

Example 4-17: Querying the BookName and price in the Book table requires a descending sort of the result of the price query.

Run the following command in the query window:

Select Bookname,price from book Order Byprice desc

The results of the operation are as follows:

Use ORDER by descending order

? Using the IN keyword

"Example 4-18" query book Access to "9702", "9708", "9705" bookname.

"Example analysis" book Access to "9702", "9708", "9705" is the condition, with the where sentence implementation, can be expressed as reader= ' 9702 ' or reader= ' 9708 ' or reader= ' 9705 '

In

Run the following command in the query window:

Select BookName from book wherereader= ' 9702 ' or reader= ' 9708 ' or reader= ' 9705 '

.

Instead of using the IN keyword to query more easily than two or operators, and easy to read and understand, the SQL statement using the IN keyword is as follows:

Select BookName from book where Readerin (' 9702 ', ' 9708 ', ' 9702 ') and runs the same result as above

? Query using the LIKE keyword

Readers often encounter the problem of querying a title that begins with a word, or querying something that ends with a word, and querying data that matches a given string can use the LIKE keyword. The LIKE keyword is a matching operator that matches a string expression that consists of a string and a wildcard character. The 4 wildcard characters for SQL are:

(1) percent percent semicolon, which matches a string containing 0 or more characters.

(2) _ Underline to match any single character.

(3) [] arranges wildcards that match any single character within a range or set, for example, [m-p] matches m, N, O, p single characters.

(4) [^] characters not within the range, matching any single character that is not within the range or set, for example, [^mnop] or [^m-p] matches any character except M, N, O, p.

Wildcard characters and strings must be enclosed in single quotes, for example;

Like '% ' matches a string that begins with "medium", and the like '% University ' matches a string that ends with a "university" two words; like ' _ Man% ' matches the second string that is "human".

Like ' n[^c]% ' matches all strings that start with the letter N and the second letter is not a C. To find wildcards themselves, you need them enclosed in square brackets. For example

Like ' 5[%] ' means to match ' 5% '.

"Example 4-19" queries the title ending with "Foundations".

Run the following command in the query window:

SELECT * FROM book where bookname like '% '

The results of the operation are as follows:

? Using the Between keyword

The Between keyword is always used with and and is used to query information within a specified range.

Example 4-21 queries all books that have a price greater than 50.00 greater than 40.00 and requires the query results to be sorted in ascending order.

Run the following command in the query window:

Select *from Book where price between 40.00 and 50.00 by price ASC

The results of the operation are as follows:

Use Computeby sentence query

? Using GROUP BY

Award query results are grouped according to the column specified after GroupBy, which is written after the WHERE clause, and is best used when you include an aggregate function in a select sentence. The columns that appear in the list of options in the select sentence are included in the aggregate function or included in the GROUP BY clause, or SQL Server will return the following error message:

Table name. The column name is not valid in the select list because the column is neither included in the aggregate function nor included in the GROUP BY clause.

"Example 4-23", according to the publishing house statistics the kind of publishing number of each publishing house

Run the following command in the query window:

Select Publish,count (publish) as ' reader ' from the book Group Bypublish

? HAVING clause

The HAVING clause is used to qualify a query condition for a group or aggregate function. This clause is often used after a GROUP BY clause and then judged after the result set is grouped. If a query condition needs to be applied before grouping, then using the WHERE clause, which restricts the query condition to be more efficient than using the HAVING clause, reduces the number of rows to be grouped. If there is no GROUP BY clause, the HAVING clause is used only in the select list for aggregate functions. In this case, the HAVING clause has the same effect as the WHERE clause. If the HAVING clause is not used in either of these cases, SQL Server returns an error prompt message.

"Example 4-24" inquires the average price of the book published by the publishing house for "Science Press".

Run the following command in the query window:

Selectpublish,avg (price) as ' average prices ' from book GroupBy Publish have publish= ' science Press '

Select Publish,avg (Price) as ' average prices ' from the book where Publish= ' Science Press ' GROUP by publish

Note: If the location of the HAVING clause changes or the location of the WHERE clause changes, an error message appears.

The results of the operation are as follows:

? Using the Union operator

The union operator is used to combine two or more query results into one result, and when the union operator is used, the following two rules are required:

(1) The Order of the columns and columns must be the same in all queries.

(2) The data type of the corresponding column in the order of all queries must be compatible.

The SELECT statement that joins the union operator is enumerated in the following manner; The first column of the first SELECT statement corresponds to the first column of each subsequent SELECT statement, and the second column corresponds to the second column of each subsequent SELECT statement ...

In addition, the corresponding columns must be used for compatible data types, which means that both corresponding columns must be of the same data type, or SQL Server must explicitly convert from one data type to another.

Practical issues

1. Which of the following operators is used to display a series of worthwhile records contained within a range of columns?

A, and B, = C, between D,%

2. A table contains duplicate values for columns in different records. How you will eliminate duplicate rows from the output of the query

3. Write a query that extracts all rows in the employee table, starting with a "P" character and containing "A" or "E" as the second character

1. Write a query that calculates the sum of the order quantities for each salesorderid in the Salesorderfetail table

2. Write a query that will search for the names of all departments starting with ' Pro ' in the Department table

Summary

In this chapter, you will learn to:

1. Use the SELECT statement to extract data from the database.

2. Use SELECT (*) to extract all columns.

3. Data that must be extracted based on conditions is specified by adding a WHERE clause.

4. The join operator is used to concatenate string expressions.

5. Arithmetic operators are used to perform arithmetic operations.

6. Use the logical operator in the SELECT statement to extract records based on one or satisfies the criteria. logical operators have and and not.

7. The range operator extracts data based on the range. There are two ranges of operators between and Notbetween.

8. The In keyword allows you to select a value in the list that satisfies any one of the values.

9. The not-in keyword restricts the selection of any one of the values in the list.

The LIKE keyword is used to specify the search pattern.

A. Is null keyword is used to extract missing values.

The ORDER BY keyword is used to extract data in a specific order.

The TOP keyword extracts only the preceding collection of rows, which can be the number or percentage of rows returned from the query results.

The DISTINCT keyword eliminates duplicate rows.

15. Aggregate functions, such as Avg,count,min,max and sum, are used to extract summary data.

GROUP BY clauses are used for grouping result sets.

MSSQL's four simple query