Learn about SQL documents

Database: SQL Sever 2000


Note:


If you configure SQL Sever to use full security or mixed security, you can use a trusted connection. If you use standard security, you will need to provide a user account and password.


Library Name: Pubs (contains the various tables used by a virtual publisher; the installation is good, this is the case with this table)


Debugging Tools: SQL Query Analyzer, which allows you to perform interactive SQL queries, is useful for testing before writing a query into a program. )


: Select the database pubs in the DB dropdown box at the top of the query window so you can select the database.


1 Example


1.1 Record query (attached: numbered)


1.1.1 Simple SELECT query statement


1.1.1.1 Description:


SELECT Field 1, Field 2, ... from table [WHERE condition]


1.1.1.2 SQL statement:


"SELECT au_lname, phone from authors


"SELECT * FROM authors WHERE au_lname = ' Ringer '


1.1.1.3 Results:


1.1.1.4 Note:


1.1.2 Operations Multiple Tables


1.1.2.1 Description:


1.1.2.2 SQL Statement


"SELECT au_lname, title from authors, titles


"Select Title,pub_name from Titles,publishers WHERE titles.pub_id=publishers.pub_id


1.1.2.3 Results:


1.1.2.4 Note:


1.1.3 Action Field


1.1.3.1 Description:


1.1.3.2 SQL Statement


"SELECT phone as" phone number ' from authors WHERE au_lname = ' Ringer '


"SELECT phone ' phone number ' from authors WHERE au_lname = ' Ringer '


"SELECT Price * 2 from titles


' Select price ' Original price ', price * 2 ' New price ' from titles


1.1.3.3 Results:


1.1.3.4 Note:


you can use most of the standard mathematical operators to manipulate field values, such as add (+), subtract (-), multiply (*) and divide (/).


You can also perform operations on multiple fields at once.


1.1.4 Sort Query Results


1.1.4.1 Description:


1.1.4.2 SQL Statement


"SELECT au_lname from authors order by au_lname


"SELECT au_lname, au_fname from authors order by au_lname, au_fname


"Select Au_lname,au_fname from authors order by au_lname, au_fname DESC


1.1.4.3 Results:


1.1.4.4 Note:


Warning:


do not sort the query results when they are not specifically needed, because the server takes some effort to complete the work. This means that a SELECT statement with an ORDER BY clause takes longer to execute than a typical SELECT statement.


1.1.5 take out different records


1.1.5.1 Description:


1.1.5.2 SQL Statement


"Select DISTINCT au_lname from authors WHERE au_lname = ' Ringer '


1.1.5.3 Results:


1.1.5.4 Note:


Warning:


As with the ORDER BY clause, forcing the server to return distinct values increases the running cost. The blessing had to take some time to finish the work. Therefore, do not use the keyword distinct when necessary.


1.1.6 aggregate function


1.1.6.1 Description:


? You can count the number of records, average, minimum, maximum, or sum.


1.1.6.2 SQL Statement


"Select AVG (lowqty) ' the_average ' from discounts


"SELECT COUNT (au_lname) from authors WHERE au_lname= ' Ringer '


"SELECT COUNT (DISTINCT au_lname) from authors WHERE au_lname= ' Ringer '


"SELECT COUNT (*) from authors WHERE au_lname= ' Ringer '


"Select SUM (MIN_LVL) from Jobs


"Select MAX (MIN_LVL) from Jobs


"Select MIN (MIN_LVL) from Jobs


1.1.6.3 Results:


1.1.6.4 Note:


1.1.7 to retrieve data by matching


1.1.7.1 Description:


? A percent semicolon is one example of a wildcard character. It represents 0 or more characters.


? The brackets ([]) are used to match a single character in a specified range.


? ' [abc]% ' any one of their names with any of the beginning records of these characters will be returned.


? Remove characters (^) to exclude specific characters.


? By using the underscore character (_), you can match any single character.


1.1.7.2 SQL Statement


"SELECT royalty from titles WHERE royalty >= and royalty <= 12


"Select royalty from titles WHERE royalty BETWEEN 12


"Select royalty from titles WHERE royalty not BETWEEN 12


"Select royalty from titles WHERE royalty = Ten OR royalty = 12


"Select royalty from titles WHERE royalty in (10,12)


Select type from the titles WHERE type like '%popular_comp% '


Select type from titles WHERE type like ' [A-m]% '


Select type from titles WHERE type like ' [abc]% '


Select type from titles WHERE type like ' [a-fm]% '


"Select type from titles WHERE type" [^ (a-fmt)]% '


1.1.7.3 Results:


1.1.7.4 Note:


Note:


if you want to match the percent or underscore character itself, you need to enclose them in square brackets. If you want to match a hyphen (-), you should specify it as the first character in square brackets. If you want to match the brackets, you should enclose them in square brackets as well. For example, the following statement returns all sites whose description contains a percent semicolon:


1.1.8 Transform Data


1.1.8.1 Description:


? SQL Sever converts most values from one type to another. For example, to compare the size of smallint and int data, you do not need an explicit type conversion. The SQL sever will do the work for you.


? When you want to convert between character data and other types of data, you need to do the conversion operation yourself.


? function CONVERT ()


1.1.8.2 SQL Statement


"Select CONVERT (CHAR (8), price) + ' $ ' as ' money ' from titles


1.1.8.3 Results:


1.1.8.4 Note:

The
function CONVERT () has two variables. The first variable specifies the data type and length. The second variable specifies the field to be converted.