MySQL getting started (3) _ MySQL

MySQL getting started (3) MySQL getting started (3)

· Learning

After learning some basic operation commands, let's learn how to create a database and a database table.

· 1. use the SHOW statement to find out the current database on the server:

Mysql> show databases;
+ ---------- +
| Database |
+ ---------- +
| Mysql |
| Test |
+ ---------- +
3 rows in set (0.00 sec)

· 2. create a database, abccs

Mysql> create database abccs;
Note that different operating systems are case sensitive.

· 3. select the database you created

Mysql> USE abccs
Database changed
Now you have entered the database abccs you just created.

· 4. create a database table

First, check what tables exist in your database:
Mysql> show tables;
Empty set (0.00 sec)
It indicates that no database table exists in the database just created. Create a database table mytable:

We want to create a birthday table for employees in your company. The table contains the employee name, gender, date of birth, and city of birth.
Mysql> create table mytable (name VARCHAR (20), sex CHAR (1 ),
-> Birth DATE, birthaddr VARCHAR (20 ));
Query OK, 0 rows affected (0.00 sec)

Because the column values of name and birthadd change, VARCHAR is selected and its length is not necessarily 20. You can select
Any length from 1 to 255. if you need to change its font length later, you can use the alter table statement .);
Gender can be expressed by only one character: "m" or "f". Therefore, CHAR (1) is used );
The birth column uses the DATE data type.

After creating a table, we can look at the results just now and use show tables to SHOW which TABLES are in the database:
Mysql> show tables;
+ --------------------- +
| Tables in menagerie |
+ --------------------- +
| Mytables |
+ --------------------- +

· 5. display table structure:

Mysql> DESCRIBE mytable;
+ ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------------- + ------ + ----- + --------- + ------- +
| Name | varchar (20) | YES | NULL |
| Sex | char (1) | YES | NULL |
| Birth | date | YES | NULL |
| Deathaddr | varchar (20) | YES | NULL |
+ ------------- + ------ + ----- + --------- + ------- +
4 rows in set (0.00 sec)

· 6. add records to the table

We first use the SELECT command to view the data in the table:
Mysql> select * from mytable;
Empty set (0.00 sec)
This indicates that the table created just now has no records.

Add a new record:
Mysql> insert into mytable
-> Values ('abccs ', 'F', '2017-07-07', 'China ');
Query OK, 1 row affected (0.05 sec)
Use the SELECT command above to check what has changed.

We can add records of all employees to the table one by one using this method.

· 7. load data into a database table in text mode

It is troublesome to input data one by one. We can add all records to your database table using text files.
Create a file named "mymysql.txt". each line contains a record. use the "tab" to separate the values and
The column order listed in the create table statement is given, for example:

Abccs f 1977-07-07 china
Mary f 1978-12-12 usa
Tom m 1970-09-02 usa

Use the following command to load the parent file named mytable.txt to the mytable table:
Mysql> load data local infile "mytable.txt" into table pet;

Run the following command to check whether the data has been input to the database table:
Mysql> select * from mytable;