توابع داخلی SQLطراحی شده برای تسهیل و سرعت بخشیدن به پردازش داده ها. ویژگی این است که می توان آنها را مستقیماً در عبارت مشخص کرد. تمام توابع داخلی را می توان به طور مشروط به گروه ها تقسیم کرد.
توابع ریاضی:
ABS(معنی) - قدر مطلق عدد را برمی گرداند.
گرد(ارزش، دقت) - یک مقدار عددی گرد شده به آرگومان مشخص شده را برمی گرداند دقتتعداد ارقام اعشار؛
امضاء کردن(معنی) - اگر عدد منفی باشد یک منهای و در غیر این صورت یک مثبت برمی‌گرداند.
قدرت(ارزش، درجه) - یک عدد را به توان می رساند.
SQRT(معنی) - جذر یک عدد را استخراج می کند.
سقف(به معنی)- نزدیکترین عدد صحیح را بزرگتر یا مساوی مقدار برمی گرداند.
- کف(به معنی)نزدیکترین عدد صحیح را کمتر یا مساوی با مقدار برمی‌گرداند.
توابع رشته:
ASCII(خط) - برمی گردد ASCIIکد اولین کاراکتر رشته؛
CHآآر(عدد) – برگرداندن یک کاراکتر توسط ASCIIکد؛
LEN (خط) - طول رشته را به کاراکتر برمی گرداند، به استثنای فاصله های انتهایی.
LTRIM(خط)/ RTRIM(خط)-فاصله های ابتدای/پایان رشته را حذف می کند.
ترک کرد(رشته، شماره)/ آرIGHT(رشته، شماره)- آرگومان مشخص شده را برمی گرداند عددتعداد کاراکترهای رشته، از لبه چپ/راست شروع می شود.
SUBSTRING(خط، موقعیت، طول) - یک رشته فرعی با طول مشخص شده از رشته را برمی گرداند که از موقعیت مشخص شده شروع می شود.
پایین تر(خط) /بالا(خط) - یک رشته تبدیل شده به کمتر / را برمی گرداند حروف بزرگو غیره.
توابع کار با تاریخ:
GETDATE() - مقداری را برمی گرداند که حاوی تاریخ و زمان رایانه ای است که نمونه SQL Server در آن اجرا می شود.
روز(تاریخ ارزش)- یک عدد را از تاریخ مشخص شده برمی گرداند.
ماه(تاریخ ارزش)- شماره ماه را از تاریخ مشخص شده برمی گرداند.
سال(تاریخ ارزش)- ارزش سال را از تاریخ مشخص شده برمی گرداند.
DATENANE( قسمت، ارزش_تاریخ) - برمی گردد رشته کاراکتر، نمایانگر قسمت مشخص شده ( روز, ماه, ساعتو غیره.) از تاریخ مشخص شده؛
DATEPART( قسمت، ارزش_تاریخ) - یک عدد صحیح نشان دهنده قسمت مشخص شده را برمی گرداند ( روز, ماه, ساعتو غیره.) از تاریخ معین.
توابع تبدیل نوع داده
قالب (معنی مانند نوع داده)
تبدیل(نوع داده, معنی)
بحث و جدل معنیدر توابع، مقداری که باید تبدیل شود را مشخص می کند.
7.3. دستورات زبان تعریف داده
زبان تعریف داده حاوی دستوراتی برای ایجاد، اصلاح و حذف پایگاه داده و اشیاء آن است.
یک جدول ایجاد کنید
ایجاد یک جدول جدید توسط دستور انجام می شود ایجاد جدول. این دستور ساختار جدول، هر ستون جدول و محدودیت های یکپارچگی را که باید برای جدول تنظیم شود، توصیف می کند.
دستور دستور:
ایجاد جدول جدول_نام (( column_description |محاسبه_ستون_ناممانند بیان | table_level_integrity_constraints) [، ...])
نام جدول یک شناسه با حداکثر طول 128 کاراکتر است.
یک جدول می تواند شامل یک ستون محاسبه شده باشد، در این صورت مقدار ستون با عبارت ذخیره شده در ساختار جدول تعیین می شود. یک ستون محاسبه شده را نمی توان تغییر داد، بنابراین نمی تواند NOT NULL، UNIQUE، PRIMARY KEY، FOREIGN KEY یا DEFAULT باشد.
نحو برای توصیف ستون جدول به صورت زیر است:
نام ستون نوع داده[(اندازه)]
[(مقدار_پیش‌فرض | IDENTITY [(ارزش، مرحله)]}]
[column_level_integrity_constraints]
DEFAULT - به شما امکان می دهد مقدار اختصاص داده شده به ستون را در رکورد جدید اضافه شده مشخص کنید.
IDENTITY نشان می دهد که یک ستون با شماره گذاری خودکار (ستون شمارنده) در حال ایجاد است. فقط یک ستون شمارنده را می توان در جدول تعریف کرد. پارامتر value مقدار اولیه شمارنده را مشخص می کند و پارامتر step مرحله افزایشی را مشخص می کند. اگر این پارامترها تنظیم نشده باشند، دارای مقدار 1 هستند. IDENTITY فقط برای ستون هایی که دارای انواع صحیح یا اعشاری هستند تنظیم می شود. درج مقادیر در ستون IDENTITY مجاز نیست.
دو گروه از محدودیت های یکپارچگی وجود دارد که توسط DBMS مدیریت می شود:
محدودیت‌های یکپارچگی اعلامی که هنگام ایجاد یا اصلاح جدول اعلام می‌شوند.
محدودیت‌های یکپارچگی رویه‌ای که توسط محرک‌ها مدیریت می‌شوند.
محدودیت های یکپارچگی اعلامی می تواند محدودیت های سطح جدول و محدودیت های سطح جدول باشد. یک محدودیت در سطح ستون فقط برای یک ستون اعمال می شود. به هر محدودیت یکپارچگی اعلامی می توان یک نام داد.
شرح محدودیت های سطح ستون دارای نحو زیر است:
((کلید اولیه | منحصر به فرد | NOT NULL ) |مرجع کلید خارجی table_name( نام ستون)
|بررسی_بیان_بولی)
نام محدودیت یکپارچگی داده باید در پایگاه داده منحصر به فرد باشد. محدودیت هایی را که می توان در سطح ستون تعریف کرد در نظر بگیرید:
محدودیت کلید اولیه. همه مقادیر کلید اصلی جدول باید منحصر به فرد باشند و تهی نباشند. یک جدول فقط می تواند یک کلید اصلی داشته باشد. اگر ترکیبی باشد، محدودیت های یکپارچگی در کلید اصلی در سطح جدول تنظیم می شود.
محدودیت های منحصر به فرد مقدار ستون UNIQUE. این بدان معناست که یک جدول نمی تواند دو رکورد با مقدار یکسان در آن ستون داشته باشد.
یک محدودیت NOT NULL که مانع از ذخیره یک مقدار NULL توسط ستون می شود.
محدودیت کلید خارجی FOREIGN KEY (محدودیت یکپارچگی مرجع). برای ستونی که یک کلید خارجی است، از REFERENCES برای تعیین نام جدولی که پیوند به آن ایجاد می شود، و نام ستونی از این جدول که به آن پیوند داده می شود، استفاده کنید. چنین جدولی جدول اصلی (والد) در رابطه با جدول ایجاد شده است. ستونی در جدول اصلی که مقادیر آن در حال پیوند هستند باید دارای یک محدودیت PRIMARY KEY باشد.
اگر کلید جدول خارجی از چندین فیلد تشکیل شده باشد، محدودیت کلید خارجی باید در سطح جدول مشخص شود. در این صورت، باید تمام ستون های موجود در کلید خارجی را فهرست کنید، نام جدول اصلی و نام ستون های جدول اصلی را که کلید خارجی به آن اشاره دارد، مشخص کنید.
یکپارچگی ارجاعی قوانینی را برای افزودن و اصلاح داده ها در یک جدول با استفاده از یک کلید خارجی و محدودیت کلید اولیه مربوط به آن ایجاد می کند. بندهای ON UPDATE و ON DELETE برای یک کلید خارجی قوانین زیر را برای اصلاح داده های مرتبط تعریف می کنند:
NO ACTION - به شما امکان می دهد فقط مقادیری را در جدول اصلی تغییر دهید (حذف کنید) که مقادیر کلید خارجی مربوطه را در جدول فرزند ندارند. این قانون به طور پیش فرض در حال اجرا است.
CASCADE به این معنی است که هر مقدار از کلید خارجی جدول فرزند به طور خودکار تغییر می کند (حذف می شود) زمانی که مقدار کلید اصلی جدول والد اصلاح شود.
SET NULL به این معنی است که در صورت تغییر (حذف) کلید اصلی جدول والد، در تمام ردیف های مرجع جدول فرزند، مقادیر کلید خارجی به طور خودکار مقادیر NULL تخصیص داده می شود.
SET DEFAULT به این معنی است که در صورت تغییر (حذف) کلید اصلی جدول والد، در تمام سطرهای ارجاع دهنده جدول فرزند، مقادیر کلید خارجی به طور خودکار به مقادیر پیش فرض اختصاص داده می شود.
بیایید نمونه پایگاه آموزشی "دانشگاه" را تکمیل کنیم که طراحی آن در فصل مورد توجه قرار گرفت. 4.3 جداول نظم و انضباط و بیانیه عمومی. جداول 6 و 7 ساختار منطقی جداول را توضیح می دهند.
جدول 6
ساختار منطقی شی اطلاعات DISCIPLINE
جدول 7
ساختار منطقی شی اطلاعات GENERAL STATEMENT
|
علامت کلید |
فرمت فیلد |
||||
|
نام |
دقت |
||||
|
ثبت شماره کتاب |
شماره دفترچه سوابق دانشجویی ثبت شده |
متن | |||
|
کد رشته |
کد رشته |
عددی |
عدد صحیح بلند | ||
|
عددی | |||||
بیایید درخواست هایی برای ایجاد جداول مطابق با آنچه در شکل نشان داده شده است ارائه دهیم. 35 مدل پایگاه داده اینفولوژیک.
برنج. 35. طرحواره پایگاه داده "دانشگاه"
همانطور که از طرح پایگاه داده می بینید، جدول FACULTY یک جدول مستقل است، بنابراین ابتدا ایجاد می شود. درخواستی برای ایجاد جدول با در نظر گرفتن توضیحات ساختار منطقی در جدول. 4 (ص 61) به این صورت خواهد بود:
ایجاد هیئت علمی جدول
([شماره بخش] tinyint PRIMARY KEY , [نام بخش] char(50))
جدول SPECIALTY نیز مستقل است، ما آن را در مرحله دوم ایجاد می کنیم. هنگام ایجاد یک پرس و جو، از توضیحات ساختار منطقی در جدول استفاده می کند. 5 (ص 62).
ایجاد جدول [تخصص] (
[شماره تخصصی] int PRIMARY KEY،
[نام تخصص] کاراکتر (60)،
[هزینه تحصیل] )
جدول GROUP به جدول FACULTY و SPECIALTY بستگی دارد. ما هنگام ایجاد پرس و جو از جدول 3 (ص 61) استفاده می کنیم و ستون ها را در نظر می گیریم شماره دانشکدهو شماره تخصصیکلیدهای خارجی هستند:
ایجاد جدول [گروه] (
[شماره گروه] smallint PRIMARY KEY،
[شماره تخصصی] تخصص منابع کلیدی خارجی( اتاق خاص- اخبار)در حذف CASCADE در UPDADE CASCADE،
[شماره دانشکده] دانشکده منابع کلیدی خارجی کوچک( اتاق دانشکده) ON DELETE CASCADE ON UPDADE CASCADE، [شماره دوره] tinyint)
جدول STUDENT یک جدول وابسته به GROUP است. بر اساس داده های جدول 2 (ص 60)، ما یک پرس و جو خواهیم کرد. ما همچنین توجه داشته باشید که ستون شماره گروهکلیدهای خارجی هستند:
ایجاد جدول [دانش آموز] (
[شماره گروه] smallint NOT NULL FOREIGN KEY References group( اتاق گروه ها) ,
[نام خانوادگی] char(15) NOT NULL،
[تاریخ تولد] تاریخ تاریخ NOT NULL،
[تجاری] بیت NOT NULL،
[نام ثبت] char (9))
داده های جدول GENERAL RECORD به جداول STUDENT و DISCIPLINE بستگی دارد. در این جدول، کلید اولیه ترکیبی و هر یک از ستون های کلید اصلی یک کلید خارجی است (جدول 7 و شکل 35 را ببینید).
بیایید از توضیحات ساختار منطقی جدول رشته ارائه شده در جدول 6 استفاده کنیم و پرس و جو کنیم:
ایجاد جدول [رشته] (
[کد رشته] int PRIMARY KEY،
[نام رشته] char (50))
اکنون می توانید یک پرس و جو برای ایجاد یک جدول بیانیه کلی ایجاد کنید. از آنجایی که کلید اصلی جدول یک کلید ترکیبی است، محدودیت کلید اصلی باید در سطح جدول تنظیم شود. به عنوان مثال، اجازه دهید محدودیت های کلید خارجی را نیز در سطح جدول تنظیم کنیم. درخواست به این صورت خواهد بود:
ایجاد جدول [برگ عمومی] (
[کد رشته]
[شماره کتاب نمره] کاراکتر (8)،
[نمره] NOT NULL، کلید اولیه ([کد رشته]، [شماره کتاب کلاسی])، کلید خارجی ([کد رشته]) مراجع [رشته] ([کد رشته])، کلید خارجی ([شماره کتاب کلاسی]) مراجع [دانشجو ] ([شماره کتاب نمره]))
تغییر ساختار جدول
تغییر ساختار جدول با دستور ALTER TABLE انجام می شود. با استفاده از دستور می‌توانید ویژگی‌های ستون‌های موجود را تغییر دهید، آنها را حذف کنید یا ستون‌های جدیدی به جدول اضافه کنید، محدودیت‌های یکپارچگی را هم در سطح ستون و هم در سطح جدول مدیریت کنید. تخصیص بسیاری از پارامترها و کلمات کلیدی مشابه تخصیص پارامترها و کلمات کلیدی مربوطه در دستور CREATE TABLE است.
حذف جدول
حذف جدول با استفاده از دستور DROP TABLE انجام می شود. دستور دستور:
میز رها کردن جدول
به عنوان مثال، یک پرس و جو برای حذف جدول STUDENT به شکل زیر است:
میز قطره دانش آموز
حذف یک جدول باید روابط ایجاد شده در پایگاه داده بین جداول را در نظر بگیرد. اگر جدول دیگری به جدول در حال حذف با استفاده از محدودیت یکپارچگی FOREIGN KEY اشاره کند، DBMS اجازه حذف آن را نخواهد داد.
یک شاخص ایجاد کنید
ایندکس ها برای تسریع دسترسی به داده های خاص در جدول پایگاه داده استفاده می شوند. ایندکس ساختاری است که مقادیر را در یک یا چند ستون از جدول پایگاه داده مانند ستون نام خانوادگی جدول STUDENT مرتب می کند. اگر به دنبال دانش آموز خاصی با نام خانوادگی هستید، این فهرست به شما کمک می کند اطلاعات مورد نیاز خود را سریعتر از جستجو در تمام ردیف های جدول به دست آورید.
یک ایندکس نشانگرهایی را برای مقادیر داده های ذخیره شده در ستون های خاص یک جدول ارائه می دهد و آن اشاره گرها را بر اساس ترتیب مرتب سازی مشخص شده مرتب می کند. جستجوی داده ها در جدول با استفاده از نمایه مشابه جستجوی فهرست در یک کتاب است. ابتدا برای یک مقدار خاص در شاخص جستجو می شود و سپس پرش مربوطه از طریق اشاره گر به خط حاوی این مقدار انجام می شود.
یک شاخص با استفاده از دستور CREATE INDEX ایجاد می شود:
ایجاد شاخص
نام_فهرست ON نام _جدول(ستون [،…])
جایی که UNIQUE نشان می دهد که شاخص فقط باید مقادیر منحصر به فرد را ذخیره کند.
یک شاخص می تواند بر روی یک یا چند ستون ایجاد شود (شاخص ترکیبی). ایندکس های ترکیبی به شما این امکان را می دهند که بین رکوردهایی که دارای مقدار یکسان در یک ستون هستند، تمایز قائل شوید.
مثال: یک نمایه ترکیبی روی جدول STUDENT ایجاد کنید برای فیلدها نام خانوادگی و تاریخ تولد
ایجاد INDEX Ind_Fam روشن
دانشجو (نام خانوادگی، [تاریخ تولد] DESC)
فقط در صورتی باید فهرستی را روی یک جدول ایجاد کنید که قصد دارید مکرراً از داده های ستون های نمایه شده پرس و جو کنید. ایندکس ها فضای دیسک را اشغال می کنند و سرعت افزودن، حذف و به روز رسانی ردیف ها را کاهش می دهند.
حذف فهرست جدول
دستور DROP یک شاخص را از جدول حذف می کند. نحو دستور DROP برای رها کردن ایندکس به صورت زیر است:
DROP INDEX فهرست مطالببر جدول
قبل از اینکه شاخصی از جدول یا خود جدول حذف شود، باید بسته شود.
مثال: فهرست Ind_Fam را از جدول STUDENT حذف کنید
DROP INDEX Ind_Fam روی دانشجو
بیایید جمع بندی را یاد بگیریم. نه، اینها نتایج یادگیری SQL نیستند، بلکه نتایج مقادیر ستون‌های جداول پایگاه داده هستند. توابع جمع SQL بر روی مقادیر یک ستون عمل می کنند تا یک مقدار نتیجه واحد تولید کنند. متداول‌ترین توابع جمع‌آوری SQL عبارتند از SUM، MIN، MAX، AVG و COUNT. دو حالت وجود دارد که باید از توابع جمع استفاده شود. ابتدا، توابع انبوه به تنهایی استفاده می‌شوند و یک مقدار نتیجه واحد را برمی‌گردانند. دوم، توابع جمع با عبارت SQL GROUP BY، یعنی با گروه بندی بر اساس فیلدها (ستون ها) برای به دست آوردن مقادیر حاصل در هر گروه استفاده می شود. ابتدا موارد استفاده از توابع جمع بدون گروه بندی را در نظر بگیرید.
تابع SQL SUM
تابع SQL SUM مجموع مقادیر یک ستون را در جدول پایگاه داده برمی گرداند. فقط می توان آن را برای ستون هایی اعمال کرد که مقادیر آنها اعداد است. پرس و جوهای SQL برای بدست آوردن مجموع حاصل به این صورت شروع می شود:
جمع را انتخاب کنید (COLUMNAME) ...
این عبارت با FROM (TABLE_NAME) دنبال می شود و سپس با استفاده از عبارت WHERE می توان یک شرط را مشخص کرد. علاوه بر این، DISTINCT را می توان پیشوند نام ستون کرد تا نشان دهد که فقط مقادیر منحصر به فرد در نظر گرفته می شود. به طور پیش فرض، تمام مقادیر در نظر گرفته می شود (برای این، شما می توانید به طور خاص نه DISTINCT، بلکه ALL را مشخص کنید، اما کلمه ALL اختیاری است).
مثال 1پایگاه داده ای از شرکت با داده های مربوط به بخش ها و کارمندان آن وجود دارد. جدول کارکنان همچنین دارای یک ستون با داده های حقوق کارمندان است. انتخاب از جدول به شکل زیر است (برای بزرگنمایی تصویر، با دکمه سمت چپ ماوس روی آن کلیک کنید):
برای دریافت مجموع حقوق و دستمزدها، از عبارت زیر استفاده کنید:
جمع (حقوق) را از کارکنان انتخاب کنید
این کوئری مقدار 287664.63 را برمی گرداند.
و حالا در تمرینات، ما از قبل شروع به پیچیده کردن وظایف می کنیم و آنها را به مواردی که در عمل با آنها مواجه می شویم نزدیک می کنیم.
تابع SQL MIN
تابع SQL MIN همچنین روی ستون هایی که مقادیر آنها اعداد است عمل می کند و حداقل تمام مقادیر موجود در ستون را برمی گرداند. این تابع دارای نحوی شبیه به تابع SUM است.
مثال 3پایگاه داده و جدول مانند مثال 1 هستند.
شما باید حداقل ها را بدانید دستمزدکارکنان بخش شماره 42. برای انجام این کار، درخواست زیر را بنویسید:
پرس و جو مقدار 10505.90 را برمی گرداند.
و دوباره ورزش برای تصمیم مستقل . در این تمرین و برخی تمرین‌های دیگر، نه تنها به جدول کارکنان، بلکه به جدول Org حاوی داده‌های مربوط به بخش‌های شرکت نیز نیاز خواهید داشت:
مثال 4جدول Org به جدول Staff اضافه می شود که حاوی داده های مربوط به بخش های شرکت است. حداقل تعداد سالهایی را که یک کارمند مجرد در یک بخش واقع در بوستون کار کرده است چاپ کنید.
تابع SQL MAX
تابع SQL MAX به طور مشابه کار می کند و دارای نحو یکسانی است که زمانی استفاده می شود که می خواهید حداکثر مقدار را در بین تمام مقادیر یک ستون تعیین کنید.
مثال 5
لازم است حداکثر حقوق کارمندان بخش 42 را دریابید. برای انجام این کار، عبارت زیر را بنویسید:
پرس و جو مقدار 18352.80 را برمی گرداند
حالا زمانش فرا رسیده تمرین هایی برای تعیین سرنوشت.
مثال 6ما دوباره با دو جدول کار می کنیم - Staff و Org. نمایش نام بخش و حداکثر میزان پورسانت های دریافتی توسط یک کارمند در بخش متعلق به گروه بخش (Division) شرقی. استفاده کنید JOIN (پیوستن به جداول) .
تابع SQL AVG
آنچه در مورد نحو توابع شرح داده شده قبلی گفته شد در مورد تابع SQL AVG نیز صادق است. این تابع میانگین تمام مقادیر یک ستون را برمی گرداند.
مثال 7پایگاه داده و جدول مانند نمونه های قبلی است.
اجازه دهید لازم باشد میانگین طول خدمت کارکنان بخش شماره 42 را دریابید. برای انجام این کار، عبارت زیر را می نویسیم:
نتیجه 6.33 خواهد بود
مثال 8ما با یک میز کار می کنیم - کارکنان. نمایش میانگین حقوق کارمندان با سابقه 4 تا 6 سال.
تابع COUNT SQL
تابع SQL COUNT تعداد رکوردهای جدول پایگاه داده را برمی گرداند. اگر SELECT COUNT(COLUMNAME) ... را در پرس و جو مشخص کنید، نتیجه تعداد رکوردها بدون در نظر گرفتن آن دسته از رکوردهایی خواهد بود که در آنها مقدار ستون NULL (تعریف نشده) است. اگر از یک ستاره به عنوان آرگومان استفاده کنید و یک جستجوی SELECT COUNT(*) ... را شروع کنید، نتیجه تعداد تمام رکوردها (ردیف ها) در جدول خواهد بود.
مثال 9پایگاه داده و جدول مانند نمونه های قبلی است.
شما می خواهید تعداد کارمندانی که کمیسیون دریافت می کنند را بدانید. تعداد کارمندانی که مقادیر ستون Comm آنها NULL نیست، کوئری زیر را برمی‌گرداند:
تعداد (Comm) FROM Staff را انتخاب کنید
نتیجه مقدار 11 خواهد بود.
مثال 10پایگاه داده و جدول مانند نمونه های قبلی است.
اگر می خواهید تعداد کل رکوردهای جدول را بدانید، از پرس و جو با یک ستاره به عنوان آرگومان برای تابع COUNT استفاده کنید:
تعداد (*) را از کارکنان انتخاب کنید
نتیجه مقدار 17 خواهد بود.
بعد تمرین برای خودمختاریباید از یک پرس و جو استفاده کنید
مثال 11.ما با یک میز کار می کنیم - کارکنان. نمایش تعداد کارمندان در بخش Plains.
توابع جمع با SQL GROUP BY
حالا بیایید به استفاده از توابع جمع همراه با عبارت SQL GROUP BY نگاه کنیم. بند SQL GROUP BY برای گروه بندی مقادیر به دست آمده بر اساس ستون ها در جدول پایگاه داده استفاده می شود. سایت دارد درس اختصاص داده شده به این اپراتور به طور جداگانه .
مثال 12.یک پایگاه داده تبلیغات پورتال وجود دارد. دارای یک جدول تبلیغات است که حاوی داده هایی در مورد تبلیغاتی است که برای هفته ارسال شده است. ستون Category شامل داده‌های مربوط به دسته‌های تبلیغات بزرگ (به عنوان مثال، املاک و مستغلات)، و ستون قطعات حاوی داده‌هایی در مورد قطعات کوچک‌تر است که در این دسته گنجانده شده‌اند (به عنوان مثال، قطعات آپارتمان‌ها و ویلاها بخشی از دسته املاک و مستغلات هستند). ستون Units حاوی داده‌هایی در مورد تعداد آگهی‌های ارسال‌شده است و ستون Money حاوی مقدار پولی است که برای ارسال آگهی‌ها به‌دست می‌آید.
| دسته بندی | بخش | واحدها | پول |
| حمل و نقل | وسایل نقلیه موتوری | 110 | 17600 |
| مشاور املاک | آپارتمان ها | 89 | 18690 |
| مشاور املاک | داچا | 57 | 11970 |
| حمل و نقل | موتور سیکلت | 131 | 20960 |
| مصالح ساختمانی | تابلوها | 68 | 7140 |
| مهندسی برق | تلویزیون ها | 127 | 8255 |
| مهندسی برق | یخچال و فریزر | 137 | 8905 |
| مصالح ساختمانی | Regips | 112 | 11760 |
| اوقات فراغت | کتاب ها | 96 | 6240 |
| مشاور املاک | خانه ها | 47 | 9870 |
| اوقات فراغت | موسیقی | 117 | 7605 |
| اوقات فراغت | بازی ها | 41 | 2665 |
با استفاده از بند SQL GROUP BY، مقدار پول ایجاد شده با ارسال تبلیغات در هر دسته را بیابید. پرس و جوی زیر را می نویسیم:
انتخاب دسته، جمع (پول) به عنوان پول از تبلیغات گروه به دسته
مثال 13پایگاه داده و جدول مانند مثال قبلی است.
با استفاده از بند SQL GROUP BY، دریابید که کدام قسمت از هر دسته بیشترین تبلیغات را داشته است. پرس و جوی زیر را می نویسیم:
انتخاب دسته، قسمت، حداکثر (واحد) به عنوان حداکثر از تبلیغات گروه بر اساس دسته
نتیجه جدول زیر خواهد بود:
مجموع و مجموع مقادیر را می توان در یک جدول به دست آورد ترکیب نتایج پرس و جو با استفاده از عملگر UNION .
پایگاه های داده رابطه ای و زبان SQL
مانند بسیاری از زبان های برنامه نویسی، SQL دارای عملکردهایی برای مدیریت داده ها است. شایان ذکر است که برخلاف دستورات SQL، توابع برای همه انواع DBMS استاندارد نشده اند، یعنی برای انجام عملیات مشابه روی داده ها، DBMS های مختلف نام تابع خود را دارند. این بدان معنی است که کد پرس و جو نوشته شده در یک DBMS ممکن است در دیگری کار نکند و این باید در آینده در نظر گرفته شود. بیشتر از همه، این به توابعی برای پردازش مقادیر متن، تبدیل انواع داده ها و دستکاری تاریخ ها مربوط می شود.
معمولا DBMS پشتیبانی می شود مجموعه استانداردانواع تابع، یعنی:
- توابع متنی که برای پردازش متن استفاده می شوند (انتخاب بخشی از کاراکترهای متن، تعیین طول متن، تبدیل کاراکترها به حروف بزرگ یا کوچک ...)
- توابع عددی برای اجرا استفاده می شود عملیات ریاضیبیش از اعداد
- توابع تاریخ و زمان (انجام دستکاری تاریخ و زمان، محاسبه دوره بین تاریخ ها، بررسی تاریخ ها برای صحت و غیره)
- توابع آماری (برای محاسبه حداکثر / حداقل مقادیر، میانگین ها، شمارش مقادیر و مجموع...)
- توابع سیستم (اطلاعات خدمات مختلف در مورد DBMS، کاربر و غیره را ارائه می دهد).
1. توابع SQL برای پردازش متن
پیاده سازی SQL در Access DBMS دارای عملکردهای زیر برای پردازش متن است:
بیایید نام محصولات را با استفاده از تابع به حروف بزرگ تبدیل کنیم UCase():
انتخاب کنید Product, UCase(Product) AS Product_UCase FROM Sumproduct
سه کاراکتر اول متن را با استفاده از تابع انتخاب کنید ترک کرد():
انتخاب کنید Product, LEFT(Product, 3) AS Product_LEFT FROM Sumproduct
2. توابع SQL برای پردازش اعداد
توابع پردازش اعداد برای انجام عملیات ریاضی روی داده های عددی طراحی شده اند. این توابع برای محاسبات جبری و هندسی در نظر گرفته شده اند، بنابراین بسیار کمتر از توابع پردازش تاریخ و زمان استفاده می شوند. با این حال، توابع عددی در تمام نسخه‌های SQL استانداردترین هستند. بیایید نگاهی به لیست توابع عددی بیندازیم:
ما فقط تعدادی از ویژگی های اصلی را فهرست کرده ایم، اما همیشه می توانید برای لیست کامل ویژگی های پشتیبانی شده و توضیحات دقیق آنها به اسناد پایگاه داده خود مراجعه کنید.
به عنوان مثال، بیایید یک پرس و جو بنویسیم تا جذر اعداد یک ستون را بدست آوریم میزانبا استفاده از تابع SQR():
انتخاب کنید Amount, SQR(Amount) AS Amount_SQR FROM Sumproduct
3. توابع SQL برای پردازش تاریخ و زمان
توابع دستکاری تاریخ و زمان یکی از مهم ترین و پرکاربردترین توابع SQL هستند. پایگاه های داده مقادیر تاریخ و زمان را در قالب خاصی ذخیره می کنند، بنابراین نمی توان آنها را مستقیماً بدون استفاده کرد پردازش اضافی. هر DBMS مجموعه ای از توابع خاص خود را برای پردازش تاریخ دارد که متأسفانه اجازه نمی دهد که آنها به پلتفرم های دیگر و پیاده سازی های SQL منتقل شوند.
فهرست برخی از عملکردها برای مدیریت تاریخ و زمان در دسترسی به DBMS:
بیایید به مثالی از نحوه عملکرد تابع نگاه کنیم DatePart():
انتخاب کنید Date1، DatePart("m"، Date1) AS Month1 FROM Sumproduct
عملکرد DatePart()این دارد پارامتر اضافی، که به ما امکان می دهد قسمت ضروری تاریخ را نمایش دهیم. در مثال از پارامتر استفاده کردیم "م"، که عدد ماه را نمایش می دهد (به همان روشی که می توانیم سال را نمایش دهیم - "yyyy"، ربع - "ق"، روز - "د"، هفته - "w"، ساعت - "h"، دقایق - "ن"، ثانیه - "s"و غیره.).
4. توابع آماری SQL
توابع آماری به ما کمک می کنند تا داده های آماده را بدون نمونه برداری از آنها بدست آوریم. پرس و جوهای SQL با این توابع اغلب برای تجزیه و تحلیل و ایجاد گزارش های مختلف استفاده می شوند. نمونه ای از این انتخاب ها می تواند این باشد: تعیین تعداد ردیف ها در یک جدول، به دست آوردن مجموع مقادیر برای یک فیلد خاص، جستجو برای بزرگترین / کوچکترین یا میانگین مقدار در یک ستون جدول مشخص شده. همچنین توجه داشته باشید که توابع آماری توسط همه DBMS ها بدون تغییر زیاد در املا پشتیبانی می شوند.
فهرست توابع آماری در دسترسی به DBMS:
شمردن():
انتخاب کنید COUNT(*) به عنوان تعداد 1 از مجموع محصول- تعداد تمام ردیف های جدول را برمی گرداند
انتخاب کنید COUNT(محصول) به عنوان تعداد2 از مجموع محصول- تعداد تمام ردیف های غیر خالی را در فیلد برمی گرداند تولید - محصول
ما عمداً یک مقدار را در یک ستون حذف کردیم تولید - محصولبرای نشان دادن تفاوت در نحوه عملکرد دو پرس و جو.
مثال های استفاده از تابع SUM():
انتخاب کنید SUM (تعداد) به عنوان مجموع 1 FROM Sumproduct WHERE ماه = "آوریل"
با این درخواست، تعداد کل کالاهای فروخته شده در فروردین را منعکس کردیم.
انتخاب کنید SUM (تعداد * مقدار) به عنوان مجموع 2 از مجموع محصول
همانطور که می بینید، در توابع مجموع، می توانیم با استفاده از عملگرهای ریاضی استاندارد، محاسباتی را روی چندین ستون انجام دهیم.
مثال استفاده از تابع MIN():
انتخاب کنید MIN (مبلغ) به عنوان حداقل 1 از مجموع محصول
مثال استفاده از تابع MAX():
انتخاب کنید MAX (مبلغ) حداکثر 1 از مجموع محصول
مثال استفاده از تابع AVG():
انتخاب کنید AVG (مبلغ) AS میانگین 1 از مجموع محصول
توابع نوع خاصی از دستورات در مجموعه دستورات SQL هستند و هر گویش اجرای خاص خود را از مجموعه دستورات دارد. در نتیجه می توان گفت که توابع دستوراتی هستند که از یک کلمه تشکیل شده و یک مقدار واحد را برمی گرداند. مقدار یک تابع ممکن است به پارامترهای ورودی بستگی داشته باشد، مثلاً در مورد تابعی که میانگین فهرستی از مقادیر را در پایگاه داده محاسبه می کند. با این حال، بسیاری از توابع هیچ پارامتر ورودی را نمی گیرند، به عنوان مثال، تابعی که زمان فعلی سیستم را برمی گرداند CURRENTJ1ME است.
استاندارد ANSI از چندین مورد پشتیبانی می کند ویژگی های مفید. این فصل شرحی از این توابع و همچنین ارائه می دهد توصیف همراه با جزئیاتو نمونه هایی برای هر پلتفرم علاوه بر این، هر پلتفرم فهرست بلندبالایی از عملکردهای داخلی خود دارد که فراتر از استاندارد SQL است. این فصل پارامترها و توضیحاتی در مورد تمام عملکردهای داخلی هر یک از پلتفرم ها ارائه می دهد.
علاوه بر این، اکثر پلتفرم ها توانایی ایجاد توابع سفارشی را دارند. مطابق اطلاعات اضافیبرای عملکردهای تعریف شده توسط کاربر، به "CREATE/ALTER FUNCTION/PROCEDURE" مراجعه کنید.
انواع عملکرد
وجود داشته باشد روش های مختلفطبقه بندی عملکردها بخش‌های فرعی زیر تفاوت‌های مهم را توضیح می‌دهند تا به شما در درک نحوه عملکرد ویژگی‌ها کمک کنند.
توابع قطعی و غیر قطعی
توابع می توانند قطعی یا غیر قطعی باشند. یک تابع قطعی همیشه همان نتیجه را برای همان مجموعه مقادیر ورودی برمی‌گرداند. توابع غیر قطعی ممکن است نتایج متفاوتی را در فراخوانی های مختلف نشان دهند، حتی اگر مقادیر ورودی یکسانی به آنها داده شود.
چرا آنقدر مهم است که با همان پارامترهای ورودیهمین نتایج را گرفتید؟ این مهم است زیرا نحوه استفاده از توابع در نماها، توابع تعریف شده توسط کاربر و رویه های ذخیره شده را مشخص می کند. محدودیت در پلتفرم های مختلفممکن است متفاوت باشد، اما گاهی اوقات فقط از توابع قطعی می توان در این اشیا استفاده کرد. به عنوان مثال، SQL Server می تواند یک شاخص روی یک عبارت در یک ستون ایجاد کند، تا زمانی که عبارت حاوی توابع غیر قطعی نباشد. قوانین و محدودیت ها از پلت فرمی به پلتفرم دیگر متفاوت است، بنابراین لطفاً هنگام استفاده از عملکردها به مستندات سازنده مراجعه کنید.
توابع جمعی و اسکالر
روش دیگر برای دسته‌بندی توابع، توانایی آنها برای عملکرد تنها بر روی یک رشته، یا مجموعه‌ای از مقادیر یا مجموعه‌ای از رشته‌ها است. توابع مجموع بر روی مجموعه ای از مقادیر عمل می کنند و یک مقدار کل را برمی گرداند. توابع اسکالر بسته به آرگومان های ورودی اسکالر یک مقدار واحد را برمی گرداند. برخی از توابع اسکالر، مانند CURRENTJTIME، به آرگومان نیاز ندارند.
توابع پنجره
توابع پنجره را می توان مشابه توابع انبوه در نظر گرفت زیرا می توانند همزمان روی چندین ردیف کار کنند. تفاوت در نحوه مشخص شدن این خطوط است. توابع انبوه بر روی مجموعه ردیف های مشخص شده در بند GROUP BY عمل می کنند. در مورد توابع پنجره، مجموعه ردیف با هر فراخوانی تابع مشخص می‌شود و فراخوانی‌های تابع مختلف در یک کوئری می‌توانند بر روی مجموعه‌های ردیف مختلف عمل کنند.
مراحل این مرحله از یادگیری پرس‌و‌جوهای SQL برای نشان دادن این واقعیت طراحی شده‌اند که SQL نه تنها می‌تواند انتخاب‌های پیچیده و مرتب‌سازی داده‌ها انجام دهد، بلکه نتایج توابع ریاضی را نیز محاسبه می‌کند، تبدیل متن، رکوردهای گروهی و غیره را انجام می‌دهد. به طور دقیق تر، نه SQL می تواند همه این کارها را انجام دهد، بلکه آنهایی که از آن پشتیبانی می کنند. SQL با استانداردهای خود فقط الزامات همین DBMS را فرموله می کند.
مرحله 15 SUM، AVG، MIN، MAX، COUNT…
این مرحله به شما نشان می دهد که چگونه از ساده ترین توابع در SQL مانند sum، min، max، average و غیره استفاده کنید. بیایید بلافاصله با مثالی از استخراج میانگین ارشدیت همه کارمندان شروع کنیم.
AVG (D_STAFF.S_EXPERIENCE) را به عنوان [متوسط تجربه کارکنان] از D_STAFF انتخاب کنید
تابع SQL AVG.
به طور مشابه، می توانید مقادیر حداقل و حداکثر (MIN، MAX)، مقدار کل (SUM) و غیره را محاسبه کنید. من به شما توصیه می کنم با استفاده از آموزش آن را امتحان کنید. ارزش تلاش برای تعریف معیارهای اضافی برای انتخاب رکوردهای مربوط به تعیین مقدار نهایی تابع با استفاده از عبارت WHERE را دارد.
توابع فهرست شده در بالا از کل نتیجه پرس و جو برای تعیین مقدار خود استفاده می کنند. چنین توابعی نامیده می شوند تجمیع . همچنین تعدادی از توابع وجود دارد که آرگومان های آنها همه مقادیر ستون تعریف شده در پرس و جو نیستند، بلکه هر مقدار جداگانه هر ردیف از نتیجه است. نمونه ای از چنین تابعی تابع SQL برای محاسبه طول است فیلد متنی LEN:
S_NAME، LEN(D_STAFF.S_NAME) را به عنوان [LENGTH] از D_STAFF انتخاب کنید
می تواند به کار رود برهم نهی توابع SQLهمانطور که در زیر نشان داده شده است، و حداکثر مقدار طول فیلد S_NAME را محاسبه کنید.
حداکثر (LEN(D_STAFF.S_NAME)) را به عنوان [MAX LENGTH] از D_STAFF انتخاب کنید
تابع SQL MAX.
خوب، در نتیجه، همه با هم.
SELECT SUM(D_STAFF.S_EXPERIENCE) به عنوان [SUM]، AVG(D_STAFF.S_EXPERIENCE) به عنوان [AVERAGE]، MIN(D_STAFF.S_EXPERIENCE) به عنوان [MINIMUM]، MAX(D_STAFF.S_EXPERIENCE) به عنوان [MAXIMUMAS] [NUMBER OF Records]، MAX(LEN(D_STAFF.S_NAME)) به عنوان [MAXIMUM LENGTH] FROM D_STAFF
نمونه ای از استفاده از توابع SQL انبوه.
به آرگومان تابع COUNT توجه کنید. من (*) را به عنوان یک آرگومان مشخص کردم، زیرا می خواهم دقیقاً دریافت کنم تعداد کلسوابق. اگر مثلاً COUNT(S_NAME) را مشخص کنید، نتیجه تعداد مقادیر غیر خالی S_NAME خواهد بود (S_NAME IS NOT NULL). می توان COUNT (DISTINCT S_NAME) را نوشت و تعداد مقادیر منحصر به فرد S_NAME را دریافت کرد، اما متاسفانه MS Access از این گزینه پشتیبانی نمی کند. در مثال ما، COUNT(S_NAME) و COUNT(*) دقیقاً نتیجه مشابهی دارند.
مرحله 16 تبدیل متن
غالبا، مقادیر متنیتوسط کاربران پر شده است نرم افزاربه طرق مختلف: چه کسی نام کامل را می نویسد. با حروف بزرگ، چه کسی نیست. کسی همه چیز را می نویسد حروف بزرگ. بسیاری از فرم‌های گزارش‌دهی نیازمند یک رویکرد واحد هستند و نه تنها فرم‌های گزارش‌دهی. برای حل این مشکل، SQL دارای دو تابع UCASE و LCASE است. یک نمونه درخواست و نتیجه پردازش آن در زیر نشان داده شده است:
SELECT UCASE(D_STAFF.S_NAME) AS , LCASE(D_STAFF.S_NAME) AS FROM D_STAFF
توابع SQL UCASE و LCASE.
مرحله 17: دستکاری SQL و رشته
همچنین یک تابع MID فوق العاده وجود دارد که به شما کمک می کند تا مشکل استخراج بخشی از یک رشته را از کل مقدار یک فیلد متنی حل کنید. در اینجا نیز بهترین نظر یک مثال خواهد بود - نمونه ای از "مسخره" نام های پروفایل کاربر.
UCASE (MID(P_NAME، 3،5)) از D_PROFILE را انتخاب کنید
انطباق توابع SQL UCASE و MID.
ما 5 کاراکتر را از مقادیر نام های پروفایل، از 3 شروع کردیم، و یک دسته از تکرارهای "آشغال" دریافت کردیم. برای اینکه فقط مقادیر منحصر به فرد باقی بمانیم، از آن استفاده خواهیم کرد کلمه کلیدیمتمایز.
UCASE متمایز (MID(P_NAME,3,5)) را از D_PROFILE انتخاب کنید
انتخاب مقادیر تابع مجموع منحصر به فرد.
گاهی اوقات لازم است از عبارات با تابع LEN به عنوان آرگومان برای تابع MID استفاده شود. در مثال زیر، ما در حال نمایش 5 کاراکتر آخر در نام پروفایل هستیم.
SELECT UCASE(MID(P_NAME,LEN(P_NAME)-4,5)) FROM D_PROFILE
با استفاده از تابع SQL LEN.
مرحله 18. استفاده از توابع SQL در معیارهای انتخاب رکوردها. داشتن بیانیه
پس از پرداختن به توابع، تقریباً بلافاصله این سؤال مطرح می شود که چگونه می توان از آنها در معیارهای انتخاب رکوردها استفاده کرد؟ برخی از توابع، یعنی آنهایی که تجمیع نیستند، به راحتی قابل استفاده هستند. به عنوان مثال، در اینجا لیستی از کارمندانی که نام کامل آنهاست آمده است بیش از 25 کاراکتر
S_NAME را از D_STAFF انتخاب کنید WHERE LEN(D_STAFF.S_NAME) > 25
استفاده از تابع LEN غیر تجمعی در شرایط پرس و جوی SQL.
خوب، اگر شما، برای مثال، نیاز به نمایش شناسه تمام موقعیت هایی دارید که توسط بیش از یک کارمند در شرکت اشغال شده است، این روش کار نخواهد کرد. منظور من این است که پرس و جو زیر ممکن است معنی نداشته باشد، اما از نظر یک پرس و جو ساخت یافته نادرست است. این به این دلیل است که برای پردازش صحیح چنین پرس و جوهای SQL با استفاده از توابع انبوه، یک عبور خطی از سوابق کارمند کافی نخواهد بود.
SELECT S_POSITION از D_STAFF WHERE COUNT(S_POSITION)> 1
برای چنین مواردی کلمه کلیدی HAVING در SQL معرفی شد که به حل مشکل پست ها و کارمندان کمک می کند.
S_POSITION از D_STAFF GROUP BY S_POSITION با داشتن COUNT(S_POSITION)> 1 انتخاب کنید
استفاده از توابع جمع در شرایط پرس و جو SQL.
مرحله 19. گروه بندی داده ها در نتایج پرس و جوی SQL با عملگر GROUP BY
بند GROUP BY برای گروه بندی مقادیر توابع انبوه بر اساس مقادیر فیلدهای مرتبط با آنها لازم است. زمانی که بخواهیم از مقدار تابع مجموع در معیارهای انتخاب رکورد استفاده کنیم (مرحله قبل) نیاز است. همچنین زمانی که بخواهیم مقدار تابع مجموع را در نتیجه پرس و جو لحاظ کنیم، لازم است. اما در بسیار نسخه سادهگروه بندی معادل برجسته کردن مقادیر ستون منحصر به فرد است. بیایید به یک درخواست مثال نگاه کنیم.
S_POSITION از D_STAFF را انتخاب کنید
و این دو گزینه هستند که به شما اجازه می دهند فقط مقادیر S_POSITION منحصر به فرد را نمایش دهید.
S_POSITION از D_STAFF GROUP BY S_POSITION انتخاب کنید
S_POSITION متمایز از D_STAFF را انتخاب کنید
خوب، حالا بیایید به گروه بندی مقادیر تابع بر اساس مقادیر فیلدهای مرتبط با آنها برگردیم. بیایید برای هر پروفایل کاربری تعداد رکوردهای مرتبط با آن را در جدول D_STAFF_PROFILE نمایش دهیم.
PROFILE_ID به عنوان، COUNT(PROFILE_ID) به عنوان [تعداد رکوردها] از D_STAFF_PROFILE GROUP BY PROFILE_ID انتخاب کنید
استفاده از تابع SQL انبوه با گروه بندی.
عملگر GROUP BY همچنین به شما این امکان را می دهد که نتایج یک پرس و جو را با بیش از یک فیلد گروه بندی کنید و آنها را با کاما از هم جدا کنید. امیدوارم پس از تمام موارد فوق، نظرات اضافی در مورد نتیجه آخرین پرس و جو مورد نیاز نباشد.
SELECT S.S_POSITION AS، S.S_NAME AS [EMPLOYEE]، COUNT(SP.STAFF_ID) به عنوان [تعداد رکوردها در جدول D_STAFF_PROFILE] FROM D_STAFF S، D_STAFF_PROFILE SP WHERE S.XD_IID=SPROUPSTAFFS. S_NAME
گروه بندی ردیف های نتیجه پرس و جو SQL بر اساس چندین فیلد.