MySQL

What's inside

Lessons go from the basics to advanced topics. Read them in order, or jump to what you need.

1. 01

   Chapter 1

   Getting Started

   • 1MySQL IntroductionMeet databases, tables and the SQL language, and discover MySQL: one of the world's most popular database systems, and why it's worth learning.
   • 2Install and Set Up MySQLInstall the MySQL server and a graphical tool such as MySQL Workbench or the XAMPP bundle, then connect to the server from the command line with mysql -u root -p.
   • 3Create a DatabaseCreate, list, select and delete databases with CREATE DATABASE, SHOW DATABASES, USE and DROP DATABASE.
   • 4Create a TableCreate tables with CREATE TABLE, choose a suitable type for each column, then inspect your work with SHOW TABLES and DESCRIBE.
   • 5MySQL Data TypesChoose the right type for every column: numbers, text, dates and booleans, and learn why this choice matters.
2. 02

   Chapter 2

   Working with Data

   • 6MySQL INSERT INTOAdd new rows to a table with INSERT INTO: one row or many at once, with or without listing the columns, and even copy rows from another table.
   • 7MySQL SELECTRead data from tables with SELECT: all columns or only the ones you need, plus calculated values and comments that explain your queries.
   • 8MySQL WHEREUse WHERE to filter rows, so you get only the records that meet a specific condition.
   • 9MySQL OperatorsCombine conditions with AND, OR and NOT, and write powerful filters with IN, BETWEEN, LIKE and IS NULL.
   • 10MySQL ORDER BYSort query results in ascending or descending order with ORDER BY, by one column or several.
   • 11MySQL LIMITReturn only a specific number of rows with LIMIT, and skip rows with OFFSET to split results into pages.
   • 12MySQL SELECT DISTINCTRemove duplicate values from your results with SELECT DISTINCT, and count the different values with COUNT(DISTINCT ...).
   • 13MySQL AliasesGive columns and tables clear, temporary names with AS to make your results and queries easier to read.
   • 14MySQL UPDATEChange existing rows with UPDATE ... SET, and always use WHERE to control which rows change.
   • 15MySQL DELETERemove rows with DELETE FROM ... WHERE, and learn the difference between DELETE, TRUNCATE and DROP.
3. 03

   Chapter 3

   Functions

   • 16Aggregate FunctionsSummarize many rows into a single value with COUNT, SUM, AVG, MIN and MAX.
   • 17MySQL GROUP BYGroup rows that share a value and apply aggregate functions to each group, such as the number of students in each city.
   • 18MySQL HAVINGFilter groups after grouping with HAVING, and understand how it differs from WHERE.
   • 19String FunctionsTransform and clean up text with CONCAT, UPPER, LOWER, LENGTH, SUBSTRING, TRIM and REPLACE.
   • 20Numeric FunctionsRound numbers and calculate with them using ROUND, CEIL, FLOOR, ABS and MOD, along with the basic arithmetic operators.
   • 21Date FunctionsWork with dates: extract the year, month and day, and count the days between two dates with DATEDIFF.
   • 22MySQL CASEAdd if-then-else logic to your queries with CASE and the shorter IF function.
   • 23NULL FunctionsUnderstand NULL (a missing value), handle it in calculations, and replace it with sensible values using IFNULL and COALESCE.
4. 04

   Chapter 4

   Relationships & Joins

   • 24MySQL ConstraintsProtect the quality of your data with constraints: NOT NULL, UNIQUE, DEFAULT, CHECK and PRIMARY KEY.
   • 25MySQL Foreign KeysLink tables with FOREIGN KEY to keep their relationships valid, and control what happens on delete with ON DELETE.
   • 26MySQL INNER JOINCombine rows from two or more tables with INNER JOIN, getting only the rows that have a match in both.
   • 27MySQL LEFT JOIN and RIGHT JOINKeep all the rows of one table, even those without a match, with LEFT JOIN and RIGHT JOIN.
   • 28MySQL Self JoinJoin a table to itself with aliases to compare rows of the same table, such as students and their mentors.
   • 29MySQL UNIONStack the results of several SELECT queries into one result with UNION and UNION ALL.
   • 30MySQL SubqueriesWrite a query inside another query: to compare with a calculated value, to search a list with IN and EXISTS, and to build derived tables.
5. 05

   Chapter 5

   Advanced Topics

   • 31MySQL ViewsSave a query as a virtual table with CREATE VIEW, then query it like any regular table.
   • 32MySQL IndexesSpeed up searches on big tables with indexes: how they work, how to create them, and when you don't need them.
   • 33MySQL ALTER TABLE and DROP TABLEChange a table's structure after creating it with ALTER TABLE: add, modify, rename and drop columns, and delete tables with DROP TABLE.
   • 34MySQL TransactionsGroup several statements into one all-or-nothing unit with START TRANSACTION, COMMIT and ROLLBACK.
   • 35Database NormalizationDesign clean tables without repetition by following the first, second and third normal forms (1NF, 2NF and 3NF).
   • 36Users and PrivilegesCreate MySQL users, give them only the privileges they need with GRANT, and take privileges away with REVOKE.
   • 37Backup and RestoreBack up your databases with mysqldump and restore them with the mysql client: the habit that saves projects.
   • 38Using MySQL in ApplicationsConnect to MySQL from PHP with PDO and from Node.js with the mysql2 package, and run prepared statements that protect your app from SQL injection.