Educator

Creating Databases & Tables

• A database is created using the CREATE DATABASE SQL command:

  CREATE DATABASE dbName;

• MySQL provides the following non-SQL commands:
  • SHOW DATABASES – lists all of the databases on a MySQL server
  • USE – selects a particular database on a MySQL server to use and to apply future commands to
• SQL defines the CREATE TABLE command for creating database tables. It accepts a comma-delimited list of column definitions between parentheses and has the following syntax:

  CREATE TABLE tblName
  (
  

  colName1 dataType1 colAttr1,
  colName2 dataType2 colAttr2,
  …

  );

• SQL Numeric Data Types:
  • INT – integer data type
  • INT UNSIGNED – positive integer data type
  • DECIMAL(M, D) – floating point number with M digits, D digits after the decimal point
• SQL String Data Types:
  • CHAR(M) – fixed-length string of M characters
  • VARCHAR(M) – variable-length string up to M characters in length
  • TEXT – large amounts of string data
• Each column in a table can have attributes set that affect the functionality of the column.
• The PRIMARY KEY attribute denotes a column as the primary key for the table.
  • Only one column can have the PRIMARY KEY attribute.
• The AUTO_INCREMENT attribute denotes that MySQL should automatically generate the value for the column for each row by incrementing the largest column value at the time a row is inserted by one.
  • This only can apply to integer or floating-point columns
• The SHOW TABLES command can be used to list all of the tables in a particular database.
• The DESCRIBE utility statement can be used to see the structure of a table.
• Additional Resources:
  • SQL Command Reference - CREATE DATABASE
  • SQL Command Reference - CREATE TABLE
  • SQL Numeric Data Types
  • SQL String Data Types