📄 subqueries.htm
字号:
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:10pt; color:#000000">SELECT Venue FROM venues WHERE VenueNo IN (SELECT VenueNo FROM events)
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:10pt; color:#000000"></span></td></tr></table><span style="font-family:Courier New; font-size:10pt; color:#000000"></span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
<table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000"></span></span><span style="font-family:Courier New; font-size:8pt; color:#000000"><span style="font-family:Courier New; font-size:8pt; color:#000080"><i>/* This query returns events that will occur on venues with capacity lower than in venues 3 and 4.*/</i></span></span><span style="font-family:Courier New; font-size:10pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:10pt; color:#000000">SELECT e.Event_Name, v.Capacity FROM events e, venues v WHERE e.VenueNo=v.VenueNo AND
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:10pt; color:#000000">v.Capacity < ALL (SELECT Capacity FROM venues WHERE VenueNo BETWEEN 3 AND 4)</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"></span></td></tr></table><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"></span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
<table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000"></span></span><span style="font-family:Courier New; font-size:8pt; color:#000000"><span style="font-family:Courier New; font-size:8pt; color:#000080"><i>/*This query returns events that will occur on venues with capacity more than in venue 3 or 4.*/</i></span></span><span style="font-family:Courier New; font-size:10pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:10pt; color:#000000">SELECT e.Event_Name, v.Capacity FROM events e, venues v WHERE e.VenueNo=v.VenueNo AND
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:10pt; color:#000000">v.Capacity > ANY (SELECT Capacity FROM venues WHERE VenueNo BETWEEN 3 AND 4)
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:10pt; color:#000000"></span></td></tr></table><span style="font-family:Courier New; font-size:10pt; color:#000000"></span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
<table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000"></span></span><span style="font-family:Courier New; font-size:8pt; color:#000000"><span style="font-family:Courier New; font-size:8pt; color:#000080"><i>/* This query shows all venues where only one event will occur.*/</i></span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:8pt; color:#000000"></span><span style="font-family:Courier New; font-size:10pt; color:#000000">SELECT Venue FROM venues v WHERE 1 = (SELECT COUNT(*) FROM events WHERE VenueNo=v.VenueNo)
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:10pt; color:#000000"></span></td></tr></table><span style="font-family:Courier New; font-size:10pt; color:#000000"></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
<b>Subqueries in the </b><b>SELECT </b><b>fields list
<br>
</b></span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">A subquery works like an advanced expression.
<br>
</span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><b>Example:</b>
<br>
</span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
<table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000"></span></span><span style="font-family:Courier New; font-size:8pt; color:#000000"><span style="font-family:Courier New; font-size:8pt; color:#000080"><i>/* For each entry in table "Venues" the query counts quantity of events from table "Events" and places this number in a column named "EventsCount".*/</i></span></span><span style="font-family:Courier New; font-size:10pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:10pt; color:#000000">SELECT Venue, (SELECT COUNT(*) FROM Events WHERE VenueNo = v.VenueNo) EventsCount FROM Venues v ORDER BY EventsCount DESC</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"></span></td></tr></table><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
<b>Subqueries in the </b><b>UPDATE </b><b>statement
<br>
</b></span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000"><b>
<br>
</b></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">A subquery works like an advanced expression allowing to assign data from another table.
<br>
</span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000"><b>
<br>
</b></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><b>Examples:
<br>
</b></span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000"><b>
<br>
</b><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000"></span></span><span style="font-family:Courier New; font-size:8pt; color:#000000"><span style="font-family:Courier New; font-size:8pt; color:#000080"><i>/* The query works as follows. First, only those entries are picked that have "Ticket_price" 5 of less. Then corresponding venue is taken, its capacity is multiplied by two and set as the new ticket price. Notice that NULL fields are not affected.*/</i></span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">UPDATE Events e SET Ticket_price = (SELECT Capacity FROM Venues WHERE VenueNo=e.VenueNo)*2 WHERE Ticket_price <= 5
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">UPDATE Orders SET (ShipToAddr1,ShipToAddr2) = (SELECT Addr1, Addr2 FROM Customer WHERE CustNo=Orders.CustNo) WHERE CustNo IN (1221, 2156)
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"></span></td></tr></table><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
<br>
</span></span>
<!--#include virtual="/inc/footer.php"-->
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -