Email This List Email This List Print This List Print This List

Cre­at­ing and manip­u­lat­ing a SQL data­base

Cre­at­ing and Manip­u­lat­ing a Data­base

  1. Create a Database in MySQL Step 1 Version 2.jpg
    1
    Cre­ate the data­base. From the MySQL com­mand line, enter the com­mandCREATE DATABASE <DATABASENAME>;. Replace <DATABASENAMEs> with the name of your data­base. It can­not include spaces.
    • For example, to cre­ate a data­base of all the US states, you might enter CREATE DATABASE us_states;
    • Note: Com­mands do not have to be entered in upper-case.
    • Note: All MySQL com­mands must end with “;”. If you for­got to include the semi­colon, you can enter just “;” on the next line to pro­cess the pre­vi­ous com­mand.
  2. Create a Database in MySQL Step 2 Version 2.jpg
    2
    Dis­play a list of your avail­able data­bases. Enter the com­mand SHOW DATABASES; to list all of the data­bases you have stored. Besides the data­base you just cre­ated, you will also see a mysql data­base and a test data­base. You can ignore these for now.
  3. Create a Database in MySQL Step 3 Version 2.jpg
    3
    Select your data­base. Once the data­base has been cre­ated, you will need to select it in order to begin edit­ing it. Enter the com­mand USE us_states;. You will see the mes­sage Database changed, let­ting you know that your act­ive data­base is nowus_states.
  4. Create a Database in MySQL Step 4 Version 2.jpg
    4
    Cre­ate a table. A table is what houses your database’s inform­a­tion. To cre­ate one, you will need to enter all of your table format­ting in the ini­tial com­mand. To cre­ate a table, enter the fol­low­ing com­mand: CREATE TABLE states (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, state CHAR(25), population INT(9));. This will cre­ate a table named “states” with three fields: id, state, and population.
    • The INT com­mand will make the id field con­tain only num­bers (integers).
    • The NOT NULL com­mand makes sure that the id field can­not be left blank.
    • The PRIMARY KEY des­ig­nates the id field as the key field in the table. The key field should be set to a field that can­not con­tain any duplic­ates.
    • The AUTO_INCREMENT com­mand will auto­mat­ic­ally assign increas­ing val­ues into the id field, essen­tially auto­mat­ic­ally num­ber­ing each entry.
    • The CHAR(char­ac­ters) and INT(integers) com­mands des­ig­nate the types of data allowed in those fields. The num­ber next to the com­mands indic­ated how many char­ac­ters or integers can fit in the field.
  5. Create a Database in MySQL Step 5 Version 2.jpg
    5
    Cre­ate an entry in the table. Now that the table has been cre­ated, it’s time to start enter­ing your inform­a­tion. Use the fol­low­ing com­mand to input your first entry:INSERT INTO states (id, state, population) VALUES (NULL, ‘Alabama’, ‘4822023’);
    • This is essen­tially telling the data­base to enter the inform­a­tion provided into the three cor­res­pond­ing fields in the table.
    • Since the id field con­tains the com­mand NOT NULL, enter­ing NULL as the value will force it to auto­mat­ic­ally incre­ment to 1, thanks to the AUTO_INCREMENTcom­mand.
  6. 6
    Cre­ate more entries. You can cre­ate mul­tiple entries using a single com­mand. To enter the next three states, use the fol­low­ing com­mand:INSERT INTO states (id, state, population) VALUES (NULL, ‘Alaska’, ‘731449’), (NULL, ‘Arizona’, ‘6553255’), (NULL, ‘Arkansas’, ‘2949131’);. This will cre­ate a table that looks like the fol­low­ing:
Your MySQL Data­base
Fields id state pop­u­la­tion
Records: 1 Alabama 4822023
2 Alaska 731449
3 Ari­zona 6553255
4 Arkan­sas 2949131
Create a Database in MySQL Step 6 Version 2.jpg
  1. Create a Database in MySQL Step 7 Version 2.jpg
    1
    Run a query on your new data­base. Now that the basic data­base has been cre­ated, you can enter quer­ies to return spe­cif­ic res­ults. First enter the com­mand:SELECT * FROM us_states;. This will return your entire data­base, as sig­ni­fied by the “*” com­mand, which means “all”.
    • For a more advanced query, enter the fol­low­ing com­mand: SELECT state, population FROM us_states ORDER BY population; This will return a table with the states sor­ted by pop­u­la­tion instead of by alpha­bet­ic­al list­ing. The idfield will also not be dis­played, since you only asked for the state andpopulation entries.
    • To list the states by pop­u­la­tion in reverse order, enter the fol­low­ing com­mand:SELECT state, population FROM us_states ORDER BY population DESC;. The DESC com­mand will list them in des­cend­ing order, which will sort it by high to low instead of low to high.[1]

Related Post

admin has written 133 articles