CC SQL 21- creating and manipulating tables

The flashcards below were created by user dau108 on FreezingBlue Flashcards.

  1. basic table creation
    • CREATE TABLE customers
    • (
    • cust_id int NOT NULL AUTO_INCREMENT,
    • cust_name char(50) NOT NULL ,
    • cust_address char(50) NULL ,
    • cust_city char(50) NULL ,
    • cust_state char(5) NULL ,
    • cust_zip char(10) NULL ,
    • cust_country char(50) NULL ,
    • cust_contact char(50) NULL ,
    • cust_email char(255) NULL ,
    • PRIMARY KEY (cust_id)
    • ) ENGINE=InnoDB;
  2. primary key
    As already explained, primary key values must be unique. That is, every row in a table must have a unique primary key value. If a single column is used for the primary key, it must be unique; if multiple columns are used, the combination of them must be unique.

    • To create a primary key made up of multiple columns, simply specify the column names as a comma delimited list, as seen in this example:CREATE TABLE orderitems
    • (
    • order_num int NOT NULL ,
    • order_item int NOT NULL ,
    • prod_id char(10) NOT NULL ,
    • quantity int NOT NULL ,
    • item_price decimal(8,2) NOT NULL ,
    • PRIMARY KEY (order_num, order_item)
    • ) ENGINE=InnoDB;
    And that's where AUTO_INCREMENT comes in. Look at the following line (part of theCREATE TABLE statement used to create the customers table)

    cust_id int NOT NULL AUTO_INCREMENT,

    AUTO_INCREMENT tells MySQL that this column is to be automatically incremented each time a row is added. Each time an INSERT operation is performed MySQL automatically increments (and thus AUTO_INCREMENT) the column, assigning it the next available value. This way each row is assigned a unique cust_id which is then used as the primary key value.

    Determining the AUTO_INCREMENT Value One downside of having MySQL generate (via auto increment) primary keys for you is that you don't know what those values are.

    So how could you obtain this value when an AUTO_INCREMENT column is used? By using the last_insert_id() function, like this:

    SELECT last_insert_id();
  4. Updating tables
    • To change a table using ALTER TABLE, you must specify the following information:
    • 1. The name of the table to be altered after the keywords ALTER TABLE. (The table must exist or an error will be generated.)
    • 2. The list of changes to be made.The following example adds a column to a table:

    • Input

    • ALTER TABLE vendors
    • ADD vend_phone CHAR(20);

    • To remove this newly added column, you can do the following:
    • • Input

    • ALTER TABLE Vendors
    • DROP COLUMN vend_phone;
  5. deleting tables
    Deleting tables (actually removing the entire table, not just the contents) is very easyarguably too easy. Tables are deleted using the DROP TABLE statement:

    • Input

    DROP TABLE customers2;

    • Analysis

    This statement deletes the customers2 table (assuming it exists). There is no confirmation, nor is there an undoexecuting the statement will permanently remove the table.
  6. renaming tables
    To rename a table, use the RENAME TABLE statement as follows:

    • Input

    RENAME TABLE customers2 TO customers;
Card Set:
CC SQL 21- creating and manipulating tables
2012-04-08 19:14:19
CC SQL 21 creating manipulating tables

CC SQL 21- creating and manipulating tables
Show Answers: