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
SELECT Commands are important!
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
] State is optional. The whole line between
] State may be repeated several times.
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
PersonsCreated with 3 columns:
IdIf 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.
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 ', ' email@example.com ' )
- This command assumes that the tables are people and comments created. If necessary, look in the previous chapter to read how to do this.
- Open http://localhost/phpmyadmin/, and go to the commentstable.
- Choose right for the SQL tab ().
- In the window that appears, paste the above sample script.
- Press Go (or start) to run the query against the database responses (as it is called).
- The result is that Piet Jansen is inserted into the people table.
- Add 4 other (fictitious) individuals by changing the values in the query and redialing them.
- 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 ' )
- 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).
- We now have in the table persons 4 rows and in the table comments 6 rows.
- Check this by using the Structure tab () to watch.
6.3. DELETEDeleting from the database is very simple, but also dangerous. Before you know it you throw a whole table empty! So be careful if you
DELETECommand used! The
DELETECommand looks like this:
DELETE FROM Table Name [WHERE condition [...]]Omitting the
WHERESection will empty the entire table. The
WHERERestriction could look like:
WHERE ID = 5.
- Open again the tab SQL in PhpMyAdmin
- Create a query that deletes 1 row from the comments table, make use of the example you just mentioned.
- Run this query.
- We now have in the table persons 4 rows and in the table comments 5 rows.
- Check this again by looking in the Structure tab.
6.4. UPDATEIt 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
UPDATECommand. The structure (or Syntax) of this is:
UPDATE Table Name SET Column name = new [,... =... [, ...]] [WHERE condition [...]]
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 = ' firstname.lastname@example.org ' WHERE id = 1
SELECTis 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
WHERERestriction 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:
6.5.1. JOINSIt 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
- If you know how to run a query, check the previous commands
- Run the query above
- View the result
- 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!