CREATE TABLE INDEX

    Cards (38)

    • 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
    See similar decks