Difference between revisions of "SQL"
From Organic Design wiki
(MediaWikiLite and MSSQL support) |
(rm old info and add intro to joins) |
||
| (27 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | + | {{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. |
| − | == | + | == 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: | |
| − | + | <source lang="bash"> | |
| − | <bash> | + | mysql -D DBNAME -o DBNAME < dump.sql |
| − | + | </source> | |
| − | |||
| − | </ | ||
| − | |||
| − | + | == Fixing crashed tables == | |
| − | <bash> | + | 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. |
| − | + | <source lang="bash"> | |
| − | + | mysqlcheck --auto-repair DBNAME -u USER -p | |
| − | </ | + | </source> |
| − | |||
| − | === Reset a password | + | == Database procedures == |
| − | + | *[[Reset MySQL root password]] | |
| + | *[[Renaming MySQL tables and databases]] | ||
| + | *[[Rename a MediaWiki database]] | ||
| + | *[[Reset a wiki user password]] | ||
| + | *[[Set a user preference]] | ||
| + | *[[Manually backup a wiki]] | ||
| + | *[[Add a wiki database]] | ||
| − | == | + | == See also == |
| − | + | *[[MariaDB]] | |
| − | + | *[https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im Good thread on table lock problems and solutions] | |
| − | + | *[http://tlug.dnho.net/?q=node/276 Convert a MySQL DB from latin1 to UTF8] | |
| − | |||
| − | |||
| − | |||
| − | See | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | * | ||
| − | * | ||
| − | |||
| − | |||
| − | |||
| − | : | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | *[ | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
*[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] | |
| − | |||
*[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] | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
Revision as of 11:15, 8 June 2018
|
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.sqlFixing 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 -pDatabase procedures
- Reset MySQL root password
- Renaming MySQL tables and databases
- Rename a MediaWiki database
- Reset a wiki user password
- Set a user preference
- Manually backup a wiki
- Add a wiki database
