درج، حذف، به روز رسانی رکوردها در پایگاه داده. نحوه ارسال پرس و جو به پایگاه داده با استفاده از VBA Access ایجاد کوئری های sql در همنام های دسترسی
کار آزمایشگاهی شماره 1
SQL: DATA EXTRACT - فرمانانتخاب کنید
هدف کار:
- با دستورات SQL آشنا شوید.
- یاد بگیرید که چگونه با استفاده از دستور SELECT کوئری های ساده SQL در Access ایجاد کنید.
· استفاده از عملگرهای IN، BETWEEN، LIKE، IS NULL.
ورزش№1. یک پرس و جو ایجاد کنید تا در حالت SQL همه مقادیر فیلدهای FIRST NAME و LAST NAME را از جدول STUDENTS انتخاب کنید.
نام، نام خانوادگی را انتخاب کنید
از دانش آموزان؛
ورزش№2 . یک پرس و جو ایجاد کنید تا در حالت SQL همه ستون های جدول STUDENTS را انتخاب کنید.
انتخاب کنید *
از دانش آموزان؛
وظیفه شماره 3.یک پرس و جو ایجاد کنید تا در حالت SQL نام شهرهایی را که دانش آموزان در آن زندگی می کنند انتخاب کنید، اطلاعات مربوط به آنها در جدول PERSONAL DATA وجود دارد.
شهر متمایز را انتخاب کنید
از [اطلاعات شخصی]؛
وظیفه شماره 4.یک کوئری انتخابی در حالت SQL ایجاد کنید که نام همه دانش آموزان با نام خانوادگی Ivanov را بازیابی می کند، اطلاعات مربوط به آن در جدول STUDENTS وجود دارد.
نام خانوادگی، نام خانوادگی را انتخاب کنید
از دانش آموزان
WHERE LAST NAME="Ivanov";
وظیفه شماره 5. یک عبارت انتخابی در حالت SQL ایجاد کنید تا نام و نام خانوادگی دانش آموزانی که در گروه UIT-22 در حال تحصیل هستند را در یک فرم بودجه آموزشی به دست آورید.
نام خانوادگی، نام خانوادگی را انتخاب کنید
از دانش آموزان
WHERE GROUP="UIT-22" AND BUDGET=true;
وظیفه شماره 6. یک پرس و جو در حالت SQL ایجاد کنید. برای نمونه ای از جدول EXAMINATION اطلاعات دانش آموزانی که فقط نمرات 4 و 5 دارند.
انتخاب کنید *
از جانب [تغییر دادنامتحانات]
جایی کهمقطع تحصیلیIN(4,5);
وظیفه شماره 7.یک حالت zanpoc و SQL ایجاد کنید تا اطلاعات دانش آموزانی را انتخاب کنید که در موضوع IOSU نمره امتحانی 3 دارند.
انتخاب کنید *
از جانب [تغییر دادنامتحانات]
جایی کهآیتم=" IOSU"ومقطع تحصیلینه در (4،5)؛
وظیفه شماره 8.برای انتخاب رکورد برای مواردی که ساعت آنها بین 100 تا 130 است، یک پرس و جو در حالت SQL ایجاد کنید.
انتخاب کنید *
از جانبموارد
جایی کهتماشا کردنبین 100 تا 130؛
وظیفه شماره 9.یک پرسش در حالت SQL ایجاد کنید تا از جدول STUDENTS اطلاعات دانش آموزانی را انتخاب کنید که نام خانوادگی آنها، به عنوان مثال، با حرف "C" شروع می شود.
انتخاب کنید *
از جانبدانش آموزان
جایی کهنام خانوادگیپسندیدن"با*";
نتیجه:در حین کار آزمایشگاهیبا دستورالعمل های SQL آشنا شد، یاد گرفت که چگونه با استفاده از دستور SELECT با استفاده از عملگرهای IN، BETWEEN، LIKE کوئری های ساده SQL در Access ایجاد کند.
این درس به پرس و جوهای SQLبه پایگاه داده در دسترسی به VBA. ما نحوه ایجاد کوئری های INSERT, UPDATE, DELETE در پایگاه داده در VBA را بررسی خواهیم کرد و همچنین یاد خواهیم گرفت که چگونه یک مقدار خاص از یک کوئری SELECT بدست آوریم.
کسانی که در دسترسی به VBAدر حین کار با پایگاه داده سرور SQL، اغلب آنها با یک کار ساده و ضروری مانند ارسال یک پرس و جو SQL به یک پایگاه داده مواجه می شوند، خواه INSERT، UPDATE یا یک پرس و جو ساده SQL SELECT. و از آنجایی که ما برنامه نویسان مبتدی هستیم، باید بتوانیم این کار را نیز انجام دهیم، بنابراین امروز همین کار را انجام خواهیم داد.
ما قبلاً به موضوع دریافت داده از یک سرور SQL پرداخته ایم که در آن کدهایی را در VBA برای به دست آوردن این داده ها نوشتیم ، به عنوان مثال در مقاله بارگذاری داده ها در یک فایل متنی از MSSql 2008 ، یا روی آن نیز یک آپلود داده‌ها از Access به یک الگوی Word و Excel کم است. اما به هر شکلی، ما سطحی به این موضوع نگاه کردیم، و امروز پیشنهاد می‌کنم در این مورد با جزئیات بیشتری صحبت کنیم.
توجه داشته باشید! تمام مثال‌های زیر با استفاده از پروژه Access 2003 ADP و پایگاه داده MSSql 2008 در نظر گرفته شده‌اند. اگر نمی‌دانید پروژه ADP چیست، ما به این موضوع در مقاله نحوه ایجاد و پیکربندی یک پروژه Access ADP نگاه کردیم.
منبع داده برای مثال
فرض کنید یک جدول test_table داریم که شامل اعداد و نام ماه های سال است (پرس و جوها با استفاده از استودیو مدیریت)
جدول ایجاد کنید.( NOT NULL، (50) NULL) در حال حرکت
همانطور که قبلاً گفتم، ما از یک پروژه ADP پیکربندی شده برای کار با MS SQL 2008 استفاده خواهیم کرد که در آن یک فرم آزمایشی ایجاد کردم و یک دکمه شروع با امضا اضافه کردم. "اجرا کن"، که ما باید کد خود را آزمایش کنیم، i.e. ما همه کدها را در کنترل کننده رویداد می نویسیم " دکمه را فشار دهید».
پرس و جو به پایگاه داده INSERT, UPDATE, DELETE در VBA
برای اینکه خیلی تاخیر نداشته باشیم، بیایید بلافاصله شروع کنیم، فرض کنید باید یک ردیف به جدول آزمایشی خود اضافه کنیم ( کد نظر داد)/
Private Sub start_Click() "متغیری را برای ذخیره رشته پرس و جو اعلام کنید Dim sql_query به عنوان رشته "پرس و جوی مورد نیاز خود را در آن بنویسید sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "June")" "Execute آن را DoCmd اجرا کنید sql_query End Sub
در این حالت، پرس و جو با استفاده از پارامترهای اتصال پایگاه داده فعلی اجرا می شود. ما می توانیم بررسی کنیم که آیا داده ها اضافه شده اند یا خیر.
همانطور که می بینید، داده ها درج شده است.
برای حذف یک خط کد زیر را می نویسیم.
Private Sub start_Click() "متغیری را برای ذخیره رشته پرس و جو اعلام کنید Dim sql_query به عنوان رشته "یک درخواست حذف را در آن بنویسید sql_query = "حذف test_table WHERE id = 6" "اجرای آن DoCmd.RunSQL sql_query پایان زیر
اگر بررسی کنیم می بینیم که خط مورد نظر حذف شده است.
برای به روز رسانی داده ها، روی متغیر sql_query بنویسید درخواست به روز رسانی، امیدوارم معنی واضح باشد.
پرس و جو را برای یک پایگاه داده در VBA انتخاب کنید
در اینجا چیزها کمی جالب تر از سایر ساختارهای SQL هستند.
ابتدا فرض کنید باید تمام داده ها را از جدول دریافت کنیم و مثلاً آن ها را پردازش کرده و در یک پیام نمایش می دهیم و شما البته می توانید برای مقاصد دیگر از آن استفاده کنید، برای این کار موارد زیر را می نویسیم. کد
Private Sub start_Click() "اعلان متغیرها "برای مجموعه ای از رکوردها از پایگاه داده Dim RS به عنوان ADODB.Recordset "رشته پرس و جو کم نور sql_query به عنوان رشته "رشته برای نمایش داده های خلاصه در یک پیام Dim str As String "یک شی جدید برای رکوردها ایجاد کنید set RS = New ADODB. Recordset "Query line sql_query = "SELECT id, name_mon FROM test_table" "پرس و جو را با استفاده از تنظیمات اتصال پروژه فعلی اجرا کنید. RS.EOF) "متغیر را پر کنید تا پیام str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline "برو به رکورد بعدی RS.MoveNext Wend" خروجی پیام msgbox str پایان زیر
در اینجا ما از حلقه های دسترسی VBA برای تکرار در تمام مقادیر موجود در مجموعه رکورد خود استفاده می کنیم.
اما اغلب لازم است که نه همه مقادیر را از مجموعه ای از رکوردها، بلکه فقط یک، به عنوان مثال، نام ماه را با کد آن به دست آوریم. و برای انجام این کار، استفاده از یک حلقه گران است، بنابراین می توانیم به سادگی یک پرس و جو بنویسیم که فقط یک مقدار را برمی گرداند و به آن دسترسی پیدا می کنیم، برای مثال، نام ماه را با استفاده از کد 5 دریافت می کنیم.
Private Sub start_Click() "اعلان متغیرها" برای مجموعه ای از رکوردها از پایگاه داده Dim RS As ADODB.Recordset "Ruery Query Dim sql_query As String "String برای نمایش مقدار نهایی Dim str به عنوان رشته "ایجاد یک شی جدید برای رکوردهای مجموعه RS = New ADODB.Recordset "Query line sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "پرس و جو را با استفاده از تنظیمات اتصال پروژه فعلی اجرا کنید. Fields(0) msgbox str پایان زیر
برای جهانی بودن، در اینجا ما قبلا نه با نام سلول، بلکه با شاخص آن، یعنی. 0، و این اولین مقدار در است مجموعه رکورد، در پایان ما به ارزش رسیدیم "ممکن است".
همانطور که می بینید، همه چیز بسیار ساده است. اگر اغلب نیاز به دریافت یک مقدار خاص از پایگاه داده دارید ( مانند آخرین مثال، سپس توصیه می کنم تمام کدها را در یک تابع جداگانه (نحوه نوشتن یک تابع در VBA Access 2003) با یک پارامتر ورودی، به عنوان مثال، کد ماه ( اگر مثال خود را در نظر بگیریم) و به سادگی، در جایی که لازم است این مقدار را نمایش دهیم، تابع مورد نیاز خود را با پارامتر مورد نیاز فراخوانی کنیم و تمام، با این کار کد VBA را به میزان قابل توجهی کاهش می دهیم و درک برنامه خود را بهبود می بخشیم.
برای امروز کافی است. موفق باشید!
توضیحات پروژه آموزشی "فروشگاه"نمودار پیوند جدول
توضیحات جداول
m_category - دسته بندی محصولات
m_درآمد - رسید کالا
m_نتیجه - مصرف کالا
m_product - فهرست، توضیحات محصول
m_supplier - فهرست؛ اطلاعات تأمینکننده
m_unit - فهرست؛ واحدها
برای تست عملی مثال های ارائه شده در این مطلب آموزشی، باید نرم افزار زیر را داشته باشید:
دسترسی مایکروسافت 2003 یا جدیدتر
SQL Query در MS Access. شروع کنید
برای مشاهده محتویات جدول، روی نام جدول در پنل سمت چپ دوبار کلیک کنید:
برای تغییر به حالت ویرایش فیلد جدول، کلیک کنید پنل بالاییحالت طراحی را انتخاب کنید:
برای نمایش نتیجه پرس و جوی SQL، روی نام پرس و جو در قسمت سمت چپ دوبار کلیک کنید:
برای رفتن به حالت ویرایش پرس و جو SQL، حالت SQL را در پانل بالا انتخاب کنید:
پرس و جوی SQL. نمونه هایی در MS Access. انتخاب کنید: 1-10
در پرس و جوی SQL، دستور SELECT برای انتخاب از جداول پایگاه داده استفاده می شود.SQL Query Q001.به عنوان مثال پرس و جوی SQL برای دریافت فقط فیلدهای مورد نیاز در دنباله مورد نظر:
SELECT dt، product_id، مقدار
از m_درآمد؛
SQL Query Q002.در این مثال پرس و جوی SQL، از کاراکتر ستاره (*) برای فهرست کردن تمام ستون های جدول m_product استفاده می شود، به عبارت دیگر، برای دریافت تمام فیلدهای رابطه m_product:
انتخاب کنید *
از m_product;
درخواستSQL Q003.دستور DISTINCT برای حذف ورودی های تکراری و به دست آوردن چندین ورودی منحصر به فرد استفاده می شود:
شناسه محصول متمایز را انتخاب کنید
از m_درآمد؛
SQL Query Q004.دستور ORDER BY برای مرتب کردن (ترتیب) رکوردها بر اساس مقادیر یک فیلد خاص استفاده می شود. نام فیلد بعد از عبارت ORDER BY مشخص می شود:
انتخاب کنید *
از m_income
سفارش بر اساس قیمت؛
SQL Query Q005.دستور ASC به عنوان مکمل دستور ORDER BY استفاده می شود و برای مشخص کردن مرتب سازی صعودی استفاده می شود. دستور DESC علاوه بر دستور ORDER BY استفاده می شود و برای تعیین مرتب سازی نزولی استفاده می شود. در مواردی که نه ASC و نه DESC مشخص نشده است، وجود ASC (پیش‌فرض) فرض می‌شود:
انتخاب کنید *
از m_income
سفارش بر اساس dt DESC , قیمت;
SQL Query Q006.برای انتخاب رکوردهای لازم از جدول، از عبارات منطقی مختلفی استفاده می شود که شرایط انتخاب را بیان می کند. عبارت Boolean بعد از عبارت WHERE ظاهر می شود. نمونه ای از گرفتن تمام رکوردها از جدول m_income که مقدار مقدار آن بیشتر از 200 است:
انتخاب کنید *
از m_income
WHERE مقدار> 200;
SQL Query Q007.برای بیان شرایط دشواراز عملگرهای منطقی AND (پیوند ربط)، OR (انفصال) و NOT (نفی منطقی) استفاده کنید. نمونه ای از گرفتن از جدول m_outcome همه رکوردهایی که مقدار مقدار آنها 20 است و ارزش قیمت بزرگتر یا مساوی 10 است:
قیمت
از m_outcome
WHERE مقدار=20 و قیمت>=10;
SQL Query Q008.برای پیوستن به داده‌ها از دو یا چند جدول، از دستورالعمل‌های JOIN داخلی، پیوستن چپ، RIGHT JOIN استفاده کنید. مثال زیر فیلدهای dt، product_id، مقدار، قیمت را از جدول m_income و فیلد عنوان را از جدول m_product بازیابی می کند. رکورد جدول m_income زمانی به رکورد جدول m_product ملحق می شود که مقدار m_income.product_id برابر با مقدار m_product.id باشد:
ON m_income.product_id=m_product.id;
SQL Query Q009.در این پرس و جوی SQL باید به دو نکته توجه کنید: 1) متنی که به دنبال آن هستید در داخل آن قرار داده شده است. نقل قول های تک(")؛ 2) تاریخ با فرمت #Month/Day/Year# داده می شود که برای MS Access صادق است. در سیستم های دیگر، فرمت نوشتن تاریخ ممکن است متفاوت باشد. نمونه ای از نمایش اطلاعات مربوط به رسید شیر در 12 ژوئن 2011. لطفاً به فرمت تاریخ #6/12/2011# توجه کنید:
SELECT dt، product_id، عنوان، مقدار، قیمت
FROM m_income INNER JOIN m_product
WHERE title="شیر" And dt=#6/12/2011#; !}
SQL Query Q010.دستور BETWEEN برای تست تعلق یک مقدار به محدوده خاصی استفاده می شود. نمونه ای از پرس و جوی SQL که اطلاعات مربوط به محصولات دریافت شده بین 1 تا 30 ژوئن 2011 را نمایش می دهد:
انتخاب کنید *
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE dt BETWEEN #6/1/2011# و #6/30/2011#;
پرس و جوی SQL. نمونه هایی در MS Access. انتخاب کنید: 11-20
یک پرس و جوی SQL را می توان درون دیگری قرار داد. یک پرس و جو فرعی چیزی بیش از یک پرس و جو در یک پرس و جو نیست. معمولاً در عبارت WHERE از یک پرسش فرعی استفاده می شود. اما راه‌های دیگری نیز برای استفاده از پرس و جوی فرعی وجود دارد.پرسش Q011.اطلاعات مربوط به محصولات از جدول m_product نمایش داده می شود که کدهای آن نیز در جدول m_income وجود دارد:
انتخاب کنید *
از m_product
WHERE id IN (انتخاب product_id از m_income)؛
پرسش Q012.لیستی از محصولات از جدول m_product نمایش داده می شود که کدهای آنها در جدول m_outcome نیست:
انتخاب کنید *
از m_product
WHERE id NOT IN (SELECT product_id FROM m_outcome);
Q013 را درخواست کنید.این پرس و جوی SQL یک لیست منحصر به فرد از کدهای محصول و نام هایی را نشان می دهد که در جدول m_income هستند اما در جدول m_outcome نیستند:
شناسه محصول متمایز، عنوان را انتخاب کنید
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE product_id NOT IN (SELECT product_id FROM m_outcome);
پرسش Q014.یک لیست منحصر به فرد از دسته هایی که نام آنها با حرف M شروع می شود از جدول m_category نمایش داده می شود:
عنوان متمایز را انتخاب کنید
از m_product
WHERE عنوانی مانند "M*"؛
پرسش Q015.نمونه ای از انجام عملیات حسابی بر روی فیلدهای یک پرس و جو و تغییر نام فیلدها در یک پرس و جو (مستعار). این مثال هزینه = مقدار * قیمت و سود را برای هر ورودی هزینه اقلام محاسبه می کند، با فرض اینکه سود 7 درصد از فروش باشد:
مبلغ*قیمت/100*7 AS سود
FROM m_outcome;
پرسش Q016.با تجزیه و تحلیل و ساده سازی عملیات حسابی، می توانید سرعت اجرای پرس و جو را افزایش دهید:
SELECT dt، product_id، مقدار، قیمت، مقدار*قیمت AS outcome_sum،
outcome_sum*0.07 AS سود
FROM m_outcome;
Q017 را درخواست کنید.می توانید از عبارت INNER JOIN برای پیوستن به داده ها از چندین جدول استفاده کنید. در مثال زیر، بسته به مقدار ctgry_id، هر ورودی در جدول m_income با نام دسته ای از جدول m_category که محصول به آن تعلق دارد تطبیق داده می شود:
SELECT c.title، b.title، dt، مقدار، قیمت، مقدار*قیمت به عنوان درآمد_جمع
FROM (m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id)
JOIN داخلی m_category AS c ON b.ctgry_id=c.id
ترتیب بر اساس c.title, b.title;
Q018 را درخواست کنید.توابعی مانند SUM - sum، COUNT - quantity، AVG - میانگین حسابی، MAX - حداکثر مقدار، MIN - حداقل مقدار را توابع جمع می گویند. آنها مقادیر زیادی را می پذیرند و پس از پردازش آنها یک مقدار واحد را برمی گردانند. مثالی از محاسبه مجموع حاصل ضرب فیلدهای مقدار و قیمت با استفاده از تابع مجموع SUM:
SUM (مبلغ*قیمت) AS Total_Sum را انتخاب کنید
از m_درآمد؛
پرسش Q019.مثالی از استفاده از چندین تابع جمع:
جمع (مبلغ) AS Amount_Sum، AVG(amount) AS Amount_AVG،
MAX(مبلغ) AS Amount_Max، حداقل(مقدار) AS Amount_Min،
شمارش(*) AS مجموع_تعداد
از m_درآمد؛
Q020 را درخواست کنید.در این مثال، مقدار کل کالاهای با کد 1 با حروف بزرگ در ژوئن 2011 محاسبه می شود:
جمع (مبلغ*قیمت) به عنوان درآمد_جمع را انتخاب کنید
از m_income
WHERE product_id=1 و dt بین #6/1/2011# و #6/30/2011#;.
پرسش Q021.کوئری SQL زیر میزان فروش اقلام را با کد 4 یا 6 محاسبه می کند:
جمع (مبلغ*قیمت) را به عنوان نتیجه_جمع انتخاب کنید
از m_outcome
WHERE product_id=4 OR product_id=6;
پرسش Q022.محاسبه می شود که در 12 ژوئن 2011 چه مقدار کالا با کد 4 یا 6 فروخته شده است:
جمع (مبلغ*قیمت) AS outcome_sum را انتخاب کنید
از m_outcome
WHERE (product_id=4 OR product_id=6) AND dt=#6/12/2011#;
پرسش Q023.وظیفه این است. مجموع کالاهایی را که در دسته «محصولات نانوایی» سرمایه گذاری شده اند، محاسبه کنید.
برای حل این مشکل باید با سه جدول m_income، m_product و m_category کار کنید، زیرا:
- مقدار و قیمت کالاهای سرمایه ای در جدول m_درآمد ذخیره می شود.
- کد دسته هر محصول در جدول m_product ذخیره می شود.
- نام دسته عنوان در جدول m_category ذخیره می شود.
برای حل این مشکل از الگوریتم زیر استفاده می کنیم:
- تعیین کد دسته "محصولات نانوایی" از جدول m_category با استفاده از یک جستجوی فرعی.
- اتصال جداول m_income و m_product برای تعیین دسته بندی هر محصول خریداری شده؛
- محاسبه مبلغ دریافتی (= مقدار*قیمت) برای کالاهایی که کد دسته آنها برابر با کد تعریف شده در استعلام فرعی فوق می باشد.
انتخاب کنید
FROM m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (انتخاب شناسه از m_category WHERE title="محصولات نانوایی"); !}
پرسش Q024.ما با استفاده از الگوریتم زیر مشکل محاسبه کل کالاهای سرمایه ای در دسته "محصولات نانوایی" را حل خواهیم کرد:
- برای هر ورودی در جدول m_income، بسته به مقدار product_id آن، از جدول m_category، نام دسته را مطابقت دهید.
- رکوردهایی را انتخاب کنید که دسته آنها "محصولات نانوایی" است.
- محاسبه مقدار دریافت = مقدار * قیمت.
FROM (m_product AS یک JOIN داخلی m_income AS b ON a.id=b.product_id)
WHERE c.title="محصولات نانوایی";
!}
پرسش Q025.این مثال تعداد اقلام مصرف شده را محاسبه می کند:
COUNT(product_id) را به عنوان product_cnt انتخاب کنید
FROM (انتخاب DISTINCT product_id FROM m_outcome) AS t;
پرسش Q026.دستور GROUP BY برای گروه بندی رکوردها استفاده می شود. به طور معمول، رکوردها بر اساس مقدار یک یا چند فیلد گروه بندی می شوند و مقداری عملیات انبوه برای هر گروه اعمال می شود. به عنوان مثال، پرس و جو زیر گزارشی از فروش کالا ایجاد می کند. به این معنی که جدولی حاوی نام کالاها و میزان فروش آنها ایجاد می شود:
عنوان، جمع (مبلغ*قیمت) به عنوان نتیجه_جمع را انتخاب کنید
FROM m_product AS یک JOIN داخلی m_نتیجه AS ب
ON a.id=b.product_id
گروه بر اساس عنوان؛
Q027 را درخواست کنید.گزارش فروش بر اساس دسته بندی یعنی جدولی تولید می شود که شامل نام دسته بندی محصولات، کل مبلغی است که محصولات این دسته ها به ازای آن فروخته شده است و میانگین میزان فروش. تابع ROUND برای گرد کردن مقدار متوسط به نزدیکترین صدم (رقم دوم بعد از جداکننده اعشاری) استفاده می شود:
عنوان c., SUM(مبلغ*قیمت) AS outcome_sum,
ROUND(AVG(مقدار*قیمت)، 2) AS نتیجه_جمع_میانگین
FROM (m_product AS a Inner Join m_outcome AS b ON a.id=b.product_id)
JOIN داخلی m_category AS c ON a.ctgry_id=c.id
GROUP BY c.title;
پرسش Q028.تعداد کل و میانگین دریافتی های آن برای هر محصول محاسبه می شود و اطلاعات مربوط به محصولاتی که مجموع دریافتی آنها حداقل 500 است را نمایش می دهد:
SELECT product_id، SUM (مبلغ) AS مقدار_sum،
Round(Avg(Amount),2) AS مقدار_avg
از m_income
GROUP BY product_id
داشتن مبلغ (مبلغ)>=500;
پرسش Q029.این استعلام برای هر محصول میزان و میانگین دریافتی آن در سه ماهه دوم سال 2011 را محاسبه می کند. اگر مبلغ کل رسید محصول حداقل 1000 باشد، اطلاعات مربوط به این محصول نمایش داده می شود:
عنوان، مجموع (مبلغ*قیمت) به عنوان درآمد_جمع را انتخاب کنید
FROM m_income a INNER JOIN m_product b ON a.product_id=b.id
جایی که dt بین #4/1/2011# و #6/30/2011#
گروه بر اساس عنوان
داشتن مبلغ (مبلغ*قیمت)>=1000;
پرسش Q030.در برخی موارد، شما باید هر رکورد یک جدول را با هر رکورد یک جدول دیگر مطابقت دهید. که محصول دکارتی نامیده می شود. جدول حاصل از چنین ارتباطی را جدول دکارت می نامند. به عنوان مثال، اگر جدول A دارای 100 رکورد و جدول B دارای 15 رکورد باشد، جدول دکارت آنها شامل 100*15=150 رکورد خواهد بود. کوئری زیر به هر رکورد در جدول m_income با هر رکورد در جدول m_outcome می پیوندد:
از m_income، m_outcome;
پرسش Q031.نمونه ای از گروه بندی رکوردها بر اساس دو فیلد. پرس و جوی SQL زیر برای هر تامین کننده مقدار و مقدار کالاهای دریافتی از او را محاسبه می کند:
مجموع (مبلغ*قیمت) به عنوان درآمد_جمع
پرسش Q032.نمونه ای از گروه بندی رکوردها بر اساس دو فیلد. پرس و جو زیر برای هر تامین کننده مقدار و مقدار محصولات فروخته شده توسط ما را محاسبه می کند:
SELECT source_id، product_id، SUM (مبلغ) AS مقدار_جمع،
GROUP BY source_id, product_id;
پرسش Q033.در این مثال، دو کوئری بالا (q031 و q032) به عنوان پرس و جوی فرعی استفاده می شوند. نتایج این پرسش‌ها با استفاده از روش LEFT JOIN در یک گزارش ترکیب می‌شوند. پرس و جو زیر گزارشی از مقدار و مقدار محصولات دریافتی و فروخته شده برای هر تامین کننده را نمایش می دهد. لطفاً توجه داشته باشید که اگر محصولی قبلاً دریافت شده است، اما هنوز فروخته نشده است، سلول outcome_sum برای این ورودی خالی خواهد بود. که این پرس و جو تنها نمونه ای از استفاده از پرس و جوهای نسبتاً پیچیده به عنوان پرس و جو است. عملکرد این پرس و جوی SQL با مقدار زیادی داده مشکوک است:
انتخاب کنید *
از جانب
مجموع (مبلغ*قیمت) به عنوان درآمد_جمع
ON a.product_id=b.id GROUP BY source_id, product_id) AS a
چپ پیوستن
(انتخاب source_id، product_id، SUM(مبلغ) AS مقدار_جمع،
SUM (مبلغ*قیمت) به عنوان نتیجه_جمع
از m_outcome به عنوان یک JOIN داخلی m_product AS b
ON a.product_id=b.id GROUP BY source_id, product_id) AS ب
روشن (a.product_id=b.product_id) و (a.supplier_id=b.supplier_id);
پرسش Q034.در این مثال، دو کوئری بالا (q031 و q032) به عنوان پرس و جوی فرعی استفاده می شوند. نتایج این پرسش‌ها با استفاده از روش RIGTH JOIN در یک گزارش ترکیب می‌شوند. پرس و جوی زیر گزارشی از میزان پرداخت های هر مشتری با توجه به سیستم های پرداختی که استفاده کرده و میزان سرمایه گذاری هایی که انجام داده را نشان می دهد. پرس و جو زیر گزارشی از مقدار و مقدار محصولات دریافتی و فروخته شده برای هر تامین کننده را نمایش می دهد. لطفاً توجه داشته باشید که اگر محصولی قبلاً فروخته شده باشد، اما هنوز وارد نشده است، سلول درآمد_جمع این ورودی خالی خواهد بود. وجود چنین سلول های خالی نشانگر خطا در حسابداری فروش است، زیرا قبل از فروش ابتدا لازم است محصول مربوطه برسد:
انتخاب کنید *
از جانب
(انتخاب source_id، product_id، SUM(مبلغ) AS مقدار_جمع،
مجموع (مبلغ*قیمت) به عنوان درآمد_جمع
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY source_id، product_id) AS a
راست بپیوندید
(انتخاب source_id، product_id، SUM(مبلغ) AS مقدار_جمع،
SUM (مبلغ*قیمت) به عنوان نتیجه_جمع
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY source_id، product_id) AS ب
روشن (a.supplier_id=b.supplier_id) و (a.product_id=b.product_id);
پرسش Q035.گزارشی نمایش داده می شود که میزان درآمد و هزینه های محصول را نشان می دهد. برای این کار فهرستی از محصولات با توجه به جداول m_income و m_outcome ایجاد می شود، سپس برای هر محصول از این لیست مجموع درآمد آن با توجه به جدول m_income و میزان هزینه های آن با توجه به جدول m_outcome محاسبه می شود:
SELECT product_id، SUM(in_amount) AS در آمد_مبلغ،
SUM(out_amount) به عنوان outcome_amount
از جانب
(Product_id، مقدار AS in_amount، 0 AS out_amount را انتخاب کنید
از m_income
اتحاد همه
SELECT product_id، 0 AS in_amount، مقدار AS out_amount
از m_outcome) AS t
GROUP BY product_id;
پرسش Q036.اگر مجموعه ای که به آن ارسال شده حاوی عناصر باشد، تابع EXISTS TRUE را برمی گرداند. تابع EXISTS اگر مجموعه ارسال شده به آن خالی باشد، یعنی فاقد عنصر باشد، FALSE را برمی گرداند. کوئری زیر کدهای محصول را که در هر دو جدول m_income و m_outcome موجود است را نمایش می دهد:
شناسه محصول متمایز را انتخاب کنید
FROM m_income AS a
WHERE EXISTS(product_id FROM m_outcome AS b
پرسش Q037.کدهای محصول که در هر دو جدول m_income و m_outcome موجود است نمایش داده می شوند:
شناسه محصول متمایز را انتخاب کنید
FROM m_income AS a
WHERE product_id در (انتخاب product_id از m_outcome)
پرسش Q038.کدهای محصول نمایش داده می شوند که در جدول m_income موجود هستند، اما در جدول m_outcome موجود نیستند:
شناسه محصول متمایز را انتخاب کنید
FROM m_income AS a
WHERE NOT EXISTS(product_id FROM m_outcome AS b
WHERE b.product_id=a.product_id);
پرسش Q039.لیستی از محصولات با حداکثر میزان فروش نمایش داده می شود. الگوریتم به شرح زیر است. برای هر محصول، میزان فروش آن محاسبه می شود. سپس حداکثر این مقادیر مشخص می شود. سپس برای هر محصول مجددا مجموع فروش آن محاسبه شده و کد و مجموع فروش کالاهایی که مجموع فروش آنها برابر با حداکثر است نمایش داده می شود:
SELECT product_id، SUM (مبلغ*قیمت) AS مقدار_جمع
از m_outcome
GROUP BY product_id
داشتن مجموع (مبلغ*قیمت) = (حداکثر (s_amount) را انتخاب کنید
FROM (انتخاب جمع (مبلغ*قیمت) AS s_amount FROM m_outcome GROUP BY product_id));
پرسش Q040.کلمه رزرو شده IIF ( عملگر شرطی) برای ارزیابی یک عبارت بولی و انجام یک عمل بسته به نتیجه (TRUE یا FALSE) استفاده می شود. در مثال زیر، اگر مقدار آن کمتر از 500 عدد باشد، تحویل "کوچک" در نظر گرفته می شود.
SELECT dt، product_id، مقدار،
IIF(مبلغ از m_income;
SQL Query Q041.در مواردی که اپراتور IIF چندین بار استفاده می شود، جایگزینی آن با اپراتور SWITCH راحت تر است. عملگر SWITCH (اپراتور انتخاب چندگانه) برای ارزیابی یک عبارت منطقی و انجام یک عمل بسته به نتیجه استفاده می شود. در مثال زیر، اگر مقدار کالا در لات کمتر از 500 عدد باشد، لات تحویلی "کوچک" در نظر گرفته می شود. در غیر این صورت، یعنی اگر مقدار کالا بزرگتر یا مساوی 500 باشد، لات "بزرگ" در نظر گرفته می شود. ":
SELECT dt، product_id، مقدار،
علامت سوئیچ (مقدار = 500 "بزرگ") AS
از m_درآمد؛
پرسش Q042.در درخواست بعدی، اگر مقدار کالا در دسته دریافتی کمتر از 300 باشد، دسته "کوچک" در نظر گرفته می شود. در غیر این صورت، یعنی اگر مقدار شرط SELECT dt، product_id، مقدار،
IIF(مبلغ IIF(مبلغ FROM m_income;
SQL Query Q043.در درخواست بعدی، اگر مقدار کالا در دسته دریافتی کمتر از 300 باشد، دسته "کوچک" در نظر گرفته می شود. در غیر این صورت، یعنی اگر مقدار شرط SELECT dt، product_id، مقدار،
SWITCH (مبلغ مقدار>=1000"large") علامت AS
از m_درآمد؛
SQL Query Q044.در پرس و جو زیر، فروش به سه گروه تقسیم می شود: کوچک (تا 150)، متوسط (از 150 تا 300)، بزرگ (300 یا بیشتر). در مرحله بعد، کل مبلغ برای هر گروه محاسبه می شود:
SELECT Category، SUM(outcome_sum) AS Ctgry_Total
FROM (انتخاب مقدار*قیمت به عنوان نتیجه_جمع،
IIf(مبلغ*قیمت IIf(مقدار*قیمت از m_outcome) AS t
گروه بر اساس دسته;
SQL Query Q045.تابع DateAdd برای اضافه کردن روز، ماه یا سال به تاریخ معین و به دست آوردن تاریخ جدید استفاده می شود. درخواست بعدی:
1) از قسمت dt 30 روز به تاریخ اضافه می کند و تاریخ جدید را در قسمت dt_plus_30d نمایش می دهد.
2) از قسمت dt 1 ماه به تاریخ اضافه می کند و تاریخ جدید را در قسمت dt_plus_1m نمایش می دهد:
SELECT dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m
از m_درآمد؛
SQL Query Q046.تابع DateDiff برای محاسبه تفاوت بین دو تاریخ در واحدهای مختلف (روز، ماه یا سال) طراحی شده است. کوئری زیر تفاوت بین تاریخ در فیلد dt و تاریخ فعلی را بر حسب روز، ماه و سال محاسبه می کند:
SELECT dt، DateDiff("d",dt,Date()) AS last_day،
DateDiff("m",dt,Date()) AS last_months,
DateDiff("yyyy",dt,Date()) AS last_years
از m_درآمد؛
SQL Query Q047.تعداد روزهای از تاریخ دریافت کالا (جدول m_درآمد) تا تاریخ جاری با استفاده از تابع DateDiff محاسبه می شود و تاریخ انقضا با هم مقایسه می شود (جدول m_product):
DateDiff("d",dt,Date()) AS last_days
FROM m_income AS یک INNER JOIN m_product AS b
ON a.product_id=b.id;
SQL Query Q048.تعداد روزهای از تاریخ دریافت کالا تا تاریخ فعلی محاسبه می شود، سپس بررسی می شود که آیا این مقدار از تاریخ انقضا بیشتر است یا خیر:
SELECT a.id، product_id، dt، lifedays،
DateDiff("d", "dt, "Date()) AS last_days, IIf(last_days>lifedays,"Yes","No") AS date_expire
از m_income یک INNER JOIN m_product b
ON a.product_id=b.id;
SQL Query Q049.تعداد ماه ها از تاریخ دریافت کالا تا تاریخ فعلی محاسبه می شود. ستون month_last1 تعداد مطلق ماه ها را محاسبه می کند، ستون month_last2 تعداد ماه های کامل را محاسبه می کند:
SELECT dt، DateDiff("m",dt,Date()) AS month_last1،
DateDiff("m",dt,Date())-iif(day(dt)>day(date()),1,0) AS month_last2
از m_درآمد؛
SQL Query Q050.گزارش فصلی در مورد مقدار و مقدار کالاهای خریداری شده برای سال 2011 نمایش داده می شود:
SELECT kvartal، SUM(outcome_sum) AS مجموع
FROM (انتخاب مقدار*قیمت AS نتیجه_جمع، ماه(dt) به متر،
سوئیچ (m = 10.4) به عنوان کوارتال
FROM m_income WHERE سال(dt)=2011) AS t
گروه به چهارم؛
پرسش Q051.پرس و جوی زیر به شما کمک می کند تا متوجه شوید که آیا کاربران می توانند اطلاعات مصرف کالا را به مقدار بیشتر از مقدار کالای دریافتی وارد سیستم کنند یا خیر:
SELECT product_id، SUM(in_sum) AS gain_sum، SUM(out_sum) AS outcome_sum
FROM (product_id، مقدار*قیمت به عنوان in_sum، 0 به عنوان out_sum انتخاب کنید
از m_income
اتحاد همه
SELECT product_id، 0 به عنوان in_sum، مقدار*قیمت به عنوان out_sum
از m_outcome) AS t
GROUP BY product_id
داشتن مجموع (in_sum)
پرسش Q052.شماره گذاری سطرهای برگردانده شده توسط یک پرس و جو به روش های مختلفی اجرا می شود. به عنوان مثال، می توانید خطوط گزارش تهیه شده در MS Access را با استفاده از خود MS Access شماره گذاری کنید. همچنین می توانید با استفاده از زبان های برنامه نویسی، به عنوان مثال، VBA یا PHP، شماره گذاری مجدد کنید. با این حال، گاهی اوقات این کار باید در خود پرس و جوی SQL انجام شود. بنابراین، پرس و جو زیر ردیف های جدول m_income را با توجه به ترتیب صعودی مقادیر فیلد ID شماره گذاری می کند:
COUNT(*) را به عنوان N، b.id، b.product_id، b.amount، b.price انتخاب کنید
FROM m_income a INNER JOIN m_income b ON a.id GROUP BY b.id, b.product_id, b.amount, b.price;
پرسش Q053.پنج محصول برتر در بین محصولات بر اساس میزان فروش نمایش داده می شود. پنج رکورد اول با استفاده از دستورالعمل TOP چاپ می شوند:
SELECT TOP 5، product_id، sum (مبلغ*قیمت) به عنوان خلاصه
از m_outcome
GROUP BY product_id
سفارش بر اساس جمع (مقدار*قیمت) DESC;
پرسش Q054.پنج محصول برتر در بین محصولات بر اساس میزان فروش نمایش داده می شود و ردیف ها به عنوان نتیجه شماره گذاری می شوند:
COUNT(*) AS N، b.product_id، b.summa را انتخاب کنید
از جانب
FROM m_outcome GROUP BY product_id) AS a
پیوستن داخلی
(انتخاب شناسه محصول، مجموع (مبلغ*قیمت) به عنوان خلاصه،
summa*10000000+product_id AS id
FROM m_outcome GROUP BY product_id) AS ب
ON a.id>=b.id
GROUP BY b.product_id، b.summa
داشتن تعداد (*) سفارش بر اساس تعداد (*)؛
پرسش Q055.پرس و جوی SQL زیر استفاده از توابع ریاضی COS، SIN، TAN، SQRT، ^ و ABS را در MS Access SQL نشان می دهد:
SELECT (انتخاب تعداد(*) از m_income) به عنوان N، 3.1415926 به عنوان pi، k،
2*pi*(k-1)/N به عنوان x، COS(x) به عنوان COS_، SIN(x) به عنوان SIN_، TAN(x) به عنوان TAN_،
SQR(x) به عنوان SQRT_، x^3 به عنوان "x^3"، ABS(x) به عنوان ABS_
FROM (انتخاب COUNT(*) AS k
FROM m_income AS a INNER JOIN m_income AS b در a.idGROUP BY b.id) t;
پرس و جوی SQL. نمونه هایی در MS Access. به روز رسانی: 1-10
درخواست U001.درخواست تغییر SQL زیر قیمت کالاهای با کد 3 در جدول m_income را 10 درصد افزایش می دهد:به روز رسانی m_income SET قیمت = قیمت * 1.1
WHERE product_id=3;
درخواست U002.درخواست به‌روزرسانی SQL زیر، تعداد تمام محصولات موجود در جدول m_income را 22 واحد افزایش می‌دهد که نام آنها با کلمه «Oil» شروع می‌شود:
به روز رسانی m_income مقدار مجموعه = مقدار + 22
WHERE product_id IN (انتخاب شناسه از m_product WHERE عنوان LIKE "Oil*");
درخواست U003.درخواست SQL زیر برای تغییر در جدول m_outcome قیمت تمام کالاهای تولید شده توسط Sladkoe LLC را 2 درصد کاهش می دهد:
به روز رسانی m_outcome SET قیمت = قیمت * 0.98
WHERE product_id در
(a.id FROM m_product a INNER JOIN m_supplier b
ON a.supplier_id=b.id WHERE b.title="LLC"Сладкое"");. !}
درج، حذف، به روز رسانی رکوردها در پایگاه داده
متد ()ExecuteReader یک شی داده خوان را بازیابی می کند که به شما امکان می دهد نتایج دستور SQL Select را با استفاده از یک جریان اطلاعات فقط خواندنی رو به جلو مشاهده کنید. با این حال، اگر شما نیاز به اجرای دستورات SQL دارید که جدول داده ها را تغییر می دهند، باید متد را فراخوانی کنید. ExecuteNonQuery() از این شیتیم ها این روش واحد برای انجام درج، تغییر و حذف بسته به فرمت متن فرمان طراحی شده است.
مفهوم غیر پرس و جوبه معنای دستور SQL است که مجموعه ای از نتایج را بر نمی گرداند. از این رو، عبارات را انتخاب کنیدپرس و جو هستند، اما عبارت Insert، Update و Delete چنین نیستند. بر این اساس، متد ExecuteNonQuery() یک int حاوی تعداد ردیف‌های تحت تأثیر این عبارات را به جای مجموعه‌ای از رکوردهای جدید برمی‌گرداند.
برای نشان دادن نحوه تغییر محتویات یک پایگاه داده موجود با استفاده از پرس و جوی ExecuteNonQuery()، گام بعدی ایجاد کتابخانه دسترسی به داده های خود است که فرآیند پایگاه داده AutoLot را کپسوله می کند.
در یک محیط تولید واقعی، منطق ADO.NET شما تقریباً به یک دلیل ساده در یک اسمبلی .NET.dll ایزوله می شود - استفاده مجدد از کد! این کار در مقالات قبلی انجام نشده بود تا حواس شما را از کارهایی که در دست دارید پرت نکند. اما توسعه منطق اتصال یکسان، منطق خواندن داده ها و منطق اجرای فرمان یکسان برای هر برنامه ای که نیاز به کار با پایگاه داده AutoLot دارد، اتلاف وقت خواهد بود.
با جداسازی منطق دسترسی به داده ها در یک کتابخانه کد دات نت، برنامه های مختلف با هر رابط کاربری (به سبک کنسول، سبک دسکتاپ، سبک وب و غیره) می توانند حتی بدون توجه به زبان به کتابخانه موجود دسترسی داشته باشند. و اگر یک کتابخانه دسترسی به داده در سی شارپ ایجاد کنید، دیگر برنامه نویسان دات نت می توانند کتابخانه خود را ایجاد کنند. رابط های کاربریدر هر زبانی (مثلا VB یا C++/CLI).
کتابخانه دسترسی به داده ما (AutoLotDAL.dll) حاوی یک فضای نام واحد (AutoLotConnectedLayer) خواهد بود که با استفاده از انواع متصل ADO.NET با پایگاه داده AutoLot تعامل خواهد داشت.
با ایجاد یک پروژه جدید کتابخانه کلاس C# به نام AutoLotDAL (مخفف "AutoLot Data Access Layer") شروع کنید و سپس نام فایل کد اصلی C# را به AutoLotConnDAL.cs تغییر دهید.
سپس دامنه فضای نام را به AutoLotConnectedLayer تغییر نام دهید و نام کلاس اصلی را به InventoryDAL تغییر دهید، زیرا این کلاس اعضای مختلفی را تعریف می کند که برای تعامل با جدول Inventory پایگاه داده AutoLot طراحی شده اند. در نهایت فضاهای نام دات نت زیر را وارد کنید:
استفاده از سیستم؛ با استفاده از System.Collections.Generic. با استفاده از System.Text. با استفاده از System.Data؛ با استفاده از System.Data.SqlClient. فضای نام AutoLotConnectedLayer (کلاس عمومی InventoryDAL ())
اضافه کردن منطق اتصال
اولین وظیفه ما تعریف روش هایی است که به فرآیند فراخوانی اجازه می دهد با استفاده از یک رشته اتصال معتبر به منبع داده متصل شود و از آن جدا شود. از آنجایی که مجموعه AutoLotDAL.dll ما برای استفاده از انواع کلاس System.Data.SqlClient کدگذاری سختی خواهد داشت، یک متغیر SqlConnection خصوصی تعریف کنید که با ایجاد شی InventoryDAL تخصیص داده شود.
علاوه بر این، یک متد OpenConnection() و سپس یکی دیگر از CloseConnection() تعریف کنید که با این متغیر تعامل خواهد داشت:
کلاس عمومی InventoryDAL ( خصوصی SqlConnection connect = null؛ public void OpenConnection(string connectionString) (connect = new SqlConnection(connectionString); connect.Open(); ) public void CloseConnection() (connect.Close();) )
برای اختصار، نوع InventoryDAL همه استثناهای ممکن را بررسی نمی‌کند و در صورت وقوع موقعیت‌های مختلف (مثلاً وقتی رشته اتصال بد شکل است) استثناهای سفارشی را ایجاد نمی‌کند. با این حال، اگر در حال ساختن یک کتابخانه دسترسی به داده های تولیدی بودید، احتمالاً باید از تکنیک های مدیریت استثنایی ساختاریافته برای محاسبه هر گونه ناهنجاری که ممکن است در زمان اجرا رخ دهد استفاده کنید.
اضافه کردن منطق درج
درج کنید ورودی جدیدبه جدول Inventory به قالب بندی دستور SQL می رسد درج کنید(بسته به ورودی کاربر) و فراخوانی متد ExecuteNonQuery() با استفاده از شی فرمان. برای انجام این کار، یک متد عمومی ()InsertAuto را به کلاس InventoryDAL اضافه کنید که چهار پارامتر مربوط به چهار ستون جدول Inventory (CarID، Color، Make و PetName) را می گیرد. بر اساس این آرگومان ها، یک خط برای اضافه کردن یک ورودی جدید ایجاد کنید. در نهایت، دستور SQL را با استفاده از شی SqlConnection اجرا کنید:
فضای خالی عمومی InsertAuto(int id، رنگ رشته، رشته ساخت، رشته petName) (// بیانیه SQL string sql = string.Format("Insert Into Inventory" + "(CarID، Make، Color، PetName) Values(@CarId، @Make، @Color، @PetName)"); با استفاده از (SqlCommand cmd = new SqlCommand(sql, this.connect)) (// افزودن پارامترها cmd.Parameters.AddWithValue("@CarId"، id)؛ cmd.Parameters.AddWithValue("@Make", make); cmd. Parameters.AddWithValue("@Color"، color); cmd.Parameters.AddWithValue("@PetName"، petName); cmd.ExecuteNonQuery(); ))
تعریف کلاس هایی که رکوردها را در یک پایگاه داده رابطه ای نشان می دهند، یک راه متداول برای ایجاد یک کتابخانه دسترسی به داده است. در واقع، ADO.NET Entity Framework به طور خودکار کلاس هایی با تایپ قوی تولید می کند که به شما امکان می دهد با داده های پایگاه داده تعامل داشته باشید. به هر حال، لایه مستقل ADO.NET اشیاء DataSet با تایپ قوی تولید می کند تا داده ها را از یک جدول داده شده در یک پایگاه داده رابطه ای نشان دهد.
ایجاد یک دستور SQL با استفاده از الحاق رشته ها می تواند یک خطر امنیتی باشد (به حملات درج SQL فکر کنید). بهتر است متن دستور را با استفاده از یک کوئری پارامتری ایجاد کنید که کمی بعد توضیح داده خواهد شد.
اضافه کردن Delete Logic
حذف رکورد موجودسخت تر از درج یک رکورد جدید نیست. برخلاف کد InsertAuto()، یک ناحیه مهم try/catch نشان داده می‌شود که موقعیت احتمالی را کنترل می‌کند که در آن تلاش می‌شود خودرویی را که قبلاً شخصی سفارش داده از جدول مشتریان حذف شود. متد زیر را به کلاس InventoryDAL اضافه کنید:
Public void DeleteCar(int id) ( string sql = string.Format ("Delete from Inventory where CarID = "(0)""، ID)؛ با استفاده از (SqlCommand cmd = new SqlCommand(sql, this.connect)) ( امتحان کنید ( cmd.ExecuteNonQuery(); ) catch (SqlException ex) ( Exception error = New Exception ("متأسفیم، این ماشین در سفارش قبلی است!"، ex)؛ خطای پرتاب؛ ) ) )
اضافه کردن منطق تغییر
وقتی صحبت از به‌روزرسانی یک رکورد موجود در جدول موجودی می‌شود، بلافاصله این سؤال آشکار می‌شود: فرآیند فراخوانی دقیقاً چه چیزی را می‌توان تغییر داد: رنگ خودرو، نام دوستانه، مدل یا هر سه؟ یکی از راه‌های به حداکثر رساندن انعطاف‌پذیری، تعریف روشی است که پارامتری از نوع رشته را می‌گیرد، که می‌تواند حاوی هر دستور SQL باشد، اما حداقل می‌توان گفت این کار خطرناک است.
در حالت ایده‌آل، بهتر است مجموعه‌ای از روش‌ها داشته باشید که به فرآیند فراخوانی اجازه می‌دهد رکوردها را تغییر دهد راه های مختلف. با این حال، برای کتابخانه ساده دسترسی به داده خود، یک روش واحد را تعریف می کنیم که به فرآیند فراخوانی اجازه می دهد نام دوستانه ماشین مشخص شده را تغییر دهد:
Public void UpdateCarPetName(int id, string newpetName) ( string sql = string.Format("Update Inventory Set PetName = "(0)" Where CarID = "(1)"" newpetName, id)؛ با استفاده از (SqlCommand cmd = new SqlCommand(sql, this.connect)) (cmd.ExecuteNonQuery(); ))
اضافه کردن منطق نمونه گیری
حال باید روشی برای انتخاب رکوردها اضافه کنیم. همانطور که قبلا نشان داده شد، شی داده خوان یک ارائه دهنده داده خاص به شما امکان می دهد رکوردها را با استفاده از مکان نما فقط خواندنی انتخاب کنید. با فراخوانی متد Read() می توانید هر رکورد را یکی یکی پردازش کنید. همه اینها عالی است، اما اکنون باید بفهمیم که چگونه این رکوردها را به لایه برنامه فراخوانی برگردانیم.
یک روش بازیابی داده ها با استفاده از متد Read() و سپس پر کردن و برگرداندن یک آرایه چند بعدی (یا شی دیگری مانند لیست عمومی) است.
راه دیگر برگرداندن یک شی System.Data.DataTable است که در واقع به لایه مستقل ADO.NET تعلق دارد. DataTable کلاسی است که یک بلوک جدولی از داده ها (مانند یک کاغذ یا صفحه گسترده) را نشان می دهد.
کلاس DataTable شامل داده ها به صورت مجموعه ای از ردیف ها و ستون ها است. این مجموعه ها را می توان به صورت برنامه نویسی پر کرد، اما نوع DataTable دارای یک متد Load() است که می تواند آنها را به طور خودکار با استفاده از یک آبجکت داده خوان پر کند! در اینجا مثالی آورده شده است که در آن داده های جدول Inventory به عنوان DataTable برگردانده می شوند:
Public DataTable GetAllInventoryAsDataTable() ( DataTable inv = new DataTable(); string sql = "Select * From Inventory"؛ با استفاده از (SqlCommand cmd = new SqlCommand(sql, this.connect)) ( SqlDataReader dr = cmd.ExevReader); .Load(dr); dr.Close(); ) return inv;)
کار با اشیاء فرمان پارامتری شده
تا کنون، در منطق درج، به روز رسانی و حذف برای نوع InventoryDAL، برای هر کوئری SQL از حروف رشته ای سخت کد شده استفاده کرده ایم. احتمالاً از وجود پرس‌و‌جوهای پارامتری شده آگاه هستید، که به شما امکان می‌دهند پارامترهای SQL را به‌عنوان یک شی و نه صرفاً یک قطعه متن در نظر بگیرید.
کار با پرس و جوهای SQL به شیوه ای شی گرا نه تنها به کاهش اشتباهات تایپی (با ویژگی های تایپ قوی) کمک می کند، بلکه پرس و جوهای پارامتری شده معمولاً بسیار سریعتر از پرس و جوهای رشته ای هستند زیرا فقط یک بار (و نه هر بار) تجزیه می شوند. ویژگی CommandText روی یک رشته SQL تنظیم شده است. علاوه بر این، پرس و جوهای پارامتری شده در برابر حملات تزریق SQL (یک مشکل شناخته شده امنیتی دسترسی به داده) محافظت می کنند.
برای پشتیبانی از پرس و جوهای پارامتری شده، اشیاء دستوری ADO.NET مجموعه ای از اشیاء پارامتر جداگانه را نگهداری می کنند. به طور پیش‌فرض، این مجموعه خالی است، اما می‌توانید هر تعداد پارامتری را که مطابقت دارند اضافه کنید پارامترهای نگهدارنده مکاندر پرس و جوی SQL اگر نیاز دارید که یک پارامتر پرس و جوی SQL را با یکی از اعضای مجموعه پارامترهای یک شیء دستوری مرتبط کنید، قبل از پارامتر SQL با نماد @ قرار دهید (حداقل هنگام کار با مایکروسافت SQL Server، اگرچه همه DBMS ها از این نام پشتیبانی نمی کنند).
تنظیم پارامترها با استفاده از نوع DbParameter
قبل از شروع ایجاد کوئری های پارامتری شده، اجازه دهید با نوع DbParameter (کلاس پایه برای اشیاء پارامتر ارائه دهنده) آشنا شویم. این کلاس دارای تعدادی ویژگی است که به شما امکان می دهد نام، اندازه و نوع پارامتر و همچنین ویژگی های دیگر مانند جهت مشاهده پارامتر را مشخص کنید. برخی از ویژگی های مهم نوع DbParameter در زیر آورده شده است:
DbTypeنوع داده را از یک پارامتر، که به عنوان یک نوع CLR نشان داده می شود، دریافت یا تنظیم می کند
جهتنوع پارامتر را برمی گرداند یا تنظیم می کند: فقط ورودی، فقط خروجی، ورودی و خروجی، یا پارامتر برای برگرداندن یک مقدار
قابل حذف استبرمی گرداند یا تنظیم می کند که آیا یک پارامتر می تواند مقادیر خالی را بپذیرد
نام پارامترنام DbParameter را دریافت یا تنظیم می کند
اندازهمشکلات یا نصب می کند حداکثر اندازهداده برای پارامتر (فقط برای داده های متنی مفید است)
ارزشمقدار یک پارامتر را برمی گرداند یا تنظیم می کند
برای نشان دادن نحوه پر کردن مجموعه ای از اشیاء فرمان با اشیاء سازگار با DBParameter، بیایید متد InsertAuto() را بازنویسی کنیم تا از اشیاء پارامتر استفاده کند (همه روش های دیگر را می توان به طور مشابه بازسازی کرد، اما مثال حاضر برای ما کافی است):
Public void InsertAuto(int id، رنگ رشته، رشته ساخت، رشته petName) (// عبارت SQL string sql = string.Format("Insert Into Inventory" + "(CarID, Make, Color, PetName) مقادیر("(0) ""(1)""(2)""(3)")"، شناسه، ساخت، رنگ، petName؛ // فرمان پارامتری شده با استفاده از (SqlCommand cmd = new SqlCommand(sql, this.connect)) ( SqlParameter param = new SqlParameter(); param.ParameterName = "@CarID"; param.Value = id; param.SqlDbType = SqlDbType.Int; cmd.Parameters.Add(param); param = new SqlParameter(); ParameterName = "@Make"; param.Value = make; param.SqlDbType = SqlDbType.Char; param.Size = 10; cmd.Parameters.Add(param); param = جدید SqlParameter(); param.ParameterName = "@Color "; param.Value = color; param.SqlDbType = SqlDbType.Char; param.Size = 10; cmd.Parameters.Add(param); param = new SqlParameter(); param.ParameterName = "@PetName"; param.Value = petName؛ param.SqlDbType = SqlDbType.Char؛ param.Size = 10؛ cmd.Parameters.Add(param)؛ cmd.ExecuteNonQuery(); ))
توجه داشته باشید که پرس و جوی SQL در اینجا همچنین شامل چهار کاراکتر مکان نگهدار است که قبل از هر کدام یک علامت @ وجود دارد. با استفاده از ویژگی ParameterName در نوع SqlParameter، می‌توانید هر یک از این متغیرها را توصیف کنید و اطلاعات مختلف (مقدار، نوع داده، اندازه و غیره) را به صورت قوی تایپ کنید. پس از آماده شدن تمام اشیاء پارامتر، با استفاده از فراخوانی Add() به مجموعه شی دستور اضافه می شوند.
در اینجا از ویژگی های مختلفی برای طراحی اشیاء پارامتر استفاده می شود. با این حال، توجه داشته باشید که اشیاء پارامتر از تعدادی سازنده بارگذاری شده پشتیبانی می‌کنند که به شما امکان می‌دهد مقادیر ویژگی‌های مختلف را تنظیم کنید (که منجر به یک پایه کد فشرده‌تر می‌شود). همچنین توجه داشته باشید که Visual Studio 2010 طراحان گرافیکی مختلفی دارد که به طور خودکار مقدار زیادی از این کد پارامتر خسته کننده را برای شما تولید می کنند.
ایجاد یک پرس و جوی پارامتری شده اغلب منجر به کد بیشتری می شود، اما نتیجه یک راه راحت تر برای تنظیم برنامه نویسی دستورات SQL و همچنین عملکرد بهتر است. این تکنیک را می توان برای هر پرس و جوی SQL استفاده کرد، اگرچه پرس و جوهای پارامتری شده در صورت نیاز به اجرای رویه های ذخیره شده بسیار مفید هستند.
قوانین براکت های مربعمخفف [بخش اختیاری] یک ساختار. یک نوار عمودی انتخاب بین گزینه ها را نشان می دهد (var1|var2). بیضی به معنای تکرار ممکن چند بار - 1 بار، 2 بار [، …]
عبارت SELECT
به موتور پایگاه داده Microsoft Access دستور می دهد تا اطلاعات را از پایگاه داده به عنوان مجموعه ای از رکوردها بازگرداند.
نحو
انتخاب کنید [ محمول] { * | جدول.* | [جدول.]میدان 1
[, [جدول.]میدان 2 [, ...]]}
از جانب table_expression [, ...]
دستور SELECT شامل عناصر زیر است.
عنصر | شرح |
محمول | یکی از قضایای زیر: ALL، DISTINCT، DISTINCTROW یا TOP. گزاره ها برای محدود کردن تعداد رکوردهای برگشتی استفاده می شوند. اگر محمولی داده نشود، پیش فرض ALL است. |
نشان می دهد که همه فیلدها از جدول یا جداول مشخص شده انتخاب شده اند |
|
جدول | نام جدولی که رکوردها از فیلدهای آن انتخاب می شوند |
میدان 1, میدان 2 | نام فیلدهایی که حاوی داده هایی هستند که باید بازیابی شوند. اگر چندین فیلد مشخص شده باشد، داده ها به ترتیبی که نام آنها فهرست شده است، بازیابی می شود |
نام مستعار 1, نام مستعار 2 | نام هایی که به عنوان سرفصل ستون ها به جای نام ستون های اصلی استفاده می شوند جداول |
table_expression | یک یا چند نام جدول حاوی داده هایی که باید بازیابی شوند. |
پایگاه_داده خارجی | نام پایگاه داده حاوی جداول مشخص شده در جزء table_expressionاگر آنها در پایگاه داده فعلی نیستند |
یادداشت
برای انجام این عملیات، موتور پایگاه داده مایکروسافت اکسس، جدول(های) مشخص شده را جستجو می کند، ستون های مورد نظر را بازیابی می کند، سطرهایی را انتخاب می کند که شرایط مشخص شده را دارند، و ردیف های حاصل را به ترتیب مشخص شده مرتب یا گروه بندی می کند.
عبارات SELECT داده های پایگاه داده را تغییر نمی دهند.
دستور SELECT معمولاً اولین کلمه دستور SQL است (عبارت SQL (رشته). عبارتی که تعریف می کند دستور SQL، مانند SELECT، UPDATE، یا DELETE و شامل بندهایی مانند WHERE یا ORDER BY. عبارات/رشته های SQL معمولاً در پرس و جوها و توابع آماری استفاده می شوند.) بیشتر دستورات SQL یا عبارت SELECT یا عبارت SELECT...INTO هستند.
حداقل نحو برای دستور SELECT به شرح زیر است:
انتخاب کنید زمینه هایاز جانب جدول
برای انتخاب تمام فیلدهای جدول می توانید از ستاره (*) استفاده کنید. مثال زیر تمام فیلدهای جدول Employees را انتخاب می کند.
SELECT * FROM کارکنان.
اگر نام فیلد در چند جدول در عبارت FROM گنجانده شده است، قبل از آن نام جدول و عبارت را قرار دهید. «.» (نقطه). در مثال زیر، فیلد «دپارتمان» در جداول «کارمندان» و «سرپرست» وجود دارد. دستور SQL بخش ها را از جدول Employees و نام سرپرست ها را از جدول Supervisors انتخاب می کند.
کارمندان را انتخاب کنید روسا یا مدیران واحدها. نام اجرایی FROM Employees INNER JOIN Executives WHERE Employees. بخش = مدیران بخش؛
هنگامی که یک شی RecordSet ایجاد می کنید، نام فیلد جدول توسط موتور پایگاه داده Microsoft Access به عنوان نام شی "Field" در شی استفاده می شود. RecordSet. اگر نام فیلد نیاز به تغییر دارد یا توسط عبارتی که فیلد را ایجاد می کند ارائه نمی شود، از یک کلمه رزرو شده استفاده کنید (کلمه رزرو شده. کلمه ای که عنصری از یک زبان است، مانند ویژوال بیسیک. کلمات رزرو شده شامل نام عبارات، توابع داخلی و انواع داده ها، روش ها، عملگرها و اشیاء.) AS. مثال زیر نشان می دهد که چگونه از هدر "Day" برای نام گذاری شی برگشتی استفاده می شود رشتهدر شی دریافتی RecordSet.
انتخاب روز تولد به عنوان روز از کارکنان؛
هنگام کار با توابع انبوه یا پرس و جوهایی که نام اشیاء مبهم یا یکسان را برمی گرداند رشته، باید از بند AS برای ایجاد یک نام شی متفاوت استفاده کنید رشته. در مثال زیر، شی برگردانده شده است رشتهدر شی دریافتی RecordSetنام "سرشماری" داده شده است.
SELECT COUNT(EmployeeCode) AS Census FROM Employees.
هنگام کار با یک عبارت SELECT، می توانید از بندهای اضافی برای محدود کردن و سازماندهی بیشتر داده های بازیابی شده استفاده کنید. برای اطلاعات بیشتر، به موضوع راهنمای پیشنهادی که استفاده می‌کنید مراجعه کنید.
بند FROM
جداول و پرس و جوهایی را مشخص می کند که حاوی فیلدهای فهرست شده در عبارت SELECT هستند.
نحو
انتخاب کنید فیلد_لیست
از جانب table_expression
یک دستور SELECT حاوی یک عبارت FROM شامل عناصر زیر است:
عنصر | شرح |
فیلد_لیست | |
table_expression | عبارتی که یک یا چند جدول - منابع داده را تعریف می کند. عبارت می تواند یک نام جدول، یک نام پرس و جو ذخیره شده یا یک عبارت نتیجه باشد که با استفاده از عملگر JOIN داخلی، LEFT JOIN یا RIGHT JOIN ساخته شده است. |
پایگاه_داده خارجی | مسیر کامل به پایگاه داده خارجی حاوی تمام جداول مشخص شده در table_expression |
یادداشت
وجود یک عبارت FROM بعد از دستور SELECT الزامی است.
ترتیب فهرست بندی جداول table_expressionمهم نیست
استفاده از جداول پیوندی (جدول پیوندی. جدولی که در فایلی ذخیره می شود که بخشی از پایگاه داده باز نیست اما از طریق Microsoft Access قابل دسترسی است. کاربر می تواند رکوردها را در جدول پیوند داده شده اضافه، حذف و تغییر دهد، اما ساختار آن را نمی تواند تغییر دهد. .) به جای عبارت IN، می توانید فرآیند بازیابی داده ها از یک پایگاه داده خارجی را آسان تر و کارآمدتر کنید.
مثال زیر نحوه بازیابی داده ها از جدول Employees را نشان می دهد.
نام خانوادگی، نام را انتخاب کنید
از کارکنان؛
رکوردهای انتخاب شده برای پرس و جوهای SQL را نشان می دهد ( زبان SQL(زبان پرس و جو ساختاریافته). یک زبان برنامه نویسی پرس و جو ساختاریافته و پایگاه داده که به طور گسترده برای دسترسی، پرس و جو، به روز رسانی و مدیریت داده ها در DBMS های رابطه ای استفاده می شود.
نحو
انتخاب کنید ]]
از جانب جدول
دستور SELECT حاوی این گزاره ها شامل اجزای زیر است:
جزء | شرح |
اگر محمولی در آن گنجانده نشود، ضمنی است. موتور پایگاه داده مایکروسافت اکسس تمام رکوردهایی را انتخاب می کند که با شرایط یک دستور SQL مطابقت دارند (عبارت SQL (رشته). عبارتی که یک دستور SQL را تعریف می کند، مانند SELECT، UPDATE، یا DELETE، و شامل بندهایی مانند WHERE یا ORDER BY است. عبارات/رشته های SQL معمولاً در پرس و جوها و توابع آماری استفاده می شوند. دو مثال یکسان زیر نحوه بازگرداندن تمام رکوردها از جدول Employees را نشان می دهد. از کارمندان سفارش توسط EmployeeCode؛ از کارمندان سفارش توسط EmployeeCode؛ |
|
رکوردهایی که حاوی داده های تکراری در فیلدهای انتخابی هستند را شامل نمی شود. فقط مقادیر منحصر به فرد هر یک از فیلدهای فهرست شده در عبارت SELECT در نتایج پرس و جو گنجانده شده است. به عنوان مثال، برخی از کارمندان فهرست شده در جدول Employees ممکن است نام خانوادگی یکسانی داشته باشند. اگر دو رکورد حاوی نام خانوادگی "Ivanov" در قسمت Last Name باشد، عبارت SQL زیر تنها یک رکورد حاوی نام خانوادگی "Ivanov" را برمی گرداند. نام خانوادگی متمایز را انتخاب کنید اگر جزء DISTINCT حذف شود، پرس و جو هر دو رکورد را با نام خانوادگی "Ivanov" برمی گرداند. اگر عبارت SELECT حاوی چندین فیلد باشد، ترکیبی از تمام مقادیر فیلد تنها در صورتی در نتایج پرس و جو گنجانده می شود که برای آن رکورد منحصر به فرد باشد. نتایج جستجویی که از مؤلفه DISTINCT استفاده می کند به روز نمی شود تا تغییرات بعدی ایجاد شده توسط سایر کاربران را منعکس کند. |
|
داده هایی را از رکوردهایی که به طور کامل تکرار می شوند به جای اینکه حاوی فیلدهای جداگانه با داده های یکسان باشند، حذف می کند. بیایید فرض کنیم که یک پرس و جو ایجاد شده است که جداول "مشتریان" و "سفارش ها" را با استفاده از فیلد "کد مشتری" به هم متصل می کند. جدول مشتریان شامل فیلدهای شناسه مشتری تکراری نیست، اما آنها در جدول سفارشات وجود دارند زیرا هر مشتری می تواند چندین سفارش داشته باشد. عبارت SQL زیر نحوه استفاده از مؤلفه DISTINCTROW را برای فهرست کردن سازمان‌هایی که حداقل یک سفارش انجام داده‌اند، بدون ذکر جزئیات آن سفارش‌ها نشان می‌دهد. عنوان متمایز را از سفارشات پیوستن داخلی مشتریان انتخاب کنید ON مشتریان. شناسه مشتری = سفارشات. کد مشتری سفارش بر اساس عنوان؛ اگر مؤلفه DISTINCTROW حذف شود، پرس و جو به چندین ردیف برای هر سازمانی که چندین بار سفارش داده است منجر می شود. مؤلفه DISTINCTROW فقط هنگام انتخاب فیلدها از برخی از جداول مورد استفاده در پرس و جو تأثیر می گذارد. اگر پرس و جو فقط شامل یک جدول باشد یا اگر فیلدها از همه جداول بازیابی شوند، مؤلفه DISTINCTROW نادیده گرفته می شود. |
|
بالا n | تعداد مشخص شده رکوردهایی را که جزو اولین یا آخرین رکوردها در محدوده مشخص شده توسط عبارت ORDER BY هستند، برمی گرداند. فرض کنید می خواهید اسامی 25 دانش آموز برتر کلاس سال 94 را نمایش دهید. نام، نام خانوادگی WHERE GraduationYear = 2003 ترتیب بر اساس نمره میانگین DESC. اگر عبارت ORDER BY را وارد نکنید، پرس و جو مجموعه تصادفی 25 رکوردی را از جدول Students برمی گرداند که عبارت WHERE را برآورده می کند. محمول TOP شامل انتخابی بین مقادیر مساوی نمی شود. اگر رکوردهای 25 و 26 در مثال قبلی معدل یکسانی داشته باشند، پرس و جو 26 رکورد را برمی گرداند. همچنین می توانید از کلمه رزرو شده PERCENT برای بازیابی درصدی از اولین یا آخرین رکوردها در محدوده مشخص شده توسط عبارت ORDER BY استفاده کنید. فرض کنید به جای 25 نفر برتر، می خواهید 10 درصد پایینی دانش آموزان کلاس فارغ التحصیلی را نمایش دهید. 10 درصد بالا را انتخاب کنید نام، نام خانوادگی WHERE GraduationYear = 2003 ORDER BY GradePointAverage ASC. گزاره ASC خروجی مقادیر را از قسمت پایینی محدوده مشخص می کند. مقداری که از گزاره TOP پیروی می کند باید یک نوع داده Integer باشد. نوع داده پایه که برای ذخیره مقادیر صحیح استفاده می شود. یک متغیر Integer به عنوان یک عدد 64 بیتی (8 بایتی) در محدوده -32768 تا 32767 ذخیره می شود. ) بدون علامت . گزاره TOP تأثیری در به روز رسانی پرس و جو ندارد. |
جدول | نام جدولی که رکوردها از آن بازیابی می شوند. |
همچنین ببینید
عبارت SELECT
بند FROM
بند WHERE
تعیین می کند که کدام رکوردها از جداول فهرست شده در عبارت FROM توسط دستورات SELECT، UPDATE یا DELETE پردازش شوند.
نحو
انتخاب کنید فیلد_لیست
از جانب table_expression
جایی که انتخاب_شرایط
یک دستور SELECT حاوی یک عبارت WHERE شامل بخش های زیر است.
قسمت | شرح |
فیلد_لیست | نام فیلد یا فیلدهایی که به همراه هر نام مستعار بازیابی می شوند (Alias (SQL). نام جایگزین برای یک جدول یا فیلد در یک عبارت. نام مستعار معمولاً به عنوان نام جدول یا فیلد کوتاه تر برای سهولت ارجاع بعدی در یک عبارت استفاده می شود. برنامه ها، برای جلوگیری از ارجاعات مبهم، و به دست آوردن نام های توصیفی بیشتر در هنگام نمایش نتایج پرس و جو.)، گزاره ها (ALL، DISTINCT، DISTINCTROW، یا TOP)، یا با هر پارامتر دیگری از عبارت SELECT. |
table_expression | نام جدول یا جداولی که داده ها از آنها بازیابی می شود. |
انتخاب_شرایط | بیان (عبارت. ترکیبی از عملگرهای ریاضی و منطقی، ثابت‌ها، توابع، نام فیلدها، کنترل‌ها و ویژگی‌هایی که منجر به یک مقدار واحد می‌شود. عبارت می‌تواند محاسبات را انجام دهد، متن را پردازش کند یا داده‌ها را تأیید کند.) که باید با رکوردهای موجود مطابقت داشته باشد. در نتایج پرس و جو |
یادداشت
موتور پایگاه داده مایکروسافت اکسس رکوردهایی را انتخاب می کند که شرایط فهرست شده در عبارت WHERE را داشته باشند. اگر عبارت WHERE مشخص نشده باشد، پرس و جو همه سطرهای جدول را برمی گرداند. اگر یک پرس و جو چندین جدول را مشخص کند اما یک عبارت WHERE یا JOIN را مشخص نکند، پرس و جو یک محصول دکارتی تولید می کند (محصول دکارتی. نتیجه اجرای یک دستور SQL SELECT است که دارای یک عبارت FROM است که به دو یا چند جدول ارجاع می دهد و WHERE یا وجود ندارد. بند JOIN که روش اتصال را مشخص می کند.) جداول.
بند WHERE الزامی نیست، اما در صورت استفاده باید از بند FROM پیروی کند. به عنوان مثال، می توانید همه کارمندان را از بخش فروش (WHERE Department = "Sales") یا همه مشتریان بین 18 تا 30 سال (WHERE Age Between 18 و 30) انتخاب کنید.
اگر یک بند JOIN برای عملیات Join SQL در چندین جدول استفاده نشود، شیء حاصل مجموعه رکوردبه روز رسانی غیرممکن خواهد بود.
عبارت WHERE مشابه عبارت HAVING است و رکوردهای انتخاب شده را مشخص می کند. پس از اینکه رکوردها توسط عبارت GROUP BY گروه بندی شدند، بند HAVING نیز رکورد نمایش داده شده را تعیین می کند.
عبارت WHERE برای حذف رکوردهایی که نیازی به گروه بندی با استفاده از بند GROUP BY ندارند استفاده می شود.
از عبارات مختلف برای تعیین اینکه کدام رکورد توسط دستور SQL برگردانده می شود استفاده کنید. به عنوان مثال، عبارت SQL زیر همه کارکنانی را که حقوق آنها از RUR بیشتر است، انتخاب می کند.
SELECT نام خانوادگی، حقوق و دستمزد از کارکنان WHERE حقوق > 21000;
عبارت WHERE می تواند حاوی حداکثر 40 عبارت باشد که توسط عملگرهای منطقی متصل شده اند (به عنوان مثال، وو یا).
اگر نام فیلدی را وارد کنید که حاوی فاصله یا علائم نگارشی است، باید آن را در پرانتز () قرار دهید. برای مثال، جدول جزئیات مشتری ممکن است حاوی اطلاعاتی در مورد مشتریان خاص باشد.
SELECT [رستوران مورد علاقه مشتری]
تعیین یک استدلال انتخاب_شرایط، لفظ تاریخ (تاریخ تحت اللفظی. هر دنباله ای از نویسه ها در قالب معتبر، محصور در علامت های عددی (#). قالب های معتبر، قالب تاریخ مشخص شده در تنظیمات زبان و استانداردها و قالب جهانی تاریخ هستند.) باید در قالب ایالات متحده نمایش داده شوند. ، حتی اگر از فرمت تاریخ غیر آمریکایی استفاده شود. نسخه موتور پایگاه داده Microsoft Access. به عنوان مثال، تاریخ "10 می 1996" به عنوان 10/5/96 در انگلستان و 05/10/1996 در روسیه نوشته شده است. به یاد داشته باشید که حروف تاریخ را در علامت های عددی (#) بنویسید، همانطور که در مثال های زیر نشان داده شده است.
برای یافتن رکوردهای 10 می 1996 در پایگاه داده انگلستان، از عبارت SQL زیر استفاده کنید:
SELECT * FROM Orders WHERE تاریخ ارسال = #10.05.1996#;
شما همچنین می توانید از تابع استفاده کنید DateValueشناخت پارامترهای بین المللی، توسط مایکروسافت نصب شده است Windows®. به عنوان مثال، برای روسیه از این کد استفاده کنید:
SELECT * FROM Orders WHERE تاریخ ارسال = DateValue("05/10/1996");
و کد زیر برای انگلستان است:
SELECT * FROM Orders WHERE تاریخ ارسال = DateValue("10/5/96");
توجه داشته باشید.اگر ستون مشخص شده در ردیف معیارهای انتخاب از نوع GUID باشد (Replica ID (GUID). یک فیلد 16 بایتی در پایگاه داده مایکروسافت اکسس که برای شناسایی منحصربه‌فرد تکرار استفاده می‌شود. GUIDها برای شناسایی کپی‌ها، مجموعه‌های تکراری، جداول، رکوردها و در پایگاه داده های مایکروسافت اکسس، کدهای GUID را کدهای تکراری می نامند. شرایط انتخاب از یک نحو کمی متفاوت استفاده می کند.
WHERE ReplicaID = (GUID (AB-CDEF0ABCDEF))
مطمئن شوید که پرانتزها و خط تیره های تو در تو به درستی قرار گرفته اند.
صفحه منبع: http://office. /ru-ru/access/HA.aspx؟ pid=CH
GROUP BY بند
رکوردها را با همان مقادیری که در لیست مشخص شده فیلدها هستند در یک رکورد ترکیب می کند. یک مقدار خلاصه برای هر رکورد ایجاد می شود اگر یک تابع تجمع SQL در دستور SELECT گنجانده شود، مانند مجموعیا شمردن.
نحو
انتخاب کنید فیلد_لیست
از جانب جدول
جایی که انتخاب_شرایط
یک دستور SELECT حاوی یک بند GROUP BY شامل عناصر زیر است:
عنصر | شرح |
فیلد_لیست | نام فیلدهایی که همراه با نام مستعار بازیابی می شوند (Alias (SQL). نام جایگزین برای یک جدول یا فیلد در یک عبارت. نام مستعار معمولاً به عنوان جدول کوتاهتر یا نام فیلدها برای سهولت ارجاع بعدی در برنامه ها استفاده می شود. برای جلوگیری از ارجاعات مبهم، و به دست آوردن نام های آموزنده تر هنگام نمایش نتایج پرس و جو.) و آماری توابع SQL، محمولات (ALL، DISTINCT، DISTINCTROW یا TOP)، یا سایر پارامترهای عبارت SELECT |
جدول | |
انتخاب_شرایط | شرط انتخاب اگر عبارت حاوی یک عبارت WHERE باشد، پس از اعمال آن بر روی رکوردها، مقادیر توسط موتور پایگاه داده مایکروسافت اکسس گروه بندی می شوند. |
group_field_list | group_field_list |
یادداشت
بند GROUP BY اختیاری است.
اگر توابع آماری SQL در عبارت SELECT گنجانده نشده باشد، مقادیر خلاصه محاسبه نمی شوند.
مقادیر فیلد GROUP BY که Null هستند (Null. مقداری که می تواند در یک فیلد وارد شود یا در عبارات و پرس و جوها برای نشان دادن داده های گم شده یا ناشناخته استفاده شود. در ویژوال بیسیک، کلمه کلیدی Null یک مقدار Null را مشخص می کند. برخی از فیلدها مانند به عنوان فیلدهای کلید اصلی، ممکن است حاوی مقادیر تهی نباشد.)، گروه بندی می شوند و حذف نمی شوند. با این حال، ارزش ها خالیتوسط هیچ یک از توابع آماری SQL ارزیابی نمی شوند.
عبارت WHERE برای حذف ردیف هایی که نیازی به گروه بندی ندارند استفاده می شود. عبارت HAVING برای فیلتر کردن رکوردها پس از گروه بندی استفاده می شود.
فیلدهایی از لیست فیلد GROUP BY که شامل نوع داده Memo (نوع داده Memo Field. نوع داده فیلد در پایگاه داده Microsoft Access. یک فیلد MEMO می‌تواند حداکثر 65535 کاراکتر باشد.) یا OLE Object (نوع داده فیلد) ندارد. شی OLE" یک نوع داده فیلد که برای ذخیره اشیاء از سایر برنامه‌های کاربردی مرتبط یا تعبیه‌شده در پایگاه داده مایکروسافت اکسس استفاده می‌شود.) می‌تواند به هر فیلدی در هر جدولی که در عبارت FROM مشخص شده است اشاره کند، حتی اگر فیلد در عبارت SELECT گنجانده نشده باشد. برای این کار کافی است حداقل یک تابع آماری SQL در دستور SELECT وجود داشته باشد. موتور پایگاه داده مایکروسافت اکسس اجازه گروه بندی بر اساس فیلدهای حاوی داده های MEMO Field یا OLE Object را نمی دهد.
همه فیلدها در لیست فیلد SELECT یا باید در یک عبارت GROUP BY باشند یا آرگومان های یک تابع تجمیع SQL باشند.
همچنین ببینید
عبارت SELECT
عبارت SELECT...INTO
محمولات ALL، DISTINCT، DISTINCTROW، TOP
بند FROM
داشتن پیشنهاد
ترتیب بر اساس بند
بند WHERE
توابع آماری SQL
صفحه منبع: http://office. /ru-ru/access/HA.aspx؟ pid=CH
داشتن پیشنهاد
رکوردهای گروه بندی شده ای را تعریف می کند که باید در یک عبارت SELECT با یک عبارت GROUP BY ظاهر شوند. پس از اینکه رکوردها توسط بند GROUP BY گروه بندی شدند، عبارت HAVING مواردی را نشان می دهد که شرایط آن را دارند.
نحو
انتخاب کنید فیلد_لیست
از جانب جدول
جایی که انتخاب_شرایط
دسته بندی بر اساس group_field_list
یک دستور SELECT حاوی یک عبارت HAVING شامل عناصر زیر است:
عنصر | شرح |
فیلد_لیست | نام فیلدهایی که همراه با هر نام مستعار بارگیری می شوند (Alias (SQL). نام جایگزین برای یک جدول یا فیلد در یک عبارت. نام مستعار معمولاً به عنوان جدول کوتاهتر یا نام فیلدها برای سهولت ارجاع بعدی در برنامه ها استفاده می شود. برای جلوگیری از ارجاعات مبهم، و به دست آوردن نام های آموزنده تر هنگام نمایش نتایج پرس و جو.) و توابع آماری SQL، گزاره ها (ALL، DISTINCT، DISTINCTROW، یا TOP) یا با سایر پارامترهای عبارت SELECT. |
جدول | نام جدولی که رکوردها از آن بارگیری می شوند |
انتخاب_شرایط | شرط انتخاب اگر عبارت حاوی عبارت WHERE باشد، موتور پایگاه داده مایکروسافت اکسس مقادیر را پس از اعمال آن بر روی رکوردها گروه بندی می کند. |
group_field_list | نام فیلدها (حداکثر 10) مورد استفاده برای گروه بندی رکوردها. ترتیب اسامی در group_field_listسطح گروه بندی را تعیین می کند - از بالاترین به پایین ترین |
group_condition | عبارتی که رکوردهایی را که قرار است نمایش داده شوند را مشخص می کند |
یادداشت
بند HAVING اختیاری است.
عبارت HAVING مشابه عبارت WHERE است که انتخاب رکوردها را تعیین می کند. پس از گروه بندی رکوردها با عبارت GROUP BY، عبارت HAVING رکوردهایی را که قرار است نمایش داده شوند را تعیین می کند.
نوع کد را انتخاب کنید،
جمع (در انبار)
از محصولات
گروه بندی بر اساس نوع کد
داشتن مجموع (InStock) > 100 و مانند "TEL*"؛
عبارت HAVING می تواند حاوی حداکثر 40 عبارت باشد که توسط عملگرهای منطقی مانند وو یا.
صفحه منبع: http://office. /ru-ru/access/HA.aspx؟ pid=CH
ترتیب بر اساس بند
رکوردهای برگردانده شده توسط پرس و جو را به ترتیب صعودی یا نزولی مقادیر فیلد(های) مشخص شده مرتب می کند.
نحو
انتخاب کنید فیلد_لیست
از جانب جدول
جایی که انتخاب_شرایط
[, میدان 2 ][, ...]]]
یک دستور SELECT که حاوی عبارت ORDER BY است شامل عناصر زیر است.
عنصر | شرح |
فیلد_لیست | نام فیلدهایی که همراه با نام مستعار بازیابی می شوند (Alias (SQL). نام جایگزین برای یک جدول یا فیلد در یک عبارت. نام مستعار معمولاً به عنوان جدول کوتاهتر یا نام فیلدها برای سهولت ارجاع بعدی در برنامه ها استفاده می شود. برای جلوگیری از ارجاعات مبهم، و به دست آوردن نام های آموزنده تر هنگام نمایش نتایج پرس و جو.) و توابع آماری SQL، گزاره ها (ALL، DISTINCT، DISTINCTROW، یا TOP) یا با سایر پارامترهای عبارت SELECT. |
جدول | نام جدولی که رکوردها از آن بازیابی می شوند |
انتخاب_شرایط | شرایط انتخاب اگر عبارت حاوی یک عبارت WHERE باشد، پس از اعمال آن بر روی رکوردها، موتور پایگاه داده مایکروسافت اکسس مقادیر رکوردها را ترتیب می دهد. |
میدان 1, میدان 2 | نام فیلدهایی که رکوردها بر اساس آنها مرتب می شوند. |
یادداشت
بند ORDER BY اختیاری است. زمانی که نیاز به نمایش داده ها به شکل مرتب شده دارید باید از آن استفاده شود.
ترتیب مرتب سازی پیش فرض این است (ترتیب مرتب سازی. روشی برای مرتب کردن داده ها بر اساس مقادیر و نوع آن. داده ها را می توان بر اساس حروف الفبا، مقادیر عددی یا بر اساس تاریخ مرتب کرد. ترتیب مرتب سازی می تواند صعودی باشد (0 تا 100، A تا Z) یا نزولی (از 100 به 0، از Z به A).) صعودی (از A به Z، از 0 تا 9). مثال‌های زیر مرتب‌سازی نام کارمندان بر اساس نام خانوادگی را نشان می‌دهند.
نام خانوادگی، نام را انتخاب کنید
از کارمندان
سفارش بر اساس نام خانوادگی؛
نام خانوادگی، نام را انتخاب کنید
از کارمندان
سفارش بر اساس نام خانوادگی ASC;
برای مرتب کردن فیلدها به ترتیب نزولی (از Z تا A، 9 تا 0)، کلمه رزرو شده DESC را به نام هر فیلد اضافه کنید. مثال زیر مرتب‌سازی را به ترتیب نزولی بر اساس حقوق کارکنان نشان می‌دهد.
نام خانوادگی، حقوق و دستمزد را انتخاب کنید
از کارمندان
سفارش بر اساس حقوق DESC، نام خانوادگی;
اگر فیلدهایی را در بند ORDER BY مشخص کنید که حاوی داده هایی از نوع MEMO Field (نوع داده Memo Field. نوع داده فیلد در پایگاه داده Microsoft Access. یک فیلد MEMO می تواند حداکثر 65535 کاراکتر باشد.) یا OLE Object Field (OLE Object) نوع داده فیلد "یک نوع داده میدانی که برای ذخیره اشیاء از برنامه های دیگر مرتبط یا جاسازی شده در پایگاه داده مایکروسافت اکسس استفاده می شود.)، این یک خطا ایجاد می کند. موتور پایگاه داده Microsoft Access نمی تواند این نوع فیلدها را مرتب کند.
عبارت ORDER BY معمولا آخرین بند در یک دستور SQL است (عبارت SQL (رشته). عبارتی که یک دستور SQL را تعریف می کند، مانند SELECT، UPDATE، یا DELETE، و شامل بندهایی مانند WHERE یا ORDER BY. دستورات SQL است. /strings معمولاً در پرس و جوها و توابع آماری استفاده می شود.).
می توانید فیلدهای اضافی را در بند ORDER BY قرار دهید. رکوردها ابتدا بر اساس فیلدی که ابتدا در عبارت ORDER BY مشخص شده است مرتب می شوند. سوابق با مقادیر یکسان در فیلد اول بر اساس فیلد مشخص شده توسط فیلد دوم مرتب می شوند و به همین ترتیب.
همچنین ببینید
عبارت SELECT
عبارت SELECT...INTO
محمولات ALL، DISTINCT، DISTINCTROW، TOP
بند FROM
GROUP BY بند
داشتن پیشنهاد
بند WHERE
توابع آماری SQL
صفحه منبع: http://office. /ru-ru/access/HA.aspx؟ pid=CH
عملیات JOIN داخلی
در صورتی که فیلدهای اتصال این جداول حاوی مقادیر یکسانی باشند، رکوردهای دو جدول را به هم متصل می کند.
نحو
از جانب میز 1پیوستن داخلی جدول 2بر میز 1.میدان 1 مقایسه_اپراتور جدول 2.میدان 2
عملیات JOIN داخلی از عناصر زیر تشکیل شده است:
عنصر | شرح |
میز 1, جدول 2 | نام جداول حاوی رکوردهایی که باید ملحق شوند |
میدان 1, میدان 2 | نام فیلدهایی که باید پیوند داده شوند. فیلدهای غیر عددی باید از یک نوع داده باشند (نوع داده. مشخصه فیلدی که نوع داده ای را که فیلد می تواند داشته باشد مشخص می کند. انواع داده ها عبارتند از: Boolean، Integer، Long، Currency، Single، Double، Date، String، و Variant (پیش فرض).) و حاوی داده هایی از همان نوع هستند. اما ممکن است نام این فیلدها متفاوت باشد |
مقایسه_اپراتور | هر عملگر مقایسه: (=،<, >, <=, >= یا<>) |
