SQL must know note 17th Chapter create and manipulate tables

17.1 Creating a table

There are generally two ways to create a table
(1) Most DBMS have interactive creation and management table tools
(2) Tables can also be manipulated directly with SQL statements
Syntax differences: In different SQL implementations, the syntax of the CREATE table statement may be different.

17.1.1 Table Creation Basics

The following information must be given in order to create a table by using a CREATE TABLE:
(1) The name of the new table, which is given after the keyword create table.
(2) The name and definition of the table column, separated by commas.
(3) Some DBMS also require the location of the specified table.
Create a Products table

CREATE TABLEproducts{prod_idCHAR(Ten) not NULL, vend_idCHAR(Ten) not NULL, Prod_nameCHAR(254) not NULL, Prod_priceDECIMAL(8,2) not NULL, Prod_descVARCHAR( +)NULL};

To replace an existing table
When you create a new table, the specified table name must not exist, or an error will occur. If you want to prevent overwriting an existing table, SQL requires that the table be deleted manually before rebuilding it, rather than simply overwriting it with the CREATE TABLE statement.

17.1.2 using null values

A column that allows null values also allows the column's value to be not given when the row is inserted. Columns that do not allow null values do not accept rows that have no values for the column, in other words, the column must have no value when inserting or updating rows.
Specify null: Most DBMS considers NULL to be specified without specifying NOT NULL, but not all DBMS.
Primary key and Null value: The primary key is the column whose value uniquely identifies each row in the table, and only columns that do not allow null values are available for the primary key. Columns that allow null values cannot be used for unique identities.
Understand null: do not confuse null value with empty string, null value is no value, it is not an empty string, if specified ', this is allowed in the NOT NULL column type. An empty string is a valid value, and it is not a value that is not. The null value is specified with the keyword NULL instead of the empty string.

17.1.3 Specifying a default value

SQL allows you to specify a default value that the DBMS automatically takes if the value is not given when the row is inserted. The default value is specified in the column definition of the CREATE TABLE statement with the keyword default.

CREATE TABLEorderitems{Order_numINTEGER      not NULL, Order_itemINTEGER      not NULL, prod_idCHAR(Ten) not NULL, quantityINTEGER      not NULL     DEFAULT 1, Item_priceDECIMAL(8,2) not NULL};

Get system date

17.2 Update table

To update a table definition, you can use the ALTER TABLE statement.
Use ALTER TABLE to consider the content.
(1) In general, do not update a table when it contains data.
(2) All DBMS are allowed to add columns to the Alumni table, but there is a limit to the data type of the added columns (and the use of NULL and default).
(3) Many DBSM do not allow you to delete or change columns in a table.
(4) Most DBMS allows renaming of columns in a table.
(5) Many DBMS have restrictions on the changes to columns that have already been filled with data, with little or no restrictions on the columns that are not filled with data.
In order to change the table structure using ALTER TABLE, the following information must be given:
(1) After ALTER TABLE, give the name of the table to be changed (the table must exist, or an error will occur)
(2) List of changes made
Adding columns

ALTER TABLE VendorsADD vend_phone CHAR(20);

Delete Column

ALTER TABLE VendorsDROP COLUMN vend_phone;

Be careful with ALTER TABLE: Use ALTER TABLE to be extremely careful, you should make a full backup before making the change. Changes to database tables cannot be undone, and if you add unnecessary columns, you may not be able to delete them. Similarly, if you delete a column that should not be deleted, you may lose all of the data in that column.

17.3 Deleting a table

Delete the table (delete the entire table instead of its contents), using the DROP TABLE statement.

DROP TABLE CustCopy;

17.4 Renaming a table

Tables that are supported by different DBMS are renamed differently.
Oracle with Rename

SQL must know note 17th Chapter create and manipulate tables