How to create a database in MySQL: 12 steps

MySQL can be a daunting program. All commands must be entered through the command line; there is no user-friendly interface. Therefore, knowing how to create and manipulate a database can save a lot of time and frustration. Follow the instructions to create a database of American states and their populations.

Steps

Method 1 of 2: Creating and Manipulating a Database

Step 1. Create a database

At the MySQL command line, enter the command

CREATE DATABASE;

… Replace with your database name. It cannot contain spaces.

• For example, to create a database of all American states, you can enter
  CREATE DATABASE us_states;

• Note: Commands do not need to be entered in uppercase.
• Note: All MySQL commands must end with ";". If you forgot to put a semicolon, then just enter ";" on the next line to start processing the previous command.

Step 2. Display the list of available databases

Enter the command

SHOW DATABASES;

to display a list of stored databases. In addition to the newly created database, you will also see the databases

mysql

and

test

… You can ignore them for now.

Step 3. Select a database

When the database is created, you need to select it to start editing. Enter the command

USE us_states;

… You will see a message

Database changed

which notifies that the currently active database is

us_states

.

Step 4. Create a table

A table is where information is stored in a database. To create a table, you need to enter its entire structure with one command. To create a table, enter the following command:

CREATE TABLE states (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, state CHAR (25), population INT (9));

… This command will create a table called "states" with three fields:

id

,

state

, and

population

.

• Command
  INT
  indicates that the field
  id
  will only contain numbers (integers).
• Command
  NOT NULL
  indicates that the field
  id
  must not be empty (required for input).
• PRIMARY KEY
  denotes that the field
  id
  is the key field in the table. A key field is a field that cannot contain the same values.
• Command
  AUTO_INCREMENT
  will automatically assign increasing values to the field
  id
  , essentially automatically numbering each record.
• Commands
  CHAR
  (symbols) and
  INT
  (integers) indicate the data type allowed in the corresponding fields. The number following the command indicates how many characters or numbers the field can contain.

Step 5. Create a record in the table

Now that the table has been created, it is time to enter the information. Use the following command to enter the first entry:

INSERT INTO states (id, state, population) VALUES (NULL, ‘Alabama’,’4822023’);

• This command essentially tells the database to store the information in the table in three corresponding fields.
• Since the field
  id
  contains an id
  NOT NULL
  then input
  NULL
  as a value, will cause it to increment by one, thanks to the identifier
  AUTO_INCREMENT
  .

Step 6. Create more records

You can save many entries with one command. To save three more states, enter the following command:

INSERT INTO states (id, state, population) VALUES (NULL, ‘Alaska’, ‘731449’), (NULL, ‘Arizona’, ‘6553255’), (NULL, ‘Arkansas’, ‘2949131’);

.

• Your table will now look like this:

Step 7. Query the database

Now that the simple database has been created, you can run queries to retrieve the information you want. First, enter the following command:

SELECT * FROM us_states;

… This query will return the entire database, as shown by the command "*" which means "all".

• For a more difficult query, enter a command like this:
  SELECT state, population FROM us_states ORDER BY population;
  This query will return a table with states sorted by population, instead of alphabetically sorted by name. Field
  id
  will not be displayed as you only asked for the fields
  state
  and
  population
  .
• To display the states by population in reverse order, use the following command:
  SELECT state, population FROM us_states ORDER BY population DESC;
  … Command
  DESC
  will display states in descending order of population (highest to lowest, not lowest to highest).

Method 2 of 2: Continuing with MySQL

Step 1. Install the MySQL database server on the computer

Learn how to install MySQL on your home computer.

Step 2. Delete the MySQL database

Learn how to delete a database if you need to delete old and unnecessary information.

Step 3. Learn MySQL and PHP

Knowledge of PHP and MySQL will enable you to create powerful websites for fun and work.

Step 4. Back up your data to MySQL

It is always recommended to back up your data, especially if the database is critical.

Step 5. Make changes to the database structure in MySQL

If the requirements for the database change, then you can always adjust the structure to store different information.

Advice

• Below are some of the most commonly used data types: (For a complete list, see the mysql documentation at
  • CHAR(length) - a string with a fixed number of characters "length".
  • VARCHAR(length) - a string with a different number of characters, but the maximum number of characters is "length".
  • TEXT - a string with a different number of characters, but the maximum number of characters is 64KB of text.
  • INT(length) - 32-bit number with the maximum number of digits length ('-' counts as 'digit' for a negative number.)
  • DECIMAL(length, dec) - A decimal number with a maximum "length" of the displayed digits. The dec field indicates the maximum number of decimal places.
  • DATE - Date (year, month, day))
  • TIME - Time (hours, minutes, seconds)
  • ENUM("value1", "value2",….) - List of enumerated values.
• Some optional parameters:
  • NOT NULL - A value must be entered. The field cannot be empty.
  • DEFAULT value - If no value is entered, then the value "value" is assigned to the field.
  • UNSIGNED - For numeric fields. Indicates that the number in the field cannot be negative.
  • AUTO_INCREMENT - The value will be automatically incremented by one each time a record is added to the table.