MySQL Foundation creation database and table

Assuming you have the necessary permissions to create and modify databases on the server, let's look at how to create a database and a table in the database . In the example in this chapter, we will create a database for a virtual bookstore:

CREATE DATABASE Bookstore;

With this simple SQL statement, we have created a database named bookstore . In addition, the MySQL statement and the reserved words in the clauses are not case sensitive . The sensitivity of the database and table name to case is dependent on the operating system you are using, such as sensitivity to capitalization on Linux Systems, and the Window system is insensitive to capitalization. In the usual habit , reserved words in the SQL documentation use uppercase letters, while database names, table names, and field names all use lowercase letters. You may also notice the semicolon at the end of the SQL statement . A complete SQL statement may be more than one line until the client program sends the SQL statement to the server resolution execution after the semicolon is entered.

Once the database is created, this is only an empty library, but we can switch from the default database to the new database using the following statement:

Use Bookstore

With the above statement, you do not have to specify the database name used in each SQL statement. by default, MySQL will use the last specified database as the current database . The statement is based on the SQL statement of the client program, so you do not have to add semicolons at the end of the sentence.

Next, we will create the first table, which will be added to this table by the blog at a later date. Start by creating a database that holds basic information about your books, as this is the core message of the bookstore business:

CREATE TABLE Books (book_id INT, title varchar (), author varchar (50));

The above SQL statement creates a book information table with three columns. The parentheses are the entire list of columns. Next, enter the describe statement to see the results of the table you just created, which is output in tabular form:

DESCRIBE Books;

Given the size of the bookstore, we think we need to add a few more columns to the data element: Publisher, year of Publication, ISBN, Book type, book description, etc. We also want MySQL to automatically assign a numeric number to the book_id column so that you don't have to worry about duplicate issues when adding a new row of records. In addition, we decided to change the actual author name of the author field to an identification code , which can be associated with other tables containing the author's name fields. This approach unifies data, simplifies data entry, and makes arranging lookups easier to implement. Enter the following SQL statement to complete the modification in the Created data table:

ALTER TABLE bookschange column book_id book_id INT auto_increment PRIMARY key,change COLUMN author author_id int,add Colum N description text,add column Genre ENUM (' novel ', ' Poetry ', ' drama '), ADD column publisher_id int,add column pub_year varcha R (4), ADD COLUMN ISBN VARCHAR (20);

After the start line of this SQL statement, you can see that each clause that modifies or adds a field is separated by a comma . The second row of clauses modifies the book_id column, and even if you use the original column name and data type, we'll rewrite it again . We also add the auto_increment tag for it, which is responsible for performing the task mentioned in the previous paragraph, which is to assign any unique value to each record in the table. In addition, we will set this field as the primary key to improve the speed of data retrieval.

The first change clause can be confusing because the column name (book_id) appears two times. When you understand the syntax of the change clause, you will understand what it means, the first book_id represents the current column that will be modified, and the remainder of the clause is used to specify a new column . To better understand the meaning of this clause, you can view the second change clause: This clause replaces the existing column author with the new column author_id. Columns in the data table that are no longer known as author.

In the third row of clauses, we modify the author column so that its name and data type match the name and type of the authors table that will be created. As the index column in the books table represents a book, the authors table also has an indexed column representing each author. We associate the books table with the authors table for query operations so that the corresponding records in the author table can be found through the author_id column of the books table. Because the data type of the corresponding column in the authors table is int, the data type of the AUTHOR_ID column in the clause must also be int.

The four-line clause adds a description column for each book. The data type of the column is the text type and the text type is a variable-length data type that supports data that is less than 64,000 bytes in length.

In the genre column, several available values have been enumerated in advance to ensure uniformity. The field is allowed to be null or NULL, NULL, or NULL does not need to be specified.

Before entering data into the books table, we will first quickly establish a authors table. The table is made as a checklist. The first thing to do is add data to the authors table, because when we add data to the books table, we need to know the identification number of the authors column in the books table:

CREATE TABLE Authors (author_id INT auto_increment PRIMARY KEY, Author_last varchar (+), Author_first varchar (50) , Country VARCHAR (50));

As needed, we also associate the books table with the authors table through the author_id field. This example is saved first and then used in the blog .

MySQL Foundation creation database and table