Course Detail

MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages. MySQL uses a standard form of the well-known SQL data language. MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.

1. Getting started with MySQL.

(a) What is data management?

(b) Why is it important?

2. Basics of MYSQL.

(a) DDL - Data Definition Language

(b) DML - Data Manipulation Language

(c) DCL- Data Control Language

(d) TCL- Transaction Control Language

3. MySQL data types.

(a) MySQL data types – show you various data types in MySQL so that you can apply them effectively in designing database tables.

(b) INT – show you how to use integer data type.

(c) DECIMAL – show you how to use DECIMAL datatype to store exact values in decimal format.

(d) BOOLEAN – explain to you how MySQL handles Boolean values by using TINYINT(1) internally.

(e) CHAR – a guide to CHAR data type for storing the fixed-length string.

(f) VARCHAR – give you the essential guide to VARCHAR datatype.

(g) TEXT – show you how to store text data using TEXT datatype.

(h) DATE – introduce you to the DATE datatype and show you some date functions to handle the date data effectively.

(i) TIME – walk you through the features of TIME datatype and show you how to use some useful temporal functions to handle time data.

(j) DATETIME – introduce you to the DATETIME datatype and some useful functions to manipulate DATETIME values.

4. Querying data - This section helps you learn how to query data from the MySQL database server. We will start with a simple SELECT statement that allows you to query data from a single table.

(a) SELECT – show you how to use a simple SELECT statement to query the data from a single table

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

(a) INSERT – use various forms of the INSERT statement to insert data into a table.

(b) INSERT Multiple Rows – insert multiple rows into a table.

(c) INSERT INTO SELECT – insert data into a table from the result set of a query.

(d) UPDATE – learn how to use the UPDATE statement and its options to update data in database tables.

(e) UPDATE JOIN – show you how to perform the cross-table update using the UPDATE JOIN statement with INNER JOIN and LEFT JOIN. DELETE – show you how to use the DELETE statement to delete rows from one or more tables.

(f) ON DELETE CASCADE – learn how to use ON DELETE CASCADE referential action for a foreign key to delete data from a child table automatically when you delete data from a parent table.

(g) DELETE JOIN – show you how to delete data from multiple tables.

(h) REPLACE – learn how to insert or update data depending on whether data exists in the table or not.

(i) Prepared Statement – show you how to use the prepared statement to execute a query.

6. Managing MySQL databases and tables

(a) Selecting a MySQL database – show you how to use the USE statement to select a MySQL database via the mysql program and MySQL Workbench.

(b) Managing databases – learn various statements to manage MySQL databases including creating a new database, removing an existing database, selecting a database, and listing all databases.

(c) CREATE DATABASE – show you how to create a new database in MySQL Server. • DROP DATABASE – learn how to delete an existing database.

(d) CREATE TABLE – show you how to create new tables in a database using CREATE TABLE statement.

(e) ALTER TABLE – learn how to use the ALTER TABLE statement to change the structure of a table.

(f) Renaming table – show you how to rename a table using RENAME TABLE statement.

(g) Removing a column from a table – show you how to use the ALTER TABLE DROP COLUMN statement to remove one or more columns from a table.

(h) Adding a new column to a table – show you how to add one or more columns to an existing table using ALTER TABLE ADD COLUMN statement.

(i) DROP TABLE – show you how to remove existing tables using the DROP TABLE statement.

(j) TRUNCATE TABLE – show you how to use the TRUNCATE TABLE statement to delete all data in a table fast.

7. MySQL constraints

(a) NOT NULL constraint – introduce you to the NOT NULL constraint and show you how to declare a NOT NULL column or add a NOT NULL constraint to an existing column.

(b) Primary key constraint – guide you on how to use the primary key constraint to create the primary key for a table.

(c) Foreign key constraint – introduce you to the foreign key and show you step by step how to create and drop foreign keys.

(d) UNIQUE constraint – show you how to use the UNIQUE constraint to enforce the uniqueness of values in a column or a group of columns in a table.

(e) CHECK constraint – learn how to create CHECK constraints to ensure data integrity.

8. Filtering data

(a) WHERE – learn how to use the WHERE clause to filter rows based on specified conditions.

(b) SELECT DISTINCT – show you how to use the DISTINCT operator in the SELECT statement to eliminate duplicate rows in a result set.

(c) AND – introduce you to the AND operator to combine Boolean expressions to form a complex condition for filtering data.

(d) OR– introduce you to the OR operator and show you how to combine the OR operator with the AND operator to filter data.

(e) IN – show you how to use the IN operator in the WHERE clause to determine if a value matches any value in a list or a subquery.

(f) BETWEEN – show you how to query data based on a range using BETWEEN operator.

(g) LIKE – provide you with a technique to query data based on a specific pattern. • LIMIT – use LIMIT to constrain the number of rows returned by SELECT statement

(h) IS NULL – test whether a value is NULL or not by using IS NULL operator.

9. Sorting data

(a) ORDER BY – show you how to sort the result set using the ORDER BY clause. The custom sort order with the FIELD function will be also covered.

10. Joining tables

(a) Table Column Aliases – introduce you to table and column aliases.

(b) Joins – give you an overview of joins supported in MySQL including inner join, left join, and right join.

(c) INNER JOIN – query rows from a table that has matching rows in another table.

(d) LEFT JOIN – return all rows from the left table and matching rows from the right table or null if no matching rows are found in the right table.

(e) RIGHT JOIN – return all rows from the right table and matching rows from the left table or null if no matching rows are found in the left table.

(f) CROSS JOIN – make a Cartesian product of rows from multiple tables.

(g) Self-join – join a table to itself using table alias and connect rows within the same table using inner join and left join.

11. Grouping data

(a) GROUP BY – show you how to group rows into groups based on columns or expressions.

(b) HAVING – filter the groups by a specific condition.

(c) ROLLUP – generate multiple grouping sets considering a hierarchy between columns specified in the GROUP BY clause

12. Subqueries

(a) Subquery – show you how to nest a query (inner query) within another query (outer query) and use the result of the inner query for the outer query.

(b) Derived table – introduce you to the derived table concept and show you how to use it to simplify complex queries.

(c) EXISTS – test for the existence of rows.

13. Set operators

(a) UNION and UNION ALL – combine two or more result sets of multiple queries into a single result set.

(b) INTERSECT – show you a couple of ways to simulate the INTERSECT operator.

(c) MINUS – explain to you the SQL MINUS operator and show you how to simulate it.

14. MySQL transaction

(a) Transaction – learn about MySQL transactions, and how to use COMMIT and ROLLBACK to manage transactions in MySQL.