Learnit Training

6. SQL

In This chapter you will learn to create and use simple SQL (structured Query Language) queries. If you want to know more about this, please consider following a training SQL base .

The following is only meant to give you an idea of what SQL is, so we can use it in the next chapter. In particular the INSERT And SELECT Commands are important!

6.1. CREATE

In the previous chapter, if you have studied the SQL script that created the tables in the database, you will probably CREATE have seen commands. With CREATE You can create new tables within the database. The structure of CREATE is as follows:

CREATE Table Name (
  Data type column name [integrity rule],
  [...  ...  [...], ]
  PRIMARY KEY (column name)
) [Table specification]

Note: The above notation is all words in capital letters called Key words, everything between [ And ] State is optional. The whole line between [ And ] State may be repeated several times.

A completed example looks like this:
CREATE TABLE ' People ' (
  ' ID ' int (11) NOT NULL auto_increment,
  ' Name ' varchar (50) collate latin1_general_ci NOT NULL,
  ' Email ' varchar (100) Collate latin1_general_ci default NULL,
  PRIMARY KEY (' id ')
) ENGINE = MyISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci;
A table is Persons Created with 3 columns: Id, Name And Email. The column Id If Primary key and receives the property auto_increment With it, which means that the value is always incremented by 1 relative to the previous one, so that no duplicate values arise.

6.2. INSERT

The INSERT command is used to add rows in a table. For example, We can put some visitors with comments in the database. We can also Processing. php Include script to send information from the form to the database.

The structure is as follows:

INSERT INTO Persons
  [(Column name,
    ...)]
  VALUES
    field value [,
    Field
    ...]
  )

An example of this is:

INSERT INTO Persons
  Name
   Email
  VALUES
    ' Piet Jansen ',
    ' piet@email.nl '
  )

Contract 6.1.

  1. This command assumes that the tables are people and comments created. If necessary, look in the previous chapter to read how to do this.
  2. Open http://localhost/phpmyadmin/, and go to the commentstable.
  3. Choose right for the SQL tab (Sql).
  4. In the window that appears, paste the above sample script.
  5. Press Go (or start) to run the query against the database responses (as it is called).
  6. The result is that Piet Jansen is inserted into the people table.
  7. Add 4 other (fictitious) individuals by changing the values in the query and redialing them.
  8. Also run the script below
    INSERT INTO Comments
      (persoon_id,
       Response
       Date
      VALUES
        ' 1 ',
        ' This is Piet's reaction! ',
        ' 2007-12-01 15:00:00 '
      )
    
  9. Add six comments yourself, and link them to the people you just created by persoon_id (if there is a good persoon_id 1 through 4), so there are two people with two comments (or ꨮ person with three).
  10. We now have in the table persons 4 rows and in the table comments 6 rows.
  11. Check this by using the Structure tab (Structure) to watch.

6.3. DELETE

Deleting from the database is very simple, but also dangerous. Before you know it you throw a whole table empty! So be careful if you DELETE Command used! The DELETE Command looks like this:
DELETE FROM Table Name
[WHERE condition [...]]
Omitting the WHERE Section will empty the entire table. The WHERE Restriction could look like: WHERE ID = 5.

Contract 6.2.

  1. Open again the tab SQL in PhpMyAdmin
  2. Create a query that deletes 1 row from the comments table, make use of the example you just mentioned.
  3. Run this query.
  4. We now have in the table persons 4 rows and in the table comments 5 rows.
  5. Check this again by looking in the Structure tab.

6.4. UPDATE

It may happen that something needs to be changed in the database data. Of course, you can delete a row and add the custom row again. But it can be easier with the UPDATE Command. The structure (or Syntax) of this is:
UPDATE Table Name
SET Column name = new [,... =... [, ...]]
[WHERE condition [...]]

Omitting the WHERE In this case, restriction ensures that ૬e rows in the table are adjusted! It is also possible to adjust multiple columns at once. For example, Name And Email Address in the table Persons Be adjusted at the same time.

A query to change for example Piet in Klaas (in the unrealistic case that Piet changed his name to Klaas) looks like this:

UPDATE people
SET name = ' Klaas Jansen ', email = ' klaas@email.nl '
WHERE id = 1

6.5. SELECT

SELECT is probably the most used query, the function of this command is to question the data, the data can not be changed or deleted. The simplest structure is:
SELECT column name [, ColumnName [,...]]
FROM Table Name
[WHERE condition [...]]
Here it causes omitting WHERE Restriction for rendering of ૬e rows. For example, if we have all the e-mail addresses of people named Ali Jaber We will execute the following query:
SELECT Email
FROM people
WHERE name = ' Klaas Jansen '
Running this query provides a screen similar to the following:

Select

6.5.1. JOINS

It is often that we want to combine data from multiple tables. It takes too far to give a full explanation of how so-called Joins Work exactly, but using Joins Is this possible. The following is a simple example that includes all comments with the author's name and email address.
SELECT people. Name, people. Email, comments. Comment, comments. date
FROM people, comments
WHERE comments. persoon_id = personen.id

Contract 6.3.

  1. If you know how to run a query, check the previous commands
  2. Run the query above
  3. View the result
  4. If one person has written multiple messages, it also appears multiple times in the result, whereas the name and e-mail address are only one time in the People table!