CREATE ALTER FK

• 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
• SQL CREATE


  Used to create new Databases, Tables, Views, or other database objects
• CREATE DATABASE


  Creates a new database
• DROP DATABASE


  Removes an existing database
• CREATE TABLE


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

  2. 3 methods: create new table, clone from existing table (inherits pre-existing data structure but not data), clone from existing table (inherits pre-existing data structure and data)
• AUTO_INCREMENT can only be added when specifying a primary key
• CREATE TABLE ... LIKE ...


  Inherits pre-existing data structure, but NOT data values
• CREATE TABLE ... AS SELECT * FROM ...


  Inherits pre-existing data structure, values of ALL attributes from an existing table
• CREATE TABLE ... AS SELECT ... FROM ... WHERE ...


  Inherits pre-existing data structure, values of selected attributes from an existing table with condition(s)
• 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 (...)


  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
• Table Naming Conventions


  • Can be alphanumeric, cannot be only digits, no spaces allowed, max. 64 characters
  • To create a table while within a desired database only requires a tableName
  • To create a table while in a different database use a fully qualified name --> databaseName.tableName
• Mandatory Attributes Definition


  • attribute_name, data type, Null or Not Null
  • If Null or Not Null is left blank, it will be assigned NULL as the default
• ABLE IF NOT EXISTS people (pID INT, pName VARCHAR(50))


  Create table 'people' if it doesn't already exist, with columns pID (integer) and pName (varchar 50)
• DROP TABLE IF EXISTS tName
• DROP TABLE


  Removes a table, before perhaps, creating a new version of it
• q usually precedes a CREATE TABLE statement
• Dr Bee Yen Toh, Queen's University Belfast, CSC1023 - Databases
• Table & Attributes Naming Conventions
• Table Naming Conventions


  • tableName can be alphanumeric (e.g. csc1023Pet), cannot be only digits (can't be 12345), no spaces are allowed, max. 64 characters
• To create a table while within a desired database


  only requires a tableName
• To create a table while in a different database


  use a fully qualified name --> databaseName.tableName
• Mandatory Attributes Definition


  • attribute_name, data type, Null or Not Null
• If Null or Not Null is left blank, it will be assigned NULL as the default
• CREATE TABLE csc1023Pet (age INT, name VARCHAR(30) NOT NULL)


  Create a table 'csc1023Pet' with columns age (integer) and name (varchar 30 not null)
• DESCRIBE command


  Fetches table structure details
• CREATE TABLE Pet (PetID INT NOT NULL PRIMARY KEY, Name VARCHAR(255) NOT NULL, Type VARCHAR(255) NOT NULL, Breed VARCHAR(255) NULL, Age INT NOT NULL)


  Create a table 'Pet' with columns PetID (integer not null primary key), Name (varchar 255 not null), Type (varchar 255 not null), Breed (varchar 255 null), Age (integer not null)
• Primary Key


  A column or set of columns that uniquely identifies each row in a table
• CREATE TABLE Pet (PetID INT NOT NULL, Name VARCHAR(255) NOT NULL, Type VARCHAR(255) NOT NULL, Breed VARCHAR(255) NULL, Age INT NOT NULL, PRIMARY KEY(PetID))


  Create a table 'Pet' with columns PetID (integer not null), Name (varchar 255 not null), Type (varchar 255 not null), Breed (varchar 255 null), Age (integer not null), with PetID as the primary key
• CREATE TABLE Pet (PetID INT NOT NULL, Name VARCHAR(255) NOT NULL, Type VARCHAR(255) NOT NULL, Breed VARCHAR(255) NULL, Age INT NOT NULL, PRIMARY KEY(PetID, name))


  Create a table 'Pet' with columns PetID (integer not null), Name (varchar 255 not null), Type (varchar 255 not null), Breed (varchar 255 null), Age (integer not null), with a composite primary key on PetID and name
• CREATE TABLE Pet (PetID INT NOT NULL AUTO_INCREMENT, Name VARCHAR(255) NOT NULL, Type VARCHAR(255) NOT NULL, Breed VARCHAR(255) NULL, Age INT NOT NULL, PRIMARY KEY (PetID))


  Create a table 'Pet' with columns PetID (integer not null auto-increment), Name (varchar 255 not null), Type (varchar 255 not null), Breed (varchar 255 null), Age (integer not null), with PetID as the primary key that auto-increments
• AUTO_INCREMENT


  Sets the initial integer value of a primary key and automatically increments it for each new record
• DROP TABLE IF EXISTS animals; CREATE TABLE animals (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(30) NOT NULL, PRIMARY KEY(id))


  Drop the 'animals' table if it exists, then create a new 'animals' table with columns id (integer not null auto-increment) and name (varchar 30 not null), with id as the primary key