Tutorials to .com

Tutorials to .com » Database » Sqlserver » (Primary, intermediate, advanced)

(Primary, intermediate, advanced)

Print View , by: iSee ,Total views: 29 ,Word Count: 8675 ,Date: Tue, 25 Aug 2009 Time: 10:12 AM

(Primary, intermediate, advanced)
Operational data: SQL
Operational data: SQL

n SQL-based n Intermediate SQL n Senior SQL ========================================= ===============================

n SQL-based

¨ ¨ SQL Introduction

¨ ¨ using the SELECT statement to fetch data from the table

¨ ¨ Create a new table

¨ ¨ Field Properties

¨ ¨ add data to the table

¨ ¨ delete and modify tables

In order to build interactive sites, you need to use the database to store information from the visitors. For example, you want to create a job referral services of the site, you will need to store, such as CVs, are interested in work, and so such information. Ye also need to create a dynamic network using the database, if you want to demonstrate compliance with the requirements of visitors the best job, you need to remove the job from the database information. You will find that in many cases, need to use the database.

In this chapter, you will learn how to use the "Structured Query Language" (SQL〕 to operate the database. SQL database language is standard language. In the Active Sever Pages, by whenever you want to access a database, you have to use the SQL language. Therefore, to master SQL for asp programming is very important.

Note:

You can put "SQL" read "sequel", is also the pronunciation of the letters can be read as a single S-Q-L. Two kinds of pronunciation are correct, each have their own pronunciation of a large number of supporters. In this book, that the "SQL" read "sequel".

Through this chapter to learn, you will understand how to use SQL database query to achieve, you will learn how to use this check out the information from the data in the table, finally, you will learn how to design and build their own database.

Note:

Through the following chapters of the introduction of SQL, you will have enough understanding of SQL, allowing efficient use of Active Sever Pages. However, SQL is a complex language, this book can not include its full details. To fully grasp the SQL language, you need to learn in the Microsoft SQL Sever using SQL. You can go to a nearby bookstore to buy a Microsoft SQL Sever 6.5.

SQL Introduction:

This book assumes you are in the SQL operations Microsoft SQL Sever database. You can also use SQL operators in many other types of databases. SQL is a standard language to operate the database. (In fact, on a dedicated SQL language ANSI standard〕

Note:

Do not try to use your site on the Microsoft Access instead of Microsoft SQL Sever. SQL Sever can simultaneously serve many users, if you want your site have a higher access rate, MS Access is not competent.

Before learning the details of SQL, you need to understand its two important features. A feature of easy to master, and the other a bit difficult to master.

The first characteristic is that all SQL data in the database are stored in the table. A table formed by the rows and columns. For example, the following simple table includes the name and e-mail address:

Name Email Address

.................................................. ..............

Bill Gates billg@microsoft.com

president Clinton president@whitehouse.com

Stephen Walther swalther@somewhere.com

This table has two columns (column, also known as fields, field〕: Name and Email Address. There are three rows, each row contains a set of data. Line of data together is called a record.

Whenever you add new data to the table, you add a new record. A data table can have dozens of records, they can have thousands or even billions of records. Although you may never need to store one billion Email address, but know that you can do that is always good, and maybe one day you will have such a need.

Your database is likely to contain dozens of tables, all stored in your database information is stored in these tables. When you consider how the information is stored in the database, you should consider how to store them in the table.

The second feature of SQL some difficult to master. This language is designed to allow you the order in accordance with a particular out records, because doing so would reduce the efficiency of SQL Sever to take records. The use of SQL, you can only query to read the records.

When considering how to remove records from the table, it will naturally think of reading them according to the location of records. For example, maybe you'll try a cycle-by-record scanning to select a particular record. The use of SQL, you must train yourself not to have this line of thinking.

If you want to elect all of the name "Bill Gates" of the record, if you use a traditional programming language, you might construct a cycle-by-view records in the table to see whether the domain name "Bill Gates".

Record of such an option is feasible, but inefficient. The use of SQL, you just said, "select the name of the domain is equal to Bill Gates for all the records", SQL will be elected by all eligible for your records. SQL will identify the best way to achieve the query.

Construction of the table you want to remove the first 10 records. The use of traditional programming languages, you can do a loop, remove the record before the end of 10 cycles. However, using a standard SQL query, which is impossible. From the SQL point of view, in a table does not exist in the first ten records of the concept.

The beginning, when you know you can not use SQL to achieve some of you feel like you should be able to achieve the function, you will suffer a setback. You might want to write to head against walls and even vicious letter to SQL designers. But then you will realize, SQL for this feature is not only not a restriction, but is its strength. Because SQL is not based on location to read the record, it can be quickly read the record.

In summary, SQL has two characteristics: All data is stored in the table, from a SQL point of view, there is no order of records in the table. In the next section, you will learn how to use SQL from the menu, choose a special record.

Take from the table using the SQL record.

The main functions of the SQL database query is to achieve. If you are familiar with the Internet engine, then you are already familiar with inquiring about. You use the query to obtain information to meet specific conditions. For example, if you want to find information on all the ASP site, you can connect to Yahoo! And implement an Active Sever Pages search. After you enter this query, you will receive a list of the table, including all of its descriptions of the sites containing the search expression.

Most Internet engine allows the logical query. In logic query, you can include special operators such as AND, OR and NOT, you use these operators to select specific records. For example, you can use AND to limit the query results. If you are implementing an Active Sever Pages AND SQL search. You will get the description contains both the Active Sever Pages and SQL records. When you need to restrict the query results, you can use AND.

If you need to extend the results of inquiries, you can use the logical operator OR. For example, if you perform a search, search all of the descriptions contained Active Sever Pages OR SQL site, you receive the list will include all of its description contains two expressions or any one expression site.

If you want to exclude from the search results a particular site, you can use NOT. For example, the query "Active Sever Pages" AND NOT "SQL" will return a list, the list of site contains Active Sever Pages, but does not include SQL. When the need to exclude specific records, you can use NOT.

Performed using SQL queries and using Internet search engine to perform the search are very similar. When you execute a SQL query by using logical operators, including the query, you can get a list of records. At this point the query results are from one or more tables.

SQL query syntax is very simple. Suppose there is a table named email_table, including names and addresses of two fields, to get Bill Gates of the e_mail address, you can use the following query:

SELECT email from email_table WHERE name = "Bill Gates"

When the query execution time, starting with a table named email_table read the e_mail address of Bill Gates. This simple statement consists of three parts:

■ SELECT statement to select the first part of the named column. In this case, only the email column is selected. When executed, only the email column shows the value of billg@microsoft.com.

■ SELECTT statement from which the second part of the specified (s) in the table query data. In this case, we should query the table called email_table.

■ Finally, SELECT statement's WHERE clause to choose to meet the conditions specified in the records. In this case, the query for only the name column value of Bill Gates was only selected records.

Bill Gates is likely to have more than one email address. If the table contains multiple email addresses Bill Gates. With the above SELECT statement can be read all his email addresses. SELECT statement to remove all the name from the table field values for the Bill Gates of records email field.

As I mentioned before, the query in the query contains the logical operators. If you want to read Bill Gates, or President Clinton of all email addresses, you can use the following query:

SELECT email FROM email_table WHERE name = "Bill Gates" OR

name = "president Clinton"

In this case the query a little bit complicated than the previous one. This statement is selected from the table email_table all the name as Bill Gates or a president Clinton record. If the table contains a Bill Gates or a president Clinton to multiple addresses, all addresses have been read.

SELECT statement, the structure looks very intuitive. If you ask a friend from a table for you to select a set of records, you may be in a very similar manner to your request. In the SQL SELECT statement, you "SELECT specific columns FROM table WHERE a certain column to meet a specific requirement."

The next section will describe how to implement SQL queries to select records. This will help you are familiar with the SELECT statement to extract the data from the table a variety of different ways.

Implementation of the SELECT query using the ISQL

When you install SQL Sever, you also install a called ISQL / w application. ISQL / w allows you to perform interactive SQL queries. In the query, including your ASP page before using ISQL / w to test is very useful.

Note:

In the first part of this book, you learn how to install and configure Microsoft SQL Sever. If you have not installed SQL Sever or SQL Sever can not run, please refer to Chapter III, "to install and use SQL Sever".

Select the task on SQL Sever the ISQL_w program group to start the program. When the program starts, first there would be a dialog box asked to enter the server information and login information (see Figure 10.1). In the Sever box, enter your SQL server's name. If the server is running on the local computer, the server name is the name of your computer. In the Logon Information box, enter a login ID and password or choose to use "Trusted Connection", and then click the Connect button.

Figure 10.1

Note:

If you configured to use a full SQL Sever security or mixed security, then you can use the trusted connection. If you are using standard security, you will need to provide a user name and password. For more information, see Chapter III.

If everything works, before you click the Connect button, a query window will appear, as shown in Figure 10.2. (If there is an exception, please refer to Chapter III)

Figure 10.2

Before executing the query, you need to select the database. When installing SQL Sever you have created for themselves a database, SQL Sever database, there are many systems, such as the master, model, msdb, and tempdb.

Convenience is, SQL Sever with a specific example of a database named pubs. Library pubs with publishers for the use of a virtual individual tables. Documentation procedures are all examples of the design for this library. Many examples in this book also used the database.

In the query window at the top of the DB drop-down box select the database pubs, so you select a database. All your queries will address the various tables in this library to perform. Now you can perform your first inquired. This is really exciting!

Your first query will target a named autrors the table, the table contains all the work for a virtual publisher, author of relevant data. Click the query window and enter the following statement:

SELECT phone FROM authors WHERE au_name = "Ringer"

Input is complete, click Execute Query button (a green triangle, looks like a VCR play button). You click this button, any window appear in the query statement will be executed. Query window will automatically become the results window, you can see the query results (see Figure 10.3).

The query results you see may be different as shown in Figure 10.3. In the different versions of SQL Sever, library pubs in the data will vary. Pairs of SQL Sever 6.5, the will find the two records. The results showed that the window should display the following:

phone

... ... ... ... ... ....

801 826_0752

801 826_0752

(2 row (s) affected)

Figure 10.3

SELECT statement that you executed authors remove from the table to name all the authors for the Ringer's phone number. Your WHERE clause by using a special selection criteria to restrict the query results. You can also ignore the selection criteria, remove from the table all the author's telephone number. To do this, click the Query tab to return to the query window, enter the following SELECT statement:

SELECT Phone FROM authors

This query is executed, it will remove the table authors in all telephone numbers (no particular order). Authors, if the table contains 100 phone numbers, there are 100 records were removed, if the table has billions of phone numbers, which one billion records will be removed (this may take some time).

Table authrs fields include surname, name, phone number, address, city, state, and postal code. Through the first part of the SELECT statement specifies them, you can remove from the table in any one field. In a SELECT statement, you can take out multiple fields at once, such as:

SELECT au_fname, au_lname, phone FROM authors

The SELECT statement is executed, will remove all the values of these three columns. The following are the results of this query an example (in order to save paper, only shows part of the query results, the remaining records instead of using ellipsis):

au_fname au_lname phone

... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ....

Johnson White 408 496_7223

Marjorie Green 415 986_7020

Cheryl Carson 415 548_7723

Michael O'Leary 408 286_2428

...

(23 row (s) affected)

In the SELECT statement, you need to list the number of fields, you can list how much. Do not forget the field names separated by commas. You can also use an asterisk (*) from a table, take out all the fields. Here is an example of using the asterisk:

SELECT * FROM authors

The SELECT statement is executed, the table values of all fields have been removed. You will find that you will be frequently used in the SQL query asterisk.

Skills:

You can use an asterisk to view a table of all the columns by name. To do this, just finished in the implementation of the SELECT statement to look after the query results column headings.

Operation of multiple tables

Until now, you just tried to use a SQL query from a table out of data. You can also use a SELECT statement at the same time remove the data from multiple tables, only the FROM clause in the SELECT statement is taken out from the data listed in the table name can be:

SELECT au_lname, title FROM authors, titles

The SELECT statement is executed, the same time, authors from the table and table titles in the extract data. Authors from the table, remove all of the authors names removed from the table all the titles in the title. In ISQL / w program, run this query, take a look at the query results. You will find some strange unexpected situation: the author's name and their book does not match the book, but there the author's name and title of all possible combinations, which may not be what you want to see .

Out what went wrong? The problem is that you do not specify the relationship between the two tables. You do not have any way to tell SQL how to associate table and the table. Because they do not know how to associate two tables, the server will simply return the records from two tables of all possible combinations.

Selected from the two tables record a meaningful combination, you need through the establishment of the relationship between the two fields in the table to associate the two tables. To achieve this, one way is to create a third table, specifically the other two tables used to describe the relationship between the fields.

Authors have a table called au_id field, contains the unique identifier for each author. Table titles have a field named title_id contains the unique identifier for each title. If you can au_id in the field and the field to establish a relationship between title_id, you can associate the two tables. Pubs database has a table named titleauthor, it is used to complete the job. In the table for each record includes two fields, used to list titles and authors associated with the table. The following SELECT statement uses three tables to get the correct results:

SELECT au_name, title FROM authors, titles, titleauthor

WHERE authors.au_id = titleauthor.au_id

AND titles.title_id = titleauthor.title_id

When the SELECT statement is executed, each author will be matched with the correct title. Table titleauthor the table specified in the relationship between authors and table titles, which were from two tables by including a field in each to achieve this. The third table whose sole purpose is in the other two tables to establish relations between the fields. Itself does not contain any additional data.

Note that in this case, the field name is how the writing. In order to distinguish the table authors and table titles in the same field names au_id, each field name in front of all added the table name prefix and a full stop. Author.au_id field belongs to the table named authors, the fields are named titleauthor.au_id table titleauthor, do not confuse the two.

By using a third table, two tables you can build various types of relationships between the fields. For example, an author may be written many different books, or a book may be shared by many different authors to complete. When the two fields between the tables of this "many to many" relationship, you need to use a third table to indicate this relationship.

However, in many cases, the relationship between the two tables is not complicated. For example, you need to specify the table and table titles between publishers. Because a title can not be matched with a number of publishers, you do not need to specify the table by a third between the two tables. To specify the table and table titles between publishers, you just let these two tables have a public field on it. Pubs in the database, the table titles, and publishers have a table named pub_id field. If you want a list of titles and publishers, you can use the following statement:

SELECT title, pub_name FROM titles, publishers

WHERE titles.pub_id = publishers.pub_id

Of course, if a book is published jointly by the two publishers, then you need a third table to represent this relationship.

Usually, when you first know that I exist between two tables, fields, "many" relationship, on the use of a third table to associate the two tables. Conversely, if the two fields in the table have only "one to one" or "one to many" relationship, you can use public field to associate it the door.

Operation Field

Usually, when you remove from a table field values, the value created when you defined the table field names linked. If you select from a list of all authors in the author's name, all the values will be linked with the field names au_lname. However, in some cases, you will need to operate on the field names. In the SELECT statement, you can default the field name followed only with a new name to replace it. For example, you can use a more intuitive and easy to read the names of Author Last Name to replace the field names au_lname:

SELECT au_lname "Author Last Name" FROM authors

When the SELECT statement is executed, the value of the au_lname from the field with the "Author Last Name" linked. Query result may be something like this:

Author Last Name

... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ..

White

Green

Carson

O'Leary

Straight

...

(23 row (s) affected)

Note that the title field is no longer a au_lname, but is replaced by Author Last Name.

You can also through the implementation of operations, to operate from a table field values returned. For example, if you want to list all the titles in the double the price of the book, you can use the following SELECT statement:

SELECT price * 2 FROM titles

When this query is executed, each book removed from the table when the price will be doubled. However, by this approach will not change the operation of the field is stored in the table in the book price. Pairs of field operations will only affect the output of SELECT statements, but does not affect the data in the table. To the book also shows the original price and prices at the new price, you can use the following query:

SELECT price "Original price", price * 2 "New price" FROM titles

When the data remove titles from the table, the original price Original price displayed on the title below, after doubling the price displayed on the title New price below. The result may be like this:

original price new price

... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ....

19.99 19.99 39.98

11.95 23.90

2.99 2.99 5.98

19.99 19.99 39.98

...

(18 row (s) affected)

You can use most standard mathematical operators to operate the field values, such as the plus (+), minus (-), multiplication (*) and in addition to (/). You can also carry out an operation on multiple fields, such as:

SELECT price * ytd_sales "total revenue" FROM titles

In this case, multiplied by the price and volume to calculate the total sales of each book. The results of this SELECT statement like this:

total revenue

... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ..

81,859,05

46,318,20

55,978,78

81,859,05

40,619,68

...

(18 row (s) affected)

Finally, you can also use the concatenation operator (it looks like a plus sign) to connect the two character fields:

SELECT au_fname + "" + au_lname "author name" FROM authors

In this example, you au_fname and Field au_lname field paste together in the middle separated by a comma, and the title of the query results designated as author name. Results of the implementation of this statement will be like this:

author names

... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...

Johnson White

Marjorie Green

Cheryl Carson

Michael O'Leary

Dean Straight

...

(23 row (s) affected)

You can see, SQL gives you control over many of the query results. ASP programming course you should take full advantage of these benefits. Use SQL to manipulate the query results almost always have the same effect than the use of scripts more efficient.

Sort query results

Introduction of this chapter has emphasized, SQL tables have no intrinsic order. For example, from a table, take the second record is meaningless. From the SQL point of view, there is no record of any other record before.

However, you can manipulate the order of the results of an SQL query. By default, when the record removed from the table, the records do not occur in a particular order. For example, when removed from the table authors in the field, au_lname, the query results appear as this:

au_lname

... ... ... ... ... ... ... ... ... ... ... ... ... ....

White

Green

Carson

O'Leary

Straight

...

(23 row (s) affected)

See no particular order of a name is very convenient. If these names in alphabetical order, would be much easier to read. By using the ORDER BY clause, you can force a query results in ascending order, like this:

SELECT au_lname FROM authors ORDER BY au_lname

When the SELECT statement is executed, the author's name will be displayed in alphabetical order. ORDER BY clause with an author's name in ascending order.

You can simultaneously use multiple columns ORDER BY clause. For example, if you want to show the same time, an ascending au_lname fields and fields au_fname, you need to sort two fields are:

SELECT au_lname, au_fname FROM authors ORDER BY au_lname, au_fname

First, the results of this query to sort by au_lname field, and then sort by the field au_fname. Records will be removed the following order:

au_lname au_fname

... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ....

Bennet Abraham

Ringer Albert

Ringer Anne

Smith Meander

...

(23 row (s) affected)

Note that there are two authors have the same name Ringer. There named Albert Ringer, author of the author named Anne Ringer before, this is because the name Albert should be in alphabetical order came in before the name Anne.

If you want to query results according to the opposite order, you can use the keyword DESC. DESC keyword to the query results in descending order, the following example:

SELECT au_lname, au_fname FROM authors

WHERE au_lname = "Ringer" ORDER BY au_lname, au_fname DESC

This query from the table took out all the names of authors, author of record for the Ringer. ORDER BY clause, according to the author's name and surname, the query results in descending order. The result is like this:

au_lname au_fname

... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ....

Ringer Anne

Ringer Albert

(2 row (s) affectec)

Note that in this table, the name appears in the name Albert before Anne. Author's name displayed in descending order.

You can also press the numeric field on a query to sort the results. For example, if you want to remove all the books in descending order of price, you can use the following SQL query:

SELECT price FROM titles ORDER BY price DESC

The SELECT statement is taken out from the table, the price of all the books to show results, low price and the book the first show that the high price of the book display.

Warning:

Is not particularly necessary, not to sort the query results, because the server finish the job to spend more effort. This means that with an ORDER BY clause of the SELECT statement to execute the SELECT statement than the average time spent on longer.

Removed from each other's records

A table may be in the same column have duplicate values. For example, the authors table in pubs database, there are two author's name is Ringer. If you take out from this table, all of the name, the name of Ringer will be displayed twice.

In certain circumstances, you may only have interest from a table out from each other's value. If a field has duplicate values, you may want to only be selected once for each value, you can use the keyword DISTINCT to do this:

SELCET DISTINCT au_lname FROM authors WHERE au_lname = "Ringer"

When the SELECT statement is executed, only to return a record. Included in the SELECT statement by keyword DISTINCT, you can delete all the duplicate values. For example, suppose there is a group of information release on the press table, you want to take out all those who have published in this newsgroup information on person's name, then you can use the keyword DISTINCT. Each user's name just take one - although some users to publish information of more than one.

Warning:

The same as the ORDER BY clause to force the server to return the value will increase with each other to run overhead. Good fortune had to take some time to complete this work. So it is not required when the Do not use the keyword DISTINCT.

Create a new table

As mentioned above, all the data in the database is stored in the table. Data table includes rows and columns. Column determines the type of data in the table. Line contains the actual data.

For example, the authors table in the pubs database, there are nine fields. One of the field is named for the au_lname, this field is used to store information on the author's name. Add to this list each time a new author, the author's name will be added to this field, creating a new record.

By defining the field, you can create a new table. Each field has a name and a specific data type (data type later in the "field type" described in the section), for example, au_lname field is character data is stored. A field can also store other types of data.

The use of SQL Sever, create a new table is to many. You can perform a SQL statement or use the SQL transaction manager (SQL Enterprise Manager) to create a new table. In the next section, you will learn how to use SQL statements to create a new table.

Create a new table with the SQL

Note:

If you do not create your own database, now jump back to the third chapter to create this library. You must not be able to master, tempdb, or any other database to add any system data.

SQL Sever from the program group (in the task bar) to start ISQL / w program. After the query window appears, from the top of the window of the drop-down list, select your database created in Chapter III. Next, in the query window, type the following SQL statement, click the button to execute the query, execute this statement:

CREATE TABLE guestbook (visitor VARCHAR (40), comments TEXT, entrydate

DATETIME)

If everything works, you will see in the results window, the following text (if there is an exception, see Chapter III):

This command dit not return data, and it did not return any rows

Congratulations to you, you have established your first table!

You create a table called the guestbook, you can use this table to store words long your site visitors. You are using REEATE TABLE statement to create this table, this statement has two parts: the first part of the specified table's name; The second part is enclosed in brackets in each field name and attributes, use a comma between each other separated.

Table guestbook has three fields: visitor, comments, and entrydate. visitor field stores the visitor's name, comments fields to store the views of visitors to your site, entrydate fields to store visitors to your site, the date and time.

Note that the name of each field are related to have a special behind the expression. For example, the field names comments are an expression followed by TEXT. This expression specifies the field's data type. A field data type determines what kind of data can be stored. Comments because the field contains text information, its data type is defined as a text-type.

Field, there are many different data types. Supported by the next section describes some of the important SQL data types.

Field Type

Different types of fields used to store different types of data. Create and use tables, but you should understand the five kinds of commonly used field types: character, text type, numerical, logical, and date-type.

Character data

Character data is very useful. When you need to store information on a short string, you always use the character data. For example, you can put a text box from the HTML form to collect information on the character field.

To create a field used to store variable-length string of information, you can use an expression VARCHAR. To consider creating a table in front of you guestbook:

CREATE TABLE guestbook (visitor VARCHAR (40), comments TEXT, entrydate

DATETIME)

In this case, the field visitor data type VARCHAR. Note that the data type with the numbers in parentheses behind. This field specifies the number allowed by the maximum length of the string stored. In this example, the visitor can be stored in the string field of up to 40 characters. If the name is too long, the string will be truncated, leaving only 40 characters.

VARCHAR string types can store up to 255 characters. You want to store a longer string of data, you can use a text-type data (described in next section).

Another character data is used to store fixed-length character data. The following is an example of the use of this data type:

CREATE TABLE guestbook (visitor CHAR (40), comments TEXT, entrydate

DATETIME)

,visitor。CHAR。

VARCHARCHAR,。VARCHARBill Gates。,——Bill Gates。

CHAR,,。。

,VARCHARCHAR。VARCHAR,。

VARCHARCHAR。,。



255。,。,。



CREATE  TABLE  guestbook (visitor VARCHAR(40),comments TEXT,entrydate

DATETIME)

,comments。,。,。

HTML form(TEXTAREA),。,,,。,。。



(),2K。,。



SQL Sever。、、。

,,(INT)。 INT-2,147,483,6472,147,483,647。INT:

CREATE  TABLE  visitlog (visitor VARCHAR(40),numvisits  INT)

。2,147,483,647,nubvisits。

,SMALLINT。 SMALLINT -3276832768。INT。

,,TINYINT。,INT,0255。 TINYINT。

,,。TINYINT;INT。,,。,,。,,,,。

,NUMERIC。 NUMERIC——INT。NUMERIC-10381038。 NUMERIC。,NUMERIC3.14。

NUMERIC,。:

CREATE TABLE  numeric_data (bignumber  NUMERIC(28,0),

fraction    NUMERIC (5,4) )

,numeric_data。bignumber28。fraction。

NUMERIC28,,。

INTNUMERIC。,。,MONEY。,SMALLMONEY。 MONEY-922,337,203,685,477.5808922,337,203,685,477.5807。,NUMERIC。

SMALLMONEY-214,748.3648214,748.3647 。,,SMALLMONEYMONEY,。:

CREATE TABLE products (product VARCHAR(40),price  MONEY,

Discount_price SMALLMONEY)

。price MONEY,discount_priceSMALLMONEY。



(CHECKBOX),BIT。 BIT:01。:

CREATE  TABLE  opinion (visitor VARCHAR(40),good BIT)

。。YES,BIT1。,NO,0(,)。

,,BIT。BIT,。



,。,DATETIME,:

CREATE  TABL visitorlog( visitor VARCHAR (40), arrivaltime DATETIME ,

departuretime  DATETIME)

。DATETIME17531199991231。

,SMALLDATETIME。DATETIME,DATETIME,DATETIME。SMALLDATETIME190011207966,。

DATETIME,。,SQL(“”)。VBScriptJScript DATETIME。



。,。,。



(NULL)。,,。(NULL),,。

,NULL。,:

CREATE TABLE empty (empty1 CHAR (40) NULL,empty2 INT NULL(



BIT。01。

。,,。,:

CREATE  TABLE creditcards (creditcard_number CHAR(20) NOT NULL,

Creditcard_expire  DATETIME  NOT NULL)

NOT NULL。NOT NULL,,。

,,。,,。。



,、、、。,country。

,DEFAULT。:

CREATE TABLE addresses (street VARCHAR(60)  NULL,

city VARCHAR(40) NULL,

state VARCHAR(20) NULL

zip  VARCHAR(20) NULL,

country VARCHAR(30) DEFAULT  'USA')

,country。,。,:

CREATE TABLE orders(price  MONEY DEFAULT $38.00,

quantity INT DEFAULT 50,

entrydate DATETIME DEFAULT GETDATE())

CREATE TABLE,。DATETIMEentrydate,Getdate(),。



。。,pubsjobs:

job_id   job_desc

…………………………………………………………….

1                         1                         New Hire Job not specified

2                         2                         Chief Executive officer

3                         3                         Bushness Operations Manager

4                         4                         Chief Financial Officier

5                         5                         Publisher

job_id。,job_id。

,IDENTITY。NUMERICINT,:

CREATE TABLE visitorID (theID NUBERIC(18) IDENTITY,name VARCHAR(40))

theid。,。。



,。TINYINT,255。,NUMERIC。

。,。。,,。,。,67。

SQL

。,。。

SQL SeverSQL Enterprise Manager,,10.4。,Database。Database,。



,。master,tempdb。

,Group/usersobjects。objects,,Tables。TablesNew table,10.5。

Manager  Tables。 Manager Tables7:Key,Column,Name,Datatype,Size,NullsDefault。 Manager Tables。

10.4

10.5

,。Column  Namemycolumn。,Datatype,CHAR。,10.6。

10.6

。。,mytableOK。。

Tables,。,Manager Tables,。

SQL,SQL。,。



SQL。,,SQL。,Microsoft  SQL SeverMicrosoft Query ,。

SQL SeverMicrosoft Query。File|New Query。Select Data Source(10.7)。Use。

10。7

,。(mytable ),Add,Close。

,mytable。,。(*),。

,。,,。,Records|Allow Editing,。,。

10。8

,。,Microsoft Query 。



,。,,。,(“SQL”“SQL”)。

,SQLDROP TABLE。,mytable,:

DROP TABLE mytable



DROP TABLE。,。

,。,。,TRUNCATE TABLE。,SQLmytable:

TRUNCATE TABLE mytable

,。SQLManager Tables。SQLALTER TABLE。:

ALTER TABLE mytable ADD mynewcolumn INT NULL

mytablemynewcolumn。,,。



SQL。SQL,Microsoft  SQL Sever。SELECT,CREATE TABLESQL。,。

SQL。SQL,SQL。

■  SQL

¨          ¨         

¨          ¨          SQL

¨          ¨         

¨          ¨          SQL,,

“SQL”SQL。SELECT,。,SQL。。SQL。



。,。,。

。。,,。。



,。,SQL Sever。,,1.2。,sp_spaceused,。



。,,。,。,。

,,,,,。,,。。

SQL Sever。,。,。。,。

:。,:。,:。

,。,。。,。,。

,,。

,。。,,,。

。,。,。,DATETIME。

。,。,249。。

,。,。,。,。

。,,。,。。,。,,。



:();。

,。。,。。,。,。

。,。,。,。,。

SQL

,SQL SeverISQL/w。,:

CREATE INDEX mycolumn_index ON mytable (myclumn)

mycolumn_index。,,。



SQL,:

This  command did not return data,and it did not return any rows



mycolumn_indexmytablemycolumn。,。 ()

,。,SQL:

DROP INDEX mytable.mycolumn_index

DROP INDEX 。,mycolumn_index,mytable。

,CLUSTERED。 )。 (:

CREATE CLUSTERED INDEX mycolumn_clust_index ON mytable(mycolumn)

,,。;ALLOW_DUP_ROWSQL Sever:

CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

WITH ALLOW_DUP_ROW

。,,,。

,UNIQUE。。:

CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

。,,。

,────。firstnamelastname:

CREATE INDEX name_index ON username(firstname,lastname)

。,16。



SQL。,,。

:Manage TablesManage Indexes。

Manage Tables ,Advanced Options()。Advanced Options。Primary Key(11.1)。

11。1

,。,。。,。Manage Tables,。

“”。。,。

,Manage Indexes。Manage|Indexes,Manage Indexes 。Manage Indexes ,。 (11.2)。,IndexNew Index.,。Add,。

11。2

。,。。,Build,。



,。

SQL

,SQL SELECT 。,,,。,。



,SQL INSERT 。:

INSERT mytable (mycolumn) VALUES ('some data')

'some data'mytablemycolumn。,。

INSERT :

INSERT [INTO]  {table_name|view_name} [(column_list)] {DEFAULT VALUES |

Values_list | select_statement}

,,。mytablefirst_column,second_column,third_column。INSERT:

INSERT mytable (first_column,second_column,third_column)

VALUES ('some data','some more data','yet more data')



INSERT。,,WRITETEXT。,。,Microsoft SQL Sever 。

INSERT ?,,。,:

■   ■,。,third_column,'some value'。,'some value'。

■   ■,,。

■   ■,,。:

The column in table mytable may not be null.

■   ■,,。,,。



,SQL@@identity

。SQL:

INSERT mytable (first_column) VALUES('some value')

INSERT anothertable(another_first,another_second)

VALUES(@@identity,'some value')

mytable,anothertableanother_first。@@identity。

another_firstfirst_column。,another_first。 Another_firstfirst_column。



,SQL DELETE。DELETE WHERE 。 WHERE。,DELETEfirst_column'Delete Me':

DELETE mytable WHERE first_column='Deltet Me'

DELETE :

DELETE [FROM] {table_name|view_name}  [WHERE clause]

SQL SELECT DELECT WHERE。,DELETEfirst_column'goodbye'second_column'so long':

DELETE mytable WHERE first_column='goodby' OR second_column='so long'

DELETE WHERE ,。。,TRUNCATE TABLE。



TRUNCATE TABLE DELETE?TRUNCATE TABLE,。,TRUNCATE TABLE DELETE。



,SQL UPDATE。DELETE,UPDATEWHERE。:

UPDATE mytable SET first_column='Updated!' WHERE second_column='Update Me!'

UPDATE second_column'Update Me!'。,first_column'Updated!'。

UPDATE:

UPDATE {table_name|view_name} SET [{table_name|view_name}]

{column_list|variable_list|variable_and_column_list}

[,{column_list2|variable_list2|variable_and_column_list2}…

[,{column_listN|variable_listN|variable_and_column_listN}]]

[WHERE clause]



UPDATE。,,UPDATETEXT。,。,Microsoft SQL Sever 。

WHERE,。。,titles,UPDATE :

。,UPDATEfirst_column,second_column,third_column:

UPDATE mytable  SET  first_column='Updated!'

Second_column='Updated!'

Third_column='Updated!'

WHERE first_column='Update Me1'



SQL。SQL。

SELECT

,INSERT DELETEUPDATE,。,INSERT 。,INSERT SELECT ,:

INSERT mytable (first_column,second_column)

SELECT another_first,another_second

FROM anothertable

WHERE another_first='Copy Me!'

anothertablemytable.anothertableanother_first'Copy Me! '。

,INSERT 。,。

,SELECT  INTO 。,newtable,mytable:

SELECT * INTO newtable FROM mytable

。,。,WHERE 。second_columnd'Copy Me!'first_column。

SELECT first_column INTO newtable

FROM mytable

WHERE second_column='Copy Me!'

SQL。,,。,,。,SQL,。

,。SELECT INTO ,,。,。

,。,UPDATESELECT ,。,,。



,。,。,。。,。

Microsoft SQL 。,,,,。,,。



ASP,。,SELECT,,:

SELECT AVG(vote) 'the_average' FROM opinion

,votethe_average。ASP。



COUNT()。。:

SELECT COUNT(au_lname) FROM authors

authors(last name)。,。,WHERE,:

SELECT COUNT(au_lname) FROM authors WHERE au_lname='Ringer'

'Ringer'。authors,2。

。DISTINCT。:

SELECT COUNT(DISTINCT au_lname) FROM authors

'Ringer',。DISTINCT 。

,COUNT(),。,。,,─。:

SELECT COUNT(*) FROM authors

COUNT()。,。,。

COUNT()。,。vote,0,1。0,1。,SELECT :

SELECT COUNT(vote) FROM opinion_table WHERE vote=1



COUNT(),。。AVG(),。

。110,。voteINT。,AVG():

SELECT AVG(vote) FROM opinion

SELECT。AVG()。。



,,。orders。,SUM():

SELECT SUM(purchase_amount) FROM orders

SUM()purchase_amount。purchase_amountMONEY,SUM()。



。110 。,:

SELECT MAX(vote) FROM opinion

。MAX(),。10,MAX()。

,,MIN(),:

SELECT MIN(vote) FROM opinion

MIN()。,MIN()。

SQL,,

SQL。,,,。,。



。710。,SELECT :

SELECT username FROM opinion WHERE vote>6  and  vote<11

SELECT 。SELECT :

SELECT username FROM opinion WHERE vote BETWEEN 7 AND 10

SELECT 。,BETWEEN 。

110。opinion,SELECT :

SELECT username FROM opinion WHERE vote=1 or vote

SELECT,。,。SELECT:

SELECT username FROM opinion WHERE vote IN (1,10)

IN 。SELECT vote。

IN。,Bill GatesPresident Clinton。SELECT :

SELECT vote FROM opinion  WHERE  username IN ('Bill Gates','President Clinton')

,BETWEENINNOT。,710,SELECT :

SELECT username FROM opinion WHERE vote NOT BETWEEN 7 and 10

,NOT IN,:

SELECT vote FROM opinion

WHERE username NOT IN ('Bill Gates','President Clinton')

SQLBETWEENIN,,,。



SQL Sever,。,SMALLINTINT,。 SQL Sever。,,。,MONEY,“US Dollars”。CONVERT(),:

SELECT CONVERT(CHAR(8),price)+'US Dollars' FROM orders

CONVERT()。。。,price8CHAR。price,'US Dollars'。

BIT,DATETIME,INT,NUMERIC,。,SELECT'The vote is',SELECTBIT:

SELECT 'The vote is'+CONVERT(CHAR(1),vote) FROM opinion



The vote is 1

The vote is 1

The vote is 0

(3 row(s) affected)

,:

Implicit conversion from datatype 'varchar' to 'bit' is not allowec.

Use the CONVERT function to run this query.



SQL Sever,,。,。



YahooInternet。,(URL),,,HTML form。

trading card。,:

SELECT site_name FROM site_directory  WHERE site_desc='trading card'

。,trading card。,We have the greatest collection of trading cards in the world!。

,。LIKE。LIKE,:

SELECT SITE_name FROM site_directory

WHERE site_desc LIKE '%trading cark%'

,trading card。We have the greatest collection of trading cards in the world!。,I am trading cardboard boxes online ,。

。。0。trading card,trading card。

,。。,AM。,NZ。,SQL:

SELECT site_name FROM site_directory WHERE site_name LIKE '[AM]%'

[AM],AM。([])。,:

SELECT site_name FROM  site_directory

WHERE site_name LIKE '[NZ]%'

,NZ。

,。A,BC,:

SELECT site_name FROM site_directory WHERE site_name LIKE  '[ABC]%'

,,。。

,。,,CF,Y:

SELECT site_name FROM site_directory WHERE site_name LIKE '[C-FY]%'

,CollegescapeYahoo,Magicw3。

(^)。,Y,:

SELECT site_name FROM site_directory WHERE site_name LIKE '[^Y]%'



,(_),。,:

SELECT site_name FROM site_directory WHERE site_name LIKE 'M_crosoft'

Microsoft,Macrosoft。,Moocrosoft。'%',。



,。(-),。,。,:

SELECT site_name FROM site_directory WHERE site_desc LIKE '%[%]%'



Microsoft SQL 。SOUNDEX(),DIFFERENCE()。,,。

,Internet,,,。:

SELECT site_name FROM site_directory

WHERE DIFFERENCE(site_name , 'Microsoft'>3

DEFFERENCE()Microsoft。DIFFERENCE()04。4,;0,。

,MicrosoftMacrosoft。Microsoft。32,ZicrosoftMegasoft。,1,PicosoftMinisoft。

DIFFERENCE(),SOUNDEX()DIFFERENCE()。:

SELECT site_name 'site name',SOUNDEX(site_name) 'sounds like'

site_name。:

site name                     sounds like

……………………………………………………………….

Yahoo                         Y000

Mahoo                         M000

Microsoft                     M262

Macrosoft                     M262

Minisoft                      M521

Microshoft                    M262

Zicrosoft                     Z262

Zaposoft                      Z121

Millisoft                     M421

Nanosoft                      N521

Megasoft                      M221

Picosoft                      P221

(12 row(s) affected)

,。,YahooMahoo。MicrosoftMacrosoft。

DIFFERENDE()。(y),。

,SOUNDEX()DIFFERENCE()。 WHERE。,。



,TTRIM()LTRIM(),。LTRIM();RTRIM()。RTRIM():

SELECT RTRIM(site_name) FROM site_directory

,,。

,:

SELECT LTRIM(RTRIM(site_name) FROM site_directory

,CHAR,。,CHAR,,。,,。



。。,。



GETDATE(),。,SELECT GETDATE():

……………………………..

NOV 30 1997 3:29AM

(1 row(s) affected)

,,,。

GETDATE()DATEDIME()。。,。,,,,。,,DATETIME,GETDATE(),:

CREATE TABLE site_log (

username VARCHAR(40),

useractivity VARCHAR(100),

entrydate DATETIME DEFAULT GETDATE())



,,GETDATE()。,SQL Sever(,3.33)。

,CONVERT()。,,:

SELECT CONVERT(VARCHAR(30),GETDATE(),9)

9。。,:

…………………………………..

Nov 30 1997 3:29:55:170AM

(1 row(s) affected)

CONVERT()。11.1。

11.1 

                        

0                 Default           mon dd yyyy hh:miAM

1                 USA               mm/dd/yy

2                 ANSI              yy.mm.dd

3                 British/French    dd/mm/yy

4                 German            dd.mm.yy

5                 Italian           dd-mm-yy

6                 -                 dd mon yy

7                 -                 mon dd,yy

8                 -                 hh:mi:ss

9                                                     9                                                                         Default + milliseconds--mon dd yyyy

hh:mi:ss:mmmAM(or )

10                USA              mm-dd-yy

11                JAPAN            yy/mm/dd

12                ISO              yymmdd

13                Europe           Default + milliseconds--dd mon yyyy

hh:mi:ss:mmm(24h)

14                 -                hh:mi:ss:mmm(24h)

0,9,13。,,style100。131424。0,7,13(NovNovember).

11.1,100(,002000)。,,,:

SELECT CONVERT(VARCHAR(30),GETDATE(),111)

,CONVERT(),1997/11/30



,,。,。。,DATEPART(),:

SELECT site_name 'Site Name',

DATEPART(mm,site_entrydate) 'Month Posted' FROM site_directory

DATEPART()。;。,DATEPART(),mm。SELECT :

Site Name                         Month Posted

………………………………………………………………

Yahoo                              2

Microsoft                          5

Magicw3                            5

(3 row(s) affected)

Month Posted。DATEPART()。,11.2。

11.2 

                         

year                yy                1753--9999

quarter             qq                1--4

month               mm                1--12

day of year         dy                1--366

day                 dd                1--31

week                wk                1--53

weekday             dw                1--7(Sunday--Saturday)

hour                hh                0--23

minute              mi                0--59

second              ss                0--59

milisecond          ms                0--999

,DATEPART()。,(2,5)。,DATENAME(),:

SELECT site_name 'Site Name'

DATENAME(mm,site_entrydate) 'Month Posted'

FROM site_directory

DATE


Microsoft SQL Server Tutorial Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.