MySQL Lab Guide - Cengage EMEA
MySQL Lab GuideA supplement to: Database Systems: Design, Implementation and Management(International Edition)Rob, Coronel & Crockett (ISBN: 9781844807321)
MySQL Lab GuideTable of ContentsLabTitlePage1Introduction to MySQL42Building a database: Table by Table153Data Manipulation Commands364Basic SELECT statements475Advanced SELECT statements636Joining Tables757SQL functions908Subqueries1119Views123Keeley Crockett2
MySQL Lab GuideIntroduction to the MySQL Lab GuideThis lab guide is designed to provide examples and exercises in the fundamentals of SQLwithin the MySQL environment. The objective is not to develop full blown applicationsbut to illustrate the concepts of SQL using simple examples. The lab guide has beendivided up into 9 sessions. Each one comprises of examples, tasks and exercises about aparticular 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 databaseThis lab guide assumes that you know how to perform basic operations in the MicrosoftWindows environment. Therefore, you should know what a folder is, how to maximize orminimize a folder, how to create a folder, how to select a file, how you maximize andminimize windows, what clicking and double-clicking indicate, how you drag, how touse 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 XPKeeley Crockett3
MySQL Lab GuideProfessional. The MySQL Web site (http://www.mysql.com/) provides the latestinformation about MySQL database management system.It is important to note that MySQL is an open source database and is continually underdevelopment. Each version and sub-version may implement SQL syntax differently andchanges are being made constantly. There are also problems with upward compatibilitybetween different versions. For example some SQL operations that work in versions 3.0and 4.0 do not work in version 5.0. Furthermore, different variants of a version arereleased in response to bugs that have been found by database developers who are usingthe latest versions in their work. If an SQL command does not work as expected orshown in this guide, please consult the MySQL web site for more information.Keeley Crockett4
MySQL Lab GuideLab 1: Starting MySQLThe 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 MySQL1.1 Starting MySQLBefore starting this guide, you must obtain a user ID and a password created by yourdatabase administrator in order to log on to the MySQL RDBMS. How you connect tothe MySQL database depends on how the MySQL software was installed on your serverand 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 WindowsXP installation of MySQL 5.0.45.To start MySQL you would:1. Select the Start button2. Select All Programs and then MySQL3. Select MySQL Server 5.04. Click on the MySQL Command line clientKeeley Crockett5
MySQL Lab GuideThe MySQL command line client window should then open as shown in Figure 1.Figure 1: MySQL command line client windowEnter your password to log on to MySQL as shown in Figure 2.Keeley Crockett6
MySQL Lab GuideFigure 2: Logging on to MySQLOnce you have successfully logged on you will see the opening screen as shown inFigure 2. To work in MySQL requires you to type in commands. For example typing inthe following will show you a list of help commands shown in Figure 3:mysql \hKeeley Crockett7
MySQL Lab GuideFigure 3: Help commands in MySQLFigure 3 shows some additional sources of help available from three different websites. Italso displays a list of commands and the shortcuts for running these commands. If youwant help about a specific command you can type the word help followed by the name ofthe command. For example to display information about how to create a database youwould type:mysql help create databaseKeeley Crockett8
MySQL Lab GuideFigure 4 shows the results of executing this command.Figure 4: Example Help commandA full list of help topics available through the command line can be found by first typing:mysql help contentsHowever to get more detailed help you would use the MySQL reference manual. If youare using MySQL from a Windows XP installation, then you can access the manual viathe programs menu as shown in Figure 5.Keeley Crockett9
MySQL Lab GuideFigure 5: Accessing the MySQL Reference ManualFigure 6 shows the table of contents for the reference manual.Keeley Crockett10
MySQL Lab GuideFigure 6: Contents of the MySQL Reference Manual1.2 Creating Databases from script filesIn this section you will learn how to create a small database called SaleCo from a scriptfile. The SQL script file SaleCo.sql for creating the tables and loading the data in thedatabase are located in the Student CD-ROM companion. The database design for theSaleCo database is shown in Figure 7 in the form of an Entity Relationship Diagram(ERD).Keeley Crockett11
MySQL Lab GuideFigure 7 The SaleCo Database ERDBefore creating any tables, MySQL requires you to create a database by executing theCREATE DATABASE command. To create a database called SaleCo you would typethe following:mysql CREATE DATABASE SALECO;Notice that you need a semi-colon to end the command. Figure 8 shows the successfulcreation of this database.Figure 8 Creating the SaleCo DatabaseTask 1.1 Create the SALECO database as shown in Figure 8.Keeley Crockett12
MySQL Lab GuideTo check to see if your database has been created you need to use the SHOWDATABASES command which lists the databases on the MySQL server host. You willonly be able to see those databases for which you have some kind of privilege.Task 1.2 Execute the following MySQL command to show the databases that youcurrently have access to (Figure 9 is a guide only to what you should see). Checkthat you can see the SALECO database that you have just created.mysql SHOW DATABASES;Figure 9 Executing the SHOW DATABASES commandTo work with any specific database you first have to select it. When you first login toMySQL, the default database is always selected, so you need to execute the USEcommand followed by the name of the database that you want to use.Task 1.3 Execute the following MySQL command to begin using the SALECOdatabase.mysql USE SALECO;Keeley Crockett13
MySQL Lab GuideMySQL will then inform you that the database has changed.Task 1.4 To create the SaleCo database from a MySQL script file you would enterthe following command:mysql SOURCE C:\MYSQL\SALECO2.SQLNote that in order for this command to work correctly you should have copied the scriptfiles accompanying this Lab guide into the directory C:\MYSQL\. If your files are locatedin a different directory then change the path accordingly.The command SOURCE will load and execute the script to create the SaleCo database.Notice that prompts will indicate that tables are being created and data added as shown inFigure 10. When the script has completed executing, use the SHOW TABLES commandas shown in Figure 10, to check if all five tables have been created.Figure 10. Creating the SaleCo databaseKeeley Crockett14
MySQL Lab GuideNoteWhen you run the script for the first time, you will see some error messages on thescreen. These error messages are caused by the script attempting to DROP thedatabase tables before they have been created. Including SQL DROP commands in ascript that is being used for development is a good idea to ensure that if changes aremade to the database structure, all tables are then recreated to reflect this change. Ifyou run the script again you will see that the error messages no longer appear.NoteChapter 8 Introduction to Structured Query Language and Chapter 9, Advanced SQLshould be studied alongside this lab guide.Keeley Crockett15
MySQL Lab GuideLab 2: Building a database: Table by TableThe learning objectives of this lab are to Create table structures using MySQL data types Apply SQL constraints to MySQL tables Create a simple index2.1 IntroductionIn this section you will learn how to create a small database called Theme Park from theERD shown in Figure 11. This will involve you creating the table structures in MySQLusing the CREATE TABLE command. In order to do this, appropriate data types willneed to be selected from the data dictionary for each table structure along with anyconstraints that have been imposed (e.g. primary and foreign key). Converting any ERmodel to a set of tables in a database requires following specific rules that govern theconversion. The application of those rules requires an understanding of the effects ofupdates and deletions on the tables in the database. You can read more about these rulesin Chapter 8, Introduction to Structured Query Language, and Appendix D, Convertingan ER Model into a Database Structure.2.2 The Theme Park DatabaseFigure 11 shows the ERD for the Theme Park database which will be used throughoutthis lab guide.Keeley Crockett16
MySQL Lab GuideFigure 11 The Theme park Database ERDTable 2.1 Shows the Data Dictionary for the Theme Park database which will be used tocreate each table structure.Keeley Crockett17
MySQL Lab GuideTable 2.1 Data Dictionary for the Theme Park DatabaseTableAttributeNameNameContentsData PARKdPARK CODEPark codeVARCHAR(10)XXXXXXXXNAYPARK NAMEPark NameVARCHAR(XXXXXXXXNAYNAYXXNAY##0000 – 9999YVARCHAR(4)XXXXNANPK35)PARK CITYCityVARCHAR(50)PARK COUNTRCountryCHAR(2)YEMPLOYEEEMP NUMEmployeeNUMERIC(4)PKnumberEMP TITLEEmployeetitleEMP LNAMELast nameVARCHAR(15)XXXXXXXXNAYEMP FNAMEFirst NameVARCHAR(15)XXXXXXXXNAYEMP DOBDate ofDATEDD-MON-YYNAYHire dateDATEDD-MON-YYNAYArea codeVARCHAR(4)XXXXNAYEMP PHONEPhoneVARCHAR (12)XXXXXXXXNAYPARK CODEPark codeVARCHAR(10)XXXXXXXXNAYBirthEMP HIRE DATEEMP AREACODEFKTHEMEPARKKeeley Crockett18
MySQL Lab GuideTICKETTICKET 0.00 –YnumberTICKET PRICEPrice0000.00TICKET TYPEType herPARK CODEPark RACT XXXXXXXXNAYFKnumberPARK CODEPark codeTHEMEPARKATTRACT NAMNameVARCHAR(35)XXXXXXXN/ANATTRACT AGEAgeNUMERIC(3)###Default 0YATTRACT CAPCapacityNUMERIC(3)###N/AY##0000 – 9999YEACITYHOURSEMP NUMEmployeeNUMERIC(4)numberATTRACT NOAttractionNumber ofTRACThoursHOUR RATEDATE rly RateNUMERIC(4,2)####.##N/AYDateDATEDD-MON-YYN/AYPK /ATTRACTIFKONworkedKeeley CrockettEMPLOYEEFKnumberHOURS PER ATPK /19
MySQL Lab GuideSALESTRANSACTIONTransactionNONoPARK CODEPark FKTHEMEPARKSALESLINESALE DATEDate of ERIC###########N/AYNONoLINE NOLinePK mberTICKET NOTicketFKTICKETnumberLINE QTYQuantityNUMERIC(4)####N/AYLINE PRICEPrice of lineNUMERIC(9,2)#########.##N/AY2.3 Data Types in MySQLIn order to build tables in MySQL you will need to specify the data type for each column.Table 2.2 shows some of the most common data types. If you have previously used anORACLE DBMS, you will notice that the syntax is different.Table 2.2 Common MySQL data types 1Data tores up to 255 characters. If the content is smaller than thefield size, the content will have trailing spaces appended.VARCHAR(size)fieldNameVARCHAR(100)Stores up to 255 characters, and a minimum of 4 characters. Notrailing spaces are appended to the end of this datatype.1This table was adapted from the web site http://www.developerfusion.co.uk/. A comprehensive andcomplete list of types can be taken from the MySQL Reference Manual.Keeley Crockett20
MySQL Lab GuideMySQL keeps track of a delimiter to keep track of the end ofthe field.TINYTEXTfieldNameTINYTEXTStores up to 255 characters. Equivalent to VARCHAR(255).TEXTfieldName TEXTStores up to 65,535 characters. An Index can be created on thefirst 255 characters of a field with this data type.MEDIUMTEXTfieldNameMEDIUMTEXTStores up to 16,777,215 characters. An Index can be created onthe first 255 characters of a field with this data es', 'No')Stores up to 4,294,967,295 characters. An Index can be createdon the first 255 characters of a field with this data type.Note: The maximum size of a string in MySQL is currently 16million bytes, so this data types is not useful at the moment.Stores up to 65,535 enumerated types. The DEFAULT modifiermay be used to specify the default value for this field.Stores a signed or unsigned integer number. Unsigned integershave a range of 0 to 4,294,967,295, and signed integers have arange of -2,147,438,648 to 2,147,438,647. By default, the INTdata type is signed. To create an unsigned integer, use theUNSIGNED attribute.fieldName INT UNSIGNEDINTfieldName INTThe ZEROFILL attribute may be used to left-pad any of theinteger with zero's.fieldName INT ZEROFILLThe AUTO INCREMENT attribute may be used with any ofthe Integer data types. The following example could be used tocreate a primary key using the AUTO INCREMEMNTattribute.fieldName INT UNSIGNED AUTO INCREMENT PRIMARYKEYTINYINTfieldNameTINYINTStores a signed or unsigned byte. Unsigned bytes have a rangeof 0 to 255, and signed bytes have a range of -128 to 127. Bydefault, the TINYINT data type is signed.MEDIUMINTfieldNameMEDIUMINTStores a signed or unsigned medium sized integer. Unsignedfields of this type have a range of 0 to 1,677,215, and signedfields of this type have a range of -8,388,608 to 8,388,607. Bydefault, the MEDIUMINT data type is signed.BIGINTStores a signed or unsigned big integer. Unsigned fields of thistype have a range of 0 to 18,446,744,073,709,551,615, andfieldName BIGINT signed fields of this type have a range of 9,223,372,036,854,775,808 to 9,223,327,036,854,775,807. Bydefault, the BIGINT data type is signed.Keeley Crockett21
MySQL Lab GuideFLOATfieldName FLOATUsed for single precision floating point numbers.DOUBLEfieldNameDOUBLEUsed for double precision floating point numbers.DATEfieldName DATEStores dates in the format YYYY-MM-DD.TIMESTAMP(size)fieldNameDATETIMEStores dates and times in the format YYYY-MM-DDHH:MM:SS.Automatically keeps track of the time the record was lastammended. The following table shows the formats dependingon the size of 2YYYYMMDDHHMM14YYYYMMDDHHMMSSfieldName TIMEStores times in the format HH:MM:SS.Stores the year as either a 2 digit number, or a 4 digit number,fieldName YEAR(4)depending on the size provided.2.4 Creating the Table StructuresUse the following SQL commands to create the table structures for the Theme Parkdatabase. Enter each one separately to ensure that you have no errors. Successful tablecreation will prompt MySQL to say “Query OK”. It is useful to store each correct tablestructure in a script file, in case the entire database needs to be recreated again at a laterdate. You can use a simple text editor such as notepad in order to do this. Save the file asthemepark.sql. Note that the table-creating SQL commands used in this example arebased on the data dictionary shown in Table 2.1 and the MySQL data types in Table 2.2.Keeley Crockett22
MySQL Lab GuideAs you examine each of the SQL table-creating command sequences in the followingtasks, note the following features: The NOT NULL specifications for the attributes ensure that a data entry will bemade. When it is crucial to have the data available, the NOT NULL specificationwill not allow the end user to leave the attribute empty (with no data entry at all). The UNIQUE specification creates a unique index in the respective attribute. Useit to avoid duplicated values in a column. The primary key attributes contain both a NOT NULL and a UNIQUEspecification. Those specifications enforce the entity integrity requirements. If theNOT NULL and UNIQUE specifications are not supported, use PRIMARY KEYwithout the specifications. The entire table definition is enclosed in parentheses. A comma is used to separateeach table element (attributes, primary key, and foreign key) definition. The DEFAULT constraint is used to assign a value to an attribute when a new rowis added to a table. The end user may, of course, enter a value other than thedefault value. In MYSQL the default value must be a constant; it cannot be afunction or an expression. This means, for example, that you cannot set thedefault for a date column to be the value of a function such as the system date likeyou can do in an ORACLE DBMS.Keeley Crockett23
MySQL Lab GuideNoteYou will have learnt in Chapter 8 that referential integrity is usually implementedthrough the use of foreign keys. For a long time, the open-source MySQL RDBMS didnot support the use of foreign keys. However, given the importance of maintainingreferential integrity within the database this feature was introduced in later versionsthrough the InnoDB table engine. The InnoDB engine provides MySQL with an ACID(Atomicity, Consistency, Isolation, Durability) compliant storage engine that hasfacilities such as commit and rollback. Full information about the InnoDB engine canbe found in the MySQL Reference manual 5.0. The FOREIGN KEY CONSTRAINT is used to enforce referential integrity. Inorder to set up a foreign key relationship between two MySQL tables, threeconditions must be met:1.Both tables must be of the InnoDB table type - see the note box.2.The fields used in the foreign key relationship must be indexed.3.The fields used in the foreign key relationship must be similar in data type.Keeley Crockett24
MySQL Lab GuideNoteMySQL 5.0 does not support the use of CHECK constraints which is used tovalidate data when an attribute value is entered.2.4.1 Creating the THEMEPARK Database.Task 2.1 At the MySQL prompt; create a database called Theme Park as shown in Lab 1.Then select the database for use as shown in Figure 12.Figure 12 Creating and using the Theme Park Database.2.4.2 Creating the THEMEPARK TABLETask 2.2 Enter the following SQL command to create the THEMEPARK table.CREATE TABLEKeeley CrockettTHEMEPARK (25
MySQL Lab GuidePARK CODEVARCHAR(10) PRIMARY KEY,PARK NAMEVARCHAR(35) NOT NULL,PARK CITYVARCHAR(50) NOT NULL,PARK COUNTRYCHAR(2) NOT NULL);Notice that when you create the THEMEPARK table structure you set the stage for theenforcement of entity integrity rules by using:PARK CODEVARCHAR(10) PRIMARY KEY,As you create this structure, also notice that the NOT NULL constraint is used to ensurethat the columns PARK NAME, PARK CITY and PARK COUNTRY does not acceptnulls.Remember to store this CREATE TABLE structure in your themepark.sq script.2.4.3 Creating the EMPLOYEE TABLETask 2.3 Enter the following SQL command to create the EMPLOYEE table.CREATE TABLE EMPLOYEE (EMP NUMNUMERIC(4) PRIMARY KEY,EMP TITLEVARCHAR(4),EMP LNAMEVARCHAR(15) NOT NULL,EMP FNAMEVARCHAR(15) NOT NULL,EMP DOBDATE NOT NULL,EMP HIRE DATE DATE,Keeley Crockett26
MySQL Lab GuideEMP AREA CODE VARCHAR(4) NOT NULL,EMP PHONEVARCHAR(12) NOT NULL,PARK CODEVARCHAR(10),INDEX(PARK CODE),CONSTRAINTFK EMP PARK FOREIGN KEY(PARK CODE) REFERENCESTHEMEPARK(PARK CODE));As you look at the CREATE TABLE sequence, note that referential integrity has beenenforced by specifying a constraint called FKP EMP PARK. In order to use foreign keyconstraints in MySQL, notice that the PARK CODE column is first indexed. Thisforeign key constraint definition ensures that you cannot delete a Theme Park from theTHEMEPARK table if at least one employee row references that Theme Park and thatyou cannot have an invalid entry in the foreign key column.Remember to store this CREATE TABLE structure in your themepark.sql script.2.4.4 Creating the TICKET TABLETask 2.4 Enter the following SQL command to create the TICKET table.CREATE TABLE TICKET (TICKET NOKeeley CrockettNUMERIC(10) PRIMARY KEY,27
MySQL Lab GuideTICKET PRICENUMERIC(4,2) DEFAULT 00.00 NOT NULL,TICKET TYPEVARCHAR(10),PARK CODEVARCHAR(10),INDEX(PARK CODE),CONSTRAINTFK TICKET PARK FOREIGN KEY(PARK CODE)REFERENCES THEMEPARK(PARK CODE));As you create the TICKET table, notice that both PRIMARY and FOREIGN KEYconstraints have been applied. Remember to store this CREATE TABLE structure inyour themepark.sq script.2.4.5 Creating the ATTRACTION TABLETask 2.5 Enter the following SQL command to create the ATTRACTION table.CREATETABLE ATTRACTION (ATTRACT NONUMERIC(10) PRIMARY KEY,ATTRACT NAMEVARCHAR(35),ATTRACT AGENUMERIC(3) DEFAULT 0 NOT NULL,ATTRACT CAPACITYNUMERIC(3) NOT NULL,PARK CODEVARCHAR(10),INDEX(PARK CODE),Keeley Crockett28
MySQL Lab GuideCONSTRAINTFK ATTRACT PARK FOREIGN KEY(PARK CODE)REFERENCES THEMEPARK(PARK CODE));Remember to store this CREATE TABLE structure in your themepark.sq script.2.4.6 Creating the HOURS TABLETask 2.6 Enter the following SQL command to create the HOURS table.CREATE TABLE HOURS (EMP NUMNUMERIC(4),ATTRACT NONUMERIC(10),HOURS PER ATTRACTNUMERIC(2) NOT NULL,HOUR RATENUMERIC(4,2) NOT NULL,DATE WORKEDDATE NOT NULL,INDEX(EMP NUM),INDEX(ATTRACT NO),CONSTRAINTPK HOURS PRIMARY KEY(EMP NUM, ATTRACT NO,DATE WORKED),CONSTRAINTFK HOURS EMP FOREIGN KEY (EMP NUM)REFERENCES EMPLOYEE(EMP NUM),Keeley Crockett29
MySQL Lab GuideCONSTRAINTFK HOURS ATTRACT FOREIGN KEY (ATTRACT NO)REFERENCES ATTRACTION(ATTRACT NO));As you create the HOURS table, notice that the HOURS table contains FOREIGN KEYSto both the ATTRACTION and the EMPLOYEE table.Remember to store this CREATE TABLE structure in your themepark.sq script.2.4.7 Creating the SALES TABLETask 2.7 Enter the following SQL command to create the SALES table.CREATE TABLE SALES (TRANSACTION NONUMERIC PRIMARY KEY,PARK CODEVARCHAR(10),SALE DATEDATE NOT NULL,INDEX(PARK CODE),CONSTRAINTFK SALES PARK FOREIGN KEY(PARK CODE)REFERENCES THEMEPARK(PARK CODE));Remember to store this CREATE TABLE structure in your themepark.sq script.2.4.8 Creating the SALESLINE TABLETask 2.8 Enter the following SQL command to create the SALES LINE table.Keeley Crockett30
MySQL Lab GuideCREATE TABLE SALES LINE (TRANSACTION NONUMERIC,LINE NONUMERIC(2,0) NOT NULL,TICKET NONUMERIC(10) NOT NULL,LINE QTYNUMERIC(4) DEFAULT 0 NOT NULL,LINE PRICENUMERIC(9,2) DEFAULT 0.00 NOT NULL,INDEX(TRANSACTION NO),INDEX(TICKET NO),CONSTRAINTPK SALES LINEPRIMARY KEY(TRANSACTION NO,LINE NO),CONSTRAINTFK SALES LINE SALES FOREIGN KEY(TRANSACTION NO) REFERENCES SALES(TRANSACTION NO) ON DELETECASCADE,CONSTRAINTFK SALES LINE TICKET FOREIGN KEY (TICKET NO)REFERENCES TICKET(TICKET NO));As you create the SALES LINE table, examine the constraint calledFK SALES LINE SALES. What is the purpose of ON DELETE CASCADE?Remember to store this CREATE TABLE structure in your themepark.sq script.2.5. Creating IndexesKeeley Crockett31
MySQL Lab GuideYou learned in Chapter 3, “The Relational Database Model,” that indexes can be used toimprove the efficiency of searches and to avoid duplicate column values. Using theCREATE INDEX command, SQL indexes can be created on the basis of any selectedattribute. For example, based on the attribute EMP LNAME stored in the EMPLOYEEtable, the following command creates an index named EMP LNAME INDEX:CREATE INDEX EMP LNAME INDEX ON EMPLOYEE(EMP LNAME(8));In MySQL, indexes can only be created using only the leading part of column values. Soin the example an index is created using the first 8 characters of the EMP LNAMcolumn.Task 2.9 Create the EMP LNAME INDEX shown above. Add the CREATE INDEXSQL command to your script file themepark.sql.The DROP TABLE command permanently deletes a table (and thus its data) from thedatabase schema. When you write a script file to create a database schema, it is useful toadd DROP TABLE commands at the start of the file. If you need to amend the tablestructures in any way, just one script can then be run to re-create all the databasestructures. Primary and foreign key constraints control the order in which you drop thetables – generally you drop in the reverse order of creation. The DROP commands for theTheme Park database are:DROP TABLE SALES LINE;DROP TABLE SALES;Keeley Crockett32
MySQL Lab GuideDROP TABLE HOURS;DROP TABLE ATTRACTION;DROP TABLE TICKET;DROP TABLE EMPLOYEE;DROP TABLE THEMEPARK;Task 2.10. Add the DROP commands to the start of your script file and then run thethemepark.sql script.2.6 Display a table’s structureThe command DESCRIBE is used to display the structure of an individual table. To seethe structure of the EMPLOYEE table you would enter the command:DESCRIBE EMPLOYEE as shown in Figure 13.Figure 13 Describing the structure of the THEMEPARK TableKeeley Crockett33
MySQL Lab GuideTask 2.10 Use the DESCRIBE command to view the structure of the other databasetables that you have created in this lab.2.7 Listing all tablesTask 2.11 Use the SHOW TABLES command as shown in Figure 14, to list all tablesthat have been created within the THEMEPARK database.Figure 14 Displaying all tables2.8 Altering the table structureAll changes in the table structure are made by using the ALTER TABLE command,followed by a keyword that produces the specific change you want to make. Threeoptions are available: ADD, MODIFY, and DROP. ADD enables you to add a column,and MODIFY enables you to change column characteristics. Most RDBMSs do not allowyou to delete a column (unless the column does not contain any values) because such anaction may delete crucial data that are used by other tables.Keeley Crockett34
MySQL Lab GuideSupposing you wanted to modify the column ATTRACT CAPACITY in theATTRACTION table by changing the date characteristics from NUMERIC(3) toNUMERIC(4). You would execute the following command:ALTER TABLE ATTRACTIONMODIFY ATTRACT CAPACITY NUMERIC(4);NoteSome DBMSs impose limitations on when it’s possible to change attributecharacteristics. The reason for this restriction is that an attribute modification willaffect the integrity of the data in the database. In fact, some attribute changes can bedone only when there are no data in any rows for the affected attribute.You can learn more about altering a table’s structure in Chapter 8, “Introduction toStructured Query Language”.You have now reached the end of the first MySQL lab. The tables that you have createdwill be used in the rest of this lab guide to explore the use of SQL in MySQL in moredetail.Keeley Crockett35
MySQL Lab GuideLab 3: Data Manipulation CommandsThe learning objectives for this lab are To know how to insert, update and delete data from within a table To learn how to retrieve data from a table using the SELECT statement3.1 Adding Table RowsSQL requires the use of the INSERT command to enter data into a table. The INSERTcommand’s basic syntax looks like this:INSERT INTO tablename VALUES (value1, value2, . , valuen).NoteIn MySQL there are a number of versions of the INSERT statement. As well as thebasic INSERT which inserts rows into a table, the INSERT . VALUES and INSERT. SET forms of the statement insert rows based on explicitly specified values. Forexample, the INSERT . SELECT form inserts rows selected from another table ortables. You can read more about this in the MySQL Reference manual 5.0.The order in which you insert data is important. For example, because the TICKET usesits PARK CODE to reference the THEMEPARK table’s PARK CODE, an integrityviolation will occur if those THEMEPARK table PARK CODE values don’t yet exist.Keeley Crockett36
MySQL Lab GuideTherefore, you need to enter the THEMEPARK rows before the TICKET rows.Complete the following tasks to insert data into the THEMEPARK and TICKET tables:Task 3.1 Enter the first two rows of data into the THEMEPARK table using thefollowing SQL insert commands;INSERT INTO THEMEPARK VALUES ('FR1001','FairyLand','PARIS','FR');INSERT INTO THEMEPARK VALUES ('UK3452','PleasureLand','STOKE','UK');Task 3.2 Enter the following corresponding rows of data into the TICKET table using thefollowing SQL insert commands.INSERT INTO TICKET VALUES (13001,18.99,'Child','FR1001');INSERT INTO TICKET VALUES (13002,34.99,'Adult','FR1001');INSERT INTO TICKET VALUES (13003,20.99,'Senior','FR1001');INSERT INTO TICKET VALUES (88567,22.50,'Child','UK3452');INSERT INTO TICKET VALUES (88568,42.10,'Adult','UK3452');INSERT INTO TICKET VALUES (89720,10.99,'Senior','UK3452');Any changes made to the table contents are not physically saved on disk until you closethe database, close the program you are using, or use the COMMIT command. TheCOMMIT command will permanently save any changes—such as rows added, attributesmodified, and rows deleted—made to any table in the database. Therefore, if you intendKeeley Crockett37
MySQL Lab Guideto make your changes to the THEMEPARK and TICKET tables permanent, it is a goodidea to save those changes by using COMMIT;Task 3.3 COMMIT the changes to the THEMEPARK and TICKET tables to thedatabase.Task 3.4 Run the script file themeparkdata.sql to insert the rest of the data into theTheme Park database. This script file is available on the CD-ROM companion. Ensureyou COMMIT the changes to the database.3.2 Retrieving data from a table using the SELECT StatementIn Chapter 8, Introduction to Structured Query Language, you studied the SELECTcommand. The SELECT command has many optional clauses but in its simplest
MySQL will then inform you that the database has changed. Task 1.4 To create the SaleCo database from a MySQL script file you would enter the following command: mysql SOURCE C:\MYSQL\SALECO2.SQL Note that in order for this command to work correctly you should have copied the script files accompanying this
MySQL for Excel is a 32-bit add-in for Microsoft Excel, which you can install and run on Microsoft Windows. MySQL for Excel is not compatible with Linux or macOS. MySQL for Excel can interact with MySQL Workbench to simplify the management of MySQL connections when both MySQL client tools are installed.
Lifetime Support Oracle Premier Support Oracle Product Certifications MySQL Enterprise High Availability MySQL Enterprise Security MySQL Enterprise Scalability MySQL Enterprise Backup MySQL Enterprise Monitor/Query Analyzer MySQL Workbench MySQL Enterprise Edition. 11 MySQL Database
Various forms of mysQL exist, such as oracle mysQL, mariadb, Percona server for mysQL, Galera cluster for mysQL, and Galera cluster for mariadb. oracle mysQL community edition is a freely downloadable version. commercial
MySQL PHP Syntax MySQL works very well in combination of various programming languages like PERL, C, C , JAVA and PHP. Out of these languages, PHP is the most popular one because of its web application development capabilities. PHP provides various functions to access MySQL database and to manipulate data records inside MySQL database.
Configure MySQL Download the latest version of MySQL www.dev.mysql.com The default Apple install has errors Supposed to be fixed in 10.4.4 update Install Package, Startup Item, and Preference Pane Reboot server MySQL Administrator GUI application to edit MySQL users and settings Cocoa MySQL GUI application to edit .
MySQL Performance Tuning &KDSWHU 10 MySQL settings to tune DIWHULQVWDOODWLRQ Fortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6.
MySQL Tutorial MySQL is the most popular Open Source Relational SQL database management system. MySQL is one of the best RDBMS being used for developing web-based software applications. This tutorial will give you quick start with MySQL and make you comfortable with MySQL programming.
Agile Development and Scrum The agile family of development methods were born out of a belief that an approach more grounded in human reality – and the product development reality of learning, innovation, and change – would yield better results. Agile principles emphasize building working software that
