Database Design   «Prev 

Create Database Table using SQL

SQL statements 
CREATE TABLE Employees(
 EmpID numeric,
 LastName varchar(20),
 FirstName varchar(15),
 HireDate date,
 Primary key (EmpID));
The table consists of the following columns:
  1. EmpID
  2. LastName
  3. FirstName
  4. HireDate
  5. The EmpID column is the primary key

  1. Create table Employees, Creates the Employees table.
  2. EmpID numeric, Creates the EmpID field and specifies that the data values entered in the field must be numbers.
  3. LastName varchar (20), Creates the LastName field and specifies that the data values entered in the field may contain up to (but no more than) 20 characters.
  4. FirstName varchar (15), Creates the FirstName field and specifies that the data values entered in the field may contain up to (but no more than) 15 characters.
  5. HireDate date, Creates the HireDate field.
  6. Primary key (EmpID), Creates a primary key field and specifies that EmpID is the primary key of the table.

Using Command Line Client of MySQL

After you install MySQL, give the Command Line Client a try. The following steps walk you through some simple database actions.
  1. Start the Command Line Client: Assuming you used a typical installation in Windows, open the Start menu and select 
    All Programs -> MySQL -> MySQL Server 5.0 -> MySQL Command Line Client.

    When it prompts you for the database's password, enter the password you used when you installed MySQL.
  2. List the available databases: Enter the command SHOW DATABASES;. (Remember to end each command with a semicolon and press Enter.) The Client should list the available databases running in MySQL.
  3. Select the mysql database: Enter the command USE mysql;. This makes the Client use the database named mysql.
  4. List the database's tables: Enter the command SHOW TABLES;. This makes the Client list the tables in the mysql database.
  5. Select some data: Most of the mysql database's tables will be empty, but the user table should hold one record for the root user that was created when you installed MySQL. Enter the command 
    SELECT user, password FROM user;
    The Client should list a single record showing the user name root and that user's password.
    You will not be able to read the password because it is encrypted.
  6. Create a new database: enter the command CREATE DATABASE testdb;. To select the new database, enter the command USE testdb;. If you execute the SHOW TABLES command now, you should find that the new database contains no tables.
  7. Create a table by using a SQL CREATE TABLE statement. This is a long, potentially complex statement that can span several lines. For this example, enter the command:

CREATE TABLE People (
FirstName VARCHAR(40) NOT NULL,
LastName VARCHAR(40) NOT NULL,
PRIMARY KEY (FirstName, LastName)
);