Download MySQL: Comparative Study with Textbook Chapter 5 on Data Definition and Manipulation and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
Chapter 5: Notes on MySQL
MySQL is an open-source relational database management system that is owned by Oracle Corporation, which describes it as “the world’s most popular open source database”. It uses a strictly relational model, and does not support the object-relational features described in Chapter 8 of the textbook. There is a free version, called the community edition, and several others. It is available for a wide variety of platforms and includes many features besides the basic ones described here. The community edition is sufficient for our purposes. It is easily downloaded and installed from the MySQL website at http://www.mysql.com/downloads/mysql/ MySQL Lab Exercise 5.1, which is included in a file by that name in this directory, has more details about downloading and installing the community edition.
This document covers some of the differences between the Oracle DBMS code described in Chapter 5 of the textbook and MySQL. You should read the corresponding sections in the textbook before reading each section of this document. You can also see more information about MySQL by using the help facility available from within MySQL. From command line, you can see the help topics at any time by entering the following command at the prompt, mysql>
help contents
This command displays the help categories available, and you can then ask for help on any of those categories. For additional information, you can also access the online documentation at http://dev.mysql.com/doc/ There are also many tutorials on MySQL available on the Internet.
Text Section 5.3: MySQL Data Definition Language
The most useful data definition language commands for beginners are CREATE INDEX, ALTER TABLE, RENAME TABLE, DROP TABLE, and DROP INDEX. The syntax for these commands is very similar to Oracle’s,
but there are some minor differences. To see all the data definition commands, you can enter, at the command prompt,
help data definition
Text Section 5.3.1: CREATE TABLE
To create a database in which your tables will reside, you can use the command
CREATE DATABASE [IF NOT EXISTS] database_name ;
You can optionally use the form CREATE SCHEMA instead. You can then connect to the database either by
USE database_name ;
or
CONNECT database_name ;
To create a table, MySQL uses a form very similar to Oracle’s
CREATE TABLE [IF NOT EXISTS] table_name ( column_list )[ table options ][ partition options ];
show databases;
Data Types
MySQL includes several numeric types, string types, date and time types, BOOLEAN, BLOB, and spatial types. The most commonly used numeric types are INTEGER(n), and DECIMAL(n,d). For strings, VARCHAR(l) and CHAR(l) are used for varying-length and fixed-length strings respectively. Note that the VARCHAR2 type, often used in Oracle, does not exist in MySQL. For the date type, MySQL uses the default format 'YYYY-MM-DD'
unlike Oracle. To see all the data types, enter at the command line
help data types
Other DDL Commands
An index is automatically created for the primary key of a table. Other indexes can be created by using the CREATE INDEX command, which is essentially the same as in Oracle, except it does not include the CLUSTER option. The ALTER TABLE, RENAME TABLE, and DROP TABLE commands are also similar in syntax to those in Oracle. You can drop an entire database by entering
DROP DATABASE [IF EXISTS] database_name;
Text Section 5.4: MySQL Data Manipulation Language
To follow this section, you should populate the Univ database you created earlier. Find the subdirectory called MySQLUnivDB-Create&Populate in the same directory as this document, open the file called
MySQL_UnivDB_InsertStatements_likeFig5_3 and copy and paste the INSERT commands at the
MySQL prompt.
The SELECT command is similar to Oracle’s. The syntax is
SELECT [DISTINCT] column_list
FROM table_list
[WHERE conditions ]
[GROUP BY group [HAVING group_conditions ]]
[ORDER BY sort_columns ]
[LIMIT [ beginning_row ,] number_retrieved ];
The LIMIT option allows you to limit the number of rows retrieved. You can optionally specify the row to begin the query at, and the maximum number of records to be selected. MySQL DML includes all the standard operators and options, and also allows conditions of the type BETWEEN min AND max for specifying that a value is between
the minimum and maximum specified, inclusive. It can be used for any ordered data type. MySQL versions of the SELECT examples explained in Section 5.4 of the text can be found in this directory in a subdirectory called MySQL Code for Examples, in a file called MySQL SELECT Examples 1_23&LIMIT&BETWEEN.
Except for the last two examples, which illustrate MySQL’s LIMIT and BETWEEN options for queries, the SQL code is identical to that for Oracle. You should copy and execute these commands in MySQL, and create and test some of your own queries.
The four DELETE examples shown in Section 5.4.4 of the textbook work without modification in MySQL, as shown in the file MySQL Delete Examples 1-4&LIMIT in the MySQL Code for Examples
subdirectory. MySQL also allows records to be deleted from multiple tables, as shown in Example 5. It is possible to use ORDER BY and LIMIT when deleting records from a single table, as shown in Example 6. Note that it is important to be aware of referential integrity constraints when doing DELETE. The effect on dependent records when the parent is deleted is determined by the options chosen when the tables are created. Examples 3-6 illustrate some of these effects. You should execute the commands, study the results, and try out some of your own.
MySQL also has a REPLACE command that can either update an old record or insert a new one, depending on whether the record exists already. The form is similar to the INSERT form.
REPLACE INTO tablename ( colnames )VALUES( column_values );
The file MySQL Replace Examples shows what happens for the two cases. Execute these examples and
design and execute some additional examples.
Text Section 5.4.5: Views
Views can be defined in MySQL using a statement similar to Oracle’s
CREATE [OR REPLACE] VIEW viewname[(col-list)]AS SELECT-statement ;
The file called MySQL Create View Examples 1_6 shows that the same SQL statements shown in Section
5.4.5 for Oracle run on MySQL. We added the OR REPLACE option to each one, but otherwise they are unchanged. Views can be used the same way as they are in Oracle. Try out the commands in the file and add some of your own.
Text Section 5.5: MySQL Constraints and Triggers
Triggers can be written for MySQL databases using syntax similar to Oracle’s, and they function the same way as they do in Oracle. The OR REPLACE is not used as an option in the CREATE TRIGGER statement. To signal the end of the trigger, the delimiter must be changed from the usual semicolon to some other special character, so that MySQL will not treat semicolons that are part of the trigger code as the delimiter for the entire trigger. This is done by a simple statement such as delimiter $$ at the beginning of the code. The delimiter should be set back to
the semicolon by writing delimiter ; at the end of the trigger code. Another difference is that MySQL uses the
prefix NEW. instead of :NEW. to refer to a new record , and OLD. rather than :OLD for the old record. The MySQLCode for Examples subdirectory contains another directory called MySQL Triggers with files for
the MySQL code for Figure 5.4, including creating the tables and the trigger code for the examples. Note that the declarations for local variables go after the BEGIN statement, as shown in the code for trigger EnrollRequest.
Constraints, including primary key and foreign key constraints, are checked immediately, with no DEFERRED option. Since this can cause a problem in a transaction that involves foreign key constraints that need to be temporarily violated, you can suspend foreign key checking using the following code
SET FOREIGN_KEY_CHECKS=0;
... do the transaction... SET FOREIGN_KEY_CHECKS=1;
CALL PROCEDURE FINDNAME(’S1001’);
Unlike SQL*Plus, MySQL will not complete compilation if the procedure contains an error. To drop a procedure, use
DROP PROCEDURE procedurename ;
Functions are created using
CREATE FUNCTION function_name ( parameter_list ) RETURNS type
BEGIN
procedure body, which must include a RETURN statement
END;
Since only IN parameters can be used for functions, the parameter list includes only the name and type of each parameter. Functions can be written for user-defined functions as well as for stored routines.
The flow of control statements are similar to Oracle’s and include IF…THEN…ELSE…END IF, LOOP…
END LOOP,REPEAT…UNTIL…END REPEAT, and WHILE…END WHILE.
Cursors are used in almost the same way as in Oracle, as shown in the file MySQLFig5_7-
ProcedureCursorSample. A cursor is declared using
DECLARE cursor-variable CURSOR FOR query ;
The cursor must be opened to execute the query, using
OPEN cursor_variable ;
The FETCH statement is used to retrieve a row of the results, as in
FETCH cursor-variable INTO variable, variable ,,,
In our example, we simply display the values stored in those variables by using a SELECT statement. At the end, the cursor is closed using
CLOSE cursor-variable
MySQL Error Handling
DECLARE foreign_key_error CONDITION FOR 1216;
DECLARE duplicate_key CONDITION FOR SQLSTATE '23000';
Then we can write a handler for these conditions using
DECLARE EXIT HANDLER FOR foreign_key_error, duplicate_key;
Text Sections 5.7.2 and 5.7.3:Connectors and APIs
There are many connectors and APIs available for MySQL, allowing users to connect to a MySQL database from a variety of languages and environments. There are connectors for ODBC, JDBC and .NET, among others, and APIs for C, C++, Eiffel, Java, PHP, Perl, Python, Ruby and other languages.
Text Section 5.8: Schema Information
The MySQL version of a system catalog is called the INFORMATION_SCHEMA. It contains information about all the databases on the MySQL server. Users can query the schema using SELECT statements of the form
SELECT columns
FROM INFORMATION_SCHEMA. schema_table_name
WHERE condition ;
There are INFORMATION_SCHEMA tables called TABLES, COLUMNS, TABLE CONSTRAINTS, VIEWS, TRIGGERS, ROUTINES, and many others. Each of these tables contains information about the corresponding objects in the database. For example, the TABLES table has columns called TABLE_NAME, TABLE_ROWS, and TABLE_SCHEMA, among others. The COLUMNS table has TABLE_NAME, SCHEMA_NAME, COLUMN_NAME, DATATYPE, ORDINAL_POSITION, and others. For VIEWS, the columns include TABLE_NAME, VIEW_DEFINITION, DEFINER, and others. Typical queries are shown in the file called MySQL
INFORMATION_SCHEMA queries. You should execute these queries and examine the results. Details about
all the options for these and other tables in INFORMATION_SCHEMA can be found in the MySQL documentation.
