Using SQL phần 1

Using SQL SQL (pronounced sequel) is the standard language for accessing relational databases. As youll see in this chapter, SQL is easy to learn and use
Nội dung trích xuất từ tài liệu:
Using SQL phần 1Using SQLSQL (pronounced sequel) is the standard language for accessing relational databases. Asyoull see in this chapter, SQL is easy to learn and use. With SQL, you tell the databasewhat data you want to access, and the database software figures out exactly how to getthat data.There are many types of SQL statements, but the most commonly used types of SQLstatements are these: • Data Manipulation Language (DML) statements • Data Definition Language (DDL) statementsDML statements allow you to retrieve, add, modify, and delete rows stored in thedatabase. DDL statements allow you to create database structures such as tables.Before you learn the basics of DML statements, you need to know how you can enter andrun SQL statements. You can enter and run SQL statements against a SQL Serverdatabase using the Query Analyzer tool, and youll learn about this next.Note As youll see later in the Accessing a Database Using Visual Studio .NET section, you can also use Visual Studio .NET to create SQL statements. Visual Studio .NET enables you to create SQL statements visually, as well as entering them manually.Using Query AnalyzerYou use Query Analyzer to enter and run SQL statements. You start Query Analyzer byselecting Start ➣ Microsoft SQL Server ➣ Query Analyzer. In the following sections,youll learn how to connect to a SQL server instance, enter and run a SQL statement, savea SQL statement, and load one.Connecting to a SQL Server InstanceWhen you start Query Analyzer, the first thing it displays is the Connect to SQL Serverdialog box, as shown in Figure 3.1. In the SQL Server field, you enter the name of theSQL Server instance to which you want to connect. You can click the drop-down list andselect an instance of SQL Server, or you can click the ellipsis button (three dots …) to theright of the drop-down list to display a list of SQL Server instances running on yournetwork.Figure 3.1: Connecting to a SQL Server databaseIf you select the Windows authentication radio button, then SQL Server will use theWindows 2000/NT user information to validate your request to connect to SQL Server. Ifyou select the SQL Server authentication radio button, then you will need to enter a loginname and password.In Figure 3.1, Ive entered localhost in the SQL Server field; this corresponds to theinstance of SQL Server installed on the local computer. Ive also selected the SQL Serverauthentication radio button, and entered sa in the Login Name field and sa in thePassword field (this is the password I used when installing SQL Server). These details arethen used to connect to SQL Server. If you have an instance of SQL Server running onyour local computer or on your network, you may enter the relevant details and click theOK button to connect to SQL Server.Now that youve seen how to connect to the database, lets take a look at how you enterand run a SQL statement.Entering and Running a SQL StatementOnce youve connected to SQL Server using Query Analyzer, you can use the ObjectBrowser to view the parts of a database, and you enter and run SQL statements using aQuery window. Figure 3.2 shows the Object Browser and an example Query window,along with the results of retrieving the CustomerID and CompanyName columns from theCustomers table.Figure 3.2: Viewing database items using the Object Browser and executing a SELECTstatement using the Query windowAs you can see from Figure 3.2, you enter SQL statements into the top part of the Querywindow, and the results retrieved from the database are displayed in the bottom part. Youspecify the database to access with the USE statement, and you retrieve rows from thedatabase using the SELECT statement.Tip You can also specify the database to access by using the drop-down list on the toolbar.If you want to follow along with this example, go ahead and enter the following USEstatement into your Query window:USE NorthwindThis USE statement indicates that you want to use the Northwind database. Next, on aseparate line, enter the following SELECT statement:SELECT CustomerID, CompanyName FROM Customers;This SELECT statement indicates that you want to retrieve the CustomerID andCompanyName columns from the Customers table.Note SELECT and FROM are SQL keywords. Although SQL isnt case sensitive, I use uppercase when specifying SQL keywords and mixed case when specifying column and table names. You may terminate a SQL statement using a semicolon (;), although this isnt mandatory.You can run the SQL statement entered in the Query window in five ways: • Selecting Execute from the Query menu • Clicking the Execute Query button (green triangle) on the toolbar • Pressing the F5 key on the keyboard • Pressing Ctrl+E on the keyboard • Pressing Alt+X on the keyboardOnce you run the SQL statement, your statement is sent to the database for execution.The database runs your statement and sends results back. These results are then displayedin the bottom of your Query window.Saving and Loading a SQL StatementYou can save a SQL statement previously entered into Query Analyzer as a text file.Later, you can load and run the SQL statement saved in that file. You can save a SQLstatement by • Selecting Save or Save As from the File menu • Clicking the Save Query/Result button (disk) on the toolbar • Pressing Ctrl+S on the keyboardWhen you do any of these, the Query Analyzer opens the Save Query dialog box. Letssay you save the file as CustomerSelect.sql. Once youve saved the file, you can open itby • Selecting Open from the File menu • Clicking the Load SQL Script button (open folder) on the toolbar • Pressing Ctrl+Shift+P on the keyboardWhen you do any of these, the Query Analyzer opens the Open Query File dialog box.Lets say you open CustomerSelect.sql ...