SQL VIEW£¨ÊÓͼ£©
ÊÓͼÊÇ¿ÉÊÓ»¯µÄ±í¡£
±¾Õ½²½âÈçºÎ´´½¨¡¢¸üкÍɾ³ýÊÓͼ¡£
SQL CREATE VIEW Óï¾ä
ʲôÊÇÊÓͼ£¿
ÔÚ SQL ÖУ¬ÊÓͼÊÇ»ùÓÚ SQL Óï¾äµÄ½á¹û¼¯µÄ¿ÉÊÓ»¯µÄ±í¡£
ÊÓͼ°üº¬ÐкÍÁУ¬¾ÍÏñÒ»¸öÕæʵµÄ±í¡£ÊÓͼÖеÄ×ֶξÍÊÇÀ´×ÔÒ»¸ö»ò¶à¸öÊý¾Ý¿âÖеÄÕæʵµÄ±íÖеÄ×ֶΡ£ÎÒÃÇ¿ÉÒÔÏòÊÓͼÌí¼Ó SQL º¯Êý¡¢WHERE ÒÔ¼° JOIN Óï¾ä£¬ÎÒÃÇÒ²¿ÉÒÔÌá½»Êý¾Ý£¬¾ÍÏñÕâЩÀ´×ÔÓÚij¸öµ¥Ò»µÄ±í¡£
×¢ÊÍ£ºÊý¾Ý¿âµÄÉè¼ÆºÍ½á¹¹²»»áÊܵ½ÊÓͼÖеĺ¯Êý¡¢where »ò join Óï¾äµÄÓ°Ïì¡£
SQL CREATE VIEW Óï·¨
CREATE VIEW view_name AS
SELECT column_name(s)
from table_name
WHERE condition
×¢ÊÍ£ºÊÓͼ×ÜÊÇÏÔʾ×î½üµÄÊý¾Ý¡£Ã¿µ±Óû§²éѯÊÓͼʱ£¬Êý¾Ý¿âÒýÇæͨ¹ýʹÓà SQL Óï¾äÀ´Öؽ¨Êý¾Ý¡£
SQL CREATE VIEW ʵÀý
¿ÉÒÔ´Óij¸ö²éѯÄÚ²¿¡¢Ä³¸ö´æ´¢¹ý³ÌÄÚ²¿£¬»òÕß´ÓÁíÒ»¸öÊÓͼÄÚ²¿À´Ê¹ÓÃÊÓͼ¡£Í¨¹ýÏòÊÓͼÌí¼Óº¯Êý¡¢join µÈµÈ£¬ÎÒÃÇ¿ÉÒÔÏòÓû§¾«È·µØÌá½»ÎÒÃÇÏ£ÍûÌá½»µÄÊý¾Ý¡£
Ñù±¾Êý¾Ý¿â Northwind ÓµÓÐһЩ±»Ä¬ÈÏ°²×°µÄÊÓͼ¡£ÊÓͼ "Current Product List" »á´Ó Products ±íÁгöËùÓÐÕýÔÚʹÓõIJúÆ·¡£Õâ¸öÊÓͼʹÓÃÏÂÁÐ SQL ´´½¨£º
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
from Products
WHERE Discontinued=No
ÎÒÃÇ¿ÉÒÔ²éѯÉÏÃæÕâ¸öÊÓͼ£º
SELECT * from [Current Product List]
Northwind Ñù±¾Êý¾Ý¿âµÄÁíÒ»¸öÊÓͼ»áÑ¡È¡ Products ±íÖÐËùÓе¥Î»¼Û¸ñ¸ßÓÚƽ¾ùµ¥Î»¼Û¸ñµÄ²úÆ·£º
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
from Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) from Products)
ÎÒÃÇ¿ÉÒÔÏñÕâÑù²éѯÉÏÃæÕâ¸öÊÓͼ£º
SELECT * from [Products Above Average Price]
ÁíÒ»¸öÀ´×Ô Northwind Êý¾Ý¿âµÄÊÓͼʵÀý»á¼ÆËãÔÚ 1997 Äêÿ¸öÖÖÀàµÄÏúÊÛ×ÜÊý¡£Çë×¢Ò⣬Õâ¸öÊÓͼ»á´ÓÁíÒ»¸öÃûΪ "Product Sales for 1997" µÄÊÓͼÄÇÀïÑ¡È¡Êý¾Ý£º
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
from [Product Sales for 1997]
GROUP BY CategoryName
ÎÒÃÇ¿ÉÒÔÏñÕâÑù²éѯÉÏÃæÕâ¸öÊÓͼ£º
SELECT * from [Category Sales For 1997]
ÎÒÃÇÒ²¿ÉÒÔÏò²éѯÌí¼ÓÌõ¼þ¡£ÏÖÔÚ£¬ÎÒÃǽö½öÐèÒª²é¿´ "Beverages" ÀàµÄÈ«²¿ÏúÁ¿£º
SELECT * from [Category Sales For 1997]
WHERE CategoryName='Beverages'
SQL ¸üÐÂÊÓͼ
Äú¿ÉÒÔʹÓÃÏÂÃæµÄÓï·¨À´¸üÐÂÊÓÍ
Ïà¹ØÎĵµ£º
MagentoµÄModels
ºÍCollection
ºÜÇ¿´ó£¬Ê¹ÓÃËüÃÇ¿ÉÒԺܷ½±ãµÄ²éѯºÍ²Ù×÷Êý¾Ý¿â¡£µ«ÊÇÓÐЩ³¡ºÏ£¬ÒòΪһЩÌØÊâÐèÇó»ò¶ÔMagentoµÄÁ˽ⲻ¹»É¿ÉÄÜ»áÐèÒª×Ô¼ºÊÖдSQLÓï¾äÀ´²éѯºÍ²Ù×÷Êý¾Ý¿â¡£ÒÔÏ·ֱðÊǶÁдÊý¾Ý¿âµÄ´úÂë¡£
// For Read
// fetch read database connection that is used in Mage_Core modul ......
¡¡¡¡ÔÚÕâÒ»²½ÖУ¬ÐèÒªÖ¸¶¨ÓαêµÄÊôÐԺ͸ù¾ÝÒªÇó²úÉúµÄ½á¹û¼¯¡£ÓÐÁ½ÖÖ·½·¨¿ÉÒÔÖ¸¶¨Ò»¸öÓαꡣ
¡¡¡¡ÐÎʽ1£¨ANSI 92£©
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE ][OF column_list]}]
¡¡¡¡ÐÎʽ2
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD ......
Ò»¡¢ ÏȽéÉÜÒ»ÏÂoracleµÄSGA£ºÊý¾Ý¿âµÄϵͳȫ¾ÖÇø£¬SGAÖ÷ÒªÓÉÈý²¿·Ö¹¹³É£º¹²Ïí³Ø¡¢Êý¾Ý»º³åÇø¡¢ÈÕÖ¾»º³åÇø¡£ 1¡¢¹²Ïí³ØÓÖÓÉÁ½²¿·Ö¹¹³É£º¹²ÏíSQLÇøºÍÊý¾Ý×ֵ仺³åÇø¡£¹²ÏíSQLÇøרÃÅ´æ·ÅÓû§SQLÃüÁoracleʹÓÃ×î½ü×îÉÙʹÓõÈÓÅÏȼ¶Ëã·¨À´¸üи²¸Ç£»Êý¾Ý×ֵ仺³åÇø£¨library cache£©´æ·ÅÊý¾Ý¿âÔËÐеĶ¯Ì¬ÐÅÏ¢¡£Êý¾Ý¿âÔËÐÐÒ» ......
IF NOT EXISTS (SELECT * from ::fn_listextendedproperty
(N'MS_Description' , N'SCHEMA',N'dbo',
N'TABLE',N'±íÃû', N'COLUMN',N'ÁÐÃû'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'˵Ã÷ÄÚÈÝ' , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'±íà ......
ǶÈëSQLÓïÑÔ:
ÎÒ½«Ò»¸ösqlÁ´½Óµ½Êý¾Ý¿â£¬¸ÃÊý¾Ý¿âÃûΪmaster(ϵͳÊý¾Ý¿â)
then ¸øÊý¾Ý¿âÌí¼ÓÐÂ±í£¬½¨Á¢Èý¸ökey£ºuserid(int),name(char(10)),password(char(10))
ÔÚ´°ÌåÖÐŪÈý¸ötextbox¿Ø¼þ£¬·Ö±ð¶¨ÒåNameÊôÐÔΪuserid,name,password;
ÔÙÀ´Ò»¸öbutton
ÒÔÏÂÊÇForm1.csÖеĴúÂë
using System;
using System.Coll ......