My SQL

Duration :: 32 Hrs
Introduction MySql
* Overview of MySql tools and capabilities
* Review of relational database concepts
* Database design tips
* Creating database and users
* Users and Privileges
* Setting up a user for the web
* Creating tables
* Mysql Identifiers
* Column Data Types
* Inserting, Retrieving, Updating and Deleting data in the Database.
Scaling MySQL
* Selecting columns and rows from database tables
* Queries over multiple tables
* Combining where and column choice
* Examining a MySQL database
* Using SQL insert queries to add data with and without column names
* Rearranging columns with insert
* Inserting several rows at once
* Using the SQL update statement to change existing data in a table
* Using the SQL delete statement to remove data from a table
* Counting rows with the SQL count function
* Finding the largest and smallest items (SQL min and max functions)
* Finding averages (SQL avg function)
* Rows with missing data (null values)
* Finding rows with missing data (SQL is null and is not null tests)
* Sorting result rows (SQL order by clause, sorting in ascending or descending order with asc and desc)
* Using column-name aliases for long-winded column names in select
* Simple joins across multiple tables
* SQL summary
A introduction to database design
* Creating a database (SQL create database statement)
* Creating a simple table (SQL create table statement)
* Text types (e. g., varchar(255))
* Primary keys, identifying numbers
* integer not null auto_increment primary key
* Cross-table linking (matching foreign keys to primary keys)
* Changing the type of a a column (SQL alter table statement)
* A non-entity table
* Junction tables (auxillary tables to enable many to many' joins)
Database design
* Data types
* Text types (SQL varchar and char, MySQL specific mediumtext and longtext)
* Binary column types (MySQL specific mediumblob and longblob)
* Relationships between tables ( one to many' and many to many')
* Unique IDs (including MySQL specific extension auto_increment)
* Primary and foreign keys
* not null type qualifier
* Joining across many-to-many relationships
Getting started with the MySQL server
* The MySQL suite of programs
* Obtaining MySQL
* Installing and configuring MySQL
* The MySQL data directory
* Default directories for binary installs
* How mysqld provides access to data
* MySQL database file types
* Starting up and shutting down the server on Unix and Windows
* MySQL logging and log files
* The error log
* The general query log
* The binary update log
* Log file locations
Privileges in MySQL
* Users and privileges
* MySQL users
* Local and remote users
* The MySQL specific user() function
* Controlling access rights with SQL
* Using the SQL grant statement
* grant with wildcards
* Granting multiple privileges
* Setting passwords for users (SQL grant statement with identified by clause)
* Revoking privileges (SQL revoke statement)
* Granting the grant privilege itself
* show grants
* Grant tables
* flush privileges
MySQL Development
* MySQL Development
* Subqueries in MySQL
* Character Sets and Collation
* Spatial Data and OpenGIS
* Spatial Columns
* Spatial Functions
* Spatial Indexed
* MySQL Product Objectives
* Development stages
Further MySQL queries
* Aliases for column names, table names and computed values
* Getting only distinct results (SQL select statement with distinct qualifier)
* Counting distinct rows
* Limiting the number of results (SQL limit statement)
* Limiting updates
* Specifying limit and start position
* Creating tables from query results (SQL create table statement with select clause)
* Creating temporary tables
* Replacing rows
* Copying rows into an existing table
* Replacing rows in a table from a query
* Arithmetic operators and functions
* String manipulation functions
* Storing dates and times
* Timestamp values
* Time related functions
* Increasing and decreasing dates and times
* Using and - operators with dates
* Formatting dates and times for output
* Unix time values
Advanced MySQL queries
* Aggregate queries
* Grouping rows together
* Using group by
* Multiple aggregate functions
* Grouping by multiple fields
* Using group by with other where
* Sorting group by queries
* Using group by with multiple tables
* More multi-table group by queries
* Selecting groups by their aggregate value
* where and having
* where and having example
* Inner joins (SQL inner join syntax)
* Inner joins on matching field names (natural joins)
* Left joins (SQL left join syntax)
* Left joins with multiple matching rows
* Right joins
* Equivalence of left and right joins
* Full outer joins
* Components of a select query
* Subselects
* Left joins instead of subselects
* MySQL & subselects
* Using temporary tables for difficult queries
* create temporary table syntax
* Transactions
* Atomic operations
* Locking tables
* Table locking details
* Table locking with aliases
Storage Enginges and Table Types
* Storage Engines
* MyISAM
* InnoDB
* MERGE Tables
* Berkley DB Tables
* HEAP (MEMORY) Tables
* NBD Cluster Engine
* InnoDB Transaction Support
* Performing Transactions
* InnoDB Differences from MyISAM
Optimising tables and queries
* Indexes in MySQL
* Primary keys and unique keys
* Creating primary keys
* Primary keys over multiple columns
* Creating tables with unique keys
* Non-unique indexes
* Adding an index while creating a table
* Adding indexes to existing tables
* Finding out how MySQL will execute a query
* Using explain to analyse queries
* Interpreting the output of explain
* Interpreting the join' type
* explain when an index can be used
* Differences in the output of explain
* When not to use indexes
Using the Command-Line Tools
* Why use the Command Line?
* The MySQL Command-Line Tool
* MySQL Command-Line Options
Conclusion