زبان پرس وجوی ساختاری (Structured Query Language)
اسلاید 1: Structured Query Language (SQL)CE 169B Database Systems for Engineering and ManagementArpad HorvathDepartment of Civil and Environmental EngineeringUniversity of California, BerkeleySeptember 30, 2004
اسلاید 2: Sources for this PresentationProfessor Daniel Rehak and Rebecca Buchheit, Department of Civil and Environmental Engineering, Carnegie Mellon University, Copyright, All rights reservedRamakrishnan, R, and J. Gehrke, “Database Management Systems.” 2nd ed., McGraw-Hill, 2000.
اسلاید 3: Internal Structure of DBMSDB enginesFile access mechanismsFile storage
اسلاید 4: External Views of DBMSData manipulation language processor (e.g., SQL interpreter or compiler)Query interfaceForm generatorReport generator
اسلاید 5: SQLAn ANSI/ISO standard languageANSI SQL ’86, ’89, ’92Most systems implement part or extensionsquery language used to interact with databaseselect (choose data from a table) create tablesinsert dataupdate (change) datadelete tables and dataunion (combine tables)otherqueries always manipulate one table to produce a new table
اسلاید 6: MS Accessprovides a user interface to MS JetMS Jet is really the DBMSmany people don’t even know these are SQLcreate and drop tablesinsert dataquery interface and wizards simplifyjoin tablesupdate and delete dataselect datastill need SQL for advanced queriesprojectunion
اسلاید 7: SELECT – Basic statement for data querySELECT [DISTINCT | ALL] [* | <field> | <expression>] +FROM[<table>] +[<join clause>][<where clause>][<group by clause>][<having clause>][<order by clause>]SELECT predicate columnsFROMtableswhere conditions are truegrouped by attributeshaving group criteriasorted in some order
اسلاید 8: Query Categoriescategories of SELECT queriesCRITERIA: uses a <where clause>CALCULATION/TRANSFORMATION: uses an SQL function to modify dataAGGREGATE: uses an SQL aggregation function (SUM, MAX, etc.)any combination of these three
اسلاید 9: SELECT: [DISTINCT | ALL]not required, defaults to ALLqueries sometimes result in duplicate rowsprimary keys define uniqueness of each rowif all primary keys are not included, duplicates resultDISTINCTreturn only one copy of each duplicateALLreturn all instances of duplicates
اسلاید 10: SELECT: [* | <field> | <expression>]must have at least one of the choicesseparate with commas* returns all columns in all tables in table listSELECT *FROM student<field>returns only specified fields from tablessyntax: [table name].[field name]<expression>applies a function to a field (calculation/transformation/aggregation)SELECT SUM([course].[credits]) AS [Total_Credits]FROM course;SELECT COUNT([course].[number]) AS [number_of_courses]FROM course;
اسلاید 11: SELECT: <table>list of tables that you want columns fromseparated by commasuse brackets, but don’t have to[course],[student]
اسلاید 12: SQL Aggregation FunctionsSUMthe sum of the values in a columnSELECT SUM([course].[credits]) AS [Total_Credits]FROM course;MAXthe maximum value in a columnSELECT MAX([course].[credits]) AS [maximum_credit]FROM course;
اسلاید 13: SQL Aggregation FunctionsAVGthe average of a columncalculate the average price of books:SELECT AVG([book].[unitPrice])FROM [book];COUNTa count of the number of values in a columnSELECT COUNT([course].[number]) AS [number_of_courses]FROM course;MINthe minimum value in a columnSELECT MIN([book].[unitPrice])FROM [book]
اسلاید 14: SELECT: <where clause>specifies which rows to returnbased on some criteriacan use expressionsWHERE <criteria>SELECT MIN([book].[unitPrice])FROM [book] WHERE [book].[unitPrice] < 18 AND [book].[publish_date] > 1999;subqueriesWHERE <condition> IN <select query>WHERE <condition> NOT IN <select query>WHERE [book].[ISBN] = 13-785543-X IN (SELECT * FROM [book])
اسلاید 15: SELECT: <group by>aggregates columnsrows with like values are “grouped together”GROUP BY [<expression> | <column name>]SELECT [book].[authors]FROM [book]GROUP BY [book].[authors];
اسلاید 16: SELECT: <order by clause>sort results on the values of one or more columnsORDER BY <column name> [ASC | DESC]ASC is defaultSELECT * FROM [book] ORDER BY [book].[authors] DESC
نقد و بررسی ها
هیچ نظری برای این پاورپوینت نوشته نشده است.