MySQL Crash Course #13 # Chapter 21. Creating and manipulating Tables

The data in the previous manipulate table is now the manipulate form itself.

INDEX

• Create a primary key that consists of multiple columns
• Rules for automatic growth
• View the last-inserted self-increment ID
• Try to replace NULL with default values
• Foreign keys can not cross engine

• Add fields and Delete fields & define foreign keys

• Modification of complex table structures

• Delete table and modify table name

Very neat and tidy. Exemplary script:

CREATE TABLECustomers (cust_idint        not NULLauto_increment, Cust_nameChar( -) not NULL, Cust_addressChar( -)NULL, Cust_cityChar( -)NULL, Cust_stateChar(5)NULL, Cust_zipChar(Ten)NULL, Cust_countryChar( -)NULL, Cust_contactChar( -)NULL, Cust_emailChar(255)NULL ,  PRIMARY KEY(cust_id)) ENGINE=InnoDB;

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 TABLEOrderItems (Order_numint           not NULL, Order_itemint           not NULL, prod_idChar(Ten) not NULL, Quantityint           not NULL, Item_pricedecimal(8,2) not NULL ,  PRIMARY KEY(Order_num, Order_item)) ENGINE=InnoDB;

Rules for automatic growth

CREATE TABLE' manga ' (' manga_id ' )bigint( -) not NULLAuto_increment COMMENT'Comic ID', ' Manga_name 'varchar( +) not NULLCOMMENT'Comic Name', ' manga_discription 'varchar( -)DEFAULT NULLCOMMENT'Comic Description', ' Manga_status 'tinyint(4) not NULL DEFAULT '0'COMMENT'Comic Description',  PRIMARY KEY(' manga_id ')) ENGINE=InnoDB auto_increment=1012 DEFAULTCHARSET=UTF8 COMMENT='Comic Sheet'  

Only one self-increment column is allowed per table, and it must be indexed (for example, to set it as the primary key)

View the last-inserted self-increment ID,

Must be self-increasing! Custom Inserts don't count!

Mysql> INSERT  intoManga -(Manga_name)VALUES(' What'); Query OK,1Row affected (0.00sec) MySQL> SELECTlast_insert_id ();+------------------+|LAST_INSERT_ID ()|+------------------+|             1012 |+------------------+1Rowinch Set(0.00Sec

Using DEFAULT Instead of NULL Values

Many database developers use DEFAULT values instead of NULL columns, especially in columns that would be used in calculations or data groupings.

Foreign Keys Can ' t Span Engines

There is one big downside to mixing engine types. Foreign keys (used to enforce referential integrity, as explained in Chapter 1, "Understanding SQL") cannot span engines. That is, a table using one engine cannot has a foreign key referring to a table that uses another engine.

Add fields and Delete fields & define foreign keys

ALTER TABLE Vendors ADD CHAR (a);

ALTER TABLE Vendors DROP COLUMN Vend_phone;

Modifying a table This is often used to define foreign keys:

ALTER TABLEOrderItemsADD CONSTRAINTfk_orderitems_ordersFOREIGN KEY(Order_num)REFERENCESorders (order_num);ALTER TABLEOrderItemsADD CONSTRAINTFk_orderitems_productsFOREIGN KEY(prod_id)REFERENCESProducts (prod_id);ALTER TABLEordersADD CONSTRAINTFK_Orders_CustomersFOREIGN KEY(cust_id)REFERENCESCustomers (cust_id);ALTER TABLE ProductsADD CONSTRAINTfk_products_vendorsFOREIGN KEY(vend_id)REFERENCESVendors (vend_id);

Syntax: ALTER TABLE table_name ADD CONSTRAINT fk_id FOREIGN key (foreign key field name) REFERENCES appearance (the corresponding primary key field name in the appearance);

FK_ID is the name of the foreign key. For more foreign key related content, please refer to foreign KEY constraints

Modification of complex table structures

COMPLEX table structure changes usually require a manual move process involving these steps:

1. Create A new table with the new column layout.
2. Use the INSERT SELECT statement (see Chapter, "Inserting Data," for details of this statement) to copy the DAT A from the old table to the new table. Use conversion functions and calculated fields, if needed.
3. Verify that the new table contains the desired data.
4. Rename the old table (or delete it, if really brave).
5. Rename The new table with the name previously used by the old table.
6. Re-create any triggers, stored procedures, indexes, and foreign keys as needed.

Delete table and modify table name

DROP TABLE customers2;

TABLE  to customers,               to Vendors,               to Products;

MySQL Crash Course #13 # Chapter 21. Creating and manipulating Tables