Memo MySQL

Realizare diferente intre o valoare si cea anterioara (de ex. de la o lună la alta)

SELECT *, YEAR(Data_cit) AS anul, birou1+birou2 AS total_luna
, (birou1+birou2)-LAG((birou1+birou2), 1) OVER ( ORDER BY (birou1+birou2) ASC) As dif_ante
FROM act_birou 
ORDER BY anul DESC, luna DESC

SELECT *, YEAR(Data_cit) AS anul, birou1+birou2 AS total_luna

, (birou1+birou2)-LAG((birou1+birou2), 1) OVER ( ORDER BY (birou1+birou2) ASC) As dif_ante

FROM act_birou

ORDER BY anul DESC, luna DESC

Ranking

SELECT nume, varsta, gen
, @curRank := @curRank + 1 AS nr_crt
FROM persoane p, (SELECT @curRank := 0) AS r
ORDER BY  varsta;

SELECT nume, varsta, gen

, @curRank := @curRank + 1 AS nr_crt

FROM persoane p, (SELECT @curRank := 0) AS r

ORDER BY varsta;

Calculare subtotaluri pe coloana

SELECT Fact_id, DataFact, c.ValFact
,(
    SELECT sum(ValFact) FROM Comenzi
    WHERE Fact_id <= c.Fact_id
) AS 'Total curent'
FROM Comenzi AS c

SELECT Fact_id, DataFact, c.ValFact

SELECT sum(ValFact) FROM Comenzi

WHERE Fact_id <= c.Fact_id

) AS 'Total curent'

FROM Comenzi AS c

Update View

ALTER 
    ALGORITHM=MERGE
VIEW v_utiliz AS
    SELECT id
        , nume_fam
        , nume_cas
        , pren
        , nivel
        , telefon
    FROM
        t_useri;

ALTER

ALGORITHM=MERGE

VIEW v_utiliz AS

SELECT id

, nume_fam

, nume_cas

, pren

, nivel

, telefon

FROM

t_useri;

Update pentru duplicare INSERT

INSERT INTO table (id, camp1, camp2, camp3) VALUES (1, 'val 1', 'val 2', 'val 3') 
ON DUPLICATE KEY UPDATE 
 camp1 = 'val 1'
 , camp2 = 'val 2' 
 , camp3 = 'val 3';

INSERT INTO table (id, camp1, camp2, camp3) VALUES (1, 'val 1', 'val 2', 'val 3')

ON DUPLICATE KEY UPDATE

camp1 = 'val 1'

, camp2 = 'val 2'

, camp3 = 'val 3';

Poate fi util ca, pe lângă cheia primară, să fie, de regulă, un câmp cu valori unicat.

În cazul în care INSERT-ul s-ar face doar pentru câmpuri de tip cheie primară compusă (cum ar fi tabelele copil) şi am presupune că unul din câmpurile cheii primare ar exista şi ar trebui introdus doar celălalt, putem folosi INSERT... ON DUPLICATE KEY... astfel:

INSERT INTO tbl_copil (pers_id, even_id) VALUES (1124, 500) 
ON DUPLICATE KEY UPDATE 
pers_id = pers_id;

INSERT INTO tbl_copil (pers_id, even_id) VALUES (1124, 500)

ON DUPLICATE KEY UPDATE

pers_id = pers_id;

Astfel, această interogare este folosită când introducem un nou eveniment şi există posibilitatea ca această combinaţie deja să existe.

Inserare dată şi timp curent la update-ul înregistrărilor

Ar fi de dorit ca orice tabelă să conţină înregistrarea timpului când a fost făcută înregistrarea sau, chiar, timpul când s-a făcut modificarea. Astfel, dacă pentru data curentă, câmpul va avea ca valoarea implicită CURRENT_TIMESTAMP, la modificarea unei înregistrări putem folosi clauza ON UPDATE la câmpul respectiv. Redăm mai joi cele trei situaţii în care am putea influenţa câmpul de update (la crearea tabelei, la adăugarea câmpului după crearea tabelei sau la modificarea câmpului nedefinit iniţial cu clauza de update.

La crearea tabelei

CREATE TABLE IF NOT EXISTS t_pers (
  id INT 
    NOT NULL 
    PRIMARY KEY 
    AUTO_INCREMENT
  , camp 
    VARCHAR(25) 
    NOT NULL
  , data_in 
    TIMESTAMP 
    NOT NULL 
    DEFAULT CURRENT_TIMESTAMP   -- la inregitrarea nouă afişează data curentă
  , data_up 
    TIMESTAMP 
    NOT NULL 
    ON UPDATE CURRENT_TIMESTAMP -- la modificare înregistrare, inserează data curentă.
)

CREATE TABLE IF NOT EXISTS t_pers (

id INT

NOT NULL

PRIMARY KEY

AUTO_INCREMENT

, camp

VARCHAR(25)

NOT NULL

, data_in

TIMESTAMP

NOT NULL

DEFAULT CURRENT_TIMESTAMP -- la inregitrarea nouă afişează data curentă

, data_up

TIMESTAMP

NOT NULL

ON UPDATE CURRENT_TIMESTAMP -- la modificare înregistrare, inserează data curentă.

)

Adăugarea ulterioară a câmpului

ALTER TABLE t_pers
ADD data_up 
    TIMESTAMP 
    NOT NULL 
    ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE t_pers

ADD data_up

TIMESTAMP

NOT NULL

ON UPDATE CURRENT_TIMESTAMP;

Modificarea câmpului existent

ALTER TABLE t_pers
MODIFY COLUMN data_up 
    TIMESTAMP 
    NOT NULL 
    ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE t_pers

MODIFY COLUMN data_up

TIMESTAMP

NOT NULL

ON UPDATE CURRENT_TIMESTAMP;

Cheie străină

Exemplificare

CREATE TABLE tbl_parinte(
    id_parinte INT AUTO_INCREMENT PRIMARY KEY,
    denumire VARCHAR(50) NOT NULL
) ENGINE=INNODB;
-- Varianta simpla
CREATE TABLE tbl_copil (
    id INT AUTO_INCREMENT PRIMARY KEY
    , alt_camp VARCHAR(25) NOT NULL 
   , id_cheie_str INT
   , FOREIGN KEY (id_cheie_str) 
    REFERENCES tbl_parinte(id_parinte)
); 
-- Varianta completa
CREATE TABLE tbl_copil (
    id INT AUTO_INCREMENT PRIMARY KEY
    , alt_camp VARCHAR(25) NOT NULL
    , id_cheie_str INT
    ,  CONSTRAINT fk_id
        FOREIGN KEY (id_cheie_str) 
        REFERENCES tbl_parinte(id_parinte)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE tbl_parinte(

id_parinte INT AUTO_INCREMENT PRIMARY KEY,

denumire VARCHAR(50) NOT NULL

) ENGINE=INNODB;

-- Varianta simpla

CREATE TABLE tbl_copil (

id INT AUTO_INCREMENT PRIMARY KEY

, alt_camp VARCHAR(25) NOT NULL

, id_cheie_str INT

, FOREIGN KEY (id_cheie_str)

REFERENCES tbl_parinte(id_parinte)

);

-- Varianta completa

CREATE TABLE tbl_copil (

id INT AUTO_INCREMENT PRIMARY KEY

, alt_camp VARCHAR(25) NOT NULL

, id_cheie_str INT

, CONSTRAINT fk_id

FOREIGN KEY (id_cheie_str)

REFERENCES tbl_parinte(id_parinte)

ON UPDATE CASCADE

ON DELETE CASCADE

);

După cum este şi firesc (se remarcă şi din scriptul de mai sus) întâi trebuie să existe tabela părinte, pentru a putea crea cheia străină. Dacă se doreşte vizualizarea constrângerii, în phpMyAdmin, este necesar să ne asigurăm că Storage Engine este InnoDB, accesabil din tab-ul Operations.

Modificare Collation

La nivelul bazei de date

ALTER DATABASE baza_date CHARACTER SET utf8mb4 COLLATE utf8mb4_romanian_ci;

1	ALTER DATABASE baza_date CHARACTER SET utf8mb4 COLLATE utf8mb4_romanian_ci;

La nivelul tabelei

ALTER TABLE tabela CONVERT TO CHARACTER SET utf8 COLLATE utf8_romanian_ci;

1	ALTER TABLE tabela CONVERT TO CHARACTER SET utf8 COLLATE utf8_romanian_ci;

La nivelul câmpului

ALTER TABLE tabela MODIFY nume_col VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_romanian_ci;

1	ALTER TABLE tabela MODIFY nume_col VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_romanian_ci;

Folosind PHP-ul., se poate realiza schimbarea collaţionării, pentru fiecare câmp în parte, astfel:

<?php
$result=mysqli_query('show tables');
while($tables = mysqli_fetch_array($result)) 
{
    foreach ($tables as $key => $value) 
    {
        mysqli_query($con,"ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_romanian_ci");
    }
}
echo "A fost schimbat tipul de colaţionare!";
?>

<?php

$result=mysqli_query('show tables');

while($tables = mysqli_fetch_array($result))

{

foreach ($tables as $key => $value)

{

mysqli_query($con,"ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_romanian_ci");

}

echo "A fost schimbat tipul de colaţionare!";

Sursa: StackOverflow.com

Creare câmpuri matriciale

Dacă se doreşte ca, de exemplu, dintr-o coloană conţinând sexul (M/F), se doreşte crearea a două coloane distincte (sex_M, respectiv, sex_F), se poate folosi fie funcţia SUM, fie COUNT.

Varianta 1 – SUM

SELECT sex
, SUM(CASE WHEN sex="M" THEN 1 ELSE 0 END) AS sex_M
, SUM(CASE WHEN sex="F" THEN 1 ELSE 0 END) AS sex_F
  FROM gestionar 
  GROUP BY sex;

SELECT sex

, SUM(CASE WHEN sex="M" THEN 1 ELSE 0 END) AS sex_M

, SUM(CASE WHEN sex="F" THEN 1 ELSE 0 END) AS sex_F

FROM gestionar

GROUP BY sex;

Varianta 2 – COUNT

SELECT sex
, COUNT(CASE WHEN sex="M" THEN 1 ELSE NULL END) AS sex_M
, COUNT(CASE WHEN sex="F" THEN 1 ELSE NULL END) AS sex_F
  FROM gestionar 
  GROUP BY sex;

SELECT sex

, COUNT(CASE WHEN sex="M" THEN 1 ELSE NULL END) AS sex_M

, COUNT(CASE WHEN sex="F" THEN 1 ELSE NULL END) AS sex_F

FROM gestionar

GROUP BY sex;

Se observă că diferenţa este la argumentul ELSE, unde pentru a nu însuma, se foloseşte valoarea 0, iar la numărare, pentru a nu fi contorizat, se inserează NULL. Cum este de aşteptat, în ambele situaţii rezultatul este acelaşi.

Rezultat

sex	sex_M	sex_F
F	0	26
M	8	0

Afisare lista de procese MySQL

SHOW PROCESSLIST

1	SHOW PROCESSLIST

Informaţii despre o tabelă

SHOW TABLE STATUS 
WHERE NAME = 'nume_tabela'

1 2	SHOW TABLE STATUS WHERE NAME = 'nume_tabela'

(posibil să nu funcţioneze pe tabele InnoDB) sau

SELECT CREATE_TIME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'nume_bd' 
AND TABLE_NAME = 'nume_tabela'

SELECT CREATE_TIME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'nume_bd'

AND TABLE_NAME = 'nume_tabela'

Surse: MySQL.com, StackOverflow.com

L	Ma	Mi	J	V	S	D
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

Memo MySQL

Realizare diferente intre o valoare si cea anterioara (de ex. de la o lună la alta)

Ranking

Calculare subtotaluri pe coloana

Update View

Update pentru duplicare INSERT

Inserare dată şi timp curent la update-ul înregistrărilor

La crearea tabelei

Adăugarea ulterioară a câmpului

Modificarea câmpului existent

Cheie străină

Modificare Collation

Creare câmpuri matriciale

Afisare lista de procese MySQL

Informaţii despre o tabelă

Utilităţi

Categorii

Pagini

Articole recente

Arhive

Proiecte

Realizare diferente intre o valoare si cea anterioara (de ex. de la o lună la alta)

Ranking

Calculare subtotaluri pe coloana

Update View

Update pentru duplicare INSERT

Inserare dată şi timp curent la update-ul înregistrărilor

La crearea tabelei

Adăugarea ulterioară a câmpului

Modificarea câmpului existent

Cheie străină

Modificare Collation

Creare câmpuri matriciale

Afisare lista de procese MySQL

Informaţii despre o tabelă

Partajează asta:

Utilităţi

Categorii

Pagini

Articole recente

Arhive

Proiecte

Etichete