Difference between revisions of "SQL"

From Organic Design wiki
(Common MySQL queries: Adjust user groups)
(Insert into interwiki: Add DELETE)
Line 7: Line 7:
 
=== Insert into interwiki ===
 
=== Insert into interwiki ===
 
  INSERT INTO interwiki (iw_prefix,iw_url,iw_local) VALUES('example','http://www.example.org/$1',0);
 
  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 ===
 
=== Backup and compress DB & FS ===

Revision as of 21:47, 16 March 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

<bash>mysqldump awg -u db --password='password' | 7za a -si backupfile.sql.7z</bash>
<bash>tar cf - directory | 7za a -si backupfile.t7z</bash>

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');

Some SQL queries executable by admin (need fixing to work in new MW1.9.3 environment)

Documentation

MySQL vs MSSQL

MySQL News & Information

Towards SQL for P2P environments