MySQL Dokokin Gudanar da Bayanan Bayanai - Sashi Na


Database tsari ne wanda aka tsara shi ta hanyar lantarki. Maganar kakanninmu ta san ma'anar bayanan har lokacin da babu kwamfyutoci, duk da haka ƙirƙirar da kiyaye wannan bayanan aiki ne mai wahala. A cikin kundin bayanai na hannu ka ce shafuka 100, idan ya zama dole ka nemi duk ma'aikatan da albashinsu bai gaza 10k ba, kawai ka yi tunanin irin wahalar da zai kasance, to.

A cikin duniyar yau ba za ku iya tserewa daga Bayanai ba. A yanzu haka miliyoyin rumbunan adana bayanai suna aiki a duk duniya don adanawa da kuma karɓar bayanai na kowane nau'i ya kasance dabarun bayanai ne, rikodin ma'aikata ko fasahar yanar gizo.

Galibi ana kiran Database a matsayin tsari na ƙarshen-ƙarshe, saboda saboda bayyane yake don ƙare mai amfani ko Userarshen Mai amfani yana hulɗa kai tsaye tare da bayanan. Suna aiki a kan tsari na gaba-gaba wato, PHP, VB, ASP.NET, da dai sauransu kuma suna tambayar ƙarshen gaban don magance matattarar bayanai a ƙarshen-baya.

Akwai sabar uwar garken bayanai da dama da abokan ciniki kamar Oracle, MySQL, MySQLi, MongoDB da dai sauransu. Kwarewa ɗaya yana nufin samun iko akan mafi yawansu kuma koyan tambayoyin tarin bayanai yana da sauƙin daɗi.

Bari mu fara da tambayoyi masu sauki akan rumbun adana bayanai. Za mu yi amfani da MySQL wanda ya haɗu tare da yawancin abubuwan rarraba Linux ta hanyar tsoho, za ku iya shigar da shi da hannu daga wurin ajiya, idan ba a shigar da shi ta asali ba a cikin yanayinku.

Tambayar bayanan bayanai ƙaramar lamba ce wacce aka aika zuwa ɗakunan ajiya don samun al'ada da ingantaccen sakamako, kamar yadda ake buƙata.

Sanya MySQL Database

Yi amfani da mai sarrafa kunshin “yum” ko “dace” don girka MySQL Database.

# yum install mysql mysql-client mysql-server  (on Yum based Systems)

# apt-get install mysql mysql-client mysql-server (on Apt based Systems)

Fara sabis ɗin bayanan MySQL azaman:

# service mysqld start
or
# service mysql start

To shigar da bayanan MySQL zai kai ka wurin daidaitawa inda aka umarce ka da saita kalmar wucewa ta gudanarwa, da dai sauransu. Da zarar ka gama girkawa da kuma fara sabar sai ka je wajan MySQL na sauri.

# mysql -u root -p

Sauya tushen tare da sunan mai amfani da aka saita ka kuma shigar da kalmar wucewa lokacin da aka sa ka, idan takaddun shiga sun yi daidai, za ka kasance a hanzarin MySQL ɗin idanun ka.

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>

Yanzu aiwatar da tambayoyi a wannan matakin yana da matukar ilimantarwa da kuma nishadi.

mysql> create database tecmint ;
Query OK, 1 row affected (0.02 sec) 

mysql>

Lura: Yana bayar da rahoton cewa tambayar tayi daidai, yana nufin an kirkireshi. Kuna iya tabbatar da sabon bayanan ku wanda aka kirkira azaman.

mysql> show databases; 
+--------------------+
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| tecmint            | 
| test               | 
+--------------------+ 
9 rows in set (0.00 sec) 
mysql>

Lura: Lura da bayanan bayanan ku a cikin kayan da aka sama.

Yanzu kuna buƙatar zaɓar bayanan da za ku yi aiki da shi.

mysql> use tecmint;
Database changed
mysql>

Anan zamu ƙirƙiri tebur faɗi “minttec” tare da filaye uku kamar:

mysql> CREATE TABLE minttec (
    -> id Int(3), 
    -> first_name Varchar (15), 
    -> email Varchar(20) 
    -> ); 
Query OK, 0 rows affected (0.08 sec) 
mysql>

Lura: Tambayar da ke sama ta ce OK wacce ke nufin an halicci tebur ba tare da wani kuskure ba. Don tabbatar da teburin gudanar da tambayar ta ƙasa.

mysql> show tables; 
+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| minttec           | 
+-------------------+ 

1 row in set (0.00 sec) 

mysql>

Abubuwa suna tafiya daidai har yanzu. Yup! Kuna iya duba ginshiƙan da kuka ƙirƙira a tebur “minttec” azaman:

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>

Ba komai bane face sihiri. Ko ta yaya zan gaya muku game da nau'o'in sanarwa da ma'anar su.

  1. Int ne Mai Tsarkakewa
  2. Varchar char ne mai saurin tsayi kamar yadda aka ayyana. Darajar bayan Nau'in shine tsawon filin sama wanda zai iya adana bayanai.

Yayi yanzu muna buƙatar ƙara ginshiƙi faɗi 'sunan karshe-bayan' bayan shafi 'sunan farko-'.

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

Yanzu, tabbatar da shi a teburinku.

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>

Yanzu za mu ƙara ginshiƙi zuwa dama ka ce shafi 'ƙasar' a hannun dama na imel.

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>

Tabbatar da tambayar shigar da shafi na sama.

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>

Ina batun saka dabi'u zuwa filin?

mysql> INSERT INTO minttec VALUES ('1' , 'Ravi' , 'Saive' , '[email protected]' , 'India' );
Query OK, 1 row affected (0.02 sec) 

mysql>

Yaya game da saka fiye da darajar 1 a lokaci guda a cikin teburin da ke sama.

mysql> INSERT INTO minttec VALUES ('2' , 'Narad' , 'Shrestha' , '[email protected]' , 'India' ), ('3' , 'user' , 'singh' , '[email protected]' , 'Aus' ), ('4' , 'tecmint' , '[dot]com' , '[email protected]' , 'India' );
Query OK, 3 rows affected (0.05 sec) 
Records: 3  Duplicates: 0  Warnings: 0

Tabbatar da shigar da ke sama.

mysql> select * from minttec; 
+------+------------+-----------+-------------------+---------+ 
| id   | first_name | last_name | email             | country | 
+------+------------+-----------+-------------------+---------+ 
|    1 | Ravi	    | Saive     | [email protected] | India   | 
|    2 | Narad      | Shrestha  | [email protected]     | India   | 
|    3 | user       | singh     | [email protected]      | Aus     | 
|    4 | tecmint    | [dot]com  | [email protected] | India   | 
+------+------------+-----------+-------------------+---------+ 

4 rows in set (0.00 sec)

mysql>

Bari mu ce shigarwar ta uku a cikin fitowar da ke sama ba ta da inganci kuma muna buƙatar share shigarwar ta uku.

mysql> DELETE FROM minttec WHERE id = 3;

Query OK, 1 row affected (0.02 sec)

Tabbatar da aiki na sama.

mysql> select * from minttec;

+------+------------+-----------+-------------------+---------+ 
| id   | first_name | last_name | email             | country | 
+------+------------+-----------+-------------------+---------+
|    1 | Ravi       | Saive     | [email protected] | India   | 
|    2 | Narad      | Shrestha  | [email protected]     | India   | 
|    4 | tecmint    | [dot]com  | [email protected] | India   | 
+------+------------+-----------+-------------------+---------+
3 rows in set (0.00 sec)

Id (u003d 4) yana buƙatar gyara.

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>

Tabbatar da tambayar da ke sama.

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>

Lura: Tambayar da ke sama, kamar yadda aka yi ba kyakkyawan ra'ayi bane. Zai canza id zuwa '4' inda har abada sunan farko 'tecmint'. Yana da kyau koyaushe a yi amfani da shafi fiye da ɗaya tare da inda magana zata sami ƙaramin kuskure, kamar:

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>

Bari mu bukaci sauke (share) wani shafi da muke tsammani, bashi da mahimmanci a ce 'ƙasa' a nan.

mysql> ALTER TABLE minttec drop country; 
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

Tabbatar da tebur.

mysql> select * from minttec; 

+------+------------+-----------+-------------------+ 
| id   | first_name | last_name | email             | 
+------+------------+-----------+-------------------+ 
|    1 | Ravi       | Saive     | [email protected] | 
|    2 | Narad      | Shrestha  | [email protected]     | 
|    6 | tecmint    | [dot]com  | [email protected] | 
+------+------------+-----------+-------------------+
3 rows in set (0.00 sec) 

mysql>

Shin ba ku tunanin sunan teburinmu "minttec" ba shi da matukar dacewa. Yaya game da canza shi zuwa tecmint_table.

mysql> RENAME TABLE minttec TO tecmint_table; 
Query OK, 0 rows affected (0.03 sec)

mysql>

Duba duk teburin da ke ƙarƙashin bayanan yanzu.

mysql> show tables; 

+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| tecmint_table     | 
+-------------------+
1 row in set (0.00 sec) 

mysql>

Teburin an sake masa suna. Yanzu ɗauki ajiyar bayanan MySQL na sama, a cikin layi ɗaya na umarni ba tare da kayan aiki na zamani ba. Gudun lambar da ke ƙasa a tashar ku kuma ba a kan mysql ba.

# 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

Yana da kyau koyaushe a kiyaye Ajiyayyen bayanan bayanan MySQL. Sake dawo da bayanan MySQL wanda aka adana shine layin layi mai sauki wanda kuke buƙatar gudu a tashar ku ba tare da saurin mysql ɗin ku ba.

Amma, jira na farko za mu share bayanan don tabbatar da idan sabuntawarmu ta zama cikakke.

mysql> drop database tecmint; 
Query OK, 1 row affected (0.02 sec)

Bincika don bayanan 'tecmint' a kan sabar bayananku.

mysql> show databases; 

+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| my_database        | 
| mysql              | 
| performance_schema | 
| phpmyadmin         | 
| sisso              | 
| test               | 
+--------------------+

7 rows in set (0.00 sec) 
mysql>

Babban! Bayanai sun ɓace, amma bamu buƙatar damuwa, muna samun madadin.

Don dawo da bayanan da aka rasa, gudanar da umarni mai zuwa.

# mysql -u root -p tecmint < tecmint.sql
Enter password:
ERROR 1049 (42000): Unknown database 'tecmint'

OOPS! Kuskure, hey bamu ƙirƙiri tarin bayanai na tecmint ba. Don haka je mysql ɗinka sannan ka ƙirƙiri matattarar bayanai 'tecmint'.

mysql> create database tecmint; 
Query OK, 1 row affected (0.00 sec) 

mysql>

Yanzu lokaci ne don gudanar da umarnin komowa a matanka na sauri (tsananin).

# mysql -u root -p tecmint < tecmint.sql 
Enter password:

Tabbatar da bayanan ku.

mysql> show databases; 

+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| tecmint            | 
| test               | 
+--------------------+ 
8 rows in set (0.00 sec)

Tabbatar da abinda ke ciki na database.

mysql> show tables from tecmint;

+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| tecmint_table     | 
+-------------------+ 
1 row in set (0.00 sec)

mysql>

Tabbatar da abinda ke cikin teburin da kuka dawo.

mysql> select * from tecmint_table; 

+------+------------+-----------+-------------------+ 
| id   | first_name | last_name | email             | 
+------+------------+-----------+-------------------+ 
|    1 | Ravi       | Saive     | [email protected] | 
|    2 | Narad      | Shrestha  | [email protected]     | 
|    6 | tecmint    | [dot]com  | [email protected] | 
+------+------------+-----------+-------------------+

3 rows in set (0.00 sec)

Wannan ba ƙarshen bane tabbas, zamu rufe ma'anar maɓallin farko, maɓallin waje, tebur da yawa da tambayoyi masu gudana ta amfani da rubutun PHP mai sauƙi a cikin ɓangaren na gaba na labarin.

Kar ka manta da gaya mana, yadda kuka ji yayin da kuke cikin labarin. Ana yaba da bayananku sosai. Kasance Lafiya da Tunani, kasance cikin haɗi da Tecmint.