SQL Data Operation Basics (Elementary) 1

In order to build an interactive site, you need to use a database to store information from your visitors. For example, if you want to build a job placement service site, you need to store information such as personal resumes, jobs you are interested in, and so on. Creating dynamic web pages also requires the use of a database. If you want to display the best jobs that meet the visitor's requirements, you need to retrieve the job information from the database. You will find that there are many situations where you need to use a database.

In this chapter, you will learn how to use "Structured Query Language" (SQL) to operate the database. SQL language is the standard language of the database. In Active In SeverPages, whenever you want to access a database, you use the SQL language. Therefore, mastering SQL is very important for asp programming.

Note:

You can pronounce "SQL" as "sequel", or you can pronounce it as S-Q-L according to the pronunciation of the individual letters. Both pronunciations are correct, and each has a large number of supporters.
In this book, "SQL" is pronounced as "sequel".

Through the study of this chapter, you will understand how to use SQL to implement database queries, you will learn how to use this query to retrieve information from data tables, and finally, you will learn how to design and build your own database.

Note:

Through the introduction to SQL in the following chapters, you will have enough understanding of SQL to use Active effectively Sever Pages. However, SQL is a complex language, and it is impossible to include all its details in this book. To fully master the SQL language, you need to learn Microsoft SQL SQL is used in Sever. You can go to the nearby bookstore to buy
a copy of Microsoft SQL Sever 6.5.

SQL introduction:

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

Note:

Don’t try to use Microsoft on your site access instead of Microsoft SQL Server. SQL Sever can serve many users at the same time. If you want your site to have a higher access rate, MS Access is not up to the task.

Before learning the details of SQL, you need to understand its two major characteristics. One feature is easy to master, the other is a little difficult to master.

The first feature is that all data in SQL databases are stored in tables. A table consists of rows and columns. For example, the following simple table includes 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 (columns are also called fields, domains): Name and Email Address. There are three rows, each row contains a set of data. The data in a row combined is called a
record.

Whenever you add new data to a table, you add a new record. A data table can have dozens of records, thousands or even billions of records. While you
may never need to store a billion email addresses, it's always good to know that you can, and maybe one day you'll have the need.

Your database most likely contains dozens of tables, and all the information stored in your database is stored in these tables. When you think about how to store information in a database,
you should think about how to store it in tables.

The second feature of SQL is a bit difficult to master. The language is designed not to allow you to fetch records in a specific order, because doing so would slow down the SQL Sever’s efficiency in fetching records
. Using SQL, you can only read records based on query conditions.

When thinking about how to get records out of a table, it's natural to think of reading them by their location. For example, maybe you would try to select
specific records by scanning them one by one in a loop. When using SQL, you must train yourself not to think this way.

Suppose you want to select all the names whose names are "Bill Gates" record, if you use a traditional programming language, you may construct a loop to view the records in the table one by one to see if the name field is "Bill Gates".

This method of selecting records is possible, but it is not efficient. Using SQL, you just say, "Select all name fields equal to Bill Gates records", SQL will select
all matching records for you. SQL will determine the best way to implement the query.

Build the first ten records in the table you want to retrieve. Using traditional programming languages, you You can make a loop and end the loop after fetching the first ten records, but using standard SQL queries, this is impossible. From a SQL perspective, there is no concept of the first ten records in a table.

In the beginning, when you know that you can’t use SQL to achieve certain functions that you feel you should be able to achieve, you will be frustrated. You may beat your head against the wall and even want to write vicious letters to SQL
.Designers. But later you will realize that this feature of SQL is not only not a limitation, but its strength. Because SQL does not read records based on position, it can read records very quickly.

To sum up, SQL has two characteristics: all data is stored in tables. From a SQL perspective, the records in the tables are not sequential. In the next section, you will learn how to select specific records from a table
using SQL.

Use SQL to retrieve records from the table.

One of the main functions of SQL is to implement database queries. If you are familiar with the Internet engine, then you are already familiar with queries. You use queries to obtain information that meets specific criteria. For example, if you want to find all sites that have ASP information, you can connect to Yahoo! and execute a pair of Active Sever Pages search. After you enter this
query, you will receive a list of all sites that contain the search expression in their description.

Most of the Internet The engine allows logical queries. In logical queries, you can include special operators such as AND, OR, and NOT, which you use to select specific records. For example, you can use AND to limit query results. If you execute a pair of Active Sever Pages AND SQL search. You will get
Active Sever Pages which are also included in its description and SQL records. When you need to limit query results, you can use AND.

If you need to expand the results of the query, you can use the logical operator OR. For example, if you perform a search, search for all files whose description contains Active Sever
Pages OR SQL sites, the list you receive will include all sites whose descriptions contain both or either expression.

If you want to exclude a specific site from search results, you can use NOT. For example, the query "Active Sever Pages ”AND NOT “SQL” will return a list of sites containing Active Sever Pages, but not SQL. You can use NOT when specific records must be excluded.

Queries performed with SQL are very similar to searches performed with Internet search engines. When you execute a SQL query, by using query conditions including logical operators,
you can get a list of records. At this time, the query results are from one or more tables.

The syntax of SQL query is very simple. Suppose there is a table named email_table The table contains two fields: name and address. To get Bill Gates' e_mail address, you can use the following query:

SELECT email from email_table WHERE name="Bill Gates"

When this query is executed, Bill is read from the table named email_table Gates' e_mail address. This simple statement consists of three parts:

■ The first part of the SELECT statement names the column to be selected. In this example, only the email column is selected. when executing When, only the value of the email column is displayed
billg@microsoft.com.

■ The second part of the SELECTT statement specifies which table(s) to query data from. In this example, the table to be queried is called email_table.

■ Finally, the WHERE clause of the SELECT statement specifies the records that meet the conditions to be selected. In this example, the query condition is that only the value of the name column is Bill Gates The records
are selected.

Bill Gates most likely has more than one email address. If the table contains Bill Gates' multiple email addresses. Use the above SELECT statement to read all his
email addresses. The SELECT statement retrieves all name fields whose value is Bill from the table. The value of the email field of the Gates record.

As mentioned earlier, queries can contain logical operators in the query conditions. If you want to read Bill Gates Or all email addresses of President Clinton, you can use the following
query statement:

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

name="president Clinton"

The query conditions in this example are a little more complicated than the previous one. This statement selects all names listed as Bill from the table email_table Gates or President Clinton's record. If the table contains Bill Gates or president Clinton's multiple addresses, all of which were read.

The structure of the SELECT statement looks very intuitive. If you asked a friend to select a set of records from a table for you, you might formulate your request in a very similar way. in SQL In a SELECT statement, you "SELECT specific columns FROM a table WHERE certain columns satisfy a specific condition".

The next section will introduce how to execute SQL query to select records. This will help you become familiar with the different ways to retrieve data from a table using the SELECT statement.




The above is the content of SQL Data Operation Basics (Elementary) 1. For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!