SQL

From Conservapedia
Jump to: navigation, search

SQL, which stands for "Structured Query Language" is a computer language for relational database systems. It was first developed by EF Codd at IBM. In 1986 the American National Standards Institute (ANSI) promulgated standards for SQL, and since then revisions have been approved by the International Organization for Standardization (ISO). SQL was immensely successful, and is supported by a number of database systems, including MySQL. MySQL is one of the two databases supported by MediaWiki (the other being PostgreSQL),[1] the software behind a number of wiki-based systems, including Conservapedia.

Transactions

A transaction may include several SQL statements, and the sequence of statements are executed atomically. The transaction represents a logical unit work such as a customer purchasing a product. For example, the statements may include adding the purchase price to the customer's account, and subtracting the product from inventory. If either statement is executed, they must both be executed, and if either statement is aborted, they must both be aborted. This is achieved by using the COMMIT statement and the ROLLBACK statement. Once all of the statements to execute for the current transaction have been entered, the COMMIT statement is entered, and the transaction is committed to the database. If any of the statements in the transaction cannot be completed, the entire transaction is rolled back, and none of the statements take effect.

Transactions exhibit their own properties such as an isolation level. The transaction isolation level determines the level of interactivity with other concurrently running transactions. By default each transaction is conceptually isolated from all other transactions, thereby preventing two (or more) transactions from concurrently reading and/or updating the same data records.

Example Queries

SQL statements come in many forms, but the most common types are SELECT, INSERT, and UPDATE. DELETE is also used, but far less frequently.

SELECT * FROM customer_data WHERE customer_id = 5;

This would be used by an application to read information about a specific customer.

UPDATE customer_data SET customer_firstname = 'Joe' WHERE customer_id = 5;

This statement simply sets the name of customer 5 to be Joe.

INSERT customer_data (customer_name, customer_username, customer_password) VALUES ('Mary', 'Mary7', MD5('password'));

Adding a new customer named Mary. Her password is MD5 hashed for security purposes.

DELETE FROM customer_data WHERE customer_id = 15;

Whoever customer 15 is, their account has just been deleted.

Select

As a further example, suppose a database contains only the following table:

customer_id last_name first_name city
10039 Smith John Huston
10178 Cole Christen Wortham
10052 Franks Andrew Austin
10131 Klinfelt Mark Thornberry
10014 Franklin Mary Austin

If the following query is run:

SELECT *
FROM customers
WHERE city = 'Austin'
ORDER BY last_name ASC;

then these results would be returned:

customer_id last_name first_name city
10014 Franklin Mary Austin
10052 Franks Andrew Austin

References

  1. http://www.mediawiki.org/wiki/Manual:LocalSettings.php#Database_settings