MySQL Reference Manual-create and use a database _ MySQL

MySQL Reference Manual-create and use a database since you know how to input commands, it is time to access a database.
　　
Suppose you have many pets in your home (your "Zoo") and you want to track all kinds of information about them. You can create a table to save your data and load them according to the required information. then you can retrieve data from the table to answer questions about different types of animals. This section shows how to do all of these things:
　　
How to create a database
How to create a database table
How to load data to database tables
How to retrieve data from a table in various ways
How to use multiple tables
The zoo database will be simple (intentional), but it is not difficult to imagine that it may be used in the real world of similar types of databases. For example, such a database can be used by a farmer to track livestock, or a veterinarian to track the records of diseased animals.
　　
Use the SHOW statement to find out which database exists on the server:
　　
Mysql> show databases;
+ ---------- +
| Database |
+ ---------- +
| Mysql |
| Test |
| Tmp |
+ ---------- +
　　
The database list may be different on your machine, but the mysql and test databases are likely to be different. Mysql is required because it describes the user's access permissions. The test database is often used as a work zone to give users a try.
　　
If the test database exists, try to access it:
　　
Mysql> USE test
Database changed
　　
Note that USE, similar to QUIT, does not require a semicolon. (If you like it, you can USE a semicolon to terminate such a statement; this is not a problem) the USE statement must also be given on a single row.
　　
You can use the test database in the following example (if you can access it), but anything you create in the database can be deleted by others who access it, for this reason, you may ask your MySQL administrator to permit a database to be used by yourself. Suppose you want to call your menagerie, the administrator needs to execute the following command:
　　
Mysql> grant all on menagerie. * TO your_mysql_name;
　　
Your_mysql_name is the MySQL User name assigned to you.
　　
8.4.1 create and select a database
If the administrator creates a database for you when setting your permission, you can start using it. Otherwise, you need to create it yourself:
　　
Mysql> create database menagerie;
　　
In Unix, database names are case-sensitive (unlike SQL keywords). Therefore, you must always reference your database with menagerie, instead of Menagerie, MENAGERIE, or some other variants. The same is true for the table name. (In Windows, this restriction does not apply, although you must use the same case to reference databases and tables in a given query .)
　　
Creating a database is not selected to use it. you must do this explicitly. To call menagerie the current database, run the following command:
　　
Mysql> USE menagerie
Database changed
　　
Your database only needs to be created once, but you must select it for use each time you start a mysql session. You can do this by issuing the above USE statement. In addition, when you call mysql, you can select a database on the command line and specify its name after any connection parameters you may need. For example:
　　
Shell> mysql-h host-u user-p menagerie
Enter password :********
　　
Note that menagerie is not the password of the command you just showed. If you want to provide your password after the-p option on the command line, you must do not have any extra space (for example,-pmypassword, not-p mypassword ). However, it is not recommended to place your password on the command line, because it is exposed to other users who can log on to your machine.
　　
8.4.2 create a database table
Creating a database is easy, but it is empty at this time, as show tables will tell you:
　　
Mysql> show tables;
Empty set (0.00 sec)
　　
The harder part is to determine what your database structure should be: What database tables you will need, and what columns they have.
　　
You will need a table containing records for each of your pets. It can be called a pet table, and it should contain, at least, the name of each animal. Because the name itself is not very interesting, the table should contain additional information. For example, if you have more than one pet owner in your family, you may want to list the owners of each animal. You may also want to record some basic descriptions such as the type and gender.
　　
What about age? It may be interesting, but it is not a good thing to store data in a database. Age changes over time, which means that you are constantly updating your records. Instead, if you store a fixed value such as a good birthday, you can calculate it based on the difference between the current date and the date of birth whenever you need an age. MySQL provides functions for date operations, so this is not difficult. There are other advantages to store birthdates instead of ages:
　　
You can use the database for such tasks as generating upcoming pet birthday reminders. (If you think this type of query is stupid, note that this is the same problem with the customer's environment in a business database that shows you are about to send birthday greetings to it soon, because computers help with private contact .)
You can calculate the age relative to the date instead of the current date. For example, if you store the death date in the database, you can easily calculate when a pet died.
You may think of other useful types of information in the pet table, but so far this is sufficient: name, owner, type, gender, birth, and death date.
　　
Use a create table statement to specify the layout of your database TABLE:
　　
Mysql> create table pet (name VARCHAR (20), owner VARCHAR (20 ),
-> Species VARCHAR (20), sex CHAR (1), birth DATE, death DATE );
　　
VARCHAR is a good choice for name, owner, and species columns, because the column value will become longer. These columns do not have to be of the same length or 20 length. You can choose any length from 1 to 255, which seems the most reasonable for you. (If you make a poor choice, you will need a longer field in the future. MySQL provides an alter table statement .)
　　
Animal tables can be represented in many ways, such as "m" and "f", or maybe "male" and "female ". Using a single character "m" and "f" is the simplest.
　　
Using the DATE data type for the birth and death columns is quite obvious.
　　
Since you have created a table, show tables should generate some output:
　　
Mysql> show tables;
+ --------------------- +
| Tables in menagerie |
+ --------------------- +
| Pet |
+ --------------------- +
　　
To verify that your table is created as expected, use a DESCRIBE statement:
　　
Mysql> DESCRIBE pet;
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Name | varchar (20) | YES | NULL |
| Owner | varchar (20) | YES | NULL |
| Species | varchar (20) | YES | NULL |
| Sex | char (1) | YES | NULL |
| Birth | date | YES | NULL |
| Death | date | YES | NULL |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
　　
You can DESCRIBE at any time, for example, if you forget the column name in your table or what type they are.
　　
8.4.3 load data into a database table
After you create a table, you need to enrich it. The load data and INSERT statements are used here.
　　
Assume that your pet record is described as follows. (It is observed that MySQL expects the date to be in YYYY-MM-DD format; this may be different from what you are used .)
　　
Name owner species sex birth death
Fluffy Harold cat f 1992-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13
Fang Benny dog m 1990-08-27
Bow.diane dog m
Chirpy Gwen bird f 1998-09-11
Whistler Gwen bird 1997-12-09
Slim Benny snake m 1996-04-29
　　
Because you started from an empty table, an easy way to enrich it is to create a text file containing each row of your animals, and then load the file content into the table with a single statement.
　　
You can CREATE an example file named pet.txt. each line contains a record. the values are separated by a tab and given in the order of the columns listed in the create table statement. You can use NULL for missing values (such as unknown gender, or dead dates of living animals. To represent these in your text file, use/N. For example, the record for the Whistler bird looks like this (here the blank space between values is a single positioning character ):
　　
Whistler Gwen bird/N 1997-12-09/N
　　
Run the following command to mount the upload pet.txt file to the pet table:
　　
Mysql> load data local infile "pet.txt" into table pet;
　　
If you want to, you can explicitly specify the delimiter and the end mark of the row for the column value in the load data statement, but the default is the location and line break. This statement is sufficient to compete for the readable pet.txt file.
　　
When you want to add a new record at a time, the INSERT statement is useful. In its simplest form, you provide values for each column in the order listed in the create table statement. Assume that Diane names a new hamster Puffball. you can use an INSERT statement to add a new record:
　　
Mysql> insert into pet
-> VALUES ('puffball', 'Diane ', 'hamster', 'F', '2017-03-30', NULL );
　　
Note: Here the string and date value are specified as strings expanded by quotation marks. In addition, with INSERT, you can insert null directly to indicate that the value does not exist.