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.
Method 1 of 2: Creating and editing the database
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.
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
mysqldatabase and a
testdatabase. You can ignore this one for now.
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
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:
- The command
INTensures that the field
idonly contain integers (integers).
- The command
NOT NULLensures that the field
idcannot be empty.
- The key
PRIMARY KEYindicates that the field
idthe key is from the table. The key field is unique and cannot contain duplicates.
- The command
AUTO_INCREMENTensures that the field
ideach time an entry/record is added, it is incremented, basically numbering each entry automatically.
- The codes
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.
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
NOT NULLhas, entering the value
NULLmake sure this field is automatically incremented by 1, thanks
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:
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
idis not shown, because you only asked for the inputs
- 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
DESCshows a list in reverse order, i.e. from highest to lowest.
Method 2 of 2: Moving on with MySQL
Step 1. Install MySQL on your Windows PC
Find out how to install MySQL on your home PC.
Step 2. Deleting a MySQL database
Step 3. Learn PHP and MySQL
Learning PHP and MySQL will enable you to create powerful websites for fun and work.
Step 4. Back up your MySQL databases
Making a copy of your data is always recommended, especially if it concerns an important database.
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.
- 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.