How do I create a table in a MySQL database?
Author: Deron Eriksson
Description: This tutorial describes creating a table in MySQL.
Tutorial created using: Windows XP || MySQL 5.0.27
To begin with, I'll edit the my.ini file's [client] section to access my local mysql with user 'testuser' and password 'testpassword'. This file is located in my MySQL installation directory. After doing this, I can access mysql without passing it any parameters at start-up.
[client] port = 3306 host = localhost user = testuser password = testpassword
In the example below, I log on to mysql and then switch to the 'testdatabase' database that was created in another lesson. Following that, I drop the 'employees' table if it already exists and then create the 'employees' table and add three rows to the newly created table. Following that, I display the contents of the table via a SELECT statement.
These statements are included in the employees_table.sql script below.
USE testdatabase; DROP TABLE IF EXISTS employees; CREATE TABLE employees (id INT, first_name VARCHAR(20), last_name VARCHAR(30)); INSERT INTO employees (id, first_name, last_name) VALUES (1, 'John', 'Doe'); INSERT INTO employees (id, first_name, last_name) VALUES (2, 'Bob', 'Smith'); INSERT INTO employees (id, first_name, last_name) VALUES (3, 'Jane', 'Doe'); SELECT * FROM employees;
Note that you can add the database to your my.ini file. If you do this, you don't need to specify the 'USE testdatabase;' statement in the example above.
[client] port = 3306 host = localhost user = testuser password = testpassword database = testdatabase