Custom Search
 


MySQL Database How-to Articles and Tutorials

Prerequisites

1. Prerequisites for MySQL Query Practices

Database Design

1. How to Design Relational Database

2. Enforce Data Integrity by Database Constraints

3. How to Enforce Data Type Constraint

4. How to Enforce Default Constraint and Nullability Constraint

5. Primary Key Constraint and Unique Constraint

6. Foreign Key Relationships and Considerations

Sample Database

1. What is Northwind database in MySQL

2. Create Northwind database in MySQL

3. The Categories table in MySQL Northwind database

4. The Suppliers table in MySQL Northwind database

5. The Products table in MySQL Northwind database

6. The Customers table in MySQL Northwind database

7. The Employees table in MySQL Northwind database

8. The Shippers table in MySQL Northwind database

9. The Orders table in MySQL Northwind database

10. The Order Details table in MySQL Northwind database

11. SQL Views in MySQL Northwind database

Single Table SELECT Statement

1. The Basic SELECT Statement

2. Using Column Alias in SELECT Statement

3. Using Literal Character Strings

4. Perform Arithmetic Operations

5. Use WHERE Clause to Conditionally Select Rows

6. Sorting Data

7. Using LIMIT Clause to Constrain the Number of Rows Retrieved

8. Using Comparison Operators, Part I

9. Using Comparison Operators, Part II

10. Using LIKE Comparison Operator

11. Using Logical Operators

12. Eliminating Duplicate Rows

Using Single-Row Functions

1. Using String Functions, Part 1

2. Using String Functions, Part 2

3. Using String Functions, Part 3

4. Using MySQL Date and Time Functions, Part 1

5. Using MySQL Date and Time Functions, Part 2

6. Using MySQL Date and Time Functions, Part 3

7. Using MySQL Control Flow Functions

8. Using MySQL Conversion Functions

9. Using MySQL Comparison Functions

10. Using Aggregate Functions in MySQL

11. MySQL RANK() function

12. MySQL DENSE_RANK() function

13. MySQL ROW_NUMBER() function

14. Compare RANK(), DENSE_RANK(), and ROW_NUMBER() function

15. Using LIMIT and OFFSET to Page Thru Query Result in MySQL

16. Using LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to Page Thru Query Result in MySQL

17. Use LEAST function to find the SMALLEST value from multiple arguments

18. How to make case-sensitive comparison in MySQL

19. Use GREATEST function to find the LARGEST value from multiple arguments

20. MySQL MID() function with examples

21. MySQL LOCATE() function with examples

22. MySQL SUBSTR() function with examples

23. MySQL POSITION() function with examples

24. How to use MySQL REPLACE function in SELECT and UPDATE queries

25. Numeric Functions in MySQL

26. Miscellaneous Functions in MySQL

Querying Multiple Tables

1. Using Inner Joins to Combine Data from Two Tables

2. Using Outer Joins to Combine Data from Two Tables

3. Another Example for Outer Joins (three tables)

4. Using Self Joins to Combine Data from the Same Table

5. SQL Set Operators - a Visual Guide to UNION, UNION ALL, MIMUS/EXCEPT, INTERSECT

6. Using UNION to Append Result Sets

7. Create working tables to demonstrate how to mimic set operators MINUS, EXCEPT, INTERSECT in MySQL

8. How to do MINUS/EXCEPT and INTERSECT in MySQL

9. How to Simulate Full Join in MySQL - Part 1: return both matched and unmatched rows

10. How to Simulate Full Join in MySQL - Part 2: return unmatched rows only

11. How to Simulate Full Join in MySQL - Part 3: use UNION to simulate FULL JOIN

12. Use Cross Join to Combine Data in MySQL

13. Differences Between Join and Union

Using Subquery

1. What is subquery

2. Using subquery to return a single value (known as single-value subquery or scalar subquery)

3. Using subquery to return a list of values (known as column subquery)

4. Using subquery to return one or more rows of values (known as row subquery)

5. Using correlated subqueries

6. Using EXISTS and NOT EXISTS in correlated subqueries in MySQL

7. Using subquery in SELECT statement in MySQL

8. Using subquery in FROM clause in MySQL

9. JOIN a table with a subquery

Summarizing Data

1. Using Aggregate Functions and Group By Clause in MySQL

2. More About GROUP BY Clause in MySQL

3. Using GROUP BY with HAVING Clause in MySQL

4. Summarizing Data From Multiple Tables in MySQL

5. Summarizing Data From Multiple Tables in MySQL - Part 2

Data Manipulation

1. Update Statement in MySQL

2. How to update data in a specific order in MySQL

3. How to update top N rows of data in MySQL

4. Use outer join in update

5. How to do cross-table update in MySQL - Part 1

6. How to do cross-table update in MySQL - Part 2

7. Using Bulk Insert Statement

8. Basic insert statement

9. How to create and use AUTO_INCREMENT column and use LAST_INSERT_ID function

10. Combine update and insert into one statement

11. Using MySQL REPLACE (INTO) to mimic DELETE + INSERT

12. Update multiple rows (distinctive by primary key) with different values in one query

13. Update multiple rows (distinctive by primary key) for multiple columns in one query

Managing Tables

1. Create MySQL table by using CREATE TABLE statement

2. Create MySQL table by using another table

3. Create MySQL temporary table

4. How to disable (or enable) Safe Updates in MySQL

5. MySQL constraints explained by examples

Problem Solving

1. MySQL Northwind Queries - Part 1

2. MySQL Northwind Queries - Part 2

3. MySQL Northwind Queries - Part 3

4. How to Work with Two Unrelated Values

5. How to Fill Gaps in Sales Data

6. How to Calculate Totals, Subtotals and Grand Total

7. How to Work with NULL Values

8. How to fill down empty cells with values from a previous non-empty row

9. Use RANK function to update a previous record within a group or partition

10. Two ways to add a unique number or ID to each row

11. 3 ways to get Top N rows from MySQL

12. How to generate Cumulative Sum (running total) by MySQL - Part 1

13. How to generate Cumulative Sum (running total) by MySQL - Part 2

Stored Procedures

User Defined Functions


Copyright © 2024 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy