mysql - part 2 - COP 4610L: Applications in the Enterprise...

Info iconThis preview shows pages 1–7. Sign up to view the full content.

View Full Document Right Arrow Icon
COP 4610L: MySQL Part 2 Page 1 Mark Llewellyn © COP 4610L: Applications in the Enterprise Fall 2006 Introduction To MySQL – Part 2 COP 4610L: Applications in the Enterprise Fall 2006 Introduction To MySQL – Part 2 School of Electrical Engineering and Computer Science University of Central Florida Instructor : Mark Llewellyn markl@cs.ucf.edu CSB 242, 823-2790 http://www.cs.ucf.edu/courses/cop4610/fall2006
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
COP 4610L: MySQL Part 2 Page 2 Mark Llewellyn © Manipulating Tables in MySQL (cont.) Recall that the create table command has the following general format: create [temporary] table [if not exists] tablename [( create_definition, . .. )] [ table_options ] [ select_statement ]; The table options allow you to specify the MySQL table type. The table type can be anyone of the six types listed in the table on the next slide.
Background image of page 2
COP 4610L: MySQL Part 2 Page 3 Mark Llewellyn © Manipulating Tables in MySQL (cont.) Transaction-safe tables with row locking Transaction-safe tables with page locking A collection of MyISAM tables used as one table A binary portable table handler that has replaced ISAM The data for this table is only stored in memory MySQL’s original table handler Description InnoDB BDB MERGE MyISAM HEAP ISAM Table Type MySQL Table Types ISAM, HEAP, and MyISAM are available for MySQL versions 3.23.6 or later. MERGE, BDB, and InnoDB are available for MySQL versions 4.0 and later. Default table type is ISAM.
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
COP 4610L: MySQL Part 2 Page 4 Mark Llewellyn © Altering A Table After a table has been created, it is possible to change the specifications of its schema. This is done through the alter table command: alter table table_name action_list Note: Changing the schema of a table in a database is not something that is done very often once the database has been created. The time for altering the schema is during the design phase. Altering the schema of an operational database is a very dangerous thing. Multiple changes to the table can be made at the same time by separating actions with commas in the action_list . The possible attribute (column) actions that can be used are shown in the table on the following slide.
Background image of page 4
COP 4610L: MySQL Part 2 Page 5 Mark Llewellyn © Altering A Table (cont.) Change the table options Rename a table Drop a column and all data contained within it. Modify column declaration without renaming column Modify column declaration with renaming of column Specify new default value for a column or remove old default Add a column to the table Action Performed table_options rename [as] new_table_name drop [column] column_name modify [column] column_declaration change [column] column_name column_declaration alter [column] column_name {set default literal | drop default} add [column] column_declaration [first | after column_name ] Action Syntax Actions performed by alter table (column related) command column_name represents the current name of the column, column_declaration represents the new declaration, in the same format as if it were in a create command.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
COP 4610L: MySQL Part 2 Page 6 Mark Llewellyn © Altering A Table (cont.) The screen shot below shows an example of altering a table.
Background image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 64

mysql - part 2 - COP 4610L: Applications in the Enterprise...

This preview shows document pages 1 - 7. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online