ebook img

MySQL Lab Guide - Cengage Learning: Transforming Learning PDF

129 Pages·2007·4.37 MB·English
by  
Save to my drive
Quick download
Download
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview MySQL Lab Guide - Cengage Learning: Transforming Learning

MySQL Lab Guide A supplement to: Database Systems: Design, Implementation and Management (International Edition) Rob, Coronel & Crockett (ISBN: 9781844807321) MySQL Lab Guide Table of Contents Lab Title Page 1 Introduction to MySQL 4 2 Building a database: Table by Table 15 3 Data Manipulation Commands 36 4 Basic SELECT statements 47 5 Advanced SELECT statements 63 6 Joining Tables 75 7 SQL functions 90 8 Subqueries 1 9 Views 123 Keeley Crockett 2 MySQL Lab Guide Introduction to the MySQL Lab Guide This lab guide is designed to provide examples and exercises in the fundamentals of SQL within the MySQL environment. The objective is not to develop full blown applications but to illustrate the concepts of SQL using simple examples. The lab guide has been divided up into 9 sessions. Each one comprises of examples, tasks and exercises about a particular concept in SQL and how it is implemented in MySQL. On completion of this 9 week lab guide you will be able to: • Create a simple relational database in MySQL. • Insert, update and delete data the tables. • Create queries using basic and advanced SELECT statements • Perform join operations on relational tables • Use aggregate functions in SQL • Write subqueries • Create views of the database This lab guide assumes that you know how to perform basic operations in the Microsoft Windows environment. Therefore, you should know what a folder is, how to maximize or minimize a folder, how to create a folder, how to select a file, how you maximize and minimize windows, what clicking and double-clicking indicate, how you drag, how to use drag and drop, how you save a file, and so on. MySQL, is one of the most popular Open Source SQL database management systems. The lab guide has been designed on MySQL version 5.0.45 running on Windows XP Keeley Crockett 3 MySQL Lab Guide Professional. The MySQL Web site (http://www.mysql.com/) provides the latest information about MySQL database management system. It is important to note that MySQL is an open source database and is continually under development. Each version and sub-version may implement SQL syntax differently and changes are being made constantly. There are also problems with upward compatibility between different versions. For example some SQL operations that work in versions 3.0 and 4.0 do not work in version 5.0. Furthermore, different variants of a version are released in response to bugs that have been found by database developers who are using the latest versions in their work. If an SQL command does not work as expected or shown in this guide, please consult the MySQL web site for more information. Keeley Crockett 4 MySQL Lab Guide Lab 1: Starting MySQL The learning objectives of this lab are to • Learn how to start MySQL • Learn how to use the MySQL command line client window • Obtain help in MySQL 1.1 Starting MySQL Before starting this guide, you must obtain a user ID and a password created by your database administrator in order to log on to the MySQL RDBMS. How you connect to the MySQL database depends on how the MySQL software was installed on your server and on the access paths and methods defined and managed by the database administrator. You may therefore need to follow specific instructions provided by your instructor, College or University. This section will describe how to start MySQL from a Windows XP installation of MySQL 5.0.45. To start MySQL you would: 1. Select the Start button 2. Select All Programs and then MySQL 3. Select MySQL Server 5.0 4. Click on the MySQL Command line client Keeley Crockett 5 MySQL Lab Guide The MySQL command line client window should then open as shown in Figure 1. Figure 1: MySQL command line client window Enter your password to log on to MySQL as shown in Figure 2. Keeley Crockett 6 MySQL Lab Guide Figure 2: Logging on to MySQL Once you have successfully logged on you will see the opening screen as shown in Figure 2. To work in MySQL requires you to type in commands. For example typing in the following will show you a list of help commands shown in Figure 3: mysql> \h Keeley Crockett 7 MySQL Lab Guide Figure 3: Help commands in MySQL Figure 3 shows some additional sources of help available from three different websites. It also displays a list of commands and the shortcuts for running these commands. If you want help about a specific command you can type the word help followed by the name of the command. For example to display information about how to create a database you would type: mysql> help create database Keeley Crockett 8 MySQL Lab Guide Figure 4 shows the results of executing this command. Figure 4: Example Help command A full list of help topics available through the command line can be found by first typing: mysql> help contents However to get more detailed help you would use the MySQL reference manual. If you are using MySQL from a Windows XP installation, then you can access the manual via the programs menu as shown in Figure 5. Keeley Crockett 9 MySQL Lab Guide Figure 5: Accessing the MySQL Reference Manual Figure 6 shows the table of contents for the reference manual. Keeley Crockett 10

See more

The list of books you might like

Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.