官术网_书友最值得收藏!

Database work with MySQL and SQL

Jumping back to the server, an understanding the technologies behind Joomla! can't be considered complete without a discussion on Structured Query Language (SQL). SQL is the language of choice for most databases, and anytime we want to add or retrieve data from the database of a Joomla! site, we will need to do it via SQL.

SQL is a relatively straightforward and simple language to pick up, but it can get quite complex at higher levels of development. It is designed purely to interact with data in a database and can do very little else.

The four most common SQL commands we will use are:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

There are others, but these are the ones we will need to know most.

Following one of these initial commands will usually be a preposition and a table name. Or it could be a comma separated list of columns, then the proposition and table name and is written in fairly easy to understand language. For example:

SELECT name, address FROM person_table …
INSERT INTO person_table …
UPDATE person_table …
DELETE FROM person_table …

For SELECT, UPDATE, and DELETE, there will also usually be a WHERE clause that provides the criteria upon which to do the search.

SELECT name, address FROM person_table WHERE age > "16"
UPDATE person_table … WHERE name = "John Doe"
DELETE FROM person_table WHERE name = "John Doe"

For INSERT queries, there will also be a set of values to insert, and possibly a list of columns to INSERT INTO.

INSERT INTO person_table (name, address, age)
VALUES ( "John Doe","10 This St, Atown","25")

For UPDATE queries, they are again a little different, but easy to understand.

UPDATE person_table SET address="25 Anew St, Atown" WHERE name = "John Doe"

Something we will sometimes see with SELECT statements is the use of an asterix (*) character in place of a column list. This is shorthand to indicate that we want to use all of the available columns, arranged in the same order as they appear in the database.

SELECT * FROM person_table WHERE age > "16"

Something we will quickly notice if we are looking over existing Joomla! database queries is that almost all of them will have a hash and two underscores before the table name. For example:

SELECT * FROM #__person_table

This hash underscore is replaced at run time with the current database prefix. So #__person_table will be converted into something similar to jos_person_table by the Joomla! database functions when they run the query.

The database prefix exists to allow people to install multiple Joomla! sites and use the same database for all of them, without the data for each one overwriting the others. The different sites can then just use different prefixes, and that way they keep all their data in separate tables.

Note

As with all of our programming topics, there is a lot more useful information available on the Internet regarding MySQL and the SQL programming language (sites such as http://www.w3schools.com/SQl/default.asp).

主站蜘蛛池模板: 柳林县| 温州市| 易门县| 苗栗市| 安顺市| 杭州市| 乌苏市| 济阳县| 荔波县| 武功县| 台州市| 安庆市| 大冶市| 麻栗坡县| 栾城县| 阳山县| 洪江市| 北碚区| 扎兰屯市| 海兴县| 吴堡县| 新津县| 通化市| 秦皇岛市| 龙里县| 永昌县| 黄大仙区| 内黄县| 夏邑县| 南充市| 温宿县| 余干县| 新泰市| 息烽县| 仁化县| 阳山县| 漳平市| 丹棱县| 榕江县| 加查县| 沾益县|