IPT home IPT A Virtual Approach by Peter Whitehouse
Quick Links:
 
 
SQL home
Information and Intelligent Systems Social and Ethical Implications Human Computer Interaction Software and Systems Engineering eXercise Files Course Outline and Assessment A-Z of Geeky Acronyms Terrace Work Program 2004 Sillybus FAQ = Frequently Asked Questions Help
 
 

MySQL DDL

MySQL Data Definition Commands

:: Intro :: Datatypes :: DDL :: DML :: Operators :: Built-ins ::

MySQL DATA DEFINITION COMMANDS

Allowing table and database definition......

You must create a database container FIRST, before defining the tables that belong to it, using a command such as:

     CREATE DATABASE dbName()

then you must connect to the database before using it: (this is usually done for you on our network, else you need permissions that would compromise existing databases, sorry)

     connect(dbName,username,password);

If, on the other hand you wish to create another grouping of existing tables into a new database, you can use the following command structure:

     CREATE DATABASE dbName ( [tableName {, tableName}] )

To create TABLE containers, use the following command structure:

     CREATE TABLE tableNname
          ( colName dataType [(colWidth)] [NOT NULL/UNIQUE]
            {, colName dataType [(colWidth)] [NOT NULL/UNIQUE]}
            [PRIMARY KEY (colName {,colName})] )

or more completely:

    CREATE TABLE tableName
          ( columnDefn {, columnDefn}
           [,CHECK (localSearchCond) {,CHECK (localSearchCond)}
           [,PRIMARY KEY (columnList)]
           [,UNIQUE (columnList)]
           [,FOREIGN KEY (columnList) REFERENCES tableName {,tableName}])

where columnDefn has the form:

          colName dataType [(colWidth)] [NOT NULL] [DEFAULT literal]
              [CHECK (localSearchCond)] [UNIQUE (columnList)
              [PRIMARY KEY] [REFERENCES tableName]

Note that the order of options in the column definition must be adhered to, but that CHECKs may appear in the column definition or in the table definition.

A list of CHECKs may be seen using LIST CHECKS FOR tab-name. Checks are stored in a separate file in the active database path.

     COMMIT

makes changes to a database permanent (actually, it provides a level of 'undo' for database transactions that involve structural changes)

     ROLLBACK

un-does changes last made to database since the last commit. This is the one-level un-do available in mySQL.

     CREATE TABLE tableName LIKE tableName [Nointegrity]

copies the nominated table definition to a new name - all checks and constraints are also copied

deletes named file(s)

     DROP TABLE tableName

     DROP DATABASE dbName

     DROP SYNONYM synonymName

     DROP VIEW viewName

:: Intro :: Datatypes :: DDL :: DML :: Operators :: Built-ins ::

 

wonko@wonko.info
©Copyright t 1992..2018+. Edition 26.150117
wonkosite
Creative Commons License
This work is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 2.1 Australia License
.