MySQL for business developer - Titouan BENOIT

Software

titouan-benoit
of 41
Description
Text
  • MySQL for business developer Titouan BENOIT – CTO at Welp.fr titouan.benoit@gmx.fr
  • MySQL for business developer– Titouan BENOIT Titouan BENOIT – CTO at Welp – titouan@welp.today – titouan.benoit@gmx.fr MySQL for business developer - v1.0 2 Titouan BENOIT mailto:titouan@welp.today mailto:titouan.benoit@gmx.fr
  • Plan MySQL for business developer - v1.0 3 MySQL for business developer– Titouan BENOIT 1. Introduction 2. Query Language 3. Basics – SELECT – WHERE – ORDER BY – DISTINCT – LIMIT & OFFSET 4. Advanced – JOIN – Sub-queries – Math functions – COUNT, SUM, AVG 5. phpMyAdmin 6. Examples/Exercices
  • MYSQL FOR BUSINESS DEVELOPER 1. Introduction 4
  • MySQL is an Open Source Relational Database Management System (RDBMS) – SQL (Structured Query Language) databases – The second most used in the world – Main purpose: storing data In this presentation you will learn to: – Make basics queries to search data into MySQL databases – Extract data from MySQL databases – Use PHPMyAdmin – Compute statistics on data (count, average, …) 1. Introduction MySQL for business developer - v1.0 5 MySQL for business developer– Titouan BENOIT
  • 1. Introduction: structure MySQL for business developer - v1.0 6 MySQL for business developer– Titouan BENOIT MySQL Server Databases Welp OHA … Tables needs propositions users … id title author_id … 1 Mon titre1 5 … 2 Mon titre2 84 … 3 Mon titre3 6 … … … … … data Columns Rows
  • MYSQL FOR BUSINESS DEVELOPER 2. Query Language 7
  • SQL (Structured Query Language) With SQL we can: – Insert data – Make queries (select data) – Update data – Delete data – Create and modify schema (tables) – Manage data access control 2. Query Language MySQL for business developer - v1.0 8 MySQL for business developer– Titouan BENOIT
  • MYSQL FOR BUSINESS DEVELOPER 3. Basics 9
  • SELECT * FROM database.table; – Select all colums from table in the database – Tips: • USE database; SELECT * FROM table SELECT column1, column2, … FROM table Alias: SELECT user.email FROM fos_user AS user Example: – SELECT id, email, first_name, last_name FROM welp_db.fos_user 3. Basics: SELECT MySQL for business developer - v1.0 10 MySQL for business developer– Titouan BENOIT
  • SELECT * FROM table WHERE condition; – Select all colums from table in the database where condition is true SELECT first_name, evaluation_average FROM fos_user WHERE evaluation_average = 5 – Select users where their average is equal to 5 Comparison operators Logical Operators 3. Basics: WHERE MySQL for business developer - v1.0 11 MySQL for business developer– Titouan BENOIT Operator Description = equal < less than greater than >= greater than or equal to or != different, not equal Equal (also to NULL value) Operator Symbol Description AND && AND OR || OR XOR Exclusive OR NOT ! NOT Example:
  • SELECT * FROM table ORDER BY column1; – Select all colums from table and order by column1 descending order (default behavior) SELECT * FROM table ORDER BY column1 DESC; – Descending order SELECT * FROM table ORDER BY column1 ASC; – Ascending order SELECT * FROM table ORDER BY column1, column2, …; – Multicolumn order – Note: the order of column after ORDER BY statement is important. The order will be done on the column1 and then column2, … 3. Basics: ORDER BY MySQL for business developer - v1.0 12 MySQL for business developer– Titouan BENOIT
  • SELECT DISTINCT column FROM table; – Return only different rows (one by ‘column’ occurrences) Example: – SELECT DISTINCT species FROM animal; – If I have 300 dogs, 400 cats and 250 elephants, this query will return: • Dog • Cat • Elephant – SELECT DISTINCT first_name FROM fos_user ORDER BY first_name ASC 3. Basics: DISTINCT MySQL for business developer - v1.0 13 MySQL for business developer– Titouan BENOIT
  • SELECT * FROM table LIMIT 0,10; – Return 10 rows from the beginning (row number 0). – How to use limit: LIMIT [offset, ]number_of_lines – Note: the LIMIT statement must be at the end Other syntaxe: – LIMIT number_of_lines [OFFSET offset]; Examples 3. Basics: LIMIT & OFFSET MySQL for business developer - v1.0 14 MySQL for business developer– Titouan BENOIT
  • MYSQL FOR BUSINESS DEVELOPER 4. Advanced 15
  • INNER JOIN – SELECT need.title, need.category_id, category.id, category.name FROM need INNER JOIN category ON need.category_id = category.id ORDER BY need.created_at LIMIT 10; – Note: inner join does not select rows where the relation is null (here where need has no category for example) 4. Advanced: JOIN MySQL for business developer - v1.0 16 MySQL for business developer– Titouan BENOIT Need table Category table need category INNER JOIN
  • LEFT JOIN – SELECT user.first_name, media.id AS mediaID, media.name AS avatar FROM fos_user AS user LEFT JOIN media ON user.avatar_id = media.id ORDER BY user.created_at DESC LIMIT 10, 10 4. Advanced: JOIN MySQL for business developer - v1.0 17 MySQL for business developer– Titouan BENOIT User table Media table media LEFT JOIN
  • RIGHT JOIN – SELECT category.id as categoryID, category.name AS categoryName, event.id AS eventID, event.title AS eventTitle FROM event RIGHT JOIN category ON event.category_id = category.id 4. Advanced: JOIN MySQL for business developer - v1.0 18 MySQL for business developer– Titouan BENOIT Category table Event table Difference with LEFT JOIN This category has no event This event has no category RIGHT JOIN LEFT JOIN
  • Sub-queries – In FROM statement: • SELECT goodEvaluation.comment, goodEvaluation.evaluation, goodEvaluation.evaluator_id FROM ( SELECT * FROM evaluation WHERE evaluation.evaluation = 5 ) as goodEvaluation WHERE goodEvaluation.evaluator_id = 19 – In WHERE clause: • SELECT id, title, category_id FROM need WHERE category_id IN ( SELECT id FROM category WHERE name IN ('Actions à plusieurs', 'Visites de courtoisie') ); 4. Advanced: sub-queries MySQL for business developer - v1.0 19 MySQL for business developer– Titouan BENOIT The sub-query:
  • Math functions – CEIL(n) or CEILING(n): Return the smallest integer value not less than the argument – FLOOR(n): Return the largest integer value not greater than the argument – ROUND(n, d): Round the argument (d number of decimal) – TRUNCATE(n, d): truncate the argument (d number of decimal) – POW(n, e) or POWER(n, e): n^e – SQRT(n): square root of the argument – MOD(n, div): modulo of the division n/div – RAND(): return a random value between 0 and 1 – SIGN(): return -1 for 0 and negative values and 1 for positive value – ABS(): absolute value – PI() – COS() – SIN() 4. Advanced: Math functions MySQL for business developer - v1.0 20 MySQL for business developer– Titouan BENOIT
  • Aggregate functions: – COUNT(), SUM(), AVG(), MAX(), MIN() SELECT COUNT(*) as nbNeed FROM need WHERE need.category_id = 1 SELECT AVG(fos_user.evaluation_average) as avgEvaluation FROM fos_user GROUP BY & HAVING – SELECT u.id, u.first_name, COUNT(n.author_id) need_count FROM fos_user u LEFT JOIN need n ON n.author_id = u.id GROUP BY u.id HAVING need_count > 0 ORDER BY need_count DESC 4. Advanced: COUNT, SUM, AVG MySQL for business developer - v1.0 21 MySQL for business developer– Titouan BENOIT
  • MYSQL FOR BUSINESS DEVELOPER 5. phpMyAdmin 22
  • Databases 5. phpMyAdmin MySQL for business developer - v1.0 23 MySQL for business developer– Titouan BENOIT
  • Tables 5. phpMyAdmin MySQL for business developer - v1.0 24 MySQL for business developer– Titouan BENOIT
  • Table structure 5. phpMyAdmin MySQL for business developer - v1.0 25 MySQL for business developer– Titouan BENOIT
  • Browse data 5. phpMyAdmin MySQL for business developer - v1.0 26 MySQL for business developer– Titouan BENOIT
  • SQL query 5. phpMyAdmin MySQL for business developer - v1.0 27 MySQL for business developer– Titouan BENOIT
  • Export data 5. phpMyAdmin MySQL for business developer - v1.0 28 MySQL for business developer– Titouan BENOIT
  • MYSQL FOR BUSINESS DEVELOPER 6. Examples/Exercices 29
  • Requirements: – Connect to Welp phpMyAdmin – Login to prod with the read only user 1 slide with the problem at stake and the next slide the query Good luck & have fun! 6. Examples/Exercices MySQL for business developer - v1.0 30 MySQL for business developer– Titouan BENOIT
  • 1°/ Retrieve the id, title and the category_id of the 10 last created_at need 6. Examples/Exercices MySQL for business developer - v1.0 31 MySQL for business developer– Titouan BENOIT
  • 1°/ SOLUTION SELECT id, title, category_id, created_at FROM need ORDER BY created_at DESC LIMIT 10 6. Examples/Exercices MySQL for business developer - v1.0 32 MySQL for business developer– Titouan BENOIT
  • 2°/ Retrieve the id, first_name and the rank of fos_user which has a rank more than or equal to 2 and less than 5 and sort by rank (descending) 6. Examples/Exercices MySQL for business developer - v1.0 33 MySQL for business developer– Titouan BENOIT
  • 2°/ SOLUTION SELECT id, first_name, rank FROM fos_user WHERE rank >= 2 AND rank < 5 ORDER BY rank DESC 6. Examples/Exercices MySQL for business developer - v1.0 34 MySQL for business developer– Titouan BENOIT
  • 3°/ Retrieve all the distinct place_locality as city of the need and sort them by alphabetical order 6. Examples/Exercices MySQL for business developer - v1.0 35 MySQL for business developer– Titouan BENOIT
  • 3°/ SOLUTION SELECT DISTINCT(place_locality) as city FROM need ORDER BY city ASC 6. Examples/Exercices MySQL for business developer - v1.0 36 MySQL for business developer– Titouan BENOIT
  • 4°/ Retrieve the 12 last created need with their category. Display the need id, title and the category id and name. 6. Examples/Exercices MySQL for business developer - v1.0 37 MySQL for business developer– Titouan BENOIT
  • 4°/ SOLUTION SELECT need.id as needID, need.title as needTitle, need.category_id, category.id as categoryID, category.name as categoryName FROM need INNER JOIN category ON need.category_id = category.id ORDER BY need.created_at DESC LIMIT 12 6. Examples/Exercices MySQL for business developer - v1.0 38 MySQL for business developer– Titouan BENOIT
  • 5°/ Retrieve fos_user id, first_name, last_name and for each user display their number of welpActions. Hint: use GROUP BY user.id 6. Examples/Exercices MySQL for business developer - v1.0 39 MySQL for business developer– Titouan BENOIT
  • 5°/ SOLUTION SELECT user.id, user.first_name, user.last_name, COUNT(welp_action.user_id) as WelpActions FROM fos_user as user LEFT JOIN welp_action ON user.id = welp_action.user_id GROUP BY user.id ORDER BY WelpActions DESC 6. Examples/Exercices MySQL for business developer - v1.0 40 MySQL for business developer– Titouan BENOIT
  • MySQL for business developer– Titouan BENOIT MySQL for business developer – https://en.wikipedia.org/wiki/MySQL – https://en.wikipedia.org/wiki/SQL – http://dev.mysql.com/ – https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql – http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html – http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html – http://dev.mysql.com/doc/refman/5.7/en/join.html – https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html – http://dev.mysql.com/doc/refman/5.7/en/subqueries.html – http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html MySQL for business developer - v1.0 41 SOURCES https://en.wikipedia.org/wiki/MySQL https://en.wikipedia.org/wiki/MySQL https://en.wikipedia.org/wiki/SQL https://en.wikipedia.org/wiki/SQL http://dev.mysql.com/ http://dev.mysql.com/ https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.7/en/join.html http://dev.mysql.com/doc/refman/5.7/en/join.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html http://dev.mysql.com/doc/refman/5.7/en/subqueries.html http://dev.mysql.com/doc/refman/5.7/en/subqueries.html http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html
Comments
Top