MySQL esasy maglumat bazasynyň dolandyryş buýruklary - I bölüm
Maglumat bazasy elektron görnüşinde saklanýan maglumatlaryň düzülen toplumydyr. Maglumatlar bazasy düşünjesi, kompýuter ýok wagtynda-da ata-babalarymyza mälimdi, ýöne şeýle maglumatlar binýadyny döretmek we goldamak gaty ýadaw işdi. Gollanma maglumatlar bazasynda 100 sahypa diýiň, aýlygy 10k-dan pes bolan ähli işgärleri gözlemeli bolsaňyz, munuň nähili kyn bolandygyny pikir ediň.
Häzirki zaman dünýäsinde maglumat bazasyndan gaçyp bilmersiňiz. Häzirki wagtda dünýäde millionlarça maglumat bazasy strategiki maglumatlar, işgärleriň ýazgylary ýa-da web tehnologiýalary bolsun, her dürli maglumatlary saklamak we almak üçin işleýär.
Maglumat bazasyna köplenç ahyrky proses diýilýär, sebäbi ahyrky ulanyjy üçin görünmeýär ýa-da ahyrky ulanyjy maglumatlar bazasy bilen göni aragatnaşyk saklamaýar. PHP, VB, ASP.NET we ş.m. öňdäki işleriň üstünde işleýärler we yzky böleginde maglumatlar bazasy bilen iş salyşmagyny haýyş edýärler.
Oracle, MySQL, MySQLi, MongoDB we ş.m. ýaly birnäçe maglumat bazasy serweri we müşderi bar, bularyň hemmesiniň sintaksisi birmeňzeş ýa-da az. Birini özleşdirmek, köpüsine gözegçilik etmegi we maglumat bazasynyň talaplaryny öwrenmek aňsat we gyzykly diýmekdir.
Maglumat bazasyndaky ýönekeý talaplardan başlalyň. Linux paýlaýjylarynyň köpüsi bilen birleşdirilen MySQL-i ulanarys, adaty ýagdaýda gurulmadyk bolsa, ammardan el bilen gurnap bilersiňiz.
Maglumatlar bazasynyň talaplary, talap edilişi ýaly adaty we takyk netije almak üçin maglumatlar bazasyna iberilýän ýönekeý kod bölegi.
MySQL maglumat bazasyny guruň
MySQL maglumat bazasyny gurmak üçin “yum” ýa-da “apt” paket dolandyryjysyny ulanyň.
# yum install mysql mysql-client mysql-server (on Yum based Systems) # apt-get install mysql mysql-client mysql-server (on Apt based Systems)
MySQL maglumat bazasynyň hyzmatyny şu aşakdaky ýaly başlaň:
# service mysqld start or # service mysql start
MySQL maglumat bazasyny gowy gurmak, administrator parolyny we ş.m. talap edilýän konfigurasiýa alyp barar we serweri gurmak we işe başlanyňyzdan soň MySQL haýyşnamaňyza geçiň.
# mysql -u root -p
Köküňizi sazlanan ulanyjy adyňyz bilen çalyşyň we soralanda paroly giriziň, giriş şahsyýetnamasy dogry bolsa, gözüňizi ýumup MySQL soragyňyzda bolarsyňyz.
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 195 Server version: 5.5.31-0+wheezy1 (Debian) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Indi bu haýyşda talaplary ýerine ýetirmek gaty öwrediji we gyzykly.
mysql> create database tecmint ; Query OK, 1 row affected (0.02 sec) mysql>
Bellik: Talabyň dogrudygyny, maglumat bazasynyň döredilendigini habar berýär. Täze döredilen maglumatlar binýadyňyzy barlap bilersiňiz.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | tecmint | | test | +--------------------+ 9 rows in set (0.00 sec) mysql>
Bellik: aboveokardaky çykyşda maglumat bazasyna üns beriň.
Indi üstünde işlemek üçin maglumat bazasyny saýlamaly.
mysql> use tecmint; Database changed mysql>
Bu ýerde üç meýdan ýaly “minttec” diýilýän tablisa dörederis:
mysql> CREATE TABLE minttec (
-> id Int(3),
-> first_name Varchar (15),
-> email Varchar(20)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql>
Bellik: aboveokardaky talap OK diýýär, bu bolsa tablisanyň ýalňyşsyz döredilendigini aňladýar. Tablisany barlamak üçin aşakdaky soragy işlediň.
mysql> show tables; +-------------------+ | Tables_in_tecmint | +-------------------+ | minttec | +-------------------+ 1 row in set (0.00 sec) mysql>
Şu wagta çenli ýagdaý gowy gidýär. Upok! “Minttec” tablisasynda döreden sütünleriňizi aşakdaky ýaly görüp bilersiňiz:
mysql> show columns from minttec; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | first_name | varchar(15) | YES | | NULL | | | email | varchar(20) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>
Bu jadydan pes zat däldi. Her niçigem bolsa, beýannamanyň görnüşleri we manylary barada aýdaryn.
- Int Integer
- Varchar, kesgitlenişi ýaly üýtgeýän uzynlyga eýe. “Type” -dan soňky baha, maglumatlary saklap bilýän meýdanyň uzynlygydyr.
Bolýar, indi birinji_name sütüninden soň soňky_name diýlen sütün goşmaly.
mysql> ALTER TABLE minttec ADD last_name varchar (20) AFTER first_name; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0
Indi ony tablisaňyzda barlaň.
mysql> show columns from minttec; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | first_name | varchar(15) | YES | | NULL | | | last_name | varchar(20) | YES | | NULL | | | email | varchar(20) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql>
Indi sag tarapda e-poçta sagynda “ýurt” sütüni diýeris.
mysql> ALTER TABLE minttec ADD country varchar (15) AFTER email; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>
Aboveokardaky sütün goýmak talapyny barlaň.
mysql> show columns from minttec; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | first_name | varchar(15) | YES | | NULL | | | last_name | varchar(20) | YES | | NULL | | | email | varchar(20) | YES | | NULL | | | country | varchar(15) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql>
Meýdana baha goýmak barada näme aýdyp bilersiňiz?
mysql> INSERT INTO minttec VALUES ('1' , 'Ravi' , 'Saive' , '[email ' , 'India' );
Query OK, 1 row affected (0.02 sec)
mysql>Aboveokardaky tablisada bir gezekde 1-den gowrak bahany nädip girizmeli?
mysql> INSERT INTO minttec VALUES ('2' , 'Narad' , 'Shrestha' , '[email ' , 'India' ), ('3' , 'user' , 'singh' , '[email ' , 'Aus' ), ('4' , 'tecmint' , '[dot]com' , '[email ' , 'India' );
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
Aboveokardaky goýmany barlaň.
mysql> select * from minttec; +------+------------+-----------+-------------------+---------+ | id | first_name | last_name | email | country | +------+------------+-----------+-------------------+---------+ | 1 | Ravi | Saive | [email | India | | 2 | Narad | Shrestha | [email | India | | 3 | user | singh | [email | Aus | | 4 | tecmint | [dot]com | [email | India | +------+------------+-----------+-------------------+---------+ 4 rows in set (0.00 sec) mysql>
Aboveokardaky çykyşdaky üçünji ýazgy nädogry diýeliň we üçünji ýazgyny pozmaly.
mysql> DELETE FROM minttec WHERE id = 3; Query OK, 1 row affected (0.02 sec)
Aboveokardaky amaly barlaň.
mysql> select * from minttec; +------+------------+-----------+-------------------+---------+ | id | first_name | last_name | email | country | +------+------------+-----------+-------------------+---------+ | 1 | Ravi | Saive | [email | India | | 2 | Narad | Shrestha | [email | India | | 4 | tecmint | [dot]com | [email | India | +------+------------+-----------+-------------------+---------+ 3 rows in set (0.00 sec)
ID (= 4) redaktirlemeli.
mysql> UPDATE minttec SET id = 3 WHERE first_name = 'tecmint'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>
Aboveokardaky soragy barlaň.
mysql> UPDATE minttec SET id = 3 WHERE first_name = 'tecmint'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>
Bellik: aboveokardaky talap, ýerine ýetirilişi ýaly gowy pikir däl. ID-ni “4” -e üýtgeder, ilkinji ady “tekmint”. Elmydama minimal ýalňyşlygy almak üçin birden köp sütün ulanmak gowy pikirdir:
mysql> UPDATE minttec SET id = 6 WHERE first_name = 'tecmint'AND last_name = '[dot]com'; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>
Geliň, bu ýerde “ýurt” diýmegiň ähmiýeti ýok öýdýän sütünimizi taşlamaly (pozmaly).
mysql> ALTER TABLE minttec drop country; Query OK, 3 rows affected (0.15 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql>
Tablisany barlaň.
mysql> select * from minttec; +------+------------+-----------+-------------------+ | id | first_name | last_name | email | +------+------------+-----------+-------------------+ | 1 | Ravi | Saive | [email | | 2 | Narad | Shrestha | [email | | 6 | tecmint | [dot]com | [email | +------+------------+-----------+-------------------+ 3 rows in set (0.00 sec) mysql>
Stoluňyzyň ady “minttec” kän bir ähmiýetli däl öýdemok. Tecmint_table-a nädip üýtgetmeli?
mysql> RENAME TABLE minttec TO tecmint_table; Query OK, 0 rows affected (0.03 sec) mysql>
Häzirki maglumat bazasynyň aşagyndaky ähli tablisalara serediň.
mysql> show tables; +-------------------+ | Tables_in_tecmint | +-------------------+ | tecmint_table | +-------------------+ 1 row in set (0.00 sec) mysql>
Tablisanyň ady üýtgedildi. Indi ýokardaky MySQL maglumat bazasynyň ätiýaçlyk nusgasyny alyň, çylşyrymly gural bolmazdan bir buýruk setirinde. Aşakdaky kody mysql soramasynda däl-de, terminalyňyzda işlediň.
# mysqldump -u root -p tecmint > tecmint.sql check the dumped file on your desktop which would have contents something like -- MySQL dump 10.13 Distrib 5.5.31, for debian-linux-gnu (i686) -- -- Server version 5.5.31-0+wheezy1 -- Dump completed on 2013-09-02 12:55:37
MySQL maglumat bazalarynyň ätiýaçlyk nusgasyny saklamak elmydama gowy pikir. Backtiýaçlandyrylan MySQL Maglumatlaryny dikeltmek, mysql soragyňyzda däl-de, terminal islegiňizde işlemeli ýönekeý kod setiridir.
Emma, ilki bilen dikeldişimiziň kämildigini ýa-da ýokdugyny barlamak üçin maglumat bazasyny pozarys.
mysql> drop database tecmint; Query OK, 1 row affected (0.02 sec)
Maglumat bazasynyň serwerindäki “tecmint” maglumat bazasyny barlaň.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | my_database | | mysql | | performance_schema | | phpmyadmin | | sisso | | test | +--------------------+ 7 rows in set (0.00 sec) mysql>
Gowy! Maglumat bazasy ýitdi, ýöne alada etmeli däl, ätiýaçlyk nusgasy bar.
Lostitirilen maglumat bazasyny dikeltmek üçin aşakdaky buýrugy işlediň.
# mysql -u root -p tecmint < tecmint.sql Enter password: ERROR 1049 (42000): Unknown database 'tecmint'
OOPS! Erroralňyşlyk, heý, maglumat bazasynyň tekstini döretmedik. Şonuň üçin mysql haýyşnamaňyza giriň we “tecmint” maglumat bazasyny dörediň.
mysql> create database tecmint; Query OK, 1 row affected (0.00 sec) mysql>
Indi gabyk islegiňizde dikeltmek buýrugyny işletmegiň wagty (berk).
# mysql -u root -p tecmint < tecmint.sql Enter password:
Maglumat bazasyny barlaň.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | tecmint | | test | +--------------------+ 8 rows in set (0.00 sec)
Maglumat bazasynyň mazmunyny barlaň.
mysql> show tables from tecmint; +-------------------+ | Tables_in_tecmint | +-------------------+ | tecmint_table | +-------------------+ 1 row in set (0.00 sec) mysql>
Dikeldilen tablisanyň mazmunyny barlaň.
mysql> select * from tecmint_table; +------+------------+-----------+-------------------+ | id | first_name | last_name | email | +------+------------+-----------+-------------------+ | 1 | Ravi | Saive | [email | | 2 | Narad | Shrestha | [email | | 6 | tecmint | [dot]com | [email | +------+------------+-----------+-------------------+ 3 rows in set (0.00 sec)
Bu hökman ahyrzaman däl, makalanyň indiki bölüminde ýönekeý PHP skriptini ulanyp, esasy açar, daşary ýurt açary, köp tablisa we işleýän talaplar düşünjesini açarys.
Makalanyň üstünden baranyňyzda özüňizi nähili duýandygyňyzy aýtmagy ýatdan çykarmaň. Teswirleriňize ýokary baha berilýär. Sagdyn we sazlaşykly boluň, Tecmint-e birikdiriň.