Home arrow MySQL arrow Data Definition Language, Part 1

Data Definition Language, Part 1

Studying for the MySQL Certification exam? This article, the first of two parts, covers roughly 10 percent of the material that will appear on the exam, including general database and table properties, storage engines and table types, and more. It is excerpted from chapter four of the book MySQL Certification Guide written by Paul Dubois et. al. (Sams, 2004, ISBN: 0672326329).

TABLE OF CONTENTS:
  1. Data Definition Language, Part 1
  2. 4.1 General Database and Table Properties
  3. 4.3 Limits on Number and Size of Database Components
  4. 4.4 Identifier Syntax
  5. 4.5 CREATE DATABASE and DROP DATABASE
  6. 4.7 DROP TABLE
  7. 4.9 Creating and Dropping Indexes
  8. 4.10 Column Types
  9. 4.10.2 String Column Types
  10. 4.10.3 Date and Time Column Types
  11. 4.10.4 Column Options
  12. 4.10.5 Using the AUTO_INCREMENT Column Option
  13. 4.10.6 Automatic Type Conversion and Value Clipping
By: Sams Publishing
Rating: starstarstarstarstar / 41
January 19, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

This chapter covers the following exam topics:

  • General database and table properties

  • Storage engines and table types

  • Limits on number and size of database components

  • Identifier syntax

  • The CREATE DATABASE and DROP DATABASE statements

  • The CREATE TABLE, ALTER TABLE, and DROP TABLE statements

  • The CREATE INDEX and DROP INDEX statements; specifying indexes at table-creation time

  • Creating and using primary keys

  • Column types

  • Using AUTO_INCREMENT

  • String and number formats

  • Using SHOW and DESCRIBE to review table structures

Questions on the material in this chapter make up approximately 20% of the exam.

Several of MySQL's SQL statements comprise the Data Definition Language (DDL) that is used to define the structural characteristics of your databases. The following statements create or remove databases and tables or modify the structure of tables:

  • CREATE DATABASE creates a new database.

  • DROP DATABASE removes a database and any tables it contains.

  • CREATE TABLE creates a new table.

  • DROP TABLE removes a table and any data it contains.

  • ALTER TABLE modifies the structure of an existing table.

  • CREATE INDEX adds an index to a table.

  • DROP INDEX removes an index from a table.

Several of the table-related DDL statements require you to provide column definitions. MySQL allows several different types of data to be stored, and it's important to understand what column datatypes are available so that you can define your tables appropriately for the information they'll contain.

This chapter provides a general overview of how MySQL manages databases and tables and a discussion of the syntax of legal names that can be used to refer to them. It also describes how to use each of the DDL statements and discusses the available column datatypes, their properties, how to use them, and the syntax for writing column definitions.

Related to the DDL statements, MySQL supports several statements that are helpful for checking what databases or tables exist and for getting information about the internal column and index structure of tables. These statements include SHOW and DESCRIBE; they are discussed at the end of this chapter.

This chapter excerpt is from MySQL Certification Guide by Paul Dubois et al. (Sams, 2004, ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy this book now.



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
   

MYSQL ARTICLES

- Xeround Releases Free Version of MySQL Cloud...
- Oracle Announces New MySQL Specialization
- Constant Contact Chooses SkySQL for MySQL Su...
- Revoke Statement in MySQL
- The Grant Statement in MySQL
- SuccessBricks Announces ClearDB Availability...
- Building a PHP ORM: Deploying a Blog
- TROSYS Launches Free MySQL Manager and Admin...
- Building an ORM in PHP: Domain Modeling
- Building an ORM in PHP
- MySQL Leads Open Source Market, Gets Cluster...
- Oracle Announces Milestone Release for MySQL
- How to Stop SQL Injection Attacks
- New Defragmentation Solution for SQL Server
- Comparison of MyISAM and InnoDB MySQL Databa...


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 10 - Follow our Sitemap

Dev Shed Tutorial Topics: