MySQL Examples
(Free Web Tutorials)

by Michael Thomas
<under construction>

MySQL Home Page

In this tutorial you will ....

Note: On the MySQL Home page, you can download the whole MySQL site (all content, tutorials & examples) !!!

This tutorial covers: MySQL (various versions unless specified)

Prerequisites

• Installed MySQL

Objectives

• See examples of using MySQL

<Under Construction>
Examples from Many Years Ago

Script Examples: 

Download the zip file with the source code.  Then extract into a directory.  I have listed the files and a brief descriptions of what they do.  Make sure you read the "ex_readme.txt" file with at text editor like "notepad".

• You can download all of these files on the tutorial home page.
• General notes
  • ex_readme.txt - instructions on how to run the scripts and use the batch files.
• Manage Databases & Tables
  • ex_databases.sql - Creating Databases.
  • ex_tables.sql - Creating Tables.
  • ex_columntypes.sql - Create Tables: MySql's column types.
  • ex_show.sql - examples of using the SHOW command.
  • ex_mysqlshow_databases.bat - DOS command to list all Databases (mysqlshow.exe).
• Standard Sql Statements
  • ex_update.sql - UPDATE 
  • ex_replace.sql - REPLACE
  • ex_variables.sql - example of using SET to create user Variables.
  • ex_case.sql - example of using the CASE/WHEN/ELSE expression.
  • ex_logical.sql - Logical operators: AND, OR, &&, ||, NOT, !
• Security (under construction - don't use this Security section info !!!!)
  • List of MySql Privileges (docs: 6.8)
  • List of mysql users:
    Example of default install: (big security problem!!!)
    mysql> use mysql
    mysql> select Host, User, Password from user;
    +-----------+------+----------+
    | Host      | User | Password |
    +-----------+------+----------+
    | localhost | root |          |
    | %         |      |          |
    | localhost |      |          |
    | %         | root |          |
    +-----------+------+----------+
    Example of SQL to update the Password:
    UPDATE user SET password = "password" WHERE ( user = "root" );
    Next, you need to stop and restart MySql server.
    
    Example of adding a user: (no rights)
    insert into user (host, user, password) values ("localhost","playuser","playpswd");
  • ex_security_connect_checkroot.bat - launch SQL Monitor and login as root user.  If you can connect as the root user without a password you have problems (doc: 6.1)
  • ex_security_connect_localhost_root.bat - connect to localhost as user root.  Password is prompted.

• Create & Populate tables
  • train_create.sql - Creates the Databases and then calls the scripts to create and populate the individual tables.
  • train_create_inventory.sql - Create inventory records.
  • train_create_item.sql - Create item records.
  • train_create_itemcat.sql - Create records that place items in a category.
  • train_create_itemvendor.sql - Create records that tie items to vendors.
  • train_create_itemkeyword.sql - Create Keywords for items.
  • train_create_category.sql - Create category records.
  • train_create_catsub.sql - Create records which indicate parent/child (subcategories) relationships amoung categories.
  • train_create_vendor.sql - Create vendor records.
  • train_create_price.sql - Create price records.
  • train_list_priceclass.sql - Create price class records.
• Lists of Data in tables (using Join)
  • train_list_inventory.sql - Joins listing inventory.
  • train_list_item.sql - Joins listing the items.
  • train_list_itemcat.sql - Joins listing the "itemcat" data connecting an item to one or more categories.
  • train_list_itemvendor.sql - List of items and the vendors who can supply.
  • train_list_itemkeyword.sql - List items and the associated keywords.
  • train_list_category.sql - Joins listing the categories.
  • train_list_catsub.sql - Joins listing the sub categories and their parents.
  • train_list_vendor.sql - Listing of vendors, vendor's items, etc...
  • train_list_price.sql - under construction.
  • train_list_priceclass.sql - under construction.