This article starts with the basics to help you open the MySQL database door. 1. use the SHOW statement to find out the existing Database: SHOWDATABASES; + ---------- + | Database | + ---------- + | mysql | test | + ---------- + 3 rowsinset (0.00sec) 2. create a database abccs: mysqlCREATEDATABASE
This article starts with the basics to help you open the MySQL database door.
1. use the SHOW statement to find out which database exists at the moment:
> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | +----------+ 3 rows in set (0.00 sec) |
2. CREATE a DATABASE abccs: mysql> create database abccs. Note that the DATABASE is 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 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; |