How to create a Test Table in MySQL
- Go to the Windows Start menu, choose Run, type mysql -u root -p, and press Enter. The program will then prompt you for the password.
- When the MySQL, Monitor starts, it provides its own prompt. At this prompt (mysql>), you type commands used to create tables, explain tables, insert data, select data, and so on. Get used to ending your commands with a semicolon (;) because it’s a common instruction terminator that is used in PHP as well. Now that you’ve connected to the MySQL Monitor, you need to tell it which database to use.
- At the prompt, type use testDB; and press Enter. The MySQL Monitor will respond with Database changed if the database exists and you have permission to access it.
- It’s time to create a test table. This table will have a column for an ID number and a column for some test.
- At the prompt, type
create table test_table (test_id int, test_note text);
- and press Enter. This statement cretes a table called test_table. Within the table, it creates a column called test_id of type int(integer). It also creates a column called test_note of type text.
The MySQL Monitor will respond with Query OK. it will also tell you how many rows were affected and how long it took to complete the task.
- Verify the table creation by the typing show tables; and pressing Enter.
The MySQL Monitor will respond with a list of all the tables in the current database.
- The verify the filed names and type in a specific table, use the explain command. In this case, type explain test_table ; and press Enter.
The MySQL Monitor will respond with a list of all the fields and their types in the selected table. This is a very handy command to use to keep track of your table design.
It’s time to insert a few rows of data in your table, because this is getting pretty boring.
The first row will have an ID of 1, and the note will be “This is a Note.”
To insert this row type :
insert into test_table values(‘1’, ‘This is a Note.’);
and press Enter.
- Insert another row by typing :
insert into test_table values (’99’, ‘Look ! Another note.’);
and press Enter.
Now that you have some data in your table, even if it is only two rows.