MySQL statement creates database and database table

web front end seven thousand nine hundred and forty-three 13 years ago (2011-12-15)

1. Use the SHOW statement to find out when The server What currently exists on data base  

 

 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
At this point, you have entered the database abccs you just created

4. Create a database table

First, let's see what tables exist in your database:
    mysql> SHOW TABLES;
    Empty set (0.00 sec)
It indicates that there is no database table in the database just created. Next, create a database table mytable:
We want to build you company Employee's birthday table, which contains 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 of name and birthd change, VARCHAR is not necessarily 20 long. You can select any length from 1 to 255. If you need to change its word length in the future, you can use the ALTER TABLE statement.); Gender can be represented by one character: "m" or "f", so CHAR (1) is selected; The birthday column uses the DATE data type.

After creating a table, we can take a look at the results just made, and use SHOW TABLES to display which tables are in the database:

 

 mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| mytables |
+---------------------+

 

5. Display 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

First, we 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 just created has not been recorded.

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 see what has changed. In this way, we can add the records of all employees to the table one by one.

7. Load data into a database table in text mode

It is troublesome to input one by one. We can add all records to your database tables in the form of text files. Create a text file "mysql.txt". Each line contains a record. Use the 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 text file "mytable. txt" into the mytable table: mysql>LOAD DATA LOCAL INFILE "mytable. txt" INTO TABLE pet;

Then use the following command to check whether the data has been entered into the database table: mysql>select * from mytable;