Деректер қорына қосылу
Мазмұны
Кіріспе 7
1 PostgreSQL деректер қорының сервері 8
1.1 PostgreSQL дегеніміз не? 8
1.2 PostgreSQL қысқаша тарихы 8
1.2.1 Берклидегі POSTGRES жобасы 8
1.2.2 Postgres95 9
1.3 PostgreSQL 10
2 Бастау 11
2.1 Орнату 11
2.2 Архитектура негіздері 11
2.3 Деректер қорын құру 12
2.4 Деректер қорына қосылу 13
3 SQL тілі 15
3.1 Кіріспе 15
3.2 Негізгі түсініктер 15
3.3 Кесте құру 15
3.4 Кестеге қатар қосу 16
3.5 Сұраныс орындау 17
3.6 Кестелерді қосу 19
3.7 Агрегаттық функциялар 20
3.8 Мәліметтерді өзгерту 22
3.9 Мәліметтерді жою 22
4 Кеңейтілген мүмкіншіліктер 24
4.1 Көріністер 24
4.2 Ішкі кілттер 24
4.3 Транзакциялар 25
4.4 Терезелік функциялар 27
4.5 Мұрагерлік 31
5 SQL синтаксисі 33
5.1 Лексикалық құрылым 33
5.1.1 Идентификаторлар мен кілттік сөздер 33
5.1.2 Константалар 35
5.1.2.1 Қатарлық константалар 36
5.1.2.2 С стильдегі арнайы тізбекті қатарлы константалар 36
5.1.2.3 Unicode арнайы тізбекті қатарлық константалар 37
5.1.2.4 Долларға алынған қатарлық константалар 38
6 SQL кеңейтілуі 39
6.1 Кеңейтілуді жүзеге асыру 39
6.2 PostgreSQL типтер жүйесі 40
6.2.1 Негізгі типтер 40
6.2.2 Құрамдас типтер 40
6.2.3 Домендер 41
6.2.4 Жалған типтер (псевдотип) 41
6.2.5 Полиморфты типтер 41
6.3 Пайдаланушы функциялар 42
6.4 Функцияларды қайта жүктеу 43
6.5 Функциялардың өзгергіш категориясы 44
6.6 Ішкі функциялар 45
7 Вебқұрылымның серверлік бөлігін жобалау 47
8 Еңбекті қорғау 55
8.1 Еңбек шартын талдау 55
8.2 Жұмыс орнының жоспары 57
8.3 Есептеу бөлімі 58
8.3.1 Кондиционерлеу жүйесін есептеу 58
8.3.2 Табиғи жарықты есептеу 63
8.4 ӨТҚ бойынша қорытынды 66
9 Жобаның техникалық-экономикалық негіздемесі 67
9.1 Жұмыстың сипаттамасы және оның қажеттілігін негіздеу 67
9.2 Жұмыс кезінде қолданылатын еңбек ресурсы 68
9.3 Жобалау мен өңдеуге керетін жұмыстың бағасын есептеу 68
9.4 Амортизацияға кететін шығын мөлшерін есептеу 72
9.5 Электрэнергияға кеткен шығын мөлшерін есептеу 74
9.6 Қосымша шығындарды есептеу 74
9.7 Үстеме шығындарды есептеу 75
9.8 Техникаэкономикалық бөлім қорытынды 76
Қорытынды 77
Әдебиттер тізімі 78
Кіріспе
Бүгінгі таңда интернет туралы жалқау ғана білмейді. Алғашында тек ғалымдар мен мамандар қолдануында болған ол қазіргі заман адамының үшінші қолы ретінде қабылданады. Бастапқыда үш ғана компьютерді біріктірген Интернет қазір негізгі ақпарат көзі, ол сандаған мемлекеттер, оның әрбір азаматтарын біріктіріп отыр. Мүмкіндігі шексіз Интернетке ешкім жеке дара қожалық ете алмайды, сол себептен оны шынында бүкіл әлемдік қазына деп атасақ болады.
Бүгінгі таңда нарықта мобильді операторлар, интернет провайдерлер, спутниктік теледидар және кабель өнімдерін берушілер ассортименті көп. Бірақ көрсетілетін қызмет ерекшеліктерін қалай талқылауға болады? Жобаны әзірлеу тобы Қазақстан нарығыдағы барлық қызмет көрсету мекемелеріне аназиз жасап, әрбір ұсынылатын пакеттердің барлық қырсырын талқылады, және ыңғайлы іздестіруші дайындады. Оның көмегімен пайдаланушылар өздеріне лайықты бағасы, жылдамдық, каналдар саны және тағы басқалары бойынша ұсылынған сервистердің ішінен таңдай алады.
1 PostgreSQL деректер қорының сервері
0.1 PostgreSQL дегеніміз не?
PostgreSQL - бұл объектілі - реляциялық деректер қорын басқару жүйесі(ORDBMS, ОРСУБД). Оның негізі POSTGRES, Version 4.2 - Калифорния университеті, Беркли компьютерлік ғылымдар факультетінде жобаланған бағдарлама. POSTGRES - те көптеген жаңалықтар кейінірек коммерциялық ДҚБЖ - де іске асырылды.
Бағдарлама SQL стандарттарының көпшілігін қолдайды және көптеген қазіргі заманғы функцияларын:
oo күрделі тапсырыстар;
oo ішкі кілттер;
oo триггерлер;
oo өзгертілетін көріністер;
oo транзакциялық толықтық:
oo көп нұсқалылық.
Сонымен қатар, пайдаланушылар PostgreSQL мүмкіндіктерін әрбір жолында кеңейте алады, мысалы, өзінің
oo мәліметтер типін;
oo функцияларын;
oo операторларын;
oo агрегаттық функцияларын;
oo индекс әдістерін;
oo процедуралық тілдерін жасай алады.
Тегін лицензия арқасында PostgreSQL тегін қолдануға, өзгертуге және бәріне және кез келген мақсатта - жеке, коммерциялық және оқыту - таратуға болады.
0.2 PostgreSQL қысқаша тарихы
Бүгінгі атауы PostgreSQL деректер қорын объектілі-реляционды басқару жүйесі Беркли, Калифоония университетінде жазылған POSTGRES пакетінен шықты. Екі онжылдықтан кейін PostgreSQL жоабалуы ең дамыған ашық кодды ДҚБЖ болды.
0.2.1 Берклидегі POSTGRES жобасы
Профессор Майкл Стоунбрейкер басқарған POSTGRES жобасын АҚШ Қорғаныс министрлігінің DORPA агенттігі, Әскери-зерттеу қызметін басқару (ARO), Ұлттық Ғылым Қоры (NSF) және ESL, Inc компаниясы демеушілік етті. POSTGRES іске асуы 1986 жылы басталды. Жүйенің бастапқы түсініктері ston86 құжатында, ал бірінші моделінің сипаттамасы rowe87 ұсынылған болатын [7]. Қойма менеджерінің мәні мен архитектурасы ston87b құжатында жазылып қойылған [12]. Содан бері POSTGRES бірнеше даму кезеңінен өтті. Бірінше демоннұсқасы 1987 жылы жұмыс істеді және 1988 жылы ACM-SIGMOD конферециясында көрсетілді. ston90a сипатталған 1 нұсқа сыртқы пайдаланушылар үшін 1989 жылдың маусамында шығарылды [15]. Бірінші ережелер жүйе (ston89) сынына жауап ретінде ол қайта жасалды (ston90b) және 1990 жылдың маусымында щыққан 2 нұсқада жаңа ережелер жүйесі болды [13,14]. 1991 3 нұсқа шықты, онда әртүрлі қойма менеджерлерінің қолдауы пайда болды, жақсартылған сұраныс орындаушы және қайта жазылған ережелер жүйесі. Postgres95 дейін келесі басылымдар негізінде сенімділік пен ұтқырлығын арттыруға бағытталған.
POSTGRES көптеген зерттеулік және өндірісітік тапсырыстардың іске асуына қолданылған.
Олардың ішінде: қаржы мәліметтерін талдау жүйесі, реактивті қозғалтқыштар жұмысының мониторинг пакеті, астериодттарды бақылау деректер қоры, медициналық мәліметер деректер қоры, бірнеше георграфиялық информациялық жүйелер. Сондай-ақ POSTGRES бірнеше университеттерде оқытуға қолданылды. Соңында, Illustra Information Technologies компаниясы (кейін Informix, қазір IBM тиесілі ) кодты қолданып және оған коммерциялық қолданыс тапты. 1992 жылдың соңында POSTGRES Sequoia 2000 ғылыми есептеу жобасының негізгі ДҚБЖ болды.
1993 жылы сыртқы пайдаланушылар саны екі есе артты. Код қызмет етуі мен қолдауы көп уақыт алатыны айқын болды, және ол уақыт зерттеуге жетпейді. Осы жүктемені түсіру үшін Берклидегі POSTGRES жобасы ресми түрде 4.2 нұсқасында жабылды.
1.2.2 Postgres95
1994 жылы Эндри Ю және Джолли Чен POSTGRES-ке SQL тілінің аудармашысын қосты. Жаңа атаумен Postgres95 Интернетте жарияланды және өзінің жолын ашық кодты Берклиде жасалған Postgres ұрпағы ретінде бастады. Postgres95 коды ANSI C кодымен толық сәйкестендіруге келтірілді және 25% кішірейді. Көптеген ішкі өзгерістер арқасында ол жылдамырақ және ыңғайлы болды. Postgres95 нұсқасы 1.0.х POSTGRES нұсқа 4.2 қарағанда шамалы 30-50% тезірек жұмыс істеді (Wisconsin Benchmark тесті бойынша). Қателіктерді түзегеннен басқа келесі өзгерістер енгізілді:
oo PostQUEL сұраныстар тілінің орнына SQL келді (серверде іске асқан). (libpq интерфейс кітапханасы өзінің атын PostQUEL мұрагерлік етті).
Сұраныстар(подзапрос) PostgreSQL шыққанға дейін қолдау көрсетілмеді, алайда оларды Postgres95 SQL пайдаланушы функциялары арқылы балама жасауға болды. Агрегаттық функциялар қайта іске асырылды. Сонымен қатар GROUP BY сөйлемінің қолдауы пайда болды.
oo Интерактивті SQL-сұраныстар үшін GNU Readline қолданған жаңа бағдарлама (psql) жасалды. Ескі бағдарлама monitor керектігі жоғалды.
oo Tcl-клиенттерді қолдайтын жаңа libpgtcl клиенттік библиотека пайда болды. Tcl жаңа командалары рgtclsh қабығының мысалы ретінде Tcl бағдарламаларының Postgres95 серверымен қарым-қатынасын айтуға болады.
oo Үлкен объектілермен жұмыс үшін интерфейс жақсартылды. Мәліметтерді сақтау механизмі ретінде тек инверсионды объектілер қалды. (Инверсионды файлдық жүйе жойылды.)
oo Дана деңгейіндегі ережелер жүйесі жойылды; қайта жазатын ережедер сақталды.
oo Баптапқы кодпен бірге стандартты SQL мүмкіншіліктері мен Postgres95 өзі қысқаша сипаттамалармен тарала бастады.
oo Жинауға GNU make қолданылды (BSD make орнына). Сонымен қатар, Postgres95 GCC түрлендірілмеген нұсқасымен (мәліметтерді түзеу жөнделді) құрастыру мүмкін болды.
0.3 PostgreSQL
1996 жылы Postgres95 атауы уақыт сынағынан өтпейтіні анық болды. Олар түрнұсқалық POSTGRES пен кейінгі SQL қолдауы бар нұсқасы арасандағы байланысты көрсететін PostgreSQL жаңа атауын таңдады. Осы уақытта әзірлеушілер POSTGRES Беркли жобасында басталған нұсқауды нөмірлеуді 6.0 жалғастырды
Көпшілік PostgreSQL-ді дәстүрлі немесе қарапайымдылық үшін POSTGRES атымен атауды жалғастыра берді (енді бас әріптермен сирек айтады). Осы атау лақап ат немесе ресми емес белгілеу ретінде тұр.
Postgres95 құру барысында негізгі тапсырыстар іздеу мен серверлық кодта бар мәселелерді шешу болды. PostgreSQL көшу кезінде жаңа мүмкіншіліктер мен функциялар іске асыруға баса назар аударылды, алайда жұмыс барлық бағытта жүрді.
1 Бастау
0.1 Орнату
PostgreSQL қолдану алдында, әрине, алдын ала орнату керек. PostgreSQL кез келген қарапайым пайдаланушы орната алады; суперқолданушы (root) құқығы қажет емес.
0.2 Архитектура негіздері
Жалғастырмас бұрын PostgreSQL жүйесінің архитектура негіздерін талдау керек. PostgreSQL бөлшектерінің қарым-қатынас суретін жасау керек.
Техникалық тілмен айтқанда PostgreSQL клиент-сервер архитектурасында негізделген. PostgreSQL жұмыс сессиясы келесі процесстер (бағдарламалар) қарым-қатынастарын қамтиды:
oo деректер қорының файлдарын басқаратын басты серверлік процесс, клиенттік қосымшаларын жалғайтын қабылдаушы және деректер қорына қатысты әртүрлі клиенттер тапсырысын орындаушы. Деректер қоры серверінің бұл бағдармаласы postgres деп аталады;
oo деректер қорына қатысты операциялар орындайтын пайдаланушының клиенттік қосымшасы. Клиенттік қосымшалар әртүрлі болуы мүмкін: ол мәтіндік утилита болуы мүмкін, графикалық қосымша, веб-беттерді көрсету үшін деректер қорын пайдаланатын веб-сервер, немесе деректер қорына қызмет көрсететін мамандырылған құрал. Кейбір клиенттік құрылымдар PostgreSQL дистрибутив бөлігінде жабдықталады, алайда көпшілігін бөтен әзірлеушілер жасайды.
Басқа қарапайым клиент-серверлік қосымшалар сияқты, клиент пен сервер әр түрлі жеке компьютерлерде орналаса алады. Бұл жағдайда олар TCPIP желісі арқылы қатынасады. Маңызды нәрсе: клиенттік компьютерде бар файлдарға рұқсат етілгенімен, сервер компьютерінде рұқсат етілмеуі мүмкін (немесе басқа атпен рұқсат етілуі мүмкін).
PostgreSQL сервері бірнеше қосылған клиенттерге қызмет көрсете алады. Амалды жүзеге асыру үшін ол әр қосылуға жеке процессті бастайды (тудырады). Негізгі процесс рostgres-ке әсер етпей, клиент пен серверлік процесс сөйлеседі десек болады. Осылайша негізгі серверлік процесс әрқашан жұмыс жасайды және клиенттік қосылымдарды күтеді; оларды қабылдап, ол клиент пен жеке серверлік процесстің байланысын ұйымдастырады (әрине, бұның бәрі пайдаланушыға білінбей өтеді).
0.3 Деректер қорын құру
Біріншіден, деректер қоры серверіне рұқсат бар жоғын тексеру - бұл деректер қорын құруға әрекет ету. PostgreSQL жұмыс сервері көптеген деректер қорын басқаруға мүмкіндік береді, яғни әр түрлі пайдаланушылар мен жобаларға жеке деректер қорын жасауға мүмкіндік береді.
Деректер қорын құру үшін, мысалда mydb деген атаумен, келесі команданы орындау керек:
$createdb mydb
Егер ешқандай хабарлама шықпаса, демек операция сәтті орындалды.
Егер createdb: command not found деген хабарлама шыққан жағдайда PostgreSQL дұрыс орындалмаған. Осы жағдайда сол команданы толық жолын көрсете отырып шақыру керек:
$usrlocalpgsqlbincreatedb mydb
Тағы бір мүмкін жауап:
createdb: не удалось подключиться к базе postgres:
не удалось подключиться к серверу: No such file or directory
Он действительно работает локально и принимает
соединения через доменный сокет "tmp.s.PGSQL.5432"?
Бағдарлама жергілікті жұмыс істейді және байланысты "tmp.s.PGSQL.5432"? домен сокетінен алатынын көрсетеді.
Тағы хабарлама мысалы:
createdb: не удалось подключиться к базе postgres:
Маңызды: пайдаланушының аты пайда болатын joe рөлі жоқ. Бұл PostgreSQL администраторы тіркеу жазбасын жасамағынын білдіреді. (PostgreSQL тіркеу жазбасы операциялық жүйе пайдаланушыларының тіркеу жазбасынан ерекшеленеді.)
Жаңа пайдаланушы құру үшін PostgreSQL (әдетте бұл postgres) орнатқан операциялық жүйе пайдаланушысы болу керек. Сонымен қатар, операциялық жүйедегі атпен сәйкеспейтін, - U кілтін қолданып немесе PGUSER айнымалы ортаны орнаты, PostgreSQL аты тағайындалады.
Егер пайдаланушы тіркеу жазбасы құрылып, алайда деректер қорын құруға рұқсаты жоқ жағдайда келесі хабарлама шығады:
createdb: создать базу данных не удалось:
ОШИБКА: нет прав на создание базы данных
Деректер қорын құруға барлық пайдаланушыларға рұқсат етілмеген. Егер PostgreSQL деректер қорын құрудан бас тартса, пайдаланушыға қолайлы рұқсат керек. Осы жағдайда администраторға жолығу керек. Егер PostgreSQL пайдаланушы өзі орнатқан жағдайда деректер қоры серверін іске қосатын жүйеге өз атымен кіру керек.
Бұл жағдайды былай түсіндіруге болады: PostgreSQL пайдаланушылар тіркеу жазбасы операциялық жүйе тіркеу жазбасынан өзгеше. Деректер қорына қосылу кезінде қандай пайдаланушы атымен PostgreSQL қосылуға болатынын көрсетуге болады. Әдепкі бойынша пайдаланушының операциялық жүйеде тіркелген аты қолданады. Осылайша PostgreSQL-де әрдайым серверді іске қосатын жүйелік пайдаланушының атымен сәйкес келетін тіркеу жазбасы бар. Және осы пайдаланушы атымен PostgreSQL қосылу үшін дәл осы атпен жүйеге кіру міндетті емес, тек оны - U параметрімен беру жеткілікті.
Сондай-ақ басқа есімдермен деректер қорын құруға болады. PostgreSQL кез келген мөлшерде деректер қорын құуға мүмкіндік береді. Деректер қорының аты әріптен басталу керек және 63 таңбадан аспауы тиіс. Деректер қорының аты ретінде пайдаланушының атын қолданған ыңғайлы.
Көптеген утилитаралар осылардың бәрін әдепкі бойынша болжайды, сондықтан командаларды енгізуді жеңілдетуге болады. Осы атпен деректер қорын құру үшін келесі команданы енгізу керек:
$createdb.
Егер деректер қоры қолданыстан шықса, оны жоюға болады:
$dropdb mydb.
(Бұл команда деректер қорының атын ағымдағы пайдаланушының аты деп санамайды, оны анық көрсету керек.) Нәтижесінде физикалық түрде қорға қатысты барлық файлдар жойылады, және осы амалды тоқтатуға болмағандықтан, салдары туралы ойланбай жүзеге асыруға болмайды.
0.4 Деректер қорына қосылу
Деректер қорын құрғаннан кейін , оған қосылуға болады:
oo Psql атты PostgreSQL терминалды бағдарламасын іске қосу; бағдарламада интерактивті SQL командаларын енгізу, өзгерту және орындауға болады.
oo Қолданыстағы графикалық құралдарды, мысалы pgAdmin немесе ODBC немесе JDBC қолдауы бар кеңсе пакеті, қолдану; олар деректер қорын құру мен басқаруға мүмкіндік береді.
oo Көптеген қолжетімді интерфейс тілдерін қолданып жеке қосымша жазу.
Psql көмегімен mydb деректер қорына қосылу үшін төмендегі команданы теру керек:
$psql mydb
Егер деректер қорының атын көрсетпеген жағдайда пайдаланушының аты таңдалады.
Psql-де келесі хабарлама шығады:
psql (9.5.5)
Type "help" for help.
mydb=
Соңғы жол өзгеше болуы мүмкін:
mydb=#
Бұл көрініс пайдаланушының суперпайдаланушы екенін көрсетеді, демек PostgreSQL данасын өзі орнатқан. Осы жағдайда рұқсат ету шараларының ешқайсысы пайдаланушыға таратылмайды, бірақ мәліметтер енгізу мақсатына маңыздысы жоқ.
createdb және psql командалары серверге бірдей қосылады, сондықтан бірінші команда жұмыс істесе, екіншісі де жұмыс істеуі керек. psql енгізу кезіндегі соңғы жол бұл psql пайдаланушының командаларын күтіп тұрғаны және SQL-сұраныстарын psql жұмыс ортасына енгізуге болады деген шақыру.
Мысалы:
mydb=SELECT version();
version
----------------------------------- ----------------------------------- -
PostgreSQL 9.5.5 on i586-pc-linux-gnu, compiled by GCC 2.96, 32-bit
Начало
5
(1 row)
mydb= SELECT current_date;
date
------------
2002-08-31
(1 row)
mydb= SELECT 2 + 2;
?column?
----------
4
(1 row)
Psql бағдарламасында SQL-операторлары болып табылмайтын көптеген ішкі командалары бар. Олар қиғаш сызықтан басталады, \. Мысалы, PostgreSQL SQL-командам туралы анықтама алуға болады, келесі командына енгізу арқылы:
mydb=\h
psql шығу үшін келесі команда:
mydb=\q
және psql өзінің жұмысын тоқтатады, ал пайдаланушы операциялық жүйенің командалық қабықшасына қайта келеді (ішкі командалар туралы білу үшін psql команда жолына \? енгізу керек).
3 SQL тілі
0.1 Кіріспе
PostgreSQL тілінің мүмкіндіктерінің кейбіреуі стандарт кеңейтілуі бола тұра, SQL туралы, melt93 және date97 қосқанда, көптеген кітаптар жазылған. Келесі мысалдарда mydb деректер қоры құрылып және psql іске қосылды деп есептейміз. Басқа мысалдар PostgreSQL каталогындағы (srctutorial) бастапқы код пакетінде көруге болады.
(PostgreSQL орындаулы код дистрибутивтары осы файлдарды қоспауы мүмкін.)
Бұл файлдарды қолдану үшін каталогқа өтіп make жүзеге асыру керек:
$cd ... srctutorial$make
Осы амал орындалған кезде пайдаланушы типі мен функциялары бар С модулі құрастырылып және скриптар құрылады. Содан кейін жұмысты бастау үшін келесі команданы орындау керек:
$cd ... tutorial$psql -s mydb...mydb=\i basics.sql
\i командасы берілген файлдан командаларды оқыйды және орындайды. Psql берілген - s параметрі серверге әрбір оператор жібергеннен кейін кідірген кезде оны қадамдық режимге ауыстырады. Қолданған командалар basics.sql файлда орналасқан.
0.2 Негізгі түсініктер
PostgreSQL - деректер қорын басқарудың реляционды жүйесі (ДҚБРЖ). Бұл деректер қорын басқару жүйесі қарым-қатынастар ретінде (relation) берілгенін білдіреді. Қарым-қатынас кестенің математикалық анық белгілеуі. Мәліметтерді кестеде сақтау бүгінгі таңда кең тараған, деректер қорын ұйымдастырудың көптеген тәсілдері барына қарамастан бұл айқын шешім сияқты. Мысалы, Unix сияқты операциялық жүйелерде файлдар мен каталогтар иерархиялық деректер қорын құрады, ал қазіргі таңда объектілі-бағытталған деректер қоры белсенді дамуда. Кез келген кесте атаулы қатарлардан тұрады. Кестенің барлық қатарлары бірдей атаулы бағандары бар, тағы әрбір қатарға белгілі бір мәліметтер типін тағайындалады. Бағандар реті қатарда тұрақталғанымен, SQL кестеде қатардардың ретін сақталуына кепілдік жасамайтынын есте сақтау керек (алайда оларды енгізу кезінде сұрыптауға болады).
Кестелер деректер қорында бірлеседі, ал PostgreSQL сервер данасының бірі басқаратын деректер қор жиынтығы деректер қор кластерін құрады.
0.3 Кесте құру
Кестенің атын, бағандарының аты мен типін тізіп кесте құруға болады.
CREATE TABLE weather (
city varchar(70),
temp_lo int, -- тәуліктегі минималды температура
temp_hi int, -- тәуліктегі максималды температура
prcp real, -- жауын-шашын деңгейі
date date
);
Жоғарыдағы барлық мәтінді қатарды таңба ауыстырумен psql енгізуге болады. Psql команда ; жеткенге дейін жалғасатынын түсінеді.
SQL командаларында бос орын таңбаларын (бос орын, табуляция және жол аудармалары) еркін қолдануға болады. Бұл пайдаланушы команданы басқаша туралап немесе тіпті бір қатарда орналастырып енгізуге болатынын білдеріді. Екі минус (--) түсіндірме басталғанын білдіреді. Осы таңбадан кейін жазылғанның бәрі ескерілмейді. SQL кілтті сөздер мен идентификаторлар кезінде регистрге сезімтал емес, бірақ ерекшелік ретінде бағамға алынған идентификаторлар алынады.
varchar(70) 70 таңбаға жететін ұзындықты қарапайым таңбалық қатарды сақтайтын мәліметтер типін анықтайды.
int - қарапайым бүкілсандық тип. real - өзгермелі нүктесі бар бір дәлдікті сандар сақтауға арналған тип. date - мезгіл типі. (Ия, date типті қатар date деп аталады. Бұл ыңғаайлы болуы мүмкін немесе адастыруы да шамалы - қалай қарағанға байланысты.)
PostgreSQL SQL стандартты типтерін қолдайды: int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp и interval, және де басқа да әмбебап типтер мен геометриялық типтердің бай жиынтығы. Сонымен қатар, өздігінен жеке мәлімет типтер жиынтығын құрып PostgreSQL бағдарламасын кеңейтуге болады. Салдар ретінде, типтер атауы кілттік сөздер болып табылмайды, алайда ерекшелік ретінде арнаулы SQL стандарт құрылымын іске асыруға талап етілген жағдайды атауға болады. Келесі кесте мысалында қала және олардың географиялық орналасуы сақталады:
CREATE TABLE cities (
name varchar(80),
location point
);
Бұл жерде point - PostgreSQL ерекше мәліметтер типінің типі. Соңында, қандай да бір кесте керек емес немесе басқаша қайта құру керек болған жағдайда, кестені келесі команданы қолданып жоюға болады:
DROP TABLE кесте_аты;
0.4 Кестеге қатар қосу
Кестеге қатар қосу үшін INSERT операторы қолданылады:
INSERT INTO weather VALUES ('Astana, 46, 50, 0.25, 2017-03-27');
Айта кететін нәрсе, барлық мәліметтер типіне шамалы анық формат қолданылып тұр. Қарапайым сандық белгілерден басқа константалар, осы мысал сияқты, әдетте апострофқа (') алынады. date типі шынында өте икемді және әр түрлі форматты алады. Point типі координат жұбын енгізуді талап етеді, мысалы осылай:
INSERT INTO cities VALUES ('Astana', '(-194.0, 53.0)');
Көрсетілген синтаксис қатар ретін есте сақтауды талап етеді. Сондай-ақ қатарларды анық тізіп балама жазбаны қолдануға болады:
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('Astana', 43, 57, 0.0, 2016-11-29');
Егер кейбір қатарларды қалдыру керек болса басқа ретпен тізуге болады, мысалы:
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES (2016-11-29', 'Hayward', 54, 37);
Көптеген әзірлеушілер кестедегі ретіне сену орнына кесте қатарларын анық тізгенді қалайды.
Пайдаланушы үлкен мәліметтер көлемін қарапайым мәтіндік файлдардан көшіре алады; ол амал үшін COPY командасы қолданылады. Әдетте осы тәсіл жылдамырақ, өйткені COPY командасы осындай амалға оңтайландырылған, алайда INSERT салыстырғанда икемі аздау. Мысалы: оның мына түрде қолдануға болады:
COPY weather FROM 'homeuserweather.txt';
Бұл жерде көрсетілген файл клиенттік емес, серверлік процесс жүріп жатқан компьютерде қолжетімді екені тұспалданады. Содан бері көрсетілген файл тікелей серверде оқылады.
0.5 Сұраныс орындау
Кестедегі мәліметтерді алу үшін сұраныс орындау қажет. Ол үшін SELECT SQL-операторы арналған. Оператор бірнеше бөлшектерден тұрады: таңдамалар (алынатын қатарлар тізімі жазылады), кестелер тізімі (бұнда мәліметтер алынатын кестелер тізімі жазылады) және міндетті емес шарттар (анықтайтын шектеулер). Мысалы, weather кестесіндегі барлық қатарды алу үшін келесе жолды енгізу керек:
SELECT * FROM weather;
Бұл жерде * барлық қатар дегеннің қысқаша белгілеуі. Осылайша ол төмендегі жазумен тепе-тең:
SELECT city, temp_lo, temp_hi, prcp, date
FROM weather;
Нәтижесінде келесі жазба шығу керек:
city temp_lo temp_hi prcp date
---------------+---------+--------- +------+------------
San Francisco 46 50 0.25 1994-11-27
San Francisco 43 57 0 1994-11-29
Hayward 37 54 1994-11-29
(3 rows)
Таңдамада қатарларға сілтемеден басқа айнымалыларды жазуға болады. Мысалы:
SELECT city, (temp_hi+temp_lo)2 AS temp_avg, date
FROM weather;
Нәтижесі:
city temp_avg date
---------------+----------+-------- ----
San Francisco 48 1994-11-27
San Francisco 50 1994-11-29
Hayward 45 1994-11-29
(3 rows)
AS сөйлемінің шығыс қатарының атын өзгертуге болатынына назар аудару керек. (AS сөзін жазу мақсат емес.)
Қайтаратын қатар санын шектейтін WHERE сөйлемін қосу арқылы сұранысты шартпен толықтыруға болады. WHERE сөйлемінде қатар фильтрі ретінде жұмыс атқаратын логикалық өрнек (шындыққа тексеріс) көрсетіледі: нәтижесіде өрнегі шындық болған қатарлар ғана қалады. Бұл өрнекте әдеттегі логикалық операторлар (AND, OR және NOT) болуы мүмкін. Мысалы, келесі сұраныс Сан-Фрасциско қаласында жауын күндері қандай ауа-райы болғанын көрсетеді:
SELECT * FROM weather
WHERE city = 'San Francisco' AND prcp 0.0;
Нәтижесі:
city temp_lo temp_hi prcp date
---------------+---------+--------- +------+------------
San Francisco 46 50 0.25 1994-11-27
(1 row)
SELECT * жиі дайындалусыз, тосын жазады, алайда өндірістік кодта жаман стиль ретінде қабылданады, өйткені осындай сұраныстар нәтижесі жаңа қатар қосқанда ауысатын болады.
Сұранысты белгілі ретпен алуға болады:
SELECT * FROM weather
ORDER BY city;
Нәтижесінде көрсетіледі:
city temp_lo temp_hi prcp date
---------------+---------+--------- +------+------------
Hayward 37 54 1994-11-29
San Francisco 43 57 0 1994-11-29
San Francisco 46 50 0.25 1994-11-27
Бұл мысалда сорттау реті толық анықталмаған, сондықтан пайдаланушы Сан-Франциско қатарын кез келген ретпен алуы мүмкін. Бірақ жоғарыдағы нәтижені нақты алу үшін келесі команданы теру керек:
SELECT * FROM weather
ORDER BY city, temp_lo;
Қажет болса көшірме қатарларды сұраныстан алып тастауға болады:
SELECT DISTINCT city
FROM weather;
Алынған нәтиже:
city
---------------
Hayward
San Francisco
(2 rows)
Осы жерде де қатарлар реті өзгертіле алады. Өзгеріссіз нәтижелер алу үшін DISTINCT және ORDER BY сөйлемдерін қосу керек:
SELECT DISTINCT city
FROM weather
ORDER BY city;
0.6 Кестелерді қосу
Осыған дейін барлық тапсырыстар тек бір кестеге қатынасты. Алайда сұраныстар бір уақытта бірнеше кестелерге қатынай алады немесе сол кестеге әртүрлі қатарлар жиынтығын өңдейтіндей етіп қатынауға болады. Бір кестенің әртүрлі қатар жиынына немесе бірнеше кестелерге бір уақытта қатынайтын сұраныс қосылым (JOIN) деп аталады. Мысалы, пайдаланушы барлық қалалардың ауа-рай жағдайы мен координаталарын шығарғысы келді. Ол үшін weather кестесінің әр қатарындағы city бағанын cities кестесінің барлық қатарындағы салыстыру name бағанымен салыстыру керек және осы мағыналар сәйкескен қатарларды таңдау керек.
Айта кететін жайт, бұл нақты модель емес. Әдетте қосылымдар тиімдірек орындалады (мүмкін жұп қатарлардың бәрі салыстырылмайды), бірақ бұның бәрі пайдаланушыдан жасарын түрде өтеді. Оны келесі сұраныс арқылы жүзеге асырса болады:
SELECT *
FROM weather, cities
WHERE city = name;
Алынған нәтиже:
city temp_lotemp_hi prcp date name location
--------------+-------+-------+---- -+-----------+--------------+------ ----
San Francisco 46 50 0.25 1994-11-27 San Francisco (-194,53)
San Francisco 43 57 0 1994-11-29 San Francisco (-194,53)
(2 rows)
Мәліметтерді алудың екі ерекшелігіне назау аудару керек. PostgreSQL ескі нұсқаларын қосқанда кейбір ДҚБЖ-де DISTINCT қосымшасын іске асыру қатарларды автоматты түрде реттейді, сондықтан ORDER BY қосу маңызды емес. Бірақ SQL стандарты оны талап етпейді және PostgreSQL ағымдағы нұсқасы DISTINCT кейін қатарлардың анық ретіне кепілдік бермейді.
Нәтижесінде Хейуорд (Hayward) қаласымен қатар жоқ. Оның себебі cities кестесінде бұл қалаға мәліметтер қатары жоқ, ал қосылым кезінде weather кестесінің сәйкесін таппаған барлық қатарлары түсіріледі.
Қала атауы екі бағанда болып қалды. Бұл дұрыс және түсіндірілуі: weather және cities кестелерінің қатарлары қосылғанын көрсетеді. Алайда практикада жағымсыз, сондықтан * қолданғаннан, ең жақсысы керек қатарларды айқын тізіп жазған дұрыс:
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
Негізі жағымды стиль ретінде қосылым сұранысында бағандардың толық атын жазған саналады, өйткені кейінірек кестеге қайталанатын қатарлар қосылуы мүмкін жағдайда сұраныс сынбас үшін жазады.
0.7 Агрегаттық функциялар
Көптеген реляционды деректер қоры серверлері сияқты PostgreSQL агрегаттық фунцияларды қолдайды. Агрегаттық функциялар көптеген қатарларды өңдей отырып, тек бір мағынаны есептейді. Мысалы, есептейтін агрегаттық функциялар бар: count (мөлшері), sum (суммасы), avg (арифметикалық орта), max (максимум) және min (минимум) қатар жиынығы үшін. Мысалыға, барлық көріністер ішінде ең жоғарғы температураны табуға болады:
SELECT max(temp_lo) FROM weather;
max
-----
46
(1 row)
Егер кай қалада (немесе қалаларда) осы температура бақыланғанын білу керек болса, оны шығаруға болады:
SELECT city FROM weather
WHERE temp_lo = max(temp_lo); НЕВЕРНО
Алайда бұл амал іске аспайды, өйткені max агрегаттық функциясын
WHERE сөйлемінде қолдануға болмайды. (Бұл шектеу WHERE сөйлемі қай қатар үшін агрегаттық фунцияны есептеу керектігін анықтайды, сол себептен ол, демек, агрегаттық фунцияға дейін есептелуі керек.) Бірақ, әдеттегідей, сұранысты қайта іске қосуға керек және керекті нәтиже алу үшін сұраныс ішіндегі сұранысты қолдану керек:
SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
city
---------------
San Francisco
(1 row)
Енді бәрі орнында сұраныс ішіндегі сұраныс жеке орындалады және агрегаттық функция нәтижесі сыртқы сұраныста болып жатқан амалдардан тәуелсіз есептеледі.
Агрегаттық фунциялар GROUP BY сөйлемімен комбинациясымен өте пайдалы. Мысалы, қалалардың арасындағы тәуліктік максимум температураны алуға болады:
SELECT city, max(temp_lo)
FROM weather
GROUP BY city;
Язык SQL
12
city max
---------------+-----
Hayward 37
San Francisco 46
(2 rows)
Бұл жерде әр қала үшін бір-бір қатар алынды. Әрбір агрегаттық нәтиже әр қалаға тиесілі кесте қатарлары арқылы есептеледі. Біріктірілген қатарларды HAVING сөйлемі арқылы жиынтықтай аламыз:
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING max(temp_lo) 40;
city max
---------+-----
Hayward 37
(1 row)
Дәл сол нәтижелер алынды, бірақ та temp_lo барлық мағыналары 40 кем қалалар үшін.
Соңында, егер тек S басталатын қалалар қызығушылық тудырса, оны іске асыруға болады:
SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE 'S%' 1
GROUP BY city
HAVING max(temp_lo) 40;
LIKE операторы шаблон бойынша салыстыруды орындайды.
Агрегаттық функциялар мен WHERE және HAVING SQL-сөйлемдері қалай қандай арақатынаста болатынын түсінудің маңызы зор. WHERE және HAVING негізгі артықшылығы: WHERE алдымен қатарларды таңдайды, содан кейін оларды жиынтықтап және олардың агрегаттық фунцияларын есептейді, осылайша агрегатты есептеу үшін қатарларды таңдайды. Ал HAVING агрегаттық фунцияларды есептеп жинағаннан кейін ғана қатарлар жиынтығын таңдайды. Нәтижесінде WHERE сөйлемінде агрегаттық функция болмауы керек; агрегаттық функцияларды есептеу үшін қатарларды анықтауға агрегаттық функцияларды қолданудың мағынасы жоқ. HAVING сөйлемінде, керісінше, әрдайым агрегаттық фунциялар бар. (Қатаң сөйлегенде, HAVING сөйлемін агрегат қолданбай жазса болады, бірақ оның пайдалылығы біршама. Дәл осы шарт WHERE кезеңінде тиімдірек жұмыс істеуі мүмкін.)
Жоғарыдағы мысалда қолданған фильтр HAVING қосымша шектеуіне қарағанда тиімдірек, өйткені онымен WHERE шартын қанағаттандырмайтын барлық қатар үшін агрегаттарды есептеу және жиынтықтау керек емес.
0.8 Мәліметтерді өзгерту
Қолданыстағы қатарлардағы мәліметтерді UPDATE командасын қолданып өзгертуге болады. Мысалыға, пайдаланушы 28 қарашадан кейін барлық температура көрсеткіштері екі градусқа көтерілгенін байқады. Келесі жолмен мәліметтерді түзеуге болады:
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date '1994-11-28';
Мәліметтердің жаңа күйі:
SELECT * FROM weather;
city temp_lo temp_hi prcp date
Язык SQL
13
---------------+---------+--------- +------+------------
San Francisco 46 50 0.25 1994-11-27
San Francisco 41 55 0 1994-11-29
Hayward 35 52 1994-11-29
(3 rows)
0.9 Мәліметтерді жою
Қатарларды кестеден DELETE командасы көмегімен жоюға болады. Мысалы, Хейуор қаласының бұдан былай ауа-райы қызығушылық тудырмайды. Оыс жағдайда керек емес қатарды кестеден жоюға болады:
DELETE FROM weather WHERE city = 'Hayward';
Хейуор қаласына қатысты барлық жазбалар жойылған:
SELECT * FROM weather;
city temp_lo temp_hi prcp date
---------------+---------+--------- +------+------------
San Francisco 46 50 0.25 1994-11-27
San Francisco 41 55 0 1994-11-29
(2 rows)
DELETE FROM кесте_аты;
түрдегі операторлардан сақтану керек. Шарт көрсетілмесе DELETE кестедегі барлық қатарларды жояды, оны толық тазалайды. Сонымен қатар жүйе операцияны растауды сұрамайды.
4 Кеңейтілген мүмкіншіліктер
4.1 Көріністер
Пайдаланушыға қалалардың координаталары мен ауарайы жазбаларының тізімі керек делік, алайда ол әр кезде сұранысты жаза бергісі келмейді. Осы сұраныс бойынша көрініс жасауға болады, дәлірек айтқанда, сұранысқа ат беру. Содан кейін оған қарапайым кестеге сияқты байланысу:
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;
Көрінсітердің белсенді қолдануы SQL деректер қорын жақсы жобалаудың негізгі аспектісі. Көріністер кестелердің ішкі құрылымын жасыруға мүмкіндік беред; құрылымдар қосымшалар даму және сенімді интерфейстермен өзгеруі мүмкін.
Көріністер кестелер қолданған барлық жерде дерлік қолдануға болады. Көріністер жиі басқа жеке көріністер қорында жасалынады.
4.2 Ішкі кілттер
Мысалдағы weather және cities кестелері қайта қарастырылады. Келесі тапсырманы қарастыру керек: cities кестесіндегі қатарлармен сәйкеспейтін weather кестесіне ешкім қатар қоса алмайтындай етіп жасау керек. Бұл мәліметтердің сілтемелік тұтастығын қамту деп аталады. Қарапайым ДҚБЖ мына жолмен іске асыруы керек еді (мүмкін болған жағдайда ғана) :
бастапқыда cities кестесінде сәйкес жазбалар баржоғын тексеру керек, содан кейін жаңа жазбаны weather кестесіне қосады немесе қабылдайды. Бұл өте проблемалық тәсіл және ыңғайлы емес, сол себептен PostgreSQL осының бәрін пайдаланушы орнына жасай алады.
Кестенің жаңа хабарлануы келесі түрде болады:
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
Енді мысал ретінде қолайыз жазба енгізіледі:
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
ОШИБКА: INSERT или UPDATE в таблице "weather" нарушает ограничение внешнего
ключа "weather_city_fkey"
ПОДРОБНОСТИ: Ключ (city)=(Berkeley) отсутствует в таблице "cities".
Пайдаланушы қосымшасының талабы бойынша ішкі кілттердің мінезқұлқын орналастыруға болады.
4.3 Транзакциялар
Транзакциялар бұл барлық ДҚБЖ негізгі іргетасы. Транзакциялар мағынасы әсерлер тізімін бір барлығы немесе ештеңе операциясына біріктіргенінде. Тізім ішіндегі интервал жағдайлары басқа транзакцияларға көрінбейді, және де егер транзакцияны сәтті аяқтауға бір нәрсе бөгет болған жағдайда, осы амалдардың бірдебіреуі деректер қорында сақталмайды. Мысалы, клиент шоттары және банк филиалдарының ортақ сомасы туралы мәлімет бар банктің деректер қоры қарастырылады. Алиса шотынан Боб шотына 100 доллар ауыстыру керек. Жеңілдік үшін сәйкес SQLкомандаларын келесі түрде жазуға болады:
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
Командалардың нақты мазмұны мазмұны маңызды емес; маңыздысы тек осы қарапайым операцияны орындауға бірнеше жеке операция орындау қажет болғаны. Сонымен қатар банк тұрғысынан қарағанда осы амалдардың бәрі бірге орындалғаны қажет, немесе мүлдем орындалмауы тиіс. Егер Боб 100 доллар алса, бірақ Алисаның шотынан алынбаса, бұны жүйенің қателігі деп айтуға болмайды. Және, керісінше, Алиса ақша аударып Бобқа жетпеген жағдай да жақсы деп атауға келмейді. Операцияны соңына дейін орындауға бір нәрсе бөгет болған жағдайда, бір де бір амал деректер қорында ізін қалдырмайды деген кепілдік керек. Ондай кепілдікті амалдарды бір транзакцияға бірітіру жолымен алады. Транзакция атомарлық дейді басқа транзакциялар тұрғысынан ол орындалып толық бекітіледі немесе мүлдем бекітілмейді. Сонымен қатар кепілдік керек: аяқталғаннан кейін және деректер қоры жүйесімен транзакция расталғаннан кейін, оның нәтижелері шынында сақталады және апат болған жағдайдың өзінде жоғалмайды. Мысалы, егер сомасы есептен шығарылды және Бобқа берілді; ол банктен шыққан сәтте шотындаңы сомасы қайта орнына келу мүмкіндігін болдырмау керек. Транзакциондық деректер қоры барлық өзгертулер транзакция аяқталғанша тұрақты қоймада (мысалы, дискіге) сақаталады деген кепілдік береді. Транзакциялық деректер қорының басқа маңызды сипаттамасы атомарлық өзгертулермен тығыз байланысты: бір уақытта бірнеше транзакциялар орындалған сәтте олардың әрқайсысы басқалары орындайтын аяқталмаған өзгертулерді көрмейді. Мысалы, егер бір транзакция бөлім бойынша балансты санап жатқан уақытта, егер Боб бөлімін келуді қарастырмай, Алисаның бөліміндегі шығынды санаса дұрыс емес болады. Сол себептен транзакция барлығы немесе ештеңе қасиеті өзгертулер деректер қорында қалай сақталатыны ғана емес, сонымен қатар жұмыс процесінде қалай көрінетінін анықтау керек. Ашық транзакция өндіретін өзгертулер операция аяқьалғанша басқа транзакцияларға көрінбейді, одан кейін олардың барлығы бәріне бірден көрінеді. PostgreSQL транзакциялар SQLкомандалар жиынтығымен анықталады, BEGIN және
COMMIT командаларымен қоршалған. Осылайша, банк транзакциясы келесі түрде болуы керек еді:
BEGIN;
Расширенные возможности
16
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- ...
COMMIT;
Егер транзакция орындау процесінде олардың өзгертулерін бекітпеу шешімі қабылданса (мысалы, Алиса балансы минус болса), COMMIT орнына ROLLBACK командасын орындауға болады, және барлық өзгертулер күшін жояды.
PostgreSQL шынында барлық SQLоператорды транзакция ретінде өңдейді. Егер пайдаланушы BEGIN командасын қоймаса, онда әрбір жеке оператор айқын емес BEGIN және COMMIT командаларымен қоршалаып қалады (сәтті аяқталу жағдайында). BEGIN және COMMIT командаларымен қоршалатын операторлар тобын кейде транзакция блогы деп атайды. Кейбір клиенттік кітапханалар BEGIN және COMMIT командаларын автоматты түрде қосады және пайдаланушы орнына айқын емес транзакция блоктарын жасайды. Транзакциялардағы операторлармен сақтау нүктелерін қолдана отырып егжейтегжейлі басқаруға болады. Сақтау нүктелері таңдаулы түрде транзакцияның кейбір бөліктерінің күшін жойдыруға және қалғандарын бекітуге мүмкіндік береді. Сақтау нүктесін SAVEPOINT көмегімен анықтап, керек болса оған ROLLBACK TO командасы көмегі арқылы қайта оралуға болады. Сақтау нүктесінен қайтарымға дейінгі деректер қорында болған барлық өзгерістер күшін жояды, бірақ алдында болған өзгерістер сақталады. Пайдаланушы сақтау нүктесіне қайта келгенде нүкте жұмысын жалғастырады; оған бірнеше рет қайтарым орындай аласыз. Басқа жағынан қарағанда, пайдаланушы белгілі бір сақтау нүктесіне қайта оралмайтынына сенімді болса, онда жүйе қорын босату үшін нүктені жоюға болады. Еске сақтайтын нәрсе: жоюда немесе сақтау нүктесіне қайтарымда одан кейін анықталған барлық сақтау нүктелері автоматты түрде жойылады. Осының бәрі транзакция блогында жұмыс жасайды, сол себептен басқа деректер қорымен жұмыс сеанстарында ол көрінбейді. Барлық жасалған амалдар барлығы бірден көріне бастайды тек транзакцияны бекіткен жағдайда, ал күшін жойған амалдар ешқашан көрінбейді. Банк деректер қорына қайтадан келіп, Алиса шотынан 100 доллар сомасын есептен шығарып, Боб шотына қосылады деп болжанады. Және кенеттен сомасы Уоллиға ауыстыру керек болып шықты. Осы жағдайда сақтау нүктесін қолдануға болады:
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
-- қате амал... Уолли шотын қолдану
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;
Бұл мысал, әрине, ойдан шығарылған, алайда ол сақтау нүктелерін қолдана отырып транзакция блогында командалар орындалуын қалай басқару керектігін көрсетеді. Cонымен қатар ROLLBACK TO - бұл, толық күшін жойып және қайта бастауды санамағанда, жүйе қателігі себебінен үзілген жағдайда болып қалған транзакция блогының бақылауын қайтаруының жалғыз тәсілі.
4.4 Терезелік функциялар
Терезелік функция қатарлар жиынтығын үшін есептеулер орындайды, кейбір түрде ағымдағы қатармен байланысты. Агрегаттық фунциямен салыстыруға болады, алайда әдіттегі агрегаттық фунциямен қарағанда, терезелік функцияны қолданғанда бірнеше қатарлар бір қатаға жинақталмайды, олар жеке тұра береді. Ал ішінде қараса, терезелік функция,агрегаттыө сияқты, тек сұраныс нәтижесінің ағымдағы қатарына ғана қатынасып қоймайды. Мысалыға, бөлімдегі әрбір орташа жалақылы жұмысшыларының жалақыларынан салыстыру:
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname)
FROM empsalary;
depname empno salary avg
-----------+-------+--------+------ -----------------
develop 11 5200 5020.0000000000000000
develop 7 4200 5020.0000000000000000
develop 9 4500 5020.0000000000000000
develop 8 6000 5020.0000000000000000
develop 10 5200 5020.0000000000000000
personnel 5 3500 3700.0000000000000000
personnel 2 3900 3700.0000000000000000
sales 3 4800 4866.6666666666666667
sales 1 5000 4866.6666666666666667
sales 4 4800 4866.6666666666666667
(10 rows)
Алғашқы үш баған тура empsalary кестесіден алынады, сонымен қатар кестенің әрбір қатары үшін нәтиже қатары бар. Төртінші бағанда, ағымдағы қатар секіліді depname мағынасы бар, барлық қатардан есептелген орташасы орналасқан. Орташаны есептеуді негізі, агрегаттық функция деп білетін, avg функциясы орындайды, бірақ OVER сөйлемі оны терезелік функцияға айналдырады, өйткені ол тек берілген қатарлар жиынтығын өңдейді.
Терезелік функцияны шақыру әрдайым аты мен терезелік фунцияның аргументтерінен кейін OVER сөйлемін қосады. OVER сөйлемі терезелік функция арқылы өңдеуге сұраныс үшін қатарларды қалай бөлу керектігін анықтайды. OVER сөйлемін толықтыратын PARTITION BY сөйлемі көрсетеді: қатарларды топтар немесе бөлімдер бойынша бөлу және бірдей мағыналарды PARTITION BY өрнегіне біріктіру керектігін. Терезелік функция ағымдағы қатармен бір бөлімге түскен қатарлар арқылы есептеледі. Сонымен қатар пайдаланушы OVER ішінде ORDER BY қолдану арқылы терезелік функциямен қатарлар қай ретте өңделетінін анықтай алады. Терезе үшін ORDER BY реті қатарлар көрсетілген ретпен сәйкес келмеуі мүмкін.
Мысалы:
SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
depname empno salary rank
-----------+-------+--------+------
develop 8 6000 1
develop 10 5200 2
develop 11 5200 2
develop 9 4500 4
develop 7 4200 5
personnel 2 3900 ... жалғасы
Сіз бұл жұмысты біздің қосымшамыз арқылы толығымен тегін көре аласыз.
Кіріспе 7
1 PostgreSQL деректер қорының сервері 8
1.1 PostgreSQL дегеніміз не? 8
1.2 PostgreSQL қысқаша тарихы 8
1.2.1 Берклидегі POSTGRES жобасы 8
1.2.2 Postgres95 9
1.3 PostgreSQL 10
2 Бастау 11
2.1 Орнату 11
2.2 Архитектура негіздері 11
2.3 Деректер қорын құру 12
2.4 Деректер қорына қосылу 13
3 SQL тілі 15
3.1 Кіріспе 15
3.2 Негізгі түсініктер 15
3.3 Кесте құру 15
3.4 Кестеге қатар қосу 16
3.5 Сұраныс орындау 17
3.6 Кестелерді қосу 19
3.7 Агрегаттық функциялар 20
3.8 Мәліметтерді өзгерту 22
3.9 Мәліметтерді жою 22
4 Кеңейтілген мүмкіншіліктер 24
4.1 Көріністер 24
4.2 Ішкі кілттер 24
4.3 Транзакциялар 25
4.4 Терезелік функциялар 27
4.5 Мұрагерлік 31
5 SQL синтаксисі 33
5.1 Лексикалық құрылым 33
5.1.1 Идентификаторлар мен кілттік сөздер 33
5.1.2 Константалар 35
5.1.2.1 Қатарлық константалар 36
5.1.2.2 С стильдегі арнайы тізбекті қатарлы константалар 36
5.1.2.3 Unicode арнайы тізбекті қатарлық константалар 37
5.1.2.4 Долларға алынған қатарлық константалар 38
6 SQL кеңейтілуі 39
6.1 Кеңейтілуді жүзеге асыру 39
6.2 PostgreSQL типтер жүйесі 40
6.2.1 Негізгі типтер 40
6.2.2 Құрамдас типтер 40
6.2.3 Домендер 41
6.2.4 Жалған типтер (псевдотип) 41
6.2.5 Полиморфты типтер 41
6.3 Пайдаланушы функциялар 42
6.4 Функцияларды қайта жүктеу 43
6.5 Функциялардың өзгергіш категориясы 44
6.6 Ішкі функциялар 45
7 Вебқұрылымның серверлік бөлігін жобалау 47
8 Еңбекті қорғау 55
8.1 Еңбек шартын талдау 55
8.2 Жұмыс орнының жоспары 57
8.3 Есептеу бөлімі 58
8.3.1 Кондиционерлеу жүйесін есептеу 58
8.3.2 Табиғи жарықты есептеу 63
8.4 ӨТҚ бойынша қорытынды 66
9 Жобаның техникалық-экономикалық негіздемесі 67
9.1 Жұмыстың сипаттамасы және оның қажеттілігін негіздеу 67
9.2 Жұмыс кезінде қолданылатын еңбек ресурсы 68
9.3 Жобалау мен өңдеуге керетін жұмыстың бағасын есептеу 68
9.4 Амортизацияға кететін шығын мөлшерін есептеу 72
9.5 Электрэнергияға кеткен шығын мөлшерін есептеу 74
9.6 Қосымша шығындарды есептеу 74
9.7 Үстеме шығындарды есептеу 75
9.8 Техникаэкономикалық бөлім қорытынды 76
Қорытынды 77
Әдебиттер тізімі 78
Кіріспе
Бүгінгі таңда интернет туралы жалқау ғана білмейді. Алғашында тек ғалымдар мен мамандар қолдануында болған ол қазіргі заман адамының үшінші қолы ретінде қабылданады. Бастапқыда үш ғана компьютерді біріктірген Интернет қазір негізгі ақпарат көзі, ол сандаған мемлекеттер, оның әрбір азаматтарын біріктіріп отыр. Мүмкіндігі шексіз Интернетке ешкім жеке дара қожалық ете алмайды, сол себептен оны шынында бүкіл әлемдік қазына деп атасақ болады.
Бүгінгі таңда нарықта мобильді операторлар, интернет провайдерлер, спутниктік теледидар және кабель өнімдерін берушілер ассортименті көп. Бірақ көрсетілетін қызмет ерекшеліктерін қалай талқылауға болады? Жобаны әзірлеу тобы Қазақстан нарығыдағы барлық қызмет көрсету мекемелеріне аназиз жасап, әрбір ұсынылатын пакеттердің барлық қырсырын талқылады, және ыңғайлы іздестіруші дайындады. Оның көмегімен пайдаланушылар өздеріне лайықты бағасы, жылдамдық, каналдар саны және тағы басқалары бойынша ұсылынған сервистердің ішінен таңдай алады.
1 PostgreSQL деректер қорының сервері
0.1 PostgreSQL дегеніміз не?
PostgreSQL - бұл объектілі - реляциялық деректер қорын басқару жүйесі(ORDBMS, ОРСУБД). Оның негізі POSTGRES, Version 4.2 - Калифорния университеті, Беркли компьютерлік ғылымдар факультетінде жобаланған бағдарлама. POSTGRES - те көптеген жаңалықтар кейінірек коммерциялық ДҚБЖ - де іске асырылды.
Бағдарлама SQL стандарттарының көпшілігін қолдайды және көптеген қазіргі заманғы функцияларын:
oo күрделі тапсырыстар;
oo ішкі кілттер;
oo триггерлер;
oo өзгертілетін көріністер;
oo транзакциялық толықтық:
oo көп нұсқалылық.
Сонымен қатар, пайдаланушылар PostgreSQL мүмкіндіктерін әрбір жолында кеңейте алады, мысалы, өзінің
oo мәліметтер типін;
oo функцияларын;
oo операторларын;
oo агрегаттық функцияларын;
oo индекс әдістерін;
oo процедуралық тілдерін жасай алады.
Тегін лицензия арқасында PostgreSQL тегін қолдануға, өзгертуге және бәріне және кез келген мақсатта - жеке, коммерциялық және оқыту - таратуға болады.
0.2 PostgreSQL қысқаша тарихы
Бүгінгі атауы PostgreSQL деректер қорын объектілі-реляционды басқару жүйесі Беркли, Калифоония университетінде жазылған POSTGRES пакетінен шықты. Екі онжылдықтан кейін PostgreSQL жоабалуы ең дамыған ашық кодды ДҚБЖ болды.
0.2.1 Берклидегі POSTGRES жобасы
Профессор Майкл Стоунбрейкер басқарған POSTGRES жобасын АҚШ Қорғаныс министрлігінің DORPA агенттігі, Әскери-зерттеу қызметін басқару (ARO), Ұлттық Ғылым Қоры (NSF) және ESL, Inc компаниясы демеушілік етті. POSTGRES іске асуы 1986 жылы басталды. Жүйенің бастапқы түсініктері ston86 құжатында, ал бірінші моделінің сипаттамасы rowe87 ұсынылған болатын [7]. Қойма менеджерінің мәні мен архитектурасы ston87b құжатында жазылып қойылған [12]. Содан бері POSTGRES бірнеше даму кезеңінен өтті. Бірінше демоннұсқасы 1987 жылы жұмыс істеді және 1988 жылы ACM-SIGMOD конферециясында көрсетілді. ston90a сипатталған 1 нұсқа сыртқы пайдаланушылар үшін 1989 жылдың маусамында шығарылды [15]. Бірінші ережелер жүйе (ston89) сынына жауап ретінде ол қайта жасалды (ston90b) және 1990 жылдың маусымында щыққан 2 нұсқада жаңа ережелер жүйесі болды [13,14]. 1991 3 нұсқа шықты, онда әртүрлі қойма менеджерлерінің қолдауы пайда болды, жақсартылған сұраныс орындаушы және қайта жазылған ережелер жүйесі. Postgres95 дейін келесі басылымдар негізінде сенімділік пен ұтқырлығын арттыруға бағытталған.
POSTGRES көптеген зерттеулік және өндірісітік тапсырыстардың іске асуына қолданылған.
Олардың ішінде: қаржы мәліметтерін талдау жүйесі, реактивті қозғалтқыштар жұмысының мониторинг пакеті, астериодттарды бақылау деректер қоры, медициналық мәліметер деректер қоры, бірнеше георграфиялық информациялық жүйелер. Сондай-ақ POSTGRES бірнеше университеттерде оқытуға қолданылды. Соңында, Illustra Information Technologies компаниясы (кейін Informix, қазір IBM тиесілі ) кодты қолданып және оған коммерциялық қолданыс тапты. 1992 жылдың соңында POSTGRES Sequoia 2000 ғылыми есептеу жобасының негізгі ДҚБЖ болды.
1993 жылы сыртқы пайдаланушылар саны екі есе артты. Код қызмет етуі мен қолдауы көп уақыт алатыны айқын болды, және ол уақыт зерттеуге жетпейді. Осы жүктемені түсіру үшін Берклидегі POSTGRES жобасы ресми түрде 4.2 нұсқасында жабылды.
1.2.2 Postgres95
1994 жылы Эндри Ю және Джолли Чен POSTGRES-ке SQL тілінің аудармашысын қосты. Жаңа атаумен Postgres95 Интернетте жарияланды және өзінің жолын ашық кодты Берклиде жасалған Postgres ұрпағы ретінде бастады. Postgres95 коды ANSI C кодымен толық сәйкестендіруге келтірілді және 25% кішірейді. Көптеген ішкі өзгерістер арқасында ол жылдамырақ және ыңғайлы болды. Postgres95 нұсқасы 1.0.х POSTGRES нұсқа 4.2 қарағанда шамалы 30-50% тезірек жұмыс істеді (Wisconsin Benchmark тесті бойынша). Қателіктерді түзегеннен басқа келесі өзгерістер енгізілді:
oo PostQUEL сұраныстар тілінің орнына SQL келді (серверде іске асқан). (libpq интерфейс кітапханасы өзінің атын PostQUEL мұрагерлік етті).
Сұраныстар(подзапрос) PostgreSQL шыққанға дейін қолдау көрсетілмеді, алайда оларды Postgres95 SQL пайдаланушы функциялары арқылы балама жасауға болды. Агрегаттық функциялар қайта іске асырылды. Сонымен қатар GROUP BY сөйлемінің қолдауы пайда болды.
oo Интерактивті SQL-сұраныстар үшін GNU Readline қолданған жаңа бағдарлама (psql) жасалды. Ескі бағдарлама monitor керектігі жоғалды.
oo Tcl-клиенттерді қолдайтын жаңа libpgtcl клиенттік библиотека пайда болды. Tcl жаңа командалары рgtclsh қабығының мысалы ретінде Tcl бағдарламаларының Postgres95 серверымен қарым-қатынасын айтуға болады.
oo Үлкен объектілермен жұмыс үшін интерфейс жақсартылды. Мәліметтерді сақтау механизмі ретінде тек инверсионды объектілер қалды. (Инверсионды файлдық жүйе жойылды.)
oo Дана деңгейіндегі ережелер жүйесі жойылды; қайта жазатын ережедер сақталды.
oo Баптапқы кодпен бірге стандартты SQL мүмкіншіліктері мен Postgres95 өзі қысқаша сипаттамалармен тарала бастады.
oo Жинауға GNU make қолданылды (BSD make орнына). Сонымен қатар, Postgres95 GCC түрлендірілмеген нұсқасымен (мәліметтерді түзеу жөнделді) құрастыру мүмкін болды.
0.3 PostgreSQL
1996 жылы Postgres95 атауы уақыт сынағынан өтпейтіні анық болды. Олар түрнұсқалық POSTGRES пен кейінгі SQL қолдауы бар нұсқасы арасандағы байланысты көрсететін PostgreSQL жаңа атауын таңдады. Осы уақытта әзірлеушілер POSTGRES Беркли жобасында басталған нұсқауды нөмірлеуді 6.0 жалғастырды
Көпшілік PostgreSQL-ді дәстүрлі немесе қарапайымдылық үшін POSTGRES атымен атауды жалғастыра берді (енді бас әріптермен сирек айтады). Осы атау лақап ат немесе ресми емес белгілеу ретінде тұр.
Postgres95 құру барысында негізгі тапсырыстар іздеу мен серверлық кодта бар мәселелерді шешу болды. PostgreSQL көшу кезінде жаңа мүмкіншіліктер мен функциялар іске асыруға баса назар аударылды, алайда жұмыс барлық бағытта жүрді.
1 Бастау
0.1 Орнату
PostgreSQL қолдану алдында, әрине, алдын ала орнату керек. PostgreSQL кез келген қарапайым пайдаланушы орната алады; суперқолданушы (root) құқығы қажет емес.
0.2 Архитектура негіздері
Жалғастырмас бұрын PostgreSQL жүйесінің архитектура негіздерін талдау керек. PostgreSQL бөлшектерінің қарым-қатынас суретін жасау керек.
Техникалық тілмен айтқанда PostgreSQL клиент-сервер архитектурасында негізделген. PostgreSQL жұмыс сессиясы келесі процесстер (бағдарламалар) қарым-қатынастарын қамтиды:
oo деректер қорының файлдарын басқаратын басты серверлік процесс, клиенттік қосымшаларын жалғайтын қабылдаушы және деректер қорына қатысты әртүрлі клиенттер тапсырысын орындаушы. Деректер қоры серверінің бұл бағдармаласы postgres деп аталады;
oo деректер қорына қатысты операциялар орындайтын пайдаланушының клиенттік қосымшасы. Клиенттік қосымшалар әртүрлі болуы мүмкін: ол мәтіндік утилита болуы мүмкін, графикалық қосымша, веб-беттерді көрсету үшін деректер қорын пайдаланатын веб-сервер, немесе деректер қорына қызмет көрсететін мамандырылған құрал. Кейбір клиенттік құрылымдар PostgreSQL дистрибутив бөлігінде жабдықталады, алайда көпшілігін бөтен әзірлеушілер жасайды.
Басқа қарапайым клиент-серверлік қосымшалар сияқты, клиент пен сервер әр түрлі жеке компьютерлерде орналаса алады. Бұл жағдайда олар TCPIP желісі арқылы қатынасады. Маңызды нәрсе: клиенттік компьютерде бар файлдарға рұқсат етілгенімен, сервер компьютерінде рұқсат етілмеуі мүмкін (немесе басқа атпен рұқсат етілуі мүмкін).
PostgreSQL сервері бірнеше қосылған клиенттерге қызмет көрсете алады. Амалды жүзеге асыру үшін ол әр қосылуға жеке процессті бастайды (тудырады). Негізгі процесс рostgres-ке әсер етпей, клиент пен серверлік процесс сөйлеседі десек болады. Осылайша негізгі серверлік процесс әрқашан жұмыс жасайды және клиенттік қосылымдарды күтеді; оларды қабылдап, ол клиент пен жеке серверлік процесстің байланысын ұйымдастырады (әрине, бұның бәрі пайдаланушыға білінбей өтеді).
0.3 Деректер қорын құру
Біріншіден, деректер қоры серверіне рұқсат бар жоғын тексеру - бұл деректер қорын құруға әрекет ету. PostgreSQL жұмыс сервері көптеген деректер қорын басқаруға мүмкіндік береді, яғни әр түрлі пайдаланушылар мен жобаларға жеке деректер қорын жасауға мүмкіндік береді.
Деректер қорын құру үшін, мысалда mydb деген атаумен, келесі команданы орындау керек:
$createdb mydb
Егер ешқандай хабарлама шықпаса, демек операция сәтті орындалды.
Егер createdb: command not found деген хабарлама шыққан жағдайда PostgreSQL дұрыс орындалмаған. Осы жағдайда сол команданы толық жолын көрсете отырып шақыру керек:
$usrlocalpgsqlbincreatedb mydb
Тағы бір мүмкін жауап:
createdb: не удалось подключиться к базе postgres:
не удалось подключиться к серверу: No such file or directory
Он действительно работает локально и принимает
соединения через доменный сокет "tmp.s.PGSQL.5432"?
Бағдарлама жергілікті жұмыс істейді және байланысты "tmp.s.PGSQL.5432"? домен сокетінен алатынын көрсетеді.
Тағы хабарлама мысалы:
createdb: не удалось подключиться к базе postgres:
Маңызды: пайдаланушының аты пайда болатын joe рөлі жоқ. Бұл PostgreSQL администраторы тіркеу жазбасын жасамағынын білдіреді. (PostgreSQL тіркеу жазбасы операциялық жүйе пайдаланушыларының тіркеу жазбасынан ерекшеленеді.)
Жаңа пайдаланушы құру үшін PostgreSQL (әдетте бұл postgres) орнатқан операциялық жүйе пайдаланушысы болу керек. Сонымен қатар, операциялық жүйедегі атпен сәйкеспейтін, - U кілтін қолданып немесе PGUSER айнымалы ортаны орнаты, PostgreSQL аты тағайындалады.
Егер пайдаланушы тіркеу жазбасы құрылып, алайда деректер қорын құруға рұқсаты жоқ жағдайда келесі хабарлама шығады:
createdb: создать базу данных не удалось:
ОШИБКА: нет прав на создание базы данных
Деректер қорын құруға барлық пайдаланушыларға рұқсат етілмеген. Егер PostgreSQL деректер қорын құрудан бас тартса, пайдаланушыға қолайлы рұқсат керек. Осы жағдайда администраторға жолығу керек. Егер PostgreSQL пайдаланушы өзі орнатқан жағдайда деректер қоры серверін іске қосатын жүйеге өз атымен кіру керек.
Бұл жағдайды былай түсіндіруге болады: PostgreSQL пайдаланушылар тіркеу жазбасы операциялық жүйе тіркеу жазбасынан өзгеше. Деректер қорына қосылу кезінде қандай пайдаланушы атымен PostgreSQL қосылуға болатынын көрсетуге болады. Әдепкі бойынша пайдаланушының операциялық жүйеде тіркелген аты қолданады. Осылайша PostgreSQL-де әрдайым серверді іске қосатын жүйелік пайдаланушының атымен сәйкес келетін тіркеу жазбасы бар. Және осы пайдаланушы атымен PostgreSQL қосылу үшін дәл осы атпен жүйеге кіру міндетті емес, тек оны - U параметрімен беру жеткілікті.
Сондай-ақ басқа есімдермен деректер қорын құруға болады. PostgreSQL кез келген мөлшерде деректер қорын құуға мүмкіндік береді. Деректер қорының аты әріптен басталу керек және 63 таңбадан аспауы тиіс. Деректер қорының аты ретінде пайдаланушының атын қолданған ыңғайлы.
Көптеген утилитаралар осылардың бәрін әдепкі бойынша болжайды, сондықтан командаларды енгізуді жеңілдетуге болады. Осы атпен деректер қорын құру үшін келесі команданы енгізу керек:
$createdb.
Егер деректер қоры қолданыстан шықса, оны жоюға болады:
$dropdb mydb.
(Бұл команда деректер қорының атын ағымдағы пайдаланушының аты деп санамайды, оны анық көрсету керек.) Нәтижесінде физикалық түрде қорға қатысты барлық файлдар жойылады, және осы амалды тоқтатуға болмағандықтан, салдары туралы ойланбай жүзеге асыруға болмайды.
0.4 Деректер қорына қосылу
Деректер қорын құрғаннан кейін , оған қосылуға болады:
oo Psql атты PostgreSQL терминалды бағдарламасын іске қосу; бағдарламада интерактивті SQL командаларын енгізу, өзгерту және орындауға болады.
oo Қолданыстағы графикалық құралдарды, мысалы pgAdmin немесе ODBC немесе JDBC қолдауы бар кеңсе пакеті, қолдану; олар деректер қорын құру мен басқаруға мүмкіндік береді.
oo Көптеген қолжетімді интерфейс тілдерін қолданып жеке қосымша жазу.
Psql көмегімен mydb деректер қорына қосылу үшін төмендегі команданы теру керек:
$psql mydb
Егер деректер қорының атын көрсетпеген жағдайда пайдаланушының аты таңдалады.
Psql-де келесі хабарлама шығады:
psql (9.5.5)
Type "help" for help.
mydb=
Соңғы жол өзгеше болуы мүмкін:
mydb=#
Бұл көрініс пайдаланушының суперпайдаланушы екенін көрсетеді, демек PostgreSQL данасын өзі орнатқан. Осы жағдайда рұқсат ету шараларының ешқайсысы пайдаланушыға таратылмайды, бірақ мәліметтер енгізу мақсатына маңыздысы жоқ.
createdb және psql командалары серверге бірдей қосылады, сондықтан бірінші команда жұмыс істесе, екіншісі де жұмыс істеуі керек. psql енгізу кезіндегі соңғы жол бұл psql пайдаланушының командаларын күтіп тұрғаны және SQL-сұраныстарын psql жұмыс ортасына енгізуге болады деген шақыру.
Мысалы:
mydb=SELECT version();
version
----------------------------------- ----------------------------------- -
PostgreSQL 9.5.5 on i586-pc-linux-gnu, compiled by GCC 2.96, 32-bit
Начало
5
(1 row)
mydb= SELECT current_date;
date
------------
2002-08-31
(1 row)
mydb= SELECT 2 + 2;
?column?
----------
4
(1 row)
Psql бағдарламасында SQL-операторлары болып табылмайтын көптеген ішкі командалары бар. Олар қиғаш сызықтан басталады, \. Мысалы, PostgreSQL SQL-командам туралы анықтама алуға болады, келесі командына енгізу арқылы:
mydb=\h
psql шығу үшін келесі команда:
mydb=\q
және psql өзінің жұмысын тоқтатады, ал пайдаланушы операциялық жүйенің командалық қабықшасына қайта келеді (ішкі командалар туралы білу үшін psql команда жолына \? енгізу керек).
3 SQL тілі
0.1 Кіріспе
PostgreSQL тілінің мүмкіндіктерінің кейбіреуі стандарт кеңейтілуі бола тұра, SQL туралы, melt93 және date97 қосқанда, көптеген кітаптар жазылған. Келесі мысалдарда mydb деректер қоры құрылып және psql іске қосылды деп есептейміз. Басқа мысалдар PostgreSQL каталогындағы (srctutorial) бастапқы код пакетінде көруге болады.
(PostgreSQL орындаулы код дистрибутивтары осы файлдарды қоспауы мүмкін.)
Бұл файлдарды қолдану үшін каталогқа өтіп make жүзеге асыру керек:
$cd ... srctutorial$make
Осы амал орындалған кезде пайдаланушы типі мен функциялары бар С модулі құрастырылып және скриптар құрылады. Содан кейін жұмысты бастау үшін келесі команданы орындау керек:
$cd ... tutorial$psql -s mydb...mydb=\i basics.sql
\i командасы берілген файлдан командаларды оқыйды және орындайды. Psql берілген - s параметрі серверге әрбір оператор жібергеннен кейін кідірген кезде оны қадамдық режимге ауыстырады. Қолданған командалар basics.sql файлда орналасқан.
0.2 Негізгі түсініктер
PostgreSQL - деректер қорын басқарудың реляционды жүйесі (ДҚБРЖ). Бұл деректер қорын басқару жүйесі қарым-қатынастар ретінде (relation) берілгенін білдіреді. Қарым-қатынас кестенің математикалық анық белгілеуі. Мәліметтерді кестеде сақтау бүгінгі таңда кең тараған, деректер қорын ұйымдастырудың көптеген тәсілдері барына қарамастан бұл айқын шешім сияқты. Мысалы, Unix сияқты операциялық жүйелерде файлдар мен каталогтар иерархиялық деректер қорын құрады, ал қазіргі таңда объектілі-бағытталған деректер қоры белсенді дамуда. Кез келген кесте атаулы қатарлардан тұрады. Кестенің барлық қатарлары бірдей атаулы бағандары бар, тағы әрбір қатарға белгілі бір мәліметтер типін тағайындалады. Бағандар реті қатарда тұрақталғанымен, SQL кестеде қатардардың ретін сақталуына кепілдік жасамайтынын есте сақтау керек (алайда оларды енгізу кезінде сұрыптауға болады).
Кестелер деректер қорында бірлеседі, ал PostgreSQL сервер данасының бірі басқаратын деректер қор жиынтығы деректер қор кластерін құрады.
0.3 Кесте құру
Кестенің атын, бағандарының аты мен типін тізіп кесте құруға болады.
CREATE TABLE weather (
city varchar(70),
temp_lo int, -- тәуліктегі минималды температура
temp_hi int, -- тәуліктегі максималды температура
prcp real, -- жауын-шашын деңгейі
date date
);
Жоғарыдағы барлық мәтінді қатарды таңба ауыстырумен psql енгізуге болады. Psql команда ; жеткенге дейін жалғасатынын түсінеді.
SQL командаларында бос орын таңбаларын (бос орын, табуляция және жол аудармалары) еркін қолдануға болады. Бұл пайдаланушы команданы басқаша туралап немесе тіпті бір қатарда орналастырып енгізуге болатынын білдеріді. Екі минус (--) түсіндірме басталғанын білдіреді. Осы таңбадан кейін жазылғанның бәрі ескерілмейді. SQL кілтті сөздер мен идентификаторлар кезінде регистрге сезімтал емес, бірақ ерекшелік ретінде бағамға алынған идентификаторлар алынады.
varchar(70) 70 таңбаға жететін ұзындықты қарапайым таңбалық қатарды сақтайтын мәліметтер типін анықтайды.
int - қарапайым бүкілсандық тип. real - өзгермелі нүктесі бар бір дәлдікті сандар сақтауға арналған тип. date - мезгіл типі. (Ия, date типті қатар date деп аталады. Бұл ыңғаайлы болуы мүмкін немесе адастыруы да шамалы - қалай қарағанға байланысты.)
PostgreSQL SQL стандартты типтерін қолдайды: int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp и interval, және де басқа да әмбебап типтер мен геометриялық типтердің бай жиынтығы. Сонымен қатар, өздігінен жеке мәлімет типтер жиынтығын құрып PostgreSQL бағдарламасын кеңейтуге болады. Салдар ретінде, типтер атауы кілттік сөздер болып табылмайды, алайда ерекшелік ретінде арнаулы SQL стандарт құрылымын іске асыруға талап етілген жағдайды атауға болады. Келесі кесте мысалында қала және олардың географиялық орналасуы сақталады:
CREATE TABLE cities (
name varchar(80),
location point
);
Бұл жерде point - PostgreSQL ерекше мәліметтер типінің типі. Соңында, қандай да бір кесте керек емес немесе басқаша қайта құру керек болған жағдайда, кестені келесі команданы қолданып жоюға болады:
DROP TABLE кесте_аты;
0.4 Кестеге қатар қосу
Кестеге қатар қосу үшін INSERT операторы қолданылады:
INSERT INTO weather VALUES ('Astana, 46, 50, 0.25, 2017-03-27');
Айта кететін нәрсе, барлық мәліметтер типіне шамалы анық формат қолданылып тұр. Қарапайым сандық белгілерден басқа константалар, осы мысал сияқты, әдетте апострофқа (') алынады. date типі шынында өте икемді және әр түрлі форматты алады. Point типі координат жұбын енгізуді талап етеді, мысалы осылай:
INSERT INTO cities VALUES ('Astana', '(-194.0, 53.0)');
Көрсетілген синтаксис қатар ретін есте сақтауды талап етеді. Сондай-ақ қатарларды анық тізіп балама жазбаны қолдануға болады:
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('Astana', 43, 57, 0.0, 2016-11-29');
Егер кейбір қатарларды қалдыру керек болса басқа ретпен тізуге болады, мысалы:
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES (2016-11-29', 'Hayward', 54, 37);
Көптеген әзірлеушілер кестедегі ретіне сену орнына кесте қатарларын анық тізгенді қалайды.
Пайдаланушы үлкен мәліметтер көлемін қарапайым мәтіндік файлдардан көшіре алады; ол амал үшін COPY командасы қолданылады. Әдетте осы тәсіл жылдамырақ, өйткені COPY командасы осындай амалға оңтайландырылған, алайда INSERT салыстырғанда икемі аздау. Мысалы: оның мына түрде қолдануға болады:
COPY weather FROM 'homeuserweather.txt';
Бұл жерде көрсетілген файл клиенттік емес, серверлік процесс жүріп жатқан компьютерде қолжетімді екені тұспалданады. Содан бері көрсетілген файл тікелей серверде оқылады.
0.5 Сұраныс орындау
Кестедегі мәліметтерді алу үшін сұраныс орындау қажет. Ол үшін SELECT SQL-операторы арналған. Оператор бірнеше бөлшектерден тұрады: таңдамалар (алынатын қатарлар тізімі жазылады), кестелер тізімі (бұнда мәліметтер алынатын кестелер тізімі жазылады) және міндетті емес шарттар (анықтайтын шектеулер). Мысалы, weather кестесіндегі барлық қатарды алу үшін келесе жолды енгізу керек:
SELECT * FROM weather;
Бұл жерде * барлық қатар дегеннің қысқаша белгілеуі. Осылайша ол төмендегі жазумен тепе-тең:
SELECT city, temp_lo, temp_hi, prcp, date
FROM weather;
Нәтижесінде келесі жазба шығу керек:
city temp_lo temp_hi prcp date
---------------+---------+--------- +------+------------
San Francisco 46 50 0.25 1994-11-27
San Francisco 43 57 0 1994-11-29
Hayward 37 54 1994-11-29
(3 rows)
Таңдамада қатарларға сілтемеден басқа айнымалыларды жазуға болады. Мысалы:
SELECT city, (temp_hi+temp_lo)2 AS temp_avg, date
FROM weather;
Нәтижесі:
city temp_avg date
---------------+----------+-------- ----
San Francisco 48 1994-11-27
San Francisco 50 1994-11-29
Hayward 45 1994-11-29
(3 rows)
AS сөйлемінің шығыс қатарының атын өзгертуге болатынына назар аудару керек. (AS сөзін жазу мақсат емес.)
Қайтаратын қатар санын шектейтін WHERE сөйлемін қосу арқылы сұранысты шартпен толықтыруға болады. WHERE сөйлемінде қатар фильтрі ретінде жұмыс атқаратын логикалық өрнек (шындыққа тексеріс) көрсетіледі: нәтижесіде өрнегі шындық болған қатарлар ғана қалады. Бұл өрнекте әдеттегі логикалық операторлар (AND, OR және NOT) болуы мүмкін. Мысалы, келесі сұраныс Сан-Фрасциско қаласында жауын күндері қандай ауа-райы болғанын көрсетеді:
SELECT * FROM weather
WHERE city = 'San Francisco' AND prcp 0.0;
Нәтижесі:
city temp_lo temp_hi prcp date
---------------+---------+--------- +------+------------
San Francisco 46 50 0.25 1994-11-27
(1 row)
SELECT * жиі дайындалусыз, тосын жазады, алайда өндірістік кодта жаман стиль ретінде қабылданады, өйткені осындай сұраныстар нәтижесі жаңа қатар қосқанда ауысатын болады.
Сұранысты белгілі ретпен алуға болады:
SELECT * FROM weather
ORDER BY city;
Нәтижесінде көрсетіледі:
city temp_lo temp_hi prcp date
---------------+---------+--------- +------+------------
Hayward 37 54 1994-11-29
San Francisco 43 57 0 1994-11-29
San Francisco 46 50 0.25 1994-11-27
Бұл мысалда сорттау реті толық анықталмаған, сондықтан пайдаланушы Сан-Франциско қатарын кез келген ретпен алуы мүмкін. Бірақ жоғарыдағы нәтижені нақты алу үшін келесі команданы теру керек:
SELECT * FROM weather
ORDER BY city, temp_lo;
Қажет болса көшірме қатарларды сұраныстан алып тастауға болады:
SELECT DISTINCT city
FROM weather;
Алынған нәтиже:
city
---------------
Hayward
San Francisco
(2 rows)
Осы жерде де қатарлар реті өзгертіле алады. Өзгеріссіз нәтижелер алу үшін DISTINCT және ORDER BY сөйлемдерін қосу керек:
SELECT DISTINCT city
FROM weather
ORDER BY city;
0.6 Кестелерді қосу
Осыған дейін барлық тапсырыстар тек бір кестеге қатынасты. Алайда сұраныстар бір уақытта бірнеше кестелерге қатынай алады немесе сол кестеге әртүрлі қатарлар жиынтығын өңдейтіндей етіп қатынауға болады. Бір кестенің әртүрлі қатар жиынына немесе бірнеше кестелерге бір уақытта қатынайтын сұраныс қосылым (JOIN) деп аталады. Мысалы, пайдаланушы барлық қалалардың ауа-рай жағдайы мен координаталарын шығарғысы келді. Ол үшін weather кестесінің әр қатарындағы city бағанын cities кестесінің барлық қатарындағы салыстыру name бағанымен салыстыру керек және осы мағыналар сәйкескен қатарларды таңдау керек.
Айта кететін жайт, бұл нақты модель емес. Әдетте қосылымдар тиімдірек орындалады (мүмкін жұп қатарлардың бәрі салыстырылмайды), бірақ бұның бәрі пайдаланушыдан жасарын түрде өтеді. Оны келесі сұраныс арқылы жүзеге асырса болады:
SELECT *
FROM weather, cities
WHERE city = name;
Алынған нәтиже:
city temp_lotemp_hi prcp date name location
--------------+-------+-------+---- -+-----------+--------------+------ ----
San Francisco 46 50 0.25 1994-11-27 San Francisco (-194,53)
San Francisco 43 57 0 1994-11-29 San Francisco (-194,53)
(2 rows)
Мәліметтерді алудың екі ерекшелігіне назау аудару керек. PostgreSQL ескі нұсқаларын қосқанда кейбір ДҚБЖ-де DISTINCT қосымшасын іске асыру қатарларды автоматты түрде реттейді, сондықтан ORDER BY қосу маңызды емес. Бірақ SQL стандарты оны талап етпейді және PostgreSQL ағымдағы нұсқасы DISTINCT кейін қатарлардың анық ретіне кепілдік бермейді.
Нәтижесінде Хейуорд (Hayward) қаласымен қатар жоқ. Оның себебі cities кестесінде бұл қалаға мәліметтер қатары жоқ, ал қосылым кезінде weather кестесінің сәйкесін таппаған барлық қатарлары түсіріледі.
Қала атауы екі бағанда болып қалды. Бұл дұрыс және түсіндірілуі: weather және cities кестелерінің қатарлары қосылғанын көрсетеді. Алайда практикада жағымсыз, сондықтан * қолданғаннан, ең жақсысы керек қатарларды айқын тізіп жазған дұрыс:
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
Негізі жағымды стиль ретінде қосылым сұранысында бағандардың толық атын жазған саналады, өйткені кейінірек кестеге қайталанатын қатарлар қосылуы мүмкін жағдайда сұраныс сынбас үшін жазады.
0.7 Агрегаттық функциялар
Көптеген реляционды деректер қоры серверлері сияқты PostgreSQL агрегаттық фунцияларды қолдайды. Агрегаттық функциялар көптеген қатарларды өңдей отырып, тек бір мағынаны есептейді. Мысалы, есептейтін агрегаттық функциялар бар: count (мөлшері), sum (суммасы), avg (арифметикалық орта), max (максимум) және min (минимум) қатар жиынығы үшін. Мысалыға, барлық көріністер ішінде ең жоғарғы температураны табуға болады:
SELECT max(temp_lo) FROM weather;
max
-----
46
(1 row)
Егер кай қалада (немесе қалаларда) осы температура бақыланғанын білу керек болса, оны шығаруға болады:
SELECT city FROM weather
WHERE temp_lo = max(temp_lo); НЕВЕРНО
Алайда бұл амал іске аспайды, өйткені max агрегаттық функциясын
WHERE сөйлемінде қолдануға болмайды. (Бұл шектеу WHERE сөйлемі қай қатар үшін агрегаттық фунцияны есептеу керектігін анықтайды, сол себептен ол, демек, агрегаттық фунцияға дейін есептелуі керек.) Бірақ, әдеттегідей, сұранысты қайта іске қосуға керек және керекті нәтиже алу үшін сұраныс ішіндегі сұранысты қолдану керек:
SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
city
---------------
San Francisco
(1 row)
Енді бәрі орнында сұраныс ішіндегі сұраныс жеке орындалады және агрегаттық функция нәтижесі сыртқы сұраныста болып жатқан амалдардан тәуелсіз есептеледі.
Агрегаттық фунциялар GROUP BY сөйлемімен комбинациясымен өте пайдалы. Мысалы, қалалардың арасындағы тәуліктік максимум температураны алуға болады:
SELECT city, max(temp_lo)
FROM weather
GROUP BY city;
Язык SQL
12
city max
---------------+-----
Hayward 37
San Francisco 46
(2 rows)
Бұл жерде әр қала үшін бір-бір қатар алынды. Әрбір агрегаттық нәтиже әр қалаға тиесілі кесте қатарлары арқылы есептеледі. Біріктірілген қатарларды HAVING сөйлемі арқылы жиынтықтай аламыз:
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING max(temp_lo) 40;
city max
---------+-----
Hayward 37
(1 row)
Дәл сол нәтижелер алынды, бірақ та temp_lo барлық мағыналары 40 кем қалалар үшін.
Соңында, егер тек S басталатын қалалар қызығушылық тудырса, оны іске асыруға болады:
SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE 'S%' 1
GROUP BY city
HAVING max(temp_lo) 40;
LIKE операторы шаблон бойынша салыстыруды орындайды.
Агрегаттық функциялар мен WHERE және HAVING SQL-сөйлемдері қалай қандай арақатынаста болатынын түсінудің маңызы зор. WHERE және HAVING негізгі артықшылығы: WHERE алдымен қатарларды таңдайды, содан кейін оларды жиынтықтап және олардың агрегаттық фунцияларын есептейді, осылайша агрегатты есептеу үшін қатарларды таңдайды. Ал HAVING агрегаттық фунцияларды есептеп жинағаннан кейін ғана қатарлар жиынтығын таңдайды. Нәтижесінде WHERE сөйлемінде агрегаттық функция болмауы керек; агрегаттық функцияларды есептеу үшін қатарларды анықтауға агрегаттық функцияларды қолданудың мағынасы жоқ. HAVING сөйлемінде, керісінше, әрдайым агрегаттық фунциялар бар. (Қатаң сөйлегенде, HAVING сөйлемін агрегат қолданбай жазса болады, бірақ оның пайдалылығы біршама. Дәл осы шарт WHERE кезеңінде тиімдірек жұмыс істеуі мүмкін.)
Жоғарыдағы мысалда қолданған фильтр HAVING қосымша шектеуіне қарағанда тиімдірек, өйткені онымен WHERE шартын қанағаттандырмайтын барлық қатар үшін агрегаттарды есептеу және жиынтықтау керек емес.
0.8 Мәліметтерді өзгерту
Қолданыстағы қатарлардағы мәліметтерді UPDATE командасын қолданып өзгертуге болады. Мысалыға, пайдаланушы 28 қарашадан кейін барлық температура көрсеткіштері екі градусқа көтерілгенін байқады. Келесі жолмен мәліметтерді түзеуге болады:
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date '1994-11-28';
Мәліметтердің жаңа күйі:
SELECT * FROM weather;
city temp_lo temp_hi prcp date
Язык SQL
13
---------------+---------+--------- +------+------------
San Francisco 46 50 0.25 1994-11-27
San Francisco 41 55 0 1994-11-29
Hayward 35 52 1994-11-29
(3 rows)
0.9 Мәліметтерді жою
Қатарларды кестеден DELETE командасы көмегімен жоюға болады. Мысалы, Хейуор қаласының бұдан былай ауа-райы қызығушылық тудырмайды. Оыс жағдайда керек емес қатарды кестеден жоюға болады:
DELETE FROM weather WHERE city = 'Hayward';
Хейуор қаласына қатысты барлық жазбалар жойылған:
SELECT * FROM weather;
city temp_lo temp_hi prcp date
---------------+---------+--------- +------+------------
San Francisco 46 50 0.25 1994-11-27
San Francisco 41 55 0 1994-11-29
(2 rows)
DELETE FROM кесте_аты;
түрдегі операторлардан сақтану керек. Шарт көрсетілмесе DELETE кестедегі барлық қатарларды жояды, оны толық тазалайды. Сонымен қатар жүйе операцияны растауды сұрамайды.
4 Кеңейтілген мүмкіншіліктер
4.1 Көріністер
Пайдаланушыға қалалардың координаталары мен ауарайы жазбаларының тізімі керек делік, алайда ол әр кезде сұранысты жаза бергісі келмейді. Осы сұраныс бойынша көрініс жасауға болады, дәлірек айтқанда, сұранысқа ат беру. Содан кейін оған қарапайым кестеге сияқты байланысу:
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;
Көрінсітердің белсенді қолдануы SQL деректер қорын жақсы жобалаудың негізгі аспектісі. Көріністер кестелердің ішкі құрылымын жасыруға мүмкіндік беред; құрылымдар қосымшалар даму және сенімді интерфейстермен өзгеруі мүмкін.
Көріністер кестелер қолданған барлық жерде дерлік қолдануға болады. Көріністер жиі басқа жеке көріністер қорында жасалынады.
4.2 Ішкі кілттер
Мысалдағы weather және cities кестелері қайта қарастырылады. Келесі тапсырманы қарастыру керек: cities кестесіндегі қатарлармен сәйкеспейтін weather кестесіне ешкім қатар қоса алмайтындай етіп жасау керек. Бұл мәліметтердің сілтемелік тұтастығын қамту деп аталады. Қарапайым ДҚБЖ мына жолмен іске асыруы керек еді (мүмкін болған жағдайда ғана) :
бастапқыда cities кестесінде сәйкес жазбалар баржоғын тексеру керек, содан кейін жаңа жазбаны weather кестесіне қосады немесе қабылдайды. Бұл өте проблемалық тәсіл және ыңғайлы емес, сол себептен PostgreSQL осының бәрін пайдаланушы орнына жасай алады.
Кестенің жаңа хабарлануы келесі түрде болады:
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
Енді мысал ретінде қолайыз жазба енгізіледі:
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
ОШИБКА: INSERT или UPDATE в таблице "weather" нарушает ограничение внешнего
ключа "weather_city_fkey"
ПОДРОБНОСТИ: Ключ (city)=(Berkeley) отсутствует в таблице "cities".
Пайдаланушы қосымшасының талабы бойынша ішкі кілттердің мінезқұлқын орналастыруға болады.
4.3 Транзакциялар
Транзакциялар бұл барлық ДҚБЖ негізгі іргетасы. Транзакциялар мағынасы әсерлер тізімін бір барлығы немесе ештеңе операциясына біріктіргенінде. Тізім ішіндегі интервал жағдайлары басқа транзакцияларға көрінбейді, және де егер транзакцияны сәтті аяқтауға бір нәрсе бөгет болған жағдайда, осы амалдардың бірдебіреуі деректер қорында сақталмайды. Мысалы, клиент шоттары және банк филиалдарының ортақ сомасы туралы мәлімет бар банктің деректер қоры қарастырылады. Алиса шотынан Боб шотына 100 доллар ауыстыру керек. Жеңілдік үшін сәйкес SQLкомандаларын келесі түрде жазуға болады:
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
Командалардың нақты мазмұны мазмұны маңызды емес; маңыздысы тек осы қарапайым операцияны орындауға бірнеше жеке операция орындау қажет болғаны. Сонымен қатар банк тұрғысынан қарағанда осы амалдардың бәрі бірге орындалғаны қажет, немесе мүлдем орындалмауы тиіс. Егер Боб 100 доллар алса, бірақ Алисаның шотынан алынбаса, бұны жүйенің қателігі деп айтуға болмайды. Және, керісінше, Алиса ақша аударып Бобқа жетпеген жағдай да жақсы деп атауға келмейді. Операцияны соңына дейін орындауға бір нәрсе бөгет болған жағдайда, бір де бір амал деректер қорында ізін қалдырмайды деген кепілдік керек. Ондай кепілдікті амалдарды бір транзакцияға бірітіру жолымен алады. Транзакция атомарлық дейді басқа транзакциялар тұрғысынан ол орындалып толық бекітіледі немесе мүлдем бекітілмейді. Сонымен қатар кепілдік керек: аяқталғаннан кейін және деректер қоры жүйесімен транзакция расталғаннан кейін, оның нәтижелері шынында сақталады және апат болған жағдайдың өзінде жоғалмайды. Мысалы, егер сомасы есептен шығарылды және Бобқа берілді; ол банктен шыққан сәтте шотындаңы сомасы қайта орнына келу мүмкіндігін болдырмау керек. Транзакциондық деректер қоры барлық өзгертулер транзакция аяқталғанша тұрақты қоймада (мысалы, дискіге) сақаталады деген кепілдік береді. Транзакциялық деректер қорының басқа маңызды сипаттамасы атомарлық өзгертулермен тығыз байланысты: бір уақытта бірнеше транзакциялар орындалған сәтте олардың әрқайсысы басқалары орындайтын аяқталмаған өзгертулерді көрмейді. Мысалы, егер бір транзакция бөлім бойынша балансты санап жатқан уақытта, егер Боб бөлімін келуді қарастырмай, Алисаның бөліміндегі шығынды санаса дұрыс емес болады. Сол себептен транзакция барлығы немесе ештеңе қасиеті өзгертулер деректер қорында қалай сақталатыны ғана емес, сонымен қатар жұмыс процесінде қалай көрінетінін анықтау керек. Ашық транзакция өндіретін өзгертулер операция аяқьалғанша басқа транзакцияларға көрінбейді, одан кейін олардың барлығы бәріне бірден көрінеді. PostgreSQL транзакциялар SQLкомандалар жиынтығымен анықталады, BEGIN және
COMMIT командаларымен қоршалған. Осылайша, банк транзакциясы келесі түрде болуы керек еді:
BEGIN;
Расширенные возможности
16
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- ...
COMMIT;
Егер транзакция орындау процесінде олардың өзгертулерін бекітпеу шешімі қабылданса (мысалы, Алиса балансы минус болса), COMMIT орнына ROLLBACK командасын орындауға болады, және барлық өзгертулер күшін жояды.
PostgreSQL шынында барлық SQLоператорды транзакция ретінде өңдейді. Егер пайдаланушы BEGIN командасын қоймаса, онда әрбір жеке оператор айқын емес BEGIN және COMMIT командаларымен қоршалаып қалады (сәтті аяқталу жағдайында). BEGIN және COMMIT командаларымен қоршалатын операторлар тобын кейде транзакция блогы деп атайды. Кейбір клиенттік кітапханалар BEGIN және COMMIT командаларын автоматты түрде қосады және пайдаланушы орнына айқын емес транзакция блоктарын жасайды. Транзакциялардағы операторлармен сақтау нүктелерін қолдана отырып егжейтегжейлі басқаруға болады. Сақтау нүктелері таңдаулы түрде транзакцияның кейбір бөліктерінің күшін жойдыруға және қалғандарын бекітуге мүмкіндік береді. Сақтау нүктесін SAVEPOINT көмегімен анықтап, керек болса оған ROLLBACK TO командасы көмегі арқылы қайта оралуға болады. Сақтау нүктесінен қайтарымға дейінгі деректер қорында болған барлық өзгерістер күшін жояды, бірақ алдында болған өзгерістер сақталады. Пайдаланушы сақтау нүктесіне қайта келгенде нүкте жұмысын жалғастырады; оған бірнеше рет қайтарым орындай аласыз. Басқа жағынан қарағанда, пайдаланушы белгілі бір сақтау нүктесіне қайта оралмайтынына сенімді болса, онда жүйе қорын босату үшін нүктені жоюға болады. Еске сақтайтын нәрсе: жоюда немесе сақтау нүктесіне қайтарымда одан кейін анықталған барлық сақтау нүктелері автоматты түрде жойылады. Осының бәрі транзакция блогында жұмыс жасайды, сол себептен басқа деректер қорымен жұмыс сеанстарында ол көрінбейді. Барлық жасалған амалдар барлығы бірден көріне бастайды тек транзакцияны бекіткен жағдайда, ал күшін жойған амалдар ешқашан көрінбейді. Банк деректер қорына қайтадан келіп, Алиса шотынан 100 доллар сомасын есептен шығарып, Боб шотына қосылады деп болжанады. Және кенеттен сомасы Уоллиға ауыстыру керек болып шықты. Осы жағдайда сақтау нүктесін қолдануға болады:
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
-- қате амал... Уолли шотын қолдану
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;
Бұл мысал, әрине, ойдан шығарылған, алайда ол сақтау нүктелерін қолдана отырып транзакция блогында командалар орындалуын қалай басқару керектігін көрсетеді. Cонымен қатар ROLLBACK TO - бұл, толық күшін жойып және қайта бастауды санамағанда, жүйе қателігі себебінен үзілген жағдайда болып қалған транзакция блогының бақылауын қайтаруының жалғыз тәсілі.
4.4 Терезелік функциялар
Терезелік функция қатарлар жиынтығын үшін есептеулер орындайды, кейбір түрде ағымдағы қатармен байланысты. Агрегаттық фунциямен салыстыруға болады, алайда әдіттегі агрегаттық фунциямен қарағанда, терезелік функцияны қолданғанда бірнеше қатарлар бір қатаға жинақталмайды, олар жеке тұра береді. Ал ішінде қараса, терезелік функция,агрегаттыө сияқты, тек сұраныс нәтижесінің ағымдағы қатарына ғана қатынасып қоймайды. Мысалыға, бөлімдегі әрбір орташа жалақылы жұмысшыларының жалақыларынан салыстыру:
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname)
FROM empsalary;
depname empno salary avg
-----------+-------+--------+------ -----------------
develop 11 5200 5020.0000000000000000
develop 7 4200 5020.0000000000000000
develop 9 4500 5020.0000000000000000
develop 8 6000 5020.0000000000000000
develop 10 5200 5020.0000000000000000
personnel 5 3500 3700.0000000000000000
personnel 2 3900 3700.0000000000000000
sales 3 4800 4866.6666666666666667
sales 1 5000 4866.6666666666666667
sales 4 4800 4866.6666666666666667
(10 rows)
Алғашқы үш баған тура empsalary кестесіден алынады, сонымен қатар кестенің әрбір қатары үшін нәтиже қатары бар. Төртінші бағанда, ағымдағы қатар секіліді depname мағынасы бар, барлық қатардан есептелген орташасы орналасқан. Орташаны есептеуді негізі, агрегаттық функция деп білетін, avg функциясы орындайды, бірақ OVER сөйлемі оны терезелік функцияға айналдырады, өйткені ол тек берілген қатарлар жиынтығын өңдейді.
Терезелік функцияны шақыру әрдайым аты мен терезелік фунцияның аргументтерінен кейін OVER сөйлемін қосады. OVER сөйлемі терезелік функция арқылы өңдеуге сұраныс үшін қатарларды қалай бөлу керектігін анықтайды. OVER сөйлемін толықтыратын PARTITION BY сөйлемі көрсетеді: қатарларды топтар немесе бөлімдер бойынша бөлу және бірдей мағыналарды PARTITION BY өрнегіне біріктіру керектігін. Терезелік функция ағымдағы қатармен бір бөлімге түскен қатарлар арқылы есептеледі. Сонымен қатар пайдаланушы OVER ішінде ORDER BY қолдану арқылы терезелік функциямен қатарлар қай ретте өңделетінін анықтай алады. Терезе үшін ORDER BY реті қатарлар көрсетілген ретпен сәйкес келмеуі мүмкін.
Мысалы:
SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
depname empno salary rank
-----------+-------+--------+------
develop 8 6000 1
develop 10 5200 2
develop 11 5200 2
develop 9 4500 4
develop 7 4200 5
personnel 2 3900 ... жалғасы
Сіз бұл жұмысты біздің қосымшамыз арқылы толығымен тегін көре аласыз.
Ұқсас жұмыстар
Пәндер
- Іс жүргізу
- Автоматтандыру, Техника
- Алғашқы әскери дайындық
- Астрономия
- Ауыл шаруашылығы
- Банк ісі
- Бизнесті бағалау
- Биология
- Бухгалтерлік іс
- Валеология
- Ветеринария
- География
- Геология, Геофизика, Геодезия
- Дін
- Ет, сүт, шарап өнімдері
- Жалпы тарих
- Жер кадастрі, Жылжымайтын мүлік
- Журналистика
- Информатика
- Кеден ісі
- Маркетинг
- Математика, Геометрия
- Медицина
- Мемлекеттік басқару
- Менеджмент
- Мұнай, Газ
- Мұрағат ісі
- Мәдениеттану
- ОБЖ (Основы безопасности жизнедеятельности)
- Педагогика
- Полиграфия
- Психология
- Салық
- Саясаттану
- Сақтандыру
- Сертификаттау, стандарттау
- Социология, Демография
- Спорт
- Статистика
- Тілтану, Филология
- Тарихи тұлғалар
- Тау-кен ісі
- Транспорт
- Туризм
- Физика
- Философия
- Халықаралық қатынастар
- Химия
- Экология, Қоршаған ортаны қорғау
- Экономика
- Экономикалық география
- Электротехника
- Қазақстан тарихы
- Қаржы
- Құрылыс
- Құқық, Криминалистика
- Әдебиет
- Өнер, музыка
- Өнеркәсіп, Өндіріс
Қазақ тілінде жазылған рефераттар, курстық жұмыстар, дипломдық жұмыстар бойынша біздің қор #1 болып табылады.
Ақпарат
Қосымша
Email: info@stud.kz