Content

• To step
• Method 1 of 2: Creating and editing the database
• Method 2 of 2: Continue with MySQL
• Tips

MySQL can be quite an intimidating program. All commands must be entered from 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 headaches. Follow the guide below to create a database of U.S. states. and the number of residents per state.

To step

Method 1 of 2: Creating and editing the database

1. Create the database. From the MySQL command line, run the command CREATE DATABASE DATABASENAME>; in. Replace DATABASENAMEs> 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 need to be capitalized.
   • Note: All MySQL commands must be terminated with a ";". If you forget that, you can just ";" on the next line to be able to execute the previous command anyway.
2. A list of your available databases is displayed. Run the command SHOW DATABASES; to display a list of all the databases you have saved. Besides the database you just created, you also see a mysql database and a test database. You can ignore this for now.
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 will now see the message Database changed to let you know that your active database is now us_states.
4. Create a table. A table is where all data from your database is stored. To create this you will first have to enter all the formatting of the table 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" and with 3 fields: id, state, and population.
   • The INT command ensures that the field id may only contain integers (integers).
   • The NOT NULL command ensures that the id field cannot be empty.
   • The PRIMARY KEY key indicates that the id field is the key of the table. The key field is unique and cannot contain duplicates.
   • The AUTO_INCREMENT command causes the id field to be incremented every time an entry / record is added, basically numbering every 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 can fit in the field.
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, you are telling the database to put the data in the three corresponding fields of the table.
   • Since the field id has the property NOT NULL, entering the value NULL will cause this field to be automatically incremented by 1, thanks to AUTO_INCREMENT.
6. Make even more entries. You can create multiple entries using a single command. To enter the following 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:
     
     Query your new database. Now that the database has been created, you can run 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, which means "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 by population size, rather than alphabetically. The id field is not shown, because you only asked for the entries state and population.
     • To display the population of states in reverse order, use the following query: SELECT state, population FROM us_states ORDER BY population DESC ;. The DESC query returns a list in reverse order, from highest to lowest.

Method 2 of 2: Continue with MySQL

1. Install MySQL on your Windows PC. Find out how to install MySQL on your home PC.
2. Removing a MySQL database.
3. Learn PHP and MySQL. Learning PHP and MySQL will enable you to create powerful websites for fun and for work.
4. Back up your MySQL databases. Making a copy of your data is always recommended, especially if it concerns an important database.
5. Changing the structure of your database. If the database is to be used in a different way, you may have to change its structure to handle different data.

Tips

• Some commonly used data types: (For a complete list, see the mysql documentation at http://dev.mysql.com/doc/)
  • CHAR(length) - The number of characters in a string / character 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 denoted by length (The "-" is counted as a digit to a negative number.)
  • DECIMAL(length,dec) - Decimal number with number of digits indicated by length. The field dec 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.
  • DEFAULTdefault-value - If no value is specified, the default value assigned to the field.
  • UNSIGNED - For numeric fields, make sure that the number is never negative.
  • AUTO_INCREMENT - The value is automatically incremented each time a row is added to the table.