-
Learning by doing
-
Trainers with practical experience
-
Classroom training
-
Detailed course material
-
Clear content description
-
Tailormade content possible
-
Training that proceeds
-
Small groups
In the course Queries with MySQL participants learn the syntax and use of the query language SQL in the context of a MySQL database. SQL or Standard Query Language is an ANSI and ISO standard of which the basic syntax can be used in all relational database management systems (DBMS). There are however differences in the SQL implementations and this course focuses specifically on SQL in MySQL.
The course starts with an introduction of relational databases, the installation of MySQL and available tooling such as the MySQL Workbench. Also discussed are the different storage engines and the available connectors.
Next the SQL Standard is discussed. Step by step the different parts of SQL such as Data Definition Language with CREATE TABLE, Data Manipulation Language with INSERT and UPDATE are treated.
Attention is then paid to the creation and manipulation of database tables with Primary Keys, Auto Increment, Indexes and Constraints on Columns. The relationship between tables with Foreign keys is also treated. And changing the table structure with ALTER TABLE and deleting tables with DROP is on the course program as well.
Next Data Manipulation with INSERT and UPDATE Queries is discussed. The course content also deals with passing parameters to queries by means of Prepared Statements.
Retrieving data with SELECT queries is a central part of the course program. The various clauses of SELECT such as FROM, WHERE, ORDER BY, BETWEEN, LIKE are covered. Also grouping data by means of GROUP and HAVING clauses is treated.
MySQL also has many functions that can be applied to perform calculations on data and to filter data. In addition to the standard SQL functions, the many MySQL specific functions are covered.
Next the course program discusses how to combine data from different tables by means of joins. MySQL has many types of joins to combine data from tables and many of them are covered.
Finally attention is paid to the application of SET operators such as UNION and INTERSECT and the discussion of transactions with commits and rollbacks and stored procedures.
The course Queries with MySQL is designed for developers, database administrators and other interested parties who wish to learn and use MySQL SQL.
This course has no specific requirements. General knowledge of system development and databases is beneficial to a good understanding.
The theory is treated on the basis of presentation slides. Demos are used to explain the theory. There is ample opportunity to practice. The course times are from 9.30 to 16.30.
Participants receive an official certificate Queries with MySQL after successful completion of the course.
Module 1 : MySQL Introduction |
Module 2 : SQL Introduction |
Module 3 : Data Definition |
|
MySQL Database MySQL Installation MySQL versus MariaDB MySQL Admin Database View MySQLWorkBench MySQL Storage Engines mysql Prompt MySQL Notifier Available Connectors MySQL versions MySQL Documentation |
SQL Foundations SQL Standard Relationships Primary Keys Foreign Keys SQL Query Types DDL Queries DML Queries MySQL Data Types Numeric Data Types String Data Types Date and Time Types |
CREATE TABLE Unique Constraint MySQL Sequences Column Attributes Auto Increment Columns AUTO_INCREMENT attribute LAST_INSERT_ID() NOT NULL Attribute KEY and INDEX ALTER TABLE DROP COLUMN DROP TABLE |
Module 4 : Data Manipulation |
Module 5 : SQL Queries |
Module 6 : Grouping |
|
Data Manipulation Language Transaction Control Inserting Rows INSERT Statement Prepared Statements Updating Rows UPDATE Statement Check Constraint DELETE statement TRUNCATE TABLE Renaming Tables Locking Tables |
Selecting Rows SELECT Statement FROM Clause Specifying Conditions WHERE Clause Sorting with ORDER BY NULLs, FIRST, LAST Removing Doubles BETWEEN and IN ANY Clause ALL Clause Using LIKE |
GROUP BY Clause Filtering Groups Group by Single Column Group By More Columns HAVING Clause Operators String Operators Date Operators Concatenate Operator MySQL IF Statement MySQL CASE Statement IF versus CASE |
Module 7 : Functions |
Module 8 : Joins |
Module 9 : Advanced Topics |
|
MySQL Aggregate Functions AVG, COUNT, SUM MIN, MAX, GROUP_CONCAT STD Functions STD, STDDEV, STDDEV_POP MySQL String Functions CONCAT, REPLACE, SUBSTRING Control Flow Functions Date and Time Functions Conversion Functions MySQL String Length Cast Operation |
Joining Multiple Tables ANSI Join Syntax Normal Joins Natural Join Left Join Right Join Full Join Full Outer Join Inner Join ANSI Outer Join Cross Join Multiple Join Conditions |
Combining Results SET Operatoren MySQL UNION UNION without Alias UNION ALL INTERSECT Transaction Control Commit and Rollback Implicit rollbacks Implicit commits Stored Procedures Passing Parameters |
These companies already made use of our courses:
These companies used our courses:
