Create the database and database table code with MySQL

  • 2020-05-07 20:32:40
  • OfStack

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 sensitivity of different operating systems to case.
3. Select the database that you created
mysql > USE abccs
Database changed
At this point you are in abccs, the database you just created.
4. Create a database table
First look at what tables now exist in your database:
mysql > SHOW TABLES;
Empty set (0.00 sec)
There are no database tables in the database you just created. Let's create a database table mytable:
We need to set up a birthday table for your employees, which contains their 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 birthadd are variable, VARCHAR is selected and its length is not 1 but 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. ; Gender only needs one character to indicate: "m" or "f", so CHAR(1) is used. The birth column USES the DATE data type.
After creating a table, we can look at the results we just did, 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
Let's first use the Select command to view the data in the table:
mysql > select * from mytable;
Empty set (0.00 sec)
This means that the table you just created 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 see what has changed. We can add all employee records to the table in this way, line by line.
Load data into a database table by text
If you type in one by one, it's a hassle. We can add all records to your database table as a text file. Create a text file "mysql.txt" with 1 record per line, separate the values with the locator (tab), and give them in the column order listed in the Create TABLE statement, for example:
abccs f china 1977-07-07
mary f 1978-12-12 usa
tom m 1970-09-02 usa
Load the text file "mytable.txt" into the mytable table using the following command :mysql > LOAD DATA LOCAL INFILE "mytable.txt" INTO TABLE pet;
Use the following command again to see if the data has been entered into the database table: mysql > select * from mytable;

Related articles: