MySQL must know-21st chapter-Create and manipulate tables

21st chapter-Creating and manipulating tables

This chapter teaches you the basics of creating, changing, and deleting tables.

21.1 Creating a Table

MySQL is not only used for table data manipulation, but it can also be used to perform all operations on the database and tables, including the creation and processing of the table itself. There are generally two ways to create a table:

• Use tools that have interactive creation and management of tables (as discussed in chapter 2nd);
• Tables can also be manipulated directly with MySQL statements.

In order to create a table with a program, you can use the SQL CREATE TABLE statement. It is important to note that when using interactive tools, you are actually using MySQL statements. However, these statements are not written by the user, and the interface tool automatically generates and executes the corresponding MySQL statement (as is the case when changing an existing table). Another example of the table creation script, see the code used to create the sample table in this book.

21.1.1 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. The following MySQL statement creates the Customers table used in this book:

As you can see from the example above, 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 table is made up of 9 columns. 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 (for an explanation of the data type, see Chapter 1th.) In addition, Appendix D lists the data types supported by MySQL. The table's primary key can be specified with the primary key keyword when the table is created. Here, the column cust_id is specified as the primary key column. The entire statement ends with a semicolon after the right parenthesis. (now ignore Engine=innodb and auto_increment, which are described later.) ) Statement formatting recall that you previously said that the MySQL statement ignores spaces. Statements can be entered on a long line, or they can be broken into many rows. They all work the same. This allows you to format the statement in the way that suits you best. The previous CREATE TABLE statement is a good example of statement formatting, which is arranged on multiple lines where the column definition is properly indented for reading and editing. There is no provision for arranging SQL statements in any indentation format, but I strongly recommend using some form of indentation. Processing 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 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 Ifnot 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.

21.1.2 using null values

The 6th chapter says that null values are no values or missing 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 with no values for the column, in other words, the column must have a value when the row is inserted or updated. Each table column is either a null column or a NOT NULL column, which is defined by the table definition when it is created. Take a look at the following example:

This statement creates the Orders table used in this book. Orders contains 3 columns, the order number, the order date, and the customer ID, respectively. All 3 columns are required, so the definition for each column contains the keyword not NULL. This will prevent inserting columns with no values. If you attempt to insert a column that has no value, an error is returned and the insertion fails. The next example creates a table with a mixture of NULL and NOT NULL columns:

This statement creates the vendors table used in this book. The vendor ID and the Vendor Name column are required and therefore specified as not NULL. All 5 of the remaining columns allow null values, so NOT NULL is specified. 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.

21.1.3 primary Key re-introduction

As mentioned, the primary key value must be unique. That is, 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 CREATE table example we've seen so far has been using a single column as the primary key. The primary key is defined with the following similar statements:
Primary KEY (VEND_ID)
To create a primary key consisting of multiple columns, the column names should be given in a comma-delimited list, as follows:

The OrderItems table contains the details of each order in the Orders table. Each order has a number of items, but each order has only 1 first items, 1 second items, and so on. Therefore, the combination of the order number (Order_num column) and the order item (order_item column) is unique and thus suitable as the primary key, which is defined as:
Primary KEY (Order_num, Order_item)

The primary key can be defined when the table is created (as shown here), or after the table is created (discussed later in this chapter).

Primary key and Null value in the 1th chapter, the primary key is the column whose value uniquely identifies each row in the table. Only columns that do not allow null values can be used in the primary key. Columns that allow null values cannot be identified as unique.

21.1.4 using Auto_increment

Let's review the customers and Orders tables again. The customers in the Customers table are uniquely identified by the column cust_id and each customer has a unique number. Similarly, each order in the Orders table has a unique order number, which is stored in the column order_num. These numbers have no special meaning other than they are unique. When adding a new customer or new order, a new customer ID or order number is required. These numbers can be arbitrary, as long as they are unique. Obviously, the simplest number used is the next number, the so-called next number is greater than the current maximum number. For example, if the maximum number of cust_id is 10005, the next customer in the Insert table can have cust_id equal to 10006. Is it simple? Not How do you determine the next value to use? Of course, you can use the SELECT statement to get the maximum number (using the MAX () function introduced in chapter 12th) and then add 1 to it. But this is unreliable (you need to find a way to ensure that no one else inserts rows between the SELECT and insert two statements, which is most likely to happen for multiuser applications) and is not as efficient (it is certainly not an ideal way to perform additional MySQL operations). This is the time for auto_increment to play a role. Take a look at the following line of code (the part of the CREATE TABLE statement that creates the Customers table):
cust_id int NOT NULL 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. This assigns each row a unique cust_id, which can be used as a primary key value.
Only one auto_increment column is allowed per table, and it must be indexed (for example, by making it a primary key). Overwrite 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. (For an example, see the table fill script used in this book.) One drawback to determining the auto_increment value for MySQL to generate (via AutoIncrement) primary keys is that you don't know who these values are. Consider this scenario: you are adding a new order. This requires creating a row in the Orders table, and then creating a row for each item ordered in the ORDERITMS table. Order_num is stored with order details in the OrderItems table. This is the reason why the Orders table and the OrderItems table are interrelated tables. This obviously requires you to know the generated order_num before inserting a orderitems line after inserting the orders row. So, how do you get this value when you use the Auto_increment column? This value can be obtained using the last_insert_id () function, as follows:
Select LAST_INSERT_ID ()
This statement returns the last Auto_increment value, which can then be used for subsequent MySQL statements.

21.1.5 Specifying a default value

If no value is given when inserting a row, 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. Take a look at the following example:

This statement creates a OrderItems table containing the items that make up the order (the order itself is stored in the Orders table). The quantity column contains the quantity of each item in the order. In this example, adding a text to the description of the column, default 1 indicates MySQL, using a quantity of 1 without giving a quantity. The function is not allowed to be different from most DBMS, MySQL does not allow a function to be the default value, it only supports constants. Use default values instead of NULL values many database developers use default values instead of NULL columns, especially for columns that are used for calculations or data grouping.

21.1.6 Engine Type

You may have noticed that the CREATE TABLE statements that have been used so far have all ended with a ENGINE=INNODB statement. Like other DBMS, 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. Most of the time, this engine is hidden within the DBMS and does not require much attention. But MySQL is different from other DBMS, it has a variety of engines. It packs multiple engines that are hidden inside the MySQL server and can execute commands such as CREATE table and select. Why do you want to release a variety of engines? Because they have their own different functions and features, choosing the right engine for different tasks can provide good functionality and flexibility.

Of course, you can completely ignore these database engines. If you omit the engine= statement, the default engine (most likely the MyISAM) is used, and most SQL statements will use it by default. But not all statements use it by default, which is why the engine= statement is important (that is, why the two engines are used in the sample list of the book). Here are a few of the engines you need to know:

• InnoDB is a reliable transaction processing engine (see Chapter 26th), which 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 (see Chapter 18th), but does not support transactional processing.

For more knowledge about the full list of engines supported (and the difference between them), see links. Engine types can be mixed. In addition to the Productnotes table using MyISAM, the sample tables in this book use InnoDB. The reason is that the author wants to support transactions (so using InnoDB), but it also needs to support full-text search in productnotes (therefore, using MyISAM). Foreign keys cannot mix engine types across engines there is a big flaw. Foreign keys (used to enforce referential integrity, as described in chapter 1th) cannot span the engine, even if a table with an engine cannot reference a foreign key with a table that uses a different engine. So, which engine should you use? It depends on what features you need. MyISAM is probably the most popular engine due to its performance and characteristics. But if you don't need reliable transaction processing, you can use a different engine.

21.2 Update table

21.2 Update table to update table definition, you can use the ALTER TABLE statement. However, ideally, the table should not be updated once the data is stored in the table. It takes a lot of time to consider during the design of the table so that the table is not made big changes later. In order to change the table structure using ALTER TABLE, the following information must be given:

• 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.

The following example adds a column to a table:

This statement adds a column named Vend_phone to the vendors table and must have its data type defined.

To delete the column you just added, you can do this:

A common use of ALTER table is to define foreign keys. The following is the code used to define the foreign keys used in the tables in this book:

Here, the 4 ALTER TABLE statements are used because you want to change 4 different tables. To make multiple changes to an individual table, you can use a single ALTER TABLE statement, with each change separated by a comma. Complex table structure changes typically require a manual removal process, which involves the following steps:

• Create a new table with the new column layout;
• Using the Insert SELECT statement (for a detailed introduction to this statement, see Input analysis Input 20321.4 rename table 15319) Copy the 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. Be careful to use ALTER TABLE to be extremely careful, you should make a full backup (mode and data 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.

21.3 Deleting a table

Deleting a table (deleting an entire table instead of its contents) is as simple as using the DROP TABLE statement:
drop table customers2;

Enter this statement to delete the customers (assuming it exists). The delete table is not confirmed and cannot be undone, and executing this statement will permanently delete the table.

21.4 Renaming a table

You can rename a table by using the Rename Table statement:
Rename table Customers2 to customers;
The only thing RENAME table does is rename a table. You can rename multiple tables using the following statement:
Rename table Backup_customers to customers,
Backup_vendors to vendors,
Backup_products to Products;

21.5 Summary

This chapter describes a few new SQL statements. Create TABLE is used for creating new tables, altertable is used to change table columns (or other objects such as constraints or indexes), and drop table is used to completely delete a table. These statements must be used with care and should be used after a backup has been made. This chapter also describes the database engine, defining primary keys and foreign keys, and other important table and column options.

MySQL must know-21st chapter-Create and manipulate tables