คำสั่ง-SQL เบื้องต้น

posted on 11 Sep 2006 11:36 by theera  in Visual-Basic

Introduction to Structured Query Language

พื้นฐาน คำสั่ง SELECT

ในระบบฐานข้อมมูลนั้น ข้อมูลทั้งหมดจะถูกจัดเก็บไว้ในรูปของ Table และ Columns ตัวอย่างเช่น

ในTable พนักงาน เราจัดเก็บข้อมูล Social Security Number, Name และ Address:

EmployeeAddressTable

SSN

FirstName

LastName

Address

City

State

512687458

Joe

Smith

83

First Street

Howard

Ohio

758420012

Mary

Scott

842 Vine Ave.

Losantiville

Ohio

102254896

Sam

Jones

33 Elm St

.

Paris

New York

876512563

Sarah

Ackerman

440 U.S. 110

Upton

Michigan

จากของมูลข้างบน ถ้าเราต้องการแสดงที่อยู่ของพนักงานทุกคนโดยใช้คำสั้ง "Select" SQL Statement จะเป็นดังนี้ :

SELECT FirstName, LastName, Address, City, State

FROM EmployeeAddressTable;

จาก SQL Statement ข้างบนเราจะได้ผลดังนี้

First Name

Last Name

Address

City

State

Joe

Smith

83

First Street

Howard

Ohio

Mary

Scott

842 Vine Ave.

Losantiville

Ohio

Sam

Jones

33 Elm St

.

Paris

New York

Sarah

Ackerman

440 U.S. 110

Upton

Michigan

จาก Statement ข้างบน เราได้ เราได้ร้องขอข้อมูลทั้งหมดใน TableEmployeeAddress โดยที่เราระบุ Columns ที่เราต้องการไปด้วยใน Statement ดังนั้น ข้อมูลที่เราจะได้รับก็จะถูกจัดเรียงตาม Columns ที่เราส่งไปกับคำสั้ง SELECT นั้นเอง

คำสั่ง Select แบบทั่วไป

SELECT ColumnName1,ColumnName2, ...

FROM TableName;

นอกจากนี้ในคำสั่ง SQL เราสามารถที่จะขอข้อมูลทั้งหมดทุก Columns ใน Table โดยใช้เครื่องหมาย * แทนการพิมพ์ชื่อทุก Columns ในกรณีนี้ข้อมมูลก็จะถูกส่งมาโดยเรียงตามลำดับ Columns ที่ระบุไว้ใน โครงสร้างของ Tabls

SELECT * FROM TableName;

ในการใช้ คำสั่ง SELECT * นั้น เราควรใช้ในกรณีที่เราแน่ใจจริงๆว่าเราต้องการข้อมูลทั้งหมดในทุก Columns มิฉะนั้นแล้วจะทำให้ Database ทำงานเกินความจำเป็น อีกทั้งเวลาในการประมวลผลคำสั้งนานนั้นยังนานกว่า กราใช้ SELECT statement โดยระบุบ Column name อีกด้วย

อย่างไรก็ตาม ใน Database แต่ละตัวก็อาจจะมีโครงสร้างของคำสั่งที่แตกต่างไปบ้างเล็กน้อย แต่โดยทั่วไปแล้ว พื้นฐานคำสั่งหลัก ๆ ก็จะเหมือนๆ กัน ตามมาตราฐาน SQL92 (จะพูดถึงในบทหลังๆ) ดังนั้นทุกครั้งที่เราเริ่มศึกษา Database ตัวใหม่เราก็ควรทีจะอ่าน Help file โดยเฉพาะในส่วนของ Overview Product.

คำสั่ง SELECT แบบมีเงื่อนไข

ก่อนที่เราจะพูดถึงรายละเอียด มาดูตัวอย่าง Table และเครื่องหมายในการเปรียบเทียบก่อน นะครับ (ตัวอย่างนี้ใช้ในการทดลองเท่านั้นไม่มีส่วนเกี่ยวข้องกับผู้ใด):

EmployeeTable

EmployeeID

Salary

Benefits

Position

010

75000

15000

Manager

105

65000

15000

Manager

152

60000

15000

Manager

215

60000

12500

Manager

244

50000

12000

Staff

300

45000

10000

Staff

335

40000

10000

Staff

400

32000

7500

Entry-Level

441

28000

7500

Entry-Level

เครื่องหมายเปรียบเทียบใน SQL

โดยทั่วไปแล้ว เครื่องหมายเปรียบเทียบ ที่ใช้ใน Database หลัก ๆจะมีอยู่ด้วยกัน 6 ชนิด คือ

=

เท่ากับ

<> หรือ !=

ไม่เท่ากับ

<

น้อยกว่า

>

มากกว่า

<=

น้อยกว่าหรือเท่ากับ

>=

มากกว่าหรือเท่ากับ

ในภาษา SQL นั้นเราสามารถสร้างเงื่อนไขของข้อมูลที่เราต้องการได้ โดยใช้เครื่องหมายเปรียบเทียบที่เราต้องการร่วมกับ กับคำสั่ง WHEREโดยใส่ไว้ข้างหลังคำสั่ง SELECTเสมอ โดยเราจะเรียกในส่วนเงื่อนไขนี้ว่า Where clause คราวนี้เรามาดูตัวอย่างการเขียน Where clause เลยนะครับ

จาก Table ตัวอย่างข้างบน สมมุติว่าเราต้องการ ดูข้อมูล EmployeeID ของคนที่มี salary มากกว่า 50,000 SQL statement ก็จะเป็นดังนี้

SELECT EmployeeID

FROM EmployeeTable

WHERE SALARY >= 50000;

โดยเครื่องหมาย >= (มากกว่า หรือ เท่ากับ) จะดึงข้อมูลของ Records ที่มีข้อมูลใน column salary มากกว่า หรือ เท่ากับ 50000 เท่านั้น มาแสดงผล ดังนั้น ผลที่จะแสดงจะเป็นดังนี้:

EmployeeID

---------------------

010

105

152

215

244

นอกจากนี้เรายังสามารถใช้ คำสั่ง Where caluse กับ columns ที่เก็บข้อมูลเป็น Character (Char, Varchar หรือ Text) ได้อีกด้วย. ตัวอย่าง เราต้องการ แสดงผลข้อมูลของคนที่มีตำแหน่ง "Manager" SQL statement ก็จะเป็นดังนี้

SELECT EmployeeID

FROM EmployeeTable

WHERE POSITION = 'Manager';

อย่างไรก็ตาม ในการใช้เครื่องหมาย เปรียบเทียบกับ Text Columns นั้น โดยส่วนมากจะใช้ได้กับ เครื่องหมาย มากกว่า , เท่ากับ หรือ คำสั่ง Like (จะกล่าวต่อไปในบทหลัง ๆ) เท่านั้น โดยค่าของตัวแปรที่เราส่งเข้าไป จะต้องมีเครื่องหมาย Single Quote (') หรือ Double Quote (") ก่อนและหลังตัวแปรเสมอ

ข้อแนะนำ: ในการเขียน App. ที่ดีนั้นทุกครั้งที่เรามี คำสั่ง Where Clause ใน SQL statement นั้นเราควรจะเช็คดูว่า Column ที่เรานำมาเรียบเทียบนั้น มีการสร้าง Index แล้วหรือยัง ถ้าหากว่ายังไม่มี ตัวDatabase engine จะต้องทำการ สแกนหาข้อมูลใน Table ตั้งแต่ต้น ( เราเรียกว่า Table Scan) ซึ่งจะใช้เวลาในการประมวลผลหาข้อมูลนานกว่า ในกรณีที่เราได้สร้าง Index Column ไว้ที่ Columnsนั้น.


การใช้เครื่องหมายเปรียบเทียบมากกว่า 1ตัว : Compound Conditions

คำสั่ง AND เป็นคำสั่งในการเชื่อมเงื่อนไขในกรณีที่เราต้องการข้อมมูลที่มีเงือนไขมากกว่า 1 อย่าง โดยข้อมูลที่จะแสดงผลนั้น จะต้องแมทช์ กับเงือนไข ทั้งหมด ที่เราส่งไป ที่ Database ตัวอย่างเช่น, เราต้องการ ID ของคนที่มีรายได้มากกว่า 40,000 และมี Position เท่ากับ "Staff": SQL Statement จะเป็นดังนี้

SELECT EMPLOYEEID

FROM EMPLOYEETABLE

WHERE SALARY > 40000 AND POSITION = 'Staff';

คำสั่ง OR ป็นคำสั่งที่ใช้ในการเชื่อมเงือนไขเหมือนกับ คำสั่ง AND แต่จะต่างกันตรงที่ ข้อมูลที่จะได้นั้น สามารถเป็นข้อมูลที่ แมทช์กับ เงือนไขใด เงือนไขหนึ่ง ก็ได้ ตัวอย่างเช่น, เราต้องการ ID ของคนที่ได้ Salary น้อยกว่า 40,000 หรือ มี benefits น้อยกว่า 10,000 in benefits SQL Statement จะเป็นดังนี้

SELECT EMPLOYEEID

FROM EMPLOYEETABLE

WHERE SALARY < 40000 OR BENEFITS < 10000;

นอกจากนี้ คำสั่ง AND และคำสั่ง OR ยังสามารถใช้ด้วยกันได้อีกด้วย ตัวอย่างเช่น :

SELECT EMPLOYEEID

FROM EMPLOYEETABLE

WHERE POSITION = 'Manager' AND SALARY > 60000 OR BENEFIT > 12000;

จากชุดคำสั่งข้างบน อย่างแรก SQL จะค้นหาข้อมูลของคนที่มีตำแหน่ง ='Manager' จากนั้นก็จะใช้ผลลัพธ์นั้นหาต่อไปว่ามี records ใดบ้างที่มี ข้อมูลใน column SALARY มากกว่า 60000. แล้วจากนั้นจึง ไปหาข้อมูลใน ตารางใหม่ว่ามี Record ใดบ้างที่มี BENEFITS มากกว่า 12000 บ้าง จากนั้นจึงเอาผลลัพธ์ทั้งสองมารวมกับ. ดังนั้นข้อมูลทั้งหมดที่เราจะได้จาก คำสั่งข้างบนจะเป็นข้อมูล ของคนที่มีตำแหน่ง เป็น Manager และ ได้เงินเดือนมากกว่า 60000 นอกจากนี้ยังรวมไปถึงข้อมูลของคนอื่นที่ไม่ได้เป็น Manager แต่ได้ BENEFITS มากกว่า 12000 อีกด้วย

อย่างไรก็ตาม ในกรณีที่เราต้องการสร้างเงือนไขมากกว่า 1 เงื่อนไขเราควรที่จะใส่ เครื่องหมาย ( และ ) ก่อนและหลังเงือนไข นั้น เพื่อ ง่าย แก่การเข้าใจ และจะช่วยในการแก้ error ในอนาคต ด้วย ตัวอย่าง เช่น .

SELECT EMPLOYEEID

FROM EMPLOYEETABLE

WHERE (POSITION = 'Manager' AND SALARY > 60000) OR BENEFIT > 12000;<


คำสั่ง IN และ BETWEEN

คำสั่ง IN และ BETWEEN. เป็นคำสั่งที่ใช้ในการสร้างเงือนไขที่มากกว่า 1 เงือนไข ในคำสั่งเดียว ตัวอย่างเช่น ถ้าเราต้องการรายชื่อของ คนที่มี ตำแหน่ง เป็น "MANAGER" และ "STAFF" เราสามารถนำมารวมกัน โดยใช้ คำสั่ง IN เช่น

SELECT EMPLOYEEIDNO

FROM EMPLOYEETABLE

WHERE POSITION IN ('Manager','Staff');

ส่วนคำสั่ง BETWEEN จะคล้าย ๆๆกับ กับสั่ง IN แต่จะใช้ในการ สร้างเงือนไขกับ ย่านข้อมูลที่เป็นตัวเลข ตัวอย่างเช่น เราต้องการ ข้อมูลของคนที่มีเงินเดือนระหว่าง 30,000 - 50,000 เราสามารถเขียน ได้ดังนี้

SELECT EMPLOYEEIDNO

FROM EMPLOYEETABLE

WHERE SALARY BETWEEN 30000 AND 50000;

นอกจากนี้เรายังสามารถใช้คำสั่ง NOT ร่วมกับ IN หรือ BETWEEN เพื่อที่จะได้ผลตรงข้ามกัน ดังเช่น

SELECT EMPLOYEEIDNO

FROM EMPLOYEETABLE

WHERE SALARY NOT BETWEEN 30000 AND 50000;

Using LIKE

Look at the EmployeeStatisticsTable, and say you wanted to see all people whose last names started with "L"; try:

SELECT EMPLOYEEIDNO

FROM EMPLOYEEADDRESSTABLE

WHERE LASTNAME LIKE 'L%';

The percent sign (%) is used to represent any possible character (number, letter, or punctuation) or set of characters that might appear after the "L".

To find those people with LastName's ending in "L", use '%L', or if you wanted the "L" in the middle of the word, try '%L%'. The '%' can be used for any characters, in that relative position to the given characters.

NOT LIKE displays rows not fitting the given description. Other possibilities of using LIKE, or any of these discussed conditionals, are available, though it depends on what DBMS you are using; as usual, consult a manual or your system manager or administrator for the available features on your system, or just to make sure that what you are trying to do is available and allowed

Joins

In this section, we will only discuss inner joins, and equijoins, as in general, they are the most useful. Good database design suggests that each table lists data only about a single entity, and detailed information can be obtained in a relational database, by using additional tables, and by using a join.

First, take a look at these example tables:

AntiqueOwners

OwnerID

OwnerLastName

OwnerFirstName

01

Jones

Bill

02

Smith

Bob

15

Lawson

Patricia

21

Akins

Jane

50

Fowler

Sam

Orders

OwnerID

ItemDesired

02

Table

02

Desk

21

Chair

15

Mirror

Antiques

SellerID

BuyerID

Item

01

50

Bed

02

15

Table

15

02

Chair

21

50

Mirror

50

01

Desk

01

21

Cabinet

02

21

Coffee Table

15

50

Chair

01

15

Jewelry Box

02

21

Pottery

21

02

Bookcase

50

01

Plant Stand


Keys

First, let's discuss the concept of keys. A primary key is a column or set of columns that uniquely identifies the rest of the data in any given row. For example, in the AntiqueOwners table, the OwnerID column uniquely identifies that row. This means two things: no two rows can have the same OwnerID, and, even if two owners have the same first and last names, the OwnerID column ensures that the two owners will not be confused with each other, because the unique OwnerID column will be used throughout the database to track the owners, rather than the names.

A foreign key is a column in a table where that column is a primary key of another table, which means that any data in a foreign key column must have corresponding data in the other table where that column is the primary key. In DBMS-speak, this correspondence is known as referential integrity. For example, in the Antiques table, both the BuyerID and SellerID are foreign keys to the primary key of the AntiqueOwners table (OwnerID; for purposes of argument, one has to be an Antique Owner before one can buy or sell any items), as, in both tables, the ID rows are used to identify the owners or buyers and sellers, and that the OwnerID is the primary key of the AntiqueOwners table. In other words, all of this "ID" data is used to refer to the owners, buyers, or sellers of antiques, themselves, without having to use the actual names.

Performing a Join

The purpose of these keys is so that data can be related across tables, without having to repeat data in every table--this is the power of relational databases. For example, you can find the names of those who bought a chair without having to list the full name of the buyer in the Antiques table...you can get the name by relating those who bought a chair with the names in the AntiqueOwners table through the use of the OwnerID, which relates the data in the two tables. To find the names of those who bought a chair, use the following query:

SELECT OWNERLASTNAME, OWNERFIRSTNAME

FROM ANTIQUEOWNERS, ANTIQUES

WHERE BUYERID = OWNERID AND ITEM = 'Chair';

Note the following about this query...notice that both tables involved in the relation are listed in the FROM clause of the statement. In the WHERE clause, first notice that the ITEM = 'Chair' part restricts the listing to those who have bought (and in this example, thereby owns) a chair. Secondly, notice how the ID columns are related from one table to the next by use of the BUYERID = OWNERID clause. Only where ID's match across tables and the item purchased is a chair (because of the AND), will the names from the AntiqueOwners table be listed. Because the joining condition used an equal sign, this join is called an equijoin. The result of this query is two names: Smith, Bob & Fowler, Sam.

Dot notation refers to prefixing the table names to column names, to avoid ambiguity, as such:

SELECT ANTIQUEOWNERS.OWNERLASTNAME, ANTIQUEOWNERS.OWNERFIRSTNAME

FROM ANTIQUEOWNERS, ANTIQUES

WHERE ANTIQUES.BUYERID = ANTIQUEOWNERS.OWNERID

AND ANTIQUES.ITEM = 'Chair';

As the column names are different in each table, however, this wasn't necessary.

DISTINCT and Eliminating Duplicates

Let's say that you want to list the ID and names of only those people who have sold an antique. Obviously, you want a list where each seller is only listed once--you don't want to know how many antiques a person sold, just the fact that this person sold one (for counts, see the Aggregate Function section below). This means that you will need to tell SQL to eliminate duplicate sales rows, and just list each person only once. To do this, use the DISTINCT keyword.

First, we will need an equijoin to the AntiqueOwners table to get the detail data of the person's LastName and FirstName. However, keep in mind that since the SellerID column in the Antiques table is a foreign key to the AntiqueOwners table, a seller will only be listed if there is a row in the AntiqueOwners table listing the ID and names. We also want to eliminate multiple occurences of the SellerID in our listing, so we use DISTINCT on the column where the repeats may occur.

To throw in one more twist, we will also want the list alphabetized by LastName, then by FirstName (on a LastName tie), then by OwnerID (on a LastName and FirstName tie). Thus, we will use the ORDER BY clause:

SELECT DISTINCT SELLERID, OWNERLASTNAME, OWNERFIRSTNAME

FROM ANTIQUES, ANTIQUEOWNERS

WHERE SELLERID = OWNERID

ORDER BY OWNERLASTNAME, OWNERFIRSTNAME, OWNERID

In this example, since everyone has sold an item, we will get a listing of all of the owners, in alphabetical order by last name. For future reference (and in case anyone asks), this type of join is considered to be in the category of inner joins.

Aliases & In/Subqueries

In this section, we will talk about Aliases, In and the use of subqueries, and how these can be used in a 3-table example. First, look at this query which prints the last name of those owners who have placed an order and what the order is, only listing those orders which can be filled (that is, there is a buyer who owns that ordered item):

SELECT OWN.OWNERLASTNAME Last Name, ORD.ITEMDESIRED Item Ordered

FROM ORDERS ORD, ANTIQUEOWNERS OWN

WHERE ORD.OWNERID = OWN.OWNERID AND ORD.ITEMDESIRED IN

(SELECT ITEM FROM ANTIQUES);

This gives:

Last Name Item Ordered

---------------------

Smith Table

Smith Desk

Akins Chair

Lawson Mirror


There are several things to note about this query:

1. First, the "Last Name" and "Item Ordered" in the Select lines gives the headers on the report.

2. The OWN & ORD are aliases; these are new names for the two tables listed in the FROM clause that are used as prefixes for all dot notations of column names in the query (see above). This eliminates ambiguity, especially in the equijoin WHERE clause where both tables have the column named OwnerID, and the dot notation tells SQL that we are talking about two different OwnerID's from the two different tables.

3. Note that the Orders table is listed first in the FROM clause; this makes sure listing is done off of that table, and the AntiqueOwners table is only used for the detail information (Last Name).

4. Most importantly, the AND in the WHERE clause forces the In Subquery to be invoked ("= ANY" or "= SOME" are two equivalent uses of IN). What this does is, the subquery is performed, returning all of the Items owned from the Antiques table, as there is no WHERE clause. Then, for a row from the Orders table to be listed, the ItemDesired must be in that returned list of Items owned from the Antiques table, thus listing an item only if the order can be filled from another owner. You can think of it this way: the subquery returns a set of Items from which each ItemDesired in the Orders table is compared; the In condition is true only if the ItemDesired is in that returned set from the Antiques table.

5. Also notice, that in this case, that there happened to be an antique available for each one desired...obviously, that won't always be the case. In addition, notice that when the IN, "= ANY", or "= SOME" is used, that these keywords refer to any possible row matches, not column matches...that is, you cannot put multiple columns in the subquery Select clause, in an attempt to match the column in the outer Where clause to one of multiple possible column values in the subquery; only one column can be listed in the subquery, and the possible match comes from multiple row values in that one column, not vice-versa.

Whew! That's enough on the topic of complex SELECT queries for now. Now on to other SQL statements.


Miscellaneous SQL Statements

Aggregate Functions

I will discuss five important aggregate functions: SUM, AVG, MAX, MIN, and COUNT. They are called aggregate functions because they summarize the results of a query, rather than listing all of the rows.

· SUM () gives the total of all the rows, satisfying any conditions, of the given column, where the given column is numeric.

· AVG () gives the average of the given column.

· MAX () gives the largest figure in the given column.

· MIN () gives the smallest figure in the given column.

· COUNT(*) gives the number of rows satisfying the conditions.

Looking at the tables at the top of the document, let's look at three examples:

SELECT SUM(SALARY), AVG(SALARY)

FROM EMPLOYEESTATISTICSTABLE;

This query shows the total of all salaries in the table, and the average salary of all of the entries in the table.

SELECT MIN(BENEFITS)

FROM EMPLOYEESTATISTICSTABLE

WHERE POSITION = 'Manager';

This query gives the smallest figure of the Benefits column, of the employees who are Managers, which is 12500.

SELECT COUNT(*)

FROM EMPLOYEESTATISTICSTABLE

WHERE POSITION = 'Staff';

This query tells you how many employees have Staff status (3).

Views

In SQL, you might (check your DBA) have access to create views for yourself. What a view does is to allow you to assign the results of a query to a new, personal table, that you can use in other queries, where this new table is given the view name in your FROM clause. When you access a view, the query that is defined in your view creation statement is performed (generally), and the results of that query look just like another table in the query that you wrote invoking the view. For example, to create a view:

CREATE VIEW ANTVIEW AS SELECT ITEMDESIRED FROM ORDERS;

Now, write a query using this view as a table, where the table is just a listing of all Items Desired from the Orders table:

SELECT SELLERID

FROM ANTIQUES, ANTVIEW

WHERE ITEMDESIRED = ITEM;

This query shows all SellerID's from the Antiques table where the Item in that table happens to appear in the Antview view, which is just all of the Items Desired in the Orders table. The listing is generated by going through the Antique Items one-by-one until there's a match with the Antview view. Views can be used to restrict database access, as well as, in this case, simplify a complex query.

Creating New Tables

All tables within a database must be created at some point in time...let's see how we would create the Orders table:

CREATE TABLE ORDERS

(OWNERID INTEGER NOT NULL,

ITEMDESIRED CHAR(40) NOT NULL);

This statement gives the table name and tells the DBMS about each column in the table. Please note that this statement uses generic data types, and that the data types might be different, depending on what DBMS you are using. As usual, check local listings. Some common generic data types are:

· Char(x) - A column of characters, where x is a number designating the maximum number of characters allowed (maximum length) in the column.

· Integer - A column of whole numbers, positive or negative.

· Decimal(x, y) - A column of decimal numbers, where x is the maximum length in digits of the decimal numbers in this column, and y is the maximum number of digits allowed after the decimal point. The maximum (4,2) number would be 99.99.

· Date - A date column in a DBMS-specific format.

· Logical - A column that can hold only two values: TRUE or FALSE.

One other note, the NOT NULL means that the column must have a value in each row. If NULL was used, that column may be left empty in a given row.

Altering Tables

Let's add a column to the Antiques table to allow the entry of the price of a given Item:

ALTER TABLE ANTIQUES ADD (PRICE DECIMAL(8,2) NULL);

The data for this new column can be updated or inserted as shown later.

Adding Data

To insert rows into a table, do the following:

INSERT INTO ANTIQUES VALUES (21, 01, 'Ottoman', 200.00);

This inserts the data into the table, as a new row, column-by-column, in the pre-defined order. Instead, let's change the order and leave Price blank:

INSERT INTO ANTIQUES (BUYERID, SELLERID, ITEM)

VALUES (01, 21, 'Ottoman');

Deleting Data

Let's delete this new row back out of the database:

DELETE FROM ANTIQUES

WHERE ITEM = 'Ottoman';

But if there is another row that contains 'Ottoman', that row will be deleted also. Let's delete all rows (one, in this case) that contain the specific data we added before:

DELETE FROM ANTIQUES

WHERE ITEM = 'Ottoman' AND BUYERID = 01 AND SELLERID = 21;

Updating Data

Let's update a Price into a row that doesn't have a price listed yet:

UPDATE ANTIQUES SET PRICE = 500.00 WHERE ITEM = 'Chair';

This sets all Chair's Prices to 500.00. As shown above, more WHERE conditionals, using AND, must be used to limit the updating to more specific rows. Also, additional columns may be set by separating equal statements with commas.

Miscellaneous Topics

Indexes

Indexes allow a DBMS to access data quicker (please note: this feature is nonstandard/not available on all systems). The system creates this internal data structure (the index) which causes selection of rows, when the selection is based on indexed columns, to occur faster. This index tells the DBMS where a certain row is in the table given an indexed-column value, much like a book index tells you what page a given word appears. Let's create an index for the OwnerID in the AntiqueOwners column:

CREATE INDEX OID_IDX ON ANTIQUEOWNERS (OWNERID);

Now on the names:

CREATE INDEX NAME_IDX ON ANTIQUEOWNERS (OWNERLASTNAME, OWNERFIRSTNAME);

To get rid of an index, drop it:

DROP INDEX OID_IDX;

By the way, you can also "drop" a table, as well (careful!--that means that your table is deleted). In the second example, the index is kept on the two columns, aggregated together--strange behavior might occur in this situation...check the manual before performing such an operation.

Some DBMS's do not enforce primary keys; in other words, the uniqueness of a column is not enforced automatically. What that means is, if, for example, I tried to insert another row into the AntiqueOwners table with an OwnerID of 02, some systems will allow me to do that, even though, we do not, as that column is supposed to be unique to that table (every row value is supposed to be different). One way to get around that is to create a unique index on the column that we want to be a primary key, to force the system to enforce prohibition of duplicates:

CREATE UNIQUE INDEX OID_IDX ON ANTIQUEOWNERS (OWNERID);

GROUP BY & HAVING

One special use of GROUP BY is to associate an aggregate function (especially COUNT; counting the number of rows in each group) with groups of rows. First, assume that the Antiques table has the Price column, and each row has a value for that column. We want to see the price of the most expensive item bought by each owner. We have to tell SQL to group each owner's purchases, and tell us the maximum purchase price:

SELECT BUYERID, MAX(PRICE)

FROM ANTIQUES

GROUP BY BUYERID;

Now, say we only want to see the maximum purchase price if the purchase is over $1000, so we use the HAVING clause:

SELECT BUYERID, MAX(PRICE)

FROM ANTIQUES

GROUP BY BUYERID

HAVING PRICE > 1000;

More Subqueries

Another common usage of subqueries involve the use of logical operators to allow a Where condition to include the Select output of a subquery. First, list the buyers who purchased an expensive item (the Price of the item is $100 greater than the average price of all items purchased):

SELECT OWNERID

FROM ANTIQUES

WHERE PRICE > (SELECT AVG(PRICE) + 100 FROM ANTIQUES);

The subquery calculates the average Price, plus $100, and using that figure, an OwnerID is printed for every item costing over that figure. One could use DISTINCT OWNERID, to eliminate duplicates.

List the Last Names of those in the AntiqueOwners table, ONLY if they have bought an item:

SELECT OWNERLASTNAME

FROM ANTIQUEOWNERS

WHERE OWNERID = (SELECT DISTINCT BUYERID FROM ANTIQUES);

The subquery returns a list of buyers, and the Last Name is printed for an Antique Owner if and only if the Owner's ID appears in the subquery list (sometimes called a candidate list).


For an Update example, we know that the gentleman who bought the bookcase has the wrong First Name in the database...it should be John:

UPDATE ANTIQUEOWNERS

SET OWNERFIRSTNAME = 'John'

WHERE OWNERID =

(SELECT BUYERID

FROM ANTIQUES

WHERE ITEM = 'Bookcase');

First, the subquery finds the BuyerID for the person(s) who bought the Bookcase, then the outer query updates his First Name.

Remember this rule about subqueries: when you have a subquery as part of a WHERE condition, the Select clause in the subquery must have columns that match in number and type to those in the Where clause of the outer query. In other words, if you have "WHERE ColumnName = (SELECT...);", the Select must have only one column in it, to match the ColumnName in the outer Where clause, and they must match in type (both being integers, both being character strings, etc.).

EXISTS & ALL

EXISTS uses a subquery as a condition, where the condition is True if the subquery returns any rows, and False if the subquery does not return any rows; this is a nonintuitive feature with few unique uses. However, if a prospective customer wanted to see the list of Owners only if the shop dealt in Chairs, try:

SELECT OWNERFIRSTNAME, OWNERLASTNAME

FROM ANTIQUEOWNERS

WHERE EXISTS

(SELECT *

FROM ANTIQUES

WHERE ITEM = 'Chair');

If there are any Chairs in the Antiques column, the subquery would return a row or rows, making the EXISTS clause true, causing SQL to list the Antique Owners. If there had been no Chairs, no rows would have been returned by the outside query.

ALL is another unusual feature, as ALL queries can usually be done with different, and possibly simpler methods; let's take a look at an example query:

SELECT BUYERID, ITEM

FROM ANTIQUES

WHERE PRICE >= ALL

(SELECT PRICE

FROM ANTIQUES);

This will return the largest priced item (or more than one item if there is a tie), and its buyer. The subquery returns a list of all Prices in the Antiques table, and the outer query goes through each row of the Antiques table, and if its Price is greater than or equal to every (or ALL) Prices in the list, it is listed, giving the highest priced Item. The reason ">=" must be used is that the highest priced item will be equal to the highest price on the list, because this Item is in the Price list.

UNION & Outer Joins

There are occasions where you might want to see the results of multiple queries together, combining their output; use UNION. To merge the output of the following two queries, displaying the ID's of all Buyers, plus all those who have an Order placed:

SELECT BUYERID

FROM ANTIQUEOWNERS

UNION

SELECT OWNERID

FROM ORDERS;

Notice that SQL requires that the Select list (of columns) must match, column-by-column, in data type. In this case BuyerID and OwnerID are of the same data type (integer). Also notice that SQL does automatic duplicate elimination when using UNION (as if they were two "sets"); in single queries, you have to use DISTINCT.

The outer join is used when a join query is "united" with the rows not included in the join, and are especially useful if constant text "flags" are included. First, look at the query:

SELECT OWNERID, 'is in both Orders & Antiques'

FROM ORDERS, ANTIQUES

WHERE OWNERID = BUYERID

UNION

SELECT BUYERID, 'is in Antiques only'

FROM ANTIQUES

WHERE BUYERID NOT IN

(SELECT OWNERID

FROM ORDERS);

The first query does a join to list any owners who are in both tables, and putting a tag line after the ID repeating the quote. The UNION merges this list with the next list. The second list is generated by first listing those ID's not in the Orders table, thus generating a list of ID's excluded from the join query. Then, each row in the Antiques table is scanned, and if the BuyerID is not in this exclusion list, it is listed with its quoted tag. There might be an easier way to make this list, but it's difficult to generate the informational quoted strings of text.

This concept is useful in situations where a primary key is related to a foreign key, but the foreign key value for some primary keys is NULL. For example, in one table, the primary key is a salesperson, and in another table is customers, with their salesperson listed in the same row. However, if a salesperson has no customers, that person's name won't appear in the customer table. The outer join is used if the listing of all salespersons is to be printed, listed with their customers, whether the salesperson has a customer or not--that is, no customer is printed (a logical NULL value) if the salesperson has no customers, but is in the salespersons table. Otherwise, the salesperson will be listed with each customer.

Comment

smilebig smileopen-mounthed smileconfused smilesad smileangry smiletonguequestionembarrassedsurprised smilewinkdouble winkcry ???????????????   ??????????????????
smilebig smileopen-mounthed smileconfused smilesad smileangry smiletonguequestionembarrassedsurprised smilewinkdouble winkcry ???????????????

Tweet

โอ้ว ดีมากๆเลยครับ
ขอ add นะครับ

ผมเพิ่งศึกษา mysql อยู่พอดี

#1 By Ra-18th Angel on 2006-09-11 19:53

ว่าแต่ตอนหลังๆไม่แปลแล้วเหรอครับ มาengล้วนเลย...

เออ..ผมขอถามอย่างหนึ่งครับ ประเภทข้อมูลใน sql มีอะไรบ้างครับ แล้วแตกต่างกันยังไง

เช่น varchar text date datetime int ไรพวกนี้อ่ะครับ ผมไม่ค่อยเข้าใจความแตกต่างของแต่ละอันนัก

#2 By Ra-18th Angel on 2006-09-11 19:59

ถ้าจำไม่ผิด varchar จะเก็บข้อมูลตามจริง เช่น ออกแบบไว้ 10 แต่ข้อมูลใน Field มี 5 มันจะเก็บ 5 char แต่ถ้า เป็น char ออกแบบไว้ 10 มี 5 ก็เก็บ 10 โดยมี space อีก 5 ครับ

#3 By theera on 2006-09-11 23:12

ตอบแบบทั่วไปก็... varchar ก็เก็บแบบ variable length
char ก็เก็บแบบ Fixed length

#4 By theera on 2006-09-11 23:12

Varchar เขาบอกว่าเอาไว้ใช้กับข้อมูลตัวอักษรที่ไม่มีขนาดแน่นอนไม่สามารถกำหนดได้ตรงตัว (ได้แต่เผื่อไว้) แต่ Char คือข้อมูลตัวอักษรที่มีขนาดแน่นอน

#5 By theera on 2006-09-11 23:13

ค่าที่เป็น Date/Time หรือ DateTime รวมทั้ง TimeStamp เป็นค่าของ ตัวเลขขนาด 32 bits (4 Bytes)
โดยทุก Database ที่อ้างถึงชนิดข้อมูลนี้จะมีค่าเทียบเท่ากันรวมถึงภาษาโปรแกรมมิ่งต่าง ๆ ด้วย

ขนาดของชนิดข้อมูลทั้งหมดมีค่าเท่ากันหมด
Date = Time = DateTime = TimeStamp = 4 Bytes

รายละเอียดเพิ่มเติมดูได้ในเรื่อง Julian Date

ชนิดข้อมูลใน Firebird ที่เป็น วัน/เวลามีดังนี้
DATE = เก็บ วัน เดือน ปี เช่น 12/12/2001
TIME = เก็บ เวลา (ชั่วโมง นาที วินาที เศษของวินาที) เช่น 10:50:10
TIMESTAMP = เก็บ วัน และ เวลารวมกัน เช่น 12/12/2001 10:50:10

Firebird มี funtion ที่ใช้ในการดึงวันเวลา ดังนี้
CURRENT_DATE = วันที่ปัจจุบัน
CURRENT_TIME = เวลาปัจจุบัน
CURRENT_TIMESTAMP = วันและเวลาที่ปัจจุบัน
นอกจากนี้ยังมี
'Now' = วันที่ปัจจุบัน / เวลาปัจจุบัน / วันและเวลาที่ปัจจุบัน
'Today' = วันที่ปัจจุบัน
'Yesterday' = วันที่เมื่อวาน
'Tomorrow' = วันที่พรุ่งนี้

การอ้างวันที่เป็น String
'1/14/2001' = วันที่ 14 เดือน มกราคม 2001 (2544)
'9/25/2001' = วันที่ 25 เดือน กันยายน 2001 (2544)
ให้จำไว้ว่าระบุเดือนก่อน เสมอ จะเหมือนการเขียนวันที่ในแบบ American เช่น January 25, 2001 หรือ Febuary 14, 2005 เป็นต้น
แต่ Firbird ให้ทางเลือกมากกว่านั้นในการกำหนดวันที่แบบ Text
'2001-01-14' = วันที่ 14 เดือน มกราคม 2001 (2544)
'2001-09-25' = วันที่ 25 เดือน กันยายน 2001 (2544)
หรือ
'January 14, 2001' = 'January 14 2001' = 'Jan 14, 2001' =
'14 January 2001' = '14 Jan 2001' = '2001, January 14' =
'2001, Jan 14' = วันที่ 14 เดือน มกราคม 2001 (2544)

ข้อสรุปง่ายคือ หากเราสามารถระบุให้ชัดเจนได้ว่าข้อความที่ระบุเป็นวันที่อะไร Firebird จะแปลงข้อความนั้น ๆ ไปเป็น Date ให้อย่างถูกต้องเอง

ข้อสังเกตุ
'2001 Jan 14' = วันที่ 14 เดือน มกราคม 2001 (2544)
หรือ
'2001 Feb 28' = วันที่ 28 เดือน กุมภาพันธ์ 2001 (2544)
Firebird รู้ว่า หมายถึงวันไหนทั้งที่ เราระบุปีก่อน แต่กรณีนี้
'28/2/2001' จะ Error เพราะ เดือนที่ 28 ไม่มี
พูดง่ายคือรูปแบบ 'xx/xx/xxxx' = 'mm/dd/yyyy' เท่านั้น

เรายังสามารถบวกลบวันที่ได้โดยจะเป็นการบวกหรือลบวัน
เช่น
CURRENT_DATE - 1 = 'Yesterday'
'Yesterday' + 2 = 'Tomorrow'
'January 28, 2005' - 30 = 'December 29, 2004'

Code:
SELECT
CAST('January 28, 2005'AS DATE) - 30,
CAST('Yesterday'AS DATE) +2,
FROM
RDB$DATABASE

SELECT
A.DATE_FIELD - 1,
A.DATE_FIELD - 1
FROM
TABLEA A

UPDATE TABLEA
SET DATE_FIELD = 'Today'


นอกจากนี้ เรายังสามารถอ่านค่า วัน เดือน ปี ชั่วโมง นาที และ วินาที จาก ค่าที่เป็น DateTime ได้
โดยใช้ function EXTRACT
รูปแบบ EXTRACT(<YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | YEARDAY> FROM <DATETIMEVALUE>)
ตัวอย่างCode:

EXTRACT(DAY FROM 'NOW')
EXTRACT(DAY FROM CURRENT_DATE)
EXTRACT(MONTH FROM ADATEFIELD)

SELECT
CURRENT_DATE,
EXTRACT(DAY FROM CURRENT_DATE),
EXTRACT(MONTH FROM CURRENT_DATE),
EXTRACT(YEAR FROM CURRENT_DATE),
EXTRACT(YEARDAY FROM CURRENT_DATE),
CURRENT_TIME,
EXTRACT(HOUR FROM CURRENT_TIME),
EXTRACT(MINUTE FROM CURRENT_TIME),
EXTRACT(SECOND FROM CURRENT_TIME)
-- EXTRACT(SECOND FROM 'NOW') AS CUR_SECOND
FROM
RDB$DATABASE;

/* เลือกข้อที่ฟิลด์ DATE อยู่ในเดือน 2 ปี 2005 */
SELECT
*
FROM
TABLEA A
WHERE
(EXTRACT(MONTH FROM A.DATE) = 2) AND
(EXTRACT(YEAR FROM A.DATE) = 2005)

/* หาค่าวันที่สุดท้ายของเดือนกุมภาพันธ์ */
SELECT
'Max day for Febuary of year ' || EXTRACT(YEAR FROM CURRENT_DATE) || ' is ' ||
CASE EXTRACT(MONTH FROM (CURRENT_DATE - EXTRACT(YEARDAY FROM CURRENT_DATE) + 59))
WHEN 2 THEN 29
ELSE 28
END
FROM
RDB$DATABASE;

#6 By theera on 2006-09-11 23:15

:: Function Int และ Function Fix ::

ใช้ในการปัดเศษของตัวเลขที่กำหนด มีรูปแบบดังนี้



รูปแบบ Int(number)
Fix(number)

number หมายถึงตัวเลขที่ต้องการปัดเศษ


ตัวอย่างการใช้งาน Function Int และ Function Fix คำสั่ง/ฟังก์ชั่น ความหมาย/ผลที่ได้
MyNumber = Int(99.8) MyNumber = "99"
MyNumber = Fix(99.2) MyNumber = "99"
MyNumber = Int(-99.8) MyNumber = "-100"
MyNumber = Fix(-99.8) MyNumber = "-99"
MyNumber = Int(-99.2) MyNumber = "-100"
MyNumber = Fix(-99.2) MyNumber = "-99"

#7 By theera on 2006-09-11 23:25

#8 By ววว (58.10.33.106) on 2007-09-13 15:46

เพิ่มสีที่เมนู ให้อ่านชัดกว่านี้ได้ไม๊ค่ะ

#9 By (58.8.176.20) on 2008-02-01 13:59

#10 By (61.91.225.37) on 2008-03-08 11:10

"thank you very much"..

#11 By moonoy (124.120.159.2) on 2008-05-11 11:42

#12 By (202.143.162.126) on 2008-08-13 10:13

ขอบคุณสำหรับข้อมูลดี ๆ big smile

#13 By Anny (203.170.206.188) on 2008-08-26 14:22

ขอบคุณมากค่ะ
กำลังทำโปรเจคพอดีแล้วลืมคำสั่งบ้างคำสั่ง

#14 By t (222.123.243.164) on 2008-09-10 22:55

อยากทราบว่า trigger คืออะไรคะพอดีพึ่งศึกษาเรื่องนี้

#15 By www (202.176.122.27) on 2008-10-13 00:26

ขอคำแนะนำและความช่วยเหลือครับ
ผมอยากทราบว่า sql สามารถเขียนคำส่ังให้ สามารถ generate ข้อมูลหลายๆ ตัวให้เป็นตัวเลขได้ไหมครับ เช่น ผมมีข้อมูลของหมู่บ้าน, เมือง,ประเทศ สมมุติมี 3 ตัวนะครับ แล้วผมกำหนดให้ข้อมูลแต่ละอย่างเป็นตัวเลข แล้วอยาก generate ออกมาเป็นตัวเลขเรียงกันคือ หมู่บ้าน-เมือง-ประเทศ เช่น 001-02-001 ประมาณนี้ครับ
ไม่ทราบว่าสามารถทำได้ไหมครับ
ขอบคุณครับ

#16 By leon (81.56.29.52) on 2008-11-17 06:20

ขอบคุณครับผม อ. ผมกำลังเทสขั้นนี้อยู๋เลย
กำลังงงเลยครับ ช่วยชี้แนะด้วยน่ะครับbig smile
ขอบคุณครับ
....

#18 By (203.154.27.131) on 2009-01-09 11:13

Good ครับผม ทำให้เข้าใจได้ง่ายขึ้น
ขอบคุณนะครับผม

#19 By kmojo (124.157.222.183) on 2009-01-23 00:04


Good
ดีมากครับ ผม คิดว่าน่าจะเป็นประโยชน์สำหรับคนที่กำลังศึกษาอยู่ อย่างผม นะครับ ยังไงก็ขอขอบพระคุณ มากมายเลยครับผม

#20 By kmojo (124.157.222.183) on 2009-01-25 21:39

อธิบายดีมากเลยครับ ขอชื่นชมกั๊บป๋ม

#21 By pop (117.47.125.103) on 2009-01-25 21:57

big smile open-mounthed smile confused smile sad smile angry smile tongue question embarrassed

#22 By (118.172.68.192) on 2009-01-27 15:28

ไม่เห็นบอก แหล่งอ้างอิงเลย...(ขอหน่อยนะ..เดียวผมจะศึกษาต่อ เอาต้นฉบับก็ได้ ลิงค์เว็บก็ดี จะแปลเอง หรือจะให้แปลให้ก็ได้ แต่คงไม่ดีหรอก ^^ because may not match reality อิอิ ช่วยหน่อย ๆ คุณ Admin ที่)

paitoon_koon@hotmail.com

#23 By paitoon srisomporn (202.29.24.193) on 2009-02-21 11:35

ขอบคุณมากครับ

#24 By dd (58.8.80.148) on 2009-05-12 15:36

ขอบคุณคราฟฟฟฟฟ

#25 By buu (114.128.17.242) on 2009-08-11 15:38

ขอบคุณมากครับ ได้มีอะไรดีๆ ก็ช่วยกันโพสต์หน่อยมากครับ จะไม่ลืมพระคุณ

#26 By lnw (58.9.49.41) on 2009-08-12 12:02

ถ้าจำไม่ผิด varchar จะเก็บข้อมูลตามจริง เช่น ออกแบบไว้ 10 แต่ข้อมูลใน Field มี 5 มันจะเก็บ 5 char แต่ถ้า เป็น char ออกแบบไว้ 10 มี 5 ก็เก็บ 10 โดยมี space อีก 5 ครับ

--------------

varchar จากด้านบนก็ถูกนะครับแต่

varchar จะเก็บตามที่มีอยู่ใน field และก็+เพิ่ม 1
ทุกครั้งครับ

#27 By l3araku (112.142.8.233) on 2009-09-29 07:06

ดีคับถ้าไม่กวน

#28 By 789521 (61.19.22.131) on 2009-10-26 12:30

พอดีผมอยากรู้คำสั่งแบบ

เชื่อมตาราง
เช่น เอาข้อมูลจากตาราง a1 ชื่อ ฟิล aaaa
มา jion กลับ ตาราง B1 ชื่อฟิล bbbb
มา jion กลับ ตาราง c1 ชื่อฟิล cccc
แล้วได้จำนวนออกมาครับ
ข้อมูลพอไหมครับ

#29 By ผู้สงสัย (124.157.216.79) on 2010-03-05 11:28

9+8598

#30 By 789789789 (202.29.69.22) on 2010-09-01 18:01

ขอบคุณมากๆเลยครับ


big smile

#31 By รักษ์ปักษ์ใต้ (202.12.74.249) on 2010-09-03 16:44

กำลังหาอยู่เลย ขอบคุณมากๆ

#32 By nitinun (113.53.235.166) on 2011-02-09 08:59

If you are willing to buy a car, you will have to receive the <a href="http://goodfinance-blog.com">loan</a>. Moreover, my mother commonly utilizes a secured loan, which is the most useful.

#33 By DoyleElisabeth20 (91.212.226.143) on 2011-11-09 06:21

How do you think, is it normal if I ask experts: " Write My Essay for Me "? What custom writing corporation will you suggest?

#34 By Essays for Sale (94.242.214.6) on 2011-12-09 02:17

I guess, there only people see the new colorful fact about this good topic so, this should be more plain for people to buy research paper or buy essays at the experienced essays writing companies.

#35 By buy term paper (94.242.214.6) on 2011-12-09 14:44

At our custom writing company, we are able to propose quite affordable costs for custom research papers. Our main goal is to clients' fulfillment.

#36 By high quality custom writing services (31.184.236.16) on 2011-12-24 09:49

Good custom term paper usually includes useful arguments, clearlywritten thoughts, and supportive testify. You can be sureyou are getting satisfactory quality of work useable from skillful essay writing service.

#37 By essay writing service (31.184.238.21) on 2012-01-09 01:30

ดีมากเลยครับ เข้าใจขึ้นเยอะ

http://www.tiretruckcenter.com

#38 By adthaphon (110.168.147.153) on 2012-03-03 09:03