Amri za Usimamizi wa Hifadhidata ya Msingi ya MySQL - Sehemu ya I
Hifadhidata ni seti iliyoundwa ya data iliyohifadhiwa kielektroniki. Wazo la hifadhidata lilijulikana kwa mababu zetu hata wakati hapakuwa na kompyuta, hata hivyo kuunda na kudumisha hifadhidata kama hiyo ilikuwa kazi ya kuchosha sana. Katika hifadhidata ya mwongozo sema kurasa 100, ikiwa itabidi utafute wafanyikazi wote ambao mshahara wao ulikuwa chini ya 10k, fikiria tu ingekuwa ngumu sana, basi.
Katika ulimwengu wa kisasa huwezi kutoroka Hifadhidata. Hivi sasa mamilioni ya hifadhidata zinafanya kazi kote ulimwenguni kuhifadhi na kuleta data ya kila aina iwe data ya kimkakati, rekodi za wafanyikazi au teknolojia za wavuti.
Hifadhidata mara nyingi huitwa mchakato wa nyuma, kwa sababu haionekani kwa mtumiaji wa mwisho wala Mtumiaji wa Hatima huingiliana moja kwa moja na hifadhidata. Wanafanya kazi kwenye mchakato wa mwisho yaani, PHP, VB, ASP.NET, n.k. na kuuliza sehemu ya mbele kushughulika na hifadhidata iliyo nyuma-mwisho.
Kuna seva nyingi za hifadhidata na mteja zinazopatikana kama vile Oracle, MySQL, MySQLi, MongoDB n.k. Sintaksia ya haya yote ni zaidi au kidogo sawa. Kujua moja kunamaanisha kupata udhibiti kwa wengi wao na kujifunza maswali ya hifadhidata ni rahisi sana na ya kufurahisha.
Wacha tuanze na maswali rahisi kwenye hifadhidata. Tutakuwa tukitumia MySQL ambayo huja pamoja na usambazaji mwingi wa Linux kwa chaguo-msingi, unaweza kuisakinisha wewe mwenyewe kutoka kwa hazina, ikiwa haijasakinishwa kwa chaguo-msingi katika kesi yako.
Sawa swala la hifadhidata ni kipande rahisi cha msimbo ambacho hutumwa kwa hifadhidata ili kupata matokeo maalum na yaliyosafishwa, kama inavyohitajika.
Sakinisha Hifadhidata ya MySQL
Tumia kidhibiti kifurushi cha yum au apt kusakinisha Hifadhidata ya MySQL.
# yum install mysql mysql-client mysql-server (on Yum based Systems) # apt-get install mysql mysql-client mysql-server (on Apt based Systems)
Anzisha huduma ya hifadhidata ya MySQL kama:
# service mysqld start or # service mysql start
Kusakinisha hifadhidata ya MySQL kutakupeleka kwenye usanidi ambapo utaombwa kusanidi nenosiri la msimamizi, n.k. Baada ya kumaliza kusakinisha na kuanzisha seva nenda kwa kidokezo chako cha MySQL.
# mysql -u root -p
Badilisha mzizi na jina lako la mtumiaji lililosanidiwa na uweke nenosiri unapoombwa, ikiwa kitambulisho cha kuingia ni sahihi, utakuwa kwenye dodoso lako la MySQL katika kufumba na kufumbua kwa macho yako.
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>
Sasa kutekeleza maswali kwa haraka hii ni jambo la kuelimisha na la kufurahisha sana.
mysql> create database tecmint ; Query OK, 1 row affected (0.02 sec) mysql>
Kumbuka: Inaripoti kuwa swali lilikuwa sahihi, inamaanisha kuwa hifadhidata imeundwa. Unaweza kuthibitisha hifadhidata yako mpya iliyoundwa kama.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | tecmint | | test | +--------------------+ 9 rows in set (0.00 sec) mysql>
Kumbuka: Angalia hifadhidata yako katika matokeo hapo juu.
Sasa unahitaji kuchagua hifadhidata ili uifanyie kazi.
mysql> use tecmint; Database changed mysql>
Hapa tutakuwa tunaunda jedwali sema minttec na sehemu tatu kama:
mysql> CREATE TABLE minttec (
-> id Int(3),
-> first_name Varchar (15),
-> email Varchar(20)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql>
Kumbuka: Hoja iliyo hapo juu inasema sawa ambayo inamaanisha kuwa jedwali liliundwa bila hitilafu yoyote. Ili kuthibitisha jedwali endesha hoja iliyo hapa chini.
mysql> show tables; +-------------------+ | Tables_in_tecmint | +-------------------+ | minttec | +-------------------+ 1 row in set (0.00 sec) mysql>
Mambo yanaendelea vizuri mpaka sasa. Ndio! Unaweza kuona safu wima ulizounda kwenye jedwali la minttec kama:
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>
Haikuwa kitu kidogo kuliko uchawi. Hata hivyo nitakuambia kuhusu aina za tamko na maana yake.
- Int ni Nambari kamili
- Varchar ni char yenye urefu tofauti kama inavyofafanuliwa. Thamani baada ya Aina ni urefu wa sehemu ambayo inaweza kuhifadhi data.
SAWA sasa tunahitaji kuongeza safu kusema 'jina_la_mwisho' baada ya safu wima 'jina_la_kwanza'.
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
Sasa, ithibitishe kwenye jedwali lako.
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>
Sasa tutaongeza safu upande wa kulia sema safu 'nchi' iliyo upande wa kulia wa barua pepe.
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>
Thibitisha swali la uwekaji safu wima hapo juu.
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>
Vipi kuhusu kuingiza maadili kwenye uwanja?
mysql> INSERT INTO minttec VALUES ('1' , 'Ravi' , 'Saive' , '[email ' , 'India' );
Query OK, 1 row affected (0.02 sec)
mysql>Vipi kuhusu kuingiza zaidi ya thamani 1 kwa wakati mmoja kwenye jedwali lililo hapo juu.
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
Thibitisha uwekaji hapo juu.
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>
Hebu sema kiingilio cha tatu katika pato hapo juu ni batili na tunahitaji kufuta ingizo la tatu.
mysql> DELETE FROM minttec WHERE id = 3; Query OK, 1 row affected (0.02 sec)
Thibitisha operesheni iliyo hapo juu.
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)
Kitambulisho (=4) kinahitaji kuhaririwa.
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>
Thibitisha swali lililo hapo juu.
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>
Kumbuka: Swali lililo hapo juu, kama lilivyotekelezwa sio wazo zuri. Itabadilisha kitambulisho kuwa '4' ambapo jina la kwanza ni 'tecmint'. Daima ni wazo nzuri kutumia safu zaidi ya moja na ambapo kifungu kupata makosa kidogo, kama:
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>
Wacha tunahitaji kuangusha (kufuta) safu tunayofikiria, haina umuhimu sema 'nchi' hapa.
mysql> ALTER TABLE minttec drop country; Query OK, 3 rows affected (0.15 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql>
Thibitisha jedwali.
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>
Je, unadhani jina letu la jedwali minttec halifai sana. Vipi kuhusu kuibadilisha kuwa tecmint_table.
mysql> RENAME TABLE minttec TO tecmint_table; Query OK, 0 rows affected (0.03 sec) mysql>
Tazama majedwali yote chini ya hifadhidata ya sasa.
mysql> show tables; +-------------------+ | Tables_in_tecmint | +-------------------+ | tecmint_table | +-------------------+ 1 row in set (0.00 sec) mysql>
Jedwali limebadilishwa jina. Sasa chukua nakala rudufu ya hifadhidata ya MySQL hapo juu, katika safu moja ya amri bila zana yoyote ya kisasa. Endesha nambari iliyo hapa chini kwenye terminal yako na sio kwa haraka ya mysql.
# 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
Daima ni wazo nzuri kudumisha Hifadhi Nakala ya hifadhidata za MySQL. Kurejesha data iliyohifadhiwa ya MySQL tena ni safu rahisi ya nambari unayohitaji kutekeleza kwa haraka yako ya terminal na sio kwa haraka ya mysql.
Lakini, subiri kwanza tutafuta hifadhidata ili kuthibitisha ikiwa urejeshaji wetu ni kamili.
mysql> drop database tecmint; Query OK, 1 row affected (0.02 sec)
Angalia hifadhidata 'tecmint' kwenye seva yako ya hifadhidata.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | my_database | | mysql | | performance_schema | | phpmyadmin | | sisso | | test | +--------------------+ 7 rows in set (0.00 sec) mysql>
Kubwa! Hifadhidata imepotea, lakini hatuhitaji kuwa na wasiwasi, tunahifadhi nakala.
Ili kurejesha hifadhidata iliyopotea, endesha amri ifuatayo.
# mysql -u root -p tecmint < tecmint.sql Enter password: ERROR 1049 (42000): Unknown database 'tecmint'
OOPS! Hitilafu, jamani hatujaunda tecmint ya hifadhidata. Kwa hivyo nenda kwa haraka ya mysql na uunda hifadhidata 'tecmint'.
mysql> create database tecmint; Query OK, 1 row affected (0.00 sec) mysql>
Sasa ni wakati wa kutekeleza amri ya kurejesha kwa haraka ya ganda lako (madhubuti).
# mysql -u root -p tecmint < tecmint.sql Enter password:
Thibitisha hifadhidata yako.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | tecmint | | test | +--------------------+ 8 rows in set (0.00 sec)
Thibitisha yaliyomo kwenye hifadhidata.
mysql> show tables from tecmint; +-------------------+ | Tables_in_tecmint | +-------------------+ | tecmint_table | +-------------------+ 1 row in set (0.00 sec) mysql>
Thibitisha yaliyomo kwenye jedwali lako lililorejeshwa.
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)
Huu sio mwisho dhahiri, tutashughulikia dhana ya ufunguo msingi, ufunguo wa kigeni, jedwali nyingi na maswali yanayoendesha kwa kutumia hati rahisi ya PHP katika sehemu inayofuata ya kifungu.
Usisahau kutuambia, jinsi ulivyohisi wakati wa kupitia makala. Maoni yako yanathaminiwa sana. Kuwa na Afya Bora na Ufuatiliaji, endelea kushikamana na Tecmint.