1 БАЗЫ ДАННЫХ. 2 ПРЕДЛОЖЕНИЯ SQL ВЫБОРКА - SELECT SELECT [предикат] { * | таблица.* | [таблица.]поле_1 [AS псевдоним_1] [, [таблица.]поле_2 [AS псевдоним_2] - презентация

1 1 БАЗЫ ДАННЫХ

2 2 ПРЕДЛОЖЕНИЯ SQL ВЫБОРКА - SELECT SELECT [предикат] { * | таблица.* | [таблица.]поле_1 [AS псевдоним_1] [, [таблица.]поле_2 [AS псевдоним_2] [,...]]} FROM выражение [,...] [IN внешняяБазаДанных] [WHERE... ] [GROUP BY... ] [HAVING... ] [ORDER BY... ] [WITH OWNERACCESS OPTION]

3 3 ПРЕДЛОЖЕНИЯ SQL ПРОСТЕЙШИЙ ВАРИАНТ SELECTа SELECT column_name FROM table_name;

4 4 ПРЕДЛОЖЕНИЯ SQL ПРОСТЕЙШИЙ ВАРИАНТ SELECTа Persons LastNameFirstNameAddressCity HansenOlaTimoteivn 10Sandnes SvendsonToveBorgvn 23Sandnes PettersenKariStorgt 20Stavanger SELECT LastName, FirstName FROM Persons; LastNameFirstName HansenOla SvendsonTove PettersenKari

5 5 ПРЕДЛОЖЕНИЯ SQL ПРОСТЕЙШИЙ ВАРИАНТ SELECTа SELECT * FROM Persons; Persons LastNameFirstNameAddressCity HansenOlaTimoteivn 10Sandnes SvendsonToveBorgvn 23Sandnes PettersenKariStorgt 20Stavanger LastNameFirstNameAddressCity HansenOlaTimoteivn 10Sandnes SvendsonToveBorgvn 23Sandnes PettersenKariStorgt 20Stavanger

6 6 ПРЕДЛОЖЕНИЯ SQL АЛИАСЫ SELECT column AS column_alias FROM table; SELECT column FROM table AS table_alias;

7 7 ПРЕДЛОЖЕНИЯ SQL АЛИАСЫ Persons LastNameFirstNameAddressCity HansenOlaTimoteivn 10Sandnes SvendsonToveBorgvn 23Sandnes PettersenKariStorgt 20Stavanger ASAS SELECT LastName AS Family, FirstName AS Name FROM Persons; FamilyName HansenOla SvendsonTove PettersenKari

8 8 ПРЕДЛОЖЕНИЯ SQL АЛИАСЫ Persons LastNameFirstNameAddressCity HansenOlaTimoteivn 10Sandnes SvendsonToveBorgvn 23Sandnes PettersenKariStorgt 20Stavanger AS SELECT LastName, FirstName FROM Persons AS Employees LastNameFirstName HansenOla SvendsonTove PettersenKari Employees

9 9 ПРЕДЛОЖЕНИЯ SQL SELECT без повторов SELECT DISTINCT column_name(s) FROM table_name;

10 10 ПРЕДЛОЖЕНИЯ SQL SELECT без повторов "Orders" CompanyOrderNumber Sega3412 W3Schools2312 Trio4678 W3Schools6798 SELECT Company FROM Orders; Company Sega W3Schools Trio W3Schools DISTINCT SELECT DISTINCT Company FROM Orders; Company Sega W3Schools Trio

11 11 ПРЕДЛОЖЕНИЯ SQL SELECT с условием SELECT column FROM table WHERE column operator value; =Равно Не равно >Больше чем =Больше или равно

12 12 ПРЕДЛОЖЕНИЯ SQL SELECT с условием WHERE SELECT * FROM Persons WHERE City='Sandnes; "Persons" LastNameFirstNameAddressCityYear HansenOlaTimoteivn 10Sandnes1951 SvendsonToveBorgvn 23Sandnes1978 SvendsonStaleKaivn 18Sandnes1980 PettersenKariStorgt 20Stavanger1960 LastNameFirstNameAddressCityYear HansenOlaTimoteivn 10Sandnes1951 SvendsonToveBorgvn 23Sandnes1978 SvendsonStaleKaivn 18Sandnes1980

13 13 ПРЕДЛОЖЕНИЯ SQL SELECT с условием '' SELECT * FROM Persons WHERE FirstName='Tove' ; SELECT * FROM Persons WHERE FirstName=Tove; SELECT * FROM Persons WHERE Year>1965 ; SELECT * FROM Persons WHERE Year>'1965';

14 14 ПРЕДЛОЖЕНИЯ SQL SELECT с условием LIKE SELECT column FROM table WHERE column LIKE pattern;

15 15 ПРЕДЛОЖЕНИЯ SQL SELECT с условием LIKE Store_Information store_nameSalesDate LOS ANGELES$1500Jan SAN DIEGO$250Jan SAN FRANCISCO$300Jan BOSTON$700Jan SELECT * FROM Store_Information LIKE%AN% WHERE store_name LIKE '%AN%; store_nameSalesDate AN LOS ANGELES$1500Jan ANAN SAN FRANCISCO$300Jan AN SAN DIEGO$250Jan

16 16 ПРЕДЛОЖЕНИЯ SQL SELECT с условием LIKE % SELECT * FROM Persons WHERE FirstName LIKE 'O%'; SELECT * FROM Persons WHERE FirstName LIKE '%a'; SELECT * FROM Persons WHERE FirstName LIKE '%la%'; SELECT * FROM Persons WHERE FirstName LIKE *la*'; _ SELECT * FROM Persons WHERE FirstName LIKE '%la_ _ _a';

17 17 ПРЕДЛОЖЕНИЯ SQL BETWEEN SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2; ЗАВИСИТ ОТ КОНКРЕТНОЙ СУБД!

18 18 ПРЕДЛОЖЕНИЯ SQL BETWEEN LastNameFirstNameAddressCity HansenOlaTimoteivn 10Sandnes NordmannAnnaNeset 18Sandnes PettersenKariStorgt 20Stavanger SvendsonToveBorgvn 23Sandnes SELECT * FROM Persons BETWEENAND WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'; LastNameFirstNameAddressCity HansenOlaTimoteivn 10Sandnes NordmannAnnaNeset 18Sandnes

19 19 ПРЕДЛОЖЕНИЯ SQL BETWEEN LastNameFirstNameAddressCity HansenOlaTimoteivn 10Sandnes NordmannAnnaNeset 18Sandnes PettersenKariStorgt 20Stavanger SvendsonToveBorgvn 23Sandnes SELECT * FROM Persons NOT WHERE LastName NOT BETWEEN 'Hansen' AND 'Pettersen'; LastNameFirstNameAddressCity PettersenKariStorgt 20Stavanger SvendsonToveBorgvn 23Sandnes

20 20 ПРЕДЛОЖЕНИЯ SQL ORDER BY Orders CompanyOrderNumber Sega3412 ABC Shop5678 W3Schools2312 W3Schools6798 SELECT Company, OrderNumber FROM Orders ORDER BY ORDER BY Company; CompanyOrderNumber ABC Shop 5678 Sega3412 W3Schools6798 W3Schools2312

21 21 ПРЕДЛОЖЕНИЯ SQL ORDER BY Orders CompanyOrderNumber Sega3412 ABC Shop5678 W3Schools2312 W3Schools6798 SELECT Company, OrderNumber FROM Orders ORDER BY ORDER BY Company, OrderNumber ; CompanyOrderNumber ABC Shop5678 Sega3412 W3Schools2312 W3Schools6798 CompanyOrderNumber ABC Shop 5678 Sega3412 W3Schools6798 W3Schools2312

22 22 ПРЕДЛОЖЕНИЯ SQL ORDER BY Orders CompanyOrderNumber Sega3412 ABC Shop5678 W3Schools2312 W3Schools6798 SELECT Company, OrderNumber FROM Orders DESC ORDER BY Company DESC ; CompanyOrderNumber ABC Shop 5678 Sega3412 W3Schools6798 W3Schools2312 CompanyOrderNumber W3Schools6798 W3Schools2312 Sega3412 ABC Shop5678

23 23 ПРЕДЛОЖЕНИЯ SQL ORDER BY Orders CompanyOrderNumber Sega3412 ABC Shop5678 W3Schools2312 W3Schools6798 SELECT Company, OrderNumber FROM Orders DESC ORDER BY Company DESC, ASC OrderNumber ASC ; CompanyOrderNumber W3Schools2312 W3Schools6798 Sega3412 ABC Shop5678 CompanyOrderNumber ABC Shop5678 Sega3412 W3Schools2312 W3Schools6798

24 24 ПРЕДЛОЖЕНИЯ SQL ORDER BY Sales SELECT store_name, Sales, Date FROM Store_Information 2 ORDER BY 2 DESC ПО ВТОРОМУ СТОЛБЦУ

25 25 ПРЕДЛОЖЕНИЯ SQL AND & OR LastNameFirstNameAddressCity HansenOlaTimoteivn 10Sandnes SvendsonToveBorgvn 23Sandnes SvendsonStephenKaivn 18Sandnes SELECT * FROM Persons AND WHERE FirstName='Tove' AND LastName='Svendson'; LastNameFirstNameAddressCity SvendsonToveBorgvn 23Sandnes

26 26 ПРЕДЛОЖЕНИЯ SQL AND & OR LastNameFirstNameAddressCity HansenOlaTimoteivn 10Sandnes SvendsonToveBorgvn 23Sandnes SvendsonStephenKaivn 18Sandnes SELECT * FROM Persons OR WHERE firstname='Tove' OR lastname='Svendson' ; LastNameFirstNameAddressCity SvendsonToveBorgvn 23Sandnes SvendsonStephenKaivn 18Sandnes

27 27 ПРЕДЛОЖЕНИЯ SQL AND & OR LastNameFirstNameAddressCity HansenOlaTimoteivn 10Sandnes SvendsonToveBorgvn 23Sandnes SvendsonStephenKaivn 18Sandnes SELECT * FROM Persons (OR) WHERE (FirstName='Tove' OR FirstName='Stephen') AND AND LastName='Svendson' ; LastNameFirstNameAddressCity SvendsonToveBorgvn 23Sandnes SvendsonStephenKaivn 18Sandnes

28 28 ПРЕДЛОЖЕНИЯ SQL AND & OR Store_Information store_nameSalesDate Los Angeles$1500Jan San Diego$250Jan San Francisco$300Jan Boston$700Jan SELECT store_name FROM Store_Information WHERE Sales > 1000 OR (Sales 275) ; store_name Los Angeles San Francisco

29 29 ПРЕДЛОЖЕНИЯ SQL ФУНКЦИИ SELECT function(column) FROM table ; AVG – среднее значение в столбце COUNT – число значений в столбце MAX – самое большое значение в столбце MIN – самое малое значение в столбце SUM – сумма значений по столбцу

30 30 ПРЕДЛОЖЕНИЯ SQL AVG Persons NameAge Hansen, Ola34 Svendson, Tove45 Pettersen, Kari19 AVG SELECT AVG(Age) FROM Persons AVG SELECT AVG(Age) FROM Persons WHERE Age>

31 31 ПРЕДЛОЖЕНИЯ SQL COUNT Store_Information store_nameSalesDate Los Angeles$1500Jan San Diego$250Jan Los Angeles$300Jan Boston$700Jan COUNT SELECT COUNT(store_name) FROM Store_Information; Count(store_name) 4

32 32 ПРЕДЛОЖЕНИЯ SQL COUNT Store_Information store_nameSalesDate Los Angeles$1500Jan San Diego$250Jan Los Angeles$300Jan Boston$700Jan COUNTDISTINCT SELECT COUNT(DISTINCT store_name) FROM Store_Information ; Count(store_name) 3

33 33 ПРЕДЛОЖЕНИЯ SQL MAX MAX SELECT MAX(Age) FROM Persons Persons NameAge Hansen, Ola34 Svendson, Tove45 Pettersen, Kari19 45

34 34 ПРЕДЛОЖЕНИЯ SQL MIN Persons NameAge Hansen, Ola34 Svendson, Tove45 Pettersen, Kari19 SELECT MIN(Age) FROM Persons 19

35 35 ПРЕДЛОЖЕНИЯ SQL SUM Store_Information store_nameSalesDate Los Angeles$1500Jan San Diego$250Jan Los Angeles$300Jan Boston$700Jan SUM SELECT SUM(Sales) FROM Store_Information; $ $250 + $300 + $700 = $2750 SUM(Sales) $2750

36 36 Некоторые функции MS Access ФункцияОписание AVG(column)Returns the average value of a column COUNT(column)Returns the number of rows (without a NULL value) of a column COUNT(*)Returns the number of selected rows FIRST(column)Returns the value of the first record in the specified field LAST(column)Returns the value of the last record in the specified field MAX(column)Returns the highest value of a column MIN(column)Returns the lowest value of a column STDEV(column) STDEVP(column) SUM(column)Returns the total sum of a column VAR(column) VARP(column)

37 37 Некоторые функции MS Access ФункцияОписание UCASE(c)Converts a field to upper case LCASE(c)Converts a field to lower case MID(c,start[,end])Extract characters from a text field LEN(c)Returns the length of a text field INSTR(c)Returns the numeric position of a named character within a text field LEFT(c,number_of_char)Return the left part of a text field requested RIGHT(c,number_of_char)Return the right part of a text field requested ROUND(c,decimals)Rounds a numeric field to the number of decimals specified MOD(x,y)Returns the remainder of a division operation NOW()Returns the current system date FORMAT(c,format)Changes the way a field is displayed DATEDIFF(d,date1,date2)Used to perform date calculations

38 38 Некоторые функции MS SQL Server ФункцияОписание AVG(column)Returns the average value of a column BINARY_CHECKSUM CHECKSUM CHECKSUM_AGG COUNT(column)Returns the number of rows (without a NULL value) of a column COUNT(*)Returns the number of selected rows COUNT(DISTINCT column)Returns the number of distinct results FIRST(column)Returns the value of the first record in the specified field (not supported in SQLServer2K) LAST(column)Returns the value of the last record in the specified field (not supported in SQLServer2K) MAX(column)Returns the highest value of a column MIN(column)Returns the lowest value of a column STDEV(column) STDEVP(column) SUM(column)Returns the total sum of a column VAR(column) VARP(column)

39 39 ПРЕДЛОЖЕНИЯ SQL SELECT IN SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..); IN SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen'); LastNameFirstNameAddressCity HansenOlaTimoteivn 10Sandnes NordmannAnnaNeset 18Sandnes PettersenKariStorgt 20Stavanger SvendsonToveBorgvn 23Sandnes LastNameFirstNameAddressCity HansenOlaTimoteivn 10Sandnes PettersenKariStorgt 20Stavanger