Kategoria: MySQL

Export z tabeli do pliku CSV

SELECT "Nazwa Firmy", "Płeć", "Imię", "Nazwisko", "Stanowisko", "Tel", "Email", "Komórka"

UNION

SELECT
  c.name,
  CASE cc.is_man WHEN 2 THEN "K" WHEN 1 THEN 'M' ELSE '-' END,
  cc.firstname, cc.lastname,
  IFNULL(cc.stanowisko, "-"), IFNULL(cc.tel, "-"), IFNULL(cc.email,"-"), IFNULL(cc.kom,"-")
FROM company_contacts cc
 INNER JOIN companies c ON c.id = cc.company_id
ORDER BY cc.company_id, cc.lastname, cc.firstname
LIMIT 20

INTO  OUTFILE '/tmp/company_contacts.csv'
      CHARACTER SET CP1250
      FIELDS ENCLOSED BY ""
      TERMINATED BY ';'
      ESCAPED BY '\\'
      LINES TERMINATED BY '\n'
;

Grant – Revoke

Przydzielanie, sprawdzanie i wycofywanie uprawnień

CREATE USER bob@'165.129.76.20' IDENTIFIED BY 'superpassword';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON myDb.* TO bob@'165.129.76.20'
SHOW GRANTS FOR bob@'165.129.76.20';
REVOKE ALL PRIVILEGES ON myDb.* FROM bob@'165.129.76.20';

Czytaj dalej Grant – Revoke

mysql – UPDATE xxxx INNER JOIN

Aktualizuje rekordy powiązane z inną tabelą

UPDATE projects p
 INNER JOIN companies c ON c.id=p.company_id
SET p.manager_id = c.manager_id
WHERE c.manager_id <> p.manager_id
 AND (p.prev_manager_id <> p.manager_id OR p.prev_manager_id IS NULL);
 AND c.manager_id = 1023;

Mysql – sortowanie ciągu znaków jako INT

Sortowanie po 3 kategoriach. Ostatnia kategoria jest typu znakowego  ale przechowuje numery np. 5, 5a, 5b, 6, 6a. Chcemy sortować tak jak liczby

 $query->order(['Procedures.proc_type_id', 'Procedures.proc_category_id']);

-- numer jest polem typu varchar, wartości: 5, 5a, 5b, 6, 6a ...
$query->orderAsc( function($exp, $q) {
     return $q->func()->cast('numer','UNSIGNED');
});

CakePHP MySQL – procedury składowane

Utworzenie procedury składowanej w MySQL

DELIMITER $$
CREATE PROCEDURE low_amount()
BEGIN
SELECT ip.id, ip.product_id, ip.brand_id, ip.amount_alert, ip.is_ordered, SUM(ip.amount) AS suma, 
       p.name AS product, b.initials, b.name AS brand
FROM item_packs ip 
    INNER JOIN products p ON p.id=ip.product_id
    INNER JOIN brands   b ON b.id=ip.brand_id
GROUP BY ip.product_id, ip.brand_id
HAVING suma <= ip.amount_alert;
END $$
DELIMITER ;
SHOW PROCEDURE STATUS    -- wszystkie procedury

SHOW PROCEDURE STATUS [LIKE 'my_procedure' | WHERE search_condition]

SHOW CREATE PROCEDURE my_procedure;
DROP PROCEDURE [IF EXISTS] my_procedure;

SHOW PROCEDURE STATUS WHERE Db='myDababaseName' \G
DELIMITER $
CREATE PROCEDURE user_projects(p_user_id INT)
BEGIN
  SELECT p.id, p.status_id, COUNT(*) AS count,
        SUM(p.price) AS total, SUM(p.price * (ch.percent / 100)) AS weighted
  FROM projects p
   LEFT JOIN chances ch ON p.chance_id=ch.id
  WHERE p.manager_id = p_user_id
   AND p.status_id <> 3                        -- bez odrzuconych
  GROUP BY p.status_id ;
END $
DELIMITER ;

Wywołanie procedury w CakePHP 3

$connection = \Cake\Datasource\ConnectionManager::get('default');

$results = $connection->execute('CALL low_amount()')->fetchAll('assoc');

$this->set(compact('results'));

MySQL – foreign key – lista powiązanych kluczy

W jaki sposób sprawdzić które tabele mają zdefiniowane klucze obce do tabeli users.

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  CONSTRAINT_NAME,   -- <<-- Nazwa potrzebna do skasowania 
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'users'\G
+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+      
| articles   | user_id     | user_key        | users                 | id                     |
| bookmarks  | user_id     | ub_key          | users                 | id                     |
+------------+-------------+-----------------+-----------------------+------------------------+

Druga wersja

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
  WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL; 
+--------------+---------------+-------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME    | COLUMN_NAME | CONSTRAINT_NAME |
+--------------+---------------+-------------+-----------------+
| intranet     | articles      | user_id     | user_key        |
| intranet     | articles_tags | article_id  | article_key     |
| intranet     | articles_tags | tag_id      | tag_key         |
| intranet     | bookmarks     | user_id     | ub_key          |
+--------------+---------------+-------------+-----------------+

sql – update z podzapytaniem

Tabela łącząca posiada wpisy powiązane z usuniętymi rekordami suits. Dla tych rekordów zerujemy klucz obcy.

 UPDATE ingredients_suits si SET suit_id = 0 WHERE NOT EXISTS (
        SELECT id FROM suits s WHERE s.id = si.suit_id
);

Można skasować rekordy tabeli łączącej które mają wyzerowany klucz obcy

DELETE FROM ingredients_suits WHERE suit_id=0;