Setting up a database in MySQL: 12 steps (with pictures)

Table of contents:

Setting up a database in MySQL: 12 steps (with pictures)
Setting up a database in MySQL: 12 steps (with pictures)
Anonim

MySQL can be quite an intimidating program. All commands must be entered through the Command Prompt; there is no graphical interface. That is why it is important to have some basic knowledge of creating and editing a database so that you can save yourself a lot of time and headache. Follow the guide below to create a database of US states. and the number of inhabitants per state.

Steps

Method 1 of 2: Creating and editing the database

Create a Database in MySQL Step 1

Step 1. Create the database

From the MySQL command line, run the command

CREATE DATABASE;

in. Substitute for the name of your database, without spaces.

  • For example, to create a database of all US states, enter the following code:

    CREATE DATABASE us_states;

  • Note: Commands do not have to be entered in capital letters.
  • Note: All MySQL commands must be terminated with a ";". If you forget that, you can just use ";" on the next line, to still be able to execute the previous command.
Create a Database in MySQL Step 2

Step 2. A list of your available databases will be displayed

Enter the command

SHOW DATABASES;

to display a list of all the databases you have saved. In addition to the database you just created, you will also see a

mysql

database and a

test

database. You can ignore this one for now.

Create a Database in MySQL Step 3

Step 3. Select your database

Once the database has been created, you will need to select it to start editing it. Enter the following command:

USE us_states;

. You now see the message

Database changed

, to let you know that your active database is now

us_states

is.

Create a Database in MySQL Step 4

Step 4. Create a table

A table is where all the data of your database is stored. To create this you will first have to enter all the table formatting in the first 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 will create a table called "states" with 3 fields:

id

,

state

, and

population

.

  • The command

    INT

    ensures that the field

    id

    only contain integers (integers).
  • The command

    NOT NULL

    ensures that the field

    id

    cannot be empty.
  • The key

    PRIMARY KEY

    indicates that the field

    id

    the key is from the table. The key field is unique and cannot contain duplicates.
  • The command

    AUTO_INCREMENT

    ensures that the field

    id

    each time an entry/record is added, it is incremented, basically numbering each entry automatically.
  • The codes

    CHAR

    (characters) and

    INT

    (integers) indicate which type of data may be entered in those fields. The number next to the commands indicates how many characters will fit in the field.
Create a Database in MySQL Step 5

Step 5. Create an entry in the table

Now that the table has been created it is time to start entering data. Use the following command for the first entry:

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

  • Essentially, this tells the database to put the data in the three corresponding fields of the table.
  • Because the field

    id

    the property

    NOT NULL

    has, entering the value

    NULL

    make sure this field is automatically incremented by 1, thanks

    AUTO_INCREMENT

    .
Create a Database in MySQL Step 6

Step 6. Create even more entries

You can create multiple entries using a single command. To enter the next 3 states, type the following:

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

.

  • This will create a table that looks like this:

    Screenshot_20150730_103118
Create a Database in MySQL Step 7

Step 7. Run a query on your new database

Now that the database has been created, you can start running queries to get specific results. First enter the following command:

SELECT * FROM us_states;

. This returns the entire database, because of the asterisk "*" in the command, meaning "all" or all.

  • A more advanced query is the following:

    SELECT state, population FROM us_states ORDER BY population;

    This returns a table of the states in order of population size, rather than alphabetically. The field

    id

    is not shown, because you only asked for the inputs

    state

    and

    population

    .
  • To show the population of the states in reverse order, use the following query:

    SELECT state, population FROM us_states ORDER BY population DESC;

    . The query

    DESC

    shows a list in reverse order, i.e. from highest to lowest.

Method 2 of 2: Moving on with MySQL

Create a Database in MySQL Step 8

Step 1. Install MySQL on your Windows PC

Find out how to install MySQL on your home PC.

Create a Database in MySQL Step 9

Step 2. Deleting a MySQL database

Create a Database in MySQL Step 10

Step 3. Learn PHP and MySQL

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

Create a Database in MySQL Step 11

Step 4. Back up your MySQL databases

Making a copy of your data is always recommended, especially if it concerns an important database.

Create a Database in MySQL Step 12

Step 5. Changing the structure of your database

If the database needs to be used in a different way, you may have to change its structure to deal with different data.

Tips

  • Some commonly used data types: (For a full list, see mysql's documentation at

    • CHAR(length) – The number of characters of a string/string
    • VARCHAR(length) – Variable number of characters of a string with a max length of length.
    • TEXT - Variable number of characters of a string with a max. length of 64KB of text.
    • INT(length) - 32-bit integer with a max number of digits indicated by length (the '-' is counted as a digit with a negative number.)
    • DECIMAL(length, dec) - Decimal number with number of digits indicated by length. The dec field indicates the maximum number of decimal places allowed.
    • DATE - Date (year, month, date))
    • TIME - Time (hours, minutes, seconds)
    • ENUM(" value1 ", " value2 ", ….) - List of enumeration values.
  • Some optional parameters:

    • NOT NULL - A value must be provided. The field cannot be left empty.
    • DEFAULT default-value - If no value is specified, the default value will be assigned to the field.
    • UNSIGNED - For numeric fields, make sure the number is never negative.
    • AUTO_INCREMENT - The value is automatically incremented each time a row is added to the table.

Popular by topic