SQL:Data Definition Language

From virtualMV2015wiki
Jump to: navigation, search
 Home  MySQL <

Introduction

Icon Objective.png

Objective

By the end of this page you should be :

  • familiar with the basic DDL commands

DDL Example

In the heights database the following DDL commands were sent to MySQL:

SELECT Database();
CREATE DATABASE dbHeight;
USE dbHeight;
SELECT DATABASE();
 
CREATE TABLE tblPeople
(
  PerID       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PerFullName VARCHAR(40),
  PerHeightmm INT  
    PRIMARY KEY (PerID)
);

CREATE DATABASE

Backus-Naur Form

CREATE DATABASE database_name;

Examples

CREATE DATABASE dbHeight;
CREATE DATABASE dbHireCenter;

If we want to start the database again we may want to "DROP" it first.. but only if it already exists so:

# Create database
DROP DATABASE IF EXISTS dbHeight;
SELECT Database();
CREATE DATABASE dbHeight;
USE dbHeight;
 
SELECT DATABASE(); # Shows that the database has been selected

CREATE TABLE

Backus-Naur Form

CREATE TABLE table_name 
  (column_name datatype [NULL | NOT NULL] [AUTO_INCREMENT]
  [, column_name datatype [NULL | NOT NULL] ]...)  
  [, [CONSTRAINT [name]] PRIMARY  KEY (index_columns)]...]

For the complete format refer to 12.1.10. CREATE TABLE Syntax (2010)[1]

Examples

CREATE TABLE tblPeople
(
  PerID       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PerFullName VARCHAR(40),
  PerHeightmm INT,  
    PRIMARY KEY (PerID)
);


CREATE TABLE tblEmployee
 ( EmpID CHAR(11) NOT NULL,
   EmpLname CHAR(20),
   EmpFname CHAR(15),
   EmpBirthdate DATE(mm"/"dd"/"yy));

Note how we can create a template for the date.

Icon References.png References

  1. 12.1.10. CREATE TABLE Syntax (2010) In MySQL 5.0 Reference Manual. Oracle. Retrieved September 3, 2010 from http://dev.mysql.com/doc/refman/5.0/en/create-table.html

SQL:Data Definition Language. (2017). In virtualMV's ( Michael Verhaart ) wiki. Retrieved December 17, 2017, from http://www.virtualmv.com/wiki/index.php?title=SQL%3AData_Definition_Language    (zotero)