Ta yaya tambayar MySQL ke sarrafa kwafin bayanai?Tace don cire kwafin bayanin bayanan

MySQLTa yaya tambayoyin ke sarrafa kwafin bayanai?Tace don cire kwafin bayanin bayanan

MySQL Gudanar da kwafin bayanai

Ana iya samun kwafin bayanai a wasu teburan bayanan MySQL. A wasu lokuta, muna ba da izinin wanzuwar kwafin bayanai, amma wani lokacin ma muna buƙatar share waɗannan kwafin bayanan.

A cikin wannan babi, za mu gabatar da yadda ake hana kwafin bayanai a cikin tebur ɗin bayanai da yadda ake goge kwafin bayanan da ke cikin tebur ɗin bayanai.

Hana kwafin bayanai a cikin teburi

Kuna iya saita ƙayyadadden filin a cikin teburin bayanan MySQL azaman MABUDIN FARKO ko UNIQUE (na musamman) Index don tabbatar da keɓancewar bayanai.

Bari mu gwada misali: Babu fihirisa da maɓalli na farko a cikin teburin da ke ƙasa, don haka tebur yana ba da damar kwafin bayanai da yawa.

CREATE TABLE person_tbl
(
    first_name CHAR(20),
    last_name CHAR(20),
    sex CHAR(10)
);

Idan kana son saita filayen first_name da last_name a cikin tebur, ba za a iya maimaita bayanan ba, za ka iya saita yanayin maɓalli na farko don saita keɓancewar bayanan. ba zai iya zama NULL ba, amma ana iya saita shi zuwa BA NULL.Mai bi:

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

Idan muka saita fihirisa na musamman, to lokacin shigar da kwafin bayanai, bayanin SQL zai kasa aiwatarwa cikin nasara kuma ya jefa kuskure.

Bambancin dake tsakanin INSERT IGNORE INTO da INSERT INTO shine INSERT IGNORE ya yi watsi da bayanan da ke cikin ma’adanar bayanai, idan kuma babu bayanai a cikin ma’adanar bayanai, sai ya sa sabbin bayanai, idan kuma akwai bayanai, za su tsallake wadannan bayanan.Ta haka ne za a iya adana bayanan da ake da su a cikin ma’adanar bayanai, kuma ana iya cimma manufar shigar da bayanai a cikin gibin.

Misali mai zuwa yana amfani da INSERT IGNORE INTO, wanda ke aiwatarwa ba tare da kuskure ba kuma ba tare da shigar da kwafin bayanai a cikin teburin bayanai ba:

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

SHIGA KISANCI CIKIN SAUKI Lokacin shigar da bayanai, bayan an saita keɓancewar rikodin, idan aka sanya kwafin bayanai, ba za a dawo da kuskure ba, amma kawai za a dawo da faɗakarwa.Kuma MUSA zuwa cikin Idan akwai rikodin farko ko na musamman, za a fara share shi.Saka sabon rikodin.

Wata hanya don saita keɓancewar bayanan ku ita ce ƙara UNIQUE fihirisa kamar haka:

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

Ƙididdiga sun kwafi bayanai

A ƙasa za mu ƙidaya adadin rikodi na first_name da last_name a cikin tebur:

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

Bayanin tambayar da ke sama zai dawo da adadin kwafin bayanan da ke cikin tebur person_tbl.Gabaɗaya, don tambayar ƙima mai kwafi, yi masu zuwa:

• Ƙayyade ko wane ginshiƙi ya ƙunshi yuwuwar ƙima mai kwafi.
• Yi amfani da COUNT(*) a cikin ginshiƙi zaɓi lissafin don jera waɗannan ginshiƙan.
• Rukunin da aka jera a cikin GROUP BY magana.
• Sashe na HAVING yana saita adadin maimaitawa sama da 1.

tace kwafi data

Idan kuna buƙatar karanta bayanai na musamman, zaku iya amfani da kalmar DISTINCT a cikin bayanin SELECT don tace kwafin bayanai.

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl;

Hakanan zaka iya amfani da GROUP BY don karanta bayanai na musamman daga tebur:

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);

kwafi bayanai

Idan kuna son share kwafin bayanai a cikin tebur ɗin bayanai, kuna iya amfani da bayanin SQL mai zuwa:

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
    ->                  FROM person_tbl;
    ->                  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

Tabbas, zaku iya ƙara INDEX (index) da PRIMAY KEY (maɓalli na farko) a cikin tebur ɗin bayanai don share kwafin bayanan da ke cikin tebur.Hanyoyin kamar ƙasa:

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);