Difference between revisions of "SQL"
m (→Towards SQL for P2P environments: MediaWikiLite & P2P Extension) |
m (→Documentation: no need for info box here) |
||
Line 71: | Line 71: | ||
== Documentation == | == Documentation == | ||
− | + | *[http://www.sql-tutorial.com/ SQL Tutorial] ''- A nice tutorial on SQL'' | |
*[http://dev.mysql.com/doc/index.html Official MySQL Documentation] | *[http://dev.mysql.com/doc/index.html Official MySQL Documentation] | ||
*[http://www.w3schools.com/sql/sql_join.asp W3C SQL Tutorial] [http://www.w3schools.com/sql/sql_quickref.asp (QuickRef)] | *[http://www.w3schools.com/sql/sql_join.asp W3C SQL Tutorial] [http://www.w3schools.com/sql/sql_quickref.asp (QuickRef)] |
Revision as of 06:32, 27 May 2008
SQL (Structured Query Language) is a computer language used to create, retrieve, update and delete data from relational database management systems. SQL has been standardised by both ANSI and ISO.
SQL is commonly spoken either as the names of the letters ess-cue-el, or like the word sequel. The official pronunciation of SQL according to ANSI is ess-cue-el. However, each of the major database products (or projects) containing the letters SQL has its own convention: MySQL is officially and commonly pronounced "My Ess Cue El"; PostgreSQL is expediently pronounced postgres (being the name of the predecessor to PostgreSQL); and Microsoft SQL Server is commonly spoken as Microsoft-sequel-server.
Common MediaWiki MySQL queries
Insert into interwiki
INSERT INTO interwiki (iw_prefix,iw_url,iw_local) VALUES('example','http://www.example.org/$1',0);
To delete entry;
DELETE FROM interwiki WHERE iw_prefix LIKE "example";
Backup and compress DB & FS
Reset a password
UPDATE user SET user_password=md5(CONCAT('184-',md5('password'))) WHERE user_id=184;
Reset or set a page hit counter
UPDATE page SET page_counter=0 WHERE page_title='Main_Page';
Adjust user groups
INSERT INTO user_groups (ug_user,ug_group) VALUES(999,'sysop');
Selecting current articles by category
See MediaWiki schema for a description of the tables. Essentially categorylinks store the category member relationships , page identifies the title and metadata information, revision identifies al revisions of an article, and text contains the actual wikitext of articles. there is a one to many relationship between pages and revisions, and a one to many relationship between text and revisions.
The database schema used by MediaWiki allows variable article content to be stored as key => value pairs where the atomic unit for the content varies depending on the content of the article. A way around this is to use categorization to group common atomic unit structure together. Basically this is a filtering problem, however queried atomic unit structure needs to be checked downstream by any downstream processing of content within categories.
Example
- In this example the prefix is set to 1120_, set it in Template:SQLprefix.
- In this example the category is set to Selenium, set it in Template:SQLcat.
- SELECT old_text FROM 1120_text, 1120_revision
- WHERE old_id = rev_text_id and rev_id IN (
- SELECT page_latest FROM 1120_page, 1120_categorylinks
- WHERE cl_to = "Portal" and cl_from = page_id );
Some SQL queries executable by admin (need fixing to work in new MW1.9.3 environment)
- Tables
- Drop
- Info
- Select User
- CL
- CUR
- OLD
- Delete
- Update
- IW
- IW-Update
- IW-Insert
- RC
- UR
- makeBot
- Reset Password
- Owner-Old
- Owner-Cur
- Sys:User articles
- xwAdmins
Documentation
- SQL Tutorial - A nice tutorial on SQL
- Official MySQL Documentation
- W3C SQL Tutorial (QuickRef)
MySQL vs MSSQL
MySQL News & Information
- Falcon Overview - the new MySQL 6 Falcon storage engine
- MySQL Stored procedures & triggers