MySQL Introduction-Intermediate

Database concepts

• What is a database?
• Database management systems
• Tables, rows and columns
• Indexes, primary keys, unique constraints and
• foreign keys
• Client-Server architecture
• Supported data types
• Storage engines and table types
• The Information_Schema and MySQL
• Databases
• Exercise: Using a MySQL Database

USING THE mysql CLIENT

• What is the MySQL client?
• Getting started and Logging in
• Selecting a database
• Client commands
• Entering and executing SQL statements
• Retrieving previous commands
• Creating, editing and executing SQL files
• Redirecting output into a file
• Command line execution of MySQL Scripts
• Exercise: Using the MySQL Client

Basic select

• The SQL SELECT statement
• Case sensitivity
• Quotes
• Statement terminator
• Syntax conventions
• The select clause
• The FROM clause
• Conditions and the WHERE clause
• Logical operators
• The ORDER BY clause
• Column aliases
• Arithmetic expressions
• Precedence of operators
• Exercises: Querying the sample database

Limit, union and aggregate functions

• The LIMIT clause
• UNION
• UNION ALL
• Aggregate functions
• The GROUP BY clause
• Using Rollup with GROUP BY
• The HAVING clause
• Exercises: Using Limit, Union and Aggregate Functions

Subqueries and joins

• Subqueries
• Cartesian products
• Joins with original syntax
• Table aliases
• Natural joins
• Join using
• Join on
• Multi-table joins
• Exercises: Using Subqueries and Joins

Numeric and character functions

• Function types
• Testing functions
• Numeric functions
• Character functions
• Exercise: Using Numeric and Character Functions

Date, time and other functions

• Date and time column types
• Date and time formats
• The date format function
• Functions to return date time
• Functions to extract components from date time
• Date time arithmetic
• Miscellaneous functions
• Exercise: Using Date, Time and other functions

Databases and tables

• Creating a database
• Selecting a database
• Creating tables
• Auto_increment
• Show create table
• Column operations
• Constraint operations
• Copying tables
• Renaming tables
• Changing engine for tables
• Dropping tables
• Temporary tables
• Exercises: Maintaining databases and tables
• Indexes and views
• What is an index?
• Creating an index
• Reviewing indexes
• Dropping indexes
• What is a view?
• Creating views
• View restrictions
• Dropping views
• Exercise: Maintaining indexes and views

Date, time and other functions

• Date and time column types
• Date and time formats
• The date format function
• Functions to return date time
• Functions to extract components from date time
• Date time arithmetic
• Miscellaneous functions
• Exercise: Using Date, Time and other functions

Databases and tables

• Creating a database
• Selecting a databasee
• Creating tables
• Auto_increment
• Show create table
• Column operations
• Constraint operations
• Copying tables
• Renaming tables
• Changing engine for tables
• Dropping tables
• Temporary tables
• Exercises: Maintaining databases and tables

Indexes and views

• What is an index?
• Creating an index
• Reviewing indexes
• Dropping indexes
• What is a view?
• Creating views
• View restrictions
• Dropping views
• Exercise: Maintaining indexes and views

Managing data

• Inserting rows
• Replacing rows
• Updating rows
• Deleting rows
• The truncate statement
• The COMMIT and ROLLBACK commands
• Savepoints
• Implicit commits

Access control

• Creating users
• Renaming users
• Dropping users
• Granting privileges
• Revoking privileges
• Exercise: Creating users, granting and revoking

Import and export

• Exporting using SQL
• Importing using SQL
• Exporting from the command line
• Importing from the command line
• Exercise: Export and import data