AVAJAVA Web Tutorials

The most basic way to create a table in a mysqlW databaseW is to use the 'mysql' tool, switch to the database in question and then to issue a CREATE TABLE statement.

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.

employees_table.sql

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

An easier way of creating a database table and inserting data into that table is to execute an SQLW script. This is covered in another lesson.