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: Create a birthday table for your employees. The table contains the employee name, gender, birth date, 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 choose any length from 1 to 255. If you need to change its font length in the future, you can use the alter table statement .); Gender can be expressed by only one character: "m" or "f". Therefore, CHAR (1) is used, and DATE is used for the birth column.
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′,′1977-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 an example file named "“mysql.txt". Each line contains a record. Use a tab to separate the values and give them in the order of the columns listed in the create table statement. 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;