11 Advance MySQL Database Tambayoyin Tambayoyi da Amsoshi don Masu Amfani da Linux

Mun riga mun buga labaran MySQL guda biyu, Tecmint Community sun yaba sosai. Wannan shine labari na uku akan jerin tambayoyin MySQL kuma goma sha shida a cikin Salon Interview Genre.

  1. 15 Basic Tambayoyin Hirar MySQL
  2. 10 MySQL Database Tambayoyi don Tambayoyi don Matsakaici

Mun isa nan duk saboda goyon bayan ku kuma muna neman hakan nan gaba don samar da ƙarshen ku. Anan a cikin wannan labarin za mu mai da hankali kan yanayin aiki na MySQL, tambayar da ta dace da yanayin Interview.

Ans: Umurnin MySQL na ƙasa zai nuna sigar uwar garken kuma zaɓaɓɓen Database a halin yanzu.

mysql> SELECT VERSION(), DATABASE();

+-------------------------+------------+
| VERSION()               | DATABASE() |
+-------------------------+------------+
| 5.5.34-0ubuntu0.13.10.1 | NULL       |
+-------------------------+------------+
1 row in set (0.06 sec)

A cikin Rukunin Database yana nuna ƙimar NULL saboda ba mu zaɓi kowace rumbun adana bayanai ba. Don haka, zaɓi bayanan bayanai kamar yadda aka nuna a cikin umarni mai zuwa.

mysql> use Tecmint;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select VERSION(), DATABASE();

+-------------------------+------------+
| VERSION()               | DATABASE() |
+-------------------------+------------+
| 5.5.34-0ubuntu0.13.10.1 | tecmint    |
+-------------------------+------------+
1 row in set (0.00 sec)

Ans: Bayanin da ke ƙasa zai nuna duk ginshiƙan duk masu amfani daga tebur 'Tecmint' ban da mai amfani' SAM'.

mysql> SELECT * FROM Tecmint WHERE user !=SAM;

+---------------------+---------+---------+---------+---------+-------+ 
| date                | user    | host 	 | root     | local   | size  | 
+---------------------+---------+---------+---------+---------+-------+ 
| 2001-05-14 14:42:21 | Anthony | venus  | barb     | venus   | 98151 | 
| 2001-05-15 08:50:57 | TIM     | venus  | phil     | venus   | 978   | 
+---------------------+---------+---------+---------+---------+-------+

Ans: Ana amfani da mai aiki da AND lokacin da muke amfani da (=) kuma ana amfani da afaretan OR lokacin da muke amfani da (!=). Misali na (=) tare da AND Operator.

mysql> SELECT * FROM mail WHERE user = SAM AND root = phil

Misalin (!=) tare da OR Operator.

mysql> SELECT * FROM mail WHERE user != SAM OR root != phil

+---------------------+---------+---------+---------+---------+-------+ 
| date                | user    | host    | root    | local   | size  | 
+---------------------+---------+---------+---------+---------+-------+ 
| 2001-05-14 14:42:21 | Anthony | venus   | barb    | venus   | 98151 | 
+---------------------+---------+---------+---------+---------+-------+

  1. = : yana nufin Daidai da
  2. != : Ba Daidai da
    3. ba
  3. ! : yana wakiltar BA Mai Gudanarwa ba

Ana kula da AND & OR azaman masu haɗin gwiwa a cikin MySQL.

Ans: Tambayar a MySQL za a iya rubuta ta ta amfani da bayanin IFNULL(). Bayanin IFNULL() yana gwada hujjarsa ta farko kuma ta dawo idan ba NULL ba, ko kuma ta dawo da hujjar ta ta biyu, in ba haka ba.

mysql> SELECT name, IFNULL(id,'Unknown') AS 'id' FROM taxpayer;

+---------+---------+ 
| name 	  | id      | 
+---------+---------+ 
| bernina | 198-48  | 
| bertha  | Unknown | 
| ben     | Unknown | 
| bill    | 475-83  | 
+---------+---------+

Ans: Muna bukatar mu yi amfani da LIMIT magana tare da ORDER BY don cimma yanayin da aka bayyana a sama.

mysql> SELECT * FROM name LIMIT 1;

+----+------+------------+-------+----------------------+------+ 
| id | name | birth      | color | foods                | cats | 
+----+------+------------+-------+----------------------+------+ 
| 1  | Fred | 1970-04-13 | black | lutefisk,fadge,pizza | 0    | 
+----+------+------------+-------+----------------------+------+
mysql> SELECT * FROM profile LIMIT 5;

+----+------+------------+-------+-----------------------+------+ 
| id | name | birth      | color | foods                 | cats | 
+----+------+------------+-------+-----------------------+------+ 
| 1  | Fred | 1970-04-13 | black | lutefisk,fadge,pizza  | 0    | 
| 2  | Mort | 1969-09-30 | white | burrito,curry,eggroll | 3    | 
| 3  | Brit | 1957-12-01 | red   | burrito,curry,pizza   | 1    |   
| 4  | Carl | 1973-11-02 | red   | eggroll,pizza         | 4    | 
| 5  | Sean | 1963-07-04 | blue  | burrito,curry         | 5    | 
+----+------+------------+-------+-----------------------+------+
mysql> SELECT * FROM profile ORDER BY birth LIMIT 1;

+----+------+------------+-------+----------------+------+ 
| id | name | birth      | color | foods          | cats | 
+----+------+------------+-------+----------------+------+ 
| 9  | Dick | 1952-08-20 | green | lutefisk,fadge | 0    | 
+----+------+------------+-------+----------------+------+

Ans: To duka biyun suna da fa'ida da rashin amfani. A tsawon lokaci na fi son MySQL.

  1. Mysql FOSS ne.
  2. MySQL na iya ɗauka.
  3. MySQL yana goyan bayan GUI biyu da kuma Umurnin Umurni.
  4. Ana goyan bayan Gudanarwar MySQL akan Mai Binciken Tambaya.

Ans: Samun kwanan wata a MySQL abu ne mai sauƙi kamar aiwatar da bayanin SELECT na ƙasa.

mysql> SELECT CURRENT_DATE();

+----------------+
| CURRENT_DATE() |
+----------------+
| 2014-06-17     |
+----------------+

Ans: Muna amfani da zabin '-e' (fitarwa) don fitar da tebur MySQL ko duka bayanan bayanai cikin fayil na XML. Tare da manyan teburi muna iya buƙatar aiwatar da shi da hannu amma don ƙananan tebur, aikace-aikace kamar phpMyAdmin na iya yin aikin.

Umarnin asali na MySQL na iya yin shi.

mysql -u USER_NAME –xml -e 'SELECT * FROM table_name' > table_name.xml

Inda USER_NAME shine sunan mai amfani na Database, table_name shine tebur da muke fitarwa zuwa XML kuma table_name.xml shine fayil ɗin xml inda ake adana bayanai.

Ans: MySQL_pconnect() yana buɗe hanyar haɗin da ke dawwama zuwa ga MySQL Database wanda kawai ke nufin cewa ba a buɗe ma'ajin bayanai a duk lokacin da shafin ya ɗauka don haka mu ba zai iya amfani da MySQL_close() don rufe haɗin gwiwa mai tsayi ba.

Wani ɗan gajeren bambanci tsakanin MySQL_pconnect da MySQL_connect sune.

Ba kamar MySQL_pconnect ba, MySQL_connect - Yana buɗe Database duk lokacin da aka ɗora shafin wanda za'a iya rufe kowane lokaci ta amfani da sanarwa MySQL_close().

Ans: Umurnin da ke biyowa zai nuna duk fihirisar 'mai amfani'' tebur.

mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
| user  |          0 | PRIMARY  |            1 | Host        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               | 
| user  |          0 | PRIMARY  |            2 | User        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
2 rows in set (0.00 sec)

Ans: CSV tana nufin Wakafi-Raba Ƙimomin aka Rarraba Dabi'u. Teburin CSV yana adana bayanai a cikin rubutu a sarari da tsarin tambura. Yawanci yana ƙunshi rikodin guda ɗaya akan kowane layi.

Kowane rikodi yana rabu da ƙayyadaddun ƙayyadaddun ƙayyadaddun bayanai (Waƙafi, Semi-colon,…) inda kowane rikodin yana da jerin filin. An fi amfani da allunan CSV don adana lambobin waya zuwa Shigo da Fitarwa kuma ana iya amfani da su don adana kowane irin bayanan rubutu na fili.

Shi ke nan a yanzu. Zan sake kasancewa a nan tare da wani labari mai ban sha'awa, mutane za ku so ku karanta. Har sai ku kasance a saurara kuma ku haɗa zuwa Tecmint kuma kar ku manta da samar mana da mahimman ra'ayoyin ku a cikin sashin sharhin da ke ƙasa.