Create a Database in MySQL

MySQL can be an intimidating program. All of the commands have to be entered through a command prompt; there is no visual interface. Because of this, having basic knowledge of how to create and manipulate a database can save you a lot of time and headaches. Follow this guide to create a database of US states and their populations.

Steps

Creating and Manipulating a Database

  1. Create the database. From the MySQL command line, enter the command CREATE DATABASE <DATABASENAME>;. Replace <DATABASENAMEs> with the name of your database. It cannot include spaces.
    • For example, to create a database of all the US states, you might enter CREATE DATABASE us_states;
    • Note: Commands do not have to be entered in upper-case.
    • Note: All MySQL commands must end with ";". If you forgot to include the semicolon, you can enter just ";" on the next line to process the previous command.
  2. Display a list of your available databases. Enter the command SHOW DATABASES; to list all of the databases you have stored. Besides the database you just created, you will also see a mysql database and a test database. You can ignore these for now.
  3. Select your database. Once the database has been created, you will need to select it in order to begin editing it. Enter the command USE us_states;. You will see the message Database changed, letting you know that your active database is now us_states.
  4. Create a table. A table is what houses your database’s information. To create one, you will need to enter all of your table formatting in the initial 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 named "states" with three fields: id, state, and population.
    • The INT command will make the id field contain only numbers (integers).
    • The NOT NULL command makes sure that the id field cannot be left blank.
    • The PRIMARY KEY designates the id field as the key field in the table. The key field should be set to a field that cannot contain any duplicates.
    • The AUTO_INCREMENT command will automatically assign increasing values into the id field, essentially automatically numbering each entry.
    • The CHAR(characters) and INT(integers) commands designate the types of data allowed in those fields. The number next to the commands indicated how many characters or integers can fit in the field.
  5. Create an entry in the table. Now that the table has been created, it’s time to start entering your information. Use the following command to input your first entry: INSERT INTO states (id, state, population) VALUES (NULL, 'Alabama', '4822023');
    • This is essentially telling the database to enter the information provided into the three corresponding fields in the table.
    • Since the id field contains the command NOT NULL, entering NULL as the value will force it to automatically increment to 1, thanks to the AUTO_INCREMENT command.
  6. Create more entries. You can create multiple entries using a single command. To enter the next three states, use the following command:INSERT INTO states (id, state, population) VALUES (NULL, 'Alaska', '731449'), (NULL, 'Arizona', '6553255'), (NULL, 'Arkansas', '2949131');.
  7. Run a query on your new database. Now that the basic database has been created, you can enter queries to return specific results. First enter the command: SELECT * FROM states;. This will return your entire database, as signified by the "*" command, which means "all".
    • For a more advanced query, enter the following command: SELECT state, population FROM states ORDER BY population; This will return a table with the states sorted by population instead of by alphabetical listing. The id field will also not be displayed, since you only asked for the state and population entries.
    • To list the states by population in reverse order, enter the following command: SELECT state, population FROM states ORDER BY population DESC;. The DESC command will list them in descending order, which will sort it by high to low instead of low to high.[1]

Continuing with MySQL

  1. Install MySQL on your Windows PC. Find out how to install MySQL on your PC at home.
  2. Delete a MySQL database. If you need to trim some of your old outdated databases, follow this guide.
  3. Learn PHP and MySQL. Learning PHP and MySQL will enable you to create powerful websites for fun and for work.
  4. Back Up and Restore Data in MySQL. Backing up your data is always recommended, especially if it's an important database.
  5. Make Changes to the Database Structure in MySQL. If the needs of your database are changing, you may need to adjust the structure to handle different information. This guide will show you how.

Tips

  • Some commonly used data types: (For full list, see the mysql documentation at http://dev.mysql.com/doc/)
    • CHAR(length) - fixed length character string
    • VARCHAR(length) - variable length character string with max length length.
    • TEXT - variable length character string with max length of 64KB of text.
    • INT(length) - 32-bit integer with max length digits (the '-' is counted as a 'digit' for a negative number.)
    • DECIMAL(length,dec) - Decimal number up to total length display characters. The dec field indicates the maximum number of decimal places allowed.
    • DATE - Date value (year, month, date))
    • TIME - Time value (hours, minutes, seconds)
    • ENUM("value1","value2", ....) - List of enumerated values.
  • Some optional parameters:
    • NOT NULL - A value must be provided. The field cannot be left blank.
    • DEFAULT default-value - If no value is given, the default-value is assigned to the field.
    • UNSIGNED - For numeric fields, ensures that the number is never negative.
    • AUTO_INCREMENT - The value will be incremented automatically each time a row is added to the table.

Related Articles

Sources and Citations

  1. ↑ http://inpics.net/tutorials/mysql/started14.html
Retrieved from "https://kipkis.com/index.php?title=Create_a_Database_in_MySQL&oldid=273405"