نام کتاب ، مبلغ فروش کتاب هایی که بیش از 500 دلار فروش داشته باشند
اسم کتاب رو از titles میاریم و مبلغ فروش رو از qty* price میاریم
اول میایم و inner join میزنیم :
تعدادش 21 میشه ولی تکراری توش داره برای همین هم باید بیایم و group by استفاده کنیم
اینجا داره میگه که در یکی از فاکتور ها 821 شده فروش این کتاب ، باید اینجا بیایم و از group by استفاده کنیم
نکته : ستون ها و column های اطلاعاتی که به واسطه ی aggrigate function ها تولید میشن رو نمیتونم ببریم توی where condition پس بنابراین میایم از having استفاده میکنیم.
دو مدل ترتیب داریم ، نوشتاری و اجرایی
ترتیب اجرای sql اول from هستش بعد قسمت on توی join ها و بعد خوده join ها و بعد where condition ها و بعدیش group by و بعدیش having و بعدیش دستور select و بعدی distinct و بعدی order by و بعدی limit هستش
SQL Queries Execution Order - JigNect Technologies Pvt Ltd SQL Queries Execution Order
از having زمانی استفاده میکنیم که بخوایم از دیتایی که توسط aggrigate function تولید شده بیایم و شرط روشون بزاریم نام و نام خانوادگی نویسنده هایی که بیشتر از یک کتاب دارند.
از یه روش دیگه بخوایم بریم :
این روش از drived table استفاده میکنه و این روش سرعتش بیشتر میشه ،
راه مناسب اینه که توی drived table ها چیزی که میخوایم رو بر اساس جزییات روی فیلد PK اش بدست بیاریم و بعد ازش استفاده کنیم
نام گرونترین کتاب رو میخوایم
روش درستش میشه این :
خوب این نکته رو باید در نظر داشته باشیم که drived table به ما یه جدول میده ولی استفاده از sub query در نوع یک بهمون یک مقدار رو برمیگردونه
ولی در drived table ها در from و join اازش استفاده میکنیم ، و اسم هم میگرفت
ولی sub query نوع یک یه تک مقدار میده و دور پرانتر هم بهش اسم ندادیم و توی where condition ازش استفاده میکنیم
تفاوت اولی با دومی اینه که توی اولی اگر دو تا کتاب داشته باشیم که قیمتشون max باشه میره هر دوتای اون ها رو میاره ولی توی دومی فقط همون اولی رو میاره
تایم 1:1
اینجا در مورد دومی اومدیم اول اون هایی که بیشتر از 1 یکی کتاب دارند رو جدا کنه و بعد ادامه ی مطالب در کد اولی این کار در مرحله ی آخر انجام میشه
خوب sub query ها نوع 1 بهمون یک تک مقدار تولید میکنه و بهش نمیتونیم اسم بدیم
خوب top قبلش Distinct قرار میگیره :
در اولی میاد 5 تای اول titles رو بهمون میده و توی دومی میاد 10 درصد از تعداد کل کتاب ها رو محاسبه میکنه و بعد به همون تعداد میاره برامون که میشه 2 تا
SQL SELECT LIMIT, TOP, FETCH FIRST (With Examples) (programiz.com)
خوب with ties استفاده کنیم باید بیایم از دستور order by کنارش استفاده کنیم
ما دو تا 100 داشتیم
خوب اول top 1 میره یک جوابی میاره و بعد with ties میره دیتاهایی که شبیه به top 1 بود رو هم میاره یعنی اول میره 100 رو میاره و بعد وقتی که از with ties استفاده کردیم میره هر چند تایی که مشابه اش هستش هست رو هم میاره ، که در این جا چون ما یه 100 دیگه هم داریم اون رو هم میاره
اگر این جا top 2 رو هم بزنیم نتیجه یکی هستش چون که در کل دو تا رکورد داره میاره که 100 هستن و دیگه 100 دیگری وجود ندارد پس جوابش یکیه ، ولی اگر top 3 بزنیم جوابش متفاوته چون که توی دیتایی که میاره 100 100 و 90 رو داریم ، در این حالت میره و دیتایی که شبیه به 90 هستش رو میاره
خوب اینجا اومدیم برای اجرای این کار کوئری رو به به دو روش متفاوت نوشتیم :
در حالت قبلی کوئری اولی بهتره
چون که میخواد بره و کل دیتاهایی که وجود داره رو به ترتیب بچینه
نکته order by کوئری رو میپوکونه و برای بهتر شدنشون باید براشون index تعیین کنیم
خوب with ties فقط با top میادش و وقتی که از این دو تا داریم استفاده میکنیم باید از order by استفاده کنیم
جای نوشته های سبز باید بیایم و عدد بزاریم و بقیه اش هم syntax هستش
شماره ایندکس از 0 شروع میشه
خوب offset مشخص کننده ی این هستش که از کجا شروع کنه دیتا رو بیاره
خوب fetch هم به تعدادش از اونجا میره دیتا ها رو میاره
ما داریم با کدمون میگیم که برو توی هارد اون قسمتی که دیتا ذخیره شده و شماره ی این دیتا ها از 0 شروع میشه و بیا اون دیتا رو از 0 تا اون جایی که گفتیم ( که اینجا میشه 5 تا) یعنی از 0 تا 4 برو و دیتا رو بگیر و بیار
توی صفحه ی بعدی offset میشه 5
در جلوتر به صورت فانکشن میتونیم این کار رو انجام بدیم ، برنامه نویس اعداد رو توی کوئری وارد میکنه و اینطوری میاد دیتایی که میخواد رو میگیره که ببره برای نمایش روی سایت
در oracle ما top نداریم و باید از offset , fetch استفاده کنیم به این شکل که offset 0 هستش و fetch هم همونقدری که میخوایم و اینطوری ما میتونیم عملکرد top رو در oracle پیاده سازی کنیم
نام پنجمین گرانقدر ترین کتاب یا کتاب ها رو لیست کنیم :
کوئری که نام کتاب و قیمت کتاب و قیمت میانگین کل کتاب ها و avg و اختلاف قیمت هر کتاب از میانگین قیمت کل کتاب ها
اینجا داستانی که اتفاق افتاده اینه که price با avg یکسان شده چون که اینطوری محاسبه کرده که برای هر کتاب اومده قیمت اون کتاب رو تقسیم بر یک کرده که یعنی میشه همون قیمت خودش
نکته در اینجا هر بار از Select استفاده میکنیم میره توی دیتابیس و دیتا هارو میاره یعنی یه بار میره price رو میاره بعد یه بار اون avg و بعد یه بار دیگه اختلاف اون avg این داستان برای هر row هر سری تکرار میشه و به همین دلیل کند هستش و خوب نیستش
در حجم زیاد اطلاعات کلا میپوکه
توضیح ضرب دکارتی :
اختلاف 20 درصد در سرعت اجرا دارند
کوئری ها باید صحیح باشند و بعد زیبا نوشته شده باشند و بعد سریع باشند
فانکشن هایی که خوده sql دارن خیلی سریع هستند ، این ها از سال 2008 به بعد اضافه شدن
همه نویسنده ها رو لیست کنه ، بر حسب کد نویسنده sort اش کنه و در یک فیلد در مجموعه جواب شماره ردیف بندازه
اینجا شماره ی سطر هایی که هستش برای نمایش و برای خوده ssms هستش ، حالا اگر ما بخوایم خودمون هم این index ها برای دیتامون تولید کنیم باید چی کار کنیم :
خوب به خاطر این که ما یه select بیرونی و یک select داخلی اول select بیرونی انجام میشه و به ازای هر select داخلی هم دوباره اجرا میشه
خوب در تصویر بالا هر باری که select داخلی داره اجرا میشه به این شکل که برای سطر اول کل دیتاش رو میورد یعنی au id, au lname , … و دونه دونه میاد count اش میکنه و بعد ما چون اومدیم و order by au id رو انجام دادیم یعنی هر چی au id از بالا به پایین میره بیشتر میشه بنابراین اومدیم تنظیم کردیم که اگر au id کوچکتر مساوری اون مقدار بود بیاد شمارش رو روشون انجام بده
خوب حالا اومدیم به جای اون عدد دستی اومدیم از a.au id استفاده کردیم ، که همونطور که میدونید a همون alias جدول authors هستش که اومدیم ازش استفاده کردیم یعنی از au id که select خارجی داره استفاده کردیم ،
و sql 2008 به قبل اینطوری شماره ایندکس درست میکنه ولی از بعدش روشش تغییر کرده :
خوب حالا میخوایم شمارنده index مون هر بار که عنوانمون تغییر میکنه بیاد و از 1 شروع کنه شمردن ، برای این کار میایم از partion استفاده میکنیم
تایم : 2:52
همه ی اشخاصی که اهل استان تهران و قم باشند رو بیاریم بعد میتونیم با اون field name بیایم اون دیتایی که گرفتیم رو به ترتیب اسم بچینیم ، در حالت اول مقادیر ثابت هستند ولی در حالت دوم داریم یه متغییر رو میزاریم قسمت دوم حالت دوم میشه sub query نوع سوم ، توی این حالت ممکنه که بیش از یک جواب داشته باشیم
اینی که این بالا نوشتم اینطوریه که اگر ما مثلا میومدیم 2 با تعریف میکردیم با مساوی میشد همینی که نوشتیم
کوئری که همه ی نوسیندگانی رو لیست بکنه که هم ایالتی نویسنده ای هستند به نام livia karsen
حالا ممکنه یکی با همین اسم و فامیل توی یه ایالت دیگه باشه
مشخصات نویسنده هایی که کتاب ندارند رو بیاریم
حالا:
خوب توی حالت عکس بالایی اول پرسیدیم که کیا کتاب بیزینسی دارند و بعد گفتیم اون ها ، نه
توی مورد دوم گفتیم که کیا کتاب بیزینسی ندارند اون ها رو میخوایم ، حالا اتفاقی که اینجا میفته اینه که مثلا یک نویسنده هستش که هم کتاب روانشناسی داره و هم کتاب تجارتی ولی به خاطر اون کتاب روانشناسی ( غیر بیزینسی ) در این دسته قرار میگیره
حالا در مورد بالایی فقط یک نفر هستش که کتاب غیر بیزینسی داره و بقیه شون اصلا کتاب ندارند
اگر بخوایم فقط بیایم از بین اون هایی فقط کتاب چاپ کردن رو پیدا کنیم باید شرط بزاریم »:
این دو حالت پایین کاملا اشتباهن
خوب
بین نرم افزار excel و sql server یه exe نیاز هست که این دو تا رو بهم وصل کنه نکته وقتی که یه ورژنی از office روی سیستممون نثب باشه دیگه نمیتونیم ورژن دیگه ای روش نصب کنیم
اگر ورژن های مختلفی از excel رو داریم که میخوایم بیایم و اون ها رو بریزیم توی sql باید از نرم افزار access database engine استفاده کنیم
اونجا ace همون access database engine هستش
اینجا داره میگه که این ورژنی که انتخاب کردی روی این سیستم نصب نشده
اگر توی لیست نبود به خاطر نصب نکردن connectivity client
خوب اگر توی هیچ کدوم از اون دیتابیس هایی که داریم نمیخواستیم این دیتا رو بریزیم میایم میزنیم روی new و یه دیتابیس جدید میسازیم
میتونیم اسمش رو عوض کنیم یا بریزیمش توی table ای که هست
خوب اونجا اگر تیک drop and re create destination table رو بزنیم جدول رو یه بار از اول پاک میکنه و دوباره میسازه برای همین دیتاها در بالا قرار میگیره
اینجا اومده تایپ ها رو در بزرگترین و بیشترین حجم در نظر گرفته ، چرا که ممکنه یکی از این رکورد ها به طور مثال اعشار خورده باشه و همون یه دونه باعث میشه که import انجام نشه برای همین به جای عدد صحیح اومده از float که اعداد اعشاری رو ساپورت میکنه استفاده کرده
برای همه ی تایپ ها همین کار رو کرده ، چرا که میخواد import انجام بشه و بعد میتونیم تغییرش بدیم
اتمام جلسه