CREATE TABLE INDEX

• Database


  A collection of tables (and rules) stored within a RDBMS
• RDBMS


  Can store many databases
• EEECS MySQL


  Only allows 1 database
• MySQL (on local PCs)


  Can have many databases
• Data Definition Language (DDL)


  • Essential for designing a database schema, defining how data is stored
  • Ensures the accuracy and reliability of data within the database
• DDL Commands


  • CREATE
  • ALTER
  • DROP, TRUNCATE
  • COMMENT
  • RENAME
• CREATE


  Used to create new databases, tables, views, or other database objects
• ALTER


  Modifies existing database objects. For example, it can add, delete, or modify columns in an existing table
• DROP, TRUNCATE


  Already discussed in Week 5
• COMMENT


  Adds descriptive comments to the data dictionary about database objects
• RENAME


  Changes the name of database objects
• SQL CREATE


  1. Create new Databases

  2. Create new Tables

  3. Create new Views
• CREATE DATABASE


  Used to create a new database
• DROP DATABASE


  Used to remove a database
• CREATE TABLE


  1. Specify data structure (attribute name, data type, Null or Not Null, Primary key and any other constraints)

  2. Clone from existing table (inherits pre-existing data structure, but not data)

  3. Clone from existing table (inherits pre-existing data structure + data)
• CREATE TABLE table_name (cName1 dataType, cName2 dataType, ...);
• CREATE TABLE `Customer` ( `id` INT NOT NULL, `firstname` VARCHAR(255) NOT NULL, `lastname` VARCHAR(255) NOT NULL, `email` VARCHAR(255) NOT NULL, `address` TEXT NOT NULL, `joined` DATE NOT NULL );
• CREATE TABLE Pet ( PetID INT NOT NULL AUTO_INCREMENT, Name VARCHAR(150) NOT NULL, Type VARCHAR(20) NOT NULL, Breed VARCHAR(50) NULL, Age INT NOT NULL );
• AUTO_INCREMENT can only be added when specifying a primary key
• Clone Table Structure Only (No Data)


  CREATE TABLE copyPet LIKE Pet;
• Clone Table Structure and Data


  CREATE TABLE TestPet AS SELECT * FROM Pet;
• Clone selected Columns and Data


  CREATE TABLE fakePet AS SELECT Name, Type FROM Pet;
• Clone selected columns from existing table with condition(s)


  CREATE TABLE fakePet AS SELECT Name, Type FROM Pet WHERE Age > 5;
• Managing MySQL Table Structures


  • SHOW CREATE TABLE tName
  • DESCRIBE tName
  • CREATE TABLE IF NOT EXISTS tName (cName1 dataType, cName2 dataType , ...)
  • DROP TABLE IF EXISTS tName
• SHOW CREATE TABLE tName


  Allows you to see the written SQL statement used to create a table, including all columns, data types, indexes, PK, FKs, etc
• DESCRIBE tName


  Shows all columns in a table format including indexes, but not FK
• CREATE TABLE IF NOT EXISTS tName (cName1 dataType, cName2 dataType , ...)


  No-operation if the table already exists, thus preventing runtime error
• DROP TABLE IF EXISTS tName


  Removes a table, before perhaps, creating a new version of it
• SHOW CREATE TABLE


  Shows the full SQL statement used to create a table, including all columns, data types, indexes, PKs, FKs, etc.
• DESCRIBE


  Shows the table structure in a tabular format, including columns, data types, and indexes, but not foreign keys
• CREATE TABLE IF NOT EXISTS


  Creates a new table, but does not throw an error if the table already exists
• DROP TABLE IF EXISTS


  Removes a table, before perhaps creating a new version of it
• Table Naming Conventions


  • Can be alphanumeric, cannot be only digits, no spaces allowed, max 64 characters
• Mandatory Attributes Definition


  Attribute name, data type, Null or Not Null
• Adding Primary Key


  1. Specify PRIMARY KEY within attribute definition

  2. Specify PRIMARY KEY in separate line

  3. Specify Composite Primary Key
• AUTO_INCREMENT


  Automatically increments the primary key value for each new record, starting from 1
• Indexes


  • Data structure used to quickly find records in a table
  • Without indexes, MySQL scans every record to find matches
  • Indexes improve query speed by maintaining indexed column values in a B-tree structure
  • Indexes consume disk space and slow down updates
• Adding Indexes


  1. During new table creation

  2. Add to an existing table using CREATE INDEX or ALTER TABLE ADD INDEX