1. Use the SHOW statement to find out what database currently exists 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 the case sensitivity of different operating systems.
3. Select the database you have created
mysql> USE abccs
Database changed
At this point you are in the database you just created abccs.
4. Create a database table
First look at what tables exist in your database right now:
mysql> SHOW TABLES;
Empty set (0.00 sec)
This means that there is no database table in the database you just created. Let's create a database table mytable.
We are going to create a table of birthdays of your company's employees, the table will contain the employee's 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)
Since the column values for name and birthadd are variable, choose VARCHAR, which does not have to be 20. you can choose any length from 1 to 255, and if you need to change its word length later, you can use the Alter TABLE statement.) The gender is represented by a single character: "m" or "f", so CHAR(1) is chosen; the DATE data type is used for the BIRTH column.
After creating a table, we can look at the results of what we just did and use SHOW TABLES to show what tables are in the database:
mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| mytables |
+---------------------+
5. Show the structure of the table:
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'll start by using the Select command to view the data in the table:
mysql> select * from mytable;
Empty set (0.00 sec)
This indicates that the table just created has no records yet.
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 again to see what has changed. We can follow this method to add all the employee records to the table one by one.
7. Textual loading of data into a database table
It is cumbersome to enter them one by one. We can use a text file to add all the records to your database table. Create a text file "" containing one record per line, with values separated by locators (tabs) and given 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 text file "" into the mytable table:mysql> LOAD DATA LOCAL INFILE "" INTO TABLE pet;
Then use the following command to see if the data has been entered into the database table: mysql> select * from mytable;.
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
3 rows in set (0.00 sec)
2. Create a database abccs
mysql> Create DATABASE abccs;
Note the case sensitivity of different operating systems.
3. Select the database you have created
mysql> USE abccs
Database changed
At this point you are in the database you just created abccs.
4. Create a database table
First look at what tables exist in your database right now:
mysql> SHOW TABLES;
Empty set (0.00 sec)
This means that there is no database table in the database you just created. Let's create a database table mytable.
We are going to create a table of birthdays of your company's employees, the table will contain the employee's 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)
Since the column values for name and birthadd are variable, choose VARCHAR, which does not have to be 20. you can choose any length from 1 to 255, and if you need to change its word length later, you can use the Alter TABLE statement.) The gender is represented by a single character: "m" or "f", so CHAR(1) is chosen; the DATE data type is used for the BIRTH column.
After creating a table, we can look at the results of what we just did and use SHOW TABLES to show what tables are in the database:
mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| mytables |
+---------------------+
5. Show the structure of the table:
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'll start by using the Select command to view the data in the table:
mysql> select * from mytable;
Empty set (0.00 sec)
This indicates that the table just created has no records yet.
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 again to see what has changed. We can follow this method to add all the employee records to the table one by one.
7. Textual loading of data into a database table
It is cumbersome to enter them one by one. We can use a text file to add all the records to your database table. Create a text file "" containing one record per line, with values separated by locators (tabs) and given 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 text file "" into the mytable table:mysql> LOAD DATA LOCAL INFILE "" INTO TABLE pet;
Then use the following command to see if the data has been entered into the database table: mysql> select * from mytable;.