« CONTACT « TUTOS

Mysql Comprendre vite



CRUD

CREATE -- INSERT INTO table( colonne ) VALUES (' ')
READ     -- SELECT colonne FROM table WHERE colonne ...
UPDATE -- UPDATE table SET colonne ='New_value' WHERE colonne ='Old_value'
DELETE -- DELETE FROM table WHERE colonne ...

-- CREATE :
-- ------
INSERT INTO cats(name, breed, age)
       VALUES
       ('Ringo', 'Tabby', 4),
       ('Cindy', 'Maine Coon', 10),
       ('Dumbledore', 'Maine Coon', 11),
       ('Egg', 'Persian', 4),
       ('Misty', 'Tabby', 13),
       ('Rod Stewart', 'Ragdoll', 9),
       ('Jackson', 'Sphynx', 7);


-- READ :
-- ----
SELECT * FROM cats;

SELECT cat_id FROM cats;
SELECT name FROM cats;
SELECT name, age FROM cats;                  -- Multi
SELECT age, name FROM cats;                  -- Inverse

SELECT * FROM cats WHERE age=4;              -- Specific
SELECT * FROM cats WHERE age>=4 AND age<=8;
SELECT * FROM cats WHERE name='Egg';
SELECT * FROM cats WHERE name LIKE 'E%';
SELECT * FROM cats WHERE name LIKE '%y';

SELECT cat_id AS id, name FROM cats;         -- ALIAS


-- UPDATE :
-- ------
UPDATE cats SET breed='Shorthair' WHERE breed='Tabby';
UPDATE cats SET age=14 WHERE name='Misty';


UPDATE table
SET colonne_1 = 'valeur 1', colonne_2 = 'valeur 2', colonne_3 = 'valeur 3'
WHERE id = 1;


UPDATE client
SET rue = '49 Rue Ameline',
  ville = 'Saint-Eustache-la-Forêt',
  code_postal = '76210'
WHERE id = 2;


UPDATE client SET pays = 'FRANCE'
-- valeur « FRANCE » dans la colonne « pays » pour toutes les lignes de la table


-- DELETE :
-- --------           DELETE->Efface table_name  VS  DROP->Supprime table_name

DELETE FROM cats WHERE name='Egg';
DELETE FROM cats;
DELETE FROM cats WHERE age='4';
DELETE FROM cats WHERE cat_id=age;

DELETE FROM items;         -- efface tout le contenu table
DELETE * FROM items;       -- same

DELETE FROM items ORDER BY price DESC LIMIT 1;        -- retire le prix le + fort, 1er de la list
DELETE FROM items ORDER BY price ASC LIMIT 1;         -- retire le plus petit prix
DELETE FROM items WHERE price='999.99' limit 2;       -- retire les 2 + anciens
DELETE FROM items WHERE price > 7;


DELETE FROM items;    -- VIDE mais ne ré-initialise pas l’auto-incrément
TRUNCATE TABLE items; -- VIDE mais ré-initialise l’auto-incrément           BEST

DROP TABLE items;     -- SUPPRIME la table

Results :

+--------+----------------+------------+------+
| cat_id | name           | breed      | age  |
+--------+----------------+------------+------+
|      1 | Ringo          | Tabby      |    4 |
|      2 | Cindy          | Maine Coon |   10 |
|      3 | Dumbledore     | Maine Coon |   11 |
|      4 | Egg            | Persian    |    4 |
|      5 | Misty          | Tabby      |   13 |
|      6 | Rod Stewart    | Ragdoll    |    9 |
|      7 | Jackson        | Sphynx     |    7 |
+--------+----------------+------------+------+
	




START

CREATE DATABASE database_name;
DROP DATABASE database_name;

SELECT database();         -- En cours

SHOW DATABASES;            -- Liste
SHOW TABLES;
USE database_name;         -- Choose

SHOW COLUMNS FROM cats;    -- DESC cats

SHOW TRIGGERS;

DROP TABLE cats;           -- Delete
DROP DATABASE name;
DROP TRIGGER trigger_name;

DELETE FROM items;    -- VIDE mais ne ré-initialise pas l’auto-incrément
TRUNCATE TABLE items; -- VIDE mais ré-initialise l’auto-incrément           BEST


-- To Call function from That File :
-- -------------------------------
SOURCE file_name.sql;


-- SAMPLE :
-- ------
CREATE TABLE employees (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    middle_name VARCHAR(255),
    age INT NOT NULL,
    current_status VARCHAR(255) NOT NULL DEFAULT 'employed'
);

INSERT INTO employees(first_name, last_name, age) VALUES
('Dora', 'Smith', 58),('Gilius', 'Blanchard', 58);

SELECT * FROM employees;

Results :

+----+------------+-----------+-------------+-----+----------------+
| id | first_name | last_name | middle_name | age | current_status |
+----+------------+-----------+-------------+-----+----------------+
|  1 | Dora       | Smith     | NULL        |  58 | employed       |
|  2 | Gilius     | Blanchard | NULL        |  58 | employed       |
+----+------------+-----------+-------------+-----+----------------+




Functions Aggregate :

-- number of books in database :
--------------------------------
SELECT COUNT(title) FROM books;   -- 25
SELECT COUNT(*) FROM books;       -- 25


-- total number of books in stock :
-----------------------------------
SELECT SUM(title) FROM books;           -- 30  why != then count 25 ? 30 coz INT
SELECT SUM(stock_quantity) FROM books;  -- 5082


-- how many books each years :
------------------------------
SELECT released_year, COUNT(title) FROM books GROUP BY released_year; -- 16 row
SELECT COUNT(*) FROM books GROUP BY released_year;                    -- 16 row


-- find the average year for each author :
------------------------------------------
SELECT CONCAT(author_fname, ' ', author_lname) AS 'Author',
       AVG(released_year) AS 'Year'
FROM books GROUP BY author_fname, author_lname;


-- find full_name of author who wrote the longest book :
--------------------------------------------------------
--error trap :
--------------
SELECT MAX(pages), author_fname FROM books ORDER BY author_fname; -- 634 Jhumpa   false
SELECT pages, author_fname FROM books ORDER BY author_fname;      -- 634 Michael  true

-- solutions :
--------------
SELECT author_fname FROM books
WHERE pages=(SELECT MAX(pages) FROM books);                  -- by WHERE ? query

-- OR

SELECT author_fname FROM books
WHERE pages=634;                                             -- by WHERE knowed

-- OR

SELECT author_fname FROM books ORDER BY pages DESC LIMIT 1;  -- by ORDER BY     BEST

SELECT pages, author_fname FROM books ORDER BY pages DESC;   -- no LIMIT 1


SELECT released_year AS 'year', COUNT(title) AS '# books', AVG(pages) AS 'avg pages'
FROM books GROUP BY released_year ASC;

Results :

-- +-------+--------------+
-- | pages | author_fname |
-- +-------+--------------+
-- |   634 | Michael      |   to check wich one match
-- |   526 | Raymond      |
-- |   504 | Dave         |
-- +-------+--------------+

Functions Date Time

-- USE ONE CHAR SAMPLE
CREATE TABLE exot(
    sex CHAR(1), -- for f (female) or m (male)
    changed_at TIMESTAMP DEFAULT NOW()
               ON UPDATE CURRENT_TIMESTAMP -- NOW()
);

INSERT INTO exot(sex) VALUES('m');
SELECT sex FROM exot;

-- FILL IN THE BLANKS
CREATE TABLE inventories(
    item_name  VARCHAR(100)  ,
    price  DECIMAL(8,2),             -- < 1,000,000
    quantity INT(10)
);

INSERT INTO inventories(item_name, price) VALUES('book', 56.5055559);  -- 56.51
SELECT * FROM inventories;


-- DATETIME VS TIMESTAMP :
--------------------------
-- timestamp from 1970 and can use ON UPDATE CURRENT_TIMESTAMP


-- print out the current time :
-------------------------------
SELECT CURTIME();


-- print out the current date no time :
---------------------------------------
SELECT CURDATE();


-- The current day of the week :
--------------------------------
SELECT DAYOFWEEK(CURDATE());     -- 2


-- current day of the week name :
---------------------------------
SELECT DAYNAME(CURDATE());       -- Monday
SELECT DATE_FORMAT(NOW(), '%W'); -- same


-- format mm/dd/yyyy :
----------------------
SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y'); -- 11/27/2017


-- current day and time as January 2nd at 3:15 | April 1st at 10:18 :
---------------------------------------------------------------------
SELECT CONCAT(
DAYNAME(CURDATE()), ' ',
DAYOFWEEK(CURDATE()),
'nd at ',
DATE_FORMAT(CURTIME(), '%l:%i')               -- Monday 2nd at 12:00
);
-- OR
SELECT DATE_FORMAT(NOW(), '%M %D At chez anne-marie %h:%i');  -- Shortly !


-- create a tweets table tweet content, username, time it was created :
-----------------------------------------------------------------------
CREATE TABLE tweets(
tweet_content VARCHAR(255),
username VARCHAR(100),
datedo TIMESTAMP DEFAULT NOW()
);

INSERT INTO tweets(tweet_content, username)
VALUES('hello world', 'Gilius');

SELECT * FROM tweets;

Functions String

SELECT REVERSE(UPPER('Why does my cat look at me with such hatred?'));


SELECT REPLACE(
CONCAT('I', ' ', 'like', ' ', 'cats'),
' ', '-'
);
-- I-like-cats


SELECT REPLACE(title, ' ', '->') AS 'Link' FROM books;
-- Norse->Mythology


SELECT author_fname AS 'forwards', REVERSE(author_fname) AS 'backwards' FROM books;
-- +----------------------+
-- | forwards | backwards |
-- +----------+-----------+
-- | Jhumpa   | apmuhJ    |


SELECT CONCAT(UPPER(author_fname), ' ', UPPER(author_fname))
AS 'Full name in cap' FROM books;
-- +------------------+
-- | Full name in cap |
-- +------------------+
-- | JHUMPA JHUMPA    |


SELECT CONCAT(title, ' was realised in ', released_year) AS 'blurb' FROM books;
-- +--------------------------------------------------------------------------+
-- | blurb                                                                    |
-- +--------------------------------------------------------------------------+
-- | The Namesake was realised in 2003                                        |


SELECT title, CHAR_LENGTH(title) AS 'caracter count' FROM books;
-- +-----------------------------------------------------+----------------+
-- | title                                               | caracter count |
-- +-----------------------------------------------------+----------------+
-- | The Namesake                                                      12 |


SELECT
CONCAT(SUBSTRING(title, 1, 10), '...') AS 'short title',
CONCAT(author_fname, ',', author_lname) AS 'author',
CONCAT(stock_quantity, ' in stock') AS 'quantity'
FROM books;

Results :

-- +---------------+----------------------+--------------+
-- | short title   | author               | quantity     |
-- +---------------+----------------------+--------------+
-- | The Namesa... | Jhumpa,Lahiri        | 32 in stock  |
-- | Norse Myth... | Neil,Gaiman          | 43 in stock  |
-- +---------------+----------------------+--------------+




Logical Operations

SELECT 10 != 10;  -- 0
SELECT 15 > 14 && 99 - 5 <= 94; -- 1
SELECT 1 IN (5,3) || 9 BETWEEN 8 AND 10; -- 1


-- select all books written before 1980 (non inclusive) :
---------------------------------------------------------
SELECT title, released_year FROM books
WHERE released_year<1980;


-- select all books writen by Eggers or Chabon :
------------------------------------------------
SELECT title, author_lname FROM books
where author_lname='Eggers' || author_lname='Chabon';

SELECT title, author_lname FROM books
where author_lname IN('Eggers', 'Chabon');


-- select all books writteh by lahiri, published after 2000 :
-------------------------------------------------------------
SELECT title, author_lname FROM books
WHERE author_lname='Lahiri' AND released_year>2000 ;


-- select all books with page counts betwee100 and 200 :
--------------------------------------------------------
SELECT title, pages FROM books WHERE pages>=100 AND pages<=200;
SELECT title, pages FROM books WHERE pages BETWEEN 100 AND 200;


-- select all books where author_lname starts with a 'C' or an 'S' :
--------------------------------------------------------------------
SELECT title,author_lname FROM books
WHERE author_lname LIKE 'C%' OR author_lname LIKE 'S%';
-- +-----------------------------------------------------+--------------+
-- | title                                               | author_lname |
-- +-----------------------------------------------------+--------------+
-- | The Amazing Adventures of Kavalier & Clay           | Chabon       |
-- | Just Kids                                           | Smith        |
-- | What We Talk About When We Talk About Love: Stories | Carver       |
-- | Where I'm Calling From: Selected Stories            | Carver       |
-- | Cannery Row                                         | Steinbeck    |
-- | Lincoln In The Bardo                                | Saunders     |
-- | Lincoln In The Bardo                                | Saunders     |
-- +-----------------------------------------------------+--------------+


SELECT title, pages, author_lname,
CASE
    WHEN pages BETWEEN 0 AND 200 THEN 'Short Stories'
    WHEN pages BETWEEN 201 AND 400 THEN 'Novel'
    ELSE 'Memoir'
END AS 'TYPE'
FROM books;
-- +-----------------------------------------------------+-------+----------------+---------------+
-- | title                                               | pages | author_lname   | TYPE          |
-- +-----------------------------------------------------+-------+----------------+---------------+
-- | American Gods                                       |   465 | Gaiman         | Memoir        |
-- | Interpreter of Maladies                             |   198 | Lahiri         | Short Stories |
-- | Coraline                                            |   208 | Gaiman         | Novel         |
-- | Where I'm Calling From: Selected Stories            |   526 | Carver         | Memoir        |
-- | White Noise                                         |   320 | DeLillo        | Novel         |
-- | Cannery Row                                         |   181 | Steinbeck      | Short Stories |
-- +-----------------------------------------------------+-------+----------------+---------------+


SELECT title, author_lname,
CASE
    WHEN title LIKE '%Stories%' THEN 'Short Stories'
    WHEN title LIKE '%just kids%' OR title LIKE '%A heartbreaking work%' THEN 'Memoir'
    ELSE 'Novel'
END AS 'TYPE'
FROM books;
-- +-----------------------------------------------------+----------------+---------------+
-- | title                                               | author_lname   | TYPE          |
-- +-----------------------------------------------------+----------------+---------------+
-- | The Amazing Adventures of Kavalier & Clay           | Chabon         | Novel         |
-- | A Heartbreaking Work of Staggering Genius           | Eggers         | Memoir        |
-- | Oblivion: Stories                                   | Foster Wallace | Short Stories |
-- +-----------------------------------------------------+----------------+---------------+


SELECT title,author_lname, COUNT(DISTINCT title)
AS 'Number Books' FROM books GROUP BY author_lname, author_fname;

Results :

-- +-------------------------------------------+----------------+--------------+
-- | title                                     | author_lname   | Number Books |
-- +-------------------------------------------+----------------+--------------+
-- | Where I'm Calling From: Selected Stories  | Carver         |            2 |
-- | The Amazing Adventures of Kavalier & Clay | Chabon         |            1 |
-- | Dan                                       | Dan            |            3 |
-- | White Noise                               | DeLillo        |            1 |
-- | A Heartbreaking Work of Staggering Genius | Eggers         |            3 |
-- | Oblivion: Stories                         | Foster Wallace |            2 |
-- | fake_book                                 | Freida         |            1 |
-- | Coraline                                  | Gaiman         |            3 |
-- | Interpreter of Maladies                   | Lahiri         |            2 |
-- +-------------------------------------------+----------------+--------------+




Selecteurs

-- FIND TITLES THAT COUNTAIN 'stories' :
----------------------------------------
SELECT title FROM books WHERE title LIKE '%stories%';


-- FIND THE LONGEST BOOK :
--------------------------
SELECT title, pages FROM books ORDER BY pages DESC LIMIT 1;


-- FIND author_lname that countains a space :
---------------------------------------------
SELECT title, author_lname FROM books WHERE author_lname LIKE '% %';


-- SORTED by author_lame and title :
------------------------------------
SELECT title, author_lname FROM books ORDER BY author_lname, title;


-- SORTED by + concat string :
------------------------------
SELECT CONCAT('MY FAVORITE AUTHOR IS ', UPPER(author_fname), ' ', UPPER(author_lname), '!')
AS 'yell' FROM books
ORDER BY author_lname;


-- FIND THE 3 MOST RECENTS BOOKS :
----------------------------------
SELECT CONCAT(title, ' - ', released_year) AS 'summary' FROM books
ORDER BY released_year DESC LIMIT 3;


-- 	FIND 3 books where stock less :
-----------------------------------
SELECT title, released_year, stock_quantity FROM books
ORDER BY stock_quantity LIMIT 3;

Results :

+-----------------------------------------------------+---------------+----------------+
| title                                               | released_year | stock_quantity |
+-----------------------------------------------------+---------------+----------------+
| Where I'm Calling From: Selected Stories            |          1989 |             12 |
| American Gods                                       |          2001 |             12 |
| What We Talk About When We Talk About Love: Stories |          1981 |             23 |
+-----------------------------------------------------+---------------+----------------+




Relationships :

-- create tables students and paper with join id

CREATE TABLE students(
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100)
);

CREATE TABLE papers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    grade VARCHAR(100),
    student_id INT,
    FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE
);  -- MUL in phpmyadmin

INSERT INTO students (first_name) VALUES
('Caleb'), ('Samantha'), ('Raj'), ('Carlos'), ('Lisa');

INSERT INTO papers (student_id, title, grade ) VALUES
(1, 'My First Book Report', 60),
(1, 'My Second Book Report', 75),
(2, 'Russian Lit Through The Ages', 94),
(2, 'De Montaigne and The Art of The Essay', 98),
(4, 'Borges and Magical Realism', 89);


SELECT first_name, title, grade FROM students
JOIN papers
ON papers.student_id = students.id
ORDER BY grade DESC;
-- +------------+---------------------------------------+-------+
-- | first_name | title                                 | grade |
-- +------------+---------------------------------------+-------+
-- | Samantha   | De Montaigne and The Art of The Essay | 98    |
-- | Samantha   | Russian Lit Through The Ages          | 94    |
-- | Carlos     | Borges and Magical Realism            | 89    |
-- | Caleb      | My Second Book Report                 | 75    |
-- | Caleb      | My First Book Report                  | 60    |
-- +------------+---------------------------------------+-------+


SELECT first_name, title, grade FROM students
LEFT JOIN papers
ON papers.student_id = students.id;
-- +------------+---------------------------------------+-------+
-- | first_name | title                                 | grade |
-- +------------+---------------------------------------+-------+
-- | Caleb      | My First Book Report                  | 60    |
-- | Caleb      | My Second Book Report                 | 75    |
-- | Samantha   | Russian Lit Through The Ages          | 94    |
-- | Samantha   | De Montaigne and The Art of The Essay | 98    |
-- | Raj        | NULL                                  | NULL  |
-- | Carlos     | Borges and Magical Realism            | 89    |
-- | Lisa       | NULL                                  | NULL  |
-- +------------+---------------------------------------+-------+


SELECT
first_name,
IFNULL(title, 'Missing') AS 'Title',
IFNULL(grade, '0') AS 'Grade'
FROM students
LEFT JOIN papers
ON papers.student_id = students.id
ORDER BY students.id;
-- +------------+---------------------------------------+-------+
-- | first_name | Title                                 | Grade |
-- +------------+---------------------------------------+-------+
-- | Caleb      | My First Book Report                  | 60    |
-- | Caleb      | My Second Book Report                 | 75    |
-- | Samantha   | Russian Lit Through The Ages          | 94    |
-- | Samantha   | De Montaigne and The Art of The Essay | 98    |
-- | Raj        | Missing                               | 0     |
-- | Carlos     | Borges and Magical Realism            | 89    |
-- | Lisa       | Missing                               | 0     |
-- +------------+---------------------------------------+-------+


SELECT first_name, IFNULL(AVG(grade),0) AS Average
FROM students
LEFT JOIN papers
ON papers.student_id = students.id
GROUP BY students.id
ORDER BY Average DESC;
-- +------------+---------------+
-- | first_name | Moyenne Grade |
-- +------------+---------------+
-- | Samantha   |            96 |
-- | Carlos     |            89 |
-- | Caleb      |          67.5 |
-- | Lisa       |             0 |
-- | Raj        |             0 |
-- +------------+---------------+


SELECT first_name,
       AVG(grade) AS 'Moyenne Grade',
CASE
    WHEN AVG(grade) IS NULL THEN 'FAILING'
    WHEN AVG(grade) >= 75 THEN 'PASSING'
    ELSE 'FAILING'
END AS 'Passing_status'
FROM students
LEFT JOIN papers
ON papers.student_id = students.id
GROUP BY first_name
ORDER BY grade DESC;
-- +------------+---------------+----------------+
-- | first_name | Moyenne Grade | Passing_status |
-- +------------+---------------+----------------+
-- | Samantha   |            96 | PASSING        |
-- | Carlos     |            89 | PASSING        |
-- | Caleb      |          67.5 | FAILING        |
-- | Lisa       |             0 | FAILING        |
-- | Raj        |             0 | FAILING        |
-- +------------+---------------+----------------+


SELECT
first_name AS 'Eleves',
IFNULL(title, 'Missing') AS 'Devoirs',
IFNULL(AVG(grade), '0') AS 'Note Global',
CASE
    WHEN AVG(grade) IS NULL THEN 'Refus'
    WHEN AVG(grade) <= 75 THEN 'Refus'
    ELSE 'Accepté !'
END AS 'Diplôme'
FROM students
LEFT JOIN papers
ON papers.student_id = students.id
GROUP BY first_name
ORDER BY grade DESC;

Results :

+----------+------------------------------+-------------+------------+
| Eleves   | Devoirs                      | Note Global | Diplôme   |
+----------+------------------------------+-------------+------------+
| Samantha | Russian Lit Through The Ages | 96          | Accepté !  |
| Carlos   | Borges and Magical Realism   | 89          | Accepté !  |
| Caleb    | My First Book Report         | 67.5        | Refus      |
| Lisa     | Missing                      | 0           | Refus      |
| Raj      | Missing                      | 0           | Refus      |
+----------+------------------------------+-------------+------------+

« CONTACT

Cette page a été vue  335 fois !