1 / 18

Creating and Managing Tables

Creating and Managing Tables. Objectives. At the end of this lesson, you will be able to: Describe the main database objects Create tables Describe the datatypes that can be used when specifying column definition Alter table definitions Drop, rename and truncate tables.

byron
Download Presentation

Creating and Managing Tables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Creating and Managing Tables

  2. Objectives • At the end of this lesson, you will be able to: • Describe the main database objects • Create tables • Describe the datatypes that can be used when specifying column definition • Alter table definitions • Drop, rename and truncate tables

  3. Naming Conventions • Required • Can be 1–64 characters long • Must contain only A–Z, a–z, 0–9, _ , and $ • Must not duplicate the name of another object owned by the same user • Must not be a MySQL reserved word • Must begin with a letter or digit

  4. Naming Conventions • Suggested • Field names should be descriptive. • Use the underscore (_) to separate words. • Use entirely lowercase words (this is a personal preference rather than a rule). • Use plural table names (to indicate multiple values stored) and singular column names. • End primary and foreign key columns with id (or ID) • List the primary key first in a table, followed by foreign keys.

  5. The CREATE TABLE Statement • You must have : • CREATE TABLE privilege • A storage area • You specify: • Table name • Column name, column datatype, and column size CREATE TABLE [schema.]table (columndatatype [DEFAULT expr]);

  6. The DEFAULT Option • Specify a default value for a column during an insert. • Legal values are literal value, expression, or MySQL function. • Illegal values are another column’s name or pseudocolumn. • The default datatype must match the column datatype. … hire_dateDATE DEFAULTCURDATE(), …

  7. Creating Tables • Creating the table • Confirm table creation MySQL> CREATE TABLE dept -> (dept_nbr INTEGER(2), -> dept_name VARCHAR(14), -> location VARCHAR(13)); Table created. MySQL> DESCRIBE dept Name Null? Type --------------------------- -------- --------- dept_nbr INTEGER(2) dept_name VARCHAR(14) locaton VARCHAR(13)

  8. Querying the Data Dictionary • Show tables owned by the user. MySQL> SHOW TABLES;

  9. Datatypes Numeric Types Bold item most frequently used in Business Environments

  10. Datatypes Text Types Bold item most frequently used in Business Environments

  11. Datatypes Date and Time Types Bold item most frequently used in Business Environments

  12. The ALTER TABLE Statement • Use the ALTER TABLE statement to: • Add a new column • Modify an existing column • Define a default value for the new column ALTER TABLE table ADD (column datatype[DEFAULT expr] [, column datatype]...); ALTER TABLE table MODIFY (column datatype[DEFAULT expr] [, column datatype]...);

  13. “…add a newcolumn intoDepartment30 table…” job Adding a Column Department30 New column job employee_nbr name annual_salaryhire_date 7698 Blake34200 2001-05-01 7654 Martin 15000 2001-09-28 7499 Allen 192002001-02-20 7844 Turner18000 2001-09-08 ... Department30 employee_nbr name annual_salaryhire_date 7698 Blake34200 2001-05-01 7654 Martin 15000 2001-09-28 7499 Allen 192002001-02-20 7844 Turner 18000 2001-09-08 ...

  14. Adding a Column • You use the ADD clause to add columns. • The new column becomes the last column. MySQL>ALTER TABLE dept30 ->ADD (job VARCHAR(9)); Table altered. employee_nbr name annual_salaryhire_date job ------------ ----- ------------- --------- --- 7698 Blake 34200 2001-05-01 7654 Martin 15000 2001-09-28 7499 Allen 19200 2001-02-20 7844 Turner 18000 2001-09-08 ... 6 rows selected.

  15. Modifying a Column • You can change a column’s datatype, size, and default value. • A change to the default value affects only subsequent insertions to the table. ALTER TABLE dept30 MODIFY (name VARCHAR(15)); Table altered.

  16. Dropping a Table • All data in the table is deleted. • Any pending transactions are committed. • All indexes are dropped. • You cannot roll back this statement. MySQL> DROP TABLE dept30; Table dropped.

  17. Dropping a Column • All data in the column is deleted. • Any pending transactions are committed. • You cannot roll back this statement. MySQL> ALTER TABLE tablenameDROP COLUMN (columname) [CASCADE CONSTRAINTS]

  18. Changing the Name of an Object • To change the name of a table, view, sequence, or synonym, you execute the RENAME statement. • You must be the owner of the object. MySQL> RENAME dept TO department; Table renamed.

More Related