Sue Brandreth's Learning Resources

Published on 29 Aug 2014
Cheat Sheet / Transcript is Here : http://goo.gl/Z1p50M
Best MySQL Book : http://goo.gl/zJC254
Welcome to my MySQL video tutorial. I’ll cover 95 – 98% of everything you’ll ever need to know in one video.
I cover creating / destroying databases, creating / destroying tables, data types, NULL, DEFAULT, ENUM, AUTO_INCREMENT, primary keys, foreign keys, atomic data, normalized, DESCRIBE, INSERT, ALTER, SELECT, SHOW, RENAME, WHERE, logical operators, comparison operators, ORDER BY, GROUP BY, LIMIT, string operators, joins, LIKE, DISTINCT, math functions and more.

---

MySQL is the most popular Open Source Relational SQL database management system. MySQL is one of the best RDBMS being used for developing web-based software applications.

This tutorial will give you quick start with MySQL and make you comfortable with MySQL programming.

---

MySQL Tutorial
(http://www.mysqltutorial.org/)

Welcome to the MySQL Tutorial website!
You will learn MySQL fast, easy and fun. This website provides you with a complete MySQL tutorial presented in an easy-to-follow manner. Each tutorial has practical examples with SQL script and screenshots available.

Are you new here? If so, first, you may want to find out what MySQL is. Then, you can install MySQL database server, download MySQL sample database and load the sample database into your own local MySQL database server to get started with MySQL quickly.

MySQL Tutorial for Developers

Are you a web developer looking for learning MySQL fast?

In this section, you will be getting started with MySQL right away. You will learn how to use MySQL effectively to get your work done more productively.

If you go through the entire tutorials, you will know how to manage MySQL databases and manipulate data using various techniques such as SQL queries, MySQL stored procedures, database views, triggers, etc.

MySQL Tutorial for Database Administrators

This step-by-step tutorial gives you in-depth background information on MySQL administration.

We cover everything from basic to advanced MySQL administration and configuration. All MySQL administration tutorials presented in this section are practical and widely used in enterprise environments.

MySQL Programming Interfaces

In this section, we will show you how to write MySQL-based applications for accessing MySQL databases.

MySQL Tutorial References

If you need more information on MySQL, check it out the MySQL resources.

---

Basic MySQL Tutorial
(http://www.mysqltutorial.org/basic-mysql-tutorial.aspx)

Download MySQL Sample Database (classicmodels)

Download MySQL Sample Database Schema (classicmodels)

The MySQL sample database schema consists of the following tables:

• Customers: stores customer’s data.
• Products: stores a list of scale model cars.
• ProductLines: stores a list of product line categories.
• Orders: stores  sales orders placed by customers.
• OrderDetails: stores sales order line items for each sales order.
• Payments: stores payments made by customers based on their accounts.
• Employees: stores all employee information as well as the organization structure such as who reports to whom.
• Offices: stores sales office data.

This basic MySQL tutorial section explains some of the basic SQL statements. If this is the first time you have used a database, this section gives you the basic information you need to interact with MySQL database server such as managing databases, creating tables, and querying data. Alternatively, if you’re already familiar with other RDBMS such as PostgreSQL, Oracle, or Microsoft SQL Server, you can use this section to refresh your knowledge and also understand how SQL dialect of MySQL is different from other database management systems.

Getting started with MySQL

This section helps you get started with MySQL. We will start installing MySQL, downloading a sample database, and loading data into the MySQL server for practicing.

• Installing MySQL database server – shows you step by step how to install MySQL database server in your computer.
• Downloading MySQL sample database – introduces you to a MySQL sample database named classicmodels. We will provide you links to download the sample database and its diagram.
• Loading the sample database into your own local MySQL database server – guides you how to load the classicmodels sample database into your MySQL database server for practicing.

Querying data from MySQL database

This section helps you learn how to query data from the MySQL database sever. We will start with a simple SELECT statement that allows you to query data from a single table. And then we will demonstrate some advanced techniques to query data from multiple tables such as join and union.

• Using MySQL SELECT statement to query data – shows you how to simple SELECT statement to query the data from a single database table.
• Filtering rows using MySQL WHERE – learns how to use WHERE clause to filter rows based on specified conditions.
• Sorting Rows with MySQL ORDER BY – shows you how to sort the result set using ORDER BY clause. The custom sort order with the FIELD function will be also covered.
• Eliminating duplicate rows with DISTINCT Operator – learns how to use DISTINCT operator in the SELECT statement to eliminate duplicate rows in a result set.
• Using MySQL LIMIT to constrain the number of rows returned by SELECT statement
• Querying data with MySQL IN operator – shows you how to use IN operator in WHERE clause to determine if a value matches any value in a list or a subquery.
• Querying data with BETWEEN Operator – shows you how to query data based on a ranges using BETWEEN operator.
• Using MySQL LIKE operator to select data based on patterns – provides you with technique to query data based on specific pattern.
• Using MySQL alias to make the queries more readable – introduces you to aliases including table alias and column alias to improve the readability of complex queries and avoid ambiguous error when querying data from tables that have same column names.
• Combining result sets using MySQL UNION and UNION ALL – helps you combine two or more result sets from multiple SELECT statements into a single one using UNION and UNION ALL.
• Joining tables using INNER JOIN – applies inner join technique to query data from multiple related tables.
• Joining tables using LEFT JOIN – learns how to use left join to produce a result set that contains rows from the table on the left side of the join and fill the unmatched rows with NULL values.
• Joining a table to itself using MySQL self join – joins a table to itself using table alias, and connects rows within the same table using other joins such as inner join or left join.
• Grouping rows into subgroups using GROUP BY clause– shows you how to group rows into subgroups based on columns or expressions.
• Filtering groups using MySQL HAVING – filters the groups by a specific condition.
• Nesting a query within another using MySQL subquery – shows you how to nest a query (inner query) within another query (outer query) and use result of the inner query for the outer query. We will also introduce you to the correlated subquery concept.

Modifying data in MySQL

In this section, you will learn how to insert, update, and delete data from tables using various MySQL statements.

This section shows you how to manage the most important database objects in MySQL including database, tables, and indexes. Note that we have specific sections for stored procedures, triggers, and views.

• Managing database in MySQL – you will learn various statements to manage MySQL databases including creating a new database, removing an existing database, selecting a database, and listing all databases.
• Understanding MySQL Table Types – it is essential to understand the features of each table type so that you can use them effectively to maximize the performance of your databases.
• MySQL data types – shows you various MySQL data types so that you can apply them effectively in designing database tables.
• MySQL INT – shows you how to use integer data type. We also show you how to use ZEROFILL and display width attributes of the integer column.
• MySQL DECIMAL – shows you how to use DECIMAL data type to store exact values in decimal format.
• MySQL DATE – introduces you to the DATE data type and show you some date functions to handle the date data effectively.
• MySQL TIME – walks you through the features of TIME data type and shows you how to use some useful temporal functions to handle time data.
• MySQL DATETIME – introduces you to the DATETIME data type and some useful functions to manipulate DATETIME values.
• MySQL TIMESTAMP – introduces you to TIMESTAMP and its features called automatic initialization and automatic update that allow you to define auto-initialized and auto-updated columns for a table.
• Creating tables using MySQL CREATE TABLE statement – shows you how to create a new tables in a database using CREATE TABLE statement.
• MySQL sequence – shows you how to use sequence to generate unique numbers automatically for the primary key column of a table.
• MySQL primary key  – you will learn how to use primary key constraint to create the primary key for the table.
• MySQL foreign key – introduces you to foreign key and shows you step by step how to create and drop foreign keys.
• Using MySQL ALTER TABLE to change table structure – you will learn about the ALTER TABLE statement that changes existing table structure such as adding or removing column, changing column attribute, etc.
• Renaming table –  we will show you how to rename a table using RENAME TABLE statement.
• Removing a column from a table – shows you how to use the ALTER TABLE DROP COLUMN statement to remove one or more columns from a table.
• Adding new column to a table – this tutorial shows you how to add one or more columns to existing table using ALTER TABLE ADD COLUMN statement.
• Using MySQL DROP TABLE statement to remove tables – shows you how to remove existing tables using DROP TABLE statement.
• MySQL temporary table – discusses about MySQL temporary table and shows you step by step how to manage temporary tables.
• Managing MySQL database indexes – you will learn how to work with MySQL indexes and how to take advantages of indexes to speed up the data retrieval.
• MySQL TRUNCATE TABLE – this tutorial shows you how to use the TRUNCATE TABLE statement to delete all data in a table.

MySQL Globalization

---

Learn SQL The Hard Way
(http://sql.learncodethehardway.org/)
This book will teach you the 80% of SQL you probably need to use it effectively, and will mix in concepts in data modeling at the same time. If you’ve been fumbling around building web, desktop, or mobile applications because you don’t know SQL, then this book is for you. It is written for people with no prior database, programming, or SQL knowledge, but knowing at least one programming language will help.

Stanford University’s Database YouTube playlist (https://www.youtube.com/playlist?list=PL6hGtHedy2Z4EkgY76QOcueU8lAC4o6c3)