Difference between revisions of "SQL"

From Organic Design wiki
m (RODBC)
(rm old info and add intro to joins)
(22 intermediate revisions by 3 users not shown)
Line 1: Line 1:
{{Merge|MySQL}}
+
{{cleanup}}
 
 
 
 
 
'''[[w:SQL|SQL]]''' ('''Structured Query Language''') is a computer language used to create, retrieve, update and delete data from [[w:relational database management system|relational database management system]]s. SQL has been standardised by both [[w:American National Standards Institute|ANSI]] and [[w:International Organization for Standardization|ISO]].   
 
'''[[w:SQL|SQL]]''' ('''Structured Query Language''') is a computer language used to create, retrieve, update and delete data from [[w:relational database management system|relational database management system]]s. SQL has been standardised by both [[w:American National Standards Institute|ANSI]] and [[w:International Organization for Standardization|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 [[w:ANSI|ANSI]] is ''ess-cue-el''. However, each of the major database products (or projects) containing the letters ''SQL'' has its own convention: [[w:MySQL|MySQL]] is officially and commonly pronounced "''My Ess Cue El''"; [[w:PostgreSQL|PostgreSQL]] is expediently pronounced ''postgres'' (being the name of the predecessor to PostgreSQL); and [[w:Microsoft SQL Server|Microsoft SQL Server]] is commonly spoken as ''Microsoft-sequel-server''. See [[MediaWikiLite]] for information about [http://www.sqlite.org SQLite] support in MediaWiki, or [[MSSQL]] for information about Microsoft SQL Server support.
+
''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 [[w:ANSI|ANSI]] is ''ess-cue-el''. However, each of the major database products (or projects) containing the letters ''SQL'' has its own convention: [[w:MySQL|MySQL]] is officially and commonly pronounced "''My Ess Cue El''"; [[w:PostgreSQL|PostgreSQL]] is expediently pronounced ''postgres'' (being the name of the predecessor to PostgreSQL); and [[w:Microsoft SQL Server|Microsoft SQL Server]] is commonly spoken as ''Microsoft-sequel-server''. See [[MediaWikiLite]] for information about [http://www.sqlite.org SQLite] support in MediaWiki, or [[MSSQL]] for information about Microsoft SQL Server support. We're now using [[MariaDB]] for our SQL server which is a drop-in replacement for MySQL but is fully open source and more efficient too.
 
 
== Common MediaWiki MySQL queries ==
 
=== Insert into interwiki ===
 
{{Code|<PHP>
 
INSERT INTO interwiki (iw_prefix,iw_url,iw_local)
 
VALUES('example','http://www.example.org/$1',0);
 
</PHP>
 
}}
 
To delete entry;
 
{{Code|<PHP>
 
DELETE FROM interwiki
 
WHERE iw_prefix LIKE "example";
 
</PHP>
 
}}
 
=== Backup and compress DB & FS ===
 
{{code|
 
<bash>
 
mysqldump -u [user] --password='**********' -A > | 7za a -si backupfile.sql.7z # Backup
 
tar cf - directory | 7za a -si backupfile.t7z
 
</bash>
 
}}
 
 
 
{{code|
 
<bash>
 
mysqldump -u [user] --password='**********' --single-transaction -B [database] > backupfile.sql # Backup
 
mysql -u [user] -p [database] < backupfile.sql # Restore
 
</bash>
 
}}
 
 
 
=== Reset a password ===
 
first identify the username you want to reset;
 
{{Code|<PHP>
 
SELECT user_id, user_name from user
 
WHERE user_name="WikiSysop";
 
</PHP>
 
}}
 
Then update that users password. Note that the password is a nested concatenation of both the md5 of the user_id combined with the md5 password.
 
 
{{Code|<PHP>
 
UPDATE user
 
SET user_password=md5(CONCAT('1-',md5('password')))
 
WHERE user_id=1;
 
</PHP>
 
}}
 
 
 
=== 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==
+
== Backup & restore ==
See [http://upload.wikimedia.org/wikipedia/commons/4/41/Mediawiki-database-schema.png 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.
+
See [[backup]] for details about this as it's doesn't outside the SQL environment so doesn't belong in this article. But don't forget to set the default character set to the same as the dump used when you import the data!
  
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.  
+
To restore only a single database from a full dump use the following:
 +
<source lang="bash">
 +
mysql -D DBNAME -o DBNAME < dump.sql
 +
</source>
  
===Example===
+
== Fixing crashed tables ==
*In this example the prefix is set to  '''{{SQLprefix}}''', set it in [[Template:SQLprefix]].
+
To fix a crashed table just use '''repair TABLENAME''' from the MySQL command line, or to automatically scan all tables and fix them run the following from the shell.
*In this example the category is set to '''{{SQLcat}}''', set it in [[Template:SQLcat]].
+
<source lang="bash">
 +
mysqlcheck --auto-repair DBNAME -u USER -p
 +
</source>
  
:SELECT  old_text FROM {{SQLprefix}}text, {{SQLprefix}}revision
+
== Database procedures ==
:WHERE old_id = rev_text_id and rev_id IN (
+
*[[Reset MySQL root password]]
:SELECT page_latest FROM {{SQLprefix}}page, {{SQLprefix}}categorylinks
+
*[[Renaming MySQL tables and databases]]
:  WHERE cl_to = "Portal" and cl_from = page_id );
+
*[[Rename a MediaWiki database]]
 +
*[[Reset a wiki user password]]
 +
*[[Set a user preference]]
 +
*[[Manually backup a wiki]]
 +
*[[Add a wiki database]]
  
== Some SQL queries executable by admin (need fixing to work in new MW1.9.3 environment) ==
+
== See also ==
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties:SQL&xpath:/properties/SQL:query=SHOW+TABLES; Tables]
+
*[[MariaDB]]
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties:SQL&xpath:/properties/SQL:query=DROP+TABLE+xwHistory; Drop]
+
*[https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im Good thread on table lock problems and solutions]
*[{{SERVER}}/index.php?title=SQL&xpath:/properties/SQL:query=DESCRIBE+xwHistory; Info]
+
*[http://tlug.dnho.net/?q=node/276 Convert a MySQL DB from latin1 to UTF8]
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties:SQL&xpath:/properties/SQL:query=SELECT+*+FROM+user+ORDER+BY+user_id; Select User]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties:SQL&xpath:/properties/SQL:query=SELECT+*+FROM+categorylinks; CL]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties:SQL&xpath:/properties/SQL:query=SELECT+*+FROM+cur+WHERE+cur_namespace=14; CUR]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties:SQL&xpath:/properties/SQL:query=SELECT+*+FROM+old+WHERE+old_namespace=14; OLD]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties:SQL&xpath:/properties/SQL:query=DELETE+FROM+user+WHERE+user_id=142; Delete]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties:SQL&xpath:/properties/SQL:query=UPDATE+cur+SET+cur_title='Article_title'+WHERE+cur_id=3047; Update]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties:SQL&xpath:/properties/SQL:query=SELECT+*+FROM+interwiki; IW]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties/SQL:query=UPDATE+interwiki+SET+iw_url='http://www.organicdesign.co.nz/$1'+WHERE+iw_prefix='OD'; IW-Update]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties:SQL&xpath:/properties/SQL:query=INSERT+INTO+interwiki+(iw_prefix,iw_url,iw_local)+VALUES('wikifs','http://www.wikifs.org/$1',0); IW-Insert]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties/SQL:query=SELECT+*+FROM+recentchanges+ORDER+BY+rc_timestamp+DESC+LIMIT+100; RC]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties/SQL:query=SELECT+*+FROM+user_rights; UR]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties/SQL:query=UPDATE+user_rights+SET+ur_rights='+'+WHERE+ur_user=15; makeBot]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties/SQL:query=UPDATE+user+SET+user_password=md5(CONCAT('184-',md5('password')))+WHERE+user_id=184; Reset Password]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties/SQL:query=SELECT+old_id,old_user_text+FROM+old+WHERE+old_title='Nobots'+ORDER+BY+old_timestamp+LIMIT+1; Owner-Old]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties/SQL:query=SELECT+cur_is_new,cur_user_text+FROM+cur+WHERE+cur_title='Nobots'; Owner-Cur]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties/SQL:query=SELECT+cur_title+FROM+cur+WHERE+cur_title+REGEXP('^Sys:'); Sys:User articles]
 
*[{{SERVER}}/wiki/index.php?title=SQL&xpath:/properties/SQL:query=SELECT+cur_title+FROM+cur+WHERE+cur_title+REGEXP('^Sys:')+AND+cur_text+REGEXP('admin'); xwAdmins]
 
 
 
== Documentation ==
 
 
*[http://www.sql-tutorial.com/ SQL Tutorial] ''- A nice tutorial on SQL''
 
*[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)]
 
+
*[https://www.databasestar.com/sql-joins/ Introduction to SQL joins]
== MySQL vs MSSQL ==
 
 
*[http://www.x7media.com/resources/SQL_Differences_Between_Microsoft_SQL_Server_and_MySQL.asp List of common differences between MySQL and MSSQL]
 
*[http://www.x7media.com/resources/SQL_Differences_Between_Microsoft_SQL_Server_and_MySQL.asp List of common differences between MySQL and MSSQL]
 
== MySQL News & Information ==
 
*[http://dev.mysql.com/doc/falcon/en/falcon-overview.html Falcon Overview] ''- the new MySQL 6 Falcon storage engine''
 
*[http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html MySQL Stored procedures & triggers]
 
 
==RODBC==
 
Using RODBC to interact with R requires the user to configure the system as described in the file README after installing [http://cran.stat.auckland.ac.nz/web/packages/RODBC/index.html RODBC].
 
;See
 
*http://www.unixODBC.org
 
*http://www.easysoft.com/developer/interfaces/odbc/linux.html (tutorial using unixODBC)
 
 
== Towards SQL for P2P environments ==
 
*[[MediaWikiLite]]
 
*[[Extension talk:P2P.php|P2P Extension]]
 
*[http://pier.cs.berkeley.edu The PIER Project]
 
*[[w:Prefix Hash Tree|Prefix Hash Trees]]
 
*[http://www.cs.huji.ac.il/~ittaia/papers/AAY-OPODIS05.pdf Skip B-Trees]
 

Revision as of 11:15, 8 June 2018

Broom icon.svg The content of this article requires cleaning up to meet OD's quality standards. Check the wiki best practices for guidelines on improving article and categorisation quality.

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. See MediaWikiLite for information about SQLite support in MediaWiki, or MSSQL for information about Microsoft SQL Server support. We're now using MariaDB for our SQL server which is a drop-in replacement for MySQL but is fully open source and more efficient too.

Backup & restore

See backup for details about this as it's doesn't outside the SQL environment so doesn't belong in this article. But don't forget to set the default character set to the same as the dump used when you import the data!

To restore only a single database from a full dump use the following:

mysql -D DBNAME -o DBNAME < dump.sql

Fixing crashed tables

To fix a crashed table just use repair TABLENAME from the MySQL command line, or to automatically scan all tables and fix them run the following from the shell.

mysqlcheck --auto-repair DBNAME -u USER -p

Database procedures

See also