MySQL creation and manipulation tables

1. Build a table

Table Creation Basics
The following information must be given in order to create a table by using a CREATE TABLE:
? The name of the new table, given after the keyword CREATE table;
? The names and definitions of the table columns are separated by commas.

The CREATE table statement may also include other keywords or options, but at a minimum, include the table's name and column details.

Example:

 create  table   students (stu_id  int  not  null   auto_increment, stu_name  char  (50 ) Span style= "COLOR: #808080" >not  null  , stu_ Address  char  (50 ) null    PRIMARY  key   (stu_id)) ENGINE  =  INNODB 

The table name is immediately following the CREATE TABLE keyword. The actual table definition (all columns) is enclosed in parentheses. Separate the columns with commas. The definition of each column begins with the column name (which must be unique in the table), followed by the data type of the column, and the table's primary key can be specified with the primary key keyword when the table is created. (spaces are ignored in MySQL statements.) Statements can be entered on a long line, or they can be broken into many rows. ）

Note: When creating a new table, the specified table name must not exist , or an error will occur. If you want to prevent accidental overwriting of an existing table, SQL requires that you delete the table manually (see later sections), and then rebuild it instead of simply overwriting it with the CREATE TABLE statement.

If you only want to create a table when it does not exist, you should give the if not EXISTS after the table name. Doing so does not check that the schema of the existing table matches the table pattern that you intend to create. It simply checks to see if the table name exists and creates it only if the table name does not exist.

Null is the default setting and if not NULL is not specified, the specified is considered null. (understand null do not confuse null values with empty strings.) a null value is no value and it is not an empty string . This is allowed in the NOT NULL column if you specify ' (two single quotes, which have no characters in between). 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. ）

2. Primary key:

Each row in the table must have a unique primary key value. If the primary key uses a single column, its value must be unique. If more than one column is used, the combined values of these columns must be unique. The primary key can be defined when the table is created (as shown here), or after the table is created.

Primary key cannot be null

For a primary key consisting of multiple columns, the column names should be given in a comma-separated list:

CREATE TABLEOrderItem (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

3, Auto_increment

Auto_increment tells MySQL that this column automatically increments whenever a row is added. Each time an insert operation is performed, MySQL automatically increments the column (thus having this keyword auto_increment), giving the column the next available value.

Only one auto_increment column is allowed per table, and it must be indexed (for example, by making it a primary key).

3.1, covering auto_increment

If a column is specified as auto_increment, does it need to use a special value? You can simply specify a value in the INSERT statement, as long as it is unique (and has not been used so far) and will be used instead of the automatically generated value . subsequent increments will begin using the manually inserted value .

How do I get this value when I use the auto_increment column? This value can be obtained using the last_insert_id () function. Example: Select LAST_INSERT_ID ()

This statement returns the last Auto_increment value, which can then be used for subsequent MySQL statements.

4. Specify default values

The value is not given when inserting a row, and MySQL allows you to specify the default value to use at this time. The default value is specified using the default keyword in the column definition of the CREATE TABLE statement.

Default value does not allow function

In general, setting the default value is better than setting null

5. Engine Type

MySQL has an internal engine that specifically manages and processes data. When you use the CREATE TABLE statement, the engine creates the table specifically, and when you use a SELECT statement or other database processing, the engine processes your request internally.

If you omit the engine= statement, the default engine (most likely the MyISAM) is used, and most SQL statements will use it by default.

Here are a few of the engines you need to know:

? InnoDB is a reliable transaction processing engine that does not support full-text search ;

? Memory is equivalent to MyISAM in function, but because the data is stored in RAM (not disk), it is very fast (especially suitable for temporary tables);

? MyISAM is an extremely high-performance engine that supports full-text search, but does not support transactional processing .

Note: Engines can be mixed between tables in the same database, but foreign keys of different engine types are not available

6. Update table

To change the table structure using ALTER TABLE, you must give the following information:

? Give the name of the table to be changed after ALTER TABLE (the table must exist, otherwise an error will occur);

? A list of changes that were made.

6.1 Adding a column

ALTER TABLE orderitemsss ADD CHAR (a);

Delete a column

ALTER TABLE orderitemsss DROP COLUMN Vend_phone;

Defining foreign keys

ALTER TABLE orderitemsss ADD CONSTRAINT fk_orderitemsss_orders FOREIGN KEY REFERENCES orders (order_num);

Note: This is a foreign KEY constraint name: Fk_orderitemsss_orders (what does it matter)

Key (Order_num): Represents a foreign key in a table

REFERENCES orders (order_num): Foreign key referenced table and table row

Note: Changes to the complex table structure:

Complex table structure changes typically require a manual removal process, which involves the following steps:
? Create a new table with the new column layout;
? Use the Insert SELECT statement to copy data from the old table to the new table. If necessary, you can use conversion functions and calculated fields;

? Examine a new table containing the required data;
? Rename the old table (you can delete it if you are sure);
? Rename the new table with the original name of the old table;
? Recreate the triggers, stored procedures, indexes, and foreign keys as needed.

7. Delete a table

DROP TABLE t888;

8. Renaming a table

TABLE  to t888999;

MySQL creation and manipulation tables