Kmandi ta' Amministrazzjoni ta' Database Bażiku MySQL - Parti I
Database hija sett strutturat ta' data maħżuna elettronikament. Il-kunċett ta 'database kien magħruf għall-antenati tagħna anke meta ma kien hemm l-ebda kompjuters, madankollu l-ħolqien u ż-żamma tali database kien xogħol tedious ħafna. F'database manwali ngħidu ta '100 paġna, jekk ikollok tfittex l-impjegati kollha li s-salarju tagħhom kien inqas minn 10k, aħseb biss kemm kien ikun diffiċli, allura.
Fid-dinja tal-lum inti sempliċiment ma tistax taħrab Database. Bħalissa miljuni ta’ database qed jaħdmu madwar id-dinja biex jaħżnu u jġibu dejta ta’ kull tip kemm jekk tkun dejta strateġika, rekord tal-impjegati jew teknoloġiji tal-web.
Database spiss tissejjaħ bħala proċess back-end, peress li la hija viżibbli għall-utent aħħari u lanqas l-Utent finali jinteraġixxi direttament mad-database. Jaħdmu fuq il-proċess front-end jiġifieri, PHP, VB, ASP.NET, eċċ u jistaqsu lill-front end biex jittratta database fil-back-end.
Hemm diversi server tad-database u klijent disponibbli bħal Oracle, MySQL, MySQLi, MongoDB eċċ. Is-sintassi ta 'dawn kollha huma bejn wieħed u ieħor l-istess. Li nikkontrollaw wieħed ifisser li tikseb kontroll fuq ħafna minnhom u t-tagħlim tal-mistoqsijiet ta 'database huwa faċli ħafna u divertenti.
Nibdew b'mistoqsijiet sempliċi fuq id-database. Se nkunu qed nużaw MySQL li jiġi magħqud mal-biċċa l-kbira tad-distribuzzjonijiet tal-Linux awtomatikament, tista 'tinstallah manwalment mir-repożitorju, jekk ma jkunx installat awtomatikament fil-każ tiegħek.
Ukoll mistoqsija tad-database hija biċċa sempliċi ta 'kodiċi li tintbagħat fid-database biex tikseb riżultat personalizzat u raffinat, kif meħtieġ.
Installa MySQL Database
Uża maniġer tal-pakketti yum jew apt biex tinstalla 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)
Ibda s-servizz tad-database MySQL bħala:
# service mysqld start or # service mysql start
L-installazzjoni tajba ta' database MySQL tieħdok għall-konfigurazzjoni fejn tintalab issettja l-password tal-amministratur, eċċ. Ladarba tispiċċa l-installazzjoni u tibda s-server mur fil-pront MySQL tiegħek.
# mysql -u root -p
Ibdel l-għeruq bl-isem tal-utent konfigurat tiegħek u daħħal il-password meta tintalab, jekk il-kredenzjali tal-login hija korretta, tkun fil-pront MySQL tiegħek f'teptip ta' għajnejk.
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>
Issa t-twettiq ta' mistoqsijiet f'dan il-pront huwa edukattiv u divertenti ħafna.
mysql> create database tecmint ; Query OK, 1 row affected (0.02 sec) mysql>
Nota: Jirrapporta li l-mistoqsija kienet korretta, tfisser database hija maħluqa. Tista' tivverifika l-bażi tad-data maħluqa ġdida tiegħek bħala.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | tecmint | | test | +--------------------+ 9 rows in set (0.00 sec) mysql>
Nota: Avviż id-database tiegħek fl-output ta 'hawn fuq.
Issa trid tagħżel id-database biex taħdem fuqha.
mysql> use tecmint; Database changed mysql>
Hawnhekk se nkunu qed noħolqu tabella jiġifieri minttec bi tliet oqsma bħala:
mysql> CREATE TABLE minttec (
-> id Int(3),
-> first_name Varchar (15),
-> email Varchar(20)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql>
Nota: Il-mistoqsija ta 'hawn fuq tgħid OK li jfisser li t-tabella ġiet maħluqa mingħajr ebda żball. Biex tivverifika t-tabella mexxi l-mistoqsija hawn taħt.
mysql> show tables; +-------------------+ | Tables_in_tecmint | +-------------------+ | minttec | +-------------------+ 1 row in set (0.00 sec) mysql>
L-affarijiet sejrin tajjeb sa issa. Iva! Tista' tara l-kolonni li ħloqt fit-tabella “minttec” bħala:
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>
Kien xejn inqas minn magic. Xorta waħda ngħidlek dwar it-tipi ta' dikjarazzjoni u t-tifsira tagħhom.
- Int huwa Integer
- Varchar huwa char li għandu tul varjabbli kif definit. Il-valur wara Tip huwa t-tul tal-qasam sa fejn jista' jaħżen id-dejta.
OK issa rridu nżidu kolonna jgħidu 'last_name' wara l-kolonna 'first_name'.
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
Issa, ivverifikaha fit-tabella tiegħek.
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>
Issa se nżidu kolonna fuq il-lemin ngħidu kolonna 'pajjiż' fuq il-lemin tal-email.
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>
Ivverifika l-mistoqsija ta 'inserzjoni tal-kolonna ta' hawn fuq.
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>
Xi ngħidu dwar id-dħul tal-valuri fil-qasam?
mysql> INSERT INTO minttec VALUES ('1' , 'Ravi' , 'Saive' , '[email ' , 'India' );
Query OK, 1 row affected (0.02 sec)
mysql>Kif dwar li ddaħħal aktar minn valur 1 kull darba fit-tabella ta 'hawn fuq.
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
Ivverifika l-inserzjoni ta' hawn fuq.
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>
Ejja ngħidu li t-tielet entrata fl-output ta 'hawn fuq hija invalida u għandna bżonn inħassru t-tielet entrata.
mysql> DELETE FROM minttec WHERE id = 3; Query OK, 1 row affected (0.02 sec)
Ivverifika l-operazzjoni ta 'hawn fuq.
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)
L-id (=4) jeħtieġ li jiġi editjat.
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>
Ivverifika l-mistoqsija ta' hawn fuq.
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>
Nota: Il-mistoqsija ta 'hawn fuq, kif imwettqa mhix idea tajba. Se jibdel l-id għal '4' fejn qatt l-ewwel isem huwa 'tecmint'. Dejjem hija idea tajba li tuża aktar minn kolonna waħda bil-klawżola where biex tikseb żball minimu, bħal:
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>
Ħalli neħtieġu li npoġġu (neħħu) kolonna naħsbu, m'għandha l-ebda importanza ngħidu 'pajjiż' hawn.
mysql> ALTER TABLE minttec drop country; Query OK, 3 rows affected (0.15 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql>
Ivverifika t-tabella.
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>
Ma taħsibx li l-isem tal-mejda tagħna minttec mhuwiex rilevanti ħafna. Kif dwar li tinbidel għal tecmint_table.
mysql> RENAME TABLE minttec TO tecmint_table; Query OK, 0 rows affected (0.03 sec) mysql>
Ara t-tabelli kollha taħt id-database attwali.
mysql> show tables; +-------------------+ | Tables_in_tecmint | +-------------------+ | tecmint_table | +-------------------+ 1 row in set (0.00 sec) mysql>
It-tabella ngħatat isem ġdid. Issa ħu backup tad-database MySQL ta' hawn fuq, f'linja waħda ta' kmand mingħajr ebda għodda sofistikata. Mexxi l-kodiċi hawn taħt fit-terminal tiegħek u mhux fuq il-pront 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
Dejjem hija idea tajba li żżomm Backup tad-databases MySQL. Ir-restawr tad-Dejta MySQL sostnuta hija għal darb'oħra linja sempliċi ta' kodiċi li għandek bżonn taħdem fil-pront tat-terminal tiegħek u mhux fil-pront mysql tiegħek.
Iżda, stenna l-ewwel se nħassru d-database biex nivverifikaw jekk ir-restawr tagħna huwiex perfett.
mysql> drop database tecmint; Query OK, 1 row affected (0.02 sec)
Iċċekkja għal database 'tecmint' fuq is-server tad-database tiegħek.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | my_database | | mysql | | performance_schema | | phpmyadmin | | sisso | | test | +--------------------+ 7 rows in set (0.00 sec) mysql>
Kbir! Id-database tintilef, iżda ma rridux ninkwetaw, qed ikollna l-backup.
Biex tirrestawra d-database mitlufa, mexxi l-kmand li ġej.
# mysql -u root -p tecmint < tecmint.sql Enter password: ERROR 1049 (42000): Unknown database 'tecmint'
OOPS! Żball, ħej aħna ma ħoloqx id-database tecmint. Allura mur fil-pront mysql tiegħek u oħloq database 'tecmint'.
mysql> create database tecmint; Query OK, 1 row affected (0.00 sec) mysql>
Issa l-ħin biex tħaddem il-kmand tar-restawr fil-pront tal-qoxra tiegħek (strettament).
# mysql -u root -p tecmint < tecmint.sql Enter password:
Ivverifika d-database tiegħek.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | tecmint | | test | +--------------------+ 8 rows in set (0.00 sec)
Ivverifika l-kontenut tad-database.
mysql> show tables from tecmint; +-------------------+ | Tables_in_tecmint | +-------------------+ | tecmint_table | +-------------------+ 1 row in set (0.00 sec) mysql>
Ivverifika l-kontenut tat-tabella restawrata tiegħek.
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)
Dan mhux it-tmiem definittivament, aħna se nkopru l-kunċett ta 'ċavetta primarja, ċavetta barranija, tabelli multipli u mistoqsijiet li jmexxu bl-użu ta' skript PHP sempliċi fil-parti li jmiss tal-artikolu.
Tinsiex tgħidilna, kif ħassejtek waqt li għaddejt mill-artiklu. Il-kummenti tiegħek huma apprezzati ħafna. Ibqa' Saħħa u Sintonizzat, ibqa' konness ma' Tecmint.